<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateTbDetailProduksiTable extends Migration
{
    public function up(): void
    {
        $this->db->query('
            CREATE TABLE IF NOT EXISTS `tb_detail_produksi` (
                `id_detail_produksi` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                `id_produksi`        INT(11) UNSIGNED NOT NULL,
                `id_bahan`           INT(11) UNSIGNED NOT NULL,
                `jumlah_per_batch`   DECIMAL(12,3)    NOT NULL DEFAULT 0.000 COMMENT \'Jumlah bahan per 1 batch resep\',
                `jumlah_total`       DECIMAL(12,3)    NOT NULL DEFAULT 0.000 COMMENT \'Jumlah bahan total yang digunakan\',
                `harga_satuan_bahan` DECIMAL(15,2)    NOT NULL DEFAULT 0.00  COMMENT \'Harga snapshot saat produksi\',
                `subtotal_bahan`     DECIMAL(15,2)    NOT NULL DEFAULT 0.00  COMMENT \'jumlah_total x harga_satuan_bahan\',
                `sudah_dikurangi`    TINYINT(1)       NOT NULL DEFAULT 0     COMMENT \'0=belum dikurangi, 1=stok sudah dikurangi\',
                `created_at`         DATETIME         DEFAULT NULL,
                `updated_at`         DATETIME         DEFAULT NULL,
                PRIMARY KEY (`id_detail_produksi`),
                KEY `idx_detail_produksi_produksi` (`id_produksi`),
                KEY `idx_detail_produksi_bahan`    (`id_bahan`),
                CONSTRAINT `fk_detail_produksi_produksi`
                    FOREIGN KEY (`id_produksi`) REFERENCES `tb_produksi` (`id_produksi`)
                    ON DELETE CASCADE ON UPDATE CASCADE,
                CONSTRAINT `fk_detail_produksi_bahan`
                    FOREIGN KEY (`id_bahan`) REFERENCES `tb_bahan` (`id_bahan`)
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
              COMMENT=\'Detail bahan yang dipakai per batch produksi\'
        ');
    }

    public function down(): void
    {
        $this->db->query('ALTER TABLE `tb_detail_produksi` DROP FOREIGN KEY `fk_detail_produksi_produksi`');
        $this->db->query('ALTER TABLE `tb_detail_produksi` DROP FOREIGN KEY `fk_detail_produksi_bahan`');
        $this->db->query('DROP TABLE IF EXISTS `tb_detail_produksi`');
    }
}