<?php
include('includes/header.php'); // Include your database connection file

// Get the current date
$today = new DateTime();
$currentMonth = (int)$today->format('m');
$currentYear = (int)$today->format('Y');

// Determine the report month (previous month)
$reportMonth = $currentMonth - 1;
$reportYear = $currentYear;
if ($reportMonth == 0) {
    $reportMonth = 12;
    $reportYear -= 1;
}

$twoMonthsPrior = $reportMonth - 1;
$twoMonthsPriorYear = $reportYear;  // Store correct year separately
if ($twoMonthsPrior == 0) {
    $twoMonthsPrior = 12;
    $twoMonthsPriorYear -= 1; // Only decrement the year for two months prior
}

// Determine financial year based on the reporting month
$financialYearStart = $reportMonth >= 4 ? $reportYear : $reportYear - 1;
$marchYear = $financialYearStart;

// Format the months as two digits
$reportMonthFormatted = str_pad($reportMonth, 2, '0', STR_PAD_LEFT);
$twoMonthsPriorFormatted = str_pad($twoMonthsPrior, 2, '0', STR_PAD_LEFT);

// Get month names for display
$reportMonthName = date('F', mktime(0, 0, 0, $reportMonth, 1, $reportYear));
$priorMonthName = date('F', mktime(0, 0, 0, $twoMonthsPrior, 1, $twoMonthsPriorYear));

// 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: misIncome.php");
    exit();
}

// Modified query to also fetch March data for the current financial year
$query = "SELECT clients.clientName, projects.projectName, milestones.milestoneName, milestones.milestoneValue,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$marchYear-03' THEN perCompletion END) AS perCompletionMarch,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$twoMonthsPriorYear-$twoMonthsPriorFormatted' THEN perCompletion END) AS perCompletionPrevMonth,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$reportYear-$reportMonthFormatted' THEN perCompletion END) AS perCompletionReportMonth
          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
          WHERE DATE_FORMAT(date, '%Y-%m') IN ('$marchYear-03', '$twoMonthsPriorYear-$twoMonthsPriorFormatted', '$reportYear-$reportMonthFormatted')
          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 and variables
$projectTotalIncome = array();
$projectIncomeTotal = 0;
$misIncome = 0;
$currentClient = "";
$currentProject = "";

// Add these arrays to track projects and their milestone completion status
$projectMilestones = [];
$completedProjects = [];

// First pass: identify which projects have all milestones at 100% in March
$result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_assoc($result)) {
    $projectKey = $row['clientName'] . "||" . $row['projectName'];
    $perCompletionMarch = $row['perCompletionMarch'] ?? 0;
    
    // Initialize tracking arrays for new projects
    if (!isset($projectMilestones[$projectKey])) {
        $projectMilestones[$projectKey] = 0;
        $completedProjects[$projectKey] = true;
    }
    
    // Count this milestone
    $projectMilestones[$projectKey]++;
    
    // If any milestone is not 100% complete, mark the project as incomplete
    if ($perCompletionMarch < 100) {
        $completedProjects[$projectKey] = false;
    }
}

// Reset the result set for the main processing
mysqli_data_seek($result, 0);

// Output HTML table headers
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>MIS Income - Month</title>";
echo "<button id='download-excel' class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download Excel</button>";
echo "</head>";
echo "<body>";
echo "<h1>MIS Income - Month</h1>";
echo "<h3>Reporting Period: $reportMonthName $reportYear (Compared to $priorMonthName $twoMonthsPriorYear)</h3>";
echo "<table class='table table-bordered table-striped'>";
echo "<thead>";
echo "<tr>";
echo "<th>Client Name</th>";
echo "<th>Project Name</th>";
echo "<th>Milestone Name</th>";
echo "<th>Milestone Value</th>";
echo "<th>$priorMonthName Completion %</th>";
echo "<th>$reportMonthName Completion %</th>";
echo "<th>Milestone Income</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";

