-- =====================================================================
-- SANGGRALOKA ONE — Migration 0010_seed_hr_hk
-- Seed CONTOH HANYA divisi Housekeeping + faculty GEN (PM-PLAN §1 butir 5:
-- "contoh konten cukup 1 divisi", jangan seed 43 posisi).
-- property_id = 1 (Sanggraloka Ubud, satu-satunya property di 0001/0002).
-- Kode kompetensi HANYA yang tertulis di dokumen jobdesk/kpi HK nyata:
--   hotelcrew/docs/jobdesk/HK-RA-01, HK-PA-01, HK-LN-01, HK-CRD-01,
--   HK-EHK-01 (kode: HK-01,03,04,08,09,10,12,13,14,15,16,17,18,19,20;
--   LDR-01,02,04,05,07; GEN-18) dan sanggraloka.md §2/§3
--   (GEN-20 antisipasi/silent service, GEN-21 Butterfly Sanctuary).
-- GEN core Lv2 (semua) disebut di jobdesk tanpa nomor eksplisit -> DIBERI
-- KODE PLACEHOLDER GEN-01..GEN-05 bertanda komentar eksplisit "GEN CORE
-- generik, nomor pasti menyusul KOMPETENSI-UNIVERSITY.xlsx" -- BUKAN
-- dikarang sbg fakta pasti, sekadar penanda supaya required_competencies
-- hr_positions bisa dirujuk; boleh diganti begitu master 232 kompetensi
-- (KOMPETENSI-UNIVERSITY.xlsx) diimpor penuh.
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- hr_shifts — jam wajar M/A/N/OFF/PH, departemen Housekeeping
-- ---------------------------------------------------------------------
INSERT INTO hr_shifts (property_id, department, code, start_time, end_time) VALUES
    (1, 'housekeeping', 'M',   '07:00:00', '15:00:00'),
    (1, 'housekeeping', 'A',   '14:00:00', '22:00:00'),
    (1, 'housekeeping', 'N',   '22:00:00', '06:00:00'),
    (1, 'housekeeping', 'M2',  '09:00:00', '17:00:00'),
    (1, 'housekeeping', 'OFF', NULL, NULL),
    (1, 'housekeeping', 'PH',  NULL, NULL);

-- ---------------------------------------------------------------------
-- hr_leave_types — tahunan/sakit/ijin adat Bali/melahirkan/unpaid
-- ---------------------------------------------------------------------
INSERT INTO hr_leave_types (property_id, name, quota, requires_doc, requires_gm, paid) VALUES
    (1, 'Cuti Tahunan',                12,  0, 0, 1),
    (1, 'Sakit',                       NULL, 1, 0, 1),
    (1, 'Ijin Adat Bali (odalan/ngaben/potong gigi)', NULL, 0, 0, 1),
    (1, 'Cuti Melahirkan',             90,  1, 1, 1),
    (1, 'Ijin Tanpa Gaji (Unpaid)',    NULL, 0, 1, 0);

-- ---------------------------------------------------------------------
-- hr_faculties — General Studies (GEN) + Housekeeping (HK)
-- kode & nama sesuai sanggraloka.md §6 (14 faculty, kode {PREFIX}-{nn})
-- ---------------------------------------------------------------------
INSERT INTO hr_faculties (id, property_id, code, name) VALUES
    (1, 1, 'GEN', 'General Studies'),
    (2, 1, 'HK',  'Housekeeping');

