-- Migration script to convert expense_date from DATE to DATETIME
-- This script should be run for each business to update existing expense records

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

-- Step 2: Convert existing DATE values to DATETIME with business timezone
-- This assumes the business timezone is set correctly in the business table
UPDATE `{business_code}_expenses_transactions` 
SET `expense_date_new` = CONCAT(expense_date, ' 00:00:00')
WHERE `expense_date_new` IS NULL;

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

-- Step 4: Update the index to work with DATETIME
DROP INDEX `idx_expenses_transactions_date` ON `{business_code}_expenses_transactions`;
CREATE INDEX `idx_expenses_transactions_date` ON `{business_code}_expenses_transactions` (`expense_date`);

-- Note: After running this migration, new expenses will use the actual transaction time with business timezone
-- Existing expenses will have their date converted to datetime at 00:00:00 in business timezone 