<?php
include "session.php";

if (!defined('BASE_URL')) {
}

require_once __DIR__ . '/../db.php';

// فلترة حسب الحالة والتاريخ
$filter     = $_GET['filter'] ?? 'all';
$dateFrom   = $_GET['date_from'] ?? '';
$dateTo     = $_GET['date_to'] ?? '';
$searchQuery = trim($_GET['search'] ?? '');

$query = "
    SELECT 
        o.id,
        o.customer_name,
        o.phone,
        o.phone2,
        w.name AS wilaya_name,
        o.municipality,
        o.address,
        o.delivery_type,
        dc.name AS delivery_company_name,
        os.name AS status_name,
        o.order_date,
        o.sent_date,
        COALESCE(o.delivery_price_modified, dp.price, 0) AS delivery_price,
        SUM(oi.quantity * oi.unit_price) AS products_total,
        SUM(oi.quantity * oi.unit_price) + COALESCE(o.delivery_price_modified, dp.price, 0) AS grand_total,
        GROUP_CONCAT(
            CONCAT(p.name, ' (x', oi.quantity, ' × ', oi.unit_price, ' دج)')
            SEPARATOR ' | '
        ) AS products_list
    FROM orders o
    LEFT JOIN order_statuses os ON o.status_id = os.id
    LEFT JOIN delivery_companies dc ON o.delivery_company_id = dc.id
    LEFT JOIN wilayas w ON o.wilaya_id = w.id
    LEFT JOIN delivery_prices dp ON dp.wilaya_id = o.wilaya_id AND dp.delivery_type = o.delivery_type AND dp.delivery_company_id = o.delivery_company_id
    LEFT JOIN order_items oi ON oi.order_id = o.id
    LEFT JOIN product_variants pv ON oi.product_variant_id = pv.id
    LEFT JOIN products p ON pv.product_id = p.id
    WHERE 1=1
";

$params = [];

if (!empty($searchQuery)) {
    $query .= " AND (o.customer_name LIKE :search OR o.phone LIKE :search OR o.phone2 LIKE :search OR w.name LIKE :search OR o.municipality LIKE :search)";
    $params[':search'] = '%' . $searchQuery . '%';
}

if ($filter !== 'all') {
    $statusMap = ['pending' => 1, 'sent' => 2, 'delivered' => 3, 'cancelled' => 4];
    if (isset($statusMap[$filter])) {
        $query .= " AND o.status_id = " . $statusMap[$filter];
    }
}

if (!empty($dateFrom)) {
    $query .= " AND DATE(o.order_date) >= :date_from";
    $params[':date_from'] = $dateFrom;
}

if (!empty($dateTo)) {
    $query .= " AND DATE(o.order_date) <= :date_to";
    $params[':date_to'] = $dateTo;
}

$query .= " GROUP BY o.id ORDER BY o.order_date DESC";

$stmt = $pdo->prepare($query);
foreach ($params as $key => $value) $stmt->bindValue($key, $value);
$stmt->execute();
$orders = $stmt->fetchAll();

// إعداد headers للتحميل
$filename = 'orders_' . date('Y-m-d_H-i-s') . '.csv';

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: no-cache, must-revalidate');
header('Pragma: no-cache');

// فتح output
$output = fopen('php://output', 'w');

// BOM لدعم العربية في Excel/Sheets
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// رأس الجدول
fputcsv($output, [
    'رقم الطلب',
    'اسم العميل',
    'الهاتف',
    'هاتف إضافي',
    'الولاية',
    'البلدية',
    'العنوان',
    'نوع التوصيل',
    'شركة التوصيل',
    'الحالة',
    'تاريخ الطلب',
    'تاريخ الإرسال',
    'المنتجات',
    'إجمالي المنتجات (دج)',
    'سعر التوصيل (دج)',
    'الإجمالي الكلي (دج)',
]);

// بيانات الطلبات
foreach ($orders as $order) {
    $deliveryTypeLabel = $order['delivery_type'] === 'home' ? 'توصيل للمنزل' : 'توصيل للمكتب';

    fputcsv($output, [
        '#' . $order['id'],
        $order['customer_name'] ?? '',
        $order['phone'] ?? '',
        $order['phone2'] ?? '',
        $order['wilaya_name'] ?? '',
        $order['municipality'] ?? '',
        $order['address'] ?? '',
        $deliveryTypeLabel,
        $order['delivery_company_name'] ?? '',
        $order['status_name'] ?? '',
        $order['order_date'] ? date('Y-m-d H:i', strtotime($order['order_date'])) : '',
        $order['sent_date'] ? date('Y-m-d H:i', strtotime($order['sent_date'])) : '',
        $order['products_list'] ?? '',
        number_format((float)$order['products_total'], 2),
        number_format((float)$order['delivery_price'], 2),
        number_format((float)$order['grand_total'], 2),
    ]);
}

fclose($output);
exit();
?>