<?php
/**
 * CRUD access to conversations & messages.
 * Public methods here are the supported surface Pro is allowed to call.
 *
 * A note on direct database queries: this plugin uses two custom
 * tables (wp_cpecs_conversations, wp_cpecs_messages) that have no
 * equivalent in WordPress core APIs, so $wpdb is used directly
 * throughout this file — there is no WP_Query/get_posts()-style
 * abstraction for arbitrary custom tables. Every query below either
 * uses $wpdb->prepare() with placeholders for all user-supplied
 * values, or interpolates only this class's own table-name methods
 * (never user input) to build the FROM/JOIN clause, since SQL
 * identifiers can't be passed through prepare() placeholders.
 */

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

class CPECS_DB {

	const CACHE_GROUP = 'cpecs';

	public function conversations_table() {
		global $wpdb;
		return $wpdb->prefix . 'cpecs_conversations';
	}

	public function messages_table() {
		global $wpdb;
		return $wpdb->prefix . 'cpecs_messages';
	}

	/**
	 * Create a new conversation for a visitor.
	 *
	 * @param array $args visitor_uuid, visitor_name, visitor_email, visitor_ip, page_url, referrer, user_agent
	 * @return int Conversation ID.
	 */
	public function create_conversation( $args ) {
		global $wpdb;

		$now = current_time( 'mysql' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table, no core API available.
		$inserted = $wpdb->insert(
			$this->conversations_table(),
			array(
				'visitor_uuid'  => sanitize_text_field( $args['visitor_uuid'] ),
				'visitor_name'  => isset( $args['visitor_name'] ) ? sanitize_text_field( $args['visitor_name'] ) : null,
				'visitor_email' => isset( $args['visitor_email'] ) ? sanitize_email( $args['visitor_email'] ) : null,
				'visitor_ip'    => isset( $args['visitor_ip'] ) ? sanitize_text_field( $args['visitor_ip'] ) : null,
				'page_url'      => isset( $args['page_url'] ) ? esc_url_raw( $args['page_url'] ) : null,
				'referrer'      => isset( $args['referrer'] ) ? esc_url_raw( $args['referrer'] ) : null,
				'user_agent'    => isset( $args['user_agent'] ) ? sanitize_text_field( $args['user_agent'] ) : null,
				// Unhandled by any agent yet. send_message() leaves this
				// alone while it stays 'new'; admin_reply() flips it to
				// 'open' the moment an agent responds.
				'status'        => 'new',
				'created_at'    => $now,
				'updated_at'    => $now,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false === $inserted ) {
			return 0;
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * Find the most recent open conversation for a visitor UUID, or false.
	 */
	public function get_open_conversation_for_visitor( $visitor_uuid ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table, not meaningfully cacheable (changes on every new visitor message); table name method is not user input, see class docblock.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only, built from $wpdb->prefix, never user input.
				"SELECT * FROM {$this->conversations_table()} WHERE visitor_uuid = %s AND status != 'closed' ORDER BY id DESC LIMIT 1",
				$visitor_uuid
			)
		);

		return $row ? $row : false;
	}

	/**
	 * Get a single conversation by ID. Cached for the duration of the
	 * request (and across requests on sites with a persistent object
	 * cache), since this is the single most frequently repeated lookup
	 * — called on every poll, reply, and Pro extension request for the
	 * same conversation.
	 */
	public function get_conversation( $id ) {
		global $wpdb;

		$id = (int) $id;
		$cache_key = 'conversation_' . $id;

		$cached = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table, no core API available; result is cached below; table name method is not user input, see class docblock.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only, built from $wpdb->prefix, never user input.
				"SELECT * FROM {$this->conversations_table()} WHERE id = %d",
				$id
			)
		);

		wp_cache_set( $cache_key, $row, self::CACHE_GROUP, MINUTE_IN_SECONDS );

		return $row;
	}

