-- =====================================================================
-- HotelFix v2 — Migration 0004_seed_demo
-- DUMMY DATA transaksional untuk QC / demo dashboard. DEV-ONLY.
-- JANGAN dijalankan di produksi.
--
-- Semua tanggal RELATIF terhadap NOW()/CURDATE() supaya Rekap Harian
-- ("kemarin", "hari ini") selalu berisi kapan pun QC dijalankan.
-- property_id = 1 (HTL1) di semua baris.
-- Tabel `photos` sengaja TIDAK di-seed (file fisik tidak ada).
--
-- Prasyarat: 0001, 0002, 0003 sudah diterapkan. Jalankan sekali.
-- Referensi id dari seed sebelumnya:
--   users:   1 super.admin, 2 property.admin, 3 depthead.eng,
--            4 tech.candra, 5 hk.reporter, 6 gm.viewer
--   assets:  1 Genset, 2 Panel LVMDP, 3 Lift Tamu, 7 Boiler, 9 Cooling
--            Tower, 10 AHU, 11-13 AC kamar, 14 Pompa Booster, 15 NVR,
--            16 Server, 17 Cold Room, 18 Freezer, 19 Pompa Kolam,
--            20 Washer
--   checklists: 1 Genset mingguan, 2 AC bulanan, 3 PPM kamar, 4 HACCP,
--            5 CCTV, 6 Server room, 7 Kolam, 8 Fire safety, 10 Cooling
--            tower, 11 Panel, 12 Kendaraan
-- =====================================================================


-- ---------------------------------------------------------------------
-- 0a. Master tambahan yang dibutuhkan demo: lokasi parkir + mobil
--     operasional (aset ber-meter km untuk checklist kendaraan & KIR)
-- ---------------------------------------------------------------------
INSERT INTO locations (id, property_id, code, name, building, wing, floor, type, active) VALUES
(16, 1, 'PARKIR-B1', 'Area Parkir Basement', 'BOH', NULL, 'B1', 'external', 1);

INSERT INTO assets
(id, property_id, asset_code, accounting_asset_no, name, category_id, location_id, brand, model, serial_number,
 purchase_date, purchase_price, vendor_id, warranty_until, install_date, expected_life_years,
 criticality, status, meter_type, meter_current, parent_asset_id, notes) VALUES
(21, 1, 'AST-000021', 'FA-2023-120', 'Mobil Operasional Avanza', 30, 16, 'Toyota', 'Avanza 1.3 G', 'TYT-AVZ-001',
 '2023-02-01', 235000000.00, NULL, '2026-02-01', '2023-02-15', 8,
 'normal', 'active', 'km', NULL, NULL, 'Mobil operasional engineering & purchasing');

-- ---------------------------------------------------------------------
-- 0b. Skills — sertifikat AK3 Candra dibuat segera habis (< 60 hari)
--     supaya badge merah profil & notifikasi kepatuhan muncul.
--     (Baris (4,5) sudah ada dari 0002 — di-UPDATE, bukan insert ulang.)
-- ---------------------------------------------------------------------
UPDATE skills
   SET valid_until = DATE_ADD(CURDATE(), INTERVAL 45 DAY)
 WHERE user_id = 4 AND category_id = 5;

INSERT INTO skills (user_id, category_id, level, cert_document, valid_until) VALUES
(3, 4, 'advanced', NULL, NULL),   -- depthead.eng: AC Split advanced
(3, 6, 'advanced', NULL, NULL);   -- depthead.eng: Panel TM advanced

-- ---------------------------------------------------------------------
-- 0c. Kontrak vendor lift (berakhir < 60 hari → muncul di kepatuhan)
--     + dokumen aset dengan expiry < 30 hari (SLO genset, KIR mobil)
-- ---------------------------------------------------------------------
INSERT INTO contracts (id, property_id, vendor_id, title, start_date, end_date, cost_per_year, sla_response_hours, visit_frequency, reminder_days, file) VALUES
(1, 1, 2, 'Kontrak Servis Lift Tahunan', DATE_SUB(CURDATE(), INTERVAL 320 DAY), DATE_ADD(CURDATE(), INTERVAL 45 DAY), 60000000.00, 4, 'monthly', 60, NULL);

INSERT INTO contract_assets (contract_id, asset_id) VALUES
(1, 3),
(1, 4);

INSERT INTO asset_documents (asset_id, type, title, file, expiry_date, reminder_days) VALUES
(1,  'permit', 'SLO Genset (Sertifikat Laik Operasi)', 'docs/slo_genset_htl1.pdf',  DATE_ADD(CURDATE(), INTERVAL 20 DAY), 60),
(21, 'permit', 'KIR Mobil Operasional Avanza',          'docs/kir_avanza_htl1.pdf', DATE_ADD(CURDATE(), INTERVAL 25 DAY), 30);

