<?php

namespace Limb_Chatbot\Includes\Repositories;

use Limb_Chatbot\Includes\Data_Objects\Dataset;
use Limb_Chatbot\Includes\Data_Objects\Dataset_Entry;
use Limb_Chatbot\Includes\Data_Objects\Setting;
use Limb_Chatbot\Includes\Data_Objects\Vector;
use Limb_Chatbot\Includes\Database_Strategies\WPDB;
use Limb_Chatbot\Includes\Services\Collection;

/**
 * Repository responsible for retrieving grouped dataset information.
 *
 * Groups datasets by source type and source sub type and enriches
 * them with additional metadata such as indexing status, autosync,
 * and content configuration.
 */
class Dataset_Group_Repository {
	/**
	 * Dataset meta key for group role access.
	 */
	private const META_USER_ROLES = 'user_roles';
	/**
	 * Plugin-native role key: everyone.
	 */
	private const ROLE_EVERYONE = 'everyone';
	/**
	 * Plugin-native role key: contacts.
	 */
	private const ROLE_CONTACTS = 'contact';
	/**
	 * Plugin-native role key: visitors.
	 */
	private const ROLE_VISITORS = 'visitor';

	/**
	 * Bulk update dataset and vector status for one group.
	 *
	 * Uses direct SQL updates instead of per-record save loops.
	 *
	 * @param  string  $source_type
	 * @param  string|null  $source_sub_type
	 * @param  bool  $enabled
	 *
	 * @return array{
	 *   enabled:int,
	 *   dataset_status:int,
	 *   vector_status:int,
	 *   dataset_ids:array<int>,
	 *   vector_ids:array<int>,
	 *   vector_uuids:array<string>,
	 *   updated_datasets:int,
	 *   updated_vectors:int
	 * }
	 */
	public function bulk_update_group_status( string $source_type, ?string $source_sub_type, bool $enabled ): array {
		$wpdb = WPDB::instance()->get_wpdb();

		$datasets_table       = $wpdb->prefix . Dataset::TABLE_NAME;
		$dataset_entries_table = $wpdb->prefix . Dataset_Entry::TABLE_NAME;
		$vectors_table         = $wpdb->prefix . Vector::TABLE_NAME;

		$dataset_status = $enabled ? Dataset::STATUS_INDEXED : Dataset::STATUS_INACTIVE;
		$vector_status  = $enabled ? Vector::STATUS_ACTIVE : Vector::STATUS_INACTIVE;
		$updated_at     = current_time( 'mysql' );

		[ $where_sql, $where_values ] = $this->build_group_scope_where_sql(
			$source_type,
			$source_sub_type
		);

		// Never bulk-update datasets that are in intermediate/unsafe states.
		// These statuses should never be "participated" in dataset/group updating.
		$excluded_dataset_statuses = [
			Dataset::STATUS_PENDING,
			Dataset::STATUS_GENERATING,
			Dataset::STATUS_GENERATED,
			Dataset::STATUS_INDEXING,
			Dataset::STATUS_FAILED,
		];
		$excluded_placeholders = implode( ', ', array_fill( 0, count( $excluded_dataset_statuses ), '%d' ) );
		$where_sql             .= ' AND d.status NOT IN (' . $excluded_placeholders . ')';
		$where_values          = array_merge( $where_values, $excluded_dataset_statuses );

		$dataset_ids_sql = "
			SELECT d.id
			FROM {$datasets_table} d
			WHERE {$where_sql}
		";
		$dataset_ids     = $wpdb->get_col( $wpdb->prepare( $dataset_ids_sql, $where_values ) );
		$dataset_ids     = array_map( 'intval', $dataset_ids ?? [] );

		$vector_rows_sql = "
			SELECT DISTINCT v.id, v.uuid
			FROM {$vectors_table} v
			INNER JOIN {$dataset_entries_table} de ON de.id = v.dataset_entry_id
			INNER JOIN {$datasets_table} d ON d.id = de.dataset_id
			WHERE {$where_sql}
		";

		$vector_rows     = $wpdb->get_results(
			$wpdb->prepare( $vector_rows_sql, $where_values ),
			ARRAY_A
		);

		$vector_ids   = [];
		$vector_uuids = [];
		foreach ( $vector_rows ?? [] as $row ) {
			$vector_ids[]   = (int) $row['id'];
			$vector_uuids[] = (string) $row['uuid'];
		}

		$update_datasets_sql = "
			UPDATE {$datasets_table} d
			SET d.status = %d, d.updated_at = %s
			WHERE {$where_sql}
		";
		$updated_datasets    = $wpdb->query(
			$wpdb->prepare(
				$update_datasets_sql,
				array_merge( [ $dataset_status, $updated_at ], $where_values )
			)
		);

		$update_vectors_sql = "
			UPDATE {$vectors_table} v
			INNER JOIN {$dataset_entries_table} de ON de.id = v.dataset_entry_id
			INNER JOIN {$datasets_table} d ON d.id = de.dataset_id
			SET v.status = %d, v.updated_at = %s
			WHERE {$where_sql}
		";
		$updated_vectors    = $wpdb->query(
			$wpdb->prepare(
				$update_vectors_sql,
				array_merge( [ $vector_status, $updated_at ], $where_values )
			)
		);

		return [
			'enabled'          => $enabled ? 1 : 0,
			'dataset_status'   => $dataset_status,
			'vector_status'    => $vector_status,
			'dataset_ids'      => $dataset_ids,
			'vector_ids'       => array_values( array_unique( $vector_ids ) ),
			'vector_uuids'     => array_values( array_unique( $vector_uuids ) ),
			'updated_datasets' => (int) ( $updated_datasets ?: 0 ),
			'updated_vectors'  => (int) ( $updated_vectors ?: 0 ),
		];
	}

