# Idempotent repair of the ZITADEL identity links that live inside three
# application databases.
#
# Why this exists: Forgejo, Paperless and Immich each store the link between
# their local account and the identity provider INSIDE their own database, not
# in Kubernetes. When the IdP changed from Authentik to ZITADEL, every one of
# those rows kept the Authentik subject and the provider name. The result was:
#
#   Forgejo   -> /user/link_account, never a login
#   Paperless -> /accounts/3rdparty/signup/, never a login
#   Immich    -> 400 "email already linked to different account"
#
# Each row is keyed on a stable, human-meaningful column (the account email or
# username) rather than a numeric primary key, so a re-run converges and a
# fresh database does not need the hard-coded ids this was first discovered on.
#
# The ZITADEL subject below is the `sub` claim of the operator account. It is
# NOT a client id: every one of the three databases stores the subject.
#
# Re-run policy: safe. Every statement is an UPDATE that matches its row by
# email/username, and the Job prunes itself so ArgoCD can recreate it on the
# next sync.
apiVersion: batch/v1
kind: Job
metadata:
  name: iam-identity-link-repair
  namespace: iam
  annotations:
    argocd.argoproj.io/hook: PostSync
    argocd.argoproj.io/hook-delete-policy: BeforeHookCreation
spec:
  backoffLimit: 2
  ttlSecondsAfterFinished: 600
  template:
    metadata:
      labels:
        app.kubernetes.io/name: iam-identity-link-repair
        app.kubernetes.io/part-of: iam
    spec:
      restartPolicy: Never
      securityContext:
        runAsNonRoot: true
        runAsUser: 70 # postgres user in the image, so psql does not run as root
        seccompProfile:
          type: RuntimeDefault
      containers:
        - name: repair
          image: postgres:16-alpine
          securityContext:
            allowPrivilegeEscalation: false
            readOnlyRootFilesystem: true
            capabilities:
              drop: ["ALL"]
          env:
            # The ZITADEL user that must own all three links.
            - name: ZITADEL_SUB
              value: "396924923823522416"
            - name: LINK_EMAIL
              value: "locartrock@gmail.com"
            - name: LINK_USERNAME
              value: "rock"
            # Forgejo
            - name: FORGEJO_PGHOST
              value: "forgejo-db-rw.cicd.svc.cluster.local"
            - name: FORGEJO_PGUSER
              valueFrom: { secretKeyRef: { name: forgejo-db-app, key: username } }
            - name: FORGEJO_PGPASSWORD
              valueFrom: { secretKeyRef: { name: forgejo-db-app, key: password } }
            - name: FORGEJO_PGDATABASE
              value: "forgejo"
            # Paperless
            - name: PAPERLESS_PGHOST
              value: "paperless-db-rw.paperless.svc.cluster.local"
            - name: PAPERLESS_PGUSER
              valueFrom: { secretKeyRef: { name: paperless-db-app, key: username } }
            - name: PAPERLESS_PGPASSWORD
              valueFrom: { secretKeyRef: { name: paperless-db-app, key: password } }
            - name: PAPERLESS_PGDATABASE
              value: "paperless"
            # Immich
            - name: IMMICH_PGHOST
              value: "immich-db-rw.immich.svc.cluster.local"
            - name: IMMICH_PGUSER
              valueFrom: { secretKeyRef: { name: immich-db-app, key: username } }
            - name: IMMICH_PGPASSWORD
              valueFrom: { secretKeyRef: { name: immich-db-app, key: password } }
            - name: IMMICH_PGDATABASE
              value: "immich"
          command: ["/bin/sh", "-c"]
          args:
            - |
              set -eu
              psql_dsn() { printf 'postgresql://%s:%s@%s:5432/%s' "$2" "$3" "$1" "$4"; }

              echo "=== Forgejo: link the OIDC subject to the local account ==="
              # login_source.type = 6 is OAuth2/OIDC. The source may be named
              # anything ("zitadel" today, "authentik" before), so select on type
              # and take the single active row rather than assuming a name.
              psql "$(psql_dsn "$FORGEJO_PGHOST" "$FORGEJO_PGUSER" "$FORGEJO_PGPASSWORD" "$FORGEJO_PGDATABASE")" -v ON_ERROR_STOP=1 -c \
                "INSERT INTO external_login_user (external_id, user_id, login_source_id, provider) SELECT '$ZITADEL_SUB', u.id, ls.id, 'openidConnect' FROM \"user\" u CROSS JOIN (SELECT id FROM login_source WHERE type = 6 AND is_active LIMIT 1) ls WHERE lower(u.lower_name) = lower('$LINK_USERNAME') ON CONFLICT DO NOTHING;"

              echo "=== Paperless: point the social link at the ZITADEL subject ==="
              # provider must match the allauth provider_id in
              # SOCIALACCOUNT_PROVIDERS_JSON, which is "zitadel".
              psql "$(psql_dsn "$PAPERLESS_PGHOST" "$PAPERLESS_PGUSER" "$PAPERLESS_PGPASSWORD" "$PAPERLESS_PGDATABASE")" -v ON_ERROR_STOP=1 -c \
                "UPDATE socialaccount_socialaccount sa SET uid = '$ZITADEL_SUB', provider = 'zitadel' FROM auth_user au WHERE au.id = sa.user_id AND lower(au.email) = lower('$LINK_EMAIL');"
              psql "$(psql_dsn "$PAPERLESS_PGHOST" "$PAPERLESS_PGUSER" "$PAPERLESS_PGPASSWORD" "$PAPERLESS_PGDATABASE")" -v ON_ERROR_STOP=1 -c \
                "UPDATE socialaccount_socialaccount sa SET provider = 'zitadel' FROM auth_user au WHERE au.id = sa.user_id AND lower(au.email) = lower('$LINK_EMAIL');"

              echo "=== Immich: replace the stale provider subject on the existing user ==="
              # Immich rejects a callback whose email is already attached to a
              # different oauthId, so the row must be re-pointed, not inserted.
              psql "$(psql_dsn "$IMMICH_PGHOST" "$IMMICH_PGUSER" "$IMMICH_PGPASSWORD" "$IMMICH_PGDATABASE")" -v ON_ERROR_STOP=1 -c \
                "UPDATE \"user\" SET \"oauthId\" = '$ZITADEL_SUB' WHERE lower(email) = lower('$LINK_EMAIL');"

              echo "=== verify ==="
              psql "$(psql_dsn "$FORGEJO_PGHOST" "$FORGEJO_PGUSER" "$FORGEJO_PGPASSWORD" "$FORGEJO_PGDATABASE")" -tAc \
                "SELECT 'forgejo:'||external_id FROM external_login_user WHERE external_id='$ZITADEL_SUB';"
              psql "$(psql_dsn "$PAPERLESS_PGHOST" "$PAPERLESS_PGUSER" "$PAPERLESS_PGPASSWORD" "$PAPERLESS_PGDATABASE")" -tAc \
                "SELECT 'paperless:'||provider||':'||uid FROM socialaccount_socialaccount WHERE uid='$ZITADEL_SUB';"
              psql "$(psql_dsn "$IMMICH_PGHOST" "$IMMICH_PGUSER" "$IMMICH_PGPASSWORD" "$IMMICH_PGDATABASE")" -tAc \
                "SELECT 'immich:'||\"oauthId\" FROM \"user\" WHERE \"oauthId\"='$ZITADEL_SUB';"
              echo "=== done ==="