-- ============================================================
-- Stock Run — Supabase database setup
-- Run this ONCE in your Supabase project:
--   Supabase dashboard  →  SQL Editor  →  New query  →  paste  →  Run
-- ============================================================

-- 1. Tables (each row stores a JSON record in "data")
create table if not exists sr_users (
  id          text primary key,
  data        jsonb not null,
  created_at  timestamptz default now()
);

create table if not exists sr_orders (
  id          text primary key,
  data        jsonb not null,
  created_at  timestamptz default now()
);

create table if not exists sr_receipts (
  id          text primary key,
  data        jsonb not null,
  created_at  timestamptz default now()
);

-- Customer product catalog (stock on hand, prices, barcodes)
create table if not exists sr_products (
  id          text primary key,
  data        jsonb not null,
  created_at  timestamptz default now()
);

-- Customer POS sales (till transactions)
create table if not exists sr_sales (
  id          text primary key,
  data        jsonb not null,
  created_at  timestamptz default now()
);

-- Weekly wholesaler specials (published by admin, seen by all tuckshops)
create table if not exists sr_specials (
  id          text primary key,
  data        jsonb not null,
  created_at  timestamptz default now()
);

-- Digital catalogue (AI-extracted wholesaler products; draft → published)
create table if not exists sr_catalogue (
  id          text primary key,
  data        jsonb not null,
  created_at  timestamptz default now()
);

-- 2. Row Level Security
-- Enable RLS, then allow the public ("anon") key to read/write.
-- NOTE: This is fine for an MVP test. It means anyone with your app link
-- can read/write data. For production you'd switch to Supabase Auth and
-- tighten these policies. See README "Security note".
alter table sr_users    enable row level security;
alter table sr_orders   enable row level security;
alter table sr_receipts enable row level security;
alter table sr_products enable row level security;
alter table sr_sales    enable row level security;
alter table sr_specials enable row level security;
alter table sr_catalogue enable row level security;

create policy "anon full access users"    on sr_users    for all using (true) with check (true);
create policy "anon full access orders"   on sr_orders   for all using (true) with check (true);
create policy "anon full access receipts" on sr_receipts for all using (true) with check (true);
create policy "anon full access products" on sr_products for all using (true) with check (true);
create policy "anon full access sales"    on sr_sales    for all using (true) with check (true);
create policy "anon full access specials" on sr_specials for all using (true) with check (true);
create policy "anon full access catalogue" on sr_catalogue for all using (true) with check (true);

-- Done. Now copy your Project URL and anon key from
-- Settings → API into the CONFIG block in StockRun.html.