MDqJtC2x

1DROP DATABASE IF EXISTS sports;
2CREATE DATABASE sports;
3USE sports;
4 
5DROP TABLE IF EXISTS users;
6CREATE TABLE users 
7(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
8 firstname VARCHAR(30),
9 lastname VARCHAR(30),
10 phone BIGINT UNSIGNED UNIQUE,
11 email VARCHAR(100) UNIQUE,
12 INDEX users_firstname_lastname_idx(firstname, lastname)
13 
14);
15 
16DROP TABLE IF EXISTS profiles;
17CREATE TABLE profiles (
18    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
19    gender ENUM("Мужской", "Женский"),
20    birthday DATE,
21    profile_photo BIGINT UNSIGNED NULL,
22    created_at DATETIME DEFAULT NOW(),
23    country VARCHAR(100),
24    user_info VARCHAR(1000),
25    favorite_teams BIGINT UNSIGNED,
26    favorite_persons BIGINT UNSIGNED,
27    favorite_blogs BIGINT UNSIGNED,
28    favorite_tournaments BIGINT UNSIGNED,
29    
30    FOREIGN KEY (user_id) REFERENCES users(id) 
31    
32    );
33 
34DROP TABLE IF EXISTS blogs;
35CREATE TABLE blogs(
36    id SERIAL,
37    name VARCHAR(100),
38    admin_user_id BIGINT UNSIGNED NOT NULL,
39 
40    INDEX blogs_name_idx(name),
41    PRIMARY KEY (id),
42    FOREIGN KEY (admin_user_id) REFERENCES users(id)
43);
44 
45DROP TABLE IF EXISTS media_types;
46CREATE TABLE media_types(
47	id SERIAL,
48    name ENUM("video", "podcast", "image")
49);
50    
51DROP TABLE IF EXISTS media;
52CREATE TABLE media(
53	id SERIAL,
54    media_type_id BIGINT UNSIGNED NOT NULL,
55    user_id BIGINT UNSIGNED NOT NULL,
56    filename VARCHAR(255),    	
57    size INT,
58	metadata JSON,
59    created_at DATETIME DEFAULT NOW(),
60    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
61
62    FOREIGN KEY (user_id) REFERENCES users(id),
63    FOREIGN KEY (media_type_id) REFERENCES media_types(id)
64);
65    
66DROP TABLE IF EXISTS posts;
67CREATE TABLE posts (
68    id SERIAL,
69    blog_id BIGINT UNSIGNED NOT NULL,
70    body TEXT,
71    media BIGINT UNSIGNED,
72    created_at DATETIME DEFAULT NOW(),
73 	
74    FOREIGN KEY (media) REFERENCES media(id),
75    FOREIGN KEY (blog_id) REFERENCES blogs(id)
76);
77 
78DROP TABLE IF EXISTS kindof_sports;
79CREATE TABLE kindof_sports (
80    id SERIAL,
81    name VARCHAR(50) UNIQUE,
82    PRIMARY KEY (id, name)
83);
84 
85DROP TABLE IF EXISTS tournaments;
86CREATE TABLE tournaments (
87    id SERIAL,
88    name VARCHAR(50),
89    kindof_sport BIGINT UNSIGNED NOT NULL,
90 	
91    PRIMARY KEY (id, name),
92    FOREIGN KEY (kindof_sport) REFERENCES kindof_sports(id)
93);
94
95DROP TABLE IF EXISTS teams;
96CREATE TABLE teams (
97    id SERIAL,
98    name VARCHAR(50) UNIQUE,
99  	kindof_sport BIGINT UNSIGNED NOT NULL,
100    
101    PRIMARY KEY (id, name)
102);
103 
104ALTER TABLE profiles ADD 
105FOREIGN KEY (favorite_teams) REFERENCES teams(id);
106 
107DROP TABLE IF EXISTS mathes;
108CREATE TABLE matches (
109    id SERIAL,
110    team_1 BIGINT UNSIGNED NOT NULL,
111    team_2 BIGINT UNSIGNED NOT NULL,
112    tournament BIGINT UNSIGNED NOT NULL,
113    playtime DATETIME,
114    score_team_1 TINYINT,
115    score_team_2 TINYINT,
116 
117    FOREIGN KEY (tournament) REFERENCES tournaments(id),
118    FOREIGN KEY (team_1) REFERENCES teams(id),
119    FOREIGN KEY (team_2) REFERENCES teams(id)
120);
121 
122ALTER TABLE matches 
123ADD CHECK (team_1 <> team_2);
124
125DROP TABLE IF EXISTS persons;
126CREATE TABLE persons (
127    id SERIAL,
128    firstname VARCHAR(30),
129    lastname VARCHAR(30),
130    gender ENUM("Мужской", "Женский"),
131    birthday DATE,
132    weight TINYINT UNSIGNED,
133    height TINYINT UNSIGNED,
134    person_photo BIGINT UNSIGNED NOT NULL UNIQUE,
135    stat BIGINT UNSIGNED NOT NULL,
136    team BIGINT UNSIGNED NOT NULL,
137    
138    FOREIGN KEY (team) REFERENCES teams(id)
139    );
140    
141DROP TABLE IF EXISTS stats;
142CREATE TABLE stats (
143	id SERIAL,
144	goals SMALLINT UNSIGNED,
145    assists SMALLINT UNSIGNED,
146    red_cards SMALLINT UNSIGNED,
147    yellow_cards SMALLINT UNSIGNED,
148    played_time SMALLINT UNSIGNED,
149	
150    PRIMARY KEY (id)
151);
152	
153ALTER TABLE persons ADD
154FOREIGN KEY (stat) REFERENCES stats(id);
155   
156   -- Делаем отдельную таблицу с фотографии спортивных персон, чтобы через неё сделать уникальными людей в таблице persons
157DROP TABLE IF EXISTS persons_photos;
158CREATE TABLE persons_photos (
159	id SERIAL,
160	media_id BIGINT UNSIGNED NOT NULL,
161
162    FOREIGN KEY (media_id) REFERENCES media(id)
163);
164
165ALTER TABLE persons ADD
166FOREIGN KEY (person_photo) REFERENCES persons_photos(id);
167    
168DROP TABLE IF EXISTS profiles_photos;
169CREATE TABLE profiles_photos (
170	id SERIAL,
171	media_id BIGINT unsigned NOT NULL,
172
173    FOREIGN KEY (media_id) REFERENCES media(id)
174);
175
176ALTER TABLE profiles ADD
177FOREIGN KEY (profile_photo) REFERENCES persons_photos(id);
178
179DROP TABLE IF EXISTS likes;
180CREATE TABLE likes(
181    id SERIAL,
182    profil_id BIGINT UNSIGNED NOT NULL,
183    post_id BIGINT UNSIGNED NOT NULL,
184    created_at DATETIME DEFAULT NOW(),
185    
186    FOREIGN KEY (profil_id) REFERENCES profiles(user_id),
187    FOREIGN KEY (post_id) REFERENCES posts(id)
188 );   
189    
190DROP TABLE IF EXISTS dislikes;
191CREATE TABLE dislikes(
192    id SERIAL,
193    profil_id BIGINT UNSIGNED NOT NULL,
194    post_id BIGINT UNSIGNED NOT NULL,
195    created_at DATETIME DEFAULT NOW(),
196    
197    FOREIGN KEY (profil_id) REFERENCES profiles(user_id),
198    FOREIGN KEY (post_id) REFERENCES posts(id)
199 );
200    
201ALTER TABLE profiles ADD 
202FOREIGN KEY (favorite_teams) REFERENCES teams(id);
203    
204ALTER TABLE profiles ADD
205FOREIGN KEY (favorite_persons) REFERENCES persons(id);
206   
207ALTER TABLE profiles ADD
208FOREIGN KEY (favorite_blogs) REFERENCES blogs(id);
209   
210ALTER TABLE profiles ADD
211FOREIGN KEY (favorite_tournaments) REFERENCES tournaments(id);