-- ============================================================================
-- B2B RLS hardening (SEC-01 + DB-01)
-- 4-agent scenario review revealed two critical RLS gaps:
--
--   SEC-01  workout_sessions UPDATE WITH CHECK does not freeze
--           user_id / trainer_id / center_id / session_type, so any
--           trainer with auth on the row could "transfer" a session
--           to another member or relabel a supervised session as self.
--
--   DB-01   load_velocity_profile / daily_wellness / rep_detection_run
--           policies allow only `user_id = auth.uid()`. During a
--           supervised session auth.uid() is the trainer, so writes
--           against the member's user_id are rejected and the BP
--           analytics tables stay empty.
--
-- This migration:
--   1. Adds a BEFORE UPDATE trigger on workout_sessions that raises
--      on any change to the four immutable columns.
--   2. Replaces the LVP / wellness / rep_detection_run policies with
--      ones that recognize the supervising trainer (or any same-shop
--      staff for read).
--
-- Helper functions used: is_shop_staff(uuid,uuid) and
-- shared_shop_with_member(uuid,uuid) — defined in
-- 20260419004009_b2b_shop_hierarchy.sql.
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. SEC-01 — workout_sessions immutable columns trigger
-- ----------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION enforce_session_immutable_columns()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.user_id IS DISTINCT FROM OLD.user_id THEN
    RAISE EXCEPTION 'workout_sessions.user_id is immutable'
      USING ERRCODE = 'check_violation';
  END IF;
  IF NEW.trainer_id IS DISTINCT FROM OLD.trainer_id THEN
    RAISE EXCEPTION 'workout_sessions.trainer_id is immutable'
      USING ERRCODE = 'check_violation';
  END IF;
  IF NEW.center_id IS DISTINCT FROM OLD.center_id THEN
    RAISE EXCEPTION 'workout_sessions.center_id is immutable'
      USING ERRCODE = 'check_violation';
  END IF;
  IF NEW.session_type IS DISTINCT FROM OLD.session_type THEN
    RAISE EXCEPTION 'workout_sessions.session_type is immutable'
      USING ERRCODE = 'check_violation';
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_sessions_immutable ON workout_sessions;
CREATE TRIGGER trg_sessions_immutable
  BEFORE UPDATE ON workout_sessions
  FOR EACH ROW EXECUTE FUNCTION enforce_session_immutable_columns();

COMMENT ON FUNCTION enforce_session_immutable_columns() IS
  'Blocks UPDATE of user_id / trainer_id / center_id / session_type on workout_sessions. RLS WITH CHECK is enforced only at INSERT, so this trigger plugs the post-INSERT mutation gap (SEC-01).';

-- ----------------------------------------------------------------------------
-- 2. DB-01 — load_velocity_profile staff-aware policies
--    Read: member self OR same-shop staff (owner/trainer)
--    Write: member self OR supervising trainer (shared_shop_with_member)
-- ----------------------------------------------------------------------------

DROP POLICY IF EXISTS lvp_own_row ON load_velocity_profile;

CREATE POLICY lvp_read_self_or_staff ON load_velocity_profile
  FOR SELECT TO authenticated
  USING (
    user_id = auth.uid()
    OR shared_shop_with_member(auth.uid(), user_id)
  );

CREATE POLICY lvp_write_self_or_supervising_trainer
  ON load_velocity_profile
  FOR INSERT TO authenticated
  WITH CHECK (
    user_id = auth.uid()
    OR shared_shop_with_member(auth.uid(), user_id)
  );

CREATE POLICY lvp_update_self_or_supervising_trainer
  ON load_velocity_profile
  FOR UPDATE TO authenticated
  USING (
    user_id = auth.uid()
    OR shared_shop_with_member(auth.uid(), user_id)
  )
  WITH CHECK (
    user_id = auth.uid()
    OR shared_shop_with_member(auth.uid(), user_id)
  );

CREATE POLICY lvp_delete_self
  ON load_velocity_profile
  FOR DELETE TO authenticated
  USING (user_id = auth.uid());

-- ----------------------------------------------------------------------------
-- 3. DB-01 — daily_wellness: read for staff, write only by self.
--    Wellness is sensitive personal data (sleep, stress, soreness)
--    — staff can READ to inform programming but cannot mutate.
-- ----------------------------------------------------------------------------

DROP POLICY IF EXISTS wellness_own_row ON daily_wellness;

CREATE POLICY wellness_read_self_or_staff ON daily_wellness
  FOR SELECT TO authenticated
  USING (
    user_id = auth.uid()
    OR shared_shop_with_member(auth.uid(), user_id)
  );

CREATE POLICY wellness_write_self
  ON daily_wellness
  FOR INSERT TO authenticated
  WITH CHECK (user_id = auth.uid());

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

CREATE POLICY wellness_delete_self
  ON daily_wellness
  FOR DELETE TO authenticated
  USING (user_id = auth.uid());

-- ----------------------------------------------------------------------------
-- 4. DB-01 — rep_detection_run: extend the via-set policy so the
--    supervising trainer (and same-shop staff) can read/write detector
--    output. The set itself already lives under workout_sessions whose
--    RLS allows the same population to SELECT, so the model stays
--    consistent.
-- ----------------------------------------------------------------------------

DROP POLICY IF EXISTS rep_runs_via_set ON rep_detection_run;

CREATE POLICY rep_runs_read_via_set ON rep_detection_run
  FOR SELECT 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()
          OR ws.trainer_id = auth.uid()
          OR (ws.center_id IS NOT NULL
              AND is_shop_staff(auth.uid(), ws.center_id))
        )
    )
  );

CREATE POLICY rep_runs_write_via_set ON rep_detection_run
  FOR INSERT TO authenticated
  WITH CHECK (
    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()
          OR ws.trainer_id = auth.uid()
        )
    )
  );

CREATE POLICY rep_runs_update_via_set ON rep_detection_run
  FOR UPDATE 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()
          OR ws.trainer_id = auth.uid()
        )
    )
  )
  WITH CHECK (
    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()
          OR ws.trainer_id = auth.uid()
        )
    )
  );

CREATE POLICY rep_runs_delete_via_set ON rep_detection_run
  FOR DELETE 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()
    )
  );
