<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * DB Trigger: auto-update stok_bahan saat INSERT / DELETE / UPDATE
 * pada tb_DETAIL_pembelian_bahan (bukan header).
 *
 * Juga update kolom cache total_pembelian_bahan di header otomatis.
 */
class AddTriggerDetailPembelianBahan extends Migration
{
    private string $trgInsert = 'trg_detail_pembelian_tambah_stok';
    private string $trgDelete = 'trg_detail_pembelian_kurang_stok';
    private string $trgUpdate = 'trg_detail_pembelian_update_stok';

    public function up(): void
    {
        // ── Buat folder upload nota ───────────────────────────────────────────
        $uploadPath = FCPATH . 'uploads/nota';
        if (! is_dir($uploadPath)) {
            mkdir($uploadPath, 0755, true);
            file_put_contents($uploadPath . '/.htaccess', implode(PHP_EOL, [
                'Options -Indexes',
                '<FilesMatch "\.(php\d*|phtml|pl|py|jsp|asp|sh|cgi)$">',
                '    Require all denied',
                '</FilesMatch>',
            ]));
            file_put_contents($uploadPath . '/index.html', '');
        }

        // ── AFTER INSERT ──────────────────────────────────────────────────────
        $this->db->query("DROP TRIGGER IF EXISTS `{$this->trgInsert}`");
        $this->db->query("
            CREATE TRIGGER `{$this->trgInsert}`
            AFTER INSERT ON `tb_detail_pembelian_bahan`
            FOR EACH ROW
            BEGIN
                -- Tambah stok bahan
                UPDATE `tb_bahan`
                SET
                    `stok_bahan`  = `stok_bahan` + NEW.`jumlah_bahan`,
                    `harga_bahan` = NEW.`harga_satuan_bahan`,
                    `updated_at`  = NOW()
                WHERE `id_bahan` = NEW.`id_bahan`;

                -- Update cache total di header
                UPDATE `tb_pembelian_bahan`
                SET
                    `total_pembelian_bahan` = (
                        SELECT COALESCE(SUM(`subtotal_bahan`), 0)
                        FROM `tb_detail_pembelian_bahan`
                        WHERE `id_pembelian_bahan` = NEW.`id_pembelian_bahan`
                    ),
                    `updated_at` = NOW()
                WHERE `id_pembelian_bahan` = NEW.`id_pembelian_bahan`;
            END
        ");

        // ── AFTER DELETE ──────────────────────────────────────────────────────
        $this->db->query("DROP TRIGGER IF EXISTS `{$this->trgDelete}`");
        $this->db->query("
            CREATE TRIGGER `{$this->trgDelete}`
            AFTER DELETE ON `tb_detail_pembelian_bahan`
            FOR EACH ROW
            BEGIN
                -- Kembalikan stok bahan
                UPDATE `tb_bahan`
                SET
                    `stok_bahan` = GREATEST(`stok_bahan` - OLD.`jumlah_bahan`, 0),
                    `updated_at` = NOW()
                WHERE `id_bahan` = OLD.`id_bahan`;

                -- Update cache total di header
                UPDATE `tb_pembelian_bahan`
                SET
                    `total_pembelian_bahan` = (
                        SELECT COALESCE(SUM(`subtotal_bahan`), 0)
                        FROM `tb_detail_pembelian_bahan`
                        WHERE `id_pembelian_bahan` = OLD.`id_pembelian_bahan`
                    ),
                    `updated_at` = NOW()
                WHERE `id_pembelian_bahan` = OLD.`id_pembelian_bahan`;
            END
        ");

        // ── AFTER UPDATE ──────────────────────────────────────────────────────
        $this->db->query("DROP TRIGGER IF EXISTS `{$this->trgUpdate}`");
        $this->db->query("
            CREATE TRIGGER `{$this->trgUpdate}`
            AFTER UPDATE ON `tb_detail_pembelian_bahan`
            FOR EACH ROW
            BEGIN
                IF OLD.`id_bahan` <> NEW.`id_bahan` THEN
                    -- Bahan ganti: kembalikan stok lama, tambah stok baru
                    UPDATE `tb_bahan`
                    SET `stok_bahan` = GREATEST(`stok_bahan` - OLD.`jumlah_bahan`, 0), `updated_at` = NOW()
                    WHERE `id_bahan` = OLD.`id_bahan`;

                    UPDATE `tb_bahan`
                    SET `stok_bahan` = `stok_bahan` + NEW.`jumlah_bahan`, `harga_bahan` = NEW.`harga_satuan_bahan`, `updated_at` = NOW()
                    WHERE `id_bahan` = NEW.`id_bahan`;
                ELSE
                    -- Bahan sama, koreksi selisih jumlah
                    UPDATE `tb_bahan`
                    SET
                        `stok_bahan`  = `stok_bahan` + (NEW.`jumlah_bahan` - OLD.`jumlah_bahan`),
                        `harga_bahan` = NEW.`harga_satuan_bahan`,
                        `updated_at`  = NOW()
                    WHERE `id_bahan` = NEW.`id_bahan`;
                END IF;

                -- Update cache total header (bisa beda pembelian jika dipindah)
                UPDATE `tb_pembelian_bahan`
                SET
                    `total_pembelian_bahan` = (
                        SELECT COALESCE(SUM(`subtotal_bahan`), 0)
                        FROM `tb_detail_pembelian_bahan`
                        WHERE `id_pembelian_bahan` = NEW.`id_pembelian_bahan`
                    ),
                    `updated_at` = NOW()
                WHERE `id_pembelian_bahan` = NEW.`id_pembelian_bahan`;
            END
        ");
    }

    public function down(): void
    {
        $this->db->query("DROP TRIGGER IF EXISTS `{$this->trgInsert}`");
        $this->db->query("DROP TRIGGER IF EXISTS `{$this->trgDelete}`");
        $this->db->query("DROP TRIGGER IF EXISTS `{$this->trgUpdate}`");
    }
}