-- =====================================================================
-- 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);
