<?php

namespace Limb_Chatbot\Includes\Services;

use WP_Error;

/**
 * Service for validating and compiling structured lead filters.
 *
 * @since 1.0.13
 */
class Lead_Filters_Service {

	/**
	 * Allowed filter targets.
	 *
	 * @var string[]
	 */
	private const ALLOWED_TARGETS = array( 'lead', 'lead_value' );

	/**
	 * Allowed lead table fields.
	 *
	 * @var string[]
	 */
	private const ALLOWED_LEAD_FIELDS = array(
		'id',
		'chatbot_user_uuid',
		'chatbot_uuid',
		'source_type',
		'source',
		'source_title',
		'created_at',
		'status',
		'is_qualified',
	);

	/**
	 * Operators that do not require a value.
	 *
	 * @var string[]
	 */
	private const VALUELESS_OPERATORS = array( 'is empty', 'is not empty' );

	/**
	 * Normalized operator aliases.
	 *
	 * @var array<string,string>
	 */
	private const OPERATOR_ALIASES = array(
		'is'               => 'is',
		'eq'               => 'is',
		'='                => 'is',
		'is not'           => 'is not',
		'is_not'           => 'is not',
		'!='               => 'is not',
		'contains'         => 'contains',
		'does not contain' => 'does not contain',
		'does_not_contain' => 'does not contain',
		'starts with'      => 'starts with',
		'starts_with'      => 'starts with',
		'ends with'        => 'ends with',
		'ends_with'        => 'ends with',
		'is empty'         => 'is empty',
		'is_empty'         => 'is empty',
		'is not empty'     => 'is not empty',
		'is_not_empty'     => 'is not empty',
	);

	/**
	 * Validate filters payload.
	 *
	 * @param mixed $filters Raw filters payload.
	 *
	 * @return true|WP_Error
	 * @since 1.0.13
	 */
	public function validate_filters( $filters ) {
		if ( null === $filters ) {
			return true;
		}

		if ( ! is_array( $filters ) ) {
			return new WP_Error( 'invalid_filters', __( 'filters must be an array.', 'limb-chatbot' ) );
		}

		foreach ( $filters as $index => $filter ) {
			$validation = $this->validate_single_filter( $filter, $index );
			if ( is_wp_error( $validation ) ) {
				return $validation;
			}
		}

		return true;
	}

	/**
	 * Compile validated filters to SQL where conditions.
	 *
	 * @param mixed  $filters Raw filters payload.
	 * @param string $values_table Lead values table name.
	 * @param string $fields_table Lead fields table name.
	 *
	 * @return array{where_conditions: array, prepare_values: array}
	 * @since 1.0.13
	 */
	public function compile_filters_to_sql( $filters, string $values_table, string $fields_table ): array {
		if ( ! is_array( $filters ) || empty( $filters ) ) {
			return array(
				'where_conditions' => array(),
				'prepare_values'   => array(),
			);
		}

		$where_conditions = array();
		$prepare_values   = array();

		foreach ( $filters as $filter ) {
			if ( ! is_array( $filter ) ) {
				continue;
			}

			$target   = isset( $filter['target'] ) ? sanitize_key( $filter['target'] ) : 'lead';
			$field    = isset( $filter['field'] ) ? sanitize_key( $filter['field'] ) : '';
			$operator = $this->normalize_operator( (string) ( $filter['operator'] ?? '' ) );
			$value    = $filter['value'] ?? null;

			if ( ! in_array( $target, self::ALLOWED_TARGETS, true ) || '' === $field || '' === $operator ) {
				continue;
			}

			if ( 'lead' === $target ) {
				if ( ! in_array( $field, self::ALLOWED_LEAD_FIELDS, true ) ) {
					continue;
				}

				$compiled = $this->compile_column_filter_sql( 'l.' . $field, $operator, $value );
				if ( '' !== $compiled['condition'] ) {
					$where_conditions[] = $compiled['condition'];
					$prepare_values     = array_merge( $prepare_values, $compiled['values'] );
				}

				continue;
			}

			$compiled = $this->compile_lead_value_filter_sql( $field, $operator, $value, $values_table, $fields_table );
			if ( '' !== $compiled['condition'] ) {
				$where_conditions[] = $compiled['condition'];
				$prepare_values     = array_merge( $prepare_values, $compiled['values'] );
			}
		}

		return array(
			'where_conditions' => $where_conditions,
			'prepare_values'   => $prepare_values,
		);
	}

	/**
	 * Validate one filter row.
	 *
	 * @param mixed $filter Filter row.
	 * @param int|string $index Row index.
	 *
	 * @return true|WP_Error
	 * @since 1.0.13
	 */
	private function validate_single_filter( $filter, $index ) {
		if ( ! is_array( $filter ) ) {
			return new WP_Error( 'invalid_filter_row', sprintf( __( 'Filter at index %s must be an object.', 'limb-chatbot' ), $index ) );
		}

		$target = isset( $filter['target'] ) ? sanitize_key( $filter['target'] ) : 'lead';
		if ( ! in_array( $target, self::ALLOWED_TARGETS, true ) ) {
			return new WP_Error( 'invalid_filter_target', sprintf( __( 'Invalid filter target at index %s. Allowed targets: lead, lead_value.', 'limb-chatbot' ), $index ) );
		}

		$field = isset( $filter['field'] ) ? sanitize_key( $filter['field'] ) : '';
		if ( '' === $field ) {
			return new WP_Error( 'invalid_filter_field', sprintf( __( 'Filter field is required at index %s.', 'limb-chatbot' ), $index ) );
		}

		if ( 'lead' === $target && ! in_array( $field, self::ALLOWED_LEAD_FIELDS, true ) ) {
			return new WP_Error( 'invalid_lead_filter_field', sprintf( __( 'Invalid lead field at index %s.', 'limb-chatbot' ), $index ) );
		}

		$operator = $this->normalize_operator( (string) ( $filter['operator'] ?? '' ) );
		if ( '' === $operator ) {
			return new WP_Error( 'invalid_filter_operator', sprintf( __( 'Invalid filter operator at index %s.', 'limb-chatbot' ), $index ) );
		}

		if ( ! in_array( $operator, self::VALUELESS_OPERATORS, true ) && ! array_key_exists( 'value', $filter ) ) {
			return new WP_Error( 'missing_filter_value', sprintf( __( 'Filter value is required at index %s for operator %s.', 'limb-chatbot' ), $index, $operator ) );
		}

		return true;
	}

