-- RoomfitV2 Workout Data Schema
-- B2C/B2B 공통. roomfit_exercise 엔진의 CompletedRep/ExerciseSet 1:1 대응.

-- ============================================================
-- Enums
-- ============================================================

CREATE TYPE weight_mode AS ENUM (
  'normal',       -- 0: constant resistance
  'eccentric',    -- 1: eccentric overload
  'elastic',      -- 2: position-proportional
  'isokinetic',   -- 3: speed-clamped
  'hydraulic',    -- 4: speed^n power law
  'vibration'     -- 5: sinusoidal oscillation
);

CREATE TYPE exercise_side AS ENUM ('left', 'right');

CREATE TYPE set_type AS ENUM ('normal', 'drop');

CREATE TYPE training_goal AS ENUM ('power', 'strength', 'hypertrophy', 'endurance');

-- ============================================================
-- Core Tables
-- ============================================================

-- Session: 1회 방문
CREATE TABLE workout_sessions (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           uuid NOT NULL REFERENCES auth.users(id),
  device_id         text,
  firmware_version  text,
  started_at        timestamptz NOT NULL DEFAULT now(),
  ended_at          timestamptz,
  notes             text,
  rating            smallint CHECK (rating BETWEEN 1 AND 5),
  created_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_sessions_user ON workout_sessions(user_id, started_at DESC);

-- Exercise Entry: 세션 내 운동 종목 단위
CREATE TABLE exercise_entries (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id      uuid NOT NULL REFERENCES workout_sessions(id) ON DELETE CASCADE,
  exercise_id     text NOT NULL,
  grip_id         text,
  is_unilateral   boolean NOT NULL DEFAULT false,
  entry_order     smallint NOT NULL,
  weight_mode     weight_mode NOT NULL DEFAULT 'normal',
  training_goal   training_goal,
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_entries_session ON exercise_entries(session_id, entry_order);

-- Set: side별 독립, set-level VBT 집계 포함
CREATE TABLE exercise_sets (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entry_id        uuid NOT NULL REFERENCES exercise_entries(id) ON DELETE CASCADE,
  side            exercise_side NOT NULL,
  set_index       smallint NOT NULL,
  started_at      timestamptz NOT NULL,
  ended_at        timestamptz NOT NULL,

  -- Config (의도)
  set_type        set_type NOT NULL DEFAULT 'normal',
  target_weight_kg real,
  target_reps     smallint,
  drop_set_config jsonb,

  -- Result (실제)
  rep_count       smallint NOT NULL,
  weight_kg       real NOT NULL,
  weight_mode     weight_mode NOT NULL,

  -- Eccentric overload
  ecc_level_l     smallint,
  ecc_level_r     smallint,

  -- VBT set metrics
  avg_mcv_mmps           real,
  peak_velocity_mmps     real,
  velocity_loss_pct      real,
  total_tut_ms           int,
  avg_rom_mm             real,

  -- ROM calibration snapshot
  rom_min_mm             real,
  rom_max_mm             real,
  rom_confidence         real,

  -- User feedback
  memo            text,

  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_sets_entry ON exercise_sets(entry_id, set_index);

-- Rep: CompletedRep 1:1 매핑, full VBT metrics
CREATE TABLE reps (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  set_id          uuid NOT NULL REFERENCES exercise_sets(id) ON DELETE CASCADE,
  side            exercise_side NOT NULL,
  rep_index       smallint NOT NULL,
  started_at      timestamptz NOT NULL,
  ended_at        timestamptz NOT NULL,

  -- Weight context
  weight_mode     weight_mode NOT NULL,
  weight_kg       real NOT NULL,

  -- Timing (ms)
  duration_ms          int NOT NULL,
  concentric_ms        int NOT NULL,
  eccentric_ms         int NOT NULL,
  turnaround_ms        int NOT NULL,

  -- ROM (mm)
  rom_mm               real NOT NULL,
  concentric_rom_mm    real NOT NULL,
  eccentric_rom_mm     real NOT NULL,

  -- Velocity (mm/s) — NULL when velocityMeaningful = false
  mean_conc_velocity   real,
  peak_conc_velocity   real,
  mean_ecc_velocity    real,
  peak_ecc_velocity    real,

  -- Force (kg)
  mean_conc_force      real,
  peak_conc_force      real,
  mean_ecc_force       real,
  peak_ecc_force       real,

  -- Power (W)
  mean_conc_power      real,
  peak_conc_power      real,

  -- Time Under Tension
  tut_ms               int NOT NULL,
  load_weighted_tut    real NOT NULL,

  -- Roomfit-specific
  mean_conc_icmd_ma    real,
  peak_conc_icmd_ma    real,
  region_transitions   smallint DEFAULT 0,
  icmd_fload_error_pct real,

  -- Position extrema
  valley_position_mm   real,
  peak_position_mm     real,

  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_reps_set ON reps(set_id, rep_index);

-- ROM Estimates: AutoRomLearner output, entry당 side당 1개
CREATE TABLE rom_estimates (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entry_id         uuid NOT NULL REFERENCES exercise_entries(id) ON DELETE CASCADE,
  side             exercise_side NOT NULL,
  position_min_mm  real NOT NULL,
  position_max_mm  real NOT NULL,
  rom_mm           real NOT NULL,
  rep_count        smallint NOT NULL,
  confidence       real NOT NULL,
  captured_at      timestamptz NOT NULL DEFAULT now(),

  UNIQUE(entry_id, side)
);

-- ============================================================
-- Optional: Raw Motion Samples (50ms, replay/분석용)
-- ============================================================

CREATE TABLE raw_motion_samples (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  session_id    uuid NOT NULL REFERENCES workout_sessions(id) ON DELETE CASCADE,
  time_ms       int NOT NULL,

  position_l real, speed_l real, accel_l real,
  icmd_l real, ifb_l real, fload_l real,
  weight_mode_l smallint, region_l smallint, weight_set_l real,

  position_r real, speed_r real, accel_r real,
  icmd_r real, ifb_r real, fload_r real,
  weight_mode_r smallint, region_r smallint, weight_set_r real,

  voltage real,
  drive_status smallint
);

CREATE INDEX idx_raw_session_time ON raw_motion_samples(session_id, time_ms);

-- ============================================================
-- B2B: Center / Membership
-- ============================================================

CREATE TABLE centers (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL,
  owner_id    uuid NOT NULL REFERENCES auth.users(id),
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE center_memberships (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  center_id   uuid NOT NULL REFERENCES centers(id) ON DELETE CASCADE,
  user_id     uuid NOT NULL REFERENCES auth.users(id),
  role        text NOT NULL DEFAULT 'member',
  joined_at   timestamptz NOT NULL DEFAULT now(),

  UNIQUE(center_id, user_id)
);

CREATE INDEX idx_membership_center ON center_memberships(center_id);
CREATE INDEX idx_membership_user ON center_memberships(user_id);
