<?php

namespace GriffinForms;

class Db
{   
    private const MIGRATIONS_VERSION = '2.3.0.4';
    protected $config;
    public $name_columns;
    public $formelement_columns;
    public $meta_columns;
    public $prefix;
    public $charset;
    
    public function setGlobals()
    {
        global $wpdb;
        $this->prefix = $wpdb->prefix;
        $this->charset = $wpdb->get_charset_collate();
        $this->config = Config::getInstance();
        $this->setNameColumns();
        $this->setFormElementColumns();
        $this->setMetaColumns();
    }
    
    protected function setNameColumns()
    {
        $this->name_columns = "name tinytext NOT NULL,
        heading varchar(1000),
        description varchar(2000)";
    }
    
    protected function setFormElementColumns()
    {
        $this->formelement_columns = "folder smallint(9) DEFAULT '0' NOT NULL";
    }
    
    public function setMetaColumns()
    {
        $this->meta_columns = "author varchar(50),
        created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        last_editor varchar(50),
        last_edited datetime DEFAULT '0000-00-00 00:00:00' NOT NULL";
    }
    
    private function addFormTable()
    {
        $table_name = $this->config->getTable('form');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        $this->name_columns,
        $this->formelement_columns,
        autofill tinyint(1),
        limit_entries text,
        availability_window longtext,
        lifetime_submission_count int(11) DEFAULT 0,
        access_level text,
        submission_edit longtext,
        user_action text,
        autoresponder text,
        alert_emails text,
        notifications longtext DEFAULT NULL,
        conditional_logic longtext,
        integration_settings longtext,
        compliance_mode tinyint(1) NOT NULL DEFAULT 0,
        compliance_profile varchar(50) NOT NULL DEFAULT 'standard',
        compliance_flags longtext,
        layout_version int(11) NOT NULL DEFAULT 0,
        layout_hash char(64) DEFAULT '',
        save_entries varchar(50),
        formpages text,
        submit_label varchar(255) DEFAULT 'Submit',
        button_layout varchar(50) DEFAULT 'ends',
        origin VARCHAR(255) DEFAULT 'local',
        status varchar(20) NOT NULL DEFAULT 'published',
        form_theme mediumint(9) DEFAULT 0,
        progress_bar text,
        frontend_url longtext,
        $this->meta_columns,
        PRIMARY KEY  (id)
        )  $this->charset;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }
    
    private function addMessageTable()
    {
        $table_name = $this->config->getTable('message');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        $this->name_columns,
        $this->meta_columns,
        content text,
        PRIMARY KEY  (id)
        )  $this->charset;";
        
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }
    
    private function addFolderTable()
    {
        $table_name = $table_name = $this->config->getTable('folder');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        $this->name_columns,
        icon tinyint(3) default '1' NOT NULL,
        $this->meta_columns,
        PRIMARY KEY  (id)
        )  $this->charset;";
        
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }
    
    private function addFormPageTable()
    {
        $table_name = $this->config->getTable('formpage');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        form_id mediumint(9) DEFAULT 0,
        $this->name_columns,
        pagerows text,
        step_data longtext,
        previous_label varchar(255) DEFAULT 'Previous',
        next_label varchar(255) DEFAULT 'Next',
        page_theme mediumint(9) DEFAULT 0,
        $this->meta_columns,
        PRIMARY KEY  (id),
        KEY form_id (form_id)
        )  $this->charset;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }    
    
    private function addPageRowTable()
    {
        $table_name = $this->config->getTable('pagerow');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        form_id mediumint(9) DEFAULT 0,
        $this->name_columns,
        rowcolumns text,
        row_theme mediumint(9) DEFAULT 0,
        $this->meta_columns,
        PRIMARY KEY  (id),
        KEY form_id (form_id)
        )  $this->charset;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }
    
    
    private function addRowColumnTable()
    {
        $table_name = $this->config->getTable('rowcolumn');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        form_id mediumint(9) DEFAULT 0,
        $this->name_columns,
        fields text,
        width tinyint(2),
        $this->meta_columns,
        PRIMARY KEY  (id),
        KEY form_id (form_id)
        )  $this->charset;";
        
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }
    
    private function addFieldTable()
    {
        $table_name = $this->config->getTable('field');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        field_type varchar(50),
        $this->name_columns,
        is_required tinyint(1),
        is_hidden tinyint(1),
        field_options text,
        placeholder varchar(1000),
        default_value text,
        is_readonly tinyint(1),
        is_disabled tinyint(1),
        autocomplete tinyint(1),
        iterations text,
        validations text,
        field_settings text,
        is_reusable tinyint(1) DEFAULT 0,
        field_theme mediumint(9) DEFAULT 0,
        $this->meta_columns,
        PRIMARY KEY  (id)
        )  $this->charset;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }
    
    private function addSubmissionsTable()
    {
        $table_name = $this->config->getTable('submission');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        form_id mediumint(9),
        status tinyint(1) NOT NULL DEFAULT 0,
        is_read tinyint(1) NOT NULL DEFAULT 0,
        is_payment_form tinyint(1) NOT NULL DEFAULT 0,
        payment_status varchar(20) NOT NULL DEFAULT 'pending',
        payment_meta longtext NULL,
        public_token varchar(40),
        submission text,
        form_layout_version int(11) NOT NULL DEFAULT 0,
        form_layout_hash char(64) DEFAULT '',
        layout_snapshot_id BIGINT(20) UNSIGNED NULL,
        submission_version int(11) NOT NULL DEFAULT 0,
        submission_edit_count int(11) NOT NULL DEFAULT 0,
        current_revision_id mediumint(9) DEFAULT NULL,
        submission_hash char(64) DEFAULT '',
        submission_ip varchar(45) DEFAULT '',
        submission_user_agent text,
        submission_meta longtext,
        source_url varchar(2083) DEFAULT '',
        $this->meta_columns,
        PRIMARY KEY  (id)
        )  $this->charset;";
        
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }

    private function addSubmissionRevisionTable(): void
    {
        $table_name = $this->config->getTable('submission_revision');
        $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        event_uid char(36) NOT NULL,
        submission_id mediumint(9) NOT NULL,
        form_id mediumint(9) NOT NULL,
        revision_number int(11) NOT NULL DEFAULT 0,
        event_type varchar(20) NOT NULL DEFAULT 'edit',
        snapshot_json longtext NOT NULL,
        snapshot_hash char(64) NOT NULL DEFAULT '',
        delta_json longtext,
        actor_user_id mediumint(9) NOT NULL DEFAULT 0,
        actor_label tinytext,
        restored_from_revision_id mediumint(9) NOT NULL DEFAULT 0,
        created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY event_uid (event_uid),
        KEY submission_id (submission_id),
        KEY form_id (form_id),
        KEY revision_number (revision_number),
        KEY restored_from_revision_id (restored_from_revision_id)
        )  $this->charset;";

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

    private function addFilesTable()
    {
        $table_name = $this->config->getTable('file');
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            form_id BIGINT(20) UNSIGNED,
            submission_id BIGINT(20) UNSIGNED NULL,
            field_id BIGINT(20) UNSIGNED,
            draft_id VARCHAR(64) NULL,
            token VARCHAR(64) NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'temp',
            storage_mode VARCHAR(20) NOT NULL DEFAULT 'private',
            path LONGTEXT,
            url LONGTEXT,
            mime VARCHAR(190),
            size_bytes BIGINT(20) UNSIGNED,
            image_w INT(11) NULL,
            image_h INT(11) NULL,
            megapixels DECIMAL(8,2) NULL,
            checksum CHAR(64) NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            expires_at DATETIME NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY token (token),
            KEY submission_id (submission_id),
            KEY status_expires (status, expires_at),
            KEY form_field (form_id, field_id)
        )  $this->charset;";

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

    private function addJobsTable()
    {
        $table_name = $this->config->getTable('job');
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            job_type VARCHAR(100) NOT NULL,
            integration_id VARCHAR(100) NOT NULL,
            submission_id BIGINT(20) UNSIGNED NOT NULL,
            form_id BIGINT(20) UNSIGNED NOT NULL,
            payload LONGTEXT NOT NULL,
            priority TINYINT(3) UNSIGNED NOT NULL DEFAULT 5,
            status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
            attempts TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
            available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            locked_at DATETIME NULL,
            last_error TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY priority_available (priority, available_at),
            KEY status_available (status, available_at),
            KEY submission_lookup (submission_id),
            KEY integration_lookup (integration_id)
        )  $this->charset;";

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

    private function addLayoutSnapshotTable()
    {
        $table_name = $this->config->getTable('layout_snapshot');
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            form_id BIGINT(20) UNSIGNED NOT NULL,
            submission_id BIGINT(20) UNSIGNED NULL,
            layout_version INT(11) NOT NULL DEFAULT 0,
            layout_hash CHAR(64) DEFAULT '',
            layout_json LONGTEXT NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_by BIGINT(20) UNSIGNED NULL,
            PRIMARY KEY (id),
            KEY form_id (form_id),
            KEY submission_id (submission_id)
        )  $this->charset;";

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

    protected function addOptionsTable()
    {
        $table_name = $this->config->getTable('options');
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            option_name VARCHAR(200) NOT NULL,
            option_value LONGTEXT NOT NULL,
            last_option_value LONGTEXT,
            option_group VARCHAR(100) DEFAULT 'general',
            editor BIGINT(20) UNSIGNED NOT NULL,
            edited_on DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY option_name (option_name),
            KEY option_group (option_group)
        ) $this->charset;";

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

    protected function addTemplatesTable()
    {
        $table_name = $this->config->getTable('template');
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            category VARCHAR(255),
            tags TEXT,
            author BIGINT(20) UNSIGNED,
            created_at DATETIME NOT NULL,
            form_data LONGTEXT NOT NULL,
            origin VARCHAR(50) DEFAULT 'template',
            version INT(11) UNSIGNED DEFAULT 1,
            PRIMARY KEY (id),
            KEY category (category),
            KEY author (author)
        ) $this->charset;";

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

    private function addLogTable()
    {
        $table_name = $this->config->getTable('log');
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            log_type VARCHAR(50) NOT NULL,
            category VARCHAR(255) NOT NULL,
            item_type VARCHAR(50) NOT NULL,
            item_id BIGINT(20) UNSIGNED NOT NULL,
            user_id BIGINT(20) UNSIGNED NOT NULL,
            message TEXT NOT NULL,
            date_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY (id),
            KEY log_type (log_type),
            KEY item_type (item_type),
            KEY item_id (item_id),
            KEY user_id (user_id)
        ) $this->charset;";
        
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta($sql);
    }

    private function addRateLimitTable()
    {
        $table_name = $this->config->getTable('ratelimiter');
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            client_hash CHAR(32) NOT NULL,
            type ENUM('burst','window') NOT NULL,
            count INT(10) UNSIGNED NOT NULL DEFAULT 0,
            first_ts INT(10) UNSIGNED NOT NULL,
            updated_ts INT(10) UNSIGNED NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY client_type (client_hash, type),
            KEY updated_ts (updated_ts)
        )  $this->charset;";

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

    private function addFormThemeTable()
    {
        $table_name = $this->config->getTable('formtheme');
        $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            form_style LONGTEXT,
            page_style LONGTEXT,
            row_style LONGTEXT,
            field_style LONGTEXT,
            submit_button LONGTEXT,
            previous_button LONGTEXT,
            next_button LONGTEXT,
            button_typography LONGTEXT,
            form_title LONGTEXT,
            form_description LONGTEXT,
            page_heading LONGTEXT,
            page_description LONGTEXT,
            row_heading LONGTEXT,
            row_description LONGTEXT,
            field_label LONGTEXT,
            field_description LONGTEXT,
            field_errors LONGTEXT,
            success_message LONGTEXT,
            category varchar(255) DEFAULT 'default',
            origin varchar(50) DEFAULT 'user',
            version float DEFAULT 1.0,
            $this->meta_columns,
            PRIMARY KEY (id)
        ) $this->charset;";

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



    public function addTables()
    {
        $this->addFormTable();
        $this->addMessageTable();
        $this->addFolderTable();
        $this->addFormPageTable();
        $this->addPageRowTable();
        $this->addRowColumnTable();
        $this->addFieldTable();
        $this->addSubmissionsTable();
        $this->addFilesTable();
        $this->addJobsTable();
        $this->addLayoutSnapshotTable();
        $this->addOptionsTable();
        $this->addLogTable();
        $this->addRateLimitTable();
        $this->addTemplatesTable();
        $this->addFormThemeTable();
    }

    public function setupTables()
    {
        $this->setGlobals();

        $current_version = (string) get_option('griffinforms_db_migrations_version', '0');
        $backfill_done = get_option('griffinforms_lifetime_submission_count_backfilled');
        $form_element_backfill_done = get_option('griffinforms_form_element_form_id_backfilled');

        if (
            version_compare($current_version, self::MIGRATIONS_VERSION, '>=') &&
            !empty($backfill_done) &&
            !empty($form_element_backfill_done)
        ) {
            return;
        }

        $this->addTables();

        if ($current_version === '' || $current_version === '0') {
            $this->markFreshInstallComplete();
            return;
        }

        $this->migrateFormTable();
        $this->migrateFormPageTable();
        $this->migrateFormThemeTable();
        $this->migrateSubmissionTable();
        $this->migrateLayoutElementTables();
        $this->markFreshInstallComplete();
    }

    protected function markFreshInstallComplete(): void
    {
        update_option('griffinforms_db_migrations_version', self::MIGRATIONS_VERSION);
        update_option('griffinforms_lifetime_submission_count_backfilled', current_time('mysql', true));
        update_option('griffinforms_form_element_form_id_backfilled', current_time('mysql', true));
        update_option('griffinforms_submission_history_backfilled', current_time('mysql', true));
    }

    /**
     * Run lightweight migrations on every request (safe + idempotent).
     */
    public function maybeRunMigrations(): void
    {
        $this->setGlobals();
        $this->ensureFormWriteSchema();
        $this->ensureSubmissionWriteSchema();
        $this->ensureSubmissionHistoryWriteSchema();
        $current_version = get_option('griffinforms_db_migrations_version', '0');
        $backfill_done = get_option('griffinforms_lifetime_submission_count_backfilled');
        $form_element_backfill_done = get_option('griffinforms_form_element_form_id_backfilled');
        $submission_history_backfill_done = get_option('griffinforms_submission_history_backfilled');

        if (version_compare($current_version, self::MIGRATIONS_VERSION, '>=') && !empty($backfill_done) && !empty($form_element_backfill_done) && !empty($submission_history_backfill_done)) {
            return;
        }

        $lock_key = 'griffinforms_db_migrations_lock';
        if (get_transient($lock_key)) {
            return;
        }
        set_transient($lock_key, 1, MINUTE_IN_SECONDS * 5);

        try {
            if (version_compare($current_version, self::MIGRATIONS_VERSION, '<')) {
                $this->migrateFormTable();
                $this->migrateSubmissionTable();
                $this->addLayoutSnapshotTable();
                update_option('griffinforms_db_migrations_version', self::MIGRATIONS_VERSION);
            }
            $this->migrateLayoutElementTables();
            $this->maybeBackfillLifetimeSubmissionCount();
            $this->maybeBackfillFormElementFormIds();
            $this->maybeBackfillSubmissionHistory();
        } finally {
            delete_transient($lock_key);
        }
    }

    /**
     * Keep the submission write path self-healing when the stored migration
     * version looks current but the table is missing a column from a partial update.
     */
    private function ensureSubmissionWriteSchema(): void
    {
        $this->setGlobals();
        $table_name = $this->config->getTable('submission');

        $columns = [
            'is_payment_form' => "tinyint(1) NOT NULL DEFAULT 0 AFTER status",
            'payment_status' => "varchar(20) NOT NULL DEFAULT 'pending' AFTER status",
            'payment_meta' => "longtext NULL AFTER payment_status",
            'is_read' => "tinyint(1) NOT NULL DEFAULT 0 AFTER status",
            'form_layout_version' => "INT(11) NOT NULL DEFAULT 0 AFTER submission",
            'form_layout_hash' => "CHAR(64) DEFAULT '' AFTER form_layout_version",
            'layout_snapshot_id' => "BIGINT(20) UNSIGNED NULL AFTER form_layout_hash",
            'submission_version' => "INT(11) NOT NULL DEFAULT 0 AFTER layout_snapshot_id",
            'submission_edit_count' => "INT(11) NOT NULL DEFAULT 0 AFTER submission_version",
            'current_revision_id' => "MEDIUMINT(9) DEFAULT NULL AFTER submission_edit_count",
            'submission_hash' => "CHAR(64) DEFAULT '' AFTER submission_version",
            'submission_ip' => "varchar(45) DEFAULT '' AFTER submission_hash",
            'submission_user_agent' => "text AFTER submission_ip",
            'submission_meta' => "longtext AFTER submission_user_agent",
            'source_url' => "varchar(2083) DEFAULT '' AFTER submission_meta",
        ];

        foreach ($columns as $column => $definition) {
            $this->ensureColumn($table_name, $column, $definition);
        }
    }

    /**
     * Keep the form write path self-healing so policy fields exist before saves.
     */
    private function ensureFormWriteSchema(): void
    {
        $this->setGlobals();
        $table_name = $this->config->getTable('form');

        $columns = [
            'submission_edit' => "LONGTEXT NULL AFTER access_level",
            'notifications'   => "LONGTEXT DEFAULT NULL AFTER alert_emails",
        ];

        foreach ($columns as $column => $definition) {
            $this->ensureColumn($table_name, $column, $definition);
        }
    }

    /**
     * Keep submission history schema self-healing so rollback revisions exist before restore flows.
     */
    private function ensureSubmissionHistoryWriteSchema(): void
    {
        $this->setGlobals();
        $this->addSubmissionRevisionTable();
        $table_name = $this->config->getTable('submission');

        $columns = [
            'current_revision_id' => "MEDIUMINT(9) DEFAULT NULL AFTER submission_edit_count",
        ];

        foreach ($columns as $column => $definition) {
            $this->ensureColumn($table_name, $column, $definition);
        }
    }

    private function ensureColumn(string $table_name, string $column_name, string $definition): void
    {
        global $wpdb;

        if ($table_name === '' || $column_name === '') {
            return;
        }

        if ($this->columnExists($table_name, $column_name)) {
            return;
        }

        $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN {$column_name} {$definition}");
    }

    /**
     * Migrate Form table to add new columns safely
     *
     * Extend this when adding future form-level columns to keep existing installs in sync.
     */
    private function migrateFormTable(): void
    {
        global $wpdb;
        $this->setGlobals();
        $table_name = $this->config->getTable('form');

        // integration_settings column holds per-form integration settings JSON
        $integration_settings_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'integration_settings'",
            $table_name
        ));

        if (empty($integration_settings_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN integration_settings LONGTEXT NULL AFTER conditional_logic");
        }

        $availability_window_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'availability_window'",
            $table_name
        ));

        if (empty($availability_window_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN availability_window LONGTEXT NULL AFTER limit_entries");
        }

        $lifetime_count_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'lifetime_submission_count'",
            $table_name
        ));

        if (empty($lifetime_count_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN lifetime_submission_count INT(11) DEFAULT 0 AFTER availability_window");
        }

        $submission_edit_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'submission_edit'",
            $table_name
        ));

        if (empty($submission_edit_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN submission_edit LONGTEXT NULL AFTER access_level");
        }

        $compliance_mode_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'compliance_mode'",
            $table_name
        ));

        if (empty($compliance_mode_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN compliance_mode tinyint(1) NOT NULL DEFAULT 0 AFTER integration_settings");
        }

        $compliance_profile_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'compliance_profile'",
            $table_name
        ));

        if (empty($compliance_profile_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN compliance_profile varchar(50) NOT NULL DEFAULT 'standard' AFTER compliance_mode");
        }

        $compliance_flags_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'compliance_flags'",
            $table_name
        ));

        if (empty($compliance_flags_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN compliance_flags LONGTEXT NULL AFTER compliance_profile");
        }

        $layout_version_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'layout_version'",
            $table_name
        ));

        if (empty($layout_version_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN layout_version INT(11) NOT NULL DEFAULT 0 AFTER compliance_flags");
        }

        $layout_hash_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'layout_hash'",
            $table_name
        ));

        if (empty($layout_hash_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN layout_hash CHAR(64) DEFAULT '' AFTER layout_version");
        }

        $status_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'status'",
            $table_name
        ));

        if (empty($status_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN status varchar(20) NOT NULL DEFAULT 'published' AFTER origin");
        }

        $progress_bar_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'progress_bar'",
            $table_name
        ));

        if (empty($progress_bar_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN progress_bar TEXT NULL AFTER form_theme");
        }

        $frontend_url_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'frontend_url'",
            $table_name
        ));

        if (empty($frontend_url_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN frontend_url LONGTEXT NULL AFTER progress_bar");
        }
    }

    /**
     * Extend the formpage table with page step metadata for progress rendering.
     */
    private function migrateFormPageTable(): void
    {
        global $wpdb;
        $this->setGlobals();
        $table_name = $this->config->getTable('formpage');

        $step_data_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'step_data'",
            $table_name
        ));

        if (empty($step_data_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN step_data LONGTEXT NULL AFTER pagerows");
        }
    }

    /**
     * One-time backfill for lifetime submission counts.
     */
    private function maybeBackfillLifetimeSubmissionCount(): void
    {
        $flag = get_option('griffinforms_lifetime_submission_count_backfilled');
        if (!empty($flag)) {
            return;
        }

        global $wpdb;
        $form_table = $this->config->getTable('form');
        $submission_table = $this->config->getTable('submission');

        $column_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'lifetime_submission_count'",
            $form_table
        ));
        if (empty($column_exists)) {
            return;
        }

        $counts = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT form_id, COUNT(*) AS total FROM %i WHERE status = %d GROUP BY form_id",
                $submission_table,
                1
            ),
            ARRAY_A
        );
        $counts_map = [];
        if (is_array($counts)) {
            foreach ($counts as $row) {
                $counts_map[(int) $row['form_id']] = (int) $row['total'];
            }
        }

        $forms = $wpdb->get_results(
            $wpdb->prepare("SELECT id, lifetime_submission_count FROM %i", $form_table),
            ARRAY_A
        );

        if (is_array($forms)) {
            foreach ($forms as $form) {
                $form_id = (int) ($form['id'] ?? 0);
                if ($form_id <= 0) {
                    continue;
                }
                $current = isset($form['lifetime_submission_count']) ? (int) $form['lifetime_submission_count'] : 0;
                $computed = $counts_map[$form_id] ?? 0;
                $next = max($current, $computed);
                if ($next !== $current) {
                    $wpdb->update(
                        $form_table,
                        ['lifetime_submission_count' => $next],
                        ['id' => $form_id],
                        ['%d'],
                        ['%d']
                    );
                }
            }
        }

        update_option('griffinforms_lifetime_submission_count_backfilled', current_time('mysql', true));
    }

    /**
     * Migrate FormTheme table to add missing columns
     * This ensures backward compatibility when new columns are added
     */
    private function migrateFormThemeTable()
    {
        global $wpdb;
        $this->setGlobals();
        $table_name = $this->config->getTable('formtheme');

        // Check if button_typography column exists
        $button_typography_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'button_typography'",
            $table_name
        ));

        // Add button_typography column if it doesn't exist
        if (empty($button_typography_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN button_typography LONGTEXT AFTER next_button");
        }

        // Check if origin column exists
        $origin_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'origin'",
            $table_name
        ));

        // Add origin column if it doesn't exist
        if (empty($origin_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN origin varchar(50) DEFAULT 'user' AFTER category");
        }

        // Check if version column exists
        $version_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'version'",
            $table_name
        ));

        // Add version column if it doesn't exist
        if (empty($version_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN version float DEFAULT 1.0 AFTER origin");
        }
    }

    /**
     * Ensure the submissions table stays in sync with new columns.
     */
    private function migrateSubmissionTable(): void
    {
        global $wpdb;
        $this->setGlobals();
        $table_name = $this->config->getTable('submission');

        $payment_status_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'payment_status'",
            $table_name
        ));

        if (empty($payment_status_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN payment_status varchar(20) NOT NULL DEFAULT 'pending' AFTER status");
        }

        $payment_flag_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'is_payment_form'",
            $table_name
        ));

        if (empty($payment_flag_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN is_payment_form tinyint(1) NOT NULL DEFAULT 0 AFTER status");
        }

        $is_read_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'is_read'",
            $table_name
        ));

        if (empty($is_read_exists)) {
            $wpdb->query($wpdb->prepare(
                "ALTER TABLE %i ADD COLUMN is_read tinyint(1) NOT NULL DEFAULT 0 AFTER status",
                $table_name
            ));
        }

        $payment_meta_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'payment_meta'",
            $table_name
        ));

        if (empty($payment_meta_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN payment_meta longtext NULL AFTER payment_status");
        }

        $form_layout_version_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'form_layout_version'",
            $table_name
        ));

        if (empty($form_layout_version_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN form_layout_version INT(11) NOT NULL DEFAULT 0 AFTER submission");
        }

        $form_layout_hash_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'form_layout_hash'",
            $table_name
        ));

        if (empty($form_layout_hash_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN form_layout_hash CHAR(64) DEFAULT '' AFTER form_layout_version");
        }

        $layout_snapshot_id_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'layout_snapshot_id'",
            $table_name
        ));

        if (empty($layout_snapshot_id_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN layout_snapshot_id BIGINT(20) UNSIGNED NULL AFTER form_layout_hash");
        }

        $submission_version_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'submission_version'",
            $table_name
        ));

        if (empty($submission_version_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN submission_version INT(11) NOT NULL DEFAULT 0 AFTER layout_snapshot_id");
        }

        $submission_edit_count_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'submission_edit_count'",
            $table_name
        ));

        if (empty($submission_edit_count_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN submission_edit_count INT(11) NOT NULL DEFAULT 0 AFTER submission_version");
        }

        $current_revision_id_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'current_revision_id'",
            $table_name
        ));

        if (empty($current_revision_id_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN current_revision_id MEDIUMINT(9) DEFAULT NULL AFTER submission_edit_count");
        }

        $submission_hash_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'submission_hash'",
            $table_name
        ));

        if (empty($submission_hash_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN submission_hash CHAR(64) DEFAULT '' AFTER submission_version");
        }

        $submission_ip_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'submission_ip'",
            $table_name
        ));

        if (empty($submission_ip_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN submission_ip varchar(45) DEFAULT '' AFTER submission_hash");
        }

        $submission_user_agent_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'submission_user_agent'",
            $table_name
        ));

        if (empty($submission_user_agent_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN submission_user_agent text AFTER submission_ip");
        }

        $submission_meta_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'submission_meta'",
            $table_name
        ));

        if (empty($submission_meta_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN submission_meta longtext AFTER submission_user_agent");
        }

        $source_url_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'source_url'",
            $table_name
        ));

        if (empty($source_url_exists)) {
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN source_url varchar(2083) DEFAULT '' AFTER submission_meta");
        }
    }

    /**
     * Ensure layout element tables include form_id columns + index.
     */
    private function migrateLayoutElementTables(): void
    {
        $this->setGlobals();
        $this->migrateLayoutElementTable('formpage');
        $this->migrateLayoutElementTable('pagerow');
        $this->migrateLayoutElementTable('rowcolumn');
    }

    private function migrateLayoutElementTable(string $table_key): void
    {
        global $wpdb;
        $table_name = $this->config->getTable($table_key);

        $form_id_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = 'form_id'",
            $table_name
        ));

        if (empty($form_id_exists)) {
            $wpdb->query($wpdb->prepare(
                "ALTER TABLE %i ADD COLUMN form_id mediumint(9) DEFAULT 0 AFTER id",
                $table_name
            ));
        }

        $index_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = %s AND INDEX_NAME = 'form_id'",
            $table_name
        ));

        if (empty($index_exists)) {
            $wpdb->query($wpdb->prepare(
                "ALTER TABLE %i ADD INDEX form_id (form_id)",
                $table_name
            ));
        }
    }

    /**
     * Backfill submission revision history with a seed create revision for legacy rows.
     */
    private function maybeBackfillSubmissionHistory(): void
    {
        $flag = get_option('griffinforms_submission_history_backfilled');
        if (!empty($flag)) {
            return;
        }

        global $wpdb;
        $submission_table = $this->config->getTable('submission');
        $revision_table = $this->config->getTable('submission_revision');

        $revision_table_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = %s",
            $revision_table
        ));

        if (empty($revision_table_exists)) {
            return;
        }

        $submissions = $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM %i", $submission_table),
            ARRAY_A
        );

        if (is_array($submissions)) {
            foreach ($submissions as $submission) {
                $submission_id = isset($submission['id']) ? (int) $submission['id'] : 0;
                $form_id = isset($submission['form_id']) ? (int) $submission['form_id'] : 0;
                if ($submission_id <= 0 || $form_id <= 0) {
                    continue;
                }

                $existing_revision = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM %i WHERE submission_id = %d LIMIT 1",
                    $revision_table,
                    $submission_id
                ));

                if (!empty($existing_revision)) {
                    continue;
                }

                $snapshot = maybe_unserialize($submission['submission'] ?? array());
                if (!is_array($snapshot)) {
                    $snapshot = array();
                }

                $actor_user_id = isset($submission['author']) ? (int) $submission['author'] : 0;
                $actor = $actor_user_id > 0 ? get_user_by('id', $actor_user_id) : null;
                $actor_label = $actor && $actor->exists() ? (string) $actor->display_name : __('Unknown user', 'griffinforms-form-builder');

                $wpdb->insert(
                    $revision_table,
                    array(
                        'event_uid' => wp_generate_uuid4(),
                        'submission_id' => $submission_id,
                        'form_id' => $form_id,
                        'revision_number' => 1,
                        'event_type' => 'create',
                        'snapshot_json' => wp_json_encode($snapshot),
                        'snapshot_hash' => \GriffinForms\Includes\Helpers\Compliance::hashSubmission($snapshot),
                        'delta_json' => '',
                        'actor_user_id' => $actor_user_id,
                        'actor_label' => $actor_label,
                        'restored_from_revision_id' => 0,
                        'created' => $submission['created'] ?? current_time('mysql', true),
                    ),
                    array('%s', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%d', '%s')
                );

                $revision_id = (int) $wpdb->insert_id;
                if ($revision_id > 0) {
                    $wpdb->update(
                        $submission_table,
                        array('current_revision_id' => $revision_id),
                        array('id' => $submission_id),
                        array('%d'),
                        array('%d')
                    );
                }
            }
        }

        update_option('griffinforms_submission_history_backfilled', current_time('mysql', true));
    }

    /**
     * Backfill form_id on formpage/pagerow/rowcolumn based on current layout relations.
     */
    private function maybeBackfillFormElementFormIds(): void
    {
        $flag = get_option('griffinforms_form_element_form_id_backfilled');
        if (!empty($flag)) {
            return;
        }

        $batch_size = 25;
        $last_id = (int) get_option('griffinforms_form_element_form_id_backfill_last_id', 0);

        global $wpdb;
        $form_table = $this->config->getTable('form');
        $page_table = $this->config->getTable('formpage');
        $row_table = $this->config->getTable('pagerow');
        $column_table = $this->config->getTable('rowcolumn');
        $log = \GriffinForms\Log::getInstance();

        if (
            !$this->columnExists($page_table, 'form_id') ||
            !$this->columnExists($row_table, 'form_id') ||
            !$this->columnExists($column_table, 'form_id')
        ) {
            return;
        }

        $forms = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, formpages FROM %i WHERE id > %d ORDER BY id ASC LIMIT %d",
                $form_table,
                $last_id,
                $batch_size
            ),
            ARRAY_A
        );

        if (empty($forms)) {
            update_option('griffinforms_form_element_form_id_backfilled', current_time('mysql', true));
            delete_option('griffinforms_form_element_form_id_backfill_last_id');
            return;
        }

        foreach ($forms as $form) {
            $form_id = (int) ($form['id'] ?? 0);
            if ($form_id <= 0) {
                continue;
            }

            $page_ids = maybe_unserialize($form['formpages']);
            if (!is_array($page_ids)) {
                $last_id = $form_id;
                continue;
            }

            $page_ids = array_values(array_filter(array_map('absint', $page_ids)));
            if (empty($page_ids)) {
                $last_id = $form_id;
                continue;
            }

            $page_placeholders = implode(',', array_fill(0, count($page_ids), '%d'));
            $page_update_args = array_merge([$form_id], $page_ids);
            $wpdb->query(
                $wpdb->prepare(
                    "UPDATE %i SET form_id = %d WHERE id IN ({$page_placeholders}) AND (form_id IS NULL OR form_id = 0)",
                    $page_table,
                    ...$page_update_args
                )
            );
            if (!empty($wpdb->last_error)) {
                $log->add('warning', 'form', $form_id, get_current_user_id(), 'Form ID backfill (formpage) failed: ' . $wpdb->last_error, 'db');
            }

            $pages = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, pagerows FROM %i WHERE id IN ({$page_placeholders})",
                    $page_table,
                    ...$page_ids
                ),
                ARRAY_A
            );

            $row_ids = [];
            if (is_array($pages)) {
                foreach ($pages as $page) {
                    $pagerows = maybe_unserialize($page['pagerows']);
                    if (!is_array($pagerows)) {
                        continue;
                    }
                    foreach ($pagerows as $row_id) {
                        $row_id = absint($row_id);
                        if ($row_id > 0) {
                            $row_ids[$row_id] = $row_id;
                        }
                    }
                }
            }

            if (!empty($row_ids)) {
                $row_ids = array_values($row_ids);
                $row_placeholders = implode(',', array_fill(0, count($row_ids), '%d'));
                $row_update_args = array_merge([$form_id], $row_ids);
                $wpdb->query(
                    $wpdb->prepare(
                        "UPDATE %i SET form_id = %d WHERE id IN ({$row_placeholders}) AND (form_id IS NULL OR form_id = 0)",
                        $row_table,
                        ...$row_update_args
                    )
                );
                if (!empty($wpdb->last_error)) {
                    $log->add('warning', 'form', $form_id, get_current_user_id(), 'Form ID backfill (pagerow) failed: ' . $wpdb->last_error, 'db');
                }

                $rows = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT id, rowcolumns FROM %i WHERE id IN ({$row_placeholders})",
                        $row_table,
                        ...$row_ids
                    ),
                    ARRAY_A
                );

                $column_ids = [];
                if (is_array($rows)) {
                    foreach ($rows as $row) {
                        $rowcolumns = maybe_unserialize($row['rowcolumns']);
                        if (!is_array($rowcolumns)) {
                            continue;
                        }
                        foreach ($rowcolumns as $column_id) {
                            $column_id = absint($column_id);
                            if ($column_id > 0) {
                                $column_ids[$column_id] = $column_id;
                            }
                        }
                    }
                }

                if (!empty($column_ids)) {
                    $column_ids = array_values($column_ids);
                    $column_placeholders = implode(',', array_fill(0, count($column_ids), '%d'));
                    $column_update_args = array_merge([$form_id], $column_ids);
                    $wpdb->query(
                        $wpdb->prepare(
                            "UPDATE %i SET form_id = %d WHERE id IN ({$column_placeholders}) AND (form_id IS NULL OR form_id = 0)",
                            $column_table,
                            ...$column_update_args
                        )
                    );
                    if (!empty($wpdb->last_error)) {
                        $log->add('warning', 'form', $form_id, get_current_user_id(), 'Form ID backfill (rowcolumn) failed: ' . $wpdb->last_error, 'db');
                    }
                }
            }

            $last_id = $form_id;
        }

        update_option('griffinforms_form_element_form_id_backfill_last_id', $last_id);
    }

    private function columnExists(string $table, string $column): bool
    {
        $columns = $this->getTableColumns($table);
        return isset($columns[$column]);
    }

    /**
     * Load a table's columns once per request so schema checks do not hammer
     * INFORMATION_SCHEMA repeatedly during upgrade and bootstrap paths.
     *
     * @return array<string, true>
     */
    private function getTableColumns(string $table): array
    {
        static $cache = array();
        if (array_key_exists($table, $cache)) {
            return $cache[$table];
        }

        global $wpdb;
        $columns = $wpdb->get_col(
            $wpdb->prepare("SHOW COLUMNS FROM %i", $table),
            0
        );

        if (!is_array($columns)) {
            $columns = array();
        }

        $cache[$table] = array_fill_keys(array_map('strval', $columns), true);
        return $cache[$table];
    }
    
    public function __construct()
    {
    }

}
