-- ============================================================
-- Time Tracker App - Automatic Session Closure at Midnight
-- Run this SQL in your Supabase SQL Editor
-- ============================================================

-- Check if pg_cron extension is available
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Function to close active sessions at midnight
-- This will only close sessions that are currently running (end_time IS NULL)
-- and will preserve all session history
CREATE OR REPLACE FUNCTION close_active_sessions_at_midnight()
RETURNS void AS $$
DECLARE
  closed_count INTEGER;
BEGIN
  -- Update all active sessions (where end_time is NULL) 
  -- Set end_time to today at 00:00:00 (midnight)
  -- This closes only currently active sessions, preserving all history
  UPDATE timers 
  SET end_time = DATE_TRUNC('day', NOW()),
      duration_ms = EXTRACT(EPOCH FROM (DATE_TRUNC('day', NOW()) - start_time)) * 1000
  WHERE end_time IS NULL 
    AND start_time < DATE_TRUNC('day', NOW());
  
  -- Get the count of closed sessions for the notice
  GET DIAGNOSTICS closed_count = ROW_COUNT;
  
  RAISE NOTICE 'Closed % active sessions at midnight (history preserved)', closed_count;
END;
$$ LANGUAGE plpgsql;

-- Schedule the function to run daily at midnight (00:00)
-- This will run in the timezone of your Supabase project
SELECT cron.schedule(
  'close-active-sessions-at-midnight', -- job name
  '0 0 * * *',                         -- cron expression (at 00:00 every day)
  $$SELECT close_active_sessions_at_midnight()$$
);

-- To manually test the function, you can run:
-- SELECT close_active_sessions_at_midnight();

-- To view all cron jobs:
-- SELECT * FROM cron.job;

-- To unschedule this job:
-- SELECT cron.unschedule('close-active-sessions-at-midnight');