-- Migration: 010_team_members_table.sql
-- Description: Team members table with indexes, triggers, and RLS policies
-- Date: 2025-11-26

-- ============================================
-- TEAM MEMBERS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS public."team_members" (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "teamId" TEXT NOT NULL REFERENCES public."teams"(id) ON DELETE CASCADE,
  "userId" TEXT NOT NULL REFERENCES public."users"(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member',  -- TEXT (not ENUM) so themes extend roles via config; see 007
  "invitedBy" TEXT REFERENCES public."users"(id),
  "joinedAt" TIMESTAMPTZ DEFAULT now(),
  "createdAt" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updatedAt" TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- A user can only have one role per team
  UNIQUE("teamId", "userId")
);

-- ============================================
-- TEAM MEMBERS INDEXES
-- ============================================

CREATE INDEX IF NOT EXISTS idx_team_members_team ON public."team_members"("teamId");
CREATE INDEX IF NOT EXISTS idx_team_members_user ON public."team_members"("userId");
CREATE INDEX IF NOT EXISTS idx_team_members_role ON public."team_members"(role);
CREATE INDEX IF NOT EXISTS idx_team_members_joined ON public."team_members"("joinedAt");

-- Composite index for common queries
CREATE INDEX IF NOT EXISTS idx_team_members_user_role
  ON public."team_members"("userId", role);

-- ============================================
-- TEAM MEMBERS TRIGGERS
-- ============================================

-- Trigger for updatedAt
DROP TRIGGER IF EXISTS team_members_set_updated_at ON public."team_members";
CREATE TRIGGER team_members_set_updated_at
BEFORE UPDATE ON public."team_members"
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- ============================================
-- HELPER FUNCTION (bypasses RLS via SECURITY DEFINER)
-- ============================================

-- Returns team IDs for a given user, bypassing RLS to prevent self-referencing
-- recursion. Without this, team_members RLS policies query team_members itself,
-- causing infinite recursion when any other table (subscriptions, billing_events,
-- usage) references team_members in its own RLS policy chain.
CREATE OR REPLACE FUNCTION public.get_user_team_ids(p_user_id TEXT)
RETURNS SETOF TEXT
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT "teamId" FROM public."team_members" WHERE "userId" = p_user_id;
$$;

-- ============================================
-- TEAM MEMBERS ROW LEVEL SECURITY (RLS)
-- ============================================

ALTER TABLE public."team_members" ENABLE ROW LEVEL SECURITY;

-- Team members: visible to members of the same team
-- Uses get_user_team_ids() (SECURITY DEFINER) to avoid self-referencing recursion
CREATE POLICY "team_members_select_policy" ON public."team_members"
  FOR SELECT TO authenticated
  USING (
    "teamId" IN (SELECT public.get_user_team_ids(public.get_auth_user_id()))
  );

-- Team members: owners and admins can add members
CREATE POLICY "team_members_insert_policy" ON public."team_members"
  FOR INSERT TO authenticated
  WITH CHECK (
    "teamId" IN (SELECT public.get_user_team_ids(public.get_auth_user_id()))
    AND EXISTS (
      SELECT 1 FROM public.get_user_team_ids(public.get_auth_user_id()) AS t
      WHERE t = "team_members"."teamId"
    )
  );

-- Team members: owners and admins can update roles
CREATE POLICY "team_members_update_policy" ON public."team_members"
  FOR UPDATE TO authenticated
  USING (
    "teamId" IN (SELECT public.get_user_team_ids(public.get_auth_user_id()))
  )
  WITH CHECK (
    "teamId" IN (SELECT public.get_user_team_ids(public.get_auth_user_id()))
  );

-- Team members: owners and admins can remove members (except themselves)
CREATE POLICY "team_members_delete_policy" ON public."team_members"
  FOR DELETE TO authenticated
  USING (
    "teamId" IN (SELECT public.get_user_team_ids(public.get_auth_user_id()))
    -- Cannot remove yourself
    AND "userId" != public.get_auth_user_id()
  );