	/**
	 * Retrieve dataset groups.
	 *
	 * @param  array  $params  {
	 *     Optional query filters.
	 *
	 * @type string|null $source_type
	 * @type string|null $source_sub_type
	 * }
	 *
	 * @return Collection
	 */
	public function get_groups( array $params ): Collection {
		$wpdb  = WPDB::instance()->get_wpdb();
		$table = $wpdb->prefix . Dataset::TABLE_NAME;

		[ $where_sql, $values ] = $this->build_where_clause( $params );

		$groups = $this->fetch_group_aggregates( $table, $where_sql, $values );

		$this->attach_dataset_ids( $groups, $table, $where_sql, $values );

		$groups = $this->normalize_groups( $groups );

		return new Collection( $groups );
	}

	/**
	 * Build SQL WHERE clause.
	 *
	 * @param  array  $params
	 *
	 * @return array{0:string,1:array}
	 */
	private function build_where_clause( array $params ): array {
		$conditions = [ 'd.type = %d' ];
		$values     = [ Dataset::TYPE_INFORMATIONAL_KNOWLEDGE ];

		if ( ! empty( $params['source_type'] ) ) {
			$conditions[] = 'd.source_type = %s';
			$values[]     = (string) $params['source_type'];
		}

		if ( ! empty( $params['source_sub_type'] ) ) {
			$conditions[] = 'd.source_sub_type = %s';
			$values[]     = (string) $params['source_sub_type'];
		}

		$where_sql = 'WHERE ' . implode( ' AND ', $conditions );

		return [ $where_sql, $values ];
	}

	/**
	 * Build SQL WHERE clause for one group scope.
	 *
	 * @param  string  $source_type
	 * @param  string|null  $source_sub_type
	 *
	 * @return array{0:string,1:array}
	 */
	private function build_group_scope_where_sql( string $source_type, ?string $source_sub_type ): array {
		$conditions = [
			'd.type = %d',
			'd.source_type = %s',
		];
		$values     = [
			Dataset::TYPE_INFORMATIONAL_KNOWLEDGE,
			$source_type,
		];

		if ( null !== $source_sub_type ) {
			$conditions[] = 'd.source_sub_type = %s';
			$values[]     = $source_sub_type;
		}

		return [ implode( ' AND ', $conditions ), $values ];
	}

	/**
	 * Fetch grouped dataset aggregates.
	 *
	 * @param  string  $table
	 * @param  string  $where_sql
	 * @param  array  $values
	 *
	 * @return array
	 */
	private function fetch_group_aggregates( string $table, string $where_sql, array $values ): array {
		$wpdb = WPDB::instance()->get_wpdb();

		$sql = "
			SELECT
				d.source_type,
				d.source_sub_type,
				COUNT(*) AS count,
				MAX(d.updated_at) AS latest_updated_at,
				SUM(CASE WHEN d.status = %d THEN 1 ELSE 0 END) AS indexed_count
			FROM {$table} d
			{$where_sql}
			GROUP BY d.source_type, d.source_sub_type
			ORDER BY d.source_type ASC, d.source_sub_type ASC
		";

		$prepared = $wpdb->prepare(
			$sql,
			array_merge( [ Dataset::STATUS_INDEXED ], $values )
		);

		$rows = $wpdb->get_results( $prepared, ARRAY_A );

		$groups = [];

		foreach ( $rows ?? [] as $row ) {
			$descriptor = $this->build_group_descriptor(
				(string) $row['source_type'],
				$row['source_sub_type'] ?? null
			);

			$slug = $descriptor['slug'];

			if ( ! isset( $groups[ $slug ] ) ) {
				$groups[ $slug ] = $this->create_empty_group( $descriptor );
			}

			$groups[ $slug ]['count']          += (int) $row['count'];
			$groups[ $slug ]['_indexed_count'] += (int) $row['indexed_count'];

			$this->update_latest_timestamp(
				$groups[ $slug ],
				$row['latest_updated_at'] ?? null
			);
		}

		return $groups;
	}

