path: root/python/Dawn/models.py

from flask_wtf import FlaskForm
from flask_wtf.file import FileField
from sqlalchemy.dialects.postgresql import ENUM
from wtforms.validators import Length
from wtforms_alchemy import model_form_factory
from sqlalchemy_utils import EmailType, PhoneNumberType

from . import db

class Counterparties(db.Model):
    __tablename__ = 'counterparties'
    code = db.Column(db.String(12), primary_key=True)
    name = db.Column(db.String)
    city = db.Column(db.String)
    state = db.Column(db.String(2))
    dtc_number = db.Column(db.Integer)
    sales_contact = db.Column(db.String)
    sales_email = db.Column(EmailType)
    sales_phone = db.Column(PhoneNumberType())
    valuation_contact1 = db.Column(db.String)
    valuation_email1 = db.Column(EmailType)
    valuation_contact2 = db.Column(db.String)
    valuation_email2 = db.Column(EmailType)
    valuation_contact3 = db.Column(db.String)
    valuation_email3 = db.Column(EmailType)
    valuation_contact4 = db.Column(db.String)
    valuation_email4 = db.Column(EmailType)
    notes = db.Column(db.String)
    instructions = db.Column(db.String, info={'form_field_class': FileField})

class Accounts(db.Model):
    __tablename__ = 'accounts'
    code = db.Column(db.String(5), primary_key=True)
    name = db.Column(db.String)
    custodian = db.Column(db.String)
    cash_account = db.Column(db.String)
    counterparty = db.Column(db.String(12), db.ForeignKey('counterparties.code'))

PORTFOLIO = ENUM('OPTIONS', 'IR', 'MORTGAGES', name='portfolio')

BOND_STRAT = ENUM('M_STR_MAV', 'M_STR_MEZZ', 'CSO_TRANCH',
                  'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU',
                  'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW', 'M_MTG_FP', 'M_MTG_LMG',
                  'M_MTG_SD', 'M_MTG_PR', 'M_MTG_CRT_SD', name='bond_strat')

CDS_STRAT = ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS',
                 'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR',
                 'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS',
                 'IGOPTDEL', 'HYOPTDEL', 'SER_ITRXCURVE', name='cds_strat')

SWAPTION_STRAT = ENUM('IGPAYER', 'IGREC', 'HYPAYER', 'HYREC', 'STEEP',
                      name='swaption_strat')

FUTURE_STRAT = ENUM('M_STR_MAV', 'M_MTG_IO', 'M_STR_MEZZ', 'M_MTG_RW', 'SER_ITRXCURVE',
                    'M_CSH_CASH', name='future_strat')

CASH_STRAT = ENUM('M_CSH_CASH', 'MBSCDSCSH', 'SER_IGCVECSH', 'SER_ITRXCVCSH', 'CSOCDSCSH',
                  'IGCDSCSH', 'HYCDSCSH', 'CLOCDSCSH', 'IGTCDSCSH', 'MACCDSCSH', 'M_STR_MEZZ',
                  'IRDEVCSH',
                  name='cash_strat')

SPOT_STRAT = ENUM('M_STR_MAV', 'M_STR_MEZZ', 'SER_ITRXCURVE', 'M_CSH_CASH',
                  name='spot_strat')

OPTION_TYPE = ENUM('PAYER', 'RECEIVER',
                   name='option_type')

SWAPTION_TYPE = ENUM('CD_INDEX_OPTION', 'SWAPTION', name='swaption_type')
SETTLEMENT_TYPE = ENUM('Delivery', 'Cash', name='settlement_type')

REPO_TYPE = ENUM('REPO', 'REVERSE REPO', name='repo_type')
FUTURE_TYPE = ENUM('FUTURE', 'CFD', 'SYNTHETIC-FUTURE', 'LME-FORWARD', name='future_type')

CALL_NOTICE = ENUM('24H', '48H', '3D', '4D', '5D', '6D',
                   '1W', '8D', '9D', '10D', '2W', '1M', '2M', name='call_notice')

ASSET_CLASS = ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared',
                   name='asset_class')
