# Using the database described in ‹p3›, write a function which lists
# all packages (package versions) that are required by the package
# given in the argument, including indirect dependencies.

from sqlite3 import connect, Connection
from typing import List, Tuple

def list_required( db: Connection, package: str,
                   version: str ) -> List[ Tuple[ str, str ] ]:
    pass

# ----- >% ----- >% -----

def mkdb() -> Connection:
    conn = connect( ':memory:' )
    c = conn.cursor()
    c.execute( 'create table package ( id integer primary key, ' + \
               'name varchar )' )
    c.execute( 'create table version ( id integer primary key, ' + \
               'package_id integer, number varchar )' )
    c.execute( 'create table depends ( version_id integer,' + \
               'depends_on integer )' )

    def add_pkg( name: str, *vers: str ) -> None:
        c.execute( 'insert into package ( name ) values ( ? )',
                   ( name, ) )
        pid = c.lastrowid
        for v in vers:
            c.execute( 'insert into version ( package_id, ' + \
                       'number ) values ( ?, ? )', ( pid, v ) )

    def add_dep( p1: str, v1: str, p2: str, v2: str ) -> None:
        get = '( select version.id from version join ' + \
              'package on package.id = package_id ' + \
              'where name = ? and number = ? )'

        c.execute( 'insert into depends ( version_id, depends_on ) ' + \
                   f'values ( {get}, {get} )', ( p1, v1, p2, v2 ) )

    add_pkg( 'libc', '2.0', '2.1', '2.2' )
    add_pkg( 'ksh', '1.0', '1.1', '1.2' )
    add_pkg( 'net', '3.0' )
    add_pkg( 'httpd', '1.0' )
    add_pkg( 'init', '1.0', '2.0' )

    add_dep( 'ksh', '1.0', 'libc', '2.0' )
    add_dep( 'ksh', '1.1', 'libc', '2.1' )
    add_dep( 'ksh', '1.2', 'libc', '2.2' )
    add_dep( 'init', '2.0', 'libc', '2.2' )

    add_dep( 'net', '3.0', 'ksh', '1.2' )
    add_dep( 'httpd', '1.0', 'net', '3.0' )
    add_dep( 'httpd', '1.0', 'init', '2.0' )

    return conn

def test_required() -> None:
    c = mkdb()

    x = set( list_required( c, 'ksh', '1.1') )
    assert x == { ( 'libc', '2.1' ) }, x
    x = set( list_required( c, 'net', '3.0') )
    assert x == { ( 'ksh', '1.2' ), ( 'libc', '2.2' ) }, x

    x = set( list_required( c, 'net', '3.0') )
    assert x == { ( 'ksh', '1.2' ), ( 'libc', '2.2' ) }, x

    x = set( list_required( c, 'init', '2.0') )
    assert x == { ( 'libc', '2.2' ) }, x

    x = set( list_required( c, 'httpd', '1.0') )
    assert x == { ( 'libc', '2.2' ), ( 'ksh', '1.2' ),
                  ( 'init', '2.0' ), ( 'net', '3.0' ) }, x

    y = [ pkg for pkg, ver in list_required( c, 'httpd', '1.0') ]
    assert y.count( 'libc' ) == 1


if __name__ == '__main__':
    test_required()