<?php

namespace App\Console\Commands;

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

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

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

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

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

        if (!file_exists($sqlPath)) {
            $this->error('Không tìm thấy file campaigns.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::CREATED_BY_HEX);
        $allRows = [];
        $totalRows = 0;

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

            foreach ($rows as $rawValues) {
                $allRows[] = [
                    'id'               => Uuid::uuid7Binary(),
                    'organization_id'  => $orgBinary,
                    'created_by_id'    => $userBinary,
                    'campaign_name'    => $this->getVal($rawValues, $colIndex, 'campaign_name'),
                    'product_name'     => $this->getVal($rawValues, $colIndex, 'product_name'),
                    'assigned_to_id' => null,
                    'status'           => $this->getVal($rawValues, $colIndex, 'status', 'draft'),
                    'start_date'       => $this->getVal($rawValues, $colIndex, 'start_date'),
                    'end_date'         => $this->getVal($rawValues, $colIndex, 'end_date'),
                    'description'      => $this->getVal($rawValues, $colIndex, 'description'),
                    'notes'            => $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ố dòng dữ liệu cũ: {$totalRows}");

        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['organization_id'] = bin2hex($display['organization_id']);
                $display['created_by_id'] = $display['created_by_id'] ? bin2hex($display['created_by_id']) : 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('campaigns')->insert($chunk);
            $bar->advance(count($chunk));
        }

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

        return self::SUCCESS;
    }

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

    private function extractInsertBlocks(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) {
            $colStr = trim($m[1]);
            preg_match_all("/`([^`]+)`/", $colStr, $colMatches);
            $columns = $colMatches[1];
            $blocks[] = [$columns, trim($m[2])];
        }

        return $blocks;
    }

    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;
        }

        if (is_numeric($val)) {
            if (ctype_digit($val) || (str_starts_with($val, '-') && ctype_digit(substr($val, 1)))) {
                return (int) $val;
            }
            return (float) $val;
        }

        return $val;
    }
}
