-- Slice 8Z-h · Procurement Batch 2 · Sourcing path picker.
--
-- Once Procurement is happy with the scrubbed items, they pick a sourcing route:
--   Path A · Product Discovery first (needs cash for market research / samples)
--   Path B · Skip to RFQ (I already know the suppliers)
--
-- Path A creates a PRODUCT_DISCOVERY LPO via the existing CashRequests pipeline, threaded
-- to this PO via lpos.sourced_from_client_po_id (which already exists — used by
-- LpoRetirements::shortlistForPo for the Batch-3 RFQ shortlist seed).
--
-- No new tables needed — everything hangs off existing infrastructure:
--   - The path itself is one column on goods_client_pos
--   - The Discovery cash + retirement + findings all reuse the LPO / retirement pipeline
--   - The engagement linkage IS lpos.sourced_from_client_po_id

-- ── 1. goods_client_pos · sourcing_path column ─────────────────────────
SET @sch := DATABASE();
SET @tbl := 'goods_client_pos';

SET @c := (SELECT COUNT(*) FROM information_schema.columns
           WHERE table_schema=@sch AND table_name=@tbl AND column_name='sourcing_path');
SET @sql := IF(@c=0,
    "ALTER TABLE goods_client_pos ADD COLUMN sourcing_path ENUM('UNSET','DISCOVERY','SKIP_TO_RFQ') NOT NULL DEFAULT 'UNSET' AFTER handover_notes",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns
           WHERE table_schema=@sch AND table_name=@tbl AND column_name='sourcing_path_set_at');
SET @sql := IF(@c=0,
    'ALTER TABLE goods_client_pos ADD COLUMN sourcing_path_set_at DATETIME NULL',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns
           WHERE table_schema=@sch AND table_name=@tbl AND column_name='sourcing_path_set_by');
SET @sql := IF(@c=0,
    'ALTER TABLE goods_client_pos ADD COLUMN sourcing_path_set_by INT UNSIGNED NULL',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns
           WHERE table_schema=@sch AND table_name=@tbl AND column_name='sourcing_path_set_by_name');
SET @sql := IF(@c=0,
    'ALTER TABLE goods_client_pos ADD COLUMN sourcing_path_set_by_name VARCHAR(160) NULL',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. Perms ────────────────────────────────────────────────────────────
-- Procurement + Admin only — this is Procurement's decision to make.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'GoodsClientPos', 'setSourcingPathAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Procurement','Administrator')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'GoodsClientPos'
      AND p.action_allowed = 'setSourcingPathAj'
);

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'GoodsClientPos', 'requestDiscoveryAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Procurement','Administrator')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'GoodsClientPos'
      AND p.action_allowed = 'requestDiscoveryAj'
);
