<?php
namespace Clusterlane\Core;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery
// -- Clusterlane's own tables and set-based reads of posts/terms that no WordPress API covers.
//    Every query goes through $wpdb->prepare(): values and ID lists as placeholders, table and
//    column names as %i. Fragments added to a query are code (Scope::sql()) or are prepared
//    where they are built (Scope::type_sql(), Lang::sql(), SeoMeta joins).

/**
 * Installer
 *
 * Handles database table creation and upgrades.
 * Called on plugin activation and checked on each boot
 * (dbDelta is idempotent — safe to run on every request if needed,
 * though we only run it when the stored version differs).
 *
 * Table prefix strategy:
 *   {wp_prefix}clusterlane_{table}
 *   e.g. stock_clusterlane_links, stock_clusterlane_clusters …
 * This keeps Clusterlane fully isolated from any other plugin using the same DB.
 */
class Installer {

    /** Option key that stores the installed schema version. */
    const VERSION_KEY = 'clusterlane_db_version';

    /** Increment this whenever the schema changes. */
    const SCHEMA_VERSION = '8'; // 8: cluster_type fits any taxonomy name (0.98.177); 7: clusterlane_snapshots + clusterlane_activity (0.98.124)

    /* ------------------------------------------------------------------
       Public API
    ------------------------------------------------------------------ */

    public static function activate(): void {
        require_once __DIR__ . '/class-capabilities.php';
        Capabilities::install();
        self::create_tables();
        update_option( self::VERSION_KEY, self::SCHEMA_VERSION, false );
    }

    public static function deactivate(): void {
        // Data is kept on deactivation (removed only by uninstall.php);
        // only the daily snapshot event is unscheduled.
        wp_clear_scheduled_hook( 'clusterlane_daily_snapshot' );
        wp_clear_scheduled_hook( 'clusterlane_read_pages' ); // published pages still queued (0.98.178)
    }

    /**
     * Called on every boot; runs migrations only when the stored version
     * is behind the current schema version.
     */
    public static function maybe_upgrade(): void {
        self::data_migrations();
        if ( get_option( self::VERSION_KEY ) === self::SCHEMA_VERSION ) return;
        self::create_tables();
        update_option( self::VERSION_KEY, self::SCHEMA_VERSION, false );
    }

    /** One-time data fixes that don't change the schema (per site, option-gated). */
    private static function data_migrations(): void {
        $done = (int) get_option( 'clusterlane_data_version', 0 );
        if ( $done >= 3 ) return;
        global $wpdb;
        if ( $done < 1 ) {
            $table = $wpdb->prefix . 'clusterlane_clusters';
            if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table ) {
                // 1: computed clusters with no pattern: "Unclassified" → "Mixed".
                $wpdb->query( $wpdb->prepare( "UPDATE %i SET archetype = 'Mixed' WHERE archetype = 'Unclassified'", $table ) );
            }
        }
        // 2 (0.98.128): undo the HTML entities the old sanitizer wrote into saved
        // prompts, stop words and SEO instructions; a prompt that is the default
        // again stops being "Custom".
        $s = $done < 2 ? get_option( 'clusterlane_settings', [] ) : null;
        if ( is_array( $s ) ) {
            require_once __DIR__ . '/class-settings.php';
            $defaults = Settings::default_prompts_public();
            foreach ( (array) ( $s['prompts'] ?? [] ) as $k => $v ) {
                $v = (string) $v;
                for ( $i = 0; $i < 5 && str_contains( $v, '\\"' ); $i++ ) $v = wp_unslash( $v );
                $v = trim( Settings::heal_entities( $v ) );
                if ( $v === '' || $v === trim( (string) ( $defaults[ $k ] ?? '' ) ) ) unset( $s['prompts'][ $k ] );
                else $s['prompts'][ $k ] = $v;
            }
            foreach ( [ 'tms_stop_words', 'seo_ai_instructions' ] as $k ) {
                if ( isset( $s[ $k ] ) && is_string( $s[ $k ] ) ) $s[ $k ] = Settings::heal_entities( $s[ $k ] );
            }
            update_option( 'clusterlane_settings', $s );
        }
        // 3 (0.98.145): site-wide roles from the clusters' roles — no more
        // "pillar" for every article with 5+ inbound links.
        $pm = $wpdb->prefix . 'clusterlane_post_metrics';
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $pm ) ) === $pm ) {
            require_once __DIR__ . '/class-cluster-engine.php';
            ClusterEngine::sync_site_roles();
        }
        update_option( 'clusterlane_data_version', 3, false );
    }

    /**
     * Returns true if all core Clusterlane tables are present in the database.
     * Used at boot time to catch a failed or incomplete installation
     * (e.g. a MySQL timeout during activation) and show a clear admin
     * notice rather than letting the plugin crash with a DB error.
     */
    public static function tables_exist(): bool {
        global $wpdb;
        $required = [
            $wpdb->prefix . 'clusterlane_clusters',
            $wpdb->prefix . 'clusterlane_post_metrics',
            $wpdb->prefix . 'clusterlane_links',
        ];
        foreach ( $required as $table ) {
            if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
                return false;
            }
        }
        return true;
    }

    /* ------------------------------------------------------------------
       Schema
    ------------------------------------------------------------------ */

    private static function create_tables(): void {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();

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

        // ----------------------------------------------------------------
        // clusterlane_links — internal link graph
        // Each row = one <a href> from a source to a target.
        //
        // target_type distinguishes a link to a post from a link to a
        // taxonomy term's archive page (e.g. the category hub for a
        // cluster). This is NOT cosmetic: WordPress term IDs and post
        // IDs are independent sequences and can collide numerically, so
        // every query against target_id must also filter on target_type
        // or it can silently match the wrong kind of row.
        // source_type is included for symmetry / future use (e.g. if a
        // page or a term archive is ever scanned as a source) but is
        // always 'post' today, since only posts are scanned.
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_links (
                id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                source_id     BIGINT UNSIGNED NOT NULL,
                source_type   VARCHAR(10)     NOT NULL DEFAULT 'post',
                target_id     BIGINT UNSIGNED NOT NULL,
                target_type   VARCHAR(10)     NOT NULL DEFAULT 'post',
                anchor_text   VARCHAR(255)    NOT NULL DEFAULT '',
                link_type     VARCHAR(20)     NOT NULL DEFAULT 'inline',
                scanned_at    DATETIME        NOT NULL,
                PRIMARY KEY  (id)
            ) $charset;
        " );

        // ----------------------------------------------------------------
        // clusterlane_clusters — one row per cluster
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_clusters (
                id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                cluster_key     VARCHAR(100)    NOT NULL,
                cluster_label   VARCHAR(255)    NOT NULL DEFAULT '',
                cluster_type    VARCHAR(32)     NOT NULL DEFAULT 'category',
                term_id         BIGINT UNSIGNED          DEFAULT NULL,
                is_active       TINYINT(1)      NOT NULL DEFAULT 1,
                archetype       VARCHAR(50)              DEFAULT NULL,
                health_score    TINYINT UNSIGNED NOT NULL DEFAULT 0,
                entropy_score   TINYINT UNSIGNED NOT NULL DEFAULT 0,
                gini_score      TINYINT UNSIGNED NOT NULL DEFAULT 0,
                density_score   TINYINT UNSIGNED NOT NULL DEFAULT 0,
                total_members   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                last_computed   DATETIME                 DEFAULT NULL,
                PRIMARY KEY  (id)
            ) $charset;
        " );

        // ----------------------------------------------------------------
        // clusterlane_post_metrics — per-article intelligence scores
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_post_metrics (
                post_id           BIGINT UNSIGNED NOT NULL,
                primary_cluster   VARCHAR(100)             DEFAULT NULL,
                seo_score         TINYINT UNSIGNED NOT NULL DEFAULT 0,
                structure_score   TINYINT UNSIGNED NOT NULL DEFAULT 0,
                overall_score     TINYINT UNSIGNED NOT NULL DEFAULT 0,
                inbound_links     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                outbound_links    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                word_count        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                image_count       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                h2_count          SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                content_class     VARCHAR(20)              DEFAULT NULL,
                last_scanned      DATETIME                 DEFAULT NULL,
                PRIMARY KEY  (post_id)
            ) $charset;
        " );

        // ----------------------------------------------------------------
        // clusterlane_ai_cache — cached AI responses (gap analysis, link suggestions)
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_ai_cache (
                id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                cache_type      VARCHAR(30)     NOT NULL,
                context_key     VARCHAR(255)    NOT NULL,
                response_data   LONGTEXT        NOT NULL,
                model_used      VARCHAR(100)             DEFAULT NULL,
                prompt_hash     VARCHAR(32)              DEFAULT NULL,
                created_at      DATETIME        NOT NULL,
                expires_at      DATETIME                 DEFAULT NULL,
                PRIMARY KEY  (id)
            ) $charset;
        " );

        // ----------------------------------------------------------------
        // clusterlane_cluster_post_class — per-cluster, cluster-relative role
        // of each member post (pillar / support / light / orphan).
        //
        // A post's structural role only makes sense relative to the
        // distribution of its OWN cluster (5 inbound links is a pillar
        // in a sparse cluster, average in a dense one), and a post can
        // belong to more than one cluster (multiple categories, or
        // overlapping custom clusters) with a different role in each.
        // So this is keyed on (cluster_key, post_id) rather than living
        // as a single global column on clusterlane_post_metrics.
        //
        // Rows are fully recomputed and replaced every time
        // Cluster_Engine::compute_cluster() runs for a given cluster_key.
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_cluster_post_class (
                id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                cluster_key     VARCHAR(100)    NOT NULL,
                post_id         BIGINT UNSIGNED NOT NULL,
                content_class   VARCHAR(20)     NOT NULL DEFAULT 'light',
                inbound_links   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                percentile      TINYINT UNSIGNED NOT NULL DEFAULT 0,
                computed_at     DATETIME                 DEFAULT NULL,
                PRIMARY KEY  (id)
            ) $charset;
        " );

        // ----------------------------------------------------------------
        // clusterlane_custom_members — posts in manually created clusters
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_custom_members (
                id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                cluster_key VARCHAR(100)    NOT NULL,
                post_id     BIGINT UNSIGNED NOT NULL,
                added_at    DATETIME        NOT NULL,
                PRIMARY KEY (id)
            ) $charset;
        " );

        // clusterlane_tms_skipped — persistently skipped Text Match pairs
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_tms_skipped (
                id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                source_id   BIGINT UNSIGNED NOT NULL,
                target_id   BIGINT UNSIGNED NOT NULL,
                skipped_at  DATETIME        NOT NULL,
                PRIMARY KEY (id),
                UNIQUE KEY uniq_pair (source_id, target_id)
            ) $charset;
        " );

        // ----------------------------------------------------------------
        // clusterlane_snapshots — the site's figures once a day (schema 7).
        // One row per local date: a later capture the same day replaces
        // it. Headline figures are columns (fast series for charts); the
        // rest — roles, meta, archetypes and every cluster — is JSON in
        // `data`. metrics_version marks formula changes, so a chart can
        // tell "the calculation changed" from "the site changed".
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_snapshots (
                id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                snap_date        DATE            NOT NULL,
                taken_at         DATETIME        NOT NULL,
                source           VARCHAR(10)     NOT NULL DEFAULT 'cron',
                metrics_version  SMALLINT UNSIGNED NOT NULL DEFAULT 1,
                articles         INT UNSIGNED    NOT NULL DEFAULT 0,
                scanned          INT UNSIGNED    NOT NULL DEFAULT 0,
                clusters         INT UNSIGNED    NOT NULL DEFAULT 0,
                health           TINYINT UNSIGNED NOT NULL DEFAULT 0,
                orphans          INT UNSIGNED    NOT NULL DEFAULT 0,
                avg_inbound      DECIMAL(7,1)    NOT NULL DEFAULT 0,
                links            INT UNSIGNED    NOT NULL DEFAULT 0,
                data             LONGTEXT        NOT NULL,
                PRIMARY KEY  (id)
            ) $charset;
        " );

        // ----------------------------------------------------------------
        // clusterlane_activity — what was done through Clusterlane (schema 7).
        // One row per write: a link added, a pillar chosen, a meta
        // description written, a draft created. Undo sets undone_at
        // instead of deleting, so totals can be counted net.
        // ----------------------------------------------------------------
        dbDelta( "
            CREATE TABLE {$wpdb->prefix}clusterlane_activity (
                id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                action       VARCHAR(20)     NOT NULL,
                tool         VARCHAR(20)     NOT NULL DEFAULT '',
                post_id      BIGINT UNSIGNED NOT NULL DEFAULT 0,
                target_id    BIGINT UNSIGNED NOT NULL DEFAULT 0,
                cluster_key  VARCHAR(100)    NOT NULL DEFAULT '',
                detail       VARCHAR(255)    NOT NULL DEFAULT '',
                user_id      BIGINT UNSIGNED NOT NULL DEFAULT 0,
                created_at   DATETIME        NOT NULL,
                undone_at    DATETIME                 DEFAULT NULL,
                PRIMARY KEY  (id)
            ) $charset;
        " );

        // dbDelta above creates the tables and (on upgrade) adds any new
        // columns — both of which it does reliably. It does NOT manage the
        // secondary indexes: those are reconciled idempotently below, for
        // the reasons documented on reconcile_indexes().
        self::reconcile_indexes();
    }

    /* ------------------------------------------------------------------
       Index management

       dbDelta is notoriously unreliable at reconciling secondary indexes.
       On an upgrade where an index's columns changed it compares the old
       and new definitions loosely, decides they differ, and tries to ADD
       the new one while the identically-named old one is still present —
       producing "Duplicate key name '<name>'" and aborting the migration.
       (That is exactly what broke the v2 -> v3 upgrade.)

       So we keep indexes out of the dbDelta CREATE statements entirely and
       manage them here: a declarative map of every secondary index, each
       added only if it is genuinely absent. This is safe to run on every
       activation and every boot-time upgrade — present indexes are left
       untouched, missing ones are created.

       CAVEAT — changing an existing index's columns: because a name match
       counts as "already present", re-declaring an index with new columns
       here will NOT alter it. To change one, drop it first with
       drop_index_if_exists() in a versioned step (see pre_reconcile_migrations)
       and let reconcile re-add the new definition.
    ------------------------------------------------------------------ */

    /**
     * Every secondary index, keyed by bare table name (no prefix). Each
     * value maps index_name => [ unique?, [ columns ] ].
     */
    private static function index_map(): array {
        return [
            'clusterlane_links' => [
                'source_target' => [ true, [ 'source_id', 'target_type', 'target_id' ] ],
                'source_id' => [ false, [ 'source_id' ] ],
                'target_id' => [ false, [ 'target_id' ] ],
                'target_type' => [ false, [ 'target_type' ] ],
            ],
            'clusterlane_clusters' => [
                'cluster_key' => [ true, [ 'cluster_key' ] ],
                'cluster_type' => [ false, [ 'cluster_type' ] ],
                'health_score' => [ false, [ 'health_score' ] ],
                'is_active' => [ false, [ 'is_active' ] ],
            ],
            'clusterlane_post_metrics' => [
                'primary_cluster' => [ false, [ 'primary_cluster' ] ],
                'content_class' => [ false, [ 'content_class' ] ],
                'overall_score' => [ false, [ 'overall_score' ] ],
            ],
            'clusterlane_ai_cache' => [
                'type_context' => [ true, [ 'cache_type', 'context_key' ] ],
                'expires_at' => [ false, [ 'expires_at' ] ],
            ],
            'clusterlane_cluster_post_class' => [
                'cluster_post' => [ true, [ 'cluster_key', 'post_id' ] ],
                'cluster_key' => [ false, [ 'cluster_key' ] ],
                'post_id' => [ false, [ 'post_id' ] ],
                'content_class' => [ false, [ 'content_class' ] ],
            ],
            'clusterlane_custom_members' => [
                'cluster_post' => [ true, [ 'cluster_key', 'post_id' ] ],
                'cluster_key' => [ false, [ 'cluster_key' ] ],
                'post_id' => [ false, [ 'post_id' ] ],
            ],
            'clusterlane_snapshots' => [
                'snap_date' => [ true, [ 'snap_date' ] ],
            ],
            'clusterlane_activity' => [
                'created_at' => [ false, [ 'created_at' ] ],
                'action_post' => [ false, [ 'action', 'post_id' ] ],
                'user_id' => [ false, [ 'user_id' ] ],
            ],
        ];
    }

    /**
     * Add every mapped index that is currently missing. Idempotent.
     */
    private static function reconcile_indexes(): void {
        global $wpdb;

        // Apply any destructive schema changes (index drops/renames) first,
        // so reconcile can then re-add the new definitions cleanly.
        self::pre_reconcile_migrations();

        foreach ( self::index_map() as $table => $indexes ) {
            $full = $wpdb->prefix . $table;
            foreach ( $indexes as $name => [ $unique, $columns ] ) {
                self::add_index_if_missing( $full, $name, $unique, $columns );
            }
        }
    }

    /**
     * Versioned, one-off destructive migrations that must run before the
     * idempotent reconcile (e.g. dropping an index whose columns changed
     * so the new definition can be added). Keep each guarded so it is a
     * no-op once applied. Left intentionally empty until the next such
     * change; the historical source_target column change is already past.
     *
     * Example, for future reference:
     *   $links = $wpdb->prefix . 'clusterlane_links';
     *   self::drop_index_if_exists( $links, 'source_target' ); // reconcile re-adds the new form
     */
    private static function pre_reconcile_migrations(): void {
        // No pending destructive migrations.
    }

    /** True if $index_name exists on $table (both are full, prefixed names). */
    private static function index_exists( string $table, string $index_name ): bool {
        global $wpdb;
        $found = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(1) FROM information_schema.STATISTICS
                  WHERE table_schema = DATABASE()
                    AND table_name   = %s
                    AND index_name   = %s",
                $table,
                $index_name
            )
        );
        return (int) $found > 0;
    }

    /**
     * ALTER TABLE ... ADD [UNIQUE] KEY — but only if the index is absent.
     * Table, index and column names go in as %i identifiers.
     *
     * @param string[] $columns
     */
    private static function add_index_if_missing( string $table, string $index_name, bool $unique, array $columns ): void {
        global $wpdb;
        if ( ! $columns || self::index_exists( $table, $index_name ) ) return;
        $args = array_merge( [ $table, $index_name ], array_values( $columns ) );
        if ( $unique ) {
            $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD UNIQUE KEY %i (' . implode( ', ', array_fill( 0, count( $columns ), '%i' ) ) . ')', $args ) ); // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- built from code-only fragments; every value is a placeholder
        } else {
            $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD KEY %i (' . implode( ', ', array_fill( 0, count( $columns ), '%i' ) ) . ')', $args ) ); // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- built from code-only fragments; every value is a placeholder
        }
    }

    /** DROP INDEX ... — but only if present. For versioned migrations. */
    private static function drop_index_if_exists( string $table, string $index_name ): void {
        global $wpdb;
        if ( ! self::index_exists( $table, $index_name ) ) return;
        $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP INDEX %i', $table, $index_name ) );
    }
}