-- ============================================================================
-- Invite confirmation flow (SEC-02)
--
-- Problem: invite_member previously upserted center_memberships immediately
-- when an invited email matched an existing auth.users row. This let an
-- attacker pre-register a victim's email and then "join" their shop the
-- moment a trainer pressed Invite, exfiltrating supervised session data.
--
-- Fix: existing-user invitations now require explicit confirmation by the
-- target user before center_memberships is written. Shadow (newly created)
-- users keep the auto-join behavior — there is no attacker to spoof a user
-- that does not yet exist.
--
-- Schema additions:
--   - pending_invitations.requires_confirmation boolean (default true)
--   - pending_invitations.confirmed_at timestamptz (null until accepted)
--   - pending_invitations.accepted_user_id uuid (the auth user that accepted)
-- ============================================================================

ALTER TABLE pending_invitations
  ADD COLUMN IF NOT EXISTS requires_confirmation boolean NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS confirmed_at timestamptz,
  ADD COLUMN IF NOT EXISTS accepted_user_id uuid REFERENCES auth.users(id);

-- Existing rows (created by the v0 invite_member without confirmation) are
-- back-filled to requires_confirmation=false to preserve current behavior.
UPDATE pending_invitations
   SET requires_confirmation = false
 WHERE confirmed_at IS NULL
   AND requires_confirmation IS DISTINCT FROM false
   AND created_at < now() - interval '1 second';

-- Allow the invitee to read their own pending invitation when the email
-- on the row matches their auth.users.email. Without this they cannot
-- enumerate which shops they have been invited to.
DROP POLICY IF EXISTS invitations_invitee_read ON pending_invitations;
CREATE POLICY invitations_invitee_read ON pending_invitations
  FOR SELECT TO authenticated
  USING (
    email = (SELECT email FROM auth.users WHERE id = auth.uid())
  );
