<?php
// ========================================
// گزارشات و بیلان مالی (نسخه استاندارد و پایدار)
// پروژه فروشگاه کیف‌لند
// ========================================
require_once '../config/database.php';
require_once '../config/settings.php';
require_once '../includes/auth.php';
require_once '../includes/functions.php';

Auth::requireAdmin();
$db = Database::getInstance()->getConnection();
$pageTitle = 'گزارشات مالی | کیف‌لند';

// ========================================
// ۱. خروجی اکسل (حتماً قبل از هر خروجی HTML)
// ========================================
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    try {
        $start = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
        $end   = $_GET['end_date']   ?? date('Y-m-d');
        $cat   = (int)($_GET['category'] ?? 0);
        
        $sql = "SELECT o.created_at, o.order_number, p.name as product, oi.quantity, oi.price as unit_price, oi.subtotal as total, o.status 
                FROM order_items oi 
                JOIN orders o ON oi.order_id = o.id 
                JOIN products p ON oi.product_id = p.id 
                WHERE o.created_at BETWEEN ? AND ? AND o.status != 'cancelled'";
        $params = [$start . ' 00:00:00', $end . ' 23:59:59'];
        
        if ($cat > 0) {
            $sql .= " AND p.category_id = ?";
            $params[] = $cat;
        }
        $sql .= " ORDER BY o.created_at DESC";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $filename = 'report_' . (!empty($end) ? $end : date('Y-m-d')) . '.csv';
        
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Pragma: no-cache'); header('Expires: 0');
        
        echo "\xEF\xBB\xBF"; // BOM UTF-8
        $output = fopen('php://output', 'w');
        fputcsv($output, ['تاریخ', 'شماره سفارش', 'محصول', 'تعداد', 'قیمت واحد (تومان)', 'مجموع (تومان)', 'وضعیت'], ',');
        
        foreach ($rows as $row) {
            $status_txt = match($row['status']) { 'delivered' => 'تحویل شده', 'shipped' => 'ارسال شده', 'pending' => 'در انتظار', default => $row['status'] };
            fputcsv($output, [
                date('Y/m/d', strtotime($row['created_at'])),
                $row['order_number'], $row['product'], $row['quantity'],
                number_format($row['unit_price']), number_format($row['total']), $status_txt
            ], ',');
        }
        fclose($output);
        exit;
    } catch (Exception $e) {
        die('خطا در تولید فایل: ' . $e->getMessage());
    }
}

// ========================================
// ۲. دریافت فیلترها و آماده‌سازی
// ========================================
$start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
$end_date   = $_GET['end_date']   ?? date('Y-m-d');
$category_filter = (int)($_GET['category'] ?? 0);

$start_dt = $start_date . ' 00:00:00';
$end_dt   = $end_date . ' 23:59:59';

try { $categories = $db->query("SELECT id, name FROM categories ORDER BY name ASC")->fetchAll(); } catch (Exception $e) { $categories = []; }

// ========================================
// ۳. کوئری‌های آماری (ساده، امن و سازگار)
// ========================================
$stats = ['revenue' => 0, 'orders' => 0, 'avg_order' => 0, 'top_cat' => '-', 'products' => [], 'chart' => []];
$db_error = '';

