<?php

namespace Modules\Reports\AriaReports\Model\GetPivotTableStructure;

abstract class RequestAbstract extends \Modules\Reports\AriaReports\Model\RequestAbstract
{

	protected function init(): void
	{

		$currenciesCfg = \App\Config\Currencies::getInstance();

		$this->systemRole = trim('
You are the AI assistant of a CEO/CFO/COO of a travel agency - you know all about the bookings, operational and financial transactions that happen every day in my travel agency and you will help me design a pivot table report.
Your goal is to define all details required to build a pivot table: filters, row grouping, column grouping, and aggregated data fields.
I will present you all the columns in my database table and based on my prompt you should suggest how I should group, order and display the data.
		');

		$this->systemRole .= "\n\n";

		// add specific report source summary
		$this->systemRole .= $this->getSpecificReportSourceSummary();

		$this->systemRole .= "\n\n";
		$this->systemRole .= sprintf('The default currency is %s and the work currency is %s.', $currenciesCfg->getDefaultCurrency(), $currenciesCfg->getWorkCurrency() );
		if (CURRENT_ID_USER) {
			$this->systemRole .= sprintf('If I want to filter by my records (invoices, services, orders, etc) you should know that my user id id %s.', CURRENT_ID_USER);
		}
		$this->systemRole .= 'When grouping by currency (for rows or columns field), the data field should use amounts displayed in the original currency. In other cases (which are more common) you should use the work or default currency based on scenario but in these cases to not suggest grouping by currency.';

	}

	abstract protected function getSpecificReportSourceSummary(): string;


	protected function populateBody(array &$body): void
	{

		$currenciesCfg = \App\Config\Currencies::getInstance();

		$body['tools'] = [
			[
				'type' => 'function',
				'name' => 'determine_pivot_table_structure',
				'description' => $this->populateBody_getPivotTableStructure(),
				'parameters' => [
					'type' => 'object',
					'properties' => [
						'tableFields' => [
							'type' => 'object',
							'properties' => $this->populateBody_getReportSourceFields(),
						],

						'filters' => [
							'type' => 'array',
							'description' => 'the list of filters that must be applied on my data before ',
							'items' => [
								'type' => 'object',
								'description' => 'the details of the a single filter that must be applied on my data',
								'properties' => [
									'field' => [
										'type' => 'string',
										'description' => 'one of the fields from the tableFields list',
									],
									'operation' => [
										'type' => 'string',
										'description' => 'the operation that I must apply on the column; for "IN" and "NOT IN" I expect a list of values separated by comma;', //  for "BETWEEN" and "NOT BETWEEN" I expect two min/max values separated by comma, usually numeric or date values
										'enum' => [ '=', '!=', '>', '<', '>=', '<=', 'IN', 'NOT IN' ], // , 'BETWEEN', 'NOT BETWEEN' // we had some issues when working with between / notBetween
									],
									'value' => [
										'type' => 'string',
										'description' => 'the value applied on current column operation',
									]
								],
								'required' => ['field', 'operation', 'value']
							],
						],

						'rowsCategory' => [
							'type' => 'object',
							'description' => 'the details needed to display the pivot table for the rows area',
							'properties' => [
								'field' => [
									'type' => 'string',
									'description' => 'one of the table fields from the list; this will be used as rows grouping criterion',
								],
								'limit' => [
									'type' => 'integer',
									'description' => 'the maximum number of elements when displaying the rows items in the pivot table; if set to 0, all items will be displayed. When the rows field is one that has many possible values (eg. companies, customers, passengers, suppliers, destinations, etc) the grouping will result in a pivot table with too many distinct values, so limiting is required (usually 10 is a common number for limiting the values, unless other values are specified). For fields like year, month, statuses of any kind, etc. we should not apply limiting.',
								],
								'displayOthers' => [
									'type' => 'boolean',
									'description' => 'if set to true, all the remaining items after limiting will be grouped under a "Others" category. This should be set only if the limit is also set as a positive value.',
								],
								'orderField' => [
									'type' => 'string',
									'description' => 'this field indicates how rows items are ordered when displayed in the pivot table; choose "currentField" if the ordering of the rows items is done based on the actual rows items or choose "dataField" if the ordering of the rows items is done based on the aggregated values from data area and associated to rows items. Usually for top clients/destinations/suppliers/etc you should choose "dataField" and probably a limit should be applied; for fields like year/month/status/etc you should choose "currentField" and limit should not be used',
									'enum' => ['currentField', 'dataField'],
								],
								'orderDirection' => [
									'type' => 'string',
									'description' => 'this field indicates the direction of the ordering from "orderField" - "ASC" is for ascending and "DESC" is for descending',
									'enum' => ['ASC', 'DESC'],
								],
							],
							'required' => ['field', 'limit', 'displayOthers', 'orderField', 'orderDirection'],
						],

						'colsCategory' => [
							'type' => 'object',
							'description' => 'the details needed to display the pivot table for the cols (columns) area',
							'properties' => [
								'field' => [
									'type' => 'string',
									'description' => 'this is the actual columns (cols) field used in the columns grouping criterion; it is one of the table fields from the list',
								],
								'limit' => [
									'type' => 'integer',
									'description' => 'the maximum number of distinct elements when displaying the column items in the pivot table; if set to 0, all items will be displayed. When the columns field is one that has many possible values (eg. companies, customers, passengers, suppliers, destinations, etc) limiting is required (usually 10 is a common value, unless other values are specified). For fields containing year, month, statuses, etc. we should not apply limiting.',
								],
								'displayOthers' => [
									'type' => 'boolean',
									'description' => 'if set to true, all the remaining items after limiting will be grouped under a "Others" category. This should be set only if the limit is also set as a positive value.',
								],
								'orderField' => [
									'type' => 'string',
									'description' => 'this field indicates how cols (columns) items are ordered when displayed in the pivot table; choose "currentField" if the ordering of the cols items is done based on the actual cols items or choose "dataField" if the ordering of the cols items is done based on the aggregated values from data area and associated to cols items. Usually for top clients/destinations/suppliers/etc you should choose "dataField" and probably a limit should be applied; for fields like year/month/status/etc you should choose "currentField" and limit should not be used',
									'enum' => ['currentField', 'dataField'],
								],
								'orderDirection' => [
									'type' => 'string',
									'description' => 'this field indicates the direction of the ordering from "orderField" - "ASC" is for ascending and "DESC" is for descending',
									'enum' => ['ASC', 'DESC'],
								],
							],
							'required' => ['field', 'limit', 'displayOthers', 'orderField', 'orderDirection'],
						],

						'dataCategory' => [
							'type' => 'object',
							'description' => 'the details needed to display the pivot table for the data area (the actual information)',
							'properties' => [
								'field' => [
									'type' => 'string',
									'description' => 'one of the table fields from the list, this will be the actual information displayed in the pivot table; on this field, an aggregate function will always be applied',
								],
								'aggregateFunction' => [
									'type' => 'string',
									'description' => 'the aggregate function that must be applied to the data field when the pivot is displayed',
									'enum' => ['SUM', 'AVG', 'MIN', 'MAX', 'COUNT'],
								],
								'currency' => [
									'type' => 'string',
									'description' => 'the currency that must be used when displaying the data in the pivot table - this is based on the currency of the selected field. This should probably be set when working with aggregate functions like SUM, AVG, MIN or MAX and the column represents an amount; if COUNT aggregate function is chosen, it should not be set.',
								]
							],
							'required' => ['field', 'aggregateFunction'],
						],

					],
					'strict' => true,
				],
			],
		];
		$body['tool_choice'] = 'auto';

	}

	private function populateBody_getPivotTableStructure(): string
	{

		// presenting the main details for the pivot table
		$return = trim('
There are four main decisions (presented as output) that I need in order to create the pivot table:
- filters - based on my prompt I should decide which filters I should apply and on which fields; I can have multiple filters applied, but there can only be one filter per field.
- row field grouping - select one of the table fields from the list that I can use as rows grouping criterion; also ordering details are needed and, optionally, a limit might be applied to display only the first N items;
- column field grouping - select one of the table fields from the list that I can use as columns grouping criterion; also ordering details are needed and, optionally, a limit might be applied to display only the first N items;
- data field - select one of the table fields from the list that I can use as data to be aggregated via one of these functions: SUM, AVG, MIN, MAX, COUNT; also ordering details are needed;
Let\'s take them one by one and explain more details for each of the output.
		');
		$return .= "\n\n";

		// describe filters
		$return .= trim('
Filters
The data that is aggregated into my pivot table can be filtered by multiple fields (you need to decide this based on my prompt). Each filter will be applied on a specific field and you mustn\'t include the same table field in two or more filters. For each filter you will need to provide three details: field (one from my table), operation (equal, greater than, between, etc) and filter value (a value to compare against - please determine correct values based on the field type).
Filtering is mandatory for the report but if you cannot decide on which filters I need, you can just suggest some filters based on your experience and knowledge of the data. Usually a filter based on a date interval should be applied, but sometimes I forget to mention that in my prompt so it is best to suggest a relevant filtering based on your experience (a common filter might be one full year, or year-to-date).
If my prompt does not mention anything related to changing filters, try to keep the previous ones.
Specific details about the format of the fields:
- for "date" format, always use an ISO format, or use null if the field should be empty;
- "month" format is a number between 1 and 12, or 0 if the field should be empty;
- "year+month" format is actually a numeric value of 6 digits composed of year (4 digits) and month (2 digits); use 0 if the field should be empty;
		');
		$return .= "\n" . $this->populateBody_getPivotTableStructure_specificFilters();
		$return .= "\n\n";

		// describe fields
		$return .= trim('
Row, column and data fields
In order to create the pivot table, you need to select the row field (the table field used as rows grouping criteria), the column field (the table field used as rows grouping criteria) and the data field (the table field used to aggregate the displayed data). For row and column fields you can select both fields or at least one, but you cannot have no field at all. In the case that only one of the fields is selected, you should decide whether to include it as row or column.   
For rows and columns grouping fields you must also decide on these details:
- order: it can be the current (row/column) field or the data field (the displayed value). For example, if I group by months, I usually want to order by the months (from Jan to Dec) - so I need current field ordering. If I group by customer I probably need to see the top 10 customers and not order by the customer name alphabetically as I will only see a small fraction of them (and the number of records will be limited) - so I need data field ordering.  
- direction: ascending or descending. Common cases are that I will use "ascending" for current field ordering and "descending" for data field ordering. 
- limit: this is used when the number of distinct values of the field is too great and we cannot display the pivot table; usually, when this happens, the ordering is done by the data field in a descending direction. A common example is when I group by customer, companies, supplier, service type, destination, etc - there are too many distinct values and we\'ll need to see only the most relevant 10 records. But for years, months, client type, statuses, etc we do not need to limit the number of records. 
For the data field you must also choose these details:
When selecting the field for row/column you must be very strict and be very sure that this is what I want, otherwise just reply with a text message and ask for more details. 
On the other hand, once you select a field, it should be easy enough to select the other details (order, direction and limit) based on my prompt or your experience.

For data field you must also choose the aggregate function that needs to be applied to the data (SUM, AVG, MIN, MAX, COUNT) - the most common case is that I will use SUM for the data field.
When selecting the data field and its aggregate function, you should decide it but be very sure that this is what I need based on my prompt or your experience. If you cannot decide on the field or the aggregate function, you should reply with a text message and ask for more details - also suggestions from your side are welcomed based on the report context.

If any of the following cases occur, you should reply with a text message and ask for more details:
- it is not clear for you what is the report that I need;
- it is not clear which filters I should apply; 
- you cannot decide neither on a field for row field grouping nor a column field grouping;
- you cannot decide on the data field;
The reply message from you should address all of the missing info mentioned above, but at the same time, if you can decide on part of these mandatory fields, you should include them in your message - this will help me write a better prompt, and I will include only the missing data that you request.   
Keep in mind that sometimes I do not know exactly what is the report that I want or maybe I find it hard to explain, so in those cases, you should just suggest how to group fields based on your CFO knowledge and experience (and given that you have the list of available fields). 

When you reply with a text message, do not include internal references (like column codes), just suggest some field names in a human friendly way - this will help me decide what data I need in my report. Try to be very concise and short when replying back.
		');

		return $return;

	}

	abstract protected function populateBody_getPivotTableStructure_specificFilters(): string;

	abstract protected function populateBody_getReportSourceFields(): array;



}