<?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 = $currentDate->format('Y');
$currentMonth = $currentDate->format('m');

// Calculate entry month (always the previous month from current)
$entryDate = clone $currentDate;
$entryDate->modify('-1 month');
$entryYear = $entryDate->format('Y');
$entryMonth = $entryDate->format('m');
$entryMonthName = date('F', $entryDate->getTimestamp());

// 1. March Previous Financial Year
$marchPrevFY = new DateTime();
if ((int)$currentMonth >= 4 && (int)$currentMonth <= 12) {
    $marchPrevFY->setDate($currentYear, 3, 1);
} else {
    $marchPrevFY->setDate($currentYear - 1, 3, 1);
}
$marchPrevFYYear = $marchPrevFY->format('Y');
$marchPrevFYMonth = $marchPrevFY->format('m');

// 2. April Current Financial Year
$aprilCurrentFY = new DateTime();
if ((int)$currentMonth >= 4 && (int)$currentMonth <= 12) {
    $aprilCurrentFY->setDate($currentYear, 4, 1);
} else {
    $aprilCurrentFY->setDate($currentYear - 1, 4, 1);
}
$aprilCurrentFYYear = $aprilCurrentFY->format('Y');
$aprilCurrentFYMonth = $aprilCurrentFY->format('m');

// 3. Month before entry month
$prevMonth = clone $entryDate;
$prevMonth->modify('-1 month');
$prevMonthYear = $prevMonth->format('Y');
$prevMonthMonth = $prevMonth->format('m');

// Format date strings for database queries
$entryDateString = $entryYear . '-' . $entryMonth . '-01';
$marchPrevFYString = $marchPrevFYYear . '-' . $marchPrevFYMonth;
$aprilCurrentFYString = $aprilCurrentFYYear . '-' . $aprilCurrentFYMonth;
$prevMonthString = $prevMonthYear . '-' . $prevMonthMonth;
$entryDateStringFull = $entryYear . '-' . $entryMonth;

// 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',
];

// Generate all months between April and previous month (excluding both endpoints)
// These are the "additional months" that are not shown as fixed columns
$allMonths = [];
$currentIterDate = clone $aprilCurrentFY;
$currentIterDate->modify('+1 month'); // Start from month after April (May)

// Continue until we reach the previous month (comp3 column) - EXCLUDE prevMonth to avoid duplication
// This ensures we show all months that are not already visible as fixed columns
while ($currentIterDate->format('Y-m') < $prevMonth->format('Y-m')) {
    $allMonths[] = [
        'year' => $currentIterDate->format('Y'),
        'month' => $currentIterDate->format('m'),
        'display' => $currentIterDate->format('F Y'),
        'string' => $currentIterDate->format('Y-m')
    ];
    $currentIterDate->modify('+1 month');
}

