-- Migration script to update cashflow transactions table
-- Changes transaction_date from DATE to DATETIME to support 24-hour timestamp format

-- Step 1: Add a temporary column to store the new datetime values
ALTER TABLE `{business_code}_cashflow_transactions` 
ADD COLUMN `transaction_date_new` DATETIME NULL AFTER `transaction_date`;

-- Step 2: Convert existing DATE values to DATETIME with default time (00:00:00)
UPDATE `{business_code}_cashflow_transactions` 
SET `transaction_date_new` = CONCAT(`transaction_date`, ' 00:00:00')
WHERE `transaction_date_new` IS NULL;

-- Step 3: Drop the old column and rename the new one
ALTER TABLE `{business_code}_cashflow_transactions` 
DROP COLUMN `transaction_date`,
CHANGE COLUMN `transaction_date_new` `transaction_date` DATETIME NOT NULL;

-- Step 4: Recreate the index on the new DATETIME column
ALTER TABLE `{business_code}_cashflow_transactions` 
ADD INDEX `idx_transaction_date` (`transaction_date`);

-- Note: This migration preserves all existing data by converting DATE values to DATETIME with 00:00:00 time
-- New transactions will use the actual transaction time with business timezone 