path: root/sql/serenitas.c

#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "lz4.h"
#include "utils/date.h"
#include "utils/builtins.h"
#include "utils/numeric.h"
#include "utils/rel.h"

#include "isda/bastypes.h"
#include "isda/dateconv.h"
#include "isda/busday.h"
#include "isda/ldate.h"
#include "isda/cdsone.h"
#include "isda/cerror.h"
#include <stdbool.h>

PG_MODULE_MAGIC;

static inline TDate next_business_day(TDate date, long method, const char* cal) {
    TDate r;
    if (JpmcdsBusinessDay(date, method, cal, &r) != SUCCESS) {
        return -1;
    } else {
        return r;
    }
}

TDate _previous_twentieth(TDate d, bool roll, const char* cal) {
    TMonthDayYear mdy;
    if (JpmcdsDateToMDY(d, &mdy) != SUCCESS) {
        return -1;
    }
    if (mdy.day < 20) {
        if(mdy.month == 1) {
            mdy.month = 12;
            mdy.year -= 1;
        } else {
            mdy.month -= 1;
        }
    }
    mdy.day = 20;
    int mod = mdy.month % 3;
    if (mod != 0) {
        mdy.month -= mod;
        if (mdy.month <= 0) {
            mdy.month += 12;
            mdy.year -= 1;
        }
    }
    TDate r;
    if (JpmcdsMDYToDate(&mdy, &r) != SUCCESS) {
        return -1;
    }
    if (roll) {
        return next_business_day(r, JPMCDS_BAD_DAY_FOLLOW, cal);
    } else {
        return r;
    }
}

// postgresql represents dates as number of days since 2000-01-01
// TDate are integers since 1601-01-01
static inline TDate TDate_from_DateADT(DateADT d) {
    return d + 145731;
}

static inline const char* cal_from_currency(const char* curr) {
    static const char default_cal[] = "NONE";
    static const char us_cal[] = "/usr/share/cds/NYM";
    static const char jp_cal[] = "/usr/share/cds/TYO";
    if (strcmp(curr, "USD") == 0) {
        return us_cal;
    } else if (strcmp(curr, "JPY") == 0) {
        return jp_cal;
    } else {
        return default_cal;
    }
}

static inline const int16 curvetype_from_currency(const char* curr) {
    if (strcmp(curr, "USD") == 0) {
        return 531;
    } else if (strcmp(curr, "EUR") == 0) {
        return 530;
    } else if (strcmp(curr, "JPY") == 0) {
        return 532;
    } else {
        elog(ERROR, "unknown currency");
    }
}


PG_FUNCTION_INFO_V1(cds_accrued);

Datum cds_accrued(PG_FUNCTION_ARGS) {
    DateADT d = PG_GETARG_DATEADT(0);
    float8 coupon = PG_GETARG_FLOAT8(1);
    bool include_cashflow = PG_GETARG_BOOL(2);
    char* currency = text_to_cstring(PG_GETARG_TEXT_PP(3));
    const char* cal = cal_from_currency(currency);
    TDate date = TDate_from_DateADT(d) + 1;
    TDate date1 = next_business_day(date, JPMCDS_BAD_DAY_PREVIOUS, cal);
    if (date1 == -1) {
        pfree(currency);
        elog(ERROR, "Please set up the US calendar in /usr/share/cds/NYM");
    }

    TDate date_prev = _previous_twentieth(date1, true, cal);

    if ((date_prev == date) && include_cashflow) {
        date_prev = _previous_twentieth(date - 1, true, cal);
    }
    pfree(currency);
    PG_RETURN_FLOAT8((date - date_prev) / 360. * coupon);
}

static inline void get_TCurve(const char* buf, uint32_t length, TCurve* curve) {
    if (LZ4_decompress_safe(buf, (char*)curve, length, 512) < 0) {
        elog(ERROR, "error during decompression");
    }
}

