-- ============================================================
-- AGG-UAE ERP — Migration 51 — Phase M7-p4
--   DAILY_ATTENDANCE + JOBFORM.slack_days
-- ============================================================
--
-- Two new mechanics for the M7-p4 scheduler refinement round:
--
-- 1. DAILY_ATTENDANCE — per-(date, employee) attendance log driving
--    the scheduler's headcount-scaling formula per CLAUDE.md §3
--    invariant 61:
--      effective_capacity = base
--                         × (1 + (active − base_emp) × scaling_ratio)
--                         × calendar_multiplier
--                         × per_day_override
--    where `active` is the count of present employees per section
--    on the given date. status enum carries Present / Half-day /
--    Leave / Sick / Other for the future per-status weighting
--    (Half-day → 0.5 contribution; Present → 1.0; everything else → 0).
--    The scheduler reads attendance via a JOIN against
--    EMPLOYEE_STAGE_ASSIGNMENT to count active per section.
--
-- 2. JOBFORM.slack_days — backward-pass slack metric ("how many
--    working days can this JF slip and still hit target_date").
--    Persisted as a column for fast read on the JF list + rollup
--    surfaces; recomputed by the scheduler at the end of every
--    recompute. NULL when JF has no target_date or no schedule run
--    yet. NEGATIVE values = already late by N days.
--
-- Gating: the scheduler reads attendance only when the .env flag
-- SCHEDULER_HEADCOUNT_SCALING_ENABLED=true is set. Default OFF so
-- the M7-p4 ship doesn't change capacity behaviour until ops
-- explicitly opt in via a flag flip. Slack computation is always
-- on — it's a read-side metric, no behavioural change.
--
-- Idempotent — re-runs find table/column already present and no-op.
-- JOBFORM is slim — no innodb_strict_mode dance.
--
-- Depends on: 14-phase4.1b-employee.sql (EMPLOYEE PK),
--             17-phase-m1-jobforms.sql (JOBFORM, EMPLOYEE_STAGE_ASSIGNMENT).

-- ─────────────────────────────────────────────────────────────
-- (1) DAILY_ATTENDANCE
-- ─────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS DAILY_ATTENDANCE (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    date            DATE NOT NULL,
    emp_no          DECIMAL(7,0) NOT NULL,
    status          ENUM('Present','Half-day','Leave','Sick','Other')
                        NOT NULL DEFAULT 'Present',
    hours_worked    DECIMAL(4,2) NULL,        -- optional explicit hours
    notes           VARCHAR(255) NULL,
    recorded_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    recorded_by     VARCHAR(100) NULL,
    UNIQUE KEY uq_date_emp (date, emp_no),
    INDEX idx_date (date),
    INDEX idx_emp  (emp_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- (2) JOBFORM.slack_days
-- ─────────────────────────────────────────────────────────────

SET @col_exists := (
    SELECT COUNT(*)
      FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM'
       AND COLUMN_NAME  = 'slack_days'
);

SET @sql := IF(
    @col_exists = 0,
    "ALTER TABLE JOBFORM
       ADD COLUMN slack_days INT NULL DEFAULT NULL
           AFTER last_forecast_check_by,
       ADD COLUMN slack_computed_at DATETIME NULL DEFAULT NULL
           AFTER slack_days",
    "SELECT 'slack_days column already present — skipping ALTER' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
      FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM'
       AND INDEX_NAME   = 'idx_jobform_slack'
);

SET @sql := IF(
    @idx_exists = 0,
    "CREATE INDEX idx_jobform_slack ON JOBFORM (slack_days)",
    "SELECT 'idx_jobform_slack already present — skipping CREATE' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ─────────────────────────────────────────────────────────────
-- Verification
-- ─────────────────────────────────────────────────────────────

SELECT 'DAILY_ATTENDANCE' AS object,
       (SELECT COUNT(*) FROM DAILY_ATTENDANCE) AS row_count;

SELECT 'slack_days' AS column_name,
       COUNT(*) AS jf_count,
       SUM(CASE WHEN slack_days IS NULL    THEN 1 ELSE 0 END) AS no_slack,
       SUM(CASE WHEN slack_days <  0       THEN 1 ELSE 0 END) AS late,
       SUM(CASE WHEN slack_days BETWEEN 0 AND 2 THEN 1 ELSE 0 END) AS tight,
       SUM(CASE WHEN slack_days >= 3       THEN 1 ELSE 0 END) AS comfortable
  FROM JOBFORM;