-- ---------------------------------------------------------------------
-- hr_competencies — HANYA kode nyata dari jobdesk/kpi HK + GEN-18/20/21
-- terkonfirmasi (sanggraloka.md §2/§3). Nama fungsi kompetensi HK
-- disimpulkan langsung dari konteks tugas jobdesk yang menyebutnya
-- (komentar sumber per baris).
-- ---------------------------------------------------------------------
INSERT INTO hr_competencies (property_id, code, name, tier, faculty_id, max_level) VALUES
    -- GEN core generik (nomor pasti belum diimpor dari KOMPETENSI-UNIVERSITY.xlsx
    -- 232 kompetensi -- placeholder eksplisit, BUKAN dikarang sbg final)
    (1, 'GEN-CORE', 'GEN core (generik) — nomor pasti menyusul impor KOMPETENSI-UNIVERSITY.xlsx', 'core', 1, 2),
    -- GEN bernomor terkonfirmasi (sanggraloka.md §3, §2, §6)
    (1, 'GEN-18', 'Frasa dasar Perancis & Korea (base tamu utama)', 'core', 1, 4),
    (1, 'GEN-20', 'Antisipasi & silent service (gaya small/ultra-luxury hospitality)', 'core', 1, 4),
    (1, 'GEN-21', 'Butterfly Sanctuary — siklus metamorfosis & spesies unggulan (Troides helena)', 'core', 1, 4),
    -- HK functional (jobdesk HK-RA-01, HK-PA-01, HK-LN-01, HK-CRD-01, HK-EHK-01)
    (1, 'HK-01', 'Standar kebersihan kamar & area publik (checklist 30 titik) — sumber: HK-RA-01, HK-PA-01(implisit no.), HK-CRD-01, HK-EHK-01', 'functional', 2, 4),
    (1, 'HK-03', 'Kompetensi inti Room Attendant — sumber: HK-RA-01', 'functional', 2, 4),
    (1, 'HK-04', 'Kompetensi inti Room Attendant — sumber: HK-RA-01', 'functional', 2, 4),
    (1, 'HK-08', 'Kompetensi bersama RA/PA (area & amenities) — sumber: HK-RA-01, HK-PA-01', 'functional', 2, 4),
    (1, 'HK-09', 'Kompetensi Public Area Attendant — sumber: HK-PA-01', 'functional', 2, 4),
    (1, 'HK-10', 'Kompetensi bersama PA/Coordinator — sumber: HK-PA-01, HK-CRD-01', 'functional', 2, 4),
    (1, 'HK-12', 'Linen & laundry handling — sumber: HK-LN-01', 'functional', 2, 4),
    (1, 'HK-13', 'Kompetensi Linen Attendant (lanjutan) — sumber: HK-LN-01', 'functional', 2, 4),
    (1, 'HK-14', 'Kompetensi bersama RA/PA — sumber: HK-RA-01, HK-PA-01', 'functional', 2, 4),
    (1, 'HK-15', 'Kompetensi inti Room Attendant — sumber: HK-RA-01', 'functional', 2, 4),
    (1, 'HK-16', 'Kompetensi Public Area Attendant — sumber: HK-PA-01', 'functional', 2, 4),
    (1, 'HK-17', 'Kompetensi Public Area Attendant — sumber: HK-PA-01', 'functional', 2, 4),
    (1, 'HK-18', 'Inspeksi kamar (checklist 30 titik) — sumber: HK-CRD-01, HK-EHK-01', 'functional', 2, 4),
    (1, 'HK-19', 'Koordinasi PPM dengan Engineering — sumber: HK-CRD-01, HK-EHK-01', 'functional', 2, 4),
    (1, 'HK-20', 'Penanganan laundry outsource — sumber: HK-CRD-01, HK-LN-01, HK-EHK-01', 'functional', 2, 4),
    -- LDR (leadership) dipakai jenjang HK Coordinator/Executive Housekeeper
    (1, 'LDR-01', 'Kepemimpinan inti — prasyarat jalur SPV (LDR-L1) — sumber: HK-CRD-01, HK-EHK-01', 'leadership', 1, 4),
    (1, 'LDR-02', 'Kepemimpinan inti — sumber: HK-CRD-01, HK-EHK-01', 'leadership', 1, 4),
    (1, 'LDR-04', 'Kepemimpinan inti — sumber: HK-CRD-01, HK-EHK-01', 'leadership', 1, 4),
    (1, 'LDR-05', 'Kepemimpinan inti — sumber: HK-CRD-01', 'leadership', 1, 4),
    (1, 'LDR-07', 'Kepemimpinan inti — sumber: HK-CRD-01, HK-EHK-01', 'leadership', 1, 4);

