<?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 invoice filters
$status_filter = $_GET['status'] ?? '';
$date_from = $_GET['date_from'] ?? '';
$date_to = $_GET['date_to'] ?? '';
$search = $_GET['search'] ?? '';

// Build query with correct joins to get student email from users table
$sql = "SELECT i.*, s.first_name, s.last_name, u.email as student_email,
        p.amount as payment_amount, p.payment_date as payment_date
        FROM invoices i
        JOIN students s ON i.student_id = s.id
        JOIN users u ON s.id = u.id
        LEFT JOIN payments p ON i.payment_id = p.id";

$conditions = [];
$params = [];

if ($status_filter) {
    $conditions[] = "i.status = ?";
    $params[] = $status_filter;
}

if ($date_from) {
    $conditions[] = "i.due_date >= ?";
    $params[] = $date_from;
}

if ($date_to) {
    $conditions[] = "i.due_date <= ?";
    $params[] = $date_to;
}

if ($search) {
    $conditions[] = "(s.first_name LIKE ? OR s.last_name LIKE ? OR u.email LIKE ? OR i.description LIKE ?)";
    $params[] = "%$search%";
    $params[] = "%$search%";
    $params[] = "%$search%";
    $params[] = "%$search%";
}

if ($conditions) {
    $sql .= " WHERE " . implode(" AND ", $conditions);
}

$sql .= " ORDER BY i.due_date DESC, i.created_at DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$invoices = $stmt->fetchAll();

// Get stats
$total_unpaid = $pdo->query("SELECT SUM(amount) FROM invoices WHERE status = 'unpaid'")->fetchColumn() ?: 0;
$total_paid = $pdo->query("SELECT SUM(amount) FROM invoices WHERE status = 'paid'")->fetchColumn() ?: 0;
$total_overdue = $pdo->query("SELECT SUM(amount) FROM invoices WHERE status = 'overdue'")->fetchColumn() ?: 0;

