<?php
/**
 * Conversation logging.
 *
 * In asynchronous mode (the default), each message only pushes one job onto the
 * queue. Cron collects them and issues a single multi-row INSERT — instead of two
 * separate INSERT statements per message as in the traditional approach.
 *
 * @package AI House
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class MYBOT_Logger
 */
class MYBOT_Logger {

	/**
	 * Log job type.
	 *
	 * @var string
	 */
	const JOB_TYPE = 'log_messages';

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

		return $wpdb->prefix . 'mybot_messages';
	}

	/**
	 * Register the queue handler.
	 *
	 * @param array $handlers Existing handler map.
	 * @return array
	 */
	public function register_handlers( $handlers ) {
		$handlers[ self::JOB_TYPE ] = array( $this, 'handle_log_job' );

		return $handlers;
	}

	/**
	 * Handle a batched log write job.
	 *
	 * @param array $payload Job data.
	 * @return void
	 */
	public function handle_log_job( $payload ) {
		$rows = isset( $payload['rows'] ) && is_array( $payload['rows'] ) ? $payload['rows'] : array();

		if ( empty( $rows ) ) {
			return;
		}

		$this->insert_many( $rows );
	}

	/**
	 * Log a single message.
	 *
	 * @param array $data Message data.
	 * @return void
	 */
	public function log( $data ) {
		$row = $this->normalize( $data );

		if ( null === $row ) {
			return;
		}

		if ( $this->async_enabled() ) {
			mybot()->queue->push( self::JOB_TYPE, array( 'rows' => array( $row ) ) );

			return;
		}

		$this->insert_many( array( $row ) );
	}

	/**
	 * Run a single multi-row INSERT statement.
	 *
	 * The rows are normalized first so that the identifiers MySQL hands back can be paired with
	 * them and carried into the optional Supabase mirror. `$wpdb->query()` cannot report those
	 * identifiers, but PDO is not in play here either: WordPress runs the statement through
	 * mysqli, so `$wpdb->insert_id` plus the number of rows affected gives the whole block.
	 *
	 * @param array $rows List of raw rows.
	 * @return int Number of inserted rows.
	 */
	public function insert_many( $rows ) {
		global $wpdb;

		$normalized = $this->valid_rows( $rows );

		if ( empty( $normalized ) ) {
			return 0;
		}

		$columns = array( 'session_id', 'role', 'content', 'attachments', 'lang', 'mode', 'provider', 'model', 'latency_ms', 'tokens_in', 'tokens_out', 'cache_hit', 'rating', 'created_at' );
		$formats = array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%s' );

		$values    = array();
		$row_marks = array();

		foreach ( $normalized as $row ) {
			$row_marks[] = '(' . implode( ', ', $formats ) . ')';

			foreach ( $columns as $column ) {
				$values[] = $row[ $column ];
			}
		}

		$table = self::table();
		$sql   = 'INSERT INTO %i (' . implode( ', ', $columns ) . ') VALUES ' . implode( ', ', $row_marks );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$result = $wpdb->query( $wpdb->prepare( $sql, array_merge( array( $table ), $values ) ) );

		$inserted = (int) $result;

		if ( $inserted > 0 ) {
			/*
			 * MySQL reports the FIRST generated identifier of a multi-row INSERT, not the last:
			 * `LAST_INSERT_ID()` — and therefore `$wpdb->insert_id` — is defined that way. An
			 * earlier version of this line subtracted the row count, which shifted every wp_id
			 * down by one per extra row in the block. The keys stayed unique, so the mirror
			 * worked, but they were no longer the IDs of the rows they claimed to describe and
			 * the Supabase copy could not be joined back to WordPress.
			 */
			$first = (int) $wpdb->insert_id;

			if ( $first < 1 ) {
				// Without a trustworthy identifier the mirror would key every row to the same
				// value and then silently drop all but the first one. Writing nothing is honest.
				return $inserted;
			}

			foreach ( $normalized as $offset => $row ) {
				$normalized[ $offset ]['wp_id'] = $first + $offset;
			}

			MYBOT_Supabase_Log::mirror( $normalized );
		}

		return $inserted;
	}

	/**
	 * Normalizes a list of rows and drops the ones that cannot be stored.
	 *
	 * @param array $rows Raw rows.
	 * @return array Normalized rows, re-indexed.
	 */
	protected function valid_rows( $rows ) {
		$valid = array();

		foreach ( (array) $rows as $row ) {
			if ( ! is_array( $row ) ) {
				continue;
			}

			$normalized = $this->normalize( $row );

			if ( null === $normalized ) {
				continue;
			}

			$valid[] = $normalized;
		}

		return $valid;
	}

	/**
	 * Normalize a log row.
	 *
	 * @param array $data Raw data.
	 * @return array|null
	 */
	protected function normalize( $data ) {
		if ( empty( $data['session_id'] ) || ! isset( $data['content'] ) ) {
			return null;
		}

		$role = isset( $data['role'] ) && 'bot' === $data['role'] ? 'bot' : 'user';

		return array(
			'session_id'  => substr( sanitize_text_field( $data['session_id'] ), 0, 64 ),
			'role'        => $role,
			'content'     => wp_kses_post( (string) $data['content'] ),
			'attachments' => self::clean_attachments( $data ),
			'lang'        => isset( $data['lang'] ) ? substr( sanitize_key( $data['lang'] ), 0, 8 ) : '',
			'mode'        => isset( $data['mode'] ) && 'deep' === $data['mode'] ? 'deep' : 'quick',
			'provider'    => isset( $data['provider'] ) ? substr( sanitize_text_field( $data['provider'] ), 0, 32 ) : '',
			'model'       => isset( $data['model'] ) ? substr( sanitize_text_field( $data['model'] ), 0, 64 ) : '',
			'latency_ms'  => isset( $data['latency_ms'] ) ? absint( $data['latency_ms'] ) : 0,
			'tokens_in'   => isset( $data['tokens_in'] ) ? absint( $data['tokens_in'] ) : 0,
			'tokens_out'  => isset( $data['tokens_out'] ) ? absint( $data['tokens_out'] ) : 0,
			'cache_hit'   => empty( $data['cache_hit'] ) ? 0 : 1,
			'rating'      => isset( $data['rating'] ) ? max( -1, min( 1, (int) $data['rating'] ) ) : 0,
			'created_at'  => isset( $data['created_at'] ) ? $data['created_at'] : current_time( 'mysql' ),
		);
	}

	/**
	 * Normalise the attachments of one row into the JSON this plugin stores.
	 *
	 * Accepts what the callers actually have — a JSON string from the database, or the record array from
	 * `MYBOT_Attachments::store()` — and refuses anything else. It is deliberately narrow: this column
	 * decides which FILE the serving route will open, so a value that arrives from a filter must not be
	 * able to name a file the plugin never wrote (`MYBOT_Attachments::path()` re-checks the name as well;
	 * two checks, because this one decides what is stored and that one decides what is opened).
	 *
	 * @param array $data Raw row.
	 * @return string
	 */
	protected static function clean_attachments( $data ) {
		if ( empty( $data['attachments'] ) ) {
			return '';
		}

		$records = is_array( $data['attachments'] )
			? $data['attachments']
			: MYBOT_Attachments::from_json( (string) $data['attachments'] );

		$clean = array();

		foreach ( (array) $records as $record ) {
			if ( ! is_array( $record ) ) {
				continue;
			}

			$file = isset( $record['file'] ) ? (string) $record['file'] : '';

			// '' means "a record with no file" — a voice note, whose only trace is this marker.
			if ( '' !== $file && '' === MYBOT_Attachments::path( $file ) ) {
				continue;
			}

			$clean[] = array(
				'kind'   => isset( $record['kind'] ) ? sanitize_key( (string) $record['kind'] ) : 'image',
				'file'   => preg_match( '/^[a-f0-9]{32}\.(jpg|png|webp)$/', $file ) ? $file : '',
				'mime'   => isset( $record['mime'] ) ? substr( sanitize_text_field( (string) $record['mime'] ), 0, 32 ) : '',
				'bytes'  => isset( $record['bytes'] ) ? absint( $record['bytes'] ) : 0,
				'width'  => isset( $record['width'] ) ? absint( $record['width'] ) : 0,
				'height' => isset( $record['height'] ) ? absint( $record['height'] ) : 0,
			);
		}

		return MYBOT_Attachments::to_json( $clean );
	}

	/**
	 * Whether asynchronous logging is enabled.
	 *
	 * @return bool
	 */
	protected function async_enabled() {
		return (bool) mybot()->settings->get( 'async_logging', 1 );
	}

	/**
	 * History of a session.
	 *
	 * @param string $session_id Session identifier.
	 * @param int    $limit      Maximum number of messages.
	 * @return array
	 */
	public function get_history( $session_id, $limit = 50 ) {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, role, content, lang, mode, created_at FROM %i WHERE session_id = %s ORDER BY id ASC LIMIT %d',
				$table,
				$session_id,
				max( 1, min( 200, absint( $limit ) ) )
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Recent sessions, used by the dashboard.
	 *
	 * @param int $limit Number of sessions.
	 * @return array
	 */
	public function recent_sessions( $limit = 20 ) {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT session_id, COUNT(*) AS total, MAX(created_at) AS last_at
				 FROM %i GROUP BY session_id ORDER BY last_at DESC LIMIT %d',
				$table,
				max( 1, min( 100, absint( $limit ) ) )
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Overview statistics.
	 *
	 * @return array
	 */
	public function stats() {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT COUNT(*) AS total,
				        COUNT(DISTINCT session_id) AS sessions,
				        SUM(cache_hit) AS cache_hits,
				        AVG(latency_ms) AS avg_latency
				 FROM %i',
				$table
			),
			ARRAY_A
		);

		return array(
			'total'       => isset( $row['total'] ) ? (int) $row['total'] : 0,
			'sessions'    => isset( $row['sessions'] ) ? (int) $row['sessions'] : 0,
			'cache_hits'  => isset( $row['cache_hits'] ) ? (int) $row['cache_hits'] : 0,
			'avg_latency' => isset( $row['avg_latency'] ) ? (int) round( (float) $row['avg_latency'] ) : 0,
		);
	}

	/**
	 * Update the visitor's rating for a message.
	 *
	 * @param int $message_id Message ID.
	 * @param int $rating     1 for satisfied, -1 for not satisfied.
	 * @return bool
	 */
	public function set_rating( $message_id, $rating ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->update(
			self::table(),
			array( 'rating' => max( -1, min( 1, (int) $rating ) ) ),
			array( 'id' => absint( $message_id ) ),
			array( '%d' ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Rate the latest bot reply in a session.
	 *
	 * The interface does not know the record ID because logging is asynchronous,
	 * so the rating is applied per session to the most recent bot message.
	 *
	 * @param string $session_id Session identifier.
	 * @param int    $rating     1 for satisfied, -1 for not satisfied.
	 * @return bool
	 */
	public function rate_last_bot_message( $session_id, $rating ) {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$message_id = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM %i WHERE session_id = %s AND role = %s ORDER BY id DESC LIMIT 1',
				$table,
				$session_id,
				'bot'
			)
		);

		if ( ! $message_id ) {
			return false;
		}

		return $this->set_rating( (int) $message_id, $rating );
	}

	/**
	 * The attachments recorded on one message (1.14.0).
	 *
	 * The REST route that serves a visitor's picture is given a **message id**, never a file name, so
	 * the name stays inside the plugin: there is no request a visitor — or a curious administrator —
	 * can make that prints it. This method is the lookup behind that decision.
	 *
	 * @param int $message_id Message ID.
	 * @return string The stored JSON, or '' when the row is gone or carried no picture.
	 */
	public function attachments_of( $message_id ) {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$json = $wpdb->get_var(
			$wpdb->prepare( 'SELECT attachments FROM %i WHERE id = %d LIMIT 1', $table, absint( $message_id ) )
		);

		return is_string( $json ) ? $json : '';
	}

	/**
	 * Recent messages that carried a picture (1.14.0).
	 *
	 * The Conversations screen is a React bundle this repository cannot rebuild (no `node_modules`), so
	 * a photo stored for a conversation would have nowhere to be seen at all. This is the query behind
	 * the plain-PHP list on the Requests screen, which at least puts the pictures in front of a human.
	 *
	 * @param int $limit Maximum number of rows.
	 * @return array Rows with id, session_id, content, attachments and created_at.
	 */
	public function recent_attachments( $limit = 12 ) {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, session_id, content, attachments, created_at FROM %i
				 WHERE attachments <> '' AND attachments IS NOT NULL
				 ORDER BY id DESC LIMIT %d",
				$table,
				max( 1, min( 50, absint( $limit ) ) )
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Delete old logs, and the pictures that belong to them.
	 *
	 * @param int $days Number of days to keep.
	 * @return int Number of deleted rows.
	 */
	public function purge_old( $days = 30 ) {
		global $wpdb;

		$table  = self::table();
		$cutoff = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - ( max( 1, (int) $days ) * DAY_IN_SECONDS ) );

		/*
		 * The files go FIRST, and they go because the rows are read before they are deleted (1.14.0).
		 *
		 * A stored photo and the row that names it are one thing. Deleting the row alone would leave a
		 * visitor's picture on the disk for ever — with nothing left that remembers it exists, no screen
		 * that can show it and no code that would ever delete it. That is a leak, and it is the kind this
		 * project writes down rather than discovers (docs/08 muc 6.31). So: read the names, remove the
		 * files, then delete the rows.
		 *
		 * If a file cannot be removed the row is STILL deleted. An unremovable leftover is a smaller
		 * problem than a retention promise the site quietly stops keeping, and the count of rows stays
		 * the number this method has always returned.
		 */
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$jsons = $wpdb->get_col(
			$wpdb->prepare( "SELECT attachments FROM %i WHERE created_at < %s AND attachments <> ''", $table, $cutoff )
		);

		foreach ( (array) $jsons as $json ) {
			MYBOT_Attachments::delete_json( (string) $json );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->query(
			$wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', $table, $cutoff )
		);
	}
}