-- ---------------------------------------------------------------------
-- hr_positions — HK (gelar Coordinator, bukan Supervisor)
-- ---------------------------------------------------------------------
INSERT INTO hr_positions (id, property_id, name, department, level, jobdesc_code, jobdesc_version, required_competencies) VALUES
    (1, 1, 'Room Attendant',          'housekeeping', 'staff',       'HK-RA-01',  2, JSON_ARRAY('HK-01','HK-03','HK-04','HK-08','HK-14','HK-15','GEN-CORE','GEN-18')),
    (2, 1, 'Public Area Attendant',   'housekeeping', 'staff',       'HK-PA-01',  1, JSON_ARRAY('HK-08','HK-09','HK-10','HK-14','HK-16','HK-17','GEN-CORE','GEN-18')),
    (3, 1, 'Linen Attendant',         'housekeeping', 'staff',       'HK-LN-01',  1, JSON_ARRAY('HK-12','HK-20','HK-13','GEN-CORE')),
    (4, 1, 'HK Coordinator',          'housekeeping', 'coordinator', 'HK-CRD-01', 1, JSON_ARRAY('HK-01','HK-18','HK-19','HK-10','HK-20','LDR-01','LDR-02','LDR-04','LDR-05','LDR-07','GEN-CORE')),
    (5, 1, 'Executive Housekeeper',   'housekeeping', 'hod',         'HK-EHK-01', 1, JSON_ARRAY('HK-01','HK-18','HK-19','HK-20','LDR-01','LDR-02','LDR-04','LDR-07','GEN-CORE'));

-- ---------------------------------------------------------------------
-- hr_employees — 8 HK ter-link ke users housekeeping/reporter dari 0006.
-- Dipilih dari 0006_seed_real_users.sql (department='housekeeping'):
-- 504 putu.sanglah (dept_head -> dipetakan Executive Housekeeper),
-- 514 made.prawita, 525 wayan.wijaya, 526 putu.raysaka, 527 wayan.nugraha,
-- 528 putu.ardita, 529 luh.putri, 530 komang.purnairawan (semua reporter).
-- Karena struktur nyata HK (HK-CRD-01 §Kedudukan) = 4 RA + 2 PA + 1 Linen
-- di bawah 1 HK Coordinator + 1 Executive Housekeeper, dan user 0006 tidak
-- membedakan sub-posisi HK, pemetaan berikut EKSPLISIT sbg contoh seed
-- (bukan fakta HR final -- perlu konfirmasi HR posisi riil tiap nama).
-- ---------------------------------------------------------------------
INSERT INTO hr_employees
    (id, property_id, user_id, nik, full_name, department, division, position_id, level,
     employment_status, join_date, supervisor_id, active)
VALUES
    (1, 1, 504, 'HK-0001', 'I Putu Ngurah Sanglah',  'housekeeping', 'housekeeping', 5, 'hod',         'permanent', '2019-03-01', NULL, 1),
    (2, 1, 514, 'HK-0002', 'I Made Tapan Prawita',   'housekeeping', 'housekeeping', 4, 'coordinator', 'permanent', '2020-06-15', 1,    1),
    (3, 1, 525, 'HK-0003', 'I Wayan Arsa Wijaya',    'housekeeping', 'housekeeping', 1, 'staff',       'contract',  '2022-01-10', 2,    1),
    (4, 1, 526, 'HK-0004', 'Putu Agus Rama Raysaka', 'housekeeping', 'housekeeping', 1, 'staff',       'contract',  '2022-04-05', 2,    1),
    (5, 1, 527, 'HK-0005', 'I Wayan Merta Nugraha',  'housekeeping', 'housekeeping', 1, 'staff',       'contract',  '2023-02-20', 2,    1),
    (6, 1, 528, 'HK-0006', 'I Putu Angga Ardita',    'housekeeping', 'housekeeping', 1, 'staff',       'contract',  '2023-07-11', 2,    1),
    (7, 1, 529, 'HK-0007', 'Ni Luh Made Satya Utami Putri', 'housekeeping', 'housekeeping', 2, 'staff', 'contract',  '2021-09-01', 2,    1),
    (8, 1, 530, 'HK-0008', 'I Komang Purnairawan',   'housekeeping', 'housekeeping', 3, 'staff',       'contract',  '2021-11-15', 2,    1);

