FreelanceBot

1
DROP SCHEMA PUBLIC CASCADE;
2
CREATE SCHEMA PUBLIC;
3

4
CREATE TABLE IF NOT EXISTS Exchange(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
5
                      exchange_name character varying(20), exchange_link text);
6

7
INSERT INTO Exchange (exchange_name, exchange_link) VALUES ('HabrFreelance', 'https://freelance.habr.com');
8
INSERT INTO Exchange (exchange_name, exchange_link) VALUES ('Fl.ru', 'https://www.fl.ru');
9
INSERT INTO Exchange (exchange_name, exchange_link) VALUES ('Kwork', 'https://kwork.ru');
10

11
CREATE INDEX IF NOT EXISTS exchange_name_index ON Exchange(exchange_name);
12

13
CREATE TABLE IF NOT EXISTS Subscription(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
14
                          lang character varying(20), status character varying(6));
15

16
INSERT INTO Subscription (lang, status) VALUES ('JavaScript', 'CREATE');
17
INSERT INTO Subscription (lang, status) VALUES ('Python', 'CREATE');
18
INSERT INTO Subscription (lang, status) VALUES ('Java', 'CREATE');
19
INSERT INTO Subscription (lang, status) VALUES ('Ruby', 'CREATE');
20
INSERT INTO Subscription (lang, status) VALUES ('PHP', 'CREATE');
21
INSERT INTO Subscription (lang, status) VALUES ('C', 'CREATE');
22

23
CREATE INDEX IF NOT EXISTS lang_index ON Subscription(lang);
24

25
CREATE TABLE IF NOT EXISTS Orders(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
26
                    order_title text, order_link text, init_date date,
27
                    exchange_id int, subscription_id int,
28
                    FOREIGN KEY (exchange_id) REFERENCES Exchange(id),
29
                    FOREIGN KEY (subscription_id) REFERENCES Subscription(id));
30

31
CREATE INDEX IF NOT EXISTS order_link_index ON Orders(order_link);
32

33
CREATE TABLE IF NOT EXISTS Users(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
34
                   chat_id character varying(30), active boolean, user_role character varying (5));
35

36
CREATE TABLE IF NOT EXISTS Users_subscriptions(user_id int, subscription_id int, PRIMARY KEY(user_id, subscription_id),
37
                                 CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES Users(id),
38
                                 CONSTRAINT fk_subscription FOREIGN KEY(subscription_id) REFERENCES Subscription(id));
39