<?php
/**
 * Comprehensive fix for transport system
 */

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

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

echo "<h2>Transport System Fix Tool</h2>";

try {
    // Create all transport tables if they don't exist
    $transport_tables_sql = [
        'buses' => "CREATE TABLE IF NOT EXISTS buses (
            id INT AUTO_INCREMENT PRIMARY KEY,
            bus_number VARCHAR(20) UNIQUE NOT NULL,
            bus_name VARCHAR(100),
            model VARCHAR(50),
            capacity INT DEFAULT 50,
            driver_name VARCHAR(100),
            driver_phone VARCHAR(20),
            conductor_name VARCHAR(100),
            conductor_phone VARCHAR(20),
            registration_number VARCHAR(50),
            insurance_expiry DATE,
            fitness_certificate_expiry DATE,
            status ENUM('active', 'maintenance', 'retired') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )",
        
        'bus_routes' => "CREATE TABLE IF NOT EXISTS bus_routes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            route_name VARCHAR(100) NOT NULL,
            route_code VARCHAR(20) UNIQUE NOT NULL,
            start_point VARCHAR(200) NOT NULL,
            end_point VARCHAR(200) NOT NULL,
            distance_km DECIMAL(8,2),
            estimated_time TIME,
            fare_amount DECIMAL(10,2) DEFAULT 0.00,
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )",
        
        'bus_route_stops' => "CREATE TABLE IF NOT EXISTS bus_route_stops (
            id INT AUTO_INCREMENT PRIMARY KEY,
            route_id INT NOT NULL,
            stop_name VARCHAR(200) NOT NULL,
            stop_order INT NOT NULL,
            arrival_time TIME,
            departure_time TIME,
            fare_from_start DECIMAL(10,2) DEFAULT 0.00,
            landmark VARCHAR(200),
            FOREIGN KEY (route_id) REFERENCES bus_routes(id) ON DELETE CASCADE,
            INDEX idx_route_order (route_id, stop_order)
        )",
        
        'bus_assignments' => "CREATE TABLE IF NOT EXISTS bus_assignments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            bus_id INT NOT NULL,
            route_id INT NOT NULL,
            shift ENUM('morning', 'evening', 'both') DEFAULT 'both',
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (bus_id) REFERENCES buses(id) ON DELETE CASCADE,
            FOREIGN KEY (route_id) REFERENCES bus_routes(id) ON DELETE CASCADE,
            UNIQUE KEY unique_bus_route_shift (bus_id, route_id, shift)
        )",
        
        'student_bus_subscriptions' => "CREATE TABLE IF NOT EXISTS student_bus_subscriptions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            bus_id INT NOT NULL,
            route_id INT NOT NULL,
            pickup_stop_id INT,
            drop_stop_id INT,
            shift ENUM('morning', 'evening', 'both') DEFAULT 'both',
            monthly_fare DECIMAL(10,2) DEFAULT 0.00,
            subscription_date DATE,
            valid_until DATE,
            status ENUM('active', 'suspended', 'cancelled') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (bus_id) REFERENCES buses(id) ON DELETE CASCADE,
            FOREIGN KEY (route_id) REFERENCES bus_routes(id) ON DELETE CASCADE,
            FOREIGN KEY (pickup_stop_id) REFERENCES bus_route_stops(id) ON DELETE SET NULL,
            FOREIGN KEY (drop_stop_id) REFERENCES bus_route_stops(id) ON DELETE SET NULL
        )",
        
        'bus_attendance' => "CREATE TABLE IF NOT EXISTS bus_attendance (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            bus_id INT NOT NULL,
            route_id INT NOT NULL,
            date DATE NOT NULL,
            morning_pickup BOOLEAN DEFAULT FALSE,
            morning_drop BOOLEAN DEFAULT FALSE,
            evening_pickup BOOLEAN DEFAULT FALSE,
            evening_drop BOOLEAN DEFAULT FALSE,
            marked_by INT,
            remarks TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (bus_id) REFERENCES buses(id) ON DELETE CASCADE,
            FOREIGN KEY (route_id) REFERENCES bus_routes(id) ON DELETE CASCADE,
            FOREIGN KEY (marked_by) REFERENCES users(id) ON DELETE SET NULL,
            UNIQUE KEY unique_student_date (student_id, date)
        )"
    ];

    echo "<h3>Creating Transport Tables...</h3>";
    $created_tables = [];
    
    foreach ($transport_tables_sql as $table_name => $sql) {
        try {
            $pdo->exec($sql);
            echo "<p style='color: green;'>✅ $table_name table - OK</p>";
            $created_tables[] = $table_name;
        } catch (Exception $e) {
            echo "<p style='color: red;'>❌ Error creating $table_name table: " . $e->getMessage() . "</p>";
        }
    }

    // Check if admin user exists, if not create one
    $admin_check = $pdo->query("SELECT COUNT(*) as count FROM users WHERE role='admin'")->fetch();
    if ($admin_check['count'] == 0) {
        echo "<h3>No admin user found. Creating default admin...</h3>";
        $default_password = password_hash('admin123', PASSWORD_DEFAULT);
        try {
            $pdo->exec("INSERT INTO users (username, password, email, role, status) VALUES ('admin', '$default_password', 'admin@example.com', 'admin', 'active')");
            echo "<p style='color: green;'>✅ Default admin user created (username: admin, password: admin123)</p>";
        } catch (Exception $e) {
            echo "<p style='color: orange;'>⚠️ Could not create admin user: " . $e->getMessage() . "</p>";
        }
    } else {
        echo "<p style='color: green;'>✅ Admin user already exists</p>";
        
        // Also check if there are any active admin users
        $active_admin_check = $pdo->query("SELECT COUNT(*) as count FROM users WHERE role='admin' AND status='active'")->fetch();
        if ($active_admin_check['count'] == 0) {
            echo "<p style='color: orange;'>⚠️ Found admin users but they might be inactive. Attempting to activate...</p>";
            try {
                $pdo->exec("UPDATE users SET status='active' WHERE role='admin' LIMIT 1");
                echo "<p style='color: green;'>✅ Activated an admin user</p>";
            } catch (Exception $e) {
                echo "<p style='color: orange;'>⚠️ Could not activate admin user: " . $e->getMessage() . "</p>";
            }
        }
    }

    // Check login status
    session_start();
    if (isset($_SESSION['user_id'])) {
        echo "<p style='color: green;'>✅ You are currently logged in as: {$_SESSION['username']} (Role: {$_SESSION['user_role']})</p>";
    } else {
        echo "<p style='color: orange;'>⚠️ You are NOT logged in. Please log in first.</p>";
    }

    echo "<h3>All Transport Pages:</h3>";
    echo "<ul>";
    echo "<li><a href='admin/transport/buses.php'>🚌 Buses Management</a></li>";
    echo "<li><a href='admin/transport/bus_routes.php'>🛤️ Bus Routes Management</a></li>";
    echo "<li><a href='admin/transport/bus_subscriptions.php'>🎫 Student Subscriptions</a></li>";
    echo "<li><a href='admin/transport/bus_attendance.php'>📋 Bus Attendance</a></li>";
    echo "</ul>";

} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error: " . $e->getMessage() . "</p>";
}

echo "<h3>Quick Actions:</h3>";
echo "<ul>";
echo "<li><a href='login.php'>🔐 Login Page</a> (Default: admin/admin123)</li>";
echo "<li><a href='index.php'>🏠 Home Page</a></li>";
echo "<li><a href='debug_buses.php'>🔍 Run Diagnostic Again</a></li>";
echo "</ul>";

echo "<p><strong>Note:</strong> If pages still don't load after fixing tables, make sure you're logged in first.</p>";
?>