<style>
/* Make the header sticky */
.sticky-header {
    position: sticky;
    top: 0;
    z-index: 100;
    background-color: #ccc; /* Adjust background color as needed */
}
</style>

<?php
include('../config/dbcon.php');

if (isset($_GET['clientID']) && isset($_GET['projectID'])) {
    $clientID = intval($_GET['clientID']);
    $projectID = intval($_GET['projectID']);

$query = "SELECT * FROM (
                -- First Subquery: Milestones with resource plans
                SELECT
                    m.milestoneID,
                    m.milestoneName,
                    m.milestoneValue,
                    u.fname,
                    u.lname,
                    COALESCE(ROUND(rp.daysBudgeted, 2), 0) as daysBudgeted,
                    COALESCE(ROUND(SUM(t.hrs) / 8, 3), 0) AS actual_days,
                    COALESCE(u.employeeRate, 0) as employeeRate,
                    COALESCE(pc.perCompletion, 0) as perCompletion,
                    v.vendorName,
                    v.budgetAmount AS vendorBudgetAmount,
                    v.actualAmount AS vendorActualAmount
                FROM resourcePlan rp
                LEFT JOIN users u ON rp.userID = u.userID
                LEFT JOIN milestones m ON rp.milestoneID = m.milestoneID
                LEFT JOIN timesheet t ON t.userID = u.userID AND t.milestoneID = m.milestoneID
                LEFT JOIN vendor v ON rp.vendorID = v.vendorID
                LEFT JOIN (
                    SELECT milestoneID, perCompletion
                    FROM perCompletion pc1
                    WHERE pc1.date = (
                        SELECT MAX(date)
                        FROM perCompletion pc2
                        WHERE pc1.milestoneID = pc2.milestoneID
                    )
                ) pc ON m.milestoneID = pc.milestoneID
                WHERE m.projectID = $projectID
                GROUP BY m.milestoneID, m.milestoneName, m.milestoneValue, u.userID, rp.daysBudgeted, u.employeeRate, pc.perCompletion, v.vendorName, v.budgetAmount, v.actualAmount

                UNION

                -- Second Subquery: Milestones with timesheet but no resource plans
                SELECT
                    m.milestoneID,
                    m.milestoneName,
                    m.milestoneValue,
                    u.fname,
                    u.lname,
                    0 as daysBudgeted,
                    COALESCE(ROUND(SUM(t.hrs) / 8, 3), 0) AS actual_days,
                    COALESCE(u.employeeRate, 0) as employeeRate,
                    COALESCE(pc.perCompletion, 0) as perCompletion,
                    v.vendorName,
                    v.budgetAmount AS vendorBudgetAmount,
                    v.actualAmount AS vendorActualAmount
                FROM timesheet t
                LEFT JOIN users u ON t.userID = u.userID
                LEFT JOIN milestones m ON t.milestoneID = m.milestoneID
                LEFT JOIN resourcePlan rp ON u.userID = rp.userID AND m.milestoneID = rp.milestoneID
                LEFT JOIN vendor v ON rp.vendorID = v.vendorID
                LEFT JOIN (
                    SELECT milestoneID, perCompletion
                    FROM perCompletion pc1
                    WHERE pc1.date = (
                        SELECT MAX(date)
                        FROM perCompletion pc2
                        WHERE pc1.milestoneID = pc2.milestoneID
                    )
                ) pc ON m.milestoneID = pc.milestoneID
                WHERE m.projectID = $projectID
                AND rp.resourceID IS NULL
                GROUP BY m.milestoneID, m.milestoneName, m.milestoneValue, u.userID, u.employeeRate, pc.perCompletion, v.vendorName, v.budgetAmount, v.actualAmount

                UNION

                -- Third Subquery: Milestones with no resource plans and no timesheet data
                SELECT
                    m.milestoneID,
                    m.milestoneName,
                    m.milestoneValue,
                    '' AS fname,
                    '' AS lname,
                    0 AS daysBudgeted,
                    0 AS actual_days,
                    0 AS employeeRate,
                    COALESCE(pc.perCompletion, 0) AS perCompletion,  -- Fix here
                    '' AS vendorName,
                    0 AS vendorBudgetAmount,
                    0 AS vendorActualAmount
                FROM milestones m
                LEFT JOIN (
                    SELECT milestoneID, perCompletion
                    FROM perCompletion pc1
                    WHERE pc1.date = (
                        SELECT MAX(date)
                        FROM perCompletion pc2
                        WHERE pc1.milestoneID = pc2.milestoneID
                    )
                ) pc ON m.milestoneID = pc.milestoneID
                LEFT JOIN resourcePlan rp ON m.milestoneID = rp.milestoneID
                LEFT JOIN timesheet t ON m.milestoneID = t.milestoneID
                WHERE m.projectID = $projectID
                AND rp.milestoneID IS NULL
                AND t.milestoneID IS NULL
            ) AS combined
            ORDER BY milestoneID, lname";


    $result = mysqli_query($conn, $query);

    if ($result) {
        // Initialize project totals
        $total_budgeted_days_project = 0;
        $total_actual_days_project = 0;
        $total_budgeted_amount_project = 0;
        $total_actual_amount_project = 0;
        $total_milestone_income_project = 0;
        $total_dl = 0;
        $total_dm = 0;

        $table_html = '<!-- Button to download the table as CSV -->';
        $table_html .= '<form method="post" action="download_csv.php" class="mb-3">';
        $table_html .= '<input type="hidden" name="query" value="' . htmlspecialchars($query) . '">';
        $table_html .= '<input type="hidden" name="projectID" value="' . $projectID . '">';  // Pass projectID
        $table_html .= '<button type="submit" class="btn btn-primary float-end">Download CSV</button>';
        $table_html .= '</form>';

        
        $table_html .= '<table id="costing-table" class="table table-bordered table-hover align-middle text-center table-responsive" style="border: 2px solid black;">';
        $table_html .= '<thead class="align-middle text-center sticky-header" style="background-color: #ccc;">'; // Add sticky-header class here
        $table_html .= '<tr>';
        $table_html .= '<th>Project Milestone</th>';
        $table_html .= '<th>Milestone Value</th>';
        $table_html .= '<th>Staff/Vendor Name</th>';
        $table_html .= '<th>Percent Completed</th>';
        $table_html .= '<th>Total Days as per RP</th>';
        $table_html .= '<th>Actual Days</th>';
        $table_html .= '<th>Balance Days in RP</th>';
        $table_html .= '<th>Pro Rata Budget Amount<span style="color:red">*</span></th>';
        $table_html .= '<th>Actual Amount</th>';
        $table_html .= '<th>Variance</th>';
        $table_html .= '</tr>';
        $table_html .= '</thead>';
        $table_html .= '<tbody style="font-weight: 600;">';

        $current_milestone = '';
        $milestone_income = 0;
        $milestone_totals = [
            'budgeted_days' => 0,
            'actual_days' => 0,
            'budgeted_amount' => 0,
            'actual_amount' => 0
        ];

        $milestone_counts = [];
        $rows = [];

        while ($row = mysqli_fetch_assoc($result)) {
            $milestone_name = $row['milestoneName'];
            if (!isset($milestone_counts[$milestone_name])) {
                $milestone_counts[$milestone_name] = 0;
            }
            $milestone_counts[$milestone_name]++;
            $rows[] = $row;
        }

        foreach ($rows as $index => $row) {
            $milestone_name = $row['milestoneName'];

            if ($current_milestone !== '' && $current_milestone !== $row['milestoneName']) {
                // Output milestone totals
                $table_html .= '<tr class="fw-bold" style="background-color: #e9e9e9; align-middle text-center;">';
                $table_html .= '<td colspan="3">Total for ' . htmlspecialchars($current_milestone) . '</td>';
                $table_html .= '<td>' . number_format($milestone_income, 2) . '</td>';
                $table_html .= '<td>NA</td>';
                $table_html .= '<td>NA</td>';
                $table_html .= '<td>NA</td>';
                $table_html .= '<td>' . number_format($milestone_totals['budgeted_amount'], 2) . '</td>';
                $table_html .= '<td>' . number_format($milestone_totals['actual_amount'], 2) . '</td>';
                $table_html .= '<td>' . number_format($milestone_totals['budgeted_amount'] - $milestone_totals['actual_amount'], 2) . '</td>';
                $table_html .= '</tr>';

                // Accumulate project totals
                $total_milestone_income_project += $milestone_income;

                // Reset milestone totals
                $milestone_totals = [
                    'budgeted_days' => 0,
                    'actual_days' => 0,
                    'budgeted_amount' => 0,
                    'actual_amount' => 0
                ];

                // Reset milestone income
                $milestone_income = 0;
            }

            $current_milestone = $row['milestoneName'];

            // Calculate amounts
            if ($row['vendorName']) {
                $budgeted_amount = $row['vendorBudgetAmount'] * ($row['perCompletion'] / 100);
                $actual_amount = $row['vendorActualAmount'];
                $variance_days = 0; // No days for vendors
            } else {
                $budgeted_amount = $row['daysBudgeted'] * $row['employeeRate'] * ($row['perCompletion'] / 100);
                $actual_amount = $row['actual_days'] * $row['employeeRate'];
                $variance_days = $row['daysBudgeted'] - $row['actual_days'];
            }
            $variance_amount = $budgeted_amount - $actual_amount;

            // Update milestone income
            $milestone_income = $row['milestoneValue'] * ($row['perCompletion'] / 100);

            // Update DL (Direct Labor) total
            $total_dl += $row['employeeRate'] * ($row['actual_days']);

            // Fetch DM (Direct Material) from Projects table based on simplified logic
            $query_dm = "SELECT 
                            CASE 
                                WHEN p.directMaterialcost > 0 THEN p.directMaterialcost + COALESCE(p.dmPrevYear, 0)
                                ELSE COALESCE(p.dmPrevYear, 0)
                            END AS dm 
                         FROM projects p
                         WHERE p.projectID = $projectID";
            
            $result_dm = mysqli_query($conn, $query_dm);
            $row_dm = mysqli_fetch_assoc($result_dm);
            $total_dm = $row_dm['dm'];

            // Update milestone totals
            $milestone_totals['budgeted_days'] += $row['daysBudgeted'];
            $milestone_totals['actual_days'] += $row['actual_days'];
            $milestone_totals['budgeted_amount'] += $budgeted_amount;
            $milestone_totals['actual_amount'] += $actual_amount;

            // Accumulate project totals
            $total_budgeted_days_project += $row['daysBudgeted'];
            $total_actual_days_project += $row['actual_days'];
            $total_budgeted_amount_project += $budgeted_amount;
            $total_actual_amount_project += $actual_amount;

            // Rowspan handling
            $rowspan = $milestone_counts[$milestone_name];

            // Build table rows
            $table_html .= '<tr>';
            if ($index === 0 || $rows[$index - 1]['milestoneName'] !== $milestone_name) {
                $table_html .= '<td rowspan="' . $rowspan . '">' . htmlspecialchars($row['milestoneName']) . '</td>';
                $table_html .= '<td rowspan="' . $rowspan . '">' . number_format($row['milestoneValue'], 2) . '</td>';
            }
            $table_html .= '<td>' . htmlspecialchars(($row['vendorName']) ? $row['vendorName'] : $row['fname'] . ' ' . $row['lname']) . '</td>';
            $table_html .= '<td>' . htmlspecialchars($row['perCompletion']) . '%</td>';
            $table_html .= '<td>' . number_format($row['daysBudgeted'], 2) . '</td>';
            $table_html .= '<td>' . number_format($row['actual_days'], 2) . '</td>';
            $table_html .= '<td>' . number_format($variance_days, 2) . '</td>';
            $table_html .= '<td>' . number_format($budgeted_amount, 2) . '</td>';
            $table_html .= '<td>' . number_format($actual_amount, 2) . '</td>';
            $table_html .= '<td>' . number_format($variance_amount, 2) . '</td>';
            $table_html .= '</tr>';
        }

        // Output the final milestone totals
        if ($current_milestone !== '') {
            $table_html .= '<tr class="fw-bold" style="background-color: #e9e9e9;">';
            $table_html .= '<td colspan="3">Total for ' . htmlspecialchars($current_milestone) . '</td>';
            $table_html .= '<td>' . number_format($milestone_income, 2) . '</td>';
            $table_html .= '<td>NA</td>';
            $table_html .= '<td>NA</td>';
            $table_html .= '<td>NA</td>';
            $table_html .= '<td>' . number_format($milestone_totals['budgeted_amount'], 2) . '</td>';
            $table_html .= '<td>' . number_format($milestone_totals['actual_amount'], 2) . '</td>';
            $table_html .= '<td>' . number_format($milestone_totals['budgeted_amount'] - $milestone_totals['actual_amount'], 2) . '</td>';
            $table_html .= '</tr>';

            // Accumulate project totals
            $total_milestone_income_project += $milestone_income;
        }

        // Output the project totals
        $table_html .= '<tr class="fw-bold" style="background-color: #ccc;">';
        $table_html .= '<td colspan="3">Total for Project</td>';
        $table_html .= '<td>' . number_format($total_milestone_income_project, 2) . '</td>';
        $table_html .= '<td>' . number_format($total_budgeted_days_project, 2) . '</td>';
        $table_html .= '<td>' . number_format($total_actual_days_project, 2) . '</td>';
        $table_html .= '<td>' . number_format($total_budgeted_days_project - $total_actual_days_project, 2) . '</td>';
        $table_html .= '<td>' . number_format($total_budgeted_amount_project, 2) . '</td>';
        $table_html .= '<td>' . number_format($total_actual_amount_project, 2) . '</td>';
        $table_html .= '<td>' . number_format($total_budgeted_amount_project - $total_actual_amount_project, 2) . '</td>';
        $table_html .= '</tr>';

        // Calculate additional totals
        $total_mis_income = $total_milestone_income_project; // Assuming total MIS income is the same as total milestone income
        $total_project_contribution = $total_mis_income - $total_dl - $total_dm;
        $contribution_percentage = ($total_project_contribution / $total_mis_income) * 100;

        $table_html .= '<table class="table table-bordered table-striped" style="border: 2px solid black;">';
        $table_html .= '<thead class="align-middle text-center" style="background-color: #ccc;">';
        $table_html .= '<tr>';
        $table_html .= '<th colspan="2">MIS Income</th>';
        $table_html .= '<th colspan="2">Total DL</th>';
        $table_html .= '<th colspan="2">Total DM</th>';
        $table_html .= '<th colspan="2">Project Contribution</th>';
        $table_html .= '<th colspan="2">Contribution %age</th>';
        $table_html .= '</tr>';
        $table_html .= '</thead>';
        $table_html .= '<tbody class="align-middle text-center" style="font-weight: 600;">';
        $table_html .= '<tr>';
        $table_html .= '<td colspan="2">' . number_format($total_mis_income, 2) . '</td>';
        $table_html .= '<td colspan="2">' . number_format($total_dl, 2) . '</td>';
        $table_html .= '<td colspan="2">' . number_format($total_dm, 2) . '</td>';
        $table_html .= '<td colspan="2">' . number_format($total_project_contribution, 2) . '</td>';
        $table_html .= '<td colspan="2">' . number_format($contribution_percentage, 2) . '%</td>';
        $table_html .= '</tr>';

        $table_html .= '</tbody>';
        $table_html .= '</table>';
        $table_html .= '<h6><span style="color:red">*</span> : Pro Rata Budgeted Amount is based on completion of Milestone</h6>';

        echo $table_html;
    } else {
        echo 'Error: ' . mysqli_error($conn);
    }
}
?>
<script>
// Smooth scroll functionality
window.addEventListener("DOMContentLoaded", function() {
    var tableHeader = document.querySelector('.sticky-header');
    var tableBody = document.querySelector('tbody');

    if (tableBody && tableHeader) {
        tableBody.addEventListener('scroll', function() {
            tableHeader.scrollLeft = tableBody.scrollLeft;
        });
    }
});
</script>