-- 1/3 : Création de la table temporaire
DROP TABLE IF EXISTS w_dbsetup
;

CREATE TEMP TABLE w_dbsetup AS
    SELECT
    t_widget_user_parameter.oid as uuid,
    t_dashboard.code || '|' || t_chart.code as dashboard_widget_code,
    username,
    title,
    x,
    y,
    w,
    h,
    time_calc,
    visible,
    t_dashboard.code || '|' || t_dashboard_onglet.index as tab_code,
    max_cat,
    array_agg(DISTINCT t_indicateur_serie.code) as include_series_code
		FROM rh.t_widget_user_parameter
		JOIN rh.t_dashboard_widget ON t_dashboard_widget.oid = dashboard_widget_id
		JOIN rh.t_dashboard ON t_dashboard.oid= t_dashboard_widget.dashboard_id
    JOIN rh.t_chart on t_chart.oid = t_dashboard_widget.chart_id
		JOIN rh.t_dashboard_onglet on t_dashboard_onglet.oid = tab_id
		LEFT JOIN rh.t_indicateur_serie ON ARRAY[t_indicateur_serie.oid] <@ include_series_id
		WHERE username = 'cti'
		GROUP BY 1,2,3,4,5,6,7,8,9,10,11, t_dashboard_onglet.index
		ORDER BY 2, 6, 7 LIMIT 0
;

-- 2/3 : Peuplement de la table temporaire
INSERT INTO w_dbsetup(
    uuid,
    dashboard_widget_code,
    username,
    title,
    x,
    y,
    w,
    h,
    time_calc,
    visible,
    tab_code,
    max_cat,
    include_series_code
)
VALUES
('60a09a2f-1714-4903-8a48-e0c7c5d5597e', 'CTI_EGAPRO|CTI_EFFAGE', 'cti', 'Répartion par tranche d''âge', 0, 18, 3, 7, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_FEMMES", "CTI_HOMMES"}'),
('2f166ff5-9eca-4a08-a72f-838cdc314385', 'CTI_EGAPRO|CTI_EFFANC', 'cti', 'Répartion par tranche d''ancienneté', 3, 18, 3, 7, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_FEMMES", "CTI_HOMMES"}'),
('4629419f-a2c6-4627-86b3-786d03ec1db7', 'CTI_EGAPRO|CTI_EFFGEN', 'cti', 'Effectif ', 2, 8, 4, 10, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_FEMMES", "CTI_HOMMES"}'),
('54980aad-8b6b-43e5-8d86-b4b47d46ff66', 'CTI_EGAPRO|CTI_EFFSTAT', 'cti', 'Effectifs par statut', 3, 25, 3, 8, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_FEMMES", "CTI_HOMMES"}'),
('fe63666a-69b0-4c35-8564-d243f605f520', 'CTI_EGAPRO|CTI_ENTSORCAR', 'cti', 'Hommes', 0, 8, 2, 5, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_DEPSALH", "CTI_EFFDH", "CTI_ENTSORH", "CTI_NVSALH", "CTI_TOH"}'),
('591c31d9-cf2a-4b9d-afd3-3605a476fc29', 'CTI_EGAPRO|CTI_ENTSORCAR', 'cti', 'Femmes', 0, 13, 2, 5, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_DEPSALF", "CTI_EFFDF", "CTI_ENTSORF", "CTI_NVSALF", "CTI_TOF"}'),
('da27e182-48ea-4c44-b874-4d3b860c875f', 'CTI_EGAPRO|CTI_MSAGE', 'cti', 'Masse salariale par tranche age', 0, 0, 6, 9, 'val', 't', 'CTI_EGAPRO|1', 0, '{"CTI_MSF", "CTI_MSH"}'),
('8230cb17-4b23-4f64-b513-c1f20d8d91f0', 'CTI_EGAPRO|CTI_MSCSP', 'cti', 'Masse salariale par CSP', 3, 9, 3, 13, 'val', 't', 'CTI_EGAPRO|1', 0, '{"CTI_MSF", "CTI_MSH"}'),
('7506a3e7-1b44-41e9-8727-e9fb1af75441', 'CTI_EGAPRO|CTI_MSCSPMAP', 'cti', 'Masse salariale par CSP (MAP)', 0, 9, 3, 13, 'val', 't', 'CTI_EGAPRO|1', 0, '{"CTI_MS"}'),
('1736e926-d668-4375-b042-95299d51b67c', 'CTI_EGAPRO|CTI_STAT', 'cti', 'Tableau statuts', 4, 0, 2, 8, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_FEMMES", "CTI_HOMMES", "CTI_TOTSAL"}'),
('1bf148e8-996a-4e64-b2b4-df19575c3368', 'CTI_EGAPRO|CTI_STATTIME', 'cti', 'Statuts', 0, 25, 3, 8, 'cum', 't', 'CTI_EGAPRO|0', 0, '{"CTI_TAUXSTAT", "CTI_TOTSAL", "CTI_TOTSALF"}'),
('732c7aad-a075-4ed8-a0b6-7a378af82d81', 'CTI_EGAPRO|CTI_TYPECNT', 'cti', 'Types de contrats - Femmes', 0, 0, 2, 8, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_FEMMES"}'),
('ac7b3909-8d3a-497e-9996-3b1111ded80e', 'CTI_EGAPRO|CTI_TYPECNT', 'cti', 'Types de contrats - Hommes', 2, 0, 2, 8, 'val', 't', 'CTI_EGAPRO|0', 0, '{"CTI_HOMMES"}')
;

-- 3/3 : Màj de la table iCTI
DELETE FROM rh.t_widget_user_parameter WHERE username = 'cti';

INSERT INTO rh.t_widget_user_parameter(
	oid, 
	dashboard_widget_id,
	username,
	title,
	x,
	y,
	w,
	h,
	time_calc,
	visible,
	tab_id,
	max_cat,
	include_series_id
)
	SELECT
		w_dbsetup.uuid,
		t_dashboard_widget.oid,
		w_dbsetup.username,
		w_dbsetup.title,
		w_dbsetup.x,
		w_dbsetup.y,
		w_dbsetup.w,
		w_dbsetup.h,
		w_dbsetup.time_calc,
		w_dbsetup.visible,
		rh.t_dashboard_onglet.oid,
		w_dbsetup.max_cat,
		case when array_agg(DISTINCT t_indicateur_serie.oid) = ARRAY[NULL]::bigint[] then ARRAY[]::bigint[] else array_agg(DISTINCT t_indicateur_serie.oid) end
	FROM w_dbsetup
	JOIN rh.t_dashboard ON t_dashboard.code = SPLIT_PART(dashboard_widget_code, '|', 1)
	JOIN rh.t_chart on t_chart.code = SPLIT_PART(dashboard_widget_code, '|', 2)
	JOIN rh.t_dashboard_widget ON t_dashboard_widget.dashboard_id = t_dashboard.oid  and t_dashboard_widget.chart_id = t_chart.oid
	JOIN rh.t_dashboard_onglet ON t_dashboard_onglet.dashboard_id = t_dashboard.oid and t_dashboard_onglet.index = SPLIT_PART(tab_code, '|', 2)
	LEFT JOIN rh.t_indicateur_serie ON ARRAY[t_indicateur_serie.code] <@ include_series_code
	GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12
	ORDER BY 1
;