<?php

/**
 * LinkCentral Updater Class
 *
 * This class handles the (database) update logic for the plugin.
 */
if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly 

class LinkCentral_Updater {
    /**
     * Check and apply database updates.
     */
    public static function update_db_check() {
        $installed_version = get_option('linkcentral_db_version');

        // Only run updates if the current site's DB version is outdated
        if (version_compare($installed_version, LINKCENTRAL_DB_VERSION, '>=')) {
            return; // Already up to date
        }

        // Version 1.0.1 
        if (version_compare($installed_version, '1.0.1', '<')) {
            self::update_to_1_0_1();
        }

        // Version 1.0.2
        if (version_compare($installed_version, '1.0.2', '<')) {
            self::update_to_1_0_2();
        }

        // Version 1.0.3
        if (version_compare($installed_version, '1.0.3', '<')) {
            self::update_to_1_0_3();
        }

        // Version 1.0.4
        if (version_compare($installed_version, '1.0.4', '<')) {
            self::update_to_1_0_4();
        }

        // Version 1.0.5
        if (version_compare($installed_version, '1.0.5', '<')) {
            self::update_to_1_0_5();
        }

        // Version 1.0.6
        if (version_compare($installed_version, '1.0.6', '<')) {
            self::update_to_1_0_6();
        }

        // Version 1.0.7 - Add per-link unique click tracking
        if (version_compare($installed_version, '1.0.7', '<')) {
            self::update_to_1_0_7();
        }

        // Version 1.0.8 - Remove IP address column for privacy
        if (version_compare($installed_version, '1.0.8', '<')) {
            self::update_to_1_0_8();
        }

        // Version 1.0.9 - Add region and city columns for extended geolocation tracking
        if (version_compare($installed_version, '1.0.9', '<')) {
            self::update_to_1_0_9();
        }

        // Version 1.0.10 - Remove unused MaxMind last-update option
        if (version_compare($installed_version, '1.0.10', '<')) {
            self::update_to_1_0_10();
        }

        // Update the database version for this site
        update_option('linkcentral_db_version', LINKCENTRAL_DB_VERSION);
    }
    

    /**
     * Update to version 1.0.1.
     * This version introduces custom capabilities.
     */
    private static function update_to_1_0_1() {
        // Use the capability assignment function from the activator
        LinkCentral_Activator::add_linkcentral_capabilities(true);
    }

    /**
     * Update to version 1.0.2.
     * This version updates the condition strings in dynamic redirects to use underscores instead of spaces.
     */
    private static function update_to_1_0_2() {
        $posts = get_posts(array(
            'post_type' => 'linkcentral_link',
            'posts_per_page' => -1,
            'meta_query' => array(
                array(
                    'key' => '_linkcentral_dynamic_rules',
                    'compare' => 'EXISTS'
                )
            )
        ));

        if (empty($posts)) {
            return;
        }

        $condition_mappings = array(
            'is not' => 'is_not',
            'is between' => 'is_between',
            'is not between' => 'is_not_between',
            'is before' => 'is_before',
            'is after' => 'is_after',
            'is on' => 'is_on',
            'is set' => 'is_set',
            'is not set' => 'is_not_set',
            'has value' => 'has_value',
            'is more than' => 'is_more_than',
            'is fewer than' => 'is_fewer_than',
            'is every' => 'is_every'
        );

        foreach ($posts as $post) {
            $rules = get_post_meta($post->ID, '_linkcentral_dynamic_rules', true);
            
            if (empty($rules)) {
                continue;
            }

            if (is_string($rules) && !is_serialized($rules)) {
                $rules = json_decode($rules, true);
            }

            if (!is_array($rules)) {
                continue;
            }

            $updated = false;

            foreach ($rules as &$rule) {
                if (!isset($rule['variables']) || !is_array($rule['variables'])) {
                    continue;
                }

                foreach ($rule['variables'] as &$variable) {
                    if (isset($variable[1]) && is_string($variable[1])) {
                        foreach ($condition_mappings as $old => $new) {
                            if (strtolower($variable[1]) === strtolower($old)) {
                                $variable[1] = $new;
                                $updated = true;
                                break;
                            }
                        }
                    }
                }
            }

            if ($updated) {
                update_metadata('post', $post->ID, '_linkcentral_dynamic_rules', $rules);
            }
        }
    }

    /**
     * Update to version 1.0.3.
     * This version adds the country column to the linkcentral_stats table for geolocation tracking
     * and fixes the click_date column default value.
     */
    private static function update_to_1_0_3() {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'linkcentral_stats');
        
