<?php
session_start();
$con = new mysqli("localhost", "underwat_transparentqdb", "Police2345@1", "underwat_transparentqdb");
/*$con = new mysqli("localhost", "underwat_uplinepj", "Police2345@1", "underwat_jetinstantTQ");*/
if ($con->connect_error) die("Connection failed: " . $con->connect_error);

$PID = $_GET['project_ref'] ?? $_POST['pid'] ?? $_SESSION['pid'] ?? '';
if (!empty($PID)) {
    $_SESSION['pid'] = $PID;
}


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

if (isset($_POST['update_materials'])) {
    foreach ($_POST['materials_id'] as $i => $id) {
        $qty = $_POST['materials_qty'][$i];
        $extra = $_POST['materials_extra'][$i];
        $price = $_POST['materials_price'][$i];
        $con->query("UPDATE foundation_materials SET COEQuantity='$qty', Extra='$extra', Price='$price' WHERE PID='$id'");
    }
}
if (isset($_POST['update_labour'])) {
    foreach ($_POST['labour_id'] as $i => $id) {
        $qty = $_POST['labour_qty'][$i];
        $price = $_POST['labour_price'][$i];
        $con->query("UPDATE foundation_labourer SET COEQuantity='$qty', Price='$price' WHERE PID='$id'");
    }
}
if (isset($_POST['update_equipment'])) {
    foreach ($_POST['equipment_id'] as $i => $id) {
        $qty = $_POST['equipment_qty'][$i];
        $price = $_POST['equipment_price'][$i];
        $con->query("UPDATE foundation_equipment SET Quantity='$qty', Price='$price' WHERE PID='$id'");
    }
}

$project_info = $con->query("SELECT * FROM upline_app_project_info WHERE project_ref='$PID'")->fetch_assoc();
$materials = $con->query("SELECT * FROM foundation_materials WHERE PID='$PID'");
$labour = $con->query("SELECT * FROM foundation_labourer WHERE PID='$PID'");
$equipment = $con->query("SELECT * FROM foundation_equipment WHERE PID='$PID'");
?>
<!DOCTYPE html>
<html>
<head>
    <title>Foundation Report</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
    <script src="https://cdn.tailwindcss.com"></script>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf-autotable/3.5.28/jspdf.plugin.autotable.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/xlsx.full.min.js"></script>
</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">
    <div class="flex justify-end mb-4">
  <a href="dashboard.php" 
     class="no-print text-blue-600 hover:underline text-sm font-semibold">
    ← Back to Dashboard
  </a><!--<br/>
		<a href="project_detail.php" 
     class="no-print text-blue-600 hover:underline text-sm font-semibold">
    ← Back to Project Detail
  </a>-->
</div>
    <h2 class="text-2xl font-bold mb-4">Foundation Report</h2>

    <?php if ($project_info): ?>
        <div class="mb-4">
            <p><strong>Project Name:</strong> <?= $project_info['project_name'] ?></p>
            <p><strong>Project Type:</strong> <?= $project_info['project_type'] ?></p>
            <p><strong>Developer:</strong> <?= $project_info['project_developer'] ?></p>
            <p><strong>Location:</strong> <?= $project_info['project_address'] ?></p>
            <p><strong>Project REF:</strong> <?= $project_info['project_ref'] ?></p>
        </div>
    <?php endif; ?>

    <?php
    function render_editable_section($section, $data, $desc_key, $qty_key, $extra_key, $price_key, $id_key) {
        $subtotal = 0;
        $html = "<h3 class='text-xl font-semibold mt-6 mb-2'>$section</h3><table class='w-full text-sm border'><thead><tr class='bg-gray-200'>
                <th class='border px-2 py-1'>Description</th>
                <th class='border px-2 py-1'>Quantity</th>";
        if ($extra_key) $html .= "<th class='border px-2 py-1'>Extra</th>";
        $html .= "<th class='border px-2 py-1'>Price</th><th class='border px-2 py-1'>Total</th></tr></thead><tbody>";

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

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

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

    $material_data = $materials->fetch_all(MYSQLI_ASSOC);
    $labour_data = $labour->fetch_all(MYSQLI_ASSOC);
    $equipment_data = $equipment->fetch_all(MYSQLI_ASSOC);

    $total_material = render_editable_section('materials', $material_data, 'MaterialDescription', 'COEQuantity', 'Extra', 'Price', 'PID');
    $total_labour = render_editable_section('labour', $labour_data, 'LabourerDescription', 'COEQuantity', null, 'Price', 'PID');
    $total_equipment = render_editable_section('equipment', $equipment_data, 'EquipmentDescription', 'Quantity', null, 'Price', 'PID');

    $foundation_total = $total_material + $total_labour + $total_equipment;
    ?>
    <div class="mt-6 text-right text-xl font-bold" id="foundation-total">FOUNDATION TOTAL: NGN <?= number_format($foundation_total, 2) ?></div>
</div>
</form>

<div class="mt-6 text-right print:hidden">
    <button onclick="exportToExcel()" class="bg-green-600 text-white px-4 py-2 rounded">Export to Excel</button>
    <button onclick="exportToPDF()" class="bg-blue-600 text-white px-4 py-2 rounded">Export to PDF</button>
</div>

<script>
function exportToExcel() {
    const tables = document.querySelectorAll("#reportContent table");
    const wb = XLSX.utils.book_new();

    tables.forEach((table, index) => {
        const ws = XLSX.utils.table_to_sheet(table);
        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
    const projectInfo = [
        "Project Name: " + document.querySelector("p:nth-child(1)")?.innerText,
        "Project Type: " + document.querySelector("p:nth-child(2)")?.innerText,
        "Developer: " + document.querySelector("p:nth-child(3)")?.innerText,
        "Location: " + document.querySelector("p:nth-child(4)")?.innerText,
        "Project REF: " + document.querySelector("p:nth-child(5)")?.innerText
    ];

    doc.setFontSize(12);
    projectInfo.forEach(line => {
        doc.text(line, 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;
            }
        });

        // ONLY get summary for THIS section — the next 3 divs after the 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;
    }

    // Add 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");
}

</script>
</body>
</html>
