-- =====================================================================
-- 0019 — UNIVERSAL TASK (keputusan owner, terkunci)
-- Papan task terpadu (hr_tasks) diperluas jadi model UNIVERSAL:
--   (a) kind instant|staged
--   (b) template journey (utk staged) + log tahapan append-only
--   (c) status seragam tampilan: menunggu/on progress/done/not done/cancel
--       (label presentasi — nilai DB TETAP open/in_progress/done/not_done/
--       cancelled; overdue = FLAG turunan due_date, BUKAN status baru)
--   (d) kaitan KPI (kpi_definition_id, nullable)
--   (e) WO TIDAK disentuh (tetap kartu FIX read-only di papan, lihat
--       TaskBoardService::board() bagian (b) — tidak diubah migrasi ini).
--
-- Acuan: TaskBoardService.php kontrak createTask/completeTask/board,
-- src/Support/TaskJourneys.php (registry template, dibuat menyertai migrasi
-- ini), hr_kpi_definitions (0008).
--
-- PENTING nilai ENUM status: nilai LAMA (open/in_progress/done/overdue/
-- cancelled) TIDAK di-rename — banyak kode existing (TaskBoardService,
-- TaskDetailPage, TasksPage) bergantung pada nilai-nilai ini. Migrasi ini
-- HANYA menambah nilai baru 'not_done'. Nilai 'overdue' DIPERTAHANKAN di
-- ENUM (kompatibilitas mundur bila ada baris lama bernilai ini) tapi
-- DEPRECATED — kode baru tidak lagi men-set status='overdue'; overdue kini
-- murni flag turunan (due_date < today) yang dihitung di TaskBoardService,
-- konsisten dgn cara papan sudah menampilkan overdue selama ini.
--
-- Jalankan setelah 0001..0018 (butuh hr_tasks dari 0008, hr_kpi_definitions
-- dari 0008, users dari 0001).
-- =====================================================================

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

