<?php
namespace PageVICO\Admin\Views;

defined( 'ABSPATH' ) || exit;

use PageVICO\Cache\CacheManager;

class TopPages {
	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;
		$current_page = $paged;

		$cache_key = CacheManager::generateDateKey( 'PageVICO_top_pages', $date_from, $date_to, $paged );
		$total_key = CacheManager::generateDateKey( 'PageVICO_top_pages_total', $date_from, $date_to );

		$results = CacheManager::get( $cache_key );
		$total   = CacheManager::get( $total_key );

		if ( $results === false || $total === false ) {

			// Step 1: Get basic pageview statistics
			$basic_stats = $wpdb->get_results(
				$wpdb->prepare(
					"
					SELECT 
						pv.page_url,
						p.post_title,
						p.ID as post_id,
						COUNT(DISTINCT pv.id) as views,
						COUNT(DISTINCT pv.session_id) as sessions,
						p.post_type,
						p.post_status,
						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 = 'page'
					GROUP BY 
						pv.page_url,
						p.post_title,
						p.ID,
						p.post_type,
						p.post_status
					HAVING sessions > 0
					ORDER BY views DESC
					LIMIT %d OFFSET %d
					",
					$date_from . ' 00:00:00',
					$date_to . ' 23:59:59',
					$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['pages_per_session'] = $stat['views'] / $stat['sessions'];
					$stat['content_type'] = $stat['post_type'] === 'page' ? 'Page' : 
						($stat['post_type'] === 'post' ? 'Post' : 
						($stat['post_type'] === null ? 'External' : $stat['post_type']));
					$stat['link_type'] = !empty($stat['post_id']) ? 'Internal' : 'External';
					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 = 'page'
					",
					$date_from . ' 00:00:00',
					$date_to . ' 23:59:59'
				)
			);

			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-pages.php';
	}

	public static function formatDuration( $seconds ) {
		// Convert and round the seconds first
		$rounded_seconds = (int) round( (float) $seconds );

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

		$minutes = floor( $seconds / 60 );
		$secs    = round( fmod( $seconds, 60 ) ); // Use fmod for floating point modulo

		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
		);
	}
}
