-- =====================================================================
--  BUEL Gurme — Supabase sema tanimi
--  Supabase panelinde SQL Editor'e yapistirip calistirin.
--  Ardindan supabase/seed.sql dosyasini calistirarak ornek verileri yukleyin.
-- =====================================================================

create extension if not exists "pgcrypto";

-- ---------------------------------------------------------------- tablolar

create table if not exists public.categories (
  id           uuid primary key default gen_random_uuid(),
  slug         text not null unique,
  name         text not null,
  description  text,
  image_url    text,
  sort_order   integer not null default 99,
  is_active    boolean not null default true,
  created_at   timestamptz not null default now()
);

create table if not exists public.products (
  id           uuid primary key default gen_random_uuid(),
  category_id  uuid not null references public.categories(id) on delete cascade,
  name         text not null,
  description  text,
  unit         text,
  price        numeric(10,2),
  image_url    text,
  is_featured  boolean not null default false,
  is_active    boolean not null default true,
  sort_order   integer not null default 99,
  created_at   timestamptz not null default now()
);

create index if not exists products_category_idx on public.products (category_id);
create index if not exists products_active_idx   on public.products (is_active, sort_order);

create table if not exists public.settings (
  id             text primary key default 'site',
  brand_name     text not null default 'BUEL Gurme',
  tagline        text not null default 'Gurme Elden',
  hero_title     text not null default '',
  hero_subtitle  text not null default '',
  about_title    text not null default '',
  about_body     text not null default '',
  phone          text not null default '',
  whatsapp       text not null default '',
  email          text not null default '',
  address        text not null default '',
  working_hours  text not null default '',
  instagram      text not null default '',
  facebook       text not null default '',
  map_embed_url  text not null default '',
  updated_at     timestamptz not null default now()
);

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

create index if not exists messages_created_idx on public.messages (created_at desc);

-- ------------------------------------------------------------ RLS politikalari
-- Genel site anon anahtariyla yalnizca OKUR. Tum yazma islemleri
-- sunucu tarafinda service-role anahtariyla yapilir; service-role RLS'i atlar.

alter table public.categories enable row level security;
alter table public.products   enable row level security;
alter table public.settings   enable row level security;
alter table public.messages   enable row level security;

drop policy if exists "categories_public_read" on public.categories;
create policy "categories_public_read" on public.categories
  for select using (is_active = true);

drop policy if exists "products_public_read" on public.products;
create policy "products_public_read" on public.products
  for select using (is_active = true);

drop policy if exists "settings_public_read" on public.settings;
create policy "settings_public_read" on public.settings
  for select using (true);

-- Giris yapmis kullanicilar (panel yoneticileri) her seyi gorebilir/yazabilir.
drop policy if exists "categories_admin_all" on public.categories;
create policy "categories_admin_all" on public.categories
  for all to authenticated using (true) with check (true);

drop policy if exists "products_admin_all" on public.products;
create policy "products_admin_all" on public.products
  for all to authenticated using (true) with check (true);

drop policy if exists "settings_admin_all" on public.settings;
create policy "settings_admin_all" on public.settings
  for all to authenticated using (true) with check (true);

drop policy if exists "messages_admin_all" on public.messages;
create policy "messages_admin_all" on public.messages
  for all to authenticated using (true) with check (true);

-- Iletisim formu: herkes mesaj birakabilir, kimse okuyamaz.
drop policy if exists "messages_public_insert" on public.messages;
create policy "messages_public_insert" on public.messages
  for insert to anon with check (true);

-- ------------------------------------------------------------------ tetikleyici

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

drop trigger if exists settings_touch on public.settings;
create trigger settings_touch before update on public.settings
  for each row execute function public.touch_updated_at();