-- Sistema de Partidas em Tempo Real - Aviator Multiplayer
-- Execute este SQL no seu banco de dados

-- Tabela de rodadas do jogo
CREATE TABLE IF NOT EXISTS game_rounds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    round_number INT NOT NULL,
    target_multiplier DECIMAL(10,2) NOT NULL,
    actual_crash_multiplier DECIMAL(10,2) DEFAULT NULL,
    status ENUM('waiting', 'betting', 'flying', 'crashed') NOT NULL DEFAULT 'waiting',
    start_time TIMESTAMP NULL,
    crash_time TIMESTAMP NULL,
    duration_seconds DECIMAL(10,3) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_round_number (round_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela de apostas ativas na rodada
CREATE TABLE IF NOT EXISTS round_bets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    round_id INT NOT NULL,
    user_id INT NOT NULL,
    bet_amount DECIMAL(10,2) NOT NULL,
    cashout_multiplier DECIMAL(10,2) DEFAULT NULL,
    payout DECIMAL(10,2) DEFAULT NULL,
    status ENUM('active', 'cashed_out', 'lost') NOT NULL DEFAULT 'active',
    bet_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    cashout_time TIMESTAMP NULL,
    FOREIGN KEY (round_id) REFERENCES game_rounds(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_round_user (round_id, user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela de estado atual do jogo (singleton - sempre 1 registro)
CREATE TABLE IF NOT EXISTS game_state (
    id INT PRIMARY KEY DEFAULT 1,
    current_round_id INT DEFAULT NULL,
    phase ENUM('waiting', 'betting', 'flying', 'crashed') NOT NULL DEFAULT 'waiting',
    current_multiplier DECIMAL(10,2) DEFAULT 1.00,
    phase_start_time TIMESTAMP NULL,
    phase_end_time TIMESTAMP NULL,
    target_multiplier DECIMAL(10,2) DEFAULT NULL,
    total_players_betting INT DEFAULT 0,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (current_round_id) REFERENCES game_rounds(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inserir estado inicial
INSERT INTO game_state (id, phase) VALUES (1, 'waiting') 
ON DUPLICATE KEY UPDATE phase = 'waiting';

-- Tabela de cashouts visíveis (para animações)
CREATE TABLE IF NOT EXISTS live_cashouts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    round_id INT NOT NULL,
    user_id INT NOT NULL,
    user_name VARCHAR(100) NOT NULL,
    cashout_multiplier DECIMAL(10,2) NOT NULL,
    payout DECIMAL(10,2) NOT NULL,
    position_x INT DEFAULT NULL,
    position_y INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL 5 SECOND),
    FOREIGN KEY (round_id) REFERENCES game_rounds(id) ON DELETE CASCADE,
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Limpar cashouts expirados automaticamente (event scheduler)
SET GLOBAL event_scheduler = ON;

DELIMITER //
CREATE EVENT IF NOT EXISTS cleanup_live_cashouts
ON SCHEDULE EVERY 10 SECOND
DO
BEGIN
    DELETE FROM live_cashouts WHERE expires_at < NOW();
END //
DELIMITER ;

-- View para estatísticas da rodada atual
CREATE OR REPLACE VIEW current_round_stats AS
SELECT 
    gr.id as round_id,
    gr.round_number,
    gr.status,
    gr.target_multiplier,
    COUNT(rb.id) as total_bets,
    SUM(rb.bet_amount) as total_wagered,
    SUM(CASE WHEN rb.status = 'active' THEN 1 ELSE 0 END) as active_bets,
    SUM(CASE WHEN rb.status = 'cashed_out' THEN 1 ELSE 0 END) as cashed_out_count
FROM game_rounds gr
LEFT JOIN round_bets rb ON gr.id = rb.round_id
WHERE gr.status IN ('betting', 'flying')
GROUP BY gr.id, gr.round_number, gr.status, gr.target_multiplier;

-- Índices adicionais para performance
ALTER TABLE game_history ADD INDEX idx_created_at (created_at DESC);
ALTER TABLE users ADD INDEX idx_balance (balance);

-- Procedure para processar crash e resolver apostas
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS process_round_crash(
    IN p_round_id INT,
    IN p_crash_multiplier DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Atualizar rodada como crashed
    UPDATE game_rounds 
    SET status = 'crashed',
        actual_crash_multiplier = p_crash_multiplier,
        crash_time = NOW()
    WHERE id = p_round_id;
    
    -- Processar apostas que não deram cashout (perderam)
    UPDATE round_bets 
    SET status = 'lost'
    WHERE round_id = p_round_id 
    AND status = 'active';
    
    -- Registrar no histórico global
    INSERT INTO game_history (user_id, bet_amount, multiplier, win_amount, result, created_at)
    SELECT 
        user_id,
        bet_amount,
        p_crash_multiplier,
        0 as win_amount,
        'loss' as result,
        NOW()
    FROM round_bets
    WHERE round_id = p_round_id
    AND status = 'lost';
    
    -- Registrar cashouts bem-sucedidos no histórico
    INSERT INTO game_history (user_id, bet_amount, multiplier, win_amount, result, created_at)
    SELECT 
        user_id,
        bet_amount,
        cashout_multiplier,
        payout as win_amount,
        'win' as result,
        cashout_time
    FROM round_bets
    WHERE round_id = p_round_id
    AND status = 'cashed_out';
    
    COMMIT;
END //
DELIMITER ;

-- Garantir que existe pelo menos uma rodada inicial
INSERT INTO game_rounds (round_number, target_multiplier, status) 
VALUES (1, 1.00, 'waiting')
ON DUPLICATE KEY UPDATE status = status;

-- Mensagem de sucesso
SELECT 'Sistema Multiplayer instalado com sucesso!' as message;
SELECT COUNT(*) as total_rounds FROM game_rounds;
SELECT * FROM game_state;
