-- =====================================================================
-- RAPID REWARD CASH — MySQL Schema
-- Import via cPanel -> phpMyAdmin -> Import -> database.sql
-- Engine: InnoDB | Charset: utf8mb4 (safe to re-import: idempotent)
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- USERS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(40) DEFAULT NULL,
  country VARCHAR(60) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  referral_code VARCHAR(20) NOT NULL,
  referred_by BIGINT UNSIGNED DEFAULT NULL,
  google_id VARCHAR(120) DEFAULT NULL,
  avatar VARCHAR(255) DEFAULT NULL,
  status ENUM('active','blocked','suspended','pending') NOT NULL DEFAULT 'active',
  email_verified_at DATETIME DEFAULT NULL,
  risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_login_at DATETIME DEFAULT NULL,
  last_login_ip VARCHAR(45) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_referral_code (referral_code),
  UNIQUE KEY uq_users_google_id (google_id),
  KEY idx_users_status (status),
  KEY idx_users_referred_by (referred_by),
  KEY idx_users_created_at (created_at),
  CONSTRAINT fk_users_referred_by FOREIGN KEY (referred_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  gender VARCHAR(20) DEFAULT NULL,
  date_of_birth DATE DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  city VARCHAR(100) DEFAULT NULL,
  state VARCHAR(100) DEFAULT NULL,
  postal_code VARCHAR(20) DEFAULT NULL,
  bio VARCHAR(500) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_profiles_user (user_id),
  CONSTRAINT fk_user_profiles_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED DEFAULT NULL,
  install_id VARCHAR(64) NOT NULL,
  device_id VARCHAR(64) DEFAULT NULL,
  model VARCHAR(120) DEFAULT NULL,
  manufacturer VARCHAR(120) DEFAULT NULL,
  os_version VARCHAR(40) DEFAULT NULL,
  sdk_version VARCHAR(10) DEFAULT NULL,
  app_version VARCHAR(20) DEFAULT NULL,
  is_emulator TINYINT(1) NOT NULL DEFAULT 0,
  is_rooted TINYINT(1) NOT NULL DEFAULT 0,
  is_vpn TINYINT(1) NOT NULL DEFAULT 0,
  push_token VARCHAR(255) DEFAULT NULL,
  risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('trusted','suspicious','blocked') NOT NULL DEFAULT 'trusted',
  first_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_devices_install (install_id),
  KEY idx_user_devices_user (user_id),
  KEY idx_user_devices_device (device_id),
  CONSTRAINT fk_user_devices_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  device_id BIGINT UNSIGNED DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  is_remember TINYINT(1) NOT NULL DEFAULT 0,
  expires_at DATETIME NOT NULL,
  last_activity DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_sessions_token (token_hash),
  KEY idx_user_sessions_user (user_id),
  KEY idx_user_sessions_expires (expires_at),
  CONSTRAINT fk_user_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_referrals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  referrer_id BIGINT UNSIGNED NOT NULL,
  referred_user_id BIGINT UNSIGNED NOT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('pending','credited','rejected') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_referrals_referred (referred_user_id),
  KEY idx_user_referrals_referrer (referrer_id),
  CONSTRAINT fk_user_referrals_referrer FOREIGN KEY (referrer_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_user_referrals_referred FOREIGN KEY (referred_user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- WALLET
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS wallets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  pending_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_earned DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_withdrawn DECIMAL(14,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  locked_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_wallets_user (user_id),
  CONSTRAINT fk_wallets_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wallet_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  transaction_id VARCHAR(36) NOT NULL,
  type VARCHAR(40) NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  balance_before DECIMAL(14,2) NOT NULL DEFAULT 0,
  balance_after DECIMAL(14,2) NOT NULL DEFAULT 0,
  reference_id VARCHAR(64) DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  status ENUM('completed','pending','failed','cancelled') NOT NULL DEFAULT 'completed',
  idempotency_key VARCHAR(64) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_wallet_tx_id (transaction_id),
  UNIQUE KEY uq_wallet_tx_idempotency (idempotency_key),
  KEY idx_wallet_tx_user (user_id, created_at),
  KEY idx_wallet_tx_type (type),
  KEY idx_wallet_tx_wallet (wallet_id),
  CONSTRAINT fk_wallet_tx_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_wallet_tx_wallet FOREIGN KEY (wallet_id) REFERENCES wallets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS withdrawals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  method VARCHAR(40) NOT NULL,
  account_details TEXT NOT NULL,
  status ENUM('pending','processing','paid','rejected') NOT NULL DEFAULT 'pending',
  admin_note VARCHAR(500) DEFAULT NULL,
  idempotency_key VARCHAR(64) DEFAULT NULL,
  processed_by BIGINT UNSIGNED DEFAULT NULL,
  processed_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_withdrawals_idempotency (idempotency_key),
  KEY idx_withdrawals_user (user_id, created_at),
  KEY idx_withdrawals_status (status),
  CONSTRAINT fk_withdrawals_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_withdrawals_wallet FOREIGN KEY (wallet_id) REFERENCES wallets (id) ON DELETE CASCADE,
  CONSTRAINT fk_withdrawals_admin FOREIGN KEY (processed_by) REFERENCES admin_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- TASKS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  type ENUM('web_visit','link_promote','video','html5_game') NOT NULL,
  title VARCHAR(190) NOT NULL,
  description VARCHAR(1000) DEFAULT NULL,
  url VARCHAR(500) DEFAULT NULL,
  thumbnail VARCHAR(255) DEFAULT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  reward_type ENUM('coin','cash') NOT NULL DEFAULT 'cash',
  daily_limit INT NOT NULL DEFAULT 1,
  total_limit INT DEFAULT NULL,
  duration_seconds INT NOT NULL DEFAULT 15,
  start_date DATETIME DEFAULT NULL,
  end_date DATETIME DEFAULT NULL,
  status ENUM('enabled','disabled') NOT NULL DEFAULT 'disabled',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tasks_type (type),
  KEY idx_tasks_status (status, start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  task_id BIGINT UNSIGNED NOT NULL,
  session_token VARCHAR(64) NOT NULL,
  status ENUM('started','completed','expired','failed') NOT NULL DEFAULT 'started',
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_task_sessions_token (session_token),
  KEY idx_task_sessions_user (user_id, created_at),
  KEY idx_task_sessions_task (task_id),
  CONSTRAINT fk_task_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_task_sessions_task FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_completions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  task_id BIGINT UNSIGNED NOT NULL,
  task_session_id BIGINT UNSIGNED NOT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('completed','rejected') NOT NULL DEFAULT 'completed',
  completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_task_completions_session (task_session_id),
  KEY idx_task_completions_user (user_id, completed_at),
  CONSTRAINT fk_task_completions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_task_completions_task FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE,
  CONSTRAINT fk_task_completions_session FOREIGN KEY (task_session_id) REFERENCES task_sessions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- SPIN & SCRATCH
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS spin_configs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  label VARCHAR(80) NOT NULL,
  prize_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  probability DECIMAL(6,4) NOT NULL DEFAULT 0,
  color VARCHAR(9) DEFAULT '#4F46E5',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_spin_configs_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS spin_results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  config_id BIGINT UNSIGNED DEFAULT NULL,
  prize_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('credited','rejected') NOT NULL DEFAULT 'credited',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_spin_results_user (user_id, created_at),
  CONSTRAINT fk_spin_results_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_spin_results_config FOREIGN KEY (config_id) REFERENCES spin_configs (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scratch_configs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  card_no TINYINT UNSIGNED NOT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  probability DECIMAL(6,4) NOT NULL DEFAULT 0,
  daily_limit INT NOT NULL DEFAULT 1,
  cooldown_minutes INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_scratch_configs_card (card_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scratch_results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  card_no TINYINT UNSIGNED NOT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('credited','rejected') NOT NULL DEFAULT 'credited',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_scratch_results_user (user_id, created_at),
  CONSTRAINT fk_scratch_results_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- DAILY REWARDS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS daily_rewards (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  day_no TINYINT UNSIGNED NOT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_daily_rewards_day (day_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS daily_claims (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  day_no TINYINT UNSIGNED NOT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  streak INT NOT NULL DEFAULT 1,
  claimed_date DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_daily_claims_user_date (user_id, claimed_date),
  KEY idx_daily_claims_user (user_id),
  CONSTRAINT fk_daily_claims_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- QUIZ
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS quiz_questions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  question VARCHAR(500) NOT NULL,
  option_a VARCHAR(255) NOT NULL,
  option_b VARCHAR(255) NOT NULL,
  option_c VARCHAR(255) NOT NULL,
  option_d VARCHAR(255) NOT NULL,
  correct_option ENUM('a','b','c','d') NOT NULL,
  difficulty ENUM('easy','medium','hard') NOT NULL DEFAULT 'easy',
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  time_limit_seconds INT NOT NULL DEFAULT 15,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_quiz_questions_enabled (enabled, difficulty)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quiz_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  session_token VARCHAR(64) NOT NULL,
  score INT NOT NULL DEFAULT 0,
  questions_count INT NOT NULL DEFAULT 0,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('started','completed','abandoned') NOT NULL DEFAULT 'started',
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_quiz_sessions_token (session_token),
  KEY idx_quiz_sessions_user (user_id, started_at),
  CONSTRAINT fk_quiz_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quiz_answers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  session_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  selected_option ENUM('a','b','c','d') DEFAULT NULL,
  is_correct TINYINT(1) NOT NULL DEFAULT 0,
  time_taken_ms INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_quiz_answers_session (session_id),
  CONSTRAINT fk_quiz_answers_session FOREIGN KEY (session_id) REFERENCES quiz_sessions (id) ON DELETE CASCADE,
  CONSTRAINT fk_quiz_answers_question FOREIGN KEY (question_id) REFERENCES quiz_questions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- GAMES (HTML5 + in-app)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS games (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  thumbnail VARCHAR(255) DEFAULT NULL,
  game_url VARCHAR(500) DEFAULT NULL,
  description VARCHAR(1000) DEFAULT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  daily_limit INT NOT NULL DEFAULT 1,
  status ENUM('enabled','disabled') NOT NULL DEFAULT 'disabled',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_games_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS game_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  game_id BIGINT UNSIGNED DEFAULT NULL,
  session_token VARCHAR(64) NOT NULL,
  game_key VARCHAR(40) NOT NULL,
  result_json TEXT,
  outcome VARCHAR(10) DEFAULT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('started','completed','rejected') NOT NULL DEFAULT 'started',
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_game_sessions_token (session_token),
  KEY idx_game_sessions_user (user_id, started_at),
  CONSTRAINT fk_game_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_game_sessions_game FOREIGN KEY (game_id) REFERENCES games (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- KING POT
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS king_pot_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  round_date DATE NOT NULL,
  status ENUM('entered','winner','paid') NOT NULL DEFAULT 'entered',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pot_round_user (round_date, user_id),
  KEY idx_pot_round (round_date, status),
  CONSTRAINT fk_pot_entries_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- LEADERBOARD
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS leaderboard (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period ENUM('daily','weekly','monthly','all_time') NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  score DECIMAL(14,2) NOT NULL DEFAULT 0,
  period_date DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_leaderboard_period_user (period, user_id, period_date),
  KEY idx_leaderboard_rank (period, period_date, score),
  CONSTRAINT fk_leaderboard_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- NOTIFICATIONS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(190) NOT NULL,
  message VARCHAR(1000) NOT NULL,
  image VARCHAR(255) DEFAULT NULL,
  target_type ENUM('all','users','group') NOT NULL DEFAULT 'all',
  target_ids TEXT,
  status ENUM('draft','scheduled','sent') NOT NULL DEFAULT 'draft',
  send_at DATETIME DEFAULT NULL,
  created_by BIGINT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notifications_status (status, send_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_targets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  notification_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  read_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_notification_targets (notification_id, user_id),
  KEY idx_notification_targets_user (user_id, is_read),
  CONSTRAINT fk_notif_targets_notif FOREIGN KEY (notification_id) REFERENCES notifications (id) ON DELETE CASCADE,
  CONSTRAINT fk_notif_targets_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- BANNERS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS banners (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(190) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  image VARCHAR(255) NOT NULL,
  target_url VARCHAR(500) DEFAULT NULL,
  position VARCHAR(30) NOT NULL DEFAULT 'home_top',
  sort_order INT NOT NULL DEFAULT 0,
  start_date DATETIME DEFAULT NULL,
  end_date DATETIME DEFAULT NULL,
  status ENUM('enabled','disabled') NOT NULL DEFAULT 'disabled',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_banners_active (status, position, start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- SETTINGS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(80) NOT NULL,
  setting_value TEXT,
  type ENUM('string','int','bool','json') NOT NULL DEFAULT 'string',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_app_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reward_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  feature_key VARCHAR(80) NOT NULL,
  title VARCHAR(120) NOT NULL,
  settings JSON,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_reward_settings_key (feature_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ad_key VARCHAR(80) NOT NULL,
  ad_value TEXT,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ad_settings_key (ad_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- FRAUD / RISK
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS fraud_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED DEFAULT NULL,
  event_type VARCHAR(60) NOT NULL,
  severity ENUM('low','medium','high') NOT NULL DEFAULT 'low',
  description VARCHAR(500) DEFAULT NULL,
  metadata JSON,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fraud_events_user (user_id, created_at),
  KEY idx_fraud_events_type (event_type),
  CONSTRAINT fk_fraud_events_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS risk_scores (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  reason VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_risk_scores_user (user_id, created_at),
  CONSTRAINT fk_risk_scores_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS blocked_devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  install_id VARCHAR(64) NOT NULL,
  device_id VARCHAR(64) DEFAULT NULL,
  reason VARCHAR(255) DEFAULT NULL,
  blocked_by BIGINT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_blocked_devices_install (install_id),
  CONSTRAINT fk_blocked_devices_admin FOREIGN KEY (blocked_by) REFERENCES admin_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- ADMIN
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_permissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_id BIGINT UNSIGNED NOT NULL,
  permission_key VARCHAR(80) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_permissions (role_id, permission_key),
  CONSTRAINT fk_admin_permissions_role FOREIGN KEY (role_id) REFERENCES admin_roles (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  username VARCHAR(60) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  last_login_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_users_email (email),
  UNIQUE KEY uq_admin_users_username (username),
  KEY idx_admin_users_role (role_id),
  CONSTRAINT fk_admin_users_role FOREIGN KEY (role_id) REFERENCES admin_roles (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  admin_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(80) NOT NULL,
  target_type VARCHAR(40) DEFAULT NULL,
  target_id VARCHAR(64) DEFAULT NULL,
  details VARCHAR(500) DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_admin_logs_admin (admin_id, created_at),
  KEY idx_admin_logs_action (action),
  CONSTRAINT fk_admin_logs_admin FOREIGN KEY (admin_id) REFERENCES admin_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(60) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_admin_login_attempts (username, ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- CONTACT & LEGAL
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contact_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED DEFAULT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  subject VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('new','processing','resolved') NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_contact_messages_status (status),
  CONSTRAINT fk_contact_messages_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legal_pages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(60) NOT NULL,
  title VARCHAR(190) NOT NULL,
  content LONGTEXT,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_legal_pages_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- AUTH SUPPORT
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS password_resets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_password_resets_token (token_hash),
  KEY idx_password_resets_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_rate_limits (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rate_key VARCHAR(190) NOT NULL,
  hits INT NOT NULL DEFAULT 1,
  window_start BIGINT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_api_rate_limits (rate_key, window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SEED DATA (safe to re-run)
-- =====================================================================

-- Admin roles ---------------------------------------------------------
INSERT INTO admin_roles (id, name, description) VALUES
  (1, 'Super Admin', 'Full access to every module'),
  (2, 'Manager', 'Manage users, tasks, rewards and settings'),
  (3, 'Support', 'Handle contact messages and user support'),
  (4, 'Task Manager', 'Manage tasks, games and quizzes'),
  (5, 'Finance', 'Manage withdrawals and wallet adjustments')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT IGNORE INTO admin_permissions (role_id, permission_key) VALUES
  (1, '*'),
  (2, 'users.view'), (2, 'users.manage'), (2, 'tasks.manage'), (2, 'rewards.manage'),
  (2, 'banners.manage'), (2, 'notifications.manage'), (2, 'settings.manage'),
  (2, 'logs.view'), (2, 'admin.manage'),
  (3, 'users.view'), (3, 'messages.manage'), (3, 'users.block'),
  (4, 'tasks.manage'), (4, 'games.manage'), (4, 'quiz.manage'),
  (5, 'withdrawals.manage'), (5, 'wallet.view'), (5, 'wallet.adjust'), (5, 'logs.view');

-- App settings --------------------------------------------------------
INSERT INTO app_settings (setting_key, setting_value, type) VALUES
  ('app_name', 'Rapid Reward Cash', 'string'),
  ('app_logo', '', 'string'),
  ('support_email', 'support@example.com', 'string'),
  ('privacy_policy', '', 'string'),
  ('terms_conditions', '', 'string'),
  ('about', '', 'string'),
  ('maintenance_mode', '0', 'bool'),
  ('maintenance_message', 'We are performing maintenance. Please check back soon.', 'string'),
  ('min_version', '1', 'int'),
  ('latest_version', '1', 'int'),
  ('force_update', '0', 'bool'),
  ('update_message', 'A new version of the app is available. Please update to continue.', 'string'),
  ('min_withdrawal', '5.00', 'string'),
  ('max_withdrawal', '0', 'string'),
  ('withdrawal_enabled', '1', 'bool'),
  ('withdrawal_methods', '[{"key":"bkash","label":"bKash","fields":[{"key":"phone","label":"bKash number","type":"phone","required":true}]},{"key":"nagad","label":"Nagad","fields":[{"key":"phone","label":"Nagad number","type":"phone","required":true}]},{"key":"bank","label":"Bank Transfer","fields":[{"key":"account_name","label":"Account holder name","type":"text","required":true},{"key":"account_number","label":"Account number","type":"text","required":true},{"key":"bank_name","label":"Bank name","type":"text","required":true},{"key":"routing_number","label":"Routing number (optional)","type":"text","required":false}]},{"key":"paypal","label":"PayPal","fields":[{"key":"email","label":"PayPal email","type":"email","required":true}]},{"key":"crypto","label":"Crypto","fields":[{"key":"wallet","label":"Wallet address","type":"text","required":true}]}]', 'json'),
  ('currency', 'USD', 'string'),
  ('currency_symbol', '$', 'string'),
  ('coin_rate', '1', 'string'),
  ('referral_enabled', '1', 'bool'),
  ('referral_reward', '1.00', 'string'),
  ('referral_qualification', '0.00', 'string'),
  ('contact_email', 'support@example.com', 'string'),
  ('contact_phone', '', 'string'),
  ('one_device_one_account', '1', 'bool')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- Reward feature settings ---------------------------------------------
INSERT INTO reward_settings (feature_key, title, settings, enabled) VALUES
  ('spin', 'Spin & Win', JSON_OBJECT('enabled', 1, 'daily_limit', 3, 'cooldown_minutes', 10, 'rewarded_ad', 1), 1),
  ('scratch', 'Scratch & Win', JSON_OBJECT('enabled', 1, 'daily_limit', 4, 'cooldown_minutes', 5), 1),
  ('daily_checkin', 'Daily Check-in', JSON_OBJECT('enabled', 1, 'streak_bonus', 1), 1),
  ('everyday_gift', 'Everyday Gift', JSON_OBJECT('enabled', 1, 'daily_limit', 1, 'cooldown_hours', 24, 'reward', '0.50'), 1),
  ('collect_reward', 'Collect Reward', JSON_OBJECT('enabled', 1, 'daily_limit', 1, 'reward', '0.75'), 1),
  ('open_reward', 'Open Reward', JSON_OBJECT('enabled', 1, 'daily_limit', 1, 'cooldown_hours', 24, 'reward_min', '0.25', 'reward_max', '2.00'), 1),
  ('king_reward', 'King Reward', JSON_OBJECT('enabled', 1, 'cooldown_hours', 24, 'reward', '2.00'), 1),
  ('gold_reward', 'Gold Reward', JSON_OBJECT('enabled', 1, 'cooldown_hours', 24, 'reward_min', '0.50', 'reward_max', '3.00'), 1),
  ('king_pot', 'King Pot', JSON_OBJECT('enabled', 1, 'reward', '10.00'), 1),
  ('solo_reward', 'Solo Reward', JSON_OBJECT('enabled', 1, 'cooldown_hours', 24, 'reward', '1.00'), 1),
  ('pay_earn_gift', 'Pay Earn Gift', JSON_OBJECT('enabled', 1, 'cooldown_hours', 24, 'reward', '1.50'), 1),
  ('math_quiz', 'Math Quiz', JSON_OBJECT('enabled', 1, 'daily_limit', 3, 'questions', 5, 'pass_score', 3), 1),
  ('tic_tac_toe', 'Tic Tac Toe', JSON_OBJECT('enabled', 1, 'reward_win', '0.50', 'reward_draw', '0.10', 'daily_limit', 5), 1),
  ('game_reward', 'HTML5 Games', JSON_OBJECT('enabled', 1, 'cooldown_seconds', 120), 1),
  ('tasks_web_visit', 'Web Visit Tasks', JSON_OBJECT('enabled', 1, 'cooldown_seconds', 60), 1),
  ('tasks_link_promote', 'Link Promote Tasks', JSON_OBJECT('enabled', 1, 'cooldown_seconds', 120), 1),
  ('tasks_video', 'Video Tasks', JSON_OBJECT('enabled', 1, 'cooldown_seconds', 60), 1)
ON DUPLICATE KEY UPDATE title = VALUES(title), settings = VALUES(settings), enabled = VALUES(enabled);

-- Daily rewards (default: Day 1..7) ----------------------------------
INSERT INTO daily_rewards (day_no, reward, enabled) VALUES
  (1, 10, 1), (2, 15, 1), (3, 20, 1), (4, 30, 1), (5, 40, 1), (6, 50, 1), (7, 100, 1)
ON DUPLICATE KEY UPDATE reward = VALUES(reward), enabled = VALUES(enabled);

-- Spin wheel segments -------------------------------------------------
INSERT INTO spin_configs (id, label, prize_value, probability, color, enabled, sort_order) VALUES
  (1, '10 Coins', 10.00, 0.2500, '#F59E0B', 1, 1),
  (2, '5 Coins', 5.00, 0.3000, '#10B981', 1, 2),
  (3, '15 Coins', 15.00, 0.1500, '#3B82F6', 1, 3),
  (4, '20 Coins', 20.00, 0.1000, '#8B5CF6', 1, 4),
  (5, '2 Coins', 2.00, 0.1500, '#EF4444', 1, 5),
  (6, '50 Coins', 50.00, 0.0500, '#F59E0B', 1, 6)
ON DUPLICATE KEY UPDATE label = VALUES(label), prize_value = VALUES(prize_value),
  probability = VALUES(probability), color = VALUES(color), enabled = VALUES(enabled), sort_order = VALUES(sort_order);

-- Quiz questions (seeded only when the table is empty) -----------------
INSERT INTO quiz_questions (question, option_a, option_b, option_c, option_d, correct_option, difficulty, reward, time_limit_seconds, enabled)
SELECT 'What is 7 × 8?', '54', '56', '58', '64', 'b', 'easy', 0.10, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 144 ÷ 12?', '10', '11', '12', '14', 'c', 'easy', 0.10, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 15 + 27?', '40', '41', '42', '43', 'c', 'easy', 0.10, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 9 × 9?', '72', '79', '81', '89', 'c', 'easy', 0.10, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 100 − 37?', '63', '67', '73', '77', 'a', 'easy', 0.10, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 3² × 4?', '24', '36', '48', '64', 'b', 'medium', 0.15, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 25% of 160?', '30', '35', '40', '45', 'c', 'medium', 0.15, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 7 × 12 − 9?', '75', '76', '83', '85', 'a', 'medium', 0.15, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 0.5 × 0.4?', '0.2', '0.25', '0.02', '2.0', 'a', 'medium', 0.15, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is the square root of 196?', '12', '13', '14', '16', 'c', 'medium', 0.15, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 2⁵?', '16', '24', '30', '32', 'd', 'hard', 0.25, 15, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 13 × 17?', '211', '221', '231', '241', 'b', 'hard', 0.25, 20, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 5! (5 factorial)?', '60', '100', '120', '150', 'c', 'hard', 0.25, 20, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 15% of 240?', '30', '34', '36', '40', 'c', 'hard', 0.25, 20, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions)
UNION ALL SELECT 'What is 8³?', '256', '384', '512', '576', 'c', 'hard', 0.25, 20, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM quiz_questions);

-- Games (seeded only when the table is empty) ---------------------------
INSERT INTO games (name, thumbnail, game_url, description, reward, daily_limit, status, sort_order)
SELECT 'Tic Tac Toe', NULL, NULL, 'Classic 3×3 — beat the AI to earn.', CAST(0.50 AS DECIMAL(14,2)), 5, 'enabled', 1
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM games);

-- Sample tasks (seeded only when the table is empty) ---------------------
-- Placeholder URLs — replace with real destinations in the Admin Panel.
INSERT INTO tasks (type, title, description, url, reward, daily_limit, duration_seconds, status, sort_order)
SELECT 'web_visit', 'Visit our partner site', 'Spend a moment exploring the site, then come back to claim.', 'https://example.com', CAST(0.10 AS DECIMAL(14,2)), 3, 30, 'enabled', 1
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM tasks)
UNION ALL SELECT 'web_visit', 'Check out the new collection', 'Browse the featured collection and return to earn.', 'https://example.com/collection', CAST(0.15 AS DECIMAL(14,2)), 3, 30, 'enabled', 2
UNION ALL SELECT 'link_promote', 'Share our landing page', 'Open the link, take a look, and come back.', 'https://example.com/landing', CAST(0.20 AS DECIMAL(14,2)), 2, 45, 'enabled', 1
UNION ALL SELECT 'link_promote', 'Discover today\'s deal', 'Visit today\'s featured deal page.', 'https://example.com/deal', CAST(0.15 AS DECIMAL(14,2)), 2, 45, 'enabled', 2
UNION ALL SELECT 'video', 'Watch our welcome video', 'Watch the short welcome video to earn.', 'https://commondatastorage.googleapis.com/gtv-videos-bucket/sample/BigBuckBunny.mp4', CAST(0.25 AS DECIMAL(14,2)), 2, 60, 'enabled', 1
UNION ALL SELECT 'video', 'Watch the product demo', 'Watch the demo video and claim your reward.', 'https://commondatastorage.googleapis.com/gtv-videos-bucket/sample/ForBiggerBlazes.mp4', CAST(0.20 AS DECIMAL(14,2)), 2, 45, 'enabled', 2
UNION ALL SELECT 'html5_game', 'Play the puzzle game', 'Play the HTML5 puzzle for a minute to earn.', 'https://example.com/game', CAST(0.30 AS DECIMAL(14,2)), 2, 60, 'enabled', 1
UNION ALL SELECT 'html5_game', 'Play the arcade challenge', 'Enjoy the arcade game and come back to claim.', 'https://example.com/arcade', CAST(0.25 AS DECIMAL(14,2)), 2, 45, 'enabled', 2;

-- Scratch cards -------------------------------------------------------
INSERT INTO scratch_configs (card_no, reward, probability, daily_limit, cooldown_minutes, enabled) VALUES
  (1, 0.50, 1.0000, 1, 60, 1),
  (2, 0.75, 1.0000, 1, 60, 1),
  (3, 1.00, 1.0000, 1, 60, 1),
  (4, 1.50, 1.0000, 1, 60, 1)
ON DUPLICATE KEY UPDATE reward = VALUES(reward), probability = VALUES(probability),
  daily_limit = VALUES(daily_limit), cooldown_minutes = VALUES(cooldown_minutes), enabled = VALUES(enabled);

-- Ad settings (defaults; real ad unit IDs are entered in the Admin Panel) --
INSERT INTO ad_settings (ad_key, ad_value) VALUES
  ('ads_enabled', '1'),
  ('banner_enabled', '1'),
  ('interstitial_enabled', '1'),
  ('rewarded_enabled', '1'),
  ('app_open_enabled', '0'),
  ('banner_ad_id', ''),
  ('interstitial_ad_id', ''),
  ('rewarded_ad_id', ''),
  ('app_open_ad_id', ''),
  ('ad_frequency_seconds', '180')
ON DUPLICATE KEY UPDATE ad_value = VALUES(ad_value);

-- Legal pages ---------------------------------------------------------
INSERT INTO legal_pages (slug, title, content) VALUES
  ('privacy-policy', 'Privacy Policy', '<h3>Privacy Policy</h3><p>This privacy policy explains how Rapid Reward Cash collects, uses and protects your information. Full policy content will be managed here by the administrator.</p>'),
  ('terms-conditions', 'Terms & Conditions', '<h3>Terms & Conditions</h3><p>By using Rapid Reward Cash you agree to these terms. Rewards are subject to verification and anti-fraud rules. Full terms will be managed here by the administrator.</p>'),
  ('about', 'About', '<h3>About Rapid Reward Cash</h3><p>A modern rewards app where users can complete tasks, play games and earn rewards.</p>'),
  ('contact-us', 'Contact Us', '<h3>Contact Us</h3><p>Reach our support team for any questions or issues.</p>')
ON DUPLICATE KEY UPDATE title = VALUES(title), content = VALUES(content);

-- Welcome notification (sent to all users, insert once) ---------------------
INSERT INTO notifications (title, message, target_type, status, send_at)
SELECT 'Welcome to Rapid Reward Cash', 'Start earning today! Claim your daily reward, invite friends and explore the games. Good luck!', 'all', 'sent', NULL
WHERE NOT EXISTS (SELECT 1 FROM notifications WHERE title = 'Welcome to Rapid Reward Cash');

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- END OF SCHEMA
-- =====================================================================