import sqlite3
import mariadb

sconn = sqlite3.connect("bj.db")
mconn = mariadb.connect(host="cpsc.umw.edu", user="umwstud",
    password="davies4ever", port=3306, database="deliveries")

scur = sconn.cursor()
mcur = mconn.cursor()

scur.execute("select max(container_id) from container")
next_container_id = scur.fetchone()[0] + 1
print(next_container_id)

print(f"Our next container id will be {next_container_id}")


                

mcur.execute("select * from containers")
results = mcur.fetchall()
for result in results:
    scur.execute(f"select needsRef from mixin where name=?",
        (result[2].lower(),) )
    ref_status = scur.fetchone()
    if ref_status is None:
        print(f"WHOA! We've never heard of {result[2].lower()}! Assuming 'no'")
        ref_status = 'no'
    else:
        ref_status = ref_status[0]
        print(f"Got {result[2].lower()} and we know that its ref status is {ref_status}.")
    for i in range(result[3]):
        scur.execute("insert into container values (?, ?, ?, ?, ?) ",
            (next_container_id,ref_status,result[1],result[1],result[2].lower()))
        next_container_id += 1
sconn.commit()