#!/usr/bin/python

from daklib.dbconn import *

from sqlalchemy import desc

command = '  dak rm -p -R -C package -m "RoQA; very outdated, see http://deb.li/33Ehn" -b -s unstable -a %s %s\n'

session = DBConn().session()

sid = get_suite('unstable', session)
all = get_architecture('all', session)

params = {
    'suite_id': sid.suite_id}

query = session.query('source', 'count').from_statement('''
    select source, count(*) as count from source
        where id in (select source from src_associations where suite = :suite_id)
        group by source having count(*) > 1 order by source''').params(params)

for (source, count) in query:
    query2 = sid.sources.filter_by(source = source).order_by(desc(DBSource.version))
    first = True
    for dbsource in query2:
        if first:
            first = False
            continue
        if dbsource.install_date.year < 2009:
	    date = dbsource.install_date.strftime('%Y-%m-%d')
            print '#', source, dbsource.version, date
            query3 = sid.binaries.filter_by(source = dbsource). \
                filter(DBBinary.architecture != all)
            pkg_set = set([b.package for b in query3])
            arch_set = set([b.architecture.arch_string for b in query3])
            pkg_string = ' '.join(sorted(pkg_set))
            arch_string = ','.join(sorted(arch_set))
            print command % (arch_string, pkg_string)