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

// Calculate the date for March
$currentYear = date('Y');
$marchDate = $currentYear . '-03-01';

// Define an array to map month numbers to month names
$monthNames = [
    '01' => 'January',
    '02' => 'February',
    '03' => 'March',
    '04' => 'April',
    '05' => 'May',
    '06' => 'June',
    '07' => 'July',
    '08' => 'August',
    '09' => 'September',
    '10' => 'October',
    '11' => 'November',
    '12' => 'December',
];

// Check if the form was submitted
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    // Initialize an array to store the perCompletion data
    $perCompletionData = [];

    // Loop through the POST data to collect perCompletion data
    foreach ($_POST["milestoneID"] as $index => $milestoneID) {
        $perCompletionJuly = $_POST["perCompletionJuly"][$index];

        // Only process rows where perCompletionAugust is provided
        if (!empty($perCompletionJuly)) {
            // Fetch the milestoneValue from the database for the current milestoneID
            $milestoneValueQuery = "SELECT milestoneValue FROM milestones WHERE milestoneID = ?";
            $stmtValue = mysqli_prepare($conn, $milestoneValueQuery);

            if ($stmtValue) {
                mysqli_stmt_bind_param($stmtValue, 'i', $milestoneID);
                mysqli_stmt_execute($stmtValue);
                mysqli_stmt_bind_result($stmtValue, $milestoneValue);
                mysqli_stmt_fetch($stmtValue);
                mysqli_stmt_close($stmtValue);

                // Calculate milestoneAmount
                $milestoneAmount = ($perCompletionJuly / 100) * $milestoneValue;

                // Store data for later insertion
                $perCompletionData[] = [
                    'milestoneID' => $milestoneID,
                    'perCompletionJuly' => $perCompletionJuly,
                    'milestoneAmount' => $milestoneAmount,
                ];
            }
        }
    }

    // Insert perCompletion data into the database for August
    $julyDate = $currentYear . '-07-01';
    $insertQuery = "INSERT INTO perCompletion (milestoneID, date, perCompletion, milestoneAmount) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE perCompletion = VALUES(perCompletion), milestoneAmount = VALUES(milestoneAmount)";
    $stmt = mysqli_prepare($conn, $insertQuery);

    if ($stmt) {
        foreach ($perCompletionData as $data) {
            $milestoneID = $data['milestoneID'];
            $perCompletionJuly = $data['perCompletionJuly'];
            $milestoneAmount = $data['milestoneAmount'];

            mysqli_stmt_bind_param($stmt, 'isdd', $milestoneID, $julyDate, $perCompletionJuly, $milestoneAmount);
            mysqli_stmt_execute($stmt);
        }

        mysqli_stmt_close($stmt);
        echo "Data for July saved successfully!";
    } else {
        echo "Error: " . mysqli_error($conn);
    }
}

// Rest of your code for fetching and displaying data
$query = "SELECT clients.clientName, projects.projectName, milestones.milestoneName,
          milestones.milestoneID,milestones.milestoneValue,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-03' THEN perCompletion END) AS perCompletionMarch,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-04' THEN perCompletion END) AS perCompletionApril,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-05' THEN perCompletion END) AS perCompletionMay,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-06' THEN perCompletion END) AS perCompletionJune,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-07' THEN perCompletion END) AS perCompletionJuly,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-08' THEN perCompletion END) AS perCompletionAugust,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$currentYear-07' THEN milestoneAmount END) AS milestoneAmountJuly
          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-03', '$currentYear-04', '$currentYear-05', '$currentYear-06', '$currentYear-07', '$currentYear-08')
          GROUP BY milestones.milestoneID
          ORDER BY clients.clientName ASC";

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

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

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Percentage Completion</title>
</head>
<body>
    <h1>Percentage Completion</h1>
    <button id="download-csv" class="btn btn-primary">Download CSV</button>

    <form method="post">
        <table class="table table-bordered table-striped">
            <thead>
                <tr>
                    <th>Client Name</th>
                    <th>Project Name</th>
                    <th>Milestone Name</th>
                    <th>Milestone Value</th>
                    <th>Per Completion in March</th>
                    <th>Per Completion in April</th>
                    <th>Per Completion in July</th>
                    <th>Per Completion in August</th>
                    <th>Enter Per Completion in July</th>
                </tr>
            </thead>
            <tbody>
                <?php
                while ($row = mysqli_fetch_assoc($result)) {
                                        if ($row['perCompletionMarch'] == 100) {
                        continue;
                    }

                    echo "<tr>";
                    echo "<td>{$row['clientName']}</td>";
                    echo "<td>{$row['projectName']}</td>";
                    echo "<td>{$row['milestoneName']}</td>";
                    echo "<td>{$row['milestoneValue']}</td>";
                    echo "<td>{$row['perCompletionMarch']}</td>";
                    echo "<td>{$row['perCompletionApril']}</td>";
                    echo "<td>{$row['perCompletionJuly']}</td>";
                    echo "<td>{$row['perCompletionAugust']}</td>";
                    echo "<input type='hidden' name='milestoneID[]' value='{$row['milestoneID']}'>";
                    echo "<td><input type='number' name='perCompletionJuly[]' step='0.01'></td>";
                    echo "</tr>";
                }
                ?>
            </tbody>
        </table>
        <button type="submit" class="btn btn-primary">Save Per Completion for July</button>
    </form>
    
    <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 = 'percent_completion.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>
</body>
</html>