<?php
/**
 * Audit Store.
 *
 * Owns the table that holds one scored row per post, so the worklist UI can
 * filter, sort, paginate, and export without recomputing on every page load.
 *
 * @package Platform\Helm\Modules\Content_Auditor
 */

declare( strict_types = 1 );

namespace Platform\Helm\Modules\Content_Auditor;

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

final class AuditStore {

	private const TABLE          = 'platform_helm_audit';
	private const SCHEMA_OPTION  = 'platform_helm_audit_schema_version';
	private const SCHEMA_VERSION = '1.1.0';

	public function table(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	public function maybe_create_table(): void {
		if ( self::SCHEMA_VERSION === (string) get_option( self::SCHEMA_OPTION, '' ) ) {
			return;
		}
		global $wpdb;
		$table   = $this->table();
		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			post_id bigint(20) unsigned NOT NULL,
			title varchar(255) NOT NULL DEFAULT '',
			url varchar(255) NOT NULL DEFAULT '',
			recommendation varchar(16) NOT NULL DEFAULT '',
			score int(10) unsigned NOT NULL DEFAULT 0,
			word_count int(10) unsigned NOT NULL DEFAULT 0,
			has_image tinyint(1) NOT NULL DEFAULT 0,
			has_meta tinyint(1) NOT NULL DEFAULT 0,
			handled tinyint(1) NOT NULL DEFAULT 0,
			age_days int(10) unsigned NOT NULL DEFAULT 0,
			clicks int(10) unsigned NOT NULL DEFAULT 0,
			impressions int(10) unsigned NOT NULL DEFAULT 0,
			ctr float NOT NULL DEFAULT 0,
			position float NOT NULL DEFAULT 0,
			reasons text NULL,
			updated_at datetime DEFAULT NULL,
			PRIMARY KEY  (post_id),
			KEY recommendation (recommendation),
			KEY score (score),
			KEY clicks (clicks)
		) {$charset};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
		update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false );
	}

	/**
	 * @param array<int,array<string,mixed>> $rows
	 */
	public function upsert( array $rows ): int {
		if ( empty( $rows ) ) {
			return 0;
		}
		global $wpdb;
		$table = $this->table();
		$now   = current_time( 'mysql' );
		$total = 0;

		foreach ( array_chunk( $rows, 100 ) as $chunk ) {
			$ph     = [];
			$values = [];
			foreach ( $chunk as $r ) {
				$ph[]     = '(%d,%s,%s,%s,%d,%d,%d,%d,%d,%d,%d,%f,%f,%s,%s)';
				$values[] = (int) $r['post_id'];
				$values[] = (string) $r['title'];
				$values[] = (string) $r['url'];
				$values[] = (string) $r['recommendation'];
				$values[] = (int) $r['score'];
				$values[] = (int) $r['word_count'];
				$values[] = (int) $r['has_image'];
				$values[] = (int) $r['has_meta'];
				$values[] = (int) $r['age_days'];
				$values[] = (int) $r['clicks'];
				$values[] = (int) $r['impressions'];
				$values[] = (float) $r['ctr'];
				$values[] = (float) $r['position'];
				$values[] = (string) $r['reasons'];
				$values[] = $now;
			}
			$sql = "INSERT INTO {$table}
				(post_id,title,url,recommendation,score,word_count,has_image,has_meta,age_days,clicks,impressions,ctr,position,reasons,updated_at)
				VALUES " . implode( ',', $ph ) . "
				ON DUPLICATE KEY UPDATE
					title=VALUES(title),url=VALUES(url),recommendation=VALUES(recommendation),score=VALUES(score),
					word_count=VALUES(word_count),has_image=VALUES(has_image),has_meta=VALUES(has_meta),age_days=VALUES(age_days),
					clicks=VALUES(clicks),impressions=VALUES(impressions),ctr=VALUES(ctr),position=VALUES(position),
					reasons=VALUES(reasons),updated_at=VALUES(updated_at)";
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( $wpdb->prepare( $sql, $values ) );
			$total += count( $chunk );
		}
		return $total;
	}

	/** @return array<string,int> recommendation => count */
	public function counts(): array {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( "SELECT recommendation, COUNT(*) c FROM {$table} GROUP BY recommendation", ARRAY_A );
		$out = [ AuditScorer::KEEP => 0, AuditScorer::REFRESH => 0, AuditScorer::MERGE => 0, AuditScorer::DELETE => 0 ];
		foreach ( (array) $rows as $r ) {
			$out[ (string) $r['recommendation'] ] = (int) $r['c'];
		}
		return $out;
	}

	public function count_rows(): int {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Paginated, filtered, sorted rows.
	 *
	 * @return array{rows:array<int,array<string,mixed>>,total:int}
	 */
	public function query( string $recommendation = '', string $orderby = 'impressions', string $order = 'desc', int $page = 1, int $per_page = 50, bool $hide_handled = false ): array {
		global $wpdb;
		$table = $this->table();

		$allowed_order = [ 'impressions', 'clicks', 'score', 'word_count', 'position', 'age_days' ];
		$orderby       = in_array( $orderby, $allowed_order, true ) ? $orderby : 'impressions';
		$order         = 'asc' === strtolower( $order ) ? 'ASC' : 'DESC';
		$per_page      = max( 10, min( 200, $per_page ) );
		$page          = max( 1, $page );
		$offset        = ( $page - 1 ) * $per_page;

		$conditions = [];
		$where_args = [];
		$valid_recs = [ AuditScorer::KEEP, AuditScorer::REFRESH, AuditScorer::MERGE, AuditScorer::DELETE ];
		if ( in_array( $recommendation, $valid_recs, true ) ) {
			$conditions[] = 'recommendation = %s';
			$where_args[] = $recommendation;
		}
		if ( $hide_handled ) {
			$conditions[] = 'handled = 0';
		}
		$where = $conditions ? ( 'WHERE ' . implode( ' AND ', $conditions ) ) : '';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$total = (int) ( empty( $where_args )
			? $wpdb->get_var( "SELECT COUNT(*) FROM {$table} {$where}" )
			: $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} {$where}", $where_args ) ) );

		$sql  = "SELECT * FROM {$table} {$where} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		$args = array_merge( $where_args, [ $per_page, $offset ] );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );

		return [ 'rows' => is_array( $rows ) ? $rows : [], 'total' => $total ];
	}

	/**
	 * Mark posts handled / not handled. Returns rows affected.
	 *
	 * @param array<int,int> $ids
	 */
	public function set_handled( array $ids, bool $handled ): int {
		$ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) );
		if ( empty( $ids ) ) {
			return 0;
		}
		global $wpdb;
		$table = $this->table();
		$ph    = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$args  = array_merge( [ $handled ? 1 : 0 ], $ids );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		return (int) $wpdb->query( $wpdb->prepare( "UPDATE {$table} SET handled = %d WHERE post_id IN ({$ph})", $args ) );
	}

	/** Stream all rows for export. @return array<int,array<string,mixed>> */
	public function all(): array {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( "SELECT * FROM {$table} ORDER BY recommendation, impressions DESC", ARRAY_A );
		return is_array( $rows ) ? $rows : [];
	}

	public function clear(): void {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( "TRUNCATE TABLE {$table}" );
	}
}
