-- Extend exercise_metadata so the dashboard (and any SQL consumer) can
-- JOIN body region, Korean name, and default grips directly. Seed with
-- the 65-exercise catalog that the Flutter app carries as constants.

-- 1. Add catalog columns
ALTER TABLE public.exercise_metadata
  ADD COLUMN IF NOT EXISTS name_ko text,
  ADD COLUMN IF NOT EXISTS name_en text,
  ADD COLUMN IF NOT EXISTS primary_region text,
  ADD COLUMN IF NOT EXISTS secondary_regions text[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS default_grips text[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS is_start_lengthening boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS add_on text;

-- 2. primary_region CHECK
ALTER TABLE public.exercise_metadata
  DROP CONSTRAINT IF EXISTS exercise_metadata_primary_region_check;
ALTER TABLE public.exercise_metadata
  ADD CONSTRAINT exercise_metadata_primary_region_check
  CHECK (primary_region IS NULL OR primary_region IN (
    'chest','back','shoulders','biceps','triceps','legs',
    'forearms','glutes','calves','traps','others'
  ));

-- 3. body_part_group generated column — push / pull / legs / core
ALTER TABLE public.exercise_metadata
  DROP COLUMN IF EXISTS body_part_group;
ALTER TABLE public.exercise_metadata
  ADD COLUMN body_part_group text GENERATED ALWAYS AS (
    CASE primary_region
      WHEN 'chest' THEN 'push'
      WHEN 'shoulders' THEN 'push'
      WHEN 'triceps' THEN 'push'
      WHEN 'back' THEN 'pull'
      WHEN 'biceps' THEN 'pull'
      WHEN 'forearms' THEN 'pull'
      WHEN 'traps' THEN 'pull'
      WHEN 'legs' THEN 'legs'
      WHEN 'glutes' THEN 'legs'
      WHEN 'calves' THEN 'legs'
      WHEN 'others' THEN 'core'
      ELSE NULL
    END
  ) STORED;

-- 4. Upsert catalog (65 exercises). ON CONFLICT preserves curated
-- mechanics / force_type / default_mvt_mps already set on 5 rows.
INSERT INTO public.exercise_metadata
  (exercise_id, display_name, name_en, name_ko, primary_region, secondary_regions, default_grips, is_start_lengthening, add_on, mechanics, force_type, default_mvt_mps)
VALUES
  -- Back (9)
  ('bentOverRow', 'Bent Over Row', 'Bent Over Row', '벤트 오버 로우', 'back', '{biceps}', '{barbell,handles}', true, NULL, 'compound', 'pull', 0.30),
  ('cableArmPullDown', 'Cable Arm Pull Down', 'Cable Arm Pull Down', '케이블 암 풀 다운', 'back', '{}', '{barbell,handles,shortBar,multiGrip}', true, 'tower', 'isolation', 'pull', 0.45),
  ('chinUp', 'Chin Up', 'Chin Up', '친 업', 'back', '{biceps}', '{}', false, 'tower', 'compound', 'pull', 0.30),
  ('deadlift', 'Deadlift', 'Deadlift', '데드리프트', 'back', '{legs,glutes}', '{barbell,handles}', true, NULL, 'compound', 'hinge', 0.20),
  ('inclineRow', 'Incline Row', 'Incline Row', '인클라인 로우', 'back', '{biceps}', '{barbell,handles}', true, 'bench', 'compound', 'pull', 0.30),
  ('latPullDown', 'Lat Pull Down', 'Lat Pull Down', '랫 풀 다운', 'back', '{}', '{barbell,handles}', true, 'tower', 'compound', 'pull', 0.30),
  ('pullUp', 'Pull Up', 'Pull Up', '풀 업', 'back', '{biceps}', '{}', false, 'tower', 'compound', 'pull', 0.30),
  ('rearPullDown', 'Rear Pull Down', 'Rear Pull Down', '리어 풀 다운', 'back', '{}', '{barbell,handles}', true, 'tower', 'isolation', 'pull', 0.45),
  ('row', 'Row', 'Row', '로우', 'back', '{biceps}', '{barbell,handles}', true, 'bench', 'compound', 'pull', 0.30),
  -- Biceps (7)
  ('bicepCurl', 'Bicep Curl', 'Bicep Curl', '바이셉 컬', 'biceps', '{}', '{barbell,handles,shortBar,multiGrip}', true, NULL, 'isolation', 'pull', 0.45),
  ('concentrationCurl', 'Concentration Curl', 'Concentration Curl', '컨센트레이션 컬', 'biceps', '{}', '{handles}', true, 'bench', 'isolation', 'pull', 0.45),
  ('hammerCurl', 'Hammer Curl', 'Hammer Curl', '헤머 컬', 'biceps', '{}', '{handles,multiGrip}', true, NULL, 'isolation', 'pull', 0.45),
  ('inclineCurl', 'Incline Curl', 'Incline Curl', '인클라인 컬', 'biceps', '{}', '{handles}', true, 'bench', 'isolation', 'pull', 0.45),
  ('inclineHammerCurl', 'Incline Hammer Curl', 'Incline Hammer Curl', '인클라인 해머 컬', 'biceps', '{}', '{handles}', true, 'bench', 'isolation', 'pull', 0.45),
  ('reverseCurl', 'Reverse Curl', 'Reverse Curl', '리버스 컬', 'biceps', '{}', '{barbell,handles,shortBar,multiGrip}', true, NULL, 'isolation', 'pull', 0.45),
  ('spiderCurl', 'Spider Curl', 'Spider Curl', '스파이더 컬', 'biceps', '{}', '{barbell,handles,shortBar,multiGrip}', true, 'bench', 'isolation', 'pull', 0.45),
  -- Calves (1)
  ('calfRaise', 'Calf Raise', 'Calf Raise', '카프레이즈', 'calves', '{}', '{barbell,handles,shortBar,waistBelt}', true, NULL, 'isolation', 'push', 0.45),
  -- Chest (9)
  ('benchPress', 'Bench Press', 'Bench Press', '벤치 프레스', 'chest', '{triceps}', '{barbell,handles}', false, 'bench', 'compound', 'push', 0.30),
  ('cableCrossoverFly', 'Cable Crossover Fly', 'Cable Crossover Fly', '케이블 크로스오버 플라이', 'chest', '{}', '{handles}', true, 'tower', 'isolation', 'push', 0.45),
  ('cableCrossoverHighFly', 'Cable Crossover High Fly', 'Cable Crossover High Fly', '케이블 크로스오버 하이 플라이', 'chest', '{shoulders}', '{handles}', true, 'tower', 'isolation', 'push', 0.45),
  ('declineBenchPress', 'Decline Bench Press', 'Decline Bench Press', '디클라인 벤치 프레스', 'chest', '{triceps}', '{barbell,handles}', false, 'bench', 'compound', 'push', 0.30),
  ('fly', 'Fly', 'Fly', '플라이', 'chest', '{}', '{handles}', false, 'bench', 'isolation', 'push', 0.45),
  ('inclineBenchPress', 'Incline Bench Press', 'Incline Bench Press', '인클라인 벤치 프레스', 'chest', '{triceps,shoulders}', '{barbell,handles}', false, 'bench', 'compound', 'push', 0.30),
  ('pullover', 'Pullover', 'Pullover', '풀오버', 'chest', '{back}', '{barbell,handles}', true, 'bench', 'compound', 'pull', 0.30),
  ('pushUp', 'Push Up', 'Push Up', '푸시 업', 'chest', '{triceps,shoulders}', '{}', false, NULL, 'compound', 'push', 0.30),
  ('squeezeBenchPress', 'Squeeze Bench Press', 'Squeeze Bench Press', '스퀴즈 벤치 프레스', 'chest', '{}', '{handles}', true, 'bench', 'compound', 'push', 0.30),
  -- Forearms (2)
  ('reverseWristCurl', 'Reverse Wrist Curl', 'Reverse Wrist Curl', '리버스 리스트 컬', 'forearms', '{}', '{barbell,handles,shortBar}', true, 'bench', 'isolation', 'pull', 0.45),
  ('wristCurl', 'Wrist Curl', 'Wrist Curl', '리스트 컬', 'forearms', '{}', '{barbell,handles,shortBar}', true, 'bench', 'isolation', 'pull', 0.45),
  -- Glutes (2)
  ('hipExtension', 'Hip Extension', 'Hip Extension', '힙 익스텐션', 'glutes', '{legs}', '{ankleStraps}', true, 'bench', 'isolation', 'hinge', 0.30),
  ('hipThrust', 'Hip Thrust', 'Hip Thrust', '힙 트러스트', 'glutes', '{}', '{barbell,waistBelt}', true, 'bench', 'compound', 'hinge', 0.20),
  -- Legs (7)
  ('bulgarianSplitSquat', 'Bulgarian Split Squat', 'Bulgarian Split Squat', '불가리안 스플릿 스쿼트', 'legs', '{glutes}', '{barbell,handles,waistBelt}', false, 'bench', 'compound', 'push', 0.30),
  ('legCurl', 'Leg Curl', 'Leg Curl', '레그컬', 'legs', '{}', '{ankleStraps}', true, 'bench', 'isolation', 'pull', 0.45),
  ('lunge', 'Lunge', 'Lunge', '런지', 'legs', '{glutes}', '{barbell,handles,waistBelt}', false, NULL, 'compound', 'push', 0.30),
  ('overheadSquat', 'Overhead Squat', 'Overhead Squat', '오버헤드 스쿼트', 'legs', '{glutes,shoulders}', '{barbell,handles}', false, NULL, 'compound', 'push', 0.30),
  ('romanianDeadlift', 'Romanian Deadlift', 'Romanian Deadlift', '루마니안 데드리프트', 'legs', '{glutes}', '{barbell,handles,shortBar}', false, NULL, 'compound', 'hinge', 0.20),
  ('squat', 'Squat', 'Squat', '스쿼트', 'legs', '{glutes}', '{barbell,handles,waistBelt}', false, NULL, 'compound', 'push', 0.30),
  ('stiffLegDeadlift', 'Stiff Leg Deadlift', 'Stiff Leg Deadlift', '스티프 레그 데드리프트', 'legs', '{glutes}', '{barbell,handles,shortBar}', false, NULL, 'compound', 'hinge', 0.20),
  -- Others / Core (6)
  ('cableCrunch', 'Weight Cable Crunch', 'Weight Cable Crunch', '중량 케이블 크런치', 'others', '{}', '{multiGrip}', true, 'tower', 'isolation', 'static', 0.10),
  ('cableStandingCrunch', 'Cable Standing Crunch', 'Cable Standing Crunch', '케이블 스탠딩 크런치', 'others', '{}', '{multiGrip}', true, 'tower', 'isolation', 'static', 0.10),
  ('cableTwist', 'Cable Twist', 'Cable Twist', '케이블 트위스트', 'others', '{}', '{handles}', true, 'tower', 'isolation', 'static', 0.10),
  ('hanging', 'Hanging', 'Hanging', '매달리기', 'others', '{back,shoulders}', '{}', false, 'tower', 'compound', 'static', 0.10),
  ('hangingLegRaise', 'Hanging Leg Raise', 'Hanging Leg Raise', '행잉 레그 레이즈', 'others', '{}', '{}', false, 'tower', 'compound', 'static', 0.10),
  ('plank', 'Plank', 'Plank', '플랭크', 'others', '{}', '{}', false, NULL, 'compound', 'static', 0.10),
  -- Shoulders (16)
  ('arnoldPress', 'Arnold Press', 'Arnold Press', '아놀드 프레스', 'shoulders', '{}', '{handles}', true, 'bench', 'compound', 'push', 0.30),
  ('backFly', 'Back Fly', 'Back Fly', '백 플라이', 'shoulders', '{}', '{handles}', true, 'bench', 'isolation', 'pull', 0.45),
  ('bentOverLateralRaise', 'Bent Over Lateral Raise', 'Bent Over Lateral Raise', '벤트 오버 레터럴 레이즈', 'shoulders', '{}', '{handles}', true, NULL, 'isolation', 'pull', 0.45),
  ('cableCrossoverReverseFly', 'Cable Crossover Reverse Fly', 'Cable Crossover Reverse Fly', '케이블 크로스오버 리버스 플라이', 'shoulders', '{back}', '{handles}', true, 'tower', 'isolation', 'pull', 0.45),
  ('cableFacePull', 'Cable Face Pull', 'Cable Face Pull', '케이블 페이스 풀', 'shoulders', '{back}', '{shortBar,multiGrip}', true, 'tower', 'isolation', 'pull', 0.45),
  ('frontRaise', 'Front Raise', 'Front Raise', '프론트 레이즈', 'shoulders', '{}', '{barbell,handles,shortBar,multiGrip}', true, NULL, 'isolation', 'push', 0.45),
  ('inclineShoulderPress', 'Incline Shoulder Press', 'Incline Shoulder Press', '인클라인 숄더 프레스', 'shoulders', '{}', '{barbell,handles}', true, 'bench', 'compound', 'push', 0.30),
  ('militaryPress', 'Military Press', 'Military Press', '밀리터리 프레스', 'shoulders', '{}', '{barbell,handles}', true, NULL, 'compound', 'push', 0.30),
  ('rearDeltRow', 'Rear Delt Row', 'Rear Delt Row', '리어 델트 로우', 'shoulders', '{}', '{barbell,handles}', true, NULL, 'isolation', 'pull', 0.45),
  ('seatedBehindNeckPress', 'Seated Behind Neck Press', 'Seated Behind Neck Press', '시티드 비하인드 넥 프레스', 'shoulders', '{}', '{barbell}', false, 'bench', 'compound', 'push', 0.30),
  ('seatedOverheadPress', 'Seated Overhead Press', 'Seated Overhead Press', '시티드 오버헤드 프레스', 'shoulders', '{}', '{barbell,handles}', false, 'bench', 'compound', 'push', 0.30),
  ('seatedShoulderPress', 'Seated Shoulder Press', 'Seated Shoulder Press', '시티드 숄더 프레스', 'shoulders', '{}', '{barbell,handles}', true, 'bench', 'compound', 'push', 0.30),
  ('sideLateralRaise', 'Side Lateral Raise', 'Side Lateral Raise', '사이드 레터럴 레이즈', 'shoulders', '{}', '{handles}', true, NULL, 'isolation', 'push', 0.45),
  ('standingArnoldPress', 'Standing Arnold Press', 'Standing Arnold Press', '스탠딩 아놀드 프레스', 'shoulders', '{}', '{handles}', true, NULL, 'compound', 'push', 0.30),
  ('thruster', 'Thruster', 'Thruster', '쓰러스터', 'shoulders', '{triceps,legs}', '{barbell,handles}', true, NULL, 'compound', 'push', 0.30),
  ('uprightRow', 'Upright Row', 'Upright Row', '업라이트 로우', 'shoulders', '{traps}', '{barbell,handles,shortBar,multiGrip}', true, NULL, 'compound', 'pull', 0.30),
  -- Traps (1)
  ('shrug', 'Shrug', 'Shrug', '슈러그', 'traps', '{}', '{barbell,handles,shortBar,multiGrip}', true, NULL, 'isolation', 'pull', 0.45),
  -- Triceps (5)
  ('cableTricepExtension', 'Cable Tricep Extension', 'Cable Tricep Extension', '케이블 트라이셉 익스텐션', 'triceps', '{}', '{barbell,handles,shortBar,multiGrip}', true, 'tower', 'isolation', 'push', 0.45),
  ('cableTricepPushDown', 'Cable Tricep Push Down', 'Cable Tricep Push Down', '케이블 트라이셉 푸시 다운', 'triceps', '{}', '{barbell,handles,shortBar,multiGrip}', true, 'tower', 'isolation', 'push', 0.45),
  ('overheadTricepExtension', 'Cable Overhead Tricep Extension', 'Cable Overhead Tricep Extension', '케이블 오버헤드 트라이셉 익스텐션', 'triceps', '{}', '{shortBar,multiGrip}', true, 'tower', 'isolation', 'push', 0.45),
  ('tricepKickBack', 'Tricep Kick Back', 'Tricep Kick Back', '트라이셉 킥 백', 'triceps', '{}', '{handles}', true, NULL, 'isolation', 'push', 0.45),
  ('tricepsExtension', 'Triceps Extension', 'Triceps Extension', '트라이셉스 익스텐션', 'triceps', '{}', '{barbell,handles}', false, 'bench', 'compound', 'push', 0.30)
ON CONFLICT (exercise_id) DO UPDATE SET
  name_en              = EXCLUDED.name_en,
  name_ko              = EXCLUDED.name_ko,
  primary_region       = EXCLUDED.primary_region,
  secondary_regions    = EXCLUDED.secondary_regions,
  default_grips        = EXCLUDED.default_grips,
  is_start_lengthening = EXCLUDED.is_start_lengthening,
  add_on               = EXCLUDED.add_on,
  display_name    = COALESCE(public.exercise_metadata.display_name,    EXCLUDED.display_name),
  mechanics       = COALESCE(public.exercise_metadata.mechanics,       EXCLUDED.mechanics),
  force_type      = COALESCE(public.exercise_metadata.force_type,      EXCLUDED.force_type),
  default_mvt_mps = COALESCE(public.exercise_metadata.default_mvt_mps, EXCLUDED.default_mvt_mps);

-- Helpful indexes for dashboard joins
CREATE INDEX IF NOT EXISTS exercise_metadata_primary_region_idx ON public.exercise_metadata (primary_region);
CREATE INDEX IF NOT EXISTS exercise_metadata_body_part_group_idx ON public.exercise_metadata (body_part_group);

COMMENT ON COLUMN public.exercise_metadata.body_part_group IS
  'Derived push/pull/legs/core grouping for dashboard donuts. Computed from primary_region.';
