supabase/schema.sql

-- PlainRewrite — run once in Supabase SQL Editor
-- Auth: enable Email + Google in Authentication → Providers

create extension if not exists "pgcrypto";

-- Packages (edit in Table Editor; Stripe later)
create table if not exists public.plans (
  id text primary key,
  name text not null,
  daily_rewrites int not null,
  max_words_per_request int not null,
  daily_word_cap int not null,
  price_inr_monthly int not null default 0,
  active boolean not null default true
);

insert into public.plans (id, name, daily_rewrites, max_words_per_request, daily_word_cap, price_inr_monthly)
values
  ('free', 'Free', 5, 400, 1500, 0),
  ('pro', 'Pro', 50, 2000, 15000, 199),
  ('plus', 'Plus', 200, 5000, 50000, 499)
on conflict (id) do update set
  name = excluded.name,
  daily_rewrites = excluded.daily_rewrites,
  max_words_per_request = excluded.max_words_per_request,
  daily_word_cap = excluded.daily_word_cap,
  price_inr_monthly = excluded.price_inr_monthly;

create table if not exists public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  email text,
  display_name text,
  role text not null default 'user' check (role in ('user', 'admin')),
  plan_id text not null default 'free' references public.plans (id),
  status text not null default 'active' check (status in ('active', 'disabled')),
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.usage_daily (
  user_id uuid not null references public.profiles (id) on delete cascade,
  usage_date date not null default (timezone('utc', now())::date),
  rewrite_count int not null default 0,
  word_count int not null default 0,
  primary key (user_id, usage_date)
);

create index if not exists usage_daily_date_idx on public.usage_daily (usage_date);

-- Auto-create profile on signup
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, email, display_name, role, plan_id)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data->>'full_name', split_part(new.email, '@', 1)),
    'user',
    'free'
  )
  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();

alter table public.plans enable row level security;
alter table public.profiles enable row level security;
alter table public.usage_daily enable row level security;

-- Public can read active plans (pricing page later)
drop policy if exists "plans_read_active" on public.plans;
create policy "plans_read_active"
  on public.plans for select
  using (active = true);

-- Users read/update own profile (plan/role only via service role / admin in dashboard)
drop policy if exists "profiles_select_own" on public.profiles;
create policy "profiles_select_own"
  on public.profiles for select
  using (auth.uid() = id);

drop policy if exists "profiles_update_own_name" on public.profiles;
create policy "profiles_update_own_name"
  on public.profiles for update
  using (auth.uid() = id)
  with check (auth.uid() = id);

drop policy if exists "usage_select_own" on public.usage_daily;
create policy "usage_select_own"
  on public.usage_daily for select
  using (auth.uid() = user_id);

-- FastAPI uses the service role key (bypasses RLS) for quota writes.
-- Promote an admin in SQL after first login:
--   update public.profiles set role = 'admin' where email = 'you@example.com';