-- Yucky slow view, since it's sub-querying for each of the summation fields
-- AND has a subquery on the inner join to find the right symbols. Anyways it
-- should preview relative strength of treating equity transaction data as
-- immutable. Any application on top of this should get the full list of
-- records so it can construct helpful views of summations based on the history
-- of changes, and/or show price data over time
CREATE VIEW account_symbol_summary_view
AS
SELECT
    acc.fiduciary_account_id,
    acc.fiduciary_account_name,
    acc.fiduciary_account_description,
    acc.fiduciary_account_number,
    acc.fiduciary_account_created_timestamp,
    sym.equity_symbol_id,
    sym.equity_symbol_type,
    sym.equity_symbol_name,
    sym.equity_symbol_managing_company,
    (SELECT
        SUM(ev.equity_change_quantity * ev.equity_change_type_score)
       FROM equity_change_event ev
      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
        AND ev.equity_symbol_id = sym.equity_symbol_id
    ) AS account_symbol_quantity,
    (SELECT
        printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score) / SUM(ev.equity_change_quantity * ev.equity_change_type_score))
       FROM equity_change_event ev
      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
        AND ev.equity_symbol_id = sym.equity_symbol_id
    ) AS account_symbol_cost_basis_price,
    (SELECT
        printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score))
       FROM equity_change_event ev
      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
        AND ev.equity_symbol_id = sym.equity_symbol_id
    ) AS account_symbol_cost_basis_total_usd,
    (SELECT
        printf( '%.2f', SUM(ev.equity_change_quantity) * (
            SELECT
                iev.equity_change_est_price
              FROM equity_change_event iev
             WHERE iev.fiduciary_account_id = acc.fiduciary_account_id
               AND iev.equity_symbol_id = sym.equity_symbol_id
              ORDER BY iev.equity_change_timestamp DESC
              LIMIT 1
        ))
       FROM equity_change_event ev
      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
        AND ev.equity_symbol_id = sym.equity_symbol_id
    ) AS account_symbol_last_effective_price,
    (SELECT
        ev.equity_change_timestamp
       FROM equity_change_event ev
      WHERE ev.fiduciary_account_id = acc.fiduciary_account_id
        AND ev.equity_symbol_id = sym.equity_symbol_id
       ORDER BY ev.equity_change_timestamp DESC
       LIMIT 1
    ) AS account_symbol_last_update_timestamp
FROM fiduciary_account acc
INNER JOIN equity_symbol sym ON EXISTS(
    SELECT *
      FROM equity_change_event e
     WHERE e.fiduciary_account_id = acc.fiduciary_account_id
       AND e.equity_symbol_id = sym.equity_symbol_id
);