<?php
/**
 * Members service for managing member records and queries.
 *
 * @package RewardsWP
 */

namespace Awesomemotive\Rewardswp\Services;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use Awesomemotive\Rewardswp\Abstracts\AbstractModelService;
use Awesomemotive\Rewardswp\Database\DatabaseHandler;
use Awesomemotive\Rewardswp\Database\Tables\MembersTable;
use Awesomemotive\Rewardswp\GlobalConfig;
use Awesomemotive\Rewardswp\Models\Member;
use DI\Container;

class MembersService extends AbstractModelService {

	/**
	 * Global config instance for database access.
	 *
	 * @since 1.1.0
	 * @var GlobalConfig
	 */
	private GlobalConfig $global_config;

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 *
	 * @param DatabaseHandler $db_handler    The database handler.
	 * @param MembersTable    $db_table      The members table.
	 * @param Container       $container     The dependency injection container.
	 * @param GlobalConfig    $global_config The global config instance.
	 */
	public function __construct(
		DatabaseHandler $db_handler,
		MembersTable $db_table,
		Container $container,
		GlobalConfig $global_config
	) {
		parent::__construct( $db_handler, $db_table, $container );
		$this->global_config = $global_config;
	}

	/**
	 * Retrieve members based on the given parameters and pagination options.
	 *
	 * Guest status is determined at runtime based on empty user_id,
	 * so we need special handling when filtering by guest status.
	 * Active status also needs special handling to exclude guests.
	 *
	 * @param array $where An associative array of conditions to filter members.
	 *                     See {@see MembersTable::get_columns()} for available parameters.
	 * @param int   $per_page Optional. The number of items per page for pagination. Defaults to 10.
	 * @param int   $page  Optional. The page number for pagination. Defaults to 1.
	 *
	 * @return Member[] Array of Member objects.
	 * @since 1.0.0
	 */
	public function get_members( array $where = [], int $per_page = 10, int $page = 1 ): array {
		// Special handling for guest status since it's determined at runtime
		// based on empty user_id, not the actual status column.
		if ( isset( $where['status'] ) && 'guest' === $where['status'] ) {
			return $this->get_guests( $where, $per_page, $page );
		}

		// Special handling for active status to exclude guests.
		// Guests also have status = 'active', so we need to filter them out.
		if ( isset( $where['status'] ) && 'active' === $where['status'] ) {
			return $this->get_active_members( $where, $per_page, $page );
		}

		return $this->get_objects( Member::class, $where, $per_page, $page );
	}

