from bbg_helpers import init_bbg_session, retreive_data, process_msgs
import pandas as pd
from sqlalchemy import create_engine
from pandas.tseries.offsets import BDay
from pandas import bdate_range
import re
import os

engine = create_engine('postgresql://dawn_user@debian/dawndb')

def get_bbg_numbers(workdate, fields):
    positions = pd.read_sql_query("select * from list_positions(%s)", engine, params=(workdate.date(),))
    positions.loc[positions.identifier.str.endswith('_A'),'bbg_id'] = positions.identifier.str.slice(stop=9)
    positions.loc[~positions.identifier.str.endswith('_A'),'bbg_id'] = positions.identifier.str.slice(stop=12)
    positions.bbg_id = positions.bbg_id + ' ' + positions.bbg_type
    data = retreive_data(session, positions.bbg_id.tolist(), fields, workdate)
    df = process_msgs(data)
    df = pd.DataFrame.from_dict(df, orient='index')
    return positions.merge(df, left_on='bbg_id', right_index=True)

def get_list(workdate):
    positions = pd.read_sql_query("select * from list_positions(%s)", engine, params=(workdate.date(),))
    positions.loc[positions.identifier.str.len() <= 11, 'cusip'] = positions.identifier.str.slice(stop=9)
    positions.loc[positions.identifier.str.len() == 12, 'isin'] = positions.identifier
    return positions

def backpopulate_marks(begin_str='2015-01-15', end_str='2015-07-15'):
    pattern = re.compile("\d{4}-\d{2}-\d{2}")
    list_of_daily_folder = (fullpath for (fullpath, _, _)  in os.walk('/home/share/Daily')
                            if pattern.match(os.path.basename(fullpath)))
    list_of_bdays = bdate_range(start=begin_str, end=end_str)
    for path in list_of_daily_folder:
        date = pd.to_datetime(os.path.basename(path))
        if date in list_of_bdays:
            marks_file = [f for f in os.listdir(path) if f.startswith("securitiesNpv")]
            if marks_file:
                marks_file.sort(key=lambda x:x[13:], reverse=True) #sort by lexicographic order which is what we want since we use ISO dates
                marks = pd.read_csv(os.path.join(path, marks_file[0]))
                positions = get_list(pd.to_datetime(date))
                positions = positions.merge(marks, left_on='identifier', right_on='IDENTIFIER')
                positions.drop(['IDENTIFIER', 'last_settle_date'], axis=1, inplace=True)
                positions['date'] = date
                positions.rename(columns={'Price': 'price'}, inplace=True)
                positions = positions.drop_duplicates()
                positions.to_sql('position', engine, if_exists='append', index=False)

if __name__=="__main__":
    session = init_bbg_session('192.168.0.4', 8194)
    fields = ["START_ACC_DT", "MTG_FACTOR_PAY_DT", "CUR_CPN", "INT_ACC",
              "DAYS_ACC", "MTG_FACTOR", "MTG_PREV_FACTOR", "MTG_FACTOR_PRINC_PAY",
              "MTG_PRINC_LOSSES"]
    workdate = pd.datetime.today()
    df = get_bbg_numbers(workdate, fields)
    # df.to_csv("../../positions_daily/positions_{0:%Y-%m-%d}.csv".format(workdate))
    # df.rename(columns={'CUR_CPN': 'coupon',
    #                    'MTG_FACTOR': 'factor',
    #                    'price':
    # conn = engine.raw_connection()
    # with conn.cursor() as c:
    #     c.executemany("UPDATE position SET WHERE identifier= %(identifier)s
    # with engine.raw_connenction.cursor() as c: