setup_database.php

<?php
/**
 * Database Setup Script
 * Run this file once to create the database and tables
 * Access via browser: http://localhost/setup_database.php
 */

// Database configuration (update these if needed)
$host = 'localhost';
$username = 'root';
$password = '';
$dbname = 'main_db';

// Connect to MySQL server (without selecting a database)
try {
    $conn = new PDO("mysql:host=$host;charset=utf8mb4", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h2>Database Setup Script</h2>";
    echo "<p>Connecting to MySQL server...</p>";
    
    // Create database if it doesn't exist
    $conn->exec("CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    echo "<p style='color: green;'>✓ Database '$dbname' created or already exists.</p>";
    
    // Select the database
    $conn->exec("USE `$dbname`");
    echo "<p>Using database '$dbname'...</p>";
    
    // Create users table
    $createUsersTable = "
    CREATE TABLE IF NOT EXISTS users (
        phone VARCHAR(12) PRIMARY KEY,
        password VARCHAR(255) NOT NULL,
        firstName VARCHAR(100) NOT NULL,
        lastName VARCHAR(100) NOT NULL,
        dob DATE NOT NULL,
        email VARCHAR(255) NOT NULL,
        gender VARCHAR(10)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $conn->exec($createUsersTable);
    echo "<p style='color: green;'>✓ Table 'users' created or already exists.</p>";
    
    // Create flights table
    $createFlightsTable = "
    CREATE TABLE IF NOT EXISTS flights (
        flight_id VARCHAR(20) PRIMARY KEY,
        origin VARCHAR(100) NOT NULL,
        destination VARCHAR(100) NOT NULL,
        departure_date DATE NOT NULL,
        arrival_date DATE NOT NULL,
        departure_time TIME NOT NULL,
        arrival_time TIME NOT NULL,
        available_seats INT NOT NULL,
        price DECIMAL(10,2) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $conn->exec($createFlightsTable);
    echo "<p style='color: green;'>✓ Table 'flights' created or already exists.</p>";
    
    // Create passenger table
    $createPassengerTable = "
    CREATE TABLE IF NOT EXISTS passenger (
        ssn VARCHAR(20) PRIMARY KEY,
        first_name VARCHAR(100) NOT NULL,
        last_name VARCHAR(100) NOT NULL,
        date_of_birth DATE NOT NULL,
        category VARCHAR(10) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $conn->exec($createPassengerTable);
    echo "<p style='color: green;'>✓ Table 'passenger' created or already exists.</p>";
    
    // Create flight-booking table
    $createFlightBookingTable = "
    CREATE TABLE IF NOT EXISTS flight_booking (
        flight_booking_id VARCHAR(50) PRIMARY KEY,
        flight_id VARCHAR(20) NOT NULL,
        total_price DECIMAL(10,2) NOT NULL,
        FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $conn->exec($createFlightBookingTable);
    echo "<p style='color: green;'>✓ Table 'flight_booking' created or already exists.</p>";
    
    // Create ticket table
    $createTicketTable = "
    CREATE TABLE IF NOT EXISTS ticket (
        ticket_id VARCHAR(50) PRIMARY KEY,
        flight_booking_id VARCHAR(50) NOT NULL,
        ssn VARCHAR(20) NOT NULL,
        price DECIMAL(10,2) NOT NULL,
        FOREIGN KEY (flight_booking_id) REFERENCES flight_booking(flight_booking_id),
        FOREIGN KEY (ssn) REFERENCES passenger(ssn)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $conn->exec($createTicketTable);
    echo "<p style='color: green;'>✓ Table 'ticket' created or already exists.</p>";
    
    // Create hotels table
    $createHotelsTable = "
    CREATE TABLE IF NOT EXISTS hotels (
        hotel_id VARCHAR(20) PRIMARY KEY,
        hotel_name VARCHAR(255) NOT NULL,
        city VARCHAR(100) NOT NULL,
        price_per_night DECIMAL(10,2) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $conn->exec($createHotelsTable);
    echo "<p style='color: green;'>✓ Table 'hotels' created or already exists.</p>";
    
    // Create hotel-booking table
    $createHotelBookingTable = "
    CREATE TABLE IF NOT EXISTS hotel_booking (
        hotel_booking_id VARCHAR(50) PRIMARY KEY,
        hotel_id VARCHAR(20) NOT NULL,
        check_in_date DATE NOT NULL,
        check_out_date DATE NOT NULL,
        number_of_rooms INT NOT NULL,
        price_per_night DECIMAL(10,2) NOT NULL,
        total_price DECIMAL(10,2) NOT NULL,
        FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $conn->exec($createHotelBookingTable);
    echo "<p style='color: green;'>✓ Table 'hotel_booking' created or already exists.</p>";
    
    // Create guesses table
    $createGuessesTable = "
    CREATE TABLE IF NOT EXISTS guesses (
        ssn VARCHAR(20) PRIMARY KEY,
        hotel_booking_id VARCHAR(50) NOT NULL,
        first_name VARCHAR(100) NOT NULL,
        last_name VARCHAR(100) NOT NULL,
        date_of_birth DATE NOT NULL,
        category VARCHAR(10) NOT NULL,
        FOREIGN KEY (hotel_booking_id) REFERENCES hotel_booking(hotel_booking_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $conn->exec($createGuessesTable);
    echo "<p style='color: green;'>✓ Table 'guesses' created or already exists.</p>";
    
    // Create admin user (phone: 222-222-2222)
    // Password: admin123 (hashed)
    $adminPassword = password_hash('admin123', PASSWORD_DEFAULT);
    $createAdmin = "
    INSERT INTO users (phone, password, firstName, lastName, dob, email, gender)
    VALUES ('222-222-2222', :password, 'Admin', 'User', '1990-01-01', 'admin@traveldeals.com', 'Male')
    ON DUPLICATE KEY UPDATE phone=phone;
    ";
    
    $stmt = $conn->prepare($createAdmin);
    $stmt->execute([':password' => $adminPassword]);
    echo "<p style='color: green;'>✓ Admin user created or already exists.</p>";
    echo "<p><strong>Admin Login:</strong></p>";
    echo "<ul>";
    echo "<li>Phone: 222-222-2222</li>";
    echo "<li>Password: admin123</li>";
    echo "</ul>";
    
    echo "<hr>";
    echo "<h3 style='color: green;'>✓ Database setup completed successfully!</h3>";
    echo "<p>You can now use the application. Make sure your db_config.php has the correct database name.</p>";
    echo "<p><a href='index.html'>Go to Home Page</a></p>";
    
} catch(PDOException $e) {
    echo "<h3 style='color: red;'>✗ Error: " . $e->getMessage() . "</h3>";
    echo "<p>Please check your MySQL credentials in this file and make sure MySQL is running.</p>";
}
?>