<?php

namespace Limb_Chatbot\Includes\Repositories;

use Exception;
use Limb_Chatbot\Includes\Data_Objects\Chatbot_User;
use Limb_Chatbot\Includes\Data_Objects\Chatbot_User_Meta;
use Limb_Chatbot\Includes\Data_Objects\Lead;
use Limb_Chatbot\Includes\Data_Objects\Message;
use Limb_Chatbot\Includes\Data_Objects\Ip_Block;
use Limb_Chatbot\Includes\Services\Data_Object_Collection;

/**
 * Repository class for managing chatbot users.
 *
 * @since 1.0.0
 */
class Chatbot_User_Repository {

	/**
	 * Creates a new Chatbot_User record.
	 *
	 * @param  int|null  $wp_user_id  WordPress user ID associated with the chatbot user.
	 * @param  string  $ip_address  IP address of the user.
	 * @param  string  $device_uuid  Unique identifier for the user's device.
	 * @param  string|null  $type  Type of chatbot user (e.g., guest, registered).
	 *
	 * @return Chatbot_User The created chatbot user instance.
	 * @throws Exception
	 * @since 1.0.0
	 */
	public function create( $wp_user_id, $ip_address, $device_uuid, $type, $status = Chatbot_User::STATUS_ACTIVE ) {
		return Chatbot_User::create( [
			'wp_user_id'  => $wp_user_id,
			'ip'          => $ip_address,
			'device_uuid' => $device_uuid,
			'type'        => $type,
			'status'      => $status
		] );
	}

	/**
	 * Retrieves a collection of chatbot users based on given parameters.
	 *
	 * Supports filtering, search, ordering, and pagination.
	 * Parameters are expected to be already sanitized and validated by the controller.
	 *
	 * @param  array  $params  Query parameters (already sanitized and validated).
	 *
	 * @return Data_Object_Collection Collection of Chatbot_User objects.
	 * @since 1.0.0
	 */
	public function get_items( array $params = [] ): Data_Object_Collection {
		$where = [];

		// Filter by type
		if ( isset( $params['type'] ) ) {
			$where['type'] = $params['type'];
		}

		// Filter by wp_user_id
		if ( isset( $params['wp_user_id'] ) ) {
			$where['wp_user_id'] = $params['wp_user_id'];
		}

		// Filter by uuid
		if ( ! empty( $params['uuid'] ) ) {
			$where['uuid'] = $params['uuid'];
		}

		// Filter by device_uuid
		if ( ! empty( $params['device_uuid'] ) ) {
			$where['device_uuid'] = $params['device_uuid'];
		}

		// Filter by geolocation (country/city) via user metas first.
		$country = isset( $params['country'] ) ? trim( (string) $params['country'] ) : '';
		$city    = isset( $params['city'] ) ? trim( (string) $params['city'] ) : '';
		if ( $country !== '' || $city !== '' ) {
			$matching_user_ids = $this->get_user_ids_by_location( $country, $city );
			if ( empty( $matching_user_ids ) ) {
				return new Data_Object_Collection();
			}
			$where['id'] = $matching_user_ids;
		}

		// Search functionality
		if ( ! empty( $params['search'] ) && ! empty( $params['search_fields'] ) ) {
			$search_term   = $params['search'];
			$search_fields = $params['search_fields'];

			if ( is_array( $search_fields ) && ! empty( $search_fields ) ) {
				// Use the first search field for the query
				// More complex OR conditions would require custom SQL
				$first_field                 = $search_fields[0];
				$where["{$first_field}LIKE"] = "%{$search_term}%";
			}
		}

		// Date range filters
		if ( ! empty( $params['start_date'] ) || ! empty( $params['created_at>='] ) ) {
			$date                  = ! empty( $params['start_date'] ) ? $params['start_date'] : $params['created_at>='];
			$where['created_at>='] = $date;
		}

		if ( ! empty( $params['end_date'] ) || ! empty( $params['created_at<='] ) ) {
			$date                  = ! empty( $params['end_date'] ) ? $params['end_date'] : $params['created_at<='];
			$where['created_at<='] = $date;
		}

		// Filter by lifecycle status IDs resolved via SQL joins.
		if ( isset( $params['status'] ) ) {
			$status_user_ids = $this->get_user_ids_by_status_joins( (int) $params['status'] );
			if ( empty( $status_user_ids ) ) {
				return new Data_Object_Collection();
			}
			$where['id'] = isset( $where['id'] ) ? array_values( array_intersect( (array) $where['id'], $status_user_ids ) ) : $status_user_ids;
			if ( empty( $where['id'] ) ) {
				return new Data_Object_Collection();
			}
		}

		// Online users: updated_at within the last 2 minutes (UTC, matching stored timestamps).
		if ( isset( $params['online'] ) && (int) $params['online'] === 1 ) {
			$where['updated_at>='] = gmdate( 'Y-m-d H:i:s', time() - 120 );
			if ( ! empty( $params['exclude_chatbot_user_id'] ) ) {
				$where['id!='] = (int) $params['exclude_chatbot_user_id'];
			}
		}

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

		// Handle pagination (already validated by controller)
		$per_page = $params['per_page'] ?? 10;
		$page     = $params['page'] ?? 1;

		// Use Chatbot_User::where() with pagination and ordering
		$collection = Chatbot_User::where( $where, $per_page, $page, $order_by, $order );
		$total      = Chatbot_User::count( $where );
		$collection->set_total( $total );

		return $collection;
	}

