<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();

$con = new mysqli("localhost", "underwat_uplinepj", "Police2345@1", "underwat_jetinstantTQ");
if ($con->connect_error) die("Connection failed: " . $con->connect_error);
$con->set_charset('utf8mb4'); // safer text handling

function result_to_array($res) {
    $rows = [];
    if ($res instanceof mysqli_result) {
        while ($row = $res->fetch_assoc()) {
            $rows[] = $row;
        }
        $res->free();
    }
    return $rows;
}

$PID = $_POST['pid'] ?? $_SESSION['pid'] ?? '';
if (isset($_POST['pid'])) $_SESSION['pid'] = $PID;

/* ------------ UPDATE scripts fixed to use PID + SN ------------- */
if (isset($_POST['update_materials'])) {
    if (!empty($_POST['materials_pid'])) {
        foreach ($_POST['materials_pid'] as $i => $pid) {
            $sn    = $con->real_escape_string($_POST['materials_sn'][$i]);
            $qty   = $con->real_escape_string($_POST['materials_qty'][$i]);
            $extra = $con->real_escape_string($_POST['materials_extra'][$i]);
            $price = $con->real_escape_string($_POST['materials_price'][$i]);
            $con->query("UPDATE foundation_materials 
                         SET COEQuantity='$qty', Extra='$extra', Price='$price' 
                         WHERE PID='$pid' AND SN='$sn'");
        }
    }
}
if (isset($_POST['update_labour'])) {
    if (!empty($_POST['labour_pid'])) {
        foreach ($_POST['labour_pid'] as $i => $pid) {
            $sn    = $con->real_escape_string($_POST['labour_sn'][$i]);
            $qty   = $con->real_escape_string($_POST['labour_qty'][$i]);
            $price = $con->real_escape_string($_POST['labour_price'][$i]);
            $con->query("UPDATE foundation_labourer 
                         SET COEQuantity='$qty', Price='$price' 
                         WHERE PID='$pid' AND SN='$sn'");
        }
    }
}
if (isset($_POST['update_equipment'])) {
    if (!empty($_POST['equipment_pid'])) {
        foreach ($_POST['equipment_pid'] as $i => $pid) {
            $sn    = $con->real_escape_string($_POST['equipment_sn'][$i]);
            $qty   = $con->real_escape_string($_POST['equipment_qty'][$i]);
            $price = $con->real_escape_string($_POST['equipment_price'][$i]);
            $con->query("UPDATE foundation_equipment 
                         SET Quantity='$qty', Price='$price' 
                         WHERE PID='$pid' AND SN='$sn'");
        }
    }
}
/* --------------------------------------------------------------- */

$project_info_res = $con->query("SELECT * FROM upline_app_project_info WHERE project_ref='$PID'");
$project_info = $project_info_res ? $project_info_res->fetch_assoc() : null;

$materials_res = $con->query("SELECT * FROM foundation_materials WHERE PID='$PID'");
$labour_res    = $con->query("SELECT * FROM foundation_labourer WHERE PID='$PID'");
$equipment_res = $con->query("SELECT * FROM foundation_equipment WHERE PID='$PID'");

$material_data  = result_to_array($materials_res);
$labour_data    = result_to_array($labour_res);
$equipment_data = result_to_array($equipment_res);
?>
<!DOCTYPE html>
<html>
<head>
  <!-- head unchanged -->
</head>
<body class="bg-gray-100 p-6">
<form method="POST">
<div class="max-w-6xl mx-auto bg-white shadow p-6 rounded-xl" id="reportContent">

<!-- project info unchanged -->

<?php
function render_editable_section($section, $data, $desc_key, $qty_key, $extra_key, $price_key) {
    $subtotal = 0;
    $has_extra = $extra_key ? '1' : '0';
    $html = "<h3 class='text-xl font-semibold mt-6 mb-2'>".ucfirst($section)."</h3>
             <table class='w-full text-sm border calc-table' data-section='{$section}' data-has-extra='{$has_extra}'>
               <thead> ... </thead><tbody>";

    foreach ($data as $i => $row) {
        $desc  = $row[$desc_key] ?? '';
        $qty   = (float)($row[$qty_key] ?? 0);
        $extra = $extra_key ? (float)($row[$extra_key] ?? 0) : 0;
        $price = (float)($row[$price_key] ?? 0);
        $total = ($qty + $extra) * $price;
        $subtotal += $total;

        $html .= "<tr>
          <td>".htmlspecialchars($desc)."</td>
          <td><input name='{$section}_qty[]' value='$qty' class='qty w-full border rounded px-1'/></td>";
        if ($extra_key) {
            $html .= "<td><input name='{$section}_extra[]' value='$extra' class='extra w-full border rounded px-1'/></td>";
        }
        $html .= "<td><input name='{$section}_price[]' value='$price' class='price w-full border rounded px-1'/></td>
                  <td class='row-total text-right'>".number_format($total,2)."</td>
        </tr>";

        // Hidden PID + SN
        $pid = $row['PID'] ?? '';
        $sn  = $row['SN'] ?? '';
        $html .= "<input type='hidden' name='{$section}_pid[]' value='$pid'/>
                  <input type='hidden' name='{$section}_sn[]' value='$sn'/>";
    }

    $cont = $subtotal * 0.05;
    $grand = $subtotal + $cont;
    $html .= "</tbody></table>
              <div class='section-subtotal'>Subtotal: NGN ".number_format($subtotal,2)."</div>
              <div class='section-contingency'>5% Contingency: NGN ".number_format($cont,2)."</div>
              <div class='section-total'>Total ($section): NGN ".number_format($grand,2)."</div>
              <button type='submit' name='update_$section' class='bg-yellow-500 text-white px-4 py-1 rounded'>Save ".ucfirst($section)."</button>";
    echo $html;
    return $grand;
}

$total_material  = render_editable_section('materials', $material_data, 'MaterialDescription','COEQuantity','Extra','Price');
$total_labour    = render_editable_section('labour', $labour_data, 'LabourerDescription','COEQuantity',null,'Price');
$total_equipment = render_editable_section('equipment',$equipment_data,'EquipmentDescription','Quantity',null,'Price');
$foundation_total = $total_material + $total_labour + $total_equipment;
?>
<div id="foundation-total">FOUNDATION TOTAL: NGN <?= number_format($foundation_total,2) ?></div>
</div>
</form>

<!-- export + JS unchanged -->
<script>
function exportToExcel() {
    const tables = document.querySelectorAll("#reportContent table");
    const wb = XLSX.utils.book_new();
    tables.forEach((table, index) => {
        // clone & swap inputs for values so Excel gets numbers, not input elements
        const clone = table.cloneNode(true);
        clone.querySelectorAll("input").forEach(input => {
            const text = document.createTextNode(input.value);
            input.parentNode.replaceChild(text, input);
        });
        const ws = XLSX.utils.table_to_sheet(clone);
        XLSX.utils.book_append_sheet(wb, ws, `Section ${index + 1}`);
    });
    XLSX.writeFile(wb, "Foundation_Report.xlsx");
}

async function exportToPDF() {
    const { jsPDF } = window.jspdf;
    const doc = new jsPDF('p', 'pt', 'a4');
    let y = 40;

    // Project Info (scoped to #projectInfo to avoid grabbing other <p>'s)
    const infoDiv = document.getElementById("projectInfo");
    if (infoDiv) {
        doc.setFontSize(12);
        infoDiv.querySelectorAll("p").forEach(p => {
            doc.text(p.innerText, 40, y);
            y += 15;
        });
        y += 10;
    }

    const tables = document.querySelectorAll("#reportContent table");

    for (const table of tables) {
        const sectionTitle = table.previousElementSibling?.innerText || "Section";
        doc.setFontSize(13);
        doc.text(sectionTitle, 40, y);
        y += 10;

        // Clone table & replace inputs with text
        const clone = table.cloneNode(true);
        clone.querySelectorAll("input").forEach(input => {
            const text = document.createTextNode(input.value);
            input.parentNode.replaceChild(text, input);
        });

        await doc.autoTable({
            html: clone,
            startY: y,
            theme: 'grid',
            headStyles: { fillColor: [100, 149, 237] },
            styles: { fontSize: 9, cellPadding: 3 },
            didDrawPage: function (data) {
                y = data.cursor.y + 10;
            }
        });

        // pull the three summary lines that follow each table
        let next = table.nextElementSibling;
        let count = 0;
        while (next && count < 3) {
            if (next.tagName === 'DIV' && next.innerText.includes("NGN")) {
                const text = next.innerText;
                doc.setFontSize(text.includes("Total") ? 11 : 10);
                const textWidth = doc.getTextWidth(text);
                doc.text(text, doc.internal.pageSize.getWidth() - 40 - textWidth, y);
                y += 14;
                count++;
            }
            next = next.nextElementSibling;
        }

        y += 20;
    }

    // FOUNDATION TOTAL at the end
    const finalTotal = document.getElementById("foundation-total")?.innerText;
    if (finalTotal) {
        doc.setFontSize(13);
        const textWidth = doc.getTextWidth(finalTotal);
        doc.text(finalTotal, doc.internal.pageSize.getWidth() - 40 - textWidth, y + 20);
    }

    doc.save("Foundation_Report.pdf");
}

/* -----------------------------
   Unified Live Recalculation
--------------------------------*/
(function() {
    function parseNum(v) {
        if (v == null) return 0;
        const n = parseFloat(String(v).toString().replace(/,/g,''));
        return isNaN(n) ? 0 : n;
    }
    function fmtNGN(n) {
        return 'NGN ' + Number(n).toLocaleString('en-NG', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
    }
    function fmt(n) {
        return Number(n).toLocaleString('en-NG', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
    }

    function recalcOneTable(table) {
        if (!table) return 0;
        const hasExtra = table.getAttribute('data-has-extra') === '1';
        let subtotal = 0;

        table.querySelectorAll('tbody tr').forEach(row => {
            const qtyEl   = row.querySelector('.qty');
            const priceEl = row.querySelector('.price');
            const extraEl = hasExtra ? row.querySelector('.extra') : null;

            const qty   = parseNum(qtyEl ? qtyEl.value : 0);
            const price = parseNum(priceEl ? priceEl.value : 0);
            const extra = hasExtra ? parseNum(extraEl ? extraEl.value : 0) : 0;

            const rowTotal = (qty + extra) * price;
            const totalCell = row.querySelector('.row-total');
            if (totalCell) totalCell.textContent = fmt(rowTotal);
            subtotal += rowTotal;
        });

        const cont = subtotal * 0.05;
        const sectionTotal = subtotal + cont;

        const subtotalDiv    = table.parentElement.querySelector('.section-subtotal');
        const contingencyDiv = table.parentElement.querySelector('.section-contingency');
        const totalDiv       = table.parentElement.querySelector('.section-total');

        if (subtotalDiv)    subtotalDiv.textContent    = `Subtotal: ${fmtNGN(subtotal)}`;
        if (contingencyDiv) contingencyDiv.textContent = `5% Contingency: ${fmtNGN(cont)}`;
        if (totalDiv)       totalDiv.textContent       = `Total (${table.dataset.section}): ${fmtNGN(sectionTotal)}`;

        return sectionTotal;
    }

    function recalcAll() {
        let foundation = 0;
        document.querySelectorAll('.calc-table').forEach(tbl => {
            foundation += recalcOneTable(tbl);
        });
        const f = document.getElementById('foundation-total');
        if (f) {
            f.textContent = `FOUNDATION TOTAL: ${fmtNGN(foundation)}`;
        }
    }

    // Event delegation for all inputs in all sections
    document.addEventListener('input', function(e) {
        if (e.target.matches('.qty, .price, .extra')) {
            const table = e.target.closest('.calc-table');
            const sectionTotal = recalcOneTable(table);
            // update foundation after a section changes
            let foundation = 0;
            document.querySelectorAll('.calc-table').forEach(tbl => {
                // If same table, we already have sectionTotal; otherwise recalc briefly
                foundation += (tbl === table) ? sectionTotal : recalcOneTable(tbl);
            });
            const f = document.getElementById('foundation-total');
            if (f) f.textContent = `FOUNDATION TOTAL: ${fmtNGN(foundation)}`;
        }
    });

    // Initial pass to normalize numbers and totals
    window.addEventListener('DOMContentLoaded', recalcAll);
})();
</script>
</body>
</html>
