-- Racing Edge OS - Supabase/PostgreSQL starter schema
-- Purpose: move the prototype from localStorage to a real SaaS database.

create extension if not exists "pgcrypto";

create table if not exists public.waitlist_leads (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  email text not null unique,
  segment text not null,
  budget text not null,
  source text default 'racing_landing_page',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.profiles (
  id uuid primary key,
  email text not null unique,
  full_name text,
  plan text not null default 'free',
  segment text,
  notes text,
  age_confirmed boolean not null default false,
  responsible_gaming_confirmed boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.subscriptions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete cascade,
  plan text not null check (plan in ('free', 'pro', 'elite', 'api')),
  status text not null default 'trial' check (status in ('trial', 'active', 'past_due', 'canceled', 'incomplete')),
  payment_provider text not null default 'stripe',
  stripe_customer_id text,
  stripe_subscription_id text unique,
  paypal_subscription_id text unique,
  paypal_payer_id text,
  crypto_invoice_id text unique,
  crypto_transaction_id text,
  current_period_start timestamptz,
  current_period_end timestamptz,
  cancel_at_period_end boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.racecourses (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  country text not null default 'FR',
  discipline_family text,
  surface_notes text,
  created_at timestamptz not null default now()
);

create table if not exists public.races (
  id uuid primary key default gen_random_uuid(),
  racecourse_id uuid references public.racecourses(id),
  source text not null,
  source_url text,
  meeting_date date not null,
  start_time time,
  reunion_number text,
  race_number text,
  title text not null,
  discipline text not null,
  class_name text,
  distance_meters integer,
  surface text,
  going text,
  start_type text,
  purse_cents integer,
  runners_count integer,
  snapshot_at timestamptz not null default now(),
  created_at timestamptz not null default now()
);

create table if not exists public.runners (
  id uuid primary key default gen_random_uuid(),
  race_id uuid not null references public.races(id) on delete cascade,
  saddle_number text not null,
  horse_name text not null,
  age text,
  sex text,
  jockey_or_driver text,
  trainer text,
  form_string text,
  odds_reference numeric(8,2),
  odds_latest numeric(8,2),
  form_score numeric(6,2),
  distance_score numeric(6,2),
  ground_score numeric(6,2),
  team_score numeric(6,2),
  consistency_score numeric(6,2),
  risk_score numeric(6,2),
  status text not null default 'declared',
  created_at timestamptz not null default now()
);

create table if not exists public.predictions (
  id uuid primary key default gen_random_uuid(),
  race_id uuid not null references public.races(id) on delete cascade,
  runner_id uuid not null references public.runners(id) on delete cascade,
  model_version text not null,
  ai_score numeric(6,2) not null,
  win_probability numeric(8,5) not null,
  place_probability numeric(8,5) not null,
  top4_probability numeric(8,5) not null,
  confidence_band text not null,
  explanation text,
  created_at timestamptz not null default now()
);

create table if not exists public.tickets (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id),
  race_id uuid not null references public.races(id) on delete cascade,
  bet_type text not null,
  risk_mode text not null,
  budget_cents integer not null,
  base_numbers text[] not null default '{}',
  backup_numbers text[] not null default '{}',
  confidence numeric(8,5),
  model_version text not null,
  disclaimer_acknowledged boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists public.premium_reports (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id),
  race_id uuid references public.races(id) on delete cascade,
  ticket_id uuid references public.tickets(id) on delete set null,
  title text not null,
  html_content text not null,
  report_status text not null default 'generated',
  generated_at timestamptz not null default now()
);

create table if not exists public.member_access_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete cascade,
  event_type text not null,
  plan text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists public.responsible_limits (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete cascade,
  daily_budget_cents integer,
  weekly_budget_cents integer,
  reminder_enabled boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.consent_logs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id),
  email text,
  consent_type text not null,
  consent_text text not null,
  ip_hash text,
  user_agent text,
  created_at timestamptz not null default now()
);

create index if not exists idx_races_meeting_date on public.races(meeting_date);
create index if not exists idx_runners_race_id on public.runners(race_id);
create index if not exists idx_predictions_race_id on public.predictions(race_id);
create index if not exists idx_tickets_user_id on public.tickets(user_id);
create index if not exists idx_subscriptions_user_id on public.subscriptions(user_id);
create index if not exists idx_premium_reports_user_id on public.premium_reports(user_id);
create index if not exists idx_member_access_events_user_id on public.member_access_events(user_id);
create index if not exists idx_waitlist_created_at on public.waitlist_leads(created_at desc);
