-- VBT 분석 계층 확장: BP(GymAware/Vitruve) 기준 메트릭 + 피로/부하 추적
-- Ref: BP 리서치 리포트 2026-04-15

-- ============================================================================
-- 1. reps 테이블에 BP 고급 메트릭 추가
-- ============================================================================
-- MPV (Mean Propulsive Velocity): concentric 중 accel > -g 구간만 평균.
-- Compound lift에서 load < 70% 1RM일 때 MCV보다 신호 품질 높음.
ALTER TABLE reps
  ADD COLUMN mean_prop_velocity  real,                 -- mm/s
  ADD COLUMN concentric_work_cj  real,                 -- centijoule, ∫F·ds over concentric
  ADD COLUMN eccentric_work_cj   real,                 -- centijoule
  ADD COLUMN concentric_impulse  real,                 -- N·s = ∫F·dt
  ADD COLUMN rfd_initial_100ms   real,                 -- N/s during first 100ms of concentric
  ADD COLUMN icmd_ifb_rms_error_ma real;               -- control-loop 품질 (Roomfit 고유)

COMMENT ON COLUMN reps.mean_prop_velocity IS 'MPV in mm/s. Concentric 중 accel > -9.81 m/s² 구간만 평균. NULL when vbt 모드 아님.';
COMMENT ON COLUMN reps.concentric_impulse IS 'Impulse(N·s) = ∫F·dt. Ballistic movement에서 power보다 의미있음.';
COMMENT ON COLUMN reps.rfd_initial_100ms IS 'Rate of Force Development 초기 100ms. 신경근 적응 지표. Roomfit icmd ground truth 기반.';

-- ============================================================================
-- 2. exercise_sets 테이블에 피로 + e1RM + auto-cutoff 필드
-- ============================================================================
ALTER TABLE exercise_sets
  ADD COLUMN rpe                 smallint CHECK (rpe BETWEEN 1 AND 10),      -- Zourdos 1-10
  ADD COLUMN rir                 smallint CHECK (rir BETWEEN 0 AND 10),       -- Reps in Reserve
  ADD COLUMN rest_before_ms      int,                                          -- 직전 set 종료 ↔ 이 set 시작
  ADD COLUMN volume_load_kg      real,                                         -- weight × reps
  ADD COLUMN estimated_1rm_kg    real,                                         -- LVP × MVT 역산
  ADD COLUMN target_rep_count    smallint,                                     -- 사용자가 의도한 rep 수 (QA용)
  ADD COLUMN cutoff_trigger      text CHECK (cutoff_trigger IN (
    'user','velocity_loss_20','velocity_loss_30','velocity_loss_40',
    'target_reached','timeout'
  )),                                                                           -- 세트 종료 사유
  ADD COLUMN warmup_detected     boolean DEFAULT false;                         -- LVP 재계산 대상

COMMENT ON COLUMN exercise_sets.rpe IS 'Rate of Perceived Exertion (Zourdos 1-10). RPE 10 = 0 RIR.';
COMMENT ON COLUMN exercise_sets.cutoff_trigger IS 'velocity_loss_N: N% 초과 2연속. target_reached: 계획 rep 달성. user: 수동 종료.';

CREATE INDEX idx_sets_warmup ON exercise_sets(entry_id) WHERE warmup_detected;

-- ============================================================================
-- 3. workout_sessions에 sRPE + mood + duration
-- ============================================================================
ALTER TABLE workout_sessions
  ADD COLUMN duration_min        int,                                          -- started_at ↔ ended_at 분 단위
  ADD COLUMN session_rpe         smallint CHECK (session_rpe BETWEEN 1 AND 10),
  ADD COLUMN mood                text CHECK (mood IN ('great','good','ok','tired','bad')),
  ADD COLUMN srpe_load           int GENERATED ALWAYS AS (
    COALESCE(session_rpe, 0) * COALESCE(duration_min, 0)
  ) STORED,                                                                     -- Foster sRPE × duration
  ADD COLUMN total_volume_load_kg real,                                         -- Σ(exercise_sets.volume_load_kg)
  ADD COLUMN total_work_kj       real,                                          -- Σ(reps.concentric_work) / 100000
  ADD COLUMN mean_vl_pct         real,                                          -- 세션 평균 velocity_loss
  ADD COLUMN nm_readiness        real;                                          -- 첫 warm-up MCV / 7일 rolling baseline

