-- CellarSheet: Supabase schema
-- Postgres + RLS for a multi-venue wine pitch-card app.
-- Run in the Supabase SQL editor on a fresh project.
--
-- Model: a venue (restaurant) has members (managers and servers), invoices
-- (one per uploaded distributor invoice file), and wines (one row per parsed
-- line item). Managers see costs; servers see selling prices only, enforced
-- here, not in the client.

create extension if not exists "pgcrypto";

-- ── Tables ────────────────────────────────────────────────────────────────

create table public.venues (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  created_at timestamptz not null default now()
);

create type public.member_role as enum ('manager', 'server');

create table public.venue_members (
  venue_id   uuid not null references public.venues (id) on delete cascade,
  user_id    uuid not null references auth.users (id) on delete cascade,
  role       public.member_role not null default 'server',
  created_at timestamptz not null default now(),
  primary key (venue_id, user_id)
);

create table public.invoices (
  id             uuid primary key default gen_random_uuid(),
  venue_id       uuid not null references public.venues (id) on delete cascade,
  distributor    text not null default '',
  invoice_number text not null default '',
  invoice_date   date,
  file_path      text not null,            -- storage object in the invoices bucket
  file_kind      text not null check (file_kind in ('image', 'pdf')),
  status         text not null default 'processing'
                 check (status in ('processing', 'review', 'published', 'failed')),
  total_cost     numeric(10, 2),
  created_at     timestamptz not null default now()
);

create table public.wines (
  id           uuid primary key default gen_random_uuid(),
  venue_id     uuid not null references public.venues (id) on delete cascade,
  invoice_id   uuid references public.invoices (id) on delete set null,
  name         text not null,
  vintage      text not null default 'NV',
  varietal     text not null default '',
  region       text not null default '',
  style        text not null check (style in ('red', 'white', 'sparkling', 'rose', 'dessert')),
  qty          integer not null default 0,
  unit_cost    numeric(10, 2) not null,    -- manager-only (see grants below)
  bottle_price numeric(10, 2) not null,
  glass_price  numeric(10, 2),             -- null = not poured by the glass
  btg          boolean not null default false,
  taste        text not null default '',
  palate       jsonb not null default '{"body":3,"acidity":3,"tannin":2,"sweetness":1}',
  pairings     text[] not null default '{}',
  pitch        text not null default '',
  published    boolean not null default false,
  created_at   timestamptz not null default now()
);

create index wines_venue_published_idx on public.wines (venue_id, published);
create index invoices_venue_idx on public.invoices (venue_id);

-- ── Membership helpers ────────────────────────────────────────────────────

create or replace function public.is_member (v uuid)
returns boolean language sql stable security definer set search_path = public as $$
  select exists (
    select 1 from venue_members
    where venue_id = v and user_id = auth.uid()
  );
$$;

create or replace function public.is_manager (v uuid)
returns boolean language sql stable security definer set search_path = public as $$
  select exists (
    select 1 from venue_members
    where venue_id = v and user_id = auth.uid() and role = 'manager'
  );
$$;

-- ── Row-level security ────────────────────────────────────────────────────

alter table public.venues        enable row level security;
alter table public.venue_members enable row level security;
alter table public.invoices      enable row level security;
alter table public.wines         enable row level security;

create policy "members read their venue"
  on public.venues for select
  using (is_member(id));

create policy "members see the roster"
  on public.venue_members for select
  using (is_member(venue_id));

create policy "managers manage the roster"
  on public.venue_members for all
  using (is_manager(venue_id))
  with check (is_manager(venue_id));

create policy "managers read invoices"
  on public.invoices for select
  using (is_manager(venue_id));

create policy "managers write invoices"
  on public.invoices for all
  using (is_manager(venue_id))
  with check (is_manager(venue_id));

-- Wines: every member can read rows for their venue; only managers write.
-- The n8n workflow writes with the service-role key and bypasses RLS.
create policy "members read wines"
  on public.wines for select
  using (is_member(venue_id));

create policy "managers write wines"
  on public.wines for all
  using (is_manager(venue_id))
  with check (is_manager(venue_id));

-- ── Column privacy: servers never see cost ────────────────────────────────
-- RLS filters rows; column grants hide unit_cost from the client role
-- entirely. The manager UI reads costs through the floor_list view's
-- manager-only sibling or the service role in server-side code.

revoke select on public.wines from authenticated;
grant select (
  id, venue_id, invoice_id, name, vintage, varietal, region, style,
  bottle_price, glass_price, btg, taste, palate, pairings, pitch,
  published, created_at
) on public.wines to authenticated;

-- Managers read costs via this security-definer function (RLS-checked inside).
create or replace function public.manager_wine_costs (v uuid)
returns table (wine_id uuid, unit_cost numeric, qty integer)
language sql stable security definer set search_path = public as $$
  select id, unit_cost, qty from wines
  where venue_id = v and is_manager(v);
$$;

-- ── The server's phone: published list only ───────────────────────────────

create view public.floor_list
with (security_invoker = true) as
  select id, venue_id, name, vintage, varietal, region, style,
         bottle_price, glass_price, btg, taste, palate, pairings, pitch
  from public.wines
  where published;

-- ── Storage: the invoice dropzone bucket ──────────────────────────────────

insert into storage.buckets (id, name, public) values ('invoices', 'invoices', false);

create policy "managers upload invoices"
  on storage.objects for insert to authenticated
  with check (
    bucket_id = 'invoices'
    and is_manager(((storage.foldername(name))[1])::uuid)  -- path: <venue_id>/<file>
  );

create policy "managers read invoice files"
  on storage.objects for select to authenticated
  using (
    bucket_id = 'invoices'
    and is_manager(((storage.foldername(name))[1])::uuid)
  );

-- ── Realtime: publishes land on servers' phones instantly ─────────────────

alter publication supabase_realtime add table public.wines;
