<?php
/**
 * Analytics Events Table.
 *
 * Manages the preco_analytics_events database table.
 * Stores daily aggregated user interaction counts and sales by page and event type.
 *
 * Table structure:
 *   date  (date)              — Day of the event in Y-m-d format.
 *   page  (tinyint)           — Page where the event occurred (see PAGE_* constants).
 *   type  (tinyint)           — Event type (see TYPE_* constants).
 *   value (decimal 14,2)      — Item count for add-to-cart/order-placed; sales amount (line total excl. tax) for purchased.
 *
 * The (date, page, type) triplet is unique — duplicate records are automatically
 * merged via INSERT … ON DUPLICATE KEY UPDATE value = value + new_value. Negative
 * values are supported (e.g. reversing a purchase total on refund).
 *
 * @package PRECO
 * @since 1.0.0
 */

namespace PRECO\Includes\Analytics;

defined( 'ABSPATH' ) || exit;

/**
 * AnalyticsTable class.
 */
class AnalyticsTable {

	// -------------------------------------------------------------------------
	// Table identifier
	// -------------------------------------------------------------------------

	const TABLE_NAME = 'preco_analytics_events';

	// -------------------------------------------------------------------------
	// Page constants
	// -------------------------------------------------------------------------

	const PAGE_PRODUCT      = 1;
	const PAGE_SHOP         = 2;
	const PAGE_ARCHIVE      = 3;
	const PAGE_CART         = 4;
	const PAGE_CHECKOUT     = 5;
	const PAGE_THANKYOU     = 6;
	const PAGE_POPUP_UPSELL = 7;
	const PAGE_ORDER_BUMP   = 8;

	// -------------------------------------------------------------------------
	// Event type constants
	// -------------------------------------------------------------------------

	const TYPE_ADD_TO_CART  = 1;
	const TYPE_ORDER_PLACED = 2;
	const TYPE_PURCHASED    = 3;

	// -------------------------------------------------------------------------
	// Helpers
	// -------------------------------------------------------------------------

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

	/**
	 * Map a page-source slug to its integer constant.
	 * Falls back to auto-detecting the WooCommerce page when no slug is given.
	 *
	 * @param  string $slug  e.g. 'product-page', 'shop-page', etc.
	 * @return int           One of the PAGE_* constants.
	 */
	public static function resolve_page( $slug ) {
		$map = array(
			'product-page'    => self::PAGE_PRODUCT,
			'shop-page'       => self::PAGE_SHOP,
			'product-archive' => self::PAGE_ARCHIVE,
			'cart'            => self::PAGE_CART,
			'checkout'        => self::PAGE_CHECKOUT,
			'thank-you'       => self::PAGE_THANKYOU,
			'popup-upsell'    => self::PAGE_POPUP_UPSELL,
			'order-bump'      => self::PAGE_ORDER_BUMP,
		);

		if ( ! empty( $slug ) && isset( $map[ $slug ] ) ) {
			return $map[ $slug ];
		}

		// Auto-detect from the current WooCommerce page context.
		if ( function_exists( 'is_product' ) && is_product() ) {
			return self::PAGE_PRODUCT;
		}
		if ( function_exists( 'is_shop' ) && is_shop() ) {
			return self::PAGE_SHOP;
		}
		if ( function_exists( 'is_product_category' ) && ( is_product_category() || is_product_tag() ) ) {
			return self::PAGE_ARCHIVE;
		}
		if ( function_exists( 'is_cart' ) && is_cart() ) {
			return self::PAGE_CART;
		}
		if ( function_exists( 'is_checkout' ) && is_checkout() ) {
			return self::PAGE_CHECKOUT;
		}

		return self::PAGE_PRODUCT; // safe fallback.
	}

	/**
	 * Return human-readable labels for page and type integers.
	 *
	 * @return array{pages: array<int,string>, types: array<int,string>}
	 */
	public static function get_labels() {
		return array(
			'pages' => array(
				self::PAGE_PRODUCT      => 'product-page',
				self::PAGE_SHOP         => 'shop-page',
				self::PAGE_ARCHIVE      => 'product-archive',
				self::PAGE_CART         => 'cart',
				self::PAGE_CHECKOUT     => 'checkout',
				self::PAGE_THANKYOU     => 'thank-you',
				self::PAGE_POPUP_UPSELL => 'popup-upsell',
				self::PAGE_ORDER_BUMP   => 'order-bump',
			),
			'types' => array(
				self::TYPE_ADD_TO_CART  => 'add-to-cart',
				self::TYPE_ORDER_PLACED => 'order-placed',
				self::TYPE_PURCHASED    => 'purchased',
			),
		);
	}

	// -------------------------------------------------------------------------
	// Schema management
	// -------------------------------------------------------------------------

