create extension if not exists "pgcrypto";

create table if not exists public.platform_categories (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  name text not null,
  symbol_name text,
  sort_order integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.platforms (
  id uuid primary key default gen_random_uuid(),
  category_id uuid references public.platform_categories(id) on delete set null,
  slug text not null unique,
  name text not null,
  subtitle text not null,
  icon_url text,
  best_for text,
  strengths text[] not null default '{}',
  watch_outs text[] not null default '{}',
  is_published boolean not null default false,
  sort_order integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.skill_categories (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  name text not null,
  symbol_name text,
  sort_order integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.skills (
  id uuid primary key default gen_random_uuid(),
  category_id uuid references public.skill_categories(id) on delete set null,
  slug text not null unique,
  title text not null,
  subtitle text not null,
  summary text,
  prompt text,
  source_notes text,
  is_published boolean not null default false,
  sort_order integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.lessons (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  topic text not null,
  title text not null,
  subtitle text not null,
  duration text,
  summary text,
  key_ideas text[] not null default '{}',
  is_published boolean not null default false,
  sort_order integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.content_sources (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  source_type text not null default 'document',
  source_url text,
  notes text,
  created_at timestamptz not null default now()
);

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

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

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

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

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

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

alter table public.platform_categories enable row level security;
alter table public.platforms enable row level security;
alter table public.skill_categories enable row level security;
alter table public.skills enable row level security;
alter table public.lessons enable row level security;
alter table public.content_sources enable row level security;

drop policy if exists "Public can read platform categories" on public.platform_categories;
create policy "Public can read platform categories"
on public.platform_categories for select
using (true);

drop policy if exists "Public can read published platforms" on public.platforms;
create policy "Public can read published platforms"
on public.platforms for select
using (is_published = true);

drop policy if exists "Public can read skill categories" on public.skill_categories;
create policy "Public can read skill categories"
on public.skill_categories for select
using (true);

drop policy if exists "Public can read published skills" on public.skills;
create policy "Public can read published skills"
on public.skills for select
using (is_published = true);

drop policy if exists "Public can read published lessons" on public.lessons;
create policy "Public can read published lessons"
on public.lessons for select
using (is_published = true);

insert into public.platform_categories (slug, name, symbol_name, sort_order)
values
  ('general', 'General', 'square.grid.2x2.fill', 10),
  ('build', 'Coding', 'chevron.left.forwardslash.chevron.right', 20),
  ('research', 'Research', 'magnifyingglass', 30)
on conflict (slug) do update
set name = excluded.name,
    symbol_name = excluded.symbol_name,
    sort_order = excluded.sort_order;

insert into public.skill_categories (slug, name, symbol_name, sort_order)
values
  ('research', 'Research', 'magnifyingglass', 10),
  ('writing', 'Writing', 'text.alignleft', 20),
  ('coding', 'Coding', 'chevron.left.forwardslash.chevron.right', 30),
  ('ops', 'Ops', 'bolt.fill', 40)
on conflict (slug) do update
set name = excluded.name,
    symbol_name = excluded.symbol_name,
    sort_order = excluded.sort_order;
