-- =============================================================================
-- B2B Shop Hierarchy — v0
--
-- - membership_role enum (owner/trainer/member); one owner per center
-- - workout_sessions: trainer_id / center_id / session_type (+check)
-- - pending_invitations (shadow account tracking)
-- - Helper functions: is_shop_staff, shared_shop_with_member
-- - RLS extended on workout tree + centers/memberships/invitations
--
-- Reference: docs/b2b/rls.md
-- =============================================================================

BEGIN;

-- -----------------------------------------------------------------------------
-- 0. citext (email UNIQUE with case-insensitive comparison)
-- -----------------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS citext;

-- -----------------------------------------------------------------------------
-- 1. membership_role enum
-- -----------------------------------------------------------------------------
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'membership_role') THEN
    CREATE TYPE membership_role AS ENUM ('owner', 'trainer', 'member');
  END IF;
END$$;

-- existing center_memberships.role (text) → enum
ALTER TABLE center_memberships
  ALTER COLUMN role DROP DEFAULT,
  ALTER COLUMN role TYPE membership_role USING role::membership_role,
  ALTER COLUMN role SET DEFAULT 'member';

-- owner per center must be unique
CREATE UNIQUE INDEX IF NOT EXISTS one_owner_per_center
  ON center_memberships(center_id) WHERE role = 'owner';

-- -----------------------------------------------------------------------------
-- 2. workout_sessions — trainer/center/session_type
-- -----------------------------------------------------------------------------
ALTER TABLE workout_sessions
  ADD COLUMN IF NOT EXISTS trainer_id   uuid REFERENCES auth.users(id),
  ADD COLUMN IF NOT EXISTS center_id    uuid REFERENCES centers(id),
  ADD COLUMN IF NOT EXISTS session_type text NOT NULL DEFAULT 'self';

ALTER TABLE workout_sessions
  DROP CONSTRAINT IF EXISTS session_type_check,
  ADD CONSTRAINT session_type_check
    CHECK (session_type IN ('self', 'supervised'));

ALTER TABLE workout_sessions
  DROP CONSTRAINT IF EXISTS supervised_requires_trainer,
  ADD CONSTRAINT supervised_requires_trainer
    CHECK (session_type = 'self'
           OR (trainer_id IS NOT NULL AND center_id IS NOT NULL));

CREATE INDEX IF NOT EXISTS idx_sessions_trainer
  ON workout_sessions(trainer_id, started_at DESC)
  WHERE trainer_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_sessions_center
  ON workout_sessions(center_id, started_at DESC)
  WHERE center_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- 3. pending_invitations (shadow account bookkeeping)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pending_invitations (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  center_id       uuid NOT NULL REFERENCES centers(id) ON DELETE CASCADE,
  email           citext NOT NULL,
  role            membership_role NOT NULL,
  invited_by      uuid NOT NULL REFERENCES auth.users(id),
  shadow_user_id  uuid REFERENCES auth.users(id),
  created_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE(center_id, email)
);

