<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange

/******************************************
 * DB Install
 ******************************************/

global $wpdb;

$collate = '';

if ( $wpdb->has_cap( 'collation' ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	if ( ! empty( $wpdb->charset ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$collate .= "DEFAULT CHARACTER SET $wpdb->charset"; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}
	if ( ! empty( $wpdb->collate ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$collate .= " COLLATE $wpdb->collate"; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	}
}

$table1 = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table1 ) ) ) != $table1 ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
	$sql_sliders_Table1 = "
		CREATE TABLE IF NOT EXISTS `$table1` (
		`id` int(11) NOT NULL AUTO_INCREMENT,
		`session_id` varchar(256) NOT NULL,
		`name` varchar(256) NOT NULL,
		`email` varchar(256) NOT NULL,
		`phone` varchar(256) NOT NULL,
		`date` datetime NOT NULL,
		`user_id` int(11) NOT NULL,
		`interaction` int(11) NOT NULL,
		PRIMARY KEY (`id`)
		)  $collate AUTO_INCREMENT=1 ";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql_sliders_Table1 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
}

$table1 = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table1 ) ) ) != strtolower( $table1 ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
	$sql_sliders_Table1 = "
		CREATE TABLE IF NOT EXISTS `$table1` (
		`id` int(11) NOT NULL AUTO_INCREMENT,
		`user_id` int(11) NOT NULL,
		`conversation` LONGTEXT NOT NULL,
		`interaction` int(11) NOT NULL,
		`environment_info` LONGTEXT NOT NULL,
		PRIMARY KEY (`id`)
		)  $collate AUTO_INCREMENT=1 ";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql_sliders_Table1 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
}

$table_failed_response = $wpdb->prefix . 'wpbot_failed_response'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_failed_response ) ) ) != $table_failed_response ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
	$sql_failed_response = "
		CREATE TABLE IF NOT EXISTS `$table_failed_response` (
		`id` int(11) NOT NULL AUTO_INCREMENT,
		`query` varchar(256) NOT NULL,
		`count` int(11) NOT NULL,
		`status` int(11) NOT NULL,
		PRIMARY KEY (`id`)
		)  $collate AUTO_INCREMENT=1 ";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql_failed_response ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
}

/******************************************
 * Helper Function: Check if a Database Column Exists
 */
if ( ! function_exists( 'qcwp_isset_table_column' ) ) {

	function qcwp_isset_table_column( $table_name, $column_name ) {
		global $wpdb;

		$columns = $wpdb->get_results( 'SHOW COLUMNS FROM `' . esc_sql( $table_name ) . '`', ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared

		foreach ( $columns as $column ) {
			if ( $column['Field'] == $column_name ) {
				return true;
			}
		}
	}
}

/******************************************
 * When the main plugin is activated,
 * Build the necessary DB Structure.
 */
register_activation_hook( __FILE__, 'qcld_wb_chatboot_sessions_defualt_options' );

function qcld_wb_chatboot_sessions_defualt_options() {
	global $wpdb;

	$collate = '';

	if ( $wpdb->has_cap( 'collation' ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! empty( $wpdb->charset ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

			$collate .= "DEFAULT CHARACTER SET $wpdb->charset"; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}
		if ( ! empty( $wpdb->collate ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

			$collate .= " COLLATE $wpdb->collate"; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

		}
	}

	// Create Table: wpbot_user

	$table1 = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	$sql_sliders_Table1 = "
		CREATE TABLE IF NOT EXISTS `$table1` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `session_id` varchar(256) NOT NULL,
          `name` varchar(256) NOT NULL,
          `email` varchar(256) NOT NULL,
		  `phone` varchar(256) NOT NULL,
		  `date` datetime NOT NULL,
		  `user_id` int(11) NOT NULL,
		  `interaction` int(11) NOT NULL,
		  PRIMARY KEY (`id`)
		)  $collate AUTO_INCREMENT=1 ";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	dbDelta( $sql_sliders_Table1 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	if ( ! qcwp_isset_table_column( $table1, 'phone' ) ) {
		$wpdb->query( 'ALTER TABLE `' . esc_sql( $table1 ) . '` ADD `phone` varchar(256) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
	}

	// Create Table: wpbot_conversation

	$table2 = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	$sql_sliders_Table2 = "
		CREATE TABLE IF NOT EXISTS `$table2` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `user_id` int(11) NOT NULL,
		  `conversation` LONGTEXT NOT NULL,
		  `interaction` int(11) NOT NULL,
		  `environment_info` LONGTEXT NOT NULL,
		  PRIMARY KEY (`id`)
		)  $collate AUTO_INCREMENT=1 ";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	dbDelta( $sql_sliders_Table2 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	if ( ! qcwp_isset_table_column( $table2, 'interaction' ) ) {
		$wpdb->query( 'ALTER TABLE `' . esc_sql( $table2 ) . '` ADD `interaction` int(11) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
	}

	if ( ! qcwp_isset_table_column( $table1, 'interaction' ) ) {
		$wpdb->query( 'ALTER TABLE `' . esc_sql( $table1 ) . '` ADD `interaction` int(11) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
	}
}

/******************************************
 * Update DB Structure, If Necessary
 ******************************************/
function qcldwpbot_chatsession_db_update_check() {

	global $wpdb;

	$version = get_option( 'wpbot_chatsession_db_version', '1.0' );

	$table1 = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	$table2 = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	if ( version_compare( $version, '2.0' ) < 0 ) {

		if ( ! qcwp_isset_table_column( $table1, 'interaction' ) ) {
			$wpdb->query( 'ALTER TABLE `' . esc_sql( $table1 ) . '` ADD `interaction` int(11) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
		}

		if ( ! qcwp_isset_table_column( $table2, 'interaction' ) ) {
			$wpdb->query( 'ALTER TABLE `' . esc_sql( $table2 ) . '` ADD `interaction` int(11) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
		}

		if ( ! qcwp_isset_table_column( $table2, 'environment_info' ) ) {
			$wpdb->query( 'ALTER TABLE `' . esc_sql( $table2 ) . '` ADD `environment_info` LONGTEXT NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
		}

		update_option( 'wpbot_chatsession_db_version', '2.0' );

	}
}

add_action( 'plugins_loaded', 'qcldwpbot_chatsession_db_update_check' );