CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin', 'gymowner', 'user') NOT NULL DEFAULT 'user',
  api_token VARCHAR(128) DEFAULT NULL,
  token_updated_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS exercises (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  muscles VARCHAR(120) NOT NULL,
  equipment VARCHAR(180) NOT NULL,
  instruction TEXT NOT NULL,
  video_url VARCHAR(255) DEFAULT '',
  default_sets INT NOT NULL DEFAULT 3,
  default_reps INT NOT NULL DEFAULT 12,
  created_by INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_exercise_created_by FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS gym_memberships (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_name VARCHAR(120) NOT NULL DEFAULT 'Standard',
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status ENUM('active', 'expired', 'cancelled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_membership_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS gym_checkins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  checkin_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  checkout_at DATETIME DEFAULT NULL,
  method ENUM('manual', 'qr', 'nfc') NOT NULL DEFAULT 'manual',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_checkin_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS meal_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  name VARCHAR(180) NOT NULL,
  calories INT NOT NULL DEFAULT 0,
  protein INT NOT NULL DEFAULT 0,
  carbs INT NOT NULL DEFAULT 0,
  fat INT NOT NULL DEFAULT 0,
  photo_base64 LONGTEXT DEFAULT NULL,
  logged_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_meal_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS workout_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  external_id VARCHAR(120) NOT NULL,
  name VARCHAR(180) NOT NULL,
  description TEXT NOT NULL,
  is_premade TINYINT(1) NOT NULL DEFAULT 0,
  focus_muscles VARCHAR(255) NOT NULL DEFAULT '',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_workout_plan_external (external_id),
  CONSTRAINT fk_workout_plan_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS workout_plan_exercises (
  id INT AUTO_INCREMENT PRIMARY KEY,
  workout_plan_id INT NOT NULL,
  exercise_name VARCHAR(180) NOT NULL,
  muscle_groups VARCHAR(255) NOT NULL DEFAULT '',
  equipment VARCHAR(255) NOT NULL DEFAULT '',
  instruction TEXT NOT NULL,
  video_url VARCHAR(255) NOT NULL DEFAULT '',
  target_sets INT NOT NULL DEFAULT 3,
  target_reps INT NOT NULL DEFAULT 12,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_workout_plan_exercise_plan FOREIGN KEY (workout_plan_id) REFERENCES workout_plans(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS daily_metrics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  metric_date DATE NOT NULL,
  calories_consumed INT NOT NULL DEFAULT 0,
  steps INT NOT NULL DEFAULT 0,
  steps_goal INT NOT NULL DEFAULT 8000,
  water_glasses INT NOT NULL DEFAULT 0,
  water_goal INT NOT NULL DEFAULT 8,
  sleep_hours DECIMAL(5,2) NOT NULL DEFAULT 0,
  active_minutes INT NOT NULL DEFAULT 0,
  nutrition_progress DECIMAL(6,4) NOT NULL DEFAULT 0,
  daily_progress DECIMAL(6,4) NOT NULL DEFAULT 0,
  weekly_steps_json TEXT DEFAULT NULL,
  weekly_calories_json TEXT DEFAULT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_daily_metrics_user_date (user_id, metric_date),
  CONSTRAINT fk_daily_metrics_user FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (name, email, password_hash, role)
VALUES
  ('Admin User', 'admin@fitnep.com', '$2y$10$VvXvQ4tY5LVZxLpI2V1EeOc4GLwRoTSesxNUFDXL9uBeb5GsyhQ2m', 'admin'),
  ('Gym Owner', 'owner@fitnep.com', '$2y$10$VvXvQ4tY5LVZxLpI2V1EeOc4GLwRoTSesxNUFDXL9uBeb5GsyhQ2m', 'gymowner'),
  ('Normal User', 'user@fitnep.com', '$2y$10$VvXvQ4tY5LVZxLpI2V1EeOc4GLwRoTSesxNUFDXL9uBeb5GsyhQ2m', 'user')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO gym_memberships (user_id, plan_name, start_date, end_date, status)
SELECT u.id, 'Monthly', CURDATE() - INTERVAL 10 DAY, CURDATE() + INTERVAL 20 DAY, 'active'
FROM users u
WHERE u.role = 'user'
AND NOT EXISTS (
  SELECT 1 FROM gym_memberships gm WHERE gm.user_id = u.id
);

INSERT INTO gym_checkins (user_id, checkin_at, checkout_at, method)
SELECT u.id, NOW() - INTERVAL 1 DAY, NOW() - INTERVAL 1 DAY + INTERVAL 90 MINUTE, 'manual'
FROM users u
WHERE u.role = 'user'
AND NOT EXISTS (
  SELECT 1 FROM gym_checkins gc WHERE gc.user_id = u.id
);

-- Default password for seed users: password123