<?php

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Repository methods use fixed plugin-owned aggregate tables with prepared value placeholders.
class AVIN_Views_Repository {
	public function record_view( $object_id, $object_type, $view_date = null ) {
		global $wpdb;

		$object_id   = absint( $object_id );
		$object_type = sanitize_key( $object_type );
		$view_date   = $view_date ? sanitize_text_field( $view_date ) : current_time( 'Y-m-d' );
		$now         = current_time( 'mysql' );

		if ( $object_id < 1 || '' === $object_type ) {
			return false;
		}

		$totals = $this->totals_table();
		$daily  = $this->daily_table();

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$totals} (object_id, object_type, total_views, last_viewed_at)
				VALUES (%d, %s, 1, %s)
				ON DUPLICATE KEY UPDATE total_views = total_views + 1, object_type = VALUES(object_type), last_viewed_at = VALUES(last_viewed_at)",
				$object_id,
				$object_type,
				$now
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$daily} (view_date, object_id, object_type, views)
				VALUES (%s, %d, %s, 1)
				ON DUPLICATE KEY UPDATE views = views + 1, object_type = VALUES(object_type)",
				$view_date,
				$object_id,
				$object_type
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		wp_cache_delete( 'avin_overview_' . md5( $view_date ), 'apex-view-insights' );
		foreach ( array( 1, 7, 30, 90 ) as $days ) {
			wp_cache_delete( 'avin_overview_days_' . $days, 'apex-view-insights' );
		}
		return true;
	}

	public function get_total_views( $object_id ) {
		global $wpdb;

		return absint(
			$wpdb->get_var(
				$wpdb->prepare(
					"SELECT total_views FROM {$this->totals_table()} WHERE object_id = %d",
					absint( $object_id )
				)
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	public function get_today_views( $object_id ) {
		return $this->get_views_for_days( $object_id, 1 );
	}

	public function get_views_on_date( $object_id, $date ) {
		global $wpdb;

		return absint(
			$wpdb->get_var(
				$wpdb->prepare(
					"SELECT COALESCE(SUM(views), 0) FROM {$this->daily_table()} WHERE object_id = %d AND view_date = %s",
					absint( $object_id ),
					sanitize_text_field( $date )
				)
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	public function get_views_for_days( $object_id, $days ) {
		global $wpdb;

		$days  = max( 1, absint( $days ) );
		$since = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . ( $days - 1 ) . ' days' ) );

		return absint(
			$wpdb->get_var(
				$wpdb->prepare(
					"SELECT COALESCE(SUM(views), 0) FROM {$this->daily_table()} WHERE object_id = %d AND view_date >= %s",
					absint( $object_id ),
					$since
				)
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	public function total_views_today() {
		return $this->total_views_on_date( current_time( 'Y-m-d' ) );
	}

	public function total_views_on_date( $date ) {
		global $wpdb;

		$date      = sanitize_text_field( $date );
		$cache_key = 'avin_overview_' . md5( $date );
		$cached    = wp_cache_get( $cache_key, 'apex-view-insights' );
		if ( false !== $cached ) {
			return absint( $cached );
		}

		$total = absint(
			$wpdb->get_var(
				$wpdb->prepare(
					"SELECT COALESCE(SUM(views), 0) FROM {$this->daily_table()} WHERE view_date = %s",
					$date
				)
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		wp_cache_set( $cache_key, $total, 'apex-view-insights', 300 );
		return $total;
	}

	public function total_views_for_days( $days ) {
		global $wpdb;

		$days      = max( 1, absint( $days ) );
		$since     = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . ( $days - 1 ) . ' days' ) );
		$cache_key = 'avin_overview_days_' . $days;
		$cached    = wp_cache_get( $cache_key, 'apex-view-insights' );
		if ( false !== $cached ) {
			return absint( $cached );
		}

		$total = absint(
			$wpdb->get_var(
				$wpdb->prepare(
					"SELECT COALESCE(SUM(views), 0) FROM {$this->daily_table()} WHERE view_date >= %s",
					$since
				)
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		wp_cache_set( $cache_key, $total, 'apex-view-insights', 300 );
		return $total;
	}

	public function total_views_all_time() {
		global $wpdb;

		return absint(
			$wpdb->get_var(
				"SELECT COALESCE(SUM(total_views), 0) FROM {$this->totals_table()}"
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	public function daily_totals( $days = 7 ) {
		global $wpdb;

		$days  = max( 1, min( 90, absint( $days ) ) );
		$since = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . ( $days - 1 ) . ' days' ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT view_date, COALESCE(SUM(views), 0) AS views
				FROM {$this->daily_table()}
				WHERE view_date >= %s
				GROUP BY view_date
				ORDER BY view_date ASC",
				$since
			),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$by_date = array();
		foreach ( $rows as $row ) {
			$by_date[ $row['view_date'] ] = absint( $row['views'] );
		}

		$series = array();
		for ( $i = $days - 1; $i >= 0; $i-- ) {
			$date     = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . $i . ' days' ) );
			$series[] = array(
				'date'  => $date,
				'label' => date_i18n( 'M j', strtotime( $date ) ),
				'views' => isset( $by_date[ $date ] ) ? absint( $by_date[ $date ] ) : 0,
			);
		}

		return $series;
	}

	public function rising_objects( $post_types, $limit = 5 ) {
		global $wpdb;

		$post_types = array_values( array_filter( array_map( 'sanitize_key', (array) $post_types ) ) );
		if ( empty( $post_types ) ) {
			return array();
		}

		$today        = current_time( 'Y-m-d' );
		$yesterday    = gmdate( 'Y-m-d', strtotime( $today . ' -1 day' ) );
		$limit        = max( 1, min( 50, absint( $limit ) ) );
		$placeholders = implode( ',', array_fill( 0, count( $post_types ), '%s' ) );
		$params       = array_merge( array( $yesterday, $today ), $post_types, array( $limit ) );
		$sql          = "SELECT today.object_id, today.object_type, p.post_title, p.post_type, today.views AS today_views, COALESCE(yesterday.views, 0) AS yesterday_views, (today.views - COALESCE(yesterday.views, 0)) AS lift
			FROM {$this->daily_table()} today
			LEFT JOIN {$this->daily_table()} yesterday ON yesterday.object_id = today.object_id AND yesterday.view_date = %s
			INNER JOIN {$wpdb->posts} p ON p.ID = today.object_id
			WHERE today.view_date = %s
				AND p.post_status = 'publish'
				AND p.post_type IN ({$placeholders})
			ORDER BY lift DESC, today.views DESC
			LIMIT %d";

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	public function top_objects( $post_types, $days = 30, $limit = 10 ) {
		global $wpdb;

		$post_types = array_values( array_filter( array_map( 'sanitize_key', (array) $post_types ) ) );
		if ( empty( $post_types ) ) {
			return array();
		}

		$days         = max( 1, absint( $days ) );
		$limit        = max( 1, min( 500, absint( $limit ) ) );
		$since        = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . ( $days - 1 ) . ' days' ) );
		$placeholders = implode( ',', array_fill( 0, count( $post_types ), '%s' ) );
		$params       = array_merge( array( $since ), $post_types, array( $limit ) );
		$sql          = "SELECT d.object_id, d.object_type, p.post_title, p.post_type, SUM(d.views) AS period_views, COALESCE(t.total_views, 0) AS total_views
			FROM {$this->daily_table()} d
			INNER JOIN {$wpdb->posts} p ON p.ID = d.object_id
			LEFT JOIN {$this->totals_table()} t ON t.object_id = d.object_id
			WHERE d.view_date >= %s
				AND p.post_status = 'publish'
				AND p.post_type IN ({$placeholders})
			GROUP BY d.object_id, d.object_type, p.post_title, p.post_type, t.total_views
			ORDER BY period_views DESC, d.object_id DESC
			LIMIT %d";

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	public function object_metrics( $object_ids ) {
		$metrics = array();
		foreach ( array_map( 'absint', (array) $object_ids ) as $object_id ) {
			if ( $object_id < 1 ) {
				continue;
			}
			$metrics[ $object_id ] = array(
				'today' => $this->get_views_for_days( $object_id, 1 ),
				'7'     => $this->get_views_for_days( $object_id, 7 ),
				'30'    => $this->get_views_for_days( $object_id, 30 ),
				'total' => $this->get_total_views( $object_id ),
			);
		}

		return $metrics;
	}

	public function export_rows( $limit = 500 ) {
		global $wpdb;

		$limit = max( 1, min( 5000, absint( $limit ) ) );
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT t.object_id, t.object_type, p.post_title, p.post_type, t.total_views
				FROM {$this->totals_table()} t
				INNER JOIN {$wpdb->posts} p ON p.ID = t.object_id
				WHERE p.post_status = 'publish'
				ORDER BY t.total_views DESC
				LIMIT %d",
				$limit
			),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	public function delete_daily_older_than( $days ) {
		global $wpdb;

		$cutoff = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . absint( $days ) . ' days' ) );
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$this->daily_table()} WHERE view_date < %s",
				$cutoff
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	public function totals_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_view_totals';
	}

	public function daily_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_view_daily';
	}
}
