1 files changed, 26 insertions, 6 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 7ad1f92b..b459c82d 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -95,14 +95,13 @@ CREATE TABLE marks(date date,
        price float,
        PRIMARY KEY(identifier, date));
 
-CREATE TABLE payment_history(
-       identifier varchar(12),
+CREATE TABLE cashflow_history(
+       identifier varchar(12) REFERENCES securities,
        date date,
-       principal_paydown float,
+       principal_bal float,
+       principal float,
        interest float,
-       principal_writedown float,
-       recovery_percentage float,
-       cumloss_percentage float,
+       coupon float,
        PRIMARY KEY (identifier, date));
 
 CREATE OR REPLACE function list_marks(p_date date, include_unsettled boolean DEFAULT False)
@@ -145,3 +144,24 @@ BEGIN
   RETURN QUERY EXECUTE sqlquery USING p_date, p_class;
 END;
 $$ LANGUAGE plpgsql;
+
+
+CREATE MATERIALIZED VIEW factors_history AS
+WITH temp AS
+(SELECT c.date, c.identifier, first_value(c.principal_bal) OVER w AS orig_bal,
+                     c.principal, c.principal_bal, c.interest, c.coupon,
+                     -c.principal-c.principal_bal+lag(c.principal_bal) OVER w AS losses
+              FROM cashflow_history c
+              WINDOW w AS (PARTITION BY c.identifier order by c.date asc))
+SELECT date, identifier, principal_bal/orig_bal AS factor, principal/orig_bal AS principal,
+interest/orig_bal AS interest, losses/orig_bal AS losses, coupon FROM temp;
+CREATE UNIQUE INDEX factors_history_pkey ON factors_history(date, identifier);
+
+CREATE OR REPLACE function factors_history(p_date date)
+RETURNS SETOF factors_history AS $$
+BEGIN
+RETURN QUERY
+SELECT DISTINCT ON (identifier) * FROM factors_history
+       WHERE date<=p_date ORDER BY identifier, date desc;
+END;
+$$ LANGUAGE plpgsql;