const fs = require('fs');
const path = require('path');

module.exports = async function runMigrations(db) {
    // Promisify db.run and db.get for easier async/await usage
    function run(sql, params = []) {
        return new Promise((resolve, reject) => {
            db.run(sql, params, function (err) {
                if (err) reject(err);
                else resolve(this);
            });
        });
    }
    function get(sql, params = []) {
        return new Promise((resolve, reject) => {
            db.get(sql, params, function (err, row) {
                if (err) reject(err);
                else resolve(row);
            });
        });
    }
    function all(sql, params = []) {
        return new Promise((resolve, reject) => {
            db.all(sql, params, function (err, rows) {
                if (err) reject(err);
                else resolve(rows);
            });
        });
    }

    // 1. Ensure migrations table exists
    await run(`
        CREATE TABLE IF NOT EXISTS migrations (
            name TEXT PRIMARY KEY,
            run_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `);

    // 2. Read all migration files
    const migrationsDir = path.join(__dirname, 'migrations');
    let files = [];
    try {
        files = fs.readdirSync(migrationsDir)
            .filter(f => f.endsWith('.js'))
            .sort();
    } catch (e) {
        // If directory doesn't exist, skip
        if (e.code !== 'ENOENT') throw e;
    }

    // 3. Get already run migrations
    const appliedRows = await all('SELECT name FROM migrations');
    const applied = new Set(appliedRows.map(row => row.name));

    // 4. Run pending migrations
    for (const file of files) {
        if (!applied.has(file)) {
            const migration = require(path.join(migrationsDir, file));
            if (typeof migration !== 'function') {
                throw new Error(`Migration file ${file} does not export a function`);
            }
            console.log(`Running migration: ${file}`);
            await migration(db, run, get, all);
            // Mark migration as applied
            await run('INSERT INTO migrations (name) VALUES (?)', [file]);
            console.log(`Migration applied: ${file}`);
        }
    }
};