import pandas as pd
from analytics import Index, Swaption
import pdb
from db import dbconn
from joblib import Parallel, delayed
from pickle import loads, dumps
serenitasdb = dbconn('serenitasdb')

data = pd.read_sql("SELECT * from swaption_ref_quotes JOIN swaption_quotes " \
                   "USING (quotedate, index, series, expiry) WHERE index=%s and series=%s " \
                   "ORDER BY quotedate",
                   "postgresql://serenitas_user@debian/serenitasdb",
                   params = ('IG', 27), parse_dates = ['quotedate', 'expiry'])

ig27 = Index.from_name("ig", 27, "5yr")
sigma = {}
sql_str = "INSERT INTO swaption_calib VALUES({}) ON CONFLICT DO NOTHING".format(",".join(["%s"] * 9))


def calib(d, option, expiry):
    option.strike = d['strike']
    option.ref = d['ref']
    r = []
    for pv_type in ['pv', 'pv_black']:
        for option_type in ['pay', 'rec']:
            mid = (d['{}_bid'.format(option_type)] + d['{}_offer'.format(option_type)])/2 * 1e-4
            option.option_type = 'payer' if option_type == 'pay' else 'receiver'
            try:
                setattr(option, pv_type, mid)
            except ValueError:
                r.append(None)
                print(d['ref'], d['strike'], mid, option.intrinsic_value)
            else:
                r.append(option.sigma)
    return [d['quotedate'], "IG", 27, expiry, d['strike']] + r

for k, v in data.groupby([data['quotedate'].dt.date, 'expiry']):
    trade_date, expiry = k
    print(trade_date, expiry.date())
    ig27.trade_date = trade_date
    option = Swaption(ig27, expiry.date(), 70)
    r = Parallel(n_jobs=4)(delayed(calib)(d, option, expiry.date()) for d in
                           v[['ref', 'quotedate', 'strike', 'pay_bid', 'pay_offer',
                              'rec_bid', 'rec_offer']].
                           to_dict(orient = 'records'))
    with serenitasdb.cursor() as c:
        c.executemany(sql_str, r)
        serenitasdb.commit()