-- Migration script to convert time fields to store as strings
-- This script handles existing records by converting TIMESTAMP to VARCHAR format
-- Format: YYYY-MM-DD HH:MM:SS (local time, no timezone conversion)

-- Step 1: Add temporary columns for string storage
ALTER TABLE attendance 
ADD COLUMN clock_in_time_str VARCHAR(19) NULL,
ADD COLUMN clock_out_time_str VARCHAR(19) NULL;

-- Step 2: Migrate existing data from TIMESTAMP to string format
-- Convert existing TIMESTAMP values to string format (YYYY-MM-DD HH:MM:SS)
UPDATE attendance 
SET clock_in_time_str = DATE_FORMAT(clock_in_time, '%Y-%m-%d %H:%i:%s')
WHERE clock_in_time IS NOT NULL;

UPDATE attendance 
SET clock_out_time_str = DATE_FORMAT(clock_out_time, '%Y-%m-%d %H:%i:%s')
WHERE clock_out_time IS NOT NULL;

-- Step 3: Drop old TIMESTAMP columns
ALTER TABLE attendance 
DROP COLUMN clock_in_time,
DROP COLUMN clock_out_time;

-- Step 4: Rename new columns to original names
ALTER TABLE attendance 
CHANGE COLUMN clock_in_time_str clock_in_time VARCHAR(19) NULL,
CHANGE COLUMN clock_out_time_str clock_out_time VARCHAR(19) NULL;

-- Step 5: Do the same for work_sessions table (breaks)
ALTER TABLE work_sessions 
ADD COLUMN start_time_str VARCHAR(19) NULL,
ADD COLUMN end_time_str VARCHAR(19) NULL;

UPDATE work_sessions 
SET start_time_str = DATE_FORMAT(start_time, '%Y-%m-%d %H:%i:%s')
WHERE start_time IS NOT NULL;

UPDATE work_sessions 
SET end_time_str = DATE_FORMAT(end_time, '%Y-%m-%d %H:%i:%s')
WHERE end_time IS NOT NULL;

ALTER TABLE work_sessions 
DROP COLUMN start_time,
DROP COLUMN end_time;

ALTER TABLE work_sessions 
CHANGE COLUMN start_time_str start_time VARCHAR(19) NULL,
CHANGE COLUMN end_time_str end_time VARCHAR(19) NULL;

-- Note: This migration preserves all existing data by converting TIMESTAMP to string format
-- The format used is YYYY-MM-DD HH:MM:SS which matches the local time format