// 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) {
        $perCompletionValue = $_POST["perCompletionValue"][$index];

        // Only process rows where perCompletionValue is provided
        if (!empty($perCompletionValue)) {
            // 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 = ($perCompletionValue / 100) * $milestoneValue;

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

    // Insert perCompletion data into the database for the entry month
    $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'];
            $perCompletionValue = $data['perCompletionValue'];
            $milestoneAmount = $data['milestoneAmount'];

            mysqli_stmt_bind_param($stmt, 'isdd', $milestoneID, $entryDateString, $perCompletionValue, $milestoneAmount);
            mysqli_stmt_execute($stmt);
        }

        mysqli_stmt_close($stmt);
        echo "Data for " . $monthNames[$entryMonth] . " saved successfully!";
    } else {
        echo "Error: " . mysqli_error($conn);
    }
    
    // Process additional months data
    $additionalMonthsData = [];
    foreach ($_POST["milestoneID"] as $index => $milestoneID) {
        // Process each additional month
        foreach ($allMonths as $monthIndex => $month) {
            $monthInputName = 'perCompletionMonth' . ($monthIndex + 1);
            if (isset($_POST[$monthInputName]) && !empty($_POST[$monthInputName][$index])) {
                $perCompletionValue = $_POST[$monthInputName][$index];
                
                // Fetch the milestoneValue from the database
                $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 = ($perCompletionValue / 100) * $milestoneValue;
                    
                    // Store data for insertion
                    $additionalMonthsData[] = [
                        'milestoneID' => $milestoneID,
                        'date' => $month['year'] . '-' . $month['month'] . '-01',
                        'perCompletionValue' => $perCompletionValue,
                        'milestoneAmount' => $milestoneAmount,
                        'monthDisplay' => $month['display']
                    ];
                }
            }
        }
    }
    
    // Insert additional months data
    if (!empty($additionalMonthsData)) {
        $stmt = mysqli_prepare($conn, $insertQuery);
        if ($stmt) {
            foreach ($additionalMonthsData as $data) {
                mysqli_stmt_bind_param($stmt, 'isdd', $data['milestoneID'], $data['date'], $data['perCompletionValue'], $data['milestoneAmount']);
                mysqli_stmt_execute($stmt);
            }
            mysqli_stmt_close($stmt);
            echo "<br>Additional months data saved successfully!";
        }
    }
    
    // Process previous month (comp3) data
    $comp3Data = [];
    if (isset($_POST['perCompletionComp3'])) {
        foreach ($_POST["milestoneID"] as $index => $milestoneID) {
            if (!empty($_POST['perCompletionComp3'][$index])) {
                $perCompletionValue = $_POST['perCompletionComp3'][$index];
                
                // Fetch the milestoneValue from the database
                $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 = ($perCompletionValue / 100) * $milestoneValue;
                    
                    // Store data for insertion
                    $comp3Data[] = [
                        'milestoneID' => $milestoneID,
                        'date' => $prevMonthString . '-01',
                        'perCompletionValue' => $perCompletionValue,
                        'milestoneAmount' => $milestoneAmount
                    ];
                }
            }
        }
    }
    
    // Insert comp3 data
    if (!empty($comp3Data)) {
        $stmt = mysqli_prepare($conn, $insertQuery);
        if ($stmt) {
            foreach ($comp3Data as $data) {
                mysqli_stmt_bind_param($stmt, 'isdd', $data['milestoneID'], $data['date'], $data['perCompletionValue'], $data['milestoneAmount']);
                mysqli_stmt_execute($stmt);
            }
            mysqli_stmt_close($stmt);
            echo "<br>Previous month data saved successfully!";
        }
    }
}

// Build the dynamic SQL query with all months
$allMonthStrings = array_map(function($month) { return $month['string']; }, $allMonths);
$allMonthsForQuery = "'" . implode("', '", $allMonthStrings) . "'";

$dynamicCases = "";
foreach ($allMonths as $index => $month) {
    $dynamicCases .= "MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '{$month['string']}' THEN perCompletion END) AS perCompletionMonth" . ($index + 1) . ",\n          ";
}

$query = "SELECT clients.clientName, projects.projectName, milestones.milestoneName,
          milestones.milestoneID, milestones.milestoneValue,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$marchPrevFYString' THEN perCompletion END) AS perCompletionComp1,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$aprilCurrentFYString' THEN perCompletion END) AS perCompletionComp2,
          $dynamicCases
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$prevMonthString' THEN perCompletion END) AS perCompletionComp3,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$entryDateStringFull' THEN perCompletion END) AS perCompletionEntry,
          MAX(CASE WHEN DATE_FORMAT(date, '%Y-%m') = '$entryDateStringFull' THEN milestoneAmount END) AS milestoneAmountEntry
          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 ('$marchPrevFYString', '$aprilCurrentFYString', $allMonthsForQuery, '$prevMonthString', '$entryDateStringFull')
          OR perCompletion.milestoneID IS NULL)
          AND clients.clientID NOT IN (27, 72, 436)
          GROUP BY milestones.milestoneID
          ORDER BY clients.clientName ASC, projects.projectName ASC";

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

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

// Get formatted date strings for display
$comp1Display = date('F Y', strtotime($marchPrevFYYear . '-' . $marchPrevFYMonth . '-01'));
$comp2Display = date('F Y', strtotime($aprilCurrentFYYear . '-' . $aprilCurrentFYMonth . '-01'));
$comp3Display = date('F Y', strtotime($prevMonthYear . '-' . $prevMonthMonth . '-01'));
$entryDisplay = date('F Y', strtotime($entryYear . '-' . $entryMonth . '-01'));
?>

