import csv
from lxml import etree
import requests, io, zipfile, shutil
import os
from db import with_connection, dbengine
import pandas as pd

def request_payload(payload):
    r = requests.get('https://www.markit.com/export.jsp', params=payload)
    res = []
    path = os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports")
    if 'Delta' in payload['report']:
        path = os.path.join(path, "Deltas")

    with zipfile.ZipFile(io.BytesIO(r.content)) as z:
        for f in z.namelist():
            if f.endswith("xml"):
                z.extract(f, path=path)
                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}

    if report in ['CredIndexAnnex', 'CredIndexAnnexSplit']:
        for family in ['CDX', 'ITRAXX-EUROPE']:
            payload.update({'family': family})
            return request_payload(payload)
    else:
        return request_payload(payload)

@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)
    data_version = []
    data_maturity = []
    for index in et.iter('index'):
        temp = {c.tag: c.text for c in index if c.tag not in ['terms', 'paymentfrequency']}
        data_version.append(temp)
        for term in index.iter('term'):
            d = {'redindexcode': temp['redindexcode']}
            d.update({c.tag: c.text for c in term})
            data_maturity.append(d)

    df_maturity = pd.DataFrame(data_maturity)
    df_version = pd.DataFrame(data_version)
    df_version['activeversion'] = df_version['activeversion'].map({'Y': True, None: False})
    df_maturity.tenor = df_maturity['tenor'].map(lambda s: s.lower() + 'r')
    df_maturity.coupon = (pd.to_numeric(df_maturity.coupon) * 10000).astype(int)
    serenitas_engine = dbengine('serenitasdb')
    df_version.to_sql("index_version_markit", serenitas_engine, index=False, if_exists='append')
    df_maturity.to_sql("index_maturity_markit", serenitas_engine, index=False, if_exists='append')

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__":
    fname = download_report("REDIndexCodes")
    update_redcodes(fname[0])
    # f1, f2 = download_report("CredIndexAnnex")
    # update_redindices(f1)
    # update_redindices(f2)
    # report_list = ['REDEntity', 'REDObligation', 'REDObligationPreferred']
    # for report in report_list:
    #     download_report(report)
    # report_list = ['REDEntityDelta', 'REDObligationDelta']
    # for report in report_list:
    #     fname = download_report(report)