-- Database Schema: Sistem Pemantauan Keuangan Dapur MBG Multi-Titik
-- Cocok untuk MySQL 5.7+ / MariaDB 10.3+

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `kas_kecil_mutasi`;
DROP TABLE IF EXISTS `distribusi_porsi`;
DROP TABLE IF EXISTS `transaksi_pengeluaran`;
DROP TABLE IF EXISTS `kategori_biaya`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `dapur`;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. Master Dapur (Titik Unit Pelayanan MBG)
CREATE TABLE `dapur` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `kode_dapur` VARCHAR(20) NOT NULL,
  `nama_dapur` VARCHAR(100) NOT NULL,
  `alamat` TEXT NULL,
  `pic_nama` VARCHAR(100) NOT NULL,
  `pic_telepon` VARCHAR(20) NOT NULL,
  `target_penerima` INT(11) NOT NULL DEFAULT 3000,
  `target_porsi_besar` INT(11) NOT NULL DEFAULT 2200,
  `target_porsi_kecil` INT(11) NOT NULL DEFAULT 800,
  `plafon_kas` DECIMAL(15,2) NOT NULL DEFAULT 10000000.00,
  `saldo_kas` DECIMAL(15,2) NOT NULL DEFAULT 10000000.00,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `kode_dapur_unique` (`kode_dapur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Master Pengguna & Role
CREATE TABLE `users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `dapur_id` INT(11) UNSIGNED NULL,
  `nama_lengkap` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('owner', 'finance_pusat', 'akuntan_dapur', 'ops_dapur') NOT NULL DEFAULT 'akuntan_dapur',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_unique` (`email`),
  KEY `fk_users_dapur` (`dapur_id`),
  CONSTRAINT `fk_users_dapur` FOREIGN KEY (`dapur_id`) REFERENCES `dapur` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Master Kategori Biaya
CREATE TABLE `kategori_biaya` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_kategori` VARCHAR(100) NOT NULL,
  `tipe_biaya` ENUM('HPP_BAHAN', 'KEMASAN', 'OPERASIONAL', 'DISTRIBUSI') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Transaksi Pengeluaran & Belanja Harian
CREATE TABLE `transaksi_pengeluaran` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `dapur_id` INT(11) UNSIGNED NOT NULL,
  `kategori_id` INT(11) UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `nominal` DECIMAL(15,2) NOT NULL,
  `keterangan` VARCHAR(255) NOT NULL,
  `nama_supplier` VARCHAR(100) NULL,
  `foto_nota` VARCHAR(255) NULL,
  `status_approval` ENUM('PENDING', 'APPROVED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
  `catatan_approval` VARCHAR(255) NULL,
  `approved_by` INT(11) UNSIGNED NULL,
  `approved_at` DATETIME NULL,
  `created_by` INT(11) UNSIGNED NOT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_transaksi_dapur` (`dapur_id`),
  KEY `fk_transaksi_kategori` (`kategori_id`),
  KEY `fk_transaksi_user` (`created_by`),
  CONSTRAINT `fk_transaksi_dapur` FOREIGN KEY (`dapur_id`) REFERENCES `dapur` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_transaksi_kategori` FOREIGN KEY (`kategori_id`) REFERENCES `kategori_biaya` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Distribusi Porsi Makanan ke Sekolah
CREATE TABLE `distribusi_porsi` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `dapur_id` INT(11) UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `nama_sekolah` VARCHAR(150) NOT NULL,
  `porsi_besar_target` INT(11) NOT NULL DEFAULT 0,
  `porsi_besar_diterima` INT(11) NOT NULL DEFAULT 0,
  `porsi_kecil_target` INT(11) NOT NULL DEFAULT 0,
  `porsi_kecil_diterima` INT(11) NOT NULL DEFAULT 0,
  `porsi_target` INT(11) NOT NULL,
  `porsi_diterima` INT(11) NOT NULL,
  `foto_bast` VARCHAR(255) NULL,
  `catatan` VARCHAR(255) NULL,
  `created_by` INT(11) UNSIGNED NOT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_distribusi_dapur` (`dapur_id`),
  CONSTRAINT `fk_distribusi_dapur` FOREIGN KEY (`dapur_id`) REFERENCES `dapur` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Mutasi Kas Kecil (Petty Cash Tracking)
CREATE TABLE `kas_kecil_mutasi` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `dapur_id` INT(11) UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `tipe` ENUM('TOP_UP', 'PENGELUARAN') NOT NULL,
  `nominal` DECIMAL(15,2) NOT NULL,
  `keterangan` VARCHAR(255) NOT NULL,
  `saldo_sebelum` DECIMAL(15,2) NOT NULL,
  `saldo_sesudah` DECIMAL(15,2) NOT NULL,
  `referensi_id` INT(11) UNSIGNED NULL,
  `created_by` INT(11) UNSIGNED NOT NULL,
  `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_mutasi_dapur` (`dapur_id`),
  CONSTRAINT `fk_mutasi_dapur` FOREIGN KEY (`dapur_id`) REFERENCES `dapur` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================================
-- DATA AWAL (SEEDER)
-- ========================================================

-- Master Dapur
INSERT INTO `dapur` (`id`, `kode_dapur`, `nama_dapur`, `alamat`, `pic_nama`, `pic_telepon`, `target_penerima`, `target_porsi_besar`, `target_porsi_kecil`, `plafon_kas`, `saldo_kas`) VALUES
(1, 'DPR-SLO1', 'Dapur MBG Solo Barat', 'Jl. Slamet Riyadi No. 120, Solo', 'Budi Santoso', '081234567891', 3000, 2200, 800, 10000000.00, 7250000.00),
(2, 'DPR-SLO2', 'Dapur MBG Solo Timur', 'Jl. Kolonel Sutarto No. 45, Solo', 'Siti Rahma', '081234567892', 3000, 2200, 800, 10000000.00, 5800000.00),
(3, 'DPR-SKH1', 'Dapur MBG Sukoharjo', 'Jl. Jenderal Sudirman No. 88, Sukoharjo', 'Agus Prasetyo', '081234567893', 3000, 2200, 800, 10000000.00, 9100000.00);

-- Master Pengguna (Password default: 'password123' ter-hash dengan BCRYPT)
-- Hash generated via password_hash('password123', PASSWORD_BCRYPT)
INSERT INTO `users` (`id`, `dapur_id`, `nama_lengkap`, `email`, `password`, `role`, `is_active`) VALUES
(1, NULL, 'Bapak Hendra (Owner)', 'owner@mbg.id', '$2y$10$LtvVzEIan0p8pzfnruh6nuaBoVWTsSqdMrXl1DzqKdVd94W1WAiNW', 'owner', 1),
(2, NULL, 'Dewi Lestari (Finance Pusat)', 'finance@mbg.id', '$2y$10$LtvVzEIan0p8pzfnruh6nuaBoVWTsSqdMrXl1DzqKdVd94W1WAiNW', 'finance_pusat', 1),
(3, 1, 'Andi Wijaya (Kasir Solo Barat)', 'akuntan.solo1@mbg.id', '$2y$10$LtvVzEIan0p8pzfnruh6nuaBoVWTsSqdMrXl1DzqKdVd94W1WAiNW', 'akuntan_dapur', 1),
(4, 2, 'Rina Marlina (Kasir Solo Timur)', 'akuntan.solo2@mbg.id', '$2y$10$LtvVzEIan0p8pzfnruh6nuaBoVWTsSqdMrXl1DzqKdVd94W1WAiNW', 'akuntan_dapur', 1),
(5, 1, 'Joko Susilo (Ops Solo Barat)', 'ops.solo1@mbg.id', '$2y$10$LtvVzEIan0p8pzfnruh6nuaBoVWTsSqdMrXl1DzqKdVd94W1WAiNW', 'ops_dapur', 1);

-- Master Kategori Biaya
INSERT INTO `kategori_biaya` (`id`, `nama_kategori`, `tipe_biaya`) VALUES
(1, 'Daging Ayam & Daging Sapi', 'HPP_BAHAN'),
(2, 'Ikan & Sumber Protein Laut/Tawar', 'HPP_BAHAN'),
(3, 'Sayuran Segar & Buah-buahan', 'HPP_BAHAN'),
(4, 'Telur, Tahu & Tempe', 'HPP_BAHAN'),
(5, 'Beras & Karbohidrat', 'HPP_BAHAN'),
(6, 'Bumbu Dapur & Minyak Goreng', 'HPP_BAHAN'),
(7, 'Kemasan / Lunch Box & Sendok', 'KEMASAN'),
(8, 'Gas LPG & Utilitas Dapur', 'OPERASIONAL'),
(9, 'BBM & Biaya Pengantaran', 'DISTRIBUSI');

-- Transaksi Belanja Contoh (Hari ini)
INSERT INTO `transaksi_pengeluaran` (`id`, `dapur_id`, `kategori_id`, `tanggal`, `nominal`, `keterangan`, `nama_supplier`, `foto_nota`, `status_approval`, `created_by`) VALUES
(1, 1, 1, CURRENT_DATE(), 18500000.00, 'Belanja Daging Ayam Fillet 280 kg', 'UD Berkah Ayam Pasar Legi', 'nota_ayam_solo1.jpg', 'APPROVED', 3),
(2, 1, 3, CURRENT_DATE(), 4200000.00, 'Belanja Sayur Buncis, Wortel, Jagung Manis', 'Kios Sayur Segar Bu Yani', 'nota_sayur_solo1.jpg', 'APPROVED', 3),
(3, 1, 6, CURRENT_DATE(), 1800000.00, 'Bumbu rempah & minyak goreng 4 jerigen', 'Toko Sembako Makmur', 'nota_bumbu_solo1.jpg', 'APPROVED', 3),
(4, 1, 7, CURRENT_DATE(), 2750000.00, 'Kemasan Thinwall Foodgrade 2.500 pcs', 'CV Aneka Plastik', 'nota_kemasan_solo1.jpg', 'APPROVED', 3),
(5, 2, 1, CURRENT_DATE(), 14500000.00, 'Daging Ayam 210 kg', 'Kios Ayam Pak Min', 'nota_ayam_solo2.jpg', 'APPROVED', 4),
(6, 2, 3, CURRENT_DATE(), 4900000.00, 'Sayuran Segar & Buah Jeruk', 'Pasar Gede Grosir', 'nota_sayur_solo2.jpg', 'PENDING', 4);

-- Distribusi Porsi Contoh (Hari ini)
INSERT INTO `distribusi_porsi` (`id`, `dapur_id`, `tanggal`, `nama_sekolah`, `porsi_besar_target`, `porsi_besar_diterima`, `porsi_kecil_target`, `porsi_kecil_diterima`, `porsi_target`, `porsi_diterima`, `foto_bast`, `catatan`, `created_by`) VALUES
(1, 1, CURRENT_DATE(), 'TK & SD Negeri 01 Manahan', 600, 600, 250, 250, 850, 850, 'bast_sdn01.jpg', '600 SD (Besar) + 250 TK (Kecil)', 5),
(2, 1, CURRENT_DATE(), 'SMP Negeri 04 Surakarta', 900, 900, 0, 0, 900, 900, 'bast_smp04.jpg', 'Diterima baik oleh waka kesiswaan', 5),
(3, 1, CURRENT_DATE(), 'PAUD & SD Negeri 02 Kerten', 500, 500, 250, 250, 750, 750, 'bast_sdn02.jpg', '500 SD (Besar) + 250 PAUD (Kecil)', 5),
(4, 2, CURRENT_DATE(), 'SD Negeri 03 Jebres', 1000, 1000, 200, 200, 1200, 1200, 'bast_sdn03.jpg', '1000 SD (Besar) + 200 PAUD (Kecil)', 4),
(5, 2, CURRENT_DATE(), 'SMP Negeri 08 Surakarta', 600, 600, 0, 0, 600, 600, 'bast_smp08.jpg', 'Diterima kepala sekolah', 4);