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

// Determine the current date
$today = new DateTime();
$currentDateTime = $today->format('Y-m-d H:i:s');
$currentMonth = (int)$today->format('m');
$currentYear = (int)$today->format('Y');

// Determine the reporting month (previous month)
$reportingMonth = $currentMonth - 1;
$reportingYear = $currentYear;
if ($reportingMonth == 0) {
    $reportingMonth = 12;
    $reportingYear -= 1;
}

// Now determine the financial year *based on the reporting month*, not the current month
$financialYearStart = $reportingMonth >= 4 ? $reportingYear : $reportingYear - 1;
$financialYearEnd = $financialYearStart + 1;

// Format the reporting month for date strings
$reportingMonthStr = str_pad($reportingMonth, 2, '0', STR_PAD_LEFT);

// Define the dates for data fetching
$marchDate = "$financialYearStart-03-01";
$reportingDate = "$reportingYear-$reportingMonthStr-01";
$aprilDate = "$financialYearStart-04-01";

// Query to fetch milestone data for the required months, including client name
$query = "SELECT 
            clients.clientName, 
            projects.projectName, 
            milestones.milestoneName, 
            milestones.milestoneValue,
            MAX(CASE WHEN DATE_FORMAT(pc.date, '%Y-%m-%d') = '$marchDate' THEN pc.perCompletion END) AS perCompletionMarch,
            MAX(CASE WHEN DATE_FORMAT(pc.date, '%Y-%m-%d') = '$reportingDate' THEN pc.perCompletion END) AS perCompletionReport,
            MAX(CASE WHEN DATE_FORMAT(pc.date, '%Y-%m-%d') >= '$aprilDate' AND DATE_FORMAT(pc.date, '%Y-%m-%d') < '$reportingDate' THEN pc.perCompletion END) AS perCompletionFallback
          FROM milestones
          INNER JOIN projects ON milestones.projectID = projects.projectID
          INNER JOIN clients ON projects.clientID = clients.clientID
          LEFT JOIN perCompletion pc ON milestones.milestoneID = pc.milestoneID
          WHERE DATE_FORMAT(pc.date, '%Y-%m-%d') IN ('$marchDate', '$reportingDate')
             OR (DATE_FORMAT(pc.date, '%Y-%m-%d') >= '$aprilDate' AND DATE_FORMAT(pc.date, '%Y-%m-%d') < '$reportingDate')
          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 an associative array to store the total income for each project
$projectTotalIncome = array();

// Initialize an associative array to store the total income for each client
$clientTotalIncome = array();

// 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 - Cumulative</title>";
echo "<button id='download-mis-income' class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download MIS Income</button>";
echo "</head>";
echo "<body>";
echo "<h1>MIS Income - Cumulative</h1>";
echo "<h3>Reporting Period: " . date('F Y', strtotime("$reportingYear-$reportingMonthStr-01")) . "</h3>";
echo "<table class='table table-bordered table-striped mis-income-table'>";
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>March Completion %</th>"; // Added for clarity
echo "<th>Current Completion %</th>"; // Added for clarity
echo "<th>Milestone Income</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";

// Variables to keep track of the current client and project
$currentClient = "";
$currentProject = "";
$projectIncomeTotal = 0;

// Iterate through the query results
while ($row = mysqli_fetch_assoc($result)) {
    $clientName = $row['clientName'];
    $projectName = $row['projectName'];
    $milestoneName = $row['milestoneName'];
    $milestoneValue = $row['milestoneValue'];
    $perCompletionMarch = $row['perCompletionMarch'] ?? 0; // Default to 0 if NULL
    $perCompletionReport = $row['perCompletionReport'];
    $perCompletionFallback = $row['perCompletionFallback'];

    if ($perCompletionMarch == 100) {
        continue; // Skip this iteration and move to the next row
    }

    // Determine the most recent completion percentage available
    // If no data is available after March, use March data (which results in $0 income)
    $finalPerCompletion = $perCompletionReport ?? $perCompletionFallback ?? $perCompletionMarch;

    // Calculate milestoneIncome - ensure it's never negative
    $completionDifference = max(0, $finalPerCompletion - $perCompletionMarch);
    $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;
    }

    // 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($perCompletionMarch, 2) . "%</td>"; // Show March completion
    echo "<td>" . number_format($finalPerCompletion, 2) . "%</td>"; // Show current completion
    echo "<td>" . number_format($milestoneIncome, 2) . "</td>";
    echo "</tr>";

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

    // Accumulate the milestoneIncome into the clientIncome total for the current client
    if (!isset($clientTotalIncome[$clientName])) {
        $clientTotalIncome[$clientName] = 0;
    }
    $clientTotalIncome[$clientName] += $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_cum (month, total_mis_income) VALUES ('$reportingYear-$reportingMonthStr-01', '$misIncome')
                ON DUPLICATE KEY UPDATE total_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"<button id='download-project-income' class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download Project Income</button>";
echo "<table class='table table-bordered table-striped project-income-table'>";
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) {
    if ($totalIncome <= 0) continue; // Skip projects with zero or negative income
    
    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>";

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

// Display the Total Client Income table
echo "<h2>Total Client Income</h2>";
echo "<button id='download-client-income' class='btn btn-success float-end' style='margin-bottom: 10px;'>Download Client Income</button>";
echo "<table class='table table-bordered table-striped client-income-table'>";
echo "<thead>";
echo "<tr>";
echo "<th>S.No.</th>"; // Add S.No. column header
echo "<th>Client Name</th>";
echo "<th>Total Income</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";

$serialNumber = 1;

foreach ($clientTotalIncome as $clientName => $totalIncome) {
    if ($totalIncome <= 0) continue; // Skip clients with zero or negative income
    
    echo "<tr>";
    echo "<td>$serialNumber</td>"; // Output the S.No.
    echo "<td>$clientName</td>";
    echo "<td>" . number_format($totalIncome, 2) . "</td>";
    echo "</tr>";

    $serialNumber++;
}

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

// Debug information - commented out in production
// echo "<div style='margin-top: 20px; font-size: 12px; color: #777;'>";
// echo "March Date: $marchDate<br>";
// echo "April Date: $aprilDate<br>";
// echo "Reporting Date: $reportingDate<br>";
// echo "Current Date/Time: $currentDateTime<br>";
// echo "</div>";

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

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

<script>
    function downloadTable(tableSelector, fileName, sheetName) {
        const table = document.querySelector(tableSelector); // Select the table
        if (!table) {
            alert("Table not found!");
            return;
        }

        const wb = XLSX.utils.book_new(); // Create a new Excel workbook
        const ws = XLSX.utils.table_to_sheet(table); // Convert the selected table to a sheet
        XLSX.utils.book_append_sheet(wb, ws, sheetName); // Add the sheet to the workbook

        // Generate and download the Excel file
        XLSX.writeFile(wb, fileName);
    }

    // Event listener for "Download Project Income" button
    document.getElementById('download-project-income').addEventListener('click', function() {
        downloadTable(".project-income-table", "Total_Project_Income.xlsx", "Total Project Income");
    });

    // Event listener for "Download Client Income" button
    document.getElementById('download-client-income').addEventListener('click', function() {
        downloadTable(".client-income-table", "Total_Client_Income.xlsx", "Total Client Income");
    });

    // Event listener for "Download MIS Income" button
    document.getElementById('download-mis-income').addEventListener('click', function() {
        downloadTable(".mis-income-table", "MIS_Income.xlsx", "MIS Income");
    });
</script>