-- Run in Supabase SQL editor (once). Leave applications for Office Management System.

create table if not exists public.leave_applications (
  id uuid primary key default gen_random_uuid(),
  employee_id uuid not null references public.users(id) on delete cascade,
  title text not null,
  reason text not null,
  leave_category text not null default 'casual',
  description text,
  start_date date not null,
  end_date date not null,
  status text not null default 'pending',
  reviewed_by uuid references public.users(id) on delete set null,
  reviewed_at timestamptz,
  review_note text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint leave_applications_status_check check (status in ('pending', 'approved', 'rejected')),
  constraint leave_applications_category_check check (leave_category in ('sick', 'casual')),
  constraint leave_applications_dates_check check (end_date >= start_date)
);

alter table public.leave_applications
  add column if not exists leave_category text not null default 'casual';

create index if not exists leave_applications_employee_idx
  on public.leave_applications (employee_id, created_at desc);

create index if not exists leave_applications_status_idx
  on public.leave_applications (status, created_at desc);