import csv
from lxml import etree
import requests, io, zipfile, shutil
import os
from db import with_connection

def request_payload(payload):
    r = requests.get('https://www.markit.com/export.jsp', params=payload)
    res = []
    with zipfile.ZipFile(io.BytesIO(r.content)) as z:
        for f in z.namelist():
            if f.endswith("xml"):
                z.extract(f, path=os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports"))
                res.append(f)
    return res

def download_report(report):
    version = 10 if 'Entity' in report else 9
    payload = {'user': 'GuillaumeHorel',
               'password': 'password',
               'version': version,
               'report': report}
    r = []
    if report in ['CredIndexAnnex', 'CredIndexAnnexSplit']:
        for family in ['CDX', 'ITRAXX-EUROPE']:
            payload.update({'family': family})
            r += request_payload(payload)
    else:
        r += request_payload(payload)
    return r

@with_connection('serenitasdb')
def update_redcodes(conn, fname):
    with open(os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports", fname)) as fh:
        et = etree.parse(fh)
    r = []
    for indextype in ['HY', 'IG', 'EU', 'XO']:
        if indextype in ['HY', 'IG']:
            subfamily = 'CDX.NA.{0}'.format(indextype)
        elif indextype in ['EU']:
            subfamily = 'iTraxx Europe'
        elif indextype in ['XO']:
            subfamily = 'iTraxx Europe Crossover'
        for index in et.xpath("./index/indexsubfamily[text()='{0}']/..".format(subfamily)):
            r.append([indextype] + [index.find(tag).text for tag in  \
                                    ['series', 'version', 'redindexcode', 'indexfactor']])
    sqlstr = """UPDATE index_version SET redindexcode=%s
    where index=%s and series=%s and version=%s and abs(indexfactor-%s)<0.001"""
    with conn.cursor() as c:
        c.executemany(sqlstr, [tuple([e[3], e[0], e[1], e[2], float(e[4])*100]) for e in r])
    conn.commit()

def update_redindices(fname):
    basedir = os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports")
    with open(os.path.join(basedir, fname)) as fh:
        e = etree.parse(fh)
        root = e.getroot()
    headers = ['referenceentity', 'redentitycode', 'role', 'redpaircode', 'jurisdiction',
               'tier','pairiscurrent', 'pairvalidto', 'pairvalidfrom', 'ticker',
               'ispreferred', 'isdatransactiontype', 'docclause','recorddate',
               'publiccomments','weight']
    for c in root.findall('index'):
        names = [c.find(tag).text for tag in ['indexsubfamily', 'series', 'version']]
        with open( os.path.join(basedir, "{0}.{1}.V{2}.csv".format(*names)), "w") as fh2:
            csvwriter = csv.DictWriter(fh2, fieldnames=headers)
            csvwriter.writeheader()
            data = []
            for constituent in c.findall('.//originalconstituent'):
                data.append({l.tag: l.text for l in constituent})
                data = sorted(data, key=lambda x: x['referenceentity'])
            csvwriter.writerows(data)

if __name__=="__main__":
    report_list = ['REDEntity', 'REDObligation', 'REDEntityDelta', 'REDObligationDelta',
                   'REDObligationPreferred', 'CredIndexAnnex', 'CredIndexAnnexSplit',
                   'REDIndexCodes']
    fname = download_report("REDIndexCodes")
    update_redcodes(fname[0])
    f1, f2 = download_report("CredIndexAnnex")
    update_redindices(f1)
    update_redindices(f2)