SELECT
  p.Name AS "Project Name",
  c.Name AS "Configuration Name",
  c.Archived AS "Config Is Archived",
  p.Archived AS "Project Is Archived",
  CASE WHEN cep.Id IS NOT NULL THEN
    'Configuration'
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      'Project'
    ELSE
      'Global'
    END
  END AS "Cleanup Policy Scope",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.maximumbuilds
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.maximumbuilds
    ELSE
      gep.maximumbuilds
    END
  END AS "Cleanup Maximum Builds",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.minimumbuilds
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.minimumbuilds
    ELSE
      gep.minimumbuilds
    END
  END AS "Cleanup Minimum Builds",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.lastexecuted
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.lastexecuted
    ELSE
      gep.lastexecuted
    END
  END AS "Cleanup Last Executed",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.expirycount
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.expirycount
    ELSE
      gep.expirycount
    END
  END AS "Cleanup Build Age",
  CASE WHEN cep.Id IS NOT NULL THEN
    CASE WHEN cep.expirytype = 0 THEN
      'Days'
    ELSE
      CASE WHEN cep.expirytype = 1 THEN
        'Weeks'
      ELSE
        'Months'
      END
    END
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      CASE WHEN pep.expirytype = 0 THEN
        'Days'
      ELSE
        CASE WHEN pep.expirytype = 1 THEN
          'Weeks'
        ELSE
          'Months'
        END
      END
    ELSE
      CASE WHEN gep.expirytype = 0 THEN
        'Days'
      ELSE
        CASE WHEN gep.expirytype = 1 THEN
          'Weeks'
        ELSE
          'Months'
        END
      END
    END
  END AS "Cleanup Build Age Units",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.hourtoexecute
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.hourtoexecute
    ELSE
      gep.hourtoexecute
    END
  END AS "Cleanup Hour To Execute",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.options
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.options
    ELSE
      gep.options
    END
  END AS "Cleanup Options",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.options & 1 = 1
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.options & 1 = 1
    ELSE
      gep.options & 1 = 1
    END
  END AS "Cleanup Artifacts",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.options & 2 = 2
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.options & 2 = 2
    ELSE
      gep.options & 2 = 2
    END
  END AS "Cleanup Logs",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.options & 4 = 4
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.options & 4 = 4
    ELSE
      gep.options & 4 = 4
    END
  END AS "Cleanup Builds",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.options & 8 = 8
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.options & 8 = 8
    ELSE
      gep.options & 1 = 1
    END
  END AS "Cleanup UnitTests",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.options & 16 = 16
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.options & 16 = 16
    ELSE
      gep.options & 16 = 16
    END
  END AS "Cleanup Statistics",
  CASE WHEN cep.Id IS NOT NULL THEN
    cep.options & 32 = 32
  ELSE
    CASE WHEN pep.Id IS NOT NULL THEN
      pep.options & 32 = 32
    ELSE
      gep.options & 32 = 32
    END
  END AS "Cleanup Other Files",
  (
    SELECT
      COUNT(b.Id)
    FROM
      public.builds_build b
    WHERE
      b.configurationid = c.id) AS "Number Of Builds", (
      SELECT
        COUNT(b.Id)
      FROM
        public.builds_build b
      WHERE
        b.configurationid = c.id
        AND b.cleanedup = TRUE) AS "Number Of 'Cleaned Up' Builds", (
        SELECT
          COUNT(b.Id)
        FROM
          public.builds_build b
        WHERE
          b.configurationid = c.id
          AND b.pinned IS NOT NULL) AS "Number Of Pinned Builds", (
          SELECT
            COUNT(b.Id)
          FROM
            public.builds_build b
          WHERE
            b.configurationid = c.id
            AND b.state = 12) AS "Number Of Builds Pending Promotion", (
            SELECT
              COUNT(b.Id)
            FROM
              public.builds_build b
            WHERE
              b.configurationid = c.id
              AND (b.state = 13
                OR b.state = 14
                OR b.state = 15
                OR b.state = 17
                OR b.state = 18) -- completed, failed, errored, stopped, discarded
) AS "Number Of Terminated Builds", (
              SELECT
                COUNT(b.Id)
              FROM
                public.builds_build b
              WHERE
                b.configurationid = c.id
                AND b.pinned IS NULL) AS "Number Of Non-Pinned Builds", (
                SELECT
                  COUNT(b.Id)
                FROM
                  public.builds_build b
                WHERE
                  b.configurationid = c.id
                  AND b.pinned IS NULL
                  AND (b.state = 13
                    OR b.state = 14
                    OR b.state = 15
                    OR b.state = 17
                    OR b.state = 18) -- completed, failed, errored, stopped, discarded
) AS "Number Of Non-Pinned Terminated Builds", (
                  SELECT
                    MIN(COALESCE(b.Finished, '9999-12-31'))
                  FROM
                    public.builds_build b
                  WHERE
                    b.configurationid = c.id
                    AND b.pinned IS NULL
                    AND (b.state = 13
                      OR b.state = 14
                      OR b.state = 15
                      OR b.state = 17
                      OR b.state = 18) -- completed, failed, errored, stopped, discarded
) AS "Least Recently Finished Non-Pinned Terminated Build", (
                    SELECT
                      CASE WHEN cep.Id IS NOT NULL THEN
                        GREATEST (COUNT(b.Id) - cep.maximumbuilds, 0)
                      ELSE
                        CASE WHEN pep.Id IS NOT NULL THEN
                          GREATEST (COUNT(b.Id) - pep.maximumbuilds, 0)
                        ELSE
                          GREATEST (COUNT(b.Id) - gep.maximumbuilds, 0)
                        END
                      END
                    FROM
                      public.builds_build b
                    WHERE
                      b.configurationid = c.id
                      AND b.pinned IS NULL
                      AND (b.state = 13
                        OR b.state = 14
                        OR b.state = 15
                        OR b.state = 17
                        OR b.state = 18) -- completed, failed, errored, stopped, discarded
) AS "Number Of Non Pinned Terminated Builds Over Maximum"
                  FROM
                    public.builds_configuration c
                    INNER JOIN public.core_project p ON c.projectid = p.id
                    LEFT JOIN builds_expirationpolicy cep ON cep.configurationid = c.id
                    LEFT JOIN builds_expirationpolicy pep ON pep.configurationid IS NULL
                      AND pep.projectid = p.id
                  LEFT JOIN builds_expirationpolicy gep ON gep.configurationid IS NULL
                    AND gep.projectid IS NULL
                  ORDER BY
                    "Number Of Non Pinned Terminated Builds Over Maximum" DESC