3MrA8un6

1create type priority as enum ('low', 'normal', 'high', 'critical', 'blocker');
2create type status as enum ('open', 'in progress', 'testing', 'close');
3create type connection_type as enum ('blocking', 'depending', 'parent');
4
5drop table users;
6drop table tickets;
7drop table connections;
8drop table comments;
9drop table commits;
10drop table observers;
11
12
13create table if not exists users (
14    id      uuid primary key,
15    login   varchar(20),
16    name    varchar(30),
17    surname varchar(30),
18    email   varchar(40),
19    phone   varchar(12),
20    team    varchar(20)
21);
22
23create table if not exists tickets (
24    id              int primary key,
25    author_id       uuid,
26    resolver_id     uuid,
27    title           varchar(100),
28    description     text,
29    priority        priority,
30    status          status,
31    creation_time   timestamp,
32
33    constraint user_author_fk foreign key (author_id) references users (id),
34    constraint user_resolver_fk foreign key (resolver_id) references users (id)
35);
36
37create table if not exists connections (
38    from_ticket_id  int,
39    to_ticket_id    int,
40    type            connection_type,
41
42    constraint ticker_from_fk foreign key (from_ticket_id) references tickets (id),
43    constraint ticker_to_fk foreign key (to_ticket_id) references tickets (id)
44);
45
46create table if not exists commits (
47    id          int,
48    ticket_id   int,
49    linked_time timestamp,
50
51    constraint to_ticket_fk foreign key (ticket_id) references tickets (id)
52);
53
54create table if not exists comments (
55    id              int,
56    ticket_id       int,
57    user_id         uuid,
58    answer_to       int,
59    creation_time   timestamp,
60
61    constraint ticket_fk foreign key (ticket_id) references tickets (id),
62    constraint user_id foreign key (user_id) references users (id)
63);
64
65create table if not exists observers (
66    ticket_id   int,
67    user_id     uuid,
68
69    constraint to_ticket_fk foreign key (ticket_id) references tickets (id),
70    constraint user_id foreign key (user_id) references users (id)
71)