<?php
include('includes/header.php'); // Include your database connection file
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Dynamic date calculations
$currentDate = new DateTime();
$currentYear = (int)$currentDate->format('Y');
$currentMonth = (int)$currentDate->format('m');

// Determine the reporting month (previous month)
$reportingDate = clone $currentDate;
$reportingDate->modify('-1 month');
$reportingMonth = (int)$reportingDate->format('m');
$reportingYear = (int)$reportingDate->format('Y');
$reportingMonthName = date('F', $reportingDate->getTimestamp());
$reportingMonthDateStr = $reportingYear . '-' . str_pad($reportingMonth, 2, '0', STR_PAD_LEFT) . '-01';

// Get reporting month end date (last day of the month)
$reportingMonthEndDate = clone $reportingDate;
$reportingMonthEndDate->modify('last day of this month');
$reportingMonthEnd = $reportingMonthEndDate->format('Y-m-d');

// Determine financial year based on reporting month
$financialYearStart = $reportingMonth >= 4 ? $reportingYear : $reportingYear - 1;
$financialYearEnd = $financialYearStart + 1;

// Set financial year boundaries
$startMonth = $financialYearStart . '-04-01';
$endMonth = $reportingMonthEnd; // End at reporting month instead of fiscal year end

// Format for display
$financialYearLabel = $financialYearStart . '-' . $financialYearEnd;

// End of previous financial year is March of the current calendar year (for April-March fiscal year)
$prevFinYearEnd = $financialYearMonth = 3; // March
$prevFinYearEndStr = "";

if ($reportingMonth >= 4) { // After April (new fiscal year)
    $prevFinYearEndStr = $reportingYear . '-03-01'; // March of current calendar year
} else {
    $prevFinYearEndStr = ($reportingYear - 1) . '-03-01'; // March of previous calendar year
}

// Generate all fallback months between reporting month and March
$fallbackMonths = [];
$fallbackMonthsText = [];
$marchDate = new DateTime($prevFinYearEndStr);

// Start from one month before reporting month
$currentFallbackDate = clone $reportingDate;
$currentFallbackDate->modify('-1 month');

// Add all months between the reporting month and March (inclusive) to fallback months
while ($currentFallbackDate >= $marchDate) {
    $monthStr = $currentFallbackDate->format('Y-m-01');
    $monthDisplay = $currentFallbackDate->format('F Y');
    
    $fallbackMonths[] = $monthStr;
    $fallbackMonthsText[] = $currentFallbackDate->format('F');
    
    // Move to the previous month
    $currentFallbackDate->modify('-1 month');
}

// Check if the form was submitted
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    // Handle form submission here if needed
    // ...

    // Redirect back to the same page after handling the form data
    header("Location: totalProjectIncome.php");
    exit();
}

// Query to retrieve the Total MIS Income for the reporting month from the database
$queryTotal = "SELECT total_mis_income FROM mis_income_cum WHERE month = '$reportingMonthDateStr'";
$resultTotal = mysqli_query($conn, $queryTotal);

if (!$resultTotal) {
    die("Total Query failed: " . mysqli_error($conn));
}

// Fetch the Total MIS Income value
$rowTotal = mysqli_fetch_assoc($resultTotal);
$misIncomeTotal = $rowTotal['total_mis_income'] ?? 0;

// Build SQL query with case statements for each fallback month
$dateFormatCases = "MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT('$prevFinYearEndStr', '%Y-%m') THEN perCompletion END) AS perCompletionPrevYearEnd,
                    MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT('$reportingMonthDateStr', '%Y-%m') THEN perCompletion END) AS perCompletionCurrent";

foreach ($fallbackMonths as $index => $fallbackMonth) {
    $dateFormatCases .= ", MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT('$fallbackMonth', '%Y-%m') THEN perCompletion END) AS perCompletionFallback$index";
}

// Build WHERE clause for all months
$whereClause = "DATE_FORMAT(date, '%Y-%m') IN (DATE_FORMAT('$prevFinYearEndStr', '%Y-%m'), DATE_FORMAT('$reportingMonthDateStr', '%Y-%m')";
foreach ($fallbackMonths as $fallbackMonth) {
    $whereClause .= ", DATE_FORMAT('$fallbackMonth', '%Y-%m')";
}
$whereClause .= ")";

