<?php

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

/**
 * Repoint `default_sender_phone_id` (on contact_templates) and `sender_phone_id`
 * (on contact_messages) away from the dialler's solicitor_phone_numbers table
 * onto the new solicitor_sms_senders whitelist.  Renames the columns in the
 * process to make the new ownership explicit.
 *
 * Safe to run when the contact hub is freshly migrated (no production rows
 * referencing solicitor_phone_numbers as SMS senders).  Drops the old columns
 * outright rather than dual-keeping them.
 *
 * Every drop is guarded so the migration is idempotent and order-safe: it can
 * re-run after a partial failure, tolerate a storage engine that silently
 * ignored the original foreign key, and no-op once the columns are already
 * repointed.  Without the guards an unconditional dropForeign throws 1091
 * ("Can't DROP ...; check that column/key exists") and halts the whole chain.
 */
return new class extends Migration
{
    public function up(): void
    {
        // contact_templates: default_sender_phone_id -> default_sms_sender_id
        $this->dropForeignKeysForColumn('contact_templates', 'default_sender_phone_id');
        if (Schema::hasColumn('contact_templates', 'default_sender_phone_id')) {
            Schema::table('contact_templates', function (Blueprint $t) {
                $t->dropColumn('default_sender_phone_id');
            });
        }
        if (! Schema::hasColumn('contact_templates', 'default_sms_sender_id')) {
            Schema::table('contact_templates', function (Blueprint $t) {
                $t->foreignId('default_sms_sender_id')
                    ->nullable()
                    ->after('default_sender_email_id')
                    ->constrained('solicitor_sms_senders')
                    ->nullOnDelete();
            });
        }

        // contact_messages: sender_phone_id -> sms_sender_id
        $this->dropForeignKeysForColumn('contact_messages', 'sender_phone_id');
        if (Schema::hasColumn('contact_messages', 'sender_phone_id')) {
            Schema::table('contact_messages', function (Blueprint $t) {
                $t->dropColumn('sender_phone_id');
            });
        }
        if (! Schema::hasColumn('contact_messages', 'sms_sender_id')) {
            Schema::table('contact_messages', function (Blueprint $t) {
                $t->foreignId('sms_sender_id')
                    ->nullable()
                    ->after('sender_email_id')
                    ->constrained('solicitor_sms_senders')
                    ->nullOnDelete();
            });
        }
    }

    public function down(): void
    {
        $this->dropForeignKeysForColumn('contact_messages', 'sms_sender_id');
        if (Schema::hasColumn('contact_messages', 'sms_sender_id')) {
            Schema::table('contact_messages', function (Blueprint $t) {
                $t->dropColumn('sms_sender_id');
            });
        }
        if (! Schema::hasColumn('contact_messages', 'sender_phone_id')) {
            Schema::table('contact_messages', function (Blueprint $t) {
                $t->foreignId('sender_phone_id')
                    ->nullable()
                    ->after('sender_email_id')
                    ->constrained('solicitor_phone_numbers')
                    ->nullOnDelete();
            });
        }

        $this->dropForeignKeysForColumn('contact_templates', 'default_sms_sender_id');
        if (Schema::hasColumn('contact_templates', 'default_sms_sender_id')) {
            Schema::table('contact_templates', function (Blueprint $t) {
                $t->dropColumn('default_sms_sender_id');
            });
        }
        if (! Schema::hasColumn('contact_templates', 'default_sender_phone_id')) {
            Schema::table('contact_templates', function (Blueprint $t) {
                $t->foreignId('default_sender_phone_id')
                    ->nullable()
                    ->after('default_sender_email_id')
                    ->constrained('solicitor_phone_numbers')
                    ->nullOnDelete();
            });
        }
    }

    /**
     * Drop every foreign key constraint defined on $table.$column, whatever its
     * name. No-op when the column is absent or carries no foreign key (a fresh
     * build where the FK was never created, a re-run after the column was already
     * repointed, or an engine that silently ignored the constraint). Resolving
     * the real constraint name also covers keys whose name does not follow
     * Laravel's "<table>_<column>_foreign" convention (e.g. carried over from an
     * earlier table rename).
     */
    private function dropForeignKeysForColumn(string $table, string $column): void
    {
        if (! Schema::hasColumn($table, $column)) {
            return;
        }

        $database = DB::connection()->getDatabaseName();

        $constraints = collect(DB::select(
            'SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
             WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
               AND REFERENCED_TABLE_NAME IS NOT NULL',
            [$database, $table, $column]
        ))->pluck('CONSTRAINT_NAME');

        foreach ($constraints as $name) {
            Schema::table($table, function (Blueprint $t) use ($name) {
                $t->dropForeign($name);
            });
        }
    }
};
