DML.sql

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

SET FOREIGN_KEY_CHECKS=0;
SET AUTOCOMMIT = 0;

-- CREATE --------------------------------------------------------------------------------------------------------------

-- Insert a new user
-- Ex: ('john_doe', 1234567890)
INSERT INTO Users (username, contact)
VALUES (:username, :contact);

-- Add a new workout session for a specific user
-- Ex: ('kangjoo', '2025-01-01', 60, 'Chest day')
INSERT INTO WorkoutSessions (user_id, workout_date, duration_minutes, notes)
VALUES (
    (SELECT user_id FROM Users WHERE username = :username),
    :workout_date, :duration_minutes, :notes
);

-- Add a new exercise to the list of exercises
-- Ex: ('Bench Press', 'Lift barbell off chest', 'Strength')
INSERT INTO ListExercises (name, description, category)
VALUES (:exercise_name, :exercise_description, :exercise_category);

-- Record an exercise in a specific workout session
-- Ex: ('kangjoo', 'Bench Press', 3, 10, 135)
INSERT INTO SessionExercises (workout_session_id, exercise_id, sets, reps, weight)
VALUES (
    (SELECT workout_session_id FROM WorkoutSessions WHERE workout_session_id = :workout_session_id),
    COALESCE ((SELECT exercise_id FROM ListExercises WHERE name = :exercise_name) NULL),
    :sets, :reps, :weight
);

-- Log a new metric for a user
-- Ex: ('kangjoo', 'Weight', 180, '2025-01-01')
INSERT INTO Metrics (user_id, metric_type, value, date_recorded)
VALUES (
    (SELECT user_id FROM Users WHERE username = :username),
    :metric_type, :metric_value, :metric_date_recorded
);


-- READ ---------------------------------------------------------------------------------------------------------------

-- Fetch all user details
-- Ex: ('john_doe')
SELECT user_id, username, contact
FROM Users
WHERE username = :username;

-- Get all workout sessions for a specific user
-- Ex: ('kangjoo')
SELECT 
    WorkoutSessions.workout_session_id, WorkoutSessions.workout_date, WorkoutSessions.duration_minutes, WorkoutSessions.notes
FROM 
    WorkoutSessions
JOIN 
    Users ON WorkoutSessions.user_id = Users.user_id
WHERE 
    Users.username = :username
ORDER BY 
    WorkoutSessions.workout_date DESC;

-- Fetch all exercises in a given workout session
-- Ex: (12)
SELECT 
    SessionExercises.session_exercise_id, ListExercises.name AS exercise_name, SessionExercises.sets, SessionExercises.reps, SessionExercises.weight
FROM 
    SessionExercises
JOIN 
    ListExercises ON SessionExercises.exercise_id = ListExercises.exercise_id
WHERE 
    SessionExercises.workout_session_id = :workout_session_id;

-- Get all exercises from the exercise list
SELECT exercise_id, name, description, category
FROM ListExercises
ORDER BY name;

-- Get all metrics for a specific user
-- Ex: ('kangjoo')
SELECT 
    Metrics.metric_id, Metrics.metric_type, Metrics.value, Metrics.date_recorded
FROM 
    Metrics
WHERE 
    Metrics.user_id = (SELECT user_id FROM Users WHERE username = :username)
ORDER BY 
    Metrics.date_recorded DESC;


-- UPDATE --------------------------------------------------------------------------------------------------------------

-- Update user's contact information
-- Ex: ('john_doe', 912012020)
UPDATE Users
SET contact = %s
WHERE username = %s;

-- Update workout session details
-- Ex: ('2025-01-01', 90, 'Updated notes', 11)
UPDATE WorkoutSessions
SET workout_date = %s, duration_minutes = %s, notes = %s
WHERE workout_session_id = %s;

-- Update exercise details in the exercise list
-- Ex: ('Bench Press', 'Updated description', 'Strength', 5)
UPDATE ListExercises
SET name = %s, description = %s, category = %s
WHERE exercise_id = %s;

-- Update session exercise details for a specific workout session
-- Ex: (3, 12, 145, 25)
UPDATE SessionExercises
SET sets = %s, reps = %s, weight = %s
WHERE session_exercise_id = %s AND exercise_id = %s;

-- Update a specific metric for a user
-- Ex: ('Weight', 185, '2025-02-01', 9)
UPDATE Metrics
SET metric_type = %s, value = %s, date_recorded = %s
WHERE metric_id = %s;


-- DELETE --------------------------------------------------------------------------------------------------------------

-- Delete a user by username
-- Ex: ('john_doe')
DELETE FROM Users 
WHERE username = :username;

-- Delete a specific workout session by session ID
-- Ex: (11)
DELETE FROM WorkoutSessions
WHERE workout_session_id = :workout_session_id;

-- Delete an exercise from the exercise list by exercise ID
-- Ex: (5)
DELETE FROM ListExercises
WHERE exercise_id = :exercise_id;

-- Delete a specific exercise from a workout session by session exercise ID
-- Ex: (15)
DELETE FROM SessionExercises
WHERE session_exercise_id = :session_exercise_id AND exercise_id = :exercise_id;

-- Delete a specific metric by metric ID
-- Ex: (9)
DELETE FROM Metrics
WHERE metric_id = :metric_id;


SET FOREIGN_KEY_CHECKS=1;
COMMIT;