-- PostgreSQL 16+. All virtual chips are non-purchasable, non-transferable and non-redeemable.
CREATE TABLE IF NOT EXISTS schema_migrations (
  name text PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(100) NOT NULL CHECK (char_length(trim(name)) >= 2),
  email varchar(254) NOT NULL UNIQUE,
  phone varchar(20) UNIQUE,
  phone_verified boolean NOT NULL DEFAULT false,
  email_verified boolean NOT NULL DEFAULT true,
  password_hash text,
  role varchar(12) NOT NULL DEFAULT 'player' CHECK (role IN ('player','admin')),
  blocked boolean NOT NULL DEFAULT false,
  referral_code varchar(12) NOT NULL UNIQUE,
  referred_by uuid REFERENCES users(id),
  vip_points int NOT NULL DEFAULT 0 CHECK (vip_points >= 0),
  avatar_color varchar(20) NOT NULL DEFAULT '#8858e9',
  last_refill_at timestamptz,
  last_login_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_users_name ON users (lower(name));
CREATE INDEX idx_users_created ON users (created_at DESC);
CREATE TABLE wallets (
  user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE RESTRICT,
  balance bigint NOT NULL DEFAULT 0 CHECK (balance >= 0 AND balance <= 1000000000),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE wallet_transactions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id),
  type varchar(40) NOT NULL,
  delta bigint NOT NULL CHECK (delta <> 0),
  before_balance bigint NOT NULL CHECK (before_balance >= 0),
  after_balance bigint NOT NULL CHECK (after_balance >= 0),
  reference varchar(160) UNIQUE,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  CHECK (after_balance = before_balance + delta)
);
CREATE INDEX idx_wallet_history ON wallet_transactions(user_id, created_at DESC);
CREATE TABLE otp_challenges (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  purpose varchar(30) NOT NULL CHECK (purpose IN ('register','login','reset','verify-phone')),
  destination varchar(254) NOT NULL,
  channel varchar(8) NOT NULL CHECK (channel IN ('email','sms')),
  code_hash char(64) NOT NULL,
  attempts int NOT NULL DEFAULT 0 CHECK (attempts BETWEEN 0 AND 5),
  expires_at timestamptz NOT NULL,
  last_sent_at timestamptz NOT NULL DEFAULT now(),
  consumed_at timestamptz,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_otp_destination ON otp_challenges(purpose, destination, created_at DESC);
CREATE TABLE sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash char(64) NOT NULL UNIQUE,
  csrf_hash char(64) NOT NULL,
  ip_address inet,
  user_agent text,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  revoked_at timestamptz
);
CREATE INDEX idx_sessions_user ON sessions(user_id, revoked_at);
CREATE TABLE login_history (
  id bigserial PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users(id),
  method varchar(16) NOT NULL,
  ip_address inet,
  user_agent text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_login_history_user ON login_history(user_id, created_at DESC);
CREATE TABLE reward_claims (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id),
  kind varchar(30) NOT NULL,
  period_key varchar(80) NOT NULL,
  amount bigint NOT NULL CHECK (amount >= 0),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id, kind, period_key)
);
CREATE INDEX idx_rewards_user ON reward_claims(user_id, created_at DESC);
CREATE TABLE referrals (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  inviter_id uuid NOT NULL REFERENCES users(id),
  referred_id uuid NOT NULL UNIQUE REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  CHECK(inviter_id <> referred_id)
);
CREATE TABLE notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title varchar(150) NOT NULL,
  body text NOT NULL,
  kind varchar(30) NOT NULL,
  data jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_read boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_notifications_user ON notifications(user_id, is_read, created_at DESC);
CREATE TABLE friend_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sender_id uuid NOT NULL REFERENCES users(id),
  receiver_id uuid NOT NULL REFERENCES users(id),
  status varchar(12) NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','accepted','declined')),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(sender_id, receiver_id),
  CHECK(sender_id <> receiver_id)
);
CREATE TABLE rooms (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code varchar(8) NOT NULL UNIQUE,
  name varchar(80) NOT NULL,
  owner_id uuid NOT NULL REFERENCES users(id),
  type varchar(12) NOT NULL CHECK(type IN ('public','private','practice')),
  mode varchar(16) NOT NULL CHECK(mode IN ('teen-patti','joker','235','ak47')),
  base_bet int NOT NULL CHECK(base_bet BETWEEN 10 AND 10000),
  max_players int NOT NULL CHECK(max_players BETWEEN 3 AND 6),
  state jsonb NOT NULL,
  closed boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_rooms_open ON rooms(closed, type, created_at DESC);
CREATE TABLE rounds (
  id uuid PRIMARY KEY,
  room_id uuid NOT NULL REFERENCES rooms(id),
  mode varchar(16) NOT NULL,
  status varchar(12) NOT NULL CHECK (status IN ('playing','finished')),
  pot bigint NOT NULL DEFAULT 0,
  winner_id uuid REFERENCES users(id),
  results jsonb NOT NULL DEFAULT '{}'::jsonb,
  started_at timestamptz NOT NULL DEFAULT now(),
  ended_at timestamptz
);
CREATE INDEX idx_rounds_room ON rounds(room_id, started_at DESC);
CREATE TABLE round_participants (
  round_id uuid NOT NULL REFERENCES rounds(id),
  user_id uuid NOT NULL REFERENCES users(id),
  PRIMARY KEY (round_id, user_id)
);
CREATE INDEX idx_rounds_user ON round_participants(user_id);
CREATE TABLE round_actions (
  id bigserial PRIMARY KEY,
  round_id uuid NOT NULL REFERENCES rounds(id),
  user_id uuid NOT NULL REFERENCES users(id),
  action varchar(20) NOT NULL,
  details jsonb NOT NULL DEFAULT '{}'::jsonb,
  occurred_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_actions_round ON round_actions(round_id, id);
CREATE TABLE admin_audit (
  id bigserial PRIMARY KEY,
  actor_id uuid NOT NULL REFERENCES users(id),
  target_id uuid REFERENCES users(id),
  action varchar(50) NOT NULL,
  reason text NOT NULL CHECK(length(trim(reason)) >= 5),
  details jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE OR REPLACE FUNCTION deny_audit_mutation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'Audit records are append-only';
END;
$$;
CREATE TRIGGER admin_audit_append_only BEFORE UPDATE OR DELETE ON admin_audit
FOR EACH ROW EXECUTE FUNCTION deny_audit_mutation();
CREATE TRIGGER wallet_ledger_append_only BEFORE UPDATE OR DELETE ON wallet_transactions
FOR EACH ROW EXECUTE FUNCTION deny_audit_mutation();