-- ---------------------------------------------------------------------
-- 0d. Jadwal PM aktif (next_due dekat, mengisi kalender & generator)
-- ---------------------------------------------------------------------
INSERT INTO pm_schedules
(id, property_id, name, scope, asset_id, location_id, category_id, department, checklist_id,
 frequency_type, frequency_value, weekly_days, monthly_day, lead_time_days, grace_days,
 schedule_base, executor, vendor_id, auto_assign, spread, estimated_minutes, priority, active,
 next_due_date, next_due_meter) VALUES
(1, 1, 'Genset - Servis 250 Jam',            'asset',    1, NULL, NULL, 'engineering', NULL,
 'meter',    250, NULL, NULL, 3, 2, 'floating', 'internal', NULL, 0, 0, 180, 'high',   1, NULL, 5000.00),
(2, 1, 'Genset - Mingguan',                  'asset',    1, NULL, NULL, 'engineering', 1,
 'weekly',   1, 'mon', NULL, 3, 1, 'fixed',    'internal', NULL, 1, 0, 45,  'high',   1, DATE_ADD(CURDATE(), INTERVAL 3 DAY), NULL),
(3, 1, 'AC Split - Bulanan (semua unit)',    'category', NULL, NULL, 4, 'engineering', 2,
 'monthly',  1, NULL, 5,    3, 3, 'fixed',    'internal', NULL, 1, 1, 45,  'normal', 1, DATE_ADD(CURDATE(), INTERVAL 2 DAY), NULL),
(4, 1, 'PPM Kamar - 3 Bulanan (30 titik)',   'location', NULL, 1, NULL, 'engineering', 3,
 'quarterly',1, NULL, NULL, 5, 5, 'floating', 'internal', NULL, 0, 1, 90,  'normal', 1, DATE_ADD(CURDATE(), INTERVAL 7 DAY), NULL);

-- =====================================================================
-- A. WORK ORDERS (30 baris, WO-000001 .. WO-000030)
-- =====================================================================

-- --- A1. Korektif selesai (done/verified) — termasuk downtime aset
--         critical (WO-4 freezer) & WO biaya besar tanpa approve (WO-25)
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 reporter_id, assignee_id, vendor_id, supervisor_id, description, priority, status, repair_note,
 created_at, due_date, started_at, done_at, verified_at, downtime_start, downtime_end,
 labor_minutes, cost_parts, cost_vendor, cost_other) VALUES
(1, 1, 'WO-000001', 'corrective', 'report', 11, 1, 4, 5, 4, NULL, 3,
 'AC kamar 1201 tidak dingin, hembusan lemah', 'high', 'verified',
 'Cuci filter & evaporator, ganti filter baru, tekanan freon normal',
 NOW() - INTERVAL 3 DAY, DATE_SUB(CURDATE(), INTERVAL 2 DAY),
 NOW() - INTERVAL 50 HOUR, NOW() - INTERVAL 48 HOUR, NOW() - INTERVAL 26 HOUR, NULL, NULL,
 60, 90000.00, 0, 50000.00),
(2, 1, 'WO-000002', 'corrective', 'report', NULL, 4, 21, 5, 4, NULL, 3,
 'Sealant wastafel kamar 0801 bocor, air merembes ke kabinet', 'normal', 'verified',
 'Kupas sealant lama, aplikasi sealant silikon baru, uji rendam OK',
 NOW() - INTERVAL 2 DAY, DATE_SUB(CURDATE(), INTERVAL 1 DAY),
 NOW() - INTERVAL 30 HOUR, NOW() - INTERVAL 27 HOUR, NOW() - INTERVAL 20 HOUR, NULL, NULL,
 90, 0, 0, 225000.00),
(3, 1, 'WO-000003', 'corrective', 'report', NULL, 7, 14, 5, 4, NULL, NULL,
 'Emergency lamp koridor lantai 12 mati', 'normal', 'done',
 'Ganti unit emergency lamp LED baru, tes tombol OK',
 NOW() - INTERVAL 28 HOUR, DATE_SUB(CURDATE(), INTERVAL 1 DAY),
 NOW() - INTERVAL 26 HOUR, NOW() - INTERVAL 25 HOUR, NULL, NULL, NULL,
 30, 120000.00, 0, 0),
(4, 1, 'WO-000004', 'corrective', 'checklist_fail', 18, 13, 26, NULL, 4, NULL, 3,
 'Suhu freezer kitchen naik ke -12C (ambang -18C) dari checklist HACCP', 'urgent', 'done',
 'Bersihkan kondensor, ganti karet pintu, suhu kembali -19C stabil',
 NOW() - INTERVAL 32 HOUR, DATE_SUB(CURDATE(), INTERVAL 1 DAY),
 NOW() - INTERVAL 30 HOUR, NOW() - INTERVAL 26 HOUR, NULL,
 NOW() - INTERVAL 30 HOUR, NOW() - INTERVAL 26 HOUR,
 240, 0, 0, 0),
