-- AnzzzStore — jalankan di Supabase SQL Editor
-- Auth: aktifkan Google, Discord, Facebook di Authentication > Providers

create extension if not exists "pgcrypto";

create type public.order_category as enum ('product', 'joki');
create type public.order_status as enum ('pending', 'processing', 'completed', 'cancelled');

create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  full_name text,
  email text,
  avatar_url text,
  whatsapp_number text,
  provider text default 'email',
  role text not null default 'member' check (role in ('member', 'admin')),
  points integer not null default 0,
  total_transactions integer not null default 0,
  account_status text not null default 'active' check (account_status in ('active', 'suspended')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.products (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  description text,
  price integer not null,
  stock integer not null default 0,
  image_url text,
  featured boolean not null default false,
  active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.joki_packages (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  description text,
  price integer not null,
  estimated_days integer,
  point_reward integer not null default 0,
  game_tag text not null default 'genshin',
  active boolean not null default true,
  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 references public.profiles (id) on delete set null,
  category order_category not null,
  product_id uuid references public.products (id) on delete set null,
  joki_package_id uuid references public.joki_packages (id) on delete set null,
  status order_status not null default 'pending',
  amount integer not null default 0,
  discount_amount integer not null default 0,
  applied_user_voucher_id uuid,
  points_earned integer not null default 0,
  whatsapp_contact text,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint orders_item_chk check (
    (category = 'product' and product_id is not null and joki_package_id is null)
    or (category = 'joki' and joki_package_id is not null and product_id is null)
  )
);

create table public.user_vouchers (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles (id) on delete cascade not null,
  tier_key text not null,
  discount_type text not null check (discount_type in ('percent', 'fixed')),
  discount_value numeric(12, 2) not null,
  cost_points integer not null default 0,
  consumed boolean not null default false,
  consumed_order_id uuid,
  expires_at timestamptz,
  created_at timestamptz not null default now()
);

alter table public.orders
  add constraint orders_applied_voucher_fkey
  foreign key (applied_user_voucher_id) references public.user_vouchers (id) on delete set null;

alter table public.user_vouchers
  add constraint user_vouchers_consumed_order_fkey
  foreign key (consumed_order_id) references public.orders (id) on delete set null;

create table public.point_logs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles (id) on delete cascade not null,
  delta integer not null,
  reason text not null,
  ref_order_id uuid references public.orders (id) on delete set null,
  created_at timestamptz not null default now()
);

create table public.testimonials (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles (id) on delete cascade not null,
  category text not null check (category in ('app_premium', 'joki')),
  rating smallint not null check (rating between 1 and 5),
  content text not null,
  verified_order_id uuid references public.orders (id) on delete set null,
  approved boolean not null default false,
  created_at timestamptz not null default now()
);

create index testimonials_approved_category_idx on public.testimonials (approved, category);

create table public.activity_logs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles (id) on delete set null,
  action text not null,
  details jsonb default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table public.site_settings (
  key text primary key,
  value jsonb not null default '{}'::jsonb,
  updated_at timestamptz not null default now()
);

-- Profile on signup
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, full_name, email, avatar_url, provider)
  values (
    new.id,
    coalesce(
      new.raw_user_meta_data ->> 'full_name',
      new.raw_user_meta_data ->> 'name',
      ''
    ),
    new.email,
    new.raw_user_meta_data ->> 'avatar_url',
    coalesce(lower(new.raw_app_meta_data ->> 'provider'), 'email')
  );
  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 or replace function public.touch_profiles_updated ()
returns trigger language plpgsql as $$
begin new.updated_at = now(); return new; end;
$$;

create trigger profiles_touch before update on public.profiles for each row
  execute function public.touch_profiles_updated ();

-- Award points after joki order completed (Rp10k = 1 pt)
create or replace function public.on_order_status_change ()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  pts int;
begin
  if tg_op = 'UPDATE'
     and new.status = 'completed'
     and old.status is distinct from 'completed'
     and new.category = 'joki'
     and new.user_id is not null
  then
    pts := floor(new.amount / 10000.0)::int;
    if pts > 0 then
      update public.profiles
      set points = points + pts,
          total_transactions = total_transactions + 1,
          updated_at = now()
      where id = new.user_id;

      insert into public.point_logs (user_id, delta, reason, ref_order_id)
      values (new.user_id, pts, 'joki_order_completed', new.id);

      new.points_earned := pts;
    else
      update public.profiles
      set total_transactions = total_transactions + 1,
          updated_at = now()
      where id = new.user_id;
    end if;
  elsif tg_op = 'UPDATE'
        and new.status = 'completed'
        and old.status is distinct from 'completed'
        and new.category = 'product'
        and new.user_id is not null then
    update public.profiles
    set total_transactions = total_transactions + 1,
        updated_at = now()
    where id = new.user_id;
  end if;
  return new;
end;
$$;

drop trigger if exists orders_status_award_points on public.orders;
create trigger orders_status_award_points
  before update of status on public.orders for each row
  execute function public.on_order_status_change ();

-- Consume voucher when referenced + completed optional: mark voucher used
create or replace function public.consume_voucher_on_completed ()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if new.status = 'completed'
     and old.status is distinct from 'completed'
     and new.applied_user_voucher_id is not null
  then
    update public.user_vouchers
    set consumed = true, consumed_order_id = new.id
    where id = new.applied_user_voucher_id;
  end if;
  return new;
end;
$$;

drop trigger if exists orders_complete_consume_voucher on public.orders;
create trigger orders_complete_consume_voucher
  after update of status on public.orders for each row
  execute function public.consume_voucher_on_completed ();

