<?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 detailed transactions for export
    $query = "
        SELECT 
            ps.ID as 'Sale ID',
            ps.SaleNumber as 'Sale Number',
            ps.SaleDate as 'Date',
            ps.SaleTime as 'Time',
            c.CustomerName as 'Customer Name',
            c.MobileNumber as 'Mobile Number',
            ps.SubTotal as 'Subtotal',
            ps.DiscountAmount as 'Discount',
            ps.TotalAmount as 'Total Amount',
            ps.PaidAmount as 'Paid Amount',
            ps.BalanceAmount as 'Balance',
            ps.PaymentMethod as 'Payment Method',
            ps.ReferenceNumber as 'Reference Number',
            ps.PaymentStatus as 'Payment Status',
            ps.SaleStatus as 'Sale Status',
            ps.Notes as 'Notes',
            ps.CashierName as 'Cashier',
            (
                SELECT GROUP_CONCAT(
                    CONCAT(psi.ProductName, ' (', psi.Quantity, ' x ₹', psi.UnitPrice, ')')
                    SEPARATOR '; '
                )
                FROM tblpossaleitems psi
                WHERE psi.SaleID = ps.ID
            ) as 'Items'
        FROM tblpossales ps
        LEFT JOIN tblcustomers c ON ps.CustomerID = c.ID
        WHERE DATE(ps.SaleDate) BETWEEN ? AND ?";
    
    $params = [$fromDate, $toDate];
    
    if (!empty($customerId)) {
        $query .= " AND ps.CustomerID = ?";
        $params[] = $customerId;
    }
    
    $query .= " ORDER BY ps.SaleDate DESC, ps.ID DESC";

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

    // Set headers for CSV download
    $filename = 'pos_sales_report_' . $fromDate . '_to_' . $toDate . '.csv';
    
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    // Output CSV
    $output = fopen('php://output', 'w');

    // Add BOM for proper UTF-8 encoding in Excel
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

    // Add header row
    if (!empty($transactions)) {
        fputcsv($output, array_keys($transactions[0]));
        
        // Add data rows
        foreach ($transactions as $transaction) {
            fputcsv($output, $transaction);
        }
    } else {
        fputcsv($output, ['No data found for the selected criteria']);
    }

    fclose($output);

} catch (PDOException $e) {
    header('Content-Type: text/plain');
    echo 'Database error: ' . $e->getMessage();
} catch (Exception $e) {
    header('Content-Type: text/plain');
    echo 'Error: ' . $e->getMessage();
}
?>
