-- Users table (extends Supabase auth)
create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  full_name text,
  role text check (role in ('manager','cleaner','client')) default 'cleaner',
  location_id uuid,
  created_at timestamptz default now()
);
-- Locations / contracts
create table public.locations (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  address text,
  status text default 'active',
  monthly_rate numeric,
  created_at timestamptz default now()
);
-- Clock-in records
create table public.clock_ins (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.profiles(id),
  location_id uuid references public.locations(id),
  clocked_in_at timestamptz default now(),
  clocked_out_at timestamptz,
  scheduled_start timestamptz,
  status text -- 'early','on_time','late'
);
-- Tasks
create table public.tasks (
  id uuid default gen_random_uuid() primary key,
  location_id uuid references public.locations(id),
  room_name text,
  task_text text,
  frequency text default 'daily',
  is_general boolean default false
);
-- Completed tasks per shift
create table public.task_completions (
  id uuid default gen_random_uuid() primary key,
  task_id uuid references public.tasks(id),
  user_id uuid references public.profiles(id),
  completed_at timestamptz default now()
);
-- Manager todos
create table public.manager_todos (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.profiles(id),
  title text,
  client_name text,
  cadence text default 'weekly',
  done boolean default false,
  created_at timestamptz default now()
);
-- Issues
create table public.issues (
  id uuid default gen_random_uuid() primary key,
  location_id uuid references public.locations(id),
  reported_by uuid references public.profiles(id),
  title text,
  description text,
  severity text default 'normal',
  status text default 'open',
  created_at timestamptz default now()
);
-- Enable row level security
alter table public.profiles enable row level security;
alter table public.locations enable row level security;
alter table public.clock_ins enable row level security;
alter table public.tasks enable row level security;
alter table public.task_completions enable row level security;
alter table public.manager_todos enable row level security;
alter table public.issues enable row level security;
-- Basic policies (managers see all, cleaners see their location)
create policy "Managers see all" on public.profiles for all using (true);
create policy "Managers see all locations" on public.locations for all using (true);
create policy "Managers see all clock-ins" on public.clock_ins for all using (true);
create policy "All see tasks" on public.tasks for all using (true);
create policy "All see completions" on public.task_completions for all using (true);
create policy "Managers see todos" on public.manager_todos for all using (true);
create policy "Managers see issues" on public.issues for all using (true);