<?php
/**
 * Analytics class.
 *
 * Manages the click-tracking DB table, recording clicks, and querying stats.
 *
 * @package IGIT_RPWT
 */

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

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- All queries interpolate only $table which is $wpdb->prefix . constant (never user input).

class IGIT_RPWT_Analytics {

	/** DB table suffix (without prefix). */
	const TABLE_SUFFIX = 'igit_rpwt_clicks';

	/** WP AJAX action for recording a click. */
	const AJAX_ACTION = 'igit_rpwt_track_click';

	/** Nonce action string used to verify tracking AJAX requests. */
	const NONCE_ACTION = 'igit_rpwt_track';

	// ── Schema ───────────────────────────────────────────────────────────────

	/**
	 * Full table name including WP prefix.
	 *
	 * @return string
	 */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_SUFFIX;
	}

	/**
	 * Create (or upgrade) the clicks table via dbDelta.
	 *
	 * Safe to call on every activation — dbDelta is idempotent.
	 */
	public static function create_table() {
		global $wpdb;

		$table           = self::table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id             bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			source_post_id bigint(20) unsigned NOT NULL,
			target_post_id bigint(20) unsigned NOT NULL,
			session_hash   char(32)            NOT NULL DEFAULT '',
			click_date     date                NOT NULL,
			click_count    int(10) unsigned    NOT NULL DEFAULT 1,
			click_source   varchar(20)         NOT NULL DEFAULT 'widget',
			PRIMARY KEY  (id),
			UNIQUE KEY session_daily (source_post_id, target_post_id, session_hash, click_date, click_source),
			KEY target_post_id (target_post_id),
			KEY click_date     (click_date),
			KEY click_source   (click_source)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Migrate existing tables to add the click_source column if missing.
	 *
	 * Idempotent — checks for column existence before altering. Runs once per
	 * DB version bump, gated by the igit_rpwt_db_version option.
	 */
	public static function maybe_migrate() {
		global $wpdb;

		$current_db_version = '1.1.0';
		if ( get_option( 'igit_rpwt_db_version' ) === $current_db_version ) {
			return;
		}

		$table = self::table_name();

		// Only alter if click_source column does not yet exist.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col = $wpdb->get_var( "SHOW COLUMNS FROM `{$table}` LIKE 'click_source'" );
		if ( ! $col ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN click_source varchar(20) NOT NULL DEFAULT 'widget'" );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE `{$table}` DROP INDEX session_daily, ADD UNIQUE KEY session_daily (source_post_id, target_post_id, session_hash, click_date, click_source)" );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE `{$table}` ADD KEY click_source (click_source)" );
		}

		update_option( 'igit_rpwt_db_version', $current_db_version );
	}

	/**
	 * Drop the clicks table entirely.
	 *
	 * Called from uninstall.php — irreversible.
	 */
	public static function drop_table() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( 'DROP TABLE IF EXISTS `' . self::table_name() . '`' );
	}

	// ── Recording ────────────────────────────────────────────────────────────

	/**
	 * Record a related-post click.
	 *
	 * Deduplicated per (source, target, visitor, day, source_type) using a
	 * privacy-safe session hash (MD5 of IP + UA + date — no PII stored).
	 *
	 * @param int    $source_post_id The post that displayed the related posts block.
	 * @param int    $target_post_id The related post that was clicked.
	 * @param string $click_source   'widget' or 'nudge'.
	 */
	public static function record_click( $source_post_id, $target_post_id, $click_source = 'widget' ) {
		global $wpdb;

		$source = absint( $source_post_id );
		$target = absint( $target_post_id );

		if ( ! $source || ! $target ) {
			return;
		}

		$allowed_sources = array( 'widget', 'nudge' );
		if ( ! in_array( $click_source, $allowed_sources, true ) ) {
			$click_source = 'widget';
		}

		// Build privacy-safe daily session hash — IP and UA never stored raw.
		$ip   = isset( $_SERVER['REMOTE_ADDR'] )
			? sanitize_text_field( wp_unslash( $_SERVER['REMOTE_ADDR'] ) )
			: '';
		$ua   = isset( $_SERVER['HTTP_USER_AGENT'] )
			? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) )
			: '';
		$hash = md5( $ip . '|' . $ua . '|' . wp_date( 'Y-m-d' ) );

		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO `{$table}`
				    (source_post_id, target_post_id, session_hash, click_date, click_count, click_source)
				 VALUES (%d, %d, %s, %s, 1, %s)
				 ON DUPLICATE KEY UPDATE click_count = click_count + 1",
				$source,
				$target,
				$hash,
				wp_date( 'Y-m-d' ),
				$click_source
			)
		);
	}

	// ── AJAX handler ─────────────────────────────────────────────────────────

	/**
	 * Handle the click-tracking AJAX request.
	 *
	 * Expects POST: nonce, source (post ID), target (post ID).
	 * Soft-fails on nonce error so the link navigation is never blocked.
	 */
	public static function ajax_track_click() {
		if ( ! check_ajax_referer( self::NONCE_ACTION, 'nonce', false ) ) {
			wp_send_json_error( null, 403 );
		}

		if ( ! IGIT_RPWT_Settings::get( 'enable_click_tracking' ) ) {
			wp_send_json_error( null, 403 );
		}

		$source       = isset( $_POST['source'] ) ? absint( $_POST['source'] ) : 0;
		$target       = isset( $_POST['target'] ) ? absint( $_POST['target'] ) : 0;
		$click_source = isset( $_POST['click_source'] ) ? sanitize_key( $_POST['click_source'] ) : 'widget';

		if ( ! $source || ! $target ) {
			wp_send_json_error( null, 400 );
		}

		self::record_click( $source, $target, $click_source );
		wp_send_json_success();
	}

	// ── Queries ──────────────────────────────────────────────────────────────

	/**
	 * Get top clicked target posts.
	 *
	 * @param int    $days   Look-back window in days. 0 = all time.
	 * @param int    $limit  Max rows.
	 * @param string $source '' = all, 'widget' or 'nudge' to filter.
	 * @return array[]  Each row: { target_post_id, total_clicks }
	 */
	public static function get_top_posts( $days = 30, $limit = 10, $source = '' ) {
		global $wpdb;

		$table  = self::table_name();
		$limit  = max( 1, absint( $limit ) );
		$source = in_array( $source, array( 'widget', 'nudge' ), true ) ? $source : '';

		if ( $days > 0 ) {
			if ( $source ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				return $wpdb->get_results(
					$wpdb->prepare(
						"SELECT target_post_id, SUM(click_count) AS total_clicks
						 FROM `{$table}`
						 WHERE click_date >= %s AND click_source = %s
						 GROUP BY target_post_id
						 ORDER BY total_clicks DESC
						 LIMIT %d",
						wp_date( 'Y-m-d', strtotime( "-{$days} days" ) ),
						$source,
						$limit
					),
					ARRAY_A
				) ?: array();
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT target_post_id, SUM(click_count) AS total_clicks
					 FROM `{$table}`
					 WHERE click_date >= %s
					 GROUP BY target_post_id
					 ORDER BY total_clicks DESC
					 LIMIT %d",
					wp_date( 'Y-m-d', strtotime( "-{$days} days" ) ),
					$limit
				),
				ARRAY_A
			) ?: array();
		}

		if ( $source ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT target_post_id, SUM(click_count) AS total_clicks
					 FROM `{$table}`
					 WHERE click_source = %s
					 GROUP BY target_post_id
					 ORDER BY total_clicks DESC
					 LIMIT %d",
					$source,
					$limit
				),
				ARRAY_A
			) ?: array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT target_post_id, SUM(click_count) AS total_clicks
				 FROM `{$table}`
				 GROUP BY target_post_id
				 ORDER BY total_clicks DESC
				 LIMIT %d",
				$limit
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Get total click count (sum of all click_count values).
	 *
	 * @param int    $days   0 = all time.
	 * @param string $source '' = all, 'widget' or 'nudge' to filter.
	 * @return int
	 */
	public static function get_total_clicks( $days = 30, $source = '' ) {
		global $wpdb;
		$table  = self::table_name();
		$source = in_array( $source, array( 'widget', 'nudge' ), true ) ? $source : '';

		if ( $days > 0 ) {
			if ( $source ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				return (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT SUM(click_count) FROM `{$table}` WHERE click_date >= %s AND click_source = %s",
						wp_date( 'Y-m-d', strtotime( "-{$days} days" ) ),
						$source
					)
				);
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT SUM(click_count) FROM `{$table}` WHERE click_date >= %s",
					wp_date( 'Y-m-d', strtotime( "-{$days} days" ) )
				)
			);
		}

		if ( $source ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT SUM(click_count) FROM `{$table}` WHERE click_source = %s",
					$source
				)
			);
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT SUM(click_count) FROM `{$table}`" );
	}

	/**
	 * Daily click totals for the last $days days (used for the activity chart).
	 *
	 * @param int    $days
	 * @param string $source '' = all, 'widget' or 'nudge' to filter.
	 * @return array[]  Each row: { click_date, daily_total }
	 */
	public static function get_daily_totals( $days = 30, $source = '' ) {
		global $wpdb;
		$table  = self::table_name();
		$source = in_array( $source, array( 'widget', 'nudge' ), true ) ? $source : '';

		if ( $source ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT click_date, SUM(click_count) AS daily_total
					 FROM `{$table}`
					 WHERE click_date >= %s AND click_source = %s
					 GROUP BY click_date
					 ORDER BY click_date ASC",
					wp_date( 'Y-m-d', strtotime( "-{$days} days" ) ),
					$source
				),
				ARRAY_A
			) ?: array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT click_date, SUM(click_count) AS daily_total
				 FROM `{$table}`
				 WHERE click_date >= %s
				 GROUP BY click_date
				 ORDER BY click_date ASC",
				wp_date( 'Y-m-d', strtotime( "-{$days} days" ) )
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Daily click totals split by widget vs nudge, for the dual chart.
	 *
	 * @param int $days
	 * @return array[]  Each row: { click_date, widget_total, nudge_total }
	 */
	public static function get_daily_totals_by_source( $days = 30 ) {
		global $wpdb;
		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT click_date, click_source, SUM(click_count) AS daily_total
				 FROM `{$table}`
				 WHERE click_date >= %s
				 GROUP BY click_date, click_source
				 ORDER BY click_date ASC",
				wp_date( 'Y-m-d', strtotime( "-{$days} days" ) )
			),
			ARRAY_A
		) ?: array();

		$map = array();
		foreach ( $rows as $row ) {
			$date = $row['click_date'];
			if ( ! isset( $map[ $date ] ) ) {
				$map[ $date ] = array( 'click_date' => $date, 'widget_total' => 0, 'nudge_total' => 0 );
			}
			if ( 'nudge' === $row['click_source'] ) {
				$map[ $date ]['nudge_total'] = (int) $row['daily_total'];
			} else {
				$map[ $date ]['widget_total'] = (int) $row['daily_total'];
			}
		}
		return array_values( $map );
	}

	/**
	 * Total clicks broken down by source type.
	 *
	 * @param int $days 0 = all time.
	 * @return array { 'widget' => int, 'nudge' => int }
	 */
	public static function get_clicks_by_source( $days = 30 ) {
		global $wpdb;
		$table  = self::table_name();
		$result = array( 'widget' => 0, 'nudge' => 0 );

		if ( $days > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT click_source, SUM(click_count) AS total
					 FROM `{$table}`
					 WHERE click_date >= %s
					 GROUP BY click_source",
					wp_date( 'Y-m-d', strtotime( "-{$days} days" ) )
				),
				ARRAY_A
			) ?: array();
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results(
				"SELECT click_source, SUM(click_count) AS total FROM `{$table}` GROUP BY click_source",
				ARRAY_A
			) ?: array();
		}

		foreach ( $rows as $row ) {
			if ( isset( $result[ $row['click_source'] ] ) ) {
				$result[ $row['click_source'] ] = (int) $row['total'];
			}
		}
		return $result;
	}

	/**
	 * Get click stats for clicks originating from a specific source post.
	 *
	 * @param int $source_post_id
	 * @param int $limit
	 * @return array[]  Each row: { target_post_id, total_clicks }
	 */
	public static function get_clicks_from_post( $source_post_id, $limit = 10 ) {
		global $wpdb;
		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT target_post_id, SUM(click_count) AS total_clicks
				 FROM `{$table}`
				 WHERE source_post_id = %d
				 GROUP BY target_post_id
				 ORDER BY total_clicks DESC
				 LIMIT %d",
				absint( $source_post_id ),
				max( 1, absint( $limit ) )
			),
			ARRAY_A
		) ?: array();
	}

	// ── Data management ──────────────────────────────────────────────────────

	/**
	 * Approximate size of the clicks table in bytes (data + index).
	 *
	 * @return int
	 */
	public static function get_table_size_bytes() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT (data_length + index_length)
				 FROM information_schema.TABLES
				 WHERE table_schema = DATABASE() AND table_name = %s',
				self::table_name()
			)
		);
		return (int) $result;
	}

	/**
	 * Total number of rows currently in the clicks table.
	 *
	 * @return int
	 */
	public static function get_row_count() {
		global $wpdb;
		$table = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
	}

	/**
	 * Delete all click data by truncating the table.
	 *
	 * Keeps the table schema intact; only removes rows.
	 */
	public static function truncate_data() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( 'TRUNCATE TABLE `' . self::table_name() . '`' );
	}
}
