import mariadb
import sqlite3

sconn = sqlite3.connect("bj.db")
mconn = mariadb.connect(
    username="umwstud",
    password="supersecret",
    port=3306,
    database="deliveries"
)

mcursor = mconn.cursor()
mcursor.execute("select * from containers")
results = mcursor.fetchall()
for i in range(len(results)):
    mixin_type = results[i][2].lower()
    num_containers = results[i][3]
    size = results[i][1]
    print(f"Lookie! We received {num_containers} {size}-oz containers of {mixin_type}!")
    highest_container_id = sconn.execute("select max(container_id) from container").fetchall()[0][0]
    # Go to my mixins table and find out whether this type of mixin normally
    # even NEEDS refrigeration, and just use that value as a shortcut.
    print(f"Let's look up {mixin_type}...")
    needs_ref = sconn.execute("select needsRef from mixin where name=?",
        (mixin_type,) ).fetchall()
    if len(needs_ref) == 0:
        needs_ref = "yes"
    else:
        needs_ref = needs_ref[0][0]
    for k in range(num_containers):
        sconn.execute("insert into container values (?, ?, ?, ?, ?)",
        (
            highest_container_id+k+1,
            needs_ref,
            size,
            size,
            mixin_type,
        )
        )
        sconn.commit()