<!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>
    <style>
        .table-container {
            overflow-x: auto;
            margin-left: 0;
            width: calc(100vw - 250px); /* Adjust based on sidebar width */
            max-width: 100%;
        }
        
        .table {
            min-width: 800px;
            white-space: nowrap;
        }
        
        @media (max-width: 768px) {
            .table-container {
                width: 100vw;
                margin-left: -15px;
            }
        }
    </style>
</head>
<body>
    <h1>Percentage Completion for <?php echo $entryDisplay; ?></h1>
    <div class="mb-3">
        <button id="download-csv" class="btn btn-primary">Download CSV</button>
        <button id="toggle-months" class="btn btn-secondary ml-2">Show All Months</button>
    </div>

    <div class="table-container">
        <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 <?php echo $comp1Display; ?></th>
                        <th>Per Completion in <?php echo $comp2Display; ?></th>
                        <?php foreach ($allMonths as $index => $month): ?>
                        <th class="additional-month" style="display: none;">Per Completion in <?php echo $month['display']; ?></th>
                        <?php endforeach; ?>
                        <th>Per Completion in <?php echo $comp3Display; ?></th>
                        <th>Per Completion in <?php echo $entryDisplay; ?></th>
                        <th>Enter Per Completion in <?php echo $entryMonthName; ?></th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    while ($row = mysqli_fetch_assoc($result)) {
                        if ($row['perCompletionComp1'] == 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['perCompletionComp1']}</td>";
                        echo "<td>{$row['perCompletionComp2']}</td>";
                        
                        // Display all additional months with input fields for editing
                        foreach ($allMonths as $index => $month) {
                            $monthColumn = 'perCompletionMonth' . ($index + 1);
                            $inputName = 'perCompletionMonth' . ($index + 1) . '[]';
                            echo "<td class='additional-month' style='display: none;'>";
                            echo "<input type='number' name='{$inputName}' step='0.01' value='{$row[$monthColumn]}' style='width: 80px;'>";
                            echo "</td>";
                        }
                        
                        echo "<td class='comp3-cell'><span class='comp3-display'>{$row['perCompletionComp3']}</span><input type='number' name='perCompletionComp3[]' step='0.01' value='{$row['perCompletionComp3']}' style='width: 80px; display: none;' class='comp3-input'></td>";
                        echo "<td>{$row['perCompletionEntry']}</td>";
                        echo "<input type='hidden' name='milestoneID[]' value='{$row['milestoneID']}'>";
                        echo "<td><input type='number' name='perCompletionValue[]' step='0.01'></td>";
                        echo "</tr>";
                    }
                    ?>
                </tbody>
            </table>
            <button type="submit" class="btn btn-primary">Save Per Completion for <?php echo $entryMonthName; ?></button>
        </form>
    </div>
    
    <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) => {
                    // Skip hidden columns in CSV
                    if (cell.style.display === 'none') {
                        return;
                    }
                    // 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_<?php echo $entryMonthName . "_" . $entryYear; ?>.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);
        
        // Toggle additional months visibility
        let monthsVisible = false;
        document.getElementById('toggle-months').addEventListener('click', function() {
            const additionalMonths = document.querySelectorAll('.additional-month');
            const comp3Displays = document.querySelectorAll('.comp3-display');
            const comp3Inputs = document.querySelectorAll('.comp3-input');
            const button = document.getElementById('toggle-months');
            
            if (monthsVisible) {
                additionalMonths.forEach(element => {
                    element.style.display = 'none';
                });
                // Hide comp3 inputs and show displays
                comp3Displays.forEach(element => {
                    element.style.display = 'inline';
                });
                comp3Inputs.forEach(element => {
                    element.style.display = 'none';
                });
                button.textContent = 'Show All Months';
                monthsVisible = false;
            } else {
                additionalMonths.forEach(element => {
                    element.style.display = 'table-cell';
                });
                // Show comp3 inputs and hide displays
                comp3Displays.forEach(element => {
                    element.style.display = 'none';
                });
                comp3Inputs.forEach(element => {
                    element.style.display = 'inline';
                });
                button.textContent = 'Hide Additional Months';
                monthsVisible = true;
            }
        });
    </script>
</body>
</html>