SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL,
  username VARCHAR(64) NULL,
  first_name VARCHAR(128) NULL,
  language VARCHAR(8) NOT NULL DEFAULT 'en',
  skin VARCHAR(24) NOT NULL DEFAULT '',
  skin_auto TINYINT(1) NOT NULL DEFAULT 1,
  last_spin_at DATETIME NULL,
  tap_energy DECIMAL(12,3) NOT NULL DEFAULT 0,
  tap_energy_at INT UNSIGNED NOT NULL DEFAULT 0,
  tap_today DECIMAL(20,4) NOT NULL DEFAULT 0,
  tap_date DATE NULL,
  ref_code VARCHAR(16) NOT NULL,
  referred_by BIGINT UNSIGNED NULL,
  coins DECIMAL(20,4) NOT NULL DEFAULT 0,
  total_earned DECIMAL(20,4) NOT NULL DEFAULT 0,
  total_mined DECIMAL(20,4) NOT NULL DEFAULT 0,
  spent_coins DECIMAL(20,4) NOT NULL DEFAULT 0,
  converted_coins DECIMAL(20,4) NOT NULL DEFAULT 0,
  usd_balance DECIMAL(18,4) NOT NULL DEFAULT 0,
  usd_earned DECIMAL(18,4) NOT NULL DEFAULT 0,
  usd_withdrawn DECIMAL(18,4) NOT NULL DEFAULT 0,
  usd_pending DECIMAL(18,4) NOT NULL DEFAULT 0,
  tier VARCHAR(16) NOT NULL DEFAULT 'free',
  extra_boost DECIMAL(10,4) NOT NULL DEFAULT 0,
  premium_boost TINYINT(1) NOT NULL DEFAULT 0,
  status VARCHAR(12) NOT NULL DEFAULT 'active',
  flagged TINYINT(1) NOT NULL DEFAULT 0,
  flag_reason VARCHAR(255) NULL,
  notify TINYINT(1) NOT NULL DEFAULT 1,
  mined_today DECIMAL(20,4) NOT NULL DEFAULT 0,
  mined_date DATE NULL,
  daily_streak INT NOT NULL DEFAULT 0,
  last_daily_date DATE NULL,
  checkin_streak INT NOT NULL DEFAULT 0,
  last_checkin_date DATE NULL,
  last_withdraw_at DATETIME NULL,
  last_seen DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tg (telegram_id),
  UNIQUE KEY uq_ref (ref_code),
  KEY idx_referred_by (referred_by),
  KEY idx_mined (total_mined),
  KEY idx_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mining_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  started_at INT UNSIGNED NOT NULL,
  ends_at INT UNSIGNED NOT NULL,
  speed DECIMAL(12,4) NOT NULL,
  credited DECIMAL(20,4) NOT NULL DEFAULT 0,
  status VARCHAR(12) NOT NULL DEFAULT 'active',
  active_user BIGINT UNSIGNED NULL,
  notified TINYINT(1) NOT NULL DEFAULT 0,
  tx_row BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_one_active (active_user),
  KEY idx_user (user_id, status),
  KEY idx_due (status, ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mining_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  session_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(20,4) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  KEY idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS referrals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referrer_id BIGINT UNSIGNED NOT NULL,
  referred_id BIGINT UNSIGNED NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  bonus_paid DECIMAL(20,4) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activated_at DATETIME NULL,
  UNIQUE KEY uq_referred (referred_id),
  KEY idx_referrer (referrer_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tasks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  description VARCHAR(255) NULL,
  type VARCHAR(24) NOT NULL DEFAULT 'website',
  url VARCHAR(500) NULL,
  chat_id VARCHAR(64) NULL,
  reward DECIMAL(20,4) NOT NULL DEFAULT 0,
  min_seconds INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS task_claims (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  task_id INT UNSIGNED NOT NULL,
  started_at INT UNSIGNED NOT NULL,
  claimed_at DATETIME NULL,
  reward DECIMAL(20,4) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_user_task (user_id, task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conversions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  coins DECIMAL(20,4) NOT NULL,
  usd DECIMAL(18,4) NOT NULL,
  rate DECIMAL(20,4) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS withdrawals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(18,4) NOT NULL,
  method VARCHAR(40) NOT NULL,
  address VARCHAR(191) NOT NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'pending',
  admin_note VARCHAR(255) NULL,
  payout_ref VARCHAR(191) NULL,
  processed_by VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  KEY idx_user (user_id),
  KEY idx_status (status),
  KEY idx_address (address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS premium_memberships (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  channel VARCHAR(100) NULL,
  bonus_speed DECIMAL(10,4) NOT NULL DEFAULT 0,
  claimed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS daily_rewards (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  day_no INT NOT NULL,
  reward DECIMAL(20,4) NOT NULL,
  claim_date DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_date (user_id, claim_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS daily_checkins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  checkin_date DATE NOT NULL,
  streak INT NOT NULL,
  reward DECIMAL(20,4) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_date (user_id, checkin_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS wallet_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tx_id VARCHAR(24) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  currency VARCHAR(4) NOT NULL,
  amount DECIMAL(20,4) NOT NULL,
  type VARCHAR(24) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'completed',
  note VARCHAR(255) NULL,
  ref_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tx (tx_id),
  KEY idx_user (user_id, id),
  KEY idx_ref (type, ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  k VARCHAR(64) NOT NULL PRIMARY KEY,
  v TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS announcements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  withdrawal_id BIGINT UNSIGNED NOT NULL,
  message_id BIGINT NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_withdrawal (withdrawal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id INT UNSIGNED NULL,
  admin_name VARCHAR(64) NULL,
  action VARCHAR(64) NOT NULL,
  target_user BIGINT UNSIGNED NULL,
  details TEXT NULL,
  ip VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_target (target_user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(24) NOT NULL,
  message VARCHAR(500) NULL,
  sent TINYINT(1) NOT NULL DEFAULT 0,
  error VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  last_login DATETIME NULL,
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS broadcasts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  body TEXT NOT NULL,
  image_url VARCHAR(500) NULL,
  btn_text VARCHAR(64) NULL,
  btn_url VARCHAR(500) NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'pending',
  total INT NOT NULL DEFAULT 0,
  sent INT NOT NULL DEFAULT 0,
  failed INT NOT NULL DEFAULT 0,
  last_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rate_limits (
  k VARCHAR(100) NOT NULL PRIMARY KEY,
  hits INT NOT NULL DEFAULT 0,
  win_start INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_skins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  skin VARCHAR(24) NOT NULL,
  source VARCHAR(12) NOT NULL DEFAULT 'coins',
  price DECIMAL(18,4) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_skin (user_id, skin)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;