-- Run in Supabase SQL editor (once). Attendance tracking tables.

create table if not exists public.attendance_sessions (
  id uuid primary key default gen_random_uuid(),
  employee_id uuid not null references public.users(id) on delete cascade,
  date date not null,
  check_in_time timestamptz not null,
  check_out_time timestamptz,
  status text not null default 'checked_in',
  duration_minutes integer,
  screenshots_count integer not null default 0,
  project text,
  description text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.attendance_screenshots (
  id uuid primary key default gen_random_uuid(),
  attendance_session_id uuid not null references public.attendance_sessions(id) on delete cascade,
  employee_id uuid not null references public.users(id) on delete cascade,
  image_path text not null,
  captured_at timestamptz not null default now(),
  created_at timestamptz not null default now()
);

create index if not exists attendance_sessions_employee_idx
  on public.attendance_sessions (employee_id, date desc);

create index if not exists attendance_sessions_status_idx
  on public.attendance_sessions (status);

create index if not exists attendance_screenshots_session_idx
  on public.attendance_screenshots (attendance_session_id, captured_at asc);