<?php
/**
 * Temporary script to create transport tables
 */

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

try {
    // Buses table
    $pdo->exec("
        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
        )
    ");

    // Routes table
    $pdo->exec("
        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
        )
    ");

    // Route stops table
    $pdo->exec("
        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 route assignments table
    $pdo->exec("
        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 table
    $pdo->exec("
        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 table
    $pdo->exec("
        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 "<h2>✅ Transport Tables Created Successfully!</h2>";
    echo "<p>The following tables have been created:</p>";
    echo "<ul>";
    echo "<li>buses</li>";
    echo "<li>bus_routes</li>";
    echo "<li>bus_route_stops</li>";
    echo "<li>bus_assignments</li>";
    echo "<li>student_bus_subscriptions</li>";
    echo "<li>bus_attendance</li>";
    echo "</ul>";
    echo "<p>You can now access the <a href='admin/transport/buses.php'>Bus Management System</a>.</p>";
    
} catch(PDOException $e) {
    echo "<h2>❌ Error creating tables: " . $e->getMessage() . "</h2>";
}
?>