import requests
import os
import os.path
import datetime
from common import root
import csv
from db import conn
import psycopg2

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

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

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

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

# filename = os.path.join(common.root, "data", "Facility files", "facility_latest.csv")
# with open(filename, "r") as fh:
#     reader = csv.reader(fh)
#     reader.next()
#     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))
#     for line in reader:
#         newline = [convertToNone(v) for v in line] + [date]
#         newline.pop(9)          # remove the spread to maturity value
#         common.cursor.execute(sqlstring, newline)
# common.conn.commit()

# common.cursor.execute("SELECT DISTINCT LoanXID from markit_facility")
# cusips = [c[0] for c in common.cursor.fetchall()]
# filename = os.path.join(common.root, "data", "Facility files", "facility_update_" + date + ".csv")
# with open( filename, "r") as fh:
#     reader = csv.reader(fh)
#     reader.next()
#     sqlstring = "INSERT INTO markit_facility VALUES( {0} )".format( ",".join(["%s"] * 13))
#     for line in reader:
#         newline = [convertToNone(v) for v in line]
#         if newline[0] in cusips:
#             common.cursor.execute("DELETE FROM markit_facility WHERE LoanXID = %s", (newline[0],))
#             common.cursor.execute(sqlstring, newline)
#         else:
#             common.cursor.execute(sqlstring, newline)

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