<?php
// phpcs:disable WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL -- Activation and migrations necessarily manage plugin-owned schemas directly.

/**
 * Fired during plugin activation
 *
 * @link       https://example.com
 * @since      1.0.0
 *
 * @package    Instant_Popup_Builder
 * @subpackage Instant_Popup_Builder/includes
 */

/**
 * Fired during plugin activation.
 *
 * This class defines all code necessary to run during the plugin's activation.
 *
 * @since      1.0.0
 * @package    Instant_Popup_Builder
 * @subpackage Instant_Popup_Builder/includes
 * @author     7th Sky Software’s <example@gmail.com>
 */
class Instant_Popup_Builder_Activator
{

	/**
	 * Short Description. (use period)
	 *
	 * Long Description.
	 *
	 * @since    1.0.0
	 */
	public static function activate()
	{

		global $wpdb;
		$table_name = $wpdb->prefix . 'instant_popup_builder';

		$sql = "CREATE TABLE IF NOT EXISTS $table_name (
			id INT(11) NOT NULL AUTO_INCREMENT,
			type VARCHAR(255) NOT NULL,
			title VARCHAR(255) NOT NULL,
			class VARCHAR(255) NOT NULL,
			views VARCHAR(255) NOT NULL,
			content VARCHAR(10000) NOT NULL,
			status VARCHAR(255) NOT NULL,
			display VARCHAR(255) NOT NULL,
			ipb_trigger TEXT NOT NULL,
			closing VARCHAR(255) NOT NULL,
			position VARCHAR(255) NOT NULL,
    		date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			design VARCHAR(1000) NOT NULL,
			advance LONGTEXT NOT NULL,
			PRIMARY KEY (id)
		);";

