<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once('includes/load.php'); // Load database connection and functions

// Get the current month in 'm' format (01 to 12)
$current_month = date('m');

// Set default location to 'sby' (Surabaya)
$default_location = 'sby';

// Function to fetch the best buy summary based on quantity and part_number
function get_best_buy_summary($month = null, $location = null) {
    global $db;

    // Prepare the SQL query for the current month and previous month
    $sql = "
        SELECT 
            CASE 
                WHEN loc IS NULL THEN 'Surabaya'  -- Treat NULL as Surabaya
                WHEN loc = 'makassar' THEN 'Makassar'
                WHEN loc = 'bali' THEN 'Bali'
                WHEN loc = 'jakarta' THEN 'Jakarta'
                ELSE loc
            END AS location, 
            part_number, 
            SUM(CASE WHEN MONTH(scan_in) = '{$month}' THEN quantity ELSE 0 END) AS total_quantity,
            SUM(CASE WHEN MONTH(scan_in) = MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)) THEN quantity ELSE 0 END) AS previous_quantity
        FROM 
            awb 
        WHERE 
            scan_in IS NOT NULL 
    ";

    // Add month filter if provided
    if ($month) {
        $sql .= " AND MONTH(scan_in) = '{$month}'";
    }

    // Add location filter if provided
    if ($location) {
        $sql .= " AND CASE 
                        WHEN loc IS NULL THEN 'Surabaya'
                        WHEN loc = 'makassar' THEN 'Makassar'
                        WHEN loc = 'bali' THEN 'Bali'
                        WHEN loc = 'jakarta' THEN 'Jakarta'
                        ELSE loc
                    END = '{$location}'";
    }

    $sql .= "
        GROUP BY 
            location, part_number 
        ORDER BY 
            total_quantity DESC
    ";

    return $db->query($sql);
}

// Handle form submission for month and location selection
$selected_month = isset($_POST['month']) ? $_POST['month'] : $current_month; // Default to current month
$selected_location = isset($_POST['location']) ? $_POST['location'] : $default_location; // Default to Surabaya

// Fetch the summary based on the selected month and location
$summary_records = get_best_buy_summary($selected_month, $selected_location);

// Prepare an array to hold the results with percentage change
$results_with_percentage = [];

// Calculate percentage change for each part number
if ($summary_records && $db->num_rows($summary_records) > 0) {
    while ($row = $db->fetch_assoc($summary_records)) {
        $current_total = $row['total_quantity'];
        $previous_total = $row['previous_quantity'];

        // Calculate percentage change
        $percentage_change = 0;
        if ($previous_total > 0) {
            $percentage_change = (($current_total - $previous_total) / $previous_total) * 100;
        }

        // Store the result
        $results_with_percentage[] = [
            'location' => $row['location'],
            'part_number' => $row['part_number'],
            'total_quantity' => $current_total,
            'previous_quantity' => $previous_total,
            'percentage_change' => round($percentage_change, 2) // Round to 2 decimal places
        ];
    }
}

// Get the list of months for the dropdown
$months = [
    '01' => 'January',
    '02' => 'February',
    '03' => 'March',
    '04' => 'April',
    '05' => 'May',
    '06' => 'June',
    '07' => 'July',
    '08' => 'August',
    '09' => 'September',
    '10' => 'October',
    '11' => 'November',
    '12' => 'December',
];

// Get the list of locations for the dropdown
$locations = [
    'sby' => 'Surabaya',
    'makassar' => 'Makassar',
    'bali' => 'Bali',
    'jakarta' => 'Jakarta',
    // Add more locations as needed
];
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Best Buy Summary</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        body {
            background-color: #f4f7fa;
        }
        .card-header {
            background-color: #007bff;
            color: white;
        }
        .table th, .table td {
            vertical-align: middle;
        }
    </style>
</head>
<body>

<div class="container mt-5">
    <h2 class="text-center mb-4">Best Buy Summary</h2>

    <form action="best_buy.php" method="post" class="mb-4">
        <div class="form-row">
            <div class="form-group col-md-4">
                <label for="month">Select Month:</label>
                <select name="month" id="month" class="form-control">
                    <option value="">-- Select Month --</option>
                    <?php foreach ($months as $key => $value): ?>
                        <option value="<?php echo $key; ?>" <?php echo ($selected_month == $key) ? 'selected' : ''; ?>>
                            <?php echo $value; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="form-group col-md-4">
                <label for="location">Select Location:</label>
                <select name="location" id="location" class="form-control">
                    <option value="">-- Select Location --</option>
                    <?php foreach ($locations as $key => $value): ?>
                        <option value="<?php echo $key; ?>" <?php echo ($selected_location == $key) ? 'selected' : ''; ?>>
                            <?php echo $value; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="form-group col-md-4 align-self-end">
                <button type="submit" class="btn btn-primary">Filter</button>
            </div>
        </div>
    </form>

    <div class="card">
        <div class="card-header">
            <h4 class="card-title">Quantity by Part Number and Location</h4>
        </div>
        <div class="card-body">
            <table class="table table-bordered">
                <thead class="thead-dark">
                    <tr>
                        <th>Location</th>
                        <th>Part Number</th>
                        <th>Total Quantity</th>
                        <th>Previous Month Quantity</th>
                        <th>Percentage Change (%)</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (!empty($results_with_percentage)): ?>
                        <?php foreach ($results_with_percentage as $result): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($result['location']); ?></td>
                                <td><?php echo htmlspecialchars($result['part_number']); ?></td>
                                <td><?php echo htmlspecialchars($result['total_quantity']); ?></td>
                                <td><?php echo htmlspecialchars($result['previous_quantity']); ?></td>
                                <td><?php echo htmlspecialchars($result['percentage_change']); ?>%</td>
                            </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="5" class="text-center">No records found.</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.2/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</body>
</html>