$stats = [
    'total_invoices' => $pdo->query("SELECT COUNT(*) FROM invoices")->fetchColumn(),
    'unpaid' => $pdo->query("SELECT COUNT(*) FROM invoices WHERE status = 'unpaid'")->fetchColumn(),
    'paid' => $pdo->query("SELECT COUNT(*) FROM invoices WHERE status = 'paid'")->fetchColumn(),
    'overdue' => $pdo->query("SELECT COUNT(*) FROM invoices WHERE status = 'overdue'")->fetchColumn(),
];

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

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Invoices - 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;
        }
        .status-badge {
            padding: 5px 12px;
            border-radius: 20px;
            font-weight: 600;
            font-size: 0.9em;
        }
        .status-unpaid {
            background: #f8d7da;
            color: #721c24;
        }
        .status-paid {
            background: #d4edda;
            color: #155724;
        }
        .status-overdue {
            background: #fff3cd;
            color: #856404;
        }
        .status-cancelled {
            background: #d6d8d9;
            color: #383d41;
        }
    </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">Financial Management</h6>
        <h1 class="display-5">Manage Invoices</h1>
    </div>

    <!-- Search and Filter -->
    <div class="row mb-4">
        <div class="col-lg-8">
            <form method="GET" class="d-flex">
                <div class="flex-grow-1 mr-2">
                    <input type="text" name="search" class="form-control" placeholder="Search by student, description, or amount" 
                           value="<?= htmlspecialchars($search) ?>">
                </div>
                <button type="submit" class="btn btn-primary">Search</button>
            </form>
        </div>
        <div class="col-lg-4">
            <select name="status" class="form-control" onchange="window.location='admin-invoices.php?status='+this.value<?= $date_from ? '&date_from='.urlencode($date_from) : '' ?><?= $date_to ? '&date_to='.urlencode($date_to) : '' ?><?= $search ? '&search='.urlencode($search) : '' ?>">
                <option value="">All Statuses</option>
                <option value="unpaid" <?= $status_filter === 'unpaid' ? 'selected' : '' ?>>Unpaid</option>
                <option value="paid" <?= $status_filter === 'paid' ? 'selected' : '' ?>>Paid</option>
                <option value="overdue" <?= $status_filter === 'overdue' ? 'selected' : '' ?>>Overdue</option>
                <option value="cancelled" <?= $status_filter === 'cancelled' ? 'selected' : '' ?>>Cancelled</option>
            </select>
        </div>
    </div>

    <!-- Date 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>Due 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>Due 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>

    <!-- Stats -->
    <div class="row mb-4">
        <div class="col-md-3 mb-3">
            <div class="stat-card">
                <div class="card-body">
                    <h6>Total Unpaid</h6>
                    <h3>$<?= number_format($total_unpaid, 2) ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #28a745;">
                <div class="card-body">
                    <h6>Paid</h6>
                    <h3>$<?= number_format($total_paid, 2) ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #ffc107;">
                <div class="card-body">
                    <h6>Overdue</h6>
                    <h3>$<?= number_format($total_overdue, 2) ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #dc3545;">
                <div class="card-body">
                    <h6>Invoices</h6>
                    <h3><?= $stats['total_invoices'] ?></h3>
                </div>
            </div>
        </div>
    </div>

    <!-- Invoices Table -->
    <div class="table-responsive">
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Invoice #</th>
                    <th>Student</th>
                    <th>Description</th>
                    <th>Amount</th>
                    <th>Due Date</th>
                    <th>Status</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($invoices as $i): ?>
                <tr>
                    <td><strong>INV-<?= str_pad($i['id'], 6, '0', STR_PAD_LEFT) ?></strong></td>
                    <td>
                        <?= htmlspecialchars($i['first_name'] . ' ' . $i['last_name']) ?>
                        <div class="small text-muted"><?= htmlspecialchars($i['student_email']) ?></div>
                    </td>
                    <td>
                        <?= htmlspecialchars($i['description'] ?? 'No description') ?>
                        <?php if ($i['payment_id']): ?>
                        <div class="small text-success mt-1">
                            <i class="fas fa-check"></i> Paid on <?= date('M j, Y', strtotime($i['payment_date'])) ?>
                        </div>
                        <?php endif; ?>
                    </td>
                    <td>
                        <strong>$<?= number_format($i['amount'], 2) ?></strong>
                        <div class="small text-muted"><?= $i['currency'] ?></div>
                    </td>
                    <td>
                        <?= date('M j, Y', strtotime($i['due_date'])) ?>
                        <?php if ($i['status'] === 'overdue'): ?>
                        <div class="small text-danger mt-1"><i class="fas fa-exclamation-triangle"></i> Overdue</div>
                        <?php endif; ?>
                    </td>
                    <td>
                        <span class="status-badge status-<?= $i['status'] ?>">
                            <?= ucfirst($i['status']) ?>
                        </span>
                    </td>
                    <td>
                        <div class="btn-group">
                            <button class="btn btn-sm btn-primary generate-invoice" data-id="<?= $i['id'] ?>">
                                <i class="fas fa-file-pdf"></i> PDF
                            </button>
                            <?php if ($i['status'] === 'unpaid'): ?>
                            <button class="btn btn-sm btn-success mark-paid" data-id="<?= $i['id'] ?>">
                                <i class="fas fa-check"></i> Mark Paid
                            </button>
                            <?php endif; ?>
                            <button class="btn btn-sm btn-danger delete-invoice" data-id="<?= $i['id'] ?>">Delete</button>
                        </div>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </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>
    $('.generate-invoice').on('click', function() {
        const invoiceId = $(this).data('id');
        alert('In a real application, this would generate a PDF invoice for download.');
    });
    
    $('.mark-paid').on('click', function() {
        const invoiceId = $(this).data('id');
        if (confirm('Mark this invoice as paid? This will update the payment status.')) {
            alert('In a real application, this would update the invoice status to paid.');
        }
    });
    
    $('.delete-invoice').on('click', function() {
        const invoiceId = $(this).data('id');
        if (confirm('Are you sure you want to delete this invoice? This action cannot be undone.')) {
            alert('In a real application, this would delete the invoice from the database.');
        }
    });
</script>
</body>
</html>