<?php
require_once('includes/load.php');
$page_require_level(3);

// Initialize variables
$errors = [];
$response = [];

// Function to calculate total omzet within the date range with scan_status filter
function get_total_omzet($start_date, $end_date) {
    global $db;

    // Query to calculate total omzet per unique invoice where scan_status is not null
    $sql = "SELECT SUM(total_price) AS total_omzet 
            FROM (
                SELECT invoice, MAX(scan_status) AS latest_scan_status, SUM(total_price) AS total_price
                FROM awb
                WHERE scan_status IS NOT NULL
                AND DATE(scan_status) BETWEEN '{$db->escape($start_date)}' AND '{$db->escape($end_date)}'
                GROUP BY invoice
                HAVING invoice IS NOT NULL
            ) AS sales_summary";
    
    return find_by_sql($sql);
}

// Function to calculate total omzet per hour based on scan_status for the current date
function get_omzet_per_hour($date) {
    global $db;

    // Query to calculate total omzet per hour where scan_status is not null
    $sql = "SELECT DATE_FORMAT(scan_status, '%H:00:00') AS hour,
                   SUM(total_price) AS total_omzet
            FROM awb
            WHERE scan_status IS NOT NULL
            AND DATE(scan_status) = '{$db->escape($date)}'
            GROUP BY hour
            ORDER BY hour";

    return find_by_sql($sql);
}

// Function to get top 5 unique invoices with highest total_price
function get_top_invoices($start_date, $end_date) {
    global $db;

    // Query to get top 5 invoices with highest total_price
    $sql = "SELECT invoice, SUM(total_price) AS total_price
            FROM awb
            WHERE scan_status IS NOT NULL
            AND DATE(scan_status) BETWEEN '{$db->escape($start_date)}' AND '{$db->escape($end_date)}'
            GROUP BY invoice
            ORDER BY total_price DESC
            LIMIT 5";

    return find_by_sql($sql);
}

// Get POST data
$start_date = isset($_POST['start_date']) ? $_POST['start_date'] : date('Y-m-d');
$end_date = isset($_POST['end_date']) ? $_POST['end_date'] : date('Y-m-d');

// Fetch data
$response['total_omzet'] = get_total_omzet($start_date, $end_date)[0]['total_omzet'] ?? 0;
$response['omzet_per_hour'] = get_omzet_per_hour($end_date);
$response['top_invoices'] = get_top_invoices($start_date, $end_date);

// Return JSON response
header('Content-Type: application/json');
echo json_encode($response);
?>
