-- Cabinet memberships for sharing access

create table if not exists public.cabinet_memberships (
    id uuid default uuid_generate_v4() primary key,
    cabinet_id uuid not null references public.file_cabinets(id) on delete cascade,
    profile_id uuid not null references public.profiles(id) on delete cascade,
    role text not null check (role in ('owner','editor','viewer')),
    created_at timestamp with time zone default timezone('utc'::text, now()),
    updated_at timestamp with time zone default timezone('utc'::text, now()),
    unique(cabinet_id, profile_id)
);

create index if not exists idx_cabinet_memberships_cabinet on public.cabinet_memberships(cabinet_id);
create index if not exists idx_cabinet_memberships_profile on public.cabinet_memberships(profile_id);

-- Updated at trigger
drop trigger if exists trg_cabinet_memberships_updated_at on public.cabinet_memberships;
create trigger trg_cabinet_memberships_updated_at
    before update on public.cabinet_memberships
    for each row execute function public.handle_updated_at();

-- RLS and policies
alter table if exists public.cabinet_memberships enable row level security;

-- Members can select their own memberships; cabinet owners can also see memberships
drop policy if exists cm_read_self_or_owner on public.cabinet_memberships;
create policy cm_read_self_or_owner on public.cabinet_memberships for select to authenticated
using (
  profile_id = auth.uid() or exists (
    select 1 from public.file_cabinets c where c.id = cabinet_memberships.cabinet_id and c.user_id = auth.uid()
  )
);

-- Cabinet owners can insert memberships
drop policy if exists cm_insert_by_owner on public.cabinet_memberships;
create policy cm_insert_by_owner on public.cabinet_memberships for insert to authenticated
with check (exists (
  select 1 from public.file_cabinets c where c.id = cabinet_memberships.cabinet_id and c.user_id = auth.uid()
));

-- Cabinet owners can update memberships (e.g., role)
drop policy if exists cm_update_by_owner on public.cabinet_memberships;
create policy cm_update_by_owner on public.cabinet_memberships for update to authenticated
using (exists (
  select 1 from public.file_cabinets c where c.id = cabinet_memberships.cabinet_id and c.user_id = auth.uid()
))
with check (exists (
  select 1 from public.file_cabinets c where c.id = cabinet_memberships.cabinet_id and c.user_id = auth.uid()
));

-- Cabinet owners can delete memberships
drop policy if exists cm_delete_by_owner on public.cabinet_memberships;
create policy cm_delete_by_owner on public.cabinet_memberships for delete to authenticated
using (exists (
  select 1 from public.file_cabinets c where c.id = cabinet_memberships.cabinet_id and c.user_id = auth.uid()
));

-- Extend access to cabinets/files for members (after table exists)
drop policy if exists "User can access cabinets via membership" on public.file_cabinets;
create policy "User can access cabinets via membership" on public.file_cabinets for select to authenticated
using (exists (
  select 1 from public.cabinet_memberships m
  where m.cabinet_id = file_cabinets.id and m.profile_id = auth.uid()
));

drop policy if exists "User can access files via membership" on public.files;
create policy "User can access files via membership" on public.files for select to authenticated
using (exists (
  select 1 from public.cabinet_memberships m
  where m.cabinet_id = files.cabinet_id and m.profile_id = auth.uid()
));