(25, 1, 'WO-000025', 'corrective', 'manual', 7, 11, 9, NULL, NULL, 1, 3,
 'Boiler water heater bocor pada heat exchanger', 'high', 'done',
 'Perbaikan heat exchanger oleh vendor, penggantian gasket set lengkap',
 NOW() - INTERVAL 4 DAY, DATE_SUB(CURDATE(), INTERVAL 2 DAY),
 NOW() - INTERVAL 60 HOUR, NOW() - INTERVAL 50 HOUR, NULL, NULL, NULL,
 NULL, 0, 4000000.00, 2600000.00),
(29, 1, 'WO-000029', 'corrective', 'report', 20, 14, 16, 5, 4, NULL, NULL,
 'Belt washer laundry putus', 'high', 'done',
 'Ganti belt baru (beli toko), alignment pulley, uji jalan 2 siklus OK',
 NOW() - INTERVAL 26 HOUR, DATE_SUB(CURDATE(), INTERVAL 1 DAY),
 NOW() - INTERVAL 24 HOUR, NOW() - INTERVAL 22 HOUR, NULL, NULL, NULL,
 120, 0, 0, 550000.00);

-- --- A2. Sedang dikerjakan (in_progress) milik Candra — WO-11 urgent
--         cold room dengan downtime berjalan (aset critical)
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 reporter_id, assignee_id, description, priority, status, created_at, due_date, started_at, downtime_start) VALUES
(5, 1, 'WO-000005', 'corrective', 'report', 12, 2, 4, 5, 4,
 'AC kamar 1202 menetes air dari unit indoor', 'normal', 'in_progress',
 NOW() - INTERVAL 4 HOUR, CURDATE(), NOW() - INTERVAL 2 HOUR, NULL),
(6, 1, 'WO-000006', 'corrective', 'manual', 14, 11, 8, NULL, 4,
 'Pompa booster bunyi kasar, dicurigai bearing aus', 'high', 'in_progress',
 NOW() - INTERVAL 7 HOUR, CURDATE(), NOW() - INTERVAL 5 HOUR, NULL),
(11, 1, 'WO-000011', 'corrective', 'checklist_fail', 17, 13, 26, NULL, 4,
 'Suhu cold room naik ke 9C - pendinginan tidak tercapai', 'urgent', 'in_progress',
 NOW() - INTERVAL 2 HOUR, CURDATE(), NOW() - INTERVAL 1 HOUR, NOW() - INTERVAL 90 MINUTE);

-- --- A3. Menunggu sparepart (waiting_parts) — ter-link wo_parts di bawah
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 reporter_id, assignee_id, description, priority, status, created_at, due_date, started_at) VALUES
(7, 1, 'WO-000007', 'corrective', 'report', 13, 4, 4, 5, 4,
 'AC kamar 0801 tidak dingin, filter rusak perlu penggantian', 'high', 'waiting_parts',
 NOW() - INTERVAL 1 DAY, CURDATE(), NOW() - INTERVAL 20 HOUR);

-- --- A4. Laporan baru (reported) + 1 urgent SLA breach + 1 rejected duplikat
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 reporter_id, description, priority, status, skip_reason, created_at, due_date) VALUES
(8,  1, 'WO-000008', 'corrective', 'report', NULL, 3, 8, 5,
 'Wastafel kamar 1203 mampet', 'normal', 'reported', NULL,
 NOW() - INTERVAL 3 HOUR, DATE_ADD(CURDATE(), INTERVAL 1 DAY)),
(9,  1, 'WO-000009', 'corrective', 'report', NULL, 2, 24, 5,
 'TV kamar 1202 tidak ada sinyal (aset belum ter-link)', 'normal', 'reported', NULL,
 NOW() - INTERVAL 2 HOUR, DATE_ADD(CURDATE(), INTERVAL 1 DAY)),
(10, 1, 'WO-000010', 'corrective', 'report', 3, 6, 11, 5,
 'Lift tamu 1 bunyi berdecit dan pintu menutup lambat', 'urgent', 'reported', NULL,
 NOW() - INTERVAL 30 HOUR, DATE_SUB(CURDATE(), INTERVAL 1 DAY)),   -- due kemarin = SLA breach
(26, 1, 'WO-000026', 'corrective', 'report', 11, 1, 4, 5,
 'AC kamar 1201 tidak dingin (laporan kedua)', 'normal', 'rejected',
 'Duplikat dari WO-000001',
 NOW() - INTERVAL 3 DAY, DATE_SUB(CURDATE(), INTERVAL 2 DAY));

-- --- A5. Preventif terjadwal DUE HARI INI, ditugaskan ke Candra
--         (mengisi halaman "Hari Ini" & kalender)
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 schedule_id, checklist_version_id, assignee_id, description, priority, status, created_at, due_date) VALUES
(12, 1, 'WO-000012', 'preventive', 'schedule', 11, 1,  4,  3,    2,  4,
 'PM bulanan AC split kamar 1201', 'normal', 'scheduled', NOW() - INTERVAL 3 DAY, CURDATE()),
