/**
 * pricingService.js
 * Advanced Pricing & Interpolation Logic
 */

/**
 * Calculates the exact price for a custom quantity using Run-Rate interpolation.
 * @param {number} customQty - The quantity requested (e.g. 1250)
 * @param {Array} priceMatrix - Sorted array of {qty_threshold, price}
 * @returns {number} The interpolated unit price
 */
export function calculateInterpolatedPrice(customQty, priceMatrix) {
    if (!priceMatrix || priceMatrix.length === 0) return 0;

    // Sort matrix by threshold ascending
    const sorted = [...priceMatrix].sort((a, b) => a.qty_threshold - b.qty_threshold);

    // If qty below lowest threshold, use lowest price (or handle as error)
    if (customQty <= sorted[0].qty_threshold) return sorted[0].price;

    // If qty above highest threshold, use highest (or apply a diminishing run-rate)
    if (customQty >= sorted[sorted.length - 1].qty_threshold) {
        return sorted[sorted.length - 1].price;
    }

    // Find Lower and Upper bounds
    let lower = sorted[0];
    let upper = sorted[1];

    for (let i = 0; i < sorted.length - 1; i++) {
        if (customQty >= sorted[i].qty_threshold && customQty <= sorted[i + 1].qty_threshold) {
            lower = sorted[i];
            upper = sorted[i + 1];
            break;
        }
    }

    // Run-Rate Calculation: (Price Diff / Qty Diff)
    const priceDiff = upper.price - lower.price;
    const qtyDiff = upper.qty_threshold - lower.qty_threshold;
    const runRate = priceDiff / qtyDiff;

    const extraQty = customQty - lower.qty_threshold;
    const finalPrice = lower.price + (extraQty * runRate);

    return finalPrice;
}

/**
 * Calculates total cost for a component including setup and finishing.
 */
export async function calculateComponentCost(db, componentDetails) {
    const { paperTypeId, qty, finishingOptions } = componentDetails;
    
    // 1. Fetch Price Matrix for the paper
    const [matrix] = await db.execute(
        'SELECT qty_threshold, price FROM price_matrix WHERE paper_type_id = ? ORDER BY qty_threshold ASC',
        [paperTypeId]
    );

    const baseUnitPrice = calculateInterpolatedPrice(qty, matrix);
    const baseTotal = baseUnitPrice * qty;

    // 2. Fetch finishing costs
    let finishingSetup = 0;
    let finishingUnit = 0;

    if (finishingOptions && finishingOptions.length > 0) {
        const [options] = await db.execute(
            'SELECT id, setup_cost, price_tiers FROM finishing_options WHERE id IN (?)',
            [finishingOptions]
        );

        options.forEach(opt => {
            finishingSetup += parseFloat(opt.setup_cost || 0);
            
            // Simplified: use first tier for now, or implement deeper logic
            const tiers = JSON.parse(opt.price_tiers || '[]');
            const tier = tiers.find(t => qty >= t.fromQty && (t.toQty === null || qty <= t.toQty));
            finishingUnit += (tier?.sellPrice || 0) * qty;
        });
    }

    return {
        baseTotal,
        finishingSetup,
        finishingUnit,
        grandTotal: baseTotal + finishingSetup + finishingUnit,
        unitPrice: (baseTotal + finishingSetup + finishingUnit) / qty
    };
}