ACTION = ENUM('NEW', 'UPDATE', 'CANCEL', name='action')

CCY = ENUM('USD', 'CAD', 'EUR', 'YEN', name='currency')

BBG_TYPE = ENUM('Mtge', 'Corp', name='bbg_type')

SWAP_TYPE = ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS', name='swap_type')

ISDA = ENUM('ISDA2014', 'ISDA2003Cred', name='isda')

DAY_COUNT = ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365', name='day_count')
FREQ = ENUM('Yearly', 'Half-Yearly', 'Quarterly', 'Bi-Monthly', 'Monthly',
            'Weekly', 'Daily', 'Straight', name='frequency')
BUS_DAY_CONVENTION = ENUM('Modified Following', 'Following', 'Modified Preceding', 'Preceding',
                          'Second-Day-After', 'End-of-Month', name='bus_day_convention')
PROTECTION = ENUM('Buyer', 'Seller', name='protection')

CAPFLOOR_TYPE = ENUM('CAPFLOOR', 'CMS_OPTION', 'STRUCTURED', 'FORWARD_PREMIUM', name='capfloor_type')
CAPFLOOR = ENUM('C', 'F', name='cap_or_floor')
PAYMENT_MODE = ENUM('F', 'S', name='payment_mode')
PRICING_TYPE = ENUM('PlainVanilla', 'EquityIndex', name='pricing_type')
BEGIN_OR_END = ENUM('B', 'E', name='begin_or_end')

class BondDeal(db.Model):
    __tablename__ = 'bonds'
    id = db.Column('id', db.Integer, primary_key=True)
    dealid = db.Column(db.String(28))
    lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
    action = db.Column(ACTION)
    folder = db.Column(BOND_STRAT, nullable=False)
    custodian = db.Column(db.String(12), default='BAC', nullable=False)
    cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False)
    cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                        info={'choices': [(None, '')],
                              'label': 'counterparty'}, nullable=False)
    trade_date = db.Column(db.Date, nullable = False)
    settle_date = db.Column(db.Date, nullable = False)
    cusip = db.Column(db.String(9), info={'validators': Length(9, 9),
                                          'filters': [lambda x: x or None,],
                                          'trim': True})
    isin = db.Column(db.String(12), info={'validators': Length(12, 12),
                                          'filters': [lambda x: x or None,],
                                          'trim': True})
    identifier = db.Column(db.String(12), info={'filters': [lambda x: x or None,],
                                                'trim': True})
    description = db.Column(db.String(32), nullable = False, info={'trim': True})
    buysell = db.Column(db.Boolean, nullable=False, info={'choices': [(0, 'sell'), (1, 'buy')],
                                                          'coerce': lambda x: bool(int(x)) \
                                                          if x is not None else x})
    faceamount = db.Column(db.Float, nullable=False)
    price = db.Column(db.Float, nullable=False)
    accrued = db.Column(db.Float, nullable = False)
    asset_class = db.Column(ASSET_CLASS)
    ticket = db.Column(db.String, info={'form_field_class': FileField})
    principal_payment = db.Column(db.Float)
    accrued_payment = db.Column(db.Float)
    counterparty = db.relationship(Counterparties)
    __table_args__= (db.CheckConstraint('cusip is not Null or isin is not Null'),)

