<?php
/**
 * Centralised database schema manager for SEO Forge.
 *
 * Owns the per-table dbDelta DDL and the version-pin pattern that was
 * previously baked only into SEOFORGE_Redirects::maybe_create_table.
 * Each table has its own option flag so a schema bump on one table never
 * forces a no-op rebuild of unrelated tables.
 *
 * Pattern (per table):
 * 1. SEOFORGE_DB_VERSION constant in seoforge.php is the GLOBAL ceiling
 * that triggers the upgrade pass on plugins_loaded.
 * 2. self::TABLE_VERSIONS[$table] is the per-table ceiling — bump THIS
 * when you change a CREATE TABLE block in this file.
 * 3. self::TABLE_FLAGS[$table] holds the wp_options key that pins the
 * table to a specific version. dbDelta is skipped if the flag matches.
 *
 * NOTE on existing installs: the redirects table previously stored its
 * version under 'seoforge_redirects_table_ver'. We keep that exact key so
 * sites that already shipped 1.0.0 don't run dbDelta a second time.
 */

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

class SEOFORGE_Schema_Manager {
    /**
 * Per-table version ceiling. Bump the value when the corresponding
 * CREATE TABLE block in this file changes shape (added column, new
 * index, modified type). dbDelta handles the actual ALTER TABLE work.
 *
 * Logical name => version string.
 */
    const TABLE_VERSIONS = [
        'analysis'  => '1',
        'redirects' => '2',
    ];

    /**
 * wp_options key that records the version currently installed on disk
 * for each table. These names are kept verbatim from the legacy code so
 * existing 1.0.0 installs don't get a redundant first upgrade pass.
 */
    const TABLE_FLAGS = [
        'analysis'  => 'seoforge_analysis_table_ver',
        'redirects' => 'seoforge_redirects_table_ver', // pre-existing key
    ];

    /**
 * Run on plugins_loaded (priority 5, before SEOFORGE_Redirects @20).
 * Idempotent — each table check is a single get_option comparison
 * after the first successful pass.
 */
    public static function maybe_upgrade() {
        // Cheap global guard: if every per-table flag matches the ceiling
        // and the global SEOFORGE_DB_VERSION hasn't moved, return without
        // touching the DB at all.
        $installed_db_ver = get_option( 'seoforge_db_version', '' );
        if (
            $installed_db_ver === SEOFORGE_DB_VERSION
            && self::all_tables_current()
        ) {
            return;
        }

        foreach ( self::TABLE_VERSIONS as $logical => $target_ver ) {
            $flag    = self::TABLE_FLAGS[ $logical ] ?? null;
            if ( ! $flag ) continue;

            $current = get_option( $flag, '' );
            if ( (string) $current === (string) $target_ver ) {
                continue; // table already at target version
            }

            $method = 'create_' . $logical;
            if ( method_exists( __CLASS__, $method ) ) {
                self::$method();
                update_option( $flag, $target_ver, false );
            }
        }

        update_option( 'seoforge_db_version', SEOFORGE_DB_VERSION, false );
    }

    /**
 * Quick check used by maybe_upgrade's fast-path.
 */
    private static function all_tables_current() {
        foreach ( self::TABLE_VERSIONS as $logical => $target_ver ) {
            $flag = self::TABLE_FLAGS[ $logical ] ?? null;
            if ( ! $flag ) return false;
            if ( (string) get_option( $flag, '' ) !== (string) $target_ver ) {
                return false;
            }
        }
        return true;
    }

    /**
 * wp_seoforge_analysis — per-post analysis cache (score, issues,
 * suggestions, generated meta).
 *
 * Mirrors the SEOFORGE_Core::activate DDL verbatim so the migration
 * path from the old activation-only flow is a no-op for existing rows.
 */
    public static function create_analysis() {
        global $wpdb;
        $table   = $wpdb->prefix . 'seoforge_analysis';
        $charset = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            post_id bigint(20) unsigned NOT NULL,
            score int(3) NOT NULL DEFAULT 0,
            issues text DEFAULT '',
            suggestions text DEFAULT '',
            meta_title varchar(255) DEFAULT '',
            meta_description text DEFAULT '',
            schema_json text DEFAULT '',
            internal_links text DEFAULT '',
            analyzed_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY post_id (post_id)
        ) $charset;";

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

    /**
 * wp_seoforge_redirects — 301/302/307 redirect rules with hit counts
 * and 404 auto-redirect provenance.
 *
 * Identical to SEOFORGE_Redirects::create_table — that method now
 * delegates here so there's a single source of truth.
 */
    public static function create_redirects() {
        global $wpdb;
        $table   = $wpdb->prefix . 'seoforge_redirects';
        $charset = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            source_url varchar(500) NOT NULL,
            target_url varchar(500) NOT NULL DEFAULT '',
            redirect_type int(3) NOT NULL DEFAULT 301,
            hits int(11) NOT NULL DEFAULT 0,
            last_hit datetime DEFAULT NULL,
            auto_created tinyint(1) NOT NULL DEFAULT 0,
            allow_external tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY source_url (source_url(191)),
            KEY redirect_type (redirect_type)
        ) $charset;";

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