DROP TABLE IF EXISTS addresses CASCADE;
CREATE TABLE addresses(
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);


DROP TABLE IF EXISTS categories CASCADE;
CREATE TABLE categories(
id SERIAL PRIMARY KEY,
name VARCHAR(10) NOT NULL
);


DROP TABLE IF EXISTS clients CASCADE;
CREATE TABLE clients(
id SERIAL PRIMARY KEY,
full_name VARCHAR(50) NOT NULL,
phone_number VARCHAR(20) NOT NULL
);


DROP TABLE IF EXISTS drivers CASCADE;
CREATE TABLE drivers(
id SERIAL PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
age INT NOT NULL CHECK (age > 0),
rating NUMERIC(2) DEFAULT 5.5
);

DROP TABLE IF EXISTS cars CASCADE;
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 CHAR(1) NOT NULL,
category_id INT NOT NULL,

CONSTRAINT fk_cars_categories
FOREIGN KEY (category_id)
REFERENCES categories(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);


DROP TABLE IF EXISTS courses CASCADE;
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 > 0),
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
ON UPDATE CASCADE,

CONSTRAINT fk_courses_cars
FOREIGN KEY (car_id)
REFERENCES cars(id)
ON DELETE CASCADE
ON UPDATE CASCADE,

CONSTRAINT fk_courses_clients
FOREIGN KEY (client_id)
REFERENCES clients(id)
ON DELETE CASCADE
ON UPDATE CASCADE

);

DROP TABLE IF EXISTS cars_drivers 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
ON UPDATE CASCADE,

CONSTRAINT fk_cars_drivers_drivers
FOREIGN KEY (driver_id)
REFERENCES drivers(id)
ON DELETE CASCADE
ON UPDATE CASCADE

);