<?php
header('Content-Type: application/json');
require_once './shikanisha.kts.php'; // Your existing connection file

// Pagination parameters
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;

// Sorting parameters
$sortField = isset($_GET['sort']) ? $_GET['sort'] : 'start_time';
$sortOrder = isset($_GET['order']) ? $_GET['order'] : 'DESC';

// Validate sort field
$allowedSortFields = ['shift_id', 'start_time', 'end_time', 'status', 'employee_id'];
if (!in_array($sortField, $allowedSortFields)) {
    $sortField = 'start_time';
}

$sortOrder = strtoupper($sortOrder) === 'ASC' ? 'ASC' : 'DESC';

// Get total number of shifts
$countQuery = "SELECT COUNT(*) as total FROM shifts";
$countResult = $conn->query($countQuery);
$totalShifts = $countResult->fetch_assoc()['total'];
$totalPages = ceil($totalShifts / $perPage);

// Get shifts data
$query = "
    SELECT 
        s.shift_id, 
        s.start_time, 
        s.end_time, 
        s.status,
        s.employee_id,
        s.total_orders,
        s.total_payments,
        s.total_debt,
        e.name as employee_name
    FROM 
        shifts s
    LEFT JOIN 
        employees e ON s.employee_id = e.employee_id
    ORDER BY 
        $sortField $sortOrder
    LIMIT 
        $perPage OFFSET $offset
";

$result = $conn->query($query);
$shifts = [];

while ($row = $result->fetch_assoc()) {
    // Format numeric values
    $row['total_orders'] = (float)$row['total_orders'];
    $row['total_payments'] = (float)$row['total_payments'];
    $row['total_debt'] = (float)$row['total_debt'];
    
    // Calculate variance (total_orders - total_payments)
    $row['variance'] = $row['total_orders'] - $row['total_payments'];
    
    $shifts[] = $row;
}

// Return JSON response
echo json_encode([
    'success' => true,
    'shifts' => $shifts,
    'totalPages' => $totalPages,
    'currentPage' => $page
]);

// Close connection
$conn->close();
?>