-- ════════════════════════════════════════════════════════════
-- SETUP KEAMANAN — SUPABASE AUTH + ROW LEVEL SECURITY
-- ════════════════════════════════════════════════════════════
-- PENTING: Jalankan SQL ini SETELAH Anda membuat akun login
-- di Authentication → Users (lihat panduan di atas).
-- SQL ini akan mengganti sistem keamanan lama (allow_all / publik)
-- menjadi: HANYA user yang login via Supabase Auth yang bisa akses data.

-- 1. Tabel PROFILES — menyimpan role tiap akun (admin / owner)
CREATE TABLE IF NOT EXISTS profiles (
  id          uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email       text,
  role        text NOT NULL DEFAULT 'owner' CHECK (role IN ('admin','owner')),
  nama        text DEFAULT '',
  created_at  timestamptz DEFAULT now()
);
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Setiap user hanya boleh baca profil dirinya sendiri & semua user lain boleh dibaca (untuk tampilkan nama)
DROP POLICY IF EXISTS "profiles_select_own" ON profiles;
CREATE POLICY "profiles_select_own" ON profiles FOR SELECT USING (auth.uid() = id);

-- Hanya boleh update profil sendiri (bukan ganti role sendiri jadi admin!)
DROP POLICY IF EXISTS "profiles_update_own" ON profiles;
CREATE POLICY "profiles_update_own" ON profiles FOR UPDATE USING (auth.uid() = id);

-- Trigger otomatis: setiap kali ada user baru daftar via Supabase Auth,
-- otomatis dibuatkan row di profiles dengan role default 'owner'.
-- (Ubah role jadi 'admin' manual lewat Table Editor setelah akun dibuat)
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, email, role)
  VALUES (new.id, new.email, 'owner')
  ON CONFLICT (id) DO NOTHING;
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

-- Fungsi bantu: cek apakah user yang sedang login adalah admin
CREATE OR REPLACE FUNCTION is_admin()
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- ════════════════════════════════════════════════════════════
-- 2. HAPUS SEMUA POLICY LAMA YANG MENGIZINKAN AKSES PUBLIK (anon)
-- ════════════════════════════════════════════════════════════
DROP POLICY IF EXISTS "Allow anon read"   ON koling_penjualan;
DROP POLICY IF EXISTS "Allow anon insert" ON koling_penjualan;
DROP POLICY IF EXISTS "Allow anon delete" ON koling_penjualan;
DROP POLICY IF EXISTS "Allow anon update" ON koling_penjualan;

DROP POLICY IF EXISTS "Allow anon read"   ON koling_sellin;
DROP POLICY IF EXISTS "Allow anon insert" ON koling_sellin;
DROP POLICY IF EXISTS "Allow anon delete" ON koling_sellin;
DROP POLICY IF EXISTS "Allow anon update" ON koling_sellin;

DROP POLICY IF EXISTS "Allow anon read"   ON koling_outlet;
DROP POLICY IF EXISTS "Allow anon insert" ON koling_outlet;
DROP POLICY IF EXISTS "Allow anon delete" ON koling_outlet;
DROP POLICY IF EXISTS "Allow anon update" ON koling_outlet;

DROP POLICY IF EXISTS "Allow anon read"   ON koling_piutang_bayar;
DROP POLICY IF EXISTS "Allow anon insert" ON koling_piutang_bayar;
DROP POLICY IF EXISTS "Allow anon delete" ON koling_piutang_bayar;
DROP POLICY IF EXISTS "Allow anon update" ON koling_piutang_bayar;

DROP POLICY IF EXISTS "Allow anon read"   ON koling_stock_opname;
DROP POLICY IF EXISTS "Allow anon insert" ON koling_stock_opname;
DROP POLICY IF EXISTS "Allow anon delete" ON koling_stock_opname;
DROP POLICY IF EXISTS "Allow anon update" ON koling_stock_opname;

DROP POLICY IF EXISTS "allow_all_targets" ON koling_targets;

-- ════════════════════════════════════════════════════════════
-- 3. POLICY BARU — HANYA USER LOGIN (authenticated) YANG BOLEH AKSES
--    Semua role (admin & owner) boleh READ, INSERT, UPDATE.
--    HANYA admin yang boleh DELETE.
-- ════════════════════════════════════════════════════════════

-- koling_penjualan
CREATE POLICY "auth_read_penjualan"   ON koling_penjualan FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "auth_insert_penjualan" ON koling_penjualan FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "auth_update_penjualan" ON koling_penjualan FOR UPDATE USING (auth.role() = 'authenticated') WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "admin_delete_penjualan" ON koling_penjualan FOR DELETE USING (is_admin());

-- koling_sellin
CREATE POLICY "auth_read_sellin"   ON koling_sellin FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "auth_insert_sellin" ON koling_sellin FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "auth_update_sellin" ON koling_sellin FOR UPDATE USING (auth.role() = 'authenticated') WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "admin_delete_sellin" ON koling_sellin FOR DELETE USING (is_admin());

