<?php

namespace Yipresser\AdminOptimizer\Modules;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Site_Traffic_Manager_DB class
 */
class Site_Traffic_Manager_DB {

	const OPTION_NAME = 'adminoptim_site_traffic_manager_db';

	const TOTAL_DB_VERSION = '1.2';

	const DAILY_DB_VERSION = '1.2';

	const TOTAL_TABLE_NAME = 'ao_pv_stats_total';

	const DAILY_TABLE_NAME = 'ao_pv_stats_daily';

	/**
	 * Total table name
	 *
	 * @var string
	 */
	protected $total_table_name;

	/**
	 * Daily table name
	 *
	 * @var string
	 */
	protected $daily_table_name;

	/**
	 * Constructor
	 */
	public function __construct() {
		global $wpdb;
		$this->total_table_name = $wpdb->prefix . self::TOTAL_TABLE_NAME;
		$this->daily_table_name = $wpdb->prefix . self::DAILY_TABLE_NAME;
		$this->setup_db();
	}

	/**
	 * Install or upgrade database tables if necessary.
	 *
	 * @return void
	 */
	private function setup_db() {
		$db_option = get_option( self::OPTION_NAME, [] );
		$changed   = false;

		if ( empty( $db_option['total_db_installed'] ) || self::TOTAL_DB_VERSION !== ( $db_option['total_db_version'] ?? '' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			global $wpdb;
			$charset_collate = $wpdb->get_charset_collate();
			$sql             = "CREATE TABLE {$this->total_table_name} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				post_id bigint(20) unsigned NULL DEFAULT NULL,
				url_path varchar(191) NOT NULL DEFAULT '',
				label varchar(191) NOT NULL DEFAULT '',
				row_key varchar(40) NOT NULL DEFAULT '',
				pageview bigint(20) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (id),
				UNIQUE KEY row_key (row_key)
			) {$charset_collate};";
			dbDelta( $sql );

			$this->backfill_row_keys( $this->total_table_name );

			$db_option['total_db_installed'] = 1;
			$db_option['total_db_version']   = self::TOTAL_DB_VERSION;
			$changed                         = true;
		}

		if ( empty( $db_option['daily_db_installed'] ) || self::DAILY_DB_VERSION !== ( $db_option['daily_db_version'] ?? '' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			global $wpdb;
			$charset_collate = $wpdb->get_charset_collate();
			$sql             = "CREATE TABLE {$this->daily_table_name} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				post_id bigint(20) unsigned NULL DEFAULT NULL,
				url_path varchar(191) NOT NULL DEFAULT '',
				label varchar(191) NOT NULL DEFAULT '',
				row_key varchar(40) NOT NULL DEFAULT '',
				date date NOT NULL,
				pageview bigint(20) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (id),
				UNIQUE KEY row_date (row_key, date),
				KEY date (date)
			) {$charset_collate};";
			dbDelta( $sql );

			$this->backfill_row_keys( $this->daily_table_name );

			$db_option['daily_db_installed'] = 1;
			$db_option['daily_db_version']   = self::DAILY_DB_VERSION;
			$changed                         = true;
		}

		if ( $changed ) {
			update_option( self::OPTION_NAME, $db_option, false );
		}
	}

