<?php

namespace Limb_Chatbot\Includes\Repositories;

use Limb_Chatbot\Includes\Data_Objects\Lead;
use Limb_Chatbot\Includes\Services\Collection;
use Limb_Chatbot\Includes\Services\Data_Object_Collection;

/**
 * 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;

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

	/**
	 * 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   = [];
		$needs_join       = false;

		// 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 multiple field value filters (lead_values[field_key]=value)
		$field_value_filters = [];
		foreach ( $params as $key => $value ) {
			if ( strpos( $key, 'lead_values[' ) === 0 && strpos( $key, ']' ) !== false ) {
				// Extract field_key from lead_values[field_key]
				preg_match( '/lead_values\[(.+?)\]/', $key, $matches );
				if ( ! empty( $matches[1] ) ) {
					$field_key = sanitize_key( $matches[1] );
					$field_value = sanitize_text_field( $value );
					$field_value_filters[] = [
						'field_key' => $field_key,
						'value'     => $field_value,
					];
					$needs_join = true;
				}
			}
		}

		// Handle general value search (searches across all value columns)
		if ( ! empty( $params['value_search'] ) ) {
			$value_search = sanitize_text_field( $params['value_search'] );
			$needs_join   = true;
		}

		// Build JOIN and additional WHERE conditions for field value filters
		$join_clause = '';
		if ( $needs_join ) {
			// For multiple field filters, we need multiple JOINs (one per field)
			// For general search or single filter, one JOIN is enough
			if ( ! empty( $field_value_filters ) && count( $field_value_filters ) > 1 ) {
				// Multiple field filters - use multiple JOINs with conditions in ON clause
				$join_parts = [];
				$join_index = 0;
				
				foreach ( $field_value_filters as $filter ) {
					$join_index++;
					$lv_alias = "lv{$join_index}";
					$lf_alias = "lf{$join_index}";
					
					$join_parts[] = "INNER JOIN {$values_table} {$lv_alias} ON {$lv_alias}.lead_id = l.id
						INNER JOIN {$fields_table} {$lf_alias} ON {$lf_alias}.id = {$lv_alias}.field_id
						AND {$lf_alias}.field_key = %s
						AND {$lv_alias}.value_string = %s";
					$prepare_values[] = $filter['field_key'];
					$prepare_values[] = $filter['value'];
				}
				
				// If general search is also present, add another JOIN for it
				if ( ! empty( $value_search ) ) {
					$search_index = $join_index + 1;
					$lv_search = "lv{$search_index}";
					$join_parts[] = "INNER JOIN {$values_table} {$lv_search} ON {$lv_search}.lead_id = l.id
						AND ({$lv_search}.value_string LIKE %s OR {$lv_search}.value_number LIKE %s OR {$lv_search}.value_date LIKE %s)";
					$search_pattern = '%' . $this->wpdb->esc_like( $value_search ) . '%';
					$prepare_values[] = $search_pattern;
					$prepare_values[] = $search_pattern;
					$prepare_values[] = $search_pattern;
				}
				
				$join_clause = implode( "\n\t\t\t", $join_parts );
			} else {
				// Single field filter or general search only - use one JOIN
				$join_clause = "INNER JOIN {$values_table} lv ON lv.lead_id = l.id
					INNER JOIN {$fields_table} lf ON lf.id = lv.field_id";

				// Single field value filter
				if ( ! empty( $field_value_filters ) && count( $field_value_filters ) === 1 ) {
					$filter = $field_value_filters[0];
					$where_conditions[] = 'lf.field_key = %s AND lv.value_string = %s';
					$prepare_values[]   = $filter['field_key'];
					$prepare_values[]   = $filter['value'];
				}

				// General value search (searches all value columns)
				if ( ! empty( $value_search ) ) {
					$where_conditions[] = '(lv.value_string LIKE %s OR lv.value_number LIKE %s OR lv.value_date LIKE %s)';
					$search_pattern     = '%' . $this->wpdb->esc_like( $value_search ) . '%';
					$prepare_values[]   = $search_pattern;
					$prepare_values[]   = $search_pattern;
					$prepare_values[]   = $search_pattern;
				}
			}
		}

		// 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     = $needs_join ? 'DISTINCT' : '';

		$sql = "SELECT {$distinct} l.*
			FROM {$leads_table} l
			{$join_clause}
			{$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 );
	}
}