(13, 1, 'WO-000013', 'preventive', 'schedule', 12, 2,  4,  3,    2,  4,
 'PM bulanan AC split kamar 1202', 'normal', 'scheduled', NOW() - INTERVAL 3 DAY, CURDATE()),
(14, 1, 'WO-000014', 'preventive', 'schedule', 1,  12, 5,  2,    1,  4,
 'PM mingguan genset', 'high', 'scheduled', NOW() - INTERVAL 3 DAY, CURDATE()),
(15, 1, 'WO-000015', 'preventive', 'schedule', 19, 8,  15, NULL, 7,  4,
 'Checklist harian kolam renang & jacuzzi', 'normal', 'scheduled', NOW() - INTERVAL 6 HOUR, CURDATE()),
(16, 1, 'WO-000016', 'preventive', 'schedule', 16, 15, 22, NULL, 6,  4,
 'PM bulanan server room & backup', 'normal', 'scheduled', NOW() - INTERVAL 3 DAY, CURDATE()),
(30, 1, 'WO-000030', 'preventive', 'schedule', 21, 16, 30, NULL, 12, 4,
 'PM bulanan kendaraan operasional (Avanza)', 'normal', 'scheduled', NOW() - INTERVAL 3 DAY, CURDATE());

-- --- A6. PM terlewat (due kemarin, masih scheduled)
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 schedule_id, checklist_version_id, assignee_id, description, priority, status, created_at, due_date) VALUES
(17, 1, 'WO-000017', 'preventive', 'schedule', NULL, 6, 14, NULL, 8,  4,
 'Rute bulanan fire safety area publik', 'high', 'scheduled',
 NOW() - INTERVAL 4 DAY, DATE_SUB(CURDATE(), INTERVAL 1 DAY)),
(18, 1, 'WO-000018', 'preventive', 'schedule', 9, 11, 2, NULL, 10, 4,
 'PM mingguan cooling tower', 'normal', 'scheduled',
 NOW() - INTERVAL 4 DAY, DATE_SUB(CURDATE(), INTERVAL 1 DAY));

-- --- A7. WO vendor (servis bulanan lift, done + biaya vendor)
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 vendor_id, supervisor_id, description, priority, status, repair_note,
 created_at, due_date, started_at, done_at, cost_vendor) VALUES
(19, 1, 'WO-000019', 'vendor', 'manual', 3, 6, 11, 2, 3,
 'Servis bulanan lift tamu oleh vendor (kontrak)', 'normal', 'done',
 'Servis rutin vendor: pelumasan rel, kalibrasi pintu. Berita acara diterima, pendamping internal hadir',
 NOW() - INTERVAL 4 DAY, DATE_SUB(CURDATE(), INTERVAL 3 DAY),
 NOW() - INTERVAL 76 HOUR, NOW() - INTERVAL 74 HOUR, 2500000.00);

-- --- A8. WO skipped (PPM kamar occupied)
INSERT INTO work_orders
(id, property_id, wo_number, type, source, location_id, schedule_id, checklist_version_id,
 assignee_id, description, priority, status, skip_reason, created_at, due_date) VALUES
(20, 1, 'WO-000020', 'preventive', 'schedule', 1, 4, 3, 4,
 'PPM kamar 1201 (30 titik)', 'normal', 'skipped',
 'Kamar occupied - tamu long stay, reschedule minggu depan',
 NOW() - INTERVAL 3 DAY, DATE_SUB(CURDATE(), INTERVAL 2 DAY));

-- --- A9. Preventif selesai (riwayat, dengan checklist version & meter)
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 schedule_id, checklist_version_id, assignee_id, supervisor_id, description, priority, status, repair_note,
 created_at, due_date, started_at, done_at, verified_at, meter_at_service) VALUES
(21, 1, 'WO-000021', 'preventive', 'schedule', 1, 12, 5, 2, 1, 4, 3,
 'PM mingguan genset', 'high', 'verified',
 'Semua parameter normal, test run 12 menit OK, aki 12.7V',
 NOW() - INTERVAL 10 DAY, DATE_SUB(CURDATE(), INTERVAL 7 DAY),
 NOW() - INTERVAL 170 HOUR, NOW() - INTERVAL 168 HOUR, NOW() - INTERVAL 144 HOUR, 4805.00),
(22, 1, 'WO-000022', 'preventive', 'schedule', NULL, 13, 26, NULL, 4, 4, 3,
 'HACCP suhu harian (shift pagi)', 'normal', 'verified',
 'Semua suhu dalam ambang kecuali freezer 1 (ditindaklanjuti WO-000004)',
 NOW() - INTERVAL 30 HOUR, DATE_SUB(CURDATE(), INTERVAL 1 DAY),
 NOW() - INTERVAL 28 HOUR, NOW() - INTERVAL 26 HOUR, NOW() - INTERVAL 24 HOUR, NULL),
