<?php
/**
 * Admin Database Manager.
 *
 * Handles database operations and admin statistics queries.
 *
 * @package Accessibility_Audio_TTS_Player
 * @subpackage Admin\Database
 */

namespace Accessibility_Audio_TTS\Admin;

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

/**
 * Class Admin_Database_Manager.
 */
class Admin_Database_Manager {

	/**
	 * Get safe post types for SQL queries.
	 *
	 * @return array{types:string[]} Validated post types array.
	 */
	public function get_safe_post_types_string() {
		$saved_post_types = get_option( 'accessibility_audio_tts_post_types', array( 'post' ) );

		// Validate post types exist and are public.
		$all_post_types  = get_post_types( array( 'public' => true ) );
		$safe_post_types = array_values( array_intersect( $saved_post_types, array_keys( $all_post_types ) ) );

		if ( empty( $safe_post_types ) ) {
			$safe_post_types = array( 'post' ); // Fallback to safe default.
		}

		return array(
			'types' => $safe_post_types,
		);
	}

	/**
	 * Get count of posts with generated audio.
	 *
	 * @return int Number of posts with audio generated.
	 */
	public function get_generated_posts_count() {
		$post_types_data = $this->get_safe_post_types_string();
		$cache_key       = 'accessibility_audio_tts_generated_posts_count_' . md5( implode( ',', $post_types_data['types'] ) );
		$cached          = wp_cache_get( $cache_key, 'accessibility_audio_tts' );
		if ( false !== $cached ) {
			return (int) $cached;
		}

		$query = new \WP_Query(
			array(
				'post_type'              => $post_types_data['types'],
				'post_status'            => 'publish',
				'meta_query'             => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- Required for counting posts with generated audio.
					array(
						'key'     => 'accessibility_audio_tts_generated',
						'compare' => 'EXISTS',
					),
				),
				'fields'                 => 'ids',
				'posts_per_page'         => 1,
				'no_found_rows'          => false,
				'update_post_meta_cache' => false,
				'update_post_term_cache' => false,
			)
		);

		$value = (int) $query->found_posts;
		wp_cache_set( $cache_key, $value, 'accessibility_audio_tts', 300 );
		return $value;
	}

	/**
	 * Get count of published posts.
	 *
	 * @return int Number of published posts for enabled types.
	 */
	public function get_published_posts_count() {
		$post_types_data = $this->get_safe_post_types_string();
		$cache_key       = 'accessibility_audio_tts_published_posts_count_' . md5( implode( ',', $post_types_data['types'] ) );
		$cached          = wp_cache_get( $cache_key, 'accessibility_audio_tts' );
		if ( false !== $cached ) {
			return (int) $cached;
		}

		$query = new \WP_Query(
			array(
				'post_type'              => $post_types_data['types'],
				'post_status'            => 'publish',
				'fields'                 => 'ids',
				'posts_per_page'         => 1,
				'no_found_rows'          => false,
				'update_post_meta_cache' => false,
				'update_post_term_cache' => false,
			)
		);

		$value = (int) $query->found_posts;
		wp_cache_set( $cache_key, $value, 'accessibility_audio_tts', 300 );
		return $value;
	}

	/**
	 * Calculate audio generation percentage.
	 *
	 * @param int $generated_count Number of generated posts.
	 * @param int $published_posts Total published posts.
	 * @return int Percentage value (0-100).
	 */
	public function calculate_generation_percentage( $generated_count, $published_posts ) {
		return $published_posts > 0 ? (int) round( ( $generated_count / $published_posts ) * 100 ) : 0;
	}

	/**
	 * Get paginated list of generated posts.
	 *
	 * @param int $page     Page number (1-based).
	 * @param int $per_page Items per page.
	 * @return array{posts:array,total:int,page:int,has_more:bool,current_count:int} Paginated result.
	 */
	public function get_generated_posts_paginated( $page = 1, $per_page = 10 ) {
		$post_types_data = $this->get_safe_post_types_string();

		$query = new \WP_Query(
			array(
				'post_type'              => $post_types_data['types'],
				'post_status'            => 'publish',
				'meta_query'             => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- Required for listing posts with generated audio.
					array(
						'key'     => 'accessibility_audio_tts_generated',
						'compare' => 'EXISTS',
					),
				),
				'posts_per_page'         => $per_page,
				'paged'                  => $page,
				'orderby'                => 'date',
				'order'                  => 'DESC',
				'no_found_rows'          => false,
				'update_post_term_cache' => false,
			)
		);

		$total_posts = (int) $query->found_posts;

		if ( 0 === $total_posts ) {
			return array(
				'posts'    => array(),
				'total'    => 0,
				'page'     => 1,
				'has_more' => false,
			);
		}

		$offset = ( (int) $page - 1 ) * (int) $per_page;

		return array(
			'posts'         => $query->posts,
			'total'         => $total_posts,
			'page'          => (int) $page,
			'has_more'      => $total_posts > ( $offset + (int) $per_page ),
			'current_count' => $offset + count( $query->posts ),
		);
	}

	/**
	 * Get average character count for a specific post type.
	 *
	 * @param string $post_type Post type to analyze.
	 * @return int Average character count.
	 */
	public function get_post_type_char_count( $post_type ) {
		// Validate post type.
		$all_post_types = get_post_types( array( 'public' => true ) );
		if ( ! in_array( $post_type, array_keys( $all_post_types ), true ) ) {
			return 3000; // Default fallback.
		}

		$query = new \WP_Query(
			array(
				'post_type'              => $post_type,
				'post_status'            => 'publish',
				'posts_per_page'         => 100,
				'orderby'                => 'date',
				'order'                  => 'DESC',
				'no_found_rows'          => true,
				'update_post_meta_cache' => false,
				'update_post_term_cache' => false,
			)
		);

		if ( empty( $query->posts ) ) {
			return 3000; // Default fallback.
		}

		$total_chars = 0;
		$count       = 0;
		foreach ( $query->posts as $post ) {
			if ( ! empty( $post->post_content ) ) {
				$total_chars += mb_strlen( $post->post_content );
				++$count;
			}
		}

		if ( 0 === $count ) {
			return 3000;
		}

		$avg_chars = (int) round( $total_chars / $count );

		// Ensure reasonable bounds (minimum 500, maximum 10000).
		return max( 500, min( 10000, $avg_chars ) );
	}
}