-- Slice 8d-LpoChain · seed the WORKS_LPO_APPROVAL chain (idempotent)

INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by, created_on)
SELECT 'WORKS_LPO_APPROVAL',
       'Works LPO Approval',
       'Supplier LPO drafted from a Works CR fulfilment. PC review → PM approval → FM review → Exec authorise. On terminal APPROVED, LPO cash_out_status flips to APPROVED and Payables can prep a PV.',
       'ACTIVE', 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM fin_chain_defs WHERE chain_key = 'WORKS_LPO_APPROVAL');

-- Steps — mirror GOODS_LPO_APPROVAL exactly (PC → PM → FM → EXEC)
INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order, created_by, created_on)
SELECT d.c0d, 1, 'PC_REVIEW',    'PC Review',      'PROJECTS COORDINATOR', 0, 'PC confirms sourcing choice + margin', 1, 0, NOW()
  FROM fin_chain_defs d
 WHERE d.chain_key = 'WORKS_LPO_APPROVAL'
   AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'PC_REVIEW');

INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order, created_by, created_on)
SELECT d.c0d, 2, 'PM_APPROVE',   'PM Approval',    'PROJECT MANAGER',      0, 'PM checks supplier terms + total against pillar budget', 2, 0, NOW()
  FROM fin_chain_defs d
 WHERE d.chain_key = 'WORKS_LPO_APPROVAL'
   AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'PM_APPROVE');

INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order, created_by, created_on)
SELECT d.c0d, 3, 'FM_REVIEW',    'FM Review',      'FINANCE MANAGER',      0, 'FM verifies cash availability + retention applicability', 3, 0, NOW()
  FROM fin_chain_defs d
 WHERE d.chain_key = 'WORKS_LPO_APPROVAL'
   AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'FM_REVIEW');

INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order, created_by, created_on)
SELECT d.c0d, 4, 'EXEC',         'Exec Decision',  'EXECUTIVE',            1, 'Exec terminal authorisation. On APPROVED, LPO flips to APPROVED and Payables can PV.', 4, 0, NOW()
  FROM fin_chain_defs d
 WHERE d.chain_key = 'WORKS_LPO_APPROVAL'
   AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'EXEC');
