-- ============================================================
-- HOTSTOCK — Supabase sync + Auth setup
-- Run this ONCE in the Supabase Dashboard → SQL Editor → New query
-- (paste the whole file, then Run).
--
-- SAFE TO RE-RUN: it only creates tables/columns/functions/policies
-- that are missing (policies are dropped and recreated with the same
-- definitions). It never deletes, truncates or wipes any data.
--
-- All HOTSTOCK tables start with "hotstock_" so they sit alongside
-- the existing Inventory / Playback tables in the same GCP Props
-- Supabase project without clashing with them.
--
-- SECURITY MODEL (real Supabase Auth — there is NO admin code in the
-- HOTSTOCK page any more):
--   * Viewers (not signed in): can only READ shows/seasons/bins/props.
--   * Admins: sign in with an email/password account (Supabase Auth),
--     and must be listed in hotstock_admins. Only Admins can write,
--     and only Admins can see or manage Users/codes (hotstock_users).
--   * Row Level Security below enforces all of this on the server.
--
-- AFTER RUNNING THIS FILE, make the first Admin (one-time):
--   1. Supabase Dashboard → Authentication → Users → "Add user":
--      create the admin's email + password account.
--   2. Back in the SQL Editor, run this one line with their email:
--        insert into public.hotstock_admins (user_id, email)
--        select id, email from auth.users where email = 'ADMIN-EMAIL-HERE'
--        on conflict (user_id) do nothing;
--   3. That person can now tap Admin in HOTSTOCK and sign in.
--   Later Admins can be added the same way; Admins can also remove a
--   row from hotstock_admins to take Admin away (their Auth account
--   can also be deleted under Authentication → Users).
-- ============================================================

-- ---------- Shows ----------
create table if not exists public.hotstock_shows (
  id text primary key,
  name text not null default '',
  sort_order integer not null default 0,
  updated_at timestamptz not null default now()
);
alter table public.hotstock_shows add column if not exists name text not null default '';
alter table public.hotstock_shows add column if not exists sort_order integer not null default 0;
alter table public.hotstock_shows add column if not exists updated_at timestamptz not null default now();

-- ---------- Seasons ----------
create table if not exists public.hotstock_seasons (
  id text primary key,
  show_id text,
  show_name text not null default '',
  name text not null default '',
  sort_order integer not null default 0,
  updated_at timestamptz not null default now()
);
alter table public.hotstock_seasons add column if not exists show_id text;
alter table public.hotstock_seasons add column if not exists show_name text not null default '';
alter table public.hotstock_seasons add column if not exists name text not null default '';
alter table public.hotstock_seasons add column if not exists sort_order integer not null default 0;
alter table public.hotstock_seasons add column if not exists updated_at timestamptz not null default now();

-- ---------- Bins ----------
create table if not exists public.hotstock_bins (
  id text primary key,
  season_id text,
  name text not null default '',
  cat text not null default 'character',
  loc text not null default '',
  block_label text not null default '',
  notes text not null default '',
  sort_order integer not null default 0,
  updated_at timestamptz not null default now()
);
alter table public.hotstock_bins add column if not exists season_id text;
alter table public.hotstock_bins add column if not exists name text not null default '';
alter table public.hotstock_bins add column if not exists cat text not null default 'character';
alter table public.hotstock_bins add column if not exists loc text not null default '';
alter table public.hotstock_bins add column if not exists block_label text not null default '';
alter table public.hotstock_bins add column if not exists notes text not null default '';
alter table public.hotstock_bins add column if not exists sort_order integer not null default 0;
alter table public.hotstock_bins add column if not exists updated_at timestamptz not null default now();

-- ---------- Props ----------
create table if not exists public.hotstock_props (
  id text primary key,
  bin_id text,
  season_id text,
  name text not null default '',
  description text not null default '',
  qty integer not null default 1,
  condition text not null default 'Good',
  hot boolean not null default false,
  photo text,
  sort_order integer not null default 0,
  updated_at timestamptz not null default now()
);
alter table public.hotstock_props add column if not exists bin_id text;
alter table public.hotstock_props add column if not exists season_id text;
alter table public.hotstock_props add column if not exists name text not null default '';
alter table public.hotstock_props add column if not exists description text not null default '';
alter table public.hotstock_props add column if not exists qty integer not null default 1;
alter table public.hotstock_props add column if not exists condition text not null default 'Good';
alter table public.hotstock_props add column if not exists hot boolean not null default false;
alter table public.hotstock_props add column if not exists photo text;
alter table public.hotstock_props add column if not exists sort_order integer not null default 0;
alter table public.hotstock_props add column if not exists updated_at timestamptz not null default now();