class CDSDeal(db.Model):
    __tablename__ = 'cds'
    id = db.Column('id', db.Integer, primary_key=True)
    dealid = db.Column(db.String(28))
    lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
    action = db.Column(ACTION)
    folder = db.Column(CDS_STRAT, nullable=False)
    account_code = db.Column(db.String(5), db.ForeignKey('accounts.code'),
                             info={'choices': [(None,'')],
                                   'label': 'fcm_account'}, nullable=False)
    custodian = db.Column(db.String(12), default='NONE', nullable=False)
    cashaccount = db.Column(db.String(10), default='V0NSCLMFCM', nullable=False)
    cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                        info={'choices': [(None, '')],
                              'label': 'counterparty'}, nullable=False)
    trade_date = db.Column(db.Date, nullable=False)
    effective_date = db.Column(db.Date, nullable=False)
    maturity = db.Column(db.Date, nullable=False)
    currency = db.Column(CCY, nullable=False)
    payment_rolldate = db.Column(BUS_DAY_CONVENTION, nullable=False)
    notional = db.Column(db.Float, nullable=False)
    fixed_rate = db.Column(db.Float, nullable=False)
    day_count = db.Column(DAY_COUNT, nullable=False)
    frequency = db.Column(db.SmallInteger, default=4, nullable=False)
    protection = db.Column(PROTECTION, nullable=False)
    security_id = db.Column(db.String(12), nullable=False)
    security_desc = db.Column(db.String(32), nullable=False)
    upfront = db.Column(db.Float, nullable=False)
    upfront_settle_date = db.Column(db.Date, nullable=False)
    initial_margin_percentage = db.Column(db.Float)
    swap_type = db.Column(SWAP_TYPE, nullable=False)
    attach = db.Column(db.SmallInteger, info={'min': 0, 'max': 100})
    detach = db.Column(db.SmallInteger, info={'min': 0, 'max': 100})
    corr_attach = db.Column(db.Float, info={'min': 0.})
    corr_detach = db.Column(db.Float, info={'min': 0.})
    index_ref = db.Column(db.Float)
    clearing_facility = db.Column(db.String(12), default=None,
                                  info={'filters': [lambda x: x or None,]})
    isda_definition = db.Column(ISDA)
    termination_date = db.Column(db.Date)
    termination_amount = db.Column(db.Float)
    termination_cp = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                               info={'choices': [(None, '')],
                                     'label': 'termination_counterparty'})
    counterparty = db.relationship(Counterparties, foreign_keys=[cp_code])
    termination_counterparty = db.relationship(Counterparties, foreign_keys=[termination_cp])
    fcm_account = db.relationship(Accounts, foreign_keys=[account_code])
    __table_args__ = (db.CheckConstraint("swap_type!='CD_INDEX_TRANCHE' or " \
                                         "(attach is not NULL and detach is not NULL AND " \
                                         "clearing_facility is NULL)"),)

class RepoDeal(db.Model):
    __tablename__ = 'repo'
    id = db.Column('id', db.Integer, primary_key=True)
    lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
    action = db.Column(ACTION)
    folder = db.Column(CDS_STRAT, nullable=False)
    custodian = db.Column(db.String(12), default='SGFCM', nullable=False)
    cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False)
    cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                        info={'choices': [(None, '')],
                              'label': 'counterparty'}, nullable=False)
    trade_date = db.Column(db.Date, nullable=False)
    settle_date = db.Column(db.Date, nullable=False)
    cusip = db.Column(db.String(9), info={'validators': Length(9,9),
                                          'filters': [lambda x: x or None,],
                                          'trim': True})
    isin = db.Column(db.String(12), info={'validators': Length(12, 12),
                                          'filters': [lambda x: x or None,],
                                          'trim': True})
    identifier = db.Column(db.String(12), info={'filters': [lambda x: x or None,],
                                                'trim': True})
    description = db.Column(db.String(32), nullable=False, info={'trim': True})
    transation_indicator = db.Column(REPO_TYPE)
    faceamount = db.Column(db.Float, nullable=False)
    price = db.Column(db.Float, nullable=False)
    currency = db.Column(CCY, nullable=False)
    expiration_date = db.Column(db.Date)
    weighted_amount = db.Column(db.Float)
    haircut = db.Column(db.Float)
    repo_rate = db.Column(db.Float, nullable=False)
    call_notice = db.Column(CALL_NOTICE)
    daycount = db.Column(DAY_COUNT)
    ticket = db.Column(db.String, info={'form_field_class': FileField})
    __table__args = (db.CheckConstraint("haircut is not NULL and weighted_amount is NULL) or " \
                                        "haircut is NULL and weighted_amount is NOT NULL)"),
                     db.CheckConstraint("cusip is NOT NULL or isin is NOT NULL"))


