-- Add profile fields to users and weight history table

ALTER TABLE users
  ADD COLUMN height_cm INT UNSIGNED NULL AFTER password_hash,
  ADD COLUMN age TINYINT UNSIGNED NULL AFTER height_cm,
  ADD COLUMN fitness_goal VARCHAR(255) NULL AFTER age,
  ADD COLUMN profile_notes TEXT NULL AFTER fitness_goal;

CREATE TABLE IF NOT EXISTS user_weight_entries (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  weight_kg DECIMAL(6,2) NOT NULL,
  recorded_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_user_weight_user_date (user_id, recorded_at),
  CONSTRAINT fk_user_weight_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
