<?php

namespace App\Console\Commands;

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

class ImportOldCampaignKocs extends Command
{
    protected $signature = 'app:import-old-campaign-kocs
        {--dry-run : Chạy thử, không ghi vào DB}';

    protected $description = 'Import dữ liệu campaign_kocs từ file campaign_kocs.sql vào bảng bookings';

    const ORG_HEX = '019e64ae67177381e8bb0e7daeb239ba';
    const USER_HEX = '019e64e5a46571b199647af9e96fe051';

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

        // --- Bước 1: Xây map campaign_id cũ → mới ---
        $this->info('Đang xây dựng campaign ID map...');
        $campaignIdMap = $this->buildCampaignIdMap();
        $this->line('Map được ' . count($campaignIdMap) . ' campaign.');

        // --- Bước 2: Xây map koc_id cũ → creator UUID ---
        $this->info('Đang xây dựng creator ID map...');
        $creatorIdMap = $this->buildCreatorIdMap();
        $this->line('Map được ' . count($creatorIdMap) . ' creator.');

        // --- Bước 3: Parse campaign_kocs.sql ---
        $sqlPath = base_path('campaign_kocs.sql');
        if (!file_exists($sqlPath)) {
            $this->error('Không tìm thấy file campaign_kocs.sql');
            return self::FAILURE;
        }

        $sql = file_get_contents($sqlPath);
        $insertBlocks = $this->extractInsertBlocks($sql);

        if (empty($insertBlocks)) {
            $this->error('Không tìm thấy INSERT nào trong file SQL');
            return self::FAILURE;
        }

        $orgBinary = hex2bin(self::ORG_HEX);
        $userBinary = hex2bin(self::USER_HEX);
        $allRows = [];
        $bookingIdMap = [];
        $totalRows = 0;
        $skipped = 0;

        foreach ($insertBlocks as [$columns, $valuesStr]) {
            $colIndex = array_flip($columns);
            $rows = $this->parseValueRows($valuesStr);

            foreach ($rows as $rawValues) {
                $oldCampaignId = $this->getVal($rawValues, $colIndex, 'campaign_id');
                $oldKocId = $this->getVal($rawValues, $colIndex, 'koc_id');
                $oldId = $this->getVal($rawValues, $colIndex, 'id');

                // Bỏ qua nếu không tìm thấy campaign hoặc creator trong map
                if (!isset($campaignIdMap[$oldCampaignId])) {
                    $this->warn("Bỏ qua campaign_kocs id=$oldId, không tìm thấy campaign_id=$oldCampaignId");
                    $skipped++;
                    continue;
                }
                if (!isset($creatorIdMap[$oldKocId])) {
                    $this->warn("Bỏ qua campaign_kocs id=$oldId, không tìm thấy koc_id=$oldKocId trong creators");
                    $skipped++;
                    continue;
                }

                $newId = Uuid::uuid7Binary();
                $bookingIdMap[$oldId] = bin2hex($newId);

                $allRows[] = [
                    'id'               => $newId,
                    'organization_id'  => $orgBinary,
                    'campaign_id'      => $campaignIdMap[$oldCampaignId],
                    'creator_id'       => $creatorIdMap[$oldKocId],
                    'created_by_id'    => $userBinary,
                    'assigned_to_id'   => $userBinary,
                    'status'           => $this->getVal($rawValues, $colIndex, 'status', 'not_contacted'),
                    'negotiating_at'   => $this->getVal($rawValues, $colIndex, 'negotiating_at'),
                    'confirmed_at'     => $this->getVal($rawValues, $colIndex, 'confirmed_at'),
                    'completed_at'     => $this->getVal($rawValues, $colIndex, 'completed_at'),
                    'engagement_natural' => $this->getVal($rawValues, $colIndex, 'engagement_natural'),
                    'engagement_ads'   => $this->getVal($rawValues, $colIndex, 'engagement_ads'),
                    'cast_cost'        => (int) ($this->getVal($rawValues, $colIndex, 'cast_cost') ?? 0),
                    'sample_cost'      => (int) ($this->getVal($rawValues, $colIndex, 'sample_cost') ?? 0),
                    'shipping_cost'    => (int) ($this->getVal($rawValues, $colIndex, 'shipping_cost') ?? 0),
                    'order_code'       => $this->getVal($rawValues, $colIndex, 'order_code'),
                    'send_date'        => $this->getVal($rawValues, $colIndex, 'send_date'),
                    'eta_date'         => $this->getVal($rawValues, $colIndex, 'eta_date'),
                    'air_date'         => $this->getVal($rawValues, $colIndex, 'air_date'),
                    'notes'            => $this->getVal($rawValues, $colIndex, 'notes'),
                    'number_of_videos' => (int) ($this->getVal($rawValues, $colIndex, 'number_of_videos') ?? 0),
                    'created_at'       => $this->getVal($rawValues, $colIndex, 'created_at'),
                    'updated_at'       => $this->getVal($rawValues, $colIndex, 'updated_at'),
                ];
                $totalRows++;
            }
        }

