DolphinDB/modules/task_weeklyPerformance.dos

module fundit::task_weeklyPerformance

use fundit::sqlUtilities;
use fundit::operationDataPuller;
use fundit::performanceDataPuller;
use fundit::indicatorCalculator;
use fundit::rbsaCalculator;
use fundit::navCalculator;
use fundit::bfiMatcher;
use fundit::dataSaver;



/*
 *   根据收益更新日期计算 RBSA (1h for funds; for portfolios)
 *   
 *   @param entityType <STRING>: MF, HF, PF   (MF和HF等效)
 *   
 *   TODO: 是否要 monthly 运行，并写入 history 表？
 *         portfolio 未测试
 * 
 *   Example: CalEntityRBSATask('MF', ['MF00003PW1'], 2024.10.14T10:00:00);
 *            CalEntityRBSATask('MF', NULL, 2025.02.20);
 *            CalEntityRBSATask('PF', NULL, 2025.01.23);
 */
def CalEntityRBSATask(entityType, entityIds, updateTime) {
// entityType = 'MF'
//entityIds = NULL
//updateTime = 2025.03.21T10:00:00

	t_cal = get_entity_list_by_latest_return_updatetime(entityType, entityIds, updateTime, true);

	window = 48;
	step = 13;

	if(t_cal.isVoid() || t_cal.size() == 0) return;

	// 拿到所有指数ID
	t_asset_index_map = get_asset_type_index_mapping(NULL, NULL, NULL);
	if(t_asset_index_map.isVoid() || t_asset_index_map.size() == 0) return;
	
	v_asset = t_asset_index_map.asset_type_id.distinct();
	v_index = t_asset_index_map.index_id.distinct();
	
    // 因为用来做基准指数的可能是指数、因子、基金等等任何时间序列数据，所以不用填 entity_type
    t_index_ret = get_entity_return(NULL, v_index, 'w', t_cal.price_date.min().temporalAdd(-window, 'w'), today(), true);

	i = 0;
	batch_size = 200; // 1 hour total
	total_cnt = t_cal.size();

	do {

		tb_result = table(1000:0,
						  ["entity_id", "asset_type_id", "index_id", "effective_date", "level", "alternative_id", "weighting"], 
		                  [iif(entityType=='PF', INT, STRING), STRING, STRING, STRING, INT, STRING, DOUBLE]);

		t = t_cal[i: min(total_cnt, i+batch_size)];

		// 起始日期是最早更新的净值日期再向前推一个时间窗口+10(防止数据缺失的冗余)
		s_json = (SELECT entity_id, price_date.temporalAdd(-window-10, 'w') AS price_date FROM t).toStdJson();
		t_entity_ret = get_entity_ret_by_date(entityType, s_json, 'w', true);
		if(entityType == 'PF') {
			v_entity_id = t_entity_ret.entity_id$INT;
			t_entity_ret.replaceColumn!('entity_id', v_entity_id);
		}

		for(entity in t) {
//entity=t[0]
			entity_ret = SELECT * FROM t_entity_ret WHERE entity_id = entity.entity_id;

			for(asset in v_asset) {
//asset=v_asset[0]
				index_ret = SELECT entity_id, price_date, ret 
				            FROM t_index_ret r
				            INNER JOIN t_asset_index_map m ON r.entity_id = m.index_id
				            WHERE m.asset_type_id = asset
				              AND r.price_date IS NOT NULL;

				// 起始日期是最早更新日期再向前推一个时间窗口
				res = cal_entity_RBSA(entityType, entity_ret, index_ret, 'w', 
				                      entity.price_date.temporalAdd(-window-10, 'w')[0], today(), true, window, step);
	
				if(res.isVoid() || res.size() == 0) continue;
	
				// 每日任务只负责更新最新的rbsa结果
				latest_date = (EXEC price_date.max() AS price_date FROM res)[0];
	
				tb_result.tableInsert(SELECT entity_id, asset, index_id, price_date, level, alternative_id, weights 
				                      FROM res WHERE price_date = latest_date);
		
			}
		}

		if(entityType IN ['MF', 'HF'])
			save_and_sync(tb_result, 'raw_db.pf_fund_rbsa_breakdown', 'raw_db.pf_fund_rbsa_breakdown', 'fund_id', 'effective_date');
		else
			save_and_sync(tb_result, 'raw_db.pf_portfolio_rbsa_breakdown', 'raw_db.pf_portfolio_rbsa_breakdown', 'portfolio_id', 'effective_date');

		i += batch_size;

	} while (i < total_cnt);

}


