-- =====================================================================
-- 0025_complaints.sql — master KOMPLAIN (object baru, object-creator
-- 9 Jul 2026, mandat owner "untuk mencatat semua complain yang masuk").
--
-- hr_complaints: eskalasi komplain TAMU (guest) + komplain INTERNAL antar
-- departemen. Kolom `department` = VARCHAR code (POLA SAMA dgn
-- hr_employees.department/hr_positions.department — TIDAK direfaktor jadi
-- FK di migrasi ini, selaras keputusan 0022_departments; objectLink
-- 'department' via LEFT JOIN hr_departments d ON d.code = hc.department,
-- persis pola HrEmployee::listForProperty).
--
-- Seed 3 baris DUMMY berlabel ("Demo Guest ...") supaya halaman langsung
-- hidup (anti-karang: BUKAN tamu/insiden nyata). reported_by = user id 1
-- (Super Admin, diverifikasi ada di DB live sanggraloka_one, aman FK).
-- =====================================================================

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

CREATE TABLE hr_complaints (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    source          ENUM('guest','internal') NOT NULL DEFAULT 'guest',
    channel         ENUM('langsung','telepon','whatsapp','email','review_online','ota','lainnya') NOT NULL DEFAULT 'langsung',
    guest_name      VARCHAR(150) NULL COMMENT 'nama tamu; NULL utk komplain internal',
    room            VARCHAR(20)  NULL,
    department      VARCHAR(60)  NULL COMMENT 'dept tertuju, match kode hr_departments.code',
    category        VARCHAR(100) NULL,
    severity        ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
    description     TEXT NOT NULL,
    status          ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
    resolution      TEXT NULL,
    reported_by     INT UNSIGNED NOT NULL,
    resolved_by     INT UNSIGNED NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at     DATETIME NULL,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_complaints_property_status (property_id, status),
    KEY idx_hr_complaints_property_department (property_id, department),
    CONSTRAINT fk_hr_complaints_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_complaints_reported_by FOREIGN KEY (reported_by) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_hr_complaints_resolved_by FOREIGN KEY (resolved_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed DUMMY (property_id=1, satu-satunya properti live) — 2 open beda
-- departemen (housekeeping severity medium, front_office severity high) +
-- 1 resolved dgn resolusi (kitchen).
INSERT INTO hr_complaints
    (property_id, source, channel, guest_name, room, department, category, severity, description, status, resolution, reported_by, resolved_by, resolved_at) VALUES
    (1, 'guest', 'langsung', 'Demo Guest Satu', '204', 'housekeeping', 'kebersihan kamar', 'medium',
        'DUMMY — Tamu melapor kamar belum dibersihkan saat check-in siang.', 'open', NULL, 1, NULL, NULL),
    (1, 'guest', 'whatsapp', 'Demo Guest Dua', '112', 'front_office', 'proses check-in lambat', 'high',
        'DUMMY — Tamu menunggu lebih dari 30 menit saat check-in, sempat komplain via WhatsApp resort.', 'open', NULL, 1, NULL, NULL),
    (1, 'guest', 'review_online', 'Demo Guest Tiga', NULL, 'kitchen', 'rasa makanan', 'low',
        'DUMMY — Ulasan online menyebut menu sarapan kurang variatif.', 'resolved',
        'DUMMY — Chef menambah 2 menu rotasi sarapan mingguan, follow-up ke tamu via email.', 1, 1, NOW());