-- Elimina le tabelle se esistono
DROP TABLE IF EXISTS workouts_meta;
DROP TABLE IF EXISTS goals_meta;
DROP TABLE IF EXISTS progress_stats_meta;
DROP TABLE IF EXISTS workout_details_meta;

-- Crea la tabella workouts_meta
CREATE TABLE IF NOT EXISTS workouts_meta (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    workout_date DATE NOT NULL,
    duration_minutes INT NOT NULL DEFAULT 0,
    calories_burned INT NOT NULL DEFAULT 0,
    workout_type ENUM('Cardio', 'Forza', 'Funzionale', 'Bodyweight') NOT NULL DEFAULT 'Forza',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users_meta(id) ON DELETE CASCADE
);

-- Crea la tabella workout_details
CREATE TABLE IF NOT EXISTS workout_details_meta (
    id INT AUTO_INCREMENT PRIMARY KEY,
    workout_id INT NOT NULL,
    user_id INT NOT NULL,
    exercise_name VARCHAR(255) NOT NULL,
    sets INT NOT NULL DEFAULT 0,
    reps INT NOT NULL DEFAULT 0,
    weight_lifted FLOAT NOT NULL DEFAULT 0,
    workout_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (workout_id) REFERENCES workouts_meta(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users_meta(id) ON DELETE CASCADE
);

-- Crea la tabella goals_meta
CREATE TABLE IF NOT EXISTS goals_meta (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    target_value FLOAT NOT NULL,
    current_value FLOAT NOT NULL,
    unit VARCHAR(50) NOT NULL,
    achieved BOOLEAN DEFAULT FALSE,
    achieved_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users_meta(id) ON DELETE CASCADE
);

-- Crea la tabella progress_meta
CREATE TABLE IF NOT EXISTS progress_meta (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    stat_date DATE NOT NULL,
    stat_type VARCHAR(50) NOT NULL,
    value FLOAT NOT NULL,
    unit VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users_meta(id) ON DELETE CASCADE
);
