from db import dbconn, dbengine
from analytics import Portfolio, DualCorrTranche
import datetime
import pandas as pd

sql_string = ("SELECT id, sum(notional * case when protection='Buyer' then -1 else 1 end) "
              "OVER (partition by security_id, attach) AS ntl_agg "
              "FROM cds WHERE swap_type='CD_INDEX_TRANCHE' AND termination_cp IS NULL")
conn = dbconn('dawndb')
with conn.cursor() as c:
    c.execute(sql_string)
    trade_ids = [dealid for dealid, ntl in c if ntl != 0]
portf = Portfolio([DualCorrTranche.from_tradeid(dealid) for dealid in trade_ids],
                  trade_ids)
portf.value_date = datetime.date(2018, 10, 10)
portf.mark()