alter table public.profiles enable row level security;
alter table public.products enable row level security;
alter table public.joki_packages enable row level security;
alter table public.orders enable row level security;
alter table public.user_vouchers enable row level security;
alter table public.point_logs enable row level security;
alter table public.testimonials enable row level security;
alter table public.activity_logs enable row level security;
alter table public.site_settings enable row level security;

-- Profiles
create policy "profiles_self_select"
  on public.profiles for select
  using (id = auth.uid()
         or exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

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

-- Products public read active; admin full
create policy "products_public_read_active"
  on public.products for select
  using (active = true or exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

create policy "products_admin_write"
  on public.products for all
  using (exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'))
  with check (exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

create policy "joki_public_read_active"
  on public.joki_packages for select
  using (active = true or exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

create policy "joki_admin_write"
  on public.joki_packages for all
  using (exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'))
  with check (exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

-- Orders
create policy "orders_self_or_admin"
  on public.orders for select
  using (user_id = auth.uid()
         or exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

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

create policy "orders_admin_update"
  on public.orders for update
  using (exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

-- User vouchers
create policy "uv_self_or_admin"
  on public.user_vouchers for select
  using (user_id = auth.uid()
         or exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

create policy "uv_insert_member_or_admin"
  on public.user_vouchers for insert
  with check (
    user_id = auth.uid()
    or exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin')
  );

create policy "uv_update_member_or_admin"
  on public.user_vouchers for update
  using (user_id = auth.uid()
         or exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin'));

-- Point logs (trigger SECURITY DEFINER tetap bisa menulis tanpa policy)
create policy "pl_self_or_admin"
  on public.point_logs for select
  using (user_id = auth.uid()
         or exists (select 1 from public.profiles a where a.id = auth.uid() and a.role = 'admin'));

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

create policy "pl_admin_insert"
  on public.point_logs for insert
  with check (exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin'));

-- Testimonials (publik setelah approve)
create policy "tst_read_public_own_or_admin"
  on public.testimonials for select
  using (
    approved
    or user_id = auth.uid()
    or exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin')
  );

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

create policy "tst_admin_update"
  on public.testimonials for update
  using (exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin'));

-- Activity logs
create policy "activity_read_self_admin"
  on public.activity_logs for select
  using (
    user_id = auth.uid()
    or exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin')
  );

create policy "activity_insert_logged_in"
  on public.activity_logs for insert
  to authenticated
  with check (
    user_id = auth.uid()
    or user_id is null
    or exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin')
  );

-- Site settings: baca untuk publik storefront, tulis admin
create policy "settings_read_public"
  on public.site_settings for select
  using (true);

create policy "settings_write_admin"
  on public.site_settings for all
  using (exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin'))
  with check (exists (select 1 from public.profiles p where p.id = auth.uid() and p.role = 'admin'));

/*
Storage (Supabase UI):
- Buat bucket `avatars`, public read.
- Tambahkan policy: user auth boleh upload/delete file prefix auth.uid()/...
*/

-- Seed demo (hapus atau sesuaikan)
insert into public.products (name, slug, description, price, stock, featured, active)
values
  ('Canva Pro', 'canva-pro', 'Akun/reseller Canva Pro terpercaya. Aktivasi cepat.', 49000, 40, true, true),
  ('CapCut Pro', 'capcut-pro', 'Langganan CapCut Pro / region ID.', 35000, 25, true, true),
  ('ChatGPT Plus', 'chatgpt-plus', 'Slot / sharing sesuai S&K penyedia.', 185000, 10, true, true),
  ('VPN Premium', 'vpn-premium', 'Paket VPN kecepatan stabil bulanan.', 25000, 50, false, true),
  ('Apple Music', 'apple-music', 'Family / individual renewal.', 45000, 30, false, true),
  ('Disney+', 'disney-plus', 'Sharing profile aman sesuai slot.', 40000, 20, false, true),
  ('HBO', 'hbo-max', 'Akses streaming HBO wilayah ID.', 35000, 15, false, true),
  ('iQIYI', 'iqiyi', 'VIP streaming drachin & lebih.', 30000, 18, false, true);

insert into public.joki_packages (name, slug, description, price, estimated_days, point_reward, game_tag, active)
values
  ('Joki Genshin — Daily Lite', 'joki-genshin-daily-lite', 'Daily resin + expedition harian.', 80000, 7, 8, 'genshin', true),
  ('Joki Genshin — Spiral Abyss', 'joki-genshin-abyss', 'Full clear Spiral Abyss per season.', 150000, 3, 15, 'genshin', true),
  ('Joki WuWa — Farming', 'joki-wuwa-farming', 'Farming material elite & echo.', 120000, 5, 12, 'wuthering', true),
  ('Joki HSR — Forgotten Hall', 'joki-hsr-hall', 'Clear Forgotten Hall maks stars.', 100000, 4, 10, 'hsr', true),
  ('Paket Quest & Story', 'joki-quest-bundle', 'Selesai quest utama / cerita besar.', 200000, 10, 20, 'quest', true),
  ('Eksplorasi Map Full', 'joki-exploration', 'Eksplorasi map + koleksi chest.', 250000, 14, 25, 'exploration', true),
  ('Event Weekly Bundle', 'joki-event-bundle', 'Selesai event limited time.', 90000, 5, 9, 'event', true);

insert into public.site_settings (key, value)
values
  ('store_name', '{"value":"AnzzzStore"}'::jsonb),
  ('hero_cta_secondary', '{"value":"/kontak"}'::jsonb)
on conflict (key) do nothing;