تفعيل Supabase

create type public.user_role as enum ('buyer', 'merchant', 'admin');
create type public.merchant_status as enum ('pending', 'approved', 'rejected', 'suspended');
create type public.product_status as enum ('draft', 'pending_review', 'published', 'hidden', 'rejected');
create type public.stock_status as enum ('in_stock', 'out_of_stock', 'preorder');
create type public.order_status as enum ('new', 'forwarded', 'completed', 'cancelled');

create table public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text not null,
  full_name text,
  role public.user_role not null default 'buyer',
  created_at timestamptz not null default now()
);

create table public.merchant_profiles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete cascade,
  store_name text not null,
  store_slug text not null unique,
  logo_url text,
  description text,
  city text,
  phone text,
  status public.merchant_status not null default 'pending',
  created_at timestamptz not null default now(),
  approved_at timestamptz
);

create table public.categories (
  id uuid primary key default gen_random_uuid(),
  name_ar text not null,
  name_en text,
  slug text not null unique,
  parent_id uuid references public.categories(id),
  is_active boolean not null default true
);

create table public.products (
  id uuid primary key default gen_random_uuid(),
  merchant_id uuid not null references public.merchant_profiles(id) on delete cascade,
  category_id uuid references public.categories(id),
  title text not null,
  slug text not null unique,
  description text,
  price numeric(12, 2) not null,
  currency text not null default 'USD',
  stock_status public.stock_status not null default 'in_stock',
  status public.product_status not null default 'pending_review',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.product_images (
  id uuid primary key default gen_random_uuid(),
  product_id uuid not null references public.products(id) on delete cascade,
  image_url text not null,
  sort_order int not null default 0
);

create table public.order_requests (
  id uuid primary key default gen_random_uuid(),
  product_id uuid references public.products(id),
  merchant_id uuid references public.merchant_profiles(id),
  buyer_id uuid references public.profiles(id),
  buyer_name text,
  buyer_phone text,
  buyer_city text,
  whatsapp_message text not null,
  status public.order_status not null default 'new',
  created_at timestamptz not null default now()
);

alter table public.profiles enable row level security;
alter table public.merchant_profiles enable row level security;
alter table public.categories enable row level security;
alter table public.products enable row level security;
alter table public.product_images enable row level security;
alter table public.order_requests enable row level security;

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

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  requested_role public.user_role;
begin
  requested_role := coalesce((new.raw_user_meta_data ->> 'role')::public.user_role, 'buyer');

  insert into public.profiles (id, email, full_name, role)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data ->> 'full_name',
    requested_role
  )
  on conflict (id) do update
  set
    email = excluded.email,
    full_name = excluded.full_name,
    role = excluded.role;

  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
after insert on auth.users
for each row execute function public.handle_new_user();

create policy "profiles_select_own_or_admin"
on public.profiles for select
using (id = auth.uid() or public.is_admin());

create policy "profiles_update_own"
on public.profiles for update
using (id = auth.uid())
with check (id = auth.uid());

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

create policy "categories_admin_all"
on public.categories for all
using (public.is_admin())
with check (public.is_admin());

create policy "merchants_public_approved_read"
on public.merchant_profiles for select
using (status = 'approved' or user_id = auth.uid() or public.is_admin());

create policy "merchants_insert_own"
on public.merchant_profiles for insert
with check (user_id = auth.uid());

create policy "merchants_update_own_pending"
on public.merchant_profiles for update
using (user_id = auth.uid())
with check (user_id = auth.uid() and status in ('pending', 'approved'));

create policy "merchants_admin_all"
on public.merchant_profiles for all
using (public.is_admin())
with check (public.is_admin());

create policy "products_public_published_read"
on public.products for select
using (status = 'published' or public.is_admin() or exists (
  select 1 from public.merchant_profiles mp
  where mp.id = products.merchant_id
    and mp.user_id = auth.uid()
));

create policy "products_merchant_insert"
on public.products for insert
with check (exists (
  select 1 from public.merchant_profiles mp
  where mp.id = merchant_id
    and mp.user_id = auth.uid()
    and mp.status = 'approved'
));

create policy "products_merchant_update"
on public.products for update
using (exists (
  select 1 from public.merchant_profiles mp
  where mp.id = products.merchant_id
    and mp.user_id = auth.uid()
))
with check (exists (
  select 1 from public.merchant_profiles mp
  where mp.id = merchant_id
    and mp.user_id = auth.uid()
));

create policy "products_admin_all"
on public.products for all
using (public.is_admin())
with check (public.is_admin());

create policy "product_images_read_for_visible_products"
on public.product_images for select
using (exists (
  select 1 from public.products p
  where p.id = product_images.product_id
    and (
      p.status = 'published'
      or public.is_admin()
      or exists (
        select 1 from public.merchant_profiles mp
        where mp.id = p.merchant_id
          and mp.user_id = auth.uid()
      )
    )
));

create policy "product_images_merchant_insert"
on public.product_images for insert
with check (exists (
  select 1
  from public.products p
  join public.merchant_profiles mp on mp.id = p.merchant_id
  where p.id = product_id
    and mp.user_id = auth.uid()
));

create policy "order_requests_buyer_insert"
on public.order_requests for insert
with check (buyer_id is null or buyer_id = auth.uid());

create policy "order_requests_admin_all"
on public.order_requests for all
using (public.is_admin())
with check (public.is_admin());

create policy "order_requests_merchant_read_forwarded"
on public.order_requests for select
using (exists (
  select 1 from public.merchant_profiles mp
  where mp.id = order_requests.merchant_id
    and mp.user_id = auth.uid()
    and order_requests.status in ('forwarded', 'completed', 'cancelled')
));