/*
 *   [定时任务] 计算基金和组合的BFI
 *   
 *   @param entityType <STRING>: MF, HF, PF
 * 
 * 
 *   TODO: max_r2 表在哪里被用到了？应该和基金推荐有关系
 *   
 *   Example: MatchEntityBFITask('MF', 2025.02.21);
 */
def MatchEntityBFITask(entityType, date) {
//entityType = 'MF'
//date = 2025.02.21

	rt = '';

    if(find(['HF', 'MF', 'PF'], entityType) < 0) return null;

    // 取有最新周收益变动的基金列表 (1s)
    tb_cal_entity = get_entity_list_by_weekly_return_updatetime(entityType, NULL, date, true);

    if(tb_cal_entity.isVoid() || tb_cal_entity.size() == 0 ) return;

	i = 0;
	size = tb_cal_entity.size();
	batch_size = 100;
	
	do {

		t_tmp_entity = tb_cal_entity[i : min(size, i+batch_size)];

	    i = i + batch_size;

		// 4 min per 1000 funds or 2 min per 1000 portfolios
	    coe = cal_entity_index_coe(entityType, t_tmp_entity);
	    
	    if(coe.isVoid() || coe.size() == 0) continue;

		entity_info = get_entity_info(entityType, t_tmp_entity.entity_id);
		
		bfi_raw = match_entity_bfi(entityType, entity_info, coe);

		// 先存到数据库，落袋为安
		try {
		    
			// 筛掉 correlation 绝对值不够阈值的记录
	    	t_coe = SELECT entity_id, end_date, index_id,
	    				   iif(coe_1y.abs() < get_min_threshold('correlation'), double(NULL), coe_1y) AS coe_1y,
	    				   iif(coe_3y.abs() < get_min_threshold('correlation'), double(NULL), coe_3y) AS coe_3y,
	    				   iif(coe_5y.abs() < get_min_threshold('correlation'), double(NULL), coe_5y) AS coe_5y,
	    				   t_value_1y, t_value_3y, t_value_5y, beta_1y, beta_3y, beta_5y
					FROM coe;

			DELETE FROM t_coe WHERE coe_1y IS NULL AND coe_3y IS NULL AND coe_5y IS NULL;

			// 候选因子
			t_bfi_candidates = SELECT entity_id, end_date, index_id AS factor_id, coe_1y AS coe, coe_1y.square() AS r2, 'w' AS performance_flag, t_value_1y, beta_1y
			                   FROM t_coe WHERE index_id LIKE 'FA%';
			
			chg_columns_for_mysql(t_coe, iif(entityType == 'PF', 'portfolio_id', 'fund_id'));

			// 只有基金需要存 index_coe 表
		    if(entityType IN ['MF', 'HF']) save_and_sync(t_coe, 'raw_db.pf_fund_index_coe', , 'fund_id', 'end_date');

			// 所有的 factors 存到 xxx_factor_bfi 表；NOTE: Java 把所有 factor 的数据都存起来，这里只存 correlation 达标的记录 （反正这个表没啥用？）
			entity_id_col = iif(entityType == 'PF', 'portfolio_id', 'fund_id');
			chg_columns_for_mysql(t_bfi_candidates, entity_id_col);
	        save_and_sync(t_bfi_candidates, iif(entityType == 'PF', 'raw_db.pf_portfolio_factor_bfi', 'raw_db.cm_fund_factor_bfi'), , entity_id_col, 'end_date');

			if(bfi_raw.isVoid() || bfi_raw.size() == 0) continue;

			// 有效因子
	        t_bfi = SELECT entity_id, end_date, factor_id, coe_1y AS coe, r2, performance_flag, t_value_1y, beta_1y 
	                FROM bfi_raw ORDER BY entity_id, end_date, r2 DESC;

	        // 最大R2因子及所有有效因子标签
			t_max_r2 = SELECT entity_id, factor_id.first() AS factor_id, end_date,
			                  string(NULL) AS performance_flag, coe.first() AS coe, r2.first() AS r2, concat(factor_name, ",") AS rz_portrait
			           FROM ej(t_bfi, get_bfi_index_list(), 'factor_id')
			           GROUP BY entity_id, end_date;

			// 有效 factors 存到 xxx_factor_bfi_by_category_group 表
			chg_columns_for_mysql(t_bfi, entity_id_col);
			save_and_sync(t_bfi, iif(entityType == 'PF', 'raw_db.pf_portfolio_factor_bfi_by_category_group', 'raw_db.pf_fund_factor_bfi_by_category_group'), , entity_id_col, 'end_date');

			// 有效因子中 R2 最大的因子存 xxx_max_r2 
			chg_columns_for_mysql(t_max_r2, entity_id_col);
			save_and_sync(t_max_r2, iif(entityType == 'PF', 'raw_db.pf_portfolio_factor_bfi_max_r2', 'raw_db.pf_fund_factor_bfi_by_category_group_max_r2'), , entity_id_col, 'end_date');


		} catch (ex) {

            //TODO: Log errors
            rt += ex;
        }

	} while (i<size)

	
    return rt;
}

