-- Slice 8Z-w · Slice D4 — Batch PR execution via Payment Schedules
--
-- Adds:
--  1) PaymentRequests.batchToScheduleAj perm for Finance Manager + Administrator.
--  2) Relaxes payment_schedule_items unique constraint so multiple PR-milestones on the
--     same LPO can co-exist on one schedule (was: unique (schedule_id, lpo_id) — blocked
--     any LPO from appearing twice; now: allows duplicates when payment_request_id differs).

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

-- Drop the old strict unique + add a permissive index instead. The uniqueness we still want
-- (no double-linking of the SAME PR to the SAME schedule) is guaranteed at the app level
-- by createFromPrs's "not already on a schedule" filter + PR.next_check_status gating.
SET @sch := DATABASE();
SET @i := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@sch AND table_name='payment_schedule_items' AND index_name='uq_lpo_schedule');
SET @sql := IF(@i>0, "ALTER TABLE payment_schedule_items DROP INDEX uq_lpo_schedule, ADD KEY idx_lpo_schedule (schedule_id, lpo_id)", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