	/**
	 * List conversations for the admin inbox.
	 *
	 * @param array $args status, search, per_page, page
	 */
	public function list_conversations( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'   => '',
			'search'   => '',
			'per_page' => 20,
			'page'     => 1,
		);
		$args = wp_parse_args( $args, $defaults );

		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}

		if ( ! empty( $args['search'] ) ) {
			$where[]  = '(visitor_name LIKE %s OR visitor_email LIKE %s)';
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$params[] = $like;
			$params[] = $like;
		}

		$where_sql = implode( ' AND ', $where );
		$offset    = max( 0, ( (int) $args['page'] - 1 ) * (int) $args['per_page'] );

		$params[] = (int) $args['per_page'];
		$params[] = $offset;

		// The WHERE clause and its placeholder count are assembled
		// together above (one push to $where[] always pairs with one
		// or more pushes to $params[], in the same order), so every
		// placeholder in $sql has a matching value in $params — this
		// is a genuinely safe use of prepare() with a dynamic clause,
		// which static analysis can't verify but is provably correct.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name + WHERE clause built from this method's own fixed placeholders only, see comment above.
		$sql = "SELECT * FROM {$this->conversations_table()} WHERE {$where_sql} ORDER BY updated_at DESC LIMIT %d OFFSET %d";

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared on the next line; sniff can't trace the two-step assignment.
		$prepared = $wpdb->prepare( $sql, $params );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table, paginated/filtered list is not meaningfully cacheable; $prepared is the direct return value of $wpdb->prepare() on the line above, sniff can't trace the two-step assignment.
		return $wpdb->get_results( $prepared );
	}

	public function count_conversations( $status = '' ) {
		global $wpdb;
		if ( $status ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table; count changes too often to usefully cache; table name method is not user input, see class docblock.
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only, built from $wpdb->prefix, never user input.
					"SELECT COUNT(*) FROM {$this->conversations_table()} WHERE status = %s",
					$status
				)
			);
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table, no user input in query; count changes too often to usefully cache; table name method is not user input, see class docblock.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->conversations_table()}" );
	}

	public function update_conversation_status( $id, $status ) {
		global $wpdb;

		$data = array(
			'status'     => sanitize_text_field( $status ),
			'updated_at' => current_time( 'mysql' ),
		);
		if ( 'closed' === $status ) {
			$data['closed_at'] = current_time( 'mysql' );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table, no core API available.
		$result = $wpdb->update(
			$this->conversations_table(),
			$data,
			array( 'id' => (int) $id ),
			null,
			array( '%d' )
		);

		wp_cache_delete( 'conversation_' . (int) $id, self::CACHE_GROUP );

		return $result;
	}

	public function touch_conversation( $id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table, no core API available.
		$result = $wpdb->update(
			$this->conversations_table(),
			array( 'updated_at' => current_time( 'mysql' ) ),
			array( 'id' => (int) $id ),
			array( '%s' ),
			array( '%d' )
		);

		wp_cache_delete( 'conversation_' . (int) $id, self::CACHE_GROUP );

		return $result;
	}

	/**
	 * Add a message to a conversation.
	 *
	 * @param int    $conversation_id
	 * @param string $sender_type 'visitor' | 'agent'
	 * @param int|null $sender_id  WP user ID for agents.
	 * @param string $message
	 * @return int Message ID.
	 */
	public function add_message( $conversation_id, $sender_type, $sender_id, $message ) {
		global $wpdb;

		$now = current_time( 'mysql' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table, no core API available.
		$inserted = $wpdb->insert(
			$this->messages_table(),
			array(
				'conversation_id' => (int) $conversation_id,
				'sender_type'     => sanitize_text_field( $sender_type ),
				'sender_id'       => $sender_id ? (int) $sender_id : null,
				'message'         => wp_kses_post( $message ),
				'created_at'      => $now,
			),
			array( '%d', '%s', '%d', '%s', '%s' )
		);

		if ( false === $inserted ) {
			return 0;
		}

		$this->touch_conversation( $conversation_id );

		return (int) $wpdb->insert_id;
	}

	/**
	 * Get messages for a conversation, optionally only those after a given message ID (for polling).
	 */
	public function get_messages( $conversation_id, $after_id = 0 ) {
		global $wpdb;

		if ( $after_id > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table; polling query is intentionally always fresh; table name method is not user input, see class docblock.
			return $wpdb->get_results(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only, built from $wpdb->prefix, never user input.
					"SELECT * FROM {$this->messages_table()} WHERE conversation_id = %d AND id > %d ORDER BY id ASC",
					$conversation_id,
					$after_id
				)
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table; polling query is intentionally always fresh; table name method is not user input, see class docblock.
		return $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only, built from $wpdb->prefix, never user input.
				"SELECT * FROM {$this->messages_table()} WHERE conversation_id = %d ORDER BY id ASC",
				$conversation_id
			)
		);
	}

	public function get_last_message( $conversation_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table; changes on every new message, not meaningfully cacheable; table name method is not user input, see class docblock.
		return $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only, built from $wpdb->prefix, never user input.
				"SELECT * FROM {$this->messages_table()} WHERE conversation_id = %d ORDER BY id DESC LIMIT 1",
				$conversation_id
			)
		);
	}

	/**
	 * Delete conversations (and their messages) older than a given DATETIME string.
	 * Used by the retention/cleanup cron. Returns number of conversations deleted.
	 */
	public function delete_conversations_older_than( $datetime ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom table, no core API available; table name method is not user input, see class docblock.
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only, built from $wpdb->prefix, never user input.
				"SELECT id FROM {$this->conversations_table()} WHERE created_at < %s",
				$datetime
			)
		);

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

		// $ids comes straight from the query above (integer primary
		// keys only), then is force-cast through intval(), so this is
		// safe to interpolate directly — IN (...) lists can't be
		// passed through a single prepare() placeholder anyway.
		$ids_sql = implode( ',', array_map( 'intval', $ids ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name + integer-only ID list, see comment above; table name method is not user input, see class docblock.
		$wpdb->query( "DELETE FROM {$this->messages_table()} WHERE conversation_id IN ({$ids_sql})" );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name + integer-only ID list, see comment above; table name method is not user input, see class docblock.
		$wpdb->query( "DELETE FROM {$this->conversations_table()} WHERE id IN ({$ids_sql})" );

		foreach ( $ids as $id ) {
			wp_cache_delete( 'conversation_' . (int) $id, self::CACHE_GROUP );
		}

		return count( $ids );
	}

	public function delete_conversation( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no core API available.
		$wpdb->delete( $this->messages_table(), array( 'conversation_id' => (int) $id ), array( '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no core API available.
		$wpdb->delete( $this->conversations_table(), array( 'id' => (int) $id ), array( '%d' ) );

		wp_cache_delete( 'conversation_' . (int) $id, self::CACHE_GROUP );
	}
}