	/**
	 * Build group descriptor.
	 *
	 * @param  string  $source_type
	 * @param  string|null  $source_sub_type
	 *
	 * @return array
	 */
	private function build_group_descriptor( string $source_type, ?string $source_sub_type ): array {
		$source_sub_type = $source_sub_type ?: null;

		switch ( $source_type ) {
			case Dataset::SOURCE_FILE:
				return [
					'slug'            => 'media',
					'label'           => __( 'Media', 'limb-chatbot' ),
					'source_type'     => Dataset::SOURCE_FILE,
					'source_sub_type' => null,
				];

			case Dataset::SOURCE_TEXT:
				return [
					'slug'            => 'texts',
					'label'           => __( 'Texts', 'limb-chatbot' ),
					'source_type'     => Dataset::SOURCE_TEXT,
					'source_sub_type' => null,
				];

			case Dataset::SOURCE_QA:
				return [
					'slug'            => 'q_a',
					'label'           => __( 'Q&A', 'limb-chatbot' ),
					'source_type'     => Dataset::SOURCE_QA,
					'source_sub_type' => null,
				];
			case Dataset::SOURCE_POST:
			case Dataset::SOURCE_CPT:

				$label = $source_sub_type;

				if ( $source_sub_type ) {
					$post_type = get_post_type_object( $source_sub_type );
					if ( $post_type && ! empty( $post_type->labels->name ) ) {
						$label = $post_type->labels->name;
					}
				}

				return [
					'slug'            => sprintf(
						'%s:%s',
						$source_type,
						rawurlencode( (string) $source_sub_type )
					),
					'label'           => $label,
					'source_type'     => $source_type,
					'source_sub_type' => $source_sub_type,
				];

			default:

				$label = $source_sub_type ?: ucfirst(
					str_replace( '_', ' ', $source_type )
				);

				$slug = $source_sub_type
					? sprintf(
						'%s:%s',
						$source_type,
						rawurlencode( $source_sub_type )
					)
					: $source_type;

				return [
					'slug'            => $slug,
					'label'           => $label,
					'source_type'     => $source_type,
					'source_sub_type' => $source_sub_type,
				];
		}
	}

	/**
	 * Create empty group structure.
	 *
	 * @param  array  $descriptor
	 *
	 * @return array
	 */
	private function create_empty_group( array $descriptor ): array {
		return [
			'item'            => $descriptor['slug'],
			'slug'            => $descriptor['slug'],
			'label'           => $descriptor['label'],
			'source_type'     => $descriptor['source_type'],
			'source_sub_type' => $descriptor['source_sub_type'],
			'count'           => 0,
			'status'          => 0,
			'ids'             => [],
			'updated_at'      => null,
			'_indexed_count'  => 0,
		];
	}

	/**
	 * Update group latest update timestamp.
	 *
	 * @param  array  $group
	 * @param  string|null  $timestamp
	 *
	 * @return void
	 */
	private function update_latest_timestamp( array &$group, ?string $timestamp ): void {
		if ( empty( $timestamp ) ) {
			return;
		}

		if (
			empty( $group['updated_at'] ) ||
			strtotime( $timestamp ) > strtotime( $group['updated_at'] )
		) {
			$group['updated_at'] = $timestamp;
		}
	}

