-- Migration: Add 'Payment Not Made' maintenance stage and transitions
-- Run as a MySQL user with appropriate privileges

START TRANSACTION;

-- 1) Ensure the stage exists
INSERT INTO maintenance_stages (stage_name, created_at)
SELECT 'Payment Not Made', NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM maintenance_stages WHERE stage_name = 'Payment Not Made');

-- 2) Ensure transition from 'Requisition Raised' -> 'Payment Not Made'
INSERT INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT fs.id, ts.id, 1
FROM (SELECT id FROM maintenance_stages WHERE stage_name = 'Requisition Raised' LIMIT 1) fs
CROSS JOIN (SELECT id FROM maintenance_stages WHERE stage_name = 'Payment Not Made' LIMIT 1) ts
WHERE NOT EXISTS (
  SELECT 1 FROM stage_transitions st WHERE st.from_stage_id = fs.id AND st.to_stage_id = ts.id
);

-- 3) Ensure self-transition (idempotent) for 'Payment Not Made' if desired
INSERT INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT s.id, s.id, 1
FROM (SELECT id FROM maintenance_stages WHERE stage_name = 'Payment Not Made' LIMIT 1) s
WHERE NOT EXISTS (
  SELECT 1 FROM stage_transitions st WHERE st.from_stage_id = s.id AND st.to_stage_id = s.id
);

-- 4) Grant action permission for roles 'Finance Manager' and 'HOD' on the new stage
INSERT INTO role_stage_permissions (role_id, stage_id, can_act)
SELECT r.id, s.id, 1
FROM roles r
CROSS JOIN (SELECT id FROM maintenance_stages WHERE stage_name = 'Payment Not Made' LIMIT 1) s
WHERE r.name IN ('Finance Manager', 'HOD')
  AND NOT EXISTS (
    SELECT 1 FROM role_stage_permissions rsp WHERE rsp.role_id = r.id AND rsp.stage_id = s.id
  );

COMMIT;

-- Notes:
-- - Table/column names are inferred from the application; please verify they match your schema.
-- - Run this migration against your dev/test DB first.
-- - If your migrations system expects a different format, adapt accordingly.