	/**
	 * Retrieve active members (excluding guests).
	 *
	 * Active members are identified by having status = 'active' AND a valid user_id.
	 * This excludes guests who have status = 'active' but no user_id.
	 *
	 * @since 1.1.0
	 *
	 * @param array $where    Additional filter conditions (status is ignored, but like_query is supported).
	 * @param int   $per_page Items per page.
	 * @param int   $page     Page number.
	 * @return Member[] Array of active Member objects (non-guests).
	 */
	private function get_active_members( array $where = [], int $per_page = 10, int $page = 1 ): array {
		$db     = $this->global_config->get_db();
		$table  = $this->db_table->get_prefixed_table_name();
		$offset = ( $page - 1 ) * $per_page;

		// Build ORDER BY clause from where args.
		$orderby = isset( $where['orderby'] ) ? sanitize_sql_orderby( $where['orderby'] ) : 'created_at';
		$order   = isset( $where['order'] ) && strtoupper( $where['order'] ) === 'ASC' ? 'ASC' : 'DESC';

		// Base WHERE clause for active members (excluding guests).
		$where_clause = "status = 'active' AND user_id IS NOT NULL AND user_id != 0";

		// Build search clause if like_query is provided.
		$search_clause = $this->build_search_clause( $where, $db );
		if ( ! empty( $search_clause ) ) {
			$where_clause .= " AND ({$search_clause})";
		}

		// Query for members with valid user_id AND status = 'active' (non-guest active members).
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name, orderby, and where_clause are safe.
		$query = $db->prepare(
			"SELECT * FROM {$table} WHERE {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d",
			$per_page,
			$offset
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared above.
		$rows = $db->get_results( $query );

		if ( empty( $rows ) ) {
			return [];
		}

		// Hydrate Member objects.
		$members = [];
		foreach ( $rows as $row ) {
			try {
				$member = $this->container->make( Member::class );
				// Convert stdClass to array for hydrate() method.
				$member->hydrate( (array) $row );
				$members[] = $member;
			} catch ( \Exception $e ) {
				continue;
			}
		}

		return $members;
	}

	/**
	 * Retrieve guest members.
	 *
	 * Guest members are identified by having an empty user_id AND status = 'active'.
	 * Members with blocked/excluded status are not considered guests even if they
	 * don't have a WordPress user account.
	 *
	 * @since 1.1.0
	 *
	 * @param array $where    Additional filter conditions (status is ignored, but like_query is supported).
	 * @param int   $per_page Items per page.
	 * @param int   $page     Page number.
	 * @return Member[] Array of guest Member objects.
	 */
	private function get_guests( array $where = [], int $per_page = 10, int $page = 1 ): array {
		$db     = $this->global_config->get_db();
		$table  = $this->db_table->get_prefixed_table_name();
		$offset = ( $page - 1 ) * $per_page;

		// Build ORDER BY clause from where args.
		$orderby = isset( $where['orderby'] ) ? sanitize_sql_orderby( $where['orderby'] ) : 'created_at';
		$order   = isset( $where['order'] ) && strtoupper( $where['order'] ) === 'ASC' ? 'ASC' : 'DESC';

		// Base WHERE clause for guest members.
		$where_clause = "(user_id IS NULL OR user_id = 0) AND status = 'active'";

		// Build search clause if like_query is provided.
		$search_clause = $this->build_search_clause( $where, $db );
		if ( ! empty( $search_clause ) ) {
			$where_clause .= " AND ({$search_clause})";
		}

		// Query for members with no user_id AND status = 'active' (guest members).
		// Members with blocked/excluded status are not considered guests.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name, orderby, and where_clause are safe.
		$query = $db->prepare(
			"SELECT * FROM {$table} WHERE {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d",
			$per_page,
			$offset
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared above.
		$rows = $db->get_results( $query );

		if ( empty( $rows ) ) {
			return [];
		}

		// Hydrate Member objects.
		$members = [];
		foreach ( $rows as $row ) {
			try {
				$member = $this->container->make( Member::class );
				// Convert stdClass to array for hydrate() method.
				$member->hydrate( (array) $row );
				$members[] = $member;
			} catch ( \Exception $e ) {
				continue;
			}
		}

		return $members;
	}

	/**
	 * Build a search clause from like_query parameters.
	 *
	 * @since 1.1.0
	 *
	 * @param array  $where The where array containing like_query.
	 * @param object $db    The database object.
	 * @return string The search SQL clause or empty string.
	 */
	private function build_search_clause( array $where, $db ): string {
		if ( empty( $where['like_query'] ) || empty( $where['like_query']['search_term'] ) ) {
			return '';
		}

		$like_query  = $where['like_query'];
		$search_term = '%' . $db->esc_like( $like_query['search_term'] ) . '%';
		$columns     = $like_query['columns'] ?? [];

		if ( empty( $columns ) ) {
			return '';
		}

		$conditions = [];
		foreach ( $columns as $column ) {
			// Sanitize column name - allow CONCAT expressions.
			$safe_column = preg_match( '/^[a-zA-Z_]+$/', $column )
				? esc_sql( $column )
				: $column; // Allow CONCAT expressions as-is.

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Column is validated above.
			$conditions[] = $db->prepare( "{$safe_column} LIKE %s", $search_term );
		}

		$compare = strtoupper( $like_query['compare'] ?? 'OR' );
		$compare = in_array( $compare, [ 'AND', 'OR' ], true ) ? $compare : 'OR';

		return implode( " {$compare} ", $conditions );
	}

	/**
	 * Get the number of members based on the given parameters.
	 *
	 * Guest status is determined at runtime based on empty user_id,
	 * so we need special handling when counting guests.
	 * Active status also needs special handling to exclude guests.
	 *
	 * @param array $where An associative array of conditions to filter members.
	 *                     See {@see MembersTable::get_columns()} for available parameters.
	 * @return int The number of entries found.
	 * @since 1.0.0
	 */
	public function count_members( array $where = [] ): int {
		// Special handling for guest status since it's determined at runtime
		// based on empty user_id, not the actual status column.
		if ( isset( $where['status'] ) && 'guest' === $where['status'] ) {
			return $this->count_guests( $where );
		}

		// Special handling for active status to exclude guests.
		// Guests also have status = 'active', so we need to filter them out.
		if ( isset( $where['status'] ) && 'active' === $where['status'] ) {
			return $this->count_active_members( $where );
		}

		return $this->count_objects( $where );
	}

	/**
	 * Count guest members.
	 *
	 * Guest members are identified by having an empty user_id AND status = 'active'.
	 * Members with blocked/excluded status are not considered guests.
	 *
	 * @since 1.1.0
	 *
	 * @param array $where Additional filter conditions (like_query is supported).
	 * @return int The number of guest members.
	 */
	private function count_guests( array $where = [] ): int {
		$db    = $this->global_config->get_db();
		$table = $this->db_table->get_prefixed_table_name();

		// Base WHERE clause for guest members.
		$where_clause = "(user_id IS NULL OR user_id = 0) AND status = 'active'";

		// Build search clause if like_query is provided.
		$search_clause = $this->build_search_clause( $where, $db );
		if ( ! empty( $search_clause ) ) {
			$where_clause .= " AND ({$search_clause})";
		}

		// Count members with no user_id AND status = 'active' (guest members).
		// Members with blocked/excluded status are not considered guests.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and where_clause are safe.
		$count = $db->get_var( "SELECT COUNT(*) FROM {$table} WHERE {$where_clause}" );

		return (int) $count;
	}

	/**
	 * Count active members (excluding guests).
	 *
	 * Active members are identified by having status = 'active' AND a valid user_id.
	 * This excludes guests who have status = 'active' but no user_id.
	 *
	 * @since 1.1.0
	 *
	 * @param array $where Additional filter conditions (like_query is supported).
	 * @return int The number of active members (non-guests).
	 */
	private function count_active_members( array $where = [] ): int {
		$db    = $this->global_config->get_db();
		$table = $this->db_table->get_prefixed_table_name();

		// Base WHERE clause for active members (excluding guests).
		$where_clause = "status = 'active' AND user_id IS NOT NULL AND user_id != 0";

		// Build search clause if like_query is provided.
		$search_clause = $this->build_search_clause( $where, $db );
		if ( ! empty( $search_clause ) ) {
			$where_clause .= " AND ({$search_clause})";
		}

		// Count members with valid user_id AND status = 'active' (non-guest active members).
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and where_clause are safe.
		$count = $db->get_var( "SELECT COUNT(*) FROM {$table} WHERE {$where_clause}" );

		return (int) $count;
	}
}
