CREATE DATABASE IF NOT EXISTS matka_arena
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE matka_arena;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  mobile VARCHAR(15) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  mpin_hash VARCHAR(255) NOT NULL,
  referral_code VARCHAR(40) NULL,
  points_balance BIGINT NOT NULL DEFAULT 0,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_sessions_user_expiry (user_id, expires_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  permissions JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  mobile VARCHAR(15) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES admin_roles(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
  INDEX idx_admin_sessions_expiry (admin_id, expires_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS agents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by BIGINT UNSIGNED NOT NULL,
  parent_agent_id BIGINT UNSIGNED NULL,
  name VARCHAR(100) NOT NULL,
  mobile VARCHAR(15) NOT NULL UNIQUE,
  agent_code VARCHAR(40) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  share_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  points_balance BIGINT NOT NULL DEFAULT 0,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES admins(id),
  FOREIGN KEY (parent_agent_id) REFERENCES agents(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS agent_partnerships (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agent_id BIGINT UNSIGNED NOT NULL,
  game VARCHAR(100) NOT NULL,
  market VARCHAR(100) NULL,
  share_percent DECIMAL(5,2) NOT NULL,
  effective_from DATETIME NOT NULL,
  effective_to DATETIME NULL,
  status ENUM('active','ended') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
  INDEX idx_partnership_agent_status (agent_id, status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ludo_rooms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_code VARCHAR(20) NOT NULL UNIQUE,
  mode ENUM('full','one_home','two_home','friends') NOT NULL,
  host_user_id BIGINT UNSIGNED NOT NULL,
  guest_user_id BIGINT UNSIGNED NULL,
  stake_points BIGINT NOT NULL,
  total_pot BIGINT NOT NULL,
  platform_fee BIGINT NOT NULL,
  winner_payout BIGINT NOT NULL,
  winner_user_id BIGINT UNSIGNED NULL,
  game_state JSON NULL,
  state_version BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('waiting','playing','completed','cancelled') NOT NULL DEFAULT 'waiting',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  FOREIGN KEY (host_user_id) REFERENCES users(id),
  FOREIGN KEY (guest_user_id) REFERENCES users(id),
  FOREIGN KEY (winner_user_id) REFERENCES users(id),
  INDEX idx_ludo_rooms_status_created (status,created_at DESC)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ludo_join_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','approved','rejected','expired') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  FOREIGN KEY (room_id) REFERENCES ludo_rooms(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_ludo_room_request_user (room_id,user_id),
  INDEX idx_ludo_requests_room_status (room_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  reference VARCHAR(120) NOT NULL,
  title VARCHAR(180) NOT NULL,
  game VARCHAR(80) NOT NULL,
  type ENUM('Deposit','Withdrawal','Bet','Win','Refund') NOT NULL,
  amount BIGINT NOT NULL,
  status ENUM('Pending','Completed','Rejected','Refunded','Failed') NOT NULL,
  metadata JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_wallet_user_reference (user_id, reference),
  INDEX idx_wallet_user_created (user_id, created_at DESC),
  INDEX idx_wallet_user_game_type (user_id, game, type),
  INDEX idx_wallet_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS game_entries (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  wallet_transaction_id BIGINT UNSIGNED NOT NULL,
  game VARCHAR(80) NOT NULL,
  market VARCHAR(100) NULL,
  round_ref VARCHAR(100) NULL,
  selection_data JSON NOT NULL,
  points BIGINT NOT NULL,
  result_data JSON NULL,
  outcome ENUM('pending','won','lost','cancelled','refunded') NOT NULL DEFAULT 'pending',
  payout BIGINT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  settled_at DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (wallet_transaction_id) REFERENCES wallet_transactions(id) ON DELETE CASCADE,
  INDEX idx_entries_user_created (user_id, created_at DESC),
  INDEX idx_entries_game_round (game, round_ref),
  INDEX idx_entries_outcome (outcome)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS withdrawal_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  wallet_transaction_id BIGINT UNSIGNED NOT NULL UNIQUE,
  method ENUM('upi','qr') NOT NULL,
  destination VARCHAR(255) NULL,
  qr_path VARCHAR(255) NULL,
  status ENUM('Pending','Completed','Rejected') NOT NULL DEFAULT 'Pending',
  admin_note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (wallet_transaction_id) REFERENCES wallet_transactions(id) ON DELETE CASCADE,
  INDEX idx_withdrawal_status_created (status, created_at DESC)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  external_key VARCHAR(160) NULL,
  title VARCHAR(160) NOT NULL,
  message VARCHAR(500) NOT NULL,
  category VARCHAR(40) NOT NULL DEFAULT 'general',
  payload JSON NULL,
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notifications_user_read (user_id, is_read, created_at DESC),
  UNIQUE KEY uq_notifications_user_external (user_id, external_key)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_settings (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  settings JSON NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS game_results (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  game VARCHAR(80) NOT NULL,
  market VARCHAR(100) NOT NULL,
  round_ref VARCHAR(100) NOT NULL,
  result_value VARCHAR(80) NOT NULL,
  result_label VARCHAR(120) NULL,
  result_time DATETIME NOT NULL,
  metadata JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_game_result_round (game, market, round_ref),
  INDEX idx_game_results_lookup (game, market, result_time DESC)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS game_controls (
  game VARCHAR(100) PRIMARY KEY,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  maintenance_message VARCHAR(255) NULL,
  updated_by BIGINT UNSIGNED NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS game_timing_controls (
  game VARCHAR(100) PRIMARY KEY,
  round_seconds INT UNSIGNED NOT NULL DEFAULT 15,
  lock_seconds INT UNSIGNED NOT NULL DEFAULT 3,
  result_display_ms INT UNSIGNED NOT NULL DEFAULT 3000,
  cutoff_minutes INT UNSIGNED NOT NULL DEFAULT 10,
  updated_by BIGINT UNSIGNED NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (updated_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO game_timing_controls(game,round_seconds,lock_seconds,result_display_ms,cutoff_minutes) VALUES
('Bombay Games',15,3,3000,10),('Delhi Games',15,3,3000,10),
('Starline',15,3,3000,10),('Dragon Tiger',15,3,3000,10),
('Lucky Wheel',20,4,3200,10),('Color Prediction',15,3,3600,10),
('7 Up Down',15,3,3000,10),('Aviator',8,0,3000,10),
('Ice Fishing',15,0,3500,10),
('Ludo',60,0,3000,10)
ON DUPLICATE KEY UPDATE game=VALUES(game);

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity_type VARCHAR(60) NOT NULL,
  entity_ref VARCHAR(120) NULL,
  payload JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_created (created_at DESC),
  INDEX idx_audit_entity (entity_type, entity_ref)
) ENGINE=InnoDB;
