-- =====================================================================
-- SANGGRALOKA ONE — Migration 0008_hr_init
-- Modul CREW (HotelCrew / HRIS + Hotel University + Payroll + Ops).
-- Semua tabel prefix hr_ + audit_logs (append-only, kedua modul) +
-- auth_stepup (zona merah payroll) + ALTER users.
-- Target: MySQL 8 — ENGINE=InnoDB, utf8mb4_unicode_ci.
-- Acuan: hotelcrew/docs/SPEK-HOTELCREW-MASTER.md §4 (kontrak nama kolom),
--        sanggraloka-one/docs/PM-PLAN-SANGGRALOKA-ONE.md §4.
-- Jalankan setelah 0001..0007. Referensi ke users/user_property_access/
-- notifications/skills/asset_categories dari 0001_init.sql.
-- =====================================================================

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

-- =====================================================================
-- 1. MASTER KARYAWAN & DOKUMEN (F1)
-- =====================================================================

-- ---------------------------------------------------------------------
-- hr_positions — dibuat sebelum hr_employees (FK position_id)
-- ---------------------------------------------------------------------
CREATE TABLE hr_positions (
    id                      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id             INT UNSIGNED NOT NULL,
    name                    VARCHAR(150) NOT NULL,
    department              VARCHAR(60)  NOT NULL,
    level                   ENUM('staff','coordinator','hod','manager','director') NOT NULL DEFAULT 'staff',
    jobdesc_code            VARCHAR(30)  NULL COMMENT 'kode jobdesk mis. HK-RA-01 (dokumen versi di hotelcrew/docs/jobdesk)',
    jobdesc_version         INT UNSIGNED NULL,
    min_wage_grade          VARCHAR(30)  NULL,
    active                  TINYINT(1)   NOT NULL DEFAULT 1,
    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_hr_positions_property_dept (property_id, department),
    CONSTRAINT fk_hr_positions_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_employees
-- Kolom nominal/rekening sensitif (bank_account) = TEXT ciphertext,
-- dienkripsi level aplikasi (§3.2 SPEK-HOTELCREW zona merah).
-- ---------------------------------------------------------------------
CREATE TABLE hr_employees (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    user_id             INT UNSIGNED NULL,
    nik                 VARCHAR(20)  NULL COMMENT 'nomor induk karyawan internal',
    full_name           VARCHAR(150) NOT NULL,
    photo               VARCHAR(255) NULL,
    ktp_no              VARCHAR(20)  NULL,
    npwp                VARCHAR(25)  NULL,
    bpjs_kes_no         VARCHAR(30)  NULL,
    bpjs_tk_no          VARCHAR(30)  NULL,
    ptkp_status         VARCHAR(10)  NULL COMMENT 'TK/0, K/1, dst',
    address             VARCHAR(255) NULL,
    domicile            VARCHAR(150) NULL,
    banjar              VARCHAR(100) NULL,
    religion            VARCHAR(30)  NULL,
    birth_date          DATE NULL,
    marital             ENUM('single','married','divorced','widowed') NULL,
    emergency_contact   VARCHAR(150) NULL,
    department          VARCHAR(60)  NOT NULL,
    division            VARCHAR(60)  NULL,
    position_id         INT UNSIGNED NULL,
    level               VARCHAR(30)  NULL,
    employment_status   ENUM('dw','contract','permanent','casual','trainee') NOT NULL DEFAULT 'contract',
    join_date           DATE NULL,
    contract_end        DATE NULL,
    supervisor_id       INT UNSIGNED NULL,
    bank_account        TEXT NULL COMMENT 'ciphertext — nomor rekening & bank, enkripsi level aplikasi',
    salary_grade        VARCHAR(30)  NULL,
    sc_point            DECIMAL(10,2) NULL COMMENT 'poin service charge',
    uniform_size        VARCHAR(10)  NULL,
    locker_no           VARCHAR(20)  NULL,
    active              TINYINT(1)   NOT NULL DEFAULT 1,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_employees_property_nik (property_id, nik),
    KEY idx_hr_employees_property_dept (property_id, department),
    KEY idx_hr_employees_user (user_id),
    KEY idx_hr_employees_position (position_id),
    KEY idx_hr_employees_supervisor (supervisor_id),
    CONSTRAINT fk_hr_employees_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_employees_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_employees_position FOREIGN KEY (position_id) REFERENCES hr_positions(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_employees_supervisor FOREIGN KEY (supervisor_id) REFERENCES hr_employees(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK susulan: hr_positions tidak mereferensikan hr_competencies di sini
-- (required_competencies disimpan sbg JSON kode kompetensi, bukan FK,
-- karena banyak-ke-banyak dgn level per kompetensi — lihat hr_employee_comp).
ALTER TABLE hr_positions
    ADD COLUMN required_competencies JSON NULL COMMENT 'array kode kompetensi wajib mis. ["HK-01","HK-18"]' AFTER jobdesc_version;

-- ---------------------------------------------------------------------
-- hr_documents
-- ---------------------------------------------------------------------
CREATE TABLE hr_documents (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    employee_id     INT UNSIGNED NOT NULL,
    type            ENUM('ktp','ijazah','sertifikat','kontrak','mcu','kitas','lainnya') NOT NULL,
    title           VARCHAR(150) NOT NULL,
    file            VARCHAR(255) NOT NULL,
    expiry_date     DATE NULL,
    reminder_days   SMALLINT UNSIGNED NULL,
    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_hr_documents_employee (employee_id),
    KEY idx_hr_documents_expiry (expiry_date),
    CONSTRAINT fk_hr_documents_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_contracts
-- ---------------------------------------------------------------------
CREATE TABLE hr_contracts (
    id                      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    employee_id             INT UNSIGNED NOT NULL,
    type                    ENUM('pkwt','pkwtt') NOT NULL,
    start_date              DATE NOT NULL,
    end_date                DATE NULL,
    file                    VARCHAR(255) NULL,
    compensation_paid       DECIMAL(14,2) NULL COMMENT 'kompensasi PKWT (1 bulan upah / 12 bln masa kerja, prorata)',
    status                  ENUM('draft','active','expired','terminated') NOT NULL DEFAULT 'draft',
    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_hr_contracts_employee (employee_id),
    KEY idx_hr_contracts_end_date (end_date),
    CONSTRAINT fk_hr_contracts_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 2. ABSENSI, SHIFT, ROSTER, CUTI (F2, F3, F4)
-- =====================================================================

-- ---------------------------------------------------------------------
-- hr_attendance_logs — immutable (mesin/mobile-gps)
-- ---------------------------------------------------------------------
CREATE TABLE hr_attendance_logs (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    employee_id     INT UNSIGNED NOT NULL,
    machine_id      VARCHAR(60)  NULL,
    ts              DATETIME NOT NULL,
    source          ENUM('machine','mobile_gps') NOT NULL DEFAULT 'machine',
    photo           VARCHAR(255) NULL,
    lat             DECIMAL(10,7) NULL,
    lng             DECIMAL(10,7) NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_attendance_logs_property_employee_ts (property_id, employee_id, ts),
    CONSTRAINT fk_hr_attendance_logs_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_attendance_logs_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_shifts
-- ---------------------------------------------------------------------
CREATE TABLE hr_shifts (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    department      VARCHAR(60)  NOT NULL,
    code            ENUM('M','A','N','M2','OFF','PH') NOT NULL,
    start_time      TIME NULL,
    end_time        TIME NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_shifts_property_dept_code (property_id, department, code),
    KEY idx_hr_shifts_property_dept (property_id, department),
    CONSTRAINT fk_hr_shifts_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_attendance_days
-- ---------------------------------------------------------------------
CREATE TABLE hr_attendance_days (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    employee_id     INT UNSIGNED NOT NULL,
    date            DATE NOT NULL,
    shift_code      ENUM('M','A','N','M2','OFF','PH') NULL,
    time_in         DATETIME NULL,
    time_out        DATETIME NULL,
    late_min        INT UNSIGNED NOT NULL DEFAULT 0,
    early_min       INT UNSIGNED NOT NULL DEFAULT 0,
    overtime_min    INT UNSIGNED NULL COMMENT 'approved',
    status          ENUM('present','late','alpha','leave','off','holiday','incomplete') NOT NULL DEFAULT 'present',
    dispute_status  ENUM('none','submitted','approved','rejected') NOT NULL DEFAULT 'none',
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_attendance_days_employee_date (employee_id, date),
    KEY idx_hr_attendance_days_property_date (property_id, date),
    CONSTRAINT fk_hr_attendance_days_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_attendance_days_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_roster
-- ---------------------------------------------------------------------
CREATE TABLE hr_roster (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    employee_id     INT UNSIGNED NOT NULL,
    date            DATE NOT NULL,
    shift_id        INT UNSIGNED NOT NULL,
    published_at    DATETIME NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_roster_employee_date (employee_id, date),
    KEY idx_hr_roster_property_date (property_id, date),
    KEY idx_hr_roster_shift (shift_id),
    CONSTRAINT fk_hr_roster_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_roster_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_roster_shift FOREIGN KEY (shift_id) REFERENCES hr_shifts(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_leave_types
-- ---------------------------------------------------------------------
CREATE TABLE hr_leave_types (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    name            VARCHAR(100) NOT NULL,
    quota           SMALLINT UNSIGNED NULL COMMENT 'hari/tahun, NULL = tanpa kuota tetap',
    requires_doc    TINYINT(1)   NOT NULL DEFAULT 0,
    requires_gm     TINYINT(1)   NOT NULL DEFAULT 0,
    paid            TINYINT(1)   NOT NULL DEFAULT 1,
    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_hr_leave_types_property (property_id),
    CONSTRAINT fk_hr_leave_types_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_leaves
-- ---------------------------------------------------------------------
CREATE TABLE hr_leaves (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    employee_id     INT UNSIGNED NOT NULL,
    type_id         INT UNSIGNED NOT NULL,
    from_date       DATE NOT NULL,
    to_date         DATE NOT NULL,
    reason          VARCHAR(255) NULL,
    doc_file        VARCHAR(255) NULL,
    status          ENUM('draft','submitted','approved_spv','approved_hod','approved_hr','approved_gm','rejected','cancelled') NOT NULL DEFAULT 'draft',
    approvals       JSON NULL COMMENT '{spv:{by,at}, hod:{...}, hr:{...}, gm:{...}}',
    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_hr_leaves_property_status (property_id, status),
    KEY idx_hr_leaves_employee (employee_id),
    KEY idx_hr_leaves_type (type_id),
    CONSTRAINT fk_hr_leaves_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_leaves_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_leaves_type FOREIGN KEY (type_id) REFERENCES hr_leave_types(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_shift_swaps
-- ---------------------------------------------------------------------
CREATE TABLE hr_shift_swaps (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    requester_id    INT UNSIGNED NOT NULL,
    partner_id      INT UNSIGNED NOT NULL,
    date            DATE NOT NULL,
    status          ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
    approvals       JSON NULL,
    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_hr_shift_swaps_property_date (property_id, date),
    KEY idx_hr_shift_swaps_requester (requester_id),
    KEY idx_hr_shift_swaps_partner (partner_id),
    CONSTRAINT fk_hr_shift_swaps_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_shift_swaps_requester FOREIGN KEY (requester_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_shift_swaps_partner FOREIGN KEY (partner_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 3. HOTEL UNIVERSITY & KOMPETENSI (F5, F6, F7)
-- =====================================================================

-- ---------------------------------------------------------------------
-- hr_faculties
-- ---------------------------------------------------------------------
CREATE TABLE hr_faculties (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    code            VARCHAR(10)  NOT NULL COMMENT 'GEN, FO, HK, FBS, BAR, KIT, ENG, SPA, WEL, SEC, GRD, SLS, ADM, LDR',
    name            VARCHAR(150) NOT NULL,
    head_user_id    INT UNSIGNED NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_faculties_property_code (property_id, code),
    KEY idx_hr_faculties_head (head_user_id),
    CONSTRAINT fk_hr_faculties_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_faculties_head FOREIGN KEY (head_user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_competencies
-- ---------------------------------------------------------------------
CREATE TABLE hr_competencies (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    code                VARCHAR(20)  NOT NULL COMMENT 'kode {PREFIX}-{nn} mis. HK-01, GEN-18',
    name                VARCHAR(150) NOT NULL,
    tier                ENUM('core','functional','leadership') NOT NULL DEFAULT 'functional',
    faculty_id          INT UNSIGNED NOT NULL,
    asset_category_id   INT UNSIGNED NULL COMMENT 'NULL -> tidak sync ke skills FIX',
    max_level           TINYINT UNSIGNED NOT NULL DEFAULT 4 COMMENT '1 Tahu, 2 Bisa dgn supervisi, 3 Mandiri, 4 Trainer',
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_competencies_property_code (property_id, code),
    KEY idx_hr_competencies_faculty (faculty_id),
    KEY idx_hr_competencies_asset_category (asset_category_id),
    CONSTRAINT fk_hr_competencies_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_competencies_faculty FOREIGN KEY (faculty_id) REFERENCES hr_faculties(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_hr_competencies_asset_category FOREIGN KEY (asset_category_id) REFERENCES asset_categories(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_employee_comp
-- ---------------------------------------------------------------------
CREATE TABLE hr_employee_comp (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    employee_id     INT UNSIGNED NOT NULL,
    competency_id   INT UNSIGNED NOT NULL,
    level           TINYINT UNSIGNED NOT NULL DEFAULT 1,
    assessed_by     INT UNSIGNED NULL,
    assessed_at     DATETIME NULL,
    evidence_type   ENUM('test','practical','cert') NULL,
    evidence_ref_id BIGINT UNSIGNED NULL COMMENT 'test_id atau dokumen sertifikat',
    valid_until     DATE NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_employee_comp (employee_id, competency_id),
    KEY idx_hr_employee_comp_competency (competency_id),
    KEY idx_hr_employee_comp_assessed_by (assessed_by),
    CONSTRAINT fk_hr_employee_comp_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_employee_comp_competency FOREIGN KEY (competency_id) REFERENCES hr_competencies(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_employee_comp_assessed_by FOREIGN KEY (assessed_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_courses
-- ---------------------------------------------------------------------
CREATE TABLE hr_courses (
    id                      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id             INT UNSIGNED NOT NULL,
    faculty_id              INT UNSIGNED NOT NULL,
    competency_id           INT UNSIGNED NULL,
    title                   VARCHAR(150) NOT NULL,
    week_of                 DATE NULL,
    deadline                DATE NULL,
    materials               JSON NULL COMMENT 'array {type: video|article|card, url/ref}',
    mandatory_departments   JSON NULL COMMENT 'array nama departemen wajib, NULL = semua',
    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_hr_courses_property_faculty (property_id, faculty_id),
    KEY idx_hr_courses_competency (competency_id),
    CONSTRAINT fk_hr_courses_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_courses_faculty FOREIGN KEY (faculty_id) REFERENCES hr_faculties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_courses_competency FOREIGN KEY (competency_id) REFERENCES hr_competencies(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_tests
-- ---------------------------------------------------------------------
CREATE TABLE hr_tests (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    course_id           INT UNSIGNED NULL,
    competency_id       INT UNSIGNED NULL,
    title               VARCHAR(150) NOT NULL,
    question_bank       JSON NOT NULL COMMENT 'array soal {q, options[], answer, ...}',
    pass_grade          TINYINT UNSIGNED NOT NULL DEFAULT 85,
    time_limit_minutes  SMALLINT UNSIGNED NULL,
    max_attempts        TINYINT UNSIGNED NOT NULL DEFAULT 3,
    type                ENUM('course','entrance','certification','placement') NOT NULL DEFAULT 'course',
    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_hr_tests_property (property_id),
    KEY idx_hr_tests_course (course_id),
    KEY idx_hr_tests_competency (competency_id),
    CONSTRAINT fk_hr_tests_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_tests_course FOREIGN KEY (course_id) REFERENCES hr_courses(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_tests_competency FOREIGN KEY (competency_id) REFERENCES hr_competencies(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_test_attempts — user_id/candidate_id: pakai users.id; kandidat tanpa
-- akun direferensikan via hr_candidates.id di kolom candidate_id terpisah.
-- ---------------------------------------------------------------------
CREATE TABLE hr_test_attempts (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_id         INT UNSIGNED NOT NULL,
    user_id         INT UNSIGNED NULL,
    candidate_id    INT UNSIGNED NULL COMMENT '-> hr_candidates(id) bila test kandidat (FK ditambah setelah hr_candidates dibuat)',
    score           DECIMAL(5,2) NULL,
    answers         JSON NULL,
    passed          TINYINT(1)   NULL,
    ts              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_test_attempts_test (test_id),
    KEY idx_hr_test_attempts_user (user_id),
    KEY idx_hr_test_attempts_candidate (candidate_id),
    CONSTRAINT fk_hr_test_attempts_test FOREIGN KEY (test_id) REFERENCES hr_tests(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_test_attempts_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 4. OPS HARIAN: MOD, GUEST CARDS, BRIEFING, TASK, AI INTAKE (F15, F16)
-- =====================================================================

-- ---------------------------------------------------------------------
-- hr_mod_roster
-- ---------------------------------------------------------------------
CREATE TABLE hr_mod_roster (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    date            DATE NOT NULL,
    shift           ENUM('day','night') NOT NULL,
    user_id         INT UNSIGNED NOT NULL,
    set_by          INT UNSIGNED NULL COMMENT 'HRD',
    published_at    DATETIME NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_mod_roster_property_date_shift (property_id, date, shift),
    KEY idx_hr_mod_roster_user (user_id),
    CONSTRAINT fk_hr_mod_roster_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_mod_roster_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_mod_roster_set_by FOREIGN KEY (set_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_guest_imports
-- ---------------------------------------------------------------------
CREATE TABLE hr_guest_imports (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    date            DATE NOT NULL,
    file            VARCHAR(255) NOT NULL,
    imported_by     INT UNSIGNED NULL,
    rows_arrival    INT UNSIGNED NOT NULL DEFAULT 0,
    rows_inhouse    INT UNSIGNED NOT NULL DEFAULT 0,
    rows_departure  INT UNSIGNED NOT NULL DEFAULT 0,
    parsed_at       DATETIME NULL,
    ai_cost_idr     DECIMAL(12,2) NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_guest_imports_property_date (property_id, date),
    CONSTRAINT fk_hr_guest_imports_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_guest_imports_imported_by FOREIGN KEY (imported_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_guest_cards
-- ---------------------------------------------------------------------
CREATE TABLE hr_guest_cards (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    import_id       INT UNSIGNED NOT NULL,
    date            DATE NOT NULL,
    section         ENUM('arrival','inhouse','departure') NOT NULL,
    room            VARCHAR(20)  NULL,
    room_type       VARCHAR(80)  NULL,
    guest_name      VARCHAR(160) NOT NULL,
    pax             SMALLINT UNSIGNED NULL,
    ci              DATE NULL,
    co              DATE NULL,
    vip             ENUM('none','vip1','vip2','vip3') NOT NULL DEFAULT 'none',
    source          VARCHAR(60)  NULL,
    language_hint   VARCHAR(10)  NULL COMMENT 'FR/KR/EN/ID dst',
    occasion        VARCHAR(100) NULL,
    family_rooms    JSON NULL COMMENT 'array nomor kamar 1 keluarga',
    expires_at      DATETIME NULL,
    attributes      JSON NULL COMMENT 'array {text, type(dietary|spa|bed|transfer|occasion|security|inclusion|care), depts:[...]}',
    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_hr_guest_cards_property_date_section (property_id, date, section),
    KEY idx_hr_guest_cards_import (import_id),
    CONSTRAINT fk_hr_guest_cards_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_guest_cards_import FOREIGN KEY (import_id) REFERENCES hr_guest_imports(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_briefings
-- ---------------------------------------------------------------------
CREATE TABLE hr_briefings (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    date            DATE NOT NULL,
    scope           ENUM('resort','department') NOT NULL DEFAULT 'resort',
    department      VARCHAR(60)  NULL COMMENT 'wajib bila scope=department',
    sections        JSON NULL COMMENT 'blok konten (guest, maintenance F16, dst)',
    generated_at    DATETIME NULL,
    edited_by       INT UNSIGNED NULL,
    published_at    DATETIME NULL,
    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_hr_briefings_property_date (property_id, date),
    KEY idx_hr_briefings_edited_by (edited_by),
    CONSTRAINT fk_hr_briefings_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_briefings_edited_by FOREIGN KEY (edited_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_briefing_acks
-- ---------------------------------------------------------------------
CREATE TABLE hr_briefing_acks (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    briefing_id     INT UNSIGNED NOT NULL,
    user_id         INT UNSIGNED NOT NULL,
    ack_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_briefing_acks (briefing_id, user_id),
    KEY idx_hr_briefing_acks_user (user_id),
    CONSTRAINT fk_hr_briefing_acks_briefing FOREIGN KEY (briefing_id) REFERENCES hr_briefings(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_briefing_acks_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_briefing_items
-- ---------------------------------------------------------------------
CREATE TABLE hr_briefing_items (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    briefing_id             INT UNSIGNED NOT NULL,
    text                    VARCHAR(500) NOT NULL,
    ai_suggested_class      ENUM('task','kpi_proposal','hotelfix_wo','info') NULL,
    confirmed_class         ENUM('task','kpi_proposal','hotelfix_wo','info') NULL,
    confirmed_by            INT UNSIGNED NULL,
    ref_id                  BIGINT UNSIGNED NULL COMMENT 'id baris hasil (hr_tasks.id / work_orders.id / hr_kpi_definitions.id)',
    created_at              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_briefing_items_briefing (briefing_id),
    KEY idx_hr_briefing_items_confirmed_by (confirmed_by),
    CONSTRAINT fk_hr_briefing_items_briefing FOREIGN KEY (briefing_id) REFERENCES hr_briefings(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_briefing_items_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_tasks — papan task terpadu Sanggraloka One (kontrak PM-PLAN §4):
-- source_app/source_ref WAJIB untuk label sumber FIX/CREW/SELL di tasks.php.
-- ---------------------------------------------------------------------
CREATE TABLE hr_tasks (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    source              ENUM('briefing','intake','manual') NOT NULL DEFAULT 'manual',
    source_app          ENUM('crew','sell') NOT NULL DEFAULT 'crew' COMMENT 'kontrak papan task terpadu PM-PLAN §4',
    source_ref          VARCHAR(100) NULL COMMENT 'referensi bebas ke record asal, mis. mkt_quotations:123',
    title               VARCHAR(200) NOT NULL,
    description         TEXT NULL,
    department          VARCHAR(60)  NULL,
    assignee_id         INT UNSIGNED NULL,
    due_date            DATE NULL,
    priority            ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    status              ENUM('open','in_progress','done','overdue','cancelled') NOT NULL DEFAULT 'open',
    progress_notes      JSON NULL,
    hotelfix_wo_id      BIGINT UNSIGNED NULL,
    verified_by         INT UNSIGNED NULL,
    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_hr_tasks_property_status_due (property_id, status, due_date),
    KEY idx_hr_tasks_property_source_app (property_id, source_app),
    KEY idx_hr_tasks_assignee (assignee_id),
    KEY idx_hr_tasks_hotelfix_wo (hotelfix_wo_id),
    CONSTRAINT fk_hr_tasks_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_tasks_assignee FOREIGN KEY (assignee_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_tasks_hotelfix_wo FOREIGN KEY (hotelfix_wo_id) REFERENCES work_orders(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_tasks_verified_by FOREIGN KEY (verified_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK susulan hr_briefing_items.ref_id tidak diberi FK eksplisit (polimorfik
-- lintas 3 tabel: hr_tasks/work_orders/hr_kpi_definitions) — konsisten pola
-- kolom polimorfik lain di skema ini (mis. hr_test_attempts.candidate_id).

-- ---------------------------------------------------------------------
-- hr_ai_intakes
-- ---------------------------------------------------------------------
CREATE TABLE hr_ai_intakes (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    raw_text            TEXT NOT NULL,
    source_type         ENUM('guest_request','event','note') NOT NULL DEFAULT 'note',
    parsed              JSON NULL,
    model               VARCHAR(60)  NULL,
    tokens_in           INT UNSIGNED NULL,
    tokens_out          INT UNSIGNED NULL,
    cost_idr            DECIMAL(12,2) NULL,
    confirmed_by        INT UNSIGNED NULL,
    created_task_ids    JSON NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_ai_intakes_property (property_id),
    KEY idx_hr_ai_intakes_confirmed_by (confirmed_by),
    CONSTRAINT fk_hr_ai_intakes_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_ai_intakes_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 5. CHALLENGE, KPI, INSPEKSI, DISIPLIN (F8, F9)
-- =====================================================================

-- ---------------------------------------------------------------------
-- hr_challenges
-- ---------------------------------------------------------------------
CREATE TABLE hr_challenges (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    name                VARCHAR(150) NOT NULL,
    department          VARCHAR(60)  NULL,
    type                ENUM('judged','timed','data_driven') NOT NULL,
    metric              VARCHAR(150) NULL,
    rules               TEXT NULL,
    schedule            VARCHAR(150) NULL,
    prize               VARCHAR(150) NULL,
    budget_source       VARCHAR(100) NULL,
    competency_id       INT UNSIGNED NULL,
    status              ENUM('draft','open','running','closed','cancelled') NOT NULL DEFAULT 'draft',
    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_hr_challenges_property_status (property_id, status),
    KEY idx_hr_challenges_competency (competency_id),
    CONSTRAINT fk_hr_challenges_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_challenges_competency FOREIGN KEY (competency_id) REFERENCES hr_competencies(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_challenge_results — participant_id/team: employee tunggal via
-- participant_id; tim disimpan sbg nama bebas di kolom team.
-- ---------------------------------------------------------------------
CREATE TABLE hr_challenge_results (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    challenge_id    INT UNSIGNED NOT NULL,
    participant_id  INT UNSIGNED NULL,
    team            VARCHAR(100) NULL,
    score           DECIMAL(10,2) NULL,
    time_seconds    INT UNSIGNED NULL,
    rank            SMALLINT UNSIGNED NULL,
    judged_by       INT UNSIGNED NULL,
    evidence        JSON NULL COMMENT 'array url foto/video',
    awarded         JSON NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_challenge_results_challenge (challenge_id),
    KEY idx_hr_challenge_results_participant (participant_id),
    CONSTRAINT fk_hr_challenge_results_challenge FOREIGN KEY (challenge_id) REFERENCES hr_challenges(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_challenge_results_participant FOREIGN KEY (participant_id) REFERENCES hr_employees(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_challenge_results_judged_by FOREIGN KEY (judged_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_kpi_definitions
-- ---------------------------------------------------------------------
CREATE TABLE hr_kpi_definitions (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    department      VARCHAR(60)  NOT NULL,
    position_id     INT UNSIGNED NULL COMMENT 'NULL = indikator level departemen',
    name            VARCHAR(200) NOT NULL,
    target          VARCHAR(100) NOT NULL COMMENT 'nilai target sbg teks (mis. >=95%, <=2/bulan)',
    unit            VARCHAR(30)  NULL,
    weight          DECIMAL(5,2) NOT NULL COMMENT 'bobot %',
    source          ENUM('attendance','training','discipline','checklist','hotelfix_wo','manual','inspection') NOT NULL,
    active          TINYINT(1)   NOT NULL DEFAULT 1,
    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_hr_kpi_definitions_property_dept (property_id, department),
    KEY idx_hr_kpi_definitions_position (position_id),
    CONSTRAINT fk_hr_kpi_definitions_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_kpi_definitions_position FOREIGN KEY (position_id) REFERENCES hr_positions(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_kpi_scores — employee_id/department: skor individu via employee_id,
-- skor departemen via department (employee_id NULL).
-- ---------------------------------------------------------------------
CREATE TABLE hr_kpi_scores (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    employee_id         INT UNSIGNED NULL,
    department          VARCHAR(60)  NULL COMMENT 'diisi bila skor level departemen (employee_id NULL)',
    period              CHAR(7) NOT NULL COMMENT 'YYYY-MM',
    component_scores    JSON NOT NULL,
    total               DECIMAL(5,2) NULL,
    published_at        DATETIME NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_kpi_scores_property_period (property_id, period),
    KEY idx_hr_kpi_scores_employee (employee_id, period),
    CONSTRAINT fk_hr_kpi_scores_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_kpi_scores_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_inspections
-- ---------------------------------------------------------------------
CREATE TABLE hr_inspections (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    department      VARCHAR(60)  NOT NULL,
    template        JSON NOT NULL COMMENT 'checklist titik inspeksi',
    scored_by       INT UNSIGNED NULL,
    employee_id     INT UNSIGNED NULL COMMENT 'yang diinspeksi (mis. RA pemilik kamar)',
    location_id     INT UNSIGNED NULL,
    score           DECIMAL(5,2) NULL,
    photos          JSON NULL,
    ts              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_inspections_property_dept_ts (property_id, department, ts),
    KEY idx_hr_inspections_employee (employee_id),
    KEY idx_hr_inspections_location (location_id),
    CONSTRAINT fk_hr_inspections_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_inspections_scored_by FOREIGN KEY (scored_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_inspections_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_inspections_location FOREIGN KEY (location_id) REFERENCES locations(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_discipline
-- ---------------------------------------------------------------------
CREATE TABLE hr_discipline (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    employee_id         INT UNSIGNED NOT NULL,
    level               ENUM('coaching','sp1','sp2','sp3') NOT NULL,
    category            VARCHAR(100) NOT NULL,
    chronology          TEXT NULL,
    evidence            JSON NULL,
    issued_by           INT UNSIGNED NOT NULL,
    valid_until         DATE NULL,
    ack_at              DATETIME NULL,
    ack_signature_hash  VARCHAR(255) NULL,
    objection_note      TEXT NULL,
    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_hr_discipline_property_employee (property_id, employee_id),
    KEY idx_hr_discipline_issued_by (issued_by),
    CONSTRAINT fk_hr_discipline_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_discipline_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_discipline_issued_by FOREIGN KEY (issued_by) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 6. PAYROLL, SERVICE CHARGE, KASBON — ZONA MERAH (F10)
-- Kolom nominal (earnings/deductions/gross/net di hr_payroll_items) = TEXT
-- ciphertext, dienkripsi level aplikasi (kunci TIDAK disimpan di DB, §3.2).
-- =====================================================================

-- ---------------------------------------------------------------------
-- hr_payroll_periods
-- ---------------------------------------------------------------------
CREATE TABLE hr_payroll_periods (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    month           CHAR(7) NOT NULL COMMENT 'YYYY-MM',
    cutoff          DATE NOT NULL,
    status          ENUM('draft','review','approved','locked') NOT NULL DEFAULT 'draft',
    approvals       JSON NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_payroll_periods_property_month (property_id, month),
    CONSTRAINT fk_hr_payroll_periods_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_payroll_items — earnings/deductions/employer_costs/gross/net = TEXT
-- ciphertext (JSON dienkripsi sebelum simpan, aplikasi yang men-decrypt).
-- ---------------------------------------------------------------------
CREATE TABLE hr_payroll_items (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    period_id       INT UNSIGNED NOT NULL,
    employee_id     INT UNSIGNED NOT NULL,
    earnings        TEXT NULL COMMENT 'ciphertext JSON',
    deductions      TEXT NULL COMMENT 'ciphertext JSON',
    employer_costs  TEXT NULL COMMENT 'ciphertext JSON',
    gross           TEXT NULL COMMENT 'ciphertext (nominal)',
    net             TEXT NULL COMMENT 'ciphertext (nominal)',
    pph21           TEXT NULL COMMENT 'ciphertext (nominal)',
    published_at    DATETIME NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_payroll_items_period_employee (period_id, employee_id),
    KEY idx_hr_payroll_items_employee (employee_id),
    CONSTRAINT fk_hr_payroll_items_period FOREIGN KEY (period_id) REFERENCES hr_payroll_periods(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_payroll_items_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_payroll_params — semua tarif configurable, bertanggal-berlaku.
-- ---------------------------------------------------------------------
CREATE TABLE hr_payroll_params (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    param_key           VARCHAR(60)  NOT NULL COMMENT 'bpjs_kes_pct|jht_pct|jp_cap|umk|ot_divisor|...',
    value               VARCHAR(60)  NOT NULL,
    effective_from      DATE NOT NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_payroll_params_property_key_from (property_id, param_key, effective_from),
    CONSTRAINT fk_hr_payroll_params_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_service_charge
-- ---------------------------------------------------------------------
CREATE TABLE hr_service_charge (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    period_id           INT UNSIGNED NOT NULL,
    total_collected     DECIMAL(16,2) NOT NULL DEFAULT 0,
    risk_pct            DECIMAL(5,2)  NOT NULL DEFAULT 0,
    hr_dev_pct          DECIMAL(5,2)  NOT NULL DEFAULT 0,
    dist_method         ENUM('point','equal','grade_weighted') NOT NULL DEFAULT 'point',
    point_value         DECIMAL(14,4) NULL,
    detail              JSON NULL COMMENT 'per employee: {employee_id, points, amount}',
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_service_charge_period (period_id),
    CONSTRAINT fk_hr_service_charge_period FOREIGN KEY (period_id) REFERENCES hr_payroll_periods(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_loans (kasbon)
-- ---------------------------------------------------------------------
CREATE TABLE hr_loans (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    employee_id     INT UNSIGNED NOT NULL,
    amount          DECIMAL(14,2) NOT NULL,
    installments    TINYINT UNSIGNED NOT NULL DEFAULT 1,
    balance         DECIMAL(14,2) NOT NULL,
    approvals       JSON NULL,
    status          ENUM('requested','approved','rejected','active','settled','cancelled') NOT NULL DEFAULT 'requested',
    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_hr_loans_property_employee (property_id, employee_id),
    CONSTRAINT fk_hr_loans_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_loans_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 7. REKRUTMEN, ONBOARDING, DW POOL, TRAINEE (F11, F12)
-- =====================================================================

-- ---------------------------------------------------------------------
-- hr_vacancies — dibuat sebelum hr_candidates (FK vacancy_id)
-- ---------------------------------------------------------------------
CREATE TABLE hr_vacancies (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    position_id         INT UNSIGNED NOT NULL,
    headcount           SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    budget_approved     TINYINT(1)   NOT NULL DEFAULT 0,
    status              ENUM('open','on_hold','closed','filled') NOT NULL DEFAULT 'open',
    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_hr_vacancies_property_status (property_id, status),
    KEY idx_hr_vacancies_position (position_id),
    CONSTRAINT fk_hr_vacancies_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_vacancies_position FOREIGN KEY (position_id) REFERENCES hr_positions(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_candidates
-- ---------------------------------------------------------------------
CREATE TABLE hr_candidates (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    vacancy_id      INT UNSIGNED NOT NULL,
    data            JSON NOT NULL COMMENT 'identitas & data lamaran',
    cv_file         VARCHAR(255) NULL,
    stage           ENUM('applied','test','interview','offer','hired','rejected') NOT NULL DEFAULT 'applied',
    test_token      VARCHAR(64)  NULL COMMENT 'token akses test browser tanpa akun',
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_candidates_test_token (test_token),
    KEY idx_hr_candidates_property_stage (property_id, stage),
    KEY idx_hr_candidates_vacancy (vacancy_id),
    CONSTRAINT fk_hr_candidates_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_candidates_vacancy FOREIGN KEY (vacancy_id) REFERENCES hr_vacancies(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK susulan: hr_test_attempts.candidate_id -> hr_candidates(id)
ALTER TABLE hr_test_attempts
    ADD CONSTRAINT fk_hr_test_attempts_candidate
        FOREIGN KEY (candidate_id) REFERENCES hr_candidates(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

-- ---------------------------------------------------------------------
-- hr_onboarding
-- ---------------------------------------------------------------------
CREATE TABLE hr_onboarding (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    employee_id     INT UNSIGNED NOT NULL,
    checklist       JSON NULL COMMENT 'json: d1/d7/d30/d60/d90',
    buddy_id        INT UNSIGNED NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_onboarding_employee (employee_id),
    KEY idx_hr_onboarding_buddy (buddy_id),
    CONSTRAINT fk_hr_onboarding_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_onboarding_buddy FOREIGN KEY (buddy_id) REFERENCES hr_employees(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_dw_pool
-- ---------------------------------------------------------------------
CREATE TABLE hr_dw_pool (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    full_name       VARCHAR(150) NOT NULL,
    phone           VARCHAR(30)  NULL,
    departments     JSON NULL COMMENT 'array departemen yang bisa dipanggil',
    rating_avg      DECIMAL(3,2) NULL,
    last_called     DATE NULL,
    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_hr_dw_pool_property (property_id),
    CONSTRAINT fk_hr_dw_pool_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_trainees
-- ---------------------------------------------------------------------
CREATE TABLE hr_trainees (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    full_name       VARCHAR(150) NOT NULL,
    school          VARCHAR(150) NULL,
    mou_file        VARCHAR(255) NULL,
    mentor_id       INT UNSIGNED NULL,
    batch           VARCHAR(30)  NULL,
    start_date      DATE NULL,
    end_date        DATE NULL,
    monthly_scores  JSON NULL,
    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_hr_trainees_property (property_id),
    KEY idx_hr_trainees_mentor (mentor_id),
    CONSTRAINT fk_hr_trainees_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_trainees_mentor FOREIGN KEY (mentor_id) REFERENCES hr_employees(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 8. HANDOVER, INSIDEN, LOST & FOUND, GRIEVANCE, EXIT (F13, F14)
-- hr_announcements TIDAK dibuat -> memakai broadcasts existing
-- (app_source='hrd'), sesuai §4 SPEK-HOTELCREW.
-- =====================================================================

-- ---------------------------------------------------------------------
-- hr_handover_logs
-- ---------------------------------------------------------------------
CREATE TABLE hr_handover_logs (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    department      VARCHAR(60)  NOT NULL COMMENT 'non-engineering (handover engineering ada di HotelFix)',
    shift           ENUM('M','A','N') NOT NULL,
    notes           TEXT NULL,
    by_user_id      INT UNSIGNED NOT NULL,
    ack_by          INT UNSIGNED NULL,
    ack_at          DATETIME NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_handover_logs_property_dept (property_id, department, shift),
    KEY idx_hr_handover_logs_by (by_user_id),
    CONSTRAINT fk_hr_handover_logs_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_handover_logs_by FOREIGN KEY (by_user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_hr_handover_logs_ack_by FOREIGN KEY (ack_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_incidents
-- ---------------------------------------------------------------------
CREATE TABLE hr_incidents (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    type            ENUM('guest','loss','safety') NOT NULL,
    description     TEXT NOT NULL,
    photos          JSON NULL,
    location_id     INT UNSIGNED NULL,
    reported_by     INT UNSIGNED NOT NULL,
    routed_to       VARCHAR(60)  NULL COMMENT 'departemen tujuan',
    status          ENUM('open','investigating','resolved','closed') NOT NULL DEFAULT 'open',
    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_hr_incidents_property_status (property_id, status),
    KEY idx_hr_incidents_location (location_id),
    KEY idx_hr_incidents_reported_by (reported_by),
    CONSTRAINT fk_hr_incidents_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_incidents_location FOREIGN KEY (location_id) REFERENCES locations(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_incidents_reported_by FOREIGN KEY (reported_by) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_lost_found
-- ---------------------------------------------------------------------
CREATE TABLE hr_lost_found (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    item            VARCHAR(200) NOT NULL,
    photo           VARCHAR(255) NULL,
    location_id     INT UNSIGNED NULL,
    found_by        INT UNSIGNED NOT NULL,
    stored_at       VARCHAR(100) NULL,
    returned_to     VARCHAR(150) NULL,
    status          ENUM('stored','claimed','disposed','handed_to_authority') NOT NULL DEFAULT 'stored',
    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_hr_lost_found_property_status (property_id, status),
    KEY idx_hr_lost_found_location (location_id),
    KEY idx_hr_lost_found_found_by (found_by),
    CONSTRAINT fk_hr_lost_found_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_lost_found_location FOREIGN KEY (location_id) REFERENCES locations(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_lost_found_found_by FOREIGN KEY (found_by) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_grievances
-- ---------------------------------------------------------------------
CREATE TABLE hr_grievances (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    employee_id     INT UNSIGNED NULL COMMENT 'NULL = anonim',
    channel         ENUM('normal','whistle') NOT NULL DEFAULT 'normal',
    text            TEXT NOT NULL,
    status          ENUM('open','investigating','resolved','closed') NOT NULL DEFAULT 'open',
    handled_by      INT UNSIGNED NULL COMMENT 'akses terbatas (HC Dir/GM)',
    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_hr_grievances_property_status (property_id, status),
    KEY idx_hr_grievances_employee (employee_id),
    CONSTRAINT fk_hr_grievances_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_grievances_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_hr_grievances_handled_by FOREIGN KEY (handled_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hr_exit
-- ---------------------------------------------------------------------
CREATE TABLE hr_exit (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    employee_id     INT UNSIGNED NOT NULL,
    checklist       JSON NULL,
    interview       JSON NULL,
    reason          VARCHAR(255) NULL,
    last_day        DATE NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hr_exit_employee (employee_id),
    CONSTRAINT fk_hr_exit_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 9. AUDIT & STEP-UP AUTH (satu utk kedua modul FIX+CREW; §3.2 zona merah)
-- =====================================================================

-- ---------------------------------------------------------------------
-- audit_logs — APPEND-ONLY (tidak ada UPDATE/DELETE oleh aplikasi)
-- ---------------------------------------------------------------------
CREATE TABLE audit_logs (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ts              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id         INT UNSIGNED NULL,
    role            VARCHAR(30)  NULL,
    property_id     INT UNSIGNED NULL,
    module          ENUM('fix','crew') NOT NULL,
    action          ENUM('create','update','delete','view_sensitive','export','login','logout','approve','override','stepup_ok','stepup_fail') NOT NULL,
    entity          VARCHAR(60)  NOT NULL,
    entity_id       VARCHAR(60)  NULL,
    before_data     JSON NULL COMMENT 'field sensitif ikut terenkripsi di level aplikasi',
    after_data      JSON NULL COMMENT 'field sensitif ikut terenkripsi di level aplikasi',
    ip              VARCHAR(45)  NULL,
    device          VARCHAR(255) NULL,
    note            VARCHAR(255) NULL,
    PRIMARY KEY (id),
    KEY idx_audit_logs_property_ts (property_id, ts),
    KEY idx_audit_logs_user (user_id),
    KEY idx_audit_logs_entity (entity, entity_id),
    CONSTRAINT fk_audit_logs_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- auth_stepup — PIN 6 digit + OTP WA (stub interface, gateway menyusul)
-- ---------------------------------------------------------------------
CREATE TABLE auth_stepup (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id         INT UNSIGNED NOT NULL,
    zone            ENUM('payroll') NOT NULL DEFAULT 'payroll',
    pin_hash        VARCHAR(255) NULL,
    otp_hash        VARCHAR(255) NULL,
    wa_msisdn       VARCHAR(30)  NULL,
    sent_at         DATETIME NULL,
    expires_at      DATETIME NULL,
    verified_at     DATETIME NULL,
    fail_count      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    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_auth_stepup_user_zone (user_id, zone),
    CONSTRAINT fk_auth_stepup_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 10. ALTER users — link ke hr_employees + flag Trainer
-- =====================================================================

ALTER TABLE users
    ADD COLUMN employee_id INT UNSIGNED NULL AFTER phone,
    ADD COLUMN is_trainer  TINYINT(1) NOT NULL DEFAULT 0 AFTER employee_id;

ALTER TABLE users
    ADD CONSTRAINT fk_users_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

-- =====================================================================
-- END 0008_hr_init.sql
-- Catatan penyimpangan/keputusan:
-- 1. hr_positions & hr_faculties/hr_competencies ditambah property_id
--    eksplisit (§4 spek tidak menyebut eksplisit tapi §1 PM-PLAN mewajibkan
--    SEMUA tabel ber-property_id) — konsisten dgn tabel FIX 0001_init.sql.
-- 2. hr_positions.required_competencies & hr_employees kolom kompetensi:
--    disimpan JSON kode kompetensi (bukan FK M:N terpisah) karena §4 spek
--    hanya menulis "required_competencies (→hr_competencies)" tanpa
--    tabel pivot eksplisit; hr_employee_comp sudah menutupi relasi
--    per-individu dgn level.
-- 3. Kolom polimorfik (hr_briefing_items.ref_id, hr_test_attempts.
--    candidate_id + user_id, hr_challenge_results.participant_id + team)
--    TIDAK diberi FK tunggal karena mengacu >1 tabel target — konsisten
--    pola FIX (mis. tidak ada di 0001, tapi ini pola umum utk polimorfik).
-- 4. hr_kpi_scores & hr_inspections: department disimpan sbg VARCHAR
--    (bukan FK) mengikuti pola `hr_employees.department`,
--    `hr_positions.department`, `work_orders` dst di seluruh skema
--    (department = string bebas, bukan tabel master terpisah).
-- 5. hr_announcements TIDAK dibuat — memakai broadcasts existing
--    (0001_init.sql) dgn app_source; kolom app_source ditambahkan ke
--    broadcasts di migrasi ini TIDAK dilakukan (di luar scope Agent DB;
--    PM-PLAN §4 tidak mendaftar ALTER broadcasts pada Agent DB — dicatat
--    sbg catatan utk Agent SHELL/HR bila dibutuhkan).
-- =====================================================================