class SwaptionDeal(db.Model):
    __tablename__ = 'swaptions'
    id = db.Column('id', db.Integer, primary_key=True)
    dealid = db.Column(db.String(28))
    lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
    action = db.Column(ACTION)
    portfolio = db.Column(PORTFOLIO)
    folder = db.Column(SWAPTION_STRAT, nullable=False)
    custodian = db.Column(db.String(12), default='NONE', nullable=False)
    cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False)
    cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                        info={'choices': [(None, '')],
                              'label': 'counterparty'}, nullable=False)
    swap_type = db.Column(SWAPTION_TYPE, nullable=False)
    trade_date = db.Column(db.Date, nullable=False)
    settle_date = db.Column(db.Date, nullable=False)
    buysell = db.Column(db.Boolean, nullable=False, info={'choices': [(0, 'sell'), (1, 'buy')],
                                                          'coerce': lambda x: bool(int(x))
                                                          if x is not None else x})
    notional = db.Column(db.Float, nullable=False)
    option_type = db.Column(OPTION_TYPE, nullable=False)
    strike = db.Column(db.Float, nullable=False)
    price = db.Column(db.Float, nullable=False)
    expiration_date = db.Column(db.Date, nullable=False)
    initial_margin_percentage = db.Column(db.Float)
    index_ref = db.Column(db.Float)
    security_id = db.Column(db.String(12), nullable=False)
    security_desc = db.Column(db.String(32))
    fixed_rate = db.Column(db.Float)
    maturity = db.Column(db.Date, nullable=False)
    currency = db.Column(CCY, nullable=False)
    settlement_type = db.Column(SETTLEMENT_TYPE, nullable=False)
    termination_date = db.Column(db.Date)
    termination_amount = db.Column(db.Float)
    termination_cp = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                               info={'choices': [(None, '')],
                                     'label': 'termination_counterparty'})
    counterparty = db.relationship(Counterparties, foreign_keys=[cp_code])
    termination_counterparty = db.relationship(Counterparties, foreign_keys=[termination_cp])


class FutureDeal(db.Model):
    __tablename__ = 'futures'
    id = db.Column('id', db.Integer, primary_key=True)
    dealid = db.Column(db.String(28))
    lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
    action = db.Column(ACTION)
    folder = db.Column(FUTURE_STRAT, nullable=False)
    custodian = db.Column(db.String(12), default='INTBR', nullable=False)
    cashaccount = db.Column(db.String(10), default='IANSCLMAFU', nullable=False)
    cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                        info={'choices': [('IBKRNY', 'Interactive Brokers')],
                              'label': 'counterparty'}, nullable=False)
    trade_date = db.Column(db.Date, nullable=False)
    settle_date = db.Column(db.Date, nullable=False)
    buysell = db.Column(db.Boolean, nullable=False, info={'choices':[(0, 'sell'), (1, 'buy')],
                                                          'coerce': lambda x: bool(int(x)) \
                                                            if x is not None else x})
    bbg_ticker = db.Column(db.String(32), nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    price = db.Column(db.Float, nullable=False)
    commission = db.Column(db.Float)
    swap_type = db.Column(FUTURE_TYPE, nullable=False)
    security_desc = db.Column(db.String(32), nullable=False)
    maturity = db.Column(db.Date, nullable=False)
    currency = db.Column(CCY, nullable=False)
    exchange = db.Column(db.String(3), default='CME', nullable=False)
    counterparty = db.relationship(Counterparties)

class CashFlowDeal(db.Model):
    __tablename__ = 'wires'
    id = db.Column('id', db.Integer, primary_key=True)
    dealid = db.Column(db.String(28))
    lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
    action = db.Column(ACTION)
    folder = db.Column(CASH_STRAT, nullable=False)
    code = db.Column(db.String(5), db.ForeignKey('accounts.code'), nullable=False)
    amount = db.Column(db.Float, nullable=False)
    currency = db.Column(CCY, nullable=False)
    trade_date = db.Column(db.Date, nullable=False)
    account = db.relationship(Accounts)


class SpotDeal(db.Model):
    __tablename__ = "spots"
    id = db.Column('id', db.Integer, primary_key=True)
    dealid = db.Column(db.String(28))
    lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
    action = db.Column(ACTION)
    folder = db.Column(SPOT_STRAT, nullable=False)
    custodian = db.Column(db.String(12), default='INTBR', nullable=False)
    cashaccount = db.Column(db.String(10), default='IANSCLMAFU', nullable=False)
    cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                        info={'choices': [('IBKRNY', 'Interactive Brokers')],
                              'label': 'counterparty'}, nullable=False)
    trade_date = db.Column(db.Date, nullable=False)
    settlement_date = db.Column(db.Date, nullable=False)
    spot_rate = db.Column(db.Float, nullable=False)
    buy_currency = db.Column(CCY, nullable=False)
    buy_amount = db.Column(db.Float, nullable=False)
    sell_currency = db.Column(CCY, nullable=False)
    sell_amount = db.Column(db.Float, nullable=False)
    commission_currency = db.Column(CCY)
    commission = db.Column(db.Float)
    counterparty = db.relationship(Counterparties)

