<?php

namespace App\Database\Migrations;

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

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

        if ($db->tableExists('barang_masuk') && ! $db->tableExists('pembelian')) {
            $this->forge->renameTable('barang_masuk', 'pembelian');
        }

        if ($db->tableExists('barang_masuk_detail') && ! $db->tableExists('pembelian_detail')) {
            $this->forge->renameTable('barang_masuk_detail', 'pembelian_detail');
        }

        if ($db->tableExists('pembelian') && $db->fieldExists('tanggal_masuk', 'pembelian') && ! $db->fieldExists('tanggal_pembelian', 'pembelian')) {
            $this->forge->modifyColumn('pembelian', [
                'tanggal_masuk' => [
                    'name' => 'tanggal_pembelian',
                    'type' => 'DATE',
                ],
            ]);
        }

        if ($db->tableExists('pembelian_detail') && $db->fieldExists('barang_masuk_id', 'pembelian_detail') && ! $db->fieldExists('pembelian_id', 'pembelian_detail')) {
            $this->dropForeignKeys('pembelian_detail', 'barang_masuk_id');

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

            $this->renameIndex('pembelian_detail', 'barang_masuk_id', 'pembelian_id');
            $this->addForeignKeyIfMissing('pembelian_detail', 'pembelian_id', 'pembelian', 'id', 'CASCADE', 'CASCADE');
        }
    }

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

        if ($db->tableExists('pembelian_detail') && $db->fieldExists('pembelian_id', 'pembelian_detail') && ! $db->fieldExists('barang_masuk_id', 'pembelian_detail')) {
            $this->dropForeignKeys('pembelian_detail', 'pembelian_id');

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

            $this->renameIndex('pembelian_detail', 'pembelian_id', 'barang_masuk_id');
            $this->addForeignKeyIfMissing('pembelian_detail', 'barang_masuk_id', 'pembelian', 'id', 'CASCADE', 'CASCADE');
        }

        if ($db->tableExists('pembelian') && $db->fieldExists('tanggal_pembelian', 'pembelian') && ! $db->fieldExists('tanggal_masuk', 'pembelian')) {
            $this->forge->modifyColumn('pembelian', [
                'tanggal_pembelian' => [
                    'name' => 'tanggal_masuk',
                    'type' => 'DATE',
                ],
            ]);
        }

        if ($db->tableExists('pembelian_detail') && ! $db->tableExists('barang_masuk_detail')) {
            $this->forge->renameTable('pembelian_detail', 'barang_masuk_detail');
        }

        if ($db->tableExists('pembelian') && ! $db->tableExists('barang_masuk')) {
            $this->forge->renameTable('pembelian', 'barang_masuk');
        }
    }

    private function dropForeignKeys(string $table, string $column): void
    {
        $db = Database::connect();
        $rows = $db->query(
            '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',
            [$db->getDatabase(), $table, $column]
        )->getResultArray();

        foreach ($rows as $row) {
            $db->query('ALTER TABLE `' . $table . '` DROP FOREIGN KEY `' . $row['CONSTRAINT_NAME'] . '`');
        }
    }

    private function renameIndex(string $table, string $from, string $to): void
    {
        $db = Database::connect();
        $indexes = $db->query('SHOW INDEX FROM `' . $table . '`')->getResultArray();
        $hasFrom = false;
        $hasTo = false;

        foreach ($indexes as $index) {
            if (($index['Key_name'] ?? '') === $from) {
                $hasFrom = true;
            }

            if (($index['Key_name'] ?? '') === $to) {
                $hasTo = true;
            }
        }

        if ($hasFrom && ! $hasTo) {
            $db->query('ALTER TABLE `' . $table . '` RENAME INDEX `' . $from . '` TO `' . $to . '`');
        }
    }

    private function addForeignKeyIfMissing(string $table, string $column, string $referencedTable, string $referencedColumn, string $onDelete, string $onUpdate): 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;
        }

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