sam-api/app/Console/Commands/MigrateBDModelsPrices.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

/**
 * chandj 원본 가격 테이블 → items + item_details + prices 마이그레이션
 *
 * 레거시 chandj DB의 BDmodels, price_motor, price_raw_materials,
 * price_shaft, price_pipe, price_angle, price_smokeban 데이터를
 * items + item_details + prices 통합 구조로 마이그레이션
 */
class MigrateBDModelsPrices extends Command
{
    protected $signature = 'kd:migrate-prices
        {--dry-run : 실제 DB 변경 없이 미리보기}
        {--fresh : 기존 EST-* 항목 삭제 후 재생성}';

    protected $description = '경동 견적 단가를 chandj 원본에서 items+item_details+prices로 마이그레이션';

    private const TENANT_ID = 287;

    private int $created = 0;

    private int $updated = 0;

    private int $skipped = 0;

    private int $deleted = 0;

    public function handle(): int
    {
        $dryRun = $this->option('dry-run');
        $fresh = $this->option('fresh');

        $this->info('=== 경동 견적 단가 마이그레이션 (chandj 원본) ===');
        $this->info($dryRun ? '[DRY RUN] 실제 변경 없음' : '[LIVE] DB에 반영합니다');
        $this->newLine();

        DB::beginTransaction();

        try {
            // --fresh: 기존 EST-* 항목 삭제
            if ($fresh) {
                $this->cleanExistingEstItems($dryRun);
            }

            // 1. BDmodels (절곡품: 케이스, 가이드레일, 하단마감재, 마구리, 연기차단재, L바, 보강평철)
            $this->migrateBDModels($dryRun);

            // 2. price_motor (모터 + 제어기)
            $this->migrateMotors($dryRun);

            // 3. price_raw_materials (원자재: 실리카, 화이바, 와이어 등)
            $this->migrateRawMaterials($dryRun);

            // 4. price_shaft (감기샤프트)
            $this->migrateShafts($dryRun);

            // 5. price_pipe (각파이프)
            $this->migratePipes($dryRun);

            // 6. price_angle (앵글)
            $this->migrateAngles($dryRun);

            // 7. price_smokeban (연기차단재 - BDmodels에 없는 경우 보완)
            $this->migrateSmokeBan($dryRun);

            if ($dryRun) {
                DB::rollBack();
                $this->warn('[DRY RUN] 롤백 완료');
            } else {
                DB::commit();
                $this->info('커밋 완료');
            }

            $this->newLine();
            $this->info("생성: {$this->created}건, 업데이트: {$this->updated}건, 스킵: {$this->skipped}건, 삭제: {$this->deleted}건");

            return Command::SUCCESS;
        } catch (\Exception $e) {
            DB::rollBack();
            $this->error("오류: {$e->getMessage()}");
            $this->error($e->getTraceAsString());

            return Command::FAILURE;
        }
    }

    /**
     * 기존 EST-* 항목 삭제 (--fresh 옵션)
     */
    private function cleanExistingEstItems(bool $dryRun): void
    {
        $this->info('--- 기존 EST-* 항목 삭제 ---');

        $items = DB::table('items')
            ->where('tenant_id', self::TENANT_ID)
            ->where('code', 'LIKE', 'EST-%')
            ->whereNull('deleted_at')
            ->get(['id', 'code']);

        foreach ($items as $item) {
            $this->line("  [삭제] {$item->code}");
            if (! $dryRun) {
                DB::table('prices')->where('item_id', $item->id)->delete();
                DB::table('item_details')->where('item_id', $item->id)->delete();
                DB::table('items')->where('id', $item->id)->delete();
            }
            $this->deleted++;
        }
    }