	/**
	 * Resolve user IDs by matching country/city against user metas.
	 *
	 * @param  string  $country  Country value to match.
	 * @param  string  $city  City value to match.
	 *
	 * @return int[] Matching chatbot user IDs.
	 * @since 1.0.0
	 */
	private function get_user_ids_by_location( string $country = '', string $city = '' ): array {
		if ( $country === '' && $city === '' ) {
			return array();
		}

		$where = array();
		if ( $city !== '' ) {
			$where['meta_key']       = array( 'geolocation' );
			$where['meta_valueLIKE'] = '%' . $city . '%';
		} elseif ( $country !== '' ) {
			$where['meta_key']       = array( 'geolocation', 'country' );
			$where['meta_valueLIKE'] = '%' . $country . '%';
		}

		$metas = Chatbot_User_Meta::where( $where );
		if ( $metas->is_empty() ) {
			return array();
		}

		return array_values( array_unique( $metas->pluck('chatbot_user_id') ) );
	}

	/**
	 * Resolve user IDs by lifecycle status using direct SQL joins.
	 *
	 * STATUS_CONVERTED (3): users with messages + leads, excluding blocked.
	 * STATUS_BLOCKED (4): users that have at least one IP block row.
	 * STATUS_INTERACTED (2): users with messages and no leads, excluding blocked.
	 * STATUS_NEW (1): users with no messages, excluding blocked.
	 *
	 * @param  int  $status  Lifecycle status.
	 *
	 * @return int[] Matching chatbot user IDs.
	 * @since 1.0.0
	 */
	private function get_user_ids_by_status_joins( int $status ): array {
		// Lifecycle status map:
		// 1 = new, 2 = interacted, 3 = converted, 4 = blocked.
		if ( ! in_array( $status, [ 1, 2, 3, 4 ], true ) ) {
			return array();
		}

		global $wpdb;

		$users_table    = $wpdb->prefix . Chatbot_User::TABLE_NAME;
		$messages_table = $wpdb->prefix . Message::TABLE_NAME;
		$leads_table    = $wpdb->prefix . Lead::TABLE_NAME;
		$blocks_table   = $wpdb->prefix . Ip_Block::TABLE_NAME;

		if ( 4 === $status ) {
			$sql = "SELECT DISTINCT u.id
				FROM {$users_table} u
				INNER JOIN {$blocks_table} b ON b.chatbot_user_id = u.id";
		} elseif ( 3 === $status ) {
			$sql = "SELECT DISTINCT u.id
				FROM {$users_table} u
				INNER JOIN {$messages_table} m ON m.user_id = u.id
				INNER JOIN {$leads_table} l ON l.chatbot_user_uuid = u.uuid
				LEFT JOIN {$blocks_table} b ON b.chatbot_user_id = u.id
				WHERE b.id IS NULL";
		} elseif ( 2 === $status ) {
			$sql = "SELECT DISTINCT u.id
				FROM {$users_table} u
				INNER JOIN {$messages_table} m ON m.user_id = u.id
				LEFT JOIN {$leads_table} l ON l.chatbot_user_uuid = u.uuid
				LEFT JOIN {$blocks_table} b ON b.chatbot_user_id = u.id
				WHERE l.id IS NULL
				AND b.id IS NULL";
		} else {
			$sql = "SELECT DISTINCT u.id
				FROM {$users_table} u
				LEFT JOIN {$messages_table} m ON m.user_id = u.id
				LEFT JOIN {$blocks_table} b ON b.chatbot_user_id = u.id
				WHERE m.id IS NULL
				AND b.id IS NULL";
		}

		$user_ids = $wpdb->get_col( $sql );

		return array_values( array_unique( array_map( 'intval', array_filter( $user_ids ) ) ) );
	}

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