-- Kinnected: shop and blog tables.
-- Run this once in Supabase: SQL Editor -> New query -> paste -> Run.

-- ---------- Blog ----------
create table if not exists public.blog_posts (
  id uuid primary key default gen_random_uuid(),
  slug text unique not null,
  title text not null,
  excerpt text,
  body text not null,
  category text default 'Family history',
  published boolean not null default false,
  published_at timestamptz not null default now(),
  created_at timestamptz not null default now()
);

-- ---------- Shop ----------
create table if not exists public.products (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  description text,
  price_cents integer not null check (price_cents >= 0),
  category text,
  tone text not null default 'purple',
  active boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists public.orders (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete set null,
  email text not null,
  items jsonb not null,
  total_cents integer not null check (total_cents >= 0),
  status text not null default 'pending',
  created_at timestamptz not null default now()
);

-- ---------- Row Level Security ----------
-- Visitors can read published posts and active products.
-- Only signed-in users can see and create their own orders.
-- Writing posts and products is done from the Supabase dashboard for now.
alter table public.blog_posts enable row level security;
alter table public.products enable row level security;
alter table public.orders enable row level security;

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

drop policy if exists "Public read active products" on public.products;
create policy "Public read active products" on public.products
  for select using (active = true);

drop policy if exists "Users create own orders" on public.orders;
create policy "Users create own orders" on public.orders
  for insert with check (auth.uid() = user_id);

drop policy if exists "Users read own orders" on public.orders;
create policy "Users read own orders" on public.orders
  for select using (auth.uid() = user_id);

-- ---------- Starter content ----------
insert into public.products (name, description, price_cents, category, tone) values
  ('Printed family tree poster', 'An 18 × 24 inch poster of your family tree on matte paper.', 3900, 'Keepsakes', 'purple'),
  ('Family history book', 'A 40-page hardcover with your stories and photos.', 6900, 'Keepsakes', 'teal'),
  ('Photo digitizing (25 photos)', 'We scan and tag your old prints and send the originals back.', 2500, 'Services', 'amber'),
  ('Memorial candle set', 'Two candles with a printed memorial card.', 1800, 'Memorial', 'purple');

insert into public.blog_posts (slug, title, excerpt, body, category, published, published_at) values
  ('welcome-to-kinnected',
   'Welcome to Kinnected',
   'Why we built a free, ad-free place for family history.',
   'Kinnected started with a simple question: why is family history so hard to share?

We built a place where your family tree, photos, and stories live together, and where nothing is sold to advertisers.

Start with the people you know best, and invite the rest of the family when you are ready.',
   'News', true, now() - interval '3 days'),
  ('how-to-start-a-family-tree',
   'How to start a family tree',
   'Start with what you already know, then ask one relative at a time.',
   'Begin with yourself, your parents, and your grandparents. Write down names, birth years, and places as you remember them.

Then ask one relative to add what they know. Small, specific questions work best: “Where did Grandpa grow up?” gets better answers than “Tell me about the family.”

Add photos as you go. A photo often answers a question you forgot to ask.',
   'Family history', true, now() - interval '10 days'),
  ('scanning-old-family-photos',
   'Scanning old family photos',
   'Simple steps for scanning prints at home, and what to do with the originals.',
   'Scan at 600 dpi or higher so faces stay sharp. Save the original file before any editing.

Write the names, date, and place on the back of the print, or in a note next to the scan. Labels are the part people forget, and they matter most later.

Keep originals in acid-free sleeves away from heat and damp.',
   'Tips', true, now() - interval '20 days');