<?php
// reports.php - Sales and Bidding Reports
// File location: /bidwheels.net/admin/reports.php

require_once '../includes/auth.php';
require_once '../includes/functions.php';
require_once 'includes/admin_header.php';

requireAdmin();

// Get date range filters
$start_date = isset($_GET['start_date']) ? sanitize($_GET['start_date']) : date('Y-m-d', strtotime('-30 days'));
$end_date = isset($_GET['end_date']) ? sanitize($_GET['end_date']) : date('Y-m-d');

// Get report type
$report_type = isset($_GET['type']) ? $_GET['type'] : 'overview';

// Sales Report Data
$sales_data = fetchAll("
    SELECT 
        DATE(created_at) as date,
        COUNT(*) as total_payments,
        SUM(CASE WHEN payment_method = 'Credit Card' THEN 1 ELSE 0 END) as credit_card_count,
        SUM(CASE WHEN payment_method = 'Bank Transfer' THEN 1 ELSE 0 END) as bank_transfer_count
    FROM payments
    WHERE DATE(created_at) BETWEEN '$start_date' AND '$end_date'
    GROUP BY DATE(created_at)
    ORDER BY date DESC
");

// Total sales stats
$total_stats = fetchOne("
    SELECT 
        COUNT(*) as total_payments,
        COUNT(DISTINCT user_id) as unique_customers
    FROM payments
    WHERE DATE(created_at) BETWEEN '$start_date' AND '$end_date'
");

// Bidding statistics
$bidding_stats = fetchOne("
    SELECT 
        COUNT(*) as total_bids,
        SUM(CASE WHEN is_system_bid = 1 THEN 1 ELSE 0 END) as system_bids,
        SUM(CASE WHEN is_system_bid = 0 THEN 1 ELSE 0 END) as real_bids,
        COUNT(DISTINCT product_id) as products_with_bids,
        COUNT(DISTINCT user_id) as unique_bidders
    FROM bids
    WHERE DATE(created_at) BETWEEN '$start_date' AND '$end_date'
");

// Top products by bids
$top_products = fetchAll("
    SELECT 
        p.id,
        p.name,
        COUNT(b.id) as bid_count,
        MAX(b.amount) as highest_bid,
        p.starting_price
    FROM products p
    LEFT JOIN bids b ON p.id = b.product_id
    WHERE DATE(b.created_at) BETWEEN '$start_date' AND '$end_date'
    GROUP BY p.id
    ORDER BY bid_count DESC
    LIMIT 10
");

// Top bidders
$top_bidders = fetchAll("
    SELECT 
        u.id,
        u.name,
        u.email,
        COUNT(b.id) as bid_count,
        SUM(b.amount) as total_bid_amount
    FROM users u
    LEFT JOIN bids b ON u.id = b.user_id
    WHERE DATE(b.created_at) BETWEEN '$start_date' AND '$end_date' AND u.is_dummy = 0
    GROUP BY u.id
    ORDER BY bid_count DESC
    LIMIT 10
");

// Category performance
$category_performance = fetchAll("
    SELECT 
        c.id,
        c.name,
        COUNT(DISTINCT p.id) as product_count,
        COUNT(b.id) as bid_count,
        AVG(b.amount) as avg_bid
    FROM categories c
    LEFT JOIN products p ON c.id = p.category_id
    LEFT JOIN bids b ON p.id = b.product_id AND DATE(b.created_at) BETWEEN '$start_date' AND '$end_date'
    GROUP BY c.id
    ORDER BY bid_count DESC
");
?>

<div class="admin-page">
    <h1 class="page-title">Reports & Analytics</h1>
    
    <!-- Date Filter -->
    <div class="filter-panel" style="background: white; padding: 20px; border-radius: 10px; margin-bottom: 25px;">
        <form method="GET" action="" class="filter-form" style="display: flex; gap: 15px; align-items: flex-end; flex-wrap: wrap;">
            <div class="form-group">
                <label>Report Type</label>
                <select name="type" class="form-control" style="padding: 10px;">
                    <option value="overview" <?php echo $report_type == 'overview' ? 'selected' : ''; ?>>Overview</option>
                    <option value="products" <?php echo $report_type == 'products' ? 'selected' : ''; ?>>Products</option>
                    <option value="users" <?php echo $report_type == 'users' ? 'selected' : ''; ?>>Users</option>
                </select>
            </div>
            <div class="form-group">
                <label>Start Date</label>
                <input type="date" name="start_date" value="<?php echo $start_date; ?>" class="form-control" style="padding: 10px;">
            </div>
            <div class="form-group">
                <label>End Date</label>
                <input type="date" name="end_date" value="<?php echo $end_date; ?>" class="form-control" style="padding: 10px;">
            </div>
            <div class="form-group">
                <button type="submit" class="btn btn-primary">Apply Filter</button>
                <a href="export_data.php?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>" class="btn btn-secondary" style="margin-left: 10px;">Export to CSV</a>
            </div>
        </form>
    </div>
    
    <!-- Stats Cards -->
    <div class="stats-grid" style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; margin-bottom: 30px;">
        <div class="stat-card" style="background: white; padding: 20px; border-radius: 10px; text-align: center;">
            <i class="fas fa-chart-line" style="font-size: 36px; color: #e63946;"></i>
            <div class="stat-number" style="font-size: 28px; font-weight: bold;"><?php echo $total_stats['total_payments'] ?? 0; ?></div>
            <div class="stat-label">Total Payments</div>
        </div>
        <div class="stat-card" style="background: white; padding: 20px; border-radius: 10px; text-align: center;">
            <i class="fas fa-users" style="font-size: 36px; color: #e63946;"></i>
            <div class="stat-number" style="font-size: 28px; font-weight: bold;"><?php echo $total_stats['unique_customers'] ?? 0; ?></div>
            <div class="stat-label">Unique Customers</div>
        </div>
        <div class="stat-card" style="background: white; padding: 20px; border-radius: 10px; text-align: center;">
            <i class="fas fa-gavel" style="font-size: 36px; color: #e63946;"></i>
            <div class="stat-number" style="font-size: 28px; font-weight: bold;"><?php echo $bidding_stats['total_bids'] ?? 0; ?></div>
            <div class="stat-label">Total Bids</div>
        </div>
        <div class="stat-card" style="background: white; padding: 20px; border-radius: 10px; text-align: center;">
            <i class="fas fa-car" style="font-size: 36px; color: #e63946;"></i>
            <div class="stat-number" style="font-size: 28px; font-weight: bold;"><?php echo $bidding_stats['products_with_bids'] ?? 0; ?></div>
            <div class="stat-label">Products with Bids</div>
        </div>
    </div>
    
    <?php if ($report_type == 'overview'): ?>
        <!-- Bid Statistics -->
        <div class="report-section" style="background: white; padding: 20px; border-radius: 10px; margin-bottom: 25px;">
            <h2 style="margin-bottom: 20px;">Bidding Statistics</h2>
            <div class="bid-stats" style="display: grid; grid-template-columns: repeat(3, 1fr); gap: 15px;">
                <div class="stat-item" style="text-align: center; padding: 15px; background: #f8f9fa; border-radius: 5px;">
                    <div class="stat-value" style="font-size: 24px; font-weight: bold; color: #28a745;"><?php echo $bidding_stats['real_bids'] ?? 0; ?></div>
                    <div class="stat-label">Real User Bids</div>
                </div>
                <div class="stat-item" style="text-align: center; padding: 15px; background: #f8f9fa; border-radius: 5px;">
                    <div class="stat-value" style="font-size: 24px; font-weight: bold; color: #ffc107;"><?php echo $bidding_stats['system_bids'] ?? 0; ?></div>
                    <div class="stat-label">System Bids</div>
                </div>
                <div class="stat-item" style="text-align: center; padding: 15px; background: #f8f9fa; border-radius: 5px;">
                    <div class="stat-value" style="font-size: 24px; font-weight: bold; color: #17a2b8;"><?php echo $bidding_stats['unique_bidders'] ?? 0; ?></div>
                    <div class="stat-label">Unique Bidders</div>
                </div>
            </div>
        </div>
        
        <!-- Daily Sales Trend -->
        <div class="report-section" style="background: white; padding: 20px; border-radius: 10px; margin-bottom: 25px;">
            <h2 style="margin-bottom: 20px;">Daily Payment Trend</h2>
            <div class="table-responsive">
                <table class="admin-table">
                    <thead>
                        <tr>
                            <th>Date</th>
                            <th>Total Payments</th>
                            <th>Credit Card</th>
                            <th>Bank Transfer</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($sales_data as $row): ?>
                            <tr>
                                <td><?php echo date('M d, Y', strtotime($row['date'])); ?></td>
                                <td><?php echo $row['total_payments']; ?></td>
                                <td><?php echo $row['credit_card_count']; ?></td>
                                <td><?php echo $row['bank_transfer_count']; ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <?php if (count($sales_data) == 0): ?>
                            <tr><td colspan="4" style="text-align: center;">No data available</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
        
        <!-- Top Products -->
        <div class="report-section" style="background: white; padding: 20px; border-radius: 10px; margin-bottom: 25px;">
            <h2 style="margin-bottom: 20px;">Top Products by Bids</h2>
            <div class="table-responsive">
                <table class="admin-table">
                    <thead>
                        <tr>
                            <th>Product Name</th>
                            <th>Starting Price</th>
                            <th>Highest Bid</th>
                            <th>Total Bids</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($top_products as $product): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($product['name']); ?></td>
                                <td><?php echo formatPrice($product['starting_price']); ?></td>
                                <td><?php echo formatPrice($product['highest_bid'] ?? $product['starting_price']); ?></td>
                                <td><?php echo $product['bid_count']; ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <?php if (count($top_products) == 0): ?>
                            <tr><td colspan="4" style="text-align: center;">No data available</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
        
        <!-- Top Bidders -->
        <div class="report-section" style="background: white; padding: 20px; border-radius: 10px; margin-bottom: 25px;">
            <h2 style="margin-bottom: 20px;">Top Bidders</h2>
            <div class="table-responsive">
                <table class="admin-table">
                    <thead>
                        <tr>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Bids Placed</th>
                            <th>Total Bid Amount</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($top_bidders as $bidder): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($bidder['name']); ?></td>
                                <td><?php echo htmlspecialchars($bidder['email']); ?></td>
                                <td><?php echo $bidder['bid_count']; ?></td>
                                <td><?php echo formatPrice($bidder['total_bid_amount']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <?php if (count($top_bidders) == 0): ?>
                            <tr><td colspan="4" style="text-align: center;">No data available</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
        
    <?php elseif ($report_type == 'products'): ?>
        <!-- Category Performance -->
        <div class="report-section" style="background: white; padding: 20px; border-radius: 10px; margin-bottom: 25px;">
            <h2 style="margin-bottom: 20px;">Category Performance</h2>
            <div class="table-responsive">
                <table class="admin-table">
                    <thead>
                        <tr>
                            <th>Category</th>
                            <th>Products</th>
                            <th>Total Bids</th>
                            <th>Average Bid</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($category_performance as $cat): ?>
                            <tr>
                                <td><i class="<?php echo $cat['icon'] ?? 'fas fa-car'; ?>"></i> <?php echo htmlspecialchars($cat['name']); ?></td>
                                <td><?php echo $cat['product_count']; ?></td>
                                <td><?php echo $cat['bid_count']; ?></td>
                                <td><?php echo formatPrice($cat['avg_bid'] ?? 0); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    <?php elseif ($report_type == 'users'): ?>
        <!-- User Registration Stats -->
        <div class="report-section" style="background: white; padding: 20px; border-radius: 10px; margin-bottom: 25px;">
            <h2 style="margin-bottom: 20px;">User Registration</h2>
            <?php
            $user_stats = fetchAll("
                SELECT 
                    DATE(created_at) as date,
                    COUNT(*) as new_users
                FROM users
                WHERE is_dummy = 0 AND DATE(created_at) BETWEEN '$start_date' AND '$end_date'
                GROUP BY DATE(created_at)
                ORDER BY date DESC
            ");
            ?>
            <div class="table-responsive">
                <table class="admin-table">
                    <thead>
                        <tr>
                            <th>Date</th>
                            <th>New Users</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($user_stats as $stat): ?>
                            <tr>
                                <td><?php echo date('M d, Y', strtotime($stat['date'])); ?></td>
                                <td><?php echo $stat['new_users']; ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <?php if (count($user_stats) == 0): ?>
                            <tr><td colspan="2" style="text-align: center;">No data available</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    <?php endif; ?>
</div>

<style>
.filter-panel {
    background: white;
    padding: 20px;
    border-radius: 10px;
    margin-bottom: 25px;
}
.report-section {
    background: white;
    padding: 20px;
    border-radius: 10px;
    margin-bottom: 25px;
}
.report-section h2 {
    border-bottom: 2px solid #eee;
    padding-bottom: 10px;
    margin-bottom: 20px;
}
.btn-secondary {
    background: #6c757d;
    color: white;
    padding: 10px 15px;
    text-decoration: none;
    border-radius: 5px;
}
</style>

<?php require_once 'includes/admin_footer.php'; ?>