-- ═══════════════════════════════════════════════════════════════
--  BTC CATCH MINER — Migration Script (v3)
--  Run this on an EXISTING database (safe, all IF NOT EXISTS).
--  For a fresh install, use install.sql instead.
-- ═══════════════════════════════════════════════════════════════

SET NAMES utf8mb4;

-- ── 1. players: add game_points columns if missing ───────────────
ALTER TABLE `players`
  ADD COLUMN IF NOT EXISTS `game_points`           INT  NOT NULL DEFAULT 10,
  ADD COLUMN IF NOT EXISTS `last_gamepoints_reset` DATE DEFAULT NULL;

-- Backfill existing players who have 0 or NULL game_points
UPDATE `players`
SET `game_points` = 10
WHERE `game_points` IS NULL OR `game_points` = 0;

UPDATE `players`
SET `last_gamepoints_reset` = NULL
WHERE `last_gamepoints_reset` IS NULL;

-- ── 2. players: indexes ──────────────────────────────────────────
ALTER TABLE `players`
  ADD INDEX IF NOT EXISTS `idx_referrer_id` (`referrer_id`);

-- ── 3. referrals: add game_points_awarded column ─────────────────
ALTER TABLE `referrals`
  ADD COLUMN IF NOT EXISTS `game_points_awarded` INT UNSIGNED NOT NULL DEFAULT 0;

UPDATE `referrals`
SET `game_points_awarded` = 3
WHERE `game_points_awarded` = 0;

-- ── 4. withdrawals: composite index for SSE poll ─────────────────
ALTER TABLE `withdrawals`
  ADD INDEX IF NOT EXISTS `idx_status_created` (`status`, `created_at`);

-- ── 5. game_sessions: index for daily task queries ───────────────
ALTER TABLE `game_sessions`
  ADD INDEX IF NOT EXISTS `idx_tid_played` (`telegram_id`, `played_at`);

-- ── 6. rate_limits: window_start index ───────────────────────────
ALTER TABLE `rate_limits`
  ADD INDEX IF NOT EXISTS `idx_window_start` (`window_start`);

-- Done
SELECT 'Migration v3 complete.' AS result;
