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

try {
    $fromDate = $_GET['from_date'] ?? date('Y-m-01');
    $toDate = $_GET['to_date'] ?? date('Y-m-d');
    $customerId = $_GET['customer_id'] ?? '';

    // Build WHERE clause
    $whereConditions = [];
    $params = [];

    $whereConditions[] = "DATE(cp.TransactionDate) BETWEEN ? AND ?";
    $params[] = $fromDate;
    $params[] = $toDate;

    if (!empty($customerId)) {
        $whereConditions[] = "cp.CustomerID = ?";
        $params[] = $customerId;
    }

    $whereClause = implode(' AND ', $whereConditions);

    // Get statistics
    $statsQuery = "
        SELECT 
            COUNT(DISTINCT ps.ID) as total_orders,
            COUNT(DISTINCT ps.CustomerID) as unique_customers,
            SUM(ps.TotalAmount) as total_sales,
            AVG(ps.TotalAmount) as avg_order_value
        FROM tblpossales ps 
        WHERE DATE(ps.SaleDate) BETWEEN ? AND ?";
    
    $statsParams = [$fromDate, $toDate];
    
    if (!empty($customerId)) {
        $statsQuery .= " AND ps.CustomerID = ?";
        $statsParams[] = $customerId;
    }

    $stmt = $dbh->prepare($statsQuery);
    $stmt->execute($statsParams);
    $statistics = $stmt->fetch(PDO::FETCH_ASSOC);

    // Get transactions with customer details
    // Detect if MobileNumber column exists in tblcustomers (for backward compatibility)
    $mobileExists = false;
    try {
        $colCheck = $dbh->query("SHOW COLUMNS FROM tblcustomers LIKE 'MobileNumber'");
        if ($colCheck && $colCheck->rowCount() > 0) { $mobileExists = true; }
    } catch (Exception $e) { /* ignore */ }

    $mobileSelect = $mobileExists ? 'c.MobileNumber,' : "'' AS MobileNumber,";

    $transactionsQuery = "
        SELECT 
            ps.ID,
            ps.SaleNumber,
            ps.SaleDate as TransactionDate,
            ps.CustomerID,
            c.CustomerName,
            $mobileSelect
            ps.TotalAmount as Amount,
            ps.PaidAmount,
            ps.PaymentMethod,
            ps.ReferenceNumber,
            ps.Notes as Description,
            ps.PaymentStatus as Status,
            (SELECT COUNT(*) FROM tblpossaleitems psi WHERE psi.SaleID = ps.ID) as ItemCount
        FROM tblpossales ps
        LEFT JOIN tblcustomers c ON ps.CustomerID = c.ID
        WHERE DATE(ps.SaleDate) BETWEEN ? AND ?";
    
    $transParams = [$fromDate, $toDate];
    
    if (!empty($customerId)) {
        $transactionsQuery .= " AND ps.CustomerID = ?";
        $transParams[] = $customerId;
    }
    
    $transactionsQuery .= " ORDER BY ps.SaleDate DESC, ps.ID DESC";

    $stmt = $dbh->prepare($transactionsQuery);
    $stmt->execute($transParams);
    $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Format the response
    $response = [
        'success' => true,
        'statistics' => [
            'total_sales' => number_format($statistics['total_sales'] ?? 0, 2),
            'total_orders' => intval($statistics['total_orders'] ?? 0),
            'unique_customers' => intval($statistics['unique_customers'] ?? 0),
            'avg_order_value' => number_format($statistics['avg_order_value'] ?? 0, 2)
        ],
        'transactions' => $transactions
    ];

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

} catch (PDOException $e) {
    header('Content-Type: application/json');
    echo json_encode([
        'success' => false,
        'message' => 'Database error: ' . $e->getMessage()
    ]);
} catch (Exception $e) {
    header('Content-Type: application/json');
    echo json_encode([
        'success' => false,
        'message' => 'Error: ' . $e->getMessage()
    ]);
}
?>