-- Backfill users.employee_id (kolom ditambahkan di 0008_hr_init.sql)
UPDATE users SET employee_id = 1 WHERE id = 504;
UPDATE users SET employee_id = 2 WHERE id = 514;
UPDATE users SET employee_id = 3 WHERE id = 525;
UPDATE users SET employee_id = 4 WHERE id = 526;
UPDATE users SET employee_id = 5 WHERE id = 527;
UPDATE users SET employee_id = 6 WHERE id = 528;
UPDATE users SET employee_id = 7 WHERE id = 529;
UPDATE users SET employee_id = 8 WHERE id = 530;

-- ---------------------------------------------------------------------
-- hr_roster — 1 minggu contoh (7 Jul - 13 Jul 2026), 4 RA + 2 PA + 1 Linen
-- rotasi M/A sederhana, 1 OFF/minggu per orang (Coordinator & EHK jam kantor
-- M2, tidak diroster penuh minggu ini -- fokus tim lapangan sesuai instruksi).
-- ---------------------------------------------------------------------
INSERT INTO hr_roster (property_id, employee_id, date, shift_id, published_at) VALUES
    -- employee 3 (RA)
    (1, 3, '2026-07-07', 1, '2026-07-05 10:00:00'),
    (1, 3, '2026-07-08', 1, '2026-07-05 10:00:00'),
    (1, 3, '2026-07-09', 1, '2026-07-05 10:00:00'),
    (1, 3, '2026-07-10', 2, '2026-07-05 10:00:00'),
    (1, 3, '2026-07-11', 2, '2026-07-05 10:00:00'),
    (1, 3, '2026-07-12', 5, '2026-07-05 10:00:00'),
    (1, 3, '2026-07-13', 1, '2026-07-05 10:00:00'),
    -- employee 4 (RA)
    (1, 4, '2026-07-07', 2, '2026-07-05 10:00:00'),
    (1, 4, '2026-07-08', 2, '2026-07-05 10:00:00'),
    (1, 4, '2026-07-09', 5, '2026-07-05 10:00:00'),
    (1, 4, '2026-07-10', 1, '2026-07-05 10:00:00'),
    (1, 4, '2026-07-11', 1, '2026-07-05 10:00:00'),
    (1, 4, '2026-07-12', 1, '2026-07-05 10:00:00'),
    (1, 4, '2026-07-13', 2, '2026-07-05 10:00:00'),
    -- employee 5 (RA)
    (1, 5, '2026-07-07', 1, '2026-07-05 10:00:00'),
    (1, 5, '2026-07-08', 5, '2026-07-05 10:00:00'),
    (1, 5, '2026-07-09', 1, '2026-07-05 10:00:00'),
    (1, 5, '2026-07-10', 1, '2026-07-05 10:00:00'),
    (1, 5, '2026-07-11', 2, '2026-07-05 10:00:00'),
    (1, 5, '2026-07-12', 2, '2026-07-05 10:00:00'),
    (1, 5, '2026-07-13', 2, '2026-07-05 10:00:00'),
    -- employee 6 (RA)
    (1, 6, '2026-07-07', 2, '2026-07-05 10:00:00'),
    (1, 6, '2026-07-08', 2, '2026-07-05 10:00:00'),
    (1, 6, '2026-07-09', 2, '2026-07-05 10:00:00'),
    (1, 6, '2026-07-10', 5, '2026-07-05 10:00:00'),
    (1, 6, '2026-07-11', 1, '2026-07-05 10:00:00'),
    (1, 6, '2026-07-12', 1, '2026-07-05 10:00:00'),
    (1, 6, '2026-07-13', 1, '2026-07-05 10:00:00'),
    -- employee 7 (PA)
    (1, 7, '2026-07-07', 1, '2026-07-05 10:00:00'),
    (1, 7, '2026-07-08', 1, '2026-07-05 10:00:00'),
    (1, 7, '2026-07-09', 1, '2026-07-05 10:00:00'),
    (1, 7, '2026-07-10', 1, '2026-07-05 10:00:00'),
    (1, 7, '2026-07-11', 1, '2026-07-05 10:00:00'),
    (1, 7, '2026-07-12', 5, '2026-07-05 10:00:00'),
    (1, 7, '2026-07-13', 2, '2026-07-05 10:00:00'),
    -- employee 8 (Linen)
    (1, 8, '2026-07-07', 1, '2026-07-05 10:00:00'),
    (1, 8, '2026-07-08', 1, '2026-07-05 10:00:00'),
    (1, 8, '2026-07-09', 1, '2026-07-05 10:00:00'),
    (1, 8, '2026-07-10', 1, '2026-07-05 10:00:00'),
    (1, 8, '2026-07-11', 5, '2026-07-05 10:00:00'),
    (1, 8, '2026-07-12', 1, '2026-07-05 10:00:00'),
    (1, 8, '2026-07-13', 1, '2026-07-05 10:00:00');

