<?php
/**
 * Plan category repository.
 *
 * @package Lucrativeit_Subscription_Recurring_Billing
 */

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

/**
 * Data access for plan categories.
 */
class LUCRSURE_Plan_Category_Repository extends LUCRSURE_Repository {

	/**
	 * Table base name.
	 *
	 * @var string
	 */
	protected $table = 'plan_categories';

	/**
	 * Column formats.
	 *
	 * @var array<string,string>
	 */
	protected $formats = array(
		'name'        => '%s',
		'slug'        => '%s',
		'description' => '%s',
		'is_active'   => '%d',
		'created_at'  => '%s',
		'updated_at'  => '%s',
	);

	/**
	 * Get active categories.
	 *
	 * @return array[]
	 */
	public function active() {
		global $wpdb;
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT * FROM %i WHERE is_active = 1 ORDER BY name ASC',
				$this->table()
			),
			ARRAY_A
		);
	}

	/**
	 * Find a category by slug (prefers row with most plans).
	 *
	 * @param string $slug Category slug.
	 * @return array|null
	 */
	public function find_by_slug( $slug ) {
		global $wpdb;

		$slug = sanitize_title( $slug );
		if ( '' === $slug ) {
			return null;
		}

		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT c.* FROM %i c
				LEFT JOIN %i p ON p.category_id = c.id AND p.is_active = 1
				WHERE c.slug = %s
				GROUP BY c.id
				ORDER BY COUNT( p.id ) DESC, c.id ASC
				LIMIT 1',
				$this->table(),
				lucrsure_table( 'plans' ),
				$slug
			),
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * All active category IDs sharing a slug.
	 *
	 * @param string $slug Category slug.
	 * @return int[]
	 */
	public function all_ids_by_slug( $slug ) {
		global $wpdb;

		$slug = sanitize_title( $slug );
		if ( '' === $slug ) {
			return array();
		}

		$rows = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT id FROM %i WHERE slug = %s ORDER BY id ASC',
				$this->table(),
				$slug
			)
		);

		return array_map( 'intval', $rows ? $rows : array() );
	}

	/**
	 * All active category IDs sharing a slug.
	 *
	 * @param string $slug Category slug.
	 * @return int[]
	 */
	public function active_ids_by_slug( $slug ) {
		global $wpdb;

		$slug = sanitize_title( $slug );
		if ( '' === $slug ) {
			return array();
		}

		$rows = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT id FROM %i WHERE slug = %s AND is_active = 1 ORDER BY id ASC',
				$this->table(),
				$slug
			)
		);

		return array_map( 'intval', $rows ? $rows : array() );
	}

	/**
	 * All category IDs that represent the same logical category as the given row.
	 *
	 * Merges duplicate slugs, slug variants (e.g. gym-membership-2), and same-name rows.
	 *
	 * @param array $category Category row.
	 * @return int[]
	 */
	public function related_ids( array $category ) {
		global $wpdb;

		$ids = array();

		if ( ! empty( $category['id'] ) ) {
			$ids[] = (int) $category['id'];
		}

		if ( ! empty( $category['slug'] ) ) {
			$slug = sanitize_title( $category['slug'] );
			if ( '' !== $slug ) {
				$ids = array_merge( $ids, $this->all_ids_by_slug( $slug ) );

				$variant_rows = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
					$wpdb->prepare(
						'SELECT id FROM %i WHERE slug LIKE %s',
						$this->table(),
						$wpdb->esc_like( $slug ) . '-%'
					)
				);
				if ( $variant_rows ) {
					$ids = array_merge( $ids, array_map( 'intval', $variant_rows ) );
				}
			}
		}

		if ( ! empty( $category['name'] ) ) {
			$name_rows = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare(
					'SELECT id FROM %i WHERE name = %s',
					$this->table(),
					$category['name']
				)
			);
			if ( $name_rows ) {
				$ids = array_merge( $ids, array_map( 'intval', $name_rows ) );
			}
		}

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

	/**
	 * Merge duplicate category rows and reassign plans to one canonical row per name.
	 *
	 * @return int Number of plans reassigned.
	 */
	public function consolidate_duplicates() {
		global $wpdb;

		$plan_table = lucrsure_table( 'plans' );
		$moved      = 0;
		$groups     = array();

		foreach ( $this->all( 'name', 'ASC' ) as $category ) {
			$key = strtolower( trim( (string) $category['name'] ) );
			if ( '' === $key ) {
				continue;
			}
			$groups[ $key ][] = $category;
		}

		foreach ( $groups as $categories ) {
			if ( count( $categories ) < 2 ) {
				continue;
			}

			$canonical = $this->pick_canonical_category( $categories, $plan_table );
			if ( ! $canonical ) {
				continue;
			}

			$canonical_id = (int) $canonical['id'];

			foreach ( $categories as $category ) {
				$duplicate_id = (int) $category['id'];
				if ( $duplicate_id === $canonical_id ) {
					continue;
				}

				$updated = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
					$wpdb->prepare(
						'UPDATE %i SET category_id = %d WHERE category_id = %d',
						$plan_table,
						$canonical_id,
						$duplicate_id
					)
				);

				if ( $updated ) {
					$moved += (int) $updated;
				}

				$remaining = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
					$wpdb->prepare(
						'SELECT COUNT(*) FROM %i WHERE category_id = %d',
						$plan_table,
						$duplicate_id
					)
				);

				if ( 0 === $remaining ) {
					$this->update(
						$duplicate_id,
						array(
							'is_active' => 0,
						)
					);
				}
			}

			if ( class_exists( 'LUCRSURE_Plan_Pages' ) ) {
				$defs = LUCRSURE_Plan_Pages::definitions();
				foreach ( $defs as $slug => $def ) {
					if ( isset( $def['name'] ) && strtolower( trim( $def['name'] ) ) === strtolower( trim( (string) $canonical['name'] ) ) ) {
						$this->update(
							$canonical_id,
							array(
								'slug'      => $slug,
								'is_active' => 1,
							)
						);
						break;
					}
				}
			}
		}

		return $moved;
	}

	/**
	 * Pick the best category row to keep when duplicates share a name.
	 *
	 * @param array[] $categories Category rows.
	 * @param string  $plan_table Plans table name.
	 * @return array|null
	 */
	protected function pick_canonical_category( array $categories, $plan_table ) {
		global $wpdb;

		$best       = null;
		$best_count = -1;

		foreach ( $categories as $category ) {
			$count = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i WHERE category_id = %d AND is_active = 1',
					$plan_table,
					(int) $category['id']
				)
			);

			$is_base_slug = ! preg_match( '/-\d+$/', (string) $category['slug'] );

			if (
				$count > $best_count
				|| ( $count === $best_count && $best && $is_base_slug && preg_match( '/-\d+$/', (string) $best['slug'] ) )
			) {
				$best       = $category;
				$best_count = $count;
			}
		}

		return $best;
	}

	/**
	 * Map of category ID => name for dropdowns.
	 *
	 * @param bool $active_only Only include active categories.
	 * @return array<int,string>
	 */
	public function options( $active_only = true ) {
		$rows    = $active_only ? $this->active() : $this->all( 'name', 'ASC' );
		$options = array();
		foreach ( $rows as $row ) {
			$options[ (int) $row['id'] ] = $row['name'];
		}
		return $options;
	}
}
