<?php
session_start();
require_once '../config/database.php';

// Check if user is logged in and has permission
if (!isset($_SESSION['user_id']) || ($_SESSION['user_role'] != 'admin' && $_SESSION['user_role'] != 'teacher')) {
    header('Location: ../login.php');
    exit();
}

// Get filter parameters
$from_date = $_GET['from_date'] ?? date('Y-m-01');
$to_date = $_GET['to_date'] ?? date('Y-m-t');
$class_id = $_GET['class_id'] ?? '';
$session = $_GET['session'] ?? '';

// Get classes for dropdown
$stmt = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section");
$classes = $stmt->fetchAll();

// Get academic sessions for dropdown
$stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC");
$sessions = $stmt->fetchAll();

// Build query for report data
$where_conditions = [];
$params = [];

if (!empty($class_id)) {
    $where_conditions[] = "s.class_id = ?";
    $params[] = $class_id;
}

if (!empty($session)) {
    $where_conditions[] = "s.session = ?";
    $params[] = $session;
}

$where_clause = !empty($where_conditions) ? "WHERE " . implode(' AND ', $where_conditions) : "";

// Main report query
$sql = "
    SELECT 
        s.id as student_id,
        s.student_id as student_code,
        s.first_name,
        s.last_name,
        s.roll_number,
        s.session,
        s.group_name,
        c.name as class_name,
        c.section as class_section,
        COUNT(DISTINCT f.id) as total_bills,
        COALESCE(SUM(f.amount), 0) as total_bill_amount,
        COALESCE(SUM(f.paid_amount), 0) as total_paid_amount,
        COALESCE(SUM(f.amount - f.paid_amount), 0) as total_due_amount,
        COUNT(fp.id) as total_payments,
        COALESCE(SUM(fp.amount), 0) as total_payment_received
    FROM students s
    JOIN classes c ON s.class_id = c.id
    LEFT JOIN fees f ON s.id = f.student_id 
        AND f.created_at BETWEEN ? AND ?
    LEFT JOIN fee_payments fp ON f.id = fp.fee_id 
        AND fp.payment_date BETWEEN ? AND ?
    $where_clause
    GROUP BY s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session, s.group_name, c.name, c.section
    HAVING total_bills > 0 OR total_payments > 0
    ORDER BY c.name, c.section, s.roll_number
";

array_unshift($params, $from_date, $to_date, $from_date, $to_date);

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

// Calculate summary statistics
$total_students = count($report_data);
$total_bills_sum = array_sum(array_column($report_data, 'total_bills'));
$total_bill_amount_sum = array_sum(array_column($report_data, 'total_bill_amount'));
$total_paid_amount_sum = array_sum(array_column($report_data, 'total_paid_amount'));
$total_due_amount_sum = array_sum(array_column($report_data, 'total_due_amount'));
$total_payments_sum = array_sum(array_column($report_data, 'total_payments'));
$total_payment_received_sum = array_sum(array_column($report_data, 'total_payment_received'));

// Get site settings for header
$settings_stmt = $pdo->query("SELECT * FROM site_settings LIMIT 1");
$site_settings = $settings_stmt->fetch();
$institution_name = $site_settings['site_name'] ?? 'প্রতিষ্ঠানের নাম';
$institution_address = $site_settings['site_address'] ?? 'ঠিকানা';
$logo_image = $site_settings['logo_image'] ?? null;

