#
#
# add_raw_data()
#
#


# This builds the raw report element table rows,

include "templates.statistics.raw_report_element.standard.add_raw_data_row";
include "templates.statistics.raw_report_element.standard.add_raw_data_outer_row";
include "templates.statistics.raw_report_element.standard.add_raw_aggregation_row";

subroutine(add_raw_data(
	string session_id,
	string profile_name,
	node query_fields,
	string database_last_modification_time,
	node raw_report_element,
	node raw_rows,
	node raw_aggregation_rows,
	string alpha_root_table_id,
	string root_table_id, # from outer table if pivot table
	string sorted_table_id, # from outer table if pivot table
	node base_query_header, # from outer table if pivot table (same is true for starting row, etc.)
	int starting_row,
	int ending_row,
	bool show_remainder_row,
	bool show_averages_row,
	bool show_min_row,
	bool show_max_row,
	bool show_totals_row,
	bool is_log_detail,
	bool is_extended_log_detail,
	bool is_pivot_table,
	bool is_hierarchical_table,
	string main_report_field_name,
	string pivot_report_field_name,
	string combined_filter_expression,
	bool is_table_filter_expression,
	bool use_overview_for_totals,
	int number_of_days,
	node progress_substep_info,
	node collected_whitelist_objects), (

	debug_message("\n\n#### add_raw_data() START \n\n");
	
	table the_table = load_table(sorted_table_id);
	
	# DEBUG TABLE
	# include "lib.table.table_as_string";
	# string the_table_string = table_as_string(the_table);
	# debug_message("\n" . the_table_string . "\n");
	
	bool is_inner_table = false;
	node column;
	
	#
	#
	# Set ssql row numbers and remainder values
	#
	#
	
	int number_of_ssql_rows = table_get_num_rows(the_table);
	int number_of_items_in_remainder = 0;
	bool is_remainder_value = false;
	
	int ssql_starting_row;
	int ssql_ending_row;
	
	int row_number = starting_row; # The row number displayed in the index column
	
	if (!is_log_detail or is_extended_log_detail) then (

		ssql_starting_row = starting_row - 1;
		ssql_ending_row = ending_row - 1;
		
		if (ssql_ending_row >= number_of_ssql_rows) then (
			ending_row = number_of_ssql_rows;
			ssql_ending_row = number_of_ssql_rows - 1;
		);

		if (show_remainder_row and (ending_row < number_of_ssql_rows)) then (
			is_remainder_value = true;
			number_of_items_in_remainder = number_of_ssql_rows - ending_row;
		);
	)
	else (
	
		# Log detail ssql table with limited number of rows
		# It only contains the number of rows between starting_row and ending_row, 
		# ssql_starting_row is always 0
	
		ssql_starting_row = 0;
		ssql_ending_row = ending_row - starting_row;
	);
	
	int ssql_row_number = ssql_starting_row;

	debug_message("\n#### starting_row: " . starting_row . "\n");
	debug_message("#### ending_row: " . ending_row . "\n");

	debug_message("#### number_of_ssql_rows: " . number_of_ssql_rows . "\n");
	
	debug_message("#### ssql_starting_row: " . ssql_starting_row . "\n");
	debug_message("#### ssql_ending_row: " . ssql_ending_row . "\n");
	
	debug_message("#### ssql_row_number: " . ssql_row_number . "\n");
	
	debug_message("#### is_remainder_value: " . is_remainder_value . "\n");
	debug_message("#### number_of_items_in_remainder: " . number_of_items_in_remainder . "\n");
	
	
	#
	#
	# set pivot table parameters
	#
	#
	
	int number_of_non_aggregating_fields_in_outer_table;
	bool is_multiple_non_aggregating_fields_in_outer_table;

	# int main_ssql_column_number;
	
	node where_column;
	node pivot_table_qs_where_structure;
	int where_ssql_column_number;
	string where_table_field_name;
	
	node inner_raw_rows;
	node inner_raw_aggregation_rows;
	int main_column_itemnumber;
	
	string draft_inner_table_qs;
	string inner_table_qs_where_part;
	string inner_table_qs;
	node inner_base_query_header;
	string inner_table_id;
	
	int number_of_items_in_inner_remainder = 0;
		
	table inner_table;
	
	int inner_ending_row;
	int inner_total_rows;
	
	bool show_inner_remainder_row;
	bool show_inner_averages_row;
	bool show_inner_min_row;
	bool show_inner_max_row;
	bool show_inner_totals_row;
	
	bool set_inner_remainder_values = false;
	node inner_remainder_query_header;
	string inner_cutoff_table_id;
	string inner_cutoff_sum_table_id;
	string inner_cutoff_sum_qs;
	
	bool set_inner_min_values = false;
	string inner_min_table_id;
	string inner_min_qs;
	node inner_min_query_header;
	
	bool set_inner_max_values = false;
	string inner_max_table_id;
	string inner_max_qs;
	node inner_max_query_header;
	
	
	if (is_pivot_table) then (
	
		node pivot_table_info = "v.pivot_table_info";
		
		pivot_table_qs_where_structure = pivot_table_info{"qs_where_structure"};

		number_of_non_aggregating_fields_in_outer_table = @pivot_table_info{"number_of_non_aggregating_fields_in_outer_table"};
		is_multiple_non_aggregating_fields_in_outer_table = if (number_of_non_aggregating_fields_in_outer_table > 1) then (true) else (false);
	
		debug_message("#### number_of_non_aggregating_fields_in_outer_table: " . number_of_non_aggregating_fields_in_outer_table . "\n");
		debug_message("#### is_multiple_non_aggregating_fields_in_outer_table: " . is_multiple_non_aggregating_fields_in_outer_table . "\n");		
		
		draft_inner_table_qs = @pivot_table_info{"qs"};
		inner_base_query_header = pivot_table_info{"base_query_header"};
		
		inner_table_id = @pivot_table_info{"inner_table_id"};
		
		show_inner_remainder_row = @pivot_table_info{"show_remainder_row"};
		show_inner_averages_row = @pivot_table_info{"show_averages_row"};
		show_inner_min_row = @pivot_table_info{"show_min_row"};
		show_inner_max_row = @pivot_table_info{"show_max_row"};
		show_inner_totals_row = @pivot_table_info{"show_totals_row"};
		
		debug_message("#### show_inner_remainder_row: " . show_inner_remainder_row . "\n\n");
		debug_message("#### show_inner_totals_row: " . show_inner_totals_row . "\n\n");
		
		if (show_inner_remainder_row and pivot_table_info?{"inner_remainder_query_header"}) then (
		
			# We only need to set remainder values in inner_base_query_header if an inner_remainder_query_header exists!
			set_inner_remainder_values = true;
			inner_remainder_query_header = pivot_table_info{"inner_remainder_query_header"};
			inner_cutoff_table_id = @pivot_table_info{"inner_cutoff_table_id"};
			inner_cutoff_sum_table_id = @pivot_table_info{"inner_cutoff_sum_table_id"};
			inner_cutoff_sum_qs = @pivot_table_info{"inner_cutoff_sum_qs"};
		);
		
		if (show_inner_min_row and pivot_table_info?{"inner_min_query_header"}) then (
		
			set_inner_min_values = true;
			inner_min_query_header = pivot_table_info{"inner_min_query_header"};
			inner_min_table_id = @pivot_table_info{"inner_min_table_id"};
			inner_min_qs = @pivot_table_info{"inner_min_qs"};
		);
		
		if (show_inner_max_row and pivot_table_info?{"inner_max_query_header"}) then (
		
			set_inner_max_values = true;
			inner_max_query_header = pivot_table_info{"inner_max_query_header"};
			inner_max_table_id = @pivot_table_info{"inner_max_table_id"};
			inner_max_qs = @pivot_table_info{"inner_max_qs"};
		);
		
		#
		# Create report_field_to_inner_ssql_column_number node so
		# that we get the ssql column number of the inner_base_query_header
		# by the report_field_name
		# v.report_field_to_inner_ssql_column_number.file_types = 0
		# v.report_field_to_inner_ssql_column_number.hits = 1
		#
		
		delete_node("v.report_field_to_inner_ssql_column_number");
		
		v.report_field_to_inner_ssql_column_number = "";
		
		foreach column inner_base_query_header (
		
			"v.report_field_to_inner_ssql_column_number." . @column{"report_field_name"} = node_name(column);
		);
		
	);


	# node column;
	int ssql_column_number;
	
	float _100_divided_by_total;
	float cutoff_value;
	float remainder_value;
	float average_value;
	
	string column_name;
	node inner_column;
	
	
	#
	#
	#
	#
	# Set aggregation values in outer base_query_header (remainder, average, min, max, sum)
	#
	#
	#
	#
	
	# Check if we have to aggregate something
	bool is_aggregating_field_in_output = get_is_aggregating_field_in_output(base_query_header);
	
	if (is_aggregating_field_in_output) then (
	
		# Always set the total because it may be required for percentage, remainder or average calculation.
		
		set_sum_and_total_in_query_header(
			session_id,
			profile_name,
			query_fields,
			database_last_modification_time,
			alpha_root_table_id,
			root_table_id,
			base_query_header,
			combined_filter_expression,
			is_table_filter_expression,
			use_overview_for_totals,
			number_of_days,
			collected_whitelist_objects
		);
	
	
		#
		# Handle remainder
		#

		if (is_remainder_value) then (
		
			node remainder_query_header = get_aggregation_query_header("remainder", "remainder_query_header", base_query_header);
			
			set_remainder_in_query_header(
				profile_name,
				sorted_table_id,
				remainder_query_header,
				base_query_header,
				ending_row,
				number_of_days,
				collected_whitelist_objects
			);
		);
	
	
		#
		# Handle average
		#
		
		if (show_averages_row) then (
			set_average_in_query_header(
				base_query_header,
				number_of_ssql_rows,
				number_of_days
			);
		);
		
		
		#
		# Handle min, max
		#
		
		if (show_min_row) then (
		
			node min_query_header = get_aggregation_query_header("min", "min_query_header", base_query_header);
			
			set_min_max_in_query_header(
				profile_name,
				"min",
				root_table_id,
				min_query_header,
				base_query_header,
				number_of_days,
				collected_whitelist_objects
			);
		);
		
		if (show_max_row) then (
		
			node max_query_header = get_aggregation_query_header("max", "max_query_header", base_query_header);
			
			set_min_max_in_query_header(
				profile_name,
				"max",
				root_table_id,
				max_query_header,
				base_query_header,
				number_of_days,
				collected_whitelist_objects
			);
		);
	);
	
	#
	#
	#
	# Create the rows with columns
	#
	#
	#

	# Note, the column numbers in rows may not match the query header columns
	# I.e., if "show_percent_column = true" then we give it its own columns
	# in rows. So the rows columns truly match the final html table columns.
	
	node the_row;
	node the_inner_row;
	
	int inner_ssql_row_number;
	int inner_row_number;
	
	
	#
	#
	# Build the rows
	#
	#
		
	int raw_row_number = 0;

	# PROGRESS_SETTER
	prepare_increment_iterative_progress_substep(progress_substep_info); # Required before we use increment_iterative_progress_substep in loop!
	
	# Make sure ssql_row_number is >= 0
	
	if (ssql_row_number >= 0) then (
		
		for (ssql_row_number; ssql_row_number <= ssql_ending_row; ssql_row_number++) (
		
			set_subnode_value(raw_rows, raw_row_number, "");
			the_row = raw_rows{raw_row_number};
			
			if (!is_pivot_table) then (
				
				debug_message("2 ssql_row_number: " . ssql_row_number . "\n");
				
				add_raw_data_row(
					ssql_row_number,
					row_number,
					the_row,
					base_query_header,
					the_table,
					is_hierarchical_table,
					main_report_field_name,
					pivot_report_field_name,
					false
				);
				
				
				# PROGRESS_SETTER
				# Handle progress for non-pivot table, in this case we set progress for every 25 rows
				
				if ((ending_row > 25) and ((ssql_row_number % 25) == 0)) then (
					
					# set_iterative_progress_substep(progress_substep_info, ssql_row_number, ending_row);
					increment_iterative_progress_substep(progress_substep_info, ssql_row_number, ending_row);
				);
			)
			else (
			
				add_raw_data_outer_row(
					ssql_row_number,
					the_row,
					base_query_header,
					inner_base_query_header,
					"v.report_field_to_inner_ssql_column_number",
					the_table,
					is_hierarchical_table,
					main_report_field_name
				);
				
				
				# PROGRESS_SETTER
				# Handle progress for pivot table, in this case we set progress for every outer row
				# set_iterative_progress_substep(progress_substep_info, ssql_row_number, ending_row);
				increment_iterative_progress_substep(progress_substep_info, ssql_row_number, ending_row);
			);
			
			#
			#
			#
			# Handle Pivot Table
			#
			#
			#
			
			if (is_pivot_table) then (
			
			
				#
				#
				# Create inner table
				#
				#
			
				
				#
				# Get the main report field itemnumber(s).
				# Note, we don't use the itemvalue in WHERE but the itemnumber only!
				#
				
				inner_table_qs_where_part = "WHERE ";
				
				foreach where_column pivot_table_qs_where_structure (
				
					where_ssql_column_number = @where_column{"ssql_column_number"};
					where_table_field_name = @where_column{"table_field_name"};
				
					inner_table_qs_where_part .= where_table_field_name . "=" . table_get_cell_value(the_table, ssql_row_number, where_ssql_column_number) . " AND ";
				);
				
				inner_table_qs_where_part = replace_last(inner_table_qs_where_part, " AND ", "");
				inner_table_qs = replace_last(draft_inner_table_qs, "__WHERE__CLAUSE__PLACEHOLDER__", inner_table_qs_where_part);
							
				
				debug_message("\n#### inner_table_id: " . inner_table_id . "\n");
				debug_message("#### inner_table_qs: " . inner_table_qs . "\n\n");
				
				ssql_query(inner_table_qs);
				
				
				# TEMP
				# debug_ssql_table(inner_table_id);
				
				inner_table = load_table(inner_table_id);
				
				inner_total_rows = table_get_num_rows(inner_table);
				inner_ending_row = @pivot_table_info{"number_of_rows"};
				if (inner_ending_row > inner_total_rows) then (
					inner_ending_row = inner_total_rows;
				);
				
				
				# debug_message("#### inner_ending_row: " . inner_ending_row . "\n");
				
				
				#
				#
				# Set sum in inner_base_query_header
				#
				#
				
				# The sum has already been set in inner_base_query_header by add_raw_data_outer_row()!
	
				
				
				#
				#
				# Set remainder in inner_base_query_header
				#
				#
				
				bool is_inner_remainder_values = false;
				
				if (set_inner_remainder_values and (inner_ending_row < inner_total_rows)) then (
				
					is_inner_remainder_values = true;
					number_of_items_in_inner_remainder = inner_total_rows - inner_ending_row;
					
					set_inner_remainder_in_query_header(
						inner_table_id,
						inner_cutoff_table_id,
						inner_cutoff_sum_table_id,
						inner_cutoff_sum_qs,
						inner_remainder_query_header,
						inner_base_query_header,
						"v.report_field_to_inner_ssql_column_number",
						inner_ending_row
					);
				);
				
				debug_message("#### is_inner_remainder_values: " . is_inner_remainder_values . "\n");
				
				#
				#
				# Set average in inner_base_query_header
				#
				#
				
				if (show_inner_averages_row) then (
				
					set_average_in_query_header(
						inner_base_query_header,
						inner_total_rows,
						number_of_days
					);
				);
				
				#
				#
				# Set min in inner_base_query_header
				#
				#
				
				if (set_inner_min_values) then (
				
					set_inner_min_max_in_query_header(
						"min",
						inner_table_id,
						inner_min_table_id,
						inner_min_qs,
						inner_min_query_header,
						inner_base_query_header,
						"v.report_field_to_inner_ssql_column_number"
					);
				);
				
				#
				#
				# Set max in inner_base_query_header
				#
				#
				
				if (set_inner_max_values) then (
				
					set_inner_min_max_in_query_header(
						"max",
						inner_table_id,
						inner_max_table_id,
						inner_max_qs,
						inner_max_query_header,
						inner_base_query_header,
						"v.report_field_to_inner_ssql_column_number"
					);
				);
				
				
				#
				#
				# Build the inner rows
				#
				#
				
				the_row . ".rows" = "";
				the_row . ".aggregation_rows" = "";
				inner_raw_rows = the_row{"rows"};
				inner_raw_aggregation_rows = the_row{"aggregation_rows"};
					
				for (inner_ssql_row_number = 0; inner_ssql_row_number < inner_ending_row; inner_ssql_row_number++) (
					
					inner_row_number = inner_ssql_row_number + 1;
					
					inner_raw_rows . "." . inner_ssql_row_number = "";
					the_inner_row = inner_raw_rows{inner_ssql_row_number};
					
					add_raw_data_row(
						inner_ssql_row_number,
						inner_row_number,
						the_inner_row,
						inner_base_query_header,
						inner_table,
						is_hierarchical_table,
						main_report_field_name,
						pivot_report_field_name,
						true
					);
				);
				
				#
				# Unload and Drop the inner table if the drill down rows are complete
				#
				
				unload_table(inner_table);
				ssql_query("DROP TABLE " . inner_table_id);
				
				#
				#
				# Add inner aggregation rows (remainder, average, min, ...)
				#
				#
				
				is_inner_table = true;
				
				if (is_inner_remainder_values) then (
					add_raw_aggregation_row(inner_base_query_header, inner_raw_aggregation_rows, "remainder", is_inner_table, pivot_report_field_name, number_of_items_in_inner_remainder);
				);
				
				if (show_inner_averages_row) then (
					add_raw_aggregation_row(inner_base_query_header, inner_raw_aggregation_rows, "average", is_inner_table, pivot_report_field_name, 0);
				);
				
				if (show_inner_min_row) then (
					add_raw_aggregation_row(inner_base_query_header, inner_raw_aggregation_rows, "min", is_inner_table, pivot_report_field_name, 0);
				);
			
				if (show_inner_max_row) then (
					add_raw_aggregation_row(inner_base_query_header, inner_raw_aggregation_rows, "max", is_inner_table, pivot_report_field_name, 0);
				);
				
				if (show_inner_totals_row) then (
					add_raw_aggregation_row(inner_base_query_header, inner_raw_aggregation_rows, "total", is_inner_table, pivot_report_field_name, 0);
				);
				
			); # is_pivot_table
			
			row_number++;
			raw_row_number++;
		);
	);
	
	#
	#
	# Add outer aggregation rows (remainder, average, min, ...)
	#
	#
	
	is_inner_table = false;
	
	if (is_remainder_value) then (
		add_raw_aggregation_row(base_query_header, raw_aggregation_rows, "remainder", is_inner_table, pivot_report_field_name, number_of_items_in_remainder);
	);
	
	if (show_averages_row) then (
		add_raw_aggregation_row(base_query_header, raw_aggregation_rows, "average", is_inner_table, pivot_report_field_name, 0);
	);
	
	if (show_min_row) then (
		add_raw_aggregation_row(base_query_header, raw_aggregation_rows, "min", is_inner_table, pivot_report_field_name, 0);
	);
	
	if (show_max_row) then (
		add_raw_aggregation_row(base_query_header, raw_aggregation_rows, "max", is_inner_table, pivot_report_field_name, 0);
	);
	
	if (show_totals_row) then (
		add_raw_aggregation_row(base_query_header, raw_aggregation_rows, "total", is_inner_table, pivot_report_field_name, 0);
	);
	
	
	#
	# Unload the outer table
	#
	
	unload_table(the_table);
	
	debug_message("\n\n#### add_raw_data() END \n\n");
));