class CapFloorDeal(db.Model):
    __tablename__ = 'capfloors'
    id = db.Column('id', db.Integer, primary_key=True)
    dealid = db.Column(db.String(28))
    lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
    action = db.Column(ACTION)
    folder = db.Column(SWAPTION_STRAT, nullable=False)
    custodian = db.Column(db.String(12), default='NONE', nullable=False)
    cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False)
    cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                        info={'choices': [(None, '')],
                              'label': 'counterparty'}, nullable=False)
    comments = db.Column(db.String(100))
    floating_rate_index = db.Column(db.String(12), nullable=False,
                                    info={'label': 'Floating Rate Index'})
    floating_rate_index_desc = db.Column(db.String(32))
    buysell = db.Column(db.Boolean, nullable=False, info={'choices':[(0, 'sell'), (1, 'buy')],
                                                          'coerce': lambda x: bool(int(x)) \
                                                          if x is not None else x})
    cap_or_floor = db.Column(CAPFLOOR, nullable=False,
                             info={'choices': [('C', 'Cap'), ('F', 'Floor')],
                                   'label': 'Cap or Floor?'})
    strike = db.Column(db.Float, nullable=False)
    value_date = db.Column(db.Date, nullable=False)
    expiration_date = db.Column(db.Date, nullable=False)
    premium_percent = db.Column(db.Float, nullable=False)
    pricing_type = db.Column(PRICING_TYPE, nullable=False)
    payment_frequency = db.Column(FREQ, nullable=False, default='Straight')
    fixing_frequency = db.Column(FREQ, nullable=False, default='Straight')
    day_count_counvention = db.Column(DAY_COUNT, default='Act/Act')
    bdc_convention = db.Column(BUS_DAY_CONVENTION, default='Modified')
    payment_mode = db.Column(PAYMENT_MODE, nullable=False,
                             info={'choices': [('F', 'Flat'), ('S', 'Schedule')]})
    payment_at_beginning_or_end = db.Column(BEGIN_OR_END, nullable=False,
                                            info={'label': 'In arrears?',
                                                  'choices': [('E', True),
                                                              ('B', False)]})
    initial_margin_percentage = db.Column(db.Float)
    initial_margin_currency = db.Column(CCY)
    amount = db.Column(db.Float, nullable=False, info={'label': 'notional'})
    trade_date = db.Column(db.Date, nullable=False)
    swap_type = db.Column(CAPFLOOR_TYPE, nullable=False)
    reset_lag = db.Column(db.Integer, default=2)
    trade_confirm = db.Column(db.String, info={'form_field_class': FileField})
    termination_date = db.Column(db.Date)
    termination_amount = db.Column(db.Float)
    termination_cp = db.Column(db.String(12), db.ForeignKey('counterparties.code'),
                               info={'choices': [(None, '')],
                                     'label': 'termination counterparty'})
    cpty_id = db.Column(db.Text)
    counterparty = db.relationship(Counterparties, foreign_keys=[cp_code])
    termination_counterparty = db.relationship(Counterparties,
                                               foreign_keys=[termination_cp])

BaseModelForm = model_form_factory(FlaskForm)
class ModelForm(BaseModelForm):
    @classmethod
    def get_session(self):
        return db.session