create type priority as enum ('low', 'normal', 'high', 'critical', 'blocker');
create type status as enum ('open', 'in progress', 'testing', 'close');
create type connection_type as enum ('blocking', 'depending', 'parent');

drop table users;
drop table tickets;
drop table connections;
drop table comments;
drop table commits;
drop table observers;


create table if not exists users (
    id      uuid primary key,
    login   varchar(20),
    name    varchar(30),
    surname varchar(30),
    email   varchar(40),
    phone   varchar(12),
    team    varchar(20)
);

create table if not exists tickets (
    id              int primary key,
    author_id       uuid,
    resolver_id     uuid,
    title           varchar(100),
    description     text,
    priority        priority,
    status          status,
    creation_time   timestamp,

    constraint user_author_fk foreign key (author_id) references users (id),
    constraint user_resolver_fk foreign key (resolver_id) references users (id)
);

create table if not exists connections (
    from_ticket_id  int,
    to_ticket_id    int,
    type            connection_type,

    constraint ticker_from_fk foreign key (from_ticket_id) references tickets (id),
    constraint ticker_to_fk foreign key (to_ticket_id) references tickets (id)
);

create table if not exists commits (
    id          int,
    ticket_id   int,
    linked_time timestamp,

    constraint to_ticket_fk foreign key (ticket_id) references tickets (id)
);

create table if not exists comments (
    id              int,
    ticket_id       int,
    user_id         uuid,
    answer_to       int,
    creation_time   timestamp,

    constraint ticket_fk foreign key (ticket_id) references tickets (id),
    constraint user_id foreign key (user_id) references users (id)
);

create table if not exists observers (
    ticket_id   int,
    user_id     uuid,

    constraint to_ticket_fk foreign key (ticket_id) references tickets (id),
    constraint user_id foreign key (user_id) references users (id)
)