-- Portal handoff: one-time codes for cross-domain session (login.saventh.com -> crm/corporate)
-- sessionStorage is per-origin, so we pass a code; app consumes it and sets session

CREATE TABLE IF NOT EXISTS portal_handoff (
  code TEXT PRIMARY KEY,
  app TEXT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  expires_at TIMESTAMPTZ DEFAULT (now() + interval '5 minutes')
);

CREATE INDEX idx_portal_handoff_expires ON portal_handoff(expires_at);

ALTER TABLE portal_handoff ENABLE ROW LEVEL SECURITY;

-- anon can insert (Portal) and select/delete by code (CRM/Corporate consume)
CREATE POLICY "portal_handoff_insert" ON portal_handoff FOR INSERT WITH CHECK (true);
CREATE POLICY "portal_handoff_select" ON portal_handoff FOR SELECT USING (true);
CREATE POLICY "portal_handoff_delete" ON portal_handoff FOR DELETE USING (true);

-- RPC: consume code, return payload, delete row (one-time use)
CREATE OR REPLACE FUNCTION consume_portal_handoff(p_code TEXT)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_payload JSONB;
BEGIN
  SELECT payload INTO v_payload FROM portal_handoff
  WHERE code = p_code AND expires_at > now();
  IF v_payload IS NOT NULL THEN
    DELETE FROM portal_handoff WHERE code = p_code;
    RETURN v_payload;
  END IF;
  RETURN NULL;
END;
$$;
