<?php
namespace PageVICO\Admin\Views;

defined( 'ABSPATH' ) || exit;

use PageVICO\Cache\CacheManager;

class TopCustomPosts {
	public static function render() {
		global $wpdb;
	
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Nonce verification is not needed for GET parameters
		$date_from = isset($_GET['from']) ? sanitize_text_field(wp_unslash($_GET['from'])) : date('Y-m-d', current_time('timestamp') - (7 * 86400));
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Nonce verification is not needed for GET parameters
		$date_to   = isset($_GET['to']) ? sanitize_text_field(wp_unslash($_GET['to'])) : date('Y-m-d', current_time('timestamp'));
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Nonce verification is not needed for GET parameters
		$paged     = max(1, (int) (wp_unslash($_GET['paged'] ?? 1)));
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Nonce verification is not needed for GET parameters
		$limit     = get_option('posts_per_page', 25);
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Nonce verification is not needed for GET parameters
		$offset    = ($paged - 1) * $limit;
	
		$cache_key = CacheManager::generateDateKey('PageVICO_top_custom_posts', $date_from, $date_to, $paged);
		$total_key = CacheManager::generateDateKey('PageVICO_top_custom_posts_total', $date_from, $date_to);
	
		$results = CacheManager::get($cache_key);
		$total   = CacheManager::get($total_key);
	
		$custom_post_types = get_post_types(['_builtin' => false], 'names');
	
		if ($results === false || $total === false) {
			// Generate placeholders for each post type
			$post_type_placeholders = implode(',', array_fill(0, count($custom_post_types), '%s'));
			
			// Base query parameters
			$base_params = [
				$date_from . ' 00:00:00',
				$date_to . ' 23:59:59'
			];
			
			// Step 1: Get basic custom post statistics
			$basic_stats = $wpdb->get_results(
				$wpdb->prepare(
					"
					SELECT 
						pv.page_url,
						p.post_title,
						p.ID as post_id,
						p.post_type,
						COUNT(DISTINCT pv.id) as views,
						COUNT(DISTINCT pv.session_id) as unique_visitors,
						DATE(MIN(pv.created_at)) as first_view,
						DATE(MAX(pv.created_at)) as last_view
					FROM {$wpdb->prefix}pagevico_pageviews pv
					LEFT JOIN {$wpdb->posts} p ON p.ID = pv.post_id
					WHERE pv.created_at BETWEEN %s AND %s
					AND pv.post_type IN ($post_type_placeholders)
					AND (p.post_status = 'publish' OR p.post_status IS NULL)
					GROUP BY 
						pv.page_url,
						p.post_title,
						p.ID,
						p.post_type
					ORDER BY views DESC
					LIMIT %d OFFSET %d
					",
					array_merge($base_params, $custom_post_types, [$limit, $offset])
				),
				ARRAY_A
			);

			if (empty($basic_stats)) {
				$results = array();
			} else {
				// Step 2: Get time on page statistics
				$page_urls = array_map(function($stat) {
					return $stat['page_url'];
				}, $basic_stats);

				// Create placeholders for the IN clause
				$placeholders = array_fill(0, count($page_urls), '%s');
				$in_clause = implode(',', $placeholders);

				$time_stats = $wpdb->get_results(
					$wpdb->prepare(
						"
						WITH next_pageviews AS (
							SELECT 
								pv1.id,
								pv1.page_url,
								pv1.created_at,
								MIN(pv2.created_at) as next_view_time
							FROM {$wpdb->prefix}pagevico_pageviews pv1
							LEFT JOIN {$wpdb->prefix}pagevico_pageviews pv2 
								ON pv1.session_id = pv2.session_id 
								AND pv2.created_at > pv1.created_at
							WHERE pv1.created_at BETWEEN %s AND %s
							AND pv1.page_url IN ({$in_clause})
							GROUP BY pv1.id, pv1.page_url, pv1.created_at
						)
						SELECT 
							pv.page_url,
							CAST(
								AVG(
									CASE 
										WHEN np.next_view_time IS NOT NULL 
										THEN TIMESTAMPDIFF(SECOND, pv.created_at, np.next_view_time)
										WHEN s.last_seen > pv.created_at 
										THEN TIMESTAMPDIFF(SECOND, pv.created_at, s.last_seen)
										ELSE 0 
									END
								) AS UNSIGNED
							) as avg_time_on_page
						FROM {$wpdb->prefix}pagevico_pageviews pv
						LEFT JOIN {$wpdb->prefix}pagevico_sessions s ON s.session_id = pv.session_id
						LEFT JOIN next_pageviews np ON np.id = pv.id
						WHERE pv.created_at BETWEEN %s AND %s
						AND pv.page_url IN ({$in_clause})
						GROUP BY pv.page_url
						",
						array_merge(
							[$date_from . ' 00:00:00'],
							$page_urls,
							[$date_to . ' 23:59:59'],
							$page_urls
						)
					),
					ARRAY_A
				);

				// Step 3: Merge the results
				$time_stats_map = array();
				if (!empty($time_stats)) {
					$time_stats_map = array_column($time_stats, 'avg_time_on_page', 'page_url');
				}
				
				$results = array_map(function($stat) use ($time_stats_map) {
					$stat['avg_time_on_page'] = $time_stats_map[$stat['page_url']] ?? 0;
					$stat['views_per_visitor'] = round($stat['views'] / $stat['unique_visitors'], 1);
					$stat['display_url'] = self::truncateUrl($stat['page_url']);
					return $stat;
				}, $basic_stats);
			}

			// Get total count
			$total = $wpdb->get_var(
				$wpdb->prepare(
					"
					SELECT COUNT(DISTINCT page_url)
					FROM {$wpdb->prefix}pagevico_pageviews
					WHERE created_at BETWEEN %s AND %s
					AND post_type IN ($post_type_placeholders)
					",
					array_merge($base_params, $custom_post_types)
				)
			);

			if ($wpdb->last_error || $total === null) {
				$total = 0;
			}

			CacheManager::set($cache_key, $results);
			CacheManager::set($total_key, $total);
		}
	
		include PageVICO_PLUGIN_DIR . 'templates/pages/top-custom-posts.php';
	}
	

	public static function formatDuration( $seconds ) {
		if ( $seconds === null || ! is_numeric( $seconds ) || $seconds <= 0 ) {
			return '0 sec';
		}

		$seconds = (int) $seconds;

		if ( $seconds < 60 ) {
			// translators: %d: number of seconds
			return sprintf( _n( '%d sec', '%d secs', $seconds, 'page-visit-counter-analytics' ), $seconds );
		}

		$minutes = floor( $seconds / 60 );
		$secs    = $seconds % 60;

		if ( $minutes < 60 ) {
			return sprintf(
				'%d:%02d min',
				$minutes,
				$secs
			);
		}

		$hours = floor( $minutes / 60 );
		$mins  = $minutes % 60;

		return sprintf(
			'%d:%02d:%02d hrs',
			$hours,
			$mins,
			$secs
		);
	}

	/**
	 * Truncates a URL to a more readable format
	 *
	 * @param string $url The URL to truncate
	 * @param int    $max_length Maximum length of the truncated URL
	 * @return string The truncated URL
	 */
	public static function truncateUrl($url, $max_length = 50) {
		// Remove protocol and www
		$url = preg_replace('#^https?://(www\.)?#', '', $url);
		
		// Remove trailing slash
		$url = rtrim($url, '/');
		
		// If URL is already short enough, return it
		if (strlen($url) <= $max_length) {
			return $url;
		}
		
		// Truncate and add ellipsis
		return substr($url, 0, $max_length) . '...';
	}
}
