import requests
from common import root
from db import conn
import os
import datetime
import csv

legal = 'serecap'
username = 'serecapuser'
password = 'Welcome1'

workdate = str(datetime.date.today())

def convertToNone(v):
    return v if v else None

r = requests.get('https://loans.markit.com/loanx/LoanXMarks.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}&EOD=Y'.format(legal, username, password))
marks_filename = os.path.join(root, "data", "markit", "markit_data_{0}.csv".format(workdate))
with open(marks_filename, "wb") as fh:
    fh.write(r.content)

r = requests.get('https://loans.markit.com/loanx/LoanXFacilityUpdates.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}'.format(legal, username, password))

facility_filename = os.path.join(root, "data", "Facility files", "facility_{0}.csv".format(workdate))
with open( facility_filename, "wb") as fh:
    fh.write(r.content)

sqlstring = "INSERT INTO markit_prices2 VALUES( {0} )".format( ",".join([ "%s" ] * 5))
with open(marks_filename, "r") as fh:
    reader = csv.reader(fh)
    next(reader)               # we skip the headers
    c = conn.cursor()
    for line in reader:
        if line[4] == "implied":
            line[4] = 0
        c.execute(sqlstring, (line[0], line[2], line[3], line[4], line[1]))
conn.commit()
c.close()

sqlstring = "INSERT INTO markit_facility VALUES( {0} )".format( ",".join(["%s"] * 13))
with open( facility_filename, "r") as fh:
    reader = csv.reader(fh)
    reader.next() # we skip the headers
    c = conn.cursor()
    for line in reader:
        newline = [convertToNone(v) for v in line]
        c.execute(sqlstring, newline)
conn.commit()
c.close()

#we update the missing facility loanxids
c = conn.cursor()
sqlstring = "SELECT loanxid FROM markit_prices2 EXCEPT SELECT loanxid FROM markit_facility";
c.execute(sqlstring)

facility_diff_filename = os.path.join(root, "data", "Facility files",
                                      "facility_diff_{0}.csv".format(workdate))
with open( facility_diff_filename, "wb") as fh:
    flag = False
    for loanxid in c.fetchall():
        r = requests.get('https://loans.markit.com/loanx/LoanXOneFacility.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}&LOANXID={3}'.format(legal, username, password, loanxid[0]))
        if flag:
            fh.write(r.content.split('\n')[1] + "\n")
        else:
            fh.write(r.content.split('\n')[0] + "\n")
            fh.write(r.content.split('\n')[1] + "\n")
            flag = True
c.close()

sqlstring = "INSERT INTO markit_facility(LoanXID, PMDID, IssuerName, dealname, facility_type," \
    "loanx_facility_type, initial_amount, initial_spread, maturity, industry, modified_time)" \
    "VALUES( {0} )".format( ",".join( ["%s"] * 11))
c = conn.cursor()
if os.path.getsize(facility_diff_filename):
    with open(facility_diff_filename, "r") as fh:
        reader = csv.reader(fh)
        next(reader)
        for line in reader:
            newline = [convertToNone(v) for v in line] + [workdate]
            newline.pop(9)          # remove the spread to maturity value
            c.execute(sqlstring, newline)
    conn.commit()
c.close()

conn.close()