-- ============================================================================
-- Gaming Review Site — schema
-- Run this in the Supabase SQL editor (or `supabase db push`) on a fresh project.
-- ============================================================================

create extension if not exists "pgcrypto";

-- ---------------------------------------------------------------------------
-- Enums
-- ---------------------------------------------------------------------------
create type price_type as enum ('free', 'paid', 'freemium');
create type game_status as enum ('seeded', 'draft', 'ready', 'published');

-- ---------------------------------------------------------------------------
-- Taxonomy tables
-- ---------------------------------------------------------------------------
create table platforms (
  id uuid primary key default gen_random_uuid(),
  name text not null unique,
  slug text not null unique,
  icon_url text
);

create table genres (
  id uuid primary key default gen_random_uuid(),
  name text not null unique,
  slug text not null unique,
  icon_url text
);

create table tags (
  id uuid primary key default gen_random_uuid(),
  name text not null unique,
  slug text not null unique,
  icon_url text
);

-- ---------------------------------------------------------------------------
-- Core games table
-- ---------------------------------------------------------------------------
create table games (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,

  icon_image text,
  cover_image text,
  screenshots text[] not null default '{}',

  trailer_youtube_id text,
  trailer_enabled boolean not null default false,

  developer text,
  publisher text,
  release_date date,

  price_type price_type,
  size_by_platform jsonb not null default '{}'::jsonb,
  age_rating text,

  editor_score numeric(3,1) check (editor_score >= 0 and editor_score <= 10),
  verdict_short text,

  overview text,
  gameplay text,
  who_its_for text,

  pros text[] not null default '{}',
  cons text[] not null default '{}',
  tips jsonb not null default '[]'::jsonb, -- array of rich-text blocks

  system_requirements jsonb not null default '{}'::jsonb, -- {min:{...}, recommended:{...}}
  faq jsonb not null default '[]'::jsonb, -- array of {question, answer}

  official_links jsonb not null default '{}'::jsonb, -- {steam, play_store, app_store, ps_store, xbox, epic}

  meta_title text,
  meta_description text,
  og_image text,

  status game_status not null default 'seeded',
  last_verified_date date,

  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index games_status_idx on games (status);
create index games_slug_idx on games (slug);

-- Many-to-many joins
create table game_platforms (
  game_id uuid references games(id) on delete cascade,
  platform_id uuid references platforms(id) on delete cascade,
  primary key (game_id, platform_id)
);

create table game_genres (
  game_id uuid references games(id) on delete cascade,
  genre_id uuid references genres(id) on delete cascade,
  primary key (game_id, genre_id)
);

create table game_tags (
  game_id uuid references games(id) on delete cascade,
  tag_id uuid references tags(id) on delete cascade,
  primary key (game_id, tag_id)
);

-- ---------------------------------------------------------------------------
-- Homepage / curation
-- ---------------------------------------------------------------------------
create table collections (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  slug text not null unique,
  game_ids uuid[] not null default '{}',
  sort_order int not null default 0
);

create table homepage_config (
  id int primary key default 1 check (id = 1), -- singleton row
  featured_game_id uuid references games(id),
  row_order text[] not null default '{}' -- ordered list of collection slugs
);
insert into homepage_config (id) values (1);

-- ---------------------------------------------------------------------------
-- Media & icon libraries
-- ---------------------------------------------------------------------------
create table media_library (
  id uuid primary key default gen_random_uuid(),
  file_path text not null, -- path within the Supabase Storage bucket
  original_filename text,
  width int,
  height int,
  uploaded_at timestamptz not null default now()
);

create table icon_library (
  id uuid primary key default gen_random_uuid(),
  category text not null, -- 'platform' | 'genre' | 'tag' | 'badge'
  name text not null,
  image_url text not null
);

-- ---------------------------------------------------------------------------
-- Ads (off by default — see compliance rules)
-- ---------------------------------------------------------------------------
create table ad_slots (
  id uuid primary key default gen_random_uuid(),
  placement_key text not null unique, -- e.g. 'home_below_hero', 'game_detail_sidebar'
  enabled boolean not null default false,
  ad_unit_code text
);

-- ---------------------------------------------------------------------------
-- Admin auth (single admin, 2FA secret stored; real auth via Supabase Auth
-- is layered on top — this table just carries the extra 2FA field)
-- ---------------------------------------------------------------------------
create table admin_users (
  id uuid primary key default gen_random_uuid(),
  email text not null unique,
  password_hash text not null,
  totp_secret text,
  created_at timestamptz not null default now()
);

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

create trigger games_set_updated_at
before update on games
for each row execute function set_updated_at();

-- ---------------------------------------------------------------------------
-- Row Level Security
-- Public (anon) read: published games and their taxonomy/collections only.
-- All writes: service role / authenticated admin only.
-- ---------------------------------------------------------------------------
alter table games enable row level security;
alter table platforms enable row level security;
alter table genres enable row level security;
alter table tags enable row level security;
alter table game_platforms enable row level security;
alter table game_genres enable row level security;
alter table game_tags enable row level security;
alter table collections enable row level security;
alter table homepage_config enable row level security;
alter table ad_slots enable row level security;
alter table admin_users enable row level security;
alter table media_library enable row level security;
alter table icon_library enable row level security;

create policy "public read published games"
  on games for select
  using (status = 'published');

create policy "public read taxonomy"
  on platforms for select using (true);
create policy "public read genres"
  on genres for select using (true);
create policy "public read tags"
  on tags for select using (true);

create policy "public read game_platforms of published games"
  on game_platforms for select
  using (exists (select 1 from games g where g.id = game_id and g.status = 'published'));
create policy "public read game_genres of published games"
  on game_genres for select
  using (exists (select 1 from games g where g.id = game_id and g.status = 'published'));
create policy "public read game_tags of published games"
  on game_tags for select
  using (exists (select 1 from games g where g.id = game_id and g.status = 'published'));

create policy "public read collections"
  on collections for select using (true);
create policy "public read homepage_config"
  on homepage_config for select using (true);
create policy "public read enabled ad_slots"
  on ad_slots for select using (enabled = true);
create policy "public read icon_library"
  on icon_library for select using (true);

-- No public policies exist for admin_users or media_library (admin-only, via service role).
-- All INSERT/UPDATE/DELETE on every table is intentionally left with no anon policy,
-- so writes only succeed through the service-role key used by the admin API routes.