<?php

namespace Limb_Chatbot\Includes\Repositories;

use Limb_Chatbot\Includes\Data_Objects\Lead;
use Limb_Chatbot\Includes\Data_Objects\Lead_Field;
use Limb_Chatbot\Includes\Data_Objects\Lead_Value;
use Limb_Chatbot\Includes\Exceptions\Error_Codes;
use Limb_Chatbot\Includes\Exceptions\Exception;
use Limb_Chatbot\Includes\Services\Collection;
use Limb_Chatbot\Includes\Services\Data_Object_Collection;
use Limb_Chatbot\Includes\Services\Helper;
use Limb_Chatbot\Includes\Services\Lead_Filters_Service;

/**
 * Repository class for managing Lead records.
 *
 * Provides methods for retrieving leads with advanced filtering and search capabilities.
 *
 * @package Limb_Chatbot\Includes\Repositories
 * @since 1.0.13
 */
class Lead_Repository {

	/**
	 * WordPress database instance.
	 *
	 * @var \wpdb
	 * @since 1.0.13
	 */
	protected $wpdb;
	/**
	 * Structured lead filters service.
	 *
	 * @var Lead_Filters_Service
	 * @since 1.0.13
	 */
	protected Lead_Filters_Service $lead_filters_service;

	/**
	 * Lead_Repository constructor.
	 *
	 * @since 1.0.13
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
		$this->lead_filters_service = new Lead_Filters_Service();
	}

	/**
	 * Retrieves a collection of leads based on given parameters.
	 *
	 * Supports advanced filtering, search, field value filtering, and pagination.
	 *
	 * @param  array  $params  Query parameters.
	 *
	 * @return Collection Collection of Lead objects.
	 * @since 1.0.13
	 */
	public function get_items( array $params = [] ): Collection {
		$table_prefix = $this->wpdb->prefix;
		$leads_table  = $table_prefix . 'lbaic_leads';
		$values_table = $table_prefix . 'lbaic_lead_values';
		$fields_table = $table_prefix . 'lbaic_lead_fields';

		// Build WHERE conditions
		$where_conditions = [];
		$prepare_values   = [];

		// Standard lead filters
		if ( ! empty( $params['source_type'] ) ) {
			$where_conditions[] = 'l.source_type = %s';
			$prepare_values[]   = sanitize_text_field( $params['source_type'] );
		}

		if ( ! empty( $params['status'] ) ) {
			$where_conditions[] = 'l.status = %s';
			$prepare_values[]   = sanitize_text_field( $params['status'] );
		}

		if ( isset( $params['is_qualified'] ) ) {
			$where_conditions[] = 'l.is_qualified = %d';
			$prepare_values[]   = absint( $params['is_qualified'] );
		}

		if ( ! empty( $params['source'] ) ) {
			$where_conditions[] = 'l.source = %s';
			$prepare_values[]   = sanitize_text_field( $params['source'] );
		}

		if ( ! empty( $params['chatbot_user_uuid'] ) ) {
			$where_conditions[] = 'l.chatbot_user_uuid = %s';
			$prepare_values[]   = sanitize_text_field( $params['chatbot_user_uuid'] );
		}

		if ( ! empty( $params['chatbot_uuid'] ) ) {
			$where_conditions[] = 'l.chatbot_uuid = %s';
			$prepare_values[]   = sanitize_text_field( $params['chatbot_uuid'] );
		}

		if ( ! empty( $params['start_date'] ) || ! empty( $params['created_at>='] ) ) {
			$date = ! empty( $params['start_date'] ) ? $params['start_date'] : $params['created_at>='];
			$where_conditions[] = 'l.created_at >= %s';
			$prepare_values[]   = sanitize_text_field( $date );
		}

		if ( ! empty( $params['end_date'] ) || ! empty( $params['created_at<='] ) ) {
			$date = ! empty( $params['end_date'] ) ? $params['end_date'] : $params['created_at<='];
			$where_conditions[] = 'l.created_at <= %s';
			$prepare_values[]   = sanitize_text_field( $date );
		}

		// Search on lead fields
		if ( ! empty( $params['search'] ) && ! empty( $params['search_fields'] ) ) {
			$search_term      = sanitize_text_field( $params['search'] );
			$search_conditions = [];
			foreach ( $params['search_fields'] as $field ) {
				$field = sanitize_key( $field );
				if ( in_array( $field, [ 'source_title', 'source', 'chatbot_user_uuid', 'chatbot_uuid' ], true ) ) {
					$search_conditions[] = "l.{$field} LIKE %s";
					$prepare_values[]    = "%{$search_term}%";
				}
			}
			if ( ! empty( $search_conditions ) ) {
				$where_conditions[] = '(' . implode( ' OR ', $search_conditions ) . ')';
			}
		}

		// Handle general value search (searches across all value columns).
		if ( ! empty( $params['value_search'] ) ) {
			$value_search       = sanitize_text_field( $params['value_search'] );
			$search_pattern     = '%' . $this->wpdb->esc_like( $value_search ) . '%';
			$where_conditions[] = "EXISTS (
				SELECT 1
				FROM {$values_table} lv
				WHERE lv.lead_id = l.id
				AND (lv.value_string LIKE %s OR CAST(lv.value_number AS CHAR) LIKE %s OR lv.value_date LIKE %s)
			)";
			$prepare_values[]   = $search_pattern;
			$prepare_values[]   = $search_pattern;
			$prepare_values[]   = $search_pattern;
		}

