9xJyRjcp

1CREATE EXTENSION IF NOT EXISTS CITEXT;
2SET TIMEZONE TO 'Europe/Moscow';
3
4CREATE TABLE IF NOT EXISTS users (
5  id       SERIAL PRIMARY KEY,
6  is_admin BOOLEAN NOT NULL DEFAULT FALSE,
7  login    CITEXT  NOT NULL UNIQUE,
8  password CITEXT  NOT NULL,
9  fullname CITEXT  NOT NULL,
10  bdate    DATE    NOT NULL
11);
12
13CREATE TABLE IF NOT EXISTS feedbacks (
14  id      SERIAL PRIMARY KEY,
15  author  INT4   NOT NULL REFERENCES users (id),
16  rating  INT    NOT NULL DEFAULT 5,
17  comment CITEXT NOT NULL
18);
19
20CREATE TABLE IF NOT EXISTS countries (
21  id    SERIAL PRIMARY KEY,
22  title CITEXT NOT NULL UNIQUE,
23  city  CITEXT NOT NULL UNIQUE
24);
25
26CREATE TABLE IF NOT EXISTS airports (
27  id      SERIAL PRIMARY KEY,
28  closed  BOOLEAN NOT NULL DEFAULT FALSE,
29  title   CITEXT  NOT NULL UNIQUE,
30  country INT4 REFERENCES countries (id),
31  code    CITEXT  NOT NULL UNIQUE
32);
33
34CREATE TABLE IF NOT EXISTS hotels (
35  id           SERIAL PRIMARY KEY,
36  title        CITEXT NOT NULL,
37  airport      INT4 REFERENCES airports (id),
38  price        INT    NOT NULL,
39  stars        INT    NOT NULL DEFAULT 0,
40  vacant_place INT    NOT NULL DEFAULT 0
41);
42
43CREATE TABLE IF NOT EXISTS companies (
44  id      SERIAL PRIMARY KEY,
45  title   CITEXT NOT NULL UNIQUE,
46  country INT4   NOT NULL REFERENCES countries (id)
47);
48
49CREATE TABLE IF NOT EXISTS pilots (
50  id            SERIAL PRIMARY KEY,
51  fullname      CITEXT NOT NULL,
52  bdate         DATE   NOT NULL,
53  country       INT4   NOT NULL REFERENCES countries (id),
54  total_flights INT    NOT NULL DEFAULT 0
55);
56
57CREATE TABLE IF NOT EXISTS payments (
58  id           SERIAL PRIMARY KEY,
59  payee        INT4        NOT NULL REFERENCES pilots (id),
60  payment_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
61  amount       INT         NOT NULL
62);
63
64CREATE TABLE IF NOT EXISTS planes (
65  id            SERIAL PRIMARY KEY,
66  created       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
67  total_flights INT         NOT NULL DEFAULT 0,
68  company       INT4        NOT NULL REFERENCES companies (id),
69  model         CITEXT      NOT NULL UNIQUE
70);
71
72CREATE TABLE IF NOT EXISTS tickets (
73  id           SERIAL PRIMARY KEY,
74  sold         BOOLEAN     NOT NULL              DEFAULT FALSE,
75  buyer        INT4 REFERENCES users (id),
76  pilot        INT4        NOT NULL REFERENCES pilots (id),
77  from_airport INT4        NOT NULL REFERENCES airports (id),
78  to_airport   INT4        NOT NULL REFERENCES airports (id),
79  flight_date  TIMESTAMPTZ NOT NULL              DEFAULT NOW(),
80  gate         INT         NOT NULL,
81  seat         CITEXT      NOT NULL,
82  plane        INT4        NOT NULL REFERENCES planes (id)
83);