<?php

/**
 * Redirects Table Installer
 *
 * Handles creation and management of redirect-related database tables
 *
 * @package ProRank\SEO\Database
 * @since   1.5.0
 */

declare(strict_types=1);

namespace ProRank\SEO\Database;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}


/**
 * RedirectsTableInstaller class
 */


class RedirectsTableInstaller {
    
    /**
     * Install redirect tables
     *
     * @return void
     */
    public static function install(): void {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Create redirects table
        self::create_redirects_table($charset_collate);
        
        // Create 404 monitor table
        self::create_404_monitor_table($charset_collate);
        
        // Create redirect logs table for analytics
        self::create_redirect_logs_table($charset_collate);
        
        // Update database version
        update_option('prorank_redirects_db_version', '1.0.0');
    }
    
    /**
     * Create redirects table
     *
     * @param string $charset_collate Character set and collation
     * @return void
     */
    private static function create_redirects_table(string $charset_collate): void {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'prorank_redirects';
        
        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            source_url varchar(2048) NOT NULL,
            target_url varchar(2048) NOT NULL,
            type int(3) NOT NULL DEFAULT 301,
            is_regex tinyint(1) NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'active',
            hits bigint(20) unsigned NOT NULL DEFAULT 0,
            last_hit_timestamp datetime DEFAULT NULL,
            priority int(11) NOT NULL DEFAULT 0,
            notes text DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY (id),
            KEY source_url (source_url(191)),
            KEY status (status),
            KEY is_regex (is_regex),
            KEY priority (priority),
            KEY hits (hits),
            KEY last_hit_timestamp (last_hit_timestamp),
            KEY updated_at (updated_at),
            KEY status_regex_priority (status, is_regex, priority)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
    
    /**
     * Create 404 monitor table
     *
     * @param string $charset_collate Character set and collation
     * @return void
     */
    private static function create_404_monitor_table(string $charset_collate): void {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'prorank_404_monitor';
        
        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            url varchar(2048) NOT NULL,
            referrer varchar(2048) DEFAULT NULL,
            user_agent varchar(500) DEFAULT NULL,
            ip_address varchar(45) DEFAULT NULL,
            hits int(11) NOT NULL DEFAULT 1,
            first_hit datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_hit datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            status varchar(20) NOT NULL DEFAULT 'unresolved',
            redirect_id bigint(20) unsigned DEFAULT NULL,
            auto_suggested tinyint(1) NOT NULL DEFAULT 0,
            suggestion_url varchar(2048) DEFAULT NULL,
            similarity_score decimal(5,2) DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY url_unique (url(191)),
            KEY status (status),
            KEY hits (hits),
            KEY last_hit (last_hit),
            KEY redirect_id (redirect_id),
            KEY auto_suggested (auto_suggested),
            KEY status_hits (status, hits)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
    
    /**
     * Create redirect logs table for analytics
     *
     * @param string $charset_collate Character set and collation
     * @return void
     */
    private static function create_redirect_logs_table(string $charset_collate): void {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'prorank_redirect_logs';
        
        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            redirect_id bigint(20) unsigned NOT NULL,
            timestamp datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            referrer varchar(2048) DEFAULT NULL,
            user_agent varchar(500) DEFAULT NULL,
            ip_address varchar(45) DEFAULT NULL,
            country_code varchar(2) DEFAULT NULL,
            device_type varchar(20) DEFAULT NULL,
            browser varchar(50) DEFAULT NULL,
            os varchar(50) DEFAULT NULL,
            response_time int(11) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY redirect_id (redirect_id),
            KEY timestamp (timestamp),
            KEY redirect_timestamp (redirect_id, timestamp),
            KEY country_code (country_code),
            KEY device_type (device_type)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Add index for analytics queries (idempotent guard for upgrades/reinstalls).
        $analytics_index_exists = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
                 WHERE table_schema = DATABASE()
                 AND table_name = %s
                 AND index_name = 'analytics_idx'",
                $table_name
            )
        );
        if ($analytics_index_exists === 0) {
            $wpdb->query("ALTER TABLE {$table_name} ADD INDEX analytics_idx (redirect_id, timestamp, country_code, device_type)");
        }
    }
    
    /**
     * Uninstall redirect tables
     *
     * @return void
     */
    public static function uninstall(): void {
        global $wpdb;
        
        $tables = [
            $wpdb->prefix . 'prorank_redirects',
            $wpdb->prefix . 'prorank_404_monitor',
            $wpdb->prefix . 'prorank_redirect_logs',
        ];
        
        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS {$table}");
        }
        
        // Remove database version option
        delete_option('prorank_redirects_db_version');
    }
    
    /**
     * Check if tables exist
     *
     * @return bool
     */
    public static function tables_exist(): bool {
        global $wpdb;
        
        $redirects_table = $wpdb->prefix . 'prorank_redirects';
        $result = $wpdb->get_var("SHOW TABLES LIKE '$redirects_table'");
        
        return $result === $redirects_table;
    }
    
    /**
     * Migrate existing data if needed
     *
     * @return void
     */
    public static function migrate(): void {
        $current_version = get_option('prorank_redirects_db_version', '0.0.0');

        // Add migration logic here as needed for future updates
        if (version_compare($current_version, '1.0.0', '<')) {
            self::install();
        }

        // 2025 Enhancement: Add updated_at index for better sorting performance
        if (version_compare($current_version, '1.0.1', '<')) {
            self::add_updated_at_index();
            update_option('prorank_redirects_db_version', '1.0.1');
        }
    }

    /**
     * Add updated_at index for better sorting performance
     *
     * 2025 Enhancement: Improves "Recently Updated" sorting performance
     *
     * @return void
     */
    private static function add_updated_at_index(): void {
        global $wpdb;

        $table_name = $wpdb->prefix . 'prorank_redirects';

        // Check if index already exists
        $index_exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
                WHERE table_schema = DATABASE()
                AND table_name = %s
                AND index_name = 'updated_at'",
                $table_name
            )
        );

        if (!$index_exists) {
            $wpdb->query("ALTER TABLE {$table_name} ADD INDEX updated_at (updated_at)");
        }
    }
    
    /**
     * Clean old logs
     *
     * @param int $days Number of days to keep logs
     * @return int Number of deleted rows
     */
    public static function clean_old_logs(int $days = 30): int {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'prorank_redirect_logs';
        $cutoff_date = wp_date('Y-m-d H:i:s', strtotime("-{$days} days"));
        
        $deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$table_name} WHERE timestamp < %s",
                $cutoff_date
            )
        );
        
        return $deleted ?: 0;
    }
    
    /**
     * Optimize tables
     *
     * @return void
     */
    public static function optimize_tables(): void {
        global $wpdb;
        
        $tables = [
            $wpdb->prefix . 'prorank_redirects',
            $wpdb->prefix . 'prorank_404_monitor',
            $wpdb->prefix . 'prorank_redirect_logs',
        ];
        
        foreach ($tables as $table) {
            $wpdb->query("OPTIMIZE TABLE {$table}");
        }
    }
}