try {
    // شرط پایه
    $base_cond = "WHERE o.created_at BETWEEN ? AND ? AND o.status != 'cancelled'";
    $params = [$start_dt, $end_dt];
    
    // فیلتر دسته‌بندی (با استفاده از IN برای جلوگیری از تداخل JOIN در توابع تجمیعی)
    if ($category_filter > 0) {
        $base_cond .= " AND o.id IN (SELECT oi.order_id FROM order_items oi JOIN products p ON oi.product_id = p.id WHERE p.category_id = ?)";
        $params[] = $category_filter;
    }

    // ۱. درآمد کل
    $stmt = $db->prepare("SELECT COALESCE(SUM(o.final_amount), 0) FROM orders o $base_cond");
    $stmt->execute($params);
    $stats['revenue'] = $stmt->fetchColumn();

    // ۲. تعداد سفارشات
    $stmt = $db->prepare("SELECT COUNT(*) FROM orders o $base_cond");
    $stmt->execute($params);
    $stats['orders'] = $stmt->fetchColumn();
    $stats['avg_order'] = $stats['orders'] > 0 ? round($stats['revenue'] / $stats['orders']) : 0;

    // ۳. پرفروش‌ترین دسته
    if ($category_filter > 0) {
        $stmt = $db->prepare("SELECT name FROM categories WHERE id = ?");
        $stmt->execute([$category_filter]);
        $stats['top_cat'] = $stmt->fetchColumn() ?: '-';
    } else {
        $stmt = $db->prepare("SELECT c.name FROM order_items oi JOIN orders o ON oi.order_id = o.id JOIN products p ON oi.product_id = p.id JOIN categories c ON p.category_id = c.id WHERE o.created_at BETWEEN ? AND ? AND o.status != 'cancelled' GROUP BY c.id ORDER BY COUNT(*) DESC LIMIT 1");
        $stmt->execute([$start_dt, $end_dt]);
        $stats['top_cat'] = $stmt->fetchColumn() ?: '-';
    }

    // ۴. ۱۰ محصول برتر
    $p_prod = [$start_dt, $end_dt];
    $prod_cond = "WHERE o.created_at BETWEEN ? AND ? AND o.status != 'cancelled'";
    if ($category_filter > 0) { $prod_cond .= " AND p.category_id = ?"; $p_prod[] = $category_filter; }
    
    $stmt = $db->prepare("SELECT p.name, SUM(oi.quantity) as sold, SUM(oi.subtotal) as rev FROM order_items oi JOIN orders o ON oi.order_id = o.id JOIN products p ON oi.product_id = p.id $prod_cond GROUP BY p.id ORDER BY sold DESC LIMIT 10");
    $stmt->execute($p_prod);
    $stats['products'] = $stmt->fetchAll();

    // ۵. داده‌های نمودار روزانه
    $stmt = $db->prepare("SELECT DATE(o.created_at) as d, SUM(o.final_amount) as r FROM orders o $base_cond GROUP BY DATE(o.created_at) ORDER BY d ASC");
    $stmt->execute($params);
    $stats['chart'] = $stmt->fetchAll();

} catch (PDOException $e) {
    $db_error = '⚠️ خطای دیتابیس: ' . $e->getMessage();
}

$max_chart = 1;
foreach ($stats['chart'] as $c) { if ($c['r'] > $max_chart) $max_chart = $c['r']; }

include 'includes/admin-header.php';
?>

