-- Migration: weight tracking for member portal "Remote Program" (homework)
-- exercises. Required by: Feature 7 (Weight Tracking Inside Remote Programs).
-- Database: pinnadb7_customer
-- Never removes existing data — new table only.
--
-- Design notes:
--   * One row per (member, homework_details row, exercise category slot,
--     week). A single homework_details row can carry up to three distinct
--     exercises (corrective/strength/flexibility columns), so
--     `exercise_section` disambiguates which of the three a weight entry
--     belongs to.
--   * `exercise_label` and `program_name` are snapshotted at save time
--     (not just foreign-keyed) so a member's history stays accurate even
--     if a coach later edits/renames the exercise or program — this
--     matches how the rest of this schema already treats homework_details
--     as a point-in-time assignment rather than a live-editable template.
--   * `week_start_date` is the Monday of the ISO week the entry is for.
--     The UNIQUE key on (member_id, homework_detailsid, exercise_section,
--     week_start_date) means saving again within the same week updates
--     that week's row (upsert) — it can never overwrite a different
--     week's row, since week_start_date is part of the key.

CREATE TABLE `member_exercise_weight` (
    `member_exercise_weightid` INT(11) NOT NULL AUTO_INCREMENT,
    `member_id` INT(11) NOT NULL,
    `homework_detailsid` INT(11) NOT NULL,
    `exercise_section` VARCHAR(20) NOT NULL,
    `exercise_label` VARCHAR(255) NOT NULL,
    `program_name` VARCHAR(255) NOT NULL,
    `week_start_date` DATE NOT NULL,
    `weight` VARCHAR(50) NOT NULL,
    `created_on` DATETIME NOT NULL,
    `updated_on` DATETIME NOT NULL,
    PRIMARY KEY (`member_exercise_weightid`),
    UNIQUE KEY `uniq_member_exercise_week` (`member_id`, `homework_detailsid`, `exercise_section`, `week_start_date`),
    KEY `idx_member_program_week` (`member_id`, `homework_detailsid`, `week_start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
