#!/usr/bin/python
# -*- coding: utf-8 -*-
'''
@since: 2014-11-28
@author: Javier Garcia
@contact: javier.garcia@bskapital.com
@summary: Update from websources and csv the Master Securities
          SQLITE database. 


'''
# General imports
import pandas.io.data as web
import pandas as pd
import numpy as np
import datetime
import Quandl
import sqlite3
from pprint import pprint
import time
# useful links:
# http://www.pythoncentral.io/advanced-sqlite-usage-in-python/

# CONSTANTS
WORKING_ON = 'PC'
QUANDL_TOKEN = "ryDzS4euF3UoFtYwswQp"


if WORKING_ON == 'PC':
    DATABASE = ('C:/Users/javgar119/Documents/Dropbox'
                '/SEC_MASTER/securities_master.sqlite')
elif WORKING_ON == 'MAC':
    DATABASE = ('//Users/Javier/Dropbox/SEC_MASTER'
                '/securities_master.sqlite')


###############################################################################


def connect_to_database(db_address):
    """
    :param from_location:
    """
    try:
        connection = sqlite3.connect(
            db_address, detect_types=sqlite3.PARSE_DECLTYPES
            | sqlite3.PARSE_COLNAMES)
        return connection
    except sqlite3.Error as err:
        print('Error connecting database', err.args[0])


def load_to_sec_master(database_conn, list_to_insert, symbol):
    """

    :param database:
    :param list_to_insert:
    """
    try:
        pointer = database_conn.cursor()
        # insert update values
        pointer.executemany(
            "INSERT OR REPLACE INTO prices VALUES (?,?,?,?,?,?,?,?,?,?,?)",
            list_to_insert)
        database_conn.commit()

        # get last update date for symbol
        last_update_qry = ('SELECT DATE(max(date)) AS price_date FROM prices'
                           ' WHERE symbol = "{}"').format(symbol)
        pointer.execute(last_update_qry)
        last_update_eod = pointer.fetchone()[0]

        # update info about last update date in symbols
        sql_update_symbols = ('UPDATE symbols SET last_update_eod = "{}"'
                              ' WHERE symbol = "{}"').format(last_update_eod,
                                                             symbol)
        pointer.execute(sql_update_symbols)
        database_conn.commit()
        print('{} update {} lines up-to-date {}'
              .format(symbol, len(list_to_insert), last_update_eod))
        return True
    except sqlite3.Error as err:
        print('Error loading data', err.args[0])
        return False


def prepare_list_for_loading(vendor_results, symbol, symbol_data):
    """

    :param vendor_results:
    :param symbol:
    :param symbol_data:
    """
    # print(vendor_results.tail(10))
    try:
        # create an index by join date + symbol, since SQLite does not have
        # clustered indexes this is my solution

        idx = symbol + \
            pd.Series(vendor_results.index, index=vendor_results.index).map(
                lambda x: x.strftime('%Y%m%d'))

        price_date = pd.Series(vendor_results.index,
                               index=vendor_results.index).map(
            lambda x: datetime.date(x.year, x.month, x.day))
        # add the series to the dataframe
        vendor_results['idx'] = idx
        vendor_results['created_date'] = datetime.date.today()
        vendor_results['symbol'] = symbol
        vendor_results['date'] = price_date

        if 'open_interest' not in vendor_results.columns:
            vendor_results['open_interest'] = None

        if 'Adj Close' not in vendor_results.columns:
            vendor_results['Adj Close'] = None

        if 'Volume' not in vendor_results.columns:
            vendor_results['Volume'] = None

        # rename the columns
        vendor_results = vendor_results.rename(
            columns={'Open': 'open',
                     'High': 'high',
                     'Low': 'low',
                     'Close': 'close',
                     'Volume': 'volume',
                     'Adj Close': 'adj_close'})

        # rearrange the columns
        cols = ['idx', 'symbol',
                'created_date',
                'date', 'open',
                'high', 'low',
                'close', 'adj_close',
                'volume', 'open_interest']

        vendor_results = vendor_results[cols]

        # Even as the column is declares as NUMERIC, it does not accept an
        # integer. :(
        vendor_results['volume'] = vendor_results['volume'].astype(float)
        output = vendor_results.itertuples(index=False)
        data = list(output)
        return data
    except:
        print('Error prepare_list_for_loading()')
        raise


def query_to_dictionary(query_result):
    """
    helper creates a dictionary from the database query to the format
    needed for the update function.
    :param query_result: dict(symbol: (last_update_eod))
    """
    # print('query_result', query_result)
    ans = dict()
    for row in query_result:
        data_for_symbol = (row[1], row[2], row[3])
        ans[row[0]] = data_for_symbol
    return ans


def get_symbols_to_update(database_conn):
    """
    INPUTS:
    :param securities_database:
    RETURNS:
    :return dictionary with info of securities to update
    """

    try:
        pointer = database_conn.cursor()
        pointer.execute('''SELECT symbol, vendor_symbol, vendor, 
                        last_update_eod FROM symbols WHERE to_update=1;''')
        query_result = pointer.fetchall()
        ans = query_to_dictionary(query_result)
        return ans
    except sqlite3.Error as err:
        print('Error get_symbols_to_update()', err.args[0])


def vendor_query(vendor, symbol, from_date, to_date):
    """
     Make a web query to data vendor
    :param vendor: 'QUANDL', 'YAHOO'
    :param symbol:
    :param from_date:
    :param to_date:
    """
    try:
        if vendor == "QUANDL":
            web_qry_result = Quandl.get(symbol,
                                        trim_start=from_date,
                                        trim_end=to_date,
                                        authtoken=QUANDL_TOKEN,
                                        verbose=False)
        elif vendor == "YAHOO":
            web_qry_result = web.get_data_yahoo(symbol,
                                                start=from_date,
                                                end=to_date)

        elif vendor == 'CSV':
            raw_read = pd.read_csv(symbol, index_col='Date', parse_dates=True)
            web_qry_result = raw_read[from_date:to_date]

        return web_qry_result
    except:
        print('Error querying the vendor')


def run_update(db_address):
    """
    Puts everything together
    """
    time0 = time.time()

    database_conn = connect_to_database(db_address)
    symbols = get_symbols_to_update(database_conn)

    result = False
    for symbol, symbol_data in symbols.items():
        print(symbol, ': updating...')
        date_to = datetime.date.today() - datetime.timedelta(1)
        if symbol_data[2] >= date_to:
            result = True
            print('{} up-to-date {}. No query was made.'
                  .format(symbol, symbol_data[2].strftime('%Y-%m-%d')))
        else:
            vendor = symbol_data[1]
            date_from = symbol_data[2].strftime('%Y-%m-%d')
            symbol_in_vendor = symbol_data[0]

            vendor_return = vendor_query(vendor,
                                         symbol_in_vendor,
                                         date_from,
                                         date_to)

            list_ready_to_load = prepare_list_for_loading(vendor_return,
                                                          symbol,
                                                          symbol_data)

            result = load_to_sec_master(database_conn,
                                        list_ready_to_load,
                                        symbol)

    database_conn.close()
    time1 = time.time()
    print('\nUpdate result:', result)
    print('\nUpdated in: {} seconds'.format(time1 - time0))


if __name__ == '__main__':

    run_update(DATABASE)
