<?php
/**
 * @package Hafenstudios\Wellenbrecher
 */

namespace Hafenstudios\Wellenbrecher\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Zugriff auf hs_spam_stats (Langzeit-Tageszaehler ohne Personenbezug).
 * Stabile API fuer die Statistik-Seite (W3) und spaetere Kompass-Kacheln
 * (Bauplan Kap. 10).
 */
class StatsRepository {

	/**
	 * Tabellenname.
	 *
	 * @return string
	 */
	public function table(): string {
		return Schema::table( 'spam_stats' );
	}

	/**
	 * Zaehlt einen Tageszaehler hoch (legt die Zeile bei Bedarf an).
	 *
	 * @param string      $channel Kanal.
	 * @param string      $verdict Verdict (blocked|quarantined|flagged).
	 * @param string|null $date    Datum Y-m-d (Default: heute UTC).
	 * @return void
	 */
	public function increment( string $channel, string $verdict, ?string $date = null ): void {
		global $wpdb;

		$date  = $date ?? gmdate( 'Y-m-d' );
		$table = $this->table();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Tabellenname aus eigener Definition, Werte prepared.
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$table} (stat_date, channel, verdict, cnt) VALUES (%s, %s, %s, 1)
				ON DUPLICATE KEY UPDATE cnt = cnt + 1",
				$date,
				$channel,
				$verdict
			)
		);
	}

	/**
	 * Aggregierte Zahlen der letzten N Tage: Gesamtsummen je Verdict plus
	 * Aufschluesselung je Kanal.
	 *
	 * @param int         $days Zeitraum in Tagen (heute eingeschlossen).
	 * @param string|null $today Bezugsdatum Y-m-d (Default: heute UTC, fuer Tests).
	 * @return array{totals: array<string, int>, channels: array<string, array<string, int>>, days: int}
	 */
	public function summary( int $days = 7, ?string $today = null ): array {
		global $wpdb;

		$days  = max( 1, $days );
		$today = $today ?? gmdate( 'Y-m-d' );
		$from  = gmdate( 'Y-m-d', strtotime( $today . ' UTC' ) - ( ( $days - 1 ) * DAY_IN_SECONDS ) );
		$table = $this->table();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Tabellenname aus eigener Definition, Wert prepared.
		$rows = (array) $wpdb->get_results(
			$wpdb->prepare( "SELECT channel, verdict, SUM(cnt) AS cnt FROM {$table} WHERE stat_date >= %s GROUP BY channel, verdict", $from ),
			ARRAY_A
		);

		$totals   = array();
		$channels = array();
		foreach ( $rows as $row ) {
			$channel = (string) ( $row['channel'] ?? '' );
			$verdict = (string) ( $row['verdict'] ?? '' );
			$cnt     = (int) ( $row['cnt'] ?? 0 );

			$totals[ $verdict ]               = ( $totals[ $verdict ] ?? 0 ) + $cnt;
			$channels[ $channel ][ $verdict ] = ( $channels[ $channel ][ $verdict ] ?? 0 ) + $cnt;
		}

		return array(
			'totals'   => $totals,
			'channels' => $channels,
			'days'     => $days,
		);
	}

	/**
	 * Roh-Tageszeilen der letzten N Tage (fuer den CSV-Export der Pro-Reports).
	 * Aggregiert und ohne Personenbezug (die Tabelle enthaelt nur Datum, Kanal,
	 * Verdict, Anzahl).
	 *
	 * @param int         $days  Zeitraum in Tagen (heute eingeschlossen).
	 * @param string|null $today Bezugsdatum Y-m-d (Default: heute UTC, fuer Tests).
	 * @return array<int, array<string, mixed>>
	 */
	public function rows( int $days = 30, ?string $today = null ): array {
		global $wpdb;

		$days  = max( 1, $days );
		$today = $today ?? gmdate( 'Y-m-d' );
		$from  = gmdate( 'Y-m-d', strtotime( $today . ' UTC' ) - ( ( $days - 1 ) * DAY_IN_SECONDS ) );
		$table = $this->table();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Tabellenname aus eigener Definition, Wert prepared.
		return (array) $wpdb->get_results(
			$wpdb->prepare( "SELECT stat_date, channel, verdict, cnt FROM {$table} WHERE stat_date >= %s ORDER BY stat_date DESC, channel ASC, verdict ASC", $from ),
			ARRAY_A
		);
	}
}