CREATE INDEX IF NOT EXISTS idx_invitations_center ON pending_invitations(center_id);
CREATE INDEX IF NOT EXISTS idx_invitations_shadow ON pending_invitations(shadow_user_id)
  WHERE shadow_user_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- 4. Helper functions
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_shop_staff(_user_id uuid, _center_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp AS $$
  SELECT EXISTS (
    SELECT 1 FROM center_memberships
    WHERE user_id = _user_id
      AND center_id = _center_id
      AND role IN ('owner','trainer')
  );
$$;

CREATE OR REPLACE FUNCTION shared_shop_with_member(_staff_id uuid, _member_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp AS $$
  SELECT EXISTS (
    SELECT 1
    FROM center_memberships sm
    JOIN center_memberships mm ON mm.center_id = sm.center_id
    WHERE sm.user_id = _staff_id
      AND sm.role IN ('owner','trainer')
      AND mm.user_id = _member_id
      AND mm.role = 'member'
  );
$$;

REVOKE ALL ON FUNCTION is_shop_staff(uuid, uuid) FROM PUBLIC;
REVOKE ALL ON FUNCTION shared_shop_with_member(uuid, uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION is_shop_staff(uuid, uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION shared_shop_with_member(uuid, uuid) TO authenticated;

-- -----------------------------------------------------------------------------
-- 5. RLS — workout_sessions
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS sessions_self ON workout_sessions;
DROP POLICY IF EXISTS sessions_read ON workout_sessions;
DROP POLICY IF EXISTS sessions_insert ON workout_sessions;
DROP POLICY IF EXISTS sessions_update ON workout_sessions;
DROP POLICY IF EXISTS sessions_delete ON workout_sessions;

CREATE POLICY sessions_read ON workout_sessions FOR SELECT TO authenticated
USING (
  user_id = auth.uid()
  OR trainer_id = auth.uid()
  OR (center_id IS NOT NULL AND is_shop_staff(auth.uid(), center_id))
);

CREATE POLICY sessions_insert ON workout_sessions FOR INSERT TO authenticated
WITH CHECK (
  (session_type = 'self'
     AND user_id = auth.uid()
     AND trainer_id IS NULL)
  OR
  (session_type = 'supervised'
     AND trainer_id = auth.uid()
     AND center_id IS NOT NULL
     AND is_shop_staff(auth.uid(), center_id)
     AND shared_shop_with_member(auth.uid(), user_id))
);

CREATE POLICY sessions_update ON workout_sessions FOR UPDATE TO authenticated
USING (user_id = auth.uid() OR trainer_id = auth.uid())
WITH CHECK (user_id = auth.uid() OR trainer_id = auth.uid());

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

-- -----------------------------------------------------------------------------
-- 6. RLS — derived tables (exercise_entries / sets / reps / rom_estimates / raw_motion_samples)
--     inherit session read rights; write limited to session author (user or trainer).
-- -----------------------------------------------------------------------------

-- exercise_entries (via session)
DROP POLICY IF EXISTS entries_via_session ON exercise_entries;
CREATE POLICY entries_via_session ON exercise_entries FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM workout_sessions ws
  WHERE ws.id = exercise_entries.session_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)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM workout_sessions ws
  WHERE ws.id = exercise_entries.session_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- exercise_sets (via entry → session)
DROP POLICY IF EXISTS sets_via_entry ON exercise_sets;
CREATE POLICY sets_via_entry ON exercise_sets FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM exercise_entries ee
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE ee.id = exercise_sets.entry_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)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM exercise_entries ee
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE ee.id = exercise_sets.entry_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- reps (via set → entry → session)
DROP POLICY IF EXISTS reps_via_set ON reps;
CREATE POLICY reps_via_set ON reps FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM exercise_sets es
  JOIN exercise_entries ee ON ee.id = es.entry_id
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE es.id = reps.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)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM exercise_sets es
  JOIN exercise_entries ee ON ee.id = es.entry_id
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE es.id = reps.set_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- rom_estimates (via entry → session)
DROP POLICY IF EXISTS rom_via_entry ON rom_estimates;
CREATE POLICY rom_via_entry ON rom_estimates FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM exercise_entries ee
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE ee.id = rom_estimates.entry_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)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM exercise_entries ee
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE ee.id = rom_estimates.entry_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- raw_motion_samples (via session)
DROP POLICY IF EXISTS raw_via_session ON raw_motion_samples;
CREATE POLICY raw_via_session ON raw_motion_samples FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM workout_sessions ws
  WHERE ws.id = raw_motion_samples.session_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)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM workout_sessions ws
  WHERE ws.id = raw_motion_samples.session_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- -----------------------------------------------------------------------------
-- 7. RLS — centers
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS centers_authenticated_read ON centers;

CREATE POLICY centers_member_read ON centers FOR SELECT TO authenticated
USING (EXISTS (
  SELECT 1 FROM center_memberships m
  WHERE m.center_id = centers.id AND m.user_id = auth.uid()
));

CREATE POLICY centers_owner_insert ON centers FOR INSERT TO authenticated
WITH CHECK (owner_id = auth.uid());

CREATE POLICY centers_owner_update ON centers FOR UPDATE TO authenticated
USING (owner_id = auth.uid()) WITH CHECK (owner_id = auth.uid());

CREATE POLICY centers_owner_delete ON centers FOR DELETE TO authenticated
USING (owner_id = auth.uid());

-- -----------------------------------------------------------------------------
-- 8. RLS — center_memberships
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS center_memberships_self_read ON center_memberships;

CREATE POLICY memberships_read ON center_memberships FOR SELECT TO authenticated
USING (
  user_id = auth.uid()
  OR is_shop_staff(auth.uid(), center_id)
);

CREATE POLICY memberships_owner_write ON center_memberships FOR INSERT TO authenticated
WITH CHECK (
  EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid())
);

CREATE POLICY memberships_owner_update ON center_memberships FOR UPDATE TO authenticated
USING (EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid()))
WITH CHECK (EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid()));

CREATE POLICY memberships_owner_delete ON center_memberships FOR DELETE TO authenticated
USING (EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid()));

-- -----------------------------------------------------------------------------
-- 9. RLS — pending_invitations
-- -----------------------------------------------------------------------------
ALTER TABLE pending_invitations ENABLE ROW LEVEL SECURITY;

CREATE POLICY invitations_staff_read ON pending_invitations FOR SELECT TO authenticated
USING (is_shop_staff(auth.uid(), center_id));

CREATE POLICY invitations_staff_insert ON pending_invitations FOR INSERT TO authenticated
WITH CHECK (is_shop_staff(auth.uid(), center_id) AND invited_by = auth.uid());

CREATE POLICY invitations_staff_update ON pending_invitations FOR UPDATE TO authenticated
USING (is_shop_staff(auth.uid(), center_id))
WITH CHECK (is_shop_staff(auth.uid(), center_id));

CREATE POLICY invitations_staff_delete ON pending_invitations FOR DELETE TO authenticated
USING (is_shop_staff(auth.uid(), center_id));

COMMIT;