    /**
     * chandj.BDmodels → items + item_details + prices
     */
    private function migrateBDModels(bool $dryRun): void
    {
        $this->info('--- BDmodels (절곡품) ---');

        $rows = DB::connection('chandj')->select("
            SELECT model_name, seconditem, finishing_type, spec, unitprice, description
            FROM BDmodels
            WHERE is_deleted = 0
            ORDER BY model_name, seconditem, finishing_type, spec
        ");

        foreach ($rows as $row) {
            $modelName = trim($row->model_name ?? '');
            $secondItem = trim($row->seconditem ?? '');
            $finishingType = trim($row->finishing_type ?? '');
            $spec = trim($row->spec ?? '');
            $unitPrice = (float) str_replace(',', '', $row->unitprice ?? '0');

            // finishing_type 정규화: 'SUS마감' → 'SUS', 'EGI마감' → 'EGI'
            $finishingType = str_replace('마감', '', $finishingType);

            if (empty($secondItem) || $unitPrice <= 0) {
                $this->skipped++;

                continue;
            }

            $codeParts = ['BD', $secondItem];
            if ($modelName) {
                $codeParts[] = $modelName;
            }
            if ($finishingType) {
                $codeParts[] = $finishingType;
            }
            if ($spec) {
                $codeParts[] = $spec;
            }
            $code = implode('-', $codeParts);

            $nameParts = [$secondItem];
            if ($modelName) {
                $nameParts[] = $modelName;
            }
            if ($finishingType) {
                $nameParts[] = $finishingType;
            }
            if ($spec) {
                $nameParts[] = $spec;
            }
            $name = implode(' ', $nameParts);

            $this->upsertEstimateItem(
                code: $code,
                name: $name,
                productCategory: 'bdmodels',
                partType: $secondItem,
                specification: $spec ?: null,
                attributes: array_filter([
                    'model_name' => $modelName ?: null,
                    'finishing_type' => $finishingType ?: null,
                    'bdmodel_source' => 'BDmodels',
                    'description' => $row->description ?: null,
                ]),
                salesPrice: $unitPrice,
                note: 'chandj.BDmodels',
                dryRun: $dryRun
            );
        }
    }

    /**
     * chandj.price_motor → 모터 + 제어기
     *
     * col1: 전압 (220, 380, 제어기, 방화, 방범)
     * col2: 용량/종류 (150K(S), 300K, 매립형, 노출형 등)
     * col13: 판매가
     */
    private function migrateMotors(bool $dryRun): void
    {
        $this->info('--- price_motor (모터/제어기) ---');

        $row = DB::connection('chandj')->selectOne(
            "SELECT itemList FROM price_motor WHERE is_deleted IS NULL OR is_deleted = 0 ORDER BY NUM LIMIT 1"
        );
        if (! $row) {
            return;
        }

        $items = json_decode($row->itemList, true);

        foreach ($items as $item) {
            $category = trim($item['col1'] ?? '');  // 220, 380, 제어기, 방화, 방범
            $name = trim($item['col2'] ?? '');       // 150K(S), 매립형 등
            $price = (int) str_replace(',', '', $item['col13'] ?? '0');

            if (empty($name) || $price <= 0) {
                $this->skipped++;

                continue;
            }

            // 카테고리 분류
            if (in_array($category, ['220', '380'])) {
                $productCategory = 'motor';
                $code = "EST-MOTOR-{$category}V-{$name}";
                $displayName = "모터 {$name} ({$category}V)";
                $partType = $name;
            } elseif ($category === '제어기') {
                $productCategory = 'controller';
                $code = "EST-CTRL-{$name}";
                $displayName = "제어기 {$name}";
                $partType = $name;
            } else {
                // 방화, 방범 등
                $productCategory = 'controller';
                $code = "EST-CTRL-{$category}-{$name}";
                $displayName = "{$category} {$name}";
                $partType = "{$category} {$name}";
            }

            $this->upsertEstimateItem(
                code: $code,
                name: $displayName,
                productCategory: $productCategory,
                partType: $partType,
                specification: null,
                attributes: ['voltage' => $category, 'source' => 'price_motor'],
                salesPrice: (float) $price,
                note: 'chandj.price_motor',
                dryRun: $dryRun
            );
        }
    }

    /**
     * chandj.price_raw_materials → 원자재
     *
     * col1: 카테고리 (슬랫, 스크린)
     * col2: 품명 (방화, 실리카, 화이바, 와이어 등)
     * col13: 판매단가
     */
    private function migrateRawMaterials(bool $dryRun): void
    {
        $this->info('--- price_raw_materials (원자재) ---');

        $row = DB::connection('chandj')->selectOne(
            "SELECT itemList FROM price_raw_materials WHERE is_deleted IS NULL OR is_deleted = 0 ORDER BY registedate DESC LIMIT 1"
        );
        if (! $row) {
            return;
        }

        $items = json_decode($row->itemList, true);

        foreach ($items as $item) {
            $category = trim($item['col1'] ?? '');
            $name = trim($item['col2'] ?? '');
            $price = (int) str_replace(',', '', $item['col13'] ?? '0');

            if (empty($name) || $price <= 0) {
                $this->skipped++;

                continue;
            }

            $code = "EST-RAW-{$category}-{$name}";
            $displayName = "{$category} {$name}";

            $this->upsertEstimateItem(
                code: $code,
                name: $displayName,
                productCategory: 'raw_material',
                partType: $name,
                specification: $category,
                attributes: ['category' => $category, 'source' => 'price_raw_materials'],
                salesPrice: (float) $price,
                note: 'chandj.price_raw_materials',
                dryRun: $dryRun
            );
        }
    }

    /**
     * chandj.price_shaft → 감기샤프트
     *
     * col4: 인치 (3, 4, 5, 6, 8, 10, 12)
     * col10: 길이 (m)
     * col19: 판매가
     */
    private function migrateShafts(bool $dryRun): void
    {
        $this->info('--- price_shaft (감기샤프트) ---');

        $row = DB::connection('chandj')->selectOne(
            "SELECT itemList FROM price_shaft WHERE is_deleted IS NULL OR is_deleted = 0 ORDER BY NUM LIMIT 1"
        );
        if (! $row) {
            return;
        }

        $items = json_decode($row->itemList, true);

        foreach ($items as $item) {
            $inch = trim($item['col4'] ?? '');
            $lengthM = trim($item['col10'] ?? '');
            $price = (int) str_replace(',', '', $item['col19'] ?? '0');

            if (empty($inch) || empty($lengthM) || $price <= 0) {
                $this->skipped++;

                continue;
            }

            $code = "EST-SHAFT-{$inch}-{$lengthM}";
            $name = "감기샤프트 {$inch}인치 {$lengthM}m";

            $this->upsertEstimateItem(
                code: $code,
                name: $name,
                productCategory: 'shaft',
                partType: $inch,
                specification: $lengthM,
                attributes: ['source' => 'price_shaft'],
                salesPrice: (float) $price,
                note: 'chandj.price_shaft',
                dryRun: $dryRun
            );
        }
    }

    /**
     * chandj.price_pipe → 각파이프
     *
     * col4: 두께 (1.4, 2)
     * col2: 길이 (3,000 / 6,000)
     * col8: 판매가
     */
    private function migratePipes(bool $dryRun): void
    {
        $this->info('--- price_pipe (각파이프) ---');

        $row = DB::connection('chandj')->selectOne(
            "SELECT itemList FROM price_pipe WHERE (is_deleted IS NULL OR is_deleted = 0 OR is_deleted = '') ORDER BY NUM LIMIT 1"
        );
        if (! $row) {
            return;
        }

        $items = json_decode($row->itemList, true);

        foreach ($items as $item) {
            $thickness = trim($item['col4'] ?? '');
            $length = (int) str_replace(',', '', $item['col2'] ?? '0');
            $price = (int) str_replace(',', '', $item['col8'] ?? '0');

            if (empty($thickness) || $length <= 0 || $price <= 0) {
                $this->skipped++;

                continue;
            }

            $code = "EST-PIPE-{$thickness}-{$length}";
            $name = "각파이프 {$thickness}T {$length}mm";

            $this->upsertEstimateItem(
                code: $code,
                name: $name,
                productCategory: 'pipe',
                partType: $thickness,
                specification: (string) $length,
                attributes: ['spec' => $item['col3'] ?? '', 'source' => 'price_pipe'],
                salesPrice: (float) $price,
                note: 'chandj.price_pipe',
                dryRun: $dryRun
            );
        }
    }

    /**
     * chandj.price_angle → 앵글 (bracket + main 분리)
     *
     * bracket angle (모터 받침용): col2가 텍스트 (스크린용, 철제300K 등)
     *   - col2: 검색옵션, col3: 브라켓크기, col4: 앵글타입, col19: 판매가
     *
     * main angle (부자재용): col2가 숫자 (4 등)
     *   - col4: 종류 (앵글3T, 앵글4T), col10: 길이 (2.5, 10), col19: 판매가
     */
    private function migrateAngles(bool $dryRun): void
    {
        $this->info('--- price_angle (앵글) ---');

        $row = DB::connection('chandj')->selectOne(
            "SELECT itemList FROM price_angle WHERE is_deleted IS NULL OR is_deleted = 0 ORDER BY NUM LIMIT 1"
        );
        if (! $row) {
            return;
        }

        $items = json_decode($row->itemList, true);

        foreach ($items as $item) {
            $col2 = trim($item['col2'] ?? '');
            $col3 = trim($item['col3'] ?? '');
            $col4 = trim($item['col4'] ?? '');
            $col10 = trim($item['col10'] ?? '');
            $price = (int) str_replace(',', '', $item['col19'] ?? '0');

            if ($price <= 0) {
                $this->skipped++;

                continue;
            }

            // col2가 숫자이면 main angle, 텍스트이면 bracket angle
            if (is_numeric($col2)) {
                // Main angle (부자재용): col4=앵글3T, col10=2.5
                if (empty($col4) || empty($col10)) {
                    $this->skipped++;

                    continue;
                }

                $code = "EST-ANGLE-MAIN-{$col4}-{$col10}";
                $name = "앵글 {$col4} {$col10}m";

                $this->upsertEstimateItem(
                    code: $code,
                    name: $name,
                    productCategory: 'angle_main',
                    partType: $col4,
                    specification: $col10,
                    attributes: ['source' => 'price_angle'],
                    salesPrice: (float) $price,
                    note: 'chandj.price_angle (main)',
                    dryRun: $dryRun
                );
            } else {
                // Bracket angle (모터 받침용): col2=스크린용, col3=380*180
                if (empty($col2)) {
                    $this->skipped++;

                    continue;
                }

                $code = "EST-ANGLE-BRACKET-{$col2}";
                $name = "모터받침 앵글 {$col2}";

                $this->upsertEstimateItem(
                    code: $code,
                    name: $name,
                    productCategory: 'angle_bracket',
                    partType: $col2,
                    specification: $col3 ?: null,
                    attributes: [
                        'angle_type' => $col4,
                        'source' => 'price_angle',
                    ],
                    salesPrice: (float) $price,
                    note: 'chandj.price_angle (bracket)',
                    dryRun: $dryRun
                );
            }
        }
    }

    /**
     * chandj.price_smokeban → 연기차단재
     *
     * col2: 용도 (레일용, 케이스용)
     * col11: 판매가
     */
    private function migrateSmokeBan(bool $dryRun): void
    {
        $this->info('--- price_smokeban (연기차단재) ---');

        $row = DB::connection('chandj')->selectOne(
            "SELECT itemList FROM price_smokeban WHERE is_deleted IS NULL OR is_deleted = 0 ORDER BY NUM LIMIT 1"
        );
        if (! $row) {
            return;
        }

        $items = json_decode($row->itemList, true);

        foreach ($items as $item) {
            $usage = trim($item['col2'] ?? '');
            $price = (int) str_replace(',', '', $item['col11'] ?? '0');

            if (empty($usage) || $price <= 0) {
                $this->skipped++;

                continue;
            }

            $code = "EST-SMOKE-{$usage}";
            $name = "연기차단재 {$usage}";

            $this->upsertEstimateItem(
                code: $code,
                name: $name,
                productCategory: 'smokeban',
                partType: $usage,
                specification: null,
                attributes: ['source' => 'price_smokeban'],
                salesPrice: (float) $price,
                note: 'chandj.price_smokeban',
                dryRun: $dryRun
            );
        }
    }

    /**
     * 견적 품목 생성 또는 가격 업데이트
     */
    private function upsertEstimateItem(
        string $code,
        string $name,
        string $productCategory,
        string $partType,
        ?string $specification,
        array $attributes,
        float $salesPrice,
        string $note,
        bool $dryRun
    ): void {
        $existing = DB::table('items')
            ->where('tenant_id', self::TENANT_ID)
            ->where('code', $code)
            ->whereNull('deleted_at')
            ->first();

        if ($existing) {
            // 가격 업데이트
            $currentPrice = DB::table('prices')
                ->where('item_id', $existing->id)
                ->where('status', 'active')
                ->orderByDesc('id')
                ->value('sales_price');

            if ((float) $currentPrice === $salesPrice) {
                $this->skipped++;

                return;
            }

            $this->line("  [업데이트] {$code} 가격: " . number_format($currentPrice ?? 0) . " → " . number_format($salesPrice));

            if (! $dryRun) {
                // 기존 가격 비활성화
                DB::table('prices')
                    ->where('item_id', $existing->id)
                    ->where('status', 'active')
                    ->update(['status' => 'inactive', 'updated_at' => now()]);

                // 새 가격 추가
                DB::table('prices')->insert([
                    'tenant_id' => self::TENANT_ID,
                    'item_type_code' => 'PT',
                    'item_id' => $existing->id,
                    'sales_price' => $salesPrice,
                    'effective_from' => now()->toDateString(),
                    'status' => 'active',
                    'note' => $note,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            $this->updated++;

            return;
        }

        // 신규 생성
        $this->line("  [생성] {$code} ({$name}) = " . number_format($salesPrice));

        if ($dryRun) {
            $this->created++;

            return;
        }

        $now = now();

        $itemId = DB::table('items')->insertGetId([
            'tenant_id' => self::TENANT_ID,
            'item_type' => 'PT',
            'code' => $code,
            'name' => $name,
            'unit' => 'EA',
            'attributes' => json_encode($attributes, JSON_UNESCAPED_UNICODE),
            'is_active' => true,
            'created_at' => $now,
            'updated_at' => $now,
        ]);

        DB::table('item_details')->insert([
            'item_id' => $itemId,
            'product_category' => $productCategory,
            'part_type' => $partType,
            'specification' => $specification,
            'item_name' => $name,
            'is_purchasable' => true,
            'created_at' => $now,
            'updated_at' => $now,
        ]);

        DB::table('prices')->insert([
            'tenant_id' => self::TENANT_ID,
            'item_type_code' => 'PT',
            'item_id' => $itemId,
            'sales_price' => $salesPrice,
            'effective_from' => $now->toDateString(),
            'status' => 'active',
            'note' => $note,
            'created_at' => $now,
            'updated_at' => $now,
        ]);

        $this->created++;
    }
}