create extension if not exists pgcrypto;

create table if not exists public.user_profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  role text not null default 'reader' check (role in ('reader', 'writer', 'admin')),
  full_name text,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.blog_posts (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  excerpt text,
  content text,
  category text not null,
  tags text[] not null default '{}',
  read_time_minutes integer not null default 1,
  image_url text,
  published boolean not null default true,
  featured boolean not null default false,
  view_count integer not null default 0,
  author_id uuid references auth.users(id) on delete set null,
  author_email text,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz
);

create index if not exists idx_blog_posts_created_at on public.blog_posts(created_at desc);
create index if not exists idx_blog_posts_category   on public.blog_posts(category);
create index if not exists idx_blog_posts_published  on public.blog_posts(published);

-- Auto-update updated_at on every row change (F04)
create or replace function public.set_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at := timezone('utc', now());
  return new;
end;
$$;

drop trigger if exists blog_posts_updated_at on public.blog_posts;

create trigger blog_posts_updated_at
  before update on public.blog_posts
  for each row execute function public.set_updated_at();

-- Row-level security (service-role key bypasses; RLS is defence-in-depth for direct DB access)
alter table public.blog_posts enable row level security;

drop policy if exists "public read published" on public.blog_posts;
create policy "public read published"
  on public.blog_posts for select
  using (published = true);

drop policy if exists "author read own drafts" on public.blog_posts;
create policy "author read own drafts"
  on public.blog_posts for select
  using (auth.uid() = author_id);

-- Supabase Storage: create bucket named 'blog-images' (public read, service-role write, 5 MB max)
-- Run in Supabase dashboard Storage tab — cannot be done via SQL migration.