<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class FixPembelianPaymentUserForeignKey extends Migration
{
    public function up(): void
    {
        $db = db_connect();

        if (
            ! $db->tableExists('pembelian_pembayaran')
            || ! $db->tableExists('user')
            || ! $db->fieldExists('user_id', 'pembelian_pembayaran')
        ) {
            return;
        }

        $constraint = 'pembelian_pembayaran_user_id_foreign';
        $exists = $db->query(
            'SELECT COUNT(*) AS total
             FROM information_schema.KEY_COLUMN_USAGE
             WHERE CONSTRAINT_SCHEMA = DATABASE()
               AND TABLE_NAME = ?
               AND CONSTRAINT_NAME = ?',
            ['pembelian_pembayaran', $constraint]
        )->getRowArray();

        if ((int) ($exists['total'] ?? 0) > 0) {
            $db->query('ALTER TABLE `pembelian_pembayaran` DROP FOREIGN KEY `' . $constraint . '`');
        }

        $db->query(
            'ALTER TABLE `pembelian_pembayaran` ADD CONSTRAINT `' . $constraint . '`'
            . ' FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)'
            . ' ON DELETE SET NULL ON UPDATE CASCADE'
        );
    }

    public function down(): void
    {
        // Keep the safe SET NULL behavior in place.
    }
}
