-- ============================================================================
-- B2B schema enrichment (DB-02 + DB-03 + DB-04 + SEC-03)
--
-- Bundle of four scenario-gap fixes that all touch core b2b tables:
--
--   DB-02  centers: address / business_hours / status / closed_at
--   DB-03  center_memberships: left_at (soft-delete)
--   DB-04  workout_sessions: status enum (active/completed/canceled)
--          + sessions_delete policy widened to trainer for self-authored
--            supervised sessions
--   SEC-03 self sessions cannot carry a center_id — server-side trigger
--          force-NULLs on INSERT and rejects on UPDATE
--
-- Additionally hardens is_shop_staff to ignore departed staff (left_at
-- IS NOT NULL) and inactive shops (status != 'active').
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. DB-02 — centers enrichment
-- ----------------------------------------------------------------------------

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_type WHERE typname = 'center_status'
  ) THEN
    CREATE TYPE center_status AS ENUM ('active', 'inactive', 'closed');
  END IF;
END$$;

ALTER TABLE centers
  ADD COLUMN IF NOT EXISTS address text,
  ADD COLUMN IF NOT EXISTS business_hours jsonb,
  ADD COLUMN IF NOT EXISTS status center_status NOT NULL DEFAULT 'active',
  ADD COLUMN IF NOT EXISTS closed_at timestamptz;

-- ----------------------------------------------------------------------------
-- 2. DB-03 — center_memberships soft-delete
-- ----------------------------------------------------------------------------

ALTER TABLE center_memberships
  ADD COLUMN IF NOT EXISTS left_at timestamptz;

CREATE INDEX IF NOT EXISTS idx_memberships_active
  ON center_memberships(center_id, user_id)
  WHERE left_at IS NULL;

-- ----------------------------------------------------------------------------
-- 3. is_shop_staff respects active-only memberships + active shops
-- ----------------------------------------------------------------------------

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 m
    JOIN centers c ON c.id = m.center_id
    WHERE m.user_id = _user_id
      AND m.center_id = _center_id
      AND m.role IN ('owner','trainer')
      AND m.left_at IS NULL
      AND c.status = 'active'
  );
$$;

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
    JOIN centers c ON c.id = sm.center_id
    WHERE sm.user_id = _staff_id
      AND sm.role IN ('owner','trainer')
      AND sm.left_at IS NULL
      AND mm.user_id = _member_id
      AND mm.role = 'member'
      AND mm.left_at IS NULL
      AND c.status = 'active'
  );
$$;

-- ----------------------------------------------------------------------------
-- 4. DB-04 — workout_sessions.status + widened delete policy
-- ----------------------------------------------------------------------------

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_type WHERE typname = 'session_status'
  ) THEN
    CREATE TYPE session_status AS ENUM ('active', 'completed', 'canceled');
  END IF;
END$$;

ALTER TABLE workout_sessions
  ADD COLUMN IF NOT EXISTS status session_status NOT NULL DEFAULT 'active';

-- Back-fill: anything with ended_at set is completed; else stays active.
UPDATE workout_sessions
   SET status = 'completed'
 WHERE ended_at IS NOT NULL
   AND status = 'active';

-- DB-04: trainer can delete supervised sessions they themselves authored.
-- Member can delete their own sessions (already covered).
DROP POLICY IF EXISTS sessions_delete ON workout_sessions;
CREATE POLICY sessions_delete ON workout_sessions FOR DELETE TO authenticated
USING (
  user_id = auth.uid()
  OR (session_type = 'supervised' AND trainer_id = auth.uid())
);

-- ----------------------------------------------------------------------------
-- 5. SEC-03 — self sessions cannot carry a center_id
-- ----------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION enforce_self_session_no_center()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.session_type = 'self' AND NEW.center_id IS NOT NULL THEN
    -- Hard reject on UPDATE; silent NULL on INSERT (defense in depth so
    -- a buggy client doesn't error the workout, but the row stays clean).
    IF TG_OP = 'INSERT' THEN
      NEW.center_id := NULL;
    ELSE
      RAISE EXCEPTION
        'self workout_sessions.center_id must be NULL'
        USING ERRCODE = 'check_violation';
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_self_session_no_center ON workout_sessions;
CREATE TRIGGER trg_self_session_no_center
  BEFORE INSERT OR UPDATE ON workout_sessions
  FOR EACH ROW EXECUTE FUNCTION enforce_self_session_no_center();
