-- Jalankan sekali di psql / pgAdmin untuk membuat database & tabel:
--   psql -U postgres -c "CREATE DATABASE doorprize;"
--   psql -U postgres -d doorprize -f schema.sql

CREATE TABLE IF NOT EXISTS karyawan (
  id SERIAL PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  departemen VARCHAR(100),
  kode_unik VARCHAR(3),
  status_hadir BOOLEAN NOT NULL DEFAULT FALSE,
  sudah_menang BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_karyawan_kode_unik ON karyawan (kode_unik) WHERE kode_unik IS NOT NULL;

CREATE TABLE IF NOT EXISTS hadiah (
  id SERIAL PRIMARY KEY,
  nama_hadiah VARCHAR(150) NOT NULL,
  kategori VARCHAR(50),
  total_kuota INTEGER NOT NULL DEFAULT 1,
  sisa_stok INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS pemenang (
  id SERIAL PRIMARY KEY,
  kode VARCHAR(3),
  karyawan_id INTEGER REFERENCES karyawan(id),
  hadiah_id INTEGER NOT NULL REFERENCES hadiah(id),
  waktu_menang TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS kupon (
  kode VARCHAR(3) PRIMARY KEY,
  terpakai BOOLEAN NOT NULL DEFAULT FALSE,
  dikeluarkan BOOLEAN NOT NULL DEFAULT FALSE,
  karyawan_id INTEGER REFERENCES karyawan(id)
);

CREATE INDEX IF NOT EXISTS idx_karyawan_undian ON karyawan (status_hadir, sudah_menang);
CREATE INDEX IF NOT EXISTS idx_hadiah_undian ON hadiah (sisa_stok);

-- Data contoh (opsional, bisa dihapus):
INSERT INTO karyawan (nama, departemen) VALUES
  ('Budi Santoso', 'Produksi'),
  ('Siti Rahayu', 'HRD'),
  ('Agus Wijaya', 'Keuangan'),
  ('Dewi Lestari', 'Marketing'),
  ('Rudi Hartono', 'IT')
ON CONFLICT DO NOTHING;

INSERT INTO hadiah (nama_hadiah, kategori, total_kuota, sisa_stok) VALUES
  ('TV LED 43 Inch', 'Utama', 1, 1),
  ('Kulkas 2 Pintu', 'Utama', 1, 1),
  ('Mesin Cuci', 'Utama', 2, 2),
  ('Rice Cooker', 'Hiburan', 5, 5),
  ('Blender', 'Hiburan', 10, 10)
ON CONFLICT DO NOTHING;