CREATE TABLE addresses
(
    id   serial primary key,
    name varchar(100) NOT NULL
);

CREATE table categories
(
    id   serial primary key,
    name varchar(10) NOT NULL
);

Create table clients
(
    id           serial primary key,
    full_name    varchar(50) NOT NULL,
    phone_number varchar(20) NOT NULL
);

CREATE TABLE drivers
(
    id         serial primary key,
    first_name varchar(30) not null,
    last_name  varchar(30) not null,
    age        INT         not null,
    rating     numeric(2) DEFAULT 5.5
);

CREATE TABLE cars
(
    id          serial primary key,
    make        varchar(20)                    NOT NULL,
    model       varchar(20),
    year        INT default 0 CHECK (year > 0) NOT NULL,
    mileage     INT default 0 CHECK (mileage > 0),
    condition   varchar(1)                     NOT NULL,
    category_id INT                            NOT NULL,
    CONSTRAINT fk_cars_categories FOREIGN KEY (category_id) REFERENCES categories (id)
);

CREATE TABLE courses
(
    id              serial primary key,
    from_address_id INT       not null,
    start           TIMESTAMP not null,
    bill            NUMERIC(10, 2) DEFAULT 10 CHECK (bill > 10),
    car_id          INT       not null,
    client_id       INT       not null,
    CONSTRAINT fk_courses_addresses FOREIGN KEY (from_address_id) REFERENCES addresses (id) ON DELETE CASCADE,
    CONSTRAINT fk_courses_cars FOREIGN KEY (car_id) REFERENCES cars (id) ON DELETE CASCADE,
    CONSTRAINT fk_courses_clients FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE
);

CREATE TABLE cars_drivers
(
    car_id    INT not null,
    driver_id INT not null,
    CONSTRAINT fk_cars_drivers_cars FOREIGN KEY (car_id) REFERENCES cars (id) ON DELETE CASCADE,
    CONSTRAINT fk_cars_drivers_drivers FOREIGN KEY (driver_id) REFERENCES drivers (id) ON DELETE CASCADE
);