double calc(TDate today, TDate start_date, TDate end_date, double recovery, double fixed_rate, const char* calendar, const TCurve* yc, double val, bool calc_upfront) {
    TDate cash_settle_date;
    JpmcdsDateFromBusDaysOffset(today, 3, calendar, &cash_settle_date);
    TDate step_in_date = today + 1;
    TStubMethod stub_type = {0, 0}; //f/s
    TDateInterval ivl = {.prd = 3, .prd_typ='M', .flag=0}; // 3 months
    double result;
    int success;
    if (calc_upfront) {
        success = JpmcdsCdsoneUpfrontCharge(today,
                                            cash_settle_date,
                                            start_date, // benchmark_start_date
                                            step_in_date,
                                            start_date,
                                            end_date,
                                            fixed_rate,
                                            true, // pay accrued on default
                                            &ivl,
                                            &stub_type,
                                            JPMCDS_ACT_360,
                                            JPMCDS_BAD_DAY_FOLLOW,
                                            calendar,
                                            yc,
                                            val,
                                            recovery,
                                            false, // pay accrued at start
                                            &result);
    } else {
        success = JpmcdsCdsoneSpread(today,
                                     cash_settle_date,
                                     start_date, // benchmark_start_date
                                     step_in_date,
                                     start_date,
                                     end_date,
                                     fixed_rate,
                                     true, // pay accrued on default
                                     &ivl,
                                     &stub_type,
                                     JPMCDS_ACT_360,
                                     JPMCDS_BAD_DAY_FOLLOW,
                                     calendar,
                                     yc,
                                     val,
                                     recovery,
                                     false, // pay accrued at start
                                     &result);
    }
    if (success == 0)
        return result;
    else
        elog(ERROR, "something went wrong");
}


PG_FUNCTION_INFO_V1(upfront_from_level);

Datum upfront_from_level(PG_FUNCTION_ARGS) {
    if (SPI_connect() == SPI_ERROR_CONNECT) {
        elog(ERROR, "something wrong happened");
    }
    const text* redindexcode = PG_GETARG_TEXT_PP(0);
    DateADT maturity = PG_GETARG_DATEADT(1);
    float8 traded_level = PG_GETARG_FLOAT8(2);
    DateADT trade_date = PG_GETARG_DATEADT(3);
    char* currency = text_to_cstring(PG_GETARG_TEXT_PP(4));
    char* sql_query = "SELECT index::text, coupon, issue_date, indexfactor/100, cumulativeloss "
        "FROM index_desc "
        "WHERE redindexcode=$1 AND maturity=$2";
    uint64 proc;
    int nargs = 2;
    Oid argtypes[2] = {TEXTOID, DATEOID};
    char nulls[2] = "  ";
    Datum values[2];
    int ret;
    values[0] = PointerGetDatum(redindexcode);
    values[1] = DateADTGetDatum(maturity);

    ret = SPI_execute_with_args(sql_query, nargs, argtypes, values, nulls, true, 1);
    proc = SPI_processed;
    int coupon;
    char *index;
    TDate issue_date;
    double factor, cumulativeloss;
    if (ret == SPI_OK_SELECT && SPI_tuptable != NULL) {
        SPITupleTable *tuptable = SPI_tuptable;
        TupleDesc tupdesc = tuptable->tupdesc;
        bool isnull;
        HeapTuple tuple = tuptable->vals[0];
        index = text_to_cstring(DatumGetTextPP(SPI_getbinval(tuple, tupdesc, 1, &isnull)));
        coupon = DatumGetInt32(SPI_getbinval(tuple, tupdesc, 2, &isnull));
        issue_date = TDate_from_DateADT(DatumGetDateADT(SPI_getbinval(tuple, tupdesc, 3, &isnull)));
        factor = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 4, &isnull));
        cumulativeloss = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 5, &isnull));
        elog(INFO, "%s %d %d %f %f", index, coupon, issue_date, factor, cumulativeloss);
    } else {
        elog(ERROR, "something wrong happened");
    }
    SPI_freetuptable(SPI_tuptable);
    int16 curve_type = curvetype_from_currency(currency);
    sql_query = "SELECT curve FROM rate_curves WHERE effective_date=$1 AND curve_type=$2";
    argtypes[0] = DATEOID;
    argtypes[1] = INT2OID;
    values[0] = DateADTGetDatum(trade_date);
    values[1] = Int16GetDatum(curve_type);
    ret = SPI_execute_with_args(sql_query, nargs, argtypes, values, nulls, true, 1);
    proc = SPI_processed;
    bytea* buf;
    Datum tmp;
    TCurve* curve;
    if (ret == SPI_OK_SELECT && SPI_tuptable != NULL) {
        SPITupleTable *tuptable = SPI_tuptable;
        TupleDesc tupdesc = tuptable->tupdesc;
        bool isnull;
        HeapTuple tuple = tuptable->vals[0];
        tmp = SPI_getbinval(tuple, tupdesc, 1, &isnull);
        if (isnull) {
            elog(ERROR, "no curve for that date");
        } else {
            buf = DatumGetByteaPP(tmp);
            uint32 data_length = VARSIZE_ANY_EXHDR(buf);
            const char *raw_data = VARDATA_ANY(buf);
            curve = (TCurve*)palloc(512);
            get_TCurve(raw_data, data_length, curve);
        }
    } else {
        elog(ERROR, "no curve for that date");
    }
    pfree(currency);
    pfree(curve);
    SPI_finish();
    double recovery = 0.4;
    double upfront;
    upfront = calc(TDate_from_DateADT(trade_date), issue_date, TDate_from_DateADT(maturity), recovery, coupon / 10000.0, cal_from_currency(currency), curve, traded_level, true);
    PG_RETURN_FLOAT8(upfront);
}


