import datetime
from sqlalchemy import MetaData, create_engine, Table

engine = create_engine('postgresql://mlpdb_user:Serenitas1@debian/mlpdb')
metadata = MetaData(bind = engine)
index_desc = Table('index_desc', metadata, autoload=True)
index_ins = index_desc.insert()

offset=182970
# index = [[offset, 'EU', 19, datetime.date(2016, 06, 20), 'Y3', 100, 0],
#          [offset, 'EU', 19, datetime.date(2018, 06, 20), 'Y5', 100, 0],
#          [offset, 'EU', 19, datetime.date(2020, 06, 20), 'Y7', 100, 0],
#          [offset, 'EU', 19, datetime.date(2023, 06, 20), 'Y10', 100, 0]]

# index = [[offset, 'HY', 21, datetime.date(2016, 12, 20), 'Y3', 100, 0],
#          [offset, 'HY', 21, datetime.date(2018, 12, 20), 'Y5', 100, 0],
#          [offset, 'HY', 21, datetime.date(2020, 12, 20), 'Y7', 100, 0],
#          [offset+10, 'EU', 20, datetime.date(2016, 12, 20), 'Y3', 100, 0],
#          [offset+10, 'EU', 20, datetime.date(2018, 12, 20), 'Y5', 100, 0],
#          [offset+10, 'EU', 20, datetime.date(2020, 12, 20), 'Y7', 100, 0],
#          [offset+10, 'EU', 20, datetime.date(2023, 12, 20), 'Y10', 100, 0],
#          [offset+20, 'IG', 21, datetime.date(2016, 12, 20), 'Y3', 100, 0],
#          [offset+20, 'IG', 21, datetime.date(2018, 12, 20), 'Y5', 100, 0],
#          [offset+20, 'IG', 21, datetime.date(2020, 12, 20), 'Y7', 100, 0],
#          [offset+20, 'IG', 21, datetime.date(2023, 12, 20), 'Y10', 100, 0]
#          ];

for line in index:
    with engine.begin() as conn:
        conn.execute(index_ins, basketid = line[0],
                     index = line[1],
                     series = line[2],
                     maturity = line[3],
                     tenor = line[4],
                     indexfactor = line[5],
                     cumulativeloss = line[6])

tranche_desc = Table('tranche_desc', metadata, autoload = True)
tranche_ins = tranche_desc.insert()

igbasketid = 182990
igattach = [0, 3, 7, 15, 100]
igtenors = ['Y3', 'Y5', 'Y7', 'Y10']
for tenor in igtenors:
    for i, (attach, detach) in enumerate(zip(igattach[:-1], igattach[1:])):
        with engine.begin() as conn:
            conn.execute(tranche_ins,
                         trancheid = igbasketid+i,
                         basketid = igbasketid,
                         tenor = tenor,
                         attach=attach,
                         detach = detach)

hybasketid = 182970
hyattach = [0, 15, 25, 35, 100]
hytenors = ['Y3', 'Y5', 'Y7']
for tenor in hytenors:
    for i, (attach, detach) in enumerate(zip(hyattach[:-1], hyattach[1:])):
        with engine.begin() as conn:
            conn.execute(tranche_ins,
                         trancheid = hybasketid+i,
                         basketid = hybasketid,
                         tenor = tenor,
                         attach = attach,
                         detach = detach)

# euattach = [0, 3, 6, 9, 12, 22, 100]
# eutenors = ['Y3', 'Y5', 'Y7']