📐 Skema Database (PostgreSQL)

CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          VARCHAR(100) NOT NULL,
  email         VARCHAR(150) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  total_poin    INTEGER DEFAULT 0,
  streak_days   INTEGER DEFAULT 0,
  last_active   DATE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE amalan_log (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    UUID REFERENCES users(id) ON DELETE CASCADE,
  surat_id   VARCHAR(50) NOT NULL,
  surat_name VARCHAR(100) NOT NULL,
  poin       INTEGER NOT NULL,
  tanggal    DATE NOT NULL DEFAULT CURRENT_DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (user_id, surat_id, tanggal)
);

-- Query Leaderboard
SELECT name, total_poin,
  RANK() OVER (ORDER BY total_poin DESC) AS rank
FROM users ORDER BY total_poin DESC LIMIT 50;