// Query to fetch milestone data for reporting month and all fallback months
$query = "SELECT projects.contractValue, clients.clientName, projects.projectName, milestones.milestoneName, milestones.milestoneValue,
           $dateFormatCases,
           SUM(users.employeeRate * (timesheet.hrs / 8)) AS CumulativeDL
    FROM milestones
    INNER JOIN projects ON milestones.projectID = projects.projectID
    INNER JOIN clients ON projects.clientID = clients.clientID
    LEFT JOIN perCompletion ON milestones.milestoneID = perCompletion.milestoneID
    LEFT JOIN timesheet ON milestones.milestoneID = timesheet.milestoneID
    LEFT JOIN users ON timesheet.userID = users.userID
    WHERE $whereClause
    GROUP BY clients.clientName, projects.projectName, milestones.milestoneName
    ORDER BY clients.clientName ASC, projects.projectName ASC, milestones.milestoneName ASC";

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

if (!$result) {
    die("Query failed: " . mysqli_error($conn));
}

// Initialize arrays to store project data
$projectTotalIncome = [];
$projectClientNames = [];
$fallbackStats = ['current' => 0]; // For tracking fallbacks used
foreach ($fallbackMonthsText as $index => $month) {
    $fallbackStats["fallback_$index"] = 0;
}
$fallbackStats["march"] = 0;

// Fetch and store project income data
while ($row = mysqli_fetch_assoc($result)) {
    $projectName = $row['projectName'];
    $clientName = $row['clientName'];
    $milestoneValue = $row['milestoneValue'];
    $perCompletionPrevYearEnd = $row['perCompletionPrevYearEnd'] ?? 0;
    $perCompletionCurrent = $row['perCompletionCurrent'];
    
    // Get all fallback completion percentages
    $fallbackCompletions = [];
    foreach (range(0, count($fallbackMonths) - 1) as $index) {
        $fallbackCompletions[$index] = $row["perCompletionFallback$index"];
    }

    // Store the clientName for the project
    $projectClientNames[$projectName] = $clientName;

    if ($perCompletionPrevYearEnd != 100) {
        // COMPLETE FALLBACK LOGIC:
        // 1. Try to use current month's completion percentage 
        // 2. If not available, try each fallback month in sequence
        // 3. If all fallbacks are NULL, use March (end of prev financial year)
        
        $finalPerCompletion = null;
        $fallbackUsed = "";
        
        if ($perCompletionCurrent !== null) {
            $finalPerCompletion = $perCompletionCurrent;
            $fallbackUsed = "current";
            $fallbackStats['current']++;
        } else {
            // Try each fallback month in sequence
            $foundFallback = false;
            foreach (range(0, count($fallbackMonths) - 1) as $index) {
                if ($fallbackCompletions[$index] !== null) {
                    $finalPerCompletion = $fallbackCompletions[$index];
                    $fallbackUsed = "fallback_$index";
                    $fallbackStats["fallback_$index"]++;
                    $foundFallback = true;
                    break;
                }
            }
            
            // If no fallback month had data, use March
            if (!$foundFallback) {
                $finalPerCompletion = $perCompletionPrevYearEnd;
                $fallbackUsed = "march";
                $fallbackStats['march']++;
            }
        }
        
        // Ensure we don't calculate negative income
        $completionDifference = max(0, $finalPerCompletion - $perCompletionPrevYearEnd);
        $milestoneIncome = $milestoneValue * ($completionDifference / 100);

        // Accumulate the milestoneIncome into the projectIncome total for the current project
        if (!isset($projectTotalIncome[$projectName])) {
            $projectTotalIncome[$projectName] = 0;
        }

        $projectTotalIncome[$projectName] += $milestoneIncome;
    }
}

// Query to fetch Cumulative Project Direct Labour and Direct Material Cost
$queryCumulative = "SELECT clients.clientName, projects.projectName, 
                          SUM(users.employeeRate * (timesheet.hrs / 8)) AS CumulativeDL, 
                          projects.directMaterialcost AS CumulativeDM,
                          projects.contractValue AS ContractValue
                   FROM timesheet
                   INNER JOIN milestones ON timesheet.milestoneID = milestones.milestoneID
                   INNER JOIN projects ON milestones.projectID = projects.projectID
                   INNER JOIN clients ON projects.clientID = clients.clientID
                   INNER JOIN users ON timesheet.userID = users.userID
                   WHERE timesheet.tentryDate BETWEEN '$startMonth' AND '$endMonth'
                   GROUP BY clients.clientName, projects.projectName
                   ORDER BY CumulativeDL DESC";

