-- Zenith Rush + payments: FRESH INSTALL. Run once on an empty database:
--   mysql -u root -e "CREATE DATABASE zenith_rush CHARACTER SET utf8mb4"
--   mysql -u root zenith_rush < sql/install.sql
-- Amounts: users.balance is USDT with 6 decimals. *_micro columns are integers (USDT x 1,000,000).

CREATE TABLE users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(30)  NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  balance       DECIMAL(18,6) NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL
);

-- One-time tokens that let the browser prove to the game server who is logged in (60 s, single use).
CREATE TABLE ws_tokens (
  token_hash CHAR(64) PRIMARY KEY,
  user_id    INT NOT NULL,
  expires_at DATETIME NOT NULL,
  INDEX (expires_at)
);

-- ---------- QR / invoice deposits ----------
CREATE TABLE invoices (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  invoice_code  CHAR(24) NOT NULL UNIQUE,
  user_id       INT NOT NULL,
  network       ENUM('TRC20','BEP20') NOT NULL DEFAULT 'TRC20',
  amount_micro  BIGINT NOT NULL,
  start_block   BIGINT NULL,
  status        ENUM('pending','paid','expired') NOT NULL DEFAULT 'pending',
  txid          VARCHAR(80) NULL UNIQUE,
  created_at    DATETIME NOT NULL,
  expires_at    DATETIME NOT NULL,
  paid_at       DATETIME NULL,
  INDEX (status, expires_at),
  INDEX (user_id)
);

CREATE TABLE ledger (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT NOT NULL,
  invoice_id  INT NOT NULL,
  amount_micro BIGINT NOT NULL,
  created_at  DATETIME NOT NULL
);

-- ---------- smart-contract vault (deposit + withdrawal) ----------
CREATE TABLE vault_deposits (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  tx_hash       CHAR(66) NOT NULL,
  log_index     INT NOT NULL,
  user_id       INT NOT NULL,
  from_address  CHAR(42) NOT NULL,
  amount_micro  BIGINT NOT NULL,
  block_number  BIGINT NOT NULL,
  credited      TINYINT NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL,
  UNIQUE KEY uq_tx (tx_hash, log_index),
  INDEX (user_id)
);

CREATE TABLE vault_withdrawals (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  user_id        INT NOT NULL,
  withdrawal_id  CHAR(66) NOT NULL UNIQUE,
  wallet         CHAR(42) NOT NULL,
  amount_micro   BIGINT NOT NULL,
  deadline       BIGINT NOT NULL,
  signature      VARCHAR(140) NOT NULL,
  status         ENUM('signed','claimed','refunded') NOT NULL DEFAULT 'signed',
  txid           CHAR(66) NULL,
  created_at     DATETIME NOT NULL,
  claimed_at     DATETIME NULL,
  INDEX (user_id, created_at),
  INDEX (status, deadline)
);

CREATE TABLE vault_state (
  k  VARCHAR(40) PRIMARY KEY,
  v  VARCHAR(100) NOT NULL
);

-- ---------- game ----------
-- Every bet. A bet is 'open' until the round ends; the server settles it as won / lost.
-- If the game server restarts mid-round, leftover 'open' bets are refunded on the next start.
CREATE TABLE game_bets (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT NOT NULL,
  round_key     VARCHAR(40) NOT NULL,          -- boot id + round number
  amount_micro  BIGINT NOT NULL,
  cashout_x100  INT NULL,                      -- multiplier x100 at cash-out (250 = 2.50x)
  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)
);

-- ---------- vouchers ----------
CREATE TABLE vouchers (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  code          CHAR(12) NOT NULL UNIQUE,      -- stored without dashes, upper-case
  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 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),   -- one redemption per user per voucher
  INDEX (user_id)
);

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