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

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

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}

def insert_quotes(year=2014):
    root_dir = '/home/share/CorpCDOs'
    quote_dir = os.path.join(root_dir, 'Tranche_data', 'Quotes')
    quotefiles = [f for f in os.listdir(quote_dir) if 'csv' in f]
    for quotefile in quotefiles:
        with open(os.path.join(quote_dir, 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=int(year))
                except ValueError:
                    logging.error("%s: Date in the wrong format", csvdict['Date'])
                    return

                attach = int(csvdict['Attach'])
                series = int(csvdict['Series'])
                version = int(csvdict['Version'])
                if csvdict['Ref']=='': #no delta quote maybe
                    continue

                #common values to all indices
                try:
                    d = {'quotedate' : timestamp,
                         'tranchedelta': convert(csvdict['Delta']),
                         'quotesource' : csvdict['Source'],
                         'series': series,
                         'version': version,
                         'attach': attach,
                         'detach': int(csvdict['Detach']),
                         'tenor': tenordict[csvdict['Tenor']]
                     }
                except KeyError:
                    continue

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

                    try:
                        trancheupfront = (float(csvdict['Price Bid'])+float(csvdict['Price Ask']))/2
                    except ValueError:
                        trancheupfront = None
                    d.update({'indexrefprice': float(csvdict['Ref']),
                              'indexrefspread': 375 if series==9 else 500,
                              'trancheupfront' : trancheupfront,
                              'trancherunning' : 0 if series in [9, 10] and attach==10 else 500,
                              'index': 'HY'
                              })

                elif csvdict['Ticker'] == 'CDX-NAIG':

                    if series>=22:
                        continue
                    try:
                        trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                    except ValueError:
                        trancheupfront = None
                    try:
                        d.update({'indexrefspread': int(float(csvdict['Ref'])),
                                  'trancheupfront' : trancheupfront,
                                  'trancherunning' : runningdict2[attach] if series<19 else runningdict1[attach],
                                  'index' : 'IG'})
                    except KeyError:
                        continue
                elif csvdict['Ticker'] == 'ITRAXX-Europe':
                    if series<=15:
                        if attach<=6:
                            trancherunning = 500 if attach<=3 else 300
                            try:
                                trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                            except ValueError:
                                trancheupfront = None
                        else:
                            try:
                                trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
                            except ValueError:
                                trancherunning = None
                            trancheupfront=0
                    if series==19:
                        if attach<=3:
                            trancherunning = 500
                            try:
                                trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                            except ValueError:
                                trancheupfront=None
                        else:
                            try:
                                trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
                            except ValueError:
                                trancherunning=(float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                            trancheupfront = 0
                    if series==21:
                        if attach<=3:
                            trancherunning =100
                            try:
                                trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
                            except ValueError:
                                trancheupfront = None
                        else:
                            trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
                            trancheupfront = 0

                    d.update({'indexrefspread': int(float(csvdict['Ref'])),
                              'trancheupfront' : trancheupfront,
                              'trancherunning' : trancherunning,
                              'index' : 'EU',
                          })
                data.append(d)
        with engine.connect() as conn:
            for i, l in enumerate(data):
                with conn.begin() as t:
                    try:
                        conn.execute(ins, l)
                    except IntegrityError as e:
                        print(e.orig)
                        t.rollback()

        os.unlink(os.path.join(quote_dir, quotefile))

if __name__=="__main__":
    insert_quotes()