<?php

/**
 * VAPPV_TTW_Installer
 * TalkToWebsite plugin and database tables installer
 */
final class VAPPV_TTW_Installer {

	/**
	 * Working with the WP database based on this article:
	 * https://codex.wordpress.org/Creating_Tables_with_Plugins
	 */

	/**
	 * Install TTW plugin
	 * @return void
	 */
	public function install_plugin() {
		$this->install_db();
	}

	/**
	 * Uninstall plugin
	 * @return void
	 */
	public function uninstall_plugin() {
		$this->uninstall_db();
	}

	/**
	 * install_db
	 * Install plugin DB tables
	 * @return void
	 */
	public function install_db() {

		global $wpdb;

		//Include DB update default wordpress function
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		//Get database tables prefix and other params
		$db_tables_prefix   = $wpdb->prefix;

		//----------------------------------------------------------
		//Install settings DB table
		$config_table_name = "{$db_tables_prefix}vappv_ttw_settings";
		$dbtable_config_sql = $wpdb->prepare( '
			CREATE TABLE IF NOT EXISTS %i (
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
				`tname` TEXT NOT NULL, 
				`tvalue` TEXT NULL, 
				PRIMARY KEY (`id`)
			) ENGINE = InnoDB',
			array( $config_table_name )
		);
		dbDelta( $dbtable_config_sql );
		//----------------------------------------------------------

		//----------------------------------------------------------
		//Install training items DB table
		$training_db_table_name = "{$db_tables_prefix}vappv_ttw_training";
		$dbtable_training_sql = $wpdb->prepare( '
			CREATE TABLE IF NOT EXISTS %i (
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				`wp_object_type` TEXT NOT NULL,
				`wp_object_id` INT NOT NULL,
				`training_date` DATETIME,
				PRIMARY KEY (`id`)
			) ENGINE = InnoDB',
			array( $training_db_table_name )
		);
		dbDelta( $dbtable_training_sql );
		//----------------------------------------------------------

		//----------------------------------------------------------
		//Install logs DB table
		$logs_db_table_name = "{$db_tables_prefix}vappv_ttw_logs";
		$dbtable_logs_sql = $wpdb->prepare( '
			CREATE TABLE IF NOT EXISTS %i (
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				`log_date` DATETIME NOT NULL,
				`log_type` TEXT,
				`information` TEXT,
				`extra_details` TEXT,
				PRIMARY KEY (`id`)
			) ENGINE = InnoDB',
			array( $logs_db_table_name )
		);
		dbDelta( $dbtable_logs_sql );
		//----------------------------------------------------------

		//----------------------------------------------------------
		//Install dialogues DB table
		//Dialogues (parent table)
		$dialogues_db_table_name = "{$db_tables_prefix}vappv_ttw_dialogues";
		$dbtable_dialogues_sql = $wpdb->prepare( '
			CREATE TABLE IF NOT EXISTS %i (
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				`start_date` DATETIME COMMENT \'Dialogue start date\',
				`user_name` TEXT COMMENT \'User name\',
				`user_email` TEXT COMMENT \'User Email\',
				`user_guid` VARCHAR(40) COMMENT \'Visitor unique identifier (UI logic)\',
				`user_ip` TEXT COMMENT \'User IP address\',
				`is_thread_by_admin` INT COMMENT \'Is inner thread (Testing tab into admin section)\',
				`ai_thread_id` TEXT COMMENT \'OpenAI thread identifier\',
				`ai_assistant_id` TEXT COMMENT \'OpenAI assistant identifier\',
				`ai_thread_all_params` TEXT COMMENT \'OpenAI thread all parameters\',
				PRIMARY KEY (`id`),
				INDEX `is_thread_by_admin_index` USING BTREE (`is_thread_by_admin`),
				INDEX `user_guid_index` USING BTREE (`user_guid`)
			) ENGINE = InnoDB',
			array( $dialogues_db_table_name )
		);
		dbDelta( $dbtable_dialogues_sql );

		//Messages (children table / parent to dialogues)
		$gialogues_messages_db_table_name = "{$db_tables_prefix}vappv_ttw_dialogues_messages";
		$dbtable_dialogues_messages_sql = $wpdb->prepare( '
			CREATE TABLE IF NOT EXISTS %i (
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				`dialogue_id` INT COMMENT \'Parent dialogue identifier. Foreign key to vappv_ttw_dialogues DB table\', 
				`sender_role` TEXT COMMENT \'Role of the message sender. Possible values = [ user, ai ]\',
				`related_user_guid` VARCHAR(40) COMMENT \'Visitor unique identifier (UI logic) if this messaged related to him (from the user or from AI)\',
				`message_type` VARCHAR(30) COMMENT \'Message type. Possible values = [ message_by_visitor, message_by_admin, response_for_visitor, response_for_admin ]\',
				`message_guid` TEXT COMMENT \'Message text key, unique identifier\',
				`message_text` TEXT COMMENT \'Message content\', 
				`message_date` DATETIME COMMENT \'Message sent date, UTC\',
				`response_date` DATETIME COMMENT \'Message responded by AI date, UTC\',  
				`ai_message_id` TEXT COMMENT \'OpenAI message ID\',
				`ai_thread_id` TEXT COMMENT \'ID of the parent thread of this message in ChatGPT\',
				`ai_run_id` TEXT COMMENT \'ID of the associated run in ChatGPT\',
				`is_responded_by_ai` INT COMMENT \'Is message responded by ChatGPT\',
				`is_executed_with_error` INT COMMENT \'Is message execured with error\',
				`is_read_by_sender` INT COMMENT \'Is message read by sender\',
				`rate_by_sender` INT COMMENT \'Is message rated by sender\',
				`is_url_followed_by_sender` INT COMMENT \'Is message inner URL followed by sender\',
				`ai_error_code` TEXT COMMENT \'OpenAI API error code\',
				`ai_error_details` TEXT COMMENT \'OpenAI API error details\',
				`ai_execution_stats` TEXT COMMENT \'OpenAI execution stats (JSON data)\',
				`read_by_user_metadata` TEXT COMMENT \'Message read by user, params\',
				PRIMARY KEY (`id`),
				INDEX `dialogue_id_index` USING BTREE (`dialogue_id`),
				INDEX `related_user_guid_index` USING BTREE (`related_user_guid`),
				INDEX `message_type_index` USING BTREE (`message_type`)
			) ENGINE = InnoDB',
			array( $gialogues_messages_db_table_name )
		);
		dbDelta( $dbtable_dialogues_messages_sql );

		//Usage stats
		$usage_stats_db_table_name = "{$db_tables_prefix}vappv_ttw_usage_statistics";
		$dbtable_usage_stats_sql = $wpdb->prepare( '
			CREATE TABLE IF NOT EXISTS %i (
				`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
				`tdate` DATE NOT NULL,
				`dialogues_count` INT NOT NULL DEFAULT 0,
				`income_messages_count` INT NOT NULL DEFAULT 0,
				`income_words_count` INT NOT NULL DEFAULT 0,
				`income_characters_count` INT NOT NULL DEFAULT 0,
				`output_words_count` INT NOT NULL DEFAULT 0,
				`output_characters_count` INT NOT NULL DEFAULT 0,
				`output_completion_tokens_count` INT NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`) 
			) ENGINE = InnoDB',
			array( $usage_stats_db_table_name )
		);
		dbDelta( $dbtable_usage_stats_sql );

