#!/usr/bin/env python

import pandas as pd
import numpy as np
import MySQLdb as db

#!connection = db.connect(user='jaju15', passwd='hkw4AqEQzRPv', host = 'blu-ray.student.bth.se')
#!c = connection.cursor()

def get_products_filtered(categories=None):
    #df = pd.read_csv('data/Products.csv')
    #if categories is not None:
    #   for category in categories.keys():
    #       df = df[df[category] == categories[category]]
    #print categories
    ''' SQL '''
    connection = db.connect(user='jaju15', passwd='hkw4AqEQzRPv', db='jaju15', host='blu-ray.student.bth.se')
    c = connection.cursor()
    Overskrifter = ['color', 'gender' , 'brand', 'price', 'subtype', 'type', 'id', 'size' ]
    c.execute(''' Call get_all_products()''')

    products = c.fetchall()

    rows = []

    for row in products:
        dictionairy = {}
        for i in range(len(Overskrifter)):
            dictionairy[Overskrifter[i]] = row[i]

        rows.append(dictionairy)


    if categories is not None:

        c.execute(''' Call get_filtered_products(%s,%s,%s)''',(categories['gender'], categories['type'], categories['subtype']))

        products = c.fetchall()

        rows = []

        for row in products:
            dictionairy = {}
            for i in range(len(Overskrifter)):
                dictionairy[Overskrifter[i]] = row[i]

            rows.append(dictionairy)

    return rows


    #return df.to_dict('records')

def get_products_search(values):
    #df = pd.read_csv('data/Products.csv')
    #df = df[df['brand'].str.contains('(?i)' + '|'.join(values))]
    ''' SQL '''
    #print values
    #print df.to_dict('records')
    #return df.to_dict('records')


    connection = db.connect(user='jaju15', passwd='hkw4AqEQzRPv', db='jaju15', host='blu-ray.student.bth.se')
    c = connection.cursor()
    value = ''
    lastspot = len(values)
    for i in range(len(values)):
        if i == (lastspot-1):
            value = value + values[i]

        else :
            value =value + values[i] +' '




    c.execute(''' Call search_product(%s) ''',[value])
    products = c.fetchall ()


    rows = []
    Overskrifter = ['color', 'gender' , 'brand', 'price', 'subtype', 'type', 'id', 'size' ]

    for row in products:
        dictionairy = {}
        for i in range(len(Overskrifter)):
            dictionairy[Overskrifter[i]] = row[i]

        rows.append(dictionairy)
    print rows

    return rows



def get_products_ids(ids):
    #df = pd.read_csv('data/Products.csv')
    #df = df.loc[df['id'].isin(ids)]
    ''' SQL '''
    print ids

    #return df.to_dict('records')

    connection = db.connect(user='jaju15', passwd='hkw4AqEQzRPv', db='jaju15', host='blu-ray.student.bth.se')
    c = connection.cursor()
    Overskrifter = ['color', 'gender' , 'brand', 'price', 'subtype', 'type', 'id', 'size' ]
    idCorrect = []
    count = 0

    for i in range(len(ids)):
        insert = 1
        for k in range(len(idCorrect)):
            if ids[i] == (idCorrect[k]):
                insert = 0

        if insert == 0:
            count = count +1

        if insert == 1:
            idCorrect.append(ids[i])

    products = []
    for i in range(len(idCorrect)):
        c.execute(''' CALL get_product_ID(%d) ''' % idCorrect[i])
        data = c.fetchone()
        dictionairy = {}
        for j in range(len(Overskrifter)):
            dictionairy[Overskrifter[j]] = data[j]

        products.append(dictionairy)
    print products
    return products



def get_categories():
    #df = pd.read_csv('data/Products.csv')
    #genders = df['gender'].unique()
    #types = [df[(df['gender'] == genders[0])]['type'].unique().tolist(), df[(df['gender'] == genders[1])]['type'].unique().tolist()]
    #children = [[{'url': '', 'name': name} for name in types[0]],[{'url': '', 'name': name} for name in types[1]]]
    ''' SQL '''

    #result = [{'title' : genders[0], 'children': children[0]}, {'title': genders[1], 'children': children[1]}]
    #return result
    connection = db.connect(user='jaju15', passwd='hkw4AqEQzRPv', db='jaju15', host='blu-ray.student.bth.se')
    c = connection.cursor()

    c.execute(''' SELECT DISTINCT kon FROM konAlderPlagg where kon = 'herr' or kon = 'dam' ''')

    genders = []
    genderrow = c.fetchall()
    for genderrows in genderrow:
        genders.append(genderrows[0])



    print genders


    c.execute('''SELECT DISTINCT kon,overkategoriTyp from ((underKategori inner JOIN konAlderPlagg on underKategori.KonId = konAlderPlagg.idKonalder)
    INNER JOIN overKategori on kategorid = overKategorid)''')
    rowskategori = c.fetchall()


    children =[]


    result = [  {'title': genders[0], 'children' : []},
                {'title': genders[1], 'children': []}]

    for row in rowskategori:
        if row[0] == genders[0]:
            result[0]['children'].append({'url': '', 'name': row[1]})
        if row[0] == genders[1]:
            result[1]['children'].append({'url': '', 'name': row[1]})

    print result

    return result
