<?php
/**
 * Activator — sets up the plugin's own wp_aetos_seo_* custom tables
 * via dbDelta and runs one-time schema migrations. Every table/column name is
 * bound as an SQL identifier via the %i placeholder (WordPress 6.2+); every
 * value (including LIKE patterns) is bound with %s through $wpdb->prepare().
 * The ALTER TABLE fallback statements are full string literals (column name and
 * type are hardcoded; only the table is bound, via %i), so nothing is
 * interpolated and the PreparedSQL injection sniffs are never suppressed.
 *
 * Direct (uncached) schema queries are intentional — activation/upgrade runs
 * once and must hit the database. Only the non-injection DirectDatabaseQuery
 * sniffs are suppressed file-wide:
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Custom-table schema setup/upgrade via dbDelta; runs once on activation and must hit the DB uncached. These three are the non-injection sniffs only; identifiers are bound with %i and values with %s via $wpdb->prepare(), and the PreparedSQL sniffs are never suppressed.
 */

declare(strict_types=1);

namespace AetosSeo\Core;

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

use AetosSeo\Storage\Schema;

class Activator
{

    /**
     * Verify all plugin tables exist.
     */
    private static function has_required_tables(): bool
    {
        global $wpdb;
        if (!isset($wpdb) || !($wpdb instanceof \wpdb)) {
            return false;
        }

        $schema = new Schema($wpdb);
        $tables = $schema->get_table_names();
        foreach ($tables as $table) {
            $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
            if (!is_string($exists) || $exists !== $table) {
                return false;
            }
        }
        return true;
    }

    /**
     * Run dbDelta for the latest schema.
     *
     * This is separated from activate() so upgrades can run in contexts where
     * there is no logged-in user (e.g. wp-cron, front-end request right after update).
     */
    private static function run_db_delta(): void
    {
        try {
            global $wpdb;

            $schema = new Schema($wpdb);
            $sql_statements = $schema->get_create_table_sql();

            if (!is_array($sql_statements) || empty($sql_statements)) {
                throw new \Exception('No SQL statements generated from schema');
            }

            foreach ($sql_statements as $sql) {
                if (!is_string($sql) || trim($sql) === '') {
                    continue;
                }
                self::apply_schema_statement($sql);
            }

            // Verify all expected tables were created.
            foreach ($schema->get_table_names() as $table) {
                $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
                if ($exists !== $table) {
    if (defined('WP_DEBUG') && WP_DEBUG) { error_log("Aetos SEO: Warning - table {$table} may not have been created"); } // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
                }
            }

        } catch (\Exception $e) {
    if (defined('WP_DEBUG') && WP_DEBUG) { error_log('Aetos SEO DB Delta Error: ' . $e->getMessage()); } // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
            throw $e; // Re-throw to be caught by activate()
        }
    }

    private static function apply_schema_statement(string $sql): void
    {
        global $wpdb;

        if (function_exists('dbDelta')) {
            dbDelta($sql);
            return;
        }

        $table = self::extract_create_table_name($sql);
        if ($table === '') {
            return;
        }

        self::create_known_table_if_missing($table);
    }

    private static function extract_create_table_name(string $sql): string
    {
        if (!preg_match('/^\s*CREATE\s+TABLE\s+`?([^`\s(]+)`?/i', $sql, $m)) {
            return '';
        }

        return (string) $m[1];
    }

    private static function create_known_table_if_missing(string $table): void
    {
        global $wpdb;

        $suffix = self::table_suffix($table);
        switch ($suffix) {
            case 'aetos_seo_runs':
                $wpdb->query($wpdb->prepare(
                    "CREATE TABLE IF NOT EXISTS %i (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                        run_type VARCHAR(20) NOT NULL DEFAULT 'audit',
                        status VARCHAR(20) NOT NULL DEFAULT 'completed',
                        created_at DATETIME NOT NULL,
                        ended_at DATETIME NULL,
                        overall_score INT NULL,
                        category_scores LONGTEXT NULL,
                        counts_json LONGTEXT NULL,
                        context_json LONGTEXT NULL,
                        duration_ms INT NULL,
                        notes TEXT NULL,
                        PRIMARY KEY  (id),
                        KEY run_type (run_type),
                        KEY created_at (created_at)
                    ) ENGINE=InnoDB",
                    $table
                ));
                break;

            case 'aetos_seo_findings':
                $wpdb->query($wpdb->prepare(
                    "CREATE TABLE IF NOT EXISTS %i (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                        run_id BIGINT UNSIGNED NOT NULL,
                        finding_id VARCHAR(191) NOT NULL,
                        check_id VARCHAR(191) NULL,
                        title TEXT NOT NULL,
                        category VARCHAR(50) NOT NULL,
                        severity VARCHAR(10) NOT NULL,
                        score_impact INT NOT NULL DEFAULT 0,
                        status VARCHAR(10) NOT NULL DEFAULT 'open',
                        location TEXT NULL,
                        evidence LONGTEXT NULL,
                        why_it_matters LONGTEXT NULL,
                        recommendation_steps LONGTEXT NULL,
                        retest LONGTEXT NULL,
                        created_at DATETIME NOT NULL,
                        updated_at DATETIME NOT NULL,
                        PRIMARY KEY  (id),
                        KEY run_id (run_id),
                        KEY finding_id (finding_id),
                        KEY category (category),
                        KEY severity (severity),
                        KEY status (status)
                    ) ENGINE=InnoDB",
                    $table
                ));
                break;

