<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;
use Config\Database;

class CleanPembelianDatabaseMetadata extends Migration
{
    public function up()
    {
        $db = Database::connect();

        if ($db->tableExists('pembelian')) {
            $db->query('UPDATE `pembelian` SET `tanggal_pembelian` = CURDATE() WHERE `tanggal_pembelian` IS NULL');
            $this->dropForeignKeyIfExists('pembelian', 'barang_masuk_suplier_id_foreign');
            $this->dropForeignKeyIfExists('pembelian', 'barang_masuk_user_id_foreign');

            $this->forge->modifyColumn('pembelian', [
                'tanggal_pembelian' => [
                    'name' => 'tanggal_pembelian',
                    'type' => 'DATE',
                    'null' => false,
                ],
            ]);

            $this->addForeignKeyIfMissing('pembelian', 'suplier_id', 'suplier', 'id', 'RESTRICT', 'CASCADE', 'pembelian_suplier_id_foreign');
            $this->addForeignKeyIfMissing('pembelian', 'user_id', 'user', 'id', 'RESTRICT', 'CASCADE', 'pembelian_user_id_foreign');
        }

        if ($db->tableExists('pembelian_detail')) {
            $this->dropForeignKeyIfExists('pembelian_detail', 'barang_masuk_detail_barang_id_foreign');
            $this->dropForeignKeyIfExists('pembelian_detail', 'pembelian_detail_pembelian_id_foreign');

            $this->forge->modifyColumn('pembelian_detail', [
                'pembelian_id' => [
                    'name'       => 'pembelian_id',
                    'type'       => 'INT',
                    'constraint' => 11,
                    'unsigned'   => true,
                    'null'       => false,
                ],
            ]);

            $this->addForeignKeyIfMissing('pembelian_detail', 'pembelian_id', 'pembelian', 'id', 'CASCADE', 'CASCADE', 'pembelian_detail_pembelian_id_foreign');
            $this->addForeignKeyIfMissing('pembelian_detail', 'barang_id', 'barang', 'id', 'RESTRICT', 'CASCADE', 'pembelian_detail_barang_id_foreign');
        }
    }

    public function down()
    {
        $db = Database::connect();

        if ($db->tableExists('pembelian_detail')) {
            $this->dropForeignKeyIfExists('pembelian_detail', 'pembelian_detail_barang_id_foreign');
            $this->dropForeignKeyIfExists('pembelian_detail', 'pembelian_detail_pembelian_id_foreign');

            $this->forge->modifyColumn('pembelian_detail', [
                'pembelian_id' => [
                    'name'       => 'pembelian_id',
                    'type'       => 'INT',
                    'constraint' => 11,
                    'unsigned'   => true,
                    'null'       => true,
                ],
            ]);

            $this->addForeignKeyIfMissing('pembelian_detail', 'pembelian_id', 'pembelian', 'id', 'CASCADE', 'CASCADE', 'pembelian_detail_pembelian_id_foreign');
            $this->addForeignKeyIfMissing('pembelian_detail', 'barang_id', 'barang', 'id', 'RESTRICT', 'CASCADE', 'barang_masuk_detail_barang_id_foreign');
        }

        if ($db->tableExists('pembelian')) {
            $this->dropForeignKeyIfExists('pembelian', 'pembelian_suplier_id_foreign');
            $this->dropForeignKeyIfExists('pembelian', 'pembelian_user_id_foreign');

            $this->forge->modifyColumn('pembelian', [
                'tanggal_pembelian' => [
                    'name' => 'tanggal_pembelian',
                    'type' => 'DATE',
                    'null' => true,
                ],
            ]);

            $this->addForeignKeyIfMissing('pembelian', 'suplier_id', 'suplier', 'id', 'RESTRICT', 'CASCADE', 'barang_masuk_suplier_id_foreign');
            $this->addForeignKeyIfMissing('pembelian', 'user_id', 'user', 'id', 'RESTRICT', 'CASCADE', 'barang_masuk_user_id_foreign');
        }
    }

    private function dropForeignKeyIfExists(string $table, string $constraint): void
    {
        $db = Database::connect();
        $exists = $db->query(
            'SELECT COUNT(*) AS total
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE TABLE_SCHEMA = ?
                AND TABLE_NAME = ?
                AND CONSTRAINT_NAME = ?
                AND CONSTRAINT_TYPE = "FOREIGN KEY"',
            [$db->getDatabase(), $table, $constraint]
        )->getRowArray();

        if ((int) ($exists['total'] ?? 0) < 1) {
            return;
        }

        $db->query('ALTER TABLE `' . $table . '` DROP FOREIGN KEY `' . $constraint . '`');
    }

    private function addForeignKeyIfMissing(string $table, string $column, string $referencedTable, string $referencedColumn, string $onDelete, string $onUpdate, string $constraint): void
    {
        $db = Database::connect();
        $exists = $db->query(
            'SELECT COUNT(*) AS total
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = ?
                AND TABLE_NAME = ?
                AND COLUMN_NAME = ?
                AND REFERENCED_TABLE_NAME = ?
                AND REFERENCED_COLUMN_NAME = ?',
            [$db->getDatabase(), $table, $column, $referencedTable, $referencedColumn]
        )->getRowArray();

        if ((int) ($exists['total'] ?? 0) > 0) {
            return;
        }

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