-- ═══════════════════════════════════════════════════════════════
--  BTC CATCH MINER — Full Database Schema
--  Run this once on a fresh MySQL/MariaDB database.
--  For existing databases, run migrate.sql instead.
-- ═══════════════════════════════════════════════════════════════

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ── PLAYERS ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `players` (
  `id`                    INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
  `telegram_id`           VARCHAR(32)     NOT NULL,
  `username`              VARCHAR(128)     NOT NULL DEFAULT 'Player',
  `first_name`            VARCHAR(128)     NOT NULL DEFAULT '',
  `photo_url`             TEXT             DEFAULT NULL,
  `photo_updated_at`      DATETIME         DEFAULT NULL,
  `coins`                 BIGINT UNSIGNED  NOT NULL DEFAULT 0,
  `btc_balance`           INT UNSIGNED     NOT NULL DEFAULT 0,
  `total_withdraw`        INT UNSIGNED     NOT NULL DEFAULT 0,
  `referrer_id`           VARCHAR(32)      DEFAULT NULL,
  `referrals`             INT UNSIGNED     NOT NULL DEFAULT 0,
  `referral_coins`        BIGINT UNSIGNED  NOT NULL DEFAULT 0,
  `faucet_email`          VARCHAR(255)     NOT NULL DEFAULT '',
  `join_date`             DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_game`             DATETIME         DEFAULT NULL,
  `last_daily`            DATETIME         DEFAULT NULL,
  `level`                 TINYINT          NOT NULL DEFAULT 1,
  `xp`                    BIGINT UNSIGNED  NOT NULL DEFAULT 0,
  `games_played`          INT UNSIGNED     NOT NULL DEFAULT 0,
  `best_score`            INT UNSIGNED     NOT NULL DEFAULT 0,
  `game_points`           INT              NOT NULL DEFAULT 10,
  `last_gamepoints_reset` DATE             DEFAULT NULL,
  `withdrawal_block_reason` TEXT DEFAULT NULL,
  UNIQUE KEY `uq_telegram_id`  (`telegram_id`),
  INDEX `idx_coins`            (`coins` DESC),
  INDEX `idx_level`            (`level`),
  INDEX `idx_referrer_id`      (`referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── WITHDRAWALS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id`             INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
  `withdrawal_id`   VARCHAR(32)     NOT NULL,
  `telegram_id`     VARCHAR(32)     NOT NULL,
  `username`        VARCHAR(128)    NOT NULL DEFAULT '',
  `photo_url`       TEXT            DEFAULT NULL,
  `email`           VARCHAR(255)    NOT NULL,
  `amount_sat`      INT UNSIGNED    NOT NULL DEFAULT 0,
  `coins_used`      BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `tx_id`           VARCHAR(128)    NOT NULL DEFAULT 'PENDING',
  `fee_sat`         INT UNSIGNED    NOT NULL DEFAULT 0,
  `status`          ENUM('pending','processing','success','failed') NOT NULL DEFAULT 'pending',
  `error_msg`       TEXT            DEFAULT NULL,
  `processed_at`    DATETIME        DEFAULT NULL,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `broadcasted`     TINYINT(1)      NOT NULL DEFAULT 0,
  UNIQUE KEY `uq_withdrawal_id`  (`withdrawal_id`),
  INDEX `idx_telegram_id`        (`telegram_id`),
  INDEX `idx_created_at`         (`created_at` DESC),
  INDEX `idx_status`             (`status`),
  INDEX `idx_broadcasted`        (`broadcasted`),
  INDEX `idx_status_created`     (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── REFERRALS ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `referrals` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `referrer_id`         VARCHAR(32)  NOT NULL,
  `referred_id`         VARCHAR(32)  NOT NULL,
  `coins_awarded`       INT UNSIGNED NOT NULL DEFAULT 0,
  `game_points_awarded` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_referral`   (`referrer_id`, `referred_id`),
  INDEX `idx_referrer`       (`referrer_id`),
  INDEX `idx_referred`       (`referred_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── GAME SESSIONS ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `game_sessions` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `telegram_id`  VARCHAR(32)  NOT NULL,
  `score`        INT UNSIGNED NOT NULL DEFAULT 0,
  `coins_earned` INT UNSIGNED NOT NULL DEFAULT 0,
  `level`        TINYINT      NOT NULL DEFAULT 1,
  `played_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_telegram_id`   (`telegram_id`),
  INDEX `idx_played_at`      (`played_at` DESC),
  INDEX `idx_tid_played`     (`telegram_id`, `played_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── RATE LIMITS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `rate_limits` (
  `ip`           VARCHAR(64)  NOT NULL PRIMARY KEY,
  `count`        INT UNSIGNED NOT NULL DEFAULT 0,
  `window_start` INT UNSIGNED NOT NULL DEFAULT 0,
  INDEX `idx_window_start`  (`window_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;