$resultCumulative = mysqli_query($conn, $queryCumulative);

if (!$resultCumulative) {
    die("Query failed: " . mysqli_error($conn));
}

// Initialize an associative array to store Cumulative Direct Labour and Direct Material Cost
$projectCumulative = [];

// Fetch and store Cumulative data
while ($rowCumulative = mysqli_fetch_assoc($resultCumulative)) {
    $projectName = $rowCumulative['projectName'];
    $cumulativeDL = $rowCumulative['CumulativeDL'];
    $cumulativeDM = $rowCumulative['CumulativeDM'];
    $contractValue = $rowCumulative['ContractValue'];

    $projectCumulative[$projectName] = [
        'CumulativeDL' => $cumulativeDL,
        'CumulativeDM' => $cumulativeDM,
        'ContractValue' => $contractValue,
    ];
}

// Calculate and store the Total Contribution and Contribution Percentage for each project
$projectTotalContribution = [];

foreach ($projectTotalIncome as $projectName => $totalIncome) {
    if (isset($projectCumulative[$projectName])) {
        $cumulativeDL = $projectCumulative[$projectName]['CumulativeDL'];
        $cumulativeDM = $projectCumulative[$projectName]['CumulativeDM'];
        $totalContribution = $totalIncome - $cumulativeDL - $cumulativeDM;

        if ($totalIncome != 0) {
            $contributionPercentage = ($totalContribution / $totalIncome) * 100;
        } else {
            $contributionPercentage = 0; // or another appropriate value or action
        }

        $projectTotalContribution[$projectName] = [
            'TotalContribution' => $totalContribution,
            'ContributionPercentage' => $contributionPercentage,
        ];
    } else {
        $projectTotalContribution[$projectName] = null;
    }
}

// Sort the projects by Total Contribution in descending order
arsort($projectTotalContribution);

// Initialize variables for totals
$totalContractValue = 0;
$totalProjectIncome = 0;
$totalCumulativeDL = 0;
$totalCumulativeDM = 0;
$totalContribution = 0;
$totalContributionPercentage = 0;
$totalProjects = 0;

foreach ($projectTotalContribution as $projectName => $contributionData) {
    // Accumulate totals for each column
    $totalProjects++;

    if (isset($projectCumulative[$projectName]['ContractValue'])) {
        $totalContractValue += $projectCumulative[$projectName]['ContractValue'];
    }

    $totalProjectIncome += $projectTotalIncome[$projectName];

    if (isset($projectCumulative[$projectName])) {
        $totalCumulativeDL += $projectCumulative[$projectName]['CumulativeDL'];
        $totalCumulativeDM += $projectCumulative[$projectName]['CumulativeDM'];

        if ($contributionData !== null) {
            $totalContribution += $contributionData['TotalContribution'];
            $totalContributionPercentage += $contributionData['ContributionPercentage'];
        }
    }
}

// Get the month name of previous financial year end for display
$prevFinYearEndMonthName = date('F', strtotime($prevFinYearEndStr));
$prevFinYearEndYear = date('Y', strtotime($prevFinYearEndStr));

// Create fallback explanation based on available months
$fallbackExplanation = "When $reportingMonthName data is unavailable, the report falls back to ";

if (count($fallbackMonthsText) > 0) {
    if (count($fallbackMonthsText) == 1) {
        $fallbackExplanation .= $fallbackMonthsText[0];
    } else {
        $lastMonth = array_pop($fallbackMonthsText);
        $fallbackExplanation .= implode(", ", $fallbackMonthsText) . " and " . $lastMonth;
    }
    $fallbackExplanation .= ", and finally to $prevFinYearEndMonthName $prevFinYearEndYear if needed.";
} else {
    $fallbackExplanation .= "$prevFinYearEndMonthName $prevFinYearEndYear if needed.";
}

