import argparse
import os

import duckdb


def orders_report(product = None):
    con = duckdb.connect()
    con.execute("SET TimeZone = 'UTC'")

    bucket = os.environ['LAB_BUCKET_NAME']
    path = f"s3://{bucket}/bronze/orders.csv"

    query = f"""
        SELECT strftime(date, '%Y-%m') AS month, count(*) AS orders, sum(quantity) AS quantity
        FROM read_csv('{path}', strict_mode = false)
        WHERE $product IS NULL OR product = $product
        GROUP BY month
        ORDER BY month
    """
    res = con.execute(query, {"product": product}).fetchall()
    return res


def main():
    parser = argparse.ArgumentParser(prog = "orders-report", description = "Monthly orders report")
    parser.add_argument("-p", "--product", help = "Filter the orders on a product.")
    args = parser.parse_args()

    rows = orders_report(args.product)

    if args.product:
        print("Orders report for", args.product)
    else:
        print("Orders report for all the products")

    for month, orders, quantity in rows:
        print(month, "->", orders, "orders and", quantity, "items")


if __name__ == "__main__":
    main()