-- ==========================================
-- Turnover System Database Tables
-- ==========================================
-- Instructions:
-- 1. Create your database first or use existing one
-- 2. Select your database: USE your_database_name;
-- 3. Run this script to create required tables
-- ==========================================

-- Users table - stores user information and balance
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique user ID',
    tg_id BIGINT NOT NULL UNIQUE COMMENT 'Telegram user ID',
    balance DECIMAL(15,2) DEFAULT 0.00 COMMENT 'User current balance',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation time',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update time',
    INDEX idx_tg_id (tg_id)
) ENGINE=InnoDB COMMENT='User accounts and balances';

-- Deposits table - stores user deposits with turnover requirements
CREATE TABLE IF NOT EXISTS deposits (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique deposit ID',
    tg_id BIGINT NOT NULL COMMENT 'Telegram user ID',
    amount DECIMAL(15,2) NOT NULL COMMENT 'Deposit amount',
    required_turnover DECIMAL(15,2) NOT NULL COMMENT 'Required turnover (2x deposit)',
    remaining_turnover DECIMAL(15,2) NOT NULL COMMENT 'Remaining turnover to complete',
    is_unlocked TINYINT(1) DEFAULT 0 COMMENT '1 if turnover unlocked, 0 if locked',
    add_time DATETIME NOT NULL COMMENT 'Deposit time from client',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
    INDEX idx_tg_id (tg_id),
    INDEX idx_tg_id_remaining (tg_id, remaining_turnover),
    INDEX idx_add_time (add_time)
) ENGINE=InnoDB COMMENT='User deposits with turnover tracking';

-- Bets table - stores all user betting activity
CREATE TABLE IF NOT EXISTS bets (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique bet ID',
    tg_id BIGINT NOT NULL COMMENT 'Telegram user ID',
    bet_amount DECIMAL(15,2) NOT NULL COMMENT 'Amount of the bet',
    bet_time DATETIME NOT NULL COMMENT 'When the bet was placed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
    INDEX idx_tg_id (tg_id),
    INDEX idx_bet_time (bet_time)
) ENGINE=InnoDB COMMENT='User betting history';

-- Withdrawals table - stores withdrawal requests and history
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique withdrawal ID',
    tg_id BIGINT NOT NULL COMMENT 'Telegram user ID',
    amount DECIMAL(15,2) NOT NULL COMMENT 'Withdrawal amount',
    withdrawal_time DATETIME NOT NULL COMMENT 'When withdrawal was processed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
    INDEX idx_tg_id (tg_id),
    INDEX idx_withdrawal_time (withdrawal_time)
) ENGINE=InnoDB COMMENT='User withdrawal history';

-- ==========================================
-- Sample data for testing (optional)
-- ==========================================
-- Uncomment below lines if you want to insert test data

-- INSERT INTO users (tg_id, balance) VALUES (123456, 0.00);
-- INSERT INTO users (tg_id, balance) VALUES (789012, 0.00);

-- ==========================================
-- Verification queries (optional)
-- ==========================================
-- Run these queries to verify tables were created successfully:

-- SHOW TABLES;
-- DESCRIBE users;
-- DESCRIBE deposits;
-- DESCRIBE bets;
-- DESCRIBE withdrawals;

-- ==========================================
-- End of setup script
-- ==========================================
