-- -*- mode: sql; sql-product: postgres; -*-
CREATE TYPE bonds_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', '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');

CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared');

CREATE TABLE counterparties(code text primary key,
       name text,
       location text,
       dtc_number integer,
       sales_contact text,
       email1 text,
       phone text,
       valuation_contact1 text,
       valuation_email1 text,
       valuation_contact2 text,
       valuation_email2 text,
       valuation_contact3 text,
       valuation_email3 text,
       notes text);

CREATE INDEX ON counterparties(name);

CREATE TABLE bonds(id serial primary key,
       trade_date date,
       settle_date date,
       cusip varchar(9),
       isin varchar(12),
       description text,
       notional float,
       price float,
       counterparty text references counterparties(code),
       strategy bonds_start,
       account text
       acc_int float,
       asset_class );