<?php
header('Content-Type: application/json');
include 'shikanisha.kts.php';

// Function to get the active cashier shift start time
function getCurrentShiftStartTime($conn) {
    $sql = "SELECT s.start_time 
            FROM shifts s
            JOIN employees e ON s.employee_id = e.employee_id
            WHERE e.type = 'Cashier'
            AND (s.end_time IS NULL OR s.end_time > NOW())
            ORDER BY s.start_time DESC
            LIMIT 1";
    
    $result = mysqli_query($conn, $sql);
    
    if ($row = mysqli_fetch_assoc($result)) {
        return $row['start_time'];
    }
    
    // Fallback to today 5:00 AM if no active cashier shift found
    return date('Y-m-d 05:00:00');
}

// Function to get dashboard stats based on current shift time
function getDashboardStats($conn) {
    $startTime = getCurrentShiftStartTime($conn);
    $endTime = date('Y-m-d H:i:s'); // Current time
    
    $stats = [];
    
    // Prepare the time condition with proper escaping
    $escapedStart = mysqli_real_escape_string($conn, $startTime);
    $escapedEnd = mysqli_real_escape_string($conn, $endTime);
    $timeCondition = "seller_date BETWEEN '$escapedStart' AND '$escapedEnd'";
    
    // Total Sales
    $sql = "SELECT SUM(total) AS total_sum FROM transaction_receipts WHERE $timeCondition";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['total_sales'] = $row['total_sum'] ?? 0;
    
    // Till Payments
    $sql = "SELECT SUM(p.amount) AS till FROM payments AS p
            JOIN transaction_receipts AS tr ON p.trans_id = tr.trans_id
            WHERE $timeCondition AND p.payment_method = 'till'";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['till'] = $row['till'] ?? 0;
    
    // Cash Payments
    $sql = "SELECT SUM(p.amount) AS cash FROM payments AS p
            JOIN transaction_receipts AS tr ON p.trans_id = tr.trans_id
            WHERE $timeCondition AND p.payment_method = 'cash'";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['cash'] = $row['cash'] ?? 0;
    
    // Debt
    $sql = "SELECT SUM(total) - SUM(amount_received) AS debt 
            FROM transaction_receipts 
            WHERE $timeCondition";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['debt'] = $row['debt'] ?? 0;
    
    // Merchant Payments
    $sql = "SELECT SUM(p.amount) AS merchant FROM payments AS p
            JOIN transaction_receipts AS tr ON p.trans_id = tr.trans_id
            WHERE $timeCondition AND p.payment_method = 'merchant'";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['merchant'] = $row['merchant'] ?? 0;
    
    // Inhouse Payments
    $sql = "SELECT SUM(p.amount) AS captain FROM payments AS p
            JOIN transaction_receipts AS tr ON p.trans_id = tr.trans_id
            WHERE $timeCondition AND p.payment_method = 'inhouse'";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['inhouse'] = $row['captain'] ?? 0;
    
    return $stats;
}

// Get the stats and output as JSON
echo json_encode(getDashboardStats($conn));
?>