-- ===================================================================== -- 0027_inventory.sql — domain INVENTORI (object-creator + workflow-creator, -- task #27 owner: STOCK ITEM barang non-aset + opname + daily request). -- -- BEDA dari `parts` (sparepart teknik, kartu stok tunggal): inventori umum -- resort (linen, F&B, beverage, guest supplies, general) yang saldonya -- DIHITUNG dari mutasi (inv_stock_moves), bukan kolom saldo denormal -- (keputusan v1 — method Model InvStockMove::balanceOf / subquery list). -- -- Nomor 0027 (bukan 0026): 0026 disisakan untuk agent lost & found yang -- berjalan paralel (koordinasi konkurensi; gap nomor tidak masalah — -- bin/migrate.php melacak per-filename, bukan urutan berturut). -- -- unit_cost DECIMAL NULL DIBIARKAN KOSONG di seed (anti-karang: harga nyata -- ditetapkan owner/purchasing kemudian — [ANGKA: tetapkan]). Semua data seed -- berlabel "DUMMY". property_id=1 (satu-satunya properti live), user id 1 -- (Super Admin) dipakai untuk by_user/created_by (FK aman). -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 1; SET default_storage_engine = InnoDB; -- --------------------------------------------------------------------- -- inv_items — master barang non-aset yang dikelola stoknya. -- --------------------------------------------------------------------- CREATE TABLE inv_items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, code VARCHAR(40) NOT NULL, name VARCHAR(150) NOT NULL, category ENUM('linen','fnb','beverage','guest_supplies','general') NOT NULL DEFAULT 'general', unit VARCHAR(30) NOT NULL DEFAULT 'pcs', par_level DECIMAL(12,3) NULL COMMENT 'stok ideal minimum (par); NULL = tak ditetapkan', department VARCHAR(60) NULL COMMENT 'dept pemilik, match kode hr_departments.code', storage VARCHAR(120) NULL COMMENT 'gudang/lokasi simpan (teks bebas)', unit_cost DECIMAL(14,2) NULL COMMENT 'harga satuan; NULL = belum ditetapkan (anti-karang)', active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_inv_items_property_code (property_id, code), KEY idx_inv_items_property_category (property_id, category), KEY idx_inv_items_property_department (property_id, department), CONSTRAINT fk_inv_items_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- inv_stock_moves — kartu stok (mutasi +/-). Saldo berjalan = SUM(qty). -- move_type: in (terima), out (keluar umum), adjust_opname (koreksi selisih -- opname), requisition (pengeluaran atas daily request). -- ref: rujukan entitas asal (mis. 'opname:3','request:7'). -- --------------------------------------------------------------------- CREATE TABLE inv_stock_moves ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, item_id INT UNSIGNED NOT NULL, qty DECIMAL(14,3) NOT NULL COMMENT 'boleh negatif (keluar/koreksi turun)', move_type ENUM('in','out','adjust_opname','requisition') NOT NULL DEFAULT 'in', ref VARCHAR(60) NULL COMMENT 'mis. opname:3, request:7', note VARCHAR(255) NULL, by_user INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_inv_moves_property_item (property_id, item_id), KEY idx_inv_moves_ref (property_id, ref), CONSTRAINT fk_inv_moves_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_inv_moves_item FOREIGN KEY (item_id) REFERENCES inv_items(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_inv_moves_user FOREIGN KEY (by_user) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- inv_opnames + inv_opname_lines — stock opname (perhitungan fisik). -- diff = qty_actual - qty_system DIHITUNG di query (tak disimpan). -- --------------------------------------------------------------------- CREATE TABLE inv_opnames ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, department VARCHAR(60) NULL, storage VARCHAR(120) NULL, status ENUM('draft','counting','review','closed') NOT NULL DEFAULT 'draft', created_by INT UNSIGNED NULL, closed_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, closed_at DATETIME NULL, PRIMARY KEY (id), KEY idx_inv_opnames_property_status (property_id, status), CONSTRAINT fk_inv_opnames_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_inv_opnames_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_inv_opnames_closed_by FOREIGN KEY (closed_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE inv_opname_lines ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, opname_id INT UNSIGNED NOT NULL, item_id INT UNSIGNED NOT NULL, qty_system DECIMAL(14,3) NOT NULL DEFAULT 0 COMMENT 'saldo sistem saat opname dibuat', qty_actual DECIMAL(14,3) NULL COMMENT 'hasil hitung fisik; NULL = belum dihitung', note VARCHAR(255) NULL, PRIMARY KEY (id), UNIQUE KEY uq_inv_opname_lines (opname_id, item_id), KEY idx_inv_opname_lines_item (item_id), CONSTRAINT fk_inv_opname_lines_opname FOREIGN KEY (opname_id) REFERENCES inv_opnames(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_inv_opname_lines_item FOREIGN KEY (item_id) REFERENCES inv_items(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- inv_requests + inv_request_lines — DAILY REQUEST (dept -> gudang). -- Alur status: diajukan -> disiapkan -> diserahkan -> selesai (atau ditolak). -- --------------------------------------------------------------------- CREATE TABLE inv_requests ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, department VARCHAR(60) NULL COMMENT 'dept peminta, match kode hr_departments.code', request_date DATE NOT NULL, status ENUM('diajukan','disiapkan','diserahkan','selesai','ditolak') NOT NULL DEFAULT 'diajukan', requested_by INT UNSIGNED NULL, handled_by INT UNSIGNED NULL, note VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_inv_requests_property_status (property_id, status), KEY idx_inv_requests_property_dept (property_id, department), CONSTRAINT fk_inv_requests_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_inv_requests_requested_by FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_inv_requests_handled_by FOREIGN KEY (handled_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE inv_request_lines ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, request_id INT UNSIGNED NOT NULL, item_id INT UNSIGNED NOT NULL, qty_requested DECIMAL(14,3) NOT NULL DEFAULT 0, qty_issued DECIMAL(14,3) NULL COMMENT 'jumlah yang benar-benar diserahkan; NULL = belum', PRIMARY KEY (id), UNIQUE KEY uq_inv_request_lines (request_id, item_id), KEY idx_inv_request_lines_item (item_id), CONSTRAINT fk_inv_request_lines_request FOREIGN KEY (request_id) REFERENCES inv_requests(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_inv_request_lines_item FOREIGN KEY (item_id) REFERENCES inv_items(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- SEED DUMMY (property_id=1). unit_cost SENGAJA NULL (anti-karang harga). -- ===================================================================== INSERT INTO inv_items (property_id, code, name, category, unit, par_level, department, storage, unit_cost, active) VALUES (1, 'LIN-SHEET-Q', 'DUMMY — Sprei Queen', 'linen', 'lembar', 40, 'housekeeping', 'Gudang Linen', NULL, 1), (1, 'LIN-TOWEL-B', 'DUMMY — Handuk Mandi', 'linen', 'lembar', 60, 'housekeeping', 'Gudang Linen', NULL, 1), (1, 'FNB-BERAS', 'DUMMY — Beras Premium 5kg', 'fnb', 'karung', 20, 'fnb', 'Gudang F&B', NULL, 1), (1, 'FNB-MINYAK', 'DUMMY — Minyak Goreng 2L', 'fnb', 'botol', 24, 'fnb', 'Gudang F&B', NULL, 1), (1, 'BEV-KOPI', 'DUMMY — Kopi Bubuk Arabika 1kg', 'beverage', 'pak', 15, 'fnb', 'Gudang F&B', NULL, 1), (1, 'BEV-TEH', 'DUMMY — Teh Celup (box 25)', 'beverage', 'box', 20, 'fnb', 'Gudang F&B', NULL, 1), (1, 'BEV-AQUA', 'DUMMY — Air Mineral 600ml', 'beverage', 'dus', 30, 'fnb', 'Gudang F&B', NULL, 1), (1, 'GS-SABUN', 'DUMMY — Sabun Batang 25g', 'guest_supplies', 'pcs', 200, 'housekeeping', 'Gudang GS', NULL, 1), (1, 'GS-DENTAL', 'DUMMY — Dental Kit', 'guest_supplies', 'set', 150, 'housekeeping', 'Gudang GS', NULL, 1), (1, 'GS-SHAMPOO', 'DUMMY — Sampo Sachet 30ml', 'guest_supplies', 'pcs', 200, 'housekeeping', 'Gudang GS', NULL, 1), (1, 'GEN-TISU', 'DUMMY — Tisu Gulung', 'general', 'roll', 100, 'engineering', 'Gudang Umum', NULL, 1), (1, 'GEN-KANTONG', 'DUMMY — Kantong Sampah 60x90', 'general', 'pak', 40, 'engineering', 'Gudang Umum', NULL, 1); -- Mutasi awal (in) — membentuk saldo. by_user=1. INSERT INTO inv_stock_moves (property_id, item_id, qty, move_type, ref, note, by_user) VALUES (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='LIN-SHEET-Q'), 50, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='LIN-TOWEL-B'), 80, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='FNB-BERAS'), 25, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='FNB-MINYAK'), 30, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='BEV-KOPI'), 18, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='BEV-TEH'), 22, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='BEV-AQUA'), 40, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GS-SABUN'), 250, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GS-DENTAL'), 180, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GS-SHAMPOO'), 260, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GEN-TISU'), 120, 'in', NULL, 'DUMMY — stok awal', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GEN-KANTONG'), 45, 'in', NULL, 'DUMMY — stok awal', 1); -- Daily request #1 (housekeeping, SELESAI) + pengeluaran (requisition). INSERT INTO inv_requests (property_id, department, request_date, status, requested_by, handled_by, note) VALUES (1, 'housekeeping', CURDATE(), 'selesai', 1, 1, 'DUMMY — permintaan guest supplies harian'); SET @req1 = LAST_INSERT_ID(); INSERT INTO inv_request_lines (request_id, item_id, qty_requested, qty_issued) VALUES (@req1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GS-SABUN'), 50, 50), (@req1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GS-DENTAL'), 40, 40); INSERT INTO inv_stock_moves (property_id, item_id, qty, move_type, ref, note, by_user) VALUES (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GS-SABUN'), -50, 'requisition', CONCAT('request:', @req1), 'DUMMY — diserahkan ke HK', 1), (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='GS-DENTAL'), -40, 'requisition', CONCAT('request:', @req1), 'DUMMY — diserahkan ke HK', 1); -- Daily request #2 (fnb, DIAJUKAN — belum diproses, tanpa mutasi). INSERT INTO inv_requests (property_id, department, request_date, status, requested_by, handled_by, note) VALUES (1, 'fnb', CURDATE(), 'diajukan', 1, NULL, 'DUMMY — permintaan beverage restoran'); SET @req2 = LAST_INSERT_ID(); INSERT INTO inv_request_lines (request_id, item_id, qty_requested, qty_issued) VALUES (@req2, (SELECT id FROM inv_items WHERE property_id=1 AND code='BEV-KOPI'), 10, NULL), (@req2, (SELECT id FROM inv_items WHERE property_id=1 AND code='BEV-AQUA'), 15, NULL); -- Opname #1 (housekeeping / Gudang Linen, CLOSED) + koreksi selisih. INSERT INTO inv_opnames (property_id, department, storage, status, created_by, closed_by, closed_at) VALUES (1, 'housekeeping', 'Gudang Linen', 'closed', 1, 1, NOW()); SET @op1 = LAST_INSERT_ID(); INSERT INTO inv_opname_lines (opname_id, item_id, qty_system, qty_actual, note) VALUES (@op1, (SELECT id FROM inv_items WHERE property_id=1 AND code='LIN-SHEET-Q'), 50, 48, 'DUMMY — 2 hilang/rusak'), (@op1, (SELECT id FROM inv_items WHERE property_id=1 AND code='LIN-TOWEL-B'), 80, 80, NULL); -- Koreksi selisih tercatat sebagai mutasi adjust_opname (LIN-SHEET-Q -2). INSERT INTO inv_stock_moves (property_id, item_id, qty, move_type, ref, note, by_user) VALUES (1, (SELECT id FROM inv_items WHERE property_id=1 AND code='LIN-SHEET-Q'), -2, 'adjust_opname', CONCAT('opname:', @op1), 'DUMMY — koreksi hasil opname', 1); -- Opname #2 (fnb / Gudang F&B, DRAFT — baru dibuat, belum dihitung). INSERT INTO inv_opnames (property_id, department, storage, status, created_by) VALUES (1, 'fnb', 'Gudang F&B', 'draft', 1); SET @op2 = LAST_INSERT_ID(); INSERT INTO inv_opname_lines (opname_id, item_id, qty_system, qty_actual, note) VALUES (@op2, (SELECT id FROM inv_items WHERE property_id=1 AND code='BEV-KOPI'), 18, NULL, NULL), (@op2, (SELECT id FROM inv_items WHERE property_id=1 AND code='BEV-TEH'), 22, NULL, NULL);