CREATE TABLE IF NOT EXISTS addresses (
    id                     UUID              NOT NULL PRIMARY KEY,
    dadata_value           VARCHAR(250)      NOT NULL,
    fias                   VARCHAR(37)       NOT NULL
);

CREATE TABLE IF NOT EXISTS codes (
    id                     UUID              NOT NULL PRIMARY KEY,
    address                UUID              NOT NULL REFERENCES addresses(id),
    entrance               VARCHAR(10)       NULL,
    code                   VARCHAR(10)       NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
    id                     UUID              NOT NULL PRIMARY KEY,
    telegram_chat_id       BIGINT            NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS users_telegram_chat_id ON users (telegram_chat_id);

CREATE TABLE IF NOT EXISTS code_ratings (
    code_id                UUID              NOT NULL PRIMARY KEY,
    rating                 INT               NOT NULL
);

CREATE TABLE IF NOT EXISTS user_code_ratings (
    code_id                UUID              NOT NULL,
    user_id                UUID              NOT NULL,
    rating                 INT               NOT NULL,

    PRIMARY KEY (code_id, user_id)
);

CREATE TABLE IF NOT EXISTS user_codes (
    code_id                UUID              NOT NULL,
    user_id                UUID              NOT NULL,

    PRIMARY KEY (code_id, user_id)
);