import psycopg2
import os
import os.path
import datetime
from datetime import date
import pdb
import csv

if os.name =='nt':
    root = "//WDsentinel/share/CorpCDOs/data/markit"
elif os.name == 'posix':
    root = '/home/share/CorpCDOS/data/markit'

def convertToNone(s):
    return None if (s=='' or s=='NA') else s

conn = psycopg2.connect(database="ET",
                        user="et_user",
                        password="Serenitas1",
                        host="192.168.1.108")
cursor = conn.cursor()

fields = ["LoanX ID", "Issuer", "Deal Name", "Facility", "Industry", \
          "Current SP Rating", "Current Moodys Rating", "Initial Amount",\
          "Final Maturity", "Initial Spread", "Bid", "Offer", "Depth", \
          "Spread To Maturity"]

latestdate = cursor.execute("SELECT MAX(pricingdate) from markit_prices")
latestdate = cursor.fetchone()[0]

sql_fields = ["LoanXID", "Issuer", "DealName", "Facility", "Industry", "SP",
              "Moodys", "Amount", "Maturity", "Spread", "Bid", "Offer", "Depth",
              "STM", "PricingDate"]

for filename in os.listdir(root):
    date = datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
    # only update the new data
    if latestdate is None or date > latestdate:
        with open( os.path.join(root, filename) ) as fh:
            dr = csv.DictReader(fh)
            data = []
            for line in dr:
                try:
                    line['Final Maturity'] = \
                        datetime.datetime.strptime(line['Final Maturity'], '%d-%b-%y')
                except ValueError:
                    if line['Final Maturity'] !='':
                        print 'Maturity non empty'
                if line['Depth'] == 'implied':
                    line['Depth'] = 0
                try:
                    temp = [convertToNone(line[key]) for key in fields]
                except KeyError:
                    pdb.set_trace()
                data.append(temp)

        for row in data:
            sqlstring = "INSERT INTO markit_prices({0}) " \
                "VALUES({1})".format(",".join(sql_fields), ",".join(["%s"] * len(sql_fields)))
            cursor.execute(sqlstring, tuple(row) + (date,))
        conn.commit()