---

-- Таблица Driver (Водители)
CREATE TABLE Driver (
    id_driver INT PRIMARY KEY,
    First_name VARCHAR(50),
    Last_name VARCHAR(50),
    Patronymic VARCHAR(50),
    Passport VARCHAR(20),
    House_address VARCHAR(100),
    Home_phone VARCHAR(20),
    driver_license VARCHAR(20),
    experience INT,
    id_car INT
);

-- Таблица Car (Автомобили)
CREATE TABLE Car (
    id_car INT PRIMARY KEY,
    driver_route VARCHAR(50),
    id_driver INT,
    id_route INT,
    car_registration_number VARCHAR(20),
    car_make VARCHAR(50),
    year_of_manufacture INT,
    number_of_seats INT,
    FOREIGN KEY (id_driver) REFERENCES Driver(id_driver)
);

-- Таблица Route (Маршруты)
CREATE TABLE Route (
    id_route INT PRIMARY KEY,
    route_number VARCHAR(20),
    time TIME,
    start_route VARCHAR(100),
    end_route VARCHAR(100),
    ticket_price DECIMAL(10, 2),
    activity BOOLEAN
);

-- Таблица Stops (Остановки)
CREATE TABLE Stops (
    id_stops INT PRIMARY KEY,
    stop_name VARCHAR(100),
    adress VARCHAR(100),
    nearby VARCHAR(100),
    id_route INT,
    FOREIGN KEY (id_route) REFERENCES Route(id_route)
);

-- Таблица DriverRoute (Связь водителей и маршрутов)
CREATE TABLE DriverRoute (
    id_Voditel_Route INT PRIMARY KEY,
    id_driver INT,
    id_route INT,
    FOREIGN KEY (id_driver) REFERENCES Driver(id_driver),
    FOREIGN KEY (id_route) REFERENCES Route(id_route)
);

-- Добавляем внешние ключи в Driver и Car после создания всех таблиц
ALTER TABLE Driver
    ADD FOREIGN KEY (id_car) REFERENCES Car(id_car);

ALTER TABLE Car
    ADD FOREIGN KEY (id_route) REFERENCES Route(id_route);