-- Tabella per i template delle schede predefinite
CREATE TABLE IF NOT EXISTS workout_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    difficulty ENUM('Principiante', 'Intermedio', 'Avanzato') NOT NULL,
    estimated_duration INT NOT NULL COMMENT 'Durata stimata in minuti',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabella per gli esercizi disponibili
CREATE TABLE IF NOT EXISTS exercises (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    muscle_group ENUM('Petto', 'Schiena', 'Gambe', 'Spalle', 'Braccia', 'Addominali', 'Cardio', 'Altro') NOT NULL,
    difficulty ENUM('Principiante', 'Intermedio', 'Avanzato') NOT NULL,
    instructions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabella per collegare esercizi ai template
CREATE TABLE IF NOT EXISTS template_exercises (
    template_id INT NOT NULL,
    exercise_id INT NOT NULL,
    sets INT NOT NULL,
    reps VARCHAR(50) NOT NULL COMMENT 'Può contenere range come "8-12" o "Fino al cedimento"',
    rest_time INT NOT NULL COMMENT 'Tempo di riposo in secondi',
    notes TEXT,
    sort_order INT NOT NULL COMMENT 'Ordine degli esercizi nella scheda',
    FOREIGN KEY (template_id) REFERENCES workout_templates(id) ON DELETE CASCADE,
    FOREIGN KEY (exercise_id) REFERENCES exercises(id) ON DELETE CASCADE,
    PRIMARY KEY (template_id, exercise_id, sort_order)
);

-- Tabella per le schede personalizzate degli utenti
CREATE TABLE IF NOT EXISTS user_workouts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_from_template BOOLEAN DEFAULT FALSE,
    template_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users_meta(id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES workout_templates(id) ON DELETE SET NULL
);

-- Tabella per gli esercizi nelle schede personalizzate
CREATE TABLE IF NOT EXISTS user_workout_exercises (
    workout_id INT NOT NULL,
    exercise_id INT NOT NULL,
    sets INT NOT NULL,
    reps VARCHAR(50) NOT NULL,
    rest_time INT NOT NULL COMMENT 'Tempo di riposo in secondi',
    notes TEXT,
    sort_order INT NOT NULL,
    FOREIGN KEY (workout_id) REFERENCES user_workouts(id) ON DELETE CASCADE,
    FOREIGN KEY (exercise_id) REFERENCES exercises(id) ON DELETE CASCADE,
    PRIMARY KEY (workout_id, exercise_id, sort_order)
);

-- Tabella per tracciare gli allenamenti completati
CREATE TABLE IF NOT EXISTS completed_workouts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    workout_id INT NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NULL,
    duration INT NULL COMMENT 'Durata effettiva in minuti',
    calories_burned INT NULL,
    notes TEXT,
    FOREIGN KEY (user_id) REFERENCES users_meta(id) ON DELETE CASCADE,
    FOREIGN KEY (workout_id) REFERENCES user_workouts(id) ON DELETE CASCADE
);

-- Tabella per i dettagli degli esercizi completati
CREATE TABLE IF NOT EXISTS completed_exercises (
    completed_workout_id INT NOT NULL,
    exercise_id INT NOT NULL,
    set_number INT NOT NULL,
    reps_completed INT NOT NULL,
    weight DECIMAL(5,2) NULL COMMENT 'Peso in kg, se applicabile',
    notes TEXT,
    FOREIGN KEY (completed_workout_id) REFERENCES completed_workouts(id) ON DELETE CASCADE,
    FOREIGN KEY (exercise_id) REFERENCES exercises(id) ON DELETE CASCADE,
    PRIMARY KEY (completed_workout_id, exercise_id, set_number)
);
