-- Dashboard convenience view: exercise_sets joined with entry/session
-- context + on-the-fly PR detection. Warmup sets are excluded from the
-- PR comparison window so a "new max" really means a working-set PR.
-- security_invoker = true forces the view to honor the RLS policies on
-- exercise_sets / entries / sessions.

CREATE OR REPLACE VIEW public.exercise_sets_with_pr
WITH (security_invoker = true) AS
SELECT
  es.id,
  es.entry_id,
  e.session_id,
  e.exercise_id,
  e.entry_order,
  s.user_id,
  s.trainer_id,
  s.center_id,
  s.session_type,
  s.started_at AS session_started_at,
  es.side,
  es.set_index,
  es.started_at,
  es.ended_at,
  es.set_type,
  es.weight_kg,
  es.rep_count,
  es.weight_mode,
  es.avg_mcv_mmps,
  es.velocity_loss_pct,
  es.rpe,
  es.rir,
  es.volume_load_kg,
  es.estimated_1rm_kg,
  es.cutoff_trigger,
  COALESCE(es.warmup_detected, false) AS warmup_detected,
  -- Epley-derived e1RM (used if estimated_1rm_kg is NULL)
  COALESCE(
    es.estimated_1rm_kg,
    es.weight_kg * (1 + es.rep_count::real / 30.0)
  ) AS e1rm_kg,
  -- is_weight_pr: strict weight PR among WORKING sets only.
  -- warmup sets always false; working sets compare against MAX(weight_kg)
  -- over all PRIOR working sets for the same (user, exercise_id).
  CASE
    WHEN COALESCE(es.warmup_detected, false) THEN false
    ELSE es.weight_kg > COALESCE(
      MAX(CASE WHEN COALESCE(es.warmup_detected, false) THEN NULL ELSE es.weight_kg END) OVER (
        PARTITION BY s.user_id, e.exercise_id
        ORDER BY s.started_at, es.set_index, es.side
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
      ),
      0
    )
  END AS is_weight_pr,
  -- is_e1rm_pr: same logic on estimated_1rm_kg (catches rep PRs at the
  -- same weight). Falls back to Epley if estimated_1rm_kg is NULL.
  CASE
    WHEN COALESCE(es.warmup_detected, false) THEN false
    ELSE
      COALESCE(es.estimated_1rm_kg, es.weight_kg * (1 + es.rep_count::real / 30.0))
      >
      COALESCE(
        MAX(CASE
              WHEN COALESCE(es.warmup_detected, false) THEN NULL
              ELSE COALESCE(es.estimated_1rm_kg, es.weight_kg * (1 + es.rep_count::real / 30.0))
            END) OVER (
          PARTITION BY s.user_id, e.exercise_id
          ORDER BY s.started_at, es.set_index, es.side
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
      )
  END AS is_e1rm_pr
FROM public.exercise_sets es
JOIN public.exercise_entries e ON e.id = es.entry_id
JOIN public.workout_sessions s ON s.id = e.session_id;

GRANT SELECT ON public.exercise_sets_with_pr TO authenticated;

COMMENT ON VIEW public.exercise_sets_with_pr IS
  'Dashboard join: exercise_sets + entry + session + on-the-fly PR flags (weight PR, e1RM PR). Honors RLS via security_invoker.';

-- Convenience view for the recent-sessions timeline (left column of the
-- personal dashboard). One row per (session, exercise), with working-set
-- list as a JSONB array so clients don't need to aggregate.
CREATE OR REPLACE VIEW public.session_exercise_summary
WITH (security_invoker = true) AS
SELECT
  s.id                 AS session_id,
  s.user_id,
  s.trainer_id,
  s.center_id,
  s.session_type,
  s.started_at,
  s.ended_at,
  s.duration_min,
  s.session_rpe,
  s.mood,
  s.total_volume_load_kg,
  e.id                 AS entry_id,
  e.exercise_id,
  em.name_ko           AS exercise_name_ko,
  em.primary_region,
  em.body_part_group,
  em.mechanics,
  e.entry_order,
  e.weight_mode,
  e.training_goal,
  -- Working sets (non-warmup), ordered by set_index/side, as JSONB for
  -- compact client-side rendering.
  COALESCE((
    SELECT jsonb_agg(
      jsonb_build_object(
        'set_index', es.set_index,
        'side',      es.side,
        'weight_kg', es.weight_kg,
        'rep_count', es.rep_count,
        'rpe',       es.rpe,
        'e1rm_kg',   COALESCE(es.estimated_1rm_kg, es.weight_kg * (1 + es.rep_count::real / 30.0))
      )
      ORDER BY es.set_index, es.side
    )
    FROM public.exercise_sets es
    WHERE es.entry_id = e.id AND COALESCE(es.warmup_detected, false) = false
  ), '[]'::jsonb) AS working_sets,
  -- Warmup count
  (SELECT count(*) FROM public.exercise_sets es
     WHERE es.entry_id = e.id AND COALESCE(es.warmup_detected, false) = true) AS warmup_set_count,
  -- Max working weight (session-level PR quick check)
  (SELECT max(es.weight_kg) FROM public.exercise_sets es
     WHERE es.entry_id = e.id AND COALESCE(es.warmup_detected, false) = false) AS max_working_weight_kg,
  -- Any PR in this entry
  EXISTS (
    SELECT 1 FROM public.exercise_sets_with_pr ep
    WHERE ep.entry_id = e.id AND (ep.is_weight_pr OR ep.is_e1rm_pr)
  ) AS has_pr
FROM public.workout_sessions s
JOIN public.exercise_entries e ON e.session_id = s.id
LEFT JOIN public.exercise_metadata em ON em.exercise_id = e.exercise_id;

GRANT SELECT ON public.session_exercise_summary TO authenticated;

COMMENT ON VIEW public.session_exercise_summary IS
  'Per-(session, exercise) dashboard row with denormalized name/region and working_sets jsonb.';
