DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_type
    WHERE typname = 'organization_invitation_status_enum'
  ) THEN
    CREATE TYPE organization_invitation_status_enum AS ENUM (
      'PENDING',
      'ACCEPTED',
      'CANCELLED',
      'EXPIRED'
    );
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS organization_invitations (
  id serial PRIMARY KEY,
  organization_id integer NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email text NOT NULL,
  first_name text,
  last_name text,
  role org_member_role_enum NOT NULL DEFAULT 'MEMBER',
  invited_by_user_id integer REFERENCES users(id) ON DELETE SET NULL,
  token_hash text NOT NULL UNIQUE,
  status organization_invitation_status_enum NOT NULL DEFAULT 'PENDING',
  expires_at timestamp NOT NULL,
  accepted_at timestamp,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS org_invites_org_status_created_idx
ON organization_invitations(organization_id, status, created_at);

CREATE UNIQUE INDEX IF NOT EXISTS org_invites_org_email_pending_uidx
ON organization_invitations(organization_id, email)
WHERE status = 'PENDING';
