import pool from './db.js';

export async function runStartupMigrations() {
    console.log('🔄 Running startup database migrations...');
    
    const alterCommands = [
        // Printing Options specific schema upgrades
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS setup_cost DECIMAL(10, 2) DEFAULT 0.00;",
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS click_charge DECIMAL(10, 4) DEFAULT 0.00;",
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS hourly_rate DECIMAL(10, 2) DEFAULT 0.00;",
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS min_width DECIMAL(10, 2);",
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS min_length DECIMAL(10, 2);",
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS max_width DECIMAL(10, 2);",
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS max_length DECIMAL(10, 2);",
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS setup_cost DECIMAL(10, 2) DEFAULT 0.00;", // Safe to repeat

        // Add universally safe Flexible params JSON column
        "ALTER TABLE paper_types ADD COLUMN IF NOT EXISTS params JSON;",
        "ALTER TABLE materials ADD COLUMN IF NOT EXISTS params JSON;",
        "ALTER TABLE finishing_options ADD COLUMN IF NOT EXISTS params JSON;",
        "ALTER TABLE plates ADD COLUMN IF NOT EXISTS params JSON;",
        "ALTER TABLE finishing_blocks ADD COLUMN IF NOT EXISTS params JSON;",
        "ALTER TABLE printing_options ADD COLUMN IF NOT EXISTS params JSON;"
    ];

    for (const cmd of alterCommands) {
        try {
            // Because MySQL 'IF NOT EXISTS' for columns is tricky on older MariaDB/MySQL versions,
            // we will catch and ignore duplicate column errors gracefully.
            await pool.execute(cmd);
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME' || error.message.includes('Duplicate column')) {
                // Ignore gracefully
            } else if (error.code === 'ER_PARSE_ERROR' && cmd.includes('IF NOT EXISTS')) {
                // Older MySQL/MariaDB doesn't support 'IF NOT EXISTS', so we strip it and try again
                try {
                    const fallbackCmd = cmd.replace('IF NOT EXISTS ', '');
                    await pool.execute(fallbackCmd);
                } catch (fallbackError) {
                    if (fallbackError.code !== 'ER_DUP_FIELDNAME' && !fallbackError.message.includes('Duplicate column')) {
                         console.warn(`⚠️ Auto-migration schema error (fallback): ${fallbackError.message}`);
                    }
                }
            } else {
                console.warn(`⚠️ Auto-migration schema error: ${error.message}`);
            }
        }
    }
    
    console.log('✅ Startup database migrations completed.');
}
