-- Migration script to update payment methods in expenses table
-- This script should be run for each branch

-- Step 1: Add payment_method_id column
ALTER TABLE `{branch_code}_expenses` 
ADD COLUMN `payment_method_id` INT UNSIGNED NULL AFTER `payment_method`;

-- Step 2: Update payment_method_id based on existing payment_method values
UPDATE `{branch_code}_expenses` e
JOIN `{branch_code}_payment_methods` p ON LOWER(e.payment_method) = LOWER(p.method_name)
SET e.payment_method_id = p.method_id;

-- Step 3: Make payment_method_id NOT NULL
ALTER TABLE `{branch_code}_expenses` 
MODIFY COLUMN `payment_method_id` INT UNSIGNED NOT NULL;

-- Step 4: Add foreign key constraint
ALTER TABLE `{branch_code}_expenses`
ADD CONSTRAINT `{branch_code}_expenses_payment_method_fk` 
FOREIGN KEY (`payment_method_id`) REFERENCES `{branch_code}_payment_methods` (`method_id`);

-- Step 5: Drop the old payment_method column
ALTER TABLE `{branch_code}_expenses` 
DROP COLUMN `payment_method`; 