-- Sponzza — per-user storage. Run once in Supabase → SQL Editor → New query → Run.
-- Each signed-in user gets their own private rows (row-level security enforces it).

create extension if not exists "pgcrypto";

-- Everything a user interacts with: roles viewed, applied, sponsor status, etc.
create table if not exists public.activity (
  id            uuid primary key default gen_random_uuid(),
  user_id       uuid not null,
  company       text,
  job_title     text,
  job_url       text,
  sponsor_status text,                 -- 'sponsor' | 'not_found' | null
  soc_code      text,
  action        text default 'viewed', -- 'viewed' | 'applied' | 'saved'
  created_at    timestamptz default now(),
  updated_at    timestamptz default now(),
  unique (user_id, job_url)
);

alter table public.activity enable row level security;

drop policy if exists "activity_select" on public.activity;
drop policy if exists "activity_insert" on public.activity;
drop policy if exists "activity_update" on public.activity;
create policy "activity_select" on public.activity for select using (auth.uid() = user_id);
create policy "activity_insert" on public.activity for insert with check (auth.uid() = user_id);
create policy "activity_update" on public.activity for update using (auth.uid() = user_id) with check (auth.uid() = user_id);

-- Private resume storage, one folder per user (folder name = user id).
insert into storage.buckets (id, name, public)
values ('resumes', 'resumes', false)
on conflict (id) do nothing;

drop policy if exists "resume_rw" on storage.objects;
create policy "resume_rw" on storage.objects
  for all
  using  (bucket_id = 'resumes' and auth.uid()::text = (storage.foldername(name))[1])
  with check (bucket_id = 'resumes' and auth.uid()::text = (storage.foldername(name))[1]);