PG_FUNCTION_INFO_V1(update_attach);

Datum
update_attach(PG_FUNCTION_ARGS)
{
    TriggerData *trigdata = (TriggerData *) fcinfo->context;
    Trigger    *trigger;        /* to get trigger name */
    char       *relname;        /* triggered relation name */
    Relation    rel;            /* triggered relation */
    HeapTuple   rettuple = NULL;
    TupleDesc   tupdesc;        /* tuple description */
    bool        isnull;
    if (!CALLED_AS_TRIGGER(fcinfo))
        /* internal error */
        elog(ERROR, "not fired by trigger manager");
    if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
        /* internal error */
        elog(ERROR, "must be fired for row");
    if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        /* internal error */
        elog(ERROR, "must be fired before event");

    if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
        rettuple = trigdata->tg_trigtuple;
    else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
        rettuple = trigdata->tg_newtuple;
    else
        /* internal error */
        elog(ERROR, "cannot process DELETE events");

    rel = trigdata->tg_relation;

    if (rettuple)
        relname = SPI_getrelname(rel);

    trigger = trigdata->tg_trigger;
    tupdesc = rel->rd_att;
    /* int attname = SPI_fnumer(tupdesc, "traded_level"); */
    /* Oid traded_level = DatumGetObjectId(SPI_getbinval(rettuple, tupdesc, SPI_fnumber(tupdesc, "traded_level"), &isnull)); */
    int16 orig_attach, orig_detach;
    bool attach_is_null, detach_is_null;
    orig_attach = DatumGetInt16(SPI_getbinval(rettuple, tupdesc, SPI_fnumber(tupdesc, "orig_attach"), &attach_is_null));
    orig_detach = DatumGetInt16(SPI_getbinval(rettuple, tupdesc, SPI_fnumber(tupdesc, "orig_detach"), &detach_is_null));

    if (attach_is_null && detach_is_null)
        return PointerGetDatum(rettuple);

    char* sql_query = "SELECT indexfactor, cumulativeloss "
        "FROM index_factors "
        "WHERE redindexcode=$1";
    int nargs = 1;
    Oid argtypes[1] = {TEXTOID};
    char nulls[1] = " ";
    Datum values[1];
    int ret;
    values[0] = SPI_getbinval(rettuple, tupdesc, SPI_fnumber(tupdesc, "security_id"), &isnull);
    if (isnull) {
        elog(ERROR, "no security id");
    }
    if (SPI_connect() == SPI_ERROR_CONNECT) {
        elog(ERROR, "something wrong happened");
    }
    ret = SPI_execute_with_args(sql_query, nargs, argtypes, values, nulls, true, 1);
    double factor, cumloss;
    if (SPI_processed != 1) {
        return PointerGetDatum(rettuple);
    }
    if (ret == SPI_OK_SELECT && SPI_tuptable != NULL) {
        SPITupleTable *tuptable = SPI_tuptable;
        TupleDesc tupdesc = tuptable->tupdesc;
        bool isnull;
        HeapTuple tuple = tuptable->vals[0];
        factor = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 1, &isnull));
        cumloss = DatumGetFloat8(SPI_getbinval(tuple, tupdesc, 2, &isnull));
        elog(INFO, "%f %f", factor, cumloss);
    } else {
        elog(ERROR, "SPI query didn't work");
    }
    SPI_finish();
    double detach, attach;
    detach = detach_is_null ? 1.0 : factor * Min(Max((orig_detach - cumloss) / factor, 0.0), 1.0);
    attach = attach_is_null ? 0.0 : factor * Min(Max((orig_attach - cumloss) / factor, 0.0), 1.0);

    int chnattrs = 2;
    int chattrs[2];
    Datum newvals[2];
    bool newnulls[2] = {false, false};
    chattrs[0] = SPI_fnumber(tupdesc, "attach");
    chattrs[1] = SPI_fnumber(tupdesc, "detach");
    newvals[0] = Float8GetDatum(attach);
    newvals[1] = Float8GetDatum(detach);

    rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc,
                                         chnattrs, chattrs,
                                         newvals, newnulls);
    return PointerGetDatum(rettuple);
}