<?php

declare( strict_types=1 );

namespace PulseViews\Database;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery

use DateTimeImmutable;

final class ViewsRepository {

	private const PRUNE_AFTER_DAYS = 90;

	public function insert_view( array $data ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'pulseviews_views';

		$result = $wpdb->insert(
			$table,
			[
				'post_id'      => $data['post_id'],
				'viewed_at'    => current_time( 'mysql' ),
				'ip_hash'      => $data['ip_hash'],
				'session_key'  => $data['session_key'],
				'user_agent'   => mb_substr( $data['user_agent'], 0, 255 ),
				'is_bot'       => $data['is_bot'] ? 1 : 0,
				'country_code' => mb_substr( $data['country_code'], 0, 2 ),
				'referrer'     => mb_substr( $data['referrer'], 0, 512 ),
			],
			[ '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s' ]
		);

		if ( false === $result ) {
			return 0;
		}

		$this->upsert_totals( (int) $data['post_id'] );

		return (int) $wpdb->insert_id;
	}

	public function upsert_totals( int $post_id ): void {
		global $wpdb;

		$views_table  = $wpdb->prefix . 'pulseviews_views';
		$totals_table = $wpdb->prefix . 'pulseviews_post_totals';
		$today        = current_time( 'Y-m-d' );
		$week_start   = gmdate( 'Y-m-d', strtotime( 'monday this week', current_time( 'timestamp' ) ) );
		$month_start  = current_time( 'Y-m' ) . '-01';

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO %i (post_id, total_views, today_views, week_views, month_views, last_viewed)
				SELECT
					%d,
					COUNT(*),
					SUM(viewed_at >= %s),
					SUM(viewed_at >= %s),
					SUM(viewed_at >= %s),
					MAX(viewed_at)
				FROM %i
				WHERE post_id = %d AND is_bot = 0
				ON DUPLICATE KEY UPDATE
					total_views = VALUES(total_views),
					today_views = VALUES(today_views),
					week_views  = VALUES(week_views),
					month_views = VALUES(month_views),
					last_viewed = VALUES(last_viewed)",
				$totals_table,
				$post_id,
				$today . ' 00:00:00',
				$week_start . ' 00:00:00',
				$month_start . ' 00:00:00',
				$views_table,
				$post_id
			)
		);
	}

	public function get_post_views( int $post_id, string $period = 'total' ): int {
		global $wpdb;

		$allowed_cols = [ 'total_views', 'today_views', 'week_views', 'month_views' ];
		$col          = match ( $period ) {
			'today' => 'today_views',
			'week'  => 'week_views',
			'month' => 'month_views',
			default => 'total_views',
		};

		if ( ! in_array( $col, $allowed_cols, true ) ) {
			return 0;
		}

		$table = $wpdb->prefix . 'pulseviews_post_totals';
		$cache_key = "pulseviews_totals_{$post_id}_{$period}";

		$cached = wp_cache_get( $cache_key, 'pulseviews' );
		if ( false !== $cached ) {
			return (int) $cached;
		}

		$count = (int) $wpdb->get_var(
			$wpdb->prepare( 'SELECT %i FROM %i WHERE post_id = %d', $col, $table, $post_id )
		);

		wp_cache_set( $cache_key, $count, 'pulseviews', 60 );

		return $count;
	}

	public function get_daily_views( int $post_id, int $days = 30 ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'pulseviews_aggregates';

		$start = gmdate( 'Y-m-d', strtotime( "-{$days} days", current_time( 'timestamp' ) ) );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT period_date AS date, view_count AS views
				 FROM %i
				 WHERE post_id = %d AND period_type = 'day' AND period_date >= %s
				 ORDER BY period_date ASC",
				$table,
				$post_id,
				$start
			),
			ARRAY_A
		);

		$indexed = array_column( (array) $rows, 'views', 'date' );
		$result  = [];

		for ( $i = $days - 1; $i >= 0; $i-- ) {
			$date      = gmdate( 'Y-m-d', strtotime( "-{$i} days", current_time( 'timestamp' ) ) );
			$result[]  = [ 'date' => $date, 'views' => (int) ( $indexed[ $date ] ?? 0 ) ];
		}

		return $result;
	}

	public function get_top_posts( int $limit = 10, string $post_type = '', string $period = 'total' ): array {
		global $wpdb;

		$col = match ( $period ) {
			'today' => 'today_views',
			'week'  => 'week_views',
			'month' => 'month_views',
			default => 'total_views',
		};
		$totals_table = $wpdb->prefix . 'pulseviews_post_totals';

		if ( $post_type ) {
			$posts_table = $wpdb->posts;
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT pt.post_id, pt.%i AS views
					 FROM %i pt
					 INNER JOIN %i p ON p.ID = pt.post_id
					 WHERE p.post_type = %s AND p.post_status = 'publish'
					 ORDER BY views DESC
					 LIMIT %d",
					$col,
					$totals_table,
					$posts_table,
					$post_type,
					$limit
				),
				ARRAY_A
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT post_id, %i AS views
					 FROM %i
					 ORDER BY views DESC
					 LIMIT %d",
					$col,
					$totals_table,
					$limit
				),
				ARRAY_A
			);
		}

		$rows = array_filter( (array) $rows, static fn( $r ) => (int) $r['views'] > 0 );

		if ( empty( $rows ) ) {
			$views_table = $wpdb->prefix . 'pulseviews_views';

			$rows = match ( $period ) {
				'today' => 
					$wpdb->get_results(
					$wpdb->prepare(
						"SELECT post_id, COUNT(*) AS views
						 FROM %i
						 WHERE is_bot = 0 AND DATE(viewed_at) = CURDATE()
						 GROUP BY post_id
						 ORDER BY views DESC
						 LIMIT %d",
						$views_table,
						$limit
					),
					ARRAY_A
				),
				'week' => 
					$wpdb->get_results(
					$wpdb->prepare(
						"SELECT post_id, COUNT(*) AS views
						 FROM %i
						 WHERE is_bot = 0 AND viewed_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
						 GROUP BY post_id
						 ORDER BY views DESC
						 LIMIT %d",
						$views_table,
						$limit
					),
					ARRAY_A
				),
				'month' =>
					$wpdb->get_results(
					$wpdb->prepare(
						"SELECT post_id, COUNT(*) AS views
						 FROM %i
						 WHERE is_bot = 0 AND viewed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
						 GROUP BY post_id
						 ORDER BY views DESC
						 LIMIT %d",
						$views_table,
						$limit
					),
					ARRAY_A
				),
				default => 
					$wpdb->get_results(
					$wpdb->prepare(
						"SELECT post_id, COUNT(*) AS views
						 FROM %i
						 WHERE is_bot = 0
						 GROUP BY post_id
						 ORDER BY views DESC
						 LIMIT %d",
						$views_table,
						$limit
					),
					ARRAY_A
				),
			} ?? [];
		}

		return array_map( static fn( $r ) => [
			'post_id' => (int) $r['post_id'],
			'views'   => (int) $r['views'],
		], (array) $rows );
	}

	public function get_site_totals(): array {
		global $wpdb;

		$cache_key = 'pulseviews_site_totals';
		$cached    = wp_cache_get( $cache_key, 'pulseviews' );
		if ( false !== $cached ) {
			return $cached;
		}

		$views_table  = $wpdb->prefix . 'pulseviews_views';
		$totals_table = $wpdb->prefix . 'pulseviews_post_totals';
		$today        = current_time( 'Y-m-d' );
		$week_start   = gmdate( 'Y-m-d', strtotime( 'monday this week', current_time( 'timestamp' ) ) );
		$month_start  = current_time( 'Y-m' ) . '-01';

		$total_row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT SUM(total_views) AS total,
					 COUNT(CASE WHEN week_views > 0 THEN 1 END) AS trending_count
				 FROM %i",
				$totals_table
			),
			ARRAY_A
		);

		$period_row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					 SUM(viewed_at >= %s) AS today,
					 SUM(viewed_at >= %s) AS week,
					 SUM(viewed_at >= %s) AS month
				 FROM %i
				 WHERE is_bot = 0",
				$today . ' 00:00:00',
				$week_start . ' 00:00:00',
				$month_start . ' 00:00:00',
				$views_table
			),
			ARRAY_A
		);

		$totals = [
			'total'          => (int) ( $total_row['total'] ?? 0 ),
			'today'          => (int) ( $period_row['today'] ?? 0 ),
			'week'           => (int) ( $period_row['week'] ?? 0 ),
			'month'          => (int) ( $period_row['month'] ?? 0 ),
			'trending_count' => (int) ( $total_row['trending_count'] ?? 0 ),
		];

		wp_cache_set( $cache_key, $totals, 'pulseviews', 60 );

		return $totals;
	}

	public function is_duplicate( int $post_id, string $session_key, string $ip_hash, int $interval = 1800 ): bool {
		global $wpdb;
		$table    = $wpdb->prefix . 'pulseviews_views';
		$earliest = gmdate( 'Y-m-d H:i:s', time() - $interval );

		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i
				 WHERE post_id = %d
				 AND (session_key = %s OR ip_hash = %s)
				 AND viewed_at > %s
				 LIMIT 1",
				$table,
				$post_id,
				$session_key,
				$ip_hash,
				$earliest
			)
		);

		return $count > 0;
	}

	public function prune_old_rows(): void {
		global $wpdb;
		$table  = $wpdb->prefix . 'pulseviews_views';
		$cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-' . self::PRUNE_AFTER_DAYS . ' days', time() ) );

		$wpdb->query(
			$wpdb->prepare( 'DELETE FROM %i WHERE viewed_at < %s LIMIT 5000', $table, $cutoff )
		);
	}
}