create extension if not exists pgcrypto;

create type public.user_role as enum ('customer','admin');
create type public.order_status as enum ('pending','paid','processing','delivered','cancelled','refunded');
create type public.payment_status as enum ('unpaid','pending','paid','failed','refunded');

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

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

create table public.products (
  id uuid primary key default gen_random_uuid(),
  category_id uuid references public.categories(id) on delete set null,
  name text not null,
  slug text unique not null,
  category text not null,
  short_description text,
  description text,
  price numeric(12,2) not null default 0,
  duration_days integer not null default 30,
  access_type text not null,
  features jsonb not null default '[]'::jsonb,
  image_url text,
  active boolean not null default true,
  featured boolean not null default false,
  stock integer,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.orders (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete restrict,
  product_id uuid not null references public.products(id) on delete restrict,
  quantity integer not null default 1 check(quantity>0),
  total_amount numeric(12,2) not null,
  status public.order_status not null default 'pending',
  payment_status public.payment_status not null default 'unpaid',
  payment_transaction_id text,
  whatsapp_message_id text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.subscriptions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete cascade,
  order_id uuid not null references public.orders(id) on delete restrict,
  product_id uuid not null references public.products(id) on delete restrict,
  starts_at timestamptz not null default now(),
  expires_at timestamptz not null,
  status text not null default 'active',
  created_at timestamptz not null default now()
);

create table public.whatsapp_events (
  id uuid primary key default gen_random_uuid(),
  event_type text,
  wa_message_id text unique,
  from_number text,
  payload jsonb,
  created_at timestamptz not null default now()
);

create index products_slug_idx on public.products(slug);
create index products_category_idx on public.products(category);
create index orders_user_idx on public.orders(user_id);
create index subscriptions_user_idx on public.subscriptions(user_id);

create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  insert into public.profiles(id,email,full_name) values(new.id,new.email,new.raw_user_meta_data->>'full_name')
  on conflict (id) do update set email=excluded.email, full_name=excluded.full_name;
  return new;
end; $$;

create trigger on_auth_user_created after insert on auth.users
for each row execute procedure public.handle_new_user();

alter table public.profiles enable row level security;
alter table public.categories enable row level security;
alter table public.products enable row level security;
alter table public.orders enable row level security;
alter table public.subscriptions enable row level security;
alter table public.whatsapp_events enable row level security;

create policy "public read active products" on public.products for select using(active=true);
create policy "users read own profile" on public.profiles for select using(auth.uid()=id);
create policy "users update own profile" on public.profiles for update using(auth.uid()=id);
create policy "users read own orders" on public.orders for select using(auth.uid()=user_id);
create policy "users create own orders" on public.orders for insert with check(auth.uid()=user_id);
create policy "users read own subscriptions" on public.subscriptions for select using(auth.uid()=user_id);

-- Admin policy helper. Promote your first admin manually after account creation:
-- update public.profiles set role='admin' where email='YOUR_ADMIN_EMAIL';
create policy "admins manage products" on public.products for all using((select role from public.profiles where id=auth.uid())='admin') with check((select role from public.profiles where id=auth.uid())='admin');
create policy "admins read orders" on public.orders for select using(auth.uid()=user_id or (select role from public.profiles where id=auth.uid())='admin');
create policy "admins update orders" on public.orders for update using((select role from public.profiles where id=auth.uid())='admin');
create policy "admins read customers" on public.profiles for select using(auth.uid()=id or (select role from public.profiles where id=auth.uid())='admin');

insert into public.categories(name,slug) values
('AI','ai'),('Design','design'),('OTT','ott'),('Music','music'),('Productivity','productivity'),('Education','education')
on conflict do nothing;

insert into public.products(name,slug,category,short_description,description,price,duration_days,access_type,features,featured)
values
('ChatGPT Plus','chatgpt-plus','AI','Advanced AI tools for work and study','Replace with your actual product description and fulfillment terms.',350,30,'Own Account','["AI tools","30 days","WhatsApp support"]',true),
('Canva Pro','canva-pro','Design','Premium design toolkit','Replace with your actual product description and fulfillment terms.',250,30,'Own Account','["Design tools","30 days","WhatsApp support"]',true),
('Netflix Premium','netflix-premium','OTT','Premium streaming plan','Replace with your actual product description and fulfillment terms.',390,30,'Plan Dependent','["Streaming","30 days","Support"]',true)
on conflict (slug) do nothing;