<?php
session_start();
include_once('../includes/config.php');

// Check if user is logged in
if (!isset($_SESSION['uid'])) {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit();
}

$customerType = isset($_GET['type']) ? mysqli_real_escape_string($con, $_GET['type']) : '';
$search = isset($_GET['search']) ? mysqli_real_escape_string($con, $_GET['search']) : '';
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$export = isset($_GET['export']) ? true : false;

$recordsPerPage = 10;
$offset = ($page - 1) * $recordsPerPage;

// Build WHERE clause
$whereClause = "WHERE c.IsActive = 1";
if (!empty($customerType)) {
    $whereClause .= " AND c.CustomerType = '$customerType'";
}
if (!empty($search)) {
    $whereClause .= " AND (c.CustomerName LIKE '%$search%' OR c.Mobile LIKE '%$search%' OR c.Area LIKE '%$search%')";
}

// Calculate outstanding amounts
// This assumes you have a payments table - adjust according to your actual table structure
$outstandingQuery = "
    SELECT 
        c.*,
        COALESCE(
            (SELECT SUM(Amount) FROM tblcustomerpayments WHERE CustomerID = c.ID AND PaymentType = 'Credit' AND IsActive = 1) - 
            (SELECT SUM(Amount) FROM tblcustomerpayments WHERE CustomerID = c.ID AND PaymentType = 'Debit' AND IsActive = 1), 
            0
        ) as outstandingAmount,
        (SELECT MAX(PaymentDate) FROM tblcustomerpayments WHERE CustomerID = c.ID AND IsActive = 1) as lastPaymentDate
    FROM tblcustomers c 
    $whereClause 
    ORDER BY c.CustomerName ASC
";

// For export, get all records
if ($export) {
    $customersResult = mysqli_query($con, $outstandingQuery);
    
    if (!$customersResult) {
        http_response_code(500);
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
        exit();
    }
    
    // Set headers for Excel download
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="Customer_Outstanding_' . date('Y-m-d') . '.xls"');
    header('Pragma: no-cache');
    header('Expires: 0');
    
    // Output Excel format
    echo '<table border="1">';
    echo '<tr>';
    echo '<th>S.No</th>';
    echo '<th>Customer Name</th>';
    echo '<th>Area</th>';
    echo '<th>Mobile</th>';
    echo '<th>Customer Type</th>';
    echo '<th>Outstanding Amount</th>';
    echo '<th>Last Payment Date</th>';
    echo '</tr>';
    
    $serialNo = 1;
    while ($customer = mysqli_fetch_assoc($customersResult)) {
        echo '<tr>';
        echo '<td>' . $serialNo++ . '</td>';
        echo '<td>' . htmlspecialchars($customer['CustomerName']) . '</td>';
        echo '<td>' . htmlspecialchars($customer['Area']) . '</td>';
        echo '<td>' . htmlspecialchars($customer['Mobile']) . '</td>';
        echo '<td>' . htmlspecialchars($customer['CustomerType']) . '</td>';
        echo '<td>' . number_format($customer['outstandingAmount'], 2) . '</td>';
        echo '<td>' . ($customer['lastPaymentDate'] ? date('d-m-Y', strtotime($customer['lastPaymentDate'])) : 'No payments') . '</td>';
        echo '</tr>';
    }
    echo '</table>';
    exit();
}

// For regular API call, get paginated data
// Count total records
$countQuery = "SELECT COUNT(*) as total FROM tblcustomers c $whereClause";
$countResult = mysqli_query($con, $countQuery);

if (!$countResult) {
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    exit();
}

$totalRecords = mysqli_fetch_assoc($countResult)['total'];
$totalPages = ceil($totalRecords / $recordsPerPage);

// Get paginated results
$paginatedQuery = $outstandingQuery . " LIMIT $offset, $recordsPerPage";
$customersResult = mysqli_query($con, $paginatedQuery);

if (!$customersResult) {
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    exit();
}

$customers = [];
$totalOutstanding = 0;

while ($customer = mysqli_fetch_assoc($customersResult)) {
    // Format last payment date
    if ($customer['lastPaymentDate']) {
        $customer['lastPaymentDate'] = date('d-m-Y', strtotime($customer['lastPaymentDate']));
    }
    
    $customers[] = $customer;
    $totalOutstanding += $customer['outstandingAmount'];
}

// Calculate total outstanding for all records (not just current page)
$totalOutstandingQuery = "
    SELECT SUM(
        COALESCE(
            (SELECT SUM(Amount) FROM tblcustomerpayments WHERE CustomerID = c.ID AND PaymentType = 'Credit' AND IsActive = 1) - 
            (SELECT SUM(Amount) FROM tblcustomerpayments WHERE CustomerID = c.ID AND PaymentType = 'Debit' AND IsActive = 1), 
            0
        )
    ) as totalOutstanding
    FROM tblcustomers c 
    $whereClause
";

$totalOutstandingResult = mysqli_query($con, $totalOutstandingQuery);
$allTotalOutstanding = 0;

if ($totalOutstandingResult) {
    $totalOutstandingRow = mysqli_fetch_assoc($totalOutstandingResult);
    $allTotalOutstanding = $totalOutstandingRow['totalOutstanding'] ?? 0;
}

$response = [
    'success' => true,
    'customers' => $customers,
    'pagination' => [
        'currentPage' => $page,
        'totalPages' => $totalPages,
        'totalRecords' => $totalRecords,
        'recordsPerPage' => $recordsPerPage
    ],
    'total' => count($customers),
    'totalOutstanding' => $allTotalOutstanding
];

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