<?php
if (basename(__FILE__) === basename($_SERVER['SCRIPT_FILENAME'] ?? '')) { http_response_code(403); exit('Forbidden'); }
require_once __DIR__ . '/../../app/services/QuotationCalculator.php';

function save_quotation(int $id = 0): int
{
    $systemType = (string)post('system_type');
    $method = (string)post('calculation_method');
    if (!QuotationCalculator::isValidSystemMethod($systemType, $method)) {
        flash('error', 'Invalid calculation method for selected system type.');
        redirect($id > 0 ? '/quotations/edit.php?id=' . $id : '/quotations/create.php');
    }
    if (!in_array((string)post('panel_mounting', 'Rooftop'), QuotationCalculator::PANEL_MOUNTING, true)) {
        flash('error', 'Please select valid quotation system, calculation method, and panel mounting.');
        redirect($id > 0 ? '/quotations/edit.php?id=' . $id : '/quotations/create.php');
    }
    foreach (['monthly_bill', 'peak_load_kw', 'pump_hp', 'pump_head', 'vat_percent', 'discount_amount', 'average_sun_hour', 'performance_ratio', 'grid_emission_factor', 'coal_factor', 'electricity_rate', 'pump_efficiency', 'system_efficiency', 'running_hours_per_day', 'lc_fee_percent', 'freight_usd', 'insurance_usd', 'other_import_cost_usd', 'dollar_rate', 'ait_percent', 'margin_percent', 'dc_capacity_kwp', 'ac_capacity_kw'] as $numberField) {
        if ((float)post($numberField, 0) < 0) {
            flash('error', 'Numeric quotation values cannot be negative.');
            redirect($id > 0 ? '/quotations/edit.php?id=' . $id : '/quotations/create.php');
        }
    }

    // ── Retry loop ──────────────────────────────────────────────────────────────
    // next_quotation_no() uses SELECT FOR UPDATE on a dedicated counter table,
    // so duplicate numbers should be extremely rare.  This loop is the final
    // safety net: if a UNIQUE constraint violation on quotation_no still occurs
    // (e.g. clock skew, manual inserts, or the counter table not yet migrated)
    // we simply retry with a freshly generated number up to 3 times before
    // giving the user a clear, friendly error message.
    // ────────────────────────────────────────────────────────────────────────────
    $maxAttempts = 3;

    for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
        $pdo = db();
        $pdo->beginTransaction();
        try {
            $customerId = (int)post('customer_id', 0);
            if ($customerId <= 0) {
                if (!can('customer:create')) {
                    throw new RuntimeException('You do not have permission to create new customers. Please select your existing profile.');
                }
                $name = trim((string)post('customer_name'));
                if ($name === '') { throw new RuntimeException('Customer name is required.'); }
                $stmt = $pdo->prepare('INSERT INTO customers (customer_name, phone, email, address, location, customer_type, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
                $stmt->execute([$name, post('phone'), post('email'), post('address'), post('location'), post('site_type'), current_user()['id'], now(), now()]);
                $customerId = (int)$pdo->lastInsertId();
                log_activity('customer.create', 'Created customer ' . $name);
            }

            $subtotal = 0.0;
            foreach ((array)post('items', []) as $item) {
                $qty = (float)($item['qty'] ?? 0);
                $price = (float)($item['unit_price'] ?? 0);
                if ($qty <= 0 || $price < 0) {
                    throw new RuntimeException('Product quantity must be greater than 0 and unit price cannot be negative.');
                }
                $subtotal += $qty * $price;
            }
            $hasImportedMaterials = !empty($_POST['has_imported_materials']) ? 1 : 0;
            $importSubtotalUsd = 0.0;
            foreach ((array)post('import_items', []) as $importItem) {
                $qty = (float)($importItem['qty'] ?? 0);
                $rate = (float)($importItem['fob_rate_usd'] ?? 0);
                if ($qty < 0 || $rate < 0) {
                    throw new RuntimeException('Imported item quantity and FOB rate cannot be negative.');
                }
                $importSubtotalUsd += $qty * $rate;
            }
            $lcFeePercent = (float)post('lc_fee_percent', 7);
            $lcFeeUsd = $importSubtotalUsd * ($lcFeePercent / 100);
            $freightUsd = (float)post('freight_usd', 0);
            $insuranceUsd = (float)post('insurance_usd', 0);
            $otherImportCostUsd = (float)post('other_import_cost_usd', 0);
            $totalImportCostUsd = $hasImportedMaterials ? $importSubtotalUsd + $lcFeeUsd + $freightUsd + $insuranceUsd + $otherImportCostUsd : 0.0;
            $dollarRate = (float)post('dollar_rate', 125);
            $totalImportCostBdt = $totalImportCostUsd * $dollarRate;

            $vatPercent = (float)post('vat_percent', 0);
            $aitPercent = (float)post('ait_percent', 5);
            $aitAmount = $hasImportedMaterials ? $subtotal * ($aitPercent / 100) : 0.0;
            $vatBase = $hasImportedMaterials ? $subtotal + $aitAmount : $subtotal;
            $vatAmount = $vatBase * ($vatPercent / 100);
            $localTotalIncludingTax = $subtotal + $aitAmount + $vatAmount;
            $discount = (float)post('discount_amount', 0);
            $normalGrand = max(0, $subtotal + $vatAmount - $discount);
            $totalExcludingVatAit = $totalImportCostBdt + $subtotal;
            $totalIncludingVatAit = $totalImportCostBdt + $localTotalIncludingTax;
            $marginPercent = (float)post('margin_percent', 0);
            $marginAmount = $hasImportedMaterials ? $totalIncludingVatAit * ($marginPercent / 100) : 0.0;
            $finalQuotationAmount = $hasImportedMaterials ? $totalIncludingVatAit + $marginAmount : $normalGrand;
            $grand = max(0, $finalQuotationAmount);
            $dcCapacityKwp = (float)post('dc_capacity_kwp', 0);
            if ($dcCapacityKwp <= 0) {
                $dcCapacityKwp = calculate_dc_capacity_from_items((array)post('items', []));
            }
            $acCapacityKw = (float)post('ac_capacity_kw', 0);
            $costPerWattExcluding = $dcCapacityKwp > 0 ? $totalExcludingVatAit / ($dcCapacityKwp * 1000) : null;
            $costPerWattIncluding = $dcCapacityKwp > 0 ? $totalIncludingVatAit / ($dcCapacityKwp * 1000) : null;
            $costPerKwExcluding = $dcCapacityKwp > 0 ? $totalExcludingVatAit / $dcCapacityKwp : null;
            $costPerKwIncluding = $dcCapacityKwp > 0 ? $totalIncludingVatAit / $dcCapacityKwp : null;

            $averageSunHour = (float)post('average_sun_hour', 4.5);
            $performanceRatio = (float)post('performance_ratio', 0.75);
            $gridEmissionFactor = (float)post('grid_emission_factor', 0.62);
            $coalFactor = (float)post('coal_factor', 0.4);
            $electricityRate = (float)post('electricity_rate', 10);
            $pumpEfficiency = (float)post('pump_efficiency', 0.55);
            $systemEfficiency = (float)post('system_efficiency', 0.75);
            $loadsInput = (array)post('loads', []);
            $loadCalc = QuotationCalculator::load($loadsInput, $averageSunHour);
            $waterDemandLiters = (float)preg_replace('/[^0-9.]/', '', (string)post('water_demand', '0'));
            $pumpCalc = QuotationCalculator::pump((float)post('pump_hp', 0), (float)post('pump_head', 0), $waterDemandLiters, $averageSunHour, $pumpEfficiency, $systemEfficiency, (string)post('pump_type', 'AC'));

            $systemCapacityKw = 0.0;
            $suggestedInverterKw = null;
            $batteryVoltage = null;
            $batteryAh = null;
            $batterySpec = 'Not Required';
            if ($method === 'Load Calculation') {
                $systemCapacityKw = ((float)$loadCalc['solar_panel_w']) / 1000;
                $suggestedInverterKw = ((float)$loadCalc['inverter_va']) / 1000;
                $batteryVoltage = $systemType === 'On-Grid Solar System' ? null : $loadCalc['battery_voltage'];
                $batteryAh = $systemType === 'On-Grid Solar System' ? null : $loadCalc['battery_ah'];
                $batterySpec = $systemType === 'On-Grid Solar System' ? 'Not Required' : (string)$loadCalc['battery_spec'];
            } elseif ($method === 'Monthly Electricity Bill') {
                $billCalc = QuotationCalculator::bill((float)post('monthly_bill', 0), $electricityRate, $averageSunHour);
                $systemCapacityKw = (float)$billCalc['solar_kw'];
                $suggestedInverterKw = $systemCapacityKw;
                $batterySpec = $systemType === 'On-Grid Solar System' ? 'Not Required' : 'As per backup requirement';
            } elseif ($method === 'Peak Load') {
                $peakCalc = QuotationCalculator::peak((float)post('peak_load_kw', 0), 25);
                $systemCapacityKw = (float)$peakCalc['suggested_kw'];
                $suggestedInverterKw = $systemCapacityKw;
                $batterySpec = 'Not Required';
            } elseif ($method === 'Water Pumping') {
                $systemCapacityKw = (float)$pumpCalc['suggested_solar_kw'];
                $suggestedInverterKw = (float)$pumpCalc['suggested_inverter_kw'];
                $batterySpec = 'Not Required';
            }
            $energy = QuotationCalculator::energy($systemCapacityKw, $grand, (float)post('monthly_bill', 0), $averageSunHour, $performanceRatio, $gridEmissionFactor, $coalFactor, $electricityRate, $method);

            $data = [
                // On UPDATE: keep the existing quotation_no from the form.
                // On INSERT: ALWAYS generate a fresh atomic number inside this
                //            transaction — never trust the pre-filled form value,
                //            which may be stale if the user left the tab open.
                'quotation_no' => $id > 0 ? post('quotation_no') : next_quotation_no(),
                'customer_id' => $customerId,
                'system_type' => $systemType,
                'calculation_method' => $method,
                'site_type' => post('site_type'),
                'installation_area' => post('installation_area'),
                'panel_mounting' => post('panel_mounting', 'Rooftop'),
                'monthly_bill' => (float)post('monthly_bill', 0),
                'peak_load_kw' => (float)post('peak_load_kw', 0),
                'backup_hours' => 0,
                'pump_hp' => (float)post('pump_hp', 0),
                'pump_head' => (float)post('pump_head', 0),
                'water_demand' => post('water_demand'),
                'has_imported_materials' => $hasImportedMaterials,
                'import_subtotal_usd' => round($importSubtotalUsd, 2),
                'lc_fee_percent' => $lcFeePercent,
                'lc_fee_usd' => round($lcFeeUsd, 2),
                'freight_usd' => $freightUsd,
                'insurance_usd' => $insuranceUsd,
                'other_import_cost_usd' => $otherImportCostUsd,
                'total_import_cost_usd' => round($totalImportCostUsd, 2),
                'dollar_rate' => $dollarRate,
                'total_import_cost_bdt' => round($totalImportCostBdt, 2),
                'local_subtotal_excluding_tax' => round($subtotal, 2),
                'ait_percent' => $aitPercent,
                'ait_amount' => round($aitAmount, 2),
                'subtotal' => $subtotal,
                'vat_percent' => $vatPercent,
                'vat_amount' => $vatAmount,
                'local_total_including_tax' => round($localTotalIncludingTax, 2),
                'discount_amount' => $discount,
                'total_excluding_vat_ait' => round($totalExcludingVatAit, 2),
                'total_including_vat_ait' => round($totalIncludingVatAit, 2),
                'margin_percent' => $marginPercent,
                'margin_amount' => round($marginAmount, 2),
                'final_quotation_amount' => round($grand, 2),
                'grand_total' => $grand,
                'dc_capacity_kwp' => round($dcCapacityKwp, 2),
                'ac_capacity_kw' => round($acCapacityKw, 2),
                'cost_per_watt_excluding_tax' => $costPerWattExcluding !== null ? round($costPerWattExcluding, 2) : null,
                'cost_per_watt_including_tax' => $costPerWattIncluding !== null ? round($costPerWattIncluding, 2) : null,
                'cost_per_kw_excluding_tax' => $costPerKwExcluding !== null ? round($costPerKwExcluding, 2) : null,
                'cost_per_kw_including_tax' => $costPerKwIncluding !== null ? round($costPerKwIncluding, 2) : null,
                'show_cost_per_watt_on_pdf' => !empty($_POST['show_cost_per_watt_on_pdf']) ? 1 : 0,
                'import_tax_note' => post('import_tax_note', 'No VAT & AIT for Import Part'),
                'status' => post('status', 'draft'),
                'notes' => post('notes'),
                'terms_conditions' => post('terms_conditions'),
                'system_capacity_kw' => $systemCapacityKw,
                'annual_energy_kwh' => $energy['annual_energy_kwh'],
                'carbon_reduced_kg' => $energy['carbon_reduced_kg'],
                'coal_reduced_kg' => $energy['coal_reduced_kg'],
                'annual_savings_amount' => $energy['annual_savings_amount'],
                'payback_period_years' => $energy['payback_period_years'],
                'average_sun_hour' => $averageSunHour,
                'performance_ratio' => $performanceRatio,
                'grid_emission_factor' => $gridEmissionFactor,
                'coal_factor' => $coalFactor,
                'electricity_rate' => $electricityRate,
                'battery_voltage' => $batteryVoltage,
                'battery_ah' => $batteryAh,
                'battery_spec' => $batterySpec,
                'pump_efficiency' => $pumpEfficiency,
                'system_efficiency' => $systemEfficiency,
                'daily_pumping_energy_kwh' => $pumpCalc['daily_pumping_energy_kwh'],
                'suggested_inverter_kw' => $suggestedInverterKw,
                'pump_type' => post('pump_type', 'AC'),
                'pump_phase' => post('pump_phase', 'Single Phase'),
                'pipe_diameter' => post('pipe_diameter'),
                'running_hours_per_day' => (float)post('running_hours_per_day', 0),
            ];

            if ($id > 0) {
                if (current_user()['role'] !== 'admin') {
                    $qStmt = $pdo->prepare('SELECT created_by FROM quotations WHERE id=?');
                    $qStmt->execute([$id]);
                    if ((int)$qStmt->fetchColumn() !== current_user()['id']) {
                        throw new RuntimeException('Permission denied. You can only edit your own quotations.');
                    }
                }
                $sets = implode(', ', array_map(static fn(string $column): string => $column . '=?', array_keys($data)));
                $stmt = $pdo->prepare('UPDATE quotations SET ' . $sets . ', updated_at=? WHERE id=?');
                $stmt->execute([...array_values($data), now(), $id]);
                $pdo->prepare('DELETE FROM quotation_items WHERE quotation_id=?')->execute([$id]);
                $pdo->prepare('DELETE FROM quotation_loads WHERE quotation_id=?')->execute([$id]);
                $pdo->prepare('DELETE FROM quotation_import_items WHERE quotation_id=?')->execute([$id]);
                log_activity('quotation.update', 'Updated quotation #' . $id);
            } else {
                $insertData = $data + ['created_by' => current_user()['id'], 'created_at' => now(), 'updated_at' => now()];
                $columns = array_keys($insertData);
                $placeholders = implode(', ', array_fill(0, count($columns), '?'));
                $stmt = $pdo->prepare('INSERT INTO quotations (' . implode(', ', $columns) . ') VALUES (' . $placeholders . ')');
                $stmt->execute(array_values($insertData));
                $id = (int)$pdo->lastInsertId();
                log_activity('quotation.create', 'Created quotation #' . $id);
            }

            $itemStmt = $pdo->prepare('INSERT INTO quotation_items (quotation_id, product_id, product_category, product_name, description, brand, model, qty, unit, unit_price, total, is_custom_item, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
            foreach ((array)post('items', []) as $item) {
                $name = trim((string)($item['product_name'] ?? ''));
                if ($name === '' && empty($item['product_id'])) { continue; }
                if (!empty($item['product_id']) && $name === '') {
                    $pstmt = $pdo->prepare('SELECT * FROM products WHERE id=?'); $pstmt->execute([(int)$item['product_id']]); $p = $pstmt->fetch();
                    $name = $p['product_name'] ?? '';
                    $item['product_category'] = $item['product_category'] ?: ($p['product_category'] ?? '');
                }
                $qty = (float)($item['qty'] ?? 0); $price = (float)($item['unit_price'] ?? 0);
                $itemStmt->execute([$id, !empty($item['product_id']) ? (int)$item['product_id'] : null, $item['product_category'] ?? '', $name, $item['description'] ?? '', $item['brand'] ?? '', $item['model'] ?? '', $qty, $item['unit'] ?? 'pcs', $price, $qty * $price, (int)($item['is_custom_item'] ?? 0), now(), now()]);
            }

            if ($method === 'Load Calculation') {
                $loadStmt = $pdo->prepare('INSERT INTO quotation_loads (quotation_id, load_name, qty, watt_rating, backup_when_no_sun, total_watt, watt_hour, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
                foreach ($loadsInput as $load) {
                    $name = trim((string)($load['load_name'] ?? ''));
                    if ($name === '') { continue; }
                    $qty = (float)($load['qty'] ?? 0); $watt = (float)($load['watt_rating'] ?? 0); $backup = (float)($load['backup_when_no_sun'] ?? 0);
                    $loadStmt->execute([$id, $name, $qty, $watt, $backup, $qty * $watt, $qty * $watt * $backup, now(), now()]);
                }
            }
            if ($hasImportedMaterials) {
                $importStmt = $pdo->prepare('INSERT INTO quotation_import_items (quotation_id, item_head, specification, brand, model, qty, unit, fob_rate_usd, fob_total_usd, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
                foreach ((array)post('import_items', []) as $importItem) {
                    $head = trim((string)($importItem['item_head'] ?? ''));
                    if ($head === '') { continue; }
                    $qty = (float)($importItem['qty'] ?? 0);
                    $rate = (float)($importItem['fob_rate_usd'] ?? 0);
                    $importStmt->execute([$id, $head, $importItem['specification'] ?? '', $importItem['brand'] ?? '', $importItem['model'] ?? '', $qty, $importItem['unit'] ?? 'pcs', $rate, $qty * $rate, now(), now()]);
                }
            }
            $pdo->commit();
            return $id;

        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }

            // Detect a UNIQUE constraint violation specifically on quotation_no
            $isDuplicateNo = ($e instanceof \PDOException)
                && in_array($e->getCode(), ['23000', 23000], true)
                && str_contains((string)$e->getMessage(), 'quotation_no');

            if ($isDuplicateNo && $attempt < $maxAttempts) {
                // Another concurrent request took this number — loop and retry
                log_activity('quotation.no_retry', 'Quotation number collision on attempt ' . $attempt . ', retrying');
                continue;
            }

            // Non-recoverable error or max retries exhausted
            $userMessage = $isDuplicateNo
                ? 'Could not assign a unique quotation number (high traffic). Please try saving again.'
                : $e->getMessage();

            flash('error', $userMessage);
            redirect($id > 0 ? '/quotations/edit.php?id=' . $id : '/quotations/create.php');
        }
    } // end retry loop

    // Unreachable — satisfies static analysis
    flash('error', 'Failed to save quotation. Please try again.');
    redirect($id > 0 ? '/quotations/edit.php?id=' . $id : '/quotations/create.php');
}

function calculate_dc_capacity_from_items(array $items): float
{
    $capacity = 0.0;
    foreach ($items as $item) {
        $text = strtolower(implode(' ', [
            $item['product_category'] ?? '',
            $item['product_name'] ?? '',
            $item['description'] ?? '',
            $item['model'] ?? '',
        ]));
        if (!str_contains($text, 'panel') && !str_contains($text, 'module')) {
            continue;
        }
        if (preg_match('/(\d+(?:\.\d+)?)\s*w\b/i', $text, $match)) {
            $capacity += ((float)($item['qty'] ?? 0) * (float)$match[1]) / 1000;
        }
    }
    return round($capacity, 2);
}
