#!/bin/sh

PGPASSWORD=udd-mirror psql --host=udd-mirror.debian.net --user=udd-mirror udd -A -t -c "SELECT JSON_AGG(t.*) FROM (
SELECT source,version,date,vcswatch.url as vcs,sources.maintainer_name,sources.maintainer_email,maintains,COALESCE(all_bugs,0) AS all_bugs,COALESCE(rc_bugs,0) AS rc_bugs,COALESCE(insts,0) AS insts,COALESCE(vote,0) AS vote
FROM upload_history
JOIN (SELECT source,MAX(version) AS version FROM upload_history WHERE distribution='unstable' GROUP BY source) AS s USING (source,version)
JOIN sources USING (source,version)
LEFT JOIN vcswatch USING (source,version)
LEFT JOIN popcon_src USING (source)
LEFT JOIN bugs_count USING (source)
LEFT JOIN (SELECT maintainer,COUNT(DISTINCT source) AS maintains FROM sources WHERE release='sid' GROUP BY maintainer) AS m ON m.maintainer=sources.maintainer
WHERE release='sid' AND date < current_date - INTERVAL '3 year'
) AS t;" > data.json