<?php
namespace PageVICO\Admin\Views;

defined( 'ABSPATH' ) || exit;

use PageVICO\Cache\CacheManager;

class TopSessions {
	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_sessions', $date_from, $date_to, $paged );
		$total_key = CacheManager::generateDateKey( 'PageVICO_top_sessions_total', $date_from, $date_to );

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

		if ( $results === false || $total === false ) {
			// First, let's check if we have any data in the tables
			$check_data = $wpdb->get_results(
				$wpdb->prepare(
					"
					SELECT 
						'pagevico_sessions' as table_name,
						COUNT(*) as total_records,
						COUNT(CASE WHEN is_bot = 0 THEN 1 END) as non_bot_sessions,
						COUNT(CASE WHEN session_id IS NOT NULL THEN 1 END) as valid_sessions,
						MIN(first_seen) as earliest_date,
						MAX(first_seen) as latest_date
					FROM {$wpdb->prefix}pagevico_sessions
					WHERE first_seen BETWEEN %s AND %s
					UNION ALL
					SELECT 
						'pagevico_pageviews' as table_name,
						COUNT(*) as total_records,
						COUNT(CASE WHEN session_id IS NOT NULL THEN 1 END) as valid_sessions,
						COUNT(CASE WHEN session_id IS NOT NULL THEN 1 END) as valid_sessions,
						MIN(created_at) as earliest_date,
						MAX(created_at) as latest_date
					FROM {$wpdb->prefix}pagevico_pageviews
					WHERE created_at BETWEEN %s AND %s
					",
					$date_from . ' 00:00:00',
					$date_to . ' 23:59:59',
					$date_from . ' 00:00:00',
					$date_to . ' 23:59:59'
				),
				ARRAY_A
			);

			// Modified main query with more inclusive conditions and optimized subquery
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"
					WITH session_pages AS (
						SELECT 
							session_id,
							GROUP_CONCAT(DISTINCT page_url ORDER BY created_at ASC SEPARATOR '||') as pages_visited
						FROM {$wpdb->prefix}pagevico_pageviews
						WHERE created_at BETWEEN %s AND %s
						GROUP BY session_id
					)
					SELECT 
						s.session_id,
						COALESCE(s.country, 'unknown') as country,
						COALESCE(s.device_type, 'unknown') as device_type,
						COALESCE(s.browser, 'unknown') as browser,
						COALESCE(s.os, 'unknown') as os,
						s.first_seen,
						s.last_seen,
						1 as sessions,
						COUNT(DISTINCT pv.id) as pageviews,
						ROUND(COUNT(DISTINCT pv.id), 1) as pages_per_session,
						COALESCE(sp.pages_visited, '') as pages_visited,
						CAST(TIMESTAMPDIFF(SECOND, s.first_seen, s.last_seen) AS UNSIGNED) as duration
					FROM {$wpdb->prefix}pagevico_sessions s
					LEFT JOIN {$wpdb->prefix}pagevico_pageviews pv ON s.session_id = pv.session_id
					LEFT JOIN session_pages sp ON s.session_id = sp.session_id
					WHERE (s.first_seen BETWEEN %s AND %s OR pv.created_at BETWEEN %s AND %s)
					AND s.is_bot = 0
					GROUP BY 
						s.session_id,
						s.country,
						s.device_type,
						s.browser,
						s.os,
						s.first_seen,
						s.last_seen,
						sp.pages_visited
					HAVING pageviews > 0
					ORDER BY pageviews DESC, duration DESC
					LIMIT %d OFFSET %d
					",
					$date_from . ' 00:00:00',
					$date_to . ' 23:59:59',
					$date_from . ' 00:00:00',
					$date_to . ' 23:59:59',
					$date_from . ' 00:00:00',
					$date_to . ' 23:59:59',
					$limit,
					$offset
				),
				ARRAY_A
			);

			if (empty($results)) {
				// Try a simpler query to see if we can get any data
				$simple_check = $wpdb->get_results(
					$wpdb->prepare(
						"
						SELECT 
							session_id,
							country,
							device_type,
							browser,
							os,
							first_seen,
							last_seen
						FROM {$wpdb->prefix}pagevico_sessions
						WHERE first_seen BETWEEN %s AND %s
						AND is_bot = 0
						LIMIT 5
						",
						$date_from . ' 00:00:00',
						$date_to . ' 23:59:59'
					),
					ARRAY_A
				);
			}

			// Get total count with the same conditions
			$total = $wpdb->get_var(
				$wpdb->prepare(
					"
					SELECT COUNT(DISTINCT s.session_id)
					FROM {$wpdb->prefix}pagevico_sessions s
					LEFT JOIN {$wpdb->prefix}pagevico_pageviews pv ON s.session_id = pv.session_id
					WHERE (s.first_seen BETWEEN %s AND %s OR pv.created_at BETWEEN %s AND %s)
					AND s.is_bot = 0
					",
					$date_from . ' 00:00:00',
					$date_to . ' 23:59:59',
					$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-sessions.php';
	}

	public static function formatDuration( $seconds ) {
		if ( $seconds < 60 ) {
			// translators: %d is the number of seconds
			return sprintf( _n( '%d sec', '%d secs', $seconds, 'page-visit-counter-analytics' ), $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
		);
	}

	public static function getDeviceIcon( $device_type ) {
		$icons = array(
			'desktop' => 'dashicons-desktop',
			'mobile'  => 'dashicons-smartphone',
			'tablet'  => 'dashicons-tablet',
			'unknown' => 'dashicons-welcome-view-site',
		);

		return $icons[ strtolower( $device_type ) ] ?? $icons['unknown'];
	}

	public static function formatShort( $number, $precision = 1 ) {
		// Handle null or non-numeric values
		if ( $number === null || ! is_numeric( $number ) ) {
			return '0';
		}

		if ( $number >= 1000000 ) {
			return number_format( $number / 1000000, $precision ) . 'M';
		} elseif ( $number >= 1000 ) {
			return number_format( $number / 1000, $precision ) . 'K';
		}
		return number_format( $number, $precision );
	}

	public static function formatDecimal( $number, $precision = 2 ) {
		// Handle null or non-numeric values
		if ( $number === null || ! is_numeric( $number ) ) {
			return '0';
		}
		return number_format( $number, $precision );
	}
}