-- koling_outlet
CREATE POLICY "auth_read_outlet"   ON koling_outlet FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "auth_insert_outlet" ON koling_outlet FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "auth_update_outlet" ON koling_outlet FOR UPDATE USING (auth.role() = 'authenticated') WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "admin_delete_outlet" ON koling_outlet FOR DELETE USING (is_admin());

-- koling_piutang_bayar
CREATE POLICY "auth_read_piutang"   ON koling_piutang_bayar FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "auth_insert_piutang" ON koling_piutang_bayar FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "auth_update_piutang" ON koling_piutang_bayar FOR UPDATE USING (auth.role() = 'authenticated') WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "admin_delete_piutang" ON koling_piutang_bayar FOR DELETE USING (is_admin());

-- koling_stock_opname
CREATE POLICY "auth_read_opname"   ON koling_stock_opname FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "auth_insert_opname" ON koling_stock_opname FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "auth_update_opname" ON koling_stock_opname FOR UPDATE USING (auth.role() = 'authenticated') WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "admin_delete_opname" ON koling_stock_opname FOR DELETE USING (is_admin());

-- koling_targets
CREATE POLICY "auth_read_targets"   ON koling_targets FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "auth_insert_targets" ON koling_targets FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "auth_update_targets" ON koling_targets FOR UPDATE USING (auth.role() = 'authenticated') WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "admin_delete_targets" ON koling_targets FOR DELETE USING (is_admin());

SELECT 'Keamanan database berhasil diaktifkan! Hanya user login yang bisa akses data. ✅' AS status;

-- Jalankan SQL ini di Supabase SQL Editor
-- Dashboard → SQL Editor → New Query → paste → Run

CREATE TABLE IF NOT EXISTS koling_penjualan (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at  timestamptz DEFAULT now(),
  tgl         date NOT NULL,
  nama        text NOT NULL,
  -- kolom tim dihapus (sistem tidak lagi menggunakan Tim 1/Tim 2)
  awal        numeric DEFAULT 0,
  akhir       numeric DEFAULT 0,
  terjual     numeric DEFAULT 0,
  noo         int  DEFAULT 0,
  outlet      text DEFAULT '',
  alamat      text DEFAULT '',
  hp          text DEFAULT '',
  produk      text DEFAULT 'Produk A',
  setoran     numeric DEFAULT 0,
  kom_dasar   numeric DEFAULT 0,
  bonus       numeric DEFAULT 0,
  kick        numeric DEFAULT 0,
  tot_kom     numeric DEFAULT 0,
  thp         numeric DEFAULT 0,
  laba        numeric DEFAULT 0,
  status_bayar  text DEFAULT 'cash',
  jatuh_tempo   date,
  sisa_piutang  numeric DEFAULT 0,
  created_by    text DEFAULT ''   -- email user yang menginput data ini (audit trail)
);

-- Jika tabel sudah ada sebelumnya, tambahkan kolom baru ini (aman dijalankan berulang):
ALTER TABLE koling_penjualan ADD COLUMN IF NOT EXISTS status_bayar text DEFAULT 'cash';
ALTER TABLE koling_penjualan ADD COLUMN IF NOT EXISTS jatuh_tempo date;
ALTER TABLE koling_penjualan ADD COLUMN IF NOT EXISTS sisa_piutang numeric DEFAULT 0;
ALTER TABLE koling_penjualan ADD COLUMN IF NOT EXISTS terjual_ren numeric DEFAULT 0;
ALTER TABLE koling_penjualan ADD COLUMN IF NOT EXISTS created_by text DEFAULT '';

-- PENTING: Jika tabel lama Anda masih punya kolom "tim" (Tim 1/Tim 2),
-- jalankan baris ini untuk menghapusnya total beserta seluruh datanya:
ALTER TABLE koling_penjualan DROP COLUMN IF EXISTS tim;

-- Aktifkan Row Level Security (RLS)
ALTER TABLE koling_penjualan ENABLE ROW LEVEL SECURITY;

-- Tabel Target Sales Individual
CREATE TABLE IF NOT EXISTS koling_targets (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at  timestamptz DEFAULT now(),
  nama        text NOT NULL,
  periode     text NOT NULL,
  target_krt_bulan numeric DEFAULT 0,
  target_krt_harian numeric DEFAULT 0,
  catatan     text DEFAULT ''
);
ALTER TABLE koling_targets ENABLE ROW LEVEL SECURITY;

-- ⚠️ CATATAN KEAMANAN: Policy akses (siapa boleh baca/tulis/hapus) TIDAK
-- dibuat di sini secara sengaja. Jalankan SQL pada kartu
-- "🔒 SQL — Aktifkan Keamanan Login" di atas untuk mengatur akses yang aman
-- (hanya user yang login via Supabase Auth yang bisa mengakses data ini).

