<?php
/**
 * User Events Table Handler.
 *
 * @package PRECO
 * @since 1.0.0
 */

namespace PRECO\Includes\Frontend\UserEvents;

defined( 'ABSPATH' ) || exit;

/**
 * User Events Table class.
 *
 * Manages the preco_user_events table for tracking user interactions.
 */
class UserEventsTable {

	/**
	 * Table name (without prefix).
	 *
	 * @var string
	 */
	const TABLE_NAME = 'preco_user_events';

	/**
	 * Event types.
	 */
	const EVENT_VIEW             = 1;
	const EVENT_ADD_TO_CART      = 2;
	const EVENT_REMOVE_FROM_CART = 3;

	/**
	 * Retention period in days.
	 *
	 * @var int
	 */
	const RETENTION_DAYS = 90;

	/**
	 * Rows deleted per DELETE statement in cleanup_old_records(), and the
	 * max number of such batches run per invocation — see that method for
	 * why deletes are chunked instead of one unbounded DELETE.
	 *
	 * @var int
	 */
	const CLEANUP_BATCH_SIZE  = 1000;
	const CLEANUP_MAX_BATCHES = 50;

	/**
	 * Get the full table name with prefix.
	 *
	 * @return string
	 */
	public static function get_table_name() {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_NAME;
	}

	/**
	 * Create the table on plugin activation.
	 *
	 * @return void
	 */
	public static function create_table() {
		global $wpdb;

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

		$sql = "CREATE TABLE {$table_name} (
			user_id bigint(20) NOT NULL DEFAULT 0,
			product_id bigint(20) NOT NULL,
			event_type tinyint(1) NOT NULL COMMENT '1=view, 2=add_to_cart, 3=remove_from_cart',
			count int(11) NOT NULL DEFAULT 1,
			updated_at datetime NOT NULL,
			PRIMARY KEY (user_id, product_id, event_type),
			KEY idx_updated_at (updated_at),
			KEY idx_user_event (user_id, event_type),
			KEY idx_product_event (product_id, event_type),
			KEY idx_event_product (event_type, product_id)
		) {$charset_collate};";
		// Note: no standalone idx_product_id (product_id) index — it would be a
		// strict prefix of idx_product_event (product_id, event_type) above, so
		// the optimizer can already satisfy any product_id-only lookup from that
		// index's leftmost column. A separate index would only add B-tree
		// maintenance cost to every write for zero query benefit.
		// Sites whose table was created before this comment was added may still
		// have that now-redundant index from the original schema — dbDelta()
		// doesn't drop removed indexes, so it's left in place there rather than
		// being migrated live. See test-audit-2.md, finding §5-DB.

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

		// Only mark as created once the table actually exists.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) === $table_name ) {
			update_option( 'preco_user_events_table_version', '1.0.0' );
		}
	}

	/**
	 * Track an event.
	 *
	 * @param int $user_id    User ID (0 for guests).
	 * @param int $product_id Product ID.
	 * @param int $event_type Event type constant.
	 * @param int $count      Number of times to increment the count.
	 * @return bool Success status.
	 */
	public static function track_event( $user_id, $product_id, $event_type, $count = 1 ) {
		global $wpdb;

		$user_id    = absint( $user_id );
		$product_id = absint( $product_id );
		$event_type = absint( $event_type );
		$count      = max( 1, absint( $count ) );

		if ( ! $product_id || ! in_array( $event_type, array( self::EVENT_VIEW, self::EVENT_ADD_TO_CART, self::EVENT_REMOVE_FROM_CART ), true ) ) {
			return false;
		}

		$table_name = esc_sql( self::get_table_name() );
		$now        = current_time( 'mysql', true );

		// Use INSERT ... ON DUPLICATE KEY UPDATE for atomic operation.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$result = $wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$table_name} (user_id, product_id, event_type, count, updated_at)
				VALUES (%d, %d, %d, %d, %s)
				ON DUPLICATE KEY UPDATE
					count = count + %d,
					updated_at = %s",
				$user_id,
				$product_id,
				$event_type,
				$count,
				$now,
				$count,
				$now
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return false !== $result;
	}

	/**
	 * Cleanup old records based on retention policy.
	 *
	 * Deletes in small batches (CLEANUP_BATCH_SIZE rows per statement)
	 * instead of one unbounded DELETE: on a high-traffic store this table
	 * can grow into the millions of rows, and a single unbatched DELETE
	 * against a large, actively-written table can hold row locks and
	 * generate a large single transaction/binlog entry. Capped at
	 * CLEANUP_MAX_BATCHES per invocation so a very large backlog (e.g. the
	 * first run after upgrading to this batched version) can't turn one
	 * daily cron tick into an indefinitely long-running request — each
	 * batch is its own committed statement, so if a run stops early
	 * (execution-time limit, etc.) no progress is lost and the remainder
	 * is picked up by the next scheduled run.
	 *
	 * @return int Number of deleted rows.
	 */
	public static function cleanup_old_records() {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );
		$cutoff     = gmdate( 'Y-m-d H:i:s', strtotime( '-' . self::RETENTION_DAYS . ' days' ) );
		$total      = 0;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		for ( $batch = 0; $batch < self::CLEANUP_MAX_BATCHES; $batch++ ) {
			$deleted = $wpdb->query(
				$wpdb->prepare(
					"DELETE FROM {$table_name} WHERE updated_at < %s LIMIT %d",
					$cutoff,
					self::CLEANUP_BATCH_SIZE
				)
			);

			if ( ! $deleted ) {
				break;
			}

			$total += $deleted;

			if ( $deleted < self::CLEANUP_BATCH_SIZE ) {
				break;
			}
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return $total;
	}

	/**
	 * Get event count for a user/product/event combination.
	 *
	 * @param int $user_id    User ID.
	 * @param int $product_id Product ID.
	 * @param int $event_type Event type.
	 * @return int Event count.
	 */
	public static function get_event_count( $user_id, $product_id, $event_type ) {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$count = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT count FROM {$table_name}
				WHERE user_id = %d AND product_id = %d AND event_type = %d",
				$user_id,
				$product_id,
				$event_type
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return $count ? (int) $count : 0;
	}

	/**
	 * Get products for a user by event type and time period.
	 *
	 * @param int    $user_id    User ID.
	 * @param int    $event_type Event type.
	 * @param string $period     Time period (e.g., '7 days', '30 days', 'alltime').
	 * @return array Product IDs.
	 */
	public static function get_user_products( $user_id, $event_type, $period = 'alltime' ) {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );
		$user_id    = absint( $user_id );
		$event_type = absint( $event_type );

		$where = $wpdb->prepare( 'WHERE user_id = %d AND event_type = %d', $user_id, $event_type );

		if ( 'alltime' !== $period ) {
			$cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-' . $period ) );
			$where .= $wpdb->prepare( ' AND updated_at >= %s', $cutoff );
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$results = $wpdb->get_col(
			"SELECT product_id FROM {$table_name} {$where} ORDER BY updated_at DESC"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return $results ? array_map( 'intval', $results ) : array();
	}
}
