<?php
session_start();

// Database connection
$host = 'localhost';
$dbname = 'jv_overseas';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// Authentication check
if (!isset($_SESSION['user_id']) || $_SESSION['user_type'] !== 'admin') {
    header('Location: admin-login.php');
    exit;
}

// Get admin data
$stmt = $pdo->prepare("SELECT a.*, u.email FROM admins a JOIN users u ON a.id = u.id WHERE a.id = ?");
$stmt->execute([$_SESSION['user_id']]);
$admin = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$admin) {
    die("Admin profile not found.");
}

// Get date range filter inputs
$date_from = $_GET['date_from'] ?? date('Y-m-01'); // First day of current month
$date_to = $_GET['date_to'] ?? date('Y-m-t'); // Last day of current month

// Financial statistics queries
$stats = [
    'total_revenue' => $pdo->query("SELECT SUM(amount) FROM payments WHERE status = 'completed'")->fetchColumn() ?: 0,
    'monthly_revenue' => $pdo->query("SELECT SUM(amount) FROM payments WHERE status = 'completed' AND payment_date BETWEEN '$date_from' AND '$date_to'")->fetchColumn() ?: 0,
    'pending_payments' => $pdo->query("SELECT SUM(amount) FROM payments WHERE status = 'pending'")->fetchColumn() ?: 0,
    'refunded_payments' => $pdo->query("SELECT SUM(amount) FROM payments WHERE status = 'refunded'")->fetchColumn() ?: 0,
    'total_invoices' => $pdo->query("SELECT COUNT(*) FROM invoices")->fetchColumn(),
    'unpaid_invoices' => $pdo->query("SELECT SUM(amount) FROM invoices WHERE status = 'unpaid'")->fetchColumn() ?: 0,
];

