<?php
/**
 * Reports Dashboard
 * LandSales Pro - Analytics and reports overview
 */
define('APP_RUNNING', true);
require_once '../../db_connect.php';
require_once '../../includes/auth.php';
require_once '../../includes/subscription.php';
require_once '../../includes/functions.php';

initSecureSession();
requireAuth();
requireSubscription();

$userId = $_SESSION['user_id'];

// Date range
$period = $_GET['period'] ?? 'month';
switch ($period) {
    case 'week':
        $startDate = date('Y-m-d', strtotime('-7 days'));
        break;
    case 'year':
        $startDate = date('Y-m-d', strtotime('-1 year'));
        break;
    case 'all':
        $startDate = '2000-01-01';
        break;
    default: // month
        $startDate = date('Y-m-d', strtotime('-30 days'));
}
$endDate = date('Y-m-d');

// Sales Stats
$salesStats = db_fetch_one("SELECT
    COUNT(*) as total_sales,
    COALESCE(SUM(total_price), 0) as total_value,
    COALESCE(AVG(total_price), 0) as avg_sale
    FROM sales WHERE user_id = ? AND status = 'active' AND sale_date >= ?", 'is', [$userId, $startDate]);

// Payments Stats
$paymentStats = db_fetch_one("SELECT
    COUNT(*) as total_payments,
    COALESCE(SUM(amount), 0) as total_collected
    FROM payments WHERE user_id = ? AND status = 'confirmed' AND payment_date >= ?", 'is', [$userId, $startDate]);

// Plot Stats
$plotStats = db_fetch_one("SELECT
    COUNT(*) as total_plots,
    SUM(CASE WHEN status = 'available' THEN 1 ELSE 0 END) as available,
    SUM(CASE WHEN status = 'reserved' THEN 1 ELSE 0 END) as reserved,
    SUM(CASE WHEN status = 'sold' THEN 1 ELSE 0 END) as sold
    FROM plots WHERE user_id = ?", 'i', [$userId]);

// Outstanding Balance
$outstandingBalance = db_fetch_one("SELECT
    COALESCE(SUM(s.total_price) - SUM(COALESCE((SELECT SUM(amount) FROM payments WHERE sale_id = s.id AND status = 'confirmed'), 0)), 0) as balance
    FROM sales s WHERE s.user_id = ? AND s.status = 'active'", 'i', [$userId])['balance'];

// Sales by Month (last 6 months)
$monthlySales = db_fetch_all("SELECT
    DATE_FORMAT(sale_date, '%Y-%m') as month,
    DATE_FORMAT(sale_date, '%b %Y') as month_label,
    COUNT(*) as count,
    SUM(total_price) as value
    FROM sales WHERE user_id = ? AND status IN ('active', 'completed') AND sale_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
    ORDER BY month", 'i', [$userId]);

// Payments by Month (last 6 months)
$monthlyPayments = db_fetch_all("SELECT
    DATE_FORMAT(payment_date, '%Y-%m') as month,
    DATE_FORMAT(payment_date, '%b %Y') as month_label,
    COUNT(*) as count,
    SUM(amount) as value
    FROM payments WHERE user_id = ? AND status = 'confirmed' AND payment_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
    ORDER BY month", 'i', [$userId]);

// Top Projects by Sales
$topProjects = db_fetch_all("SELECT pr.name, COUNT(s.id) as sales_count, SUM(s.total_price) as total_value
    FROM projects pr
    LEFT JOIN plots p ON pr.id = p.project_id
    LEFT JOIN sales s ON p.id = s.plot_id AND s.status IN ('active', 'completed')
    WHERE pr.user_id = ?
    GROUP BY pr.id
    ORDER BY total_value DESC LIMIT 5", 'i', [$userId]);

// Recent Sales
$recentSales = db_fetch_all("SELECT s.*, b.full_name as buyer_name, p.plot_number, pr.name as project_name
    FROM sales s
    JOIN buyers b ON s.buyer_id = b.id
    JOIN plots p ON s.plot_id = p.id
    JOIN projects pr ON p.project_id = pr.id
    WHERE s.user_id = ?
    ORDER BY s.created_at DESC LIMIT 5", 'i', [$userId]);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Reports - <?php echo APP_NAME; ?></title>
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700;800&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="<?php echo SITE_URL; ?>/assets/font-awesome/css/all.min.css">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <?php include '../includes/sidebar.php'; ?>

    <div class="main-content">
        <?php
        $pageTitle = 'Reports & Analytics';
        $headerActions = '<div class="period-selector">
            <a href="?period=week" class="period-btn ' . ($period === 'week' ? 'active' : '') . '">Week</a>
            <a href="?period=month" class="period-btn ' . ($period === 'month' ? 'active' : '') . '">Month</a>
            <a href="?period=year" class="period-btn ' . ($period === 'year' ? 'active' : '') . '">Year</a>
            <a href="?period=all" class="period-btn ' . ($period === 'all' ? 'active' : '') . '">All Time</a>
        </div>';
        include '../includes/header.php';
        ?>

        <div class="content-wrapper">
            <!-- Stats Cards -->
            <div class="stats-grid">
                <div class="stat-card gradient-purple">
                    <div class="stat-icon"><i class="fas fa-handshake"></i></div>
                    <div class="stat-content">
                        <div class="stat-value"><?php echo number_format($salesStats['total_sales']); ?></div>
                        <div class="stat-label">Sales</div>
                    </div>
                    <div class="stat-trend">
                        <span>TZS <?php echo formatMoney($salesStats['total_value']); ?></span>
                    </div>
                </div>

                <div class="stat-card gradient-green">
                    <div class="stat-icon"><i class="fas fa-money-bill-wave"></i></div>
                    <div class="stat-content">
                        <div class="stat-value">TZS <?php echo formatMoney($paymentStats['total_collected']); ?></div>
                        <div class="stat-label">Collected</div>
                    </div>
                    <div class="stat-trend">
                        <span><?php echo number_format($paymentStats['total_payments']); ?> payments</span>
                    </div>
                </div>

                <div class="stat-card gradient-orange">
                    <div class="stat-icon"><i class="fas fa-clock"></i></div>
                    <div class="stat-content">
                        <div class="stat-value">TZS <?php echo formatMoney($outstandingBalance); ?></div>
                        <div class="stat-label">Outstanding</div>
                    </div>
                    <div class="stat-trend">
                        <span>Pending collection</span>
                    </div>
                </div>

                <div class="stat-card gradient-blue">
                    <div class="stat-icon"><i class="fas fa-th"></i></div>
                    <div class="stat-content">
                        <div class="stat-value"><?php echo number_format($plotStats['total_plots']); ?></div>
                        <div class="stat-label">Total Plots</div>
                    </div>
                    <div class="stat-trend">
                        <span class="text-success"><?php echo $plotStats['available']; ?> available</span>
                    </div>
                </div>
            </div>

            <!-- Charts Row -->
            <div class="charts-grid">
                <div class="card">
                    <div class="card-header">
                        <h3><i class="fas fa-chart-line"></i> Sales & Collections Trend</h3>
                    </div>
                    <div class="card-body">
                        <canvas id="trendChart" height="250"></canvas>
                    </div>
                </div>

                <div class="card">
                    <div class="card-header">
                        <h3><i class="fas fa-chart-pie"></i> Plot Status Distribution</h3>
                    </div>
                    <div class="card-body">
                        <canvas id="plotStatusChart" height="250"></canvas>
                    </div>
                </div>
            </div>

            <!-- Quick Reports -->
            <div class="quick-reports">
                <h2>Quick Reports</h2>
                <div class="reports-grid">
                    <a href="project-worth.php" class="report-card featured">
                        <div class="report-icon"><i class="fas fa-gem"></i></div>
                        <div class="report-info">
                            <h4>Project Worth & Collections</h4>
                            <p>Total value, collections by date range per project</p>
                        </div>
                        <i class="fas fa-chevron-right"></i>
                    </a>
                    <a href="sales.php" class="report-card">
                        <div class="report-icon"><i class="fas fa-chart-bar"></i></div>
                        <div class="report-info">
                            <h4>Sales Report</h4>
                            <p>Detailed sales analysis by date, project, agent</p>
                        </div>
                        <i class="fas fa-chevron-right"></i>
                    </a>
                    <a href="payments.php" class="report-card">
                        <div class="report-icon"><i class="fas fa-file-invoice-dollar"></i></div>
                        <div class="report-info">
                            <h4>Payments Report</h4>
                            <p>Track payments, collections, and outstanding</p>
                        </div>
                        <i class="fas fa-chevron-right"></i>
                    </a>
                    <a href="projects.php" class="report-card">
                        <div class="report-icon"><i class="fas fa-building"></i></div>
                        <div class="report-info">
                            <h4>Project Performance</h4>
                            <p>Project-wise sales and availability</p>
                        </div>
                        <i class="fas fa-chevron-right"></i>
                    </a>
                    <a href="buyers.php" class="report-card">
                        <div class="report-icon"><i class="fas fa-users"></i></div>
                        <div class="report-info">
                            <h4>Buyer Analysis</h4>
                            <p>Top buyers and purchase patterns</p>
                        </div>
                        <i class="fas fa-chevron-right"></i>
                    </a>
                </div>
            </div>

            <div class="bottom-grid">
                <!-- Top Projects -->
                <div class="card">
                    <div class="card-header">
                        <h3><i class="fas fa-trophy"></i> Top Projects by Sales</h3>
                    </div>
                    <div class="card-body">
                        <?php if (empty($topProjects)): ?>
                        <div class="empty-state-mini">
                            <p>No sales data yet</p>
                        </div>
                        <?php else: ?>
                        <div class="project-list">
                            <?php foreach ($topProjects as $index => $project): ?>
                            <div class="project-item">
                                <div class="project-rank"><?php echo $index + 1; ?></div>
                                <div class="project-info">
                                    <div class="project-name"><?php echo htmlspecialchars($project['name']); ?></div>
                                    <div class="project-sales"><?php echo $project['sales_count']; ?> sales</div>
                                </div>
                                <div class="project-value">TZS <?php echo formatMoney($project['total_value'] ?? 0); ?></div>
                            </div>
                            <?php endforeach; ?>
                        </div>
                        <?php endif; ?>
                    </div>
                </div>

                <!-- Recent Sales -->
                <div class="card">
                    <div class="card-header">
                        <h3><i class="fas fa-history"></i> Recent Sales</h3>
                        <a href="../sales/index.php" class="btn btn-outline btn-sm">View All</a>
                    </div>
                    <div class="card-body">
                        <?php if (empty($recentSales)): ?>
                        <div class="empty-state-mini">
                            <p>No sales recorded yet</p>
                        </div>
                        <?php else: ?>
                        <div class="recent-list">
                            <?php foreach ($recentSales as $sale): ?>
                            <a href="../sales/view.php?id=<?php echo $sale['id']; ?>" class="recent-item">
                                <div class="recent-avatar">
                                    <?php echo strtoupper(substr($sale['buyer_name'], 0, 2)); ?>
                                </div>
                                <div class="recent-info">
                                    <div class="recent-name"><?php echo htmlspecialchars($sale['buyer_name']); ?></div>
                                    <div class="recent-details">Plot <?php echo $sale['plot_number']; ?> - <?php echo htmlspecialchars($sale['project_name']); ?></div>
                                </div>
                                <div class="recent-amount">TZS <?php echo formatMoney($sale['total_price']); ?></div>
                            </a>
                            <?php endforeach; ?>
                        </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <style>
        body { font-family: 'Inter', -apple-system, BlinkMacSystemFont, sans-serif; margin: 0; background: #f1f5f9; }

        .header-actions { display: flex; gap: 0.75rem; }
        .period-selector { display: flex; background: white; border-radius: 8px; padding: 4px; box-shadow: 0 2px 8px rgba(0,0,0,0.05); }
        .period-btn { padding: 0.5rem 1rem; border-radius: 6px; text-decoration: none; font-size: 0.85rem; font-weight: 500; color: #64748b; transition: all 0.2s; }
        .period-btn.active { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; }
        .period-btn:hover:not(.active) { background: #f1f5f9; }

        .btn { padding: 0.625rem 1.25rem; border-radius: 8px; font-weight: 500; text-decoration: none; display: inline-flex; align-items: center; gap: 0.5rem; transition: all 0.2s; border: none; cursor: pointer; font-size: 0.875rem; }
        .btn-sm { padding: 0.5rem 0.75rem; font-size: 0.8rem; }
        .btn-outline { border: 1px solid #e2e8f0; background: white; color: #64748b; }

        .stats-grid { display: grid; grid-template-columns: repeat(4, 1fr); gap: 1.5rem; margin-bottom: 1.5rem; }
        .stat-card { background: white; border-radius: 14px; padding: 1.5rem; display: flex; align-items: center; gap: 1rem; position: relative; overflow: hidden; box-shadow: 0 2px 8px rgba(0,0,0,0.05); }
        .stat-card::before { content: ''; position: absolute; top: 0; left: 0; right: 0; height: 4px; }
        .gradient-purple::before { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); }
        .gradient-green::before { background: linear-gradient(135deg, #22c55e 0%, #16a34a 100%); }
        .gradient-orange::before { background: linear-gradient(135deg, #f59e0b 0%, #d97706 100%); }
        .gradient-blue::before { background: linear-gradient(135deg, #3b82f6 0%, #2563eb 100%); }
        .stat-icon { width: 56px; height: 56px; border-radius: 12px; display: flex; align-items: center; justify-content: center; font-size: 1.5rem; }
        .gradient-purple .stat-icon { background: linear-gradient(135deg, rgba(102, 126, 234, 0.1) 0%, rgba(118, 75, 162, 0.1) 100%); color: #667eea; }
        .gradient-green .stat-icon { background: rgba(34, 197, 94, 0.1); color: #22c55e; }
        .gradient-orange .stat-icon { background: rgba(245, 158, 11, 0.1); color: #f59e0b; }
        .gradient-blue .stat-icon { background: rgba(59, 130, 246, 0.1); color: #3b82f6; }
        .stat-content { flex: 1; }
        .stat-value { font-size: 1.5rem; font-weight: 700; color: #1e293b; }
        .stat-label { font-size: 0.85rem; color: #64748b; }
        .stat-trend { font-size: 0.8rem; color: #64748b; }
        .text-success { color: #16a34a; }

        .charts-grid { display: grid; grid-template-columns: 2fr 1fr; gap: 1.5rem; margin-bottom: 1.5rem; }
        .card { background: white; border-radius: 14px; box-shadow: 0 2px 8px rgba(0,0,0,0.05); overflow: hidden; }
        .card-header { padding: 1rem 1.25rem; border-bottom: 1px solid #f1f5f9; display: flex; justify-content: space-between; align-items: center; }
        .card-header h3 { margin: 0; font-size: 0.95rem; font-weight: 600; color: #1e293b; display: flex; align-items: center; gap: 0.5rem; }
        .card-header h3 i { color: #667eea; }
        .card-body { padding: 1.25rem; }

        .quick-reports { margin-bottom: 1.5rem; }
        .quick-reports h2 { font-size: 1.1rem; color: #1e293b; margin-bottom: 1rem; }
        .reports-grid { display: grid; grid-template-columns: repeat(4, 1fr); gap: 1rem; }
        .report-card { background: white; border-radius: 12px; padding: 1.25rem; display: flex; align-items: center; gap: 1rem; text-decoration: none; transition: all 0.2s; box-shadow: 0 2px 8px rgba(0,0,0,0.05); }
        .report-card:hover { transform: translateY(-2px); box-shadow: 0 4px 15px rgba(0,0,0,0.1); }
        .report-icon { width: 48px; height: 48px; border-radius: 12px; background: linear-gradient(135deg, rgba(102, 126, 234, 0.1) 0%, rgba(118, 75, 162, 0.1) 100%); display: flex; align-items: center; justify-content: center; color: #667eea; font-size: 1.25rem; }
        .report-info { flex: 1; }
        .report-info h4 { margin: 0 0 0.25rem; font-size: 0.95rem; color: #1e293b; }
        .report-info p { margin: 0; font-size: 0.75rem; color: #64748b; }
        .report-card > i { color: #94a3b8; }
        .report-card.featured { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; }
        .report-card.featured .report-icon { background: rgba(255,255,255,0.2); color: white; }
        .report-card.featured .report-info h4 { color: white; }
        .report-card.featured .report-info p { color: rgba(255,255,255,0.8); }
        .report-card.featured > i { color: rgba(255,255,255,0.8); }

        .bottom-grid { display: grid; grid-template-columns: repeat(2, 1fr); gap: 1.5rem; }

        .project-list { }
        .project-item { display: flex; align-items: center; gap: 1rem; padding: 0.75rem 0; border-bottom: 1px solid #f1f5f9; }
        .project-item:last-child { border-bottom: none; }
        .project-rank { width: 28px; height: 28px; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; border-radius: 50%; display: flex; align-items: center; justify-content: center; font-weight: 700; font-size: 0.85rem; }
        .project-info { flex: 1; }
        .project-name { font-weight: 500; color: #1e293b; }
        .project-sales { font-size: 0.8rem; color: #64748b; }
        .project-value { font-weight: 600; color: #667eea; }

        .recent-list { }
        .recent-item { display: flex; align-items: center; gap: 1rem; padding: 0.75rem 0; border-bottom: 1px solid #f1f5f9; text-decoration: none; transition: all 0.2s; }
        .recent-item:last-child { border-bottom: none; }
        .recent-item:hover { background: #f8fafc; margin: 0 -1.25rem; padding-left: 1.25rem; padding-right: 1.25rem; }
        .recent-avatar { width: 40px; height: 40px; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; border-radius: 50%; display: flex; align-items: center; justify-content: center; font-weight: 600; font-size: 0.85rem; }
        .recent-info { flex: 1; }
        .recent-name { font-weight: 500; color: #1e293b; }
        .recent-details { font-size: 0.8rem; color: #64748b; }
        .recent-amount { font-weight: 600; color: #16a34a; }

        .empty-state-mini { text-align: center; padding: 2rem; color: #94a3b8; }

        @media (max-width: 1200px) {
            .stats-grid { grid-template-columns: repeat(2, 1fr); }
            .reports-grid { grid-template-columns: repeat(2, 1fr); }
        }

        @media (max-width: 768px) {
            .stats-grid { grid-template-columns: 1fr; }
            .charts-grid { grid-template-columns: 1fr; }
            .reports-grid { grid-template-columns: 1fr; }
            .bottom-grid { grid-template-columns: 1fr; }
            .period-selector { flex-wrap: wrap; }
        }
    </style>

    <script>
        // Sales & Payments Trend Chart
        const trendCtx = document.getElementById('trendChart').getContext('2d');
        new Chart(trendCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode(array_column($monthlySales, 'month_label')); ?>,
                datasets: [{
                    label: 'Sales Value',
                    data: <?php echo json_encode(array_column($monthlySales, 'value')); ?>,
                    borderColor: '#667eea',
                    backgroundColor: 'rgba(102, 126, 234, 0.1)',
                    fill: true,
                    tension: 0.4
                }, {
                    label: 'Payments',
                    data: <?php echo json_encode(array_column($monthlyPayments, 'value')); ?>,
                    borderColor: '#22c55e',
                    backgroundColor: 'rgba(34, 197, 94, 0.1)',
                    fill: true,
                    tension: 0.4
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: { position: 'bottom' }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'TZS ' + (value / 1000000).toFixed(1) + 'M';
                            }
                        }
                    }
                }
            }
        });

        // Plot Status Chart
        const statusCtx = document.getElementById('plotStatusChart').getContext('2d');
        new Chart(statusCtx, {
            type: 'doughnut',
            data: {
                labels: ['Available', 'Reserved', 'Sold'],
                datasets: [{
                    data: [<?php echo $plotStats['available']; ?>, <?php echo $plotStats['reserved']; ?>, <?php echo $plotStats['sold']; ?>],
                    backgroundColor: ['#22c55e', '#f59e0b', '#ef4444'],
                    borderWidth: 0
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: { position: 'bottom' }
                },
                cutout: '60%'
            }
        });
    </script>
</body>
</html>
