<?php
/**
 * Database installer for logs.
 *
 * @package NimblyMail\SMTP
 */

namespace NimblyMail\SMTP\Log;

defined( 'ABSPATH' ) || exit;

/**
 * Class Installer
 *
 * Handles database table creation and upgrades.
 *
 * @since 1.0.0
 * @since 1.0.3 Added FULLTEXT_INDEX_OPTION so Model can detect the search index once instead of querying INFORMATION_SCHEMA on every request; ensure_fulltext_index() now records that option from the actual index state so a concurrent duplicate-key ALTER failure does not overwrite a successful one.
 * @since 1.0.6 install_for_blog() now skips a blog id that no longer resolves to an existing site.
 */
class Installer {

    public const DB_VERSION  = '1.0.1';
    public const VERSION_KEY = 'nimblymail_db_version';

    /**
     * Option name recording whether the idx_search FULLTEXT index is usable.
     *
     * Stores '1' or '0' once ensure_fulltext_index() has checked, so Model
     * can read this option instead of running SHOW INDEX / INFORMATION_SCHEMA
     * lookups on every search request.
     *
     * @since 1.0.3
     *
     * @var string
     */
    public const FULLTEXT_INDEX_OPTION = 'nimblymail_fulltext_index_available';

    /**
     * Run on plugin activation for the current blog.
     *
     * @since 1.0.0
     *
     * @return void
     */
    public static function install(): void {
        self::create_table();
        update_option( self::VERSION_KEY, self::DB_VERSION );
    }

    /**
     * Install the logs table for a specific blog on a multisite network.
     *
     * Wraps install() in switch_to_blog/restore_current_blog so that
     * $wpdb->prefix resolves to the target blog's prefix. No-op outside of
     * multisite.
     *
     * @since 1.0.0
     * @since 1.0.6 Added a get_site() existence check: NetworkActivationInstaller
     *              can now run a network activation's per-site installs across
     *              a much longer wall-clock window than the old synchronous
     *              loop, during which a site could be deleted mid-run.
     *
     * @param int $blog_id Target blog id.
     *
     * @return void
     */
    public static function install_for_blog( int $blog_id ): void {
        if ( $blog_id <= 0 || ! function_exists( 'switch_to_blog' ) ) {
            return;
        }

        if ( function_exists( 'get_site' ) && ! get_site( $blog_id ) ) {
            return;
        }

        switch_to_blog( $blog_id );
        self::install();
        restore_current_blog();
    }

    /**
     * Check if DB upgrade is needed.
     *
     * @since 1.0.3 Also calls ensure_fulltext_index() unconditionally (it
     *              short-circuits once FULLTEXT_INDEX_OPTION is recorded) so
     *              sites that already have the index from an earlier version
     *              get the option set without waiting for a DB_VERSION bump.
     */
    public static function maybe_upgrade(): void {
        $installed = get_option( self::VERSION_KEY, '0' );
        if ( version_compare( $installed, self::DB_VERSION, '<' ) ) {
            self::create_table();
            update_option( self::VERSION_KEY, self::DB_VERSION );
        }

        self::ensure_fulltext_index();
    }

    /**
     * Create or alter the logs table.
     *
     * Uses concatenation for table name in SQL strings. The dbDelta function
     * requires the CREATE TABLE statement with the table name inline.
     *
     * @since 1.0.0 Refactored SQL to use concatenation for table name.
     * @since 1.0.3 Clears FULLTEXT_INDEX_OPTION before calling ensure_fulltext_index() so activation and DB-version-bump events (the only callers of this method) retry a transient ALTER TABLE failure instead of leaving search pinned to the LIKE fallback forever.
     */
    private static function create_table(): void {
        global $wpdb;

        $charset = $wpdb->get_charset_collate();

        // dbDelta requires the full CREATE TABLE statement as a single string.
        // Table name is interpolated from $wpdb->prefix which is internal,
        // not user-supplied.
        $sql = "CREATE TABLE {$wpdb->prefix}nimblymail_logs (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            queue_id BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            to_address TEXT NOT NULL,
            cc TEXT NULL,
            bcc TEXT NULL,
            subject VARCHAR(255) NOT NULL DEFAULT '',
            body LONGTEXT NULL,
            headers TEXT NULL,
            attachments TEXT NULL,
            content_type VARCHAR(50) NOT NULL DEFAULT 'text/html',
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            sandbox_mode VARCHAR(20) NULL,
            error_message TEXT NULL,
            source VARCHAR(255) NOT NULL DEFAULT '',
            mailer_used VARCHAR(50) NOT NULL DEFAULT '',
            send_duration_ms INT UNSIGNED NULL,
            retry_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
            debug_log LONGTEXT NULL,
            PRIMARY KEY (id),
            KEY idx_created_at (created_at),
            KEY idx_status (status),
            KEY idx_source (source),
            KEY idx_mailer_used (mailer_used),
            KEY idx_queue_id (queue_id)
        ) {$charset};";

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

