-- Advanced system: admins, reminders, comments, workout sessions, set logs
-- Run after schema.sql (or on existing workout_generator DB)

USE workout_generator;

-- Admins table (optional separate admin login; we use users.role instead)
-- CREATE TABLE admins (...) if you want separate admin accounts;

-- Reminders
CREATE TABLE reminders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    remind_at DATETIME NOT NULL,
    type ENUM('workout', 'rest', 'custom') DEFAULT 'workout',
    title VARCHAR(255) NOT NULL,
    message TEXT,
    plan_id INT NULL,
    day_number INT NULL,
    dismissed BOOLEAN DEFAULT FALSE,
    created_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
);

-- Comments (on exercises or on progress logs)
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    target_type ENUM('exercise', 'progress_log') NOT NULL,
    target_id INT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Workout sessions (one per "workout day" when user starts a workout)
CREATE TABLE workout_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    day_number INT NOT NULL,
    session_date DATE NOT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    difficulty_rating INT NULL CHECK (difficulty_rating BETWEEN 1 AND 5),
    notes TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES workout_plans(id) ON DELETE CASCADE,
    UNIQUE KEY (user_id, plan_id, day_number, session_date)
);

-- Index for reminders (scheduled)
CREATE INDEX idx_reminders_user_dismissed ON reminders(user_id, dismissed, remind_at);
CREATE INDEX idx_comments_target ON comments(target_type, target_id);
CREATE INDEX idx_sessions_user_plan ON workout_sessions(user_id, plan_id, session_date);
