-- Move pickup workflow state source from legacy `status` to `current_state`.
-- Run section 1 first. Run section 2 only after the application no longer reads/writes `pickup.status`.

-- =========================
-- 1. Safe transition
-- =========================

ALTER TABLE `pickup`
  ADD COLUMN `current_state` VARCHAR(64) DEFAULT NULL AFTER `status`;

UPDATE `pickup`
SET `current_state` = CASE
  WHEN `status` = 'PENDIENTE' THEN 'PENDING'
  WHEN `status` = 'PROGRAMADO' THEN 'SCHEDULED'
  WHEN `status` = 'REPROGRAMADO' THEN 'RESCHEDULED'
  WHEN `status` = 'RECOGIDO' THEN 'PICKED_UP'
  WHEN `status` = 'EN_TRANSITO' THEN 'IN_TRANSIT'
  WHEN `status` = 'RETARDADO' THEN 'DELAYED'
  WHEN `status` = 'ENTREGADO' THEN 'DELIVERED'
  WHEN `status` = 'ANULADO' THEN 'REJECTED'
  WHEN `status` = 'PROCESADO' THEN 'PROCESSED'
  ELSE `current_state`
END
WHERE `current_state` IS NULL
  AND `status` IS NOT NULL;

UPDATE `pickup`
SET `current_state` = 'PENDING'
WHERE `current_state` IS NULL;

-- Legacy consistency: pickups already assigned to a route should behave as scheduled.
UPDATE `pickup`
SET `current_state` = 'SCHEDULED'
WHERE `current_state` = 'PENDING'
  AND `listpickup_id` IS NOT NULL;

UPDATE `event_definition`
SET `target_state` = CASE
  WHEN `target_state` = 'PENDIENTE' THEN 'PENDING'
  WHEN `target_state` = 'PROGRAMADO' THEN 'SCHEDULED'
  WHEN `target_state` = 'REPROGRAMADO' THEN 'RESCHEDULED'
  WHEN `target_state` = 'RECOGIDO' THEN 'PICKED_UP'
  WHEN `target_state` = 'EN_TRANSITO' THEN 'IN_TRANSIT'
  WHEN `target_state` = 'RETARDADO' THEN 'DELAYED'
  WHEN `target_state` = 'ENTREGADO' THEN 'DELIVERED'
  WHEN `target_state` = 'ANULADO' THEN 'REJECTED'
  WHEN `target_state` = 'PROCESADO' THEN 'PROCESSED'
  ELSE `target_state`
END
WHERE `entity_type` = 'pickup';

UPDATE `tenant_workflow_rule`
SET `allowed_from_states` = JSON_ARRAY('PENDING')
WHERE `entity_type` = 'pickup' AND `event_code` = 'pickup_created';

UPDATE `tenant_workflow_rule`
SET `allowed_from_states` = JSON_ARRAY('PENDING', 'RESCHEDULED')
WHERE `entity_type` = 'pickup' AND `event_code` = 'pickup_scheduled';

UPDATE `tenant_workflow_rule`
SET `allowed_from_states` = JSON_ARRAY('PENDING', 'SCHEDULED', 'RESCHEDULED', 'IN_TRANSIT', 'REJECTED')
WHERE `entity_type` = 'pickup' AND `event_code` = 'pickup_rescheduled';

UPDATE `tenant_workflow_rule`
SET `allowed_from_states` = JSON_ARRAY('IN_TRANSIT', 'REJECTED')
WHERE `entity_type` = 'pickup' AND `event_code` = 'pickup_picked_up';

UPDATE `tenant_workflow_rule`
SET `allowed_from_states` = JSON_ARRAY('PENDING', 'SCHEDULED', 'RESCHEDULED')
WHERE `entity_type` = 'pickup' AND `event_code` = 'pickup_in_transit';

UPDATE `tenant_workflow_rule`
SET `allowed_from_states` = JSON_ARRAY('IN_TRANSIT', 'PICKED_UP', 'REJECTED')
WHERE `entity_type` = 'pickup' AND `event_code` = 'pickup_delivered';

UPDATE `tenant_workflow_rule`
SET `allowed_from_states` = JSON_ARRAY('IN_TRANSIT', 'PICKED_UP')
WHERE `entity_type` = 'pickup' AND `event_code` = 'pickup_rejected';

UPDATE `tenant_workflow_rule`
SET `allowed_from_states` = JSON_ARRAY('PICKED_UP', 'DELIVERED')
WHERE `entity_type` = 'pickup' AND `event_code` = 'pickup_processed';

DELETE FROM `tenant_workflow_rule`
WHERE `entity_type` = 'pickup' AND `event_code` IN ('pickup_delayed', 'pickup_cancelled');

DELETE FROM `event_definition`
WHERE `entity_type` = 'pickup' AND `code` IN ('pickup_delayed', 'pickup_cancelled');

ALTER TABLE `pickup`
  ADD INDEX `IDX_PICKUP_CURRENT_STATE` (`current_state`);

ALTER TABLE `pickup`
  MODIFY COLUMN `current_state` VARCHAR(64) NOT NULL;

-- =========================
-- 2. Final cleanup
-- =========================
-- Uncomment only when the codebase is fully migrated away from `pickup.status`.

-- ALTER TABLE `pickup`
--   DROP COLUMN `status`;