-- Slice 8g-GoodsCrm · CRM-authored Client PO + 4-stage onboarding chain

/* ── 1. Extend Client PO items with CRM authoring fields ──────────── */
ALTER TABLE `goods_client_po_items`
    ADD COLUMN `brand`           VARCHAR(120) NOT NULL DEFAULT '' AFTER `description`,
    ADD COLUMN `source_pref`     ENUM('LOCAL','IMPORT','ANY') NOT NULL DEFAULT 'ANY' AFTER `brand`,
    ADD COLUMN `delivery_method` VARCHAR(60)  NOT NULL DEFAULT '' AFTER `source_pref`;

/* ── 2. Seed GOODS_PROJECT_ONBOARDING chain (idempotent) ──────────── */
INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by, created_on)
SELECT 'GOODS_PROJECT_ONBOARDING',
       'Goods Project Onboarding',
       'CRM captures items on the Client PO → FM reviews finance + assigns Accountant/Procurement → Exec approves + assigns PM → PM assigns PC. Terminal opens Ops gates.',
       'ACTIVE', 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM fin_chain_defs WHERE chain_key = 'GOODS_PROJECT_ONBOARDING');

-- Step 1: CRM_CAPTURE (originator step — no assignments needed here, CRM just submits)
INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, required_assignments, sort_order, created_by, created_on)
SELECT d.c0d, 1, 'CRM_CAPTURE',  'CRM captures items', 'CRM', 0,
       'CRM has authored the Client PO items (brand, qty, source, delivery). Submits for finance review.',
       '', 1, 0, NOW()
  FROM fin_chain_defs d
 WHERE d.chain_key = 'GOODS_PROJECT_ONBOARDING'
   AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'CRM_CAPTURE');

-- Step 2: FM_REVIEW — required_assignments: ACCOUNTANT, PROCUREMENT
INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, required_assignments, sort_order, created_by, created_on)
SELECT d.c0d, 2, 'FM_REVIEW',    'FM reviews finance + assigns Acct/Proc', 'FINANCE MANAGER', 0,
       'FM reviews the contract vs planned COG margin. Must assign the Accountant and Procurement seats before approving.',
       'ACCOUNTANT,PROCUREMENT', 2, 0, NOW()
  FROM fin_chain_defs d
 WHERE d.chain_key = 'GOODS_PROJECT_ONBOARDING'
   AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'FM_REVIEW');

-- Step 3: EXEC_APPROVE — required_assignments: PROJECT_MANAGER
INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, required_assignments, sort_order, created_by, created_on)
SELECT d.c0d, 3, 'EXEC_APPROVE', 'Exec approves + assigns PM', 'EXECUTIVE', 0,
       'Executive gives commercial go-ahead and assigns the Project Manager seat.',
       'PROJECT_MANAGER', 3, 0, NOW()
  FROM fin_chain_defs d
 WHERE d.chain_key = 'GOODS_PROJECT_ONBOARDING'
   AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'EXEC_APPROVE');

-- Step 4: PM_ASSIGN — TERMINAL — required_assignments: PROJECTS_COORDINATOR
INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, required_assignments, sort_order, created_by, created_on)
SELECT d.c0d, 4, 'PM_ASSIGN',    'PM assigns PC', 'PROJECT MANAGER', 1,
       'PM designates the Projects Coordinator who owns day-to-day scrub + fulfilment. Terminal — Ops gates open on approval.',
       'PROJECTS_COORDINATOR', 4, 0, NOW()
  FROM fin_chain_defs d
 WHERE d.chain_key = 'GOODS_PROJECT_ONBOARDING'
   AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'PM_ASSIGN');

/* ── 3. Backfill: existing goods projects that already cleared old chain → migrated_flag=1 (D1) ─
       Only touch projects that are APPROVED but have no goods_client_po → definitely legacy path. */
UPDATE c_projects p
   SET p.migrated_flag = 1,
       p.edited_on     = NOW()
 WHERE p.project_type = 'goods'
   AND p.deleted      = 0
   AND p.migrated_flag = 0
   AND p.crm_status    IN ('APPROVED','SUBMITTED','IN_PROGRESS','NOT_STARTED')
   AND NOT EXISTS (SELECT 1 FROM goods_client_pos po WHERE po.project_id = p.c0d AND po.deleted = 0);