// Iterate through the query results
while ($row = mysqli_fetch_assoc($result)) {
    $clientName = $row['clientName'];
    $projectName = $row['projectName'];
    $projectKey = $clientName . "||" . $projectName;
    
    // Skip this milestone if it's in a project where all milestones were 100% in March
    if ($completedProjects[$projectKey]) {
        continue;
    }
    
    // Skip milestones that were already 100% complete in March
    if ($row['perCompletionMarch'] == 100) {
        continue;
    }
    
    $milestoneName = $row['milestoneName'];
    $milestoneValue = $row['milestoneValue'];
    $perCompletionPrevMonth = $row['perCompletionPrevMonth'] ?? 0;
    $perCompletionReportMonth = $row['perCompletionReportMonth'] ?? $perCompletionPrevMonth;

    // Calculate milestoneIncome - ensure it's never negative
    $completionDifference = max(0, $perCompletionReportMonth - $perCompletionPrevMonth);
    $milestoneIncome = $milestoneValue * ($completionDifference / 100);

    // Check if the client or project has changed
    if ($currentClient !== $clientName || $currentProject !== $projectName) {
        // Output the project income total and reset it for the new client or project
        if ($currentClient !== "" && $currentProject !== "") {
            echo "<tr>";
            echo "<td colspan='6' style='text-align: right;'><b>Total Project Income:</b></td>";
            echo "<td><b>" . number_format($projectIncomeTotal, 2) . "</b></td>";
            echo "</tr>";
            $projectTotalIncome[$currentClient . "||" . $currentProject] = $projectIncomeTotal;
        }

        // Output a new client header if the client has changed
        if ($currentClient !== $clientName) {
            echo "<tr>";
            echo "<td colspan='7' style='background-color: #eee;'><b>Client: $clientName</b></td>";
            echo "</tr>";
        }

        // Output a new project header if the project has changed
        if ($currentProject !== $projectName) {
            echo "<tr>";
            echo "<td colspan='7' style='background-color: #ccc;'><b>Project: $projectName</b></td>";
            echo "</tr>";
        }

        // Reset the project income total for the new client or project
        $projectIncomeTotal = 0;

        // Update the current client and project
        $currentClient = $clientName;
        $currentProject = $projectName;
    }

    // Only display rows where there's actual income
    if ($completionDifference > 0) {
        // Output the milestone data
        echo "<tr>";
        echo "<td>$clientName</td>";
        echo "<td>$projectName</td>";
        echo "<td>$milestoneName</td>";
        echo "<td>" . number_format($milestoneValue, 2) . "</td>";
        echo "<td>" . number_format($perCompletionPrevMonth, 2) . "%</td>";
        echo "<td>" . number_format($perCompletionReportMonth, 2) . "%</td>";
        echo "<td>" . number_format($milestoneIncome, 2) . "</td>";
        echo "</tr>";
    }

    // Accumulate the milestoneIncome into the projectIncome total for the current project
    $projectIncomeTotal += $milestoneIncome;
}

// Output the last project's income total
if ($currentClient !== "" && $currentProject !== "") {
    $projectTotalIncome[$currentClient . "||" . $currentProject] = $projectIncomeTotal;

    echo "<tr>";
    echo "<td colspan='6' style='text-align: right;'><b>Total Project Income:</b></td>";
    echo "<td><b>" . number_format($projectIncomeTotal, 2) . "</b></td>";
    echo "</tr>";
}

// Calculate misIncome as the sum of all projectIncome
$misIncome = array_sum($projectTotalIncome);

echo "<tr>";
echo "<td colspan='6' style='text-align: right;'><b>Total MIS Income:</b></td>";
echo "<td><b>" . number_format($misIncome, 2) . "</b></td>";
echo "</tr>";

// Store the calculated MIS income in the database
$insertQuery = "INSERT INTO mis_income_monthly (month, monthly_mis_income) VALUES ('$reportYear-$reportMonthFormatted-01', '$misIncome')
                ON DUPLICATE KEY UPDATE monthly_mis_income = '$misIncome'";

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

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

// Close the HTML table
echo "</tbody>";
echo "</table>";

// Sort the $projectTotalIncome array in descending order
arsort($projectTotalIncome);

echo "<h2>Total Project Income</h2>";
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>Total Income</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";

$serialNumber = 1;

foreach ($projectTotalIncome as $key => $totalIncome) {
    // Skip projects with zero income
    if ($totalIncome <= 0) continue;
    
    list($clientName, $projectName) = explode("||", $key); // Extract Client & Project Names
    
    echo "<tr>";
    echo "<td>$serialNumber</td>"; // Serial Number
    echo "<td>$clientName</td>"; // Client Name
    echo "<td>$projectName</td>"; // Project Name
    echo "<td>" . number_format($totalIncome, 2) . "</td>"; // Total Income
    echo "</tr>";

    $serialNumber++; // Increment the S.No.
}

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

echo "</body>";
echo "</html>";

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

<script>
    function downloadExcel() {
        const table = document.querySelector('.table'); // Select the table
        const wb = XLSX.utils.table_to_book(table, { sheet: "MIS Income Month" }); // Convert table to Excel workbook
        
        // Generate the Excel file and trigger the download
        XLSX.writeFile(wb, 'MIS Income - <?php echo $reportMonthName . "_" . $reportYear; ?>.xlsx');
    }
    
    // Add event listener to the "Download Excel" button
    document.getElementById('download-excel').addEventListener('click', downloadExcel);
</script>