import MySQLdb as sql

import config

def sql_connect():
    db = sql.connect(config.db_host, config.db_user, config.db_password, config.db_database)
    return db

def __sql_execute(db, query, *args, **kwargs):
    cursor = db.cursor(sql.cursors.DictCursor)
    r = cursor.execute(query, *args, **kwargs)
    if not query.startswith("SELECT"):
        cursor.close()
        return r

    data = cursor.fetchall()
    cursor.close()
    return data

def initialize(db):
    __sql_execute(db, "DROP TABLE IF EXISTS %sproducts;" % config.db_prefix)
    __sql_execute(db, "DROP TABLE IF EXISTS %srestocks;" % config.db_prefix)
    __sql_execute(db, "DROP TABLE IF EXISTS %stopups;" % config.db_prefix)
    __sql_execute(db, "DROP TABLE IF EXISTS %sfinancing;" % config.db_prefix)
    __sql_execute(db, "DROP TABLE IF EXISTS %spurchases;" % config.db_prefix)

    __sql_execute(db, """CREATE TABLE %sproducts (
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    code VARCHAR(100) NOT NULL,
    cost INT(11) NOT NULL,
    value INT(11) NOT NULL);""" % config.db_prefix)

    __sql_execute(db, """CREATE TABLE %srestocks (
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    product_id INT(11) NOT NULL,
    title VARCHAR(100) NOT NULL,
    datetime DATETIME NOT NULL,
    count INT(11) NOT NULL);""" % config.db_prefix)

    __sql_execute(db, """CREATE TABLE %sfinancing (
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    datetime DATETIME NOT NULL,
    value INT(11) NOT NULL);""" % config.db_prefix)

    __sql_execute(db, """CREATE TABLE %stopups (
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    value INT(11) NOT NULL,
    datetime DATETIME NOT NULL,
    user_id VARCHAR(40) NOT NULL);""" % config.db_prefix)

    __sql_execute(db, """CREATE TABLE %spurchases (
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    datetime DATETIME,
    product_id  INT(11) NOT NULL,
    user_id VARCHAR(40) NOT NULL);""" % config.db_prefix)

def add_product(db, name, code, value, cost):
    __sql_execute(db, "INSERT INTO %sproducts (name, code, value, cost) VALUES (%%s, %%s, %%s, %%s);" % config.db_prefix, (name, code, value, cost))


def add_restock(db, product_id, title, count):
    __sql_execute(db, "INSERT INTO %srestocks (product_id, title, count, datetime) VALUES (%%s, %%s, %%s, NOW());" % config.db_prefix, (product_id, title, count))

def add_financing(db, title, value):
    __sql_execute(db, "INSERT INTO %sfinancing (title, value, datetime) VALUES (%%s, %%s, NOW());" % config.db_prefix, (title, value))

def add_topup(db, user_id, value):
    __sql_execute(db, "INSERT INTO %stopups (value, user_id, datetime) VALUES (%%s, %%s, NOW());" % config.db_prefix, (value, user_id))

def add_purchase(db, user_id, product_id, code=None):
    if code:
        product_id = __sql_execute(db, "SELECT id FROM %sproducts WHERE code=%%s" % 
                config.db_prefix, (code,))[0]['id']
    __sql_execute(db, "INSERT INTO %spurchases (product_id, user_id, datetime) VALUES (%%s, %%s, NOW());" % config.db_prefix, (product_id, user_id))

def print_info(db):
    f = get_financing_data(db)
    u = get_users_data(db)
    s = get_summary(db)
    r = get_restocks_data(db)
    p = get_products_data(db)

    print " FINANCING"
    print "-" * 76
    print "% 40s | % 20s | % 10s" % ("title", "date", "value")
    print "-" * 76
    for financing in f["financings"]:
        print "% 40s | % 20s | % 10.2f" % (financing["title"], financing["datetime"].strftime("%H:%M %m/%d/%y"), financing["value"]/100.0)
    print "-" * 76
    print " " * (63 - len("total")) + "total | " + "% 10.2f" % (f["financings_amount"]/100.0)
    print "-" * 76


    print "\n\n USERS"
    print "-" * 76
    print "% 63s | % 10s" % ("user id", "balance")
    print "-" * 76
    for user_id, balance in u["balance"].iteritems():
        print "% 63s | % 10.2f" % (user_id, balance/100.0)
    print "-" * 76

    print "\n\n PRODUCTS"
    print "-" * 76
    print "% 24s | % 10s | % 10s | % 10s | % 10s" % ("name", "sold", "in stock", "revenue", "profit")
    print "-" * 76
    for product_id, product in p["products"].iteritems():
        print "% 24s | % 10i | % 10i | % 10.2f | % 10.2f" % (product["name"], product["sold"], product["available"], product["revenue"]/100.0, product["profit"]/100.0)
    print "-" * 76

    print "\n\n RESTOCKS"
    print "-" * 76
    print "% 21s | % 15s | % 15s | % 6s | % 6s" % ("title", "date", "product", "count", "cost")
    print "-" * 76
    for restock in r["restocks"]:
        print "% 21s | % 15s | % 15s | % 6i | % 6.2f" % (restock["title"], restock["datetime"].strftime("%H:%M %m/%d/%y"), restock["name"], restock["count"], restock["count"] * restock["cost"]/100.0)
    print "-" * 76

    print "\n\n"
    print "Revenue (without financing): %.2f" % (s["revenue"]/100.0)
    print "Revenue    (with financing): %.2f" % (s["revenue_financing"]/100.0)
    print "Expenses                   : %.2f" % (s["expenses"]/100.0)
    print "Profit                     : %.2f" % (s["profit"]/100.0)
    print "Vault                      : %.2f" % (s["real_money"]/100.0)

