-- Migration 017: Update Payment Methods
-- Date: 2026-01-14
-- Description: 
--   Updates payment provider enum to support CASH, CARD, MPESA.
--   Current values: PAYSTACK, CASH, MOBILE, BANK
--   New values: CASH, CARD, MPESA
--   Note: This migration changes the ENUM values. Existing data may need migration.

-- ============================================
-- Update payment provider enum
-- ============================================

-- First, alter the column to use new ENUM values (temporarily allow old values)
-- We'll use a MODIFY COLUMN to change the ENUM, which will allow existing values temporarily
-- Then update the data, then modify again to enforce new values only

-- Step 1: Change ENUM to include both old and new values
ALTER TABLE `payments` 
MODIFY COLUMN `provider` ENUM('PAYSTACK', 'CASH', 'MOBILE', 'BANK', 'CARD', 'MPESA') NOT NULL;

-- Step 2: Update existing data to map to new values
-- MOBILE -> MPESA
UPDATE `payments` 
SET `provider` = 'MPESA' 
WHERE `provider` = 'MOBILE';

-- PAYSTACK -> CARD (if any exist)
UPDATE `payments` 
SET `provider` = 'CARD' 
WHERE `provider` = 'PAYSTACK';

-- BANK -> CARD (if any exist)
UPDATE `payments` 
SET `provider` = 'CARD' 
WHERE `provider` = 'BANK';

-- CASH remains CASH (no change needed)

-- Step 3: Now modify ENUM to only allow new values
ALTER TABLE `payments` 
MODIFY COLUMN `provider` ENUM('CASH', 'CARD', 'MPESA') NOT NULL 
COMMENT 'Payment provider (CASH, CARD, MPESA)';

-- Verify the changes
SELECT 'Migration 017 completed: Payment methods updated' AS status;
SELECT DISTINCT `provider` FROM `payments` AS 'Current payment providers';
