-- =============================================================================
-- Migration: Work Activities - Add company_id & sequence_number
-- Purpose: 1) workactivities.company_id untuk filter per company
--          2) workactivityitems.sequence_number untuk kolom "No." (Rencana Kerja)
-- Safe:    Hanya ADD COLUMN (NULL/default), tidak mengubah/hapus data existing.
-- Run:     phpMyAdmin — jalankan per blok (Step 1, 2, 3, 4, 5) dan cek hasil.
-- Backup:  Disarankan backup database sebelum menjalankan.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Step 1: Tambah company_id di workactivities
-- -----------------------------------------------------------------------------
-- 1a: Tambah kolom (NULL dulu agar data lama tidak error)
ALTER TABLE `workactivities`
ADD COLUMN `company_id` INT(11) NULL DEFAULT NULL AFTER `employee_id`;

-- 1b: Index untuk filter/join
CREATE INDEX `idx_workactivities_company_id` ON `workactivities` (`company_id`);

-- 1c: Foreign key ke companies (pastikan tabel companies ada dan id INT)
ALTER TABLE `workactivities`
ADD CONSTRAINT `fk_workactivities_company_id`
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`)
  ON DELETE RESTRICT ON UPDATE CASCADE;


-- -----------------------------------------------------------------------------
-- Step 2: Backfill company_id dari employee (isi data lama)
-- -----------------------------------------------------------------------------
UPDATE `workactivities` wa
INNER JOIN `employees` e ON e.id = wa.employee_id
SET wa.company_id = e.company_id
WHERE wa.company_id IS NULL AND wa.employee_id IS NOT NULL;


-- -----------------------------------------------------------------------------
-- Step 3 (OPSIONAL): Jadikan company_id NOT NULL
-- Hanya jalankan jika semua baris workactivities sudah punya company_id.
-- Cek dulu: SELECT COUNT(*) FROM workactivities WHERE company_id IS NULL;
-- Jika hasil 0, boleh uncomment blok di bawah.
-- -----------------------------------------------------------------------------
-- ALTER TABLE `workactivities`
-- MODIFY COLUMN `company_id` INT(11) NOT NULL;


-- -----------------------------------------------------------------------------
-- Step 4: Tambah sequence_number di workactivityitems (untuk kolom "No.")
-- -----------------------------------------------------------------------------
ALTER TABLE `workactivityitems`
ADD COLUMN `sequence_number` INT(11) NULL DEFAULT 1 AFTER `workactivity_result`;

CREATE INDEX `idx_workactivityitems_sequence` ON `workactivityitems` (`workactivity_id`, `sequence_number`);


-- -----------------------------------------------------------------------------
-- Step 5 (OPSIONAL): Backfill sequence_number untuk data lama
-- Memberi urutan 1, 2, 3, ... per workactivity_id.
-- Jika ragu dengan variabel MySQL, cukup jalankan baris terakhir saja.
-- -----------------------------------------------------------------------------
-- Opsi A: Set semua yang NULL jadi 1
UPDATE `workactivityitems` SET sequence_number = 1 WHERE sequence_number IS NULL;

-- Opsi B (advanced): Urutan 1,2,3 per workactivity_id (MySQL 8+ / MariaDB 10.2+)
-- UPDATE workactivityitems t
-- JOIN (
--   SELECT id, workactivity_id,
--          ROW_NUMBER() OVER (PARTITION BY workactivity_id ORDER BY id) AS rn
--   FROM workactivityitems
-- ) src ON t.id = src.id
-- SET t.sequence_number = src.rn;