#!/bin/sh

echo "Known debian maintainers:"

psql --html projectb <<EOF
  SELECT uid.uid, uid.name, f.fingerprint
    FROM uid LEFT OUTER JOIN fingerprint f ON (uid.id = f.uid) 
   WHERE uid.uid LIKE 'dm:%'
ORDER BY uid.uid;
EOF

echo "Packages debian maintainers may update:"

psql --html projectb <<EOF
  SELECT s.source, s.version, u.uid
    FROM src_uploaders su JOIN source s ON (su.source = s.id) 
         JOIN src_associations sa ON (s.id = sa.source)
         JOIN maintainer m ON (su.maintainer = m.id)
         JOIN uid u ON (m.name LIKE u.name || ' <%>')
   WHERE u.uid LIKE 'dm:%' AND sa.suite = 5
ORDER BY u.uid, s.source, s.version;
EOF

echo "Source packages in the pool uploaded by debian maintainers:"

psql --html projectb <<EOF
  SELECT s.source, s.version, s.install_date, u.uid
    FROM source s JOIN fingerprint f ON (s.sig_fpr = f.id) 
         JOIN uid u ON (f.uid = u.id)
   WHERE u.uid LIKE 'dm:%'
ORDER BY u.uid, s.source, s.version;
EOF

echo "Binary packages in the pool uploaded by debian maintainers:"

psql --html projectb <<EOF
  SELECT b.package, b.version, a.arch_string AS arch, u.uid
    FROM binaries b JOIN architecture a ON (b.architecture = a.id)
         JOIN fingerprint f ON (b.sig_fpr = f.id) 
         JOIN uid u ON (f.uid = u.id)
   WHERE u.uid LIKE 'dm:%'
ORDER BY u.uid, b.package, b.version;
EOF

echo "Recorded Uploaders:"

psql --html projectb <<EOF
  SELECT s.source, s.version, m.name
    FROM src_uploaders su JOIN source s ON (su.source = s.id) 
         JOIN maintainer m ON (su.maintainer = m.id)
ORDER BY m.name, s.source, s.version;
EOF

echo "Keys without a recorded uid:"

psql --html projectb <<EOF
  SELECT *
    FROM fingerprint f
   WHERE f.uid IS NULL;
EOF