1 | -- DROP TYPE project."offer_status";
|
---|
2 |
|
---|
3 | CREATE TYPE project."offer_status" AS ENUM (
|
---|
4 | 'IN PROGRESS',
|
---|
5 | 'WAITING FOR PAYMENT',
|
---|
6 | 'COMPLETED',
|
---|
7 | 'DECLINED');
|
---|
8 |
|
---|
9 | -- DROP TYPE project."user_type";
|
---|
10 |
|
---|
11 | CREATE TYPE project."user_type" AS ENUM (
|
---|
12 | 'ORGANIZER',
|
---|
13 | 'ARTIST',
|
---|
14 | 'MANAGER');
|
---|
15 |
|
---|
16 | -- project.artist_types definition
|
---|
17 |
|
---|
18 | -- Drop table
|
---|
19 |
|
---|
20 | -- DROP TABLE project.artist_types;
|
---|
21 |
|
---|
22 | CREATE TABLE project.artist_types (
|
---|
23 | id serial4 NOT NULL,
|
---|
24 | "name" varchar NOT NULL,
|
---|
25 | CONSTRAINT artist_types_pk PRIMARY KEY (id)
|
---|
26 | );
|
---|
27 | CREATE UNIQUE INDEX artist_types_id_uindex ON project.artist_types USING btree (id);
|
---|
28 |
|
---|
29 |
|
---|
30 | -- project.event_types definition
|
---|
31 |
|
---|
32 | -- Drop table
|
---|
33 |
|
---|
34 | -- DROP TABLE project.event_types;
|
---|
35 |
|
---|
36 | CREATE TABLE project.event_types (
|
---|
37 | id serial4 NOT NULL,
|
---|
38 | "name" varchar NOT NULL,
|
---|
39 | CONSTRAINT event_types_pk PRIMARY KEY (id)
|
---|
40 | );
|
---|
41 | CREATE UNIQUE INDEX event_types_id_uindex ON project.event_types USING btree (id);
|
---|
42 |
|
---|
43 |
|
---|
44 | -- project.genres definition
|
---|
45 |
|
---|
46 | -- Drop table
|
---|
47 |
|
---|
48 | -- DROP TABLE project.genres;
|
---|
49 |
|
---|
50 | CREATE TABLE project.genres (
|
---|
51 | id serial4 NOT NULL,
|
---|
52 | "name" varchar NOT NULL,
|
---|
53 | CONSTRAINT genres_pk PRIMARY KEY (id)
|
---|
54 | );
|
---|
55 | CREATE UNIQUE INDEX genres_id_uindex ON project.genres USING btree (id);
|
---|
56 |
|
---|
57 |
|
---|
58 | -- project.password_resets definition
|
---|
59 |
|
---|
60 | -- Drop table
|
---|
61 |
|
---|
62 | -- DROP TABLE project.password_resets;
|
---|
63 |
|
---|
64 | CREATE TABLE project.password_resets (
|
---|
65 | email varchar(255) NOT NULL,
|
---|
66 | "token" varchar(255) NOT NULL,
|
---|
67 | created_at timestamp(0) NULL
|
---|
68 | );
|
---|
69 | CREATE INDEX password_resets_email_index ON project.password_resets USING btree (email);
|
---|
70 |
|
---|
71 |
|
---|
72 | -- project.users definition
|
---|
73 |
|
---|
74 | -- Drop table
|
---|
75 |
|
---|
76 | -- DROP TABLE project.users;
|
---|
77 |
|
---|
78 | CREATE TABLE project.users (
|
---|
79 | id serial4 NOT NULL,
|
---|
80 | email varchar NOT NULL,
|
---|
81 | username varchar(100) NOT NULL,
|
---|
82 | "password" varchar NOT NULL,
|
---|
83 | "name" varchar(80) NOT NULL,
|
---|
84 | created_at timestamp NOT NULL,
|
---|
85 | updated_at timestamp NULL,
|
---|
86 | deleted_at timestamp NULL,
|
---|
87 | email_verified_at timestamp NULL,
|
---|
88 | profile_picture varchar NOT NULL,
|
---|
89 | last_login_at timestamp NULL,
|
---|
90 | last_login_ip varchar(45) NULL,
|
---|
91 | user_agent varchar NULL,
|
---|
92 | "type" project."user_type" NOT NULL,
|
---|
93 | remember_token varchar(100) NULL,
|
---|
94 | stripe_id varchar NULL,
|
---|
95 | pm_type varchar NULL,
|
---|
96 | pm_last_four bpchar(4) NULL,
|
---|
97 | trial_ends_at timestamp NULL,
|
---|
98 | CONSTRAINT users_pk PRIMARY KEY (id)
|
---|
99 | );
|
---|
100 | CREATE UNIQUE INDEX users_email_uindex ON project.users USING btree (email);
|
---|
101 | CREATE UNIQUE INDEX users_id_uindex ON project.users USING btree (id);
|
---|
102 | CREATE UNIQUE INDEX users_username_uindex ON project.users USING btree (username);
|
---|
103 |
|
---|
104 |
|
---|
105 | -- project.managers definition
|
---|
106 |
|
---|
107 | -- Drop table
|
---|
108 |
|
---|
109 | -- DROP TABLE project.managers;
|
---|
110 |
|
---|
111 | CREATE TABLE project.managers (
|
---|
112 | user_id int4 NOT NULL,
|
---|
113 | CONSTRAINT managers_pk PRIMARY KEY (user_id),
|
---|
114 | CONSTRAINT managers_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
|
---|
115 | );
|
---|
116 |
|
---|
117 |
|
---|
118 | -- project.organizers definition
|
---|
119 |
|
---|
120 | -- Drop table
|
---|
121 |
|
---|
122 | -- DROP TABLE project.organizers;
|
---|
123 |
|
---|
124 | CREATE TABLE project.organizers (
|
---|
125 | user_id int4 NOT NULL,
|
---|
126 | city varchar(85) NULL,
|
---|
127 | country varchar(56) NULL,
|
---|
128 | CONSTRAINT organizers_pk PRIMARY KEY (user_id),
|
---|
129 | CONSTRAINT organizers_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
|
---|
130 | );
|
---|
131 | CREATE UNIQUE INDEX organizers_user_id_uindex ON project.organizers USING btree (user_id);
|
---|
132 |
|
---|
133 |
|
---|
134 | -- project.artists definition
|
---|
135 |
|
---|
136 | -- Drop table
|
---|
137 |
|
---|
138 | -- DROP TABLE project.artists;
|
---|
139 |
|
---|
140 | CREATE TABLE project.artists (
|
---|
141 | user_id int4 NOT NULL,
|
---|
142 | birth_date date NULL,
|
---|
143 | city varchar(85) NULL,
|
---|
144 | country varchar(56) NULL,
|
---|
145 | short_description text NULL,
|
---|
146 | price_per_hour float4 NULL,
|
---|
147 | instagram_link varchar NULL,
|
---|
148 | soundcloud_link varchar NULL,
|
---|
149 | apple_music_link varchar NULL,
|
---|
150 | youtube_link varchar NULL,
|
---|
151 | spotify_link varchar NULL,
|
---|
152 | manager_id int4 NULL,
|
---|
153 | artist_type_id int4 NULL,
|
---|
154 | admin_verified_at timestamp NULL,
|
---|
155 | CONSTRAINT artists_pk PRIMARY KEY (user_id),
|
---|
156 | CONSTRAINT artists_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
---|
157 | CONSTRAINT artists_fk_manager FOREIGN KEY (manager_id) REFERENCES project.managers(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
|
---|
158 | CONSTRAINT artists_fk_type FOREIGN KEY (artist_type_id) REFERENCES project.artist_types(id) ON DELETE RESTRICT ON UPDATE CASCADE
|
---|
159 | );
|
---|
160 | CREATE UNIQUE INDEX artists_user_id_uindex ON project.artists USING btree (user_id);
|
---|
161 |
|
---|
162 |
|
---|
163 | -- project.events definition
|
---|
164 |
|
---|
165 | -- Drop table
|
---|
166 |
|
---|
167 | -- DROP TABLE project.events;
|
---|
168 |
|
---|
169 | CREATE TABLE project.events (
|
---|
170 | id serial4 NOT NULL,
|
---|
171 | title varchar(100) NOT NULL,
|
---|
172 | slug varchar(150) NOT NULL,
|
---|
173 | start_time time NOT NULL,
|
---|
174 | end_time time NOT NULL,
|
---|
175 | event_date date NOT NULL,
|
---|
176 | city varchar(85) NOT NULL,
|
---|
177 | country varchar(56) NOT NULL,
|
---|
178 | description text NULL,
|
---|
179 | event_type_id int4 NOT NULL,
|
---|
180 | updated_at timestamp NULL,
|
---|
181 | created_at timestamp NOT NULL,
|
---|
182 | organizer_id int4 NOT NULL,
|
---|
183 | CONSTRAINT events_pk PRIMARY KEY (id),
|
---|
184 | CONSTRAINT events_fk_event_type FOREIGN KEY (event_type_id) REFERENCES project.event_types(id) ON DELETE RESTRICT ON UPDATE CASCADE,
|
---|
185 | CONSTRAINT events_fk_organizer FOREIGN KEY (organizer_id) REFERENCES project.organizers(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT
|
---|
186 | );
|
---|
187 | CREATE UNIQUE INDEX events_id_uindex ON project.events USING btree (id);
|
---|
188 | CREATE UNIQUE INDEX events_slug_uindex ON project.events USING btree (slug);
|
---|
189 |
|
---|
190 |
|
---|
191 | -- project.manager_invites definition
|
---|
192 |
|
---|
193 | -- Drop table
|
---|
194 |
|
---|
195 | -- DROP TABLE project.manager_invites;
|
---|
196 |
|
---|
197 | CREATE TABLE project.manager_invites (
|
---|
198 | id serial4 NOT NULL,
|
---|
199 | email varchar NOT NULL,
|
---|
200 | invitation_token varchar NOT NULL,
|
---|
201 | created_at timestamp NOT NULL,
|
---|
202 | updated_at timestamp NULL,
|
---|
203 | registered_at timestamp NULL,
|
---|
204 | artist_id int4 NOT NULL,
|
---|
205 | CONSTRAINT manager_invites_pk PRIMARY KEY (id),
|
---|
206 | CONSTRAINT manager_invites_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT
|
---|
207 | );
|
---|
208 | CREATE UNIQUE INDEX manager_invites_id_uindex ON project.manager_invites USING btree (id);
|
---|
209 |
|
---|
210 |
|
---|
211 | -- project.offers definition
|
---|
212 |
|
---|
213 | -- Drop table
|
---|
214 |
|
---|
215 | -- DROP TABLE project.offers;
|
---|
216 |
|
---|
217 | CREATE TABLE project.offers (
|
---|
218 | id serial4 NOT NULL,
|
---|
219 | slug varchar(150) NOT NULL,
|
---|
220 | payment_type int4 NOT NULL,
|
---|
221 | created_at timestamp NOT NULL,
|
---|
222 | updated_at timestamp NULL,
|
---|
223 | completed_at timestamp NULL,
|
---|
224 | artist_id int4 NOT NULL,
|
---|
225 | event_id int4 NOT NULL,
|
---|
226 | price float4 NULL,
|
---|
227 | status project."offer_status" NOT NULL,
|
---|
228 | CONSTRAINT offers_pk PRIMARY KEY (id),
|
---|
229 | CONSTRAINT offers_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
---|
230 | CONSTRAINT offers_fk_event FOREIGN KEY (event_id) REFERENCES project.events(id) ON DELETE RESTRICT ON UPDATE RESTRICT
|
---|
231 | );
|
---|
232 | CREATE UNIQUE INDEX offers_id_uindex ON project.offers USING btree (id);
|
---|
233 | CREATE UNIQUE INDEX offers_slug_uindex ON project.offers USING btree (slug);
|
---|
234 |
|
---|
235 |
|
---|
236 | -- project.reviews definition
|
---|
237 |
|
---|
238 | -- Drop table
|
---|
239 |
|
---|
240 | -- DROP TABLE project.reviews;
|
---|
241 |
|
---|
242 | CREATE TABLE project.reviews (
|
---|
243 | id serial4 NOT NULL,
|
---|
244 | rating int4 NOT NULL,
|
---|
245 | "content" text NOT NULL,
|
---|
246 | created_at timestamp NOT NULL DEFAULT now(),
|
---|
247 | organizer_id int4 NOT NULL,
|
---|
248 | artist_id int4 NOT NULL,
|
---|
249 | CONSTRAINT reviews_pk PRIMARY KEY (id),
|
---|
250 | CONSTRAINT reviews_fk_artists FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
|
---|
251 | CONSTRAINT reviews_fk_organizers FOREIGN KEY (organizer_id) REFERENCES project.organizers(user_id) ON DELETE CASCADE ON UPDATE CASCADE
|
---|
252 | );
|
---|
253 | CREATE UNIQUE INDEX reviews_id_uindex ON project.reviews USING btree (id);
|
---|
254 |
|
---|
255 |
|
---|
256 | -- project.transactions definition
|
---|
257 |
|
---|
258 | -- Drop table
|
---|
259 |
|
---|
260 | -- DROP TABLE project.transactions;
|
---|
261 |
|
---|
262 | CREATE TABLE project.transactions (
|
---|
263 | id serial4 NOT NULL,
|
---|
264 | "name" varchar NOT NULL,
|
---|
265 | stripe_id varchar NOT NULL,
|
---|
266 | stripe_price float4 NULL,
|
---|
267 | created_at timestamp NOT NULL,
|
---|
268 | updated_at timestamp NULL,
|
---|
269 | offer_id int4 NOT NULL,
|
---|
270 | invoice_id varchar NULL,
|
---|
271 | CONSTRAINT transactions_pk PRIMARY KEY (id),
|
---|
272 | CONSTRAINT transactions_fk_offer FOREIGN KEY (offer_id) REFERENCES project.offers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
|
---|
273 | );
|
---|
274 | CREATE UNIQUE INDEX transactions_id_uindex ON project.transactions USING btree (id);
|
---|
275 | CREATE UNIQUE INDEX transactions_name_uindex ON project.transactions USING btree (name);
|
---|
276 |
|
---|
277 |
|
---|
278 | -- project.artist_images definition
|
---|
279 |
|
---|
280 | -- Drop table
|
---|
281 |
|
---|
282 | -- DROP TABLE project.artist_images;
|
---|
283 |
|
---|
284 | CREATE TABLE project.artist_images (
|
---|
285 | id serial4 NOT NULL,
|
---|
286 | "path" varchar NOT NULL,
|
---|
287 | created_at timestamp NOT NULL,
|
---|
288 | updated_at timestamp NULL,
|
---|
289 | artist_id int4 NOT NULL,
|
---|
290 | CONSTRAINT images_pk PRIMARY KEY (id),
|
---|
291 | CONSTRAINT artist_images_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE
|
---|
292 | );
|
---|
293 | CREATE UNIQUE INDEX images_id_uindex ON project.artist_images USING btree (id);
|
---|
294 |
|
---|
295 |
|
---|
296 | -- project.artist_sings_genres definition
|
---|
297 |
|
---|
298 | -- Drop table
|
---|
299 |
|
---|
300 | -- DROP TABLE project.artist_sings_genres;
|
---|
301 |
|
---|
302 | CREATE TABLE project.artist_sings_genres (
|
---|
303 | artist_id int4 NOT NULL,
|
---|
304 | genre_id int4 NOT NULL,
|
---|
305 | CONSTRAINT artist_sings_genres_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
|
---|
306 | CONSTRAINT artist_sings_genres_fk_genre FOREIGN KEY (genre_id) REFERENCES project.genres(id) ON DELETE RESTRICT ON UPDATE RESTRICT
|
---|
307 | );
|
---|
308 |
|
---|
309 |
|
---|
310 | -- project.offer_comments definition
|
---|
311 |
|
---|
312 | -- Drop table
|
---|
313 |
|
---|
314 | -- DROP TABLE project.offer_comments;
|
---|
315 |
|
---|
316 | CREATE TABLE project.offer_comments (
|
---|
317 | id serial4 NOT NULL,
|
---|
318 | "content" text NOT NULL,
|
---|
319 | created_at timestamp NOT NULL,
|
---|
320 | updated_at timestamp NULL,
|
---|
321 | author_id int4 NOT NULL,
|
---|
322 | offer_id int4 NOT NULL,
|
---|
323 | CONSTRAINT offer_comments_pk PRIMARY KEY (id),
|
---|
324 | CONSTRAINT offer_comments_fk_author FOREIGN KEY (author_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
---|
325 | CONSTRAINT offer_comments_fk_offer FOREIGN KEY (offer_id) REFERENCES project.offers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
|
---|
326 | );
|
---|
327 | CREATE UNIQUE INDEX offer_comments_id_uindex ON project.offer_comments USING btree (id); |
---|