<?php

namespace App\Console\Commands;

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

class ImportOldKocs extends Command
{
    protected $signature = 'app:import-old-kocs
        {--dry-run : Chạy thử, không ghi vào DB}
        {--update-content : Chỉ cập nhật cột content cho các creator đã import}';

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

    const ORG_HEX = '019e64ae67177381e8bb0e7daeb239ba';

    private array $columnIndices;

    public function __construct()
    {
        parent::__construct();

        $this->columnIndices = array_flip([
            'id', 'creator_name', 'tiktok_link', 'instagram_link',
            'facebook_link', 'contact_email', 'contact_phone', 'contact_zalo',
            'followers', 'gmv', 'items', 'average_views', 'audience', 'search_command',
            'advantages', 'suggested_quote', 'address', 'notes',
            'created_by_id', 'created_at', 'updated_at', 'content',
        ]);
    }

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

        if (!file_exists($sqlPath)) {
            $this->error('Không tìm thấy file 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;
        }

        if ($updateContent) {
            return $this->handleUpdateContent($insertBlocks, $dryRun);
        }

        $orgBinary = hex2bin(self::ORG_HEX);
        $allRows = [];
        $totalRows = 0;

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

            foreach ($rows as $rawValues) {
                $data = [];
                foreach ($columns as $i => $col) {
                    $data[$col] = $rawValues[$i] ?? null;
                }

                $allRows[] = [
                    'id'              => Uuid::uuid7Binary(),
                    'organization_id' => $orgBinary,
                    'created_by_id'   => null,
                    'creator_name'    => $data['creator_name'],
                    'tiktok_link'     => $data['tiktok_link'] ?? null,
                    'instagram_link'  => $data['instagram_link'] ?? null,
                    'facebook_link'   => $data['facebook_link'] ?? null,
                    'contact_email'   => $data['contact_email'] ?? null,
                    'contact_phone'   => $data['contact_phone'] ?? null,
                    'contact_zalo'    => $data['contact_zalo'] ?? null,
                    'followers'       => (int) ($data['followers'] ?? 0),
                    'gmv'             => (int) ($data['gmv'] ?? 0),
                    'items'           => (int) ($data['items'] ?? 0),
                    'average_views'   => isset($data['average_views']) ? (int) $data['average_views'] : null,
                    'audience'        => $data['audience'] ?? null,
                    'advantages'      => $data['advantages'] ?? null,
                    'suggested_quote' => $data['suggested_quote'] !== null ? (int) $data['suggested_quote'] : null,
                    'address'         => $data['address'] ?? null,
                    'notes'           => $data['notes'] ?? null,
                    'content'         => $data['content'] ?? null,
                    'created_at'      => $data['created_at'] ?? null,
                    'updated_at'      => $data['updated_at'] ?? null,
                ];
                $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) {
                $row['id'] = bin2hex($row['id']);
                $row['organization_id'] = bin2hex($row['organization_id']);
                $this->line(json_encode($row, 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('creators')->insert($chunk);
            $bar->advance(count($chunk));
        }

        $bar->finish();
        $this->newLine();
        $this->info("Đã import {$totalRows} KOC vào bảng creators thành công!");

        return self::SUCCESS;
    }

    private function handleUpdateContent(array $insertBlocks, bool $dryRun): int
    {
        $updates = [];

        foreach ($insertBlocks as [$columns, $valuesStr]) {
            $colIndex = array_flip($columns);
            if (!isset($colIndex['creator_name'], $colIndex['content'])) {
                continue;
            }
            $nameIdx = $colIndex['creator_name'];
            $contentIdx = $colIndex['content'];

            $rows = $this->parseValueRows($valuesStr);
            foreach ($rows as $rawValues) {
                $name = $rawValues[$nameIdx] ?? null;
                $name = $this->cleanValue($name);
                if ($name === null) continue;

                $content = $rawValues[$contentIdx] ?? null;
                $content = $this->cleanValue($content);

                $updates[$name] = $content;
            }
        }

        $this->info("Tổng số dòng cần cập nhật content: " . count($updates));

        if ($dryRun) {
            $this->warn('Dry-run: không ghi dữ liệu. Hiển thị 5 dòng đầu:');
            $i = 0;
            foreach ($updates as $name => $content) {
                if ($i >= 5) break;
                $this->line("creator_name: {$name} -> content: " . ($content ?? 'NULL'));
                $i++;
            }
            return self::SUCCESS;
        }

        $updated = 0;
        $bar = $this->output->createProgressBar(count($updates));
        $bar->start();

        foreach ($updates as $name => $content) {
            $affected = DB::table('creators')
                ->where('creator_name', $name)
                ->update(['content' => $content]);

            if ($affected > 0) {
                $updated++;
            }
            $bar->advance();
        }

        $bar->finish();
        $this->newLine();
        $this->info("Đã cập nhật content cho {$updated} creator thành công!");

        return self::SUCCESS;
    }

    private function extractInsertBlocks(string $sql): array
    {
        $blocks = [];

        preg_match_all(
            "/INSERT\s+INTO\s+`kocs`\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;
    }
}
