/**
 * Database Migration Module
 * Handles schema updates for device tracking
 */

function migrateDeviceColumns(db) {
    try {
        // Check if columns already exist in sessions table
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const hasDeviceType = sessionsInfo.some(col => col.name === 'device_type');

        if (!hasDeviceType) {
            console.log('📊 Adding device tracking columns to sessions table...');
            
            db.exec(`
                ALTER TABLE sessions ADD COLUMN device_type TEXT;
            `);
            db.exec(`
                ALTER TABLE sessions ADD COLUMN os TEXT;
            `);
            db.exec(`
                ALTER TABLE sessions ADD COLUMN browser TEXT;
            `);

            console.log('✅ Sessions table updated with device tracking columns');
        }

        // Check if columns already exist in page_views table
        const pageViewsInfo = db.prepare('PRAGMA table_info(page_views)').all();
        const hasDeviceTypeInPageViews = pageViewsInfo.some(col => col.name === 'device_type');

        if (!hasDeviceTypeInPageViews) {
            console.log('📊 Adding device tracking columns to page_views table...');
            
            db.exec(`
                ALTER TABLE page_views ADD COLUMN device_type TEXT;
            `);
            db.exec(`
                ALTER TABLE page_views ADD COLUMN os TEXT;
            `);
            db.exec(`
                ALTER TABLE page_views ADD COLUMN browser TEXT;
            `);

            console.log('✅ Page_views table updated with device tracking columns');
        }

        if (!hasDeviceType || !hasDeviceTypeInPageViews) {
            console.log('✅ Device tracking migration completed successfully');
        }
    } catch (error) {
        console.error('❌ Migration error:', error);
    }
}

function migrateGameMaintenanceColumns(db) {
    try {
        // Check if columns already exist in games table
        const gamesInfo = db.prepare('PRAGMA table_info(games)').all();
        const hasMaintenanceMode = gamesInfo.some(col => col.name === 'maintenance_mode');

        if (!hasMaintenanceMode) {
            console.log('📊 Adding maintenance columns to games table...');
            
            db.exec(`
                ALTER TABLE games ADD COLUMN maintenance_mode INTEGER DEFAULT 0;
            `);
            db.exec(`
                ALTER TABLE games ADD COLUMN maintenance_message TEXT DEFAULT '';
            `);
            db.exec(`
                ALTER TABLE games ADD COLUMN maintenance_eta TEXT DEFAULT '';
            `);

            console.log('✅ Games table updated with maintenance columns');
            console.log('✅ Game maintenance migration completed successfully');
        }
    } catch (error) {
        console.error('❌ Error in game maintenance migration:', error);
        throw error;
    }
}

/**
 * Add supports_rooms column to games table
 */
function migrateSupportsRoomsColumn(db) {
    try {
        // Check if column already exists
        const gamesInfo = db.prepare('PRAGMA table_info(games)').all();
        const hasSupportsRooms = gamesInfo.some(col => col.name === 'supports_rooms');

        if (!hasSupportsRooms) {
            console.log('📊 Adding supports_rooms column to games table...');
            
            db.exec(`
                ALTER TABLE games ADD COLUMN supports_rooms INTEGER DEFAULT 0;
            `);

            // Update existing multiplayer games
            db.exec(`
                UPDATE games SET supports_rooms = 1 WHERE id IN ('chess', 'bagh-chal', 'ludo');
            `);

            console.log('✅ Games table updated with supports_rooms column');
        }
    } catch (error) {
        console.error('❌ Error in supports_rooms migration:', error);
        throw error;
    }
}

/**
 * Add is_coming_soon column to games table
 */
function migrateComingSoonColumn(db) {
    try {
        // Check if column already exists
        const gamesInfo = db.prepare('PRAGMA table_info(games)').all();
        const hasComingSoon = gamesInfo.some(col => col.name === 'is_coming_soon');

        if (!hasComingSoon) {
            console.log('📊 Adding is_coming_soon column to games table...');
            
            db.exec(`
                ALTER TABLE games ADD COLUMN is_coming_soon INTEGER DEFAULT 0;
            `);

            console.log('✅ Games table updated with is_coming_soon column');
        }
    } catch (error) {
        console.error('❌ Error in is_coming_soon migration:', error);
        throw error;
    }
}

/**
 * Add player_mode column to games table
 */
