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

if (isset($_POST['query']) && isset($_POST['projectID'])) {
    $query = $_POST['query'];
    $projectID = intval($_POST['projectID']);  // Safely cast projectID to an integer
    
    $result = mysqli_query($conn, $query);

    if ($result) {
        $csv_data = [];

        // Column headers for the main report
        $csv_data[] = [
            'Project Milestone',
            'Milestone Value',
            'Staff/Vendor Name',
            'Percent Completed',
            'Total Days As Per RP',
            'Actual Days',
            'Balance Days In RP',
            'Pro Rata Budget Amount*',
            'Actual Amount',
            'Variance (Amount)'
        ];

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

        $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;

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

        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'];

            // Calculate milestone income only once per milestone
            if ($current_milestone !== $milestone_name) {
                $current_milestone = $milestone_name;

                // Calculate the income for this milestone
                $milestone_income = $row['milestoneValue'] * ($row['perCompletion'] / 100);

                // Accumulate MIS Income (only once per milestone)
                $total_milestone_income_project += $milestone_income;
            }

            // 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 DL (Direct Labor) total
            $total_dl += $row['employeeRate'] * ($row['actual_days']);

            // Fetch DM (Direct Material) from Projects table based on priority logic
            if ($index === 0) {
                $query_dm = "SELECT 
                                CASE 
                                    WHEN p.directMaterialcost > 0 THEN p.directMaterialcost + COALESCE(p.dmPrevYear, 0)
                                    WHEN p.directMaterialcost = 0 AND p.directMaterialcostPrev > 0 THEN p.directMaterialcostPrev + 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);
                if (!$result_dm) {
                    die('Error fetching DM: ' . mysqli_error($conn));
                }
                $row_dm = mysqli_fetch_assoc($result_dm);
                $total_dm = $row_dm['dm'] ?? 0;
            }

            // 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;

            // Row for the current milestone entry
            $csv_data[] = [
                $milestone_name,
                number_format($row['milestoneValue'], 2),
                $row['vendorName'] ?: $row['fname'] . ' ' . $row['lname'],
                number_format($row['perCompletion'], 2) . '%',
                $row['vendorName'] ? '' : number_format($row['daysBudgeted'], 2),
                $row['vendorName'] ? '' : number_format($row['actual_days'], 2),
                $row['vendorName'] ? '' : number_format($variance_days, 2),
                number_format($budgeted_amount, 2),
                number_format($actual_amount, 2),
                number_format($variance_amount, 2)
            ];

            // Check if the next row is a different milestone or this is the last row
            if (!isset($rows[$index + 1]) || $rows[$index + 1]['milestoneName'] !== $milestone_name) {
                // Milestone total row
                $csv_data[] = [
                    'Total for ' . $milestone_name,
                    '', '', '', // No milestone value or name for the total row
                    '', '', '',
                    number_format($milestone_totals['budgeted_amount'], 2),
                    number_format($milestone_totals['actual_amount'], 2),
                    number_format($milestone_totals['budgeted_amount'] - $milestone_totals['actual_amount'], 2)
                ];

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

        // Project total row
        $csv_data[] = [
            'Project Total',
            '', '', '', // No milestone value or name for the total row
            number_format($total_budgeted_days_project, 2),
            number_format($total_actual_days_project, 2),
            number_format($total_budgeted_days_project - $total_actual_days_project, 2),
            number_format($total_budgeted_amount_project, 2),
            number_format($total_actual_amount_project, 2),
            number_format($total_budgeted_amount_project - $total_actual_amount_project, 2)
        ];

        // Additional table for MIS Income, Total DL, Total DM, Project Contribution, and Contribution Percentage
        $total_project_contribution = $total_milestone_income_project - $total_dl - $total_dm;
        $contribution_percentage = ($total_project_contribution / $total_milestone_income_project) * 100;

        $csv_data[] = [
            '', // Empty row for spacing
            '', '', '', '', '', '', '', '', ''
        ];

        $csv_data[] = [
            'MIS Income',
            number_format($total_milestone_income_project, 2),
            'Total DL',
            number_format($total_dl, 2),
            'Total DM',
            number_format($total_dm, 2),
            'Project Contribution',
            number_format($total_project_contribution, 2),
            'Contribution %age',
            number_format($contribution_percentage, 2) . '%'
        ];

        // Output CSV
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment;filename=costing_report.csv');

        $output = fopen('php://output', 'w');
        foreach ($csv_data as $row) {
            fputcsv($output, $row);
        }
        fclose($output);
        exit();
    } else {
        echo '<div class="alert alert-danger">Query failed: ' . mysqli_error($conn) . '</div>';
    }
} else {
    echo '<div class="alert alert-danger">Invalid request.</div>';
}