<?php
include('includes/header.php');

if (!isset($_GET['projectID'])) {
    echo '<h5>No project ID found!</h5>';
    exit(0);
}

$projectID = validate($_GET['projectID']);

?>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h3 class="mb-4">Resource Plan For DL</h3>
                <div class="text-right">
                    <a href="resource-plan.php" class="btn btn-danger float-end">Back</a>
                    <a href="add-resource-plan.php?projectID=<?= $projectID; ?>" class="btn btn-success mx-2 float-end">Add Resource Plan</a>
                    <a href="add-vendor-details.php?projectID=<?= $projectID; ?>" class="btn btn-info float-end">Add Vendor Details</a>
                </div>
                <?php
                $projectQuery = "SELECT projectName, clientName, contractValue FROM projects 
                                 INNER JOIN clients ON projects.clientID = clients.clientID 
                                 WHERE projectID = $projectID";
                $projectResult = mysqli_query($conn, $projectQuery);

                if ($projectResult && mysqli_num_rows($projectResult) > 0) {
                    $projectData = mysqli_fetch_assoc($projectResult);
                    echo '<h6>Project Name: ' . $projectData['projectName'] . '</h6>';
                    echo '<h6>Client Name: ' . $projectData['clientName'] . '</h6>';
                    echo '<h6>Project Value: ₹' . number_format($projectData['contractValue'], 2) . '</h6>';
                } else {
                    echo '<span class="text-danger">Project details not found</span>';
                }
                ?>
            </div>
            <div class="card-body">
                <?= alertMessage(); ?>
                <?php
                $resourcePlanQuery = "SELECT users.fname, users.lname, milestones.milestoneName, milestones.milestoneValue, resourcePlan.daysBudgeted, resourcePlan.resourceID, 
                                             vendor.vendorName, vendor.budgetAmount AS vendorBudgetAmount, 
                                             resourcePlan.vendorID, users.employeeRate, (resourcePlan.daysBudgeted * users.employeeRate) AS userBudgetAmount
                                      FROM resourcePlan
                                      LEFT JOIN users ON resourcePlan.userID = users.userID
                                      LEFT JOIN milestones ON resourcePlan.milestoneID = milestones.milestoneID
                                      LEFT JOIN vendor ON resourcePlan.vendorID = vendor.vendorID
                                      WHERE milestones.projectID = $projectID
                                      ORDER BY milestones.milestoneName, users.lname, vendor.vendorName";
                $resourcePlanResult = mysqli_query($conn, $resourcePlanQuery);

                if (!$resourcePlanResult || mysqli_num_rows($resourcePlanResult) == 0) {
                    echo '<div class="alert alert-warning">No resource plans found for this project.</div>';
                } else {
                    ?>
                    <!-- Button to download the resource plan table as Excel -->
                    <button onclick="exportTableToExcel('resource_plan.xlsx', 'resourcePlanTable')" class="btn btn-primary float-end" style="margin-bottom: 10px;">Download Excel</button>
                    <table id="resourcePlanTable" class="table table-bordered table-hover align-middle text-center table-responsive" style="border: 2px solid black;">
                        <thead style="background-color: #ccc;">
                            <tr>
                                <th>S.No.</th>
                                <th>Milestone</th>
                                <th>Milestone Value</th>
                                <th>User/Vendor</th>
                                <th>Days Budgeted</th>
                                <th>Budget Amount</th>
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tbody style="background-color: #f4f4f4; font-weight: 600;">
                            <?php
                            $sno = 1;
                            $previousMilestone = null;
                            $rowCount = 0;

                            $milestoneCountsQuery = "SELECT milestoneName, COUNT(*) AS count FROM resourcePlan 
                                                     INNER JOIN milestones ON resourcePlan.milestoneID = milestones.milestoneID 
                                                     WHERE milestones.projectID = $projectID 
                                                     GROUP BY milestoneName";
                            $milestoneCountsResult = mysqli_query($conn, $milestoneCountsQuery);
                            $milestoneCounts = [];
                            while ($countRow = mysqli_fetch_assoc($milestoneCountsResult)) {
                                $milestoneCounts[$countRow['milestoneName']] = $countRow['count'];
                            }

                            $totalDaysBudgeted = 0;
                            $totalBudgetAmount = 0;

                            while ($row = mysqli_fetch_assoc($resourcePlanResult)) {
                                if ($previousMilestone != $row['milestoneName']) {
                                    if ($previousMilestone !== null) {
                                        echo '<tr>';
                                        echo '<td colspan="3" style="text-align: center;"><strong>Total</strong></td>';
                                        echo '<td></td>';
                                        echo '<td><strong>' . number_format($totalDaysBudgeted, 2) . '</strong></td>';
                                        echo '<td><strong>' . number_format($totalBudgetAmount, 2) . '</strong></td>';
                                        echo '<td></td>';
                                        echo '</tr>';
                                    }
                                    $rowCount = $milestoneCounts[$row['milestoneName']];
                                    echo '<tr><td rowspan="' . $rowCount . '">' . $sno++ . '</td><td rowspan="' . $rowCount . '">' . $row['milestoneName'] . '</td>';
                                    echo '<td rowspan="' . $rowCount . '">' . $row['milestoneValue'] . '</td>';
                                    $previousMilestone = $row['milestoneName'];

                                    // Reset totals for new milestone
                                    $totalDaysBudgeted = 0;
                                    $totalBudgetAmount = 0;
                                }

                                // Calculate totals
                                $totalDaysBudgeted += ($row['vendorID'] ? 0 : $row['daysBudgeted']);
                                $totalBudgetAmount += ($row['vendorID'] ? $row['vendorBudgetAmount'] : $row['userBudgetAmount']);
                                ?>
                                <td><?= $row['fname'] ? $row['fname'] . ' ' . $row['lname'] : $row['vendorName']; ?></td>
                                <td><?= $row['vendorID'] ? 'NA' : number_format($row['daysBudgeted'], 2); ?></td>
                                <td><?= $row['vendorID'] ? number_format($row['vendorBudgetAmount'], 2) : number_format($row['userBudgetAmount'], 2); ?></td>
                                <td>
                                    <?php if ($row['fname']) { ?>
                                        <a href="edit-resource-plan.php?resourceID=<?= $row['resourceID']; ?>&projectID=<?= $projectID; ?>" class="btn btn-warning btn-sm">Edit</a>
                                        <a href="delete-resource-plan.php?resourceID=<?= $row['resourceID']; ?>&projectID=<?= $projectID; ?>" class="btn btn-danger">Delete</a>
                                    <?php } else { ?>
                                        <a href="edit-vendor-details.php?vendorID=<?= $row['vendorID']; ?>&projectID=<?= $projectID; ?>" class="btn btn-warning btn-sm">Edit</a>
                                        <a href="delete-vendor-details.php?vendorID=<?= $row['vendorID']; ?>&projectID=<?= $projectID; ?>" class="btn btn-danger">Delete</a>
                                    <?php } ?>
                                </td>
                                </tr>
                            <?php
                            }

                            // Final row with totals for the last milestone group
                            if ($previousMilestone !== null) {
                                echo '<tr>';
                                echo '<td colspan="3" style="text-align: center;"><strong>Total</strong></td>';
                                echo '<td></td>';
                                echo '<td><strong>' . number_format($totalDaysBudgeted, 2) . '</strong></td>';
                                echo '<td><strong>' . number_format($totalBudgetAmount, 2) . '</strong></td>';
                                echo '<td></td>';
                                echo '</tr>';
                            }

                            // Grand total across all milestones
                            $grandTotalDaysBudgeted = 0;
                            $grandTotalBudgetAmount = 0;

                            // Calculate grand totals
                            mysqli_data_seek($resourcePlanResult, 0); // Reset pointer to start of result set
                            while ($row = mysqli_fetch_assoc($resourcePlanResult)) {
                                $grandTotalDaysBudgeted += ($row['vendorID'] ? 0 : $row['daysBudgeted']);
                                $grandTotalBudgetAmount += ($row['vendorID'] ? $row['vendorBudgetAmount'] : $row['userBudgetAmount']);
                            }
                            ?>
                            <tr>
                                <td colspan="3" style="text-align: center;"><strong>Grand Total</strong></td>
                                <td></td>
                                <td><strong><?= number_format($grandTotalDaysBudgeted, 2); ?></strong></td>
                                <td><strong><?= number_format($grandTotalBudgetAmount, 2); ?></strong></td>
                                <td></td>
                            </tr>
                        </tbody>
                    </table>
                <?php
                }

                // Calculate project contribution and contribution percentage
                $contractValue = $projectData['contractValue'];
                $grandTotal = $grandTotalBudgetAmount;
                $projectContribution = $contractValue - $grandTotal;
                $contributionPercentage = ($contractValue > 0) ? ($projectContribution / $contractValue) * 100 : 0;
                ?>
                <div class="mt-4">
                    <table class="table table-bordered table-hover align-middle text-center table-responsive" style="border: 2px solid black;">
                        <thead style="background-color: #ccc;">
                            <tr>
                                <th>Contract Value</th>
                                <th>Grand Total</th>
                                <th>Project Contribution</th>
                                <th>Contribution %age</th>
                            </tr>
                        </thead>
                        <tbody style="background-color: #f4f4f4; font-weight: 600;">
                            <tr>
                                <td>₹<?= number_format($contractValue, 2); ?></td>
                                <td>₹<?= number_format($grandTotal, 2); ?></td>
                                <td>₹<?= number_format($projectContribution, 2); ?></td>
                                <td><?= number_format($contributionPercentage, 2); ?>%</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
function exportTableToExcel(filename, tableId) {
    // Get the table element by ID
    let table = document.getElementById(tableId);
    
    // Create a new table to hold the data for Excel export
    let newTable = table.cloneNode(true);  // Clone the table (this prevents altering the original table)
    
    // Loop through each row and remove the last column (Action column) from the clone
    let rows = newTable.rows;
    for (let i = 0; i < rows.length; i++) {
        rows[i].deleteCell(rows[i].cells.length - 1); // Remove the last column (Action column)
    }

    // Convert the modified cloned table (without the Action column) to Excel workbook format
    let wb = XLSX.utils.table_to_book(newTable, { sheet: "Sheet 1" });

    // Generate and download the Excel file with the specified filename
    XLSX.writeFile(wb, filename);
}
</script>

<?php include('includes/footer.php'); ?>