<?php

namespace App\Console\Commands;

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

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

    protected $description = 'Import dữ liệu campaign brief từ file campaign_briefs.sql vào bảng campaign_briefs (mới)';

    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 map campaign ID...');
        $campaignIdMap = $this->buildCampaignIdMap();
        if (empty($campaignIdMap)) {
            $this->warn('Không tìm thấy campaign nào để map. Kiểm tra lại dữ liệu.');
        } else {
            $this->line("Map được " . count($campaignIdMap) . " campaign.");
        }

        // --- Bước 2: Parse campaign_briefs.sql ---
        $sqlPath = base_path('campaign_briefs.sql');
        if (!file_exists($sqlPath)) {
            $this->error('Không tìm thấy file campaign_briefs.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;
        }

        $allRows = [];
        $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');

                if (!isset($campaignIdMap[$oldCampaignId])) {
                    $this->warn("Bỏ qua brief, không tìm thấy campaign_id=$oldCampaignId trong map");
                    $skipped++;
                    continue;
                }

                $allRows[] = [
                    'id'                => Uuid::uuid7Binary(),
                    'campaign_id'       => $campaignIdMap[$oldCampaignId],
                    'product_name'      => $this->getVal($rawValues, $colIndex, 'product_name'),
                    'product_type'      => $this->getVal($rawValues, $colIndex, 'product_type'),
                    'form_shape'        => $this->getVal($rawValues, $colIndex, 'form_shape'),
                    'material'          => $this->getVal($rawValues, $colIndex, 'material'),
                    'color'             => $this->getVal($rawValues, $colIndex, 'color'),
                    'size'              => $this->getVal($rawValues, $colIndex, 'size'),
                    'price'             => $this->getVal($rawValues, $colIndex, 'price'),
                    'target_audience'   => $this->getVal($rawValues, $colIndex, 'target_audience'),
                    'category'          => $this->getVal($rawValues, $colIndex, 'category'),
                    'video_type'        => $this->getVal($rawValues, $colIndex, 'video_type'),
                    'video_goal'        => $this->getVal($rawValues, $colIndex, 'video_goal'),
                    'shop_handle'       => $this->getVal($rawValues, $colIndex, 'shop_handle'),
                    'koc_voucher_code'  => $this->getVal($rawValues, $colIndex, 'koc_voucher_code'),
                    'product_link'      => $this->getVal($rawValues, $colIndex, 'product_link'),
                    'product_hashtags'  => $this->getVal($rawValues, $colIndex, 'product_hashtags'),
                    'category_hashtags' => $this->getVal($rawValues, $colIndex, 'category_hashtags'),
                    'product_image_url' => $this->getVal($rawValues, $colIndex, 'product_image_url'),
                    'talks'             => $this->getVal($rawValues, $colIndex, 'talks'),
                    'guidelines'        => $this->getVal($rawValues, $colIndex, 'guidelines'),
                    'hooks'             => $this->getVal($rawValues, $colIndex, 'hooks'),
                    'ctas'              => $this->getVal($rawValues, $colIndex, 'ctas'),
                    'notes_json'        => $this->getVal($rawValues, $colIndex, 'notes'),
                    'created_at'        => $this->getVal($rawValues, $colIndex, 'created_at'),
                    'updated_at'        => $this->getVal($rawValues, $colIndex, 'updated_at'),
                ];
                $totalRows++;
            }
        }

        $this->info("Tổng số brief: {$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;
                $display['id'] = bin2hex($display['id']);
                $display['campaign_id'] = bin2hex($display['campaign_id']);
                $display['price'] = $display['price'] !== null ? (string) $display['price'] : null;
                $this->line(json_encode($display, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
            }
            return self::SUCCESS;
        }

        $bar = $this->output->createProgressBar($totalRows);
        $bar->start();

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

        $bar->finish();
        $this->newLine();
        $this->info("Đã import {$totalRows} campaign brief thành công!");

        return self::SUCCESS;
    }

    private function buildCampaignIdMap(): array
    {
        $campaignsPath = base_path('campaigns.sql');
        if (!file_exists($campaignsPath)) {
            $this->error('Không tìm thấy file campaigns.sql để xây map');
            return [];
        }

        $sql = file_get_contents($campaignsPath);
        $insertBlocks = $this->extractInsertBlocksFromCampaigns($sql);

        // Map old_id → campaign_name
        $oldCampaigns = [];
        foreach ($insertBlocks as [$columns, $valuesStr]) {
            $colIndex = array_flip($columns);
            $rows = $this->parseValueRows($valuesStr);
            foreach ($rows as $rawValues) {
                $id = $this->getVal($rawValues, $colIndex, 'id');
                $name = $this->getVal($rawValues, $colIndex, 'campaign_name');
                if ($id !== null && $name !== null) {
                    $oldCampaigns[(int) $id] = $name;
                }
            }
        }

        if (empty($oldCampaigns)) {
            return [];
        }

        // Query DB để lấy new UUID theo campaign_name
        $dbCampaigns = DB::table('campaigns')
            ->whereIn('campaign_name', array_values($oldCampaigns))
            ->get(['id', 'campaign_name'])
            ->keyBy('campaign_name');

        // Build map: old_id → binary uuid
        $map = [];
        foreach ($oldCampaigns as $oldId => $name) {
            if (isset($dbCampaigns[$name])) {
                $map[$oldId] = $dbCampaigns[$name]->id;
            } else {
                $this->warn("Không tìm thấy campaign '$name' (id cũ: $oldId) trong DB");
            }
        }

        return $map;
    }

    private function extractInsertBlocksFromCampaigns(string $sql): array
    {
        $blocks = [];
        preg_match_all(
            "/INSERT\s+INTO\s+`campaigns`\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
    {
        $blocks = [];
        preg_match_all(
            "/INSERT\s+INTO\s+`campaign_briefs`\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 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;
    }
}