// Output the Combined Table
echo "<!DOCTYPE html>";
echo "<html lang='en'>";
echo "<head>";
echo "<meta charset='UTF-8'>";
echo "<meta name='viewport' content='width=device-width, initial-scale=1.0'>";
echo "<title>Project Contribution</title>";
echo "<script type='text/javascript' src='https://www.gstatic.com/charts/loader.js'></script>";
echo "</head>";
echo "<body>";
echo "<h1>Project Contribution</h1>";
echo "<h3>Period: April $financialYearStart - $reportingMonthName $reportingYear</h3>";
echo "<p><strong>Fallback Strategy:</strong> $fallbackExplanation</p>";
echo "<button id='download-excel' class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download Excel</button>";
echo "<table class='table table-bordered table-striped'>";
echo "<thead>";
echo "<tr>";
echo "<th>S.No.</th>"; // Add S.No. column header
echo "<th>Client Name</th>";
echo "<th>Project Name</th>";
echo "<th>Contract Value</th>"; // Add Contract Value column header
echo "<th>Project Total Income</th>";
echo "<th>Cumulative Direct Labour</th>";
echo "<th>Cumulative Direct Material Cost</th>";
echo "<th>Total Contribution</th>"; // Add Total Contribution column header
echo "<th>Project Contribution Percentage</th>"; // Add Contribution Percentage column header
echo "</tr>";
echo "</thead>";
echo "<tbody>";

// Initialize a counter for S.No.
$serialNumber = 1;

foreach ($projectTotalContribution as $projectName => $contributionData) {
    echo "<tr>";
    echo "<td>$serialNumber</td>"; // Output the S.No.
    echo "<td>{$projectClientNames[$projectName]}</td>";
    echo "<td>$projectName</td>";

    // Check if Contract Value exists for the project
    if (isset($projectCumulative[$projectName]['ContractValue'])) {
        echo "<td>" . number_format($projectCumulative[$projectName]['ContractValue'], 2) . "</td>";
    } else {
        echo "<td>N/A</td>"; // Output N/A if Contract Value is not available
    }

    echo "<td>" . number_format($projectTotalIncome[$projectName], 2) . "</td>";

    if (isset($projectCumulative[$projectName])) {
        echo "<td>" . number_format($projectCumulative[$projectName]['CumulativeDL'], 2) . "</td>";
        echo "<td>" . number_format($projectCumulative[$projectName]['CumulativeDM'], 2) . "</td>";

        if ($contributionData !== null) {
            echo "<td>" . number_format($contributionData['TotalContribution'], 2) . "</td>";
            echo "<td>" . number_format($contributionData['ContributionPercentage'], 2) . "%</td>";
        } else {
            echo "<td>N/A</td>"; // Output N/A if Contribution Data is not available
            echo "<td>N/A</td>"; // Output N/A if Contribution Data is not available
        }
    } else {
        echo "<td>N/A</td>"; // Output N/A if Cumulative Data is not available
        echo "<td>N/A</td>"; // Output N/A if Cumulative Data is not available
        echo "<td>N/A</td>"; // Output N/A if Contribution Data is not available
        echo "<td>N/A</td>"; // Output N/A if Contribution Data is not available
    }

    echo "</tr>";
    $serialNumber++; // Increment the S.No.
}

// Output the Totals Row
echo "<tr>";
echo "<td colspan='3'><strong>Total</strong></td>";
echo "<td>" . number_format($totalContractValue, 2) . "</td>";
echo "<td>" . number_format($totalProjectIncome, 2) . "</td>";
echo "<td>" . number_format($totalCumulativeDL, 2) . "</td>";
echo "<td>" . number_format($totalCumulativeDM, 2) . "</td>";
echo "<td>" . number_format($totalContribution, 2) . "</td>";
echo "<td>" . number_format($totalContributionPercentage / $totalProjects, 2) . "%</td>";
echo "</tr>";

echo "</tbody>";
echo "</table>";

// JavaScript function to trigger CSV download
echo '
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.1/xlsx.full.min.js"></script>
<script>
    function downloadExcel() {
        const table = document.querySelector(".table"); // Select the table
        const wb = XLSX.utils.table_to_book(table, { sheet: "Project Contribution" }); // Convert table to Excel workbook
        
        // Generate the Excel file and trigger the download
        XLSX.writeFile(wb, "Project_Contribution_' . $reportingMonthName . '_' . $reportingYear . '.xlsx");
    }
    
    // Add event listener to the "Download Excel" button
    document.getElementById("download-excel").addEventListener("click", downloadExcel);
</script>
';

// Close the HTML body and document
echo "</body>";
echo "</html>";

// Close the database connection
mysqli_close($conn);
?>