-- Use this ONLY if your database ALREADY has the old payment tables (users, invoices, ledger, vault_*).
-- It adds what the game needs and does not touch your existing data. Safe to run once.
-- (If it says a column already exists, that part was already done: skip that line.)

ALTER TABLE users ADD COLUMN password_hash VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN created_at DATETIME NULL;
ALTER TABLE users ADD UNIQUE KEY uq_username (username);

CREATE TABLE IF NOT EXISTS ws_tokens (
  token_hash CHAR(64) PRIMARY KEY,
  user_id    INT NOT NULL,
  expires_at DATETIME NOT NULL,
  INDEX (expires_at)
);

CREATE TABLE IF NOT EXISTS game_bets (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT NOT NULL,
  round_key     VARCHAR(40) NOT NULL,
  amount_micro  BIGINT NOT NULL,
  cashout_x100  INT NULL,
  payout_micro  BIGINT NOT NULL DEFAULT 0,
  status        ENUM('open','won','lost','refunded') NOT NULL DEFAULT 'open',
  created_at    DATETIME NOT NULL,
  settled_at    DATETIME NULL,
  INDEX (user_id, id),
  INDEX (status)
);

CREATE TABLE IF NOT EXISTS vouchers (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  code          CHAR(12) NOT NULL UNIQUE,
  amount_micro  BIGINT NOT NULL,
  max_uses      INT NOT NULL DEFAULT 1,
  used_count    INT NOT NULL DEFAULT 0,
  expires_at    DATETIME NULL,
  active        TINYINT NOT NULL DEFAULT 1,
  note          VARCHAR(120) NULL,
  created_at    DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS voucher_redemptions (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  voucher_id   INT NOT NULL,
  user_id      INT NOT NULL,
  amount_micro BIGINT NOT NULL,
  created_at   DATETIME NOT NULL,
  UNIQUE KEY uq_user_voucher (voucher_id, user_id),
  INDEX (user_id)
);

CREATE TABLE IF NOT EXISTS voucher_attempts (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX (user_id, created_at)
);
