---

-- Таблица Stops (создаём первой, так как на неё ссылается Route)
CREATE TABLE Stops (
    id_stops INT PRIMARY KEY,
    stop_name VARCHAR(100),
    address VARCHAR(100),
    nearby VARCHAR(100)
);

-- Таблица Route
CREATE TABLE Route (
    id_route INT PRIMARY KEY,
    route_number VARCHAR(20),
    time TIME,
    start_route INT,
    end_route INT,
    ticket_price DECIMAL(10, 2),
    activity BIT,
    FOREIGN KEY (start_route) REFERENCES Stops(id_stops),
    FOREIGN KEY (end_route) REFERENCES Stops(id_stops)
);

-- Таблица Car (создаём до Driver, так как Driver ссылается на Car)
CREATE TABLE Car (
    id_car INT PRIMARY KEY,
    driver_route VARCHAR(50),
    id_route INT,
    car_registration_number VARCHAR(20),
    car_make VARCHAR(50),
    year_of_manufacture INT,
    number_of_seats INT,
    FOREIGN KEY (id_route) REFERENCES Route(id_route)
);

-- Таблица 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,
    FOREIGN KEY (id_car) REFERENCES Car(id_car)
);

-- Таблица 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)
);