-- ---------------------------------------------------------------------
-- hr_courses — 2-3 course GEN/HK contoh
-- ---------------------------------------------------------------------
INSERT INTO hr_courses (id, property_id, faculty_id, competency_id, title, week_of, deadline, materials, mandatory_departments) VALUES
    (1, 1, 1, (SELECT id FROM hr_competencies WHERE code='GEN-21' AND property_id=1), 'Mengenal Butterfly Sanctuary Sanggraloka', '2026-07-06', '2026-07-12',
        JSON_ARRAY(JSON_OBJECT('type','article','ref','materi/gen-21-butterfly.md'), JSON_OBJECT('type','card','ref','flashcard/troides-helena.json')),
        NULL),
    (2, 1, 1, (SELECT id FROM hr_competencies WHERE code='GEN-18' AND property_id=1), 'Frasa Dasar Perancis & Korea utk Tamu', '2026-07-06', '2026-07-12',
        JSON_ARRAY(JSON_OBJECT('type','video','ref','materi/gen-18-frasa-fr-kr.mp4'), JSON_OBJECT('type','card','ref','flashcard/gen-18-frasa.json')),
        NULL),
    (3, 1, 2, (SELECT id FROM hr_competencies WHERE code='HK-01' AND property_id=1), 'Checklist 30 Titik Kamar — Refresher Mingguan', '2026-07-06', '2026-07-12',
        JSON_ARRAY(JSON_OBJECT('type','video','ref','materi/hk-01-checklist-30.mp4'), JSON_OBJECT('type','article','ref','materi/hk-01-standar-foto.md')),
        JSON_ARRAY('housekeeping'));

-- ---------------------------------------------------------------------
-- hr_tests — 1 test dgn question_bank JSON 5 soal (course HK-01)
-- ---------------------------------------------------------------------
INSERT INTO hr_tests (id, property_id, course_id, competency_id, title, question_bank, pass_grade, time_limit_minutes, max_attempts, type) VALUES
    (1, 1, 3, (SELECT id FROM hr_competencies WHERE code='HK-01' AND property_id=1),
     'Test Mingguan — Checklist 30 Titik Kamar',
     JSON_ARRAY(
        JSON_OBJECT('q','Berapa target waktu maksimal make-up room sesuai standar Sanggraloka?','options', JSON_ARRAY('15 menit','25 menit','45 menit','60 menit'),'answer',1),
        JSON_OBJECT('q','Berapa target waktu maksimal check-out cleaning (kamar kotor total)?','options', JSON_ARRAY('25 menit','35 menit','45 menit','90 menit'),'answer',2),
        JSON_OBJECT('q','Kerusakan kamar yang ditemukan Room Attendant dilaporkan lewat sistem apa?','options', JSON_ARRAY('WhatsApp grup','HotelFix (foto)','Buku catatan manual','Telepon Engineering langsung'),'answer',1),
        JSON_OBJECT('q','Berapa persen sampling minimal kamar/hari yang diinspeksi HK Coordinator?','options', JSON_ARRAY('10%','20%','30%','50%'),'answer',2),
        JSON_OBJECT('q','Barang temuan tamu di kamar diserahkan ke lost & found kapan?','options', JSON_ARRAY('Akhir minggu','Shift berikutnya','Pada shift yang sama','Setelah tamu check-out'),'answer',2)
     ),
     85, 15, 3, 'course');

