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

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit;
}

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Get site settings for header
$settings = get_all_settings($pdo);
$institution_name = $settings['site_name'] ?? 'প্রতিষ্ঠানের নাম';
$institution_address = $settings['site_address'] ?? 'ঠিকানা';
$logo_image = $settings['logo_image'] ?? null;

// Get filter parameters
$start_date = $_GET['start_date'] ?? date('Y-m-01');
$end_date = $_GET['end_date'] ?? date('Y-m-t');
$payment_method = $_GET['payment_method'] ?? '';
$search_term = $_GET['search'] ?? '';

// Pagination settings
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$available_page_sizes = [10, 25, 50, 100, 200, 500, 1000];
$records_per_page = isset($_GET['per_page']) && in_array($_GET['per_page'], $available_page_sizes) ? intval($_GET['per_page']) : 20;
$offset = ($page - 1) * $records_per_page;

// Convert dates to datetime format for database comparison
$start_datetime = $start_date . ' 00:00:00';
$end_datetime = $end_date . ' 23:59:59';

// Build base query with filters - USING CORRECT COLUMN NAMES
$base_sql = "SELECT fp.*, 
               f.fee_type as fee_title, 
               f.student_id, 
               CONCAT(COALESCE(s.first_name, ''), ' ', COALESCE(s.last_name, '')) as student_name, 
               c.name as class, 
               s.roll_number, 
               COALESCE(u.username, 'Unknown User') as collected_by_name 
        FROM fee_payments fp 
        LEFT JOIN fees f ON fp.fee_id = f.id 
        LEFT JOIN students s ON f.student_id = s.id 
        LEFT JOIN classes c ON s.class_id = c.id
        LEFT JOIN users u ON fp.created_by = u.id 
        WHERE fp.payment_date BETWEEN ? AND ?";

$base_params = [$start_datetime, $end_datetime];

// Add payment method filter if specified
if ($payment_method) {
    $base_sql .= " AND fp.payment_method = ?";
    $base_params[] = $payment_method;
}

// Add search filter if specified
if (!empty($search_term)) {
    $base_sql .= " AND (s.first_name LIKE ? OR s.last_name LIKE ? OR s.roll_number LIKE ? OR f.fee_type LIKE ? OR fp.payment_method LIKE ? OR u.username LIKE ?)";
    $search_param = "%" . trim($search_term) . "%";
    $base_params[] = $search_param;
    $base_params[] = $search_param;
    $base_params[] = $search_param;
    $base_params[] = $search_param;
    $base_params[] = $search_param;
    $base_params[] = $search_param;
}

// Count total records for pagination
$count_sql = "SELECT COUNT(*) as total FROM (" . $base_sql . ") as counted_records";
$count_stmt = $pdo->prepare($count_sql);
$count_stmt->execute($base_params);
$total_records = $count_stmt->fetch()['total'];
$total_pages = ceil($total_records / $records_per_page);

// Add ordering and pagination to the main query
$sql = $base_sql . " ORDER BY fp.payment_date DESC, fp.id DESC LIMIT ? OFFSET ?";
$params = array_merge($base_params, [$records_per_page, $offset]);

try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $income_records = $stmt->fetchAll();
} catch (Exception $e) {
    // Log error and show user-friendly message
    error_log("Income Report Query Error: " . $e->getMessage());
    $income_records = [];
    $error_message = "ডেটাবেইস কুয়েরি ত্রুটি: " . $e->getMessage();
}

// Calculate totals
$total_income = 0;
$payment_method_totals = [];
foreach ($income_records as $record) {
    $total_income += $record['amount'];
    if (!isset($payment_method_totals[$record['payment_method']])) {
        $payment_method_totals[$record['payment_method']] = 0;
    }
    $payment_method_totals[$record['payment_method']] += $record['amount'];
}

// Get all payment methods for filter
$payment_methods = ['cash', 'bank_transfer', 'mobile_banking', 'check'];

