<?php
/**
 * Migration: Multilingual backfill — ensures all records have a language value
 * and configures the primary language setting.
 *
 * This migration runs once when upgrading to the multilingual architecture.
 * It is idempotent: running it multiple times is safe and produces the same result.
 *
 * What it does:
 * 1. For every table with a `language` column, sets `language = 'he'` where empty/null
 * 2. Sets `boostbot_primary_language` to 'he' if not already configured
 * 3. Ensures Hebrew is in the enabled languages list
 * 4. Seeds Hebrew defaults if missing (idempotent)
 * 5. Logs everything for post-migration verification
 *
 * @package BoostBot
 * @since   1.3.0
 */

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class BoostBot_Migration_Multilingual_Backfill {

    private static array $log = array();

    /**
     * Run the migration.
     */
    public static function run(): void {
        $flag = get_option( 'boostbot_multilingual_migrated', '' );
        if ( '' !== $flag ) {
            self::log( 'Migration already completed on ' . $flag . ' — skipping.' );
            self::flush_log();
            return;
        }

        self::log( '=== Multilingual Backfill Migration START ===' );

        self::backfill_language_columns();
        self::configure_primary_language();
        self::ensure_enabled_languages();
        self::seed_defaults();

        update_option( 'boostbot_multilingual_migrated', current_time( 'mysql' ) );
        self::log( 'Migration flag set.' );
        self::log( '=== Multilingual Backfill Migration COMPLETE ===' );
        self::flush_log();
    }

    /**
     * Step 1: Backfill empty/null language values to 'he' in all plugin tables.
     */
    private static function backfill_language_columns(): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'boostbot_';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $tables = $wpdb->get_col(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME LIKE %s",
                DB_NAME,
                $prefix . '%'
            )
        );

        if ( empty( $tables ) ) {
            self::log( 'No boostbot tables found — nothing to backfill.' );
            return;
        }

        foreach ( $tables as $table ) {
            $short = str_replace( $prefix, '', $table );

            // Check if table has a language column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $col = $wpdb->get_results(
                $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", 'language' ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            );

            if ( empty( $col ) ) {
                self::log( "  {$short}: no language column — skipping." );
                continue;
            }

            // Count rows with empty/null language
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $empty_count = (int) $wpdb->get_var(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT COUNT(*) FROM `{$table}` WHERE `language` = '' OR `language` IS NULL"
            );

            if ( 0 === $empty_count ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
                $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
                self::log( "  {$short}: {$total} rows, all have language set — no backfill needed." );
                continue;
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $updated = $wpdb->query(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "UPDATE `{$table}` SET `language` = 'he' WHERE `language` = '' OR `language` IS NULL"
            );

            if ( false === $updated ) {
                self::log( "  {$short}: ERROR updating — {$wpdb->last_error}" );
            } else {
                self::log( "  {$short}: backfilled {$updated} rows to language='he'." );
            }
        }
    }

    /**
     * Step 2: Set the primary language to 'he' if not already configured.
     */
    private static function configure_primary_language(): void {
        $current = get_option( 'boostbot_primary_language', '' );
        if ( '' !== $current ) {
            self::log( "Primary language already set to '{$current}' — keeping." );
            return;
        }

        update_option( 'boostbot_primary_language', 'he' );
        if ( class_exists( 'BoostBot_Language' ) ) {
            BoostBot_Language::clear_cache();
        }
        self::log( "Primary language set to 'he'." );
    }

    /**
     * Step 3: Ensure Hebrew is in the enabled languages list.
     */
    private static function ensure_enabled_languages(): void {
        $enabled = get_option( 'boostbot_enabled_languages', array() );
        if ( is_string( $enabled ) ) {
            $enabled = json_decode( $enabled, true ) ?: array();
        }
        if ( ! is_array( $enabled ) ) {
            $enabled = array();
        }

        if ( in_array( 'he', $enabled, true ) ) {
            self::log( "Hebrew already in enabled languages: [" . implode( ', ', $enabled ) . "]." );
            return;
        }

        array_unshift( $enabled, 'he' );
        update_option( 'boostbot_enabled_languages', $enabled );
        self::log( "Added 'he' to enabled languages: [" . implode( ', ', $enabled ) . "]." );
    }

    /**
     * Step 4: Seed Hebrew defaults (idempotent — won't duplicate).
     */
    private static function seed_defaults(): void {
        if ( ! class_exists( 'BoostBot_DB_Manager' ) ) {
            self::log( 'BoostBot_DB_Manager not available — skipping seed.' );
            return;
        }

        try {
            $dbm = new BoostBot_DB_Manager();
            $dbm->seed_language_defaults( 'he' );
            self::log( "Hebrew defaults seeded (idempotent)." );
        } catch ( \Throwable $e ) {
            self::log( "Seed error: " . $e->getMessage() );
        }
    }

    /**
     * Log a message (stored in memory, flushed to WP option at end).
     */
    private static function log( string $msg ): void {
        $line = '[' . gmdate( 'Y-m-d H:i:s' ) . '] ' . $msg;
        self::$log[] = $line;

        if ( class_exists( 'BoostBot_Logger' ) ) {
            BoostBot_Logger::info( 'Multilingual migration: ' . $msg );
        }
    }

    /**
     * Flush the migration log to a WP option for later retrieval.
     */
    private static function flush_log(): void {
        $existing = get_option( 'boostbot_multilingual_migration_log', '' );
        $new_log = implode( "\n", self::$log );
        update_option( 'boostbot_multilingual_migration_log', $existing . ( $existing ? "\n" : '' ) . $new_log );
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}