?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>বিল সাবমিশন রিপোর্ট - <?php echo date('d/m/Y'); ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Hind+Siliguri:wght@300;400;500;600;700&display=swap" rel="stylesheet">
    <style>
        body {
            font-family: 'Hind Siliguri', sans-serif;
            background-color: #f8f9fa;
        }
        @media print {
            body {
                background-color: white;
                font-size: 12px;
            }
            .no-print {
                display: none !important;
            }
            .print-header {
                display: block !important;
            }
            .card {
                box-shadow: none;
                border: 1px solid #dee2e6;
            }
            .table th, .table td {
                padding: 0.5rem;
            }
            .page-break {
                page-break-before: always;
            }
        }
        .print-header {
            display: none;
        }
        .report-header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 15px;
            padding: 1.5rem;
            margin-bottom: 2rem;
        }
        .summary-card {
            border-radius: 15px;
            box-shadow: 0 0 15px rgba(0,0,0,0.1);
        }
        .summary-card-income {
            background: linear-gradient(135deg, #28a745 0%, #20c997 100%);
            color: white;
        }
        .summary-card-expense {
            background: linear-gradient(135deg, #dc3545 0%, #fd7e14 100%);
            color: white;
        }
        .summary-card-balance {
            background: linear-gradient(135deg, #007bff 0%, #6f42c1 100%);
            color: white;
        }
        .table th {
            background-color: #f8f9fa;
            font-weight: 600;
        }
        .amount-positive {
            color: #28a745;
            font-weight: 600;
        }
        .amount-negative {
            color: #dc3545;
            font-weight: 600;
        }
        .amount-neutral {
            color: #007bff;
            font-weight: 600;
        }
    </style>
</head>
<body>
    <!-- Print Header (visible only when printing) -->
    <div class="print-header text-center mb-4">
        <?php if ($logo_image): ?>
            <img src="../uploads/<?php echo htmlspecialchars($logo_image); ?>" alt="Logo" style="max-height: 80px; max-width: 150px;" class="mb-2">
        <?php endif; ?>
        <h2><?php echo htmlspecialchars($institution_name); ?></h2>
        <p><?php echo htmlspecialchars($institution_address); ?></p>
        <h4>বিল সাবমিশন রিপোর্ট</h4>
        <p>তারিখ: <?php echo date('d/m/Y', strtotime($from_date)); ?> থেকে <?php echo date('d/m/Y', strtotime($to_date)); ?></p>
    </div>

    <!-- Filter Section (not printed) -->
    <div class="container-fluid mt-4 no-print">
        <div class="card">
            <div class="card-header bg-primary text-white">
                <h5 class="mb-0"><i class="fas fa-filter"></i> রিপোর্ট ফিল্টার</h5>
            </div>
            <div class="card-body">
                <form method="GET" class="row g-3">
                    <div class="col-md-3">
                        <label class="form-label">শুরুর তারিখ</label>
                        <input type="date" class="form-control" name="from_date" value="<?php echo $from_date; ?>">
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">শেষ তারিখ</label>
                        <input type="date" class="form-control" name="to_date" value="<?php echo $to_date; ?>">
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">সেশন</label>
                        <select class="form-select" name="session">
                            <option value="">সব সেশন</option>
                            <?php foreach ($sessions as $sess): ?>
                                <option value="<?php echo htmlspecialchars($sess['session_name']); ?>" <?php echo $session == $sess['session_name'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($sess['session_name']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">ক্লাস</label>
                        <select class="form-select" name="class_id">
                            <option value="">সব ক্লাস</option>
                            <?php foreach ($classes as $class): ?>
                                <option value="<?php echo $class['id']; ?>" <?php echo $class_id == $class['id'] ? 'selected' : ''; ?>>
                                    <?php echo $class['name'] . ' - ' . $class['section']; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">&nbsp;</label>
                        <div class="d-grid">
                            <button type="submit" class="btn btn-primary">
                                <i class="fas fa-search"></i> খুঁজুন
                            </button>
                        </div>
                    </div>
                </form>
            </div>
        </div>

        <!-- Action Buttons -->
        <div class="mt-3 no-print">
            <button class="btn btn-success" onclick="window.print()">
                <i class="fas fa-print"></i> প্রিন্ট করুন
            </button>
            <button class="btn btn-secondary" onclick="window.location.href='bill_submission.php'">
                <i class="fas fa-arrow-left"></i> বিল সাবমিশনে ফিরে যান
            </button>
        </div>
    </div>

    <!-- Report Content -->
    <div class="container-fluid mt-4">
        <!-- Report Header -->
        <div class="report-header no-print">
            <div class="row align-items-center">
                <div class="col-md-2 text-center">
                    <?php if ($logo_image): ?>
                        <img src="../uploads/<?php echo htmlspecialchars($logo_image); ?>" alt="Logo" style="max-height: 80px; max-width: 150px;">
                    <?php endif; ?>
                </div>
                <div class="col-md-8 text-center">
                    <h2><?php echo htmlspecialchars($institution_name); ?></h2>
                    <p class="mb-1"><?php echo htmlspecialchars($institution_address); ?></p>
                    <h3>বিল সাবমিশন রিপোর্ট</h3>
                    <p class="mb-0">তারিখ: <?php echo date('d/m/Y', strtotime($from_date)); ?> থেকে <?php echo date('d/m/Y', strtotime($to_date)); ?></p>
                </div>
                <div class="col-md-2 text-end">
                    <p class="mb-0">রিপোর্ট তৈরি: <?php echo date('d/m/Y h:i A'); ?></p>
                </div>
            </div>
        </div>

        <!-- Summary Cards -->
        <div class="row mb-4 no-print">
            <div class="col-md-3">
                <div class="card summary-card summary-card-income">
                    <div class="card-body text-center">
                        <h5 class="card-title">মোট বিল</h5>
                        <h2><?php echo number_format($total_bills_sum); ?></h2>
                        <p class="mb-0">টি</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card summary-card summary-card-income">
                    <div class="card-body text-center">
                        <h5 class="card-title">মোট বিল পরিমাণ</h5>
                        <h2>৳<?php echo number_format($total_bill_amount_sum, 2); ?></h2>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card summary-card summary-card-expense">
                    <div class="card-body text-center">
                        <h5 class="card-title">মোট পরিশোধিত</h5>
                        <h2>৳<?php echo number_format($total_paid_amount_sum, 2); ?></h2>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card summary-card summary-card-balance">
                    <div class="card-body text-center">
                        <h5 class="card-title">মোট বকেয়া</h5>
                        <h2>৳<?php echo number_format($total_due_amount_sum, 2); ?></h2>
                    </div>
                </div>
            </div>
        </div>

        <!-- Detailed Report Table -->
        <div class="card">
            <div class="card-header bg-secondary text-white">
                <h5 class="mb-0"><i class="fas fa-list"></i> বিস্তারিত রিপোর্ট</h5>
            </div>
            <div class="card-body">
                <?php if (empty($report_data)): ?>
                    <div class="text-center py-5">
                        <i class="fas fa-file-invoice fa-3x text-muted mb-3"></i>
                        <h4 class="text-muted">কোন ডেটা পাওয়া যায়নি</h4>
                        <p class="text-muted">নির্বাচিত তারিখের মধ্যে কোন বিল সাবমিশন বা পেমেন্ট পাওয়া যায়নি।</p>
                    </div>
                <?php else: ?>
                    <div class="table-responsive">
                        <table class="table table-bordered table-hover">
                            <thead>
                                <tr>
                                    <th>ক্রমিক</th>
                                    <th>শিক্ষার্থীর তথ্য</th>
                                    <th>ক্লাস</th>
                                    <th>বিল সংখ্যা</th>
                                    <th>মোট বিল (৳)</th>
                                    <th>পরিশোধিত (৳)</th>
                                    <th>বকেয়া (৳)</th>
                                    <th>পেমেন্ট সংখ্যা</th>
                                    <th>পেমেন্ট পরিমাণ (৳)</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php $serial = 1; foreach ($report_data as $row): ?>
                                <tr>
                                    <td><?php echo $serial++; ?></td>
                                    <td>
                                        <div><strong><?php echo htmlspecialchars($row['first_name'] . ' ' . $row['last_name']); ?></strong></div>
                                        <small class="text-muted">
                                            আইডি: <?php echo htmlspecialchars($row['student_code']); ?> | 
                                            রোল: <?php echo htmlspecialchars($row['roll_number']); ?>
                                            <?php if ($row['group_name']): ?>
                                                | গ্রুপ: <?php echo htmlspecialchars($row['group_name']); ?>
                                            <?php endif; ?>
                                        </small>
                                    </td>
                                    <td><?php echo htmlspecialchars($row['class_name'] . ' - ' . $row['class_section']); ?></td>
                                    <td class="text-center"><?php echo $row['total_bills']; ?></td>
                                    <td class="amount-neutral text-end">৳<?php echo number_format($row['total_bill_amount'], 2); ?></td>
                                    <td class="amount-positive text-end">৳<?php echo number_format($row['total_paid_amount'], 2); ?></td>
                                    <td class="<?php echo $row['total_due_amount'] > 0 ? 'amount-negative' : 'amount-positive'; ?> text-end">
                                        ৳<?php echo number_format($row['total_due_amount'], 2); ?>
                                    </td>
                                    <td class="text-center"><?php echo $row['total_payments']; ?></td>
                                    <td class="amount-positive text-end">৳<?php echo number_format($row['total_payment_received'], 2); ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                            <tfoot>
                                <tr class="table-secondary">
                                    <th colspan="3" class="text-end">মোট:</th>
                                    <th class="text-center"><?php echo number_format($total_bills_sum); ?></th>
                                    <th class="amount-neutral text-end">৳<?php echo number_format($total_bill_amount_sum, 2); ?></th>
                                    <th class="amount-positive text-end">৳<?php echo number_format($total_paid_amount_sum, 2); ?></th>
                                    <th class="amount-negative text-end">৳<?php echo number_format($total_due_amount_sum, 2); ?></th>
                                    <th class="text-center"><?php echo number_format($total_payments_sum); ?></th>
                                    <th class="amount-positive text-end">৳<?php echo number_format($total_payment_received_sum, 2); ?></th>
                                </tr>
                            </tfoot>
                        </table>
                    </div>
                    
                    <!-- Summary Statistics -->
                    <div class="row mt-4 no-print">
                        <div class="col-md-4">
                            <div class="card">
                                <div class="card-body text-center">
                                    <h5 class="card-title text-primary">মোট শিক্ষার্থী</h5>
                                    <h2><?php echo number_format($total_students); ?></h2>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="card">
                                <div class="card-body text-center">
                                    <h5 class="card-title text-success">গড় বিল প্রতি শিক্ষার্থী</h5>
                                    <h2>৳<?php echo $total_students > 0 ? number_format($total_bill_amount_sum / $total_students, 2) : '0.00'; ?></h2>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="card">
                                <div class="card-body text-center">
                                    <h5 class="card-title text-info">পরিশোধের হার</h5>
                                    <h2><?php echo $total_bill_amount_sum > 0 ? number_format(($total_paid_amount_sum / $total_bill_amount_sum) * 100, 1) : '0'; ?>%</h2>
                                </div>
                            </div>
                        </div>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Auto-print functionality (optional)
        window.addEventListener('beforeprint', function() {
            // Add any pre-print adjustments here
        });
        
        window.addEventListener('afterprint', function() {
            // Add any post-print cleanup here
        });
    </script>
</body>
</html>