<style>
.reports-header { display:flex; justify-content:space-between; align-items:center; margin-bottom:24px; flex-wrap:wrap; gap:16px; }
.filters-bar { display:flex; gap:12px; flex-wrap:wrap; align-items:flex-end; background:var(--glass-bg); backdrop-filter:blur(20px); padding:16px; border-radius:var(--radius); border:1px solid var(--glass-border); margin-bottom:24px; }
.filter-group { display:flex; flex-direction:column; gap:6px; flex:1; min-width:160px; }
.filter-group label { font-size:12px; font-weight:600; color:var(--text-secondary); }
.filter-group select, .filter-group input { padding:10px 14px; border:1px solid var(--glass-border); border-radius:var(--radius-sm); background:var(--glass-bg-light); font-family:'Vazirmatn',sans-serif; font-size:13px; color:var(--text-primary); }
.filter-group select:focus, .filter-group input:focus { outline:none; border-color:var(--primary); box-shadow:0 0 0 3px rgba(0,122,255,0.15); }
.stats-grid { display:grid; grid-template-columns:repeat(auto-fit,minmax(200px,1fr)); gap:16px; margin-bottom:24px; }
.stat-card { background:var(--glass-bg); backdrop-filter:blur(20px); border:1px solid var(--glass-border); border-radius:var(--radius-sm); padding:20px; text-align:center; transition:0.2s; }
.stat-card:hover { transform:translateY(-3px); box-shadow:var(--shadow-soft); }
.stat-card h3 { font-size:28px; font-weight:700; margin:0; color:var(--primary); }
.stat-card p { font-size:13px; color:var(--text-secondary); margin:8px 0 0; font-weight:500; }
.content-grid { display:grid; grid-template-columns:2fr 1fr; gap:20px; }
.glass-card { background:var(--glass-bg); backdrop-filter:blur(40px) saturate(180%); border:1px solid var(--glass-border); border-radius:var(--radius); padding:20px; box-shadow:var(--shadow-card); margin-bottom:20px; }
.card-header { display:flex; justify-content:space-between; align-items:center; margin-bottom:16px; padding-bottom:12px; border-bottom:1px solid rgba(255,255,255,0.2); }
.card-header h3 { margin:0; font-size:16px; font-weight:700; }
.chart-container { height:250px; display:flex; align-items:flex-end; justify-content:space-between; gap:8px; padding-top:20px; }
.bar-item { flex:1; display:flex; flex-direction:column; align-items:center; height:100%; justify-content:flex-end; position:relative; }
.bar { width:100%; background:linear-gradient(to top,var(--primary),#5856D6); border-radius:6px 6px 0 0; transition:height 0.5s ease; min-height:2px; }
.bar:hover { opacity:0.8; }
.bar-label { font-size:11px; color:var(--text-muted); margin-top:8px; white-space:nowrap; overflow:hidden; text-overflow:ellipsis; width:100%; text-align:center; }
.tooltip-val { position:absolute; top:-25px; background:var(--dark); color:#fff; padding:2px 6px; border-radius:4px; font-size:10px; opacity:0; transition:0.2s; pointer-events:none; white-space:nowrap; }
.bar:hover .tooltip-val { opacity:1; }
.table-responsive { overflow-x:auto; }
.data-table { width:100%; border-collapse:collapse; min-width:500px; }
.data-table th { text-align:right; padding:12px; font-size:12px; font-weight:600; color:var(--text-secondary); border-bottom:1px solid var(--glass-border); }
.data-table td { padding:12px; font-size:13px; border-bottom:0.5px solid rgba(255,255,255,0.1); }
.data-table tr:hover { background:rgba(255,255,255,0.15); }
.btn { display:inline-flex; align-items:center; justify-content:center; gap:8px; padding:10px 20px; border-radius:var(--radius-sm); font-weight:600; font-size:14px; cursor:pointer; transition:0.2s; border:none; font-family:'Vazirmatn',sans-serif; text-decoration:none; }
.btn-primary { background:var(--primary); color:white; }
.btn-primary:hover { background:var(--primary-hover); transform:translateY(-1px); }
.btn-outline { background:transparent; color:var(--text-primary); border:1px solid var(--glass-border); }
.btn-outline:hover { background:var(--glass-hover); }
.btn-sm { padding:6px 12px; font-size:12px; }
.alert-danger { background:rgba(255,59,48,0.15); color:#7A1815; border:1px solid rgba(255,59,48,0.3); padding:12px 16px; border-radius:10px; margin-bottom:20px; }
.empty-state { text-align:center; padding:40px; color:var(--text-muted); }
@media (max-width:992px) { .content-grid { grid-template-columns:1fr; } }
@media (max-width:768px) { .filters-bar { flex-direction:column; } .filter-group { width:100%; min-width:100%; } .stats-grid { grid-template-columns:repeat(2,1fr); } }
</style>

<main class="main-content">
    <header class="top-header">
        <button class="menu-toggle" id="menuToggle"><i class="fas fa-bars"></i></button>
        <div class="search-box"><i class="fas fa-search"></i><input type="text" placeholder="جستجو..."></div>
        <div class="header-actions">
            <a href="?start_date=<?= htmlspecialchars($start_date) ?>&end_date=<?= htmlspecialchars($end_date) ?>&category=<?= $category_filter ?>&export=csv" class="btn btn-outline btn-sm">
                <i class="fas fa-file-excel"></i> خروجی اکسل
            </a>
        </div>
    </header>

    <div class="dashboard-content">
        <div class="page-title"><h1>گزارشات و بیلان مالی</h1><p>تحلیل عملکرد فروش و سودآوری</p></div>

        <?php if ($db_error): ?><div class="alert-danger"><?= htmlspecialchars($db_error) ?></div><?php endif; ?>

        <form method="GET" class="filters-bar">
            <div class="filter-group">
                <label>از تاریخ (میلادی)</label>
                <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>">
            </div>
            <div class="filter-group">
                <label>تا تاریخ (میلادی)</label>
                <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>">
            </div>
            <div class="filter-group">
                <label>دسته‌بندی</label>
                <select name="category">
                    <option value="0">همه دسته‌ها</option>
                    <?php foreach ($categories as $c): ?>
                    <option value="<?= $c['id'] ?>" <?= $category_filter==$c['id']?'selected':'' ?>><?= htmlspecialchars($c['name']) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="filter-group" style="flex:none; display:flex; gap:8px; margin-top:20px;">
                <button type="submit" class="btn btn-primary btn-sm"><i class="fas fa-filter"></i> اعمال فیلتر</button>
                <a href="reports.php" class="btn btn-ghost btn-sm"><i class="fas fa-undo"></i> ریست</a>
            </div>
        </form>

        <div class="stats-grid">
            <div class="stat-card"><h3><?= number_format($stats['revenue']) ?></h3><p>درآمد کل (تومان)</p></div>
            <div class="stat-card"><h3><?= number_format($stats['orders']) ?></h3><p>تعداد سفارشات</p></div>
            <div class="stat-card"><h3><?= number_format($stats['avg_order']) ?></h3><p>میانگین خرید</p></div>
            <div class="stat-card"><h3 style="font-size:18px;"><?= htmlspecialchars($stats['top_cat']) ?></h3><p>پرفروش‌ترین دسته</p></div>
        </div>

        <div class="content-grid">
            <div class="glass-card">
                <div class="card-header"><h3><i class="fas fa-chart-line" style="margin-left:8px;"></i> روند درآمد روزانه</h3></div>
                <div class="chart-container">
                    <?php if (!empty($stats['chart'])): ?>
                        <?php foreach ($stats['chart'] as $item): 
                            $h = ($item['r'] / $max_chart) * 100;
                            $d = date('m/d', strtotime($item['d'])); 
                        ?>
                        <div class="bar-item"><div class="bar" style="height:<?= $h ?>%"><div class="tooltip-val"><?= number_format($item['r']) ?> ت</div></div><span class="bar-label"><?= $d ?></span></div>
                        <?php endforeach; ?>
                    <?php else: ?><div class="empty-state" style="width:100%; align-self:center;"><i class="fas fa-chart-area fa-2x"></i><br>داده‌ای برای این بازه موجود نیست</div><?php endif; ?>
                </div>
            </div>

            <div class="glass-card">
                <div class="card-header"><h3><i class="fas fa-fire" style="margin-left:8px;"></i> ۱۰ محصول برتر</h3></div>
                <div class="table-responsive">
                    <table class="data-table">
                        <thead><tr><th>محصول</th><th>فروش</th><th>درآمد</th></tr></thead>
                        <tbody>
                            <?php foreach ($stats['products'] as $p): ?>
                            <tr><td><?= htmlspecialchars($p['name']) ?></td><td><?= number_format($p['sold']) ?></td><td style="color:var(--primary);font-weight:600;"><?= number_format($p['rev']) ?> ت</td></tr>
                            <?php endforeach; ?>
                            <?php if (empty($stats['products'])): ?><tr><td colspan="3" class="text-center text-muted py-4">فروشی ثبت نشده است.</td></tr><?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</main>

<script src="assets/js/script.js"></script>
<?php include 'includes/admin-footer.php'; ?>