-- ============================================================
-- HINDUSTAN CLUB — COMPLETE DATABASE (v2.0 Updated)
-- Database: preposts_data
-- Changes: 3-wallet system, UPI multi-mode, game icons, logo/favicon
-- ============================================================


CREATE TABLE `admin_activity_log` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `admin_id` INT UNSIGNED NOT NULL,
    `action` VARCHAR(100) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_admin` (`admin_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== 3-WALLET SYSTEM ====================
-- deposit_balance  = Real deposited money (used first for games, withdrawable)
-- earning_balance  = Won from games / referral earnings (withdrawable)
-- bonus_balance    = Promotional bonus (NOT directly withdrawable, used for games only)
-- Priority for game deduction: deposit_balance → earning_balance → bonus_balance

CREATE TABLE `wallets` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL UNIQUE,
    `deposit_balance` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Deposited real money — used first',
    `earning_balance` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Game winnings + referral earnings',
    `bonus_balance` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Promo bonus — cannot withdraw directly',
    `total_deposited` DECIMAL(15,2) DEFAULT 0.00,
    `total_withdrawn` DECIMAL(15,2) DEFAULT 0.00,
    `total_wagered` DECIMAL(15,2) DEFAULT 0.00,
    `wagering_required` DECIMAL(15,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `transactions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(50) NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `wallet_type` ENUM('deposit','earning','bonus') DEFAULT 'deposit',
    `balance_after` DECIMAL(15,2) DEFAULT NULL,
    `status` VARCHAR(20) DEFAULT 'completed',
    `description` VARCHAR(255) DEFAULT NULL,
    `reference_id` VARCHAR(100) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_type` (`user_id`, `type`),
    INDEX `idx_type_date` (`type`, `created_at`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `notifications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `message` TEXT NOT NULL,
    `type` VARCHAR(50) DEFAULT 'system',
    `link` VARCHAR(255) DEFAULT '',
    `is_read` TINYINT UNSIGNED DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_read` (`user_id`, `is_read`),
    INDEX `idx_type` (`type`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_bank_details` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `type` ENUM('upi','bank') NOT NULL,
    `upi_id` VARCHAR(100) DEFAULT NULL,
    `bank_name` VARCHAR(100) DEFAULT NULL,
    `account_number` VARCHAR(20) DEFAULT NULL,
    `ifsc_code` VARCHAR(11) DEFAULT NULL,
    `account_holder` VARCHAR(100) DEFAULT NULL,
    `is_primary` TINYINT UNSIGNED DEFAULT 0,
    `is_verified` TINYINT UNSIGNED DEFAULT 0,
    `locked_until` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== REFERRALS ====================

CREATE TABLE `referrals` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `referrer_id` INT UNSIGNED NOT NULL,
    `referred_id` INT UNSIGNED NOT NULL,
    `level` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `registration_bonus_paid` TINYINT UNSIGNED DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_ref` (`referrer_id`, `referred_id`),
    INDEX `idx_referrer_level` (`referrer_id`, `level`),
    INDEX `idx_referred` (`referred_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `referral_earnings` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `from_user_id` INT UNSIGNED NOT NULL,
    `level` TINYINT UNSIGNED NOT NULL,
    `bet_amount` DECIMAL(12,2) NOT NULL,
    `commission_rate` DECIMAL(5,2) DEFAULT 0.00,
    `commission_amount` DECIMAL(12,2) NOT NULL,
    `game_type` VARCHAR(50) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_from` (`from_user_id`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== VIP ====================

CREATE TABLE `vip_levels` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `level` TINYINT UNSIGNED NOT NULL UNIQUE,
    `name` VARCHAR(50) NOT NULL,
    `color` VARCHAR(20) DEFAULT '#888888',
    `min_deposit` DECIMAL(12,2) DEFAULT 0.00,
    `withdrawal_limit` DECIMAL(12,2) DEFAULT 10000.00,
    `daily_bonus` DECIMAL(10,2) DEFAULT 0.00,
    `withdrawal_speed` VARCHAR(20) DEFAULT 'normal',
    `badge_icon` VARCHAR(50) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `vip_levels` (`level`,`name`,`color`,`min_deposit`,`withdrawal_limit`,`daily_bonus`,`withdrawal_speed`) VALUES
(0,'Member','#888888',0,10000,0,'normal'),
(1,'Bronze','#CD7F32',1000,25000,5,'normal'),
(2,'Silver','#C0C0C0',5000,50000,10,'fast'),
(3,'Gold','#FFD700',25000,100000,25,'fast'),
(4,'Platinum','#E5E4E2',100000,200000,50,'priority'),
(5,'Diamond','#B9F2FF',500000,500000,100,'instant');

-- ==================== PAYMENTS (UPI - Multi Mode) ====================

CREATE TABLE `admin_upi_settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `upi_id` VARCHAR(100) NOT NULL,
    `display_name` VARCHAR(100) DEFAULT 'Hindustan Club',
    `qr_code_image` VARCHAR(255) DEFAULT NULL,
    `is_active` TINYINT UNSIGNED DEFAULT 1,
    `collection_mode` ENUM('single','multi') DEFAULT 'single' COMMENT 'single=only this UPI shown, multi=rotated',
    `priority` TINYINT UNSIGNED DEFAULT 0 COMMENT 'Higher = shown first',
    `daily_limit` DECIMAL(12,2) DEFAULT 500000.00,
    `today_received` DECIMAL(12,2) DEFAULT 0.00,
    `min_amount` DECIMAL(10,2) DEFAULT 100.00,
    `max_amount` DECIMAL(10,2) DEFAULT 50000.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_active` (`is_active`),
    INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Global UPI collection mode setting
INSERT INTO `admin_upi_settings` (`upi_id`,`display_name`,`is_active`,`collection_mode`,`priority`,`daily_limit`) VALUES
('merchant@upi','Hindustan Club Main',1,'multi',10,500000.00);

CREATE TABLE `deposits` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `upi_id_used` VARCHAR(100) DEFAULT NULL,
    `utr_number` VARCHAR(30) NOT NULL,
    `screenshot` VARCHAR(255) DEFAULT NULL,
    `bonus_amount` DECIMAL(12,2) DEFAULT 0.00,
    `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
    `approved_by` INT UNSIGNED DEFAULT NULL,
    `approved_at` DATETIME DEFAULT NULL,
    `rejection_reason` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_utr` (`utr_number`),
    INDEX `idx_user_status` (`user_id`, `status`),
    INDEX `idx_status_date` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `withdrawals` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `method` ENUM('upi','bank') DEFAULT 'upi',
    `bank_detail_id` INT UNSIGNED DEFAULT NULL,
    `payout_ref` VARCHAR(100) DEFAULT NULL,
    `status` ENUM('pending','processing','completed','rejected','cancelled') DEFAULT 'pending',
    `processed_by` INT UNSIGNED DEFAULT NULL,
    `processed_at` DATETIME DEFAULT NULL,
    `rejection_reason` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_status` (`user_id`, `status`),
    INDEX `idx_status_date` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== WINGO GAME ====================

CREATE TABLE `wingo_periods` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `period_number` VARCHAR(30) NOT NULL UNIQUE,
    `game_duration` TINYINT UNSIGNED NOT NULL,
    `start_time` DATETIME NOT NULL,
    `end_time` DATETIME NOT NULL,
    `status` ENUM('betting','closed','resulted') DEFAULT 'betting',
    `result_number` TINYINT UNSIGNED DEFAULT NULL,
    `result_color` VARCHAR(10) DEFAULT NULL,
    `result_size` VARCHAR(5) DEFAULT NULL,
    `total_bets` DECIMAL(15,2) DEFAULT 0.00,
    `total_payout` DECIMAL(15,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_duration_status` (`game_duration`, `status`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `wingo_bets` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `period_id` BIGINT UNSIGNED NOT NULL,
    `period_number` VARCHAR(30) NOT NULL,
    `game_duration` TINYINT UNSIGNED NOT NULL,
    `bet_type` ENUM('color','number','size') NOT NULL,
    `bet_value` VARCHAR(20) NOT NULL,
    `bet_amount` DECIMAL(12,2) NOT NULL,
    `multiplier` TINYINT UNSIGNED DEFAULT 1,
    `total_amount` DECIMAL(12,2) NOT NULL,
    `potential_win` DECIMAL(12,2) DEFAULT 0.00,
    `actual_win` DECIMAL(12,2) DEFAULT 0.00,
    `status` ENUM('pending','won','lost','refunded') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_period` (`user_id`, `period_id`),
    INDEX `idx_period` (`period_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `wingo_manual_results` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `game_duration` TINYINT UNSIGNED NOT NULL,
    `period_number` VARCHAR(30) NOT NULL,
    `result_number` TINYINT UNSIGNED NOT NULL,
    `is_used` TINYINT UNSIGNED DEFAULT 0,
    `created_by` INT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_period_duration` (`period_number`, `game_duration`),
    INDEX `idx_used` (`is_used`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== MINE GAME ====================

CREATE TABLE `mine_games` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `bet_amount` DECIMAL(12,2) NOT NULL,
    `mines_count` TINYINT UNSIGNED NOT NULL,
    `mine_positions` JSON NOT NULL,
    `revealed_tiles` JSON DEFAULT NULL,
    `is_rigged` TINYINT UNSIGNED DEFAULT 0,
    `rigged_safe_tiles` JSON DEFAULT NULL,
    `current_multiplier` DECIMAL(8,4) DEFAULT 1.0000,
    `status` ENUM('active','won','lost','cashed_out') DEFAULT 'active',
    `payout_amount` DECIMAL(12,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ended_at` DATETIME DEFAULT NULL,
    INDEX `idx_user_status` (`user_id`, `status`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== SPIN GAME ====================

CREATE TABLE `spin_config` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `segment_label` VARCHAR(50) NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `probability` DECIMAL(5,4) NOT NULL,
    `color` VARCHAR(20) DEFAULT '#FFD700',
    `is_active` TINYINT UNSIGNED DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `spin_config` (`segment_label`,`amount`,`probability`,`color`) VALUES
('₹5',5,0.3000,'#FFD700'),('₹10',10,0.2500,'#FFA000'),('₹20',20,0.1500,'#FF6B35'),
('₹50',50,0.1000,'#e74c3c'),('₹100',100,0.0700,'#9b59b6'),('₹200',200,0.0500,'#3498db'),
('₹500',500,0.0200,'#2ecc71'),('₹1000',1000,0.0100,'#1abc9c');

CREATE TABLE `spin_history` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `spin_type` ENUM('free','paid') DEFAULT 'free',
    `cost` DECIMAL(10,2) DEFAULT 0.00,
    `segment_id` INT UNSIGNED DEFAULT NULL,
    `win_amount` DECIMAL(10,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== GAMES CONFIG (with custom icon support) ====================

CREATE TABLE `games_config` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `game_key` VARCHAR(50) NOT NULL UNIQUE,
    `game_name` VARCHAR(100) NOT NULL,
    `min_bet` DECIMAL(10,2) DEFAULT 10.00,
    `max_bet` DECIMAL(10,2) DEFAULT 50000.00,
    `is_active` TINYINT UNSIGNED DEFAULT 1,
    `sort_order` INT DEFAULT 0,
    `description` TEXT DEFAULT NULL,
    `icon` VARCHAR(100) DEFAULT NULL COMMENT 'FontAwesome class OR emoji OR image URL',
    `icon_type` ENUM('fa','emoji','image') DEFAULT 'fa' COMMENT 'Type of icon',
    `icon_bg_color` VARCHAR(20) DEFAULT '#FFD700' COMMENT 'Icon background color',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `games_config` (`game_key`,`game_name`,`min_bet`,`max_bet`,`is_active`,`sort_order`,`icon`,`icon_type`,`icon_bg_color`) VALUES
('wingo','Wingo',10.00,50000.00,1,1,'fas fa-bullseye','fa','#e74c3c'),
('mine','Mine Game',10.00,50000.00,1,2,'fas fa-bomb','fa','#f39c12'),
('spin','Spin & Win',10.00,10000.00,1,3,'fas fa-dharmachakra','fa','#9b59b6');

-- ==================== ALGORITHM ====================

CREATE TABLE `user_algorithm_data` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL UNIQUE,
    `current_phase` TINYINT UNSIGNED DEFAULT 1,
    `phase_start_date` DATE DEFAULT NULL,
    `total_bets` INT UNSIGNED DEFAULT 0,
    `total_wins` INT UNSIGNED DEFAULT 0,
    `total_losses` INT UNSIGNED DEFAULT 0,
    `consecutive_wins` INT UNSIGNED DEFAULT 0,
    `consecutive_losses` INT UNSIGNED DEFAULT 0,
    `last_bet_at` DATETIME DEFAULT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== GIFT CODES ====================

CREATE TABLE `gift_codes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(30) NOT NULL UNIQUE,
    `bonus_amount` DECIMAL(10,2) NOT NULL,
    `max_uses` INT UNSIGNED DEFAULT 0 COMMENT '0 = unlimited',
    `used_count` INT UNSIGNED DEFAULT 0,
    `max_per_user` TINYINT UNSIGNED DEFAULT 1,
    `min_deposit` DECIMAL(10,2) DEFAULT 0.00,
    `wagering_multiplier` DECIMAL(5,2) DEFAULT 5.00,
    `expires_at` DATETIME DEFAULT NULL,
    `is_active` TINYINT UNSIGNED DEFAULT 1,
    `created_by` INT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_code` (`code`),
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `gift_code_uses` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `gift_code_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `bonus_amount` DECIMAL(10,2) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_code_user` (`gift_code_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==================== CHECK-IN ====================

CREATE TABLE `daily_checkins` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `checkin_date` DATE NOT NULL,
    `streak` TINYINT UNSIGNED DEFAULT 1,
    `reward_amount` DECIMAL(10,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_user_date` (`user_id`, `checkin_date`),
    INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

