<?php
/**
 * Database tables handler
 *
 * @package NotifierToSlack
 */

namespace WPNTS\Inc;

/**
 * Database tables class
 */
class DbTables {

	/**
	 * Constructor
	 */
	public function __construct() {
		register_activation_hook( WP_NOTIFIER_TO_SLACK__FILE__, [ $this, 'create_tables' ] );
		add_action( 'wp_loaded', [ $this, 'check_and_update_tables_trigger' ] );
	}

	/**
	 * Create database tables
	 */
	public function create_tables() {

		/**
		 * Default setting value.
		 */
		$wpnts_default_interval = [
			'webhook' => 'https://hooks.slack.com/your-services/',
			'interval' => 2500,
			'interval_review' => 2500,
			'reviewDays' => -7,
			'activereview' => 'false',
			'activesupport' => 'false',
		];

		$wpnts_schedules_interval_site_settings = [
			'webhook' => 'https://hooks.slack.com/your-services/',
			'interval_plugin_update' => 10,
			'interval_sitehelgth_update' => 10,
			'intervalDays' => -1,
			'pluginactivation' => false,
			'plugindeactivation' => false,
			'updatenotification' => false,
			'wpnotification' => false,
			'loginandout' => false,
			'sitehelgth' => false,
			'registration' => false,

		];

		$wpnts_webhook_site_settings = [
			'webhook' => 'https://hooks.slack.com/your-services/',
			'intervalDays' => -1,
			'sitessecurityissuesInterval' => 1500,
			'sitessecurityissuesInterval' => 1500,
			'wpconfigmodification' => false,
			'htaccessmodification' => false,
		];

		$admin_email = get_option('admin_email');
		// Check if the admin email is set, otherwise use a default value.
		$admin_email = isset($admin_email) ? $admin_email : '';

		$wpnts_global_api_settings = [
			'global_webhook' => 'https://hooks.slack.com/your-services/',
			'global_interval' => 1500,
			'api_active' => false,
			'mailconfig' => false,
			'siteURL_add' => false,
			'recipiantmail' => $admin_email ? $admin_email : '',
			'activitylogduration' => '30',
			'logdurationtime' => gmdate('Y-m-d'),

		];

		$wpnts_plugin_list = [
			'id' => '1',
			'title' => 'admin-chat-box',
			'content' => 'admin-chat-box',
		];

		add_option( 'wpnts_default_interval', wp_json_encode($wpnts_default_interval) );
		add_option( 'wpnts_schedules_interval_site_settings', wp_json_encode($wpnts_schedules_interval_site_settings) );
		add_option( 'wpnts_webhook_site_settings', wp_json_encode($wpnts_webhook_site_settings) );
		add_option( 'wpnts_plugin_list', wp_json_encode($wpnts_plugin_list) );
		add_option( 'wpnts_global_api_settings', wp_json_encode($wpnts_global_api_settings) );

		// WooCommerce settings with incomplete orders tracking
		$wpnts_schedules_interval_woocommerce_settings = [
			'webhook' => 'https://hooks.slack.com/your-services/',
			'stockoutofstocknotifications' => false,
			'salesnotifications' => false,
			'wooproducteditornotify' => false,
			'shipingstatus' => false,
			'billingshippingnotify' => false,
			'wooanalytics' => false,
			'incompleteordersnotifications' => false,
			'incomplete_orders_slack_notifications' => true,
			'abandonment_delay_minutes' => 5,
			'cleanup_incomplete_orders_days' => 30,
			'cart_abandonment_minutes' => 30,
			'cart_abandonment_tracking' => true,
		];

		add_option( 'wpnts_schedules_interval_woocommerce_settings', wp_json_encode($wpnts_schedules_interval_woocommerce_settings) );
		add_option( 'wpnts_review_last_sent_time', 0 );
		add_option( 'wpnts_last_sent_time', 0 );
		add_option( 'wpnts_last_plugin_updates', 0 );
		add_option('wpnts_first_saturday_reset_done', 'false');

		/**
		 * Visitor leads.
		 */
		global $wpdb;
		$visitor_table = $wpdb->prefix . 'wpnts_visitor_data';
		$activity_log_table = $wpdb->prefix . 'wpnts_activity_log';

		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $visitor_table ) ) !== $visitor_table ) {
			$charset_collate = $wpdb->get_charset_collate();

			$sql = "CREATE TABLE $visitor_table (
				id mediumint(9) NOT NULL AUTO_INCREMENT,
				session_id VARCHAR(255) NOT NULL,
				ip VARCHAR(100) NOT NULL,
				ip_source VARCHAR(50),
				frontend_ip VARCHAR(100),
				backend_ip VARCHAR(100),
				country VARCHAR(100),
				city VARCHAR(100),
				page_url TEXT,
				referrer TEXT,
				user_agent TEXT,
				screen_resolution VARCHAR(50),
				language VARCHAR(50),
				visiting_time datetime NOT NULL,
				count INT DEFAULT 1,
				thumb VARCHAR(255),
				user_role VARCHAR(255),
				PRIMARY KEY  (id),
				KEY session_id_index (session_id),
				KEY ip_index (ip),
				KEY visiting_time_index (visiting_time)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta($sql);
		}

		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $activity_log_table ) ) !== $activity_log_table ) {
			$charset_collate = $wpdb->get_charset_collate();

			$sql = "CREATE TABLE $activity_log_table (
				id mediumint(9) NOT NULL AUTO_INCREMENT,
				severity VARCHAR(50) NOT NULL,
				date datetime NOT NULL,
				ip VARCHAR(100) NOT NULL,
				user VARCHAR(50) NOT NULL,
				event_type VARCHAR(100) NOT NULL,
				message TEXT NOT NULL,
				PRIMARY KEY (id)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta($sql);
		}

		// Maintenance mode queries option
		if ( false === get_option( 'maintenance_mode_queries' ) ) {
			add_option( 'maintenance_mode_queries', array() );
		}

		// Create incomplete orders table
		$incomplete_orders_table = $wpdb->prefix . 'wpnts_incomplete_orders';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $incomplete_orders_table ) ) !== $incomplete_orders_table ) {
			$charset_collate = $wpdb->get_charset_collate();

			$sql = "CREATE TABLE $incomplete_orders_table (
				id BIGINT(20) NOT NULL AUTO_INCREMENT,
				session_key VARCHAR(191) NOT NULL,
				user_id BIGINT(20) DEFAULT 0,
				order_id BIGINT(20) DEFAULT 0,
				email VARCHAR(191) DEFAULT '',
				name VARCHAR(255) DEFAULT '',
				phone VARCHAR(80) DEFAULT '',
				address TEXT,
				product_ids TEXT,
				product_names TEXT,
				product_links TEXT,
				cart_data TEXT,
				total_amount DECIMAL(10,2) DEFAULT 0,
				cart_interactions TEXT,
				is_abandonment BOOLEAN DEFAULT FALSE,
				time_on_checkout INT DEFAULT 0,
				status VARCHAR(50) DEFAULT 'incomplete',
				ip VARCHAR(45) DEFAULT '',
				user_agent TEXT,
				created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
				updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY (id),
				KEY session_key (session_key),
				KEY user_id (user_id),
				KEY email (email),
				KEY status (status),
				KEY is_abandonment (is_abandonment),
				KEY created_at (created_at)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta($sql);
		}

		// Create cart interactions table
		$cart_interactions_table = $wpdb->prefix . 'wpnts_cart_interactions';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $cart_interactions_table ) ) !== $cart_interactions_table ) {
			$charset_collate = $wpdb->get_charset_collate();

			$sql = "CREATE TABLE $cart_interactions_table (
				id int(11) NOT NULL AUTO_INCREMENT,
				session_key varchar(255) NOT NULL,
				interaction_type varchar(50) NOT NULL,
				product_name varchar(255) DEFAULT '',
				quantity int(11) DEFAULT 0,
				value decimal(10,2) DEFAULT 0.00,
				interaction_data longtext,
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (id),
				KEY session_key (session_key),
				KEY interaction_type (interaction_type),
				KEY created_at (created_at)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta($sql);
		}

		// Create cart sessions table for abandonment tracking
		$cart_sessions_table = $wpdb->prefix . 'wpnts_cart_sessions';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $cart_sessions_table ) ) !== $cart_sessions_table ) {
			$charset_collate = $wpdb->get_charset_collate();

			$sql = "CREATE TABLE $cart_sessions_table (
				id int(11) NOT NULL AUTO_INCREMENT,
				session_key varchar(255) NOT NULL,
				user_id bigint(20) DEFAULT 0,
				user_email varchar(191) DEFAULT '',
				user_name varchar(255) DEFAULT '',
				cart_contents longtext,
				last_activity datetime DEFAULT CURRENT_TIMESTAMP,
				ip_address varchar(45) DEFAULT '',
				user_agent text,
				page_url varchar(500) DEFAULT '',
				activity_data text,
				status varchar(50) DEFAULT 'active',
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (id),
				UNIQUE KEY session_key (session_key),
				KEY user_id (user_id),
				KEY user_email (user_email),
				KEY last_activity (last_activity),
				KEY status (status)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta($sql);
		}

	}

	/**
	 * Check if tables need to be updated based on version
	 */
	public function check_and_update_tables_trigger() {
		// Define current database schema version
		$current_db_version = '1.3.0';
		
		// Get stored database version
		$stored_db_version = get_option( 'wpnts_db_version', '0.0.0' );
		
		// Only run if version has changed
		if ( version_compare( $stored_db_version, $current_db_version, '<' ) ) {
			$this->check_and_update_tables();
			
			// Update the stored version after successful update
			update_option( 'wpnts_db_version', $current_db_version );
		}
	}

	/**
	 * Check and update database tables
	 */
	public function check_and_update_tables() {
		global $wpdb;
		
		// Check if user_id column exists in incomplete orders table
		$incomplete_orders_table = $wpdb->prefix . 'wpnts_incomplete_orders';
		$columns = $wpdb->get_results("SHOW COLUMNS FROM $incomplete_orders_table LIKE 'user_id'");
		
		if (empty($columns)) {
			// Add user_id column
			$wpdb->query("ALTER TABLE $incomplete_orders_table ADD COLUMN user_id BIGINT(20) DEFAULT 0 AFTER session_key");
			$wpdb->query("ALTER TABLE $incomplete_orders_table ADD INDEX user_id (user_id)");
		}
		
		// Check if cart_interactions column exists
		$columns = $wpdb->get_results("SHOW COLUMNS FROM $incomplete_orders_table LIKE 'cart_interactions'");
		if (empty($columns)) {
			// Add cart_interactions column
			$wpdb->query("ALTER TABLE $incomplete_orders_table ADD COLUMN cart_interactions TEXT AFTER total_amount");
		}
		
		// Check if is_abandonment column exists
		$columns = $wpdb->get_results("SHOW COLUMNS FROM $incomplete_orders_table LIKE 'is_abandonment'");
		if (empty($columns)) {
			// Add is_abandonment column
			$wpdb->query("ALTER TABLE $incomplete_orders_table ADD COLUMN is_abandonment BOOLEAN DEFAULT FALSE AFTER cart_interactions");
			$wpdb->query("ALTER TABLE $incomplete_orders_table ADD INDEX is_abandonment (is_abandonment)");
		}
		
		// Check if time_on_checkout column exists
		$columns = $wpdb->get_results("SHOW COLUMNS FROM $incomplete_orders_table LIKE 'time_on_checkout'");
		if (empty($columns)) {
			// Add time_on_checkout column
			$wpdb->query("ALTER TABLE $incomplete_orders_table ADD COLUMN time_on_checkout INT DEFAULT 0 AFTER is_abandonment");
		}

		// Check if cart sessions table exists
		$cart_sessions_table = $wpdb->prefix . 'wpnts_cart_sessions';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $cart_sessions_table ) ) !== $cart_sessions_table ) {
			// Create cart sessions table
			$this->create_tables(); // This will create the missing table
		}

		// Check and update visitor data table columns
		$visitor_table = $wpdb->prefix . 'wpnts_visitor_data';
		
		// Check if visitor table exists
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $visitor_table ) ) === $visitor_table ) {
			
			// Check if session_id column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'session_id'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN session_id VARCHAR(255) NOT NULL AFTER id");
				$wpdb->query("ALTER TABLE $visitor_table ADD INDEX session_id_index (session_id)");
			}
			
			// Check if ip_source column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'ip_source'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN ip_source VARCHAR(50) AFTER ip");
			}
			
			// Check if frontend_ip column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'frontend_ip'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN frontend_ip VARCHAR(100) AFTER ip_source");
			}
			
			// Check if backend_ip column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'backend_ip'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN backend_ip VARCHAR(100) AFTER frontend_ip");
			}
			
			// Check if page_url column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'page_url'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN page_url TEXT AFTER city");
			}
			
			// Check if referrer column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'referrer'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN referrer TEXT AFTER page_url");
			}
			
			// Check if user_agent column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'user_agent'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN user_agent TEXT AFTER referrer");
			}
			
			// Check if screen_resolution column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'screen_resolution'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN screen_resolution VARCHAR(50) AFTER user_agent");
			}
			
			// Check if language column exists
			$columns = $wpdb->get_results("SHOW COLUMNS FROM $visitor_table LIKE 'language'");
			if (empty($columns)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD COLUMN language VARCHAR(50) AFTER screen_resolution");
			}
			
			// Remove UNIQUE constraint from ip column if it exists
			$indexes = $wpdb->get_results("SHOW INDEX FROM $visitor_table WHERE Key_name = 'ip_unique'");
			if (!empty($indexes)) {
				$wpdb->query("ALTER TABLE $visitor_table DROP INDEX ip_unique");
				// Add regular index instead
				$wpdb->query("ALTER TABLE $visitor_table ADD INDEX ip_index (ip)");
			}
			
			// Add visiting_time index if not exists
			$indexes = $wpdb->get_results("SHOW INDEX FROM $visitor_table WHERE Key_name = 'visiting_time_index'");
			if (empty($indexes)) {
				$wpdb->query("ALTER TABLE $visitor_table ADD INDEX visiting_time_index (visiting_time)");
			}
		}
	}

}
