-- FinanceLearn saved scenarios and path progress.
--
-- SAME SUPABASE LOGIN AS ECONLEARN IS FINE. DO NOT PASTE THIS INTO THE
-- EXISTING ECONLEARN PROJECT. Create a new project for FinanceLearn, then
-- paste here. EconLearn already has Auth users, RLS and tables; mixing this
-- file into that database would share accounts and rows with a live site.
--
-- Exact paste path:
--   1. Open https://supabase.com/dashboard and sign in with the same account
--      you use for EconLearn.
--   2. Click New project. Do not open the EconLearn project. Name it
--      financelearn (or similar). Set a database password and a region.
--      Wait until the project shows as healthy.
--   3. Left sidebar: SQL Editor. Click New query.
--   4. Paste this entire file. Click Run. Safe to run more than once.
--   5. Authentication > URL Configuration:
--        Site URL: https://www.financelearn.org
--        Redirect URLs: add https://www.financelearn.org/saved
--        Optional local: http://localhost:3000/saved
--   6. Authentication > Providers > Email: enable.
--   7. This site requests a magic link over REST with no PKCE challenge, so
--      the link must return access_token in the URL hash (implicit flow).
--      If /saved opens with ?code= in the query, the page will say so: set
--      the project to implicit and request a new link.
--   8. On the Vercel project that serves financelearn.org (not EconLearn),
--      set NEXT_PUBLIC_SUPABASE_URL and NEXT_PUBLIC_SUPABASE_ANON_KEY from
--      this new project's Settings > API. Use the anon key only, never the
--      service_role key.
--
-- Public pages stay static. The browser talks to these tables directly with
-- the anon key; there is no Next.js middleware and no server cookie.
-- Safe to re-run: creates are IF NOT EXISTS, policies are dropped then created.

create table if not exists public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  display_name text,
  created_at timestamptz not null default now()
);

create table if not exists public.saved_scenarios (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users (id) on delete cascade,
  kind text not null check (kind in ('calculator', 'tool')),
  slug text not null,
  label text not null default '',
  inputs jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists saved_scenarios_user_updated
  on public.saved_scenarios (user_id, updated_at desc);

create unique index if not exists saved_scenarios_user_kind_slug
  on public.saved_scenarios (user_id, kind, slug);

create table if not exists public.path_progress (
  user_id uuid not null references auth.users (id) on delete cascade,
  path_slug text not null,
  href text not null,
  completed_at timestamptz not null default now(),
  primary key (user_id, path_slug, href)
);

create table if not exists public.bookmarks (
  user_id uuid not null references auth.users (id) on delete cascade,
  href text not null,
  created_at timestamptz not null default now(),
  primary key (user_id, href)
);

alter table public.profiles enable row level security;
alter table public.saved_scenarios enable row level security;
alter table public.path_progress enable row level security;
alter table public.bookmarks enable row level security;

drop policy if exists "profiles are own row" on public.profiles;
create policy "profiles are own row"
  on public.profiles for all
  using (auth.uid() = id)
  with check (auth.uid() = id);

drop policy if exists "scenarios are own rows" on public.saved_scenarios;
create policy "scenarios are own rows"
  on public.saved_scenarios for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists "progress is own rows" on public.path_progress;
create policy "progress is own rows"
  on public.path_progress for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists "bookmarks are own rows" on public.bookmarks;
create policy "bookmarks are own rows"
  on public.bookmarks for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id)
  values (new.id)
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

create or replace function public.touch_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists saved_scenarios_touch on public.saved_scenarios;
create trigger saved_scenarios_touch
  before update on public.saved_scenarios
  for each row execute function public.touch_updated_at();
