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

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

async function debugQuoteCalculation() {
    const connection = await mysql.createConnection(dbConfig);
    
    console.log('=== DEBUG QUOTE ALGORITHM CASE 1469 ===\n');
    
    // Actual case data
    const [actualData] = await connection.execute(`
        SELECT 
            SUM(oh.duration_hours) as actual_hours,
            COUNT(*) as actual_entries,
            AVG(oh.duration_hours) as avg_hours_per_entry,
            oc.total_amount as case_amount
        FROM ordrestyring_hours oh
        LEFT JOIN ordrestyring_cases oc ON oh.case_number = oc.case_number
        WHERE oh.case_number = '1469'
        GROUP BY oc.total_amount
    `);
    
    console.log('ACTUAL CASE DATA:');
    console.log('- Faktiske timer:', actualData[0].actual_hours);
    console.log('- Antal registreringer:', actualData[0].actual_entries);
    console.log('- Gennemsnit per registrering:', Number(actualData[0].avg_hours_per_entry).toFixed(2));
    console.log('- Case beløb:', actualData[0].case_amount);
    
    // Historical customer analysis
    const [customerHistory] = await connection.execute(`
        SELECT 
            customer_id,
            COUNT(DISTINCT case_number) as total_cases,
            AVG(total_amount) as avg_amount,
            SUM(total_amount) as total_amount
        FROM ordrestyring_cases 
        WHERE customer_id = (
            SELECT customer_id FROM ordrestyring_cases WHERE case_number = '1469'
        )
    `);
    
    console.log('\nCUSTOMER HISTORICAL DATA:');
    console.log('- Customer ID:', customerHistory[0].customer_id);
    console.log('- Antal sager:', customerHistory[0].total_cases);
    console.log('- Gennemsnit beløb:', customerHistory[0].avg_amount);
    console.log('- Total beløb:', customerHistory[0].total_amount);
    
    // Similar cases analysis
    const [similarCases] = await connection.execute(`
        SELECT 
            case_number,
            total_amount,
            actual_hours,
            (total_amount / actual_hours) as hourly_rate
        FROM (
            SELECT 
                oc.case_number,
                oc.total_amount,
                SUM(oh.duration_hours) as actual_hours
            FROM ordrestyring_cases oc
            LEFT JOIN ordrestyring_hours oh ON oc.case_number = oh.case_number
            WHERE oc.total_amount BETWEEN 500000 AND 800000
            AND oh.duration_hours IS NOT NULL
            GROUP BY oc.case_number, oc.total_amount
            HAVING actual_hours > 0
        ) t
        ORDER BY ABS(total_amount - 680000)
        LIMIT 5
    `);
    
    console.log('\nSIMILAR CASES (500k-800k beløb):');
    similarCases.forEach(case_ => {
        console.log(`- Sag ${case_.case_number}: Beløb ${case_.total_amount}kr, Timer ${case_.actual_hours}h, Timepris: ${(case_.hourly_rate || 0).toFixed(0)}kr`);
    });
    
    // Identify the algorithmic issue
    console.log('\n=== ALGORITHM DEBUG ===');
    
    // Check if we're multiplying by case count instead of using averages
    const totalCases = customerHistory[0].total_cases;
    const totalAmount = customerHistory[0].total_amount;
    const avgAmount = customerHistory[0].avg_amount;
    
    console.log('Potentielle problemer:');
    console.log(`- Hvis vi bruger total_amount (${totalAmount}) i stedet for avg (${avgAmount}): ${totalAmount / avgAmount}x fejl`);
    console.log(`- Hvis vi ganger med antal sager (${totalCases}): ${totalCases}x fejl`);
    
    // Calculate expected hourly rate
    const actualHours = actualData[0].actual_hours;
    const actualAmount = actualData[0].case_amount;
    const expectedHourlyRate = actualAmount / actualHours;
    console.log(`- Faktisk timepris for sag 1469: ${expectedHourlyRate.toFixed(0)}kr/time`);
    
    // Check material multiplier issues
    const [materialStats] = await connection.execute(`
        SELECT 
            COUNT(*) as material_entries,
            SUM(quantity * unit_price) as total_material_cost,
            AVG(quantity * unit_price) as avg_item_cost
        FROM ordrestyring_materials 
        WHERE case_number = '1469'
    `);
    
    console.log('\nMATERIAL DATA:');
    console.log('- Material entries:', materialStats[0].material_entries);
    console.log('- Total material cost:', materialStats[0].total_material_cost);
    console.log('- Avg item cost:', materialStats[0].avg_item_cost);
    
    await connection.end();
}

debugQuoteCalculation().catch(console.error);