<?php
/**
 * Questions the chatbot could not answer, kept for a human to reply to.
 *
 * A visitor asks something the knowledge base does not cover. Instead of ending there, the widget
 * asks for an email address and stores the question; the administrator sees it on the dashboard,
 * answers it by email, and marks it done.
 *
 * The question and the reply the bot actually gave are copied into this table rather than looked
 * up in the message log. The log is purged after the retention period — a request that disappears
 * because its conversation aged out is worse than storing a few hundred bytes twice.
 *
 * @package AI House
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class MYBOT_Requests
 */
class MYBOT_Requests {

	/**
	 * Valid statuses.
	 *
	 * @var array
	 */
	const STATUSES = array( 'new', 'answered', 'closed' );

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

		return $wpdb->prefix . MYBOT_TABLE_SUFFIX . 'requests';
	}

	/**
	 * Whether the table exists.
	 *
	 * The table arrives with the 1.2.0 schema, and a site that updates by replacing the plugin
	 * folder never re-activates — so the upgrade routine may not have run yet when the first
	 * request is saved. Refusing politely beats a database error in the middle of a chat.
	 *
	 * @return bool
	 */
	public static function table_exists() {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );

		return $found === $table;
	}

	/**
	 * Stores a request.
	 *
	 * @param array $data Request data.
	 * @return int|WP_Error Inserted ID, or an error.
	 */
	public static function create( $data ) {
		global $wpdb;

		if ( ! self::table_exists() ) {
			return new WP_Error(
				'mybot_requests_table_missing',
				__( 'The requests table is missing. Deactivate and activate the plugin once so the database upgrade can run.', 'aihouse' )
			);
		}

		$email = isset( $data['email'] ) ? sanitize_email( (string) $data['email'] ) : '';

		if ( '' === $email || ! is_email( $email ) ) {
			return new WP_Error( 'mybot_request_email', __( 'A valid email address is required so we can reply.', 'aihouse' ) );
		}

		/*
		 * The question is capped, and the cap is not cosmetic (1.18.1): this is a PUBLIC route, so the length
		 * of what it stores is chosen by a stranger. The chat route already refuses over 2000 characters, and
		 * the hand-over form sends the chat message — so a request longer than this one did not come from the
		 * widget, and there is no reason to keep a megabyte of it.
		 */
		$question = isset( $data['question'] ) ? trim( mb_substr( wp_kses_post( (string) $data['question'] ), 0, 5000 ) ) : '';

		if ( '' === $question ) {
			return new WP_Error( 'mybot_request_question', __( 'There is no question to send.', 'aihouse' ) );
		}

		$answer = isset( $data['answer'] ) ? mb_substr( wp_kses_post( (string) $data['answer'] ), 0, 20000 ) : '';

		$row = array(
			'session_id' => isset( $data['session_id'] ) ? substr( sanitize_text_field( (string) $data['session_id'] ), 0, 64 ) : '',
			'question'   => $question,
			'answer'     => $answer,
			'name'       => isset( $data['name'] ) ? substr( sanitize_text_field( (string) $data['name'] ), 0, 120 ) : '',
			'email'      => substr( $email, 0, 190 ),
			'phone'      => isset( $data['phone'] ) ? substr( sanitize_text_field( (string) $data['phone'] ), 0, 40 ) : '',
			'note'       => isset( $data['note'] ) ? substr( sanitize_textarea_field( (string) $data['note'] ), 0, 5000 ) : '',
			'lang'       => isset( $data['lang'] ) ? substr( sanitize_key( (string) $data['lang'] ), 0, 8 ) : '',
			'status'     => 'new',
			'created_at' => current_time( 'mysql' ),
			'updated_at' => current_time( 'mysql' ),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$inserted = $wpdb->insert(
			self::table(),
			$row,
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false === $inserted ) {
			return new WP_Error( 'mybot_request_insert', __( 'The request could not be saved.', 'aihouse' ) );
		}

		$id        = (int) $wpdb->insert_id;
		$row['id'] = $id;

		/**
		 * Fires after a question was handed over for a human to answer.
		 *
		 * This is the integration point: an n8n workflow, a CRM plugin, an SMS gateway or a
		 * Slack bridge can listen here and deliver the request wherever the team actually
		 * works, without AI House having to know about any of them.
		 *
		 * @param array $row The stored request, including `id`.
		 */
		do_action( 'mybot_support_request_created', $row );

		return $id;
	}

	/**
	 * Marks a request as done.
	 *
	 * @param int    $id     Request ID.
	 * @param string $status New status.
	 * @return bool
	 */
	public static function set_status( $id, $status ) {
		global $wpdb;

		$status = in_array( $status, self::STATUSES, true ) ? $status : 'new';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->update(
			self::table(),
			array(
				'status'     => $status,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Records that the notification for a request was sent.
	 *
	 * @param int $id Request ID.
	 * @return void
	 */
	public static function mark_notified( $id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update( self::table(), array( 'notified' => 1 ), array( 'id' => (int) $id ), array( '%d' ), array( '%d' ) );
	}

	/**
	 * Loads one request by ID.
	 *
	 * @param int $id Request ID.
	 * @return array|null
	 */
	public static function find( $id ) {
		global $wpdb;

		if ( ! self::table_exists() ) {
			return null;
		}

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, (int) $id ), ARRAY_A );

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Most recent requests.
	 *
	 * @param int    $limit  Maximum rows.
	 * @param string $status Optional status filter.
	 * @param int    $offset Rows to skip, for paging through a long list.
	 * @return array
	 */
	public static function recent( $limit = 50, $status = '', $offset = 0 ) {
		global $wpdb;

		if ( ! self::table_exists() ) {
			return array();
		}

		$table  = self::table();
		$limit  = max( 1, (int) $limit );
		$offset = max( 0, (int) $offset );

		if ( '' !== $status && in_array( $status, self::STATUSES, true ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$rows = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i WHERE status = %s ORDER BY created_at DESC LIMIT %d OFFSET %d', $table, $status, $limit, $offset ),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$rows = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i ORDER BY created_at DESC LIMIT %d OFFSET %d', $table, $limit, $offset ),
				ARRAY_A
			);
		}

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

	/**
	 * How many requests there are, in total or in one status.
	 *
	 * @param string $status Optional status filter.
	 * @return int
	 */
	public static function count( $status = '' ) {
		global $wpdb;

		if ( ! self::table_exists() ) {
			return 0;
		}

		$table = self::table();

		if ( '' !== $status && in_array( $status, self::STATUSES, true ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s', $table, $status ) );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
	}

	/**
	 * Number of requests per status.
	 *
	 * @return array Status => count.
	 */
	public static function counts() {
		global $wpdb;

		$counts = array_fill_keys( self::STATUSES, 0 );

		if ( ! self::table_exists() ) {
			return $counts;
		}

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS total FROM %i GROUP BY status', $table ), ARRAY_A );

		foreach ( (array) $rows as $row ) {
			$status = isset( $row['status'] ) ? (string) $row['status'] : '';

			if ( isset( $counts[ $status ] ) ) {
				$counts[ $status ] = (int) $row['total'];
			}
		}

		return $counts;
	}

	/**
	 * Whether this session already left a request very recently.
	 *
	 * The widget shows the form once per unanswered answer, but nothing stops a script from posting
	 * it in a loop. One request per session per few minutes keeps the list readable.
	 *
	 * @param string $session_id Session ID.
	 * @param int    $seconds    Window length.
	 * @return bool
	 */
	public static function recent_from_session( $session_id, $seconds = 300 ) {
		global $wpdb;

		$session_id = substr( sanitize_text_field( (string) $session_id ), 0, 64 );

		if ( '' === $session_id || ! self::table_exists() ) {
			return false;
		}

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

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$found = $wpdb->get_var(
			$wpdb->prepare( 'SELECT id FROM %i WHERE session_id = %s AND created_at > %s LIMIT 1', $table, $session_id, $cutoff )
		);

		return null !== $found;
	}

	/**
	 * Remembers that a question was stored.
	 *
	 * @param int $id Stored request ID.
	 * @return void
	 */
	public static function record_saved( $id ) {
		$trace = self::trace();

		$trace['last_ok']    = time();
		$trace['last_id']    = (int) $id;
		$trace['last_error'] = array();

		update_option( MYBOT_SUPPORT_TRACE_OPTION, $trace, false );
	}

	/**
	 * Remembers that a submission was refused, and why.
	 *
	 * Before this existed, a visitor whose request never reached the database saw a small red line
	 * in the chat and that was the end of it: no row, no log entry, nothing on any admin screen —
	 * the exact shape of failure this project keeps meeting (3.28). The visitor still gets their
	 * message; the administrator now gets the same message, where they look for problems.
	 *
	 * @param string $code    Error code.
	 * @param string $message Human readable reason.
	 * @return void
	 */
	public static function record_failed( $code, $message ) {
		$trace = self::trace();

		$trace['last_error'] = array(
			'at'      => time(),
			'code'    => sanitize_key( (string) $code ),
			'message' => (string) $message,
		);

		update_option( MYBOT_SUPPORT_TRACE_OPTION, $trace, false );
	}

	/**
	 * Where the contact details actually live, said once, on the screen people look at.
	 *
	 * This is the answer to a question the project has now been asked twice: "the customer filled
	 * the form and I cannot find it anywhere". Both places that get searched are, correctly, places
	 * the data is not in — the Supabase mirror holds conversations and never contact details (a
	 * deliberate privacy decision, docs/08 muc 11), and the React dashboard does not list requests
	 * at all.
	 *
	 * Saying it next to the success line is the whole point: docs/08 muc 3.32 already explained it
	 * in the hand-over notes, and that did not help the person searching, because documentation is
	 * not where somebody looks when they are hunting for a row.
	 *
	 * 1.6.6 moved the list off the settings page onto a screen of its own, which made the old
	 * wording — "the request is the line below" — false. A sentence that was correct when it was
	 * written and quietly stopped being correct is 6.31, so it is corrected here in the same
	 * release that moved the list, and it now ends with a link instead of a description of a place.
	 *
	 * @return string
	 */
	public static function where() {
		return sprintf(
			/* translators: %s: link to the Requests screen */
			__( 'These details stay in WordPress only: they are not copied to Supabase, whose mirror holds conversations rather than contact information, and the dashboard does not list them. They are all on the %s screen, where you can read, reply to and close them.', 'aihouse' ),
			'<a href="' . esc_url( admin_url( 'admin.php?page=' . MYBOT_REQUESTS_SLUG ) ) . '">' . esc_html__( 'Requests', 'aihouse' ) . '</a>'
		);
	}

	/**
	 * What happened to the last submission.
	 *
	 * @return array
	 */
	public static function trace() {
		$trace = get_option( MYBOT_SUPPORT_TRACE_OPTION, array() );

		if ( ! is_array( $trace ) ) {
			$trace = array();
		}

		return array(
			'last_ok'    => isset( $trace['last_ok'] ) ? (int) $trace['last_ok'] : 0,
			'last_id'    => isset( $trace['last_id'] ) ? (int) $trace['last_id'] : 0,
			'last_error' => ( isset( $trace['last_error'] ) && is_array( $trace['last_error'] ) ) ? $trace['last_error'] : array(),
		);
	}

	/**
	 * One sentence describing the state of the hand-over, for the Status card.
	 *
	 * Written without `_n()` on purpose: the string extractor in tools/build-i18n.mjs reads
	 * `__()`/`_e()` calls, so a plural form would never reach the .pot and would stay English in
	 * the Vietnamese admin. A countable phrase avoids the problem instead of hiding it.
	 *
	 * @return string
	 */
	public static function describe() {
		$trace  = self::trace();
		$counts = self::counts();
		$error  = $trace['last_error'];
		$failed = ! empty( $error ) && (int) $error['at'] > $trace['last_ok'];

		$waiting = sprintf(
			/* translators: %d: number of questions waiting for a human */
			__( 'Waiting for a human: %d.', 'aihouse' ),
			$counts['new']
		);

		if ( $failed ) {
			return $waiting . ' ' . sprintf(
				/* translators: 1: reason the submission failed, 2: time difference */
				__( 'The last visitor attempt FAILED %2$s ago and was NOT saved: %1$s', 'aihouse' ),
				(string) $error['message'],
				human_time_diff( (int) $error['at'], time() )
			);
		}

		if ( $trace['last_ok'] > 0 ) {
			return $waiting . ' ' . sprintf(
				/* translators: 1: request ID, 2: time difference */
				__( 'The last question was saved %2$s ago, as request #%1$d.', 'aihouse' ),
				$trace['last_id'],
				human_time_diff( $trace['last_ok'], time() )
			);
		}

		if ( ! self::table_exists() ) {
			// The same sentence the requests card uses: one message, one place to fix it.
			return __( 'The requests table does not exist yet. Deactivate and activate the plugin once so the database upgrade can create it.', 'aihouse' );
		}

		return $waiting . ' ' . __( 'Nothing has been sent yet. The form appears in the chat when the bot cannot answer a question — use it once to test the whole path.', 'aihouse' );
	}
}