-- ==========================================================
-- LOTUS365 GAMING & WINGO PREDICTION SYSTEM - MYSQL SCHEMA
-- Compatible with cPanel MySQL / MariaDB (InnoDB, utf8mb4)
-- ==========================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+05:30";

-- 1. Admin Accounts Table
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(60) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('superadmin','operator') NOT NULL DEFAULT 'superadmin',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default Admin: admin / Admin@123
INSERT INTO `admins` (`username`, `password_hash`, `role`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'superadmin')
ON DUPLICATE KEY UPDATE `username` = `username`;

-- 2. System Settings & Signup Bonus Configuration
CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_key` VARCHAR(64) NOT NULL,
  `setting_value` VARCHAR(255) NOT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `system_settings` (`setting_key`, `setting_value`) VALUES
('signup_bonus_enabled', '1'),
('signup_bonus_amount', '100.00'),
('referral_bonus_amount', '50.00'),
('min_deposit', '100.00'),
('min_withdraw', '200.00'),
('max_withdraw', '50000.00'),
('site_title', 'Lotus365 Gaming')
ON DUPLICATE KEY UPDATE `setting_key` = `setting_key`;

-- 3. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uid` VARCHAR(20) NOT NULL UNIQUE,
  `full_name` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(15) NOT NULL UNIQUE,
  `email` VARCHAR(120) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `referral_code` VARCHAR(20) DEFAULT NULL,
  `referred_by` VARCHAR(20) DEFAULT NULL,
  `wallet_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `bonus_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `signup_bonus_claimed` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('active','banned','suspended') NOT NULL DEFAULT 'active',
  `api_token` VARCHAR(128) DEFAULT NULL,
  `last_login` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_phone` (`phone`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Per-Game Controls (Win %, Loss %, RTP, House Edge, Enabled/Disabled, Force Controls)
CREATE TABLE IF NOT EXISTS `game_controls` (
  `game_slug` VARCHAR(32) NOT NULL,
  `game_name` VARCHAR(64) NOT NULL,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `win_percent` DECIMAL(5,2) NOT NULL DEFAULT 45.00,
  `loss_percent` DECIMAL(5,2) NOT NULL DEFAULT 55.00,
  `min_bet` DECIMAL(10,2) NOT NULL DEFAULT 10.00,
  `max_bet` DECIMAL(12,2) NOT NULL DEFAULT 50000.00,
  `max_multiplier` DECIMAL(8,2) NOT NULL DEFAULT 50.00,
  `force_crash_at` DECIMAL(8,2) DEFAULT NULL COMMENT 'For Aviator/LuckyJet next round crash override',
  `force_trap_mode` ENUM('normal','instant_loss','high_win') NOT NULL DEFAULT 'normal' COMMENT 'For Mines/Chicken/Tower',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `game_controls` (`game_slug`, `game_name`, `is_enabled`, `win_percent`, `loss_percent`, `min_bet`, `max_bet`, `max_multiplier`) VALUES
('aviator', 'Aviator', 1, 42.00, 58.00, 10.00, 50000.00, 50.00),
('chicken-road', 'Chicken Road', 1, 45.00, 55.00, 10.00, 25000.00, 72.53),
('lucky-jet', 'Lucky Jet', 1, 42.00, 58.00, 10.00, 50000.00, 50.00),
('mines', 'Mines', 1, 46.00, 54.00, 10.00, 30000.00, 24.25),
('tower-rush', 'Tower Rush', 1, 45.00, 55.00, 10.00, 25000.00, 20.00),
('wingo', 'Wingo 1 Min', 1, 40.00, 60.00, 10.00, 50000.00, 9.00)
ON DUPLICATE KEY UPDATE `game_slug` = `game_slug`;

-- 5. Wingo Periods & Admin Manual Result Override Table
CREATE TABLE IF NOT EXISTS `wingo_periods` (
  `period_id` VARCHAR(24) NOT NULL,
  `status` ENUM('open','locked','settled') NOT NULL DEFAULT 'open',
  `admin_forced_number` TINYINT DEFAULT NULL COMMENT '0-9 set by admin button click; NULL = auto/lowest-payout',
  `admin_forced_color` VARCHAR(20) DEFAULT NULL,
  `admin_forced_size` ENUM('Big','Small') DEFAULT NULL,
  `result_number` TINYINT DEFAULT NULL,
  `result_colors` VARCHAR(32) DEFAULT NULL,
  `result_size` ENUM('Big','Small') DEFAULT NULL,
  `total_bet_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `total_payout_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `settled_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`period_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Wingo Live & Historical Bets Table
CREATE TABLE IF NOT EXISTS `wingo_bets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `period_id` VARCHAR(24) NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `bet_type` ENUM('color','number','size') NOT NULL,
  `bet_value` VARCHAR(16) NOT NULL COMMENT 'green, violet, red, 0-9, Big, Small',
  `amount` DECIMAL(12,2) NOT NULL,
  `payout_multiplier` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `win_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('pending','won','lost') NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_period` (`period_id`),
  INDEX `idx_user` (`user_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Unified Live Bets Log Across All 6 Games
CREATE TABLE IF NOT EXISTS `game_bets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `game_slug` VARCHAR(32) NOT NULL,
  `round_ref` VARCHAR(48) DEFAULT NULL,
  `selection` VARCHAR(64) NOT NULL,
  `bet_amount` DECIMAL(12,2) NOT NULL,
  `multiplier` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  `win_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `outcome` ENUM('pending','win','loss') NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_game` (`game_slug`),
  INDEX `idx_outcome` (`outcome`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Wallet Transactions (Signup Bonus, Deposit, Withdraw, Admin Credit/Debit, Bet/Win)
CREATE TABLE IF NOT EXISTS `wallet_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `tx_type` ENUM('signup_bonus','deposit','withdraw','bet','win','admin_adjust') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `balance_after` DECIMAL(12,2) NOT NULL,
  `remark` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('completed','pending','rejected') NOT NULL DEFAULT 'completed',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_tx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;