        $this->info("Tổng số booking: {$totalRows} (bỏ qua {$skipped})");

        if ($dryRun) {
            $this->warn('Dry-run: không ghi dữ liệu. Hiển thị 3 dòng đầu:');
            foreach (array_slice($allRows, 0, 3) as $row) {
                $display = $row;
                foreach (['id', 'organization_id', 'campaign_id', 'creator_id', 'created_by_id', 'assigned_to_id'] as $binField) {
                    $display[$binField] = bin2hex($display[$binField]);
                }
                $this->line(json_encode($display, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
            }
            return self::SUCCESS;
        }

        // --- Deduplicate & loại bỏ booking đã tồn tại ---
        $existing = DB::table('bookings')
            ->select('campaign_id', 'creator_id')
            ->get()
            ->map(fn($r) => $r->campaign_id . "\0" . $r->creator_id)
            ->toArray();
        $existingKeys = array_flip($existing);

        $uniqueRows = [];
        $seen = [];
        foreach ($allRows as $row) {
            $key = $row['campaign_id'] . "\0" . $row['creator_id'];
            if (isset($existingKeys[$key]) || isset($seen[$key])) continue;
            $seen[$key] = true;
            $uniqueRows[] = $row;
        }

        $deduped = count($allRows) - count($uniqueRows);
        $this->line("Đã loại {$deduped} bản ghi trùng (campaign_id + creator_id).");

        // --- Insert bookings ---
        $bar = $this->output->createProgressBar(count($uniqueRows));
        $bar->start();

        foreach (array_chunk($uniqueRows, 100) as $chunk) {
            DB::table('bookings')->insert($chunk);
            $bar->advance(count($chunk));
        }

        $bar->finish();
        $this->newLine();
        $this->info("Đã import " . count($uniqueRows) . " booking thành công!");

        // --- Lưu booking ID map ---
        $mapDir = storage_path('app/import');
        if (!is_dir($mapDir)) {
            mkdir($mapDir, 0755, true);
        }
        $mapPath = "$mapDir/booking_id_map.json";
        file_put_contents($mapPath, json_encode($bookingIdMap, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));
        $this->info("Đã lưu booking ID map vào $mapPath (" . count($bookingIdMap) . " entries)");

        return self::SUCCESS;
    }

    private function buildCampaignIdMap(): array
    {
        $path = base_path('campaigns.sql');
        if (!file_exists($path)) return [];

        $sql = file_get_contents($path);
        $blocks = $this->extractInsertBlocksFromTable($sql, 'campaigns');

        $oldList = [];
        foreach ($blocks as [$columns, $valuesStr]) {
            $colIndex = array_flip($columns);
            foreach ($this->parseValueRows($valuesStr) as $raw) {
                $id = $this->getVal($raw, $colIndex, 'id');
                $name = $this->getVal($raw, $colIndex, 'campaign_name');
                if ($id !== null && $name !== null) $oldList[(int) $id] = $name;
            }
        }

        $dbList = DB::table('campaigns')
            ->whereIn('campaign_name', array_values($oldList))
            ->get(['id', 'campaign_name'])
            ->keyBy('campaign_name');

        $map = [];
        foreach ($oldList as $oldId => $name) {
            if (isset($dbList[$name])) $map[$oldId] = $dbList[$name]->id;
        }
        return $map;
    }

    private function buildCreatorIdMap(): array
    {
        $path = base_path('kocs.sql');
        if (!file_exists($path)) return [];

        $sql = file_get_contents($path);
        $blocks = $this->extractInsertBlocksFromTable($sql, 'kocs');

        $oldList = [];
        foreach ($blocks as [$columns, $valuesStr]) {
            $colIndex = array_flip($columns);
            foreach ($this->parseValueRows($valuesStr) as $raw) {
                $id = $this->getVal($raw, $colIndex, 'id');
                $name = $this->getVal($raw, $colIndex, 'creator_name');
                if ($id !== null && $name !== null) $oldList[(int) $id] = $name;
            }
        }

        // Lấy tất cả creator từ DB (có thể nhiều hơn 1 bản ghi cho cùng tên)
        $dbList = DB::table('creators')
            ->whereIn('creator_name', array_values(array_unique($oldList)))
            ->get(['id', 'creator_name']);

        // Xây map: với mỗi old_id, lấy creator UUID đầu tiên tìm được theo tên
        $byName = [];
        foreach ($dbList as $c) {
            if (!isset($byName[$c->creator_name])) {
                $byName[$c->creator_name] = $c->id;
            }
        }

        $map = [];
        foreach ($oldList as $oldId => $name) {
            if (isset($byName[$name])) $map[$oldId] = $byName[$name];
        }
        return $map;
    }

    private function extractInsertBlocksFromTable(string $sql, string $tableName): array
    {
        $blocks = [];
        preg_match_all(
            "/INSERT\s+INTO\s+`{$tableName}`\s*\(([^)]+)\)\s*VALUES\s*(.+?);/is",
            $sql,
            $matches,
            PREG_SET_ORDER
        );
        foreach ($matches as $m) {
            preg_match_all("/`([^`]+)`/", trim($m[1]), $colMatches);
            $blocks[] = [$colMatches[1], trim($m[2])];
        }
        return $blocks;
    }

    private function extractInsertBlocks(string $sql): array
    {
        return $this->extractInsertBlocksFromTable($sql, 'campaign_kocs');
    }

    private function getVal(array $rawValues, array $colIndex, string $col, mixed $default = null): mixed
    {
        if (!isset($colIndex[$col])) return $default;
        return $rawValues[$colIndex[$col]] ?? $default;
    }

    private function parseValueRows(string $valuesStr): array
    {
        $rows = [];
        $len = strlen($valuesStr);
        $i = 0;

        while ($i < $len) {
            while ($i < $len && $valuesStr[$i] !== '(') {
                $i++;
            }
            if ($i >= $len) break;

            $depth = 0;
            $inStr = false;
            $strChar = null;
            $rowStart = $i;

            for ($j = $i; $j < $len; $j++) {
                $ch = $valuesStr[$j];

                if ($inStr) {
                    if ($ch === '\\' && $j + 1 < $len) {
                        $j++;
                        continue;
                    }
                    if ($ch === $strChar) {
                        $inStr = false;
                    }
                    continue;
                }

                if ($ch === "'" || $ch === '"') {
                    $inStr = true;
                    $strChar = $ch;
                    continue;
                }

                if ($ch === '(') {
                    $depth++;
                } elseif ($ch === ')') {
                    $depth--;
                    if ($depth === 0) {
                        $inner = substr($valuesStr, $rowStart + 1, $j - $rowStart - 1);
                        $rows[] = $this->parseRowValues($inner);
                        $i = $j + 1;
                        break;
                    }
                }
            }

            if (!isset($j) || $j >= $len) break;
        }

        return $rows;
    }

    private function parseRowValues(string $inner): array
    {
        $values = [];
        $len = strlen($inner);
        $current = '';
        $inStr = false;
        $strChar = null;

        for ($i = 0; $i < $len; $i++) {
            $ch = $inner[$i];

            if ($inStr) {
                if ($ch === '\\' && $i + 1 < $len) {
                    $i++;
                    $current .= $inner[$i];
                    continue;
                }
                if ($ch === $strChar) {
                    $inStr = false;
                }
                $current .= $ch;
                continue;
            }

            if ($ch === "'" || $ch === '"') {
                $inStr = true;
                $strChar = $ch;
                $current .= $ch;
                continue;
            }

            if ($ch === ',') {
                $values[] = $this->cleanValue(trim($current));
                $current = '';
                continue;
            }

            $current .= $ch;
        }

        if (trim($current) !== '') {
            $values[] = $this->cleanValue(trim($current));
        }

        return $values;
    }

    private function cleanValue(?string $val): mixed
    {
        if ($val === null || $val === '') {
            return null;
        }

        $upper = strtoupper($val);
        if ($upper === 'NULL') {
            return null;
        }

        if ((str_starts_with($val, "'") && str_ends_with($val, "'"))
            || (str_starts_with($val, '"') && str_ends_with($val, '"'))) {
            $val = substr($val, 1, -1);
            $val = str_replace(["\\'", '\\"'], ["'", '"'], $val);
            return $val;
        }

        return $val;
    }
}
