-- =====================================================================
-- 0018 — Out of Order (OO) level LOKASI (permintaan owner)
-- Konsep baru: sebelumnya hanya ada OOS per-ASET (assets.status). Owner
-- minta status OO/OOS juga bisa ditandai langsung di LOKASI (mis. kamar
-- direnovasi) — LocationDetailPage & LocationListPage butuh flag ini.
--
-- oo_status: 'ok' (normal, default) | 'ooo' (out of order) | 'oos'
--            (out of service — dipakai bila alasannya lebih ke arah
--            "tidak dijual sementara" drpd rusak berat, sesuai kebutuhan FO).
-- oo_reason: alasan wajib diisi saat set (validasi di Service/Page, bukan DB).
-- oo_since : kapan status OO mulai berlaku.
-- oo_set_by: user yang menandai (nullable — FK SET NULL bila user dihapus,
--            audit_logs tetap simpan jejak lengkap terlepas dari kolom ini).
--
-- Idempoten secukupnya: jalankan sekali setelah 0001 (tabel locations).
-- =====================================================================

ALTER TABLE locations
    ADD COLUMN oo_status ENUM('ok','ooo','oos') NOT NULL DEFAULT 'ok' AFTER active,
    ADD COLUMN oo_reason VARCHAR(255) NULL AFTER oo_status,
    ADD COLUMN oo_since  DATETIME NULL AFTER oo_reason,
    ADD COLUMN oo_set_by INT UNSIGNED NULL AFTER oo_since,
    ADD KEY idx_locations_oo_status (property_id, oo_status),
    ADD CONSTRAINT fk_locations_oo_set_by FOREIGN KEY (oo_set_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

-- ---------------------------------------------------------------------
-- Seed contoh demo (PM-PLAN — supaya demo terlihat): 2 kamar dummy
-- ditandai 'ooo' dengan alasan wajar. oo_set_by = 507 (I Nyoman Suamba,
-- dept_head engineering, lihat 0006_seed_real_users.sql).
-- ---------------------------------------------------------------------
UPDATE locations
   SET oo_status = 'ooo',
       oo_reason = 'Renovasi kamar mandi — DUMMY',
       oo_since  = NOW() - INTERVAL 3 DAY,
       oo_set_by = 507
 WHERE id IN (20, 21) AND property_id = 1 AND type = 'room';