<?php

namespace WPDRMS\ASP\AI\ORM;

use WPDRMS\PluginCore\ORM\Model;

if ( !defined( 'ABSPATH' ) ) {
	die( '-1' );
}

class AIInteraction extends Model {
	protected static string $table_name = 'asp_ai_interactions';
	protected static array $columns     = array(
		'id'                   => 'BIGINT(20) NOT NULL AUTO_INCREMENT',
		'feature'              => 'VARCHAR(50) NOT NULL',
		'prompt_summary'       => 'VARCHAR(120) NOT NULL',
		'tokens_used'          => 'INT NOT NULL DEFAULT 0',
		'cached'               => 'TINYINT(1) NOT NULL DEFAULT 0',
		'created_at'           => 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP',
		'INDEX idx_feature'    => '(feature)',
		'INDEX idx_created_at' => '(created_at)',
		'PRIMARY KEY'          => '(id)',
	);

	public int $id                = 0;
	public string $feature        = '';
	public string $prompt_summary = '';
	public int $tokens_used       = 0;
	public int $cached            = 0;
	public ?string $created_at    = null;

	/**
	 * Deletes interaction rows older than the retention window so the log table
	 * does not grow unbounded. Called from the daily prune cron (see AIHooks).
	 *
	 * @param int $keep_days Rows older than this many days are removed.
	 * @return int Number of rows deleted.
	 */
	public static function prune( int $keep_days = 90 ): int {
		global $wpdb;
		$table = self::getTableName();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
		return (int) $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM `{$table}` WHERE created_at < DATE_SUB( NOW(), INTERVAL %d DAY )", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$keep_days
			)
		);
	}

	/**
	 * Total row count, optionally scoped to a single feature. Used for log pagination.
	 *
	 * @param string $feature Feature slug, or '' for all features.
	 * @return int
	 */
	public static function countAll( string $feature = '' ): int {
		global $wpdb;
		$table = self::getTableName();
		if ( $feature !== '' ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE feature = %s", $feature ) );
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
	}

	/**
	 * Distinct feature slugs present in the log, for building filter UIs.
	 *
	 * @return string[]
	 */
	public static function distinctFeatures(): array {
		global $wpdb;
		$table = self::getTableName();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
		$rows = $wpdb->get_col( "SELECT DISTINCT feature FROM `{$table}` ORDER BY feature ASC" );
		return array_map( 'strval', is_array( $rows ) ? $rows : array() );
	}

	/**
	 * Token spend grouped by feature within a recent window, for the Usage breakdown.
	 *
	 * @param int $days Look-back window in days.
	 * @return array<int, array{feature: string, tokens: int, calls: int}>
	 */
	public static function tokenUsageByFeature( int $days = 30 ): array {
		global $wpdb;
		$table = self::getTableName();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT feature, SUM(tokens_used) AS tokens, COUNT(*) AS calls
				 FROM `{$table}`
				 WHERE created_at >= DATE_SUB( NOW(), INTERVAL %d DAY )
				 GROUP BY feature
				 ORDER BY tokens DESC",
				$days
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery

		return array_map(
			static function ( $r ): array {
				return array(
					'feature' => (string) $r['feature'],
					'tokens'  => (int) $r['tokens'],
					'calls'   => (int) $r['calls'],
				);
			},
			is_array( $rows ) ? $rows : array()
		);
	}
}