		// Structured filters (only lead and lead_value targets).
		$compiled_filters = $this->lead_filters_service->compile_filters_to_sql(
			$params['filters'] ?? null,
			$values_table,
			$fields_table
		);
		if ( ! empty( $compiled_filters['where_conditions'] ) ) {
			$where_conditions = array_merge( $where_conditions, $compiled_filters['where_conditions'] );
			$prepare_values   = array_merge( $prepare_values, $compiled_filters['prepare_values'] );
		}

		// Handle ordering
		$order_by = $params['orderby'] ?? 'created_at';
		$order    = strtoupper( $params['order'] ?? 'DESC' );

		// Validate orderby field
		$allowed_orderby = [ 'id', 'created_at', 'source_type', 'status', 'is_qualified', 'source_title' ];
		if ( ! in_array( $order_by, $allowed_orderby, true ) ) {
			$order_by = 'created_at';
		}

		// Validate order direction
		if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
			$order = 'DESC';
		}

		// Handle pagination
		$page     = isset( $params['page'] ) ? absint( $params['page'] ) : 1;
		$per_page = isset( $params['per_page'] ) ? absint( $params['per_page'] ) : 10;
		$offset   = ( $page - 1 ) * $per_page;

		// Build SQL query
		$where_clause = ! empty( $where_conditions ) ? 'WHERE ' . implode( ' AND ', $where_conditions ) : '';
		$distinct     = '';

		$sql = "SELECT {$distinct} l.*
			FROM {$leads_table} l
			{$where_clause}
			ORDER BY l.{$order_by} {$order}
			LIMIT %d, %d";

		$prepare_values[] = $offset;
		$prepare_values[] = $per_page;

		// Execute query
		$results = $this->wpdb->get_results(
			$this->wpdb->prepare( $sql, $prepare_values ),
			ARRAY_A
		);

		// Convert to Lead objects
		$leads = [];
		if ( $results ) {
			foreach ( $results as $result ) {
				$lead = Lead::make( $result );
				if ( $lead instanceof Lead ) {
					$leads[] = $lead;
				}
			}
		}

		return new Data_Object_Collection( $leads );
	}

	/**
	 * Retrieves a single lead by ID.
	 *
	 * @param  int  $id  Lead ID.
	 *
	 * @return Lead|null The Lead object or null if not found.
	 * @since 1.0.13
	 */
	public function get_item( int $id ): ?Lead {
		return Lead::find( $id );
	}

	/**
	 * Batch delete leads and all related lead values.
	 *
	 * @param  int[]  $ids  Lead IDs to delete.
	 *
	 * @return bool True when delete queries execute successfully.
	 * @throws Exception
	 * @since 1.0.18
	 */
	public function batch_delete( array $ids ): bool {
		$ids = array_values( array_filter( array_unique( array_map( 'absint', $ids ) ) ) );
		if ( empty( $ids ) ) {
			throw new Exception(
				Error_Codes::VALIDATION_INVALID_VALUE,
				__( 'At least one valid lead id is required.', 'limb-chatbot' )
			);
		}

		$values_deleted = Lead_Value::delete( [ 'lead_id' => $ids ] );
		$leads_deleted  = Lead::delete( [ 'id' => $ids ] );

		return $values_deleted !== false && $leads_deleted !== false;
	}

	/**
	 * Updates an existing lead and its typed lead values.
	 *
	 * Supported payload keys:
	 * - status
	 * - is_qualified
	 * - metadata.notes
	 * - lead_values[field_key] => typed value
	 *
	 * @param  int  $id  Lead ID.
	 * @param  array  $data  Update payload.
	 *
	 * @return Lead
	 * @throws Exception
	 * @since 1.0.18
	 */
	public function update( int $id, array $data ): Lead {
		$lead = $this->get_item( $id );
		if ( ! $lead instanceof Lead ) {
			throw new Exception( Error_Codes::NOT_FOUND, __( 'Lead not found.', 'limb-chatbot' ), null, 404 );
		}

		$lead_value_updates = $this->compile_lead_value_updates( $lead, $data );
		$this->apply_lead_updates( $lead, $data );
		$lead->save();

		if ( ! empty( $lead_value_updates ) ) {
			$this->apply_lead_value_updates( $lead, $lead_value_updates );
		}

		return $this->get_item( $lead->get_id() ) ?? $lead;
	}

	/**
	 * Validate and apply direct Lead fields updates.
	 *
	 * @param  Lead  $lead
	 * @param  array  $data
	 *
	 * @return void
	 * @throws Exception
	 * @since 1.0.18
	 */
	private function apply_lead_updates( Lead $lead, array $data ): void {
		if ( array_key_exists( 'status', $data ) ) {
			$status = sanitize_text_field( (string) $data['status'] );
			if ( ! in_array( $status, [ Lead::STATUS_NEW, Lead::STATUS_ARCHIVED ], true ) ) {
				throw new Exception( Error_Codes::VALIDATION_INVALID_VALUE,
					__( 'Invalid status value.', 'limb-chatbot' ) );
			}
			$lead->set_status( $status );
		}

		if ( array_key_exists( 'is_qualified', $data ) ) {
			$lead->set_is_qualified( $this->normalize_is_qualified( $data['is_qualified'] ) );
		}

		if ( array_key_exists( 'metadata', $data ) ) {
			if ( ! is_array( $data['metadata'] ) ) {
				throw new Exception( Error_Codes::VALIDATION_INVALID_VALUE,
					__( 'metadata must be an object.', 'limb-chatbot' ) );
			}
			$metadata = $lead->get_metadata() ?? [];
			if ( array_key_exists( 'notes', $data['metadata'] ) ) {
				$metadata['notes'] = sanitize_textarea_field( (string) $data['metadata']['notes'] );
			}
			$lead->set_metadata( $metadata );
		}
	}

	/**
	 * Normalize is_qualified to 0 or 1.
	 *
	 * @param  mixed  $is_qualified
	 *
	 * @return int
	 * @throws Exception
	 * @since 1.0.18
	 */
	private function normalize_is_qualified( $is_qualified ): int {
		if ( is_bool( $is_qualified ) ) {
			$is_qualified = $is_qualified ? 1 : 0;
		} elseif ( is_numeric( $is_qualified ) ) {
			$is_qualified = (int) $is_qualified;
		} else {
			throw new Exception(
				Error_Codes::VALIDATION_INVALID_VALUE,
				__( 'is_qualified must be a boolean or 0/1 integer.', 'limb-chatbot' )
			);
		}

		if ( ! in_array( $is_qualified, [ 0, 1 ], true ) ) {
			throw new Exception(
				Error_Codes::VALIDATION_INVALID_VALUE,
				__( 'is_qualified must be either 0 or 1.', 'limb-chatbot' )
			);
		}

		return $is_qualified;
	}

	/**
	 * Validate and compile lead value updates before saving any value row.
	 *
	 * @param  Lead  $lead
	 * @param  array  $data
	 *
	 * @return array<int, array{field: Lead_Field, typed_value:mixed}>
	 * @throws Exception
	 * @since 1.0.18
	 */
	private function compile_lead_value_updates( Lead $lead, array $data ): array {
		if ( ! array_key_exists( 'lead_values', $data ) ) {
			return [];
		}

		if ( ! is_array( $data['lead_values'] ) ) {
			throw new Exception(
				Error_Codes::VALIDATION_INVALID_VALUE,
				__( 'lead_values must be an object map of { field_key: value }.', 'limb-chatbot' )
			);
		}

		$updates = [];
		foreach ( $data['lead_values'] as $field_key => $raw_value ) {
			$field_key = sanitize_key( (string) $field_key );
			if ( empty( $field_key ) ) {
				throw new Exception( Error_Codes::VALIDATION_INVALID_VALUE,
					__( 'Lead value field key is invalid.', 'limb-chatbot' ) );
			}

			$lead_field = Lead_Field::find_by_key( $field_key );
			if ( ! $lead_field instanceof Lead_Field ) {
				throw new Exception(
					Error_Codes::VALIDATION_INVALID_VALUE,
					sprintf(
						/* translators: %s: lead field key. */
						__( 'Unknown lead field key: %s', 'limb-chatbot' ),
						$field_key
					)
				);
			}

			$typed_value = $raw_value === null ? null : Helper::validate_value_type( $raw_value, $lead_field->get_data_type() );
			if ( $raw_value !== null && $typed_value === null ) {
				throw new Exception(
					Error_Codes::VALIDATION_INVALID_VALUE,
					sprintf(
						/* translators: %s: lead field key. */
						__( 'Invalid value for lead field: %s', 'limb-chatbot' ),
						$field_key
					)
				);
			}

			$updates[] = [
				'field'       => $lead_field,
				'typed_value' => $typed_value,
			];
		}

		return $updates;
	}

	/**
	 * Persist typed lead value updates.
	 *
	 * @param  Lead  $lead
	 * @param  array<int, array{field: Lead_Field, typed_value:mixed}>  $updates
	 *
	 * @return void
	 * @throws \Exception
	 * @since 1.0.18
	 */
	private function apply_lead_value_updates( Lead $lead, array $updates ): void {
		$values_table = $this->wpdb->prefix . 'lbaic_lead_values';

		foreach ( $updates as $update ) {
			/** @var Lead_Field $lead_field */
			$lead_field = $update['field'];
			$typed_value = $update['typed_value'];

			$existing = Lead_Value::where( [
				'lead_id'  => $lead->get_id(),
				'field_id' => $lead_field->id,
			] )->first();
			$lead_value = $existing instanceof Lead_Value ? $existing : new Lead_Value();

			if ( ! $existing instanceof Lead_Value ) {
				$lead_value->set_lead_id( $lead->get_id() );
				$lead_value->set_field_id( $lead_field->id );
			}

			if ( $typed_value === null ) {
				// Explicitly persist SQL NULL for cleared values.
				// wpdb->update() does not reliably store PHP null as SQL NULL.
				if ( $existing instanceof Lead_Value && ! empty( $lead_value->id ) ) {
					$this->wpdb->query(
						$this->wpdb->prepare(
							"UPDATE {$values_table}
							 SET value_string = NULL, value_number = NULL, value_date = NULL
							 WHERE id = %d",
							(int) $lead_value->id
						)
					);
				} else {
					$this->wpdb->insert(
						$values_table,
						[
							'lead_id'       => (int) $lead->get_id(),
							'field_id'      => (int) $lead_field->id,
							'value_string'  => null,
							'value_number'  => null,
							'value_date'    => null,
						],
						[ '%d', '%d', null, null, null ]
					);
				}
				continue;
			}

			$lead_value->set_value( $lead_field->get_data_type(), $typed_value );
			$lead_value->save();
		}
	}
}
