BEGIN;

CREATE SCHEMA auth;
CREATE SCHEMA route;
CREATE SCHEMA ticket;
CREATE SCHEMA train;
CREATE SCHEMA trip;

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,
    active BOOLEAN DEFAULT 'true' NOT NULL,
    is_admin BOOLEAN DEFAULT 'false' NOT NULL,
    CONSTRAINT pk_user PRIMARY KEY (id)
);

COMMENT ON TABLE auth."user" IS 'Пользователь';
COMMENT ON COLUMN auth."user".name IS 'Полное имя пользователя';
COMMENT ON COLUMN auth."user".created_on IS 'Дата и время регистрации';
COMMENT ON COLUMN auth."user".last_login IS 'Дата и время последней авторизации';

CREATE UNIQUE INDEX ix_auth_user_email ON auth."user" (email);

CREATE TABLE route.route (
    id SERIAL NOT NULL,
    name VARCHAR(12) NOT NULL,
    CONSTRAINT pk_route PRIMARY KEY (id),
    CONSTRAINT uq_route_name UNIQUE (name)
);

COMMENT ON TABLE route.route IS 'Маршрут';

CREATE TABLE route.station (
    id SERIAL NOT NULL,
    name VARCHAR(64) NOT NULL,
    is_passenger BOOLEAN DEFAULT true NOT NULL,
    CONSTRAINT pk_station PRIMARY KEY (id)
);

COMMENT ON TABLE route.station IS 'Станция';

CREATE UNIQUE INDEX ix_route_station_name ON route.station (name);

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

COMMENT ON TABLE train.train IS 'Поезд (состав)';

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 DEFAULT '0' NOT NULL,
    CONSTRAINT pk_wagon_class PRIMARY KEY (id),
    CONSTRAINT uq_wagon_class_name UNIQUE (name)
);

COMMENT ON TABLE train.wagon_class IS 'Класс/тип вагона';
COMMENT ON COLUMN train.wagon_class.seats IS 'Описание мест';
COMMENT ON COLUMN train.wagon_class.seats_schema IS 'Схема расположения мест в вагоне';
COMMENT ON COLUMN train.wagon_class.seats_count IS 'Количество мест';

CREATE INDEX ix_train_wagon_class_seats ON train.wagon_class (seats);

CREATE TABLE route.stage (
    route_id INTEGER NOT NULL,
    station_id INTEGER NOT NULL,
    arrival INTERVAL,
    departure INTERVAL,
    CONSTRAINT pk_stage PRIMARY KEY (route_id, station_id),
    CONSTRAINT ck_stage_arrival_lt_departure CHECK (departure > arrival),
    CONSTRAINT ck_stage_not_null_either_arrival_or_departure CHECK (true),
    CONSTRAINT fk_stage_route_id_route FOREIGN KEY(route_id) REFERENCES route.route (id) ON DELETE cascade,
    CONSTRAINT fk_stage_station_id_station FOREIGN KEY(station_id) REFERENCES route.station (id) ON DELETE restrict
);

COMMENT ON TABLE route.stage IS 'Этап маршрута';
COMMENT ON COLUMN route.stage.station_id IS 'Станция, на которой происходит стоянка';
COMMENT ON COLUMN route.stage.arrival IS 'Время прибытия. Если NULL, значит это - начальная станция';
COMMENT ON COLUMN route.stage.departure IS 'Время отправления. Если NULL, значит это - конечная станция';

CREATE TABLE train.wagon (
    id SERIAL NOT NULL,
    train_id INTEGER NOT NULL,
    number INTEGER NOT NULL,
    wagon_class_id INTEGER NOT NULL,
    CONSTRAINT pk_wagon PRIMARY KEY (id),
    CONSTRAINT fk_wagon_train_id_train FOREIGN KEY(train_id) REFERENCES train.train (id) ON DELETE cascade,
    CONSTRAINT fk_wagon_wagon_class_id_wagon_class FOREIGN KEY(wagon_class_id) REFERENCES train.wagon_class (id) ON DELETE restrict,
    CONSTRAINT uq_wagon_train_id_number UNIQUE (train_id, number)
);

