mdsBV1tj

1/* users */
2DROP TABLE IF EXISTS users CASCADE;
3
4CREATE TABLE users(
5    id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
6    coordinates POINT,
7    avatar VARCHAR(128),
8    gender CHAR(1),
9    username VARCHAR(64) NOT NULL UNIQUE,
10    firstname VARCHAR(64),
11    lastname VARCHAR(64),
12    password VARCHAR(128) NOT NULL,
13    facebook VARCHAR(64),
14    instagram VARCHAR(64),
15    twitter VARCHAR(64),
16    google VARCHAR(64),
17    email VARCHAR(128) NOT NULL UNIQUE,
18    is_visible boolean NOT NULL DEFAULT true,
19    allow_collections boolean NOT NULL DEFAULT true,
20    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
21);
22
23DROP TABLE IF EXISTS addresses CASCADE;
24
25CREATE TABLE addresses(
26    user_id INT,
27    street_type VARCHAR(8),
28    address_line_1 VARCHAR(64),
29    address_line_2 VARCHAR(64),
30    zip_code VARCHAR(10),
31    city VARCHAR(64),
32    country VARCHAR(64),
33    PRIMARY KEY(user_id),
34    FOREIGN KEY(user_id) REFERENCES users(id)
35);
36
37/* deck parts */
38DROP TABLE IF EXISTS edens CASCADE;
39
40CREATE TABLE edens(
41    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
42    qty INT
43);
44
45DROP TABLE IF EXISTS holy_books CASCADE;
46
47CREATE TABLE holy_books(
48    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
49    qty INT
50);
51
52DROP TABLE IF EXISTS registers CASCADE;
53
54CREATE TABLE registers(
55    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
56    qty INT
57);
58
59/*decks*/
60DROP TABLE IF EXISTS cards_decks CASCADE;
61
62CREATE TABLE cards_decks(
63    user_id INT,
64    eden_id INT,
65    holy_book_id INT,
66    register_id INT,
67    is_visible boolean NOT NULL DEFAULT true,
68    PRIMARY KEY (user_id, eden_id, holy_book_id, register_id),
69    FOREIGN KEY (user_id) REFERENCES users(id),
70    FOREIGN KEY (eden_id) REFERENCES edens(id),
71    FOREIGN KEY (holy_book_id) REFERENCES holy_books(id),
72    FOREIGN KEY (register_id) REFERENCES registers(id)
73);
74
75/*languages*/
76DROP TABLE IF EXISTS languages CASCADE;
77
78CREATE TABLE languages(
79    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
80    name VARCHAR(64)
81);
82
83/* kingdom, type, capacities, classes*/
84DROP TABLE IF EXISTS kingdoms CASCADE;
85
86CREATE TABLE kingdoms(
87    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
88    icon VARCHAR(128)
89);
90
91DROP TABLE IF EXISTS types CASCADE;
92
93CREATE TABLE types(
94    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
95);
96
97DROP TABLE IF EXISTS capacities CASCADE;
98
99CREATE TABLE capacities(
100    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
101);
102
103DROP TABLE IF EXISTS classes CASCADE;
104
105CREATE TABLE classes(
106    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
107);
108
109/* translations associations */
110DROP TABLE IF EXISTS kingdoms_translations CASCADE;
111
112CREATE TABLE kingdoms_translations(
113    kingdom_id INT,
114    language_id INT,
115    PRIMARY KEY (kingdom_id, language_id),
116    FOREIGN KEY (kingdom_id) REFERENCES kingdoms(id),
117    FOREIGN KEY (language_id) REFERENCES languages(id)
118);
119
120DROP TABLE IF EXISTS types_translations CASCADE;
121
122CREATE TABLE types_translations(
123    type_id INT,
124    language_id INT,
125    PRIMARY KEY (type_id, language_id),
126    FOREIGN KEY (type_id) REFERENCES types(id),
127    FOREIGN KEY (language_id) REFERENCES languages(id)
128);
129
130DROP TABLE IF EXISTS capacities_translations CASCADE;
131
132CREATE TABLE capacities_translations(
133    capacity_id INT,
134    language_id INT,
135    PRIMARY KEY (capacity_id, language_id),
136    FOREIGN KEY (capacity_id) REFERENCES capacities(id),
137    FOREIGN KEY (language_id) REFERENCES languages(id)
138);
139
140DROP TABLE IF EXISTS classes_translations CASCADE;
141
142CREATE TABLE classes_translations(
143    class_id INT,
144    language_id INT,
145    PRIMARY KEY (class_id, language_id),
146    FOREIGN KEY (class_id) REFERENCES classes(id),
147    FOREIGN KEY (language_id) REFERENCES languages(id)
148);
149
150/*cards*/
151DROP TABLE IF EXISTS cards CASCADE;
152
153CREATE TABLE cards(
154    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
155    kingdom_id INT,
156    type_id INT,
157    rareness_id INT,
158    atq INT,
159    fv INT,
160    ec INT,
161    bou INT,
162    storm INT,
163    special_capacity TEXT,
164    is_legendary boolean,
165    ado_number INT,
166    ado_to_revoke INT,
167    feathers INT,
168    image VARCHAR(128),
169    divinity_will INT,
170    time_factor INT,
171    ext VARCHAR(64),
172    artist VARCHAR(128),
173    qty_max INT,
174    FOREIGN KEY(kingdom_id) REFERENCES kingdoms(id),
175    FOREIGN KEY(type_id) REFERENCES types(id)
176);
177
178/* deck part association */
179DROP TABLE IF EXISTS edens_cards CASCADE;
180
181CREATE TABLE edens_cards(
182    eden_id INT,
183    card_id INT,
184    FOREIGN KEY (eden_id) REFERENCES edens(id),
185    FOREIGN KEY (card_id) REFERENCES cards(id),
186    PRIMARY KEY (eden_id, card_id)
187);
188
189DROP TABLE IF EXISTS holy_books_cards CASCADE;
190
191CREATE TABLE holy_books_cards (
192    holy_book_id INT,
193    card_id INT,
194    FOREIGN KEY (holy_book_id) REFERENCES holy_books(id),
195    FOREIGN KEY (card_id) REFERENCES cards(id),
196    PRIMARY KEY (holy_book_id, card_id)
197);
198
199DROP TABLE IF EXISTS registers_cards CASCADE;
200
201CREATE TABLE registers_cards(
202    register_id INT,
203    card_id INT,
204    FOREIGN KEY (register_id) REFERENCES registers(id),
205    FOREIGN KEY (card_id) REFERENCES cards(id),
206    PRIMARY KEY (register_id, card_id)
207);
208
209/*cards trandslations*/
210DROP TABLE IF EXISTS cards_translations CASCADE;
211
212CREATE TABLE cards_translations(
213    card_id INT,
214    language_id INT,
215    PRIMARY KEY (card_id, language_id),
216    FOREIGN KEY (card_id) REFERENCES cards(id),
217    FOREIGN KEY (language_id) REFERENCES languages(id)
218);
219
220/* capacities association */
221DROP TABLE IF EXISTS cards_capacities;
222
223CREATE TABLE cards_capacities(
224    card_id INT,
225    capacity_id INT,
226    PRIMARY KEY(card_id, capacity_id),
227    FOREIGN KEY(card_id) REFERENCES cards(id),
228    FOREIGN KEY(capacity_id) REFERENCES capacities(id)
229);
230
231/* classes association */
232DROP TABLE IF EXISTS cards_classes;
233
234CREATE TABLE cards_classes(
235    card_id INT,
236    class_id INT,
237    PRIMARY KEY(card_id, class_id),
238    FOREIGN KEY(card_id) REFERENCES cards(id),
239    FOREIGN KEY(class_id) REFERENCES classes(id)
240);