-- ---------- Users / codes (Admin-only under RLS) ----------
create table if not exists public.hotstock_users (
  id text primary key,
  name text not null default '',
  code text not null default '',
  show_id text,
  show_name text not null default '',
  sort_order integer not null default 0,
  updated_at timestamptz not null default now()
);
alter table public.hotstock_users add column if not exists name text not null default '';
alter table public.hotstock_users add column if not exists code text not null default '';
alter table public.hotstock_users add column if not exists show_id text;
alter table public.hotstock_users add column if not exists show_name text not null default '';
alter table public.hotstock_users add column if not exists sort_order integer not null default 0;
alter table public.hotstock_users add column if not exists updated_at timestamptz not null default now();

-- ---------- Admins (who is allowed to be a HOTSTOCK Admin) ----------
-- One row per Admin, keyed by their Supabase Auth user id.
create table if not exists public.hotstock_admins (
  user_id uuid primary key,
  email text not null default '',
  created_at timestamptz not null default now()
);
alter table public.hotstock_admins add column if not exists email text not null default '';
alter table public.hotstock_admins add column if not exists created_at timestamptz not null default now();

-- Am I a HOTSTOCK Admin? (used by every policy below)
create or replace function public.hotstock_is_admin()
returns boolean
language sql
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.hotstock_admins a where a.user_id = auth.uid()
  );
$$;

-- ---------- Row Level Security ----------
alter table public.hotstock_shows enable row level security;
alter table public.hotstock_seasons enable row level security;
alter table public.hotstock_bins enable row level security;
alter table public.hotstock_props enable row level security;
alter table public.hotstock_users enable row level security;
alter table public.hotstock_admins enable row level security;

-- Shows / Seasons / Bins / Props:
--   everyone (signed in or not) can READ; only Admins can WRITE.
drop policy if exists "hotstock anon all" on public.hotstock_shows;
drop policy if exists "hotstock read all" on public.hotstock_shows;
drop policy if exists "hotstock admin write" on public.hotstock_shows;
create policy "hotstock read all" on public.hotstock_shows
  for select to anon, authenticated using (true);
create policy "hotstock admin write" on public.hotstock_shows
  for all to authenticated using (public.hotstock_is_admin()) with check (public.hotstock_is_admin());

drop policy if exists "hotstock anon all" on public.hotstock_seasons;
drop policy if exists "hotstock read all" on public.hotstock_seasons;
drop policy if exists "hotstock admin write" on public.hotstock_seasons;
create policy "hotstock read all" on public.hotstock_seasons
  for select to anon, authenticated using (true);
create policy "hotstock admin write" on public.hotstock_seasons
  for all to authenticated using (public.hotstock_is_admin()) with check (public.hotstock_is_admin());

drop policy if exists "hotstock anon all" on public.hotstock_bins;
drop policy if exists "hotstock read all" on public.hotstock_bins;
drop policy if exists "hotstock admin write" on public.hotstock_bins;
create policy "hotstock read all" on public.hotstock_bins
  for select to anon, authenticated using (true);
create policy "hotstock admin write" on public.hotstock_bins
  for all to authenticated using (public.hotstock_is_admin()) with check (public.hotstock_is_admin());

drop policy if exists "hotstock anon all" on public.hotstock_props;
drop policy if exists "hotstock read all" on public.hotstock_props;
drop policy if exists "hotstock admin write" on public.hotstock_props;
create policy "hotstock read all" on public.hotstock_props
  for select to anon, authenticated using (true);
create policy "hotstock admin write" on public.hotstock_props
  for all to authenticated using (public.hotstock_is_admin()) with check (public.hotstock_is_admin());

-- Users/codes: Admins only — no read, no write for anyone else,
-- including signed-in non-admin accounts and anonymous viewers.
drop policy if exists "hotstock anon all" on public.hotstock_users;
drop policy if exists "hotstock admins only" on public.hotstock_users;
create policy "hotstock admins only" on public.hotstock_users
  for all to authenticated using (public.hotstock_is_admin()) with check (public.hotstock_is_admin());

-- Admins list: a signed-in person can read their OWN row (that is how
-- the app checks "am I an admin?"); only Admins can add/remove rows.
-- (The SQL Editor runs as the project owner and bypasses RLS, which is
-- how the very first Admin row gets added — see the header notes.)
drop policy if exists "hotstock admins self read" on public.hotstock_admins;
drop policy if exists "hotstock admins manage" on public.hotstock_admins;
create policy "hotstock admins self read" on public.hotstock_admins
  for select to authenticated using (user_id = auth.uid() or public.hotstock_is_admin());
create policy "hotstock admins manage" on public.hotstock_admins
  for all to authenticated using (public.hotstock_is_admin()) with check (public.hotstock_is_admin());

-- Done. Next: fill in assets/hotstock-config.js with your Project URL
-- and anon key, create the first Admin (header notes), upload the
-- HOTSTOCK files to Hostinger, and hard refresh.