CREATE SCHEMA route;

CREATE SCHEMA station;

CREATE SCHEMA train;

CREATE SCHEMA ticket;

CREATE SCHEMA trip;

CREATE SCHEMA auth;

CREATE TABLE route.stage (
    route_id INTEGER NOT NULL,
    station_id INTEGER NOT NULL,
    arrival INTERVAL NOT NULL,
    departure INTERVAL NOT NULL,
    PRIMARY KEY (route_id, station_id)
);

CREATE TABLE station.station (
    id SERIAL NOT NULL,
    name VARCHAR(64) NOT NULL,
    is_passanger BOOLEAN NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE train.train (
    id SERIAL NOT NULL,
    name VARCHAR(32) NOT NULL,
    price_multiplier FLOAT DEFAULT '1' NOT NULL,
    PRIMARY KEY (id)
);

COMMENT ON COLUMN train.train.price_multiplier IS 'Множитель стоимости на все цены';

CREATE TABLE train.wagon_class (
    id SERIAL NOT NULL,
    name VARCHAR(8) NOT NULL,
    seats JSONB NOT NULL,
    seats_schema BYTEA,
    seats_count INTEGER NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE auth."user" (
    id SERIAL NOT NULL,
    name VARCHAR NOT NULL,
    birthday DATE NOT NULL,
    email VARCHAR(40),
    password_hash VARCHAR(200) NOT NULL,
    created_on TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    last_login TIMESTAMP WITHOUT TIME ZONE,
    is_admin BOOLEAN NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (email)
);

COMMENT ON COLUMN auth."user".name IS 'Полное имя пользователя';

CREATE TABLE route.route (
    id SERIAL NOT NULL,
    name VARCHAR(12) NOT NULL,
    departure_station_id INTEGER NOT NULL,
    arrival_station_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(arrival_station_id) REFERENCES station.station (id),
    FOREIGN KEY(departure_station_id) REFERENCES station.station (id),
    UNIQUE (name)
);

CREATE TABLE train.wagon (
    train_id INTEGER NOT NULL,
    number INTEGER NOT NULL,
    wagon_class_id INTEGER NOT NULL,
    PRIMARY KEY (train_id, number),
    FOREIGN KEY(train_id) REFERENCES train.train (id),
    FOREIGN KEY(wagon_class_id) REFERENCES train.wagon_class (id)
);

CREATE TABLE trip.trip (
    id SERIAL NOT NULL,
    name VARCHAR(32) NOT NULL,
    price_multiplier FLOAT DEFAULT '1' NOT NULL,
    departure_date DATE NOT NULL,
    route_id INTEGER NOT NULL,
    train_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(route_id) REFERENCES route.route (id),
    FOREIGN KEY(train_id) REFERENCES train.train (id)
);

COMMENT ON COLUMN trip.trip.price_multiplier IS 'Множитель стоимости на все цены';

CREATE TABLE ticket.purchased (
    id SERIAL NOT NULL,
    trip_id INTEGER NOT NULL,
    wagon_number INTEGER NOT NULL,
    seat_number INTEGER NOT NULL,
    departure_id INTEGER NOT NULL,
    arrival_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    puchase_datetime TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(arrival_id) REFERENCES station.station (id),
    FOREIGN KEY(departure_id) REFERENCES station.station (id),
    FOREIGN KEY(trip_id) REFERENCES trip.trip (id),
    FOREIGN KEY(user_id) REFERENCES auth."user" (id)
);