<?php
defined( 'ABSPATH' ) || exit;

class RT_WA_Analytics_Install {

	public static function activate() {
		self::run_migrations();
		flush_rewrite_rules();
	}

	public static function deactivate() {
		flush_rewrite_rules();
	}

	/**
	 * Versioned migration, run on every load.
	 *
	 * dbDelta is idempotent and never drops existing data, so this is safe to
	 * call on every version bump: it only runs when rt_wa_db_version differs
	 * from the constant (including a missing option from pre-1.3.0 installs).
	 *
	 * The sensitive-data migration is also idempotent. If it cannot complete
	 * (for example OpenSSL is unavailable), the version option is left stale
	 * so the migration retries on the next request instead of leaving a mix
	 * of plaintext and ciphertext marked as "done".
	 */
	public static function maybe_upgrade() {
		if ( get_option( 'rt_wa_db_version' ) === RT_WA_ANALYTICS_DB_VERSION ) {
			return;
		}
		self::run_migrations();
	}

	private static function run_migrations() {
		self::create_tables();
		self::set_default_options();
		self::migrate_sensitive_data();
		update_option( 'rt_wa_db_version', RT_WA_ANALYTICS_DB_VERSION );
	}

	public static function create_tables() {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();

		$visitors_table   = $wpdb->prefix . 'rt_wa_visitors';
		$sessions_table   = $wpdb->prefix . 'rt_wa_sessions';
		$events_table     = $wpdb->prefix . 'rt_wa_events';
		$api_keys_table   = $wpdb->prefix . 'rt_wa_api_keys';
		$page_views_table = $wpdb->prefix . 'rt_wa_page_views';
		$leads_table      = $wpdb->prefix . 'rt_wa_leads';
		$history_table    = $wpdb->prefix . 'rt_wa_lead_status_history';
		$utm_links_table  = $wpdb->prefix . 'rt_wa_utm_links';

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

		$sql = "CREATE TABLE {$visitors_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            uuid VARCHAR(36) NOT NULL UNIQUE,
            first_seen DATETIME NOT NULL,
            last_seen DATETIME NOT NULL,
            sessions_count INT UNSIGNED DEFAULT 0,
            wa_clicks INT UNSIGNED DEFAULT 0,
            device VARCHAR(50),
            os VARCHAR(50),
            browser VARCHAR(50),
            language VARCHAR(20),
            country VARCHAR(100),
            ip VARCHAR(45),
            first_source VARCHAR(100),
            first_campaign VARCHAR(200),
            referrer VARCHAR(500),
            landing_page VARCHAR(500),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_uuid (uuid),
            INDEX idx_last_seen (last_seen),
            INDEX idx_first_source (first_source)
        ) {$charset_collate};";
		dbDelta( $sql );