(23, 1, 'WO-000023', 'preventive', 'schedule', 15, 15, 23, NULL, 5, 4, NULL,
 'PM bulanan CCTV & access control', 'normal', 'done',
 'Semua kamera live, playback OK, retensi 32 hari, 3 kamar baterai lock < 20% dicatat',
 NOW() - INTERVAL 5 DAY, DATE_SUB(CURDATE(), INTERVAL 2 DAY),
 NOW() - INTERVAL 54 HOUR, NOW() - INTERVAL 52 HOUR, NULL, NULL),
(24, 1, 'WO-000024', 'preventive', 'schedule', 2, 12, 6, NULL, 11, 4, 3,
 'PM bulanan panel listrik & trafo', 'high', 'verified',
 'Beban fasa seimbang (ketimpangan 8%), suhu ruang 29C, tidak ada bau abnormal',
 NOW() - INTERVAL 16 DAY, DATE_SUB(CURDATE(), INTERVAL 14 DAY),
 NOW() - INTERVAL 338 HOUR, NOW() - INTERVAL 336 HOUR, NOW() - INTERVAL 312 HOUR, NULL);

-- --- A10. Terjadwal masa depan (mengisi kalender minggu ini)
INSERT INTO work_orders
(id, property_id, wo_number, type, source, asset_id, location_id, category_id,
 schedule_id, checklist_version_id, assignee_id, description, priority, status, created_at, due_date) VALUES
(27, 1, 'WO-000027', 'preventive', 'schedule', NULL, 2, NULL, 4, 3, 4,
 'PPM kamar 1202 (30 titik)', 'normal', 'scheduled',
 NOW() - INTERVAL 1 DAY, DATE_ADD(CURDATE(), INTERVAL 7 DAY)),
(28, 1, 'WO-000028', 'preventive', 'schedule', 10, 7, 3, NULL, NULL, 4,
 'PM bulanan AHU lantai 12', 'normal', 'scheduled',
 NOW() - INTERVAL 1 DAY, DATE_ADD(CURDATE(), INTERVAL 3 DAY));

-- =====================================================================
-- B. WO PARTS & WO COSTS
--    Roll-up cost_parts/cost_vendor/cost_other & labor_minutes sudah
--    diisi langsung di kolom ringkasan work_orders di atas.
--    WO-000025 total Rp 6.600.000 > Rp 5jt TANPA approved_by → QC approval.
-- =====================================================================

INSERT INTO wo_parts (wo_id, part_id, qty, unit_price_snapshot) VALUES
(1, 1, 2, 45000.00),    -- WO-1: 2x filter AC
(3, 3, 1, 120000.00),   -- WO-3: 1x lampu emergency LED
(7, 1, 1, 45000.00);    -- WO-7 waiting_parts: filter AC ditunggu

INSERT INTO wo_costs (wo_id, kind, description, qty, unit_price, amount, incurred_at, entered_by, invoice_file, approved_by, approved_at) VALUES
(1,  'part',     'Filter AC Split x2',                      2,    45000.00,   90000.00,   NOW() - INTERVAL 48 HOUR, 4, NULL, NULL, NULL),
(1,  'labor',    'Jasa teknisi 60 menit',                   NULL, NULL,       50000.00,   NOW() - INTERVAL 48 HOUR, 4, NULL, NULL, NULL),
(2,  'material', 'Sealant silikon (beli toko)',             1,    150000.00,  150000.00,  NOW() - INTERVAL 27 HOUR, 4, NULL, NULL, NULL),
(2,  'labor',    'Jasa teknisi 90 menit',                   NULL, NULL,       75000.00,   NOW() - INTERVAL 27 HOUR, 4, NULL, NULL, NULL),
(3,  'part',     'Lampu Emergency LED x1',                  1,    120000.00,  120000.00,  NOW() - INTERVAL 25 HOUR, 4, NULL, NULL, NULL),
(19, 'vendor',   'Servis bulanan lift - invoice vendor',    NULL, NULL,       2500000.00, NOW() - INTERVAL 72 HOUR, 3, NULL, 3, NOW() - INTERVAL 70 HOUR),
(25, 'vendor',   'Jasa perbaikan heat exchanger vendor',    NULL, NULL,       4000000.00, NOW() - INTERVAL 49 HOUR, 3, NULL, NULL, NULL),
(25, 'material', 'Gasket set & material pendukung',         1,    2600000.00, 2600000.00, NOW() - INTERVAL 49 HOUR, 3, NULL, NULL, NULL),
(29, 'material', 'Belt washer (beli toko)',                 1,    450000.00,  450000.00,  NOW() - INTERVAL 22 HOUR, 4, NULL, NULL, NULL),
(29, 'labor',    'Jasa teknisi 120 menit',                  NULL, NULL,       100000.00,  NOW() - INTERVAL 22 HOUR, 4, NULL, NULL, NULL);

