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

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

/* init values for test cases  */
end_day = 2024.06.28;
fund_ids = "'HF000004KN','HF000103EU','HF00018WXG'";
entity_type = 'HF';
isFromNav = true;

/* codes from cal_fund_indicators */
    very_old_date = 1990.01.01;

    fund_info = get_fund_info(fund_ids);
    fund_info.rename!('fund_id', 'entity_id');

    if(isFromNav == true) {
        // 从净值开始计算收益
        tb_ret = SELECT * FROM cal_fund_monthly_returns(entity_type, fund_ids, true) WHERE price_date <= end_day;
        tb_ret.rename!(['fund_id', 'cumulative_nav'], ['entity_id', 'nav']);
    } else {
        // 从fund_performance表里读月收益
        tb_ret = get_monthly_ret('FD', fund_ids, very_old_date, end_day, true);
        tb_ret.rename!(['fund_id'], ['entity_id']);
    }

    // 取基金和基准的对照表
    primary_benchmark = SELECT entity_id, iif(benchmark_id.isNull(), 'IN00000008', benchmark_id) AS benchmark_id FROM fund_info;

    // 取所有出现的基准月收益
    bmk_ret = get_benchmark_return(primary_benchmark, end_day);

    risk_free_rate = SELECT fund_id, temporalParse(end_date, 'yyyy-MM') AS end_date, ret FROM get_risk_free_rate(very_old_date, end_day);


/* Tests for standard indicators */

rtn = cal_basic_performance(tb_ret, 'm');

lpm = cal_LPM(tb_ret, risk_free_rate);

lpms = cal_omega_sortino_kappa(tb_ret, risk_free_rate);

alpha_beta = cal_alpha_beta(tb_ret, primary_benchmark, bmk_ret, risk_free_rate);

bmk_tracking = cal_benchmark_tracking(tb_ret, primary_benchmark, bmk_ret);

capture_r = cal_capture_ratio(tb_ret, primary_benchmark, bmk_ret)

sharpe = cal_sharpe(tb_ret, rtn, risk_free_rate);

treynor = cal_treynor(tb_ret, risk_free_rate, alpha_beta);

jensen = cal_jensen(tb_ret, bmk_ret, risk_free_rate, alpha_beta);

m2 = cal_m2(tb_ret, primary_benchmark, bmk_ret, risk_free_rate);

ms = cal_ms_return(tb_ret, risk_free_rate);

/* codes from cal_fund_bfi_indicators  */
    start_month = 1990.01M;

    // 取基金和基准的对照表
    bfi_benchmark = SELECT fund_id AS entity_id, end_date.temporalParse('yyyy-MM') AS end_date, factor_id AS benchmark_id 
                           FROM get_fund_bfi_factors(fund_ids, start_month.temporalFormat('yyyy-MM'), end_day.temporalFormat('yyyy-MM'));

    bfi_bmk_ret = get_benchmark_return(bfi_benchmark, end_day);

/* Tests for BFI indicators */

t_bfi_bmk = SELECT * FROM bfi_benchmark WHERE entity_id = 'HF000004KN' and end_date = 2024.06M

     t0 = SELECT t.entity_id, t.end_date, t.price_date,
                 t.ret, bmk.ret AS ret_bmk, cumcount(t.entity_id) AS cnt, (t.ret - bmk.ret) AS exc_ret, bm.benchmark_id
          FROM tb_ret t
          INNER JOIN t_bfi_bmk bm ON t.entity_id = bm.entity_id 
          INNER JOIN bfi_bmk_ret bmk ON t.end_date = bmk.end_date AND bm.benchmark_id = bmk.benchmark_id
          WHERE t.ret > -1
            AND bmk.ret > -1
          CONTEXT BY t.entity_id, bm.benchmark_id;

     t = SELECT entity_id, end_date.cummax() AS end_date, price_date.cummax() AS price_date, price_date.cummin() AS min_date, benchmark_id,
                cumcount(iif(exc_ret >= 0, 1, null)) \ cnt AS winrate,
                exc_ret.cumstd() AS track_error, 
                iif(exc_ret.cumstd() == 0, null, exc_ret.cumavg() / exc_ret.cumstd()) AS info
         FROM t0 CONTEXT BY entity_id, benchmark_id
         ORDER BY entity_id, end_date, benchmark_id;
select * from bfi_benchmark where benchmark_id = 'FA00000VMP' order by end_date desc

          
bfi_bmk_tracking = cal_benchmark_tracking(tb_ret, bfi_benchmark, bfi_bmk_ret);

bfi_alpha_beta = cal_alpha_beta(tb_ret, bfi_benchmark, bfi_bmk_ret, risk_free_rate);

bfi_indicators = cal_indicators_with_benchmark(tb_ret, bfi_benchmark, bfi_bmk_ret, risk_free_rate);

SELECT * FROM bfi_indicators ORDER BY entity_id, end_date desc, benchmark_id