-- ============================================================
-- SKEMA DATABASE — Jaguar Season 6 (Voting Sekolah Se-Sumatera)
-- PostgreSQL
--
-- v2: mendukung TINGKAT (levels) + CABANG LOMBA (lomba), di
-- mana satu sekolah bisa punya banyak ENTRI cabang lomba
-- (school_entries), masing-masing dengan suara sendiri —
-- sesuai struktur LEVELS / LOMBA_LIST / entries di frontend
-- (js/data.js).
-- ============================================================

CREATE TABLE IF NOT EXISTS admins (
  id            SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  email         TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Tingkat lomba, misalnya "SD/MI" atau "SMP/MTs, SMA/SMK/MA, Management/Purna".
CREATE TABLE IF NOT EXISTS levels (
  id         TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  fee        INTEGER NOT NULL DEFAULT 0,
  sort_order INTEGER NOT NULL DEFAULT 0
);

-- Cabang lomba (LKBB, Gerak Jalan, dst), masing-masing terikat ke satu tingkat.
-- Nama cabang boleh sama di tingkat berbeda (mis. "LKBB" di SD/MI dan di
-- SMP/SMA), tapi id & suaranya tetap dihitung terpisah karena baris berbeda.
CREATE TABLE IF NOT EXISTS lomba (
  id         TEXT PRIMARY KEY,
  level_id   TEXT NOT NULL REFERENCES levels(id) ON DELETE RESTRICT,
  name       TEXT NOT NULL,
  active     BOOLEAN NOT NULL DEFAULT true,
  sort_order INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_lomba_level ON lomba(level_id);

CREATE TABLE IF NOT EXISTS schools (
  id           TEXT PRIMARY KEY,
  name         TEXT NOT NULL,
  city         TEXT NOT NULL,
  province     TEXT NOT NULL,
  description  TEXT DEFAULT '',
  level_id     TEXT REFERENCES levels(id) ON DELETE RESTRICT,
  -- votes_count = TOTAL agregat semua cabang lomba yang diikuti sekolah ini.
  -- Nilai ini SELALU sama dengan SUM(school_entries.votes_count) milik sekolah
  -- tsb — dijaga tetap sinkron di kode (checkout webhook & endpoint tambah
  -- suara manual), bukan dihitung ulang dari sini. Dipakai untuk ringkasan
  -- admin/leaderboard gabungan; leaderboard PER CABANG LOMBA memakai
  -- school_entries.votes_count langsung.
  votes_count  BIGINT NOT NULL DEFAULT 0,
  active       BOOLEAN NOT NULL DEFAULT true,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_schools_level ON schools(level_id);

-- Entri satu sekolah di satu cabang lomba tertentu = satu leaderboard/voting
-- terpisah. Satu sekolah bisa punya banyak baris di sini (satu per lomba
-- yang diikuti).
CREATE TABLE IF NOT EXISTS school_entries (
  school_id    TEXT NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  lomba_id     TEXT NOT NULL REFERENCES lomba(id) ON DELETE RESTRICT,
  votes_count  BIGINT NOT NULL DEFAULT 0,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (school_id, lomba_id)
);
CREATE INDEX IF NOT EXISTS idx_school_entries_lomba ON school_entries(lomba_id);

CREATE TABLE IF NOT EXISTS packages (
  id       TEXT PRIMARY KEY,
  name     TEXT NOT NULL,
  votes    INTEGER NOT NULL,
  price    INTEGER NOT NULL,
  badge    TEXT,
  active   BOOLEAN NOT NULL DEFAULT true
);

-- Setiap transaksi = satu invoice/order Midtrans, ditujukan ke satu
-- (school_id, lomba_id) — yaitu satu baris school_entries tertentu.
CREATE TABLE IF NOT EXISTS transactions (
  id                TEXT PRIMARY KEY,             -- order_id, contoh: INV-2026-000123
  school_id         TEXT NOT NULL REFERENCES schools(id),
  lomba_id          TEXT NOT NULL REFERENCES lomba(id),
  package_id        TEXT NOT NULL REFERENCES packages(id),
  user_name         TEXT NOT NULL,
  user_email        TEXT NOT NULL,
  user_phone        TEXT,
  votes             INTEGER NOT NULL,
  amount            INTEGER NOT NULL,
  method            TEXT,                          -- diisi setelah Midtrans callback
  status            TEXT NOT NULL DEFAULT 'Pending',-- Pending | Paid | Failed | Expired | Refunded
  midtrans_raw_status TEXT,
  counted           BOOLEAN NOT NULL DEFAULT false, -- flag anti dobel-hitung suara
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_transactions_status ON transactions(status);
CREATE INDEX IF NOT EXISTS idx_transactions_school ON transactions(school_id);
CREATE INDEX IF NOT EXISTS idx_transactions_lomba ON transactions(lomba_id);

-- Log suara: insert-only, jadi jejak audit yang tidak bisa diubah dari UI biasa.
-- Total suara satu entri (school_id, lomba_id) SEHARUSNYA selalu bisa
-- direkonstruksi ulang dari SUM(votes) baris-baris tabel ini.
--
-- source membedakan dua-satunya jalur penambahan suara yang sah:
--   'midtrans' -> otomatis dari webhook pembayaran yang terverifikasi
--   'manual'   -> ditambahkan admin lewat panel (lihat catatan keamanan di README)
-- Baris 'manual' WAJIB mengisi admin_id + note (alasan), dan transaction_id
-- boleh NULL karena tidak berasal dari transaksi Midtrans.
CREATE TABLE IF NOT EXISTS vote_logs (
  id             SERIAL PRIMARY KEY,
  transaction_id TEXT REFERENCES transactions(id),
  school_id      TEXT NOT NULL REFERENCES schools(id),
  lomba_id       TEXT NOT NULL REFERENCES lomba(id),
  votes          INTEGER NOT NULL,
  source         TEXT NOT NULL DEFAULT 'midtrans' CHECK (source IN ('midtrans','manual')),
  admin_id       INTEGER REFERENCES admins(id),
  note           TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (transaction_id) -- 1 transaksi hanya boleh menyumbang 1 baris log (anti dobel hitung)
);
CREATE INDEX IF NOT EXISTS idx_vote_logs_school ON vote_logs(school_id);
CREATE INDEX IF NOT EXISTS idx_vote_logs_lomba ON vote_logs(lomba_id);
CREATE INDEX IF NOT EXISTS idx_vote_logs_source ON vote_logs(source);

-- ---------- Seed dasar (boleh diedit lewat admin nanti) ----------

-- Harga: Rp1.000 per suara. Samakan dengan HARGA_PER_SUARA di js/data.js.
-- ON CONFLICT DO UPDATE: menjalankan ulang schema.sql akan menyamakan
-- harga & jumlah suara paket yang sudah ada di database.
INSERT INTO packages (id, name, votes, price, badge, active) VALUES
  ('satuan',   '1 Suara',        1,   1000,   NULL,             true),
  ('basic',    'Paket Basic',    5,   5000,   NULL,             true),
  ('standard', 'Paket Standard', 15,  15000,  NULL,             true),
  ('popular',  'Paket Popular',  50,  50000,  'PALING POPULER', true),
  ('premium',  'Paket Premium',  100, 100000, NULL,             true)
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name, votes = EXCLUDED.votes,
      price = EXCLUDED.price, badge = EXCLUDED.badge;

INSERT INTO levels (id, name, fee, sort_order) VALUES
  ('sd-mi',         'SD/MI',                                             600000, 1),
  ('smp-sma-purna', 'SMP/MTs, SMA/SMK/MA, Management/Purna',             700000, 2)
ON CONFLICT (id) DO NOTHING;

INSERT INTO lomba (id, level_id, name, sort_order) VALUES
  ('sdmi-lkbb',              'sd-mi',         'LKBB',            1),
  ('sdmi-gerak-jalan',       'sd-mi',         'Gerak Jalan',     2),
  ('sdmi-tari-komando',      'sd-mi',         'Tari Komando',    3),
  ('smasma-lkbb',            'smp-sma-purna', 'LKBB',            1),
  ('smasma-variasi-formasi', 'smp-sma-purna', 'Variasi Formasi', 2),
  ('smasma-tari-komando',    'smp-sma-purna', 'Tari Komando',    3)
ON CONFLICT (id) DO NOTHING;