-- Migration: Convert time_clock_aggregates from minutes to hours
-- Date: 2025-10-08
-- Description: Changes time tracking from minutes to hours for better readability

-- Add new hour columns
ALTER TABLE time_clock_aggregates 
ADD COLUMN IF NOT EXISTS total_net_hours NUMERIC(10,2),
ADD COLUMN IF NOT EXISTS total_overtime_hours NUMERIC(10,2),
ADD COLUMN IF NOT EXISTS total_regular_hours NUMERIC(10,2),
ADD COLUMN IF NOT EXISTS total_payable_hours NUMERIC(10,2);

-- Migrate existing data from minutes to hours (if any)
UPDATE time_clock_aggregates 
SET 
    total_net_hours = ROUND(CAST(total_net_minutes / 60.0 AS NUMERIC), 2),
    total_overtime_hours = ROUND(CAST(total_overtime_minutes / 60.0 AS NUMERIC), 2),
    total_regular_hours = ROUND(CAST(total_regular_minutes / 60.0 AS NUMERIC), 2),
    total_payable_hours = ROUND(CAST(total_payable_minutes / 60.0 AS NUMERIC), 2)
WHERE total_net_minutes IS NOT NULL;

-- Drop old minute columns
ALTER TABLE time_clock_aggregates 
DROP COLUMN IF EXISTS total_net_minutes,
DROP COLUMN IF EXISTS total_overtime_minutes,
DROP COLUMN IF EXISTS total_regular_minutes,
DROP COLUMN IF EXISTS total_payable_minutes;

-- Update column comments
COMMENT ON COLUMN time_clock_aggregates.total_net_hours IS 'Total worked hours (gross - breaks)';
COMMENT ON COLUMN time_clock_aggregates.total_overtime_hours IS 'Hours beyond regular work hours';
COMMENT ON COLUMN time_clock_aggregates.total_regular_hours IS 'Regular work hours (up to standard)';
COMMENT ON COLUMN time_clock_aggregates.total_payable_hours IS 'Total hours to be paid (may include premiums)';
