-- Enable extensions
create extension if not exists "pgcrypto";

-- Profiles
create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  full_name text,
  role text not null default 'admin' check (role in ('admin', 'editor')),
  avatar_url text,
  created_at timestamptz not null default now()
);

-- Categories
create table if not exists public.article_categories (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  locale text not null default 'ar' check (locale in ('ar', 'en')),
  created_at timestamptz not null default now()
);

create table if not exists public.article_tags (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  locale text not null default 'ar' check (locale in ('ar', 'en')),
  created_at timestamptz not null default now()
);

create table if not exists public.articles (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  slug text not null unique,
  excerpt text not null,
  content text not null,
  cover_image text,
  status text not null default 'draft' check (status in ('draft', 'published')),
  locale text not null default 'ar' check (locale in ('ar', 'en')),
  category_id uuid references public.article_categories(id) on delete set null,
  author_id uuid references public.profiles(id) on delete set null,
  author_name text,
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.article_article_tags (
  article_id uuid not null references public.articles(id) on delete cascade,
  tag_id uuid not null references public.article_tags(id) on delete cascade,
  primary key (article_id, tag_id)
);

create table if not exists public.ai_tool_categories (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  created_at timestamptz not null default now()
);

create table if not exists public.ai_tools (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  description text not null,
  website_url text not null,
  icon_url text,
  featured boolean not null default false,
  status text not null default 'draft' check (status in ('draft', 'published')),
  category_id uuid references public.ai_tool_categories(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.contact_messages (
  id uuid primary key default gen_random_uuid(),
  full_name text not null,
  email text not null,
  phone text,
  subject text not null,
  message text not null,
  is_read boolean not null default false,
  created_at timestamptz not null default now()
);

create table if not exists public.site_settings (
  id uuid primary key default gen_random_uuid(),
  site_name text not null default 'NovaStack',
  site_description text,
  support_email text,
  logo_url text,
  social_links jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- Helper function
create or replace function public.is_admin()
returns boolean
language sql
stable
as $$
  select exists (
    select 1
    from public.profiles
    where id = auth.uid() and role = 'admin'
  );
$$;

-- RLS
alter table public.profiles enable row level security;
alter table public.article_categories enable row level security;
alter table public.article_tags enable row level security;
alter table public.articles enable row level security;
alter table public.article_article_tags enable row level security;
alter table public.ai_tool_categories enable row level security;
alter table public.ai_tools enable row level security;
alter table public.contact_messages enable row level security;
alter table public.site_settings enable row level security;

-- Public reads
create policy "public read published articles"
on public.articles for select
to anon, authenticated
using (status = 'published');

create policy "public read categories"
on public.article_categories for select
to anon, authenticated
using (true);

create policy "public read tags"
on public.article_tags for select
to anon, authenticated
using (true);

create policy "public read published ai tools"
on public.ai_tools for select
to anon, authenticated
using (status = 'published');

create policy "public read ai tool categories"
on public.ai_tool_categories for select
to anon, authenticated
using (true);

create policy "public insert contact messages"
on public.contact_messages for insert
to anon, authenticated
with check (true);

-- Admin full access
create policy "admin manage profiles"
on public.profiles for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

create policy "admin manage article categories"
on public.article_categories for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

create policy "admin manage article tags"
on public.article_tags for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

create policy "admin manage articles"
on public.articles for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

create policy "admin manage article tag pivot"
on public.article_article_tags for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

create policy "admin manage ai tool categories"
on public.ai_tool_categories for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

create policy "admin manage ai tools"
on public.ai_tools for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

create policy "admin manage contact messages"
on public.contact_messages for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

create policy "admin manage site settings"
on public.site_settings for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

-- Storage buckets (run in SQL editor if needed)
insert into storage.buckets (id, name, public)
values ('site-assets', 'site-assets', true)
on conflict (id) do nothing;
