-- Etsy Listing Optimizer - Supabase (Postgres) schema
-- Run this in the Supabase SQL editor for your project (see DEPLOY.md).
-- Assumes Supabase Auth is enabled; auth.users is Supabase's built-in table.

-- ---------------------------------------------------------------------------
-- profiles: one row per auth user, holds subscription/tier state
-- ---------------------------------------------------------------------------
create table if not exists public.profiles (
    user_id uuid primary key references auth.users (id) on delete cascade,
    email text not null,
    tier text not null default 'free' check (tier in ('free', 'starter', 'pro', 'business')),
    stripe_customer_id text,
    stripe_subscription_id text,
    stripe_subscription_status text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

alter table public.profiles enable row level security;

create policy "profiles_select_own" on public.profiles
    for select using (auth.uid() = user_id);

create policy "profiles_update_own" on public.profiles
    for update using (auth.uid() = user_id);

-- Inserts happen via a service-role backend call on signup, not directly by
-- the client, so no client-facing insert policy is defined here.

-- ---------------------------------------------------------------------------
-- generation_counters: one row per user per calendar month
-- ---------------------------------------------------------------------------
create table if not exists public.generation_counters (
    user_id uuid not null references auth.users (id) on delete cascade,
    year_month text not null,  -- e.g. '2026-07'
    count integer not null default 0,
    updated_at timestamptz not null default now(),
    primary key (user_id, year_month)
);

alter table public.generation_counters enable row level security;

create policy "counters_select_own" on public.generation_counters
    for select using (auth.uid() = user_id);

-- Writes to counters go through the backend (service role) so quota can't
-- be tampered with by the client; no client insert/update policy.

-- ---------------------------------------------------------------------------
-- brand_voice_profiles
-- ---------------------------------------------------------------------------
create table if not exists public.brand_voice_profiles (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references auth.users (id) on delete cascade,
    name text not null,
    tone text,
    target_audience text,
    favorite_words text,
    created_at timestamptz not null default now()
);

alter table public.brand_voice_profiles enable row level security;

create policy "brand_voice_select_own" on public.brand_voice_profiles
    for select using (auth.uid() = user_id);
create policy "brand_voice_insert_own" on public.brand_voice_profiles
    for insert with check (auth.uid() = user_id);
create policy "brand_voice_update_own" on public.brand_voice_profiles
    for update using (auth.uid() = user_id);
create policy "brand_voice_delete_own" on public.brand_voice_profiles
    for delete using (auth.uid() = user_id);

-- ---------------------------------------------------------------------------
-- listings: generation history
-- ---------------------------------------------------------------------------
create table if not exists public.listings (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references auth.users (id) on delete cascade,
    brand_voice_profile_id uuid references public.brand_voice_profiles (id) on delete set null,
    product_description text not null,
    target_keywords text,
    titles jsonb not null,
    tags jsonb not null,
    description text not null,
    category_hints jsonb,
    provider_used text,
    created_at timestamptz not null default now()
);

alter table public.listings enable row level security;

create policy "listings_select_own" on public.listings
    for select using (auth.uid() = user_id);
create policy "listings_insert_own" on public.listings
    for insert with check (auth.uid() = user_id);
create policy "listings_delete_own" on public.listings
    for delete using (auth.uid() = user_id);

create index if not exists listings_user_created_idx on public.listings (user_id, created_at desc);

-- ---------------------------------------------------------------------------
-- shops: for Business tier "multiple shops/clients"
-- ---------------------------------------------------------------------------
create table if not exists public.shops (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references auth.users (id) on delete cascade,
    name text not null,
    created_at timestamptz not null default now()
);

alter table public.shops enable row level security;

create policy "shops_select_own" on public.shops
    for select using (auth.uid() = user_id);
create policy "shops_insert_own" on public.shops
    for insert with check (auth.uid() = user_id);
create policy "shops_delete_own" on public.shops
    for delete using (auth.uid() = user_id);

-- ---------------------------------------------------------------------------
-- helper: touch updated_at on profiles
-- ---------------------------------------------------------------------------
create or replace function public.set_updated_at()
returns trigger as $$
begin
    new.updated_at = now();
    return new;
end;
$$ language plpgsql;

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

-- ---------------------------------------------------------------------------
-- Reminder (see DEPLOY.md):
-- - Enable "Leaked password protection" under Auth > Policies in the Supabase
--   dashboard - this is a project setting, not a SQL statement.
-- - Provision this project in an EU region (e.g. Frankfurt) at creation time
--   for GDPR data residency - also a dashboard setting, not SQL.
-- ---------------------------------------------------------------------------