<?php
// modules/sales/export_sales.php
session_start();
require_once '../assets/db_connect.php';

// Check if user is logged in
if (!isset($_SESSION['user'])) {
    die("Access denied. Please login.");
}

$shop_id = $_SESSION['user']['shop_id'] ?? 0;

// Get filter parameters
$from_date = $_POST['from_date'] ?? date('Y-m-d', strtotime('-30 days'));
$to_date = $_POST['to_date'] ?? date('Y-m-d');
$status = $_POST['status'] ?? '';
$payment_method = $_POST['payment_method'] ?? '';
$search = $_POST['search'] ?? '';

// Build query
$sql = "SELECT 
            s.invoice_no,
            s.sale_date,
            COALESCE(c.name, 'Walk-in Customer') as customer_name,
            c.phone as customer_phone,
            COUNT(DISTINCT sl.id) as items_count,
            SUM(sl.qty) as total_quantity,
            s.total,
            s.paid,
            s.total - s.paid as due,
            s.status,
            u.fullname as cashier_name,
            GROUP_CONCAT(CONCAT(p.name, ' (', sl.qty, 'x)') SEPARATOR '; ') as items
        FROM sales s
        LEFT JOIN customers c ON s.customer_id = c.id
        LEFT JOIN sale_lines sl ON s.id = sl.sale_id
        LEFT JOIN products p ON sl.product_id = p.id
        LEFT JOIN users u ON s.created_by = u.id
        WHERE s.shop_id = ?
        AND DATE(s.sale_date) BETWEEN ? AND ?";

$params = [$shop_id, $from_date, $to_date];

// Add filters
if (!empty($status)) {
    $sql .= " AND s.status = ?";
    $params[] = $status;
}

if (!empty($search)) {
    $sql .= " AND (s.invoice_no LIKE ? OR c.name LIKE ? OR c.phone LIKE ?)";
    $searchTerm = "%$search%";
    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $params[] = $searchTerm;
}

$sql .= " GROUP BY s.id, s.invoice_no, s.sale_date, c.name, c.phone, s.total, s.paid, s.status, u.fullname
          ORDER BY s.sale_date DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$sales = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Set headers for CSV download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=sales_export_' . date('Y-m-d_H-i-s') . '.csv');

// Create output stream
$output = fopen('php://output', 'w');

// Add BOM for UTF-8
fwrite($output, "\xEF\xBB\xBF");

// Add headers
$headers = [
    'Invoice No',
    'Date',
    'Time',
    'Customer Name',
    'Customer Phone',
    'Items Count',
    'Total Quantity',
    'Total Amount (TZS)',
    'Paid Amount (TZS)',
    'Due Amount (TZS)',
    'Status',
    'Cashier',
    'Items Description'
];
fputcsv($output, $headers);

// Add data rows
foreach ($sales as $sale) {
    $row = [
        $sale['invoice_no'],
        date('Y-m-d', strtotime($sale['sale_date'])),
        date('H:i:s', strtotime($sale['sale_date'])),
        $sale['customer_name'],
        $sale['customer_phone'] ?? '',
        $sale['items_count'],
        $sale['total_quantity'],
        $sale['total'],
        $sale['paid'],
        $sale['due'],
        ucfirst(str_replace('_', ' ', $sale['status'])),
        $sale['cashier_name'],
        $sale['items'] ?? ''
    ];
    fputcsv($output, $row);
}

// Add summary row
fputcsv($output, []); // Empty row
fputcsv($output, ['SUMMARY', '', '', '', '', '', '', '', '', '', '', '', '']);
fputcsv($output, ['Total Sales', '', '', '', '', count($sales), array_sum(array_column($sales, 'total_quantity')), 
                  array_sum(array_column($sales, 'total')), array_sum(array_column($sales, 'paid')), 
                  array_sum(array_column($sales, 'due')), '', '', '']);
fputcsv($output, ['Date Range', $from_date . ' to ' . $to_date, '', '', '', '', '', '', '', '', '', '', '']);
fputcsv($output, ['Generated On', date('Y-m-d H:i:s'), '', '', '', '', '', '', '', '', '', '', '']);

fclose($output);
exit;