-- Track organization invitations sent by members so Settings can display invite history.

CREATE TABLE IF NOT EXISTS public.organization_invites (
  id bigserial PRIMARY KEY,
  organization_id integer NOT NULL,
  invited_by uuid NOT NULL,
  invited_email character varying(255) NOT NULL,
  invited_user_id uuid,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

ALTER TABLE public.organization_invites
  DROP CONSTRAINT IF EXISTS organization_invites_organization_id_fkey;

ALTER TABLE public.organization_invites
  ADD CONSTRAINT organization_invites_organization_id_fkey
  FOREIGN KEY (organization_id)
  REFERENCES public.organizations(id)
  ON DELETE CASCADE;

ALTER TABLE public.organization_invites
  DROP CONSTRAINT IF EXISTS organization_invites_invited_by_fkey;

ALTER TABLE public.organization_invites
  ADD CONSTRAINT organization_invites_invited_by_fkey
  FOREIGN KEY (invited_by)
  REFERENCES public.profiles(id)
  ON DELETE CASCADE;

ALTER TABLE public.organization_invites
  DROP CONSTRAINT IF EXISTS organization_invites_invited_user_id_fkey;

ALTER TABLE public.organization_invites
  ADD CONSTRAINT organization_invites_invited_user_id_fkey
  FOREIGN KEY (invited_user_id)
  REFERENCES public.profiles(id)
  ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS organization_invites_organization_id_idx
  ON public.organization_invites (organization_id);

CREATE INDEX IF NOT EXISTS organization_invites_invited_by_idx
  ON public.organization_invites (invited_by);

CREATE INDEX IF NOT EXISTS organization_invites_created_at_idx
  ON public.organization_invites (created_at DESC);

COMMENT ON TABLE public.organization_invites IS 'History of organization invitations sent by members.';
COMMENT ON COLUMN public.organization_invites.organization_id IS 'Organization where the invite was sent.';
COMMENT ON COLUMN public.organization_invites.invited_by IS 'Profile ID of the member who sent the invite.';
COMMENT ON COLUMN public.organization_invites.invited_email IS 'Email that was invited.';
COMMENT ON COLUMN public.organization_invites.invited_user_id IS 'Profile ID created for the invited email, when available.';

ALTER TABLE public.organization_invites ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view organization invites they sent" ON public.organization_invites;
CREATE POLICY "Users can view organization invites they sent"
  ON public.organization_invites
  FOR SELECT
  USING (
    organization_id = public.current_user_organization_id()
    AND invited_by = auth.uid()
  );

DROP POLICY IF EXISTS "Users can insert organization invites they sent" ON public.organization_invites;
CREATE POLICY "Users can insert organization invites they sent"
  ON public.organization_invites
  FOR INSERT
  WITH CHECK (
    organization_id = public.current_user_organization_id()
    AND invited_by = auth.uid()
  );

GRANT SELECT, INSERT ON public.organization_invites TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.organization_invites_id_seq TO authenticated;