/*
 *   [定时任务] 计算BFI指标并存入数据库
 * 
 *   @param entityType <STRING>: 'MF', 'HF', 'PF'  (MF和HF等效)
 *   @param date <DATETIME>: BFI更新时间, 为空时缺省为当前时间的前1天；为1989.01.01或更早日期时代表初始化，指标会被存入本地数据库
 *   
 *   
 *   Example: calEntityBfiIndicatorTask('MF', 2024.10.28);
 *            calEntityBfiIndicatorTask('PF', 2024.10.28);
 */
def calEntityBfiIndicatorTask(entityType, date) {

// entityType = 'MF'
// date = 2024.10.01

    rt = '';

    if(!(entityType IN ['MF', 'HF', 'PF'])) return null;

	very_old_day = 1900.01.01;

    if(date.isNothing() || date.isNull())
    	end_day = temporalAdd(now(), -1d);
    else
    	end_day = date;

	// 1989.01.01及以前的日期被认为从本地读数据
	isFromMySQL = iif(end_day <= 1989.01.01, false, true);

    // 取有最新bfi变动的基金列表 (1s)
    tb_cal_entities = get_entity_bfi_factors(entityType, NULL, very_old_day.month(), today().month(), end_day);

    if(tb_cal_entities.isVoid() || tb_cal_entities.size() == 0 ) return;

    v_uniq_entity_id = EXEC DISTINCT entity_id FROM tb_cal_entities;

    // 按照 MySQL 建好各表
    tb_bfi_indicator = create_entity_bfi_indicator(iif(entityType=='PF', true, false));

    // 分批跑
    i = 0;
    batch_size = 100;


    do {

        entities = SELECT * FROM tb_cal_entities WHERE entity_id IN v_uniq_entity_id[i : min(v_uniq_entity_id.size(), i+batch_size)];

        if(entities.isVoid() || entities.size() == 0) break;

        // 200ms
        entity_info = SELECT entity_id, end_date.temporalParse('yyyy-MM') AS end_date, inception_date, factor_id AS benchmark_id, ini_value 
                      FROM ej(entities, get_entity_info(entityType, entities.entity_id), 'entity_id');

        // 取月收益 (12s)
        rets = get_monthly_ret(entityType, entity_info.entity_id, very_old_day, entity_info.end_date.max().temporalFormat('yyyy-MM-dd').temporalParse('yyyy-MM-dd').monthEnd(), isFromMySQL);

		// 把 yyyy-MM 格式的 end_date 改成 dolphin 的 MONTH
        v_end_date = rets.end_date.temporalParse('yyyy-MM');
        rets.replaceColumn!('end_date', v_end_date);

        if(!rets.isVoid() && rets.size() > 0) {

            // 计算月度指标 (5s)
            indicators = cal_monthly_indicators(entityType, 'BFI', rets);

            // 仿照MySQL的表结构准备好记录 （1s)
            generate_entity_bfi_indicator(entity_info, indicators, true, tb_bfi_indicator);

        }
        
        i += batch_size;

    } while (i <= v_uniq_entity_id.size());


    if(! tb_bfi_indicator.isVoid() && tb_bfi_indicator.size() > 0) {

        // save data to MySQL
        try {

		    t_desc = get_bfi_indicator_table_description(entityType);
		    
            chg_columns_for_mysql(tb_bfi_indicator, t_desc.sec_id_col[0]);
            db_name = t_desc.table_name[0].split('.')[0];
            save_and_sync(tb_bfi_indicator, t_desc.table_name[0].strReplace(db_name, 'raw_db'), t_desc.table_name[0].strReplace(db_name, 'raw_db'), t_desc.sec_id_col[0], 'end_date');

            // 数据初始化时将指标存入本地，做排名之用
            if(end_day <= get_ini_data_const()['date'])
            	save_table(tb_bfi_indicator, t_desc.table_name[0], false);

        } catch(ex) {

            //TODO: Log errors
            rt = ex;
        }
    }
    
    return rt;
	
}


