-- ═══════════════════════════════════════════════════════
-- Paul Willemse Attorneys Inc. — Supabase Database Setup
-- Copy and run this in: Supabase → SQL Editor → New Query
-- ═══════════════════════════════════════════════════════

-- 1. Enable UUID extension (may already be enabled)
create extension if not exists "uuid-ossp";

-- 2. Create the single app_data table
--    All practice data is stored as key-value pairs per user
create table if not exists public.app_data (
  id          uuid primary key default uuid_generate_v4(),
  user_id     uuid not null references auth.users(id) on delete cascade,
  key         text not null,
  data        text not null default '[]',
  updated_at  timestamptz not null default now(),
  constraint  app_data_user_key_unique unique (user_id, key)
);

-- 3. Enable Row Level Security (RLS)
--    Each user can ONLY access their own data
alter table public.app_data enable row level security;

-- 4. Drop existing policies if re-running (safe to ignore errors)
drop policy if exists "Users can read own data"   on public.app_data;
drop policy if exists "Users can insert own data" on public.app_data;
drop policy if exists "Users can update own data" on public.app_data;
drop policy if exists "Users can delete own data" on public.app_data;

-- 5. Create RLS policies
create policy "Users can read own data"
  on public.app_data for select
  using (auth.uid() = user_id);

create policy "Users can insert own data"
  on public.app_data for insert
  with check (auth.uid() = user_id);

create policy "Users can update own data"
  on public.app_data for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

create policy "Users can delete own data"
  on public.app_data for delete
  using (auth.uid() = user_id);

-- 6. Performance index
create index if not exists idx_app_data_user_key
  on public.app_data (user_id, key);

-- 7. Auto-update updated_at timestamp on changes
create or replace function public.set_updated_at()
returns trigger language plpgsql as $$
begin new.updated_at = now(); return new; end;
$$;

drop trigger if exists trg_app_data_updated_at on public.app_data;
create trigger trg_app_data_updated_at
  before update on public.app_data
  for each row execute function public.set_updated_at();

-- ═══════════════════════════════════════════════════════
-- DONE! Your Paul Willemse Attorneys Inc. database is ready.
-- Next: Go to Project Settings → API and copy your
--       Project URL and anon/public key into the app.
-- ═══════════════════════════════════════════════════════