<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * Tambahan tabel baru untuk modul SIM:
 *  - Alter tb_reseller  → tambah tanggal_bergabung, tahu_dari
 *  - tb_harga_reseller  → harga per produk per reseller
 *  - tb_harga_maklon    → harga per produk per mitra maklon
 *  - tb_prospek         → data calon pelanggan dari free sample
 *  - tb_produksi        → header transaksi produksi
 *  - tb_detail_bahan_produksi → bahan yang dipakai (mengurangi stok bahan)
 *  - tb_hasil_produksi  → produk yang dihasilkan (menambah stok produk)
 *  - tb_penjualan       → header penjualan ecer / reseller / maklon
 *  - tb_detail_penjualan → detail item penjualan (mengurangi stok produk)
 *  - tb_penjualan_marketplace → header import Excel marketplace non-TikTok
 *  - tb_detail_penjualan_marketplace → detail item marketplace
 *  - tb_free_sample     → header transaksi sampel gratis
 *  - tb_detail_free_sample → detail produk free sample (mengurangi stok)
 *  + Trigger stok produk untuk produksi, penjualan, dan free sample
 */
class CreateKaryawanTable extends Migration
{
    public function up(): void
    {
        // ====================================================================
        // 1. ALTER TB_RESELLER — tambah field tanggal_bergabung & tahu_dari
        // ====================================================================
        if ($this->db->tableExists('tb_reseller')) {
            if (! $this->db->fieldExists('tanggal_bergabung_reseller', 'tb_reseller')) {
                $this->db->query("
                    ALTER TABLE `tb_reseller`
                    ADD COLUMN `tanggal_bergabung_reseller` DATE NULL DEFAULT NULL
                    AFTER `alamat_reseller`
                ");
            }
            if (! $this->db->fieldExists('tahu_dari_reseller', 'tb_reseller')) {
                $this->db->query("
                    ALTER TABLE `tb_reseller`
                    ADD COLUMN `tahu_dari_reseller` VARCHAR(150) NULL DEFAULT NULL
                    AFTER `tanggal_bergabung_reseller`
                ");
            }
            // Hapus harga_default_reseller — harga per produk dipindah ke tb_harga_reseller
            if ($this->db->fieldExists('harga_default_reseller', 'tb_reseller')) {
                $this->db->query("
                    ALTER TABLE `tb_reseller`
                    DROP COLUMN `harga_default_reseller`
                ");
            }
        }

        // ====================================================================
        // 2. TB_HARGA_RESELLER — harga tiap produk per reseller
        // ====================================================================
        $this->forge->addField([
            'id_harga_reseller' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'id_reseller' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'id_produk' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'harga_reseller' => [
                'type'       => 'DECIMAL',
                'constraint' => '15,2',
                'null'       => false,
                'default'    => '0.00',
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
            'updated_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_harga_reseller', true);
        $this->forge->addUniqueKey(['id_reseller', 'id_produk'], 'uq_harga_reseller');
        $this->forge->addForeignKey('id_reseller', 'tb_reseller', 'id_reseller', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('id_produk',   'tb_produk',   'id_produk',   'CASCADE', 'CASCADE');
        $this->forge->createTable('tb_harga_reseller', true);

        $this->db->query("
            ALTER TABLE `tb_harga_reseller`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            MODIFY `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ");

        // ====================================================================
        // 3. TB_HARGA_MAKLON — harga tiap produk per mitra maklon
        // ====================================================================
        $this->forge->addField([
            'id_harga_maklon' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'id_maklon' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'id_produk' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'harga_maklon' => [
                'type'       => 'DECIMAL',
                'constraint' => '15,2',
                'null'       => false,
                'default'    => '0.00',
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
            'updated_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_harga_maklon', true);
        $this->forge->addUniqueKey(['id_maklon', 'id_produk'], 'uq_harga_maklon');
        $this->forge->addForeignKey('id_maklon', 'tb_maklon',  'id_maklon', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('id_produk', 'tb_produk',  'id_produk', 'CASCADE', 'CASCADE');
        $this->forge->createTable('tb_harga_maklon', true);

        $this->db->query("
            ALTER TABLE `tb_harga_maklon`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            MODIFY `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ");

        // ====================================================================
        // 4. TB_PROSPEK — calon pelanggan (dari free sample, dll)
        // ====================================================================
        $this->forge->addField([
            'id_prospek' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'nama_prospek' => [
                'type'       => 'VARCHAR',
                'constraint' => 150,
                'null'       => false,
            ],
            'no_hp_prospek' => [
                'type'       => 'VARCHAR',
                'constraint' => 50,
                'null'       => true,
                'default'    => null,
            ],
            'alamat_prospek' => [
                'type' => 'TEXT',
                'null' => true,
            ],
            'sumber_prospek' => [
                'type'       => 'VARCHAR',
                'constraint' => 150,
                'null'       => true,
                'default'    => null,
                'comment'    => 'Contoh: Free Sample, Pameran, Referral',
            ],
            'catatan_prospek' => [
                'type' => 'TEXT',
                'null' => true,
            ],
            'status_prospek' => [
                'type'       => 'ENUM',
                'constraint' => ['Aktif', 'Non-Aktif'],
                'null'       => false,
                'default'    => 'Aktif',
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
            'updated_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_prospek', true);
        $this->forge->createTable('tb_prospek', true);

        $this->db->query("
            ALTER TABLE `tb_prospek`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            MODIFY `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ");

        // ====================================================================
        // 5. TB_PRODUKSI — header transaksi produksi
        // ====================================================================
        $this->forge->addField([
            'id_produksi' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'nama_produksi' => [
                'type'       => 'VARCHAR',
                'constraint' => 200,
                'null'       => false,
                'comment'    => 'Contoh: Produksi Rendang 10kg - 5 Mei 2026',
            ],
            'tanggal_produksi' => [
                'type' => 'DATE',
                'null' => false,
            ],
            'id_resep' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
                'default'    => null,
                'comment'    => 'Resep yang digunakan sebagai acuan (opsional)',
            ],
            'catatan_produksi' => [
                'type' => 'TEXT',
                'null' => true,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
            'updated_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_produksi', true);
        $this->forge->addKey('tanggal_produksi');
        $this->forge->addForeignKey('id_resep', 'tb_resep', 'id_resep', 'SET NULL', 'CASCADE');
        $this->forge->createTable('tb_produksi', true);

        $this->db->query("
            ALTER TABLE `tb_produksi`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            MODIFY `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ");

        // ====================================================================
        // 6. TB_DETAIL_BAHAN_PRODUKSI — bahan yang dipakai (kurangi stok bahan)
        // ====================================================================
        $this->forge->addField([
            'id_detail_bahan_produksi' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'id_produksi' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'id_bahan' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'jumlah_bahan_produksi' => [
                'type'       => 'DECIMAL',
                'constraint' => '12,3',
                'null'       => false,
                'comment'    => 'Jumlah bahan yang digunakan dalam produksi ini',
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_detail_bahan_produksi', true);
        $this->forge->addForeignKey('id_produksi', 'tb_produksi', 'id_produksi', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('id_bahan',    'tb_bahan',    'id_bahan',    'CASCADE', 'RESTRICT');
        $this->forge->createTable('tb_detail_bahan_produksi', true);

        $this->db->query("
            ALTER TABLE `tb_detail_bahan_produksi`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
        ");

        // Trigger: INSERT detail bahan produksi → kurangi stok_bahan
        $this->db->query("DROP TRIGGER IF EXISTS `trg_produksi_kurang_stok_bahan`");
        $this->db->query("
            CREATE TRIGGER `trg_produksi_kurang_stok_bahan`
            AFTER INSERT ON `tb_detail_bahan_produksi`
            FOR EACH ROW
            BEGIN
                UPDATE `tb_bahan`
                SET
                    `stok_bahan` = GREATEST(`stok_bahan` - NEW.`jumlah_bahan_produksi`, 0),
                    `updated_at` = NOW()
                WHERE `id_bahan` = NEW.`id_bahan`;
            END
        ");

        // Trigger: DELETE detail bahan produksi → kembalikan stok_bahan
        $this->db->query("DROP TRIGGER IF EXISTS `trg_produksi_kembalikan_stok_bahan`");
        $this->db->query("
            CREATE TRIGGER `trg_produksi_kembalikan_stok_bahan`
            AFTER DELETE ON `tb_detail_bahan_produksi`
            FOR EACH ROW
            BEGIN
                UPDATE `tb_bahan`
                SET
                    `stok_bahan` = `stok_bahan` + OLD.`jumlah_bahan_produksi`,
                    `updated_at` = NOW()
                WHERE `id_bahan` = OLD.`id_bahan`;
            END
        ");

        // ====================================================================
        // 7. TB_HASIL_PRODUKSI — produk yang dihasilkan (tambah stok produk)
        // ====================================================================
        $this->forge->addField([
            'id_hasil_produksi' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'id_produksi' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'id_produk' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'jumlah_hasil_produksi' => [
                'type'       => 'INT',
                'constraint' => 11,
                'null'       => false,
                'comment'    => 'Jumlah produk yang dihasilkan (dalam satuan produk)',
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_hasil_produksi', true);
        $this->forge->addForeignKey('id_produksi', 'tb_produksi', 'id_produksi', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('id_produk',   'tb_produk',   'id_produk',   'CASCADE', 'RESTRICT');
        $this->forge->createTable('tb_hasil_produksi', true);

        $this->db->query("
            ALTER TABLE `tb_hasil_produksi`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
        ");

        // Trigger: INSERT hasil produksi → tambah stok_produk + catat log
        $this->db->query("DROP TRIGGER IF EXISTS `trg_hasil_produksi_tambah_stok`");
        $this->db->query("
            CREATE TRIGGER `trg_hasil_produksi_tambah_stok`
            AFTER INSERT ON `tb_hasil_produksi`
            FOR EACH ROW
            BEGIN
                UPDATE `tb_produk`
                SET
                    `stok_produk` = `stok_produk` + NEW.`jumlah_hasil_produksi`,
                    `updated_at`  = NOW()
                WHERE `id_produk` = NEW.`id_produk`;

                INSERT INTO `tb_stok_produk_log`
                    (`id_produk`, `tanggal_stok_produk_log`, `tipe_stok_produk_log`,
                     `jumlah_stok_produk_log`, `keterangan_stok_produk_log`, `created_at`)
                VALUES
                    (NEW.`id_produk`, CURDATE(), 'masuk',
                     NEW.`jumlah_hasil_produksi`, CONCAT('Produksi ID: ', NEW.`id_produksi`), NOW());
            END
        ");

        // Trigger: DELETE hasil produksi → kurangi stok_produk kembali
        $this->db->query("DROP TRIGGER IF EXISTS `trg_hasil_produksi_kurang_stok`");
        $this->db->query("
            CREATE TRIGGER `trg_hasil_produksi_kurang_stok`
            AFTER DELETE ON `tb_hasil_produksi`
            FOR EACH ROW
            BEGIN
                UPDATE `tb_produk`
                SET
                    `stok_produk` = GREATEST(`stok_produk` - OLD.`jumlah_hasil_produksi`, 0),
                    `updated_at`  = NOW()
                WHERE `id_produk` = OLD.`id_produk`;
            END
        ");

        // ====================================================================
        // 8. TB_PENJUALAN — header transaksi penjualan (ecer/reseller/maklon)
        // ====================================================================
        $this->forge->addField([
            'id_penjualan' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'kode_penjualan' => [
                'type'       => 'VARCHAR',
                'constraint' => 50,
                'null'       => false,
                'comment'    => 'Format: PJL-YYYYMMDD-XXXX',
            ],
            'tanggal_penjualan' => [
                'type' => 'DATE',
                'null' => false,
            ],
            'jenis_penjualan' => [
                'type'       => 'ENUM',
                'constraint' => ['Ecer', 'Reseller', 'Maklon'],
                'null'       => false,
                'default'    => 'Ecer',
            ],
            'id_reseller' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
                'default'    => null,
                'comment'    => 'Diisi jika jenis = Reseller',
            ],
            'id_maklon' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
                'default'    => null,
                'comment'    => 'Diisi jika jenis = Maklon',
            ],
            'total_penjualan' => [
                'type'       => 'DECIMAL',
                'constraint' => '15,2',
                'null'       => false,
                'default'    => '0.00',
            ],
            'catatan_penjualan' => [
                'type' => 'TEXT',
                'null' => true,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
            'updated_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_penjualan', true);
        $this->forge->addUniqueKey('kode_penjualan');
        $this->forge->addKey('tanggal_penjualan');
        $this->forge->addKey('jenis_penjualan');
        $this->forge->addForeignKey('id_reseller', 'tb_reseller', 'id_reseller', 'SET NULL', 'CASCADE');
        $this->forge->addForeignKey('id_maklon',   'tb_maklon',   'id_maklon',   'SET NULL', 'CASCADE');
        $this->forge->createTable('tb_penjualan', true);

        $this->db->query("
            ALTER TABLE `tb_penjualan`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            MODIFY `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ");

        // ====================================================================
        // 9. TB_DETAIL_PENJUALAN — item per transaksi (kurangi stok produk)
        // ====================================================================
        $this->forge->addField([
            'id_detail_penjualan' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'id_penjualan' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'id_produk' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'jumlah_detail_penjualan' => [
                'type'       => 'INT',
                'constraint' => 11,
                'null'       => false,
                'default'    => 1,
            ],
            'harga_satuan_penjualan' => [
                'type'       => 'DECIMAL',
                'constraint' => '15,2',
                'null'       => false,
                'default'    => '0.00',
            ],
            'subtotal_penjualan' => [
                'type'       => 'DECIMAL',
                'constraint' => '15,2',
                'null'       => false,
                'default'    => '0.00',
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_detail_penjualan', true);
        $this->forge->addForeignKey('id_penjualan', 'tb_penjualan', 'id_penjualan', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('id_produk',    'tb_produk',    'id_produk',    'CASCADE', 'RESTRICT');
        $this->forge->createTable('tb_detail_penjualan', true);

        $this->db->query("
            ALTER TABLE `tb_detail_penjualan`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
        ");

        // Trigger: INSERT detail penjualan → kurangi stok produk + log + kas masuk
        $this->db->query("DROP TRIGGER IF EXISTS `trg_detail_penjualan_kurang_stok`");
        $this->db->query("
            CREATE TRIGGER `trg_detail_penjualan_kurang_stok`
            AFTER INSERT ON `tb_detail_penjualan`
            FOR EACH ROW
            BEGIN
                UPDATE `tb_produk`
                SET
                    `stok_produk` = GREATEST(`stok_produk` - NEW.`jumlah_detail_penjualan`, 0),
                    `updated_at`  = NOW()
                WHERE `id_produk` = NEW.`id_produk`;

                INSERT INTO `tb_stok_produk_log`
                    (`id_produk`, `tanggal_stok_produk_log`, `tipe_stok_produk_log`,
                     `jumlah_stok_produk_log`, `keterangan_stok_produk_log`, `created_at`)
                VALUES
                    (NEW.`id_produk`, CURDATE(), 'keluar',
                     NEW.`jumlah_detail_penjualan`, CONCAT('Penjualan ID: ', NEW.`id_penjualan`), NOW());
            END
        ");

        // Trigger: DELETE detail penjualan → kembalikan stok produk
        $this->db->query("DROP TRIGGER IF EXISTS `trg_detail_penjualan_kembalikan_stok`");
        $this->db->query("
            CREATE TRIGGER `trg_detail_penjualan_kembalikan_stok`
            AFTER DELETE ON `tb_detail_penjualan`
            FOR EACH ROW
            BEGIN
                UPDATE `tb_produk`
                SET
                    `stok_produk` = `stok_produk` + OLD.`jumlah_detail_penjualan`,
                    `updated_at`  = NOW()
                WHERE `id_produk` = OLD.`id_produk`;
            END
        ");

        // Trigger: INSERT tb_penjualan → otomatis catat kas masuk
        $this->db->query("DROP TRIGGER IF EXISTS `trg_penjualan_kas_masuk`");
        $this->db->query("
            CREATE TRIGGER `trg_penjualan_kas_masuk`
            AFTER INSERT ON `tb_penjualan`
            FOR EACH ROW
            BEGIN
                IF NEW.`total_penjualan` > 0 THEN
                    INSERT INTO `tb_transaksi_kas`
                        (`tanggal_transaksi_kas`, `tipe_transaksi_kas`, `kategori_transaksi_kas`,
                         `keterangan_transaksi_kas`, `nominal_transaksi_kas`)
                    VALUES
                        (NEW.`tanggal_penjualan`, 'Pemasukan', 'Penjualan',
                         CONCAT('Penjualan ', NEW.`jenis_penjualan`, ' - Kode: ', NEW.`kode_penjualan`),
                         NEW.`total_penjualan`);
                END IF;
            END
        ");

        // ====================================================================
        // 10. TB_PENJUALAN_MARKETPLACE — header import Excel marketplace
        // ====================================================================
        $this->forge->addField([
            'id_penjualan_marketplace' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'id_marketplace' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'tanggal_import' => [
                'type' => 'DATE',
                'null' => false,
            ],
            'nama_file_import' => [
                'type'       => 'VARCHAR',
                'constraint' => 255,
                'null'       => true,
                'default'    => null,
                'comment'    => 'Nama file Excel yang diimport',
            ],
            'periode_awal' => [
                'type'    => 'DATE',
                'null'    => true,
                'default' => null,
                'comment' => 'Periode awal data dalam file Excel',
            ],
            'periode_akhir' => [
                'type'    => 'DATE',
                'null'    => true,
                'default' => null,
                'comment' => 'Periode akhir data dalam file Excel',
            ],
            'total_penjualan_marketplace' => [
                'type'       => 'DECIMAL',
                'constraint' => '15,2',
                'null'       => false,
                'default'    => '0.00',
            ],
            'catatan_marketplace' => [
                'type' => 'TEXT',
                'null' => true,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
            'updated_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_penjualan_marketplace', true);
        $this->forge->addKey('tanggal_import');
        $this->forge->addForeignKey('id_marketplace', 'tb_marketplace', 'id_marketplace', 'CASCADE', 'RESTRICT');
        $this->forge->createTable('tb_penjualan_marketplace', true);

        $this->db->query("
            ALTER TABLE `tb_penjualan_marketplace`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            MODIFY `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ");

        // ====================================================================
        // 11. TB_DETAIL_PENJUALAN_MARKETPLACE — item marketplace (kurangi stok)
        // ====================================================================
        $this->forge->addField([
            'id_detail_penjualan_marketplace' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'id_penjualan_marketplace' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'id_produk' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
                'default'    => null,
                'comment'    => 'Nullable: produk mungkin belum terpetakan saat import',
            ],
            'nama_produk_raw_marketplace' => [
                'type'       => 'VARCHAR',
                'constraint' => 255,
                'null'       => true,
                'comment'    => 'Nama produk asli dari file Excel',
            ],
            'jumlah_detail_marketplace' => [
                'type'       => 'INT',
                'constraint' => 11,
                'null'       => false,
                'default'    => 0,
            ],
            'harga_satuan_marketplace' => [
                'type'       => 'DECIMAL',
                'constraint' => '15,2',
                'null'       => false,
                'default'    => '0.00',
            ],
            'subtotal_marketplace' => [
                'type'       => 'DECIMAL',
                'constraint' => '15,2',
                'null'       => false,
                'default'    => '0.00',
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_detail_penjualan_marketplace', true);
        $this->forge->addForeignKey('id_penjualan_marketplace', 'tb_penjualan_marketplace', 'id_penjualan_marketplace', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('id_produk', 'tb_produk', 'id_produk', 'SET NULL', 'CASCADE');
        $this->forge->createTable('tb_detail_penjualan_marketplace', true);

        $this->db->query("
            ALTER TABLE `tb_detail_penjualan_marketplace`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
        ");

        // Trigger: INSERT detail marketplace → kurangi stok produk (jika id_produk ada)
        $this->db->query("DROP TRIGGER IF EXISTS `trg_marketplace_kurang_stok`");
        $this->db->query("
            CREATE TRIGGER `trg_marketplace_kurang_stok`
            AFTER INSERT ON `tb_detail_penjualan_marketplace`
            FOR EACH ROW
            BEGIN
                IF NEW.`id_produk` IS NOT NULL AND NEW.`jumlah_detail_marketplace` > 0 THEN
                    UPDATE `tb_produk`
                    SET
                        `stok_produk` = GREATEST(`stok_produk` - NEW.`jumlah_detail_marketplace`, 0),
                        `updated_at`  = NOW()
                    WHERE `id_produk` = NEW.`id_produk`;

                    INSERT INTO `tb_stok_produk_log`
                        (`id_produk`, `tanggal_stok_produk_log`, `tipe_stok_produk_log`,
                         `jumlah_stok_produk_log`, `keterangan_stok_produk_log`, `created_at`)
                    VALUES
                        (NEW.`id_produk`, CURDATE(), 'keluar',
                         NEW.`jumlah_detail_marketplace`,
                         CONCAT('Marketplace Import ID: ', NEW.`id_penjualan_marketplace`), NOW());
                END IF;
            END
        ");

        // Trigger: INSERT tb_penjualan_marketplace → otomatis catat kas masuk
        $this->db->query("DROP TRIGGER IF EXISTS `trg_penjualan_marketplace_kas_masuk`");
        $this->db->query("
            CREATE TRIGGER `trg_penjualan_marketplace_kas_masuk`
            AFTER INSERT ON `tb_penjualan_marketplace`
            FOR EACH ROW
            BEGIN
                IF NEW.`total_penjualan_marketplace` > 0 THEN
                    INSERT INTO `tb_transaksi_kas`
                        (`tanggal_transaksi_kas`, `tipe_transaksi_kas`, `kategori_transaksi_kas`,
                         `keterangan_transaksi_kas`, `nominal_transaksi_kas`)
                    VALUES
                        (NEW.`tanggal_import`, 'Pemasukan', 'Penjualan',
                         CONCAT('Import Marketplace ID: ', NEW.`id_penjualan_marketplace`,
                                ' - File: ', IFNULL(NEW.`nama_file_import`, '-')),
                         NEW.`total_penjualan_marketplace`);
                END IF;
            END
        ");

        // ====================================================================
        // 12. TB_FREE_SAMPLE — header transaksi sampel gratis
        // ====================================================================
        $this->forge->addField([
            'id_free_sample' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'tanggal_free_sample' => [
                'type' => 'DATE',
                'null' => false,
            ],
            'id_prospek' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
                'default'    => null,
                'comment'    => 'Prospek yang menerima sample (opsional, bisa diisi setelah)',
            ],
            'catatan_free_sample' => [
                'type' => 'TEXT',
                'null' => true,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
            'updated_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_free_sample', true);
        $this->forge->addKey('tanggal_free_sample');
        $this->forge->addForeignKey('id_prospek', 'tb_prospek', 'id_prospek', 'SET NULL', 'CASCADE');
        $this->forge->createTable('tb_free_sample', true);

        $this->db->query("
            ALTER TABLE `tb_free_sample`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            MODIFY `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ");

        // ====================================================================
        // 13. TB_DETAIL_FREE_SAMPLE — produk yang diberikan (kurangi stok)
        // ====================================================================
        $this->forge->addField([
            'id_detail_free_sample' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'id_free_sample' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'id_produk' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => false,
            ],
            'jumlah_detail_free_sample' => [
                'type'       => 'INT',
                'constraint' => 11,
                'null'       => false,
                'default'    => 1,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true, 'default' => null],
        ]);
        $this->forge->addKey('id_detail_free_sample', true);
        $this->forge->addForeignKey('id_free_sample', 'tb_free_sample', 'id_free_sample', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('id_produk',      'tb_produk',      'id_produk',      'CASCADE', 'RESTRICT');
        $this->forge->createTable('tb_detail_free_sample', true);

        $this->db->query("
            ALTER TABLE `tb_detail_free_sample`
            MODIFY `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
        ");

        // Trigger: INSERT detail free sample → kurangi stok produk + log
        $this->db->query("DROP TRIGGER IF EXISTS `trg_free_sample_kurang_stok`");
        $this->db->query("
            CREATE TRIGGER `trg_free_sample_kurang_stok`
            AFTER INSERT ON `tb_detail_free_sample`
            FOR EACH ROW
            BEGIN
                UPDATE `tb_produk`
                SET
                    `stok_produk` = GREATEST(`stok_produk` - NEW.`jumlah_detail_free_sample`, 0),
                    `updated_at`  = NOW()
                WHERE `id_produk` = NEW.`id_produk`;

                INSERT INTO `tb_stok_produk_log`
                    (`id_produk`, `tanggal_stok_produk_log`, `tipe_stok_produk_log`,
                     `jumlah_stok_produk_log`, `keterangan_stok_produk_log`, `created_at`)
                VALUES
                    (NEW.`id_produk`, CURDATE(), 'keluar',
                     NEW.`jumlah_detail_free_sample`,
                     CONCAT('Free Sample ID: ', NEW.`id_free_sample`), NOW());
            END
        ");

        // Trigger: DELETE detail free sample → kembalikan stok produk
        $this->db->query("DROP TRIGGER IF EXISTS `trg_free_sample_kembalikan_stok`");
        $this->db->query("
            CREATE TRIGGER `trg_free_sample_kembalikan_stok`
            AFTER DELETE ON `tb_detail_free_sample`
            FOR EACH ROW
            BEGIN
                UPDATE `tb_produk`
                SET
                    `stok_produk` = `stok_produk` + OLD.`jumlah_detail_free_sample`,
                    `updated_at`  = NOW()
                WHERE `id_produk` = OLD.`id_produk`;
            END
        ");
    }

    public function down(): void
    {
        // Drop trigger dulu
        $triggers = [
            'trg_free_sample_kembalikan_stok',
            'trg_free_sample_kurang_stok',
            'trg_marketplace_kurang_stok',
            'trg_penjualan_marketplace_kas_masuk',
            'trg_detail_penjualan_kembalikan_stok',
            'trg_detail_penjualan_kurang_stok',
            'trg_penjualan_kas_masuk',
            'trg_hasil_produksi_kurang_stok',
            'trg_hasil_produksi_tambah_stok',
            'trg_produksi_kembalikan_stok_bahan',
            'trg_produksi_kurang_stok_bahan',
        ];
        foreach ($triggers as $trigger) {
            $this->db->query("DROP TRIGGER IF EXISTS `{$trigger}`");
        }

        // Drop tabel (urutan terbalik agar FK tidak konflik)
        $this->forge->dropTable('tb_detail_free_sample',             true);
        $this->forge->dropTable('tb_free_sample',                    true);
        $this->forge->dropTable('tb_detail_penjualan_marketplace',   true);
        $this->forge->dropTable('tb_penjualan_marketplace',          true);
        $this->forge->dropTable('tb_detail_penjualan',               true);
        $this->forge->dropTable('tb_penjualan',                      true);
        $this->forge->dropTable('tb_hasil_produksi',                 true);
        $this->forge->dropTable('tb_detail_bahan_produksi',          true);
        $this->forge->dropTable('tb_produksi',                       true);
        $this->forge->dropTable('tb_prospek',                        true);
        $this->forge->dropTable('tb_harga_maklon',                   true);
        $this->forge->dropTable('tb_harga_reseller',                 true);

        // Kembalikan kolom tb_reseller & tb_maklon yang diubah
        try {
            $this->db->query("ALTER TABLE `tb_reseller` DROP COLUMN IF EXISTS `tanggal_bergabung_reseller`");
            $this->db->query("ALTER TABLE `tb_reseller` DROP COLUMN IF EXISTS `tahu_dari_reseller`");
            $this->db->query("ALTER TABLE `tb_reseller` ADD COLUMN `harga_default_reseller` DECIMAL(15,2) NULL DEFAULT '0.00'");
        } catch (\Exception $e) {}

        try {
            $this->db->query("ALTER TABLE `tb_maklon` ADD COLUMN `harga_default_maklon` DECIMAL(15,2) NULL DEFAULT '0.00'");
        } catch (\Exception $e) {}
    }
}