-- ===================================================================== -- 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 -- ===================================================================== SET NAMES utf8mb4; -- --------------------------------------------------------------------- -- 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 -- =====================================================================