USE aihub;

CREATE TABLE IF NOT EXISTS surveys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  description TEXT NULL,
  reward_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  unlock_points INT NOT NULL DEFAULT 0,
  min_deposit DECIMAL(15,2) NOT NULL DEFAULT 0,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS survey_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  survey_id INT NOT NULL,
  question_text VARCHAR(500) NOT NULL,
  question_type ENUM('text','choice') NOT NULL DEFAULT 'choice',
  choices_json JSON NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (survey_id) REFERENCES surveys(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS survey_responses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  survey_id INT NOT NULL,
  user_id INT NOT NULL,
  reward_paid DECIMAL(15,2) NOT NULL DEFAULT 0,
  points_awarded INT NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_survey (survey_id, user_id),
  FOREIGN KEY (survey_id) REFERENCES surveys(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS survey_answers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  response_id INT NOT NULL,
  question_id INT NOT NULL,
  answer_text TEXT NOT NULL,
  FOREIGN KEY (response_id) REFERENCES survey_responses(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES survey_questions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS promo_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  promo_type ENUM('wallet_bonus','deposit_match','sub_discount') NOT NULL DEFAULT 'wallet_bonus',
  value DECIMAL(15,2) NOT NULL,
  max_uses INT NOT NULL DEFAULT 0,
  uses_count INT NOT NULL DEFAULT 0,
  wagering_mult DECIMAL(5,2) NOT NULL DEFAULT 0,
  expires_at DATETIME NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS promo_redemptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  promo_id INT NOT NULL,
  user_id INT NOT NULL,
  amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (promo_id) REFERENCES promo_codes(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS referral_commissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  referrer_id INT NOT NULL,
  referred_id INT NOT NULL,
  trigger_type VARCHAR(40) NOT NULL,
  base_amount DECIMAL(15,2) NOT NULL,
  commission_pct DECIMAL(5,2) NOT NULL,
  commission_amount DECIMAL(15,2) NOT NULL,
  status ENUM('pending','paid') NOT NULL DEFAULT 'paid',
  reference_id INT NULL,
  reference_table VARCHAR(40) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_missions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  mission_code VARCHAR(40) NOT NULL,
  status ENUM('pending','completed') NOT NULL DEFAULT 'pending',
  reward_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  completed_at DATETIME NULL,
  UNIQUE KEY uq_user_mission (user_id, mission_code),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS missions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mission_code VARCHAR(40) NOT NULL UNIQUE,
  title VARCHAR(150) NOT NULL,
  description VARCHAR(255) NULL,
  reward_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  mission_type VARCHAR(40) NOT NULL,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active'
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS daily_login_claims (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  claim_date DATE NOT NULL,
  streak_day INT NOT NULL DEFAULT 1,
  reward_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_user_date (user_id, claim_date),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_flags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  flag_type VARCHAR(40) NOT NULL,
  note VARCHAR(255) NULL,
  status ENUM('open','reviewed','dismissed') NOT NULL DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- bonus_balance and agent columns (ignore errors if exist)

INSERT IGNORE INTO missions (mission_code, title, description, reward_amount, mission_type) VALUES
('kyc_complete', 'Verify Identity', 'Complete KYC verification', 3000, 'kyc'),
('first_bet', 'First Casino Bet', 'Place your first casino bet', 1000, 'casino'),
('first_deposit', 'First Deposit', 'Make your first deposit', 2000, 'deposit'),
('invite_3', 'Invite 3 Friends', 'Refer 3 friends', 10000, 'referral');

INSERT IGNORE INTO surveys (title, description, reward_amount, unlock_points, status) VALUES
('Welcome Feedback', 'Tell us about yourself', 1000, 1, 'active'),
('Gaming Preferences', 'What games do you like?', 2500, 2, 'active');