-- Sand World database schema
-- Import this once into your MySQL database before uploading the PHP files.

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
  username VARCHAR(64) DEFAULT NULL,
  first_name VARCHAR(128) DEFAULT NULL,
  sand_balance BIGINT NOT NULL DEFAULT 0,
  total_sand BIGINT NOT NULL DEFAULT 0,
  usdt_balance DECIMAL(18,4) NOT NULL DEFAULT 0,
  referred_by INT UNSIGNED DEFAULT NULL,
  ref_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,
  is_suspended TINYINT(1) NOT NULL DEFAULT 0,
  ads_watched INT UNSIGNED NOT NULL DEFAULT 0,
  ads_today INT UNSIGNED NOT NULL DEFAULT 0,
  ads_today_date DATE DEFAULT NULL,
  withdrawal_ads_watched INT UNSIGNED NOT NULL DEFAULT 0,
  last_daily_claim DATE DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_referred_by (referred_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  price BIGINT NOT NULL,
  daily_yield BIGINT NOT NULL,
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_assets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  asset_id INT UNSIGNED NOT NULL,
  purchased_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  last_payout_at DATETIME DEFAULT NULL,
  KEY idx_user (user_id),
  KEY idx_asset (asset_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tasks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(128) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  link VARCHAR(255) DEFAULT NULL,
  reward BIGINT NOT NULL DEFAULT 0,
  category ENUM('social','partners','watch','refer') NOT NULL DEFAULT 'social',
  must_join TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_tasks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  task_id INT UNSIGNED NOT NULL,
  completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_task (user_id, task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS promo_codes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(64) NOT NULL UNIQUE,
  reward BIGINT NOT NULL,
  usage_limit INT UNSIGNED NOT NULL DEFAULT 0,
  used_count INT UNSIGNED NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS promo_redemptions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  promo_id INT UNSIGNED NOT NULL,
  redeemed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_promo (user_id, promo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS withdrawals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  amount_usdt DECIMAL(18,4) NOT NULL,
  wallet_address VARCHAR(128) NOT NULL,
  network VARCHAR(16) NOT NULL DEFAULT 'BEP20',
  status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  tx_hash VARCHAR(128) DEFAULT NULL,
  api_response TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS task_proofs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  task_id INT UNSIGNED NOT NULL,
  proof_text TEXT NULL,
  proof_photo VARCHAR(255) DEFAULT NULL,
  status ENUM('pending','approved','declined') NOT NULL DEFAULT 'pending',
  admin_note VARCHAR(255) DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_proof_status (status),
  KEY idx_proof_user_task (user_id, task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS broadcasts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  message TEXT NOT NULL,
  photo_url VARCHAR(255) DEFAULT NULL,
  target ENUM('all','active') NOT NULL DEFAULT 'all',
  sent_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

CREATE TABLE IF NOT EXISTS logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(32) NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default settings. Change the admin password hash after import by logging
-- into the admin panel (Settings > Admin Password) -- default password is: admin123
INSERT INTO settings (setting_key, setting_value) VALUES
 ('bot_token', ''),
 ('bot_username', ''),
 ('mini_app_url', ''),
 ('admin_chat_id', ''),
 ('channel_url', ''),
 ('group_url', ''),
 ('daily_reward', '30'),
 ('ad_reward', '5'),
 ('daily_ad_limit', '20'),
 ('ads_before_withdrawal', '10'),
 ('monetag_zone_id', ''),
 ('monetag_ad_link', ''),
 ('sand_per_usdt', '10000'),
 ('min_withdrawal', '0.3'),
 ('referral_initial_reward', '100'),
 ('referral_max_reward', '150'),
 ('referral_passive_percent', '20'),
 ('referrals_enabled', 'true'),
 ('rewarded_ads_enabled', 'true'),
 ('in_app_ads_enabled', 'true'),
 ('autopayout_enabled', 'false'),
 ('autopayout_api_key', ''),
 ('autopayout_gateway_key', ''),
 ('autopayout_api_url', ''),
 ('autopayout_token_address', ''),
 ('payout_channel_link', ''),
 ('admin_password_hash', '')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- Seed the first admin (the Telegram ID you gave Claude) so you can log
-- into /admin right away, even before that account has pressed /start.
INSERT INTO users (telegram_id, username, first_name, is_admin) VALUES
 (7710195409, NULL, 'Admin', 1)
ON DUPLICATE KEY UPDATE is_admin = 1;

-- Note: admin_password_hash starts empty. The first time you log in to
-- /admin, use password "admin123" -- login.php recognises the empty-hash
-- state and treats that as the bootstrap password. Change it immediately
-- from Settings > Admin Password once you're in.