<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * Restrukturisasi tb_resep:
 *   SEBELUM : satu resep = banyak baris (1 baris per bahan), menyimpan id_bahan & jumlah_bahan
 *   SESUDAH : tb_resep hanya header (1 baris per resep), bahan pindah ke tb_detail_resep
 *
 * Skenario yang ditangani:
 *   A) Fresh DB      → tb_resep sudah struktur baru (dari CreateTbResepTable baru), tinggal buat tb_detail_resep
 *   B) DB lama       → tb_resep masih struktur lama (ada kolom id_bahan), migrasi data penuh
 *   C) Migrate gagal → sisa tabel/constraint dibersihkan dulu sebelum mulai
 */
class RestrukturisasiTbResepDanBuatTbDetailResep extends Migration
{
    public function up(): void
    {
        // ── Deteksi skenario ─────────────────────────────────────────
        $isLegacyDb = $this->db->tableExists('tb_resep')
            && $this->db->fieldExists('id_bahan', 'tb_resep')
            && $this->db->fieldExists('jumlah_bahan', 'tb_resep');

        $isFreshDb = $this->db->tableExists('tb_resep')
            && ! $this->db->fieldExists('id_bahan', 'tb_resep');

        // ── 0. Cleanup sisa migrate gagal ────────────────────────────
        $this->db->query('SET FOREIGN_KEY_CHECKS = 0');
        $this->db->query('DROP TABLE IF EXISTS `tb_detail_resep`');
        $this->db->query('DROP TABLE IF EXISTS `tb_resep_header`');
        $this->db->query('DROP TABLE IF EXISTS `tb_resep_lama`');
        $this->db->query('SET FOREIGN_KEY_CHECKS = 1');

        // ── SKENARIO A: Fresh DB ──────────────────────────────────────
        // tb_resep sudah benar strukturnya, tinggal buat tb_detail_resep
        if ($isFreshDb) {
            $this->buatTbDetailResep();
            $this->pasangFkProduksi();
            return;
        }

        // ── SKENARIO B: DB Lama ───────────────────────────────────────

        // ── 1. Buat tb_resep_header (struktur baru sementara) ────────
        $this->db->query('
            CREATE TABLE `tb_resep_header` (
                `id_resep`      INT(11) UNSIGNED    NOT NULL AUTO_INCREMENT,
                `nama_resep`    VARCHAR(255)        NOT NULL COLLATE utf8mb4_unicode_ci,
                `id_produk`     INT(11) UNSIGNED    NULL DEFAULT NULL,
                `catatan_resep` TEXT                NULL DEFAULT NULL,
                `created_at`    DATETIME            NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at`    DATETIME            NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id_resep`),
                KEY `idx_resep_hdr_produk` (`id_produk`),
                CONSTRAINT `fk_resep_hdr_produk`
                    FOREIGN KEY (`id_produk`) REFERENCES `tb_produk` (`id_produk`)
                    ON DELETE SET NULL ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ');

        // ── 2. Migrasi data header ────────────────────────────────────
        if ($isLegacyDb) {
            $this->db->query('
                INSERT INTO `tb_resep_header`
                    (`id_resep`, `nama_resep`, `id_produk`, `catatan_resep`, `created_at`, `updated_at`)
                SELECT
                    MIN(r.id_resep),
                    r.nama_resep COLLATE utf8mb4_unicode_ci,
                    r.id_produk,
                    MAX(r.catatan_resep),
                    MIN(r.created_at),
                    NOW()
                FROM `tb_resep` r
                GROUP BY r.nama_resep COLLATE utf8mb4_unicode_ci, r.id_produk
            ');
        }

        // ── 3. Buat tb_detail_resep (sementara FK ke tb_resep_header) ─
        $this->db->query('
            CREATE TABLE `tb_detail_resep` (
                `id_detail_resep` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                `id_resep`        INT(11) UNSIGNED NOT NULL,
                `id_bahan`        INT(11) UNSIGNED NOT NULL,
                `jumlah_bahan`    DECIMAL(10,3)    NOT NULL DEFAULT 0.000,
                `created_at`      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at`      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id_detail_resep`),
                KEY `idx_dr_resep` (`id_resep`),
                KEY `idx_dr_bahan` (`id_bahan`),
                CONSTRAINT `fk_dr_resep_header`
                    FOREIGN KEY (`id_resep`) REFERENCES `tb_resep_header` (`id_resep`)
                    ON DELETE CASCADE ON UPDATE CASCADE,
                CONSTRAINT `fk_dr_bahan_ref`
                    FOREIGN KEY (`id_bahan`) REFERENCES `tb_bahan` (`id_bahan`)
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ');

        // ── 4. Migrasi bahan ke tb_detail_resep ──────────────────────
        if ($isLegacyDb) {
            $this->db->query('
                INSERT INTO `tb_detail_resep`
                    (`id_resep`, `id_bahan`, `jumlah_bahan`, `created_at`, `updated_at`)
                SELECT
                    h.id_resep,
                    r.id_bahan,
                    r.jumlah_bahan,
                    r.created_at,
                    NOW()
                FROM `tb_resep` r
                JOIN `tb_resep_header` h
                    ON  h.nama_resep COLLATE utf8mb4_unicode_ci = r.nama_resep COLLATE utf8mb4_unicode_ci
                    AND h.id_produk = r.id_produk
            ');
        }

        // ── 5. Update tb_produksi: arahkan id_resep ke header baru ───
        if ($isLegacyDb && $this->db->tableExists('tb_produksi')) {
            $this->db->query('
                UPDATE `tb_produksi` p
                JOIN `tb_resep` r ON r.id_resep = p.id_resep
                JOIN `tb_resep_header` h
                    ON  h.nama_resep COLLATE utf8mb4_unicode_ci = r.nama_resep COLLATE utf8mb4_unicode_ci
                    AND h.id_produk = r.id_produk
                SET p.id_resep = h.id_resep
            ');
        }

        // ── 6. Lepas SEMUA FK di tb_produksi.id_resep ────────────────
        if ($this->db->tableExists('tb_produksi')) {
            $fkList = $this->db->query("
                SELECT CONSTRAINT_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME   = 'tb_produksi'
                  AND COLUMN_NAME  = 'id_resep'
                  AND REFERENCED_TABLE_NAME IS NOT NULL
            ")->getResultArray();

            foreach ($fkList as $fk) {
                $this->db->query("ALTER TABLE `tb_produksi` DROP FOREIGN KEY `{$fk['CONSTRAINT_NAME']}`");
            }
        }

        // ── 7. Rename tb_resep → tb_resep_lama, tb_resep_header → tb_resep
        if ($this->db->tableExists('tb_resep')) {
            $this->db->query('RENAME TABLE `tb_resep` TO `tb_resep_lama`');
        }
        $this->db->query('RENAME TABLE `tb_resep_header` TO `tb_resep`');

        // ── 8. Update FK tb_detail_resep → tunjuk ke tb_resep ────────
        $this->db->query('ALTER TABLE `tb_detail_resep` DROP FOREIGN KEY `fk_dr_resep_header`');
        $this->db->query('
            ALTER TABLE `tb_detail_resep`
            ADD CONSTRAINT `fk_dr_resep`
                FOREIGN KEY (`id_resep`) REFERENCES `tb_resep` (`id_resep`)
                ON DELETE CASCADE ON UPDATE CASCADE
        ');

        // ── 9. FK tb_produksi → tb_resep ─────────────────────────────
        $this->pasangFkProduksi();

        // ── 10. Drop tabel backup lama ────────────────────────────────
        $this->db->query('SET FOREIGN_KEY_CHECKS = 0');
        $this->db->query('DROP TABLE IF EXISTS `tb_resep_lama`');
        $this->db->query('SET FOREIGN_KEY_CHECKS = 1');
    }

    // ── Helper: buat tb_detail_resep (untuk fresh DB) ────────────────
    private function buatTbDetailResep(): void
    {
        if ($this->db->tableExists('tb_detail_resep')) {
            return;
        }

        $this->db->query('
            CREATE TABLE `tb_detail_resep` (
                `id_detail_resep` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                `id_resep`        INT(11) UNSIGNED NOT NULL,
                `id_bahan`        INT(11) UNSIGNED NOT NULL,
                `jumlah_bahan`    DECIMAL(10,3)    NOT NULL DEFAULT 0.000,
                `created_at`      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at`      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id_detail_resep`),
                KEY `idx_dr_resep` (`id_resep`),
                KEY `idx_dr_bahan` (`id_bahan`),
                CONSTRAINT `fk_dr_resep`
                    FOREIGN KEY (`id_resep`) REFERENCES `tb_resep` (`id_resep`)
                    ON DELETE CASCADE ON UPDATE CASCADE,
                CONSTRAINT `fk_dr_bahan_ref`
                    FOREIGN KEY (`id_bahan`) REFERENCES `tb_bahan` (`id_bahan`)
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ');
    }

    // ── Helper: pasang FK tb_produksi → tb_resep ─────────────────────
    private function pasangFkProduksi(): void
    {
        if (! $this->db->tableExists('tb_produksi')) {
            return;
        }

        if (! $this->db->fieldExists('id_resep', 'tb_produksi')) {
            return;
        }

        // Pastikan tipe kolom cocok
        $this->db->query('
            ALTER TABLE `tb_produksi`
            MODIFY COLUMN `id_resep` INT(11) UNSIGNED NULL DEFAULT NULL
        ');

        // Cek apakah FK sudah ada
        $fkExists = $this->db->query("
            SELECT COUNT(*) AS cnt
            FROM information_schema.TABLE_CONSTRAINTS
            WHERE TABLE_SCHEMA    = DATABASE()
              AND TABLE_NAME      = 'tb_produksi'
              AND CONSTRAINT_NAME = 'fk_produksi_resep'
              AND CONSTRAINT_TYPE = 'FOREIGN KEY'
        ")->getRow()->cnt;

        if (! $fkExists) {
            $this->db->query('
                ALTER TABLE `tb_produksi`
                ADD CONSTRAINT `fk_produksi_resep`
                    FOREIGN KEY (`id_resep`) REFERENCES `tb_resep` (`id_resep`)
                    ON DELETE SET NULL ON UPDATE CASCADE
            ');
        }
    }

    public function down(): void
    {
        // ── Lepas FK tb_produksi ──────────────────────────────────────
        if ($this->db->tableExists('tb_produksi')) {
            $fkList = $this->db->query("
                SELECT CONSTRAINT_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME   = 'tb_produksi'
                  AND COLUMN_NAME  = 'id_resep'
                  AND REFERENCED_TABLE_NAME IS NOT NULL
            ")->getResultArray();

            foreach ($fkList as $fk) {
                $this->db->query("ALTER TABLE `tb_produksi` DROP FOREIGN KEY `{$fk['CONSTRAINT_NAME']}`");
            }
        }

        $this->db->query('SET FOREIGN_KEY_CHECKS = 0');
        $this->db->query('DROP TABLE IF EXISTS `tb_detail_resep`');

        // Kembalikan tb_resep ke struktur lama (tanpa data)
        $this->db->query('DROP TABLE IF EXISTS `tb_resep`');
        $this->db->query('
            CREATE TABLE `tb_resep` (
                `id_resep`      INT(11) UNSIGNED    NOT NULL AUTO_INCREMENT,
                `nama_resep`    VARCHAR(255)        NOT NULL,
                `id_produk`     INT(11) UNSIGNED    NULL DEFAULT NULL,
                `id_bahan`      INT(11) UNSIGNED    NOT NULL,
                `jumlah_bahan`  DECIMAL(10,3)       NOT NULL DEFAULT 0.000,
                `catatan_resep` TEXT                NULL DEFAULT NULL,
                `created_at`    DATETIME            NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at`    DATETIME            NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id_resep`),
                KEY `idx_resep_produk` (`id_produk`),
                KEY `idx_resep_bahan` (`id_bahan`),
                CONSTRAINT `fk_resep_produk_restore`
                    FOREIGN KEY (`id_produk`) REFERENCES `tb_produk` (`id_produk`)
                    ON DELETE SET NULL ON UPDATE CASCADE,
                CONSTRAINT `fk_resep_bahan_restore`
                    FOREIGN KEY (`id_bahan`) REFERENCES `tb_bahan` (`id_bahan`)
                    ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ');
        $this->db->query('SET FOREIGN_KEY_CHECKS = 1');

        // Pasang kembali FK tb_produksi → tb_resep
        if ($this->db->tableExists('tb_produksi')) {
            $this->db->query('
                ALTER TABLE `tb_produksi`
                ADD CONSTRAINT `fk_produksi_resep`
                    FOREIGN KEY (`id_resep`) REFERENCES `tb_resep` (`id_resep`)
                    ON DELETE SET NULL ON UPDATE CASCADE
            ');
        }
    }
}