-- Slice 8Z · PC scrub layer + handover-to-Procurement.
-- Adds per-line detail fields, item-level status (PENDING → CONFIRMED),
-- handover tracking on the PO header, and perm grants.

-- ── 1. Per-item scrub fields on goods_client_po_items ─────────────────────
SET @c := (SELECT COUNT(*) FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'goods_client_po_items' AND column_name = 'item_status');
SET @sql := IF(@c = 0,
    "ALTER TABLE goods_client_po_items
        ADD COLUMN item_status  ENUM('PENDING','CONFIRMED','QUERIED','ACCEPTED') NOT NULL DEFAULT 'PENDING' AFTER notes,
        ADD COLUMN priority     ENUM('LOW','MED','HIGH','CRITICAL') NOT NULL DEFAULT 'MED' AFTER item_status,
        ADD COLUMN alt_ok       TINYINT(1) NOT NULL DEFAULT 1 AFTER priority,
        ADD COLUMN alt_note     VARCHAR(300) NULL AFTER alt_ok,
        ADD COLUMN deliver_by   DATE NULL AFTER alt_note,
        ADD COLUMN deliver_to   VARCHAR(300) NULL AFTER deliver_by,
        ADD COLUMN scrub_remarks VARCHAR(1000) NULL AFTER deliver_to,
        ADD COLUMN confirmed_at DATETIME NULL AFTER scrub_remarks,
        ADD COLUMN confirmed_by INT UNSIGNED NULL AFTER confirmed_at,
        ADD COLUMN confirmed_by_name VARCHAR(160) NULL AFTER confirmed_by",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. Handover tracking on the PO header ────────────────────────────────
SET @c2 := (SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE() AND table_name = 'goods_client_pos' AND column_name = 'handover_at');
SET @sql2 := IF(@c2 = 0,
    "ALTER TABLE goods_client_pos
        ADD COLUMN handover_at    DATETIME NULL AFTER scrub_notes,
        ADD COLUMN handover_by    INT UNSIGNED NULL AFTER handover_at,
        ADD COLUMN handover_by_name VARCHAR(160) NULL AFTER handover_by,
        ADD COLUMN handover_notes VARCHAR(1000) NULL AFTER handover_by_name,
        ADD COLUMN handover_to_user_id INT UNSIGNED NULL AFTER handover_notes",
    'SELECT 1');
PREPARE stmt FROM @sql2; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 3. Perms ──────────────────────────────────────────────────────────────
-- Update item details + confirm/unconfirm — PC + PM + Admin (PM in edge cases)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'goodsUpdateItemAj' AS ac
             UNION ALL SELECT 'goodsConfirmItemAj'
             UNION ALL SELECT 'goodsUnconfirmItemAj') actions
WHERE g.user_group_name IN ('Projects Coordinator','Project Manager','Administrator')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'Projects'
      AND p.action_allowed = actions.ac
);

-- Handover to Procurement — PC + Admin
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', 'goodsHandoverToProcurementAj', 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 = 'Projects'
      AND p.action_allowed = 'goodsHandoverToProcurementAj'
);
