login(`admin, `123456)
loadPlugin("ODBC")

clearCachedModules()
use fundit::fundCalculator
use fundit::dataPuller

end_date = 2023.07.28
// portfolio_ids = "166002,364640,362736"
portfolio_id = "364640"

// size of rolling window
win = 24
// step of moving
step = 24

// get holdings
tb_holdings = get_portfolio_holding_history(portfolio_id)

// calculate current share of each holding
tb_current_holdings = SELECT portfolio_id, end_date AS holding_date, fund_id, fund_share.sum() AS fund_share
                      FROM tb_holdings
                      GROUP BY portfolio_id, fund_id
                      HAVING fund_share.sum() > 0

fund_ids = tb_current_holdings.fund_id.concat("','")$STRING
fund_ids = "'" + fund_ids + "'"

tb_latest_nav = SELECT fund_id, price_date, cumulative_nav FROM get_fund_latest_nav_performance(fund_ids, true)

// calculate portfolio total market value
UPDATE tb_current_holdings a
  SET a.market_value = round(a.fund_share * nav.cumulative_nav, 6), nav = nav.cumulative_nav
FROM ej(tb_current_holdings a, tb_latest_nav nav, "fund_id")

// calculate weighting of each holding
tb_current_holdings = SELECT *, market_value.sum() AS total_market_value, round(market_value \ market_value.sum(), 6) AS weighting
                      FROM tb_current_holdings
                      CONTEXT BY portfolio_id

// get weekly return of fund holdings
//fund_ids = fund_ids + ",'MF00003PW1','MF00003PW2','IN00000008'"


// fund_ids = fund_ids + "," + fund_pool_188.left(150*13-1)
tb_fund_weekly_ret = SELECT ret
                     FROM get_fund_weekly_rets(fund_ids, 1990.01.01, end_date, true).rename!(["year_week", "ret_1w"], ["date", "ret"])
                     PIVOT BY date, fund_id

// get portfolio weekly returns
tb_portfolio_weekly_ret = get_portfolio_weekly_rets(portfolio_id, 1990.01.01, end_date, true).rename!(["year_week", "ret_1w"], ["date", "ret"])

// calculate rolling RBSA weightings
tb = cal_rolling_rbsa(tb_portfolio_weekly_ret, tb_fund_weekly_ret, false, win, step)

// transform the data structure to mySQL friendly
tb_rbsa = table(tb.size()*(tb_fund_weekly_ret.cols()-1):0, "portfolio_id" "asset_type_id" "index_id"  "effective_date" "weight", [INT, STRING, STRING, STRING, FLOAT])

for( r in tb)
{
    if(r.status <> "solved") continue

    w = r.weights.split(" ")$DOUBLE

    for(i in 1..(tb_fund_weekly_ret.cols()-1))
    {
        tb_rbsa.tableInsert(portfolio_id$INT, "TestHolding", tb_fund_weekly_ret.colNames()[i], r.date, w[i-1])
    }
}

SELECT * FROM tb_rbsa WHERE index_id in ('MF00003Q1A', 'MF00003T43') and effective_date = '202330' order by weight desc


SELECT * FROM tb_current_holdings order by weighting desc
select * from tb_portfolio_weekly_ret order by date desc

t = (SELECT * FROM tb_fund_weekly_ret WHERE date > '202001').dropColumns!("date")
m = t.matrix().corrMatrix()
m = rename!(m, t.colNames(), t.colNames())
m