<?php
/**
 * Daily delivery counters.
 *
 * @package AdPresso
 */

namespace AdPresso\Infrastructure;

use DateTimeImmutable;

defined( 'ABSPATH' ) || exit;

/**
 * Site-wide daily counters of what was delivered: one row per day, placement and metric, incremented atomically.
 * Placement 0 holds page-level metrics (AdSense delivery); the Pro fallback counters use the placement ID.
 */
class DeliveryCounters {

	public const TABLE = 'adpresso_delivery';

	/** Rows older than this are deleted. */
	public const KEEP_DAYS = 90;

	private const PRUNE_TRANSIENT = 'adpresso_delivery_pruned';

	/**
	 * Full table name.
	 *
	 * @return string
	 */
	public static function table_name(): string {
		global $wpdb;

		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * Creates or updates the table (dbDelta is idempotent).
	 *
	 * @return void
	 */
	public static function create_table(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

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

		dbDelta(
			"CREATE TABLE {$table} (
            stat_date DATE NOT NULL,
            placement_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            metric VARCHAR(32) NOT NULL,
            hits INT(10) UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY  (stat_date, placement_id, metric)
        ) {$charset_collate};"
		);
	}

	/**
	 * Adds hits for today.
	 *
	 * @param array<string, int|bool> $metrics      Metric => hits (true counts as one).
	 * @param int                     $placement_id Placement, 0 for page-level metrics.
	 *
	 * @return void
	 */
	public static function record( array $metrics, int $placement_id = 0 ): void {
		global $wpdb;

		$date   = current_time( 'Y-m-d' );
		$values = [];
		foreach ( $metrics as $metric => $hits ) {
			$metric = sanitize_key( (string) $metric );
			$hits   = true === $hits ? 1 : (int) $hits;
			if ( '' === $metric || strlen( $metric ) > 32 || $hits < 1 ) {
				continue;
			}
			array_push( $values, $date, $placement_id, $metric, $hits );
		}
		if ( empty( $values ) ) {
			return;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $values ) / 4, '(%s, %d, %s, %d)' ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders -- Own table, placeholders built from the validated list; %i needs WP 6.2, the plugin needs 6.5.
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO %i (stat_date, placement_id, metric, hits) VALUES {$placeholders} ON DUPLICATE KEY UPDATE hits = hits + VALUES(hits)",
				self::table_name(),
				...$values
			)
		);
		// phpcs:enable

		self::maybe_prune();
	}

	/**
	 * Hits per metric between two days (inclusive) for one placement.
	 *
	 * @param string   $start        First day, Y-m-d.
	 * @param string   $end          Last day, Y-m-d.
	 * @param string[] $metrics      Metrics to sum; every one is present in the result.
	 * @param int      $placement_id Placement, 0 for page-level metrics.
	 *
	 * @return array<string, int>
	 */
	public static function totals( string $start, string $end, array $metrics, int $placement_id = 0 ): array {
		$summary = array_fill_keys( $metrics, 0 );
		foreach ( self::query_totals( $start, $end, $metrics, $placement_id ) as $row ) {
			if ( isset( $summary[ $row['metric'] ] ) ) {
				$summary[ $row['metric'] ] = (int) $row['hits'];
			}
		}

		return $summary;
	}

	/**
	 * Hits per placement and metric between two days (inclusive), page-level rows excluded.
	 *
	 * @param string   $start   First day, Y-m-d.
	 * @param string   $end     Last day, Y-m-d.
	 * @param string[] $metrics Metrics to sum.
	 *
	 * @return array<int, array<string, int>> Placement ID => metric => hits.
	 */
	public static function totals_per_placement( string $start, string $end, array $metrics ): array {
		$result = [];
		foreach ( self::query_totals( $start, $end, $metrics, null ) as $row ) {
			$result[ (int) $row['placement_id'] ][ $row['metric'] ] = (int) $row['hits'];
		}

		return $result;
	}

	/**
	 * First day of "the last $days days", today included.
	 *
	 * @param int $days Number of days.
	 *
	 * @return string
	 */
	public static function since( int $days ): string {
		return ( new DateTimeImmutable( current_time( 'Y-m-d' ) ) )->modify( '-' . max( 0, $days - 1 ) . ' days' )->format( 'Y-m-d' );
	}

	/**
	 * Sums the hits; one placement, or every placement but 0.
	 *
	 * @param string   $start        First day.
	 * @param string   $end          Last day.
	 * @param string[] $metrics      Metrics.
	 * @param int|null $placement_id One placement, null for all placements grouped.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	private static function query_totals( string $start, string $end, array $metrics, ?int $placement_id ): array {
		global $wpdb;

		$metrics = array_values( array_filter( array_map( static fn( $metric ) => sanitize_key( (string) $metric ), $metrics ) ) );
		if ( empty( $metrics ) ) {
			return [];
		}
		$in     = implode( ', ', array_fill( 0, count( $metrics ), '%s' ) );
		$scope  = null === $placement_id ? 'placement_id > 0' : 'placement_id = %d';
		$params = array_merge( [ self::table_name(), $start, $end ], null === $placement_id ? [] : [ $placement_id ], $metrics );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders -- Own table, placeholders built from the validated list; %i needs WP 6.2, the plugin needs 6.5.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT placement_id, metric, SUM(hits) AS hits FROM %i WHERE stat_date BETWEEN %s AND %s AND {$scope} AND metric IN ({$in}) GROUP BY placement_id, metric",
				...$params
			),
			ARRAY_A
		);
		// phpcs:enable

		return is_array( $rows ) ? $rows : [];
	}

	/**
	 * Deletes old rows, once a day.
	 *
	 * @return void
	 */
	private static function maybe_prune(): void {
		global $wpdb;

		if ( get_transient( self::PRUNE_TRANSIENT ) ) {
			return;
		}
		set_transient( self::PRUNE_TRANSIENT, 1, DAY_IN_SECONDS );

		$before = ( new DateTimeImmutable( current_time( 'Y-m-d' ) ) )->modify( '-' . self::KEEP_DAYS . ' days' )->format( 'Y-m-d' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder -- Own table; %i needs WP 6.2, the plugin needs 6.5.
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE stat_date < %s', self::table_name(), $before ) );
	}
}