/*
 *  计算并存储基金经理和公司月度净值
 * 
 *  @return <TABLE>: [COLUMNS] entity_id, curve_type, strategy, end_date, price_date, ret, nav
 *  
 *  NOTE: 基本上和 cal_and_save_mc_monthly_nav 一样
 * 
 */
def cal_and_save_mc_weekly_nav(entity_type, entity_date, is_save_local) {

	rt = '';

	if( !(entity_type in ['PL', 'CO']) ) return rt;

	if(entity_date.isVoid() || entity_date.size() == 0) return rt;

    // 准备类似MySQL结构的数据表
    tb_entity_nav = create_mc_nav();

    // 暂时与 MySQL 保持一致，只计算公募，私募，公私募综合三条时间序列。未来可细化至公、私募+主策略
    d_curve_type = dict(INT, INT);
    d_curve_type[1] = 1; // 私募
    d_curve_type[4] = 2; // 公募
    d_curve_type[7] = -99; // 公私募综合

    // 分批跑
    i = 0;
    batch_size = 1000;

    all_entity_id = entity_date.entity_id.distinct();

    do { // 14 sec

        tb_entity = SELECT entity_id, effective_date FROM entity_date
                    WHERE entity_id IN all_entity_id[i : min(all_entity_id.size(), i+batch_size)];

        if(tb_entity.isVoid() || tb_entity.size() == 0) break;

		s_json = tb_entity.toStdJson();
		t_ret = get_mc_average_return(entity_type, 'w', s_json, 0, 1, true);
     
        for(cur in d_curve_type.keys()) {
//cur=7
			tmp = SELECT entity_id, cur AS curve_type, 0 AS strategy, effective_date, price_date, ret, incl_cal_cnt 
			      FROM t_ret WHERE raise_type = d_curve_type[cur] AND strategy = -99; // 目前只需要全策略

			// 计算周收益
        	tb_nav = cal_mc_nav_by_return(entity_type, tmp, 'w');

	        INSERT INTO tb_entity_nav 
	        	SELECT entity_id, curve_type, strategy, effective_date AS year_week, price_date, nav, ret, incl_cal_cnt
	            FROM ej(tb_nav, tmp, ['entity_id', 'curve_type', 'strategy', 'effective_date']);

        }

        i += batch_size;

    } while (i <= all_entity_id.size());


    if(! tb_entity_nav.isVoid() && tb_entity_nav.size() > 0) {

        // save data to MySQL  (12 sec)
        try {

			entity_id_col = iif(entity_type == 'PL', 'manager_id', 'company_id');

            tb_entity_nav.rename!('entity_id', entity_id_col);
            save_and_sync(tb_entity_nav, iif(entity_type == 'PL', 'raw_db.manager_nav', 'raw_db.company_nav'), , entity_id_col, 'year_week');

            // 数据初始化时将指标存入本地
            if(is_save_local == true) {
            	save_table(tb_entity_nav, iif(entity_type == 'PL', 'mfdb.manager_nav', 'mfdb.company_nav'), false);
            }

        } catch(ex) {

            //TODO: Log errors
            rt += ex;
        }
    }

    return rt;
	
}


/*
 *  [定时任务]: 基金经理/公司周净值计算
 * 
 *   Example: CalMCWeeklyNavTask('PL', 2025.01.23);
 */
def CalMCWeeklyNavTask(entityType, updatetime) {
//updatetime = 2024.11.05;
//entityType = 'CO';
	if(!(entityType IN ['PL', 'CO'])) return;

	is_save_local = iif(updatetime <= get_ini_data_const()['updatetime'], true, false);

	// 60 sec  简化起见，不区分curve_type, strategy； TODO: 性能能否优化？
	if(entityType == 'PL') {
		entity_date = get_manager_list_by_fund_updatetime(updatetime, 'w');
		entity_date.rename!('manager_id', 'entity_id');
	}
	else {
		entity_date = get_company_list_by_fund_updatetime(updatetime, 'w');
		entity_date.rename!('company_id', 'entity_id');
	}
	
	// 15 sec
	cal_and_save_mc_weekly_nav(entityType, entity_date, is_save_local);

	entity_date = null;

}