<?php
session_start();
include('script/condb.php'); // database connection

// 1️⃣ Get PID safely
$PID = $_GET['project_ref'] ?? $_POST['pid'] ?? $_SESSION['pid'] ?? '';
if (!empty($PID)) {
    $_SESSION['pid'] = $PID;
} else {
    die("❌ Project ID not found");
}

// 2️⃣ Fetch wall records for this project
$wallsQuery = $con->prepare("SELECT * FROM wall_calculation WHERE PID = ?");
$wallsQuery->bind_param("s", $PID);
$wallsQuery->execute();
$wallsResult = $wallsQuery->get_result();

if ($wallsResult->num_rows === 0) {
    die("No wall records found for this project.");
}

// 3️⃣ Loop through each wall record
while ($row = $wallsResult->fetch_assoc()) {
    $walltype = strtolower(trim($row['walltype']));
    $blocktype = trim($row['blocktype']);
    $walllength = floatval($row['walllength']);
    $wallheight = floatval($row['wallheight']);

    // 4️⃣ Fetch block prices from settings table
    $priceQuery = $con->prepare("SELECT Block_price, BOQprice FROM upline_app_blocksettings WHERE Block_name = ?");
    $priceQuery->bind_param("s", $blocktype);
    $priceQuery->execute();
    $priceResult = $priceQuery->get_result();

    if ($priceResult->num_rows === 0) {
        // Skip if no matching block type found
        continue;
    }

    $prices = $priceResult->fetch_assoc();
    $Block_price = floatval($prices['Block_price']);
    $BOQprice = floatval($prices['BOQprice']);

    // 5️⃣ Calculate quantities based on wall type
    if ($walltype === 'external wall' || $walltype === 'external') {
        $totalblockqty_estimate = ($walllength * $wallheight) / 0.11875;
        $totalblockqty_boq = $walllength * $wallheight;
    } elseif ($walltype === 'internal wall' || $walltype === 'internal') {
        $totalblockqty_estimate = ($walllength * $wallheight) / 0.09025;
        $totalblockqty_boq = $walllength * $wallheight;
    } else {
        // default formula if type not recognized
        $totalblockqty_estimate = ($walllength * $wallheight) / 0.1;
        $totalblockqty_boq = $walllength * $wallheight;
    }

    // 6️⃣ Calculate total costs
    $totalcost_estimate = $totalblockqty_estimate * $Block_price;
    $totalcost_boq = $totalblockqty_boq * $BOQprice;

    // 7️⃣ Update foundation_materials table
    $updateQuery = $con->prepare("UPDATE foundation_materials 
        SET MaterialType = 'Block',
            COEQuantity = ?,
            BOQQuantity = ?,
            Extra = 0,
            Price = ?,
            BOQ_price = ?,
            CETotalAmount = ?,
            BOQTotalAmount = ?
        WHERE PID = ? AND MaterialDescription = ?");

    $updateQuery->bind_param(
        "ddddddss",
        $totalblockqty_estimate,
        $totalblockqty_boq,
        $Block_price,
        $BOQprice,
        $totalcost_estimate,
        $totalcost_boq,
        $PID,
        $blocktype
    );

    $updateQuery->execute();
}

// ✅ 8️⃣ Redirect back to editing page after completion
header("Location: stageofprojects_edit.php?project_ref=" . urlencode($PID));
exit;
?>
