import yaml, os, csv
from db import serenitasdb
import datetime

basedir = "/home/share/CorpCDOs"
def get_index_list(basedir):
    with open(os.path.join(basedir, "code", "etc", "runs.yml")) as fh:
        runs = yaml.load(fh)
        index_list = [[name[:2].upper(), int(name[2:]), tenor] for name, tenor
                      in zip(runs['name'], runs['tenor'])]
    return index_list

sqlstr = "INSERT INTO risk_numbers VALUES({0},{1})".format(",".join(["%s"] * 10),
                                                           ",".join(["%s::float[]"]*9))

def get_attach_from_name(index_type, series):
    if index_type.lower() == "ig":
        if series == 9:
            attach = [0, 3, 7, 10, 15, 30, 100]
        else:
            attach = [0, 3, 7, 15, 100]
    elif index_type.lower() == "hy":
        attach = [0, 15, 25, 35, 100]
    elif index_type.lower() == "xo":
        attach = [0, 10, 20, 35, 100]
    elif index_type.lower() == 'eu':
        if series >= 21:
            attach = [0, 3, 6, 12, 100]
        else:
            attach = [0, 3, 6, 9, 12, 22, 100]
    return attach

def convert(s):
    return None if s=="NA" else float(s)

def populate_risk(basedir, db):
    with db.cursor() as c:
        c.execute("DELETE FROM risk_numbers")
    db.commit()
    for index_type, series, tenor in get_index_list(basedir):
        attach = get_attach_from_name(index_type, series)
        with open(os.path.join(basedir, "Tranche_data", "Runs",
                               "{0}{1}.{2}.csv".format(index_type, series, tenor))) as fh:
            csvreader = csv.DictReader(fh)
            with db.cursor() as c:
                toinsert = []
                for line in csvreader:
                    line['date'] = datetime.datetime.strptime(line['date'], "%Y-%m-%d").date()
                    greeks = []
                    skew = [convert(line["{0} Corr".format(a)]) for a in attach[1:]]
                    for k in ["Dealer Delta", "Model Delta", "Forward Deltas", "Gamma", "Theta",
                              "Corr01", "Dur", "EL"]:
                        greeks.append([convert(line["{0}-{1} {2}".format(l, u, k)])
                                       for l, u in zip(attach[:-1], attach[1:])])
                    toinsert.append((line['date'], index_type, series, tenor, line['indexprice'],
                                     line['indexBasis'], line['indexEL'], line['indexduration'],
                                     line['indexTheta'], attach) + (skew,) + tuple(greeks))
                c.executemany(sqlstr, toinsert)
        db.commit()

if __name__=="__main__":
    basedir = "/home/share/CorpCDOs"
    populate_risk(basedir, serenitasdb)
    serenitasdb.close()