DDL.sql

-- This file corresponds to the necessary CS340 Portfolio Project deliverables.

SET foreign_key_checks = 0;
SET autocommit = 0;

-- Ensures clean import
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS WorkoutSessions;
DROP TABLE IF EXISTS ListExercises;
DROP TABLE IF EXISTS SessionExercises;
DROP TABLE IF EXISTS Metrics;

-- User table that stores platform users
CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    contact VARCHAR(15) NOT NULL UNIQUE
);

-- Log specific workout session
CREATE TABLE WorkoutSessions (
    workout_session_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    workout_date DATETIME NOT NULL,
    duration_minutes INT NOT NULL,
    notes CHAR(255),
    -- Foreign key links user_id in WorkoutSessions to the primary key in Users.
    -- This ensures that each workout session is associated with a valid user.
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

-- Exercise table to store various exercises that can be added to a workout
CREATE TABLE ListExercises (
    exercise_id INT PRIMARY KEY AUTO_INCREMENT,
    name CHAR(255) NOT NULL,
    description CHAR(255),
    category CHAR(255) NOT NULL
);

-- Stores exercises performed in a specific workout
CREATE TABLE SessionExercises (
    session_exercise_id INT PRIMARY KEY AUTO_INCREMENT,
    workout_session_id INT NOT NULL,
    exercise_id INT NULL,
    sets INT NOT NULL,
    reps INT NOT NULL,
    weight INT,
    -- Foreign key links workout_session_id to WorkoutSessions, enforcing that each 
    -- entry in SessionExercises is tied to an existing workout session.
    FOREIGN KEY (workout_session_id) REFERENCES WorkoutSessions(workout_session_id) ON DELETE CASCADE,
    -- Foreign key links exercise_id to ListExercises, ensuring exercises performed 
    -- in each session are valid and listed in ListExercises.
    FOREIGN KEY (exercise_id) REFERENCES ListExercises(exercise_id) ON DELETE CASCADE
);

-- Store various metrics for a user's progress
CREATE TABLE Metrics (
    metric_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    metric_type VARCHAR(255) NOT NULL,
    value DECIMAL(10, 2) NOT NULL,
    date_recorded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- Foreign key links user_id in Metrics to Users, ensuring each metric is tied
    -- to a valid user, so deleted users have their metrics automatically removed.
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

-- Populate Users table with sample data
INSERT INTO Users (username, contact)
VALUES
    ('kangjoo', 1234567890),
    ('hinsonj', 1234567891),
    ('tanalexis', 1234567892);

-- Populate WorkoutSessions table with sample data
INSERT INTO WorkoutSessions (user_id, workout_date, duration_minutes, notes)
VALUES
    ((SELECT user_id FROM Users WHERE username = 'kangjoo'), '2024-11-01 08:00:00', 60, 'Morning workout, low intensity, form focused'),
    ((SELECT user_id FROM Users WHERE username = 'hinsonj'), '2024-11-02 09:00:00', 45, NULL),
    ((SELECT user_id FROM Users WHERE username = 'tanalexis'), '2024-11-03 12:00:00', 30, 'Quick session, high intensity');

-- Populate ListExercises table with sample data
INSERT INTO ListExercises (name, description, category)
VALUES
    ('Push Up', 'https://www.youtube.com/watch?v=IODxDxX7oi4', 'Strength'),
    ('Squat', 'https://www.youtube.com/watch?v=byxWus7BwfQ', 'Strength'),
    ('Running', 'https://www.youtube.com/watch?v=brFHyOtTwH4', 'Cardio');

-- Populate SessionExercises table with sample data
INSERT INTO SessionExercises (workout_session_id, exercise_id, sets, reps, weight)
VALUES
    ((SELECT workout_session_id FROM WorkoutSessions WHERE notes LIKE '%Morning workout%'),
     (SELECT exercise_id FROM ListExercises WHERE name = 'Push Up'), 3, 25, NULL),
    ((SELECT workout_session_id FROM WorkoutSessions WHERE notes LIKE '%Quick session%'),
     (SELECT exercise_id FROM ListExercises WHERE name = 'Running'), 1, 0, NULL),
    ((SELECT workout_session_id FROM WorkoutSessions WHERE notes IS NULL),
     (SELECT exercise_id FROM ListExercises WHERE name = 'Squat'), 4, 10, 250);

-- Populate Metrics table with sample data
INSERT INTO Metrics (user_id, metric_type, value, date_recorded)
VALUES
    ((SELECT user_id FROM Users WHERE username = 'kangjoo'), 'Body Weight', 180.5, '2024-01-05 08:00:00'),
    ((SELECT user_id FROM Users WHERE username = 'hinsonj'), 'Personal Best', 200.0, '2024-01-05 08:00:00'),
    ((SELECT user_id FROM Users WHERE username = 'tanalexis'), 'Body Weight', 150.5, '2024-01-06 08:00:00');

SET foreign_key_checks = 1;
set autocommit = 1;

COMMIT;