bd_create_script.txt

-- Удаление таблиц, если они существуют (для очистки и предотвращения конфликтов)
DROP TABLE IF EXISTS weight_journal, users CASCADE;
-- Таблица пользователей
CREATE TABLE IF NOT EXISTS users (
                                     user_id SERIAL PRIMARY KEY,
                                     login VARCHAR(20) NOT NULL UNIQUE,
                                     password VARCHAR(20) NOT NULL CHECK (LENGTH(password) >= 8),
                                     registration_date DATE DEFAULT CURRENT_DATE,
                                     birth_date DATE CHECK (birth_date BETWEEN '1900-01-01' AND CURRENT_DATE),
				     gender VARCHAR(10),
                                     height REAL CHECK (height BETWEEN 100 AND 250),
                                     goal VARCHAR(50),
                                     fitness_level INT CHECK (fitness_level BETWEEN 1 AND 3),
                                     activity_level INT CHECK (activity_level BETWEEN 1 AND 5),
                                     available_days INT CHECK (available_days BETWEEN 1 AND 7),
                                     role VARCHAR NOT NULL,
				     start_training DATE,
				     diet_preference VARCHAR(20) NOT NULL DEFAULT 'OMNIVORE'
);

-- Таблица журнала веса
CREATE TABLE IF NOT EXISTS weight_journal (
                                              weight_journal_id SERIAL PRIMARY KEY,
                                              user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
                                              weight REAL NOT NULL CHECK (weight BETWEEN 0 AND 400),
                                              weight_date DATE DEFAULT CURRENT_DATE
);

-- Таблица достижений
CREATE TABLE IF NOT EXISTS achievement (
                                           achievement_id SERIAL PRIMARY KEY,
                                           created_by INT REFERENCES users(user_id) ON DELETE CASCADE,
                                           name VARCHAR(50) NOT NULL,
                                           description VARCHAR(200),
                                           when_created DATE DEFAULT CURRENT_DATE
);

-- Таблица достижений пользователя
CREATE TABLE IF NOT EXISTS user_achievement (
                                                user_achievement_id SERIAL PRIMARY KEY,
                                                user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
                                                achievement_id INT REFERENCES achievement(achievement_id)
);

-- Таблица упражнений
CREATE TABLE IF NOT EXISTS exercises (
                                         exercise_id SERIAL PRIMARY KEY,
                                         created_by INT REFERENCES users(user_id) ON DELETE CASCADE,
                                         when_created DATE DEFAULT CURRENT_DATE,
                                         name VARCHAR(50) NOT NULL,
                                         muscle_group VARCHAR(50) NOT NULL,
					 is_compound BOOLEAN NOT NULL DEFAULT FALSE,
					 recommended_repetitions INT,
                                         description VARCHAR(200),
					 execution_instructions TEXT NOT NULL DEFAULT ''
);

-- Таблица рабочих весов для упражнений
CREATE TABLE IF NOT EXISTS exercise_working_weight (
                                                       exercise_working_weight_id SERIAL PRIMARY KEY,
                                                       exercise_id INT REFERENCES exercises(exercise_id) ON DELETE CASCADE,
                                                       user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
                                                       weight REAL CHECK (weight BETWEEN 0 AND 1000),
                                                       weight_date DATE DEFAULT CURRENT_DATE
);

-- Таблица тренировочных дней
CREATE TABLE IF NOT EXISTS training_day (
                                            training_day_id SERIAL PRIMARY KEY,
                                            user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
                                            training_date DATE,
					    is_completed BOOLEAN DEFAULT false
);

-- Таблица связей упражнений и тренировочных дней
CREATE TABLE IF NOT EXISTS exercise_training_day (
                                                     exercise_training_day_id SERIAL PRIMARY KEY,
                                                     training_day_id INT REFERENCES training_day(training_day_id) ON DELETE CASCADE,
                                                     exercise_id INT REFERENCES exercises(exercise_id) ON DELETE CASCADE,
						     exercise_number INT NOT NULL,
						     sets INT,
						     repetitions INT
);

-- Таблица питания
CREATE TABLE IF NOT EXISTS meals (
                                     meal_id SERIAL PRIMARY KEY,
                                     created_by INT REFERENCES users(user_id) ON DELETE CASCADE,
                                     when_created DATE DEFAULT CURRENT_DATE,
                                     name VARCHAR(50) NOT NULL,
                                     calories REAL CHECK (calories BETWEEN 0 AND 1000),
                                     protein REAL CHECK (protein BETWEEN 0 AND 100),
                                     fats REAL CHECK (fats BETWEEN 0 AND 100),
                                     carbs REAL CHECK (carbs BETWEEN 0 AND 100)
);

-- Таблица аллергий
CREATE TABLE IF NOT EXISTS allergies (
                                         allergy_id SERIAL PRIMARY KEY,
                                         created_by INT REFERENCES users(user_id) ON DELETE CASCADE,
                                         when_created DATE DEFAULT CURRENT_DATE,
                                         name VARCHAR(50) NOT NULL UNIQUE
);

-- Таблица пользовательских аллергий
CREATE TABLE IF NOT EXISTS user_allergy (
                                            user_allergy_id SERIAL PRIMARY KEY,
                                            user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
                                            allergy_id INT REFERENCES allergies(allergy_id) ON DELETE CASCADE
);

-- Таблица связей аллергии и блюда
CREATE TABLE IF NOT EXISTS allergy_meal (
                                            allergy_meal_id SERIAL PRIMARY KEY,
                                            meal_id INT REFERENCES meals(meal_id) ON DELETE CASCADE,
                                            allergy_id INT REFERENCES allergies(allergy_id) ON DELETE CASCADE
);
-- Таблица диетических дней для админа
CREATE TABLE IF NOT EXISTS diet_day_admin (
                                              diet_day_admin_id SERIAL PRIMARY KEY,
                                              created_by INT REFERENCES users(user_id) ON DELETE CASCADE,
                                              when_created DATE DEFAULT CURRENT_DATE,
                                              calories REAL NOT NULL CHECK (calories BETWEEN 0 AND 5000),
                                              name VARCHAR(50) NOT NULL,
					      diet_type VARCHAR(20) NOT NULL DEFAULT 'OMNIVORE'
);
-- Таблица блюд для диетических дней админа
CREATE TABLE IF NOT EXISTS meal_diet_day_admin (
                                                   meal_diet_day_admin_id SERIAL PRIMARY KEY,
                                                   diet_day_admin_id INT REFERENCES diet_day_admin(diet_day_admin_id) ON DELETE CASCADE,
                                                   meal_id INT REFERENCES meals(meal_id) ON DELETE CASCADE,
                                                   portion_size REAL CHECK (portion_size BETWEEN 0 AND 1000),
						   meal_position VARCHAR(255) NOT NULL DEFAULT 'BREAKFAST'
);

-- Таблица дней диеты пользователя
CREATE TABLE IF NOT EXISTS diet_day_user (
                                             diet_day_user_id SERIAL PRIMARY KEY,
                                             diet_day_id INT REFERENCES diet_day_admin(diet_day_admin_id) ON DELETE CASCADE,
                                             user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
                                             rate REAL CHECK (rate BETWEEN 0 AND 100),
                                             day_date DATE DEFAULT CURRENT_DATE
);