function migratePlayerModeColumn(db) {
    try {
        // Check if column already exists
        const gamesInfo = db.prepare('PRAGMA table_info(games)').all();
        const hasPlayerMode = gamesInfo.some(col => col.name === 'player_mode');

        if (!hasPlayerMode) {
            console.log('📊 Adding player_mode column to games table...');
            
            db.exec(`
                ALTER TABLE games ADD COLUMN player_mode TEXT DEFAULT 'Single Player';
            `);

            // Update existing multiplayer games
            db.exec(`
                UPDATE games SET player_mode = 'Multiplayer' WHERE id IN ('chess', 'bagh-chal', 'ludo');
            `);

            console.log('✅ Games table updated with player_mode column');
        }
    } catch (error) {
        console.error('❌ Error in player_mode migration:', error);
        throw error;
    }
}

/**
 * Add difficulty and result columns to sessions table
 */
function migrateGameStatsColumns(db) {
    try {
        // Check if columns already exist
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const hasDifficulty = sessionsInfo.some(col => col.name === 'difficulty');
        const hasResult = sessionsInfo.some(col => col.name === 'result');

        if (!hasDifficulty) {
            console.log('📊 Adding difficulty column to sessions table...');
            db.exec(`ALTER TABLE sessions ADD COLUMN difficulty TEXT;`);
            console.log('✅ Sessions table updated with difficulty column');
        }

        if (!hasResult) {
            console.log('📊 Adding result column to sessions table...');
            db.exec(`ALTER TABLE sessions ADD COLUMN result TEXT;`);
            console.log('✅ Sessions table updated with result column');
        }

        if (!hasDifficulty || !hasResult) {
            console.log('✅ Game stats migration completed successfully');
        }
    } catch (error) {
        console.error('❌ Error in game stats migration:', error);
        throw error;
    }
}

/**
 * Add match analytics columns to sessions table
 */
function migrateMatchAnalyticsColumns(db) {
    try {
        // Check if columns already exist
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const hasMatchId = sessionsInfo.some(col => col.name === 'match_id');
        const hasPlayerRole = sessionsInfo.some(col => col.name === 'player_role');
        const hasOpponentId = sessionsInfo.some(col => col.name === 'opponent_id');

        if (!hasMatchId) {
            console.log('📊 Adding match_id column to sessions table...');
            db.exec(`ALTER TABLE sessions ADD COLUMN match_id TEXT;`);
            console.log('✅ Sessions table updated with match_id column');
        }

        if (!hasPlayerRole) {
            console.log('📊 Adding player_role column to sessions table...');
            db.exec(`ALTER TABLE sessions ADD COLUMN player_role TEXT;`);
            console.log('✅ Sessions table updated with player_role column');
        }

        if (!hasOpponentId) {
            console.log('📊 Adding opponent_id column to sessions table...');
            db.exec(`ALTER TABLE sessions ADD COLUMN opponent_id TEXT;`);
            console.log('✅ Sessions table updated with opponent_id column');
        }

        if (!hasMatchId || !hasPlayerRole || !hasOpponentId) {
            console.log('✅ Match analytics migration completed successfully');
        }
    } catch (error) {
        console.error('❌ Error in match analytics migration:', error);
        throw error;
    }
}

/**
 * Add metrics JSON column to sessions table
 */
function migrateToMetricsColumn(db) {
    try {
        // Check if column already exists
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const hasMetrics = sessionsInfo.some(col => col.name === 'metrics');

        if (!hasMetrics) {
            console.log('📊 Adding metrics column to sessions table...');
            db.exec(`ALTER TABLE sessions ADD COLUMN metrics TEXT;`);
            console.log('✅ Sessions table updated with metrics column');
            console.log('✅ Metrics migration completed successfully');
        }
    } catch (error) {
        console.error('❌ Error in metrics migration:', error);
        throw error;
    }
}

/**
 * Drop the unused difficulty column
 */
function dropDifficultyColumn(db) {
    try {
        // Check if column exists
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const hasDifficulty = sessionsInfo.some(col => col.name === 'difficulty');

        if (hasDifficulty) {
            console.log('🗑️  Removing unused difficulty column from sessions table...');
            
            // SQLite 3.35.0+ supports DROP COLUMN directly
            try {
                db.exec(`ALTER TABLE sessions DROP COLUMN difficulty;`);
                console.log('✅ Difficulty column dropped successfully');
            } catch (error) {
                // Older SQLite versions don't support DROP COLUMN
                // Need to recreate table without the column
                console.log('⚠️  SQLite version does not support DROP COLUMN, skipping...');
                console.log('   (Column will remain but is unused)');
            }
        }
    } catch (error) {
        console.error('❌ Error dropping difficulty column:', error);
        // Non-critical error, continue
    }
}

/**
 * Drop the result column (moved to metrics)
 */
