-- =========================================================
-- 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: settings
-- Single-row config untuk Telegram Bot (BOT_TOKEN, ADMIN_ID, BOT_USERNAME, status aktif)
-- =========================================================
create table if not exists settings (
  id int primary key default 1,
  bot_token text,
  admin_id text,
  bot_username text,
  store_name text default 'Auto Order Digital Store',
  banner_image text, -- data URL base64 (image/png;base64,...) buat header List Produk
  is_active boolean default false,
  updated_at timestamptz default now(),
  constraint settings_singleton check (id = 1)
);

-- pastikan selalu ada 1 baris default
insert into settings (id, bot_token, admin_id, bot_username, store_name, is_active)
values (1, null, null, null, 'Auto Order Digital Store', false)
on conflict (id) do nothing;

-- =========================================================
-- 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;
create trigger trg_settings_updated_at
before update on settings
for each row execute procedure set_updated_at();

-- =========================================================
-- 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 settings 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;

-- =========================================================
-- TABLE: admin_accounts
-- Akun admin panel tambahan yang dibuat khusus oleh OWNER (login .env).
-- Owner tetap login pakai ADMIN_EMAIL/ADMIN_PASSWORD di .env (role 'owner').
-- Admin yang dibuat di sini selalu role 'admin' (tidak bisa kelola admin lain / lihat finansial).
-- =========================================================
create table if not exists admin_accounts (
  id uuid primary key default uuid_generate_v4(),
  email text unique not null,
  username text unique not null,
  password_hash text not null,
  role text not null default 'admin' check (role in ('admin')),
  created_at timestamptz default now()
);

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

-- =========================================================
-- FITUR: MULTI-TENANT — tiap admin punya bot, produk, stok,
-- order, user (subscriber), dan setting Telegram-nya SENDIRI.
-- Owner (login .env) tidak punya toko sendiri; owner hanya mengelola
-- akun admin & memantau data bot + kontak tiap admin.
-- =========================================================

-- Saldo pendapatan admin (hasil jualan, bisa ditarik/withdraw) & kontak
-- yang ditampilkan ke owner supaya gampang dihubungi.
alter table admin_accounts add column if not exists saldo numeric(12,2) not null default 0;
alter table admin_accounts add column if not exists telegram_contact text;

-- Halaman "Profil": foto profil (data URL base64, sama pola-nya kayak banner_image
-- di settings) & kontak WhatsApp. telegram_contact sudah ada di atas, dipakai ulang.
alter table admin_accounts add column if not exists photo_url text;
alter table admin_accounts add column if not exists whatsapp_contact text;

-- Username Telegram (contoh: @Nidalopstore19) — MURNI info kontak buat owner,
-- beda sama telegram_contact di atas yang WAJIB ID angka (dipakai bot buat kirim
-- notif sambutan admin baru). Jangan disatuin lagi, supaya notif bot gak rusak
-- kalau admin isi username di sini.
alter table admin_accounts add column if not exists telegram_username text;

-- Owner sekarang juga BISA punya toko/bot sendiri (bukan cuma ngatur admin).
-- Baris admin_accounts milik owner ditandai is_owner=true supaya tetap disembunyikan
-- dari daftar "Kelola Admin" (yang isinya cuma admin biasa yang owner kelola).
alter table admin_accounts add column if not exists is_owner boolean not null default false;
alter table admin_accounts drop constraint if exists admin_accounts_role_check;
alter table admin_accounts add constraint admin_accounts_role_check check (role in ('admin', 'owner'));

-- 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);

-- settings: dari 1 baris global (id=1) -> 1 baris per admin.
-- Kolom lama "admin_id" (isinya Telegram ID owner toko) di-rename supaya
-- tidak bentrok namanya dengan admin_id (uuid) = pemilik baris setting ini.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'settings' AND column_name = 'admin_id' AND data_type = 'text'
  ) THEN
    ALTER TABLE settings RENAME COLUMN admin_id TO owner_telegram_id;
  END IF;
END $$;

alter table settings drop constraint if exists settings_singleton;
alter table settings drop constraint if exists settings_pkey;
alter table settings add column if not exists admin_id uuid references admin_accounts(id) on delete cascade;
alter table settings add column if not exists group_chat_id text;
alter table settings add column if not exists notify_group boolean not null default false;
alter table settings add column if not exists notify_owner boolean not null default true;
create unique index if not exists uq_settings_admin_id on settings(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)