<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        if (!Schema::hasTable('journal_entry_lines') || !Schema::hasColumn('journal_entry_lines', 'account_id')) {
            return;
        }

        // Drop any existing foreign keys on account_id safely
        try {
            $constraints = DB::select(<<<SQL
                SELECT CONSTRAINT_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'journal_entry_lines'
                  AND COLUMN_NAME = 'account_id'
                  AND REFERENCED_TABLE_NAME IS NOT NULL
            SQL);
            foreach ($constraints as $row) {
                $name = $row->CONSTRAINT_NAME ?? $row->constraint_name ?? null;
                if ($name) {
                    DB::statement("ALTER TABLE `journal_entry_lines` DROP FOREIGN KEY `{$name}`");
                }
            }
        } catch (\Throwable $e) {
            // ignore drops if none exist
        }

        // Add FK to accounts when table exists and data aligns
        if (Schema::hasTable('accounts')) {
            try {
                $mismatchCount = DB::table('journal_entry_lines as jl')
                    ->leftJoin('accounts as a', 'a.id', '=', 'jl.account_id')
                    ->whereNull('a.id')
                    ->count();

                if ($mismatchCount === 0) {
                    Schema::table('journal_entry_lines', function (Blueprint $table) {
                        $table->foreign('account_id')
                            ->references('id')
                            ->on('accounts')
                            ->onDelete('cascade');
                    });
                }
            } catch (\Throwable $e) {
                // skip adding FK if an error occurs
            }
        }
    }

    public function down(): void
    {
        if (!Schema::hasTable('journal_entry_lines') || !Schema::hasColumn('journal_entry_lines', 'account_id')) {
            return;
        }

        // Drop FK to accounts if present
        try {
            $constraints = DB::select(<<<SQL
                SELECT CONSTRAINT_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'journal_entry_lines'
                  AND COLUMN_NAME = 'account_id'
                  AND REFERENCED_TABLE_NAME = 'accounts'
            SQL);
            foreach ($constraints as $row) {
                $name = $row->CONSTRAINT_NAME ?? $row->constraint_name ?? null;
                if ($name) {
                    DB::statement("ALTER TABLE `journal_entry_lines` DROP FOREIGN KEY `{$name}`");
                }
            }
        } catch (\Throwable $e) {
            // ignore
        }

        // Best-effort restore FK to company_accounts if table exists and data aligns
        if (Schema::hasTable('company_accounts')) {
            try {
                $mismatchCount = DB::table('journal_entry_lines as jl')
                    ->leftJoin('company_accounts as ca', 'ca.id', '=', 'jl.account_id')
                    ->whereNull('ca.id')
                    ->count();
                if ($mismatchCount === 0) {
                    Schema::table('journal_entry_lines', function (Blueprint $table) {
                        $table->foreign('account_id')
                            ->references('id')
                            ->on('company_accounts')
                            ->onDelete('cascade');
                    });
                }
            } catch (\Throwable $e) {
                // ignore
            }
        }
    }
};