import redis
import pandas as pd
import csv
import sys
if sys.version_info.major == 3:
    from io import StringIO
else:
    from cStringIO import StringIO

import datetime
from pickle import loads
from ftplib import FTP
import config
import os
from sqlalchemy import create_engine
from bbg_helpers import init_bbg_session, retreive_data, process_msgs

def decode_dict(d):
    return {k.decode() if isinstance(k, bytes) else k: \
            v.decode() if isinstance(v, bytes) else v for k, v in d.items()}

def get_redis_queue():
    return redis.Redis(host = 'debian')

def aux(v):
    if v.action.iat[-1] == 'CANCEL':
        return None
    if v.action.iat[0] == 'NEW':
        v.action.iat[-1] = 'NEW'
    return v.iloc[-1]

def get_trades(q):
    r = q.lrange('trades', 0, -1)
    if sys.version_info.major == 3:
        df = pd.DataFrame([decode_dict(loads(e, encoding='bytes')) for e in r])
    else:
        df = pd.DataFrame([loads(e) for e in r])
    list_trades = []
    for tradeid, v  in df.sort('lastupdate').groupby('id'):
        trade = aux(v)
        if trade is not None:
            list_trades.append(trade)
    return list_trades

def build_line(obj):
    line = ["MortgageDeal", obj.dealid, obj.action ,"Serenitas", None, None , obj.folder,
            obj.custodian, obj.cashaccount, obj.cp_code, None, 'Valid',
            str(obj.trade_date), str(obj.settle_date), None, None, obj.cusip, obj['isin'],
            None, None, None, obj['description'], "Buy" if obj.buysell else "Sell", None,
            obj.accrued, obj.price, None, None, 'SERCGMAST', 'MORTGAGE',
            None, None, None, None, obj.faceamount, None, None, 'S']
    return line

def bbg_process(cursor, session, trade):
    fields = ["MTG_FACTOR_SET_DT", "INT_ACC"]
    cursor.execute("SELECT identifier FROM securities WHERE identifier=%s", (trade['identifier'],))
    if not cursor.fetchone():
        fields += ["MATURITY", "CRNCY", "NAME", "MTG_FACE_AMT", "FLOATER",
                   "FLT_SPREAD", "CPN", "CPN_FREQ", "FIRST_CPN_DT", "MTG_PAY_DELAY"]

    bbg_id = (trade['cusip'] or trade['isin']) + ' Mtge'
    bbg_type = 'Mtge'
    data = retreive_data(session, [bbg_id], fields, trade['settle_date'])
    df = process_msgs(data)
    if not df[bbg_id]:
        bbg_id = (trade['cusip'] or trade['isin']) + ' Corp'
        bbg_type = 'Corp'
        data = retreive_data(session, [bbg_id], fields, trade['settle_date'])
        df = process_msgs(data)

    bbg_data = df[bbg_id]
    if bbg_data.get('MTG_FACTOR_SET_DT', 0) == 0:
        bbg_data['MTG_FACTOR_SET_DT'] = 1
        bbg_data['INT_ACC'] = 0
    currentface = trade['faceamount'] * bbg_data['MTG_FACTOR_SET_DT']
    accrued_payment = bbg_data['INT_ACC'] * currentface /100.
    principal_payment = currentface * trade['price'] / 100.
    cursor.execute("UPDATE bonds SET principal_payment = %s, accrued_payment = %s "
                   "WHERE id = %s", (principal_payment, accrued_payment, trade['id']))
    if len(fields) > 2: #we don't have the data in the securities table
        sqlstr = "INSERT INTO securities VALUES({0})".format(",".join(["%s"] * 15))
        isfloater =  bbg_data['FLOATER'] == 'Y'
        pay_delay = bbg_data.get('MTG_PAY_DELAY', 0)
        if isinstance(pay_delay, str):
            pay_delay = int(pay_delay.split(' ')[0])
        cursor.execute(sqlstr, (trade['identifier'], trade['cusip'], trade['isin'],
                                bbg_data['NAME'], bbg_data.get('MTG_FACE_AMT'),
                                bbg_data.get('MATURITY'), isfloater,
                                bbg_data.get('FLT_SPREAD') if isfloater else None,
                                bbg_data.get('CPN') if not isfloater else None,
                                bbg_data.get('CPN_FREQ'), bbg_data.get('FIRST_CPN_DT'),
                                pay_delay,
                                bbg_data.get('CRNCY'), bbg_type, trade['asset_class']))
    #mark it at buy price
    if trade.buysell:
        sqlstr = "INSERT INTO marks VALUES(%s, %s, %s)"
        cursor.execute(sqlstr, (trade['trade_date'], trade['identifier'], trade['price']))

def generate_csv(l):
    output = StringIO()
    csvwriter = csv.writer(output)
    headers = ['Deal Type', 'Deal ID', 'Action', 'Client', 'Reserved', 'Reserved',
               'Folder', 'Custodian', 'Cash Account', 'Counterparty', 'Comments',
               'State', 'Trade Date', 'Settlement Date', 'Reserved', 'GlopeOp Security Identifier',
               'CUSIP', 'ISIN', 'Reserved', 'Reserved',
               'Reserved', 'Security Description', 'Transaction Indicator',
               'SubTransaction Indicator', 'Accrued', 'Price', 'BlockId', 'BlockAmount',
	       'Fund', 'Portfolio', 'Reserved', 'Reserved', 'ClientReference', 'ClearingMode',
               'FaceAmount', 'Pool Factor', 'FactorAsOfDate', 'Delivery']
    csvwriter.writerow(headers)
    for trade in l:
        csvwriter.writerow(build_line(trade))
    if sys.version_info.major == 3:
        return output.getvalue().encode()
    else:
        return output.getvalue()

def upload_file(timestamp):
    ftp = FTP('ftp.globeop.com')
    ftp.login('srntsftp', config.ftp_password)
    ftp.cwd('incoming')
    filename = 'Serenitas.ALL.{0:%Y%m%d.%H%M%S}.Mortgages.csv'.format(timestamp)
    cmd = 'STOR {0}'.format(filename)
    with open(os.path.join('/home/share/Daily', str(timestamp.date()), filename), 'rb') as fh:
        ftp.storbinary(cmd, fh)

def write_buffer(buf):
    timestamp = pd.datetime.now()
    filename = 'Serenitas.ALL.{0:%Y%m%d.%H%M%S}.Mortgages.csv'.format(timestamp)
    with open(os.path.join('/home/share/Daily', str(timestamp.date()), filename), 'wb') as fh:
        fh.write(buf)
    return timestamp

if __name__=="__main__":
    engine = create_engine('postgresql://dawn_user@debian/dawndb')
    conn = engine.raw_connection()
    q = get_redis_queue()
    l = get_trades(q)
    if l:
        buf = generate_csv(l)
        with conn.cursor() as c:
            with init_bbg_session('192.168.0.4', 8194) as session:
                for trade in l:
                    bbg_process(c, session, trade)
            conn.commit()
        timestamp = write_buffer(buf)
        upload_file(timestamp)
        q.delete('trades')