#!/usr/bin/python3
# SPDX-License-Identifier: GPL-2.0+

import logging

import sqlalchemy

logger = logging.getLogger()

def strip_title(title, package):
    for prefix in (package + ": ", package + " "):
        if title.startswith(prefix):
            return title[len(prefix):]
    return title

def get_affects(result, kind):
    for bugnum, package, affected, title, patched in result:
        row = dict(bugnum=bugnum, package=package, patched=patched, kind=kind)
        somesource = False
        for pkg in package.split(","):
            if not pkg.startswith("src:"):
                continue
            arow = row.copy()
            arow.update(affects=pkg[4:], title=strip_title(title, pkg[4:]))
            yield arow
            if somesource:
                logger.warning("%s #%d assigned to multiple source packages",
                               kind, bugnum)
            somesource = True
        if not somesource and affected:
            for aff in affected.split(","):
                if not aff.startswith("src:"):
                    continue
                arow = row.copy()
                arow.update(affects=aff[4:], title=strip_title(title, aff[4:]))
                yield arow
                somesource = True
        if not somesource:
            logger.warning("%s #%d affects no packages", kind, bugnum)

def make_table(name, columns):
    return sqlalchemy.table(name, *map(sqlalchemy.column, columns.split()))

udd_all_bugs = make_table(
    "all_bugs", "id package affected_packages title severity affects_unstable")
udd_bugs_tags = make_table("bugs_tags", "id tag")
udd_bugs_usertags = make_table("bugs_usertags", "id email tag")

def tagged_clause(id_, tag):
    return sqlalchemy.exists().where(
        sqlalchemy.and_(udd_bugs_tags.c.id == id_, udd_bugs_tags.c.tag == tag))

def get_bugs_where(conn, whereclause, kind):
    query = sqlalchemy.select(
        [udd_all_bugs.c.id, udd_all_bugs.c.package,
         udd_all_bugs.c.affected_packages, udd_all_bugs.c.title,
         tagged_clause(udd_all_bugs.c.id, "patch")],
        sqlalchemy.and_(whereclause, udd_all_bugs.c.affects_unstable == 't'))
    return get_affects(conn.execute(query), kind)

def get_ftbfs(conn):
    clause = sqlalchemy.and_(
        tagged_clause(udd_all_bugs.c.id, "ftbfs"),
        udd_all_bugs.c.severity.in_(["serious", "critical", "grave"]))
    return get_bugs_where(conn, clause, 'ftbfs')

def get_usertagged(conn, email, tag, kind):
    clause = sqlalchemy.exists().where(
        sqlalchemy.and_(udd_bugs_usertags.c.id == udd_all_bugs.c.id,
                        udd_bugs_usertags.c.email == email,
                        udd_bugs_usertags.c.tag == tag))
    return get_bugs_where(conn, clause, kind)

def get_bugs(conn):
    yield from get_ftbfs(conn)
    email = 'debian-cross@lists.debian.org'
    yield from get_usertagged(conn, email, 'cross-satisfiability', 'bdsat')
    yield from get_usertagged(conn, email, 'ftcbfs', 'ftcbfs')

def main():
    udde = sqlalchemy.create_engine(
        'postgresql://udd-mirror:udd-mirror@udd-mirror.debian.net/'
        'udd?client_encoding=utf8')
    with udde.connect() as conn:
        bugs = list(get_bugs(conn))

    query = sqlalchemy.text("""
        INSERT INTO bugs (kind, bugnum, package, affects, title, patched)
            VALUES (:kind, :bugnum, :package, :affects, :title, :patched);""")
    crosse = sqlalchemy.create_engine('sqlite:///db')
    with crosse.connect() as conn:
        with conn.begin():
            conn.execute("DELETE FROM bugs;")
            conn.execute(query, bugs)

if __name__ == "__main__":
    main()