-- =====================================================================
-- C. HISTORY (audit trail WO yang berproses — immutable)
-- =====================================================================

INSERT INTO history (wo_id, user_id, action, created_at) VALUES
-- WO-1 siklus penuh: reported -> assigned -> started -> done -> verified
(1,  5, 'reported',                                    NOW() - INTERVAL 3 DAY),
(1,  3, 'assigned to tech.candra',                     NOW() - INTERVAL 71 HOUR),
(1,  4, 'started',                                     NOW() - INTERVAL 50 HOUR),
(1,  4, 'done',                                        NOW() - INTERVAL 48 HOUR),
(1,  3, 'verified',                                    NOW() - INTERVAL 26 HOUR),
-- WO-5 sedang berjalan
(5,  5, 'reported',                                    NOW() - INTERVAL 4 HOUR),
(5,  3, 'assigned to tech.candra',                     NOW() - INTERVAL 3 HOUR),
(5,  4, 'started',                                     NOW() - INTERVAL 2 HOUR),
-- WO-10 urgent belum ditangani (SLA breach)
(10, 5, 'reported',                                    NOW() - INTERVAL 30 HOUR),
-- WO-11 urgent cold room + downtime
(11, 4, 'started (downtime aset critical dimulai)',    NOW() - INTERVAL 1 HOUR),
-- WO-19 vendor
(19, 3, 'created (WO vendor - kontrak lift)',          NOW() - INTERVAL 4 DAY),
(19, 4, 'done - berita acara vendor diterima',         NOW() - INTERVAL 74 HOUR),
-- WO-20 skipped
(20, 4, 'skipped: kamar occupied',                     NOW() - INTERVAL 2 DAY),
-- WO-25 biaya besar menunggu approval
(25, 3, 'created',                                     NOW() - INTERVAL 4 DAY),
(25, 4, 'done',                                        NOW() - INTERVAL 50 HOUR),
(25, 3, 'cost added: invoice vendor Rp4.000.000',      NOW() - INTERVAL 49 HOUR),
-- WO-26 ditolak duplikat
(26, 3, 'rejected: duplikat WO-000001',                NOW() - INTERVAL 70 HOUR);

-- =====================================================================
-- D. NOTIFICATIONS (badge lonceng: sebagian read_at NULL)
-- =====================================================================

INSERT INTO notifications (property_id, user_id, type, severity, title, body, link, read_at, created_at) VALUES
-- depthead.eng (user 3)
(1, 3, 'sla_breach',       'critical', 'WO urgent melewati SLA',
 'WO-000010 Lift Tamu 1 melewati due date dan belum ditugaskan', '/wo/10', NULL, NOW() - INTERVAL 2 HOUR),
(1, 3, 'pm_missed',        'warning',  'PM terlewat',
 'Fire safety rute bulanan (WO-000017) dan cooling tower (WO-000018) lewat jatuh tempo', '/wo/17', NULL, NOW() - INTERVAL 10 HOUR),
(1, 3, 'wo_pending_verification', 'info', 'WO menunggu verifikasi',
 'WO-000003, WO-000004, WO-000029 selesai kemarin menunggu verifikasi', '/wo?status=done', NOW() - INTERVAL 1 HOUR, NOW() - INTERVAL 20 HOUR),
(1, 3, 'contract_expiry',  'warning',  'Kontrak lift berakhir 45 hari lagi',
 'Kontrak Servis Lift Tahunan (PT Lift Nusantara) berakhir dalam 45 hari', '/contracts/1', NULL, NOW() - INTERVAL 1 DAY),
(1, 3, 'cert_expiry',      'warning',  'Sertifikat AK3 Candra segera habis',
 'Sertifikat AK3 listrik tech.candra habis dalam 45 hari - satu-satunya certified genset', '/users/4', NULL, NOW() - INTERVAL 1 DAY),
(1, 3, 'checklist_fail',   'critical', 'Checklist fail pada aset critical',
 'Suhu freezer kitchen di luar ambang HACCP (-12C), WO korektif otomatis dibuat', '/wo/4', NOW() - INTERVAL 25 HOUR, NOW() - INTERVAL 30 HOUR),
-- tech.candra (user 4)
(1, 4, 'wo_assigned',      'info',     'Tugas baru: PM mingguan genset',
 'WO-000014 ditugaskan kepada Anda, due hari ini', '/wo/14', NULL, NOW() - INTERVAL 8 HOUR),
(1, 4, 'pm_due_today',     'warning',  'PM due hari ini belum dimulai',
 '6 tugas PM jatuh tempo hari ini belum dimulai', '/wo?due=today', NULL, NOW() - INTERVAL 4 HOUR),