def get_financing_data(db):
    financings = __sql_execute(db, "SELECT title, datetime, value from %sfinancing;" % config.db_prefix)
    financings_amount = 0
    for financing in financings:
        financings_amount += financing["value"]

    return {"financings": financings, "financings_amount": financings_amount}

def get_restocks_data(db):
    restocks = __sql_execute(db, "SELECT %srestocks.product_id, %srestocks.title, %srestocks.datetime, %srestocks.count, %sproducts.cost, %sproducts.name FROM %srestocks LEFT JOIN %sproducts on %srestocks.product_id = %sproducts.id;" % (config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix))
    return {"restocks": restocks}

def get_products_data(db):
    products = __sql_execute(db, "SELECT id, name, cost, value, code FROM %sproducts;" % config.db_prefix)
    purchases = __sql_execute(db, "SELECT COUNT(id), product_id FROM %spurchases GROUP BY product_id;" % config.db_prefix)
    purchases_dict = dict([(purchase["product_id"], purchase["COUNT(id)"]) for purchase in purchases])
    restocks = get_restocks_data(db)["restocks"]
    restocks_dict = {}
    for restock in restocks:
        if restock["product_id"] not in restocks_dict:
            restocks_dict[restock["product_id"]] = 0
        restocks_dict[restock["product_id"]] += restock["count"]
    r = {}

    for product in products:
        p = {}

        sold = 0
        if product["id"] in purchases_dict:
            sold += purchases_dict[product["id"]]

        stocked = 0
        if product["id"] in restocks_dict:
            stocked = restocks_dict[product["id"]]

        p["name"] = product["name"]
        p["sold"] = sold
        p["stocked"] = stocked
        p["available"] = stocked - sold
        p["value"] = product["value"]
        p["cost"] = product["cost"]
        p["revenue"] = product["value"] * sold
        p["profit"] = p["revenue"] - product["cost"] * sold
        p["code"] = product["code"]
        r[product["id"]] = p

    return {"products": r}

def get_summary(db):
    u = get_users_data(db)
    f = get_financing_data(db)

    real_money = 0
    for topup in u["topups"]:
        real_money += topup["value"]

    revenue = real_money

    real_money += f["financings_amount"]
    revenue_financing = real_money

    restocks = get_restocks_data(db)["restocks"]
    expenses = 0
    for restock in restocks:
        expenses += int(restock["count"]) * int (restock["cost"])

    profit = revenue - expenses
    real_money -= expenses

    return {"revenue": revenue, "revenue_financing": revenue_financing, "expenses": expenses, "profit": profit, "real_money": real_money}

def get_users_data(db):
    topups = [{"user_id": topup["user_id"], "value": int(topup["SUM(value)"])} for topup in __sql_execute(db, "SELECT user_id, SUM(value) FROM %stopups GROUP BY user_id" % config.db_prefix)]
    users = dict([(topup["user_id"], topup["value"]) for topup in topups])

    purchases = __sql_execute(db, "SELECT %spurchases.user_id, SUM(%sproducts.value) FROM %spurchases LEFT JOIN %sproducts on %spurchases.product_id = %sproducts.id GROUP BY %spurchases.user_id;" % (config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix, config.db_prefix))

    for purchase in purchases:
        if purchase["user_id"] not in users:
            users[purchase["user_id"]] = 0

        users[purchase["user_id"]] -= int(purchase['SUM(hf_products.value)'])

    return {"balance":users, "topups": topups}