<?php
/**
 * Wishlist Analytics for Product Display.
 *
 * @package Product_Display
 */

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

class PRODDISP_Wishlist_Analytics {

	protected static $_instance = null;
	private static $table_name = null;

	public static function get_instance() {
		if ( is_null( self::$_instance ) ) {
			self::$_instance = new self();
		}
		return self::$_instance;
	}

	public function __construct() {
		add_action( 'wp_ajax_proddisp_get_wishlist_analytics', array( $this, 'get_analytics' ) );
		add_action( 'wp_ajax_proddisp_clear_wishlist_analytics', array( $this, 'clear_analytics' ) );
	}

	/**
	 * Get the analytics table name.
	 */
	public static function get_table_name() {
		global $wpdb;
		if ( is_null( self::$table_name ) ) {
			self::$table_name = $wpdb->prefix . 'proddisp_wishlist_events';
		}
		return self::$table_name;
	}

	/**
	 * Create the analytics table on plugin activation.
	 */
	public static function create_table() {
		global $wpdb;
		$table = self::get_table_name();
		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE IF NOT EXISTS {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			product_id BIGINT UNSIGNED NOT NULL,
			user_id BIGINT UNSIGNED DEFAULT 0,
			action VARCHAR(20) NOT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY product_id (product_id),
			KEY user_id (user_id),
			KEY action (action),
			KEY created_at (created_at)
		) {$charset};";

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

	/**
	 * Record a wishlist event.
	 *
	 * @param int    $product_id Product ID.
	 * @param string $action     'add' or 'remove'.
	 */
	public static function record_event( $product_id, $action ) {
		global $wpdb;
		$table = self::get_table_name();

		$wpdb->insert(
			$table,
			array(
				'product_id' => absint( $product_id ),
				'user_id'    => is_user_logged_in() ? get_current_user_id() : 0,
				'action'     => sanitize_key( $action ),
				'created_at' => current_time( 'mysql' ),
			),
			array( '%d', '%d', '%s', '%s' )
		);
	}

	/**
	 * AJAX: Get analytics data.
	 */
	public function get_analytics() {
		if ( ! isset( $_POST['nonce'] ) || ! wp_verify_nonce( sanitize_text_field( wp_unslash( $_POST['nonce'] ) ), 'proddisp_admin_nonce' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Security check failed', 'product-display' ) ) );
		}
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Insufficient permissions', 'product-display' ) ) );
		}
		if ( ! PRODDISP_Loader::is_pro_active() ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Pro version required', 'product-display' ), 'pro_required' => true ) );
		}

		$period = isset( $_POST['period'] ) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : '30';
		$days   = absint( $period );
		if ( ! in_array( $days, array( 7, 30, 90, 365 ), true ) ) {
			$days = 30;
		}

		wp_send_json_success( array(
			'summary'        => $this->get_summary( $days ),
			'top_wishlisted' => $this->get_top_wishlisted( $days ),
			'trend'          => $this->get_trend( $days ),
			'recent_activity'=> $this->get_recent_activity( 20 ),
			'user_stats'     => $this->get_user_stats( $days ),
			'conversion'     => $this->get_conversion_data( $days ),
		) );
	}

	/**
	 * Summary stats: total adds, removes, unique products, unique users.
	 */
	private function get_summary( $days ) {
		global $wpdb;
		$table = self::get_table_name();
		$since = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$adds    = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE action='add' AND created_at >= %s", $since ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$removes = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE action='remove' AND created_at >= %s", $since ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$unique_products = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT product_id) FROM {$table} WHERE action='add' AND created_at >= %s", $since ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$unique_users    = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT user_id) FROM {$table} WHERE action='add' AND created_at >= %s AND user_id > 0", $since ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$guest_adds      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE action='add' AND created_at >= %s AND user_id = 0", $since ) );

		// Current active wishlists (all-time adds minus removes per product)
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$active = (int) $wpdb->get_var( "SELECT COUNT(*) FROM (
			SELECT product_id, SUM(CASE WHEN action='add' THEN 1 WHEN action='remove' THEN -1 ELSE 0 END) as net
			FROM {$table} GROUP BY product_id HAVING net > 0
		) as t" );

		return array(
			'total_adds'      => $adds,
			'total_removes'   => $removes,
			'unique_products' => $unique_products,
			'unique_users'    => $unique_users,
			'guest_adds'      => $guest_adds,
			'active_wishlists'=> $active,
			'retention_rate'  => $adds > 0 ? round( ( ( $adds - $removes ) / $adds ) * 100, 1 ) : 0,
		);
	}