	/**
	 * Attach dataset IDs to groups.
	 *
	 * @param  array  $groups
	 * @param  string  $table
	 * @param  string  $where_sql
	 * @param  array  $values
	 *
	 * @return void
	 */
	private function attach_dataset_ids( array &$groups, string $table, string $where_sql, array $values ): void {
		$wpdb = WPDB::instance()->get_wpdb();

		$sql = "
			SELECT id, source_type, source_sub_type
			FROM {$table} d
			{$where_sql}
		";

		$prepared = ! empty( $values )
			? $wpdb->prepare( $sql, $values )
			: $sql;

		$rows = $wpdb->get_results( $prepared, ARRAY_A );

		foreach ( $rows ?? [] as $row ) {
			$descriptor = $this->build_group_descriptor(
				(string) $row['source_type'],
				$row['source_sub_type'] ?? null
			);

			$slug = $descriptor['slug'];

			if ( ! isset( $groups[ $slug ] ) ) {
				$groups[ $slug ] = $this->create_empty_group( $descriptor );
			}

			$groups[ $slug ]['ids'][] = (int) $row['id'];
		}
	}

	/**
	 * Normalize groups before returning.
	 *
	 * @param  array  $groups
	 *
	 * @return array
	 */
	private function normalize_groups( array $groups ): array {
		$dataset_roles_by_group = $this->collect_dataset_roles_for_groups( $groups );

		foreach ( $groups as &$group ) {
			$group['ids'] = array_values( array_unique( $group['ids'] ) );
			sort( $group['ids'] );

			$group['status'] = $group['_indexed_count'] > 0 ? 1 : 0;
			unset( $group['_indexed_count'] );

			$this->apply_autosync_configuration( $group );
			$this->apply_content_configuration( $group );
			$this->apply_user_roles_configuration(
				$group,
				$dataset_roles_by_group[ $group['slug'] ] ?? [ self::ROLE_EVERYONE ]
			);
		}

		return array_values( $groups );
	}

	/**
	 * Apply role setting and role-based description for one group.
	 *
	 * - `user_roles` is read from group setting.
	 * - `description` is generated from existing datasets in the group.
	 *
	 * @param  array  $group
	 *
	 * @return void
	 */
	private function apply_user_roles_configuration( array &$group, array $dataset_roles ): void {
		$setting_key = 'lbaic.utilities.chatbot.user_roles_for_' . $group['slug'];
		$setting     = Setting::find( $setting_key );
		$group['user_roles'] = $setting ? $this->normalize_role_slugs( $setting->get_value() ) : [];

		$labels        = $this->map_role_slugs_to_labels( $dataset_roles );

		$group['description'] = sprintf(
			/* translators: %s is a comma separated list of role labels. */
			__( 'Roles: %s', 'limb-chatbot' ),
			implode( ', ', $labels )
		);
	}