def get_subcategories(gender, category):
    #df = pd.read_csv('data/Products.csv')
    #types = df[(df['gender'] == gender) & (df['type'] == category)]['subtype'].unique().tolist()
    #children = [{'url': '', 'name': name} for name in types]
    #result = [{'gender' : gender, 'category': category, 'children': children}]



    ''' SQL '''
    connection = db.connect(user='jaju15', passwd='hkw4AqEQzRPv', db='jaju15', host='blu-ray.student.bth.se')
    c = connection.cursor()
    c.execute(''' CALL get_subcategories(%s, %s) ''', (gender, category))
    rows = c.fetchall()

    children = []
    for row in rows:

        children.append({'url': '', 'name': row[0]})

    result = [{'gender' : gender, 'category': category, 'children': children}]



    return result

def write_order(order):
  df_orders = pd.read_csv('data/Orders.csv')
  # Get new order ID
  #orderID = df_orders['orderid'].max() + 1
  # Grab the products id number and the amount of each product
  item_ids = list(map(int, order['items'].strip('[]').split(',')))
  items = [{'id': int(x), 'amount': item_ids.count(x)} for x in list(set(item_ids))]

  # Get the name and so on for the customer.
  firstname, lastname = order['name'].split()
  email = order['email']
  address = order['address']
  zipcode = order['zipcode']
  town = order['town']

  # Write the actual order
  #df_products = pd.read_csv('data/Products.csv')
  #for item in items:
   # product = df_products[df_products['id'] == item['id']].to_dict('records')[0]
    #df_orders.loc[len(df_orders)] = [orderID, firstname, lastname, address, town, zipcode, product['id'], product['brand'], product['type'], product['subtype'], product['color'], product['gender'], product['price'], product['size'], item['amount']]
  #df_orders.to_csv('data/Orders.csv', index=False)

    #connection = db.connect(user='jaju15', passwd='hkw4AqEQzRPv', db='jaju15', host='blu-ray.student.bth.se')


    #c = connection.cursor()

    #c.execute (''' CALL CreateCustomer(%s,%s,%s,%d,%s) ''',(firstname,lastname,address,zipcode,email))
    #c.execute(''' SELECT max(KundId) FROM kunder ''')
    #KundId = c.fetchone
    #c.execute('''skapaorder(%d) ''', KundId)
    #c.execute(''' SELECT max(orderid) FROM Ordrar ''')
    #OrderId = c.fetchone
    #for item in items
    #   c.execute('''InsertOrderlista(%d,%d,%d) ''',(orderid,item['id'], item['amount']))

    #c.execute(''' Call UpdateOrder(%s)''', OrderId)





    #connection.commit()

def get_20_most_popular():
    # Group by article # and sum of amount in Orders.
    # grab the top 20 from Products and return as dict
    #df = pd.read_csv('data/Orders.csv')
    #op20_ids = df.groupby(['id']).sum().loc[:,['amount']].sort_values('amount', ascending=False).iloc[:20].index.tolist()
    #df = pd.read_csv('data/Products.csv')
    connection = db.connect(user='jaju15', passwd='hkw4AqEQzRPv', db='jaju15', host='blu-ray.student.bth.se')


    c = connection.cursor()

    c.execute (''' CALL get_20_most_popular() ''')
    popular = c.fetchall()
    print popular
    Overskrifter = ['color', 'gender' , 'brand', 'price', 'subtype', 'type', 'id', 'size' ]
    rows = []
    for row in popular:
        dictionairy = {}
        for i in range(len(Overskrifter)):
            dictionairy[Overskrifter[i]] = row[i]

        rows.append(dictionairy)

    return rows



def main():

    #test = get_products_filtered({'color': 'Red', 'brand': 'WESC', 'price': 1599, 'size': 'XS'})
    #test = get_products_filtered({'type': 'Bags', 'subtype': 'Leather bag'})
    #test = get_products_search(['Red', 'Jack and Jones'])
    #print(get_categories())
    #print(get_subcategories('Female', 'Bags'))
    print(get_20_most_popular())
    #write_order({'town': 'asad', 'name': 'asd asd', 'items': '[2160,2160,2160,2160,2160,2160,2160,2160,2160]', 'zipcode': '123123', 'address': 'asd', 'email': 'asd'})
    #test = get_products_search(['jack', 'and', 'jones'])
    #print(test)
    #print (my_get_most_popular())

if _name_ == '_main_':
    main()