	/**
	 * Normalize operator to canonical form.
	 *
	 * @param string $operator Raw operator.
	 *
	 * @return string
	 * @since 1.0.13
	 */
	private function normalize_operator( string $operator ): string {
		$key = strtolower( trim( str_replace( '-', '_', $operator ) ) );
		$key = preg_replace( '/\s+/', ' ', $key );

		return self::OPERATOR_ALIASES[ $key ] ?? '';
	}

	/**
	 * Build SQL for direct lead-column filtering.
	 *
	 * @param string     $column   SQL column.
	 * @param string     $operator Canonical operator.
	 * @param string|int|float|bool|null $value Value.
	 *
	 * @return array{condition: string, values: array}
	 * @since 1.0.13
	 */
	private function compile_column_filter_sql( string $column, string $operator, $value ): array {
		$value = is_null( $value ) ? '' : (string) $value;
		$like  = '%' . $value . '%';

		switch ( $operator ) {
			case 'is':
				return array( 'condition' => "{$column} = %s", 'values' => array( $value ) );
			case 'is not':
				return array( 'condition' => "{$column} <> %s", 'values' => array( $value ) );
			case 'contains':
				return array( 'condition' => "{$column} LIKE %s", 'values' => array( $like ) );
			case 'does not contain':
				return array( 'condition' => "{$column} NOT LIKE %s", 'values' => array( $like ) );
			case 'starts with':
				return array( 'condition' => "{$column} LIKE %s", 'values' => array( $value . '%' ) );
			case 'ends with':
				return array( 'condition' => "{$column} LIKE %s", 'values' => array( '%' . $value ) );
			case 'is empty':
				return array( 'condition' => "({$column} IS NULL OR {$column} = '')", 'values' => array() );
			case 'is not empty':
				return array( 'condition' => "({$column} IS NOT NULL AND {$column} <> '')", 'values' => array() );
		}

		return array( 'condition' => '', 'values' => array() );
	}

	/**
	 * Build SQL for lead value filtering using EXISTS.
	 *
	 * @param string     $field_key Lead field key.
	 * @param string     $operator Canonical operator.
	 * @param string|int|float|bool|null $value Value.
	 * @param string     $values_table Lead values table.
	 * @param string     $fields_table Lead fields table.
	 *
	 * @return array{condition: string, values: array}
	 * @since 1.0.13
	 */
	private function compile_lead_value_filter_sql( string $field_key, string $operator, $value, string $values_table, string $fields_table ): array {
		$value_expression = "COALESCE(CAST(lvf.value_string AS CHAR), CAST(lvf.value_number AS CHAR), CAST(lvf.value_date AS CHAR), '')";
		$base_sql         = "EXISTS (
			SELECT 1
			FROM {$values_table} lvf
			INNER JOIN {$fields_table} lff ON lff.id = lvf.field_id
			WHERE lvf.lead_id = l.id
			AND lff.field_key = %s
			AND %s
		)";

		$condition_sql = '';
		$values        = array( $field_key );
		$value         = is_null( $value ) ? '' : (string) $value;

		switch ( $operator ) {
			case 'is':
				$condition_sql = "{$value_expression} = %s";
				$values[]      = $value;
				break;
			case 'is not':
				$condition_sql = "{$value_expression} <> %s";
				$values[]      = $value;
				break;
			case 'contains':
				$condition_sql = "{$value_expression} LIKE %s";
				$values[]      = '%' . $value . '%';
				break;
			case 'does not contain':
				$condition_sql = "{$value_expression} NOT LIKE %s";
				$values[]      = '%' . $value . '%';
				break;
			case 'starts with':
				$condition_sql = "{$value_expression} LIKE %s";
				$values[]      = $value . '%';
				break;
			case 'ends with':
				$condition_sql = "{$value_expression} LIKE %s";
				$values[]      = '%' . $value;
				break;
			case 'is empty':
				$condition_sql = "((lvf.value_string IS NULL OR lvf.value_string = '') AND lvf.value_number IS NULL AND (lvf.value_date IS NULL OR lvf.value_date = ''))";
				break;
			case 'is not empty':
				$condition_sql = "((lvf.value_string IS NOT NULL AND lvf.value_string <> '') OR lvf.value_number IS NOT NULL OR (lvf.value_date IS NOT NULL AND lvf.value_date <> ''))";
				break;
		}

		if ( '' === $condition_sql ) {
			return array( 'condition' => '', 'values' => array() );
		}

		return array(
			'condition' => sprintf( $base_sql, '%s', $condition_sql ),
			'values'    => $values,
		);
	}
}
