-- Slice 8c-Spine · Seed the two Works approval chains
-- Both: PC drafts → PM verifies → FM verifies → Exec approves (terminal)

/* WORKS_CR_APPROVAL */
INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by, created_on)
SELECT 'WORKS_CR_APPROVAL', 'Works CR (Consolidated Request) approval', 'Phase execution package + pillar budget approval', 'ACTIVE', 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM fin_chain_defs WHERE chain_key = 'WORKS_CR_APPROVAL');

SET @crChainId := (SELECT c0d FROM fin_chain_defs WHERE chain_key = 'WORKS_CR_APPROVAL');

INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, sort_order, created_on)
SELECT @crChainId, s.no, s.k, s.lbl, s.rr, s.term, s.so, NOW()
FROM (
    SELECT 1 no, 'PC_REVIEW'    k, 'PC review (originator)'     lbl, 'PROJECTS COORDINATOR' rr, 0 term, 10 so UNION ALL
    SELECT 2,   'PM_VERIFY',        'PM verifies scope + budget',     'PROJECT MANAGER',          0,      20         UNION ALL
    SELECT 3,   'FM_VERIFY',        'FM verifies pillar totals',      'FINANCE MANAGER',          0,      30         UNION ALL
    SELECT 4,   'EXEC_APPROVE',     'Executive approves the phase',   'EXECUTIVE',                1,      40
) s
WHERE NOT EXISTS (
    SELECT 1 FROM fin_chain_steps WHERE chain_id = @crChainId AND step_key = s.k
);

/* WORKS_VARIATION_APPROVAL */
INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by, created_on)
SELECT 'WORKS_VARIATION_APPROVAL', 'Works Variation Order approval', 'Client-instructed BOQ additions', 'ACTIVE', 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM fin_chain_defs WHERE chain_key = 'WORKS_VARIATION_APPROVAL');

SET @voChainId := (SELECT c0d FROM fin_chain_defs WHERE chain_key = 'WORKS_VARIATION_APPROVAL');

INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, sort_order, created_on)
SELECT @voChainId, s.no, s.k, s.lbl, s.rr, s.term, s.so, NOW()
FROM (
    SELECT 1 no, 'PC_PRICE'     k, 'PC prices the variation'    lbl, 'PROJECTS COORDINATOR' rr, 0 term, 10 so UNION ALL
    SELECT 2,   'PM_VERIFY',        'PM verifies technical merit',   'PROJECT MANAGER',          0,      20         UNION ALL
    SELECT 3,   'FM_VERIFY',        'FM verifies financial impact',  'FINANCE MANAGER',          0,      30         UNION ALL
    SELECT 4,   'EXEC_APPROVE',     'Executive authorises variation','EXECUTIVE',                1,      40
) s
WHERE NOT EXISTS (
    SELECT 1 FROM fin_chain_steps WHERE chain_id = @voChainId AND step_key = s.k
);