	/**
	 * Collect unique role slugs for all groups with a single SQL query.
	 *
	 * @param  array  $groups
	 *
	 * @return array<string, string[]>
	 */
	private function collect_dataset_roles_for_groups( array $groups ): array {
		$dataset_ids = [];
		$group_id_map = [];

		foreach ( $groups as $slug => $group ) {
			$ids = array_values( array_filter( array_map( 'absint', $group['ids'] ?? [] ) ) );
			$group_id_map[ $slug ] = $ids;
			$dataset_ids           = array_merge( $dataset_ids, $ids );
		}

		$dataset_ids = array_values( array_unique( array_filter( $dataset_ids ) ) );
		$result      = [];

		foreach ( array_keys( $groups ) as $slug ) {
			$result[ $slug ] = [ self::ROLE_EVERYONE ];
		}

		if ( empty( $dataset_ids ) ) {
			return $result;
		}

		$wpdb        = WPDB::instance()->get_wpdb();
		$metas_table = $wpdb->prefix . 'lbaic_dataset_metas';
		$placeholders = implode( ', ', array_fill( 0, count( $dataset_ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = "
			SELECT dataset_id, meta_value
			FROM {$metas_table}
			WHERE meta_key = %s
			  AND dataset_id IN ({$placeholders})
		";

		$rows = $wpdb->get_results(
			$wpdb->prepare( $sql, array_merge( [ self::META_USER_ROLES ], $dataset_ids ) ),
			ARRAY_A
		);

		$roles_by_dataset = [];
		foreach ( $rows ?? [] as $row ) {
			$dataset_id = isset( $row['dataset_id'] ) ? absint( $row['dataset_id'] ) : 0;
			if ( $dataset_id <= 0 ) {
				continue;
			}
			$current_roles = $this->normalize_role_slugs( $row['meta_value'] ?? null );
			if ( empty( $current_roles ) ) {
				$current_roles = [ self::ROLE_EVERYONE ];
			}
			$roles_by_dataset[ $dataset_id ] = $current_roles;
		}

		foreach ( $group_id_map as $slug => $ids ) {
			$group_roles = [];
			$has_meta_roles = false;
			foreach ( $ids as $dataset_id ) {
				if ( ! array_key_exists( $dataset_id, $roles_by_dataset ) ) {
					continue;
				}
				$has_meta_roles = true;
				$dataset_roles  = $roles_by_dataset[ $dataset_id ];
				$group_roles    = array_merge( $group_roles, $dataset_roles );
			}
			$group_roles = array_values( array_unique( $group_roles ) );

			// Everyone is fallback only when no dataset role metas exist in this group.
			if ( ! $has_meta_roles ) {
				$result[ $slug ] = [ self::ROLE_EVERYONE ];
			} elseif ( ! empty( $group_roles ) ) {
				$result[ $slug ] = $group_roles;
			}
		}

		return $result;
	}

	/**
	 * Normalize role payload into unique slug list.
	 *
	 * @param  mixed  $roles
	 *
	 * @return string[]
	 */
	private function normalize_role_slugs( $roles ): array {
		if ( null === $roles || false === $roles || '' === $roles ) {
			return [];
		}
		if ( is_string( $roles ) ) {
			$decoded = json_decode( $roles, true );
			if ( is_array( $decoded ) ) {
				$roles = $decoded;
			} else {
				$roles = str_contains( $roles, ',' ) ? array_map( 'trim', explode( ',', $roles ) ) : [ $roles ];
			}
		}
		if ( ! is_array( $roles ) ) {
			return [];
		}

		$normalized = array_map(
			static function ( $role ): string {
				if ( ! is_string( $role ) ) {
					return '';
				}

				return sanitize_key( $role );
			},
			$roles
		);

		return array_values( array_filter( array_unique( $normalized ) ) );
	}

	/**
	 * Convert role slugs to readable labels.
	 *
	 * @param  string[]  $role_slugs
	 *
	 * @return string[]
	 */
	private function map_role_slugs_to_labels( array $role_slugs ): array {
		$plugin_labels = [
			self::ROLE_EVERYONE => __( 'Everyone', 'limb-chatbot' ),
			self::ROLE_CONTACTS => __( 'Contacts', 'limb-chatbot' ),
			self::ROLE_VISITORS => __( 'Visitors', 'limb-chatbot' ),
		];

		$wp_role_labels = [];
		$wp_roles       = wp_roles();
		if ( $wp_roles && isset( $wp_roles->roles ) && is_array( $wp_roles->roles ) ) {
			foreach ( $wp_roles->roles as $key => $data ) {
				$wp_role_labels[ sanitize_key( (string) $key ) ] = (string) ( $data['name'] ?? $key );
			}
		}

		$labels = [];
		foreach ( $role_slugs as $slug ) {
			$slug = sanitize_key( (string) $slug );
			if ( isset( $plugin_labels[ $slug ] ) ) {
				$labels[] = $plugin_labels[ $slug ];
				continue;
			}
			if ( isset( $wp_role_labels[ $slug ] ) ) {
				$labels[] = $wp_role_labels[ $slug ];
				continue;
			}
			$labels[] = ucwords( str_replace( '_', ' ', $slug ) );
		}

		return array_values( array_unique( $labels ) );
	}

	/**
	 * Apply autosync configuration.
	 *
	 * @param  array  $group
	 *
	 * @return void
	 */
	private function apply_autosync_configuration( array &$group ): void {
		$source_type = $group['source_type'];
		$sub_type    = $group['source_sub_type'];

		if ( ! in_array( $source_type, [ Dataset::SOURCE_POST ], true ) ) {
			$group['has_autosync']     = 0;
			$group['autosync_enabled'] = 0;

			return;
		}

		$group['has_autosync'] = 1;

		$key = 'auto_sync_for_post_' . $sub_type;

		$group['autosync_enabled'] = (int) ( Setting::find(
			'lbaic.utilities.chatbot.' . $key
		)->get_value() ?? 0 );
	}

	/**
	 * Apply content configuration.
	 *
	 * @param  array  $group
	 *
	 * @return void
	 */
	private function apply_content_configuration( array &$group ): void {
		if ( $group['source_type'] !== Dataset::SOURCE_POST ) {
			$group['has_content_config'] = 0;
			$group['content_config']     = [];

			return;
		}

		$group['has_content_config'] = 1;

		$group['content_config'] = Setting::find(
			'lbaic.utilities.chatbot.content_config_for_' . $group['source_sub_type']
		)->get_value() ?? [];
	}
}