SELECT
    id,
    user_id,
    worksite_id,
    session_date,
    clock_in_time,
    clock_out_time,
    session_status,
    closed_by_type,
    close_reason
FROM work_sessions
WHERE session_status = 'OPEN'
  AND session_date < CURDATE()
ORDER BY session_date DESC, id DESC;

SELECT
    id,
    user_id,
    session_date,
    clock_in_time,
    clock_out_time,
    session_status,
    closed_by_type,
    close_reason
FROM work_sessions
WHERE closed_by_type = 'auto'
ORDER BY session_date DESC, id DESC
LIMIT 100;

UPDATE work_sessions
SET
    clock_out_time = DATE_ADD(session_date, INTERVAL 1 DAY),
    session_status = 'CLOSED',
    closed_by_type = 'auto',
    closed_by_id = NULL,
    close_reason = 'Auto-closed at midnight'
WHERE session_status = 'OPEN'
  AND (clock_out_time IS NULL OR clock_out_time = '0000-00-00 00:00:00')
  AND session_date < CURDATE();