		$sql = "CREATE TABLE {$sessions_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            session_id VARCHAR(64) NOT NULL UNIQUE,
            visitor_uuid VARCHAR(36) NOT NULL,
            start_time DATETIME NOT NULL,
            end_time DATETIME,
            duration INT UNSIGNED DEFAULT 0,
            source VARCHAR(100),
            campaign VARCHAR(200),
            landing_page VARCHAR(500),
            referrer VARCHAR(500),
            medium VARCHAR(100),
            utm_source VARCHAR(200),
            utm_medium VARCHAR(200),
            utm_campaign VARCHAR(200),
            utm_content VARCHAR(200),
            utm_term VARCHAR(200),
            click_ids TEXT,
            navigation_path TEXT,
            country VARCHAR(100),
            device VARCHAR(50),
            os VARCHAR(50),
            browser VARCHAR(50),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_session_id (session_id),
            INDEX idx_visitor_uuid (visitor_uuid),
            INDEX idx_start_time (start_time)
        ) {$charset_collate};";
		dbDelta( $sql );

		$sql = "CREATE TABLE {$events_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            event_type VARCHAR(50) NOT NULL,
            visitor_uuid VARCHAR(36),
            session_id VARCHAR(64),
            page_url VARCHAR(500),
            timestamp DATETIME NOT NULL,
            whatsapp_number TEXT,
            referrer VARCHAR(500),
            utm TEXT,
            device VARCHAR(50),
            browser VARCHAR(50),
            os VARCHAR(50),
            country VARCHAR(100),
            raw_data JSON,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_event_type (event_type),
            INDEX idx_visitor_uuid (visitor_uuid),
            INDEX idx_session_id (session_id),
            INDEX idx_timestamp (timestamp)
        ) {$charset_collate};";
		dbDelta( $sql );

		$sql = "CREATE TABLE {$api_keys_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            api_key VARCHAR(100) NOT NULL UNIQUE,
            description VARCHAR(200),
            permissions VARCHAR(100) DEFAULT 'read',
            rate_limit INT UNSIGNED DEFAULT 60,
            last_used DATETIME,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            status VARCHAR(20) DEFAULT 'active',
            INDEX idx_api_key (api_key)
        ) {$charset_collate};";
		dbDelta( $sql );

		$sql = "CREATE TABLE {$page_views_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            visitor_uuid VARCHAR(36),
            session_id VARCHAR(64),
            page_url VARCHAR(500),
            page_title VARCHAR(500),
            time_on_page INT UNSIGNED DEFAULT 0,
            timestamp DATETIME NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_visitor_uuid (visitor_uuid),
            INDEX idx_session_id (session_id),
            INDEX idx_timestamp (timestamp)
        ) {$charset_collate};";
		dbDelta( $sql );

		$sql = "CREATE TABLE {$leads_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            visitor_uuid VARCHAR(36),
            session_id VARCHAR(64),
            event_id BIGINT UNSIGNED,
            wa_phone TEXT,
            name VARCHAR(200),
            phone TEXT,
            email VARCHAR(200),
            phone_lookup VARCHAR(64) DEFAULT '',
            wa_phone_lookup VARCHAR(64) DEFAULT '',
            notes TEXT,
            status VARCHAR(20) DEFAULT 'new',
            source VARCHAR(100),
            medium VARCHAR(100),
            campaign VARCHAR(200),
            term VARCHAR(200),
            content VARCHAR(200),
            landing_page VARCHAR(500),
            lead_value DECIMAL(12,2) DEFAULT 0,
            currency VARCHAR(10) DEFAULT 'USD',
            created_at DATETIME NOT NULL,
            updated_at DATETIME,
            converted_at DATETIME,
            INDEX idx_visitor_uuid (visitor_uuid),
            INDEX idx_session_id (session_id),
            INDEX idx_event_id (event_id),
            INDEX idx_status (status),
            INDEX idx_created_at (created_at),
            INDEX idx_campaign (campaign),
            INDEX idx_source (source),
            INDEX idx_phone_lookup (phone_lookup),
            INDEX idx_wa_phone_lookup (wa_phone_lookup)
        ) {$charset_collate};";
		dbDelta( $sql );

		$sql = "CREATE TABLE {$history_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            lead_id BIGINT UNSIGNED NOT NULL,
            status VARCHAR(20) NOT NULL,
            changed_at DATETIME NOT NULL,
            note TEXT,
            INDEX idx_lead_id (lead_id),
            INDEX idx_status (status),
            INDEX idx_changed_at (changed_at)
        ) {$charset_collate};";
		dbDelta( $sql );

		$sql = "CREATE TABLE {$utm_links_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(200) NOT NULL,
            base_url VARCHAR(500) NOT NULL,
            wa_number TEXT,
            utm_source VARCHAR(150),
            utm_medium VARCHAR(150),
            utm_campaign VARCHAR(200),
            utm_term VARCHAR(150),
            utm_content VARCHAR(200),
            final_url VARCHAR(500) NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_final_url (final_url(191)),
            INDEX idx_campaign (utm_campaign),
            INDEX idx_created_at (created_at)
        ) {$charset_collate};";
		dbDelta( $sql );
	}

	public static function set_default_options() {
		$defaults = array(
			'rt_wa_enable_tracking'                 => 'yes',
			'rt_wa_cookie_lifetime'                 => '30',
			'rt_wa_session_timeout'                 => '30',
			'rt_wa_ignore_logged_in'                => 'no',
			'rt_wa_ignore_admin'                    => 'yes',
			'rt_wa_excluded_pages'                  => '',
			'rt_wa_whatsapp_link_patterns'          => '',
			'rt_wa_excluded_roles'                  => array(),
			'rt_wa_debug_mode'                      => 'no',
			'rt_wa_integration_meta_enable'         => 'no',
			'rt_wa_integration_meta_pixel_id'       => '',
			'rt_wa_integration_meta_event_name'     => '',
			'rt_wa_integration_ga4_enable'          => 'no',
			'rt_wa_integration_ga4_id'              => '',
			'rt_wa_integration_gtm_enable'          => 'no',
			'rt_wa_integration_gtm_id'              => '',
			'rt_wa_integration_tiktok_pixel_id'     => '',
			'rt_wa_integration_linkedin_insight_id' => '',
			'rt_wa_integration_snapchat_pixel_id'   => '',
			'rt_wa_integration_pinterest_tag_id'    => '',
			'rt_wa_integration_twitter_pixel_id'    => '',
			'rt_wa_integration_google_ads_id'       => '',
			'rt_wa_integration_bing_uet_id'         => '',
			'rt_wa_integration_clarity_project_id'  => '',
			'rt_wa_integration_reddit_pixel_id'     => '',
			'rt_wa_integration_quora_pixel_id'      => '',
			'rt_wa_integration_yandex_metrika_id'   => '',
			'rt_wa_integration_hubspot_id'          => '',
			'rt_wa_integration_hotjar_id'           => '',
			'rt_wa_privacy_consent_enable'          => 'no',
			'rt_wa_privacy_gdpr_mode'               => 'no',
			'rt_wa_privacy_mask_ip'                 => 'yes',
			'rt_wa_privacy_retention_days'          => '365',
			'rt_wa_privacy_policy_template'         => '',
			'rt_wa_api_rate_limit'                  => '60',
			'rt_wa_lead_auto_create'                => 'no',
			'rt_wa_lead_currency'                   => 'USD',
			'rt_wa_utm_default_enable'              => 'no',
			'rt_wa_utm_default_source'              => '',
			'rt_wa_utm_default_medium'              => '',
			'rt_wa_utm_default_campaign'            => '',
			'rt_wa_utm_default_term'                => '',
			'rt_wa_utm_default_content'             => '',
		);

		foreach ( $defaults as $key => $value ) {
			if ( get_option( $key ) === false ) {
				update_option( $key, $value );
			}
		}
	}

	/**
	 * Encrypt legacy plaintext phone numbers at rest (idempotent).
	 *
	 * Runs after the schema has been widened. Each existing value is detected
	 * with is_encrypted(); already-encrypted values are left untouched, so the
	 * routine can be interrupted and resumed safely. Values are encrypted in
	 * place (ciphertext overwrites the plaintext), so no plaintext copy
	 * remains. Batch size and an offset checkpoint keep memory bounded.
	 *
	 * If OpenSSL is unavailable the routine returns immediately without
	 * touching data; maybe_upgrade() keeps the version stale so this retries.
	 */
	public static function migrate_sensitive_data() {
		$encryption = RT_WA_Analytics_Encryption::instance();
		if ( ! $encryption->is_available() ) {
			return;
		}

		global $wpdb;
		$leads_table  = $wpdb->prefix . 'rt_wa_leads';
		$events_table = $wpdb->prefix . 'rt_wa_events';

		$leads_columns = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
			$wpdb->prepare( 'SHOW COLUMNS FROM %i', $leads_table )
		);
		if ( ! in_array( 'phone_lookup', $leads_columns, true ) ) {
			return;
		}

		self::migrate_leads_phones( $encryption );
		self::migrate_event_phones( $encryption );
		self::migrate_event_raw_data();
		self::migrate_utm_link_numbers( $encryption );
	}

	/**
	 * Encrypt phone / wa_phone in the leads table in batches.
	 */
	private static function migrate_leads_phones( $encryption ) {
		global $wpdb;
		$leads_table = $wpdb->prefix . 'rt_wa_leads';
		$batch       = 100;
		$processed   = (int) get_option( 'rt_wa_mig_leads_offset', 0 );

		while ( true ) {
			$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->prepare(
					'SELECT id, phone, wa_phone FROM %i WHERE id > %d ORDER BY id ASC LIMIT %d',
					$leads_table,
					$processed,
					$batch
				)
			);
			if ( empty( $rows ) ) {
				break;
			}

			foreach ( $rows as $row ) {
				$update = array();

				if ( $row->phone !== null && $row->phone !== '' && ! $encryption->is_encrypted( $row->phone ) ) {
					$cipher = $encryption->encrypt( $row->phone );
					if ( $cipher === false ) {
						return;
					}
					$update['phone']        = $cipher;
					$update['phone_lookup'] = $encryption->lookup_token( $row->phone );
				}
				if ( $row->wa_phone !== null && $row->wa_phone !== '' && ! $encryption->is_encrypted( $row->wa_phone ) ) {
					$cipher = $encryption->encrypt( $row->wa_phone );
					if ( $cipher === false ) {
						return;
					}
					$update['wa_phone']        = $cipher;
					$update['wa_phone_lookup'] = $encryption->lookup_token( $row->wa_phone );
				}

				if ( ! empty( $update ) ) {
					$wpdb->update( $leads_table, $update, array( 'id' => $row->id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
				}
				$processed = (int) $row->id;
			}

			update_option( 'rt_wa_mig_leads_offset', $processed );
		}

		delete_option( 'rt_wa_mig_leads_offset' );
	}

	/**
	 * Encrypt whatsapp_number in the events table in batches.
	 */
	private static function migrate_event_phones( $encryption ) {
		global $wpdb;
		$events_table = $wpdb->prefix . 'rt_wa_events';
		$batch        = 100;
		$processed    = (int) get_option( 'rt_wa_mig_events_offset', 0 );

		while ( true ) {
			$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->prepare(
					'SELECT id, whatsapp_number FROM %i WHERE id > %d ORDER BY id ASC LIMIT %d',
					$events_table,
					$processed,
					$batch
				)
			);
			if ( empty( $rows ) ) {
				break;
			}

			foreach ( $rows as $row ) {
				if ( $row->whatsapp_number === null || $row->whatsapp_number === '' || $encryption->is_encrypted( $row->whatsapp_number ) ) {
					$processed = (int) $row->id;
					continue;
				}
				$cipher = $encryption->encrypt( $row->whatsapp_number );
				if ( $cipher === false ) {
					return;
				}
				$wpdb->update( $events_table, array( 'whatsapp_number' => $cipher ), array( 'id' => $row->id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
				$processed = (int) $row->id;
			}

			update_option( 'rt_wa_mig_events_offset', $processed );
		}

		delete_option( 'rt_wa_mig_events_offset' );
	}

	/**
	 * Remove any plaintext phone number duplicated inside raw_data JSON
	 * (legacy demo data only; production raw_data carries only the user agent).
	 */
	private static function migrate_event_raw_data() {
		global $wpdb;
		$events_table = $wpdb->prefix . 'rt_wa_events';

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare(
				'SELECT id, raw_data FROM %i WHERE raw_data IS NOT NULL AND raw_data LIKE %s',
				$events_table,
				'%number%'
			)
		);

		foreach ( $rows as $row ) {
			$decoded = json_decode( $row->raw_data, true );
			if ( ! is_array( $decoded ) || ! array_key_exists( 'number', $decoded ) ) {
				continue;
			}
			unset( $decoded['number'] );
			$value = empty( $decoded ) ? null : wp_json_encode( $decoded );
			$wpdb->update( $events_table, array( 'raw_data' => $value ), array( 'id' => $row->id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
		}
	}

	/**
	 * Encrypt wa_number in the UTM links table in batches. Existing installs
	 * have a VARCHAR(50) column that cannot hold ciphertext, so it is widened
	 * to TEXT once (idempotent).
	 */
	private static function migrate_utm_link_numbers( $encryption ) {
		global $wpdb;
		$utm_links_table = $wpdb->prefix . 'rt_wa_utm_links';
		$batch           = 100;
		$processed       = (int) get_option( 'rt_wa_mig_utm_offset', 0 );

		$columns = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- volatile analytics custom table; caching intentionally omitted
			$wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $utm_links_table, 'wa_number' )
		);
		if ( ! empty( $columns ) && stripos( $columns[0]->Type, 'text' ) === false ) {
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i MODIFY wa_number TEXT NULL', $utm_links_table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Table identifiers cannot be expressed as data placeholders.
		}

		while ( true ) {
			$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->prepare(
					'SELECT id, wa_number FROM %i WHERE id > %d ORDER BY id ASC LIMIT %d',
					$utm_links_table,
					$processed,
					$batch
				)
			);
			if ( empty( $rows ) ) {
				break;
			}

			foreach ( $rows as $row ) {
				if ( $row->wa_number === null || $row->wa_number === '' || $encryption->is_encrypted( $row->wa_number ) ) {
					$processed = (int) $row->id;
					continue;
				}
				$cipher = $encryption->encrypt( $row->wa_number );
				if ( $cipher === false ) {
					return;
				}
				$wpdb->update( $utm_links_table, array( 'wa_number' => $cipher ), array( 'id' => $row->id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
				$processed = (int) $row->id;
			}

			update_option( 'rt_wa_mig_utm_offset', $processed );
		}

		delete_option( 'rt_wa_mig_utm_offset' );
	}
}