-- ═══════════════════════════════════════════════════════════════
--  BTC CATCH MINER — Admin Panel Database Tables
--  Run this AFTER install.sql to add admin features
-- ═══════════════════════════════════════════════════════════════

SET NAMES utf8mb4;

-- ── ADD ADMIN COLUMNS TO PLAYERS ────────────────────────────────
ALTER TABLE `players`
  ADD COLUMN IF NOT EXISTS `is_banned`           TINYINT(1)   NOT NULL DEFAULT 0 AFTER `faucet_email`,
  ADD COLUMN IF NOT EXISTS `ban_reason`           TEXT         DEFAULT NULL AFTER `is_banned`,
  ADD COLUMN IF NOT EXISTS `withdrawal_blocked`   TINYINT(1)   NOT NULL DEFAULT 0 AFTER `ban_reason`,
  ADD COLUMN IF NOT EXISTS `block_reason`         TEXT         DEFAULT NULL AFTER `withdrawal_blocked`,
  ADD COLUMN IF NOT EXISTS `admin_notes`          TEXT         DEFAULT NULL AFTER `block_reason`,
  ADD INDEX IF NOT EXISTS `idx_is_banned`         (`is_banned`),
  ADD INDEX IF NOT EXISTS `idx_withdrawal_blocked`(`withdrawal_blocked`);

-- ── ADMIN LOGS ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admin_logs` (
  `id`          INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
  `action`      VARCHAR(64)     NOT NULL,
  `target_id`   VARCHAR(32)     DEFAULT NULL,
  `data_json`   TEXT            DEFAULT NULL,
  `ip`          VARCHAR(64)     DEFAULT NULL,
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_action`      (`action`),
  INDEX `idx_target_id`   (`target_id`),
  INDEX `idx_created_at`  (`created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── BROADCAST JOBS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `broadcast_jobs` (
  `id`               INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  `broadcast_id`     VARCHAR(64)   NOT NULL,
  `type`             ENUM('text','photo','video') NOT NULL DEFAULT 'text',
  `text_content`     TEXT          DEFAULT NULL,
  `image_url`        TEXT          DEFAULT NULL,
  `image_local`      TEXT          DEFAULT NULL,
  `buttons_json`     TEXT          DEFAULT NULL,
  `parse_mode`       VARCHAR(16)   NOT NULL DEFAULT 'Markdown',
  `filter_type`      VARCHAR(32)   NOT NULL DEFAULT 'all',
  `total_recipients` INT UNSIGNED  NOT NULL DEFAULT 0,
  `sent_count`       INT UNSIGNED  NOT NULL DEFAULT 0,
  `failed_count`     INT UNSIGNED  NOT NULL DEFAULT 0,
  `status`           ENUM('queued','sending','completed','failed') NOT NULL DEFAULT 'queued',
  `created_at`       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at`     DATETIME      DEFAULT NULL,
  UNIQUE KEY `uq_broadcast_id` (`broadcast_id`),
  INDEX `idx_status`      (`status`),
  INDEX `idx_created_at`  (`created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ADMIN SETTINGS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admin_settings` (
  `id`            INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  `setting_key`   VARCHAR(64)   NOT NULL,
  `setting_value` TEXT          DEFAULT NULL,
  `updated_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── DEFAULT SETTINGS ────────────────────────────────────────────
INSERT IGNORE INTO `admin_settings` (`setting_key`, `setting_value`) VALUES
  ('maintenance_mode',     '0'),
  ('registration_enabled', '1'),
  ('withdrawal_enabled',   '1'),
  ('announcement',         '');
