USE aihub;

CREATE TABLE IF NOT EXISTS wallet_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  wallet_type ENUM('main','investment','bonus','cashback','referral') NOT NULL,
  balance DECIMAL(15,2) NOT NULL DEFAULT 0,
  locked_balance DECIMAL(15,2) NOT NULL DEFAULT 0,
  wagering_required DECIMAL(15,2) NOT NULL DEFAULT 0,
  wagering_progress DECIMAL(15,2) NOT NULL DEFAULT 0,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_wallet (user_id, wallet_type),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_ledger (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  wallet_type VARCHAR(20) NOT NULL,
  direction ENUM('credit','debit') NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  balance_after DECIMAL(15,2) NOT NULL DEFAULT 0,
  category ENUM('deposit','withdrawal','bet','win','loss','transfer','bonus','cashback','referral','commission','promo','income','other') NOT NULL DEFAULT 'other',
  description VARCHAR(255) NULL,
  reference_id INT NULL,
  reference_table VARCHAR(40) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_cat (user_id, category, created_at),
  INDEX idx_user_wallet (user_id, wallet_type, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_transfers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  from_wallet VARCHAR(20) NOT NULL,
  to_wallet VARCHAR(20) NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  fee_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  status ENUM('completed','failed','pending') NOT NULL DEFAULT 'completed',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fraud_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  event_type VARCHAR(40) NOT NULL,
  severity ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  detail JSON 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,
  INDEX idx_user_status (user_id, status),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

INSERT IGNORE INTO wallet_accounts (user_id, wallet_type, balance, locked_balance)
SELECT id, 'main', wallet, 0 FROM users;

INSERT IGNORE INTO wallet_accounts (user_id, wallet_type, balance, locked_balance)
SELECT id, 'investment', balance, 0 FROM users;

INSERT IGNORE INTO wallet_accounts (user_id, wallet_type, balance, locked_balance)
SELECT id, 'bonus', COALESCE(bonus_balance, 0), 0 FROM users;

INSERT IGNORE INTO wallet_accounts (user_id, wallet_type, balance, locked_balance)
SELECT id, 'cashback', 0, 0 FROM users;

INSERT IGNORE INTO wallet_accounts (user_id, wallet_type, balance, locked_balance)
SELECT id, 'referral', 0, 0 FROM users;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('cashback_loss_pct', '1'),
('wallet_referral_to_main_fee_pct', '0'),
('wallet_cashback_to_main_fee_pct', '0'),
('fraud_max_withdrawals_per_day', '5'),
('fraud_max_withdraw_amount_daily', '2000000'),
('fraud_max_bets_per_minute', '30');