<?php
/**
 * Bus Subscriptions Management Module
 */

require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../includes/SecurityHelper.php';

// Check authentication - redirect to login if not logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: ../../login.php');
    exit;
}

// Check admin role - support both new and legacy session variables
$user_role = $_SESSION['user_role'] ?? $_SESSION['role'] ?? '';
if (!isset($_SESSION['user_id']) || ($user_role !== 'admin' && $user_role !== 'teacher')) {
    http_response_code(403);
    die('❌ অ্যাক্সেস denied। আপনার এই পেজ দেখার অনুমতি নেই।<br><br>
    <a href="../../login.php" class="btn btn-primary">লগইন পেজে যান</a>');
}

// Handle form submissions
$success_message = '';
$error_message = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    SecurityHelper::validatePost();
    
    try {
        if (isset($_POST['add_subscription'])) {
            // Check if student already has active subscription
            $check = $pdo->prepare("SELECT COUNT(*) FROM student_bus_subscriptions WHERE student_id=? AND status='active'");
            $check->execute([intval($_POST['student_id'])]);
            if ($check->fetchColumn() > 0) {
                $error_message = '❌ এই ছাত্রের ইতিমধ্যে একটি অ্যাক্টিভ সাবস্ক্রিপশন আছে!';
            } else {
                // Check bus capacity
                $bus_id = intval($_POST['bus_id']);
                $capacity_check = $pdo->prepare("
                    SELECT b.capacity, COUNT(s.id) as current_count 
                    FROM buses b 
                    LEFT JOIN student_bus_subscriptions s ON b.id = s.bus_id AND s.status = 'active'
                    WHERE b.id = ?
                    GROUP BY b.id, b.capacity
                ");
                $capacity_check->execute([$bus_id]);
                $capacity_data = $capacity_check->fetch();
                
                if ($capacity_data && $capacity_data['current_count'] >= $capacity_data['capacity']) {
                    $error_message = '❌ এই বাসে আর জায়গা নেই। অন্য বাস নির্বাচন করুন।';
                } else {
                    $stmt = $pdo->prepare("
                        INSERT INTO student_bus_subscriptions (student_id, bus_id, route_id, 
                            pickup_stop_id, drop_stop_id, start_date, end_date, status) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    ");
                    $stmt->execute([
                        intval($_POST['student_id']),
                        $bus_id,
                        intval($_POST['route_id']),
                        intval($_POST['pickup_stop_id']),
                        intval($_POST['drop_stop_id']),
                        $_POST['start_date'],
                        $_POST['end_date'] ?? null,
                        $_POST['status']
                    ]);
                    $success_message = '✅ ছাত্রের বাস সাবস্ক্রিপশন সফলভাবে যোগ করা হয়েছে!';
                    logAudit($pdo, $_SESSION['user_id'], $user_role, 'INSERT', 'student_bus_subscriptions', $pdo->lastInsertId(), null, $_POST);
                }
            }
        }
        
        if (isset($_POST['update_subscription'])) {
            $stmt = $pdo->prepare("
                UPDATE student_bus_subscriptions SET bus_id=?, route_id=?, 
                    pickup_stop_id=?, drop_stop_id=?, start_date=?, end_date=?, status=?
                WHERE id=?
            ");
            $stmt->execute([
                intval($_POST['bus_id']),
                intval($_POST['route_id']),
                intval($_POST['pickup_stop_id']),
                intval($_POST['drop_stop_id']),
                $_POST['start_date'],
                $_POST['end_date'] ?? null,
                $_POST['status'],
                intval($_POST['subscription_id'])
            ]);
            $success_message = '✅ সাবস্ক্রিপশন তথ্য আপডেট হয়েছে!';
        }
        
        if (isset($_POST['delete_subscription'])) {
            $stmt = $pdo->prepare("DELETE FROM student_bus_subscriptions WHERE id=?");
            $stmt->execute([intval($_POST['subscription_id'])]);
            $success_message = '✅ সাবস্ক্রিপশন মুছে ফেলা হয়েছে!';
        }
    } catch(Exception $e) {
        $error_message = '❌ ত্রুটি: ' . $e->getMessage();
    }
}

// Get all subscriptions with details
$subscriptions = $pdo->query("
    SELECT sbs.*, 
        CONCAT(s.first_name, ' ', s.last_name) as student_name,
        s.student_id as student_roll,
        c.name as class_name,
        b.bus_number,
        b.bus_name,
        br.route_name,
        br.route_code,
        bs1.stop_name as pickup_stop,
        bs2.stop_name as drop_stop
    FROM student_bus_subscriptions sbs
    LEFT JOIN students s ON sbs.student_id = s.id
    LEFT JOIN classes c ON s.class_id = c.id
    LEFT JOIN buses b ON sbs.bus_id = b.id
    LEFT JOIN bus_routes br ON sbs.route_id = br.id
    LEFT JOIN bus_route_stops bs1 ON sbs.pickup_stop_id = bs1.id
    LEFT JOIN bus_route_stops bs2 ON sbs.drop_stop_id = bs2.id
    ORDER BY sbs.created_at DESC
")->fetchAll();

// Get students for dropdown
$students = $pdo->query("
    SELECT s.id, CONCAT(s.first_name, ' ', s.last_name) as name, s.student_id, c.name as class_name
    FROM students s
    LEFT JOIN classes c ON s.class_id = c.id
    WHERE s.status = 'active'
    ORDER BY c.name, s.first_name
")->fetchAll();

// Get buses for dropdown
$buses = $pdo->query("SELECT id, bus_number, bus_name, capacity FROM buses WHERE status='active' ORDER BY bus_number")->fetchAll();

// Get routes for dropdown
$routes = $pdo->query("SELECT id, route_name, route_code FROM bus_routes WHERE status='active' ORDER BY route_code")->fetchAll();

// Statistics
$total_subscriptions = count($subscriptions);
$active_subscriptions = count(array_filter($subscriptions, fn($s) => $s['status'] == 'active'));
$expired_subscriptions = count(array_filter($subscriptions, fn($s) => $s['status'] == 'expired' || ($s['end_date'] && strtotime($s['end_date']) < time())));

function logAudit($pdo, $userId, $role, $action, $table, $recordId, $oldValues, $newValues) {
    $stmt = $pdo->prepare("
        INSERT INTO audit_log (user_id, user_role, action, table_name, record_id, 
            old_values, new_values, ip_address, user_agent, session_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->execute([
        $userId, $role, $action, $table, $recordId,
        $oldValues ? json_encode($oldValues) : null,
        $newValues ? json_encode($newValues) : null,
        $_SERVER['REMOTE_ADDR'],
        $_SERVER['HTTP_USER_AGENT'],
        session_id()
    ]);
}
?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>বাস সাবস্ক্রিপশন ব্যবস্থাপনা - <?= APP_NAME ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    <link href="../../assets/css/style.css" rel="stylesheet">
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">
                <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
                    <h1 class="h2">🎫 ছাত্র বাস সাবস্ক্রিপশন</h1>
                    <div class="btn-toolbar mb-2 mb-md-0">
                        <a href="../../index.php" class="btn btn-outline-primary btn-sm me-2">
                            <i class="fas fa-home"></i> হোম পেজ
                        </a>
                        <a href="buses.php" class="btn btn-outline-secondary me-2">
                            <i class="fas fa-arrow-left"></i> বাস তালিকায় ফিরে যান
                        </a>
                        <button class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addSubscriptionModal">
                            <i class="fas fa-plus"></i> নতুন সাবস্ক্রিপশন যোগ করুন
                        </button>
                    </div>
                </div>
                
                <?php if ($success_message): ?>
                    <div class="alert alert-success"><?= $success_message ?></div>
                <?php endif; ?>
                
                <?php if ($error_message): ?>
                    <div class="alert alert-danger"><?= $error_message ?></div>
                <?php endif; ?>
                
                <!-- Statistics Cards -->
                <div class="row mb-4">
                    <div class="col-md-4">
                        <div class="card bg-primary text-white">
                            <div class="card-body">
                                <h5>মোট সাবস্ক্রিপশন</h5>
                                <h2><?= $total_subscriptions ?></h2>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="card bg-success text-white">
                            <div class="card-body">
                                <h5>অ্যাক্টিভ সাবস্ক্রিপশন</h5>
                                <h2><?= $active_subscriptions ?></h2>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="card bg-warning text-dark">
                            <div class="card-body">
                                <h5>মেয়াদোত্তীর্ণ</h5>
                                <h2><?= $expired_subscriptions ?></h2>
                            </div>
                        </div>
                    </div>
                </div>
                
                <!-- Subscriptions List -->
                <div class="card">
                    <div class="card-header">
                        <h5 class="mb-0">ছাত্র বাস সাবস্ক্রিপশনের তালিকা</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>
                                        <th>স্ট্যাটাস</th>
                                        <th>অ্যাকশন</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($subscriptions as $sub): ?>
                                    <tr>
                                        <td>
                                            <strong><?= htmlspecialchars($sub['student_name']) ?></strong><br>
                                            <small class="text-muted">ID: <?= $sub['student_roll'] ?></small>
                                        </td>
                                        <td><?= htmlspecialchars($sub['class_name']) ?></td>
                                        <td>
                                            <strong><?= htmlspecialchars($sub['bus_number']) ?></strong><br>
                                            <small class="text-muted"><?= htmlspecialchars($sub['bus_name']) ?></small>
                                        </td>
                                        <td>
                                            <strong><?= htmlspecialchars($sub['route_code']) ?></strong><br>
                                            <small class="text-muted"><?= htmlspecialchars($sub['route_name']) ?></small>
                                        </td>
                                        <td><?= htmlspecialchars($sub['pickup_stop'] ?? 'N/A') ?></td>
                                        <td><?= htmlspecialchars($sub['drop_stop'] ?? 'N/A') ?></td>
                                        <td><?= $sub['start_date'] ?></td>
                                        <td><?= $sub['end_date'] ?? 'চলমান' ?></td>
                                        <td>
                                            <?php 
                                            $is_expired = $sub['end_date'] && strtotime($sub['end_date']) < time();
                                            $status_class = $is_expired ? 'secondary' : ($sub['status'] == 'active' ? 'success' : 'warning');
                                            $status_text = $is_expired ? 'মেয়াদোত্তীর্ণ' : ($sub['status'] == 'active' ? 'অ্যাক্টিভ' : 'নিষ্ক্রিয়');
                                            ?>
                                            <span class="badge bg-<?= $status_class ?>"><?= $status_text ?></span>
                                        </td>
                                        <td>
                                            <button class="btn btn-sm btn-info" onclick="viewSubscription(<?= $sub['id'] ?>)" title="বিস্তারিত দেখুন">
                                                <i class="fas fa-eye"></i>
                                            </button>
                                            <button class="btn btn-sm btn-warning" onclick="editSubscription(<?= $sub['id'] ?>)" title="সম্পাদনা">
                                                <i class="fas fa-edit"></i>
                                            </button>
                                            <button class="btn btn-sm btn-danger" onclick="deleteSubscription(<?= $sub['id'] ?>)" title="মুছুন">
                                                <i class="fas fa-trash"></i>
                                            </button>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
                
                <!-- Navigation Links -->
                <div class="mt-4">
                    <a href="buses.php" class="btn btn-outline-primary">
                        <i class="fas fa-bus"></i> বাস ব্যবস্থাপনা
                    </a>
                    <a href="bus_routes.php" class="btn btn-outline-success">
                        <i class="fas fa-route"></i> রুট ব্যবস্থাপনা
                    </a>
                    <a href="bus_attendance.php" class="btn btn-outline-info">
                        <i class="fas fa-clipboard-check"></i> বাস উপস্থিতি
                    </a>
                </div>
            </main>
        </div>
    </div>
    
    <!-- Add Subscription Modal -->
    <div class="modal fade" id="addSubscriptionModal" tabindex="-1">
        <div class="modal-dialog modal-lg">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">নতুন বাস সাবস্ক্রিপশন যোগ করুন</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <form method="POST">
                    <?= csrf_field() ?>
                    <div class="modal-body">
                        <div class="row">
                            <div class="col-md-6 mb-3">
                                <label class="form-label">ছাত্র নির্বাচন করুন <span class="text-danger">*</span></label>
                                <select name="student_id" class="form-select" required>
                                    <option value="">-- ছাত্র নির্বাচন করুন --</option>
                                    <?php foreach ($students as $student): ?>
                                        <option value="<?= $student['id'] ?>">
                                            <?= htmlspecialchars($student['name']) ?> (<?= $student['student_id'] ?>) - <?= htmlspecialchars($student['class_name']) ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">বাস নির্বাচন করুন <span class="text-danger">*</span></label>
                                <select name="bus_id" class="form-select" required id="busSelect">
                                    <option value="">-- বাস নির্বাচন করুন --</option>
                                    <?php foreach ($buses as $bus): ?>
                                        <option value="<?= $bus['id'] ?>" data-capacity="<?= $bus['capacity'] ?>">
                                            <?= htmlspecialchars($bus['bus_number']) ?> - <?= htmlspecialchars($bus['bus_name']) ?> (আসন: <?= $bus['capacity'] ?>)
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">রুট নির্বাচন করুন <span class="text-danger">*</span></label>
                                <select name="route_id" class="form-select" required>
                                    <option value="">-- রুট নির্বাচন করুন --</option>
                                    <?php foreach ($routes as $route): ?>
                                        <option value="<?= $route['id'] ?>">
                                            <?= htmlspecialchars($route['route_code']) ?> - <?= htmlspecialchars($route['route_name']) ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">পিকআপ স্টপ ID</label>
                                <input type="number" name="pickup_stop_id" class="form-control" placeholder="স্টপ আইডি">
                                <small class="text-muted">রুট স্টপ পেজ থেকে আইডি দেখুন</small>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">ড্রপ স্টপ ID</label>
                                <input type="number" name="drop_stop_id" class="form-control" placeholder="স্টপ আইডি">
                                <small class="text-muted">রুট স্টপ পেজ থেকে আইডি দেখুন</small>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">শুরুর তারিখ <span class="text-danger">*</span></label>
                                <input type="date" name="start_date" class="form-control" required value="<?= date('Y-m-d') ?>">
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">শেষ তারিখ</label>
                                <input type="date" name="end_date" class="form-control">
                                <small class="text-muted">ঐচ্ছিক (খালি রাখলে চলমান থাকবে)</small>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">স্ট্যাটাস</label>
                                <select name="status" class="form-select">
                                    <option value="active">অ্যাক্টিভ</option>
                                    <option value="inactive">নিষ্ক্রিয়</option>
                                    <option value="suspended">স্থগিত</option>
                                </select>
                            </div>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">বন্ধ করুন</button>
                        <button type="submit" name="add_subscription" class="btn btn-primary">সংরক্ষণ করুন</button>
                    </div>
                </form>
            </div>
        </div>
    </div>
    
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        function viewSubscription(id) {
            // Implementation would show subscription details
            alert('সাবস্ক্রিপশন বিস্তারিত দেখুন (ID: ' + id + ')');
        }
        
        function editSubscription(id) {
            // Implementation would fetch subscription data
            alert('সাবস্ক্রিপশন সম্পাদনা (ID: ' + id + ')');
        }
        
        function deleteSubscription(id) {
            if (confirm('আপনি কি নিশ্চিতভাবে এই সাবস্ক্রিপশন মুছে ফেলতে চান?')) {
                const form = document.createElement('form');
                form.method = 'POST';
                form.innerHTML = `
                    <?= csrf_field() ?>
                    <input type="hidden" name="subscription_id" value="${id}">
                    <input type="hidden" name="delete_subscription" value="1">
                `;
                document.body.appendChild(form);
                form.submit();
            }
        }
    </script>
</body>
</html>
