# THIS FILE IS CONTROLLED BY ANSIBLE - DO NOT CHANGE IN DEPLOYMENT!

# Rewrite (envelope and header) recipient addresses:
# - if the recipient address has a local domain and does
#   not begin with SRS0 or SRS1, then strip our verp_marker
# - if the recipient address has a local domain and does
#   begin with SRS0 or SRS1, try to extract our srs_id
#   and lookup the corresponding original address

# man pgsql_table

user = {{ mailserver.postgresql.username }}
password = {{ mailserver.postgresql.password }}
dbname = {{ mailserver.postgresql.dbname }}
hosts = {{ mailserver.postgresql.host }}
query =
  with
    mydomains as (select regexp_replace('%s', '.*@([^@]+)$', '\1') in (select name from domains) v),
    srs0 as (select lower(substr('%s', 1, 4)) = 'srs0' v),
    srs1 as (select lower(substr('%s', 1, 4)) = 'srs1' v)
  select
    case
      when mydomains.v and not srs0.v and not srs1.v
      then regexp_replace('%s', '^(.*)\+{{ mailserver.postfix.verp_marker }}-\d+@', '\1@')
      else
        case
          when mydomains.v
          then
            case
              when srs0.v
              then
                case
                  when '%s' ~* '^SRS0=\d+@[^@]+$'
                  then (select original from mail_from
                         where srs_id = regexp_replace('%s', '^.....(\d+)@[^@]+$', '\1')::bigint
                           and original<>rewritten)
                  else '%s'
                end
              else (with t2 as (select regexp_replace('%s', '^(.*)@[^@]+$', '\1') v)
                    select original from mail_from, t2
                    where lower(substr(rewritten, 1, length(t2.v))) = lower(t2.v)
                      and lower(substr(original, 1, 4)) = 'srs0')
            end
          else '%s'
        end
    end
  from mydomains, srs0, srs1