// Get actual payment methods from database
try {
    $stmt = $pdo->query("SELECT DISTINCT payment_method FROM fee_payments WHERE payment_method IS NOT NULL AND payment_method != ''");
    $db_payment_methods = $stmt->fetchAll(PDO::FETCH_COLUMN);
    if (!empty($db_payment_methods)) {
        $payment_methods = array_unique(array_merge($payment_methods, $db_payment_methods));
    }
} catch (Exception $e) {
    // Continue with default payment methods if query fails
    error_log("Failed to fetch payment methods: " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>আয় রিপোর্ট - বিদ্যা সংগঠন</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: #f4f7f6;
            direction: ltr;
            text-align: left;
        }
        
        /* Override RTL for specific elements */
        .card-header, .card-body, .table, .form-label, .form-control, .form-select, .btn {
            direction: ltr;
            text-align: left;
        }
        
        /* Ensure table headers and cells are left-aligned */
        .table th, .table td {
            text-align: left;
        }
        
        /* Fix form elements */
        .form-control, .form-select {
            text-align: left;
        }
        .navbar {
            background: linear-gradient(135deg, #d32f2f 0%, #c62828 100%);
        }
        .card {
            border: none;
            border-radius: 15px;
            box-shadow: 0 5px 15px rgba(0,0,0,0.08);
            margin-bottom: 1.5rem;
        }
        .table th {
            background-color: #d32f2f;
            color: white;
        }
        .btn-primary {
            background-color: #d32f2f;
            border-color: #d32f2f;
        }
        .btn-primary:hover {
            background-color: #c62828;
            border-color: #c62828;
        }
        .summary-card {
            text-align: center;
            padding: 1.5rem;
            border-radius: 10px;
            color: white;
            margin-bottom: 1rem;
        }
        .summary-card-income {
            background: linear-gradient(135deg, #28a745 0%, #20c997 100%);
        }
        .summary-card-expense {
            background: linear-gradient(135deg, rgb(70, 2, 126) 0%, rgb(36, 2, 49) 100%);
        }
        .summary-card-balance {
            background: linear-gradient(135deg, #007bff 0%, #6f42c1 100%);
        }
        .amount-text {
            font-weight: bold;
            color: #28a745;
        }
        .print-btn {
            position: fixed;
            top: 20px;
            left: 20px;
            z-index: 1000;
        }
        
        /* Pagination styles */
        .pagination {
            margin-bottom: 0;
        }
        
        .pagination .page-link {
            color: #d32f2f;
            border-color: #dee2e6;
        }
        
        .pagination .page-link:hover {
            color: #fff;
            background-color: #d32f2f;
            border-color: #d32f2f;
        }
        
        .pagination .page-item.active .page-link {
            background-color: #d32f2f;
            border-color: #d32f2f;
        }
        
        .pagination-container {
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        
        @media (max-width: 767px) {
            .pagination-container {
                flex-direction: column;
                gap: 10px;
            }
            .pagination {
                margin-top: 10px;
            }
        }
        
        /* Print-specific styles */
        @media print {
            .no-print {
                display: none !important;
            }
            
            body {
                font-size: 12px;
                background: white;
                margin: 0;
                padding: 20px;
            }
            
            .container {
                max-width: 100% !important;
                padding: 0;
                margin: 0;
            }
            
            .print-header {
                text-align: center;
                margin-bottom: 20px;
                border-bottom: 2px solid #000;
                padding-bottom: 15px;
            }
            
            .print-logo {
                max-height: 80px;
                margin-bottom: 10px;
            }
            
            .print-institution-name {
                font-size: 20px;
                font-weight: bold;
                margin-bottom: 5px;
            }
            
            .print-institution-address {
                font-size: 14px;
                margin-bottom: 10px;
            }
            
            .print-title {
                font-size: 18px;
                font-weight: bold;
                margin: 15px 0;
                text-align: center;
            }
            
            .table {
                font-size: 11px;
            }
            
            .table th, .table td {
                padding: 4px;
            }
            
            .card {
                border: 1px solid #000;
                box-shadow: none;
                margin-bottom: 15px;
            }
            
            .card-header {
                background: #f0f0f0;
                border-bottom: 1px solid #000;
                padding: 8px;
            }
            
            .print-only {
                display: block !important;
            }
            
            .btn, .form-control, .form-select, .pagination {
                display: none !important;
            }
            
            .row > div {
                width: 100% !important;
            }
        }
        
        .print-only {
            display: none;
        }
    </style>
</head>
<body>
    <div class="container mt-4">
        <!-- Navigation Buttons -->
        <div class="row mb-4">
            <div class="col-12">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <a href="javascript:history.back()" class="btn btn-outline-secondary me-2">
                            <i class="fas fa-arrow-left"></i> Back
                        </a>
                        <a href="../index.php" class="btn btn-outline-success">
                            <i class="fas fa-home"></i> Home Page
                        </a>
                    </div>
                    <h2 class="mb-0">আয় রিপোর্ট</h2>
                </div>
            </div>
        </div>
        
        <!-- Print Header (Visible only when printing) -->
        <div class="print-header print-only">
            <?php if ($logo_image): ?>
                <img src="<?php echo htmlspecialchars($logo_image); ?>" alt="Logo" class="print-logo">
            <?php endif; ?>
            <div class="print-institution-name"><?php echo htmlspecialchars($institution_name); ?></div>
            <div class="print-institution-address"><?php echo htmlspecialchars($institution_address); ?></div>
            <div class="print-title">আয় রিপোর্ট</div>
            <div style="font-size: 12px; margin-top: 5px;">
                তারিখ পরিসর: <?php echo date('d/m/Y', strtotime($start_date)); ?> থেকে <?php echo date('d/m/Y', strtotime($end_date)); ?>
            </div>
        </div>
        
        <div class="row no-print">
            <div class="col-12">
                <div class="card">
                    <div class="card-header d-flex justify-content-between align-items-center">
                        <h5><i class="fas fa-chart-bar"></i> আয় রিপোর্ট</h5>
                        <button class="btn btn-primary" onclick="window.print()">
                            <i class="fas fa-print"></i> প্রিন্ট করুন
                        </button>
                    </div>
                    <div class="card-body">
                        <?php if (isset($error_message)): ?>
                        <div class="alert alert-danger">
                            <h4><i class="fas fa-exclamation-triangle"></i> ত্রুটি!</h4>
                            <p><?php echo htmlspecialchars($error_message); ?></p>
                            <p>দয়া করে সিস্টেম অ্যাডমিনের সাথে যোগাযোগ করুন।</p>
                        </div>
                        <?php endif; ?>
                        
                        <!-- Filters -->
                        <form method="GET" class="mb-4 no-print">
                            <div class="row">
                                <div class="col-md-3">
                                    <label class="form-label">শুরুর তারিখ</label>
                                    <input type="date" class="form-control" name="start_date" value="<?php echo $start_date; ?>">
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">শেষের তারিখ</label>
                                    <input type="date" class="form-control" name="end_date" value="<?php echo $end_date; ?>">
                                </div>
                                <div class="col-md-2">
                                    <label class="form-label">পেমেন্ট পদ্ধতি</label>
                                    <select class="form-select" name="payment_method">
                                        <option value="">সব পদ্ধতি</option>
                                        <?php foreach ($payment_methods as $method): ?>
                                            <?php 
                                            $method_labels = [
                                                'cash' => 'নগদ',
                                                'bank_transfer' => 'ব্যাংক ট্রান্সফার',
                                                'mobile_banking' => 'মোবাইল ব্যাংকিং',
                                                'check' => 'চেক',
                                                'bank' => 'ব্যাংক',
                                                'online' => 'অনলাইন',
                                                'cheque' => 'চেক'
                                            ];
                                            $display_label = $method_labels[$method] ?? ucfirst(str_replace('_', ' ', $method));
                                            $selected = ($payment_method == $method) ? 'selected' : '';
                                            ?>
                                            <option value="<?php echo $method; ?>" <?php echo $selected; ?>><?php echo $display_label; ?></option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <div class="col-md-2">
                                    <label class="form-label">অনুসন্ধান</label>
                                    <input type="text" class="form-control" name="search" placeholder="ছাত্র, ফি ধরন, ইত্যাদি..." value="<?php echo htmlspecialchars($search_term); ?>">
                                </div>
                                <div class="col-md-1">
                                    <label class="form-label">প্রতি পৃষ্ঠে</label>
                                    <select class="form-select" name="per_page">
                                        <?php foreach ($available_page_sizes as $size): ?>
                                            <option value="<?php echo $size; ?>" <?php echo ($records_per_page == $size) ? 'selected' : ''; ?>>
                                                <?php echo $size; ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <div class="col-md-1">
                                    <label class="form-label">&nbsp;</label>
                                    <div>
                                        <button type="submit" class="btn btn-primary w-100">
                                            <i class="fas fa-search"></i> খুঁজুন
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </form>

                        <!-- Summary Cards -->
                        <div class="row mb-4 no-print">
                            <div class="col-md-4">
                                <div class="summary-card summary-card-income">
                                    <h5>মোট আয়</h5>
                                    <h2 class="amount-text">৳<?php echo number_format($total_income, 2); ?></h2>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="summary-card summary-card-expense">
                                    <h5>মোট ট্রানজেকশন</h5>
                                    <h2><?php echo count($income_records); ?></h2>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="summary-card summary-card-balance">
                                    <h5>গড় আয়</h5>
                                    <h2 class="amount-text">৳<?php echo count($income_records) > 0 ? number_format($total_income / count($income_records), 2) : '0.00'; ?></h2>
                                </div>
                            </div>
                        </div>

                        <!-- Payment Method Breakdown -->
                        <div class="card mb-4 no-print">
                            <div class="card-header">
                                <h5><i class="fas fa-tags"></i> পেমেন্ট পদ্ধতি অনুযায়ী বিস্তারিত</h5>
                            </div>
                            <div class="card-body">
                                <div class="table-responsive">
                                    <table class="table table-striped">
                                        <thead>
                                            <tr>
                                                <th>পেমেন্ট পদ্ধতি</th>
                                                <th>মোট আয় (৳)</th>
                                                <th>শতাংশ</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($payment_method_totals as $method => $amount): ?>
                                            <?php 
                                                $percentage = $total_income > 0 ? ($amount / $total_income) * 100 : 0;
                                                $method_labels = [
                                                    'cash' => 'নগদ',
                                                    'bank_transfer' => 'ব্যাংক ট্রান্সফার',
                                                    'mobile_banking' => 'মোবাইল ব্যাংকিং',
                                                    'check' => 'চেক',
                                                    'bank' => 'ব্যাংক',
                                                    'online' => 'অনলাইন',
                                                    'cheque' => 'চেক'
                                                ];
                                                $display_label = $method_labels[$method] ?? ucfirst(str_replace('_', ' ', $method));
                                            ?>
                                            <tr>
                                                <td><?php echo $display_label; ?></td>
                                                <td class="amount-text">৳<?php echo number_format($amount, 2); ?></td>
                                                <td>
                                                    <div class="progress" style="height: 20px;">
                                                        <div class="progress-bar bg-success" role="progressbar" 
                                                             style="width: <?php echo $percentage; ?>%" 
                                                             aria-valuenow="<?php echo $percentage; ?>" 
                                                             aria-valuemin="0" 
                                                             aria-valuemax="100">
                                                            <?php echo number_format($percentage, 1); ?>%
                                                        </div>
                                                    </div>
                                                </td>
                                            </tr>
                                            <?php endforeach; ?>
                                            
                                            <?php if (empty($payment_method_totals)): ?>
                                            <tr>
                                                <td colspan="3" class="text-center">কোন আয় পাওয়া যায়নি</td>
                                            </tr>
                                            <?php endif; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Income Records Table (Visible in both normal and print view) -->
        <div class="row">
            <div class="col-12">
                <div class="card">
                    <div class="card-header">
                        <h5><i class="fas fa-list"></i> আয় রেকর্ড তালিকা</h5>
                    </div>
                    <div class="card-body">
                        <div class="table-responsive">
                            <table class="table table-striped table-hover">
                                <thead>
                                    <tr>
                                        <th>তারিখ</th>
                                        <th>ছাত্র/ছাত্রী</th>
                                        <th>শ্রেণি</th>
                                        <th>রোল নং</th>
                                        <th>ফি ধরন</th>
                                        <th>পরিমাণ (৳)</th>
                                        <th>পেমেন্ট পদ্ধতি</th>
                                        <th>সংগ্রহকারী</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($income_records as $record): ?>
                                    <tr>
                                        <td><?php echo date('d-m-Y', strtotime($record['payment_date'])); ?></td>
                                        <td><?php echo htmlspecialchars($record['student_name'] ?? 'N/A'); ?></td>
                                        <td><?php echo htmlspecialchars($record['class'] ?? 'N/A'); ?></td>
                                        <td><?php echo htmlspecialchars($record['roll_number'] ?? 'N/A'); ?></td>
                                        <td><?php echo htmlspecialchars($record['fee_title'] ?? 'N/A'); ?></td>
                                        <td class="amount-text">৳<?php echo number_format($record['amount'], 2); ?></td>
                                        <td>
                                            <?php 
                                            $method_labels = [
                                                'cash' => 'নগদ',
                                                'bank_transfer' => 'ব্যাংক ট্রান্সফার',
                                                'mobile_banking' => 'মোবাইল ব্যাংকিং',
                                                'check' => 'চেক',
                                                'bank' => 'ব্যাংক',
                                                'online' => 'অনলাইন',
                                                'cheque' => 'চেক'
                                            ];
                                            $display_label = $method_labels[$record['payment_method']] ?? ucfirst(str_replace('_', ' ', $record['payment_method']));
                                            echo $display_label;
                                            ?>
                                        </td>
                                        <td><?php echo htmlspecialchars($record['collected_by_name'] ?? 'N/A'); ?></td>
                                    </tr>
                                    <?php endforeach; ?>
                                    
                                    <?php if (empty($income_records)): ?>
                                    <tr>
                                        <td colspan="8" class="text-center">কোন আয় রেকর্ড পাওয়া যায়নি</td>
                                    </tr>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                        
                        <?php if (!empty($income_records)): ?>
                        <div class="mt-3 d-flex flex-column flex-md-row justify-content-between align-items-center no-print">
                            <div class="mb-2 mb-md-0">
                                <strong>মোট রেকর্ড:</strong> <?php echo $total_records; ?> টি (<?php echo $page; ?>/<?php echo $total_pages; ?> পৃষ্ঠা)
                            </div>
                            <!-- Pagination Controls -->
                            <nav aria-label="Page navigation">
                                <ul class="pagination mb-0">
                                    <!-- Previous Button -->
                                    <li class="page-item <?php echo ($page <= 1) ? 'disabled' : ''; ?>">
                                        <a class="page-link" href="?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&payment_method=<?php echo $payment_method; ?>&search=<?php echo urlencode($search_term); ?>&per_page=<?php echo $records_per_page; ?>&page=<?php echo $page - 1; ?>">
                                            <i class="fas fa-chevron-left"></i> পূর্ববর্তী
                                        </a>
                                    </li>
                                    
                                    <!-- Page Numbers -->
                                    <?php
                                    $start_page = max(1, $page - 2);
                                    $end_page = min($total_pages, $page + 2);
                                    
                                    if ($start_page > 1): ?>
                                        <li class="page-item">
                                            <a class="page-link" href="?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&payment_method=<?php echo $payment_method; ?>&search=<?php echo urlencode($search_term); ?>&per_page=<?php echo $records_per_page; ?>&page=1">1</a>
                                        </li>
                                        <?php if ($start_page > 2): ?>
                                            <li class="page-item disabled">
                                                <span class="page-link">...</span>
                                            </li>
                                        <?php endif; ?>
                                    <?php endif; ?>
                                    
                                    <?php for ($i = $start_page; $i <= $end_page; $i++): ?>
                                        <li class="page-item <?php echo ($i == $page) ? 'active' : ''; ?>">
                                            <a class="page-link" href="?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&payment_method=<?php echo $payment_method; ?>&search=<?php echo urlencode($search_term); ?>&per_page=<?php echo $records_per_page; ?>&page=<?php echo $i; ?>"><?php echo $i; ?></a>
                                        </li>
                                    <?php endfor; ?>
                                    
                                    <?php if ($end_page < $total_pages): ?>
                                        <?php if ($end_page < $total_pages - 1): ?>
                                            <li class="page-item disabled">
                                                <span class="page-link">...</span>
                                            </li>
                                        <?php endif; ?>
                                        <li class="page-item">
                                            <a class="page-link" href="?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&payment_method=<?php echo $payment_method; ?>&search=<?php echo urlencode($search_term); ?>&per_page=<?php echo $records_per_page; ?>&page=<?php echo $total_pages; ?>"><?php echo $total_pages; ?></a>
                                        </li>
                                    <?php endif; ?>
                                    
                                    <!-- Next Button -->
                                    <li class="page-item <?php echo ($page >= $total_pages) ? 'disabled' : ''; ?>">
                                        <a class="page-link" href="?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&payment_method=<?php echo $payment_method; ?>&search=<?php echo urlencode($search_term); ?>&per_page=<?php echo $records_per_page; ?>&page=<?php echo $page + 1; ?>">
                                            পরবর্তী <i class="fas fa-chevron-right"></i>
                                        </a>
                                    </li>
                                </ul>
                            </nav>
                        </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>

    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>