monquigrid/database/init.js

const sqlite3 = require('sqlite3').verbose();
const fs = require('fs');
const path = require('path');

class DatabaseManager {
    constructor(dbPath = './database/gridbattle.db') {
        this.dbPath = dbPath;
        this.db = null;
    }

    async initialize() {
        console.log('Initializing Grid Battle database...');
        
        try {
            // Ensure database directory exists
            const dbDir = path.dirname(this.dbPath);
            if (!fs.existsSync(dbDir)) {
                fs.mkdirSync(dbDir, { recursive: true });
            }

            // Open database connection
            this.db = new sqlite3.Database(this.dbPath, (err) => {
                if (err) {
                    console.error('Error opening database:', err.message);
                    throw err;
                }
                console.log('Connected to SQLite database:', this.dbPath);
            });

            // Enable foreign keys
            await this.runQuery('PRAGMA foreign_keys = ON');
            
            // Load and execute schema
            const schemaPath = path.join(__dirname, 'schema.sql');
            const schema = fs.readFileSync(schemaPath, 'utf8');
            
            // Split schema into individual statements
            const statements = schema.split(';').filter(stmt => stmt.trim().length > 0);
            
            for (const statement of statements) {
                await this.runQuery(statement);
            }

            console.log('Database schema initialized successfully');
            
            // Insert default data if needed
            await this.insertDefaultData();
            
            return this.db;
        } catch (error) {
            console.error('Database initialization failed:', error);
            throw error;
        }
    }

    async insertDefaultData() {
        // Check if we need to insert any default data
        const playerCount = await this.getScalar('SELECT COUNT(*) FROM players');
        
        if (playerCount === 0) {
            console.log('Inserting default test data...');
            
            // Insert test players for development
            await this.runQuery(`
                INSERT INTO players (id, username, games_played, games_won, skill_rating)
                VALUES 
                    ('test-player-1', 'TestPlayer1', 5, 3, 1150),
                    ('test-player-2', 'TestPlayer2', 4, 1, 950),
                    ('test-player-3', 'GridMaster', 10, 8, 1300)
            `);
            
            console.log('Default test data inserted');
        }
    }

    // Promisified database operations
    runQuery(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.run(sql, params, function(err) {
                if (err) {
                    console.error('Database query error:', err.message);
                    console.error('SQL:', sql);
                    reject(err);
                } else {
                    resolve({ lastID: this.lastID, changes: this.changes });
                }
            });
        });
    }

    getRow(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.get(sql, params, (err, row) => {
                if (err) {
                    console.error('Database query error:', err.message);
                    reject(err);
                } else {
                    resolve(row);
                }
            });
        });
    }

    getAllRows(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('Database query error:', err.message);
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    getScalar(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.get(sql, params, (err, row) => {
                if (err) {
                    console.error('Database query error:', err.message);
                    reject(err);
                } else {
                    // Return the first column value
                    const value = row ? Object.values(row)[0] : null;
                    resolve(value);
                }
            });
        });
    }

    async close() {
        return new Promise((resolve, reject) => {
            if (this.db) {
                this.db.close((err) => {
                    if (err) {
                        reject(err);
                    } else {
                        console.log('Database connection closed');
                        resolve();
                    }
                });
            } else {
                resolve();
            }
        });
    }

    // Helper methods for common operations
    async createPlayer(username, email = null) {
        const { v4: uuidv4 } = require('uuid');
        const playerId = uuidv4();
        
        await this.runQuery(`
            INSERT INTO players (id, username, email)
            VALUES (?, ?, ?)
        `, [playerId, username, email]);
        
        return playerId;
    }

    async createGame(gameMode = 'standard', tournamentId = null) {
        const { v4: uuidv4 } = require('uuid');
        const gameId = uuidv4();
        
        await this.runQuery(`
            INSERT INTO games (id, game_mode, tournament_id)
            VALUES (?, ?, ?)
        `, [gameId, gameMode, tournamentId]);
        
        return gameId;
    }

    async addPlayerToGame(gameId, playerId, playerNumber, spawnX, spawnY) {
        await this.runQuery(`
            INSERT INTO game_players (game_id, player_id, player_number, spawn_x, spawn_y, current_x, current_y)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        `, [gameId, playerId, playerNumber, spawnX, spawnY, spawnX, spawnY]);
    }

    async logGameEvent(gameId, turnNumber, sequenceNumber, eventType, playerId, eventData = {}) {
        await this.runQuery(`
            INSERT INTO game_events (game_id, turn_number, sequence_number, event_type, player_id, 
                                   from_x, from_y, to_x, to_y, event_data)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `, [
            gameId, turnNumber, sequenceNumber, eventType, playerId,
            eventData.from_x || null, eventData.from_y || null,
            eventData.to_x || null, eventData.to_y || null,
            JSON.stringify(eventData)
        ]);
    }

    async getGameState(gameId) {
        const game = await this.getRow('SELECT * FROM games WHERE id = ?', [gameId]);
        if (!game) return null;

        const players = await this.getAllRows(`
            SELECT gp.*, p.username 
            FROM game_players gp 
            JOIN players p ON gp.player_id = p.id 
            WHERE gp.game_id = ?
            ORDER BY gp.player_number
        `, [gameId]);

        return { game, players };
    }

    async getLeaderboard(limit = 10) {
        return await this.getAllRows(`
            SELECT * FROM leaderboard LIMIT ?
        `, [limit]);
    }

    async getGameHistory(gameId) {
        return await this.getAllRows(`
            SELECT * FROM game_events 
            WHERE game_id = ? 
            ORDER BY turn_number, sequence_number
        `, [gameId]);
    }
}

module.exports = DatabaseManager;