function dropResultColumn(db) {
    try {
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const hasResult = sessionsInfo.some(col => col.name === 'result');

        if (hasResult) {
            console.log('🗑️  Removing result column (moved to metrics)...');
            
            try {
                db.exec(`ALTER TABLE sessions DROP COLUMN result;`);
                console.log('✅ Result column dropped successfully');
            } catch (error) {
                console.log('⚠️  SQLite version does not support DROP COLUMN, skipping...');
            }
        }
    } catch (error) {
        console.error('❌ Error dropping result column:', error);
    }
}

function dropOpponentIdColumn(db) {
    try {
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const hasOpponentId = sessionsInfo.some(col => col.name === 'opponent_id');

        if (hasOpponentId) {
            console.log('🗑️  Removing opponent_id column (use match_id for opponent tracking)...');
            
            try {
                db.exec(`ALTER TABLE sessions DROP COLUMN opponent_id;`);
                console.log('✅ opponent_id column dropped successfully');
            } catch (error) {
                console.log('⚠️  SQLite version does not support DROP COLUMN, skipping...');
            }
        }
    } catch (error) {
        console.error('❌ Error dropping opponent_id column:', error);
    }
}

function dropPlayerRoleColumn(db) {
    try {
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const hasPlayerRole = sessionsInfo.some(col => col.name === 'player_role');

        if (hasPlayerRole) {
            console.log('🗑️  Removing player_role column (moved to metrics)...');
            
            try {
                db.exec(`ALTER TABLE sessions DROP COLUMN player_role;`);
                console.log('✅ player_role column dropped successfully');
            } catch (error) {
                console.log('⚠️  SQLite version does not support DROP COLUMN, skipping...');
            }
        }
    } catch (error) {
        console.error('❌ Error dropping player_role column:', error);
    }
}

/**
 * Migrate match_id column to INTEGER type
 * SQLite requires table recreation for type changes
 */
function migrateMatchIdToInteger(db) {
    try {
        const sessionsInfo = db.prepare('PRAGMA table_info(sessions)').all();
        const matchIdCol = sessionsInfo.find(col => col.name === 'match_id');

        // Check if already INTEGER (or if we need to migrate)
        if (matchIdCol && matchIdCol.type !== 'INTEGER') {
            console.log('🔄 Migrating match_id column to INTEGER...');
            
            db.transaction(() => {
                // 1. Create new table with correct schema
                db.exec(`
                    CREATE TABLE IF NOT EXISTS sessions_new (
                        session_id TEXT PRIMARY KEY,
                        user_id TEXT NOT NULL,
                        game_name TEXT NOT NULL,
                        start_time TEXT NOT NULL,
                        end_time TEXT,
                        duration INTEGER,
                        device_type TEXT,
                        os TEXT,
                        browser TEXT,
                        match_id INTEGER,
                        metrics TEXT
                    );
                `);

                // 2. Copy data (casting match_id to INTEGER, ignoring non-numeric)
                // Note: User said previous string records are deleted/can be ignored
                db.exec(`
                    INSERT INTO sessions_new (
                        session_id, user_id, game_name, start_time, end_time, 
                        duration, device_type, os, browser, match_id, metrics
                    )
                    SELECT 
                        session_id, user_id, game_name, start_time, end_time, 
                        duration, device_type, os, browser, 
                        CAST(match_id AS INTEGER), 
                        metrics
                    FROM sessions;
                `);

                // 3. Drop old table
                db.exec('DROP TABLE sessions;');

                // 4. Rename new table
                db.exec('ALTER TABLE sessions_new RENAME TO sessions;');
                
                // 5. Recreate indexes
                db.exec('CREATE INDEX IF NOT EXISTS idx_sessions_game_name ON sessions(game_name);');
                db.exec('CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);');
                db.exec('CREATE INDEX IF NOT EXISTS idx_sessions_start_time ON sessions(start_time);');
                db.exec('CREATE INDEX IF NOT EXISTS idx_sessions_match_id ON sessions(match_id);');
            })();

            console.log('✅ match_id column migrated to INTEGER successfully');
        }
    } catch (error) {
        console.error('❌ Error migrating match_id to INTEGER:', error);
    }
}


module.exports = { 
    migrateDeviceColumns, 
    migrateGameMaintenanceColumns, 
    migrateSupportsRoomsColumn, 
    migrateComingSoonColumn, 
    migratePlayerModeColumn,
    migrateGameStatsColumns,
    migrateMatchAnalyticsColumns,
    migrateToMetricsColumn,
    dropDifficultyColumn,
    dropResultColumn,
    dropOpponentIdColumn,
    dropPlayerRoleColumn,
    migrateMatchIdToInteger
};
