-- =====================================================================
-- 0029_purchase_requests.sql — object + workflow PERMINTAAN PEMBELIAN
-- (Purchase Request / PR). Alur operasional pembelian EKSTERNAL: form
-- permintaan -> di-PO -> indent -> barang datang -> diserahkan (diterima).
--
-- BATAS SISTEM (keputusan owner via PM, FINAL): Sanggraloka One BUKAN sistem
-- akuntansi. Modul PO/invoice/AP resmi tetap di journal.id (aplikasi pihak
-- ketiga). Tabel ini HANYA jurnal OPERASIONAL (permintaan -> barang diterima).
-- Karena itu: TIDAK ADA kolom harga/harga_satuan/total/pajak/pembayaran/
-- approval finansial di sini (itu domain journal.id). Satu-satunya kaitan ke
-- journal.id = `journal_po_ref` (nomor PO journal.id, teks bebas nullable,
-- diisi manual sebagai catatan silang) — bukan logika, hanya rujukan.
--
-- Status mengikuti PERSIS nilai stage App\Support\TaskJourneys::procurement
-- (diminta/di_po/indent/barang_datang/diserahkan) supaya transisi status
-- object bisa memanggil advanceStage(stage=status) langsung — status object =
-- sumber kebenaran, task papan (hr_tasks kind='staged') mengikuti. Tambahan
-- terminal 'selesai' (arsip setelah diserahkan) + 'ditolak'.
--
-- Nomor 0029 (bukan 0028): 0028 disisakan untuk agent lain yang berjalan
-- paralel (koordinasi konkurensi; gap nomor tidak masalah — bin/migrate.php
-- melacak per-filename). Semua seed berlabel "DUMMY", property_id=1.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;
SET default_storage_engine = InnoDB;

-- ---------------------------------------------------------------------
-- pr_requests — header permintaan pembelian.
-- ---------------------------------------------------------------------
CREATE TABLE pr_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',
    requested_by   INT UNSIGNED NULL COMMENT 'FK hr_employees.id (karyawan pengaju)',
    title          VARCHAR(200) NOT NULL,
    description    TEXT NULL,
    priority       ENUM('low','normal','urgent') NOT NULL DEFAULT 'normal',
    status         ENUM('diminta','di_po','indent','barang_datang','diserahkan','selesai','ditolak')
                       NOT NULL DEFAULT 'diminta',
    journal_po_ref VARCHAR(60) NULL COMMENT 'nomor PO journal.id (catatan silang manual; BUKAN logika PO)',
    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_pr_requests_property_status (property_id, status),
    KEY idx_pr_requests_property_dept (property_id, department),
    CONSTRAINT fk_pr_requests_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_pr_requests_requested_by FOREIGN KEY (requested_by) REFERENCES hr_employees(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- pr_request_lines — baris item yang diminta. item_name = TEKS BEBAS
-- (pembelian eksternal, bukan FK inv_items — barang bisa belum ada di
-- master stok). TANPA kolom harga (financial = journal.id).
-- ---------------------------------------------------------------------
CREATE TABLE pr_request_lines (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    pr_request_id  INT UNSIGNED NOT NULL,
    item_name      VARCHAR(200) NOT NULL,
    qty            DECIMAL(14,3) NOT NULL DEFAULT 0,
    unit           VARCHAR(30)  NOT NULL DEFAULT 'pcs',
    notes          VARCHAR(255) NULL,
    PRIMARY KEY (id),
    KEY idx_pr_request_lines_request (pr_request_id),
    CONSTRAINT fk_pr_request_lines_request FOREIGN KEY (pr_request_id) REFERENCES pr_requests(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SEED DUMMY (property_id=1). requested_by = karyawan pertama properti.
-- Task journey (hr_tasks) TIDAK di-seed di sini (dibuat saat runtime oleh
-- Model saat pengajuan nyata — konsisten dgn seed inv_requests 0027).
-- =====================================================================
SET @emp1 = (SELECT id FROM hr_employees WHERE property_id = 1 ORDER BY id LIMIT 1);

-- PR #1 (engineering, tahap di_po — punya nomor PO journal.id catatan silang).
INSERT INTO pr_requests (property_id, department, requested_by, title, description, priority, status, journal_po_ref)
    VALUES (1, 'engineering', @emp1, 'DUMMY — Pembelian spare part pompa kolam', 'Kebutuhan mendesak perbaikan pompa filter kolam utama.', 'urgent', 'di_po', 'PO-JID-2026-0141');
SET @pr1 = LAST_INSERT_ID();
INSERT INTO pr_request_lines (pr_request_id, item_name, qty, unit, notes) VALUES
    (@pr1, 'DUMMY — Mechanical seal pompa 2 inch', 2, 'pcs', 'merek setara Grundfos'),
    (@pr1, 'DUMMY — Bearing 6205 ZZ',              4, 'pcs', NULL),
    (@pr1, 'DUMMY — Gasket set',                   1, 'set', NULL);

-- PR #2 (housekeeping, tahap diminta — baru diajukan, belum diproses).
INSERT INTO pr_requests (property_id, department, requested_by, title, description, priority, status, journal_po_ref)
    VALUES (1, 'housekeeping', @emp1, 'DUMMY — Pengadaan trolley linen baru', 'Penggantian 2 trolley linen yang sudah rusak.', 'normal', 'diminta', NULL);
SET @pr2 = LAST_INSERT_ID();
INSERT INTO pr_request_lines (pr_request_id, item_name, qty, unit, notes) VALUES
    (@pr2, 'DUMMY — Trolley linen stainless',      2, 'unit', 'dengan penutup kanvas'),
    (@pr2, 'DUMMY — Roda castor 4 inch',           8, 'pcs', 'cadangan');