-- Phase M8-p1-h5 — re-categorise JOBFORM_STAGE_COMPLETION_LOG.subitem_kind
-- to the new 3-bucket scheme (Windshield / Door / Flat).
--
-- Old scheme (migration 60) bucketed purely by SubItem NAME into
-- WS / Door / Quarter / Bulkhead / Flat. The new scheme is driven by
-- JOBFORM_GLASS.is_bended + name:
--   - NOT bended                  -> 'Flat'
--   - bended + 'windshield' name  -> 'WS'   (Windshield)
--   - bended (anything else)      -> 'Door' (covers doors AND any other
--                                            bended glass, incl. a bended
--                                            quarter / bulkhead)
--
-- No schema change — 'WS', 'Door', 'Flat' are all existing members of the
-- subitem_kind ENUM('WS','Door','Quarter','Bulkhead','Flat'); the legacy
-- 'Quarter'/'Bulkhead' members simply stop being written (kept in the enum
-- per the never-drop rule). Idempotent: re-running recomputes the same
-- value for every row.
--
-- The matching read path (agg-api/v1/summary/production.php +
-- production_detail.php) + the write helper
-- (agg-api/lib/jobform_cascade.php jf_summary_subitem_kind) were updated
-- to the same rule in the M8-p1-h5 API zip.

UPDATE JOBFORM_STAGE_COMPLETION_LOG scl
  JOIN JOBFORM_GLASS g      ON g.id = scl.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
   SET scl.subitem_kind = CASE
        WHEN COALESCE(g.is_bended, 0) <> 1 THEN 'Flat'
        WHEN LOWER(COALESCE(si.CRTSUBITEM_NAME, p.CRTSITEM_NAME, '')) LIKE '%windshield%' THEN 'WS'
        ELSE 'Door'
   END;