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

// Calculate the date for April and Sept
$currentYear = date('Y');
$augustDate = $currentYear . '-08-01';
$septDate = $currentYear . '-09-01';

// 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();
}

// Query to fetch milestone data for August and Sept, including client name
$query = "SELECT clients.clientName, projects.projectName, milestones.milestoneName, milestones.milestoneValue,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-08' THEN perCompletion END) AS perCompletionAugust,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-09' THEN perCompletion END) AS perCompletionSept
          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 ('$currentYear-08', '$currentYear-09')
          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 variables to store income totals
$projectIncome = 0;
$misIncome = 0;

// Initialize variables to keep track of the current client and project
$currentClient = "";
$currentProject = "";
$projectIncomeTotal = 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</title>";
echo "<button id='download-csv' class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download CSV</button>";
echo "</head>";
echo "<body>";
echo "<h1>MIS Income</h1>";
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>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'];
    $milestoneName = $row['milestoneName'];
    $milestoneValue = $row['milestoneValue'];
    $perCompletionAugust = $row['perCompletionAugust'];
    $perCompletionSept = $row['perCompletionSept'];

    // Calculate milestoneIncome for the current milestone (August - Sept)
    $milestoneIncome = $milestoneValue * (($perCompletionSept - $perCompletionAugust) / 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='4' style='text-align: right;'><b>Total Project Income:</b></td>";
            echo "<td><b>$projectIncomeTotal</b></td>";
            echo "</tr>";
            $projectTotalIncome[$currentProject] = $projectIncomeTotal;
        }

        // Output a new client header if the client has changed
        if ($currentClient !== $clientName) {
            echo "<tr>";
            echo "<td colspan='5' 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='5' 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>$milestoneValue</td>";
    echo "<td>$milestoneIncome</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[$currentProject] = $projectIncomeTotal;

    echo "<tr>";
    echo "<td colspan='4' style='text-align: right;'><b>Total Project Income:</b></td>";
    echo "<td><b>$projectIncomeTotal</b></td>";
    echo "</tr>";
}

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

echo "<tr>";
echo "<td colspan='4' style='text-align: right;'><b>Total MIS Income:</b></td>";
echo "<td><b>$misIncome</b></td>";
echo "</tr>";

// Store the calculated MIS income in the database
$insertQuery = "INSERT INTO mis_income_monthly (month, monthly_mis_income) VALUES ('$currentYear-09-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>Project Name</th>";
echo "<th>Total Income</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";

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

foreach ($projectTotalIncome as $projectName => $totalIncome) {
    echo "<tr>";
    echo "<td>$serialNumber</td>"; // Output the S.No.
    echo "<td>$projectName</td>";
    echo "<td>$totalIncome</td>";
    echo "</tr>";

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

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

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

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

<script>
        // Function to trigger CSV download
        function downloadCSV() {
            const table = document.querySelector('.table');
            const rows = table.querySelectorAll('tr');
            const csv = [];

            // Generate CSV content
            rows.forEach((row, rowIndex) => {
                const rowData = [];
                row.querySelectorAll('td, th').forEach((cell, cellIndex) => {
                    // Wrap cell data in double quotes and escape existing double quotes
                    const cellData = cell.textContent.replace(/"/g, '""');
                    rowData.push(`"${cellData}"`);
                });

                // Include the header row if it's the first row
                if (rowIndex === 0) {
                    csv.push(rowData.join(','));
                } else {
                    csv.push(rowData.join(','));
                }
            });

            // Create a blob containing the CSV data
            const blob = new Blob([csv.join('\n')], { type: 'text/csv' });

            // Create a download link and trigger the download
            const link = document.createElement('a');
            link.href = URL.createObjectURL(blob);
            link.download = 'mis_income.csv';
            link.style.display = 'none';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }

        // Add click event listener to the download button
        document.getElementById('download-csv').addEventListener('click', downloadCSV);
    </script>