COMMENT ON TABLE train.wagon IS 'Вагон состава';
COMMENT ON COLUMN train.wagon.train_id IS 'Состав, в который входит вагон';
COMMENT ON COLUMN train.wagon.number IS 'Номер вагона в составе';

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,
    CONSTRAINT pk_trip PRIMARY KEY (id),
    CONSTRAINT fk_trip_route_id_route FOREIGN KEY(route_id) REFERENCES route.route (id) ON DELETE restrict,
    CONSTRAINT fk_trip_train_id_train FOREIGN KEY(train_id) REFERENCES train.train (id) ON DELETE restrict
);

COMMENT ON TABLE trip.trip IS 'рейс';
COMMENT ON COLUMN trip.trip.price_multiplier IS 'Множитель стоимости на все цены в рейсе';
COMMENT ON COLUMN trip.trip.departure_date IS 'День начала рейса';
COMMENT ON COLUMN trip.trip.route_id IS 'Маршрут рейса';
COMMENT ON COLUMN trip.trip.train_id IS 'Состав, идущий в рейс';

CREATE TABLE ticket.ticket (
    id SERIAL NOT NULL,
    status INTEGER DEFAULT '0' NOT NULL,
    user_id INTEGER NOT NULL,
    trip_id INTEGER NOT NULL,
    wagon_id INTEGER NOT NULL,
    seat_number INTEGER NOT NULL,
    departure_id INTEGER NOT NULL,
    arrival_id INTEGER NOT NULL,
    CONSTRAINT pk_ticket PRIMARY KEY (id),
    CONSTRAINT fk_ticket_arrival_id_station FOREIGN KEY(arrival_id) REFERENCES route.station (id) ON DELETE restrict,
    CONSTRAINT fk_ticket_departure_id_station FOREIGN KEY(departure_id) REFERENCES route.station (id) ON DELETE restrict,
    CONSTRAINT fk_ticket_trip_id_trip FOREIGN KEY(trip_id) REFERENCES trip.trip (id) ON DELETE restrict,
    CONSTRAINT fk_ticket_user_id_user FOREIGN KEY(user_id) REFERENCES auth."user" (id),
    CONSTRAINT fk_ticket_wagon_id_wagon FOREIGN KEY(wagon_id) REFERENCES train.wagon (id) ON DELETE restrict
);

COMMENT ON TABLE ticket.ticket IS 'Купленый билет';
COMMENT ON COLUMN ticket.ticket.status IS 'Статус билета: 0 - BOOKED, 1 - PAID, 2 - CANCELED';
COMMENT ON COLUMN ticket.ticket.user_id IS 'Покупатель';
COMMENT ON COLUMN ticket.ticket.trip_id IS 'Рейс, на который куплен билет';
COMMENT ON COLUMN ticket.ticket.wagon_id IS 'Вагон, в который куплен билет';
COMMENT ON COLUMN ticket.ticket.seat_number IS 'Место в вагоне';
COMMENT ON COLUMN ticket.ticket.departure_id IS 'Станция с которой куплен билет';
COMMENT ON COLUMN ticket.ticket.arrival_id IS 'Станция до которой куплен билет';

CREATE INDEX ix_ticket_trip_wagon_seat ON ticket.ticket (trip_id, wagon_id, seat_number);

CREATE TABLE ticket.log (
    id SERIAL NOT NULL,
    ticket_id INTEGER NOT NULL,
    old_status INTEGER,
    new_status INTEGER NOT NULL,
    datetime TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    CONSTRAINT pk_log PRIMARY KEY (id),
    CONSTRAINT fk_log_ticket_id_ticket FOREIGN KEY(ticket_id) REFERENCES ticket.ticket (id) ON DELETE cascade
);

COMMENT ON TABLE ticket.log IS 'Журнал статусов билетов';
COMMENT ON COLUMN ticket.log.old_status IS 'Предыдущий статус билета';
COMMENT ON COLUMN ticket.log.new_status IS 'Новый статус билета';

CREATE INDEX ix_ticket_log_ticket_id ON ticket.log (ticket_id);

COMMIT;