(1, 4, 'part_available',   'info',     'Part tersedia untuk WO-000007',
 'Filter AC Split tersedia di gudang, WO waiting_parts bisa dilanjutkan', '/wo/7', NOW() - INTERVAL 6 HOUR, NOW() - INTERVAL 1 DAY),
(1, 4, 'cert_expiry',      'warning',  'Sertifikat AK3 Anda segera habis',
 'Sertifikat AK3 listrik Anda habis dalam 45 hari, segera ajukan perpanjangan', '/profile', NULL, NOW() - INTERVAL 1 DAY),
-- hk.reporter (user 5)
(1, 5, 'report_resolved',  'info',     'Laporan Anda selesai: AC kamar 1201',
 'Laporan AC kamar 1201 telah diperbaiki dan diverifikasi', '/wo/1', NULL, NOW() - INTERVAL 26 HOUR),
(1, 5, 'report_resolved',  'info',     'Laporan Anda selesai: sealant kamar 0801',
 'Laporan sealant wastafel kamar 0801 telah diperbaiki dan diverifikasi', '/wo/2', NOW() - INTERVAL 19 HOUR, NOW() - INTERVAL 20 HOUR);

-- =====================================================================
-- E-F. METER READINGS (genset jam, mobil km) + update meter_current
-- =====================================================================

INSERT INTO meter_readings (asset_id, value, read_at, read_by, source) VALUES
-- Genset (asset 1, jam) — mingguan
(1, 4700.00,  NOW() - INTERVAL 35 DAY, 4, 'manual'),
(1, 4726.00,  NOW() - INTERVAL 28 DAY, 4, 'manual'),
(1, 4752.50,  NOW() - INTERVAL 21 DAY, 4, 'manual'),
(1, 4779.00,  NOW() - INTERVAL 14 DAY, 4, 'manual'),
(1, 4805.00,  NOW() - INTERVAL 7 DAY,  4, 'wo'),      -- dari WO-000021 (meter_at_service)
(1, 4830.50,  NOW() - INTERVAL 1 DAY,  4, 'manual'),
-- Mobil operasional (asset 21, km) — bulanan
(21, 41200.00, NOW() - INTERVAL 120 DAY, 4, 'manual'),
(21, 42600.00, NOW() - INTERVAL 90 DAY,  4, 'manual'),
(21, 44100.00, NOW() - INTERVAL 60 DAY,  4, 'manual'),
(21, 45500.00, NOW() - INTERVAL 30 DAY,  4, 'manual'),
(21, 46850.00, NOW() - INTERVAL 1 DAY,   4, 'manual');

UPDATE assets SET meter_current = 4830.50  WHERE id = 1;
UPDATE assets SET meter_current = 46850.00 WHERE id = 21;