        // Check if the country column already exists
        $column_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'country'",
            DB_NAME,
            $table_name
        ));
        
        // Add the country column if it doesn't exist
        if (empty($column_exists)) {
            $wpdb->query("ALTER TABLE `{$wpdb->prefix}linkcentral_stats` ADD COLUMN country varchar(2) DEFAULT '' AFTER destination_url");
        }
        
        // Fix the click_date column default value
        $wpdb->query("ALTER TABLE `{$wpdb->prefix}linkcentral_stats` MODIFY COLUMN click_date datetime DEFAULT CURRENT_TIMESTAMP NOT NULL");
    }

    /**
     * Update to version 1.0.4.
     * This version adds database indexes for optimal performance on the linkcentral_stats table
     * and creates the linkcentral_keywords table for auto-keyword linking.
     */
    private static function update_to_1_0_4() {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'linkcentral_stats');
        
        // Check if indexes already exist to avoid duplicate creation
        $existing_indexes = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM %s", $table_name));
        $index_names = array();
        
        foreach ($existing_indexes as $index) {
            $index_names[] = $index->Key_name;
        }
        
        // Primary composite index for most common queries (link_id + date filtering)
        if (!in_array('idx_link_date', $index_names)) {
            $wpdb->query("CREATE INDEX idx_link_date ON `{$wpdb->prefix}linkcentral_stats` (link_id, click_date)");
        }
        
        // Index for date-only queries (used in cleanup, date range reports)
        if (!in_array('idx_click_date', $index_names)) {
            $wpdb->query("CREATE INDEX idx_click_date ON `{$wpdb->prefix}linkcentral_stats` (click_date)");
        }
        
        // Index for referrer analysis (limited to 255 chars for performance)
        if (!in_array('idx_referring_url', $index_names)) {
            $wpdb->query("CREATE INDEX idx_referring_url ON `{$wpdb->prefix}linkcentral_stats` (referring_url(255))");
        }

        // Create the linkcentral_keywords table for auto-keyword linking
        $keywords_table = $wpdb->prefix . 'linkcentral_keywords';
        $charset_collate = $wpdb->get_charset_collate();
        $sql = "CREATE TABLE $keywords_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            link_id mediumint(9) NOT NULL,
            keyword varchar(255) NOT NULL,
            density enum('high', 'medium', 'low') NOT NULL DEFAULT 'medium',
            PRIMARY KEY (id),
            KEY link_id (link_id),
            KEY keyword (keyword(50))
        ) $charset_collate;";
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    /**
     * Update to version 1.0.5.
     * This version ensures the linkcentral_keywords table exists for all users,
     * regardless of their previous version, to fix auto-keyword linking issues.
     */
    private static function update_to_1_0_5() {
        global $wpdb;
        $keywords_table = esc_sql($wpdb->prefix . 'linkcentral_keywords');
        
        // Check if the keywords table exists
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $keywords_table)) === $keywords_table;
        
        if (!$table_exists) {
            // Create the keywords table if it doesn't exist
            $charset_collate = $wpdb->get_charset_collate();
            $sql = "CREATE TABLE $keywords_table (
                id mediumint(9) NOT NULL AUTO_INCREMENT,
                link_id mediumint(9) NOT NULL,
                keyword varchar(255) NOT NULL,
                density enum('high', 'medium', 'low') NOT NULL DEFAULT 'medium',
                PRIMARY KEY (id),
                KEY link_id (link_id),
                KEY keyword (keyword(50))
            ) $charset_collate;";
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
        }
    }

    /**
     * Update to version 1.0.6.
     * This version migrates old auto-keyword linking settings to the new unified structure
     * that supports custom post types and taxonomies.
     */
    private static function update_to_1_0_6() {
        // Only run for premium users who have the auto-keyword linking feature
        if (!function_exists('linkcentral_fs') || !linkcentral_fs()->can_use_premium_code__premium_only()) {
            return;
        }

        // Check if we have old settings to migrate
        $has_old_settings = (
            get_option('linkcentral_keyword_replace_posts', null) !== null ||
            get_option('linkcentral_keyword_replace_pages', null) !== null ||
            get_option('linkcentral_keyword_replace_home', null) !== null ||
            get_option('linkcentral_keyword_replace_blog', null) !== null ||
            get_option('linkcentral_keyword_replace_archive', null) !== null
        );

        if (!$has_old_settings) {
            return;
        }

        // Migrate to new unified structure
        $content_types = array(
            'post_types' => array(),
            'special_pages' => array(
                'home' => get_option('linkcentral_keyword_replace_home', false),
                'blog' => get_option('linkcentral_keyword_replace_blog', false),
                'archive' => get_option('linkcentral_keyword_replace_archive', false)
            )
        );

        // Migrate post types
        if (get_option('linkcentral_keyword_replace_posts', false)) {
            $content_types['post_types']['post'] = true;
        }
        if (get_option('linkcentral_keyword_replace_pages', false)) {
            $content_types['post_types']['page'] = true;
        }

        // Save the new unified settings
        update_option('linkcentral_keyword_content_types', $content_types);

        // Clean up old settings
        delete_option('linkcentral_keyword_replace_posts');
        delete_option('linkcentral_keyword_replace_pages');
        delete_option('linkcentral_keyword_replace_home');
        delete_option('linkcentral_keyword_replace_blog');
        delete_option('linkcentral_keyword_replace_archive');
    }

    /**
     * Update to version 1.0.7.
     * This version adds per-link unique click tracking with first_click column
     * and migrates existing click data to mark first clicks per visitor per link.
     */
    private static function update_to_1_0_7() {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'linkcentral_stats');

        // Check if the first_click column already exists
        $column_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'first_click'",
            DB_NAME,
            $table_name
        ));

        // Add the first_click column if it doesn't exist
        if (empty($column_exists)) {
            $wpdb->query("ALTER TABLE `{$wpdb->prefix}linkcentral_stats` ADD COLUMN first_click TINYINT DEFAULT 0 AFTER country");

            // Migrate existing data: mark the first click from each visitor to each link as first_click = 1
            // This provides a reasonable approximation for historical unique click tracking
            // Only process clicks that have valid visitor_id values
            $wpdb->query("
                UPDATE `{$wpdb->prefix}linkcentral_stats` AS main
                INNER JOIN (
                    SELECT MIN(id) as first_id
                    FROM `{$wpdb->prefix}linkcentral_stats`
                    WHERE visitor_id IS NOT NULL
                      AND visitor_id != ''
                      AND visitor_id != '0'
                    GROUP BY link_id, visitor_id
                ) AS first_clicks ON main.id = first_clicks.first_id
                SET main.first_click = 1
            ");
        }
    }

    /**
     * Update to version 1.0.8.
     * This version removes the ip_address column from the linkcentral_stats table for privacy reasons.
     */
    private static function update_to_1_0_8() {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'linkcentral_stats');

        // Check if the ip_address column exists
        $column_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'ip_address'",
            DB_NAME,
            $table_name
        ));

        // Drop the ip_address column if it exists
        if (!empty($column_exists)) {
            $wpdb->query("ALTER TABLE `{$wpdb->prefix}linkcentral_stats` DROP COLUMN ip_address");
        }
    }

    /**
     * Update to version 1.0.9.
     * This version adds region and city columns to the linkcentral_stats table
     * for extended geolocation tracking (alongside the existing country column).
     * 
     * Also cleans up the deprecated auto-keyword linking option and migrates
     * broken links ignore domains to the normalized array format.
     */
    private static function update_to_1_0_9() {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'linkcentral_stats');

        // Add region column if it does not exist
        $region_column_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'region'",
            DB_NAME,
            $table_name
        ));

        if (empty($region_column_exists)) {
            $wpdb->query("ALTER TABLE `{$wpdb->prefix}linkcentral_stats` ADD COLUMN region varchar(50) DEFAULT '' AFTER country");
        }

        // Add city column if it does not exist
        $city_column_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'city'",
            DB_NAME,
            $table_name
        ));

        if (empty($city_column_exists)) {
            $wpdb->query("ALTER TABLE `{$wpdb->prefix}linkcentral_stats` ADD COLUMN city varchar(100) DEFAULT '' AFTER region");
        }

        // Clean up deprecated auto-keyword linking option.
        if (get_option('linkcentral_keyword_open_new_tab', null) !== null) {
            delete_option('linkcentral_keyword_open_new_tab');
        }

        // Migrate legacy broken links ignore domains text to normalized array format.
        $ignore_domains = get_option('linkcentral_broken_links_ignore_domains', null);
        if (is_string($ignore_domains) && $ignore_domains !== '') {
            $parts = preg_split('/[\s,]+/', $ignore_domains);
            if ($parts && is_array($parts)) {
                $sanitized = array();
                foreach ($parts as $part) {
                    $part = strtolower(trim((string) $part));
                    if ($part === '') {
                        continue;
                    }

                    if (preg_match('#^https?://#', $part)) {
                        $parsed_host = wp_parse_url($part, PHP_URL_HOST);
                        $part = is_string($parsed_host) ? strtolower(trim($parsed_host)) : '';
                    } else {
                        $part = preg_replace('#/.*$#', '', $part);
                    }

                    $part = trim((string) $part, ". \t\n\r\0\x0B");
                    $part = preg_replace('/^www\./', '', $part);
                    $part = preg_replace('/[^a-z0-9.-]/', '', $part);

                    if ($part === '' || strpos($part, '.') === false) {
                        continue;
                    }

                    $sanitized[] = $part;
                }

                update_option('linkcentral_broken_links_ignore_domains', array_values(array_unique($sanitized)));
            }
        }
    }

    /**
     * Update to version 1.0.10.
     * Remove the unused MaxMind last-update option.
     */
    private static function update_to_1_0_10() {
        delete_option('linkcentral_maxmind_last_update');
    }
}

/**
 * Database update check callback.
 */
function linkcentral_update_db_check() {
    LinkCentral_Updater::update_db_check();
}