-- Index untuk performa query berdasarkan tanggal
CREATE INDEX IF NOT EXISTS idx_koling_tgl  ON koling_penjualan (tgl DESC);
CREATE INDEX IF NOT EXISTS idx_koling_nama ON koling_penjualan (nama);

-- Verifikasi tabel berhasil dibuat
SELECT 'Tabel koling_penjualan berhasil dibuat! ✅' AS status;

-- ──────────────────────────────────────────────
-- TABEL SELL IN (Barang Masuk) — jalankan juga!
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS koling_sellin (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at  timestamptz DEFAULT now(),
  tgl         date NOT NULL,
  produk      text NOT NULL,
  supplier    text DEFAULT '',
  qty_krt     numeric DEFAULT 0,
  qty_ren     numeric DEFAULT 0,
  total_ren   numeric DEFAULT 0,
  harga_modal numeric DEFAULT 0
);

ALTER TABLE koling_sellin ENABLE ROW LEVEL SECURITY;
-- ⚠️ Policy akses diatur di kartu "🔒 SQL — Aktifkan Keamanan Login" di atas.
CREATE INDEX IF NOT EXISTS idx_sellin_tgl ON koling_sellin (tgl DESC);

SELECT 'Tabel koling_sellin berhasil dibuat! ✅' AS status;

-- ──────────────────────────────────────────────
-- TABEL MASTER OUTLET — jalankan juga!
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS koling_outlet (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at  timestamptz DEFAULT now(),
  kode_outlet text NOT NULL UNIQUE,
  nama        text NOT NULL,
  tipe        text DEFAULT 'warung',
  alamat      text DEFAULT '',
  kecamatan   text DEFAULT '',
  kota        text DEFAULT '',
  hp          text DEFAULT '',
  contact_person text DEFAULT '',
  catatan     text DEFAULT ''
);

ALTER TABLE koling_outlet ENABLE ROW LEVEL SECURITY;
-- ⚠️ Policy akses diatur di kartu "🔒 SQL — Aktifkan Keamanan Login" di atas.
CREATE INDEX IF NOT EXISTS idx_outlet_nama ON koling_outlet (nama);
CREATE INDEX IF NOT EXISTS idx_outlet_kode ON koling_outlet (kode_outlet);

SELECT 'Tabel koling_outlet berhasil dibuat! ✅' AS status;

-- ──────────────────────────────────────────────
-- TABEL HISTORI PEMBAYARAN PIUTANG — jalankan juga!
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS koling_piutang_bayar (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at   timestamptz DEFAULT now(),
  penjualan_id bigint NOT NULL,
  tgl_bayar    date NOT NULL,
  jumlah_bayar numeric NOT NULL DEFAULT 0,
  metode       text DEFAULT 'cash',
  catatan      text DEFAULT ''
);

ALTER TABLE koling_piutang_bayar ENABLE ROW LEVEL SECURITY;
-- ⚠️ Policy akses diatur di kartu "🔒 SQL — Aktifkan Keamanan Login" di atas.
CREATE INDEX IF NOT EXISTS idx_piutang_bayar_pjid ON koling_piutang_bayar (penjualan_id);

SELECT 'Tabel koling_piutang_bayar berhasil dibuat! ✅' AS status;

-- ──────────────────────────────────────────────
-- TABEL STOCK OPNAME GUDANG — jalankan juga!
-- ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS koling_stock_opname (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at    timestamptz DEFAULT now(),
  tgl           date NOT NULL,
  produk        text NOT NULL,
  stok_sistem_krt   numeric DEFAULT 0,
  stok_fisik_krt    numeric DEFAULT 0,
  stok_fisik_tengah numeric DEFAULT 0,
  stok_fisik_pcs    numeric DEFAULT 0,
  selisih_krt   numeric DEFAULT 0,
  catatan       text DEFAULT '',
  petugas       text DEFAULT ''
);

ALTER TABLE koling_stock_opname ENABLE ROW LEVEL SECURITY;
-- ⚠️ Policy akses diatur di kartu "🔒 SQL — Aktifkan Keamanan Login" di atas.
CREATE INDEX IF NOT EXISTS idx_opname_tgl ON koling_stock_opname (tgl);
CREATE INDEX IF NOT EXISTS idx_opname_produk ON koling_stock_opname (produk);

SELECT 'Tabel koling_stock_opname berhasil dibuat! ✅' AS status;

-- ⚠️ JALANKAN INI DI SUPABASE SQL EDITOR
-- Dashboard Supabase → SQL Editor → New Query → Paste → Run

-- Tambah kolom harga_modal jika belum ada
ALTER TABLE koling_sellin ADD COLUMN IF NOT EXISTS harga_modal numeric DEFAULT 0;

-- Verifikasi berhasil
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'koling_sellin' AND column_name = 'harga_modal';