COMMENT ON COLUMN workout_sessions.srpe_load IS 'Foster method session RPE × duration (AU). Weekly sum 으로 training load 집계.';
COMMENT ON COLUMN workout_sessions.nm_readiness IS '신경근 준비도 = warmup_mcv / baseline_mcv. <0.93 → deload 권장.';

-- ============================================================================
-- 4. daily_wellness (Hooper Index)
-- ============================================================================
CREATE TABLE daily_wellness (
  user_id           uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  date              date NOT NULL,
  hooper_sleep      smallint NOT NULL CHECK (hooper_sleep BETWEEN 1 AND 7),
  hooper_fatigue    smallint NOT NULL CHECK (hooper_fatigue BETWEEN 1 AND 7),
  hooper_soreness   smallint NOT NULL CHECK (hooper_soreness BETWEEN 1 AND 7),
  hooper_stress     smallint NOT NULL CHECK (hooper_stress BETWEEN 1 AND 7),
  hooper_total      smallint GENERATED ALWAYS AS (
    hooper_fatigue + hooper_soreness + hooper_stress + (8 - hooper_sleep)
  ) STORED,
  note              text,
  created_at        timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, date)
);

COMMENT ON TABLE daily_wellness IS 'Hooper Index (4×1-7). hooper_total 범위 4-28, 높을수록 나쁨. HRV보다 민감하다는 2019 연구 근거.';

CREATE INDEX idx_wellness_user_date ON daily_wellness(user_id, date DESC);

-- ============================================================================
-- 5. load_velocity_profile (세션별 LVP snapshot)
-- ============================================================================
CREATE TABLE load_velocity_profile (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  exercise_id       text NOT NULL,
  side              exercise_side NOT NULL,

  slope             real NOT NULL,                                              -- m/s per kg (음수)
  intercept         real NOT NULL,                                              -- m/s at load=0 (= V0)
  v0_mps            real NOT NULL,                                              -- intercept alias
  l0_kg             real NOT NULL,                                              -- -intercept/slope (MCV=0 extrapolation)
  mvt_used          real NOT NULL,                                              -- 사용한 MVT (종목별 상수)
  estimated_1rm_kg  real NOT NULL,                                              -- (mvt - intercept) / slope
  r_squared         real,                                                        -- 적합도
  n_data_points     smallint NOT NULL,
  method            text NOT NULL CHECK (method IN ('2pt','multipoint','ml')),
  velocity_metric   text NOT NULL DEFAULT 'mcv' CHECK (velocity_metric IN ('mcv','mpv','pcv')),

  computed_at       timestamptz NOT NULL DEFAULT now(),
  session_id        uuid REFERENCES workout_sessions(id) ON DELETE SET NULL,
  drift_from_previous real                                                      -- slope 변화율 (vs 직전 LVP)
);

CREATE INDEX idx_lvp_user_exercise_time ON load_velocity_profile(
  user_id, exercise_id, side, computed_at DESC
);

COMMENT ON TABLE load_velocity_profile IS '세션 단위 LVP snapshot. Warm-up sets로부터 자동 재계산. drift 모니터링용.';

