<?php
/**
 * Database helpers for Chatbot AI message persistence.
 *
 * @package Replyan_AI
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter

final class Replyan_AI_DB {
	/**
	 * Create custom tables for sessions and messages.
	 */
	public static function create_tables(): void {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();
		$sessions_table  = $wpdb->prefix . 'replyan_ai_sessions';
		$messages_table  = $wpdb->prefix . 'replyan_ai_messages';
		$agents_table    = $wpdb->prefix . 'replyan_ai_agents';

		$sql_sessions = "CREATE TABLE {$sessions_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id VARCHAR(64) NOT NULL,
			user_id BIGINT(20) UNSIGNED DEFAULT 0,
			user_name VARCHAR(255) DEFAULT '',
			user_email VARCHAR(255) DEFAULT '',
			source VARCHAR(50) DEFAULT 'default',
			page_title VARCHAR(255) DEFAULT '',
			page_url VARCHAR(255) DEFAULT '',
			ownership_token_hash VARCHAR(64) NULL,
			is_manual_mode TINYINT(1) DEFAULT 0,
			unread_count_admin INT(11) DEFAULT 0,
			unread_count_user INT(11) DEFAULT 0,
			agent_id BIGINT(20) UNSIGNED DEFAULT 0,
			started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			last_message_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY session_id (session_id),
			KEY user_id (user_id),
			KEY last_message_at (last_message_at)
		) {$charset_collate};";

		$sql_messages = "CREATE TABLE {$messages_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id VARCHAR(64) NOT NULL,
			role VARCHAR(20) NOT NULL,
			content LONGTEXT NOT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY session_id (session_id),
			KEY created_at (created_at)
		) {$charset_collate};";

		$sql_agents = "CREATE TABLE {$agents_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			agent_name VARCHAR(255) NOT NULL,
			description TEXT NOT NULL,
			instruction LONGTEXT NOT NULL,
			provider VARCHAR(50) NOT NULL DEFAULT 'openai',
			model VARCHAR(100) NOT NULL,
			temperature FLOAT NOT NULL DEFAULT 0.8,
			max_tokens INT(11) NOT NULL DEFAULT 1000,
			include_woocommerce TINYINT(1) DEFAULT 0,
			facebook_page_id VARCHAR(100) DEFAULT NULL,
			facebook_page_name VARCHAR(255) DEFAULT NULL,
			facebook_page_token TEXT DEFAULT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id)
		) {$charset_collate};";



		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql_sessions );
		dbDelta( $sql_messages );
		dbDelta( $sql_agents );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$fb_page_id_row = $wpdb->get_row( "SHOW COLUMNS FROM {$agents_table} LIKE 'facebook_page_id'" );
		if ( ! $fb_page_id_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$agents_table} ADD COLUMN facebook_page_id VARCHAR(100) DEFAULT NULL AFTER include_woocommerce" );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$fb_page_name_row = $wpdb->get_row( "SHOW COLUMNS FROM {$agents_table} LIKE 'facebook_page_name'" );
		if ( ! $fb_page_name_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$agents_table} ADD COLUMN facebook_page_name VARCHAR(255) DEFAULT NULL AFTER facebook_page_id" );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$fb_page_token_row = $wpdb->get_row( "SHOW COLUMNS FROM {$agents_table} LIKE 'facebook_page_token'" );
		if ( ! $fb_page_token_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$agents_table} ADD COLUMN facebook_page_token TEXT DEFAULT NULL AFTER facebook_page_name" );
		}

		// Extra safety check in case dbDelta skipped the column.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row( "SHOW COLUMNS FROM {$sessions_table} LIKE 'is_manual_mode'" );
		if ( ! $row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$sessions_table} ADD COLUMN is_manual_mode TINYINT(1) DEFAULT 0 AFTER user_email" );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$source_row = $wpdb->get_row( "SHOW COLUMNS FROM {$sessions_table} LIKE 'source'" );
		if ( ! $source_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$sessions_table} ADD COLUMN source VARCHAR(50) DEFAULT 'default' AFTER user_email" );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$page_title_row = $wpdb->get_row( "SHOW COLUMNS FROM {$sessions_table} LIKE 'page_title'" );
		if ( ! $page_title_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$sessions_table} ADD COLUMN page_title VARCHAR(255) DEFAULT '' AFTER source" );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$page_url_row = $wpdb->get_row( "SHOW COLUMNS FROM {$sessions_table} LIKE 'page_url'" );
		if ( ! $page_url_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$sessions_table} ADD COLUMN page_url VARCHAR(255) DEFAULT '' AFTER page_title" );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$agent_row = $wpdb->get_row( "SHOW COLUMNS FROM {$agents_table} LIKE 'provider'" );
		if ( ! $agent_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$agents_table} ADD COLUMN provider VARCHAR(50) NOT NULL DEFAULT 'openai' AFTER instruction" );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$token_row = $wpdb->get_row( "SHOW COLUMNS FROM {$sessions_table} LIKE 'ownership_token_hash'" );
		if ( ! $token_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$sessions_table} ADD COLUMN ownership_token_hash VARCHAR(64) NULL AFTER page_url" );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$agent_id_row = $wpdb->get_row( "SHOW COLUMNS FROM {$sessions_table} LIKE 'agent_id'" );
		if ( ! $agent_id_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$sessions_table} ADD COLUMN agent_id BIGINT(20) UNSIGNED DEFAULT 0 AFTER unread_count_user" );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$include_woo_row = $wpdb->get_row( "SHOW COLUMNS FROM {$agents_table} LIKE 'include_woocommerce'" );
		if ( ! $include_woo_row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "ALTER TABLE {$agents_table} ADD COLUMN include_woocommerce TINYINT(1) DEFAULT 0 AFTER max_tokens" );
		}
	}

	/**
	 * Ensure a session row exists, then return/update it.
	 *
	 * @param string $session_id Random session identifier from the frontend.
	 * @param string $source     Source of the session (e.g., 'default' or 'pages').
	 * @param string $page_title The title of the page where the session started.
	 * @param string $page_url   The URL of the page where the session started.
	 * @param string $ownership_token_hash Token hash for session ownership.
	 * @param int    $agent_id   The AI agent ID handling this session.
	 * @return void
	 */
	public static function ensure_session( string $session_id, string $source = 'default', string $page_title = '', string $page_url = '', string $ownership_token_hash = '', int $agent_id = 0 ): void {
		global $wpdb;

		$table = $wpdb->prefix . 'replyan_ai_sessions';

		$exists = $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->prepare( "SELECT id FROM {$table} WHERE session_id = %s", $session_id )
		);

		if ( $exists ) {
			$wpdb->update(
				$table,
				array( 'last_message_at' => current_time( 'mysql' ) ),
				array( 'session_id' => $session_id ),
				array( '%s' ),
				array( '%s' )
			);
			return;
		}

		$user_id    = get_current_user_id();
		$user_name  = '';
		$user_email = '';

		if ( $user_id > 0 ) {
			$user       = get_userdata( $user_id );
			$user_name  = $user ? $user->display_name : '';
			$user_email = $user ? $user->user_email : '';
		}

		$insert_data = array(
			'session_id'      => $session_id,
			'user_id'         => $user_id,
			'user_name'       => $user_name,
			'user_email'      => $user_email,
			'source'          => $source,
			'page_title'      => $page_title,
			'page_url'        => $page_url,
			'agent_id'        => $agent_id,
			'started_at'      => current_time( 'mysql' ),
			'last_message_at' => current_time( 'mysql' ),
		);
		$insert_format = array( '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s' );

		if ( '' !== $ownership_token_hash ) {
			$insert_data['ownership_token_hash'] = $ownership_token_hash;
			$insert_format[]                     = '%s';
		}

		$wpdb->insert( $table, $insert_data, $insert_format );
	}

	/**
	 * Save a single message.
	 *
	 * @param string $session_id Session identifier.
	 * @param string $role       Message role (user|assistant).
	 * @param string $content    Message content.
	 */
	public static function save_message( string $session_id, string $role, string $content ): void {
		global $wpdb;

		$table = $wpdb->prefix . 'replyan_ai_messages';

		$wpdb->insert(
			$table,
			array(
				'session_id' => $session_id,
				'role'       => $role,
				'content'    => $content,
				'created_at' => current_time( 'mysql' ),
			),
			array( '%s', '%s', '%s', '%s' )
		);
	}

	/**
	 * Get paginated sessions ordered by most recent activity.
	 *
	 * @param int    $page          Page number (1-based).
	 * @param int    $per_page      Items per page.
	 * @param string $source_filter Optional source filter ('all', 'default', 'pages').
	 * @return array{sessions: array, total: int}
	 */
	public static function get_sessions( int $page = 1, int $per_page = 30, string $source_filter = 'all' ): array {
		global $wpdb;

		$table  = $wpdb->prefix . 'replyan_ai_sessions';
		$offset = ( $page - 1 ) * $per_page;

		$where_clause = '';
		if ( 'all' !== $source_filter ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$where_clause = $wpdb->prepare( 'WHERE source = %s', $source_filter );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} {$where_clause}" );

		$sessions = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT * FROM {$table} {$where_clause} ORDER BY last_message_at DESC LIMIT %d OFFSET %d",
				$per_page,
				$offset
			),
			ARRAY_A
		);

		// Attach last message preview to each session.
		$msg_table = $wpdb->prefix . 'replyan_ai_messages';
		foreach ( $sessions as &$session ) {
			$last_msg = $wpdb->get_var(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"SELECT content FROM {$msg_table} WHERE session_id = %s ORDER BY created_at DESC LIMIT 1",
					$session['session_id']
				)
			);
			$session['last_message'] = $last_msg ? wp_trim_words( $last_msg, 12, '...' ) : '';
		}

		return array(
			'sessions' => $sessions ?: array(),
			'total'    => $total,
		);
	}

	/**
	 * Get a single session by ID.
	 *
	 * @param string $session_id Session identifier.
	 * @return object|null Session object or null.
	 */
	public static function get_session_by_id( string $session_id ) {
		global $wpdb;
		$table = $wpdb->prefix . 'replyan_ai_sessions';

		return $wpdb->get_row(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->prepare( "SELECT * FROM {$table} WHERE session_id = %s", $session_id )
		);
	}

	/**
	 * Get all messages for a given session.
	 *
	 * @param string $session_id Session identifier.
	 * @return array
	 */
	public static function get_messages( string $session_id ): array {
		global $wpdb;

		$table = $wpdb->prefix . 'replyan_ai_messages';

		$messages = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT role, content, created_at FROM {$table} WHERE session_id = %s ORDER BY created_at ASC",
				$session_id
			),
			ARRAY_A
		);

		return $messages ?: array();
	}

	/**
	 * Toggle manual mode for a session.
	 *
	 * @param string $session_id Session identifier.
	 * @param bool   $is_manual  Whether manual mode is enabled.
	 */
	public static function set_manual_mode( string $session_id, bool $is_manual ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'replyan_ai_sessions';

		$wpdb->update(
			$table,
			array( 'is_manual_mode' => $is_manual ? 1 : 0 ),
			array( 'session_id' => $session_id ),
			array( '%d' ),
			array( '%s' )
		);
	}

	/**
	 * Increment unread count for either admin or user.
	 *
	 * @param string $session_id Session identifier.
	 * @param string $target     'admin' or 'user'.
	 */
	public static function increment_unread( string $session_id, string $target ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'replyan_ai_sessions';
		$column = $target === 'admin' ? 'unread_count_admin' : 'unread_count_user';
		
		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"UPDATE {$table} SET {$column} = {$column} + 1, last_message_at = %s WHERE session_id = %s",
				current_time( 'mysql' ),
				$session_id
			)
		);
	}

	/**
	 * Reset unread count.
	 *
	 * @param string $session_id Session identifier.
	 * @param string $target     'admin' or 'user'.
	 */
	public static function reset_unread( string $session_id, string $target ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'replyan_ai_sessions';
		$column = $target === 'admin' ? 'unread_count_admin' : 'unread_count_user';
		
		$wpdb->update(
			$table,
			array( $column => 0 ),
			array( 'session_id' => $session_id ),
			array( '%d' ),
			array( '%s' )
		);
	}

	/**
	 * Save an AI Agent to the database.
	 *
	 * @param array $data Agent data.
	 * @return int|false Insert ID or false on failure.
	 */
	public static function save_agent( array $data ) {
		global $wpdb;
		$table = $wpdb->prefix . 'replyan_ai_agents';

		$agent_data = array(
			'agent_name'  => sanitize_text_field( $data['agent_name'] ?? '' ),
			'description' => sanitize_textarea_field( $data['description'] ?? '' ),
			'instruction' => wp_kses_post( $data['instruction'] ?? '' ),
			'provider'    => sanitize_text_field( $data['provider'] ?? 'openai' ),
			'model'       => sanitize_text_field( $data['model'] ?? 'gpt-4.1-mini' ),
			'temperature' => (float) ( $data['temperature'] ?? 0.8 ),
			'max_tokens'  => absint( $data['max_tokens'] ?? 1000 ),
			'include_woocommerce' => isset( $data['include_woocommerce'] ) ? (int) $data['include_woocommerce'] : 0,
			'facebook_page_id'    => sanitize_text_field( $data['facebook_page_id'] ?? '' ),
			'facebook_page_name'  => sanitize_text_field( $data['facebook_page_name'] ?? '' ),
			'facebook_page_token' => sanitize_text_field( $data['facebook_page_token'] ?? '' ),
		);
		$format = array( '%s', '%s', '%s', '%s', '%s', '%f', '%d', '%d', '%s', '%s', '%s' );

		if ( ! empty( $data['id'] ) ) {
			// Update existing agent
			$updated = $wpdb->update(
				$table,
				$agent_data,
				array( 'id' => (int) $data['id'] ),
				$format,
				array( '%d' )
			);

			if ( $updated !== false ) {
				self::sync_meta_integration( $agent_data );
				return (int) $data['id'];
			}

			return false;
		}

		// Insert new agent
		$agent_data['created_at'] = current_time( 'mysql' );
		$format[] = '%s';

		$inserted = $wpdb->insert(
			$table,
			$agent_data,
			$format
		);

		if ( $inserted ) {
			self::sync_meta_integration( $agent_data );
			return $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Sync Facebook / Meta Integration details with the SaaS backend.
	 *
	 * @param array $agent_data Agent details including facebook_page_id and facebook_page_token.
	 * @return void
	 */
	public static function sync_meta_integration( array $agent_data ): void {
		if ( empty( $agent_data['facebook_page_id'] ) || empty( $agent_data['facebook_page_token'] ) ) {
			return;
		}

		$endpoint = defined( 'REPLYAN_AI_SAAS_URL' ) ? rtrim( REPLYAN_AI_SAAS_URL, '/' ) . '/api/integrations/meta/messenger/connect' : 'http://localhost:3000/api/integrations/meta/messenger/connect';

		$saas_data = [
			'page_id'    => $agent_data['facebook_page_id'],
			'page_token' => $agent_data['facebook_page_token'],
			'site_url'   => site_url(),
		];

		Replyan_AI_Logs::log( 'saas data: ' . json_encode( $saas_data ) );
		$response = wp_remote_post(
			$endpoint,
			array(
				'headers' => array(
					'Content-Type' => 'application/json',
				),
				'body'    => wp_json_encode( $saas_data ),
				'timeout' => 15,
			)
		);

		if ( is_wp_error( $response ) ) {
			Replyan_AI_Logs::log( 'Meta integration sync failed: ' . $response->get_error_message() );
		}
	}

	/**
	 * Get a single AI Agent by ID.
	 *
	 * @param int $id Agent ID.
	 * @return array|false Agent data array or false if not found.
	 */
	public static function get_agent_by_id( int $id ) {
		global $wpdb;
		$table = $wpdb->prefix . 'replyan_ai_agents';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$agent = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ), ARRAY_A );

		return $agent ? $agent : false;
	}

	/**
	 * Get all saved AI Agents.
	 *
	 * @return array
	 */
	public static function get_agents(): array {
		global $wpdb;
		$table = $wpdb->prefix . 'replyan_ai_agents';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$agents = $wpdb->get_results( "SELECT * FROM {$table} ORDER BY created_at DESC", ARRAY_A );

		return $agents ?: array();
	}

	/**
	 * Delete an AI Agent.
	 *
	 * @param int $id Agent ID.
	 * @return bool
	 */
	public static function delete_agent( int $id ): bool {
		global $wpdb;
		$table = $wpdb->prefix . 'replyan_ai_agents';

		$deleted = $wpdb->delete(
			$table,
			array( 'id' => $id ),
			array( '%d' )
		);

		return $deleted !== false;
	}

}
