<?php
/**
 * Handles all direct database interactions for the analytics module.
 *
 * @package    Ai_Try_N_Buy
 * @subpackage Ai_Try_N_Buy/analytics
 * @author     Edu Brazeal <contact@edubrazeal.com>
 */
class Aitnb_Database_Manager {

	/**
	 * The WordPress database access object.
	 *
	 * @since    1.0.0
	 * @access   private
	 * @var      wpdb    $wpdb
	 */
	private $wpdb;

	/**
	 * The name of the custom analytics table.
	 *
	 * @since    1.0.0
	 * @access   private
	 * @var      string    $table_name
	 */
	private $table_name;

	/**
	 * Constructor. Initializes the database object and table name.
	 *
	 * @since    1.0.0
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb       = $wpdb;
		$this->table_name = $this->wpdb->prefix . 'aitnb_analytics';
	}

	/**
	 * Retrieves the total count of 'try' vs 'purchase' events.
	 *
	 * @since    1.0.0
	 * @param    string    $period    The time period to filter by (e.g., 'all_time').
	 * @return   array|false          An array with counts for 'tries' and 'purchases', or false on error.
	 */
	public function get_conversion_overview_data( $period = 'all_time' ) {
		$date_clause = $this->get_date_clause( $period );

		if ( ! empty( $date_clause['params'] ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sql = $this->wpdb->prepare(
				"SELECT event_type, COUNT(id) as event_count
				FROM {$this->table_name}
				WHERE event_timestamp >= %s
				GROUP BY event_type",
				$date_clause['params'][0]
			);
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sql = "SELECT event_type, COUNT(id) as event_count
				FROM {$this->table_name}
				GROUP BY event_type";
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$results = $this->wpdb->get_results( $sql, ARRAY_A );

		if ( $this->wpdb->last_error ) {
			// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
			error_log( 'AITNB Database Error in get_conversion_overview_data: ' . $this->wpdb->last_error );
			return false;
		}

		$overview = array(
			'tries'     => 0,
			'purchases' => 0,
		);

		if ( $results && is_array( $results ) ) {
			foreach ( $results as $row ) {
				if ( 'try' === $row['event_type'] ) {
					$overview['tries'] = (int) $row['event_count'];
				} elseif ( 'purchase' === $row['event_type'] ) {
					$overview['purchases'] = (int) $row['event_count'];
				}
			}
		}

		return $overview;
	}

	/**
	 * Retrieves the top products based on a specific event type.
	 *
	 * @since    1.0.0
	 * @param    string    $event_type    The event type ('try' or 'purchase').
	 * @param    string    $period        The time period.
	 * @param    int       $limit         The number of products to return.
	 * @return   array|false              A list of top products, or false on error.
	 */
	public function get_top_products_data( $event_type, $period = 'all_time', $limit = 5 ) {
		$date_clause = $this->get_date_clause( $period );
		
		if ( ! in_array( $event_type, array( 'try', 'purchase' ), true ) ) {
			return false;
		}

		$limit = absint( $limit );
		if ( $limit <= 0 ) {
			$limit = 5;
		}

		if ( ! empty( $date_clause['params'] ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sql = $this->wpdb->prepare(
				"SELECT
					p.ID as product_id,
					p.post_title as product_name,
					COUNT(t.id) as event_count
				FROM
					{$this->table_name} as t
				JOIN
					{$this->wpdb->posts} as p ON t.product_id = p.ID
				WHERE
					t.event_type = %s
					AND t.event_timestamp >= %s
					AND p.post_status = 'publish'
				GROUP BY
					t.product_id
				ORDER BY
					event_count DESC
				LIMIT %d",
				$event_type,
				$date_clause['params'][0],
				$limit
			);
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sql = $this->wpdb->prepare(
				"SELECT
					p.ID as product_id,
					p.post_title as product_name,
					COUNT(t.id) as event_count
				FROM
					{$this->table_name} as t
				JOIN
					{$this->wpdb->posts} as p ON t.product_id = p.ID
				WHERE
					t.event_type = %s
					AND p.post_status = 'publish'
				GROUP BY
					t.product_id
				ORDER BY
					event_count DESC
				LIMIT %d",
				$event_type,
				$limit
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$results = $this->wpdb->get_results( $sql, ARRAY_A );

		if ( $this->wpdb->last_error ) {
			// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
			error_log( 'AITNB Database Error in get_top_products_data: ' . $this->wpdb->last_error );
			return false;
		}

		if ( ! $results ) {
			return array();
		}

		return $results;
	}

	/**
	 * Helper function to generate a date clause for SQL queries.
	 *
	 * @since    1.0.0
	 * @access   private
	 * @param    string    $period    The period ('last_7_days', 'last_30_days', 'all_time').
	 * @return   array                An array containing the SQL clause and its parameters.
	 */
	private function get_date_clause( $period ) {
		$clause = '1=1';
		$params = array();

		switch ( $period ) {
			case 'last_7_days':
				$start_date = gmdate( 'Y-m-d H:i:s', strtotime( '-7 days' ) );
				$clause     = 'event_timestamp >= %s';
				$params[]   = $start_date;
				break;
			case 'last_30_days':
				$start_date = gmdate( 'Y-m-d H:i:s', strtotime( '-30 days' ) );
				$clause     = 'event_timestamp >= %s';
				$params[]   = $start_date;
				break;
			case 'all_time':
			default:
				break;
		}

		return array(
			'clause' => $clause,
			'params' => $params,
		);
	}

	/**
	 * Checks if the analytics table exists and has the correct structure.
	 *
	 * @since    1.0.0
	 * @return   bool    True if table exists and is valid, false otherwise.
	 */
	public function verify_table_exists() {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$table_exists = $this->wpdb->get_var(
			$this->wpdb->prepare( 'SHOW TABLES LIKE %s', $this->table_name )
		) === $this->table_name;

		return $table_exists;
	}

	/**
	 * Gets the total record count in the analytics table.
	 * Useful for debugging and verifying data is being tracked.
	 *
	 * @since    1.0.0
	 * @return   int|false    The number of records, or false on error.
	 */
	public function get_total_record_count() {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$count = $this->wpdb->get_var(
			"SELECT COUNT(id) FROM {$this->table_name}"
		);

		if ( $this->wpdb->last_error ) {
			// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
			error_log( 'AITNB Database Error in get_total_record_count: ' . $this->wpdb->last_error );
			return false;
		}

		return (int) $count;
	}
}