-- ============================================================================
-- 6. exercise_metadata (종목별 MVT + transfer coefficient — lean taxonomy)
-- ============================================================================
-- 전체 exercise taxonomy (muscle, role 등)는 app bundle에 두고,
-- 서버에서 쿼리해야 하는 수치 파라미터만 DB에.
CREATE TABLE exercise_metadata (
  exercise_id          text PRIMARY KEY,
  display_name         text NOT NULL,
  mechanics            text CHECK (mechanics IN ('compound','isolation')),
  force_type           text CHECK (force_type IN ('push','pull','static','hinge')),
  default_mvt_mps      real NOT NULL,                                           -- Minimum Velocity Threshold
  mcv_at_1rm_mps       real,                                                     -- 업계 평균 MCV at 100% 1RM
  transfer_parent_id   text REFERENCES exercise_metadata(exercise_id),
  transfer_coefficient real CHECK (transfer_coefficient BETWEEN 0.5 AND 1.0),   -- parent_1rm × coef
  updated_at           timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE exercise_metadata IS '종목별 MVT (e.g. bench 0.17, squat 0.30) + 종목간 1RM transfer coefficient.';

-- Seed: 주요 종목 MVT (문헌 평균치)
INSERT INTO exercise_metadata (exercise_id, display_name, mechanics, force_type, default_mvt_mps, mcv_at_1rm_mps) VALUES
  ('barbell-bench-press',   'Barbell Bench Press',   'compound', 'push', 0.170, 0.170),
  ('barbell-back-squat',    'Barbell Back Squat',    'compound', 'push', 0.300, 0.300),
  ('barbell-deadlift',      'Barbell Deadlift',      'compound', 'pull', 0.150, 0.150),
  ('barbell-overhead-press','Barbell Overhead Press','compound', 'push', 0.200, 0.200),
  ('barbell-bent-row',      'Barbell Bent Row',      'compound', 'pull', 0.250, 0.250);

-- ============================================================================
-- 7. RLS 정책 — 신규 테이블
-- ============================================================================
ALTER TABLE daily_wellness ENABLE ROW LEVEL SECURITY;
ALTER TABLE load_velocity_profile ENABLE ROW LEVEL SECURITY;
ALTER TABLE exercise_metadata ENABLE ROW LEVEL SECURITY;

CREATE POLICY wellness_own_row ON daily_wellness
  FOR ALL TO authenticated
  USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

CREATE POLICY lvp_own_row ON load_velocity_profile
  FOR ALL TO authenticated
  USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

CREATE POLICY exercise_metadata_read ON exercise_metadata
  FOR SELECT TO authenticated USING (true);

-- ============================================================================
-- 8. ACWR materialized view (EWMA 모델)
-- ============================================================================
-- λ_acute = 2/(7+1) = 0.25, λ_chronic = 2/(28+1) ≈ 0.0690
-- EWMA_t = load_t × λ + EWMA_{t-1} × (1-λ)
-- Sweet spot: 0.8-1.3, >1.5 위험.
--
-- Materialized view로 구현 (nightly refresh). 실시간 필요없음.
CREATE MATERIALIZED VIEW user_daily_load AS
SELECT
  s.user_id,
  s.started_at::date AS day,
  SUM(s.srpe_load)                 AS total_srpe_load,
  SUM(s.total_volume_load_kg)      AS total_volume_load_kg,
  SUM(s.total_work_kj)             AS total_work_kj,
  AVG(s.mean_vl_pct)               AS avg_vl_pct,
  COUNT(*)                         AS session_count
FROM workout_sessions s
WHERE s.ended_at IS NOT NULL
GROUP BY s.user_id, s.started_at::date;

CREATE UNIQUE INDEX idx_user_daily_load ON user_daily_load(user_id, day);

-- ACWR는 view로 (daily_load 위에서 EWMA SQL window 로 계산)
-- Postgres native EWMA는 recursive CTE로 가능하나 비용 크므로 Edge Function에서 nightly 계산 권장.
-- 이 migration에서는 스키마만 준비, 계산 로직은 앱 측에서.

-- ============================================================================
-- 9. Rep detector QA (shadow mode A/B infrastructure)
-- ============================================================================
CREATE TABLE rep_detection_run (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  set_id               uuid NOT NULL REFERENCES exercise_sets(id) ON DELETE CASCADE,
  detector_version     text NOT NULL,
  detector_config      jsonb,
  detected_rep_count   int NOT NULL,
  detected_reps        jsonb NOT NULL,                                          -- [{start_ms, end_ms, mcv, ...}, ...]
  target_rep_count     smallint,
  user_confirmed_count smallint,                                                 -- post-set UI 응답
  ran_at               timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_rep_runs_set ON rep_detection_run(set_id);
CREATE INDEX idx_rep_runs_version ON rep_detection_run(detector_version, ran_at DESC);

ALTER TABLE rep_detection_run ENABLE ROW LEVEL SECURITY;
CREATE POLICY rep_runs_via_set ON rep_detection_run
  FOR ALL TO authenticated
  USING (EXISTS (
    SELECT 1 FROM exercise_sets es
    JOIN exercise_entries ee ON es.entry_id = ee.id
    JOIN workout_sessions ws ON ee.session_id = ws.id
    WHERE es.id = rep_detection_run.set_id AND ws.user_id = auth.uid()
  ));
