CREATE DATABASE airstar;
USE airstar;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);


CREATE TABLE flights (
    flight_id INT AUTO_INCREMENT PRIMARY KEY,
    origin VARCHAR(50) NOT NULL,
    destination VARCHAR(50) NOT NULL,
    departure_date DATE NOT NULL,
    return_date DATE,
    price DECIMAL(10, 2) NOT NULL
);

INSERT INTO flights (origin, destination, departure_date, return_date, price) VALUES
('Ciudad de México', 'Tokio', '2026-03-15', '2026-03-30', 18500.00),
('Guadalajara', 'Nueva York', '2026-03-18', '2026-03-25', 9800.00),
('Monterrey', 'París', '2026-04-02', '2026-04-15', 14200.00),
('Cancún', 'Madrid', '2026-05-10', '2026-05-20', 12500.00),
('Tijuana', 'Seúl', '2026-06-01', '2026-06-15', 17300.00),
('Cd. Juarez', 'Tokio', '2026-06-01', '2026-10-01', 15000.00),
('Cd. Juarez', 'París', '2026-11-20', '2027-01-10', 145000.00);

SELECT * FROM flights;

CREATE TABLE reservations (
    reservation_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    flight_id INT,
    reservation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
);