from java import *
from java.sql import *
from java.lang import *

from java.sql import DriverManager
from java.lang import Class

from com.ziclix.python.sql import zxJDBC
from sqlalchemy import create_engine
from sqlalchemy import Table
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper, relation
from sqlalchemy.ext.orderinglist import ordering_list

from decimal import *

target_db = create_engine('postgresql+zxjdbc://#####:######@localhost/otis-bone')
Session = sessionmaker(bind=target_db)
session = Session()

meta = MetaData()

# figure out how to import rattail into jython

#####################################################################
################## models, classes and mappers ######################
#####################################################################

products = Table('products', meta, autoload=True, autoload_with=target_db);
product_barcodes = Table('product_barcodes', meta, autoload=True, autoload_with=target_db);
product_sources = Table('product_sources', meta, autoload=True, autoload_with=target_db);
prices = Table('prices', meta, autoload=True, autoload_with=target_db);
vendors = Table('vendors', meta, autoload=True, autoload_with=target_db);
departments = Table('departments', meta, autoload=True, autoload_with=target_db);
orders_products = Table('orders_products', meta, autoload=True, autoload_with=target_db);
orders = Table('orders', meta, autoload=True, autoload_with=target_db);


class Product(object):
    def __init__(self, uuid='None', description='None', department_uuid='None'):
        self.description = description
        self.uuid = uuid
        self.department_uuid = department_uuid
    def __repr__(self):
        return self.description

class ProductBarcode(object):
    def __init__(self, index='None', barcode='None', product_uuid='None'):
        self.index = index
        self.barcode = barcode
        self.product_uuid = product_uuid
    def __repr__(self):
        return self.barcode

class ProductSource(object):

    def __init__(self, uuid=None, product=None, vendor=None, index=None, sku=None, quantity=None, suggested_retail=None, unit_cost=None, cost_recorded=None, cost_expires=None):
        self.product = product
        self.uuid = uuid
        self.vendor = vendor
        self.index = index
        self.sku = sku
        self.quantity = quantity
        self.suggested_retail = suggested_retail
        self.unit_cost = unit_cost
        self.cost_recorded = cost_recorded
        self.cost_expires = cost_expires

class Price(object):
    def __init__(self, uuid='None', amount='None', product_uuid='None'):
        self.uuid = uuid
        self.amount = amount
        self.product_uuid = product_uuid
    def __repr__(self):
        return self.barcode

class Vendor(object):
    def __init__(self, uuid='None', name=None):
        self.name = name
        self.uuid = uuid
    def __repr__(self):
        return self.name

class Department(object):
    def __init__(self, uuid='None', name=None, base_department=None, tax_rate=None):
        self.name = name
        self.uuid = uuid
        self.base_department = base_department
        self.tax_rate = tax_rate
    def __str__(self):
        return self.name

class OrderProduct(object):
    def __init__(self, order=None, product=None, price=None, delivered=None, purchase_order=None, status=None):
        self.order = order
        self.product = product
        self.price = price
        self.delivered = delivered
        self.purchase_order = purchase_order
        self.status = self.status
    def __repr__(self):
        return "order:%s, product:%s" % (self.order, self.product)

class Order(object):
    def __init__(self, uuid=None, status=None, opened=None, closed=None, employee=None, customer=None):
        self.uuid = uuid
        self.status = status
        self.opened = opened
        self.closed = closed
        self.employee = employee
        self.customer = customer
    def __repr__(self):
        return "#%s" % self.uuid


mapper(
    Product, products,
    properties = dict(
        _barcodes = relation(ProductBarcode, collection_class=ordering_list("index"), order_by=product_barcodes.c.index, cascade="all, delete-orphan", backref="product"),
        sources = relation(ProductSource),
        prices = relation(Price),
        ),
    )
mapper(ProductBarcode,product_barcodes)
mapper(ProductSource,product_sources,
        properties = dict(
            product = relation(Product),
            vendor = relation(Vendor),
            ),
        )
mapper(Price, prices)
mapper(Vendor, vendors)
mapper(Department, departments)
mapper(OrderProduct, orders_products)
mapper(Order, orders)


################################################################
############# ETL ##############################################
################################################################

db = zxJDBC.connect('jdbc:pervasive://##############/DEMODATA', None, None, "com.pervasive.jdbc.v2.Driver")

c = db.cursor()

# drop data from target db
session.query(ProductBarcode).delete()
session.query(Price).delete()
session.query(ProductSource).delete()
session.query(Vendor).delete()
session.query(OrderProduct).delete()
session.query(Order).delete()
session.query(Product).delete()
session.query(Department).delete()
session.commit()

############## departments ###################################

# extract data from source db
c.execute('SELECT Department, Description FROM Department')


# load data into target db
for record in c.fetchall():
    uuid = record[0]
    name = record[1]
    department = Department(uuid=uuid, name=name)
    session.add(department)
session.commit()

################# products, prices, upcs, vendors, skus ########

# extract data from source db
c.execute('SELECT UPC, UnitPrice, VendorNumber, ItemNumber, BOFDescription, Department FROM Item')

# load data into target db
i=1
for record in c.fetchall():
    upc_plu = record[0]
    amount = Decimal(record[1]) * Decimal('.01')
    sku = record[2] + record[3]
    description = record[4]
    department = record[5]

    existing_vendor = session.query(Vendor).filter(Vendor.name==record[2])
    if existing_vendor.count() < 1:
        vendor = Vendor(uuid=i, name=record[2])
        session.add(vendor)
    else:
        vendor = existing_vendor[0]

    product = Product(uuid=i, description=description, department_uuid=department)
    #barcode = ProductBarcode(index=i, product_uuid=i, barcode=upc_plu)
    barcode = ProductBarcode(index=i, barcode=upc_plu)
    product._barcodes.append(barcode)
    #product_source = ProductSource(index=i, uuid=i, quantity=1, product_uuid=i, sku=sku)
    product_source = ProductSource(uuid=i, index=i, quantity=1, sku=sku, product=product, vendor=vendor)
    #price = Price(amount=amount, product_uuid=product.uuid)
    price = Price(amount=amount)
    product.prices.append(price)

    session.add(product)

    #session.add(barcode)
    session.add(product_source)
    #session.add(price)
    i+=1
    if i==100: break

session.commit()