couteau_suisse/database.js

const mysql = require('mysql2/promise');

let pool;

const initDatabase = async () => {
    try {
        pool = mysql.createPool({
            host: process.env.DB_HOST,
            port: process.env.DB_PORT,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_NAME,
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });

        console.log('✅ Base de données connectée');
        
        await createTables();

        console.log('✅ Tables créées/vérifiées');
    } catch (error) {
        console.error('❌ Erreur de connexion à la base de données:', error);
        throw error;
    }
};

const getConnection = () => {
    if (!pool) {
        throw new Error('La base de données n\'est pas initialisée');
    }
    return pool;
};

const createTables = async () => {
    const connection = getConnection();
    
    // Table pour les codes de liaison temporaires
    await connection.execute(`
        CREATE TABLE IF NOT EXISTS link_codes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            discord_id VARCHAR(20) NOT NULL,
            code VARCHAR(6) NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP NOT NULL,
            used BOOLEAN DEFAULT FALSE,
            INDEX idx_code (code),
            INDEX idx_discord_id (discord_id)
        )
    `);

    // Table pour les liaisons confirmées
    await connection.execute(`
        CREATE TABLE IF NOT EXISTS user_links (
            id INT AUTO_INCREMENT PRIMARY KEY,
            discord_id VARCHAR(20) NOT NULL UNIQUE,
            discord_username VARCHAR(100) NOT NULL,
            steam_id VARCHAR(50) NOT NULL,
            player_id VARCHAR(50),
            palworld_username VARCHAR(100) NOT NULL,
            linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            lastConnection TIMESTAMP NULL DEFAULT NULL,
            INDEX idx_discord_id (discord_id),
            INDEX idx_steam_id (steam_id),
            INDEX idx_player_id (player_id)
        )
    `);
};

const generateLinkCode = async (discordId) => {
    const connection = getConnection();
    const code = Math.random().toString(36).substring(2, 8).toUpperCase();
    const expiresAt = new Date(Date.now() + 10 * 60 * 1000); // 10 minutes

    await connection.execute(
        'INSERT INTO link_codes (discord_id, code, expires_at) VALUES (?, ?, ?)',
        [discordId, code, expiresAt]
    );

    return code;
};

const verifyLinkCode = async (code, steamId, palworldUsername, playerId = null) => {
    const connection = getConnection();
    
    const [rows] = await connection.execute(
        'SELECT * FROM link_codes WHERE code = ? AND used = FALSE AND expires_at > NOW()',
        [code]
    );

    if (rows.length === 0) {
        return { success: false, message: 'Code invalide ou expiré' };
    }

    const linkData = rows[0];

    // Créer la liaison avec player_id
    await connection.execute(
        'INSERT INTO user_links (discord_id, discord_username, steam_id, player_id, palworld_username) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE steam_id = ?, player_id = ?, palworld_username = ?',
        [linkData.discord_id, 'temp', steamId, playerId, palworldUsername, steamId, playerId, palworldUsername]
    );

    // Marquer le code comme utilisé
    await connection.execute(
        'UPDATE link_codes SET used = TRUE WHERE code = ?',
        [code]
    );

    return { success: true, discordId: linkData.discord_id };
};

const getAllLinks = async () => {
    const connection = getConnection();
    const [rows] = await connection.execute(
        'SELECT discord_id, discord_username, steam_id, player_id, palworld_username, lastConnection, linked_at FROM user_links ORDER BY discord_username'
    );
    return rows;
};

const getUserLink = async (discordId) => {
    const connection = getConnection();
    const [rows] = await connection.execute(
        'SELECT * FROM user_links WHERE discord_id = ?',
        [discordId]
    );
    return rows.length > 0 ? rows[0] : null;
};

const updateUserLinkWithUsername = async (discordId, discordUsername) => {
    const connection = getConnection();
    await connection.execute(
        'UPDATE user_links SET discord_username = ? WHERE discord_id = ?',
        [discordUsername, discordId]
    );
};

const deleteUserLink = async (discordId) => {
    const connection = getConnection();
    await connection.execute(
        'DELETE FROM user_links WHERE discord_id = ?',
        [discordId]
    );
};

const getPendingPlayer = async (steamId) => {
    const connection = getConnection();
    const [rows] = await connection.execute(
        'SELECT * FROM pending_players WHERE steam_id = ?',
        [steamId]
    );
    return rows.length > 0 ? rows[0] : null;
};

const hasActiveLinkCodes = async () => {
    const connection = getConnection();
    const [rows] = await connection.execute(
        'SELECT COUNT(*) as count FROM link_codes WHERE used = FALSE AND expires_at > NOW()'
    );
    return rows[0].count > 0;
};

const cleanExpiredCodes = async () => {
    const connection = getConnection();
    await connection.execute(
        'DELETE FROM link_codes WHERE expires_at < NOW()'
    );
};

const updateLastConnection = async (steamId) => {
    const connection = getConnection();
    const [result] = await connection.execute(
        'UPDATE user_links SET lastConnection = NOW() WHERE steam_id = ?',
        [steamId]
    );
    
    return { success: true, changes: result.affectedRows };
};

module.exports = { 
    initDatabase, 
    getConnection, 
    createTables,
    generateLinkCode,
    verifyLinkCode,
    getAllLinks,
    getUserLink,
    updateUserLinkWithUsername,
    deleteUserLink,
    getPendingPlayer,
    hasActiveLinkCodes,
    cleanExpiredCodes,
    updateLastConnection
};