<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class HardenForeignKeyDeleteRules extends Migration
{
    /**
     * Master data must not silently remove transaction history.
     * Transaction child rows still cascade when the transaction itself is deleted.
     */
    private array $foreignKeys = [
        ['barang', 'barang_kategori_id_foreign', 'kategori_id', 'kategori', 'id', 'RESTRICT', 'CASCADE'],
        ['barang_keluar', 'barang_keluar_user_id_foreign', 'user_id', 'user', 'id', 'RESTRICT', 'CASCADE'],
        ['barang_keluar_detail', 'barang_keluar_detail_barang_id_foreign', 'barang_id', 'barang', 'id', 'RESTRICT', 'CASCADE'],
        ['penjualan', 'penjualan_pelanggan_id_foreign', 'pelanggan_id', 'pelanggan', 'id', 'RESTRICT', 'CASCADE'],
        ['penjualan', 'penjualan_user_id_foreign', 'user_id', 'user', 'id', 'RESTRICT', 'CASCADE'],
        ['penjualan_detail', 'penjualan_detail_barang_id_foreign', 'barang_id', 'barang', 'id', 'RESTRICT', 'CASCADE'],
        ['penjualan_pembayaran', 'penjualan_pembayaran_user_id_foreign', 'user_id', 'user', 'id', 'SET NULL', 'CASCADE'],
        ['service', 'service_pelanggan_id_foreign', 'pelanggan_id', 'pelanggan', 'id', 'RESTRICT', 'CASCADE'],
        ['service', 'service_teknisi_id_foreign', 'teknisi_id', 'teknisi', 'id', 'RESTRICT', 'CASCADE'],
        ['service', 'service_user_id_foreign', 'user_id', 'user', 'id', 'RESTRICT', 'CASCADE'],
        ['service_barang', 'service_barang_barang_id_foreign', 'barang_id', 'barang', 'id', 'RESTRICT', 'CASCADE'],
        ['service_jasa', 'service_jasa_jasa_id_foreign', 'jasa_id', 'jasa', 'id', 'SET NULL', 'CASCADE'],
        ['service_pembayaran', 'service_pembayaran_user_id_foreign', 'user_id', 'user', 'id', 'SET NULL', 'CASCADE'],
        ['stok_penyesuaian', 'stok_penyesuaian_barang_id_foreign', 'barang_id', 'barang', 'id', 'RESTRICT', 'CASCADE'],
        ['stok_penyesuaian', 'stok_penyesuaian_user_id_foreign', 'user_id', 'user', 'id', 'RESTRICT', 'CASCADE'],
        ['stok_penyesuaian', 'stok_penyesuaian_dibatalkan_oleh_foreign', 'dibatalkan_oleh', 'user', 'id', 'RESTRICT', 'CASCADE'],
    ];

    public function up(): void
    {
        foreach ($this->foreignKeys as $foreignKey) {
            $this->replaceForeignKey(...$foreignKey);
        }
    }

    public function down(): void
    {
        // Keep the hardened rules in place on rollback to avoid destructive cascade rules returning.
    }

    private function replaceForeignKey(
        string $table,
        string $constraint,
        string $column,
        string $referenceTable,
        string $referenceColumn,
        string $onDelete,
        string $onUpdate
    ): void {
        $db = db_connect();

        if (
            ! $db->tableExists($table)
            || ! $db->tableExists($referenceTable)
            || ! $db->fieldExists($column, $table)
            || ! $db->fieldExists($referenceColumn, $referenceTable)
        ) {
            return;
        }

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

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

        $db->query(
            'ALTER TABLE `' . $table . '` ADD CONSTRAINT `' . $constraint . '`'
            . ' FOREIGN KEY (`' . $column . '`)'
            . ' REFERENCES `' . $referenceTable . '` (`' . $referenceColumn . '`)'
            . ' ON DELETE ' . $onDelete
            . ' ON UPDATE ' . $onUpdate
        );
    }
}
