-- AGG-UAE — Migration 60 — M8-p1 Production Summary stage-completion event log.
--
-- PURPOSE
-- -------
-- The M8 Production Summary screen (per CLAUDE.md §12 + the locked
-- design decisions) needs TRUE per-month / per-day production history,
-- split by glass kind (Windshield / Door / Quarter / Bulkhead / Flat),
-- counted in physical UNITS (one 10-layer windshield = 1 produced at
-- Glass Cutting), uniform across ALL 8 stages.
--
-- The earlier (scrapped) M8-p1 build tried to derive "produced" from
-- JOBFORM_STAGE.completed_at + qty_completed live at query time. That
-- only produced sensible numbers for stages whose completed_at is set
-- and never reopened — pre-lam stages (GC/FC/PC/Printing) on
-- in-flight JFs showed nothing, so the chart was wrong. The fix (locked
-- Decision 1 — "Yes — add the event log + backfill") is an append-only
-- event log written on EVERY stage-completion advance across all 8
-- stages — the analog of VD's TASKJOBPROCESS — so the summary reads a
-- timestamped, per-kind, per-stage, units history directly.
--
-- This migration:
--   (1) Creates JOBFORM_STAGE_COMPLETION_LOG (append-only).
--   (2) Best-effort backfills one row per already-Completed
--       (glass, stage) from JOBFORM_STAGE.completed_at + qty_completed,
--       so the chart shows existing finished work the moment M8 ships.
--
-- subitem_kind is DENORMALISED at write time (one of WS / Door /
-- Quarter / Bulkhead / Flat) so the summary query never has to JOIN
-- through CRTPARTS → CRTSUBITEMS at read time. Derivation is a
-- name-pattern match on the parent glass's SubItem name — APC custom
-- glass names (Cargo Window, Crew Side, Turret, …) and anything not
-- matching the four vehicle-glass categories fall to 'Flat', which is
-- the correct bucket per the locked decision.
--
-- PHP that depends on this:
--   - agg-api/lib/jobform_cascade.php — jf_summary_subitem_kind()
--     helper + the live log-write inside
--     jf_cascade_on_complete_activity() (M7 / Auto-Schedule path).
--   - agg-api/v1/jobforms/task_complete.php — live log-write on the
--     M3 / Production-Stages Mark-Complete path.
--   - agg-api/v1/summary/production.php — reads this table for the
--     PRODUCED facts (rejected facts still come from JOBFORM_QC).
--
-- IDEMPOTENT — re-runnable. The CREATE is IF NOT EXISTS; the backfill
-- INSERT is gated on a NOT EXISTS probe per (glass, stage) so a second
-- run finds nothing to add. (Live delta rows written after deploy also
-- satisfy the NOT EXISTS gate, so a late re-run never double-inserts a
-- backfill row for a stage that has since logged live events.)

-- ---------------------------------------------------------------------
-- (1) The event log.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS JOBFORM_STAGE_COMPLETION_LOG (
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    jobform_id       INT UNSIGNED NOT NULL,
    jobform_glass_id INT UNSIGNED NOT NULL,
    stage            ENUM('Glass Cutting','Film Cutting','PC Cutting','Glass Bending',
                          'Printing','Lamination','Autoclave','Final Inspection') NOT NULL,
    subitem_kind     ENUM('WS','Door','Quarter','Bulkhead','Flat') NOT NULL DEFAULT 'Flat',
    qty              INT NOT NULL DEFAULT 0,   -- units that NEWLY cleared this stage in this event
    completed_at     DATETIME NOT NULL,        -- when the units cleared (drives the period bucket)
    completed_by     VARCHAR(100) NULL,
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_scl_stage_completed (stage, completed_at),
    INDEX idx_scl_completed (completed_at),
    INDEX idx_scl_glass_stage (jobform_glass_id, stage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- (2) Best-effort backfill of existing finished work.
--
-- One row per already-Completed (glass, stage). qty = the stage's
-- qty_completed (= units that cleared, since at Completed time
-- qty_completed = qty_required = the MIN-of-tasks bottleneck count).
-- completed_at = the stage's completion timestamp (set by BOTH the M3
-- and M7 cascades), so the units land in the calendar month they
-- actually closed.
--
-- Stages without a completed_at (Pending / In Progress / Skipped) are
-- skipped — they have no defensible timestamp to attribute to a month.
-- That is the "best-effort" boundary; live events fill in from here.
--
-- subitem_kind via SubItem-name pattern match on the parent glass.
-- ---------------------------------------------------------------------
INSERT INTO JOBFORM_STAGE_COMPLETION_LOG
    (jobform_id, jobform_glass_id, stage, subitem_kind, qty, completed_at, completed_by, created_at)
SELECT
    g.jobform_id,
    s.jobform_glass_id,
    s.stage,
    CASE
        WHEN LOWER(COALESCE(si.CRTSUBITEM_NAME, '')) LIKE '%windshield%' THEN 'WS'
        WHEN LOWER(COALESCE(si.CRTSUBITEM_NAME, '')) LIKE '%door%'       THEN 'Door'
        WHEN LOWER(COALESCE(si.CRTSUBITEM_NAME, '')) LIKE '%quarter%'    THEN 'Quarter'
        WHEN LOWER(COALESCE(si.CRTSUBITEM_NAME, '')) LIKE '%bulkhead%'   THEN 'Bulkhead'
        ELSE 'Flat'
    END                                                 AS subitem_kind,
    s.qty_completed                                     AS qty,
    s.completed_at                                      AS completed_at,
    NULL                                                AS completed_by,
    NOW()                                               AS created_at
FROM JOBFORM_STAGE s
JOIN JOBFORM_GLASS g  ON g.id = s.jobform_glass_id
LEFT JOIN CRTPARTS  p  ON p.CRTSITEM_ID = g.glass_part_id
LEFT JOIN CRTSUBITEMS si ON si.CRTSUBITEM_ID = p.CRTSITEM_CRTSUBITEMID
WHERE s.status = 'Completed'
  AND s.completed_at IS NOT NULL
  AND s.qty_completed > 0
  AND NOT EXISTS (
      SELECT 1 FROM JOBFORM_STAGE_COMPLETION_LOG l
       WHERE l.jobform_glass_id = s.jobform_glass_id
         AND l.stage = s.stage
  );