-- ---------------------------------------------------------------------
-- hr_kpi_definitions — HK dari HK-scorecard.md (6 indikator departemen)
-- ---------------------------------------------------------------------
INSERT INTO hr_kpi_definitions (property_id, department, position_id, name, target, unit, weight, source) VALUES
    (1, 'housekeeping', NULL, 'Skor inspeksi kamar (checklist 30 titik)',            '>=95',  '%',            30.00, 'inspection'),
    (1, 'housekeeping', NULL, 'Komplain kebersihan tamu',                            '<=2',   'komplain/bulan', 20.00, 'manual'),
    (1, 'housekeeping', NULL, 'Produktivitas (kamar selesai vs target manning)',     '=100',  '%',            15.00, 'checklist'),
    (1, 'housekeeping', NULL, 'Turnaround kamar check-out -> vacant-ready',          '<=2/4', 'jam',          15.00, 'hotelfix_wo'),
    (1, 'housekeeping', NULL, 'Linen & amenities loss',                              '<=1.5', '% nilai stok', 10.00, 'manual'),
    (1, 'housekeeping', NULL, 'Kompetensi tim (completion course + skill matrix HK inti Lv3)', '100/80', '% course / % tim Lv3', 10.00, 'training');

-- ---------------------------------------------------------------------
-- hr_payroll_params — default (regulasi, boleh diisi sesuai spek F10)
-- UMK = 0 sesuai instruksi eksplisit "[ANGKA: tetapkan HR]"
-- ---------------------------------------------------------------------
INSERT INTO hr_payroll_params (property_id, param_key, value, effective_from) VALUES
    (1, 'bpjs_kes_employee_pct', '1',    '2026-01-01'),
    (1, 'bpjs_kes_employer_pct', '4',    '2026-01-01'),
    (1, 'bpjs_kes_cap',          '12000000', '2026-01-01'),
    (1, 'jht_employee_pct',      '2',    '2026-01-01'),
    (1, 'jht_employer_pct',      '3.7',  '2026-01-01'),
    (1, 'jp_employee_pct',       '1',    '2026-01-01'),
    (1, 'jp_employer_pct',       '2',    '2026-01-01'),
    (1, 'jkk_pct',               '0.24', '2026-01-01'),
    (1, 'jkm_pct',               '0.3',  '2026-01-01'),
    (1, 'ot_divisor',            '173',  '2026-01-01'),
    (1, 'umk',                   '0',    '2026-01-01');
    -- umk = 0 -- [ANGKA: tetapkan HR] -- belum ada angka UMK Gianyar resmi
    -- di dokumen spek/sanggraloka.md; JANGAN diisi tebakan.

-- =====================================================================
-- END 0010_seed_hr_hk.sql
-- Catatan penyimpangan:
-- 1. GEN-CORE dipakai sbg kode PLACEHOLDER (bukan GEN-01..GEN-05 seperti
--    draf awal) supaya tidak menyiratkan nomor pasti yang belum ada
--    sumbernya -- jobdesk hanya menulis "GEN core Lv2 (semua)" tanpa
--    nomor. Diberi 1 baris representatif dgn nama menjelaskan statusnya.
-- 2. Pemetaan 8 karyawan HK ke sub-posisi (RA/PA/Linen/Coordinator/EHK)
--    adalah ASUMSI SEED demi memenuhi instruksi "6-8 hr_employees HK" —
--    data 0006 tidak membedakan sub-posisi HK, hanya department+role FIX.
--    PERLU KONFIRMASI HR sebelum dipakai sbg data karyawan riil.
-- 3. hr_kpi_definitions memakai target sbg VARCHAR bebas (kontrak §4:
--    "target" tanpa tipe eksplisit) — nilai gabungan spt "100/80" ditulis
--    utuh, penguraian ke 2 angka jadi tanggung jawab aplikasi/laporan.
-- =====================================================================