		//----------------------------------------------------------
	}

	/**
	 * Remove plugin DB tables from the WordPress
	 * @return void
	 */
	public function uninstall_db() {

		//Create settings controller and get "Store data" parameter
		$settings_controller = new VAPPV_TTW_Settings();
		$settings_controller->load_all_saved_settings();
		$settings_store_db_on_deletion = $settings_controller->try_get_setting( VAPPV_TTW_Settings::$settings_store_db_on_deletion );

		//Check is it necessary to save all plugin DB data
		if ( 'yes' === $settings_store_db_on_deletion ) {
			return;
		}

		global $wpdb;

		//Include DB update default wordpress function
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		//Get database tables prefix and other params
		$db_tables_prefix = $wpdb->prefix;

		//Prepare tables names
		$ttw_config_table_name                = "{$db_tables_prefix}vappv_ttw_settings";
		$ttw_training_db_table_name           = "{$db_tables_prefix}vappv_ttw_training";
		$ttw_logs_db_table_name               = "{$db_tables_prefix}vappv_ttw_logs";
		$ttw_dialogues_db_table_name          = "{$db_tables_prefix}vappv_ttw_dialogues";
		$ttw_gialogues_messages_db_table_name = "{$db_tables_prefix}vappv_ttw_dialogues_messages";
		$ttw_usage_stats_db_table_name        = "{$db_tables_prefix}vappv_ttw_usage_statistics";

		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', array( $ttw_config_table_name ) ) );
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', array( $ttw_training_db_table_name ) ) );
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', array( $ttw_logs_db_table_name ) ) );
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', array( $ttw_dialogues_db_table_name ) ) );
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', array( $ttw_gialogues_messages_db_table_name ) ) );
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', array( $ttw_usage_stats_db_table_name ) ) );
	}
}

/*
	© 2024 Copyright Vitalii A. Popov. All rights reserved.
*/

?>