-- ==========================================================================
-- POULTRY MANAGEMENT SYSTEM - DATABASE SCHEMA & SEED DATA
-- Framework: PHP 8 Native (MVC)
-- Compatible: MySQL 8.0+ | Engine: InnoDB | Charset: utf8mb4_unicode_ci
-- ==========================================================================

START TRANSACTION;

-- ==========================================================================
-- 1. REFERENCE TABLES (INDIVIDUAL LOOKUPS)
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `ref_user_statuses` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_roles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `slug` VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_coop_types` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_coop_statuses` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_batch_statuses` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_egg_grades` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_sales_types` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_health_outcomes` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_item_categories` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_transaction_types` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ref_financial_trx_types` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) UNIQUE NOT NULL,
    `label` VARCHAR(100) NOT NULL,
    `cashflow_direction` VARCHAR(10) NOT NULL COMMENT 'IN, OUT',
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_roles` (
    `user_id` BIGINT UNSIGNED NOT NULL,
    `role_id` BIGINT UNSIGNED NOT NULL,
    `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`, `role_id`),
    CONSTRAINT `fk_ur_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ur_role` FOREIGN KEY (`role_id`) REFERENCES `ref_roles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- 3. FARM OPERATIONS TABLES
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `farms` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `location_maps` VARCHAR(255) DEFAULT NULL,
    `pic_name` VARCHAR(100) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coops` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `farm_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `coop_type_id` BIGINT UNSIGNED NOT NULL,
    `capacity_initial` INT UNSIGNED NOT NULL,
    `capacity_current` INT UNSIGNED NOT NULL,
    `coop_status_id` BIGINT UNSIGNED NOT NULL,
    `manager_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    CONSTRAINT `fk_coops_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_coops_type` FOREIGN KEY (`coop_type_id`) REFERENCES `ref_coop_types`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_coops_status` FOREIGN KEY (`coop_status_id`) REFERENCES `ref_coop_statuses`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_coops_manager` FOREIGN KEY (`manager_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `batches` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `coop_id` BIGINT UNSIGNED NOT NULL,
    `batch_code` VARCHAR(30) UNIQUE NOT NULL,
    `breed` VARCHAR(50),
    `initial_count` INT UNSIGNED NOT NULL,
    `start_date` DATE NOT NULL,
    `target_harvest_date` DATE DEFAULT NULL,
    `batch_status_id` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_batches_coop` FOREIGN KEY (`coop_id`) REFERENCES `coops`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_batches_status` FOREIGN KEY (`batch_status_id`) REFERENCES `ref_batch_statuses`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `daily_records` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `batch_id` BIGINT UNSIGNED NOT NULL,
    `record_date` DATE NOT NULL,
    `feed_consumed_kg` DECIMAL(8,2) DEFAULT 0.00,
    `water_consumed_l` DECIMAL(8,2) DEFAULT 0.00,
    `mortality_count` INT UNSIGNED DEFAULT 0,
    `culling_count` INT UNSIGNED DEFAULT 0,
    `avg_temperature` DECIMAL(4,1) DEFAULT NULL,
    `avg_humidity` DECIMAL(5,2) DEFAULT NULL,
    `notes` TEXT,
    `created_by` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_batch_date` (`batch_id`, `record_date`),
    CONSTRAINT `fk_daily_records_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `egg_production` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `batch_id` BIGINT UNSIGNED NOT NULL,
    `record_date` DATE NOT NULL,
    `qty` INT UNSIGNED NOT NULL,
    `egg_grade_id` BIGINT UNSIGNED NOT NULL,
    `weight_total_kg` DECIMAL(8,2) DEFAULT NULL,
    `collected_by` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_eggs_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_eggs_grade` FOREIGN KEY (`egg_grade_id`) REFERENCES `ref_egg_grades`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_events` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `batch_id` BIGINT UNSIGNED NOT NULL,
    `event_date` DATETIME NOT NULL,
    `symptom_description` TEXT,
    `diagnosis` VARCHAR(150),
    `treatment` TEXT,
    `medicine_used` VARCHAR(100),
    `cost` DECIMAL(10,2) DEFAULT 0.00,
    `health_outcome_id` BIGINT UNSIGNED NOT NULL,
    `vet_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_health_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_health_outcome` FOREIGN KEY (`health_outcome_id`) REFERENCES `ref_health_outcomes`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `harvests` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `batch_id` BIGINT UNSIGNED NOT NULL,
    `harvest_date` DATE NOT NULL,
    `quantity_ekor` INT UNSIGNED NOT NULL,
    `total_weight_kg` DECIMAL(10,2) NOT NULL,
    `price_per_kg` DECIMAL(12,2) NOT NULL,
    `revenue_amount` DECIMAL(14,2) NOT NULL,
    `egg_grade_id` BIGINT UNSIGNED DEFAULT NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_harvest_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_harvest_grade` FOREIGN KEY (`egg_grade_id`) REFERENCES `ref_egg_grades`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- 4. IOT SENSOR TABLES
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `sensor_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `coop_id` BIGINT UNSIGNED NOT NULL,
    `recorded_at` TIMESTAMP NOT NULL,
    `temp_celsius` DECIMAL(5,2) DEFAULT NULL,
    `humidity_pct` DECIMAL(5,2) DEFAULT NULL,
    `ammonia_ppm` DECIMAL(6,2) DEFAULT NULL,
    `fans_active` INT DEFAULT 0,
    INDEX `idx_sensor_coop_time` (`coop_id`, `recorded_at`),
    CONSTRAINT `fk_sensor_logs_coop` FOREIGN KEY (`coop_id`) REFERENCES `coops`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- 5. INVENTORY & LOGISTICS TABLES
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `warehouses` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `farm_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `is_main_warehouse` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_warehouses_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `items` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `sku` VARCHAR(30) UNIQUE NOT NULL,
    `item_name` VARCHAR(100) NOT NULL,
    `item_category_id` BIGINT UNSIGNED NOT NULL,
    `unit_of_measurement` VARCHAR(20) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_items_category` FOREIGN KEY (`item_category_id`) REFERENCES `ref_item_categories`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `inventory_stocks` (
    `warehouse_id` BIGINT UNSIGNED NOT NULL,
    `item_id` BIGINT UNSIGNED NOT NULL,
    `current_qty` DECIMAL(12,2) DEFAULT 0.00,
    `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`warehouse_id`, `item_id`),
    CONSTRAINT `fk_stock_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_stock_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `inventory_movements` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `item_id` BIGINT UNSIGNED NOT NULL,
    `source_warehouse_id` BIGINT UNSIGNED DEFAULT NULL,
    `dest_warehouse_id` BIGINT UNSIGNED DEFAULT NULL,
    `batch_id` BIGINT UNSIGNED DEFAULT NULL,
    `transaction_type_id` BIGINT UNSIGNED NOT NULL,
    `qty` DECIMAL(10,2) NOT NULL,
    `unit_price_at_timestamp` DECIMAL(12,2) DEFAULT 0.00,
    `txn_date` DATETIME NOT NULL,
    `notes` TEXT,
    `created_by` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_inv_mov_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_inv_mov_src_wh` FOREIGN KEY (`source_warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_inv_mov_dst_wh` FOREIGN KEY (`dest_warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_inv_mov_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_inv_mov_type` FOREIGN KEY (`transaction_type_id`) REFERENCES `ref_transaction_types`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- 6. FINANCE & ACCOUNTING TABLES
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `financial_ledgers` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `farm_id` BIGINT UNSIGNED DEFAULT NULL,
    `batch_id` BIGINT UNSIGNED DEFAULT NULL,
    `financial_trx_type_id` BIGINT UNSIGNED NOT NULL,
    `amount` DECIMAL(14,2) NOT NULL,
    `transaction_date` DATETIME NOT NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_ledger_farm` FOREIGN KEY (`farm_id`) REFERENCES `farms`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_ledger_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_ledger_type` FOREIGN KEY (`financial_trx_type_id`) REFERENCES `ref_financial_trx_types`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- 7. SEED DATA: REFERENCE TABLES
-- ==========================================================================

INSERT INTO `ref_user_statuses` (`code`, `label`, `description`) VALUES
('ACTIVE', 'Aktif', 'Akun pengguna dapat digunakan'),
('DISABLED', 'Dinonaktifkan', 'Akun diblokir sementara/permanen');

INSERT INTO `ref_roles` (`name`, `slug`) VALUES
('Super Administrator', 'admin'),
('Farm Manager', 'manager'),
('Kandang Supervisor', 'supervisor'),
('Dokter Hewan / Vet', 'vet'),
('Staf Panen & Penjualan', 'sales'),
('Auditor / Viewer', 'viewer');

INSERT INTO `ref_coop_types` (`code`, `label`, `description`) VALUES
('CLOSED', 'Closed House', 'Kandang tertutup dengan kontrol lingkungan'),
('OPEN', 'Open House', 'Kandang terbuka/semi terbuka'),
('SEMI_CLOSED', 'Semi Closed', 'Campuran ventilasi alami dan mekanis');

INSERT INTO `ref_coop_statuses` (`code`, `label`, `description`) VALUES
('OPERATIONAL', 'Beroperasi', 'Kandang aktif digunakan'),
('MAINTENANCE', 'Pemeliharaan', 'Sedang perbaikan/sanitasi'),
('QUARANTINE', 'Karantina', 'Isolasi penyakit/persiapan batch baru');

INSERT INTO `ref_batch_statuses` (`code`, `label`, `description`) VALUES
('DOC_ARRIVED', 'DOC Masuk', 'Day Old Chick baru diterima'),
('GROWING', 'Pertumbuhan', 'Fase pertumbuhan/pembesaran'),
('LAYING', 'Produksi Telur', 'Fase bertelur'),
('HARVESTED', 'Panen', 'Batch selesai dipanen/dijual'),
('TERMINATED', 'Dihentikan', 'Batch dihentikan (wabah/culling massal)');

INSERT INTO `ref_egg_grades` (`code`, `label`, `description`) VALUES
('AA', 'Grade AA', 'Telur super, berat & kerangka sempurna'),
('A', 'Grade A', 'Telur berkualitas baik, standar pasar'),
('B', 'Grade B', 'Telur ukuran kecil/keretakan halus'),
('REJECT', 'Reject/Cacat', 'Telur pecah, abnormal, atau tidak layak jual');

INSERT INTO `ref_sales_types` (`code`, `label`, `description`) VALUES
('LIVE_CHICKEN', 'Ayam Hidup', 'Penjualan ayam hidup (pedaging)'),
('PROCESSED', 'Ayam Potong', 'Penjualan karkas/ayam potong'),
('EGGS', 'Telur', 'Penjualan telur konsumsi'),
('MANURE', 'Kotoran/Pupuk', 'Penjualan limbah kandang jadi pupuk');

INSERT INTO `ref_health_outcomes` (`code`, `label`, `description`) VALUES
('RECOVERED', 'Sembuh', 'Hewan pulih setelah perawatan'),
('TREATMENT_ONGOING', 'Perawatan Lanjut', 'Masih dalam proses pengobatan'),
('CULLED', 'Afkir/Culling', 'Disingkirkan atas dasar ekonomi/kesehatan'),
('DIED', 'Mati', 'Kematian tidak terduga/wabah');

INSERT INTO `ref_item_categories` (`code`, `label`, `description`) VALUES
('FEED', 'Pakan', 'Konsentrat, jagung, dedak, suplemen'),
('MEDICINE', 'Obat', 'Antibiotik, elektrolit, vitamin'),
('VACCINE', 'Vaksin', 'ND, IB, AI, Gumboro, dll'),
('EQUIPMENT', 'Peralatan', 'Tempat pakan/minum, sprayer, timbangan'),
('PACKAGING', 'Kemasan', 'Tray telur, kardus, plastik, tali');

INSERT INTO `ref_transaction_types` (`code`, `label`, `description`) VALUES
('IN', 'Masuk', 'Pembelian/penambahan stok'),
('OUT', 'Keluar', 'Penggunaan/pemakaian produksi'),
('ADJUSTMENT', 'Penyesuaian', 'Koreksi stock opname'),
('RETURN', 'Retur', 'Pengembalian ke supplier/kembali dari produksi');

INSERT INTO `ref_financial_trx_types` (`code`, `label`, `cashflow_direction`, `description`) VALUES
('SALES', 'Penjualan / Panen', 'IN', 'Pendapatan dari penjualan hasil ternak'),
('PURCHASE_INV', 'Pembelian Stok', 'OUT', 'Pengeluaran untuk stok pakan/obat'),
('OPERATIONAL', 'Biaya Operasional', 'OUT', 'Biaya listrik, servis, dan harian'),
('SALARY', 'Gaji & Upah', 'OUT', 'Pembayaran gaji pekerja'),
('OTHER_INCOME', 'Pendapatan Lain', 'IN', 'Pendapatan di luar operasional utama'),
('OTHER_EXPENSE', 'Biaya Lain-lain', 'OUT', 'Biaya tak terduga lainnya');

-- ==========================================================================
-- 9. SEED DATA: DUMMY FARM & WAREHOUSE
-- ==========================================================================
INSERT INTO `farms` (`name`, `location_maps`, `pic_name`) VALUES 
('Farm Pusat Cirebon', 'Cirebon, West Java', 'Budi Santoso');

INSERT INTO `warehouses` (`farm_id`, `name`, `is_main_warehouse`) VALUES
((SELECT `id` FROM `farms` ORDER BY `id` DESC LIMIT 1), 'Gudang Logistik Utama', 1),
((SELECT `id` FROM `farms` ORDER BY `id` DESC LIMIT 1), 'Gudang Pakan Kandang A', 0);

-- ==========================================================================
-- 10. FINALIZE
-- ==========================================================================

COMMIT;