	/**
	 * Backfill row_key for pre-1.2 rows that predate the column.
	 *
	 * dbDelta() only alters columns/indexes — it never touches existing data,
	 * so rows created before row_key existed need it derived from their
	 * post_id here before the row_key unique index can do its job.
	 *
	 * @param string $table_name Table to backfill.
	 *
	 * @return void
	 */
	private function backfill_row_keys( string $table_name ): void {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- One-time schema backfill on the module's own table; $table_name is always $this->total_table_name/$this->daily_table_name (prefix + hardcoded literal), never user input, nothing to cache.
		$wpdb->query(
			"UPDATE {$table_name} SET row_key = CONCAT('p', post_id) WHERE row_key = '' AND post_id IS NOT NULL"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Build the deterministic row_key used for atomic upserts.
	 *
	 * Post views are keyed by post_id directly; any other trackable view
	 * (home page, archives, search, anything without a resolvable post) is
	 * keyed by a hash of its normalized URL path instead, so no per-view-type
	 * schema is needed for the growing set of non-post view kinds.
	 *
	 * @param int|null $post_id  Post ID, or null for a non-post view.
	 * @param string   $url_path Normalized URL path for a non-post view.
	 *
	 * @return string
	 */
	protected function build_row_key( $post_id, string $url_path ): string {
		if ( $post_id ) {
			return 'p' . absint( $post_id );
		}

		return 'u' . md5( $url_path );
	}

	/**
	 * Get current local date for daily pageview storage.
	 *
	 * @return string
	 */
	protected function get_current_date() {
		return current_time( 'Y-m-d' );
	}

	/**
	 * Increment total page view count using a single atomic query.
	 *
	 * @param int|null $post_id  Post ID, or null when tracking a non-post URL.
	 * @param int      $count    Increment count.
	 * @param string   $url_path Normalized URL path for a non-post view. Ignored when $post_id is set.
	 * @param string   $label    Human-readable label for a non-post view (e.g. document title).
	 *
	 * @return int|false
	 */
	public function increment_total_pv( $post_id, $count = 1, $url_path = '', $label = '' ) {
		global $wpdb;

		$post_id = $post_id ? absint( $post_id ) : null;

		if ( ! $post_id && '' === $url_path ) {
			return false;
		}

		$count   = max( 1, absint( $count ) );
		$row_key = $this->build_row_key( $post_id, $url_path );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table write; not cacheable. $this->total_table_name is $wpdb->prefix + a hardcoded literal, never user input.
		return $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"INSERT INTO {$this->total_table_name} (post_id, url_path, label, row_key, pageview)
				VALUES (%d, %s, %s, %s, %d)
				ON DUPLICATE KEY UPDATE pageview = pageview + %d",
				$post_id,
				$post_id ? '' : $url_path,
				$post_id ? '' : $label,
				$row_key,
				$count,
				$count
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get total page view count for a post.
	 *
	 * @param int $post_id Post ID.
	 *
	 * @return int
	 */
	public function get_total_pv( $post_id ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table read; admin-facing display value, not worth an object-cache layer. $this->total_table_name is $wpdb->prefix + a hardcoded literal, never user input.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT pageview FROM {$this->total_table_name} WHERE row_key = %s",
				$this->build_row_key( absint( $post_id ), '' )
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get total page view count for a non-post URL.
	 *
	 * @param string $url_path Normalized URL path.
	 *
	 * @return int
	 */
	public function get_url_total_pv( string $url_path ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table read; admin-facing display value, not worth an object-cache layer. $this->total_table_name is $wpdb->prefix + a hardcoded literal, never user input.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT pageview FROM {$this->total_table_name} WHERE row_key = %s",
				$this->build_row_key( null, $url_path )
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Increment daily page view count using a single atomic query.
	 *
	 * @param int|null    $post_id  Post ID, or null when tracking a non-post URL.
	 * @param string|null $date     Date string in `Y-m-d` format.
	 * @param int         $count    Increment count.
	 * @param string      $url_path Normalized URL path for a non-post view. Ignored when $post_id is set.
	 * @param string      $label    Human-readable label for a non-post view (e.g. document title).
	 *
	 * @return int|false
	 */
	public function increment_daily_pv( $post_id, $date = '', $count = 1, $url_path = '', $label = '' ) {
		global $wpdb;

		$post_id = $post_id ? absint( $post_id ) : null;

		if ( ! $post_id && '' === $url_path ) {
			return false;
		}

		$query_date = ! empty( $date ) ? (string) $date : $this->get_current_date();
		if ( ! self::is_valid_ymd( $query_date ) ) {
			return false;
		}

		$count   = max( 1, absint( $count ) );
		$row_key = $this->build_row_key( $post_id, $url_path );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table write; not cacheable. $this->daily_table_name is $wpdb->prefix + a hardcoded literal, never user input.
		return $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"INSERT INTO {$this->daily_table_name} (post_id, url_path, label, row_key, date, pageview)
				VALUES (%d, %s, %s, %s, %s, %d)
				ON DUPLICATE KEY UPDATE pageview = pageview + %d",
				$post_id,
				$post_id ? '' : $url_path,
				$post_id ? '' : $label,
				$row_key,
				$query_date,
				$count,
				$count
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get daily page view count for a post.
	 *
	 * @param int         $post_id Post ID.
	 * @param string|null $date    Date string in `Y-m-d` format.
	 *
	 * @return int
	 */
	public function get_daily_pv( $post_id, $date = '' ) {
		global $wpdb;
		$query_date = ! empty( $date ) ? (string) $date : current_time( 'Y-m-d' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table read; admin-facing display value, not worth an object-cache layer. $this->daily_table_name is $wpdb->prefix + a hardcoded literal, never user input.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT pageview FROM {$this->daily_table_name} WHERE row_key = %s AND date = %s",
				$this->build_row_key( absint( $post_id ), '' ),
				$query_date
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get the posts/views with the highest page view counts.
	 *
	 * The `$post_type` filter has three tiers: 'all' includes every tracked
	 * row (posts and non-post URLs alike); 'all_post_types' includes only
	 * rows tied to an actual post (excludes non-post URL rows); a specific
	 * post type slug narrows to that type only.
	 *
	 * @param int    $limit                 Maximum number of rows to return.
	 * @param string $post_type             Optional post type filter — 'all', 'all_post_types', or a post type slug.
	 * @param string $time_period           Optional pageview time period key, or 'custom' to use $date_from/$date_to.
	 * @param string $published_time_period Optional publish date filter key.
	 * @param string $date_from             Inclusive custom range start (`Y-m-d`). Only used when $time_period is 'custom'.
	 * @param string $date_to               Inclusive custom range end (`Y-m-d`). Only used when $time_period is 'custom'.
	 *
	 * @return array
	 */
	public function get_top_total_pv_posts( $limit = 50, $post_type = 'all', $time_period = 'all_time', $published_time_period = 'all_time', $date_from = '', $date_to = '' ) {
		global $wpdb;

		$limit                 = min( 500, max( 1, absint( $limit ) ) );
		$post_type             = sanitize_key( $post_type );
		$time_period           = sanitize_key( $time_period );
		$published_time_period = sanitize_key( $published_time_period );
		$params                = [];
		$select_row_fields     = 'stats.post_id, stats.url_path, stats.label';

		$range = 'custom' === $time_period
			? self::get_custom_date_range( $date_from, $date_to )
			: null;

		if ( 'custom' === $time_period && null === $range ) {
			$time_period = 'all_time';
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $query is built entirely from hardcoded SQL fragments and $this->total_table_name/$this->daily_table_name/$wpdb->posts (never user input); all actual values flow through $params and $wpdb->prepare() below. Custom plugin table read; admin-facing report, not worth an object-cache layer.
		if ( 'all_time' === $time_period ) {
			$query = "SELECT {$select_row_fields}, stats.pageview, posts.post_title, posts.post_type, posts.post_date
				FROM {$this->total_table_name} AS stats
				LEFT JOIN {$wpdb->posts} AS posts ON posts.ID = stats.post_id";
		} elseif ( 'custom' === $time_period ) {
			$select_row_fields = 'daily.post_id, daily.url_path, daily.label';
			$query             = "SELECT {$select_row_fields}, SUM(daily.pageview) AS pageview, posts.post_title, posts.post_type, posts.post_date
				FROM {$this->daily_table_name} AS daily
				LEFT JOIN {$wpdb->posts} AS posts ON posts.ID = daily.post_id
				WHERE daily.date >= %s AND daily.date <= %s";
			$params[]          = $range['from'];
			$params[]          = $range['to'];
		} else {
			$start_date = self::get_time_period_start_date( $time_period );
			if ( ! $start_date ) {
				$time_period = 'all_time';
				$query       = "SELECT {$select_row_fields}, stats.pageview, posts.post_title, posts.post_type, posts.post_date
					FROM {$this->total_table_name} AS stats
					LEFT JOIN {$wpdb->posts} AS posts ON posts.ID = stats.post_id";
			} else {
				$select_row_fields = 'daily.post_id, daily.url_path, daily.label';
				$query             = "SELECT {$select_row_fields}, SUM(daily.pageview) AS pageview, posts.post_title, posts.post_type, posts.post_date
					FROM {$this->daily_table_name} AS daily
					LEFT JOIN {$wpdb->posts} AS posts ON posts.ID = daily.post_id
					WHERE daily.date >= %s";
				$params[]          = $start_date;
			}
		}

		if ( 'all_post_types' === $post_type ) {
			$query .= empty( $params ) ? ' WHERE posts.ID IS NOT NULL' : ' AND posts.ID IS NOT NULL';
		} elseif ( 'all' !== $post_type && '' !== $post_type ) {
			$query   .= empty( $params ) ? ' WHERE posts.post_type = %s' : ' AND posts.post_type = %s';
			$params[] = $post_type;
		}

		// The publish-date filter only applies to post rows; non-post URL rows
		// (posts.post_date IS NULL) are kept regardless so 'all' isn't silently
		// narrowed down to posts only by this filter.
		$published_start_date = self::get_time_period_start_date( $published_time_period );
		if ( '' !== $published_start_date ) {
			$query   .= empty( $params )
				? ' WHERE (posts.post_date >= %s OR posts.ID IS NULL)'
				: ' AND (posts.post_date >= %s OR posts.ID IS NULL)';
			$params[] = $published_start_date . ' 00:00:00';
		}

		if ( 'all_time' === $time_period ) {
			$query .= ' ORDER BY stats.pageview DESC, stats.id DESC';
		} else {
			$query .= ' GROUP BY daily.row_key, posts.post_title, posts.post_type ORDER BY pageview DESC, daily.post_id DESC';
		}

		$query   .= ' LIMIT %d';
		$params[] = $limit;

		return $wpdb->get_results(
			$wpdb->prepare( $query, $params ),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get the inclusive start date for a supported time period.
	 *
	 * @param string $time_period Time period key.
	 *
	 * @return string
	 */
	public static function get_time_period_start_date( $time_period ) {
		$days = [
			'last_3_days'  => 3,
			'last_7_days'  => 7,
			'last_30_days' => 30,
			'last_90_days' => 90,
		];

		if ( ! isset( $days[ $time_period ] ) ) {
			return '';
		}

		$current_datetime = new \DateTimeImmutable( current_time( 'mysql' ) );
		$start_datetime   = $current_datetime->modify( '-' . ( $days[ $time_period ] - 1 ) . ' days' );

		return $start_datetime->format( 'Y-m-d' );
	}

	/**
	 * Validate and normalize a custom date range.
	 *
	 * @param string $date_from Inclusive range start (`Y-m-d`).
	 * @param string $date_to   Inclusive range end (`Y-m-d`).
	 *
	 * @return array{from: string, to: string}|null Null if either date is invalid or the range is backwards.
	 */
	public static function get_custom_date_range( string $date_from, string $date_to ): ?array {
		if ( ! self::is_valid_ymd( $date_from ) || ! self::is_valid_ymd( $date_to ) ) {
			return null;
		}

		if ( $date_from > $date_to ) {
			return null;
		}

		return [
			'from' => $date_from,
			'to'   => $date_to,
		];
	}

	/**
	 * Check whether a string is a real calendar date in `Y-m-d` format.
	 *
	 * @param string $date Date string to validate.
	 *
	 * @return bool
	 */
	private static function is_valid_ymd( string $date ): bool {
		$date_object = \DateTimeImmutable::createFromFormat( 'Y-m-d', $date );
		$date_errors = \DateTimeImmutable::getLastErrors();

		return false !== $date_object
			&& ( false === $date_errors || ( 0 === $date_errors['warning_count'] && 0 === $date_errors['error_count'] ) )
			&& $date === $date_object->format( 'Y-m-d' );
	}
}