<?php

namespace App\Console\Commands;

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

class MergeDevTables extends Command
{
    protected $signature = 'merge:devtables 
                            {--dry-run : Do not execute queries}
                            {--with-data : Copy missing data from dev to prod}';

    protected $description = 'Safely merge Dev DB into Prod DB avoiding duplicate tables, columns, indexes, and foreign keys';

    public function handle()
    {
        $dryRun = $this->option('dry-run');
        $withData = $this->option('with-data');

        $this->info("Starting Dev → Prod merge");
        if ($dryRun)
            $this->warn("DRY RUN MODE ENABLED");
        if ($withData)
            $this->warn("DATA COPY ENABLED");

        $dev = DB::connection('dev_mysql');
        $prod = DB::connection('mysql');

        // Fetch tables
        $devTables = $this->getTables($dev);
        $prodTables = $this->getTables($prod);

        $missingTables = array_diff($devTables, $prodTables);
        $existingTables = array_intersect($devTables, $prodTables);

        // 1️⃣ Create missing tables safely
        foreach ($missingTables as $table) {
            $this->line("<fg=green>NEW TABLE CREATED:</> $table");

            $create = $dev->select("SHOW CREATE TABLE `$table`");
            $sql = $create[0]->{'Create Table'};

            // Remove foreign keys temporarily
            $sqlNoFK = preg_replace('/, CONSTRAINT.*FOREIGN KEY.*\)/i', ')', $sql);

            $this->runSql($prod, $sqlNoFK, $dryRun);

            // Verify table exists
            if (!in_array($table, $this->getTables($prod))) {
                $this->error("Table '$table' failed to create. Merge stopped.");
                return 1;
            }
        }

        // Highlight existing tables
        foreach ($existingTables as $table) {
            $this->line("<fg=yellow>EXISTING TABLE:</> $table");
        }

        // 2️⃣ Sync columns safely
        foreach (array_merge($missingTables, $existingTables) as $table) {
            $this->syncColumns($dev, $prod, $table, $dryRun);
        }

        // 3️⃣ Sync indexes safely
        foreach (array_merge($missingTables, $existingTables) as $table) {
            $this->syncIndexes($dev, $prod, $table, $dryRun);
        }

        // 4️⃣ Sync foreign keys safely
        foreach (array_merge($missingTables, $existingTables) as $table) {
            $this->syncForeignKeys($dev, $prod, $table, $dryRun);
        }

        // 5️⃣ Optionally copy data
        if ($withData) {
            foreach (array_merge($missingTables, $existingTables) as $table) {
                $this->syncData($dev, $prod, $table, $dryRun);
            }
        }

        $this->info("Merge completed successfully.");
        return 0;
    }

    private function getTables($conn)
    {
        return collect($conn->select("SHOW TABLES"))
            ->map(fn($t) => array_values((array) $t)[0])
            ->filter()
            ->toArray();
    }

    private function runSql($conn, $sql, $dryRun)
    {
        if ($dryRun) {
            $this->line("DRY: $sql");
            return;
        }
        $conn->statement($sql);
    }

    private function syncColumns($dev, $prod, $table, $dryRun)
    {
        $devCols = collect($dev->select("SHOW FULL COLUMNS FROM `$table`"));

        foreach ($devCols as $col) {
            // Reload prod columns fresh before each addition
            $prodCols = collect($prod->select("SHOW FULL COLUMNS FROM `$table`"));
            $prodNames = $prodCols->pluck('Field')->map(fn($f) => strtolower($f))->toArray();

            if (in_array(strtolower($col->Field), $prodNames)) {
                $this->line("Column {$col->Field} already exists, skipping.");
                continue;
            }

            $nullable = $col->Null === 'YES' ? 'NULL' : 'NOT NULL';
            $default = is_null($col->Default) ? '' : "DEFAULT '" . addslashes($col->Default) . "'";
            $extra = $col->Extra ?? '';

            $sql = "ALTER TABLE `$table` ADD `{$col->Field}` {$col->Type} {$nullable} {$default} {$extra}";
            $this->line("Adding column {$col->Field}");
            $this->runSql($prod, $sql, $dryRun);
        }
    }


    private function syncIndexes($dev, $prod, $table, $dryRun)
    {
        $devIndexes = collect($dev->select("SHOW INDEX FROM `$table`"))->groupBy('Key_name');
        $prodIndexes = collect($prod->select("SHOW INDEX FROM `$table`"))->pluck('Key_name')->unique()->toArray();

        foreach ($devIndexes as $key => $indexes) {
            if ($key === 'PRIMARY' || in_array($key, $prodIndexes))
                continue;

            $columns = $indexes->pluck('Column_name')->implode('`,`');
            $unique = $indexes->first()->Non_unique == 0 ? 'UNIQUE' : '';

            $sql = "ALTER TABLE `$table` ADD $unique INDEX `$key` (`$columns`)";
            $this->line("Adding index $key");
            $this->runSql($prod, $sql, $dryRun);
        }
    }

    private function syncForeignKeys($dev, $prod, $table, $dryRun)
    {
        $devFK = $dev->select("
            SELECT CONSTRAINT_NAME, COLUMN_NAME, 
                   REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE TABLE_NAME = ? AND REFERENCED_TABLE_NAME IS NOT NULL
            AND TABLE_SCHEMA = DATABASE()
        ", [$table]);

        $prodFK = $prod->select("
            SELECT CONSTRAINT_NAME
            FROM information_schema.TABLE_CONSTRAINTS
            WHERE TABLE_NAME = ? AND CONSTRAINT_TYPE='FOREIGN KEY'
            AND TABLE_SCHEMA=DATABASE()
        ", [$table]);

        $existingFK = collect($prodFK)->pluck('CONSTRAINT_NAME')->toArray();

        foreach ($devFK as $fk) {
            if (in_array($fk->CONSTRAINT_NAME, $existingFK)) {
                $this->line("FK {$fk->CONSTRAINT_NAME} exists, skipping.");
                continue;
            }

            $sql = "ALTER TABLE `$table`
                    ADD CONSTRAINT `{$fk->CONSTRAINT_NAME}`
                    FOREIGN KEY (`{$fk->COLUMN_NAME}`)
                    REFERENCES `{$fk->REFERENCED_TABLE_NAME}`(`{$fk->REFERENCED_COLUMN_NAME}`)";
            $this->line("Adding FK {$fk->CONSTRAINT_NAME}");
            $this->runSql($prod, $sql, $dryRun);
        }
    }

    private function syncData($dev, $prod, $table, $dryRun)
    {
        $pk = $this->getPrimaryKey($dev, $table);
        if (!$pk) {
            $this->warn("No primary key for $table, skipping data insert");
            return;
        }

        $rows = $dev->table($table)->get();
        if ($dryRun)
            return;

        if ($rows->isEmpty())
            return;

        // Disable FK checks temporarily
        $prod->statement('SET FOREIGN_KEY_CHECKS=0;');

        foreach ($rows as $row) {
            $exists = $prod->table($table)->where($pk, $row->$pk)->exists();
            if (!$exists) {
                $prod->table($table)->insert((array) $row);
                $this->line("Inserted row {$row->$pk} into $table");
            } else {
                $this->line("Row {$row->$pk} already exists in $table, skipping");
            }
        }

        // Re-enable FK checks
        $prod->statement('SET FOREIGN_KEY_CHECKS=1;');
    }


    private function getPrimaryKey($conn, $table)
    {
        $keys = $conn->select("SHOW KEYS FROM `$table` WHERE Key_name = 'PRIMARY'");
        return $keys[0]->Column_name ?? null;
    }
}
