schema.sql

        DROP TABLE IF EXISTS client_data cascade;

        DROP TABLE IF EXISTS destination cascade;

        DROP TABLE IF EXISTS guide_data cascade;

        DROP TABLE IF EXISTS reservation cascade;

        DROP TABLE IF EXISTS reservation_offer cascade;

        DROP TABLE IF EXISTS tour cascade;

        DROP TABLE IF EXISTS user_data cascade;

        DROP TABLE IF EXISTS user_data_roles cascade;

    CREATE TABLE user_data (
           id  bigserial NOT NULL,
            password VARCHAR(255),
            username VARCHAR(255),
            PRIMARY KEY (id)
        );

    CREATE TABLE client_data (
       id  bigserial NOT NULL,
        name VARCHAR(255),
        passport_number VARCHAR(255),
        surname VARCHAR(255),
        user_data_id int8,
        PRIMARY KEY (id)
    );

    CREATE TABLE destination (
       id  bigserial NOT NULL,
        description VARCHAR(255),
        name VARCHAR(255),
        PRIMARY KEY (id)
    );

    CREATE TABLE guide_data (
       id  bigserial NOT NULL,
        name VARCHAR(255),
        surname VARCHAR(255),
        user_data_id int8,
        PRIMARY KEY (id)
    );

    CREATE TABLE reservation (
       id  bigserial NOT NULL,
        actual_price float8 NOT NULL,
        client_id int8,
        tour_id int8,
        PRIMARY KEY (id)
    );

    CREATE TABLE reservation_offer (
       id  bigserial NOT NULL,
        agreed_admin BOOLEAN NOT NULL,
        agreed_client BOOLEAN NOT NULL,
        price float8 NOT NULL,
        client_data_id int8,
        tour_id int8,
        PRIMARY KEY (id)
    );

    CREATE TABLE tour (
       id  bigserial NOT NULL,
        end_date DATE,
        initial_price float8 NOT NULL,
        start_date DATE,
        destination_id int8,
        guide_id int8,
        PRIMARY KEY (id)
    );



    CREATE TABLE user_data_roles (
       user_data_id int8 NOT NULL,
        roles VARCHAR(255)
    );

    ALTER TABLE client_data
       ADD CONSTRAINT client_user_data
       FOREIGN KEY (user_data_id)
       REFERENCES user_data ;

    ALTER TABLE guide_data
       ADD CONSTRAINT guide_user_data
       FOREIGN KEY (user_data_id)
       REFERENCES user_data ;

    ALTER TABLE reservation
       ADD CONSTRAINT reservation_client_data
       FOREIGN KEY (client_id)
       REFERENCES client_data ;

    ALTER TABLE reservation
       ADD CONSTRAINT reservation_tour
       FOREIGN KEY (tour_id)
       REFERENCES tour;

    ALTER TABLE reservation_offer
       ADD CONSTRAINT offer_client_data
       FOREIGN KEY (client_data_id)
       REFERENCES client_data ;

    ALTER TABLE reservation_offer
       ADD CONSTRAINT offer_tour
       FOREIGN KEY (tour_id)
       REFERENCES tour;

    ALTER TABLE tour
       ADD CONSTRAINT tour_destination
       FOREIGN KEY (destination_id)
       REFERENCES destination;

    ALTER TABLE tour
       ADD CONSTRAINT tour_guide_data
       FOREIGN KEY (guide_id)
       REFERENCES guide_data;

    ALTER TABLE user_data_roles
       ADD CONSTRAINT roles_user_data
       FOREIGN KEY (user_data_id)
       REFERENCES user_data;