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

const dbConfig = {
    host: 'localhost',
    user: 'ordrestyring_user',
    password: process.env.DB_PASSWORD,
    database: 'ordrestyring_local'
};

async function findAlgorithmBug() {
    const connection = await mysql.createConnection(dbConfig);
    
    console.log('=== ALGORITHM BUG ANALYSIS ===\n');
    
    // Test the actual algorithm step by step
    
    // 1. Check market hours data
    const [marketHours] = await connection.execute(`
        SELECT 
            COUNT(*) as total_entries,
            AVG(oh.duration_hours) as avg_hours_per_task,
            AVG(ocm.total_sales / oh.duration_hours) as avg_hourly_rate
        FROM ordrestyring_hours oh
        LEFT JOIN ordrestyring_case_materials ocm ON oh.case_number = ocm.case_number
        WHERE oh.duration_hours > 0
        AND ocm.total_sales > 0
        LIMIT 1
    `);
    
    console.log('MARKET DATA:');
    console.log('- Total timer entries:', marketHours[0].total_entries);
    console.log('- Gennemsnit timer per task:', Number(marketHours[0].avg_hours_per_task || 0).toFixed(2));
    console.log('- Gennemsnit timepris:', Number(marketHours[0].avg_hourly_rate || 0).toFixed(2));
    
    // 2. Test the faulty calculation
    const avgHoursPerTask = Number(marketHours[0].avg_hours_per_task || 8);
    const baseHoursNewCustomer = avgHoursPerTask * 25; // This is the problem!
    
    console.log('\n=== BUG IDENTIFIED ===');
    console.log(`Avg hours per task: ${avgHoursPerTask}`);
    console.log(`Base hours for new customer: ${avgHoursPerTask} * 25 = ${baseHoursNewCustomer}`);
    console.log(`Dette giver: ${baseHoursNewCustomer * 1.0 * 1.1} timer efter multipliers`);
    console.log(`Med timepris 450kr: ${baseHoursNewCustomer * 1.0 * 1.1 * 450} kr labor cost`);
    
    // 3. Check what a realistic calculation should be
    const [realisticData] = await connection.execute(`
        SELECT 
            AVG(oh.duration_hours) as avg_hours,
            COUNT(DISTINCT oh.case_number) as cases,
            AVG(ocm.total_sales) as avg_case_value
        FROM ordrestyring_hours oh
        LEFT JOIN ordrestyring_case_materials ocm ON oh.case_number = ocm.case_number
        WHERE oh.duration_hours BETWEEN 800 AND 1200
        AND ocm.total_sales > 0
    `);
    
    console.log('\n=== REALISTIC COMPARISON ===');
    console.log('Cases with 800-1200 timer:');
    console.log('- Gennemsnit timer:', Number(realisticData[0].avg_hours || 0).toFixed(2));
    console.log('- Antal cases:', realisticData[0].cases);
    console.log('- Gennemsnit case værdi:', Number(realisticData[0].avg_case_value || 0).toFixed(0));
    
    await connection.end();
}

findAlgorithmBug().catch(console.error);