// Revenue by payment type
$revenue_by_type = $pdo->query("
    SELECT 
        pt.name AS payment_type_name,
        SUM(p.amount) AS total_amount,
        COUNT(*) AS transaction_count
    FROM payments p
    JOIN payment_types pt ON p.payment_type_id = pt.id
    WHERE p.status = 'completed'
    GROUP BY pt.id, pt.name
    ORDER BY total_amount DESC
")->fetchAll();

// Monthly revenue (last 12 months)
$revenue_by_month = $pdo->query("
    SELECT 
        DATE_FORMAT(payment_date, '%Y-%m') AS month,
        DATE_FORMAT(payment_date, '%b %Y') AS month_name,
        SUM(amount) AS monthly_revenue
    FROM payments 
    WHERE status = 'completed' 
    AND payment_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
    ORDER BY month
")->fetchAll();

// Outstanding invoices with student email (fix join to users table)
$outstanding_invoices = $pdo->query("
    SELECT 
        i.id,
        i.amount,
        i.due_date,
        i.description,
        s.first_name,
        s.last_name,
        u.email
    FROM invoices i
    JOIN students s ON i.student_id = s.id
    JOIN users u ON s.id = u.id
    WHERE i.status = 'unpaid'
    ORDER BY i.due_date ASC
    LIMIT 10
")->fetchAll();

// Average transaction value
$avg_transaction = $pdo->query("SELECT AVG(amount) FROM payments WHERE status = 'completed'")->fetchColumn() ?: 0;

// Logout handler
if (isset($_GET['logout'])) {
    session_destroy();
    header('Location: admin-login.php');
    exit;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Financial Reports - JV Overseas Education</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport">
    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Jost:wght@500;600;700&family=Open+Sans:wght@400;600&display=swap" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.10.0/css/all.min.css" rel="stylesheet">
    <link href="lib/owlcarousel/assets/owl.carousel.min.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">
    <style>
        .stat-card {
            border-left: 5px solid #3fb7fe;
            margin-bottom: 20px;
        }
        .revenue-card {
            background: linear-gradient(135deg, #28a745, #1e7e34);
            color: white;
        }
        .pending-card {
            background: linear-gradient(135deg, #ffc107, #d39e00);
            color: white;
        }
        .refunded-card {
            background: linear-gradient(135deg, #dc3545, #c82333);
            color: white;
        }
        .invoice-card {
            background: linear-gradient(135deg, #17a2b8, #117a8b);
            color: white;
        }
        .chart-container {
            height: 300px;
            margin-bottom: 30px;
        }
        .payment-type-item {
            padding: 15px;
            border-bottom: 1px solid #eee;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        .payment-type-item:last-child {
            border-bottom: none;
        }
        .payment-type-name {
            font-weight: 600;
        }
        .payment-type-amount {
            font-weight: 600;
        }
        .payment-type-count {
            color: #6c757d;
            font-size: 0.9em;
        }
    </style>
</head>
<body>

<?php include 'admin-navbar.php'; ?>

<div class="container py-5">
    <div class="section-title position-relative mb-4">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Reports</h6>
        <h1 class="display-5">Financial Analytics Report</h1>
    </div>

    <!-- Date Range Filter -->
    <div class="row mb-4">
        <div class="col-lg-6">
            <form method="GET" class="d-flex">
                <div class="flex-grow-1 mr-2">
                    <label>Date From</label>
                    <input type="date" name="date_from" class="form-control" value="<?= htmlspecialchars($date_from) ?>">
                </div>
                <div class="flex-grow-1 mr-2">
                    <label>Date To</label>
                    <input type="date" name="date_to" class="form-control" value="<?= htmlspecialchars($date_to) ?>">
                </div>
                <div class="align-self-end">
                    <button type="submit" class="btn btn-primary">Filter</button>
                </div>
            </form>
        </div>
    </div>

    <!-- Financial Summary -->
    <div class="row mb-4">
        <div class="col-md-3 mb-3">
            <div class="stat-card revenue-card">
                <div class="card-body">
                    <h6>Total Revenue</h6>
                    <h3>$<?= number_format($stats['total_revenue'], 2) ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card pending-card">
                <div class="card-body">
                    <h6>Pending Payments</h6>
                    <h3>$<?= number_format($stats['pending_payments'], 2) ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card refunded-card">
                <div class="card-body">
                    <h6>Refunded</h6>
                    <h3>$<?= number_format($stats['refunded_payments'], 2) ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card invoice-card">
                <div class="card-body">
                    <h6>Unpaid Invoices</h6>
                    <h3>$<?= number_format($stats['unpaid_invoices'], 2) ?></h3>
                </div>
            </div>
        </div>
    </div>

    <!-- Monthly Revenue Chart -->
    <div class="section-title position-relative mb-4">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Revenue Trends</h6>
        <h1 class="display-6">Monthly Revenue (Last 12 Months)</h1>
    </div>
    <div class="bg-white p-4 rounded mb-4">
        <canvas id="revenueChart" class="chart-container"></canvas>
    </div>

    <div class="row">
        <div class="col-lg-6">
            <!-- Revenue by Payment Type -->
            <div class="section-title position-relative mb-4">
                <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Revenue Sources</h6>
                <h1 class="display-6">Revenue by Payment Type</h1>
            </div>
            <div class="bg-white p-4 rounded">
                <?php foreach ($revenue_by_type as $type): ?>
                <div class="payment-type-item">
                    <div>
                        <div class="payment-type-name"><?= htmlspecialchars($type['payment_type_name']) ?></div>
                        <div class="payment-type-count"><?= $type['transaction_count'] ?> transactions</div>
                    </div>
                    <div class="payment-type-amount">$<?= number_format($type['total_amount'], 2) ?></div>
                </div>
                <?php endforeach; ?>
            </div>
        </div>

        <div class="col-lg-6">
            <!-- Key Financial Metrics -->
            <div class="section-title position-relative mb-4">
                <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Key Metrics</h6>
                <h1 class="display-6">Financial Performance</h1>
            </div>
            <div class="bg-white p-4 rounded">
                <div class="payment-type-item">
                    <div class="payment-type-name">Monthly Revenue (<?= date('M Y', strtotime($date_from)) ?>)</div>
                    <div class="payment-type-amount">$<?= number_format($stats['monthly_revenue'], 2) ?></div>
                </div>
                <div class="payment-type-item">
                    <div class="payment-type-name">Average Transaction Value</div>
                    <div class="payment-type-amount">$<?= number_format($avg_transaction, 2) ?></div>
                </div>
                <div class="payment-type-item">
                    <div class="payment-type-name">Total Invoices</div>
                    <div class="payment-type-amount"><?= $stats['total_invoices'] ?></div>
                </div>
                <div class="payment-type-item">
                    <div class="payment-type-name">Outstanding Balance</div>
                    <div class="payment-type-amount">$<?= number_format($stats['unpaid_invoices'], 2) ?></div>
                </div>
            </div>
        </div>
    </div>

    <!-- Outstanding Invoices -->
    <div class="section-title position-relative mb-4 mt-5">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Collections</h6>
        <h1 class="display-6">Outstanding Invoices</h1>
    </div>
    <div class="table-responsive">
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Invoice</th>
                    <th>Student</th>
                    <th>Email</th>
                    <th>Amount</th>
                    <th>Due Date</th>
                    <th>Description</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($outstanding_invoices as $invoice): ?>
                <tr>
                    <td>INV-<?= str_pad($invoice['id'], 6, '0', STR_PAD_LEFT) ?></td>
                    <td><?= htmlspecialchars($invoice['first_name'] . ' ' . $invoice['last_name']) ?></td>
                    <td><?= htmlspecialchars($invoice['email']) ?></td>
                    <td>$<?= number_format($invoice['amount'], 2) ?></td>
                    <td><?= date('M j, Y', strtotime($invoice['due_date'])) ?></td>
                    <td><?= htmlspecialchars($invoice['description'] ?? 'No description') ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <div class="text-center mt-5">
        <a href="#" class="btn btn-primary">Export Full Report (PDF)</a>
        <a href="#" class="btn btn-outline-primary">Export Data (CSV)</a>
    </div>
</div>

<?php include 'footer.php'; ?>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
    // Revenue Chart
    const ctx = document.getElementById('revenueChart').getContext('2d');
    const revenueChart = new Chart(ctx, {
        type: 'line',
        data: {
            labels: <?= json_encode(array_column($revenue_by_month, 'month_name')) ?>,
            datasets: [{
                label: 'Monthly Revenue',
                data: <?= json_encode(array_column($revenue_by_month, 'monthly_revenue')) ?>,
                backgroundColor: 'rgba(40, 167, 69, 0.1)',
                borderColor: '#28a745',
                borderWidth: 3,
                pointBackgroundColor: '#28a745',
                pointRadius: 5,
                pointHoverRadius: 7,
                fill: true
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            scales: {
                y: {
                    beginAtZero: true,
                    ticks: {
                        callback: function(value) {
                            return '$' + value.toLocaleString();
                        }
                    }
                }
            },
            plugins: {
                tooltip: {
                    callbacks: {
                        label: function(context) {
                            return 'Revenue: $' + context.parsed.y.toLocaleString();
                        }
                    }
                }
            }
        }
    });
</script>
</body>
</html>