-- =====================================================================
-- G. UTILITY METERS + READINGS (4 bulan riwayat, rumus konsisten:
--    consumption = reading_value - prev_reading
--    cost = consumption * tariff_snapshot + fixed_charge
--    Bulan terakhir: submeter lantai 1 melonjak +30% (cerita anomali).
-- =====================================================================

INSERT INTO utility_meters
(id, property_id, code, name, utility_type, unit, location_id, area_served, is_submeter,
 parent_meter_id, tariff_per_unit, fixed_charge, provider, account_no, active) VALUES
(1, 1, 'UTM-PLN-01',   'Meteran PLN Induk',            'electricity', 'kWh',   NULL, 'Seluruh gedung',            0, NULL, 1444.7000, 0.00,      'PLN',                    '541234567890', 1),
(2, 1, 'UTM-PLN-L1',   'Submeter Listrik Lantai 1',    'electricity', 'kWh',   6,    'Lantai 1 - Lobby & Resto',  1, 1,    1444.7000, 0.00,      'PLN (internal submeter)', NULL,           1),
(3, 1, 'UTM-PDAM-01',  'Meteran Air PDAM',             'water',       'm3',    NULL, 'Seluruh gedung',            0, NULL, 12550.0000, 150000.00, 'PDAM',                   'PD-88123456',  1),
(4, 1, 'UTM-SOLAR-01', 'Meter Solar Tangki Genset',    'fuel',        'liter', 12,   'Genset & boiler',           0, NULL, 6800.0000,  0.00,      'Pertamina Patra Niaga',  NULL,           1);

-- period_month = tanggal 1 bulan ybs (relatif NOW)
INSERT INTO utility_readings
(meter_id, period_month, reading_value, prev_reading, consumption, tariff_snapshot, cost, read_at, read_by, photo, note) VALUES
-- Meter 1: PLN induk (0 fixed charge)
(1, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m-01'), 1118000.00, 1000000.00, 118000.00, 1444.7000, 170474600.00, NOW() - INTERVAL 4 MONTH, 4, NULL, NULL),
(1, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m-01'), 1233500.00, 1118000.00, 115500.00, 1444.7000, 166862850.00, NOW() - INTERVAL 3 MONTH, 4, NULL, NULL),
(1, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01'), 1352300.00, 1233500.00, 118800.00, 1444.7000, 171630360.00, NOW() - INTERVAL 2 MONTH, 4, NULL, NULL),
(1, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01'), 1472100.00, 1352300.00, 119800.00, 1444.7000, 173075060.00, NOW() - INTERVAL 1 MONTH, 4, NULL, NULL),
-- Meter 2: submeter lantai 1 — bulan terakhir LONJAKAN ~+30% (anomali)
(2, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m-01'), 218500.00, 200000.00, 18500.00, 1444.7000, 26726950.00, NOW() - INTERVAL 4 MONTH, 4, NULL, NULL),
(2, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m-01'), 237200.00, 218500.00, 18700.00, 1444.7000, 27015890.00, NOW() - INTERVAL 3 MONTH, 4, NULL, NULL),
(2, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01'), 255600.00, 237200.00, 18400.00, 1444.7000, 26582480.00, NOW() - INTERVAL 2 MONTH, 4, NULL, NULL),
(2, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01'), 279600.00, 255600.00, 24000.00, 1444.7000, 34672800.00, NOW() - INTERVAL 1 MONTH, 4, NULL,
 'Lonjakan +30% vs rata-rata 3 bulan - dicurigai AHU lobby tidak efisien'),
-- Meter 3: PDAM (fixed charge 150.000)
(3, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m-01'), 53850.00, 50000.00, 3850.00, 12550.0000, 48467500.00, NOW() - INTERVAL 4 MONTH, 4, NULL, NULL),
(3, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m-01'), 57760.00, 53850.00, 3910.00, 12550.0000, 49220500.00, NOW() - INTERVAL 3 MONTH, 4, NULL, NULL),
(3, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01'), 61590.00, 57760.00, 3830.00, 12550.0000, 48216500.00, NOW() - INTERVAL 2 MONTH, 4, NULL, NULL),
(3, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01'), 65530.00, 61590.00, 3940.00, 12550.0000, 49597000.00, NOW() - INTERVAL 1 MONTH, 4, NULL, NULL),
-- Meter 4: solar (liter)
(4, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m-01'), 13150.00, 12000.00, 1150.00, 6800.0000, 7820000.00, NOW() - INTERVAL 4 MONTH, 4, NULL, NULL),
(4, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m-01'), 14280.00, 13150.00, 1130.00, 6800.0000, 7684000.00, NOW() - INTERVAL 3 MONTH, 4, NULL, NULL),
(4, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01'), 15420.00, 14280.00, 1140.00, 6800.0000, 7752000.00, NOW() - INTERVAL 2 MONTH, 4, NULL, NULL),
(4, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01'), 16600.00, 15420.00, 1180.00, 6800.0000, 8024000.00, NOW() - INTERVAL 1 MONTH, 4, NULL, NULL);

-- =====================================================================
-- I. BROADCASTS + RECEIPTS
-- =====================================================================

INSERT INTO broadcasts (id, property_id, sender_id, severity, title, body, target_departments, requires_ack, expires_at, created_at) VALUES
(1, 1, 3, 'critical', 'Lift 2 OOS',
 'Lift servis out of service karena perbaikan pintu, gunakan lift tamu untuk sementara. Estimasi normal besok pagi.',
 '["front_office","housekeeping"]', 1, NOW() + INTERVAL 1 DAY, NOW() - INTERVAL 2 HOUR),
(2, 1, 2, 'info', 'PLN padam terjadwal',
 'PLN padam terjadwal besok 09.00-11.00 untuk pemeliharaan gardu, genset akan running otomatis.',
 '"all"', 0, NOW() + INTERVAL 2 DAY, NOW() - INTERVAL 1 DAY);

INSERT INTO broadcast_receipts (broadcast_id, user_id, delivered_at, read_at, ack_at) VALUES
-- Broadcast 1 (critical wajib-ack): sebagian belum konfirmasi (bahan eskalasi)
(1, 5, NOW() - INTERVAL 118 MINUTE, NOW() - INTERVAL 100 MINUTE, NOW() - INTERVAL 95 MINUTE),  -- HK sudah ack
(1, 6, NOW() - INTERVAL 118 MINUTE, NOW() - INTERVAL 60 MINUTE,  NULL),                        -- dibaca, BELUM ack
-- Broadcast 2 (info "all"): campuran read
(2, 3, NOW() - INTERVAL 23 HOUR, NOW() - INTERVAL 22 HOUR, NULL),
(2, 4, NOW() - INTERVAL 23 HOUR, NOW() - INTERVAL 20 HOUR, NULL),
(2, 5, NOW() - INTERVAL 23 HOUR, NULL,                     NULL),   -- delivered, belum dibaca
(2, 6, NOW() - INTERVAL 23 HOUR, NOW() - INTERVAL 18 HOUR, NULL);

-- =====================================================================
-- END 0004_seed_demo.sql
-- =====================================================================
