with tagged_packages as (
    select distinct l.package, p.source_version, p.priority, p.maintainer, p.source
    from lintian_results l
    join packages p on p.package = l.package and p.version = l.package_version
    where l.tag = 'missing-systemd-service-for-init.d-script'
      and l.tag_type <> 'overridden'
      and l.package is not null
      and p.distribution = 'debian'
      and p.release = 'sid'
)
select
    p.package as "package_bin", p.source_version, p.priority, coalesce(pc.insts, 0) as insts, p.maintainer,
    string_agg(bug_link, ' ') as bug_links
from tagged_packages p
left join (
    select distinct 'https://bugs.debian.org/' || bugs.id as bug_link, bugs.source
    from bugs
    join bugs_usertags on bugs_usertags.id = bugs.id and (
        (email = 'pkg-systemd-maintainers@lists.alioth.debian.org' and tag = 'systemd-units')
            or
        (email = 'bluca@debian.org' and tag = 'missing-systemd-service')
    )
    and bugs.status <> 'done'
    and not (bugs.id in (select id from bugs_merged_with where id > merged_with))
) b on b.source = p.source
left join popcon pc on pc.package = p.package
group by 1,2,3,4,5
order by 3, 4 desc
;
