-- Slice 8Z-g · Procurement Batch 1 · scrub surface on Client PO detail.
--
-- Adds Accept / Query / Reopen actions on the PC-scrubbed items. Procurement locks item
-- specs once they're happy (ACCEPTED), or bounces back to PC (QUERIED). PC can also flip
-- an ACCEPTED item back to QUERIED via Reopen if specs need to change mid-sourcing.
--
-- Schema: 4 new columns on goods_client_po_items — a single reusable note + audit triad.
-- Perms: 3 new actions on the existing GoodsClientPos controller.

-- ── 1. goods_client_po_items · procurement-action audit fields ──────────
-- Idempotent guard: check information_schema before ALTER (MySQL 8 pre-8.0.29 has no IF NOT EXISTS).
SET @sch := DATABASE();
SET @tbl := 'goods_client_po_items';

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='procurement_note');
SET @sql := IF(@c=0,
    'ALTER TABLE goods_client_po_items ADD COLUMN procurement_note VARCHAR(1000) NULL AFTER scrub_remarks',
    '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='procurement_actioned_at');
SET @sql := IF(@c=0,
    'ALTER TABLE goods_client_po_items ADD COLUMN procurement_actioned_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='procurement_actioned_by');
SET @sql := IF(@c=0,
    'ALTER TABLE goods_client_po_items ADD COLUMN procurement_actioned_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='procurement_actioned_by_name');
SET @sql := IF(@c=0,
    'ALTER TABLE goods_client_po_items ADD COLUMN procurement_actioned_by_name VARCHAR(160) NULL',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. Perms ────────────────────────────────────────────────────────────
-- Accept + Query: Procurement + Administrator (Procurement owns sourcing decisions).
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'GoodsClientPos', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'acceptItemAj' AS ac UNION ALL SELECT 'queryItemAj') actions
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 = actions.ac
);

-- Reopen: Projects Coordinator + Administrator (PC re-triggers editing if specs must change).
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'GoodsClientPos', 'reopenItemAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Projects Coordinator','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 = 'reopenItemAj'
);
