# the python we used to create sql requests for inserting sample data into the tables

import sqlite3
connection = sqlite3.connect("phase2.db")

cursor = connection.cursor()

# inserting customers

customer_data = [ (666666660, "john@aol.com", "John Smith", "123 lake drive", "123 lake drive", 8041230000, 1),
               (666666661, "sam@aol.com", "Sam Smith", "124 lake drive", "124 lake drive", 8041231000, 1),
               (666666662, "tim@aol.com", "Tim Smith", "125 lake drive", "125 lake drive", 8041232000, 1),
               (666666663, "ray@aol.com", "Ray Smith", "126 lake drive", "126 lake drive", 8041233000, 1),
               (666666664, "carl@aol.com", "Carl Smith", "127 lake drive", "127 lake drive", 8041234000, 1) ]

for p in customer_data:
    format_str = """INSERT INTO Customer (Customer_Id, Email, Name, BillingAddress, ShippingAddress, PhoneNumber, TotalOrders)
    VALUES ("{id}", "{email}", "{name}", "{billing}", "{shipping}", "{phone}", "{totalorders}");"""

    sql_command = format_str.format(id=p[0], email=p[1], name=p[2], billing = p[3], shipping = p[4], phone = p[5], totalorders = p[6])
    cursor.execute(sql_command)

# inserting suppliers
supplier_data = [ (6660, "ABC games", "123 green st", 7176661000, 1),
               (6661, "GameEmporium", "124 green st", 7176662000, 1),
               (6662, "SplendidGames", "125 green st", 7176663000, 1),
               (6663, "XYZ games", "126 green st", 7176664000, 1),
               (6664, "Games Deluxe", "127 green st", 7176665000, 1) ]

for p in supplier_data:
    format_str = """INSERT INTO Supplier (Supplier_Id, SupplierName, Address, PhoneNumber, TotalProducts)
    VALUES ("{id}", "{name}", "{address}", "{phone}", "{totalproducts}");"""

    sql_command = format_str.format(id=p[0], name=p[1], address=p[2], phone = p[3], totalproducts = p[4])
    cursor.execute(sql_command)

# inserting games

game_data = [ (11110, "War 3", 19.80, "Action", "ABC Studios", 7176661000),
               (11111, "Fifa 18", 59.99, "Sports", "XYZ Studios", 7176662000),
               (11112, "Madden 18", 59.99, "Sports", "ABC Studios", 7176663000),
               (11113, "COD 4", 49.99, "FPS", "456 Studios", 7176664000),
               (11114, "Far Cry 3", 25.99, "Shooter", "XYZ Studios", 7176665000) ]

for p in game_data:
    format_str = """INSERT INTO Game (Game_Id, Title, Price, Genre, DeveloperName, Supplier_Id)
    VALUES ("{id}", "{title}", "{price}", "{genre}", "{devname}", "{sid}");"""

    sql_command = format_str.format(id=p[0], title=p[1], price=p[2], genre=p[3], devname=p[4], sid=p[5])
    cursor.execute(sql_command)


# inserting products

product_data = [ (111110, 19.80, 11110),
               (111111, 59.99, 11111),
               (111112, 59.99, 11112),
               (111113, 49.99, 11113),
               (111114, 25.99, 11114) ]

for p in product_data:
    format_str = """INSERT INTO Product (Product_Id, Price, Game_Id)
    VALUES ("{id}", "{price}", "{gid}");"""

    sql_command = format_str.format(id=p[0], price=p[1], gid=p[2])
    cursor.execute(sql_command)

# inserting payments

payment_data = [ (1000000, 59.99, "Paypal", "Complete", 666666660),
               (1000001, 159.99, "Debit", "Complete", 666666661),
               (1000002, 29.99, "Paypal", "Refunded", 666666662),
               (1000003, 25.99, "Credit", "Complete", 666666663),
               (1000004, 99.99, "Bitcoin", "Complete", 666666664)]

for p in payment_data:
    format_str = """INSERT INTO Payment (Payment_Id, Price, PaymentMethod, Payment_Status, Customer_Id)
    VALUES ("{id}", "{price}", "{method}", "{status}", "{cid}");"""

    sql_command = format_str.format(id=p[0], price=p[1], method=p[2], status=p[3], cid=p[4])
    cursor.execute(sql_command)

#inserting orders

order_data = [ (100000, 59.99, 1, "2018-04-21 12:30:37", "Shipped", 111111, 666666660, 1000000),
               (100001, 159.99, 3, "2018-02-21 04:30:37", "Shipped", 111111, 666666661, 1000001),
               (100002, 29.99, 1, "2018-01-21 06:30:37", "Shipped", 111112, 666666662, 1000002),
               (100003, 25.99, 1, "2018-04-10 06:30:37", "Not Shipped", 111114, 666666663, 1000003),
               (100004, 49.99, 1, "2018-03-12 16:20:37", "Shipped", 111113, 666666664, 1000004),
               (100005, 50.00, 1, "2018-04-12 16:20:37", "Shipped", 111113, 666666664, 1000004)]

for p in order_data:
    format_str = """INSERT INTO Orders (Order_Id, Price, Quantity, Order_DateTime, Fulfillment_Status, Product_Id, Customer_Id, Payment_Id)
    VALUES ("{id}", "{price}", "{quant}", "{time}", "{status}", "{pid}", "{cid}", "{payid}");"""

    sql_command = format_str.format(id=p[0], price=p[1], quant=p[2], time=p[3], status=p[4], pid=p[5], cid=p[6], payid=p[7])
    cursor.execute(sql_command)


# inserting Carts

cart_data = [ (10000000, 49.99, 666666660),
               (10000001, 259.99, 666666661),
               (10000002, 19.99, 666666662),
               (10000003, 35.99, 666666661),
               (10000004, 99.99, 666666660)]

for p in cart_data:
    format_str = """INSERT INTO Cart (Cart_Id, TotalPrice, Customer_Id)
    VALUES ("{id}", "{price}", "{cid}");"""

    sql_command = format_str.format(id=p[0], price=p[1], cid=p[2])
    cursor.execute(sql_command)

# inserting Line_Items

lineitem_data = [ (1000, 49.99, 1, 111113, 10000000),
               (1001, 259.99, 4, 111111, 10000001),
               (1002, 19.99, 1, 111110, 10000002),
               (1003, 35.99, 1, 111114, 10000003),
               (1004, 49.99, 1, 111113, 10000004),
               (1005, 49.99, 1, 111113, 10000004)]

for p in lineitem_data:
    format_str = """INSERT INTO Line_Item (SKU, Price, Quantity, Product_Id, Cart_Id)
    VALUES ("{sku}", "{price}", "{quant}", "{pid}", "{cid}" );"""

    sql_command = format_str.format(sku=p[0], price=p[1], quant=p[2], pid=p[3], cid=p[4])
    cursor.execute(sql_command)


connection.commit()

connection.close()