            case 'aetos_seo_crawl':
                $wpdb->query($wpdb->prepare(
                    "CREATE TABLE IF NOT EXISTS %i (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                        run_id BIGINT UNSIGNED NOT NULL,
                        url TEXT NOT NULL,
                        normalized_url TEXT NULL,
                        normalized_url_hash CHAR(32) NULL,
                        status_code INT NOT NULL DEFAULT 0,
                        redirect_chain LONGTEXT NULL,
                        canonical TEXT NULL,
                        robots_noindex TINYINT(1) NOT NULL DEFAULT 0,
                        title TEXT NULL,
                        meta_desc TEXT NULL,
                        h1_count INT NOT NULL DEFAULT 0,
                        h2_count INT NOT NULL DEFAULT 0,
                        h3_count INT NOT NULL DEFAULT 0,
                        word_count INT NOT NULL DEFAULT 0,
                        images_missing_alt INT NOT NULL DEFAULT 0,
                        images_total INT NOT NULL DEFAULT 0,
                        jsonld_types LONGTEXT NULL,
                        og_title TEXT NULL,
                        og_description TEXT NULL,
                        og_image TEXT NULL,
                        og_type VARCHAR(50) NULL,
                        twitter_card VARCHAR(50) NULL,
                        lang VARCHAR(20) NULL,
                        hreflang_count INT NOT NULL DEFAULT 0,
                        outbound_links_count INT NOT NULL DEFAULT 0,
                        analysis_json LONGTEXT NULL,
                        author_name TEXT NULL,
                        article_modified_time VARCHAR(50) NULL,
                        discovered_from TEXT NULL,
                        created_at DATETIME NOT NULL,
                        PRIMARY KEY (id),
                        KEY run_id (run_id),
                        KEY status_code (status_code),
                        KEY normalized_url_hash (normalized_url_hash)
                    ) ENGINE=InnoDB",
                    $table
                ));
                break;

            case 'aetos_seo_crawl_state':
                $wpdb->query($wpdb->prepare(
                    "CREATE TABLE IF NOT EXISTS %i (
                        run_id BIGINT UNSIGNED NOT NULL,
                        state_blob LONGTEXT NULL,
                        updated_at DATETIME NOT NULL,
                        lock_token CHAR(36) NULL,
                        lock_until DATETIME NULL,
                        PRIMARY KEY (run_id),
                        KEY updated_at (updated_at),
                        KEY lock_until (lock_until)
                    ) ENGINE=InnoDB",
                    $table
                ));
                break;

        }
    }

    private static function table_suffix(string $table): string
    {
        global $wpdb;

        $prefix = isset($wpdb->prefix) ? (string) $wpdb->prefix : '';
        if ($prefix !== '' && strpos($table, $prefix) === 0) {
            return substr($table, strlen($prefix));
        }

        return $table;
    }

    public static function activate(): void
    {
        if (!current_user_can('activate_plugins')) {
            return;
        }

        try {
            // Environment checks
            if (version_compare(PHP_VERSION, '7.4.0', '<')) {
    if (defined('WP_DEBUG') && WP_DEBUG) { error_log('Aetos SEO: PHP version 7.4+ required, current: ' . PHP_VERSION); } // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
                wp_die(
                    esc_html__('Aetos SEO requires PHP 7.4 or higher. Please upgrade PHP.', 'aetos-seo'),
                    esc_html__('Plugin Activation Error', 'aetos-seo'),
                    ['back_link' => true]
                );
                return;
            }


            // Run database schema creation
            self::run_db_delta();

            // Default settings with error handling
            if (get_option(Settings::OPTION_KEY) === false) {
                $defaults = Settings::defaults();
                if (!is_array($defaults)) {
                    throw new \Exception('Failed to load default settings');
                }
                add_option(Settings::OPTION_KEY, $defaults, '', false);
            }


            // Ensure upload directories with error handling
            Settings::ensure_upload_dirs();

            // Store DB schema version for upgrade checks
            if (defined('AETOS_SEO_DB_VERSION')) {
                update_option('aetos_seo_db_version', AETOS_SEO_DB_VERSION, false);
            }


            // Log successful activation
    if (defined('WP_DEBUG') && WP_DEBUG) { error_log('Aetos SEO: Plugin activated successfully (v' . (defined('AETOS_SEO_VERSION') ? AETOS_SEO_VERSION : 'unknown') . ')'); } // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
        } catch (\Exception $e) {
            // Log detailed error without exposing to user
    if (defined('WP_DEBUG') && WP_DEBUG) { error_log('Aetos SEO Activation Error: ' . $e->getMessage() . ' in ' . $e->getFile() . ':' . $e->getLine()); } // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
    if (defined('WP_DEBUG') && WP_DEBUG) { error_log('Stack trace: ' . $e->getTraceAsString()); } // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
            // User-friendly error message
            wp_die(
                esc_html__('There was an error activating Aetos SEO. Please check your error logs or contact support.', 'aetos-seo'),
                esc_html__('Plugin Activation Error', 'aetos-seo'),
                ['back_link' => true]
            );
        }
    }


    /**
     * Run DB upgrades when the plugin code is updated.
     *
     * This prevents fatal errors when new columns/indexes are introduced
     * (e.g. normalized_url_hash on the crawl table).
     */
    public static function maybe_upgrade(): void
    {
        if (!defined('AETOS_SEO_DB_VERSION')) {
            return;
        }


        $current = (string) get_option('aetos_seo_db_version', '');
        $code_version = (string) AETOS_SEO_DB_VERSION;
        $version_ok = ($current === $code_version);
        $tables_ok = self::has_required_tables();
        if ($version_ok && $tables_ok) {
            return;
        }

        // v3.1.0 — Refuse to run dbDelta against an older schema definition
        // when the DB is on a newer version. The DB version is a sortable
        // date-prefixed string ('YYYY-MM-DD-N'), so string compare is reliable.
        // dbDelta is monotonic forward (it only ever adds columns/indexes), so
        // running it in the "down" direction is a no-op — but if we ALSO
        // regress the version marker, the next forward upgrade would skip
        // legitimately-needed migrations. Stay put and let the admin re-install.
        if ($current !== '' && strcmp($current, $code_version) > 0) {
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log(sprintf( // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
                    '[Aetos SEO] Refusing to downgrade DB schema: stored=%s code=%s. Re-install the newer build or contact support.',
                    $current,
                    $code_version
                ));
            }
            // Record the refusal so System Health can surface it.
            update_option('aetos_seo_db_downgrade_blocked', [
                'stored'  => $current,
                'code'    => $code_version,
                'time'    => time(),
            ], false);
            return;
        }

        // Clear any prior downgrade flag — we're going forward again.
        delete_option('aetos_seo_db_downgrade_blocked');

        // Run dbDelta with the latest schema.
        self::run_db_delta();

        // v1.0.78: fallback ALTER TABLE for columns that dbDelta sometimes
        // fails to add on shared hosting (Hostinger). dbDelta is known to
        // silently skip LONGTEXT columns in some MySQL configurations.
        self::ensure_columns();

        // Ensure upload dirs.
        Settings::ensure_upload_dirs();

        // v3.3.40 — one-time settings migration: customers on the old 1000-URL
        // crawl ceiling (the default in v3.3.x ≤ 39) get their value lowered
        // to 200 so the build-inventory phase terminates in a reasonable time
        // for typical sites. Customers who already manually picked a value
        // different from 1000 are left alone — we only touch the default.
        $stored_settings = get_option(Settings::OPTION_KEY, []);
        if (is_array($stored_settings)
            && isset($stored_settings['scan_limits']['crawl_max_urls'])
            && (int) $stored_settings['scan_limits']['crawl_max_urls'] === 1000
        ) {
            $stored_settings['scan_limits']['crawl_max_urls'] = 200;
            update_option(Settings::OPTION_KEY, $stored_settings, false);
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('[Aetos] v3.3.40 migration: lowered crawl_max_urls from 1000 (old default) to 200 (new default).'); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
            }
        }

        // Update version marker.
        update_option('aetos_seo_db_version', AETOS_SEO_DB_VERSION, false);
    }

    /**
     * v1.0.78: Direct ALTER TABLE fallback for columns that dbDelta misses
     * (some shared-host MySQL configs silently skip LONGTEXT / added columns).
     *
     * Each ALTER is a full STRING LITERAL: the column name and its type spec are
     * hardcoded, and only the table is bound, via the %i identifier placeholder.
     * No "AFTER" clause (some shared-host MySQL versions reject it when the
     * reference column is absent; column order is irrelevant to WordPress).
     * Because nothing is interpolated, the PreparedSQL injection sniffs verify
     * each statement directly and nothing is suppressed.
     */
    private static function ensure_columns(): void
    {
        global $wpdb;
        $crawl = $wpdb->prefix . 'aetos_seo_crawl';

        if (self::column_missing($crawl, 'outbound_links_count')) {
            $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN outbound_links_count INT NOT NULL DEFAULT 0", $crawl));
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('Aetos SEO: added missing column outbound_links_count to ' . $crawl); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
            }
        }

        if (self::column_missing($crawl, 'analysis_json')) {
            $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN analysis_json LONGTEXT NULL", $crawl));
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('Aetos SEO: added missing column analysis_json to ' . $crawl); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
            }
        }
    }

    /**
     * True when $column is absent from $table. INFORMATION_SCHEMA lookup with
     * the table + column bound as %s values via prepare().
     */
    private static function column_missing(string $table, string $column): bool
    {
        global $wpdb;
        $exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                $table,
                $column
            )
        );
        return ((int) $exists) === 0;
    }


}