from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, String, DateTime,
                        ForeignKey, Boolean, create_engine, insert)


class DataAccessLayer:
    connection = None
    engine = None
    conn_string = None
    metadata = MetaData()

    cookies = Table(
        'cookies', metadata,
        Column('cookie_id', Integer(), primary_key=True),
        Column('cookie_name', String(50), index=True),
        Column('cookie_recipe_url', String(255)),
        Column('cookie_sku', String(55)),
        Column('quantity', Integer()),
        Column('unit_cost', String(13))
    )

    users = Table(
        'users', metadata,
        Column('user_id', Integer(), primary_key=True),
        Column('customer_number', Integer(), autoincrement=True),
        Column('username', String(15), nullable=False, unique=True),
        Column('email', String(255), nullable=False),
        Column('phone', String(20), nullable=False),
        Column('password', String(25), nullable=False),
        Column('created_on', DateTime(), default=datetime.now()),
        Column('update_on', DateTime(), default=datetime.now(), onupdate=datetime.now())
    )

    orders = Table(
        'orders', metadata,
        Column('order_id', String(), primary_key=True),
        Column('user_id', ForeignKey('users.user_id')),
        Column('shipped', Boolean(), default=False)
    )

    # The ForeignKey is used like an import statement in python which is used to get the column from other tables

    list_items = Table(
        'line_items', metadata,
        Column('list_items_id', Integer(), primary_key=True),
        Column('order_id', ForeignKey('orders.order_id')),
        Column('cookie_id', ForeignKey('cookies.cookie_id')),
        Column('quantity', Integer()),
        Column('extended_cost', String())
    )

    def db_init(self, conn_string):
        self.engine = create_engine(conn_string or self.conn_string)
        self.metadata.create_all(self.engine)
        self.connection = self.engine.connect()


dal = DataAccessLayer()


def prep_db():
    ins = dal.cookies.insert()
    dal.connection.execute(ins, cookie_name='dark chocolate chip',
                           cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html',
                           cookie_sku='CC02',
                           quantity='1',
                           unit_cost='0.75')
    inventory_list = [{
        'cookie_name': 'peanut butter',
        'cookie_recipe_url': 'http://some.aweso.me/cookie/peanut.html',
        'cookie_sku': 'PB01',
        'quantity': '24',
        'unit_cost': '0.25'
    },
    {
        'cookie_name': 'oatmeal raisin',
        'cookie_recipe_url': 'http://some.okay.me/cookie/raisin.html',
        'cookie_sku': 'EWW01',
        'quantity': '100',
        'unit_cost': '1.00'
    }
    ]
    dal.connection.execute(ins, inventory_list)
    customer_list = [
    {
        'username': "cookiemon",
        'email': "mon@cookie.com",
        'phone': "111-111-1111",
        'password': "password"
    },
    {
        'username': "cakeeater",
        'email': "cakeeater@cake.com",
        'phone': "222-222-2222",
        'password': "password"
    },
    {
        'username': "pieguy",
        'email': "guy@pie.com",
        'phone': "333-333-3333",
        'password': "password"
    }
    ]
    ins = dal.users.insert()
    dal.connection.execute(ins, customer_list)
    ins = insert(dal.orders).values(user_id=1, order_id='wlk001')
    dal.connection.execute(ins)
    ins = insert(dal.list_items)
    order_items = [
    {
        'order_id': 'wlk001',
        'cookie_id': 1,
        'quantity': 2,
        'extended_cost': 1.00
    },
    {
        'order_id': 'wlk001',
        'cookie_id': 3,
        'quantity': 12,
        'extended_cost': 3.00
    }
    ]
    dal.connection.execute(ins, order_items)
    ins = insert(dal.orders).values(user_id=2, order_id='ol001')
    dal.connection.execute(ins)
    ins = insert(dal.list_items)
    order_items = [
    {
        'order_id': 'ol001',
        'cookie_id': 1,
        'quantity': 24,
        'extended_cost': 12.00
    },
    {
        'order_id': 'ol001',
        'cookie_id': 4,
        'quantity': 6,
        'extended_cost': 6.00
    }
    ]
    dal.connection.execute(ins, order_items)