from sqlalchemy import Table, create_engine, MetaData
from sqlalchemy.exc import IntegrityError
import os
import csv
import datetime
import pdb

engine = create_engine('postgresql://mlpdb_user:Serenitas1@debian/mlpdb')
metadata = MetaData(bind = engine)
quotes = Table('tranche_quotes', metadata, autoload = True)
ins = quotes.insert()

root_dir = '/home/share/CorpCDOs'
quotefiles = [f for f in os.listdir(os.path.join(root_dir, 'Tranche_data', 'Quotes')) if '2' in f]

def convert(x):
    try:
        return float(x)
    except:
        return None

tenordict = {'3': '3yr', '5': '5yr', '7':'7yr', '10':'10yr'}
runningdict1 = {0: 500, 3:100, 7:100, 15: 25}
runningdict2 = {0: 500, 3:500, 7:500, 10:100, 15:100, 30:100}

for quotefile in quotefiles:
    with open(os.path.join(root_dir, 'Tranche_data', 'Quotes', quotefile)) as fh:
        reader = csv.DictReader(fh)
        data = []
        for csvdict in reader:
            try:
                timestamp =  datetime.datetime.strptime(csvdict['Date'], "%d %b %H:%M")
                timestamp = timestamp.replace(year=2014)
            except ValueError:
                pdb.set_trace()

            attach = int(csvdict['Attach'])
            series = int(csvdict['Series'])
            version = int(csvdict['Version'])

            if csvdict['Ticker'] == 'CDX-NAHY':
                if csvdict['Price Bid']=='':
                    continue

                if series==9:
                    version = 20
                elif series==10:
                    version = 19

                d = {'quotedate' : timestamp,
                    'indexrefprice': float(csvdict['Ref']),
                    'indexrefspread': 375 if series==9 else 500,
                    'tranchedelta': convert(csvdict['Delta']),
                    'quotesource' : csvdict['Source'],
                    'trancheupfront' : (float(csvdict['Price Bid'])+float(csvdict['Price Ask']))/2,
                    'trancherunning' : 0 if series in [9, 10] and attach==10 else 500,
                    'tenor' : tenordict[csvdict['Tenor']],
                    'index' : 'HY',
                    'series': series,
                    'version': version,
                    'attach': attach,
                    'detach': int(csvdict['Detach'])
                 }
            elif csvdict['Ticker'] == 'CDX-NAIG':
                if csvdict['Ref']=='': #no delta quote maybe
                    continue
                if series>=22:
                    continue
                try:
                    trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                except ValueError:
                    continue
                d = {'quotedate' : timestamp,
                     'indexrefspread': int(float(csvdict['Ref'])),
                     'tranchedelta': convert(csvdict['Delta']),
                     'quotesource' : csvdict['Source'],
                     'trancheupfront' : trancheupfront,
                     'trancherunning' : runningdict2[attach] if series==9 else runningdict1[attach],
                     'tenor' : tenordict[csvdict['Tenor']],
                     'index' : 'IG',
                     'series': series,
                     'version': version,
                     'attach': attach,
                     'detach': int(csvdict['Detach'])
                 }
            elif csvdict['Ticker'] == 'ITRAXX-Europe':
                if series==9:
                    if attach<=6:
                        trancherunning = 500 if attach<=3 else 300
                        trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                    else:
                        trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
                        trancheupfront=0
                if series==19:
                    if attach<=3:
                        trancherunning = 500
                        trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                    else:
                        trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
                        trancheupfront = 0
                if series==21:
                    if attach<=3:
                        trancherunning =100
                        trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                    else:
                        trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
                        trancheupfront = 0

                d = {'quotedate' : timestamp,
                     'indexrefspread': int(float(csvdict['Ref'])),
                     'tranchedelta': convert(csvdict['Delta']),
                     'quotesource' : csvdict['Source'],
                     'trancheupfront' : trancheupfront,
                     'trancherunning' : trancherunning,
                     'tenor' : tenordict[csvdict['Tenor']],
                     'index' : 'EU',
                     'series': series,
                     'version': version,
                     'attach': attach,
                     'detach': int(csvdict['Detach'])
                 }
            data.append(d)

    with engine.begin() as conn:
        for l in data:
            with conn.begin() as t:
                try:
                    conn.execute(ins, l)
                except IntegrityError:
                    t.rollback()