	/**
	 * Create (or upgrade) the analytics events table.
	 * Called on plugin activation.
	 *
	 * Design notes:
	 *  - `date` stores only the calendar day (DATE type) — keeps rows compact and
	 *    makes GROUP BY date fast with no truncation needed.
	 *  - UNIQUE KEY on (date, page, type) enables atomic upserts.
	 *  - Separate indexes on `date` and `type` support common range / filter queries.
	 *  - `value` is DECIMAL(14,2) — safely holds both counts (integers) and
	 *    monetary totals (2 decimal places, up to ~999 billion).
	 *
	 * @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} (
			date  date              NOT NULL                    COMMENT 'Event date (Y-m-d)',
			page  tinyint(3) unsigned NOT NULL                  COMMENT '1=product-page 2=shop-page 3=product-archive 4=cart 5=checkout 6=thank-you 7=popup-upsell 8=order-bump',
			type  tinyint(3) unsigned NOT NULL                  COMMENT '1=add-to-cart 2=order-placed 3=purchased',
			value decimal(14,2)    NOT NULL DEFAULT '0.00'      COMMENT 'Count for add-to-cart order-placed sales total for purchased',
			PRIMARY KEY  (date, page, type),
			KEY          idx_date (date),
			KEY          idx_type (type)
		) {$charset_collate};";

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

		// Only mark as created once the table actually exists.
		// dbDelta() fails silently, so we verify with SHOW TABLES before setting the option.
		// 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_analytics_events_table_version', '1.0.0' );
		}
	}

	// -------------------------------------------------------------------------
	// Write
	// -------------------------------------------------------------------------

	/**
	 * Record an analytics event for today (or a given date).
	 *
	 * If a row with the same (date, page, type) already exists its value is
	 * incremented atomically — no separate SELECT needed. A negative value
	 * decrements the row (e.g. reversing a purchase total on refund) and is
	 * only accepted for TYPE_PURCHASED — add-to-cart/order-placed are plain
	 * counters and reject negative deltas.
	 *
	 * @param  int       $page   One of the PAGE_* constants.
	 * @param  int       $type   One of the TYPE_* constants.
	 * @param  float|int $value  Item count (add-to-cart/order-placed) or sales amount excl. tax (purchased).
	 *                           Negative values subtract but are only allowed when $type is TYPE_PURCHASED.
	 * @param  string    $date   Date string Y-m-d. Defaults to today (UTC).
	 * @return bool               True on success.
	 */
	public static function record( $page, $type, $value, $date = '' ) {
		global $wpdb;

		$page  = absint( $page );
		$type  = absint( $type );
		$value = (float) $value;
		$date  = $date ? sanitize_text_field( $date ) : gmdate( 'Y-m-d' );

		if ( 0.0 === $value ) {
			return false;
		}

		$valid_pages = array( self::PAGE_PRODUCT, self::PAGE_SHOP, self::PAGE_ARCHIVE, self::PAGE_CART, self::PAGE_CHECKOUT, self::PAGE_THANKYOU, self::PAGE_POPUP_UPSELL, self::PAGE_ORDER_BUMP );
		$valid_types = array( self::TYPE_ADD_TO_CART, self::TYPE_ORDER_PLACED, self::TYPE_PURCHASED );

		if ( ! in_array( $page, $valid_pages, true ) || ! in_array( $type, $valid_types, true ) ) {
			return false;
		}

		// Only purchased totals may be decremented (refund reversal) — add-to-cart and
		// order-placed are plain counters and should never go negative.
		if ( $value < 0 && self::TYPE_PURCHASED !== $type ) {
			return false;
		}

		if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ) {
			return false;
		}

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

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$result = $wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$table} (date, page, type, value)
				VALUES (%s, %d, %d, %f)
				ON DUPLICATE KEY UPDATE value = value + VALUES(value)",
				$date,
				$page,
				$type,
				$value
			)
		);
		// phpcs:enable

		return false !== $result;
	}

	// -------------------------------------------------------------------------
	// Read
	// -------------------------------------------------------------------------

	/**
	 * Return every row in the table with human-readable page/type labels.
	 *
	 * No date filtering is applied — all historical data is returned so that
	 * the JS layer can perform client-side filtering.
	 *
	 * Each row shape:
	 *   date  (string)  Y-m-d
	 *   page  (string)  e.g. 'product-page'
	 *   type  (string)  e.g. 'order-placed'
	 *   value (float)   item count or sales total
	 *
	 * @return array<int, array{date: string, page: string, type: string, value: float}>
	 */
	public static function get_all_raw() {
		global $wpdb;

		$table  = esc_sql( self::get_table_name() );
		$labels = self::get_labels();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$results = $wpdb->get_results(
			"SELECT date, page, type, value FROM {$table} ORDER BY date ASC, page ASC, type ASC",
			ARRAY_A
		);
		// phpcs:enable

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

		foreach ( $results as &$row ) {
			$page_int     = (int) $row['page'];
			$type_int     = (int) $row['type'];
			$row['page']  = $labels['pages'][ $page_int ] ?? $row['page'];
			$row['type']  = $labels['types'][ $type_int ] ?? $row['type'];
			$row['value'] = (float) $row['value'];
		}
		unset( $row );

		return $results;
	}
}
