-- Adds requisition/payment workflow visibility to maintenance jobs.
ALTER TABLE maintenance_jobs
  MODIFY COLUMN status ENUM(
    'Pending',
    'Approved',
    'Rejected',
    'In Progress',
    'Completed',
    'Suspended',
    'Cancelled',
    'Closed',
    'Paid',
    'Job Order Created',
    'Requisition Raised',
    'Payment Made'
  ) NULL DEFAULT 'Pending';

ALTER TABLE maintenance_jobs
  MODIFY COLUMN workflow_stage ENUM(
    'Complaint Logged',
    'Maintenance Review',
    'Work Order Raised',
    'RFQ Sent',
    'Quote Submitted',
    'Quote Vetted',
    'Job Order Approved',
    'Workshop In Progress',
    'Inspection',
    'Internal Control',
    'Closed',
    'Requisition Raised',
    'Payment Made',
    'Suspended',
    'Cancelled'
  ) NOT NULL DEFAULT 'Complaint Logged';

ALTER TABLE maintenance_jobs
  MODIFY COLUMN requisition_status ENUM(
    'Pending',
    'Raised',
    'Approved',
    'Rejected',
    'Paid',
    'Payment Made'
  ) NULL DEFAULT 'Pending';

UPDATE maintenance_jobs
SET requisition_status = 'Pending'
WHERE requisition_status IS NULL;

INSERT IGNORE INTO maintenance_stages (stage_name, stage_order, is_terminal, is_active) VALUES
('Requisition Raised', 115, 0, 1),
('Payment Made', 125, 1, 1);

UPDATE maintenance_stages
SET is_terminal = 0
WHERE stage_name = 'Closed';

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Closed' AND t.stage_name = 'Requisition Raised';

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Requisition Raised' AND t.stage_name IN ('Requisition Raised','Payment Made','Workshop In Progress');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Payment Made' AND t.stage_name = 'Payment Made';
