<?php

namespace App\Console\Commands;

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

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

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

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

        // --- Bước 1: Load booking ID map ---
        $mapPath = storage_path('app/import/booking_id_map.json');
        if (!file_exists($mapPath)) {
            $this->error("Không tìm thấy file map: $mapPath");
            $this->warn('Chạy app:import-old-campaign-kocs trước để tạo map.');
            return self::FAILURE;
        }
        $bookingIdMap = json_decode(file_get_contents($mapPath), true);
        if (empty($bookingIdMap)) {
            $this->error('File map rỗng');
            return self::FAILURE;
        }
        $this->line("Đã load booking ID map: " . count($bookingIdMap) . " entries");

        // Lọc map: chỉ giữ booking_id thực sự tồn tại trong DB
        $hexIds = array_values($bookingIdMap);
        $existingHex = DB::table('bookings')
            ->whereIn('id', array_map('hex2bin', $hexIds))
            ->pluck('id')
            ->map(fn($b) => bin2hex($b))
            ->toArray();
        $existingSet = array_flip($existingHex);
        $validMap = [];
        foreach ($bookingIdMap as $oldKocId => $hex) {
            if (isset($existingSet[$hex])) {
                $validMap[$oldKocId] = $hex;
            } else {
                $this->warn("campaign_koc_id=$oldKocId -> booking $hex không tồn tại trong DB, bỏ qua");
            }
        }
        $bookingIdMap = $validMap;
        $this->line("Sau khi lọc: " . count($bookingIdMap) . " entries hợp lệ");

        // --- Bước 2: Parse video_links.sql ---
        $sqlPath = base_path('video_links.sql');
        if (!file_exists($sqlPath)) {
            $this->error('Không tìm thấy file video_links.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) {
                $oldKocId = $this->getVal($rawValues, $colIndex, 'campaign_koc_id');
                $oldId = $this->getVal($rawValues, $colIndex, 'id');

                if (!isset($bookingIdMap[$oldKocId])) {
                    $this->warn("Bỏ qua video id=$oldId, không tìm thấy campaign_koc_id=$oldKocId trong booking map");
                    $skipped++;
                    continue;
                }

                $url = $this->getVal($rawValues, $colIndex, 'url', '');
                if ($url === '' || $url === null) {
                    $this->warn("Bỏ qua video id=$oldId, url trống");
                    $skipped++;
                    continue;
                }

                $allRows[] = [
                    'id'         => Uuid::uuid7Binary(),
                    'booking_id' => hex2bin($bookingIdMap[$oldKocId]),
                    'url'        => $this->getVal($rawValues, $colIndex, 'url', ''),
                    'status'     => $this->getVal($rawValues, $colIndex, 'status', 'pending'),
                    'spark_ads'  => $this->getVal($rawValues, $colIndex, 'spark_ads', ''),
                    'created_at' => $this->getVal($rawValues, $colIndex, 'created_at'),
                    'updated_at' => $this->getVal($rawValues, $colIndex, 'updated_at'),
                ];
                $totalRows++;
            }
        }

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

        // --- Deduplicate ---
        $existing = DB::table('videos')
            ->select('booking_id', 'url')
            ->where('url', '!=', '')
            ->get()
            ->map(fn($r) => $r->booking_id . "\0" . $r->url)
            ->toArray();
        $existingKeys = array_flip($existing);

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

        $deduped = count($allRows) - count($uniqueRows);
        $this->line("Đã loại {$deduped} video trùng (booking_id + url).");

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

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

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

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

        return self::SUCCESS;
    }

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