<?php

/**
 * Internal Linking table registry and schema health checks.
 *
 * @package ProRank\SEO\Modules\Content\InternalLinking
 * @since   1.0.0
 */

declare(strict_types=1);

namespace ProRank\SEO\Modules\Content\InternalLinking;

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


/**
 * TableRegistry class
 */
class TableRegistry {
    /**
     * Current internal-linking schema version.
     */
    public const SCHEMA_VERSION = '2.1.0';

    /**
     * DB version option key.
     */
    private const VERSION_OPTION = 'prorank_internal_linking_db_version';

    /**
     * Last schema check health snapshot option.
     */
    private const HEALTH_OPTION = 'prorank_internal_linking_schema_health';

    /**
     * Get canonical table map.
     *
     * @param bool $include_broken_links Whether to include the broken links table.
     * @return array<string, string>
     */
    public static function get_tables( bool $include_broken_links = true ): array {
        global $wpdb;

        $tables = [
            'autolink_keywords' => $wpdb->prefix . 'prorank_autolink_keywords',
            'link_clicks'       => $wpdb->prefix . 'prorank_link_clicks',
            'link_embeddings'   => $wpdb->prefix . 'prorank_link_embeddings',
            'phrase_embeddings' => $wpdb->prefix . 'prorank_phrase_embeddings',
            'link_context'      => $wpdb->prefix . 'prorank_link_context',
        ];

        if ( $include_broken_links ) {
            $tables['broken_links'] = $wpdb->prefix . 'prorank_broken_links';
        }

        return $tables;
    }

    /**
     * Ensure all required tables/columns exist.
     *
     * @param bool $include_broken_links Whether to include the broken links table.
     * @param bool $force                Force dbDelta even when schema appears up to date.
     * @return array<string, mixed>
     */
    public static function ensure_tables( bool $include_broken_links = true, bool $force = false ): array {
        $before = self::get_readiness_report( $include_broken_links, false );

        if ( ! $force && ! $before['version_mismatch'] && $before['ready'] ) {
            return $before;
        }

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

        foreach ( self::get_schema_sql( $include_broken_links ) as $sql ) {
            dbDelta( $sql );
        }

        // Explicitly patch known legacy installs where dbDelta can miss additions.
        self::ensure_legacy_columns();

        $after = self::get_readiness_report( $include_broken_links, true );
        $after['checked_at'] = current_time( 'mysql' );

        $created_tables = [];
        foreach ( $after['tables'] as $key => $table_status ) {
            $existed_before = (bool) ( $before['tables'][ $key ]['exists'] ?? false );
            if ( ! $existed_before && ! empty( $table_status['exists'] ) ) {
                $created_tables[] = $key;
            }
        }
        $after['created_tables'] = $created_tables;

        update_option(
            self::HEALTH_OPTION,
            [
                'checked_at'     => $after['checked_at'],
                'ready'          => $after['ready'],
                'missing_tables' => $after['missing_tables'],
                'created_tables' => $created_tables,
                'version'        => self::SCHEMA_VERSION,
            ],
            false
        );

        if ( $after['ready'] ) {
            update_option( self::VERSION_OPTION, self::SCHEMA_VERSION, false );
            $after['current_version']  = self::SCHEMA_VERSION;
            $after['version_mismatch'] = false;

            if ( function_exists( 'prorank_log' ) ) {
                prorank_log(
                    sprintf(
                        '[InternalLinking] Schema ready at version %1$s (%2$s).',
                        self::SCHEMA_VERSION,
                        gmdate( 'c' )
                    )
                );
            }
        } elseif ( function_exists( 'prorank_log' ) ) {
            prorank_log(
                sprintf(
                    '[InternalLinking] Schema not ready. Missing tables: %1$s',
                    implode( ',', $after['missing_tables'] )
                ),
                'error'
            );
        }

        return $after;
    }

    /**
     * Get table readiness report.
     *
     * @param bool $include_broken_links Whether to include broken links table checks.
     * @param bool $include_columns       Whether to include required/missing columns in the report.
     * @return array<string, mixed>
     */
    public static function get_readiness_report( bool $include_broken_links = true, bool $include_columns = false ): array {
        $tables           = self::get_tables( $include_broken_links );
        $required_columns = self::get_required_columns( $include_broken_links );
        $table_status     = [];
        $missing_tables   = [];

        foreach ( $tables as $key => $table_name ) {
            $exists = self::table_exists( $table_name );
            $status = [
                'name'   => $table_name,
                'exists' => $exists,
            ];

            if ( $include_columns ) {
                $columns                      = $exists ? self::get_columns( $table_name ) : [];
                $expected                     = $required_columns[ $key ] ?? [];
                $missing                      = array_values( array_diff( $expected, $columns ) );
                $status['required_columns']   = $expected;
                $status['missing_columns']    = $missing;
                $status['columns_valid']      = empty( $missing );
                $status['detected_columns']   = $columns;
            }

            if ( ! $exists ) {
                $missing_tables[] = $key;
            } elseif ( $include_columns && ! empty( $status['missing_columns'] ) ) {
                $missing_tables[] = $key;
            }

            $table_status[ $key ] = $status;
        }

        $current_version = (string) get_option( self::VERSION_OPTION, '0' );
        $version_mismatch = version_compare( $current_version, self::SCHEMA_VERSION, '<' );

        return [
            'ready'            => empty( $missing_tables ),
            'expected_version' => self::SCHEMA_VERSION,
            'current_version'  => $current_version,
            'version_mismatch' => $version_mismatch,
            'tables'           => $table_status,
            'missing_tables'   => $missing_tables,
            'health_snapshot'  => self::get_health_snapshot(),
        ];
    }

    /**
     * Get last schema health snapshot.
     *
     * @return array<string, mixed>
     */
    public static function get_health_snapshot(): array {
        $snapshot = get_option( self::HEALTH_OPTION, [] );
        return is_array( $snapshot ) ? $snapshot : [];
    }

