ig6A0uGv

1DROP TABLE IF EXISTS Jf_trainings;
2DROP TABLE IF EXISTS JF_Clients;
3DROP TABLE IF EXISTS JF_payments;
4DROP TABLE IF EXISTS JF_Trainers;
5DROP TABLE IF EXISTS JF_products;
6DROP TABLE IF EXISTS JF_purchase;
7DROP TABLE IF EXISTS JF_discounts;
8DROP INDEX IF EXISTS JF_purchase_fk3;
9DROP INDEX IF EXISTS JF_payments_fk0;
10DROP TABLE IF EXISTS JF_duty_plan_type;
11DROP TABLE IF EXISTS JF_duty_shedule;
12DROP TABLE IF EXISTS JF_training_plan;
13DROP TABLE IF EXISTS JF_exercises;
14DROP TABLE IF EXISTS JF_user_roles;
15DROP TABLE IF EXISTS JF_roles;
16DROP TABLE IF EXISTS JF_user;
17
18
19CREATE TABLE JF_Trainers (
20  trainer_id   BIGSERIAL    NOT NULL PRIMARY KEY,
21  trainer_name VARCHAR(100) NOT NULL
22);
23
24CREATE TABLE JF_clients
25(
26  client_id  BIGSERIAL PRIMARY KEY NOT NULL,
27  surname    VARCHAR(100)          NOT NULL,
28  phone      INT                   NOT NULL,
29  email      VARCHAR(100)          NOT NULL,
30  birth_date DATE,
31  name       VARCHAR(100)          NOT NULL
32);
33
34CREATE TABLE JF_products
35(
36  product_id   BIGSERIAL PRIMARY KEY NOT NULL,
37  product_name VARCHAR(100)          NOT NULL,
38  expire_days  INT                   NOT NULL,
39  price        FLOAT8                NOT NULL
40);
41
42
43CREATE TABLE JF_discounts
44(
45  discount_id     BIGSERIAL PRIMARY KEY NOT NULL,
46  discount_amount BIGINT                NOT NULL
47);
48
49CREATE TABLE JF_purchase
50(
51  purchase_id   BIGSERIAL PRIMARY KEY NOT NULL,
52  is_provided   BOOLEAN,
53  client_id     BIGINT                NOT NULL,
54  purchase_date DATE                  NOT NULL,
55  product_id    BIGINT                NOT NULL,
56  trainer_id    BIGINT                NOT NULL,
57  expired       BOOLEAN,
58  discount_id   BIGINT,
59  CONSTRAINT JF_purchase_fk3 FOREIGN KEY (discount_id) REFERENCES JF_discounts (discount_id)
60);
61CREATE INDEX JF_purchase_fk3
62  ON JF_purchase (discount_id);
63
64CREATE TABLE JF_payments
65(
66  payment_id     BIGSERIAL PRIMARY KEY NOT NULL,
67  payment_amount BIGINT                NOT NULL,
68  payment_date   TIMESTAMP             NOT NULL,
69  purchase_id    BIGINT,
70  CONSTRAINT JF_payments_fk0 FOREIGN KEY (purchase_id) REFERENCES JF_purchase (purchase_id)
71);
72CREATE INDEX JF_payments_fk0
73  ON JF_payments (purchase_id);
74
75CREATE TABLE JF_training_status (
76  status_id   BIGSERIAL PRIMARY KEY NOT NULL,
77  status_name VARCHAR(50)           NOT NULL
78);
79
80CREATE TABLE JF_trainings
81(
82  training_id   BIGSERIAL PRIMARY KEY NOT NULL,
83  training_plan BIGINT,
84  client_id     BIGINT                NOT NULL,
85  trainer_id    BIGINT                NOT NULL,
86  purchase_id   BIGINT,
87  start         TIMESTAMP             NOT NULL,
88  status_id     BIGINT                NOT NULL REFERENCES JF_training_status (status_id),
89  CONSTRAINT JF_trainings_ibfk_1 FOREIGN KEY (client_id) REFERENCES JF_clients (client_id),
90  CONSTRAINT JF_trainings_ibfk_2 FOREIGN KEY (purchase_id) REFERENCES JF_purchase (purchase_id)
91);
92
93CREATE TABLE JF_training_plan
94(
95  id_training_plan   BIGINT PRIMARY KEY NOT NULL,
96  repetitions_amount INT                NOT NULL,
97  exercise_id        BIGINT             NOT NULL
98);
99
100CREATE TABLE JF_exercises
101(
102  exercise_id BIGSERIAL PRIMARY KEY NOT NULL,
103  description VARCHAR(100)          NOT NULL,
104  name        VARCHAR(100)          NOT NULL
105);
106
107CREATE TABLE JF_duty_plan_type
108(
109  duty_plan_type_id BIGINT NOT NULL,
110  duty_plan_name    BIGINT NOT NULL
111);
112
113CREATE TABLE JF_duty_shedule
114(
115  duty_id        BIGSERIAL PRIMARY KEY NOT NULL,
116  trainer_id     BIGINT                NOT NULL,
117  datetime_start TIMESTAMP             NOT NULL,
118  datetime_end   TIMESTAMP             NOT NULL,
119  duty_plan_type BOOLEAN               NOT NULL
120);
121
122CREATE TABLE JF_roles (
123  role_id   BIGSERIAL PRIMARY KEY NOT NULL,
124  role_name VARCHAR(50)
125);
126
127
128CREATE TABLE JF_users (
129  username   VARCHAR(50) PRIMARY KEY NOT NULL,
130  password   VARCHAR(150)            NOT NULL,
131  trainer_id BIGINT REFERENCES JF_Trainers (trainer_id)
132);
133
134CREATE TABLE JF_user_roles (
135  username VARCHAR(50) NOT NULL,
136  role_id  BIGINT      NOT NULL,
137  CONSTRAINT JF_user_to_roles1 FOREIGN KEY (role_id) REFERENCES JF_roles (role_id),
138  CONSTRAINT JF_user_to_roles2 FOREIGN KEY (username) REFERENCES JF_users (username)
139)