-- =============================================================================
-- Migration: Work Activities - Monthly document support
-- Purpose:
--   1) Support 1 Work Activities doc = 1 month (period_year/period_month) stored in workactivities
--   2) Store activity_date per item (workactivityitems.activity_date) for per-day entries inside the month
-- Safe:
--   - Additive changes (ADD COLUMN + ADD INDEX)
--   - Backfill only when target column is NULL
-- Run:
--   - Execute in MySQL (phpMyAdmin) in order.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Step 1: Add period fields to workactivities (header)
-- -----------------------------------------------------------------------------
ALTER TABLE `workactivities`
  ADD COLUMN `period_month` INT(11) NULL DEFAULT NULL AFTER `work_type`,
  ADD COLUMN `period_year` INT(11) NULL DEFAULT NULL AFTER `period_month`;

CREATE INDEX `idx_workactivities_period` ON `workactivities` (`period_year`, `period_month`);
CREATE INDEX `idx_workactivities_employee_period` ON `workactivities` (`employee_id`, `period_year`, `period_month`);

-- Optional: if you frequently query by company + period
CREATE INDEX `idx_workactivities_company_period` ON `workactivities` (`company_id`, `period_year`, `period_month`);


-- -----------------------------------------------------------------------------
-- Step 2: Add activity_date to workactivityitems (detail)
-- -----------------------------------------------------------------------------
ALTER TABLE `workactivityitems`
  ADD COLUMN `activity_date` DATE NULL DEFAULT NULL AFTER `workactivity_plan`;

CREATE INDEX `idx_workactivityitems_workactivity_date_seq`
  ON `workactivityitems` (`workactivity_id`, `activity_date`, `sequence_number`);


-- -----------------------------------------------------------------------------
-- Step 3: Backfill (best-effort) for existing data
-- - items.activity_date <- header.activity_date
-- - header.period_year/month <- YEAR/MONTH(activity_date)
-- -----------------------------------------------------------------------------
UPDATE `workactivityitems` wi
INNER JOIN `workactivities` wa ON wa.id = wi.workactivity_id
SET wi.activity_date = wa.activity_date
WHERE wi.activity_date IS NULL AND wa.activity_date IS NOT NULL;

UPDATE `workactivities`
SET period_year = YEAR(activity_date),
    period_month = MONTH(activity_date)
WHERE (period_year IS NULL OR period_month IS NULL)
  AND activity_date IS NOT NULL;