# import sqlite3
# import requests
# import datetime

# people = requests.get("https://uinames.com/api/?amount=25").json()

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

# conn = sqlite3.connect("barbs_energy.db")
# c = conn.cursor()

# c.execute("""CREATE TABLE users(
#     id integer PRIMARY KEY AUTOINCREMENT,
#     firstname text,
#     lastname text,
#     gender text
# )""")

# c.execute("""CREATE TABLE products(
#     id integer,
#     product text,
#     manufacturer text,
#     datereleased text,
#     FOREIGN KEY (id) REFERENCES users(id)
# )""")

# conn.commit()


# for names in people:
#     c.execute("INSERT INTO users VALUES(NULL, ?, ?, ?)", (names['name'], names['surname'], names['gender']))
#     conn.commit()

# prodhere = []
# manuhere = []
# datehere = []
# for prod in source[0:25]:
#     prodhere.append(prod['product'])
#     manuhere.append(prod['manufacturer'])
#     datehere.append(prod['dateReleased'])

# for index, (prodf, manuf, datef) in enumerate(zip(prodhere, manuhere, datehere), start=1):
#     date_strp = datetime.datetime.strptime(datef.split('.')[0], '%Y-%m-%dT%H:%M:%S')
#     date_str = date_strp.strftime("%b %d, %Y")
#     # print(date_str)
#     c.execute("INSERT INTO products VALUES(?, ?, ?, ?)", (index, prodf, manuf, date_str))
#     conn.commit()
    
# c.execute("SELECT * FROM products")
# print(c.fetchall())
# conn.close()

import sqlite3
from requests import get
from dateutil.parser import *
conn = sqlite3.connect('users.db')
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute("""CREATE TABLE IF NOT EXISTS users (
    id integer PRIMARY KEY AUTOINCREMENT,
    firstname text,
    lastname text,
    gender text)""")
c.execute("""CREATE TABLE IF NOT EXISTS products (
    id integer PRIMARY KEY,
    product text,
    manufacturer text,
    datereleased text,
    FOREIGN KEY(id) REFERENCES users(id))""")
users = get('https://uinames.com/api/?amount=25').json()
products = get('https://thereportoftheweek-api.herokuapp.com/reports').json()
for i in users:
    with conn:
        c.execute("INSERT INTO users VALUES(NULL, ? , ?, ?)",
                  (i['name'], i['surname'], i['gender']))
c.execute("SELECT * FROM users ORDER BY id")
ids = [j['id'] for j in c.fetchall()]
for idx, k in enumerate(products[:25]):
    with conn:
        c.execute("INSERT INTO products VALUES(?, ? , ?, ?)",
                  (ids[idx], k['product'], k['manufacturer'], parse(k['dateReleased']).strftime('%b %d, %Y')))
c.close()
conn.close()
