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

if (isset($_POST['query'])) {
    $query = $_POST['query'];
    $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',
            'Budgeted Days',
            'Actual Days',
            'Variance (Days)',
            'Budgeted 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;

        while ($row = mysqli_fetch_assoc($result)) {
            if ($current_milestone !== '' && $current_milestone !== $row['milestoneName']) {
                // Add milestone totals to CSV
                $csv_data[] = [
                    'Total for ' . $current_milestone,
                    '', // No milestone value in the total row
                    '',
                    '',
                    round($milestone_totals['budgeted_days'], 2),
                    round($milestone_totals['actual_days'], 2),
                    round($milestone_totals['budgeted_days'] - $milestone_totals['actual_days'], 2),
                    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
                $milestone_totals = [
                    'budgeted_days' => 0,
                    'actual_days' => 0,
                    'budgeted_amount' => 0,
                    'actual_amount' => 0
                ];
            }

            $current_milestone = $row['milestoneName'];

            // Calculate amounts
            if ($row['vendorName']) {
                $budgeted_amount = $row['vendorBudgetAmount'];
                $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;

            $csv_data[] = [
                $row['milestoneName'],
                $row['milestoneValue'],
                $row['fname'] ? $row['fname'] . ' ' . $row['lname'] : $row['vendorName'],
                number_format($row['perCompletion'], 2) . '%',
                $row['vendorName'] ? '' : round($row['daysBudgeted'], 2),
                $row['vendorName'] ? '' : round($row['actual_days'], 2),
                $row['vendorName'] ? '' : round($variance_days, 2),
                number_format($budgeted_amount, 2),
                number_format($actual_amount, 2),
                number_format($variance_amount, 2)
            ];

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

            // Update project totals
            $total_budgeted_days_project += $row['vendorName'] ? 0 : $row['daysBudgeted'];
            $total_actual_days_project += $row['vendorName'] ? 0 : $row['actual_days'];
            $total_budgeted_amount_project += $budgeted_amount;
            $total_actual_amount_project += $actual_amount;
        }

        // Add last milestone totals to CSV
        if ($current_milestone !== '') {
            $csv_data[] = [
                'Total for ' . $current_milestone,
                '', // No milestone value in the total row
                '',
                '',
                round($milestone_totals['budgeted_days'], 2),
                round($milestone_totals['actual_days'], 2),
                round($milestone_totals['budgeted_days'] - $milestone_totals['actual_days'], 2),
                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)
            ];
        }

        // Add project totals to CSV
        $csv_data[] = [
            'Project Total',
            '', // No milestone value in the project total row
            '',
            '',
            round($total_budgeted_days_project, 2),
            round($total_actual_days_project, 2),
            round($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
        $csv_data[] = [
            '', // Empty row for spacing
            '', '', '', '', '', '', '', '', ''
        ];

        $csv_data[] = [
            'MIS Income',
            number_format($total_mis_income, 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>';
}
?>