<?php
/**
 * Event repository. The only class that touches the log table.
 *
 * @package Enrollment_Verifier_Logger
 */

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This repository is the only code that touches the plugin's own log table (no WordPress API exists for it). Reads are deliberately uncached: the admin screen and the duplicate check must see events the moment they are written.

/**
 * Persistence for log rows.
 */
class EVLFL_Logger {

	const MAX_PER_PAGE = 100;

	/**
	 * Allowed orderby columns.
	 *
	 * @return string[]
	 */
	public static function sortable_columns() {
		return array( 'event_time', 'action', 'status', 'source', 'id' );
	}

	/**
	 * Row counts by verification status, for the dashboard-style stat strip.
	 * Always reflects the whole table (not the current filters), so the
	 * numbers stay stable while someone narrows the list below them.
	 *
	 * @return array{total:int,valid:int,invalid:int,unknown:int}
	 */
	public function status_counts() {
		global $wpdb;
		$table  = EVLFL_Database::table_name();
		$counts = array(
			'total'   => 0,
			'valid'   => 0,
			'invalid' => 0,
			'unknown' => 0,
		);

		$rows = $wpdb->get_results( "SELECT status, COUNT(*) AS c FROM {$table} GROUP BY status", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix (identifiers cannot be placeholders before WP 6.2); the query has no user input.
		foreach ( (array) $rows as $row ) {
			$status = isset( $row['status'] ) ? (string) $row['status'] : '';
			$n      = (int) $row['c'];
			if ( isset( $counts[ $status ] ) ) {
				$counts[ $status ] = $n;
			}
			$counts['total'] += $n;
		}
		return $counts;
	}

	/**
	 * Insert a normalized event.
	 *
	 * @param array $event Output of EVLFL_Event::normalize().
	 * @return int Insert ID or 0.
	 */
	public function insert( array $event ) {
		global $wpdb;

		$metadata = wp_json_encode( isset( $event['metadata'] ) ? $event['metadata'] : array() );
		if ( ! is_string( $metadata ) || strlen( $metadata ) > 60000 ) {
			$metadata = wp_json_encode( array( 'truncated' => true ) );
		}

		$ok = $wpdb->insert(
			EVLFL_Database::table_name(),
			array(
				'user_id'             => $event['user_id'],
				'course_id'           => $event['course_id'],
				'action'              => $event['action'],
				'source'              => $event['source'],
				'event_trigger'       => $event['event_trigger'],
				'actor_type'          => $event['actor_type'],
				'actor_id'            => $event['actor_id'],
				'status'              => $event['status'],
				'verification_reason' => $event['verification_reason'],
				'confidence'          => $event['confidence'],
				'event_time'          => $event['event_time'],
				'metadata'            => $metadata,
				'created_at'          => gmdate( 'Y-m-d H:i:s' ),
			),
			array( '%d', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
		);
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Fetch one row.
	 *
	 * @param int $id Row ID.
	 * @return array|null
	 */
	public function get( $id ) {
		global $wpdb;
		$table = EVLFL_Database::table_name();
		$row   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $id ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $row ? $this->decode( $row ) : null;
	}

	/**
	 * Most recent event for a user/course pair (any action), used for idempotency.
	 *
	 * @param int $user_id   User.
	 * @param int $course_id Course.
	 * @return array|null Keys: action, event_time.
	 */
	public function get_latest_for_pair( $user_id, $course_id ) {
		global $wpdb;
		$table = EVLFL_Database::table_name();
		$row   = $wpdb->get_row(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix; every value is a placeholder.
			$wpdb->prepare( "SELECT action, event_time FROM {$table} WHERE user_id = %d AND course_id = %d ORDER BY event_time DESC, id DESC LIMIT 1", absint( $user_id ), absint( $course_id ) ),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * Paginated query. Never loads the whole table.
	 *
	 * @param array $args search, action, status, source, date_from, date_to (GMT datetimes), orderby, order, per_page, paged.
	 * @return array{items:array,total:int}
	 */
	public function query( array $args ) {
		global $wpdb;
		$table = EVLFL_Database::table_name();

		$args = array_merge(
			array(
				'search'       => '',
				'action'       => '',
				'status'       => '',
				'source'       => '',
				'date_from'    => '',
				'date_to'      => '',
				'search_email' => true,
				'orderby'      => 'event_time',
				'order'        => 'DESC',
				'per_page'     => 20,
				'paged'        => 1,
			),
			$args
		);

		$orderby  = in_array( $args['orderby'], self::sortable_columns(), true ) ? $args['orderby'] : 'event_time';
		$order    = 'ASC' === strtoupper( (string) $args['order'] ) ? 'ASC' : 'DESC';
		$per_page = min( self::MAX_PER_PAGE, max( 1, absint( $args['per_page'] ) ) );
		$offset   = ( max( 1, absint( $args['paged'] ) ) - 1 ) * $per_page;

		$params = array();
		$parts  = $this->build_where( $args, $params );
		$from   = "{$table} l {$parts['join']} {$parts['where']}";

		$count_sql = "SELECT COUNT(*) FROM {$from}";
		$total     = (int) ( empty( $params ) ? $wpdb->get_var( $count_sql ) : $wpdb->get_var( $wpdb->prepare( $count_sql, $params ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $count_sql is built only from the table name and fixed, allowlisted WHERE/JOIN fragments (build_where()); every value is a %s/%d placeholder. Without values there is nothing to prepare().

		$sql         = "SELECT l.* FROM {$from} ORDER BY l.{$orderby} {$order}, l.id {$order} LIMIT %d OFFSET %d";
		$params_page = array_merge( $params, array( $per_page, $offset ) );
		$rows        = $wpdb->get_results( $wpdb->prepare( $sql, $params_page ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql: table name, allowlisted fragments and an ORDER BY column/direction checked against sortable_columns(); values are placeholders.

		$items = array();
		foreach ( (array) $rows as $row ) {
			$items[] = $this->decode( $row );
		}
		return array(
			'items' => $items,
			'total' => $total,
		);
	}

	/**
	 * Walk every row matching the filters, newest first (event_time DESC, id DESC), calling
	 * $callback( $row ) for each decoded row. Uses keyset pagination in batches, so memory
	 * stays flat on large logs and rows written meanwhile cannot shift or repeat a page.
	 * Rows come without their metadata column (up to 60 KB each; not needed to list or export).
	 *
	 * @param array         $args     Same filter keys as query(); orderby/order/paging are ignored.
	 * @param callable      $callback Receives one decoded row.
	 * @param int           $batch    Rows per database round trip (1-1000).
	 * @param callable|null $on_batch Receives each batch (array of decoded rows) before its rows go
	 *                                to $callback, e.g. to look names up once per batch.
	 * @return int Rows visited.
	 */
	public function each( array $args, callable $callback, $batch = 500, $on_batch = null ) {
		global $wpdb;
		$table   = EVLFL_Database::table_name();
		$args    = array_merge(
			array(
				'search'       => '',
				'action'       => '',
				'status'       => '',
				'source'       => '',
				'date_from'    => '',
				'date_to'      => '',
				'search_email' => true,
			),
			$args
		);
		$batch   = min( 1000, max( 1, absint( $batch ) ) );
		$columns = 'l.id, l.user_id, l.course_id, l.action, l.source, l.event_trigger, l.actor_type, l.actor_id, l.status, l.verification_reason, l.confidence, l.event_time, l.related_event_id, l.created_at';

		$params = array();
		$parts  = $this->build_where( $args, $params );
		$count  = 0;
		$cursor = null;
		do {
			$where = $parts['where'];
			$p     = $params;
			if ( null !== $cursor ) {
				$where .= ( '' === $where ? 'WHERE ' : ' AND ' ) . '(l.event_time < %s OR (l.event_time = %s AND l.id < %d))';
				array_push( $p, $cursor[0], $cursor[0], $cursor[1] );
			}
			$p[]     = $batch;
			$sql     = "SELECT {$columns} FROM {$table} l {$parts['join']} {$where} ORDER BY l.event_time DESC, l.id DESC LIMIT %d";
			$rows    = (array) $wpdb->get_results( $wpdb->prepare( $sql, $p ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name, column list and WHERE are built from fixed/allowlisted pieces; values are placeholders.
			$fetched = count( $rows );
			$rows    = array_map( array( $this, 'decode' ), $rows );
			if ( $rows && null !== $on_batch ) {
				call_user_func( $on_batch, $rows );
			}
			foreach ( $rows as $row ) {
				call_user_func( $callback, $row );
				++$count;
				$cursor = array( (string) $row['event_time'], (int) $row['id'] );
			}
		} while ( $fetched === $batch );
		return $count;
	}

	/**
	 * Build the WHERE/JOIN pieces and collect prepare() params.
	 *
	 * @param array $args   Query args.
	 * @param array $params Params (by reference).
	 * @return array{join:string,where:string}
	 */
	private function build_where( array $args, array &$params ) {
		global $wpdb;
		$where = array();
		$join  = '';

		foreach ( array( 'action', 'status', 'source' ) as $field ) {
			if ( '' !== (string) $args[ $field ] ) {
				$where[]  = "l.{$field} = %s";
				$params[] = (string) $args[ $field ];
			}
		}
		if ( isset( $args['user_id'] ) && absint( $args['user_id'] ) > 0 ) {
			$where[]  = 'l.user_id = %d';
			$params[] = absint( $args['user_id'] );
		}
		if ( '' !== (string) $args['date_from'] ) {
			$where[]  = 'l.event_time >= %s';
			$params[] = (string) $args['date_from'];
		}
		if ( '' !== (string) $args['date_to'] ) {
			$where[]  = 'l.event_time <= %s';
			$params[] = (string) $args['date_to'];
		}

		$search = trim( (string) $args['search'] );
		if ( '' !== $search ) {
			$join   = "LEFT JOIN {$wpdb->users} u ON u.ID = l.user_id LEFT JOIN {$wpdb->posts} p ON p.ID = l.course_id";
			$like   = '%' . $wpdb->esc_like( $search ) . '%';
			$clause = array( 'u.display_name LIKE %s', 'u.user_login LIKE %s', 'p.post_title LIKE %s' );
			array_push( $params, $like, $like, $like );
			if ( ! isset( $args['search_email'] ) || $args['search_email'] ) {
				$clause[] = 'u.user_email LIKE %s';
				$params[] = $like;
			}
			if ( ctype_digit( $search ) && strlen( $search ) < 19 ) {
				$clause[] = 'l.user_id = %d';
				$clause[] = 'l.course_id = %d';
				array_push( $params, (int) $search, (int) $search );
			}
			$where[] = '(' . implode( ' OR ', $clause ) . ')';
		}

		return array(
			'join'  => $join,
			'where' => $where ? 'WHERE ' . implode( ' AND ', $where ) : '',
		);
	}

	/**
	 * Fetch several rows (without the metadata column), oldest first.
	 *
	 * @param int[] $ids Row IDs (capped at 200).
	 * @return array[]
	 */
	public function get_by_ids( array $ids ) {
		global $wpdb;
		$ids = array_slice( array_values( array_filter( array_map( 'absint', $ids ) ) ), 0, 200 );
		if ( ! $ids ) {
			return array();
		}
		$table        = EVLFL_Database::table_name();
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$sql          = "SELECT id, user_id, course_id, action, source, event_trigger, actor_type, actor_id, status, verification_reason, confidence, event_time FROM {$table} WHERE id IN ({$placeholders}) ORDER BY event_time ASC, id ASC";
		$rows         = $wpdb->get_results( $wpdb->prepare( $sql, $ids ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.UnfinishedPrepare -- Table name from $wpdb->prefix; one %d placeholder per ID.
		return array_map( array( $this, 'decode' ), (array) $rows );
	}

	/**
	 * Delete events older than a cutoff (one bounded batch).
	 *
	 * @param string $cutoff_gmt Y-m-d H:i:s in UTC.
	 * @param int    $limit      Max rows to delete.
	 * @return int Rows deleted.
	 */
	public function delete_older_than( $cutoff_gmt, $limit = 1000 ) {
		global $wpdb;
		$table = EVLFL_Database::table_name();
		$n     = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE event_time < %s LIMIT %d", (string) $cutoff_gmt, max( 1, absint( $limit ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix; values are placeholders.
		return false === $n ? 0 : (int) $n;
	}

	/**
	 * Delete events about a user (one bounded batch).
	 *
	 * @param int $user_id User ID.
	 * @param int $limit   Max rows to delete.
	 * @return int Rows deleted.
	 */
	public function delete_for_user( $user_id, $limit = 1000 ) {
		global $wpdb;
		$table = EVLFL_Database::table_name();
		$n     = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE user_id = %d LIMIT %d", absint( $user_id ), max( 1, absint( $limit ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix; values are placeholders.
		return false === $n ? 0 : (int) $n;
	}

	/**
	 * Forget a user as the actor of other events (the events themselves stay).
	 *
	 * @param int $user_id User ID.
	 * @return void
	 */
	public function clear_actor( $user_id ) {
		global $wpdb;
		$wpdb->update(
			EVLFL_Database::table_name(),
			array( 'actor_id' => null ),
			array( 'actor_id' => absint( $user_id ) ),
			null,
			array( '%d' )
		);
	}

	/**
	 * Decode a DB row (metadata JSON to array, ints cast).
	 *
	 * @param array $row Row.
	 * @return array
	 */
	private function decode( array $row ) {
		foreach ( array( 'id', 'user_id', 'course_id' ) as $k ) {
			$row[ $k ] = (int) $row[ $k ];
		}
		$row['actor_id'] = ( isset( $row['actor_id'] ) && null !== $row['actor_id'] ) ? (int) $row['actor_id'] : null;
		$meta            = ! empty( $row['metadata'] ) ? json_decode( (string) $row['metadata'], true ) : array();
		$row['metadata'] = is_array( $meta ) ? $meta : array();
		return $row;
	}
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching