<?php
/**
 * File Path: includes/data/class-mbs-buy-link-clicks-dao.php
 *
 * Buy Link Clicks DAO
 *
 * CRUD and reporting operations for the mbs_buy_link_clicks table.
 * Table created by MBS_Migration_4_2. Extracted from
 * MBS_Buy_Link_Router (log_click) and MBS_Buy_Link_Analytics
 * (get_report_data) during RF-2 DAO boundary enforcement.
 *
 * @package MyBookShowroom
 * @since   1.2.0
 */

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

/**
 * Class MBS_Buy_Link_Clicks_DAO
 *
 * @since 1.2.0
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_Buy_Link_Clicks_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	// ── Write operations ────────────────────────────────────────────────────

	/**
	 * Record a buy-link click.
	 *
	 * Called by MBS_Buy_Link_Router on every PRO-tier redirect. Inserts one
	 * row into mbs_buy_link_clicks with a UTC timestamp.
	 *
	 * @since  1.2.0
	 * @param  int    $link_id      ID of the mbs_buy_links row that was clicked.
	 * @param  int    $post_id      Book post ID the link belongs to.
	 * @param  string $retailer_key Retailer slug (sanitized, max 50 chars).
	 * @param  string $referrer_host Parsed hostname of the HTTP Referer, or ''.
	 * @return void
	 */
	public static function log_click(
		int    $link_id,
		int    $post_id,
		string $retailer_key,
		string $referrer_host
	): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert(
			$wpdb->prefix . 'mbs_buy_link_clicks',
			array(
				'link_id'       => $link_id,
				'post_id'       => $post_id,
				'retailer_key'  => $retailer_key,
				'referrer_host' => $referrer_host,
				'clicked_at'    => current_time( 'mysql', true ),
			),
			array( '%d', '%d', '%s', '%s', '%s' )
		);
	}

	// ── Read / reporting operations ──────────────────────────────────────────

	/**
	 * Return click data grouped by buy-link for the analytics report.
	 *
	 * Returns one row per link_id/post_id/retailer_key combination with:
	 *   - total_clicks  : all-time count
	 *   - clicks_30d    : count in the last 30 days (UTC)
	 *   - last_click    : timestamp of the most recent click
	 *
	 * When $range is '30d' or '7d' the WHERE clause filters rows so only
	 * clicks within that window are counted at all. For 'all', no WHERE is
	 * applied and the 30-day sub-count comes from a CASE expression.
	 *
	 * UTC timestamps: gmdate() for the PHP boundary; UTC_TIMESTAMP() in SQL.
	 * Both were fixed in Sprint 174 (A-002) and must be preserved verbatim.
	 *
	 * @since  1.2.0
	 * @param  string $range Date range key: 'all' | '30d' | '7d'.
	 * @return object[]      Result rows from $wpdb->get_results().
	 */
	public static function get_report_data( string $range ): array {
		global $wpdb;

		$clicks = $wpdb->prefix . 'mbs_buy_link_clicks';
		// V7-C-016: $links ($wpdb->prefix.'mbs_buy_links') removed -- unused in the query.

		$where = '';
		if ( '30d' === $range ) {
			$where = "WHERE c.clicked_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)";
		} elseif ( '7d' === $range ) {
			$where = "WHERE c.clicked_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 DAY)";
		}

		// Sprint 174 A-002: gmdate() (not date()) ensures UTC boundary regardless
		// of server local timezone. UTC_TIMESTAMP() in SQL matches the column timezone.
		$thirty_ago = gmdate( 'Y-m-d H:i:s', strtotime( '-30 days' ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table names from $wpdb->prefix; dates via strtotime.
		$rows = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT
			    c.link_id,
			    c.post_id,
			    c.retailer_key,
			    COUNT(*)                                                  AS total_clicks,
			    SUM( CASE WHEN c.clicked_at >= %s THEN 1 ELSE 0 END )    AS clicks_30d,
			    MAX(c.clicked_at)                                         AS last_click
			   FROM {$clicks} c
			  {$where}
			  GROUP BY c.link_id, c.post_id, c.retailer_key
			  ORDER BY total_clicks DESC",
			$thirty_ago
		) );

		return $rows ?: array();
	}
}