		// Create analytics table for detailed tracking
		$analytics_table = $wpdb->prefix . 'instant_popup_analytics';
		$analytics_sql = "CREATE TABLE IF NOT EXISTS $analytics_table (
			id INT(11) NOT NULL AUTO_INCREMENT,
			popup_id INT(11) NOT NULL,
			event_type VARCHAR(50) NOT NULL,
			user_ip VARCHAR(45),
			user_agent TEXT,
			session_id VARCHAR(255),
			page_url TEXT,
			referrer TEXT,
			event_data JSON,
			event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY popup_id (popup_id),
			KEY event_type (event_type),
			KEY event_timestamp (event_timestamp)
		);";

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

		// Set review notice activation date (5-7 days from now)
		$activation_date = time();
		$review_show_date = $activation_date + (5 * DAY_IN_SECONDS); // Show after 5 days

		add_option('ipb_activation_date', $activation_date);
		add_option('ipb_review_show_date', $review_show_date);
		add_option('ipb_show_review_notice', 'yes');

		// Create table
		dbDelta($sql);
		dbDelta($analytics_sql);

		// Schedule analytics cleanup
		if (!wp_next_scheduled('ipb_analytics_cleanup')) {
			wp_schedule_event(time(), 'weekly', 'ipb_analytics_cleanup');
		}

		// Check if targeting column exists and add it if not (for existing installations)
		$upgrade_succeeded = self::upgrade_database();
		self::install_feature_tables();
		if ($upgrade_succeeded) {
			update_option('instant_popup_builder_db_version', defined('INSTANT_POPUP_BUILDER_DB_VERSION') ? INSTANT_POPUP_BUILDER_DB_VERSION : INSTANT_POPUP_BUILDER_VERSION);
		}
	}

	/**
	 * Run schema upgrades after a normal plugin update.
	 *
	 * WordPress does not execute activation hooks when a plugin is updated.
	 * Keeping a separate schema version ensures database repairs run once.
	 */
	public static function maybe_upgrade()
	{
		$target_version = defined('INSTANT_POPUP_BUILDER_DB_VERSION') ? INSTANT_POPUP_BUILDER_DB_VERSION : INSTANT_POPUP_BUILDER_VERSION;

		if (get_option('instant_popup_builder_db_version') === $target_version) {
			return;
		}

		$upgrade_succeeded = self::upgrade_database();
		self::install_feature_tables();
		if ($upgrade_succeeded) {
			update_option('instant_popup_builder_db_version', $target_version);
		}
	}
	/**
	 * Upgrade database schema for existing installations
	 *
	 * @since    1.0.6.2
	 */
	public static function upgrade_database()
	{
		global $wpdb;
		$table_name = $wpdb->prefix . 'instant_popup_builder';
		$escaped_table_name = esc_sql($table_name);
		$analytics_table = $wpdb->prefix . 'instant_popup_analytics';

		// maybe_upgrade() also runs outside the activation hook. On a fresh or
		// partially installed site the main table may therefore not exist yet;
		// create its current schema before issuing any SHOW/ALTER statements.
		$table_exists = $wpdb->get_var(
			$wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table_name))
		);

		if ($table_exists !== $table_name) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';

			$charset_collate = $wpdb->get_charset_collate();
			$sql = "CREATE TABLE $table_name (
				id INT(11) NOT NULL AUTO_INCREMENT,
				type VARCHAR(255) NOT NULL,
				title VARCHAR(255) NOT NULL,
				class VARCHAR(255) NOT NULL,
				views VARCHAR(255) NOT NULL,
				content VARCHAR(10000) NOT NULL,
				status VARCHAR(255) NOT NULL,
				display VARCHAR(255) NOT NULL,
				ipb_trigger TEXT NOT NULL,
				closing VARCHAR(255) NOT NULL,
				position VARCHAR(255) NOT NULL,
				date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				design VARCHAR(1000) NOT NULL,
				advance LONGTEXT NOT NULL,
				targeting VARCHAR(1000) NOT NULL DEFAULT '{}',
				clicks VARCHAR(255) NOT NULL DEFAULT '0',
				PRIMARY KEY (id)
			) $charset_collate;";

			dbDelta($sql);
		}

		// Do not generate a cascade of misleading migration errors if table
		// creation failed (for example, because the DB user lacks CREATE rights).
		$table_exists = $wpdb->get_var(
			$wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table_name))
		);
		if ($table_exists !== $table_name) {
			return false;
		}

		// Rename legacy column `triger` to `ipb_trigger` and ensure type is TEXT
		$ipb_trigger_col = $wpdb->get_row($wpdb->prepare(
			"SHOW COLUMNS FROM `{$escaped_table_name}` LIKE %s",
			'ipb_trigger'
		));
		$legacy_triger_col = $wpdb->get_row($wpdb->prepare(
			"SHOW COLUMNS FROM `{$escaped_table_name}` LIKE %s",
			'triger'
		));
		if (!$ipb_trigger_col && $legacy_triger_col) {
			// Try direct rename first
			$wpdb->query("ALTER TABLE `{$escaped_table_name}` CHANGE `triger` `ipb_trigger` TEXT NOT NULL");
		} elseif ($ipb_trigger_col && $legacy_triger_col) {
			// If both exist (edge case), copy data if target empty then drop legacy
			$wpdb->query("UPDATE `{$escaped_table_name}` SET `ipb_trigger` = CASE WHEN (`ipb_trigger` IS NULL OR `ipb_trigger` = '') THEN `triger` ELSE `ipb_trigger` END");
			$wpdb->query("ALTER TABLE `{$escaped_table_name}` DROP COLUMN `triger`");
		}
		// Ensure ipb_trigger exists and is TEXT
		$ipb_trigger_col = $wpdb->get_row($wpdb->prepare(
			"SHOW COLUMNS FROM `{$escaped_table_name}` LIKE %s",
			'ipb_trigger'
		));
		if (!$ipb_trigger_col) {
			$wpdb->query("ALTER TABLE `{$escaped_table_name}` ADD COLUMN `ipb_trigger` TEXT NOT NULL AFTER `display`");
		}

		// Check if targeting column exists
		$column_exists = $wpdb->get_results($wpdb->prepare(
			"SHOW COLUMNS FROM `{$escaped_table_name}` LIKE %s",
			'targeting'
		));

		// Add targeting column if it doesn't exist
		if (empty($column_exists)) {
			$wpdb->query("ALTER TABLE `{$escaped_table_name}` ADD COLUMN `targeting` VARCHAR(1000) NOT NULL DEFAULT '{}'");
		}

		// Check if clicks column exists
		$clicks_column_exists = $wpdb->get_results($wpdb->prepare(
			"SHOW COLUMNS FROM `{$escaped_table_name}` LIKE %s",
			'clicks'
		));

		// Add clicks column if it doesn't exist
		if (empty($clicks_column_exists)) {
			$wpdb->query("ALTER TABLE `{$escaped_table_name}` ADD COLUMN `clicks` VARCHAR(255) NOT NULL DEFAULT '0'");
		}

		// Expand advance column: VARCHAR(1000) truncates large JSON (e.g. extension popup settings).
		$advance_column = $wpdb->get_row(
			$wpdb->prepare(
				"SHOW COLUMNS FROM `{$escaped_table_name}` LIKE %s",
				'advance'
			)
		);
		if ( $advance_column && isset( $advance_column->Type ) && false !== stripos( $advance_column->Type, 'varchar' ) ) {
			$wpdb->query( "ALTER TABLE `{$escaped_table_name}` MODIFY COLUMN `advance` LONGTEXT NOT NULL" );
		}

		// Check if analytics table exists
		$analytics_table_exists = $wpdb->get_var($wpdb->prepare(
			"SHOW TABLES LIKE %s",
			$analytics_table
		));

		// Create analytics table if it doesn't exist
		if ($analytics_table_exists != $analytics_table) {
			$analytics_sql = "CREATE TABLE IF NOT EXISTS $analytics_table (
				id INT(11) NOT NULL AUTO_INCREMENT,
				popup_id INT(11) NOT NULL,
				event_type VARCHAR(50) NOT NULL,
				user_ip VARCHAR(45),
				user_agent TEXT,
				session_id VARCHAR(255),
				page_url TEXT,
				referrer TEXT,
				event_data JSON,
				event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (id),
				KEY popup_id (popup_id),
				KEY event_type (event_type),
				KEY event_timestamp (event_timestamp)
			);";

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

		return true;
	}
	public static function create_subscription_settings()
	{
		global $wpdb;
		$table_name = $wpdb->prefix . 'instant_popup_subscription_settings';
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE $table_name (
            id INT NOT NULL AUTO_INCREMENT,
            popup_id VARCHAR(255) NOT NULL,
            settings LONGTEXT NOT NULL,
            created_by INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_created_by (created_by),
            KEY idx_created_at (created_at)
        ) $charset_collate;";

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

	/**
	 * Install and repair feature tables without deleting existing data.
	 */
	public static function install_feature_tables()
	{
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset_collate = $wpdb->get_charset_collate();

		self::create_subscription_settings();

		$subscriber_table = $wpdb->prefix . 'instant_popup_subscriber';
		$subscriber_sql = "CREATE TABLE $subscriber_table (
			id INT NOT NULL AUTO_INCREMENT,
			campaign VARCHAR(255) NOT NULL DEFAULT 'newsletter',
			emailaddress VARCHAR(255) NOT NULL,
			lastname VARCHAR(255) NOT NULL DEFAULT '',
			firstname VARCHAR(255) NOT NULL DEFAULT '',
			privacy VARCHAR(255) NOT NULL DEFAULT 'yes',
			status VARCHAR(255) NOT NULL DEFAULT 'active',
			verification_token VARCHAR(255) NULL,
			verification_expires DATETIME NULL,
			created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY unique_email (emailaddress)
		) $charset_collate;";

		$templates_table = $wpdb->prefix . 'instant_popup_templates';
		$templates_sql = "CREATE TABLE $templates_table (
			id INT NOT NULL AUTO_INCREMENT,
			name VARCHAR(255) NOT NULL,
			description TEXT,
			category VARCHAR(100) NOT NULL DEFAULT 'general',
			content LONGTEXT NOT NULL,
			template_data LONGTEXT,
			created_by INT NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_category (category),
			KEY idx_status (status),
			KEY idx_created_by (created_by),
			KEY idx_created_at (created_at)
		) $charset_collate;";

		$campaigns_table = $wpdb->prefix . 'instant_popup_newsletter_campaigns';
		$campaigns_sql = "CREATE TABLE $campaigns_table (
			id INT NOT NULL AUTO_INCREMENT,
			name VARCHAR(255) NOT NULL,
			campaign_associations LONGTEXT NOT NULL,
			sender_name VARCHAR(255) NOT NULL,
			sender_email VARCHAR(255) NOT NULL,
			subject_line VARCHAR(500) NOT NULL,
			template_id INT DEFAULT 0,
			template_content LONGTEXT,
			status VARCHAR(20) NOT NULL DEFAULT 'draft',
			send_type VARCHAR(20) NOT NULL DEFAULT 'now',
			scheduled_date DATE NULL,
			scheduled_time TIME NULL,
			scheduled_datetime DATETIME NULL,
			subscribers_count INT DEFAULT 0,
			sent_count INT DEFAULT 0,
			open_count INT DEFAULT 0,
			click_count INT DEFAULT 0,
			last_sent TIMESTAMP NULL,
			created_by INT NOT NULL,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_status (status),
			KEY idx_send_type (send_type),
			KEY idx_scheduled_datetime (scheduled_datetime),
			KEY idx_created_by (created_by),
			KEY idx_created_at (created_at)
		) $charset_collate;";

		$autoresponders_table = $wpdb->prefix . 'instant_popup_autoresponders';
		$autoresponders_sql = "CREATE TABLE $autoresponders_table (
			id INT NOT NULL AUTO_INCREMENT,
			title VARCHAR(255) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			trigger_rules LONGTEXT NOT NULL,
			campaign_ids LONGTEXT NOT NULL,
			sender_name VARCHAR(255) NOT NULL,
			sender_email VARCHAR(255) NOT NULL,
			reply_to_email VARCHAR(255) DEFAULT '',
			disable_reply TINYINT DEFAULT 0,
			template_id INT DEFAULT 0,
			created_by INT NOT NULL,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_status (status),
			KEY idx_created_by (created_by),
			KEY idx_created_at (created_at)
		) $charset_collate;";

		dbDelta($subscriber_sql);
		dbDelta($templates_sql);
		dbDelta($campaigns_sql);
		dbDelta($autoresponders_sql);
	}
}
