-- ============================================================================
-- Phase M5-p1 — Composition MainCategory serial counter seed
-- ============================================================================
--
-- Per CLAUDE.md §14 (Permanent serial number allocation) the agg-api
-- handler that allocates a permanent_serial at Final Inspection Pass
-- reads + increments CRTMAINITEM.CRTMITEM_SERIALCOUNT on the row
-- `CRTMITEM_NAME='Composition' AND CRTMITEM_TYPE='MainCategory'`.
-- That row must EXIST before the first FI Pass fires, otherwise the
-- allocation transaction throws (the FOR UPDATE SELECT returns no
-- rows). The legacy AGG-UAE catalogue may or may not already have
-- such a row depending on how the database was seeded.
--
-- This migration is **idempotent**:
--   - If the row already exists (any value of SERIALCOUNT), leave
--     it alone — production data is sacrosanct.
--   - If it doesn't exist, insert a fresh row with SERIALCOUNT=1000
--     so the first allocated serial number is the digit-suffix 1000
--     and the next is 1001 (matches the §14 pseudocode which reads
--     `count` then writes `count + 1`).
--
-- CRTMITEM_ID is decimal(5,0) NOT NULL with no AUTO_INCREMENT (per
-- invariant 53 about CRTMAINITEM's schema — same posture as
-- CRTPARTRELATE / EMPLOYEE.EMP_NO). We compute MAX(CRTMITEM_ID)+1
-- inline in the INSERT...SELECT subquery so the new row gets a
-- non-colliding PK without touching the existing PK strategy.
--
-- The agg-api qc_create.php handler this migration unblocks will
-- ship in the same phase (M5-p1) and will:
--   - SELECT CRTMITEM_SERIALCOUNT FROM CRTMAINITEM
--       WHERE CRTMITEM_NAME='Composition' AND CRTMITEM_TYPE='MainCategory'
--       FOR UPDATE
--   - If count > 9999, reset to 1000 (wrap)
--   - permanent_serial = 'AGG-' + year_letter + month_letter +
--                        sprintf('%04d', count)
--   - UPDATE CRTMITEM_SERIALCOUNT = count + 1
--   - UPDATE JOBFORM_SERIAL.permanent_serial = above
-- ============================================================================

-- Idempotent seed. INSERT..SELECT..WHERE NOT EXISTS is the safe shape
-- when the table has no UNIQUE index on (CRTMITEM_NAME, CRTMITEM_TYPE)
-- — the legacy schema doesn't. Concurrent migrations are not a
-- concern (this is a one-time deploy step).
INSERT INTO CRTMAINITEM (
    CRTMITEM_ID,
    CRTMITEM_NAME,
    CRTMITEM_TYPE,
    CRTMITEM_MAINCATPK,
    CRTMITEM_STKREGLIST,
    CRTMITEM_SERIALCOUNT,
    CRTMITEM_PRIORITY
)
SELECT
    COALESCE((SELECT MAX(CRTMITEM_ID) FROM CRTMAINITEM AS x), 0) + 1,
    'Composition',
    'MainCategory',
    0,
    0,
    1000,
    100
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM CRTMAINITEM
     WHERE CRTMITEM_NAME = 'Composition'
       AND CRTMITEM_TYPE = 'MainCategory'
);

-- ============================================================================
-- Verification:
--   SELECT CRTMITEM_ID, CRTMITEM_NAME, CRTMITEM_TYPE, CRTMITEM_SERIALCOUNT
--     FROM CRTMAINITEM
--    WHERE CRTMITEM_NAME='Composition' AND CRTMITEM_TYPE='MainCategory';
--     expect exactly 1 row with SERIALCOUNT >= 1000 (1000 if freshly
--     seeded; whatever the production catalogue had already if a row
--     pre-existed).
-- ============================================================================