<?php
declare(strict_types=1);

namespace Help4BuilderSuite\Database;

use Help4BuilderSuite\Support\FeatureFlags;
use Help4BuilderSuite\Support\NetworkSettings;
use Help4BuilderSuite\Support\Settings;

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

final class Installer
{
    public static function install(bool $networkWide = false): void
    {
        self::installForCurrentSite();

        if (! is_multisite() || ! $networkWide) {
            return;
        }

        update_site_option(
            NetworkSettings::PROVISION_STATE_OPTION,
            [
                'status' => 'running',
                'offset' => 0,
                'processed' => 0,
                'failed' => [],
                'version' => defined('H4BS_VERSION') ? H4BS_VERSION : '',
                'started_at' => gmdate('c'),
                'completed_at' => '',
            ]
        );
        if (! wp_next_scheduled(NetworkSettings::PROVISION_HOOK)) {
            wp_schedule_single_event(time() + 5, NetworkSettings::PROVISION_HOOK);
        }
    }

    public static function installForCurrentSite(): void
    {
        global $wpdb;

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

        $charsetCollate = $wpdb->get_charset_collate();
        $prefix         = $wpdb->prefix;

        $productsTable = "{$prefix}h4_products";
        $variantsTable = "{$prefix}h4_product_variants";
        $ordersTable   = "{$prefix}h4_orders";
        $orderItems    = "{$prefix}h4_order_items";
        $customersTable = "{$prefix}h4_customers";
        $customerFieldsTable = "{$prefix}h4_customer_fields";
        $customerFieldValuesTable = "{$prefix}h4_customer_field_values";
        $reviewsTable   = "{$prefix}h4_product_reviews";
        $subsTable     = "{$prefix}h4_subscriptions";
        $promotionsTable = "{$prefix}h4_promotions";
        $promotionRedemptions = "{$prefix}h4_promotion_redemptions";
        $lmsEnrollments = "{$prefix}h4_lms_enrollments";
        $lmsProgress    = "{$prefix}h4_lms_progress";
        $lmsCertificates = "{$prefix}h4_lms_certificates";
        $formsTable      = "{$prefix}h4_forms";
        $formEntriesTable = "{$prefix}h4_form_entries";
        $formPrivacyAuditTable = "{$prefix}h4_form_privacy_audit";
        $newsletterTable = "{$prefix}h4_newsletter_subscribers";
        $emailSegments = "{$prefix}h4_email_segments";
        $emailTemplates = "{$prefix}h4_email_templates";
        $emailCampaigns = "{$prefix}h4_email_campaigns";
        $emailRecipients = "{$prefix}h4_email_recipients";
        $emailEvents = "{$prefix}h4_email_events";
        $emailSuppressions = "{$prefix}h4_email_suppressions";
        $emailAutomations = "{$prefix}h4_email_automations";
        $emailAutomationSteps = "{$prefix}h4_email_automation_steps";
        $emailEnrollments = "{$prefix}h4_email_enrollments";
        $redirectsTable  = "{$prefix}h4_redirects";
        $notFoundTable   = "{$prefix}h4_404_logs";

        dbDelta(
            "CREATE TABLE {$productsTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                sku VARCHAR(100) NOT NULL DEFAULT '',
                name VARCHAR(255) NOT NULL,
                slug VARCHAR(190) NOT NULL,
                status VARCHAR(32) NOT NULL DEFAULT 'active',
                price DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                currency VARCHAR(8) NOT NULL DEFAULT 'USD',
                stock_qty BIGINT NOT NULL DEFAULT 0,
                payload LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY sku (sku),
                KEY slug (slug),
                KEY status (status),
                KEY name (name),
                KEY updated_at (updated_at),
                KEY updated_id (updated_at, id),
                KEY status_updated_id (status, updated_at, id),
                KEY status_sku (status, sku),
                KEY status_slug (status, slug)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$variantsTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                product_id BIGINT UNSIGNED NOT NULL,
                sku VARCHAR(100) NOT NULL DEFAULT '',
                attributes_json LONGTEXT NULL,
                price DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                stock_qty BIGINT NOT NULL DEFAULT 0,
                image_id BIGINT UNSIGNED NULL,
                position INT NOT NULL DEFAULT 0,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY product_id (product_id),
                KEY sku (sku),
                KEY product_position (product_id, position, id),
                KEY product_sku (product_id, sku)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$ordersTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                order_number VARCHAR(80) NOT NULL,
                checkout_key VARCHAR(64) NULL,
                customer_email VARCHAR(190) NOT NULL DEFAULT '',
                customer_user_id BIGINT UNSIGNED NULL,
                total DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                currency VARCHAR(8) NOT NULL DEFAULT 'USD',
                status VARCHAR(32) NOT NULL DEFAULT 'pending',
                gateway VARCHAR(64) NOT NULL DEFAULT '',
                gateway_reference VARCHAR(190) NOT NULL DEFAULT '',
                payload LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY order_number (order_number),
                UNIQUE KEY checkout_key (checkout_key),
                KEY customer_email (customer_email),
                KEY customer_user_id (customer_user_id),
                KEY status (status),
                KEY status_created_id (status, created_at, id),
                KEY customer_email_created (customer_email, created_at, id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$customersTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                wp_user_id BIGINT UNSIGNED NULL,
                email VARCHAR(190) NOT NULL DEFAULT '',
                first_name VARCHAR(120) NOT NULL DEFAULT '',
                last_name VARCHAR(120) NOT NULL DEFAULT '',
                role VARCHAR(64) NOT NULL DEFAULT 'customer',
                status VARCHAR(64) NOT NULL DEFAULT 'active',
                order_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
                total_spent DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                last_order_id BIGINT UNSIGNED NULL,
                payload LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY email (email),
                KEY wp_user_id (wp_user_id),
                KEY role (role),
                KEY status (status),
                KEY updated_at (updated_at),
                KEY updated_id (updated_at, id),
                KEY email_status (email(150), status)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$customerFieldsTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                field_key VARCHAR(64) NOT NULL,
                label VARCHAR(190) NOT NULL DEFAULT '',
                field_type VARCHAR(32) NOT NULL DEFAULT 'text',
                field_group VARCHAR(120) NOT NULL DEFAULT 'Customer details',
                placements VARCHAR(255) NOT NULL DEFAULT '[\"admin\"]',
                status VARCHAR(20) NOT NULL DEFAULT 'active',
                sort_order INT NOT NULL DEFAULT 10,
                required TINYINT(1) NOT NULL DEFAULT 0,
                customer_editable TINYINT(1) NOT NULL DEFAULT 1,
                show_in_orders TINYINT(1) NOT NULL DEFAULT 1,
                exportable TINYINT(1) NOT NULL DEFAULT 1,
                sensitivity VARCHAR(20) NOT NULL DEFAULT 'personal',
                settings LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY field_key (field_key),
                KEY status_sort (status, sort_order, id),
                KEY field_group (field_group)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$customerFieldValuesTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                customer_id BIGINT UNSIGNED NOT NULL,
                field_key VARCHAR(64) NOT NULL,
                field_value LONGTEXT NULL,
                search_value VARCHAR(190) NOT NULL DEFAULT '',
                sensitivity VARCHAR(20) NOT NULL DEFAULT 'personal',
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY customer_field (customer_id, field_key),
                KEY field_search (field_key, search_value(150)),
                KEY customer_updated (customer_id, updated_at)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$orderItems} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                order_id BIGINT UNSIGNED NOT NULL,
                product_id BIGINT UNSIGNED NOT NULL,
                variant_id BIGINT UNSIGNED NULL,
                qty DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
                unit_price DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                payload LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY order_id (order_id),
                KEY product_id (product_id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$reviewsTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                product_id BIGINT UNSIGNED NOT NULL,
                order_id BIGINT UNSIGNED NULL,
                customer_user_id BIGINT UNSIGNED NULL,
                customer_email VARCHAR(190) NOT NULL DEFAULT '',
                rating TINYINT UNSIGNED NOT NULL DEFAULT 5,
                title VARCHAR(255) NOT NULL DEFAULT '',
                review_text LONGTEXT NULL,
                status VARCHAR(32) NOT NULL DEFAULT 'pending',
                verified_purchase TINYINT(1) NOT NULL DEFAULT 1,
                payload LONGTEXT 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 product_status_created (product_id, status, created_at),
                KEY customer_lookup (customer_user_id, customer_email),
                KEY status_created (status, created_at),
                KEY order_id (order_id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$subsTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                customer_email VARCHAR(190) NOT NULL DEFAULT '',
                product_id BIGINT UNSIGNED NOT NULL,
                variant_id BIGINT UNSIGNED NULL,
                interval_code VARCHAR(32) NOT NULL DEFAULT 'monthly',
                amount DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                currency VARCHAR(8) NOT NULL DEFAULT 'USD',
                status VARCHAR(32) NOT NULL DEFAULT 'active',
                gateway VARCHAR(64) NOT NULL DEFAULT '',
                payload LONGTEXT 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 customer_email (customer_email),
                KEY status (status),
                KEY product_id (product_id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$promotionsTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                code VARCHAR(80) NOT NULL,
                name VARCHAR(190) NOT NULL DEFAULT '',
                status VARCHAR(32) NOT NULL DEFAULT 'active',
                discount_type VARCHAR(32) NOT NULL DEFAULT 'percent',
                amount DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                currency VARCHAR(8) NOT NULL DEFAULT '',
                minimum_subtotal DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                maximum_discount DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                usage_limit BIGINT UNSIGNED NOT NULL DEFAULT 0,
                usage_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
                per_customer_limit BIGINT UNSIGNED NOT NULL DEFAULT 0,
                starts_at DATETIME NULL,
                ends_at DATETIME NULL,
                payload LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY code (code),
                KEY status_dates (status, starts_at, ends_at),
                KEY updated_id (updated_at, id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$promotionRedemptions} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                promotion_id BIGINT UNSIGNED NOT NULL,
                order_id BIGINT UNSIGNED NOT NULL,
                customer_hash CHAR(64) NOT NULL DEFAULT '',
                code VARCHAR(80) NOT NULL DEFAULT '',
                discount DECIMAL(16,4) NOT NULL DEFAULT 0.0000,
                status VARCHAR(32) NOT NULL DEFAULT 'reserved',
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY promotion_order (promotion_id, order_id),
                KEY order_id (order_id),
                KEY promotion_customer_status (promotion_id, customer_hash, status)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$lmsEnrollments} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                course_post_id BIGINT UNSIGNED NOT NULL,
                user_id BIGINT UNSIGNED NULL,
                user_email VARCHAR(190) NOT NULL DEFAULT '',
                status VARCHAR(32) NOT NULL DEFAULT 'active',
                progress_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
                payload LONGTEXT NULL,
                enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY course_post_id (course_post_id),
                KEY user_id (user_id),
                KEY user_email (user_email),
                KEY status (status)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$lmsProgress} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                enrollment_id BIGINT UNSIGNED NOT NULL,
                lesson_post_id BIGINT UNSIGNED NOT NULL,
                status VARCHAR(32) NOT NULL DEFAULT 'incomplete',
                score DECIMAL(6,2) NOT NULL DEFAULT 0.00,
                payload LONGTEXT NULL,
                completed_at DATETIME NULL,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY enrollment_id (enrollment_id),
                KEY lesson_post_id (lesson_post_id),
                KEY status (status)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$lmsCertificates} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                enrollment_id BIGINT UNSIGNED NOT NULL,
                certificate_code VARCHAR(80) NOT NULL,
                payload LONGTEXT NULL,
                issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                expires_at DATETIME NULL,
                PRIMARY KEY (id),
                UNIQUE KEY certificate_code (certificate_code),
                KEY enrollment_id (enrollment_id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$formsTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                slug VARCHAR(160) NOT NULL DEFAULT '',
                name VARCHAR(190) NOT NULL DEFAULT '',
                type VARCHAR(64) NOT NULL DEFAULT 'contact',
                status VARCHAR(32) NOT NULL DEFAULT 'active',
                fields_json LONGTEXT NULL,
                settings_json LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY slug (slug),
                KEY type_status (type, status),
                KEY updated_id (updated_at, id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$formEntriesTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                form_id BIGINT UNSIGNED NOT NULL,
                entry_type VARCHAR(64) NOT NULL DEFAULT 'submission',
                name VARCHAR(190) NOT NULL DEFAULT '',
                email VARCHAR(190) NOT NULL DEFAULT '',
                phone VARCHAR(64) NOT NULL DEFAULT '',
                subject VARCHAR(255) NOT NULL DEFAULT '',
                payload LONGTEXT NULL,
                source_url VARCHAR(255) NOT NULL DEFAULT '',
                ip_address VARCHAR(64) NOT NULL DEFAULT '',
                user_agent VARCHAR(255) NOT NULL DEFAULT '',
                is_spam TINYINT(1) NOT NULL DEFAULT 0,
                staff_status VARCHAR(32) NOT NULL DEFAULT 'new',
                assigned_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                archived_at DATETIME NULL,
                operations_revision VARCHAR(64) NOT NULL DEFAULT '',
                privacy_hold_at DATETIME NULL,
                privacy_hold_reason VARCHAR(500) NOT NULL DEFAULT '',
                privacy_hold_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
                privacy_hold_revision CHAR(64) NOT NULL DEFAULT '',
                timestamp_basis VARCHAR(32) NOT NULL DEFAULT 'legacy_unknown',
                timestamp_basis_version VARCHAR(32) NOT NULL DEFAULT '',
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY form_id_created (form_id, created_at, id),
                KEY entry_type_created (entry_type, created_at, id),
                KEY email_created (email, created_at, id),
                KEY is_spam_created (is_spam, created_at, id)
                ,KEY staff_status_created (staff_status, created_at, id)
                ,KEY assigned_created (assigned_user_id, created_at, id)
                ,KEY archived_created (archived_at, created_at, id)
                ,KEY privacy_hold_created (privacy_hold_at, created_at, id)
                ,KEY timestamp_basis_created (timestamp_basis, created_at, id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$formPrivacyAuditTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                request_id CHAR(36) NOT NULL,
                action VARCHAR(32) NOT NULL,
                entry_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                payload_hash CHAR(64) NOT NULL,
                previous_revision CHAR(64) NOT NULL DEFAULT '',
                new_revision CHAR(64) NOT NULL DEFAULT '',
                reason_hash CHAR(64) NOT NULL DEFAULT '',
                item_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
                cutoff_at DATETIME NULL,
                policy_revision CHAR(64) NOT NULL DEFAULT '',
                fingerprint CHAR(64) NOT NULL DEFAULT '',
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY request_id (request_id),
                KEY entry_created (entry_id, created_at, id),
                KEY action_created (action, created_at, id)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$newsletterTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                email VARCHAR(190) NOT NULL DEFAULT '',
                first_name VARCHAR(120) NOT NULL DEFAULT '',
                last_name VARCHAR(120) NOT NULL DEFAULT '',
                status VARCHAR(32) NOT NULL DEFAULT 'subscribed',
                tags VARCHAR(255) NOT NULL DEFAULT '',
                source VARCHAR(255) NOT NULL DEFAULT '',
                ip_address VARCHAR(64) NOT NULL DEFAULT '',
                payload LONGTEXT NULL,
                confirmation_token_hash CHAR(64) NOT NULL DEFAULT '',
                confirmation_expires_at DATETIME NULL,
                confirmation_sent_at DATETIME NULL,
                consent_at DATETIME NULL,
                consent_source VARCHAR(255) NOT NULL DEFAULT '',
                consent_method VARCHAR(32) NOT NULL DEFAULT '',
                user_agent_hash CHAR(64) NOT NULL DEFAULT '',
                unsubscribed_at DATETIME NULL,
                subscribed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY email (email),
                KEY status_updated (status, updated_at, id),
                KEY subscribed_at (subscribed_at)
            ) {$charsetCollate};"
        );

        dbDelta("CREATE TABLE {$emailSegments} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(190) NOT NULL,
            match_type VARCHAR(16) NOT NULL DEFAULT 'all',
            rules_json LONGTEXT NULL,
            status VARCHAR(24) NOT NULL DEFAULT 'active',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status_name (status,name)
        ) {$charsetCollate};");

        dbDelta("CREATE TABLE {$emailTemplates} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(190) NOT NULL,
            subject VARCHAR(255) NOT NULL DEFAULT '',
            preview_text VARCHAR(255) NOT NULL DEFAULT '',
            content_html LONGTEXT NOT NULL,
            content_text LONGTEXT NULL,
            status VARCHAR(24) NOT NULL DEFAULT 'active',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status_name (status,name)
        ) {$charsetCollate};");

        dbDelta("CREATE TABLE {$emailCampaigns} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(190) NOT NULL,
            segment_id BIGINT UNSIGNED NULL,
            subject VARCHAR(255) NOT NULL,
            preview_text VARCHAR(255) NOT NULL DEFAULT '',
            content_html LONGTEXT NOT NULL,
            content_text LONGTEXT NULL,
            status VARCHAR(24) NOT NULL DEFAULT 'draft',
            tracking_enabled TINYINT(1) NOT NULL DEFAULT 0,
            scheduled_at DATETIME NULL,
            started_at DATETIME NULL,
            completed_at DATETIME NULL,
            created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
            snapshot_cursor BIGINT UNSIGNED NOT NULL DEFAULT 0,
            snapshot_complete TINYINT(1) NOT NULL DEFAULT 0,
            recipient_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
            queued_at DATETIME NULL,
            pause_reason VARCHAR(190) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status_schedule (status,scheduled_at),
            KEY segment_id (segment_id)
        ) {$charsetCollate};");

        dbDelta("CREATE TABLE {$emailRecipients} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            campaign_id BIGINT UNSIGNED NOT NULL,
            subscriber_id BIGINT UNSIGNED NOT NULL,
            status VARCHAR(24) NOT NULL DEFAULT 'queued',
            attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            next_attempt_at DATETIME NULL,
            accepted_at DATETIME NULL,
            message_id VARCHAR(190) NOT NULL DEFAULT '',
            last_error TEXT NULL,
            open_count INT UNSIGNED NOT NULL DEFAULT 0,
            first_opened_at DATETIME NULL,
            last_opened_at DATETIME NULL,
            click_count INT UNSIGNED NOT NULL DEFAULT 0,
            first_clicked_at DATETIME NULL,
            last_clicked_at DATETIME NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY campaign_subscriber (campaign_id,subscriber_id),
            KEY queue (status,next_attempt_at),
            KEY subscriber_id (subscriber_id)
        ) {$charsetCollate};");

        dbDelta("CREATE TABLE {$emailEvents} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            campaign_id BIGINT UNSIGNED NOT NULL,
            recipient_id BIGINT UNSIGNED NOT NULL,
            subscriber_id BIGINT UNSIGNED NOT NULL,
            automation_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
            enrollment_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
            step_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
            event_type VARCHAR(20) NOT NULL,
            url_hash CHAR(64) NOT NULL DEFAULT '',
            destination_url TEXT NULL,
            request_hash CHAR(64) NOT NULL DEFAULT '',
            is_automated TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY campaign_event (campaign_id,event_type),
            KEY recipient_event (recipient_id,event_type),
            KEY automation_event (automation_id,event_type),
            KEY enrollment_event (enrollment_id,event_type),
            KEY created_at (created_at)
        ) {$charsetCollate};");

        dbDelta("CREATE TABLE {$emailSuppressions} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            email VARCHAR(190) NOT NULL,
            reason VARCHAR(40) NOT NULL DEFAULT 'unsubscribe',
            detail TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY email (email),
            KEY reason (reason)
        ) {$charsetCollate};");

        dbDelta("CREATE TABLE {$emailAutomations} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(190) NOT NULL,
            trigger_type VARCHAR(40) NOT NULL DEFAULT 'subscriber_added',
            segment_id BIGINT UNSIGNED NULL,
            status VARCHAR(24) NOT NULL DEFAULT 'draft',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status_trigger (status,trigger_type)
        ) {$charsetCollate};");

        dbDelta("CREATE TABLE {$emailAutomationSteps} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            automation_id BIGINT UNSIGNED NOT NULL,
            step_order SMALLINT UNSIGNED NOT NULL,
            delay_hours INT UNSIGNED NOT NULL DEFAULT 0,
            subject VARCHAR(255) NOT NULL,
            content_html LONGTEXT NOT NULL,
            content_text LONGTEXT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY automation_step (automation_id,step_order)
        ) {$charsetCollate};");

        dbDelta("CREATE TABLE {$emailEnrollments} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            automation_id BIGINT UNSIGNED NOT NULL,
            subscriber_id BIGINT UNSIGNED NOT NULL,
            next_step SMALLINT UNSIGNED NOT NULL DEFAULT 1,
            status VARCHAR(24) NOT NULL DEFAULT 'active',
            next_send_at DATETIME NULL,
            last_sent_at DATETIME NULL,
            attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            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),
            UNIQUE KEY automation_subscriber (automation_id,subscriber_id),
            KEY queue (status,next_send_at)
        ) {$charsetCollate};");

        dbDelta(
            "CREATE TABLE {$redirectsTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                source_path VARCHAR(255) NOT NULL DEFAULT '',
                match_type VARCHAR(32) NOT NULL DEFAULT 'exact',
                target_url VARCHAR(255) NOT NULL DEFAULT '',
                status VARCHAR(32) NOT NULL DEFAULT 'active',
                http_code SMALLINT UNSIGNED NOT NULL DEFAULT 301,
                hits BIGINT UNSIGNED NOT NULL DEFAULT 0,
                last_hit_at DATETIME 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 status_match (status, match_type),
                KEY source_path (source_path),
                KEY hits (hits)
            ) {$charsetCollate};"
        );

        dbDelta(
            "CREATE TABLE {$notFoundTable} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                request_path VARCHAR(255) NOT NULL DEFAULT '',
                referer_url VARCHAR(255) NOT NULL DEFAULT '',
                hits BIGINT UNSIGNED NOT NULL DEFAULT 1,
                first_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                last_ip VARCHAR(64) NOT NULL DEFAULT '',
                user_agent VARCHAR(255) NOT NULL DEFAULT '',
                PRIMARY KEY (id),
                UNIQUE KEY request_path (request_path),
                KEY last_seen (last_seen),
                KEY hits (hits)
            ) {$charsetCollate};"
        );

        $existingFlags = get_option(FeatureFlags::OPTION_KEY, []);
        if (! is_array($existingFlags)) {
            $existingFlags = [];
        }
        update_option(FeatureFlags::OPTION_KEY, array_merge((new FeatureFlags())->defaults(), $existingFlags));

        $existingSettings = get_option(Settings::OPTION_KEY, []);
        if (! is_array($existingSettings)) {
            $existingSettings = [];
        }
        update_option(Settings::OPTION_KEY, array_merge(Settings::defaults(), $existingSettings));
        if (defined('H4BS_VERSION')) {
            update_option('h4bs_db_version', H4BS_VERSION, false);
        }
    }
}
