---

CREATE TABLE clients(
	id_client INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	passport TINYINT(11) UNIQUE NOT NULL,
	date_give DATE NOT NULL,
	who_give VARCHAR(150) NOT NULL,
	number VARCHAR(20) UNIQUE NOT NULL DEFAULT " ",
	address VARCHAR(200) NOT NULL
);

CREATE TABLE routes(
id_routes INT AUTO_INCREMENT PRIMARY KEY,
    name_route VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    target_routes VARCHAR(200) NOT NULL DEFAULT 'Отдых'
);


CREATE TABLE price(
	id_price INT AUTO_INCREMENT PRIMARY KEY,
	id_routes INT NOT NULL UNIQUE,
    price_1_day DECIMAL(10,2) NOT NULL,
    price_ts DECIMAL(10,2) NOT NULL,
    price_visa DECIMAL(10,2) NOT NULL,
    
   CONSTRAINT for_id_routes
    FOREIGN KEY(id_routes)
    REFERENCES routes(id_routes)
);

CREATE TABLE trips(
id_trips INT AUTO_INCREMENT PRIMARY KEY,
id_client INT NOT NULL,
id_routes INT NOT NULL,
date_start DATE NOT NULL,
quantity_day INT NOT NULL,

CONSTRAINT for_id_client
	FOREIGN KEY(id_client)
    REFERENCES clients(id_client),
    
CONSTRAINT for_id_route
	FOREIGN KEY(id_route)
    REFERENCES routes(id_routes),

CONSTRAINT check_quantity_day
    CHECK(quantity_day > 0)
);