-- Existing Tables (inferred from index.php and assumed structures)

-- Users Table (Primary user information)
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(255) NOT NULL UNIQUE,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(255) DEFAULT '/assets/images/avtar/default.png',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- Add any other existing user fields
  `role` ENUM('user', 'admin', 'master_admin') DEFAULT 'user' -- Added for admin roles
);

-- Wallets Table (User balances and wagered/won totals)
CREATE TABLE IF NOT EXISTS `wallets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL UNIQUE,
  `main_balance` DECIMAL(15, 2) DEFAULT 0.00,
  `bonus_balance` DECIMAL(15, 2) DEFAULT 0.00, -- Added for freebets/bonuses
  `total_wagered` DECIMAL(15, 2) DEFAULT 0.00,
  `total_won` DECIMAL(15, 2) DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- Transactions Table (Detailed record of all money movements)
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `type` VARCHAR(50) NOT NULL, -- e.g., 'bet', 'win', 'deposit', 'withdrawal', 'bonus', 'freebet_spend'
  `amount` DECIMAL(15, 2) NOT NULL,
  `currency` VARCHAR(10) DEFAULT 'ZMK',
  `method` VARCHAR(50), -- e.g., 'game_bet', 'game_cashout', 'mpesa', 'card'
  `reference_id` VARCHAR(255), -- Reference to bet_id, deposit_id, etc.
  `description` TEXT,
  `status` VARCHAR(50) DEFAULT 'pending', -- e.g., 'completed', 'pending', 'failed'
  `wallet_type` VARCHAR(50) DEFAULT 'main', -- 'main' or 'bonus'
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- GameResult Table (History of each game round's outcome)
CREATE TABLE IF NOT EXISTS `gameresult` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `round_number` INT UNIQUE NOT NULL, -- Added for easier tracking
  `result` DECIMAL(15, 2) DEFAULT 1.00, -- The crash multiplier (e.g., 2.50)
  `seed` VARCHAR(255) NOT NULL, -- Server seed for provably fair
  `hash` VARCHAR(255) NOT NULL, -- Hash of the server seed
  `salt` VARCHAR(255) NOT NULL, -- Client salt for provably fair
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- UserBits Table (Individual bets placed by users in each game round)
CREATE TABLE IF NOT EXISTS `userbits` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `userid` INT NOT NULL,
  `gameid` INT NOT NULL,
  `amount` DECIMAL(15, 2) NOT NULL,
  `type` VARCHAR(50) DEFAULT 'manual', -- 'manual', 'auto_bet', 'freebet'
  `section_no` TINYINT DEFAULT 1, -- For dual bets (e.g., 1 or 2)
  `status` TINYINT DEFAULT 0, -- 0: active, 1: cashed out/lost, 2: cancelled (e.g. freebet expired)
  `cashout_multiplier` DECIMAL(15, 2) DEFAULT 0.00,
  `cashout_amount` DECIMAL(15, 2) DEFAULT 0.00,
  `profit_loss` DECIMAL(15, 2) DEFAULT 0.00,
  `auto_cashout_target` DECIMAL(15, 2) DEFAULT 0.00, -- Target for auto cashout
  `auto_bet_enabled` TINYINT DEFAULT 0, -- 1 if auto bet was enabled
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`userid`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`gameid`) REFERENCES `gameresult`(`id`) ON DELETE CASCADE
);

-- GSettings Table (General game settings)
CREATE TABLE IF NOT EXISTS `gsettings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category` VARCHAR(100) NOT NULL UNIQUE,
  `value` TEXT,
  `description` TEXT,
  `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- New Tables for Aviator Game Features

-- GameRounds Table (More detailed game round management)
-- This table will store more volatile data for the current active round
CREATE TABLE IF NOT EXISTS `game_rounds` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `gameresult_id` INT NOT NULL UNIQUE, -- Foreign key to gameresult for historical data
  `status` ENUM('waiting', 'betting', 'flying', 'crashed', 'completed') DEFAULT 'waiting',
  `current_multiplier` DECIMAL(15, 2) DEFAULT 1.00,
  `start_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `crash_time` TIMESTAMP NULL,
  `players_in_round` INT DEFAULT 0,
  `total_bets_amount` DECIMAL(15, 2) DEFAULT 0.00,
  `total_cashed_out_amount` DECIMAL(15, 2) DEFAULT 0.00,
  FOREIGN KEY (`gameresult_id`) REFERENCES `gameresult`(`id`) ON DELETE CASCADE
);

-- FreeBets Table (Manage freebets issued to users)
CREATE TABLE IF NOT EXISTS `freebets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(15, 2) NOT NULL,
  `remaining_amount` DECIMAL(15, 2) NOT NULL,
  `status` ENUM('active', 'used', 'expired', 'cancelled') DEFAULT 'active',
  `valid_until` DATETIME,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `issued_by` INT, -- Admin user ID who issued it
  `reason` TEXT,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- ChatMessages Table (For the in-game chat system)
CREATE TABLE IF NOT EXISTS `chat_messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `message` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- RainSystem Table (To manage the "rain" feature where freebets are dropped)
CREATE TABLE IF NOT EXISTS `rain_system` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `amount` DECIMAL(15, 2) NOT NULL,
  `number_of_users` INT NOT NULL,
  `status` ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME,
  `claimed_by_users` JSON, -- Store user_ids who claimed it as JSON array
  `issued_by` INT, -- Admin user ID
  FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
);

-- AdminLog Table (To track significant admin actions)
CREATE TABLE IF NOT EXISTS `admin_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `admin_id` INT NOT NULL,
  `action` VARCHAR(255) NOT NULL,
  `details` JSON, -- Store details of the action as JSON
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`admin_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);
