-- =========================================================
-- AUTO ORDER DIGITAL STORE - SUPABASE SCHEMA
-- Jalankan seluruh file ini di Supabase SQL Editor
-- =========================================================

-- Extension untuk UUID
create extension if not exists "uuid-ossp";

-- =========================================================
-- TABLE: profiles
-- Menyimpan data tambahan admin, terhubung ke auth.users bawaan Supabase Auth
-- =========================================================
create table if not exists profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  full_name text,
  role text default 'admin',
  created_at timestamptz default now()
);

-- =========================================================
-- TABLE: products
-- =========================================================
create table if not exists products (
  id uuid primary key default uuid_generate_v4(),
  name text not null,
  category text not null,
  price numeric(12,2) not null default 0,
  description text,
  status boolean default true, -- true = aktif, false = nonaktif
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- =========================================================
-- TABLE: stocks
-- Setiap baris = satu unit stok (misal 1 akun) milik satu produk
-- =========================================================
create table if not exists stocks (
  id uuid primary key default uuid_generate_v4(),
  product_id uuid not null references products(id) on delete cascade,
  data text not null, -- contoh: "email1@gmail.com|password1"
  created_at timestamptz default now()
);

create index if not exists idx_stocks_product_id on stocks(product_id);

-- =========================================================
-- TABLE: orders (riwayat transaksi)
-- =========================================================
create table if not exists orders (
  id uuid primary key default uuid_generate_v4(),
  product_id uuid references products(id) on delete set null,
  product_name text not null, -- disimpan redundant agar riwayat tetap utuh walau produk dihapus
  telegram_user_id text not null,
  telegram_username text,
  stock_data text not null, -- isi stok yang terkirim ke pembeli
  price numeric(12,2) not null default 0,
  created_at timestamptz default now()
);

create index if not exists idx_orders_product_id on orders(product_id);
create index if not exists idx_orders_telegram_user_id on orders(telegram_user_id);

-- =========================================================
-- TABLE: subscribers
-- Menyimpan setiap user Telegram yang pernah /start bot,
-- dipakai sebagai daftar tujuan fitur Broadcast.
-- =========================================================
create table if not exists subscribers (
  telegram_user_id text primary key,
  telegram_username text,
  first_seen timestamptz default now(),
  last_seen timestamptz default now()
);

-- =========================================================
-- TRIGGER: auto update updated_at pada products
-- =========================================================
create or replace function set_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

drop trigger if exists trg_products_updated_at on products;
create trigger trg_products_updated_at
before update on products
for each row execute procedure set_updated_at();

drop trigger if exists trg_settings_updated_at on settings;

-- =========================================================
-- ROW LEVEL SECURITY
-- Backend mengakses via SERVICE ROLE KEY (bypass RLS), jadi RLS di sini
-- hanya untuk menutup akses langsung dari anon key ke tabel data.
-- =========================================================
alter table products enable row level security;
alter table stocks enable row level security;
alter table orders enable row level security;
alter table profiles enable row level security;
alter table subscribers enable row level security;

-- Tidak ada policy anon dibuat sengaja -> akses hanya lewat backend (service role)
-- kecuali profiles: admin boleh baca profil sendiri
create policy "profiles_select_own" on profiles
  for select using (auth.uid() = id);

-- =========================================================
-- PAYMENT SYSTEM (saldo, admin, transaksi QRIS deposit/pembelian)
-- =========================================================

-- Saldo & role admin nempel di tabel subscribers (satu user Telegram = satu baris)
alter table subscribers add column if not exists saldo numeric(12,2) not null default 0;
alter table subscribers add column if not exists is_admin boolean not null default false;

-- payment_method dicatat di setiap order (saldo / qris)
alter table orders add column if not exists payment_method text not null default 'saldo';

-- =========================================================
-- TABLE: transactions
-- Menyimpan transaksi QRIS (deposit saldo & pembelian produk) yang masih
-- pending sampai dikonfirmasi oleh check-pending (otomatis) atau /acc (manual).
-- =========================================================
create table if not exists transactions (
  id uuid primary key default uuid_generate_v4(),
  type text not null check (type in ('deposit', 'purchase')),
  telegram_user_id text not null,
  telegram_username text,
  product_id uuid references products(id) on delete set null,
  product_name text,
  quantity int default 1,
  amount numeric(12,2) not null default 0,
  fee_amount numeric(12,2) not null default 0, -- fee QRIS (punya OWNER, bukan pendapatan admin)
  reff_id text unique not null,
  transaction_id text, -- id transaksi dari Casaku/Cashify, dipakai buat cek status
  qr_image_url text,
  status text not null default 'pending' check (status in ('pending', 'paid', 'expired', 'failed', 'cancelled')),
  payment_method text not null default 'qris',
  qr_chat_id text, -- chat id tempat pesan foto QR dikirim, dipakai buat hapus pesan QR pas udah dibayar
  qr_message_id bigint, -- message_id pesan foto QR, dipakai buat hapus pesan QR pas udah dibayar
  created_at timestamptz default now(),
  paid_at timestamptz,
  expired_at timestamptz
);

create index if not exists idx_transactions_status on transactions(status);
create index if not exists idx_transactions_telegram_user_id on transactions(telegram_user_id);

alter table transactions enable row level security;
-- Tidak ada policy anon dibuat sengaja -> akses hanya lewat backend (service role)

-- =========================================================
-- FITUR: Kode produk singkat, harga grosir (tiered pricing), qty per order
-- =========================================================
alter table products add column if not exists code text;
-- wholesale_prices: array of {"min": <qty>, "price": <harga_satuan>}, urut menaik dari min
alter table products add column if not exists wholesale_prices jsonb not null default '[]'::jsonb;
alter table orders add column if not exists quantity int not null default 1;

-- Tandai kepemilikan data per admin. Nullable supaya data lama (instalasi
-- single-tenant sebelumnya) tidak langsung error; owner bisa assign manual
-- lewat SQL kalau mau migrasi data lama ke admin tertentu.
alter table products add column if not exists admin_id uuid references admin_accounts(id) on delete cascade;
alter table stocks add column if not exists admin_id uuid references admin_accounts(id) on delete cascade;
alter table orders add column if not exists admin_id uuid references admin_accounts(id) on delete cascade;
alter table transactions add column if not exists admin_id uuid references admin_accounts(id) on delete cascade;

create index if not exists idx_products_admin_id on products(admin_id);
create index if not exists idx_stocks_admin_id on stocks(admin_id);
create index if not exists idx_orders_admin_id on orders(admin_id);
create index if not exists idx_transactions_admin_id on transactions(admin_id);

-- subscribers: satu orang Telegram yang chat ke bot Admin A harus jadi baris
-- TERPISAH dari orang yang sama chat ke bot Admin B -> ganti primary key dari
-- telegram_user_id saja, jadi (admin_id, telegram_user_id).
alter table subscribers add column if not exists id uuid default uuid_generate_v4();
alter table subscribers add column if not exists admin_id uuid references admin_accounts(id) on delete cascade;
update subscribers set id = uuid_generate_v4() where id is null;
alter table subscribers alter column id set not null;
alter table subscribers drop constraint if exists subscribers_pkey;
alter table subscribers add primary key (id);
create unique index if not exists uq_subscribers_admin_telegram on subscribers(admin_id, telegram_user_id);
create index if not exists idx_subscribers_admin_id on subscribers(admin_id);

-- =========================================================
-- TABLE: withdrawals
-- Permintaan tarik saldo pendapatan admin, disetujui/ditolak oleh owner.
-- =========================================================
create table if not exists withdrawals (
  id uuid primary key default uuid_generate_v4(),
  admin_id uuid not null references admin_accounts(id) on delete cascade,
  amount numeric(12,2) not null,
  note text,
  status text not null default 'pending' check (status in ('pending', 'approved', 'rejected')),
  created_at timestamptz default now(),
  processed_at timestamptz
);

create index if not exists idx_withdrawals_admin_id on withdrawals(admin_id);
create index if not exists idx_withdrawals_status on withdrawals(status);

alter table withdrawals enable row level security;
-- Tidak ada policy anon dibuat sengaja -> akses hanya lewat backend (service role)

-- =========================================================
-- TABLE: global_settings
-- Singleton (1 baris, id=1) khusus OWNER: fee platform (dipakai di notif
-- transaksi) + konfigurasi grup "TRX ALL BOT" (rekap transaksi SEMUA admin,
-- dikirim lewat bot milik owner sendiri).
-- =========================================================
create table if not exists global_settings (
  id int primary key default 1,
  platform_fee numeric(12,2) not null default 0,
  all_trx_group_chat_id text,
  notify_all_trx_group boolean not null default false,
  updated_at timestamptz default now(),
  constraint global_settings_singleton check (id = 1)
);

insert into global_settings (id)
values (1)
on conflict (id) do nothing;

-- =========================================================
-- FITUR: Auto Backup Source Code — owner bisa atur interval (tiap berapa jam)
-- backup .zip source code otomatis dikirim lewat bot owner ke Telegram owner.
-- =========================================================
alter table global_settings add column if not exists auto_backup_enabled boolean not null default false;
alter table global_settings add column if not exists backup_interval_hours int not null default 24;
alter table global_settings add column if not exists last_backup_at timestamptz;

-- =========================================================
-- FITUR: Notif "Akun Admin Baru" ke kontak Telegram admin yang baru dibuat
-- (dikirim lewat bot OWNER). Owner isi link grup resmi & link web stok sekali
-- di sini, otomatis kepakai di pesan sambutan tiap kali ada admin baru dibuat.
-- =========================================================
alter table global_settings add column if not exists official_group_link text;
alter table global_settings add column if not exists stock_web_link text;

-- =========================================================
-- TABLE: notifications
-- Notifikasi broadcast dari OWNER, tampil di lonceng notif semua admin
-- (dikelola lewat halaman Kelola Admin -> Kirim Notifikasi).
-- =========================================================
create table if not exists notifications (
  id uuid primary key default uuid_generate_v4(),
  title text not null,
  message text not null,
  created_by uuid references admin_accounts(id) on delete set null,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create index if not exists idx_notifications_created_at on notifications(created_at desc);

alter table notifications enable row level security;
-- Tidak ada policy anon dibuat sengaja -> akses hanya lewat backend (service role)

-- =========================================================
-- TABLE: notification_reads
-- Nyimpen kapan tiap admin terakhir buka lonceng notif, dipakai buat
-- ngitung badge "belum dibaca" per admin.
-- =========================================================
create table if not exists notification_reads (
  admin_id uuid primary key references admin_accounts(id) on delete cascade,
  last_read_at timestamptz default now()
);

alter table notification_reads enable row level security;
-- Tidak ada policy anon dibuat sengaja -> akses hanya lewat backend (service role)