-- ---------------------------------------------------------------------
-- 1. hr_tasks — kolom UNIVERSAL TASK baru (additive, tidak mengubah kolom
--    lama selain status yang HANYA menambah nilai ENUM).
-- ---------------------------------------------------------------------
ALTER TABLE hr_tasks
    ADD COLUMN kind ENUM('instant','staged') NOT NULL DEFAULT 'instant'
        COMMENT 'instant = selesai sekali aksi; staged = melalui tahapan journey_template' AFTER source_app,
    ADD COLUMN journey_template VARCHAR(50) NULL
        COMMENT 'key template journey (App\\Support\\TaskJourneys) — wajib diisi bila kind=staged' AFTER kind,
    ADD COLUMN current_stage VARCHAR(50) NULL
        COMMENT 'stage aktif (key di template journey_template) — NULL bila kind=instant' AFTER journey_template,
    ADD COLUMN kpi_definition_id INT UNSIGNED NULL
        COMMENT 'kaitan opsional ke hr_kpi_definitions — dipakai HrKpiService::completionStats()' AFTER hotelfix_wo_id,
    ADD KEY idx_hr_tasks_kpi_definition (kpi_definition_id),
    ADD KEY idx_hr_tasks_kind (property_id, kind),
    ADD CONSTRAINT fk_hr_tasks_kpi_definition FOREIGN KEY (kpi_definition_id) REFERENCES hr_kpi_definitions(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

-- MODIFY status: TAMBAH 'not_done', nilai lama tidak berubah/tidak dihapus.
ALTER TABLE hr_tasks
    MODIFY COLUMN status ENUM('open','in_progress','done','overdue','cancelled','not_done') NOT NULL DEFAULT 'open'
        COMMENT 'label tampilan: open=Menunggu, in_progress=On Progress, done=Done, not_done=Not Done, cancelled=Cancel. overdue DEPRECATED (baris lama saja) -- kini FLAG turunan due_date, bukan status.';

-- ---------------------------------------------------------------------
-- 2. hr_task_stage_log — jejak tahapan journey, APPEND-ONLY (tidak ada
--    UPDATE/DELETE dari aplikasi; riwayat lengkap per task staged).
-- ---------------------------------------------------------------------
CREATE TABLE hr_task_stage_log (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    task_id     BIGINT UNSIGNED NOT NULL,
    stage       VARCHAR(50)  NOT NULL,
    note        VARCHAR(255) NULL,
    by_user_id  INT UNSIGNED NULL,
    at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_hr_task_stage_log_task_at (task_id, at),
    CONSTRAINT fk_hr_task_stage_log_task FOREIGN KEY (task_id) REFERENCES hr_tasks(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_hr_task_stage_log_user FOREIGN KEY (by_user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 3. SEED CONTOH (demo — konsisten pola "(DUMMY demo)" migrasi 0016/0017)
-- =====================================================================

-- ---------------------------------------------------------------------
-- 3.1 Task staged baru: contoh journey 'procurement' (diminta -> di_po ->
-- indent -> barang_datang -> diserahkan), tahap aktif 'di_po'.
-- assignee 530 = Linen Attendant HK (lihat 0016, dipakai jg task id=2);
-- kpi_definition_id=5 = "Linen & amenities loss" (housekeeping, 0010) —
-- kaitan wajar krn task ini soal pengadaan amenities.
-- ---------------------------------------------------------------------
INSERT INTO hr_tasks
    (id, property_id, source, source_app, source_ref, kind, journey_template, current_stage,
     title, description, department, assignee_id, due_date, priority, status, kpi_definition_id)
VALUES
    (9, 1, 'manual', 'crew', NULL, 'staged', 'procurement', 'di_po',
     'Pengadaan sabun & shampo amenities kamar (DUMMY demo)',
     'Stok amenities menipis, sudah diajukan PO ke purchasing (DUMMY demo — contoh journey procurement).',
     'housekeeping', 530, DATE_ADD(CURDATE(), INTERVAL 10 DAY), 'normal', 'in_progress', 5);

INSERT INTO hr_task_stage_log (task_id, stage, note, by_user_id, at) VALUES
    (9, 'diminta', 'Linen Attendant melapor stok amenities tinggal 3 hari (DUMMY demo).', 530, DATE_SUB(NOW(), INTERVAL 3 DAY)),
    (9, 'di_po', 'PO diajukan ke purchasing, menunggu konfirmasi vendor (DUMMY demo).', 546, DATE_SUB(NOW(), INTERVAL 1 DAY));

-- ---------------------------------------------------------------------
-- 3.2 Update 1-2 task dummy existing (0016) jadi `kind` sesuai sifatnya:
-- - id=6 "Reminder kontrak ... H-60": pola journey nego kontrak yang jelas
--   (reminder -> nego -> ttd) -> staged, template contract_renewal, stage
--   aktif 'reminder' (baru mulai, belum ada progres nego).
-- - id=3 "Update roster housekeeping minggu depan": aksi tunggal tanpa
--   tahapan bertingkat -> instant (eksplisit, walau sudah default).
-- ---------------------------------------------------------------------
UPDATE hr_tasks
   SET kind = 'staged', journey_template = 'contract_renewal', current_stage = 'reminder'
 WHERE id = 6 AND property_id = 1;

INSERT INTO hr_task_stage_log (task_id, stage, note, by_user_id, at) VALUES
    (6, 'reminder', 'Reminder H-60 otomatis dari cron kontrak SELL (DUMMY demo).', NULL, DATE_SUB(NOW(), INTERVAL 7 DAY));

UPDATE hr_tasks
   SET kind = 'instant'
 WHERE id = 3 AND property_id = 1;

-- =====================================================================
-- 4. KAITAN KPI UNIVERSAL (penegasan owner: "dikaitkan ke KPI SEMUA
--    task-nya — dibuat universal juga"). kpi_definition_id TIDAK boleh
--    bergantung pengisian manual per task: TaskBoardService::createTask()
--    kini me-resolve OTOMATIS ke indikator hr_kpi_definitions ber-
--    source='task' aktif pada departemen yang sama (kpi_definition_id
--    eksplisit tetap menang). Lihat App\Model\HrKpiDefinition::
--    resolveTaskIndicator() + App\Service\HrKpiService::taskKpiStats()/
--    taskStatsForEmployee().
-- =====================================================================

-- MODIFY source: TAMBAH nilai 'task' (nilai lama tidak berubah).
ALTER TABLE hr_kpi_definitions
    MODIFY COLUMN source ENUM('attendance','training','discipline','checklist','hotelfix_wo','manual','inspection','task')
        NOT NULL COMMENT "'task' = dihitung OTOMATIS dari completion hr_tasks bertaut (bukan input manual) -- lihat HrKpiService::taskKpiStats().";

-- ---------------------------------------------------------------------
-- Seed contoh (demo — DUMMY, konsisten pola 0016/0017): 1 indikator
-- source='task' utk Housekeeping supaya resolusi otomatis kaitan KPI
-- terlihat hidup. ANTI-KARANG: target & weight BELUM ditetapkan HOD/GM
-- (bukan hasil kalkulasi/kalibrasi HR) -> disimpan target='0' + weight=0.00
-- (weight 0 = TIDAK berkontribusi ke computeTotal() departemen, jadi
-- indikator ini aman ditampilkan aktif tanpa mendistorsi skor bulanan yang
-- sudah berjalan; menunggu HOD/GM menetapkan bobot & target riil sebelum
-- dipakai dlm penilaian resmi -- lihat REVIEW-GM utk proses kalibrasi).
-- ---------------------------------------------------------------------
INSERT INTO hr_kpi_definitions (property_id, department, position_id, name, target, unit, weight, source, active) VALUES
    (1, 'housekeeping', NULL, 'Penyelesaian task tepat waktu (auto dari Universal Task)', '0', '%', 0.00, 'task', 1);

-- Task demo tambahan (kind=instant) departemen housekeeping TANPA
-- kpi_definition_id eksplisit di INSERT ini -- nilai kpi_definition_id di
-- baris ini murni menunjukkan END-STATE hasil resolusi otomatis (subquery
-- meniru App\Model\HrKpiDefinition::resolveTaskIndicator() saat runtime;
-- resolusi SEBENARNYA terjadi di kode PHP createTask(), bukan di migrasi
-- ini -- diverifikasi terpisah, lihat laporan QC).
INSERT INTO hr_tasks
    (id, property_id, source, source_app, source_ref, kind, journey_template, current_stage,
     title, description, department, assignee_id, due_date, priority, status, kpi_definition_id)
VALUES
    (10, 1, 'manual', 'crew', NULL, 'instant', NULL, NULL,
     'Cek kelengkapan amenities kamar checkout (DUMMY demo)',
     'Contoh task instant departemen housekeeping yang otomatis tertaut ke indikator KPI "Penyelesaian task tepat waktu" (DUMMY demo — kaitan KPI universal).',
     'housekeeping', 514, DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'normal', 'open',
     (SELECT id FROM hr_kpi_definitions WHERE property_id = 1 AND department = 'housekeeping' AND source = 'task' ORDER BY id DESC LIMIT 1));