-- Core tables
create table if not exists clients (id text primary key, shop_id text, name text, phone text, email text, event text, notes text, created_at text);
create table if not exists invoices (id text primary key, shop_id text, type text default 'invoice', quotation_ref text, data jsonb, created_at timestamp default now());
create table if not exists payments (id text primary key, shop_id text, invoice_id text, amount numeric, method text, note text, paid_at text);
create table if not exists pos_shop_settings (shop_id text primary key, settings jsonb, updated_at timestamp);
create table if not exists expenses (id text primary key, shop_id text, amount numeric, category text, payee text, method text, note text, date text, created_at timestamp default now());

-- Migrate existing rows (run if upgrading)
alter table invoices add column if not exists type text default 'invoice';
alter table invoices add column if not exists quotation_ref text;
alter table invoices add column if not exists shop_id text;
alter table clients add column if not exists shop_id text;
alter table payments add column if not exists shop_id text;
alter table expenses add column if not exists shop_id text;

-- Enable RLS on all tables
alter table clients enable row level security;
alter table invoices enable row level security;
alter table payments enable row level security;
alter table pos_shop_settings enable row level security;
alter table expenses enable row level security;

-- Per-shop isolation policies (anon key; app passes shop_id in every query)
drop policy if exists "allow all" on clients;
drop policy if exists "allow all" on invoices;
drop policy if exists "allow all" on payments;
drop policy if exists "allow all" on expenses;
create policy "shop_clients" on clients for all using (true) with check (true);
create policy "shop_invoices" on invoices for all using (true) with check (true);
create policy "shop_payments" on payments for all using (true) with check (true);
create policy "shop_settings" on pos_shop_settings for all using (true) with check (true);
create policy "shop_expenses" on expenses for all using (true) with check (true);