-- Workout Plan Generator Database Schema
-- Run this to create the database and tables

CREATE DATABASE IF NOT EXISTS workout_generator CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE workout_generator;

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Profiles table
CREATE TABLE profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    age INT,
    gender ENUM('male', 'female', 'other'),
    fitness_level INT CHECK (fitness_level BETWEEN 1 AND 5),
    health_conditions TEXT,
    goals JSON,
    preferences JSON,
    weekly_time_available INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY (user_id)
);

-- Exercises table
CREATE TABLE exercises (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    target_muscles JSON,
    equipment JSON,
    difficulty ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
    demonstration_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Workout plans table
CREATE TABLE workout_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    start_date DATE,
    duration_weeks INT DEFAULT 4,
    status ENUM('active', 'completed', 'archived') DEFAULT 'active',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Plan exercises (junction + details)
CREATE TABLE plan_exercises (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_id INT NOT NULL,
    exercise_id INT NOT NULL,
    day_number INT NOT NULL,
    sets INT DEFAULT 3,
    reps VARCHAR(50) DEFAULT '10',
    intensity VARCHAR(50),
    rest_seconds INT DEFAULT 60,
    FOREIGN KEY (plan_id) REFERENCES workout_plans(id) ON DELETE CASCADE,
    FOREIGN KEY (exercise_id) REFERENCES exercises(id)
);

-- Progress logs table
CREATE TABLE progress_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NULL,
    workout_date DATE NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    difficulty_rating INT CHECK (difficulty_rating BETWEEN 1 AND 5),
    notes TEXT,
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES workout_plans(id) ON DELETE SET NULL
);

-- Seed sample exercises
INSERT INTO exercises (name, description, target_muscles, equipment, difficulty, demonstration_url) VALUES
('Push-ups', 'Classic bodyweight chest and tricep exercise.', '["chest","triceps","shoulders"]', '["none"]', 'beginner', ''),
('Squats', 'Lower body compound movement targeting quads and glutes.', '["quadriceps","glutes","hamstrings"]', '["bodyweight","barbell"]', 'beginner', ''),
('Plank', 'Core stability hold.', '["core","abs"]', '["none"]', 'beginner', ''),
('Dumbbell Rows', 'Single-arm row for back and biceps.', '["back","biceps"]', '["dumbbell"]', 'intermediate', ''),
('Lunges', 'Unilateral leg exercise.', '["quadriceps","glutes","hamstrings"]', '["bodyweight","dumbbell"]', 'beginner', ''),
('Deadlift', 'Hip hinge for posterior chain.', '["back","hamstrings","glutes"]', '["barbell","dumbbell"]', 'advanced', ''),
('Bench Press', 'Horizontal push for chest.', '["chest","triceps","shoulders"]', '["barbell","dumbbell"]', 'intermediate', ''),
('Overhead Press', 'Vertical push for shoulders.', '["shoulders","triceps"]', '["barbell","dumbbell"]', 'intermediate', ''),
('Bicep Curls', 'Isolation for biceps.', '["biceps"]', '["dumbbell","barbell"]', 'beginner', ''),
('Tricep Dips', 'Tricep and chest dip.', '["triceps","chest"]', '["bench","parallel_bars"]', 'intermediate', '');