	/**
	 * Top wishlisted products.
	 */
	private function get_top_wishlisted( $days, $limit = 10 ) {
		global $wpdb;
		$table = self::get_table_name();
		$since = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT product_id,
				SUM(CASE WHEN action='add' THEN 1 ELSE 0 END) as adds,
				SUM(CASE WHEN action='remove' THEN 1 ELSE 0 END) as removes,
				COUNT(DISTINCT user_id) as unique_users
			FROM {$table}
			WHERE created_at >= %s
			GROUP BY product_id
			ORDER BY adds DESC
			LIMIT %d",
			$since, $limit
		) );

		$products = array();
		foreach ( $rows as $row ) {
			$product = wc_get_product( $row->product_id );
			if ( ! $product ) continue;

			$products[] = array(
				'id'           => (int) $row->product_id,
				'name'         => $product->get_name(),
				'price'        => $product->get_price_html(),
				'price_raw'    => (float) $product->get_price(),
				'image'        => get_the_post_thumbnail_url( $row->product_id, 'thumbnail' ),
				'url'          => get_permalink( $row->product_id ),
				'adds'         => (int) $row->adds,
				'removes'      => (int) $row->removes,
				'unique_users' => (int) $row->unique_users,
				'net'          => (int) $row->adds - (int) $row->removes,
				'stock_status' => $product->get_stock_status(),
				'category'     => wp_get_post_terms( $row->product_id, 'product_cat', array( 'fields' => 'names' ) ),
			);
		}

		return $products;
	}

	/**
	 * Daily trend data for chart.
	 */
	private function get_trend( $days ) {
		global $wpdb;
		$table = self::get_table_name();
		$since = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT DATE(created_at) as date,
				SUM(CASE WHEN action='add' THEN 1 ELSE 0 END) as adds,
				SUM(CASE WHEN action='remove' THEN 1 ELSE 0 END) as removes
			FROM {$table}
			WHERE created_at >= %s
			GROUP BY DATE(created_at)
			ORDER BY date ASC",
			$since
		) );

		// Fill in missing dates with zeros
		$data   = array();
		$map    = array();
		foreach ( $rows as $row ) {
			$map[ $row->date ] = array( 'adds' => (int) $row->adds, 'removes' => (int) $row->removes );
		}

		for ( $i = $days - 1; $i >= 0; $i-- ) {
			$date = gmdate( 'Y-m-d', strtotime( "-{$i} days" ) );
			$data[] = array(
				'date'    => $date,
				'adds'    => isset( $map[ $date ] ) ? $map[ $date ]['adds'] : 0,
				'removes' => isset( $map[ $date ] ) ? $map[ $date ]['removes'] : 0,
			);
		}

		return $data;
	}

	/**
	 * Recent activity feed.
	 */
	private function get_recent_activity( $limit = 20 ) {
		global $wpdb;
		$table = self::get_table_name();

		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$table} ORDER BY created_at DESC LIMIT %d",
			$limit
		) );

		$activity = array();
		foreach ( $rows as $row ) {
			$product = wc_get_product( $row->product_id );
			$user    = $row->user_id > 0 ? get_userdata( $row->user_id ) : null;

			$activity[] = array(
				'product_id'   => (int) $row->product_id,
				'product_name' => $product ? $product->get_name() : 'Unknown Product',
				'product_url'  => $product ? get_permalink( $row->product_id ) : '#',
				'action'       => $row->action,
				'user'         => $user ? $user->display_name : 'Guest',
				'user_id'      => (int) $row->user_id,
				'time'         => human_time_diff( strtotime( $row->created_at ), current_time( 'timestamp' ) ) . ' ago',
				'time_raw'     => $row->created_at,
			);
		}

		return $activity;
	}

	/**
	 * User engagement stats.
	 */
	private function get_user_stats( $days ) {
		global $wpdb;
		$table = self::get_table_name();
		$since = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		// Top users by wishlist activity
		$top_users = $wpdb->get_results( $wpdb->prepare(
			"SELECT user_id, COUNT(*) as total_actions,
				SUM(CASE WHEN action='add' THEN 1 ELSE 0 END) as adds
			FROM {$table}
			WHERE created_at >= %s AND user_id > 0
			GROUP BY user_id
			ORDER BY adds DESC
			LIMIT 5",
			$since
		) );

		$users = array();
		foreach ( $top_users as $row ) {
			$user = get_userdata( $row->user_id );
			if ( ! $user ) continue;
			$users[] = array(
				'user_id'  => (int) $row->user_id,
				'name'     => $user->display_name,
				'email'    => $user->user_email,
				'adds'     => (int) $row->adds,
				'actions'  => (int) $row->total_actions,
			);
		}

		$logged_in_adds = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table} WHERE action='add' AND created_at >= %s AND user_id > 0", $since
		) );
		$guest_adds = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table} WHERE action='add' AND created_at >= %s AND user_id = 0", $since
		) );

		return array(
			'top_users'      => $users,
			'logged_in_adds' => $logged_in_adds,
			'guest_adds'     => $guest_adds,
		);
	}

	/**
	 * Conversion data: products wishlisted that are also purchased.
	 */
	private function get_conversion_data( $days ) {
		global $wpdb;
		$table = self::get_table_name();
		$since = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		// Products with most wishlist adds that are out of stock (opportunity)
		$out_of_stock = $wpdb->get_results( $wpdb->prepare(
			"SELECT e.product_id, COUNT(*) as wishlist_count
			FROM {$table} e
			WHERE e.action = 'add' AND e.created_at >= %s
			GROUP BY e.product_id
			ORDER BY wishlist_count DESC
			LIMIT 5",
			$since
		) );

		$opportunities = array();
		foreach ( $out_of_stock as $row ) {
			$product = wc_get_product( $row->product_id );
			if ( ! $product || $product->is_in_stock() ) continue;
			$opportunities[] = array(
				'id'             => (int) $row->product_id,
				'name'           => $product->get_name(),
				'wishlist_count' => (int) $row->wishlist_count,
				'url'            => get_edit_post_link( $row->product_id ),
			);
		}

		return array(
			'out_of_stock_opportunities' => $opportunities,
		);
	}

	/**
	 * AJAX: Clear analytics data.
	 */
	public function clear_analytics() {
		if ( ! isset( $_POST['nonce'] ) || ! wp_verify_nonce( sanitize_text_field( wp_unslash( $_POST['nonce'] ) ), 'proddisp_admin_nonce' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Security check failed', 'product-display' ) ) );
		}
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Insufficient permissions', 'product-display' ) ) );
		}
		if ( ! PRODDISP_Loader::is_pro_active() ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Pro version required', 'product-display' ), 'pro_required' => true ) );
		}

		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Table name is a trusted, pre-escaped constant from $wpdb->prefix.
		$wpdb->query( 'TRUNCATE TABLE ' . self::get_table_name() );
		wp_send_json_success( array( 'message' => esc_html__( 'Analytics data cleared.', 'product-display' ) ) );
	}
}
