import sqlite3
import requests
import datetime

source = requests.get("https://thereportoftheweek-api.herokuapp.com/reports").json()


conn = sqlite3.connect("barbs_energy.db")

c = conn.cursor()

# c.execute("""CREATE TABLE products(
#     id text PRIMARY KEY,
#     product text,
#     manufacturer text,
#     datereleased text
# )""")

# conn.commit()

# for data in source:
#     if data['category'] == 'Energy Crisis':
#         print(data)
#         date_strp = datetime.datetime.strptime(data['dateReleased'].split('.')[0], '%Y-%m-%dT%H:%M:%S')
#         date_str = date_strp.strftime("%b %d, %Y")
        
#         c.execute("INSERT INTO products VALUES(?, ?, ?, ?)", (data['_id'], data['product'], data['manufacturer'], date_str))
#         conn.commit()

print("ID".rjust(14), "PRODUCT".rjust(20), "MANUFACTURER".rjust(88), "DATE RELEASED".rjust(22))
for dbdata in c.execute("SELECT * FROM products"):
    print(f"{dbdata[0]:<10} {dbdata[1]:<50} {dbdata[2]:>50} {dbdata[3]:>20}")


conn.close()

# for data in source:
#     if data['gender'] == 'male':
#         c.execute("INSERT INTO male VALUES(?, ?, ?, ?)", (data['name'], data['surname'], data['gender'], data['region']))
#         conn.commit()
#     else:
#         c.execute("INSERT INTO female VALUES(?, ?, ?, ?)", (data['name'], data['surname'], data['gender'], data['region']))
#         conn.commit()

# print("MALE".rjust(10))
# for male in c.execute("SELECT * FROM male"):
#     print(f"{male[0]} {male[1]} - {male[3]}")
# print("FEMALE".rjust(10))   
# for female in c.execute("SELECT * FROM female"):
#     print(f"{female[0]} {female[1]} - {female[3]}")
# conn.close()

# SQLITE, DATETIME AND REQUESTS Exercise

# 1. Create a new database energy_crisis. The datable should have products table
# 2. The products table should have the following attributes: id, product, manufacturer, datereleased
# 2. Given this link `https://thereportoftheweek-api.herokuapp.com/reports`. Fetch the data and convert the date to string format using datetime module and insert the following data from the api to the product