<?php

namespace App\Console\Commands;

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

class MergeTablesCommand extends Command
{
    protected $signature = 'tables:merge {source} {target}';
    protected $description = 'Merge columns from one table to another dynamically';

    public function handle()
    {
        $source = $this->argument('source');
        $target = $this->argument('target');
        $database = env('DB_DATABASE');

        // Get columns in source not in target
        $columns = DB::select("
            SELECT COLUMN_NAME, COLUMN_TYPE
            FROM information_schema.columns
            WHERE table_schema = ?
            AND table_name = ?
            AND COLUMN_NAME NOT IN (
                SELECT COLUMN_NAME
                FROM information_schema.columns
                WHERE table_schema = ?
                AND table_name = ?
            )
        ", [$database, $source, $database, $target]);

        if (empty($columns)) {
            $this->info("No new columns to merge from $source to $target.");
            return;
        }

        // Add columns to target table
        foreach ($columns as $column) {
            $sql = "ALTER TABLE `$target` ADD COLUMN `{$column->COLUMN_NAME}` {$column->COLUMN_TYPE}";
            DB::statement($sql);
            $this->info("Added column {$column->COLUMN_NAME} to $target");
        }

        $this->info("Merge complete.");
    }
}