        self::ensure_sandbox_mode_column();

        // Activation and DB-version bumps are infrequent, so this is the
        // right place to retry a previous ALTER TABLE failure: clear the
        // recorded flag so ensure_fulltext_index() re-checks and re-attempts
        // instead of trusting a stale '0'. maybe_upgrade() never reaches
        // create_table() once the site is on the latest DB_VERSION, so this
        // does not turn into a per-request retry.
        delete_option( self::FULLTEXT_INDEX_OPTION );
        self::ensure_fulltext_index();
    }

    /**
     * Add the FULLTEXT index for log search if it is not already present.
     *
     * The dbDelta() function does not manage FULLTEXT indexes reliably, so
     * we add it manually. MySQL 5.7 (and older MariaDB) do not support the
     * `ADD INDEX IF NOT EXISTS` syntax, so existence is checked against
     * INFORMATION_SCHEMA first to keep the operation idempotent across
     * versions. Errors are swallowed so an unsupported environment
     * doesn't break activation — full-text search on logs gracefully
     * degrades to LIKE queries when the index is missing.
     *
     * Records the result in FULLTEXT_INDEX_OPTION so Model::find() knows
     * whether it can use MATCH...AGAINST without re-checking
     * INFORMATION_SCHEMA on every search request. Once the option is set,
     * this method returns immediately on subsequent calls (it is invoked on
     * every request via maybe_upgrade()) — a failed ALTER is not retried on
     * every request. It is retried, however, whenever create_table() runs
     * (plugin activation, DB-version bumps), because that method clears the
     * option first; those events are infrequent enough that re-attempting
     * there does not reintroduce a per-request cost.
     *
     * The recorded flag is taken from the actual index state, not from
     * $wpdb->last_error: two concurrent requests can both pass the
     * short-circuit and both attempt the ALTER, in which case the loser
     * fails with a duplicate-key error even though the index now exists.
     * Re-running the existence check after the ALTER attempt means that
     * race is harmless — whichever request finishes last still records '1'.
     *
     * @since 1.0.0
     * @since 1.0.3 Records FULLTEXT_INDEX_OPTION and short-circuits once it is set, instead of only running during create_table(); create_table() now clears the option first so activation/upgrade events still retry a failed ALTER; records the result from the post-ALTER index state instead of $wpdb->last_error so a concurrent duplicate-key loser still records '1' when the index exists.
     *
     * @return void
     */
    private static function ensure_fulltext_index(): void {
        global $wpdb;

        if ( false !== get_option( self::FULLTEXT_INDEX_OPTION, false ) ) {
            return;
        }

        if ( self::fulltext_index_exists() ) {
            update_option( self::FULLTEXT_INDEX_OPTION, '1' );
            return;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is internal; no user input.
        $wpdb->query( "ALTER TABLE {$wpdb->prefix}nimblymail_logs ADD FULLTEXT idx_search (to_address, subject, body)" );

        update_option( self::FULLTEXT_INDEX_OPTION, self::fulltext_index_exists() ? '1' : '0' );
    }

    /**
     * Check whether the idx_search FULLTEXT index exists on the logs table.
     *
     * Queries INFORMATION_SCHEMA rather than trusting $wpdb->last_error so
     * callers can find out the actual index state regardless of whether an
     * ALTER TABLE attempt in the same request succeeded, failed, or was
     * never run.
     *
     * @since 1.0.3
     *
     * @phpstan-impure Queries live database state, so two calls in the same
     *                 request can return different results.
     *
     * @return bool True if idx_search exists on the logs table.
     */
    private static function fulltext_index_exists(): bool {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $exists = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s LIMIT 1',
                $wpdb->prefix . 'nimblymail_logs',
                'idx_search'
            )
        );

        return (bool) $exists;
    }

    /**
     * Add the sandbox_mode column if it is not already present.
     *
     * Idempotent column-add fallback used when dbDelta misses the schema
     * delta on existing installations. Checks INFORMATION_SCHEMA.COLUMNS
     * before issuing ALTER TABLE so it is safe to call on every upgrade.
     * Errors are swallowed so an edge-case environment doesn't break
     * activation.
     *
     * @since 1.0.0
     *
     * @return void
     */
    private static function ensure_sandbox_mode_column(): void {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $exists = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s LIMIT 1',
                $wpdb->prefix . 'nimblymail_logs',
                'sandbox_mode'
            )
        );

        if ( $exists ) {
            return;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is internal; no user input.
        $wpdb->query( "ALTER TABLE {$wpdb->prefix}nimblymail_logs ADD COLUMN sandbox_mode VARCHAR(20) NULL AFTER status" );
    }

    /**
     * Remove table on uninstall if requested.
     *
     * @since 1.0.0 Refactored SQL to use concatenation for table name.
     */
    public static function uninstall(): void {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is internal; no user input.
        $wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}nimblymail_logs" );
        delete_option( self::VERSION_KEY );
    }
}