    /**
     * Build canonical CREATE TABLE SQL statements.
     *
     * @param bool $include_broken_links Whether to include broken links table.
     * @return array<string, string>
     */
    private static function get_schema_sql( bool $include_broken_links = true ): array {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();
        $tables          = self::get_tables( $include_broken_links );

        $sql = [
            'autolink_keywords' => "CREATE TABLE IF NOT EXISTS {$tables['autolink_keywords']} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                keyword VARCHAR(255) NOT NULL,
                url VARCHAR(2048) NOT NULL,
                pattern_type ENUM('exact', 'regex', 'synonym', 'phrase') DEFAULT 'exact',
                variations TEXT DEFAULT NULL,
                placement_preference ENUM('any', 'beginning', 'middle', 'end') DEFAULT 'any',
                match_whole_word BOOLEAN DEFAULT TRUE,
                context_words INT DEFAULT 5,
                max_links INT DEFAULT 1,
                case_sensitive BOOLEAN DEFAULT FALSE,
                priority INT DEFAULT 50,
                exclude_posts TEXT DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY idx_keyword (keyword),
                KEY idx_priority (priority),
                KEY idx_pattern (pattern_type)
            ) {$charset_collate};",
            'link_clicks' => "CREATE TABLE IF NOT EXISTS {$tables['link_clicks']} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                link_id BIGINT(20) UNSIGNED DEFAULT 0,
                source_post_id BIGINT(20) UNSIGNED NOT NULL,
                target_post_id BIGINT(20) UNSIGNED DEFAULT NULL,
                target_url VARCHAR(2048) NOT NULL DEFAULT '',
                anchor_text VARCHAR(255) DEFAULT NULL,
                click_position INT DEFAULT 0,
                is_autolinked BOOLEAN DEFAULT FALSE,
                user_id BIGINT(20) UNSIGNED DEFAULT NULL,
                user_ip VARCHAR(45) DEFAULT NULL,
                ip_hash VARCHAR(64) DEFAULT NULL,
                user_agent TEXT DEFAULT NULL,
                referer VARCHAR(2048) DEFAULT NULL,
                referrer VARCHAR(2048) DEFAULT NULL,
                clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY idx_link_post (link_id, source_post_id),
                KEY idx_source_post (source_post_id),
                KEY idx_target_post (target_post_id),
                KEY idx_clicked (clicked_at),
                KEY idx_user (user_id)
            ) {$charset_collate};",
            'link_embeddings' => "CREATE TABLE IF NOT EXISTS {$tables['link_embeddings']} (
                post_id BIGINT(20) UNSIGNED NOT NULL,
                embedding_vector LONGTEXT NOT NULL,
                model_version VARCHAR(50) DEFAULT 'text-embedding-3-large',
                dimensions INT DEFAULT 3072,
                calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (post_id),
                KEY idx_calculated (calculated_at),
                KEY idx_model (model_version)
            ) {$charset_collate};",
            'phrase_embeddings' => "CREATE TABLE IF NOT EXISTS {$tables['phrase_embeddings']} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                post_id BIGINT(20) UNSIGNED NOT NULL,
                phrase_hash VARCHAR(64) NOT NULL,
                phrase_text TEXT NOT NULL,
                embedding_vector TEXT NOT NULL,
                sentence_position INT DEFAULT 0,
                word_count INT DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY idx_phrase_hash (post_id, phrase_hash),
                KEY idx_post (post_id),
                KEY idx_position (sentence_position)
            ) {$charset_collate};",
            'link_context' => "CREATE TABLE IF NOT EXISTS {$tables['link_context']} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                source_post_id BIGINT(20) UNSIGNED NOT NULL,
                target_post_id BIGINT(20) UNSIGNED NOT NULL,
                anchor_text VARCHAR(255) NOT NULL,
                context_before TEXT DEFAULT NULL,
                context_after TEXT DEFAULT NULL,
                sentence TEXT DEFAULT NULL,
                relevance_score FLOAT DEFAULT 0,
                ai_score FLOAT DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY idx_source_target (source_post_id, target_post_id),
                KEY idx_relevance (relevance_score),
                KEY idx_ai_score (ai_score)
            ) {$charset_collate};",
        ];

        if ( $include_broken_links && isset( $tables['broken_links'] ) ) {
            $sql['broken_links'] = "CREATE TABLE IF NOT EXISTS {$tables['broken_links']} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                post_id BIGINT(20) UNSIGNED NOT NULL,
                url VARCHAR(500) NOT NULL,
                anchor_text TEXT DEFAULT NULL,
                context TEXT DEFAULT NULL,
                link_type ENUM('internal','external') NOT NULL DEFAULT 'external',
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                status_code INT(3) DEFAULT NULL,
                status_message TEXT DEFAULT NULL,
                redirect_url VARCHAR(500) DEFAULT NULL,
                confidence VARCHAR(20) DEFAULT 'low',
                last_checked DATETIME DEFAULT NULL,
                check_count INT(11) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY post_url (post_id, url),
                KEY status (status),
                KEY link_type (link_type),
                KEY status_code (status_code),
                KEY confidence (confidence),
                KEY last_checked (last_checked)
            ) {$charset_collate};";
        }

        return $sql;
    }

    /**
     * Required columns per table for readiness verification.
     *
     * @param bool $include_broken_links Whether to include broken links.
     * @return array<string, array<int, string>>
     */
    private static function get_required_columns( bool $include_broken_links = true ): array {
        $required = [
            'autolink_keywords' => [ 'id', 'keyword', 'url', 'pattern_type', 'variations', 'placement_preference', 'match_whole_word', 'context_words', 'max_links', 'case_sensitive', 'priority', 'exclude_posts' ],
            'link_clicks'       => [ 'id', 'source_post_id', 'target_url', 'anchor_text', 'clicked_at' ],
            'link_embeddings'   => [ 'post_id', 'embedding_vector', 'model_version', 'dimensions' ],
            'phrase_embeddings' => [ 'id', 'post_id', 'phrase_hash', 'phrase_text', 'embedding_vector' ],
            'link_context'      => [ 'id', 'source_post_id', 'target_post_id', 'anchor_text', 'relevance_score' ],
        ];

        if ( $include_broken_links ) {
            $required['broken_links'] = [ 'id', 'post_id', 'url', 'anchor_text', 'context', 'link_type', 'status', 'status_code', 'status_message', 'redirect_url', 'confidence', 'last_checked', 'check_count' ];
        }

        return $required;
    }

    /**
     * Best-effort legacy patching for older installs.
     *
     * @return void
     */
    private static function ensure_legacy_columns(): void {
        $tables = self::get_tables( true );

        self::maybe_add_column(
            $tables['autolink_keywords'],
            'pattern_type',
            "ALTER TABLE {$tables['autolink_keywords']} ADD COLUMN pattern_type ENUM('exact', 'regex', 'synonym', 'phrase') DEFAULT 'exact' AFTER url"
        );
        self::maybe_add_column(
            $tables['autolink_keywords'],
            'variations',
            "ALTER TABLE {$tables['autolink_keywords']} ADD COLUMN variations TEXT DEFAULT NULL AFTER pattern_type"
        );
        self::maybe_add_column(
            $tables['autolink_keywords'],
            'placement_preference',
            "ALTER TABLE {$tables['autolink_keywords']} ADD COLUMN placement_preference ENUM('any', 'beginning', 'middle', 'end') DEFAULT 'any' AFTER variations"
        );
        self::maybe_add_column(
            $tables['autolink_keywords'],
            'match_whole_word',
            "ALTER TABLE {$tables['autolink_keywords']} ADD COLUMN match_whole_word BOOLEAN DEFAULT TRUE AFTER placement_preference"
        );
        self::maybe_add_column(
            $tables['autolink_keywords'],
            'context_words',
            "ALTER TABLE {$tables['autolink_keywords']} ADD COLUMN context_words INT DEFAULT 5 AFTER match_whole_word"
        );

        if ( isset( $tables['broken_links'] ) ) {
            self::maybe_add_column(
                $tables['broken_links'],
                'context',
                "ALTER TABLE {$tables['broken_links']} ADD COLUMN context TEXT DEFAULT NULL AFTER anchor_text"
            );
            self::maybe_add_column(
                $tables['broken_links'],
                'confidence',
                "ALTER TABLE {$tables['broken_links']} ADD COLUMN confidence VARCHAR(20) DEFAULT 'low'"
            );
        }

        if ( isset( $tables['link_clicks'] ) ) {
            self::maybe_add_column(
                $tables['link_clicks'],
                'link_id',
                "ALTER TABLE {$tables['link_clicks']} ADD COLUMN link_id BIGINT(20) UNSIGNED DEFAULT 0 AFTER id"
            );
            self::maybe_add_column(
                $tables['link_clicks'],
                'source_post_id',
                "ALTER TABLE {$tables['link_clicks']} ADD COLUMN source_post_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER link_id"
            );
            self::maybe_add_column(
                $tables['link_clicks'],
                'target_post_id',
                "ALTER TABLE {$tables['link_clicks']} ADD COLUMN target_post_id BIGINT(20) UNSIGNED DEFAULT NULL AFTER source_post_id"
            );
            self::maybe_add_column(
                $tables['link_clicks'],
                'anchor_text',
                "ALTER TABLE {$tables['link_clicks']} ADD COLUMN anchor_text VARCHAR(255) DEFAULT NULL AFTER target_url"
            );
            self::maybe_add_column(
                $tables['link_clicks'],
                'clicked_at',
                "ALTER TABLE {$tables['link_clicks']} ADD COLUMN clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
            );

            self::backfill_legacy_column(
                $tables['link_clicks'],
                'source_post_id',
                'source_id',
                "source_post_id = 0 OR source_post_id IS NULL"
            );
            self::backfill_legacy_column(
                $tables['link_clicks'],
                'target_post_id',
                'target_id',
                "target_post_id = 0 OR target_post_id IS NULL"
            );
            self::backfill_legacy_column(
                $tables['link_clicks'],
                'clicked_at',
                'click_date',
                "clicked_at IS NULL OR clicked_at = '0000-00-00 00:00:00'"
            );
            self::backfill_legacy_column(
                $tables['link_clicks'],
                'referrer',
                'referer',
                "referrer = '' OR referrer IS NULL"
            );
        }

        if ( isset( $tables['broken_links'] ) ) {
            self::maybe_add_column(
                $tables['broken_links'],
                'link_type',
                "ALTER TABLE {$tables['broken_links']} ADD COLUMN link_type ENUM('internal','external') NOT NULL DEFAULT 'external' AFTER context"
            );
            self::maybe_add_column(
                $tables['broken_links'],
                'status_message',
                "ALTER TABLE {$tables['broken_links']} ADD COLUMN status_message TEXT DEFAULT NULL AFTER status_code"
            );
            self::maybe_add_column(
                $tables['broken_links'],
                'redirect_url',
                "ALTER TABLE {$tables['broken_links']} ADD COLUMN redirect_url VARCHAR(500) DEFAULT NULL AFTER status_message"
            );
            self::maybe_add_column(
                $tables['broken_links'],
                'check_count',
                "ALTER TABLE {$tables['broken_links']} ADD COLUMN check_count INT(11) DEFAULT 0 AFTER last_checked"
            );

            self::backfill_broken_link_type( $tables['broken_links'] );
        }
    }

    /**
     * Add a column if it does not exist.
     *
     * @param string $table_name  Table name.
     * @param string $column_name Column name.
     * @param string $sql         ALTER TABLE SQL.
     * @return void
     */
    private static function maybe_add_column( string $table_name, string $column_name, string $sql ): void {
        global $wpdb;

        if ( ! self::table_exists( $table_name ) ) {
            return;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $exists = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table_name} LIKE %s", $column_name ) );
        if ( $exists !== $column_name ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query( $sql );
        }
    }

    /**
     * Copy values from a legacy column into canonical column when needed.
     *
     * @param string $table_name    Table name.
     * @param string $target_column Canonical target column.
     * @param string $source_column Legacy source column.
     * @param string $where_sql     Additional WHERE condition.
     * @return void
     */
    private static function backfill_legacy_column(
        string $table_name,
        string $target_column,
        string $source_column,
        string $where_sql
    ): void {
        global $wpdb;

        if ( ! self::column_exists( $table_name, $target_column ) || ! self::column_exists( $table_name, $source_column ) ) {
            return;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query(
            "UPDATE {$table_name}
             SET {$target_column} = {$source_column}
             WHERE {$source_column} IS NOT NULL
               AND ({$where_sql})"
        );
    }

    /**
     * Best-effort backfill for broken-links link_type on legacy rows.
     *
     * @param string $table_name Table name.
     * @return void
     */
    private static function backfill_broken_link_type( string $table_name ): void {
        global $wpdb;

        if ( ! self::column_exists( $table_name, 'link_type' ) ) {
            return;
        }

        $home = wp_parse_url( home_url(), PHP_URL_HOST );
        if ( ! is_string( $home ) || $home === '' ) {
            return;
        }

        $like_host = '%' . $wpdb->esc_like( $home ) . '%';

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$table_name}
                 SET link_type = 'internal'
                 WHERE (link_type = '' OR link_type = 'external' OR link_type IS NULL)
                   AND url LIKE %s",
                $like_host
            )
        );
    }

    /**
     * Check whether a column exists in a table.
     *
     * @param string $table_name  Table name.
     * @param string $column_name Column name.
     * @return bool
     */
    private static function column_exists( string $table_name, string $column_name ): bool {
        global $wpdb;

        if ( ! self::table_exists( $table_name ) ) {
            return false;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $exists = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table_name} LIKE %s", $column_name ) );
        return $exists === $column_name;
    }

    /**
     * Check whether a table exists.
     *
     * @param string $table_name Table name.
     * @return bool
     */
    private static function table_exists( string $table_name ): bool {
        global $wpdb;

        $found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
        return $found === $table_name;
    }

    /**
     * Get table column names.
     *
     * @param string $table_name Table name.
     * @return array<int, string>
     */
    private static function get_columns( string $table_name ): array {
        global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $columns = $wpdb->get_col( "SHOW COLUMNS FROM {$table_name}" );
        if ( ! is_array( $columns ) ) {
            return [];
        }

        return array_values( array_map( 'strval', $columns ) );
    }
}
