<?php

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

if (!function_exists('aieo_push_install_schema')) {
    
    function aieo_push_install_schema() {
        if (!function_exists('dbDelta')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
        aieo_push_create_customer_intelligence_table();
        aieo_push_create_subscriptions_table();
        aieo_push_create_dispatch_table();
        aieo_push_create_templates_table();
        aieo_push_create_caps_table();
        aieo_push_create_campaigns_table();
        aieo_push_create_newsletter_sync_log_table();
        aieo_push_seed_default_templates();
    }
}

if (!function_exists('aieo_push_seed_default_templates')) {
    
    function aieo_push_seed_default_templates() {
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_push_templates';
        
        
        
        
        
        if (function_exists('get_woocommerce_currency_symbol')) {
            $cur = html_entity_decode(get_woocommerce_currency_symbol(), ENT_QUOTES, 'UTF-8');
        } else {
            $cur = (string) get_option('woocommerce_currency', '');
        }
        $defaults = array(
            'price_drop_wishlist' => array(
                'title' => 'Έπεσε η τιμή! −{{drop_pct}}%',
                'body'  => sprintf('{{product_name}} τώρα %1$s{{current_price}} (από %1$s{{snapshot_price}}). Δες τώρα.', $cur),
            ),
            'back_in_stock_viewed' => array(
                'title' => 'Διαθέσιμο ξανά: {{product_name}}',
                'body'  => 'Το προϊόν που είχες δει επέστρεψε. Άρπαξέ το πριν εξαντληθεί.',
            ),
            'cart_1h' => array(
                'title' => 'Άφησες κάτι στο καλάθι σου',
                'body'  => '{{product_name}} σε περιμένει. Ολοκλήρωσε σε λίγα κλικ.',
            ),
            'cart_24h' => array(
                'title' => 'Το {{product_name}} σε περιμένει ακόμα',
                'body'  => 'Το καλάθι σου είναι έτοιμο — ολοκλήρωσε την παραγγελία σου.',
            ),
            'cart_72h' => array(
                'title' => 'Τελευταία ευκαιρία για το {{product_name}}',
                'body'  => 'Το αγαπημένο σου παραμένει διαθέσιμο. Δες πριν εξαντληθεί.',
            ),
            'replenishment' => array(
                'title' => 'Καιρός για ανανέωση: {{product_name}}',
                'body'  => 'Με βάση τον ρυθμό σου, μάλλον σου τελειώνει. Παράγγειλε ξανά εύκολα.',
            ),
            'new_arrival_loved_brand' => array(
                'title' => 'Νέα άφιξη από {{brand_name}}',
                'body'  => '{{product_name}} μόλις προστέθηκε στη συλλογή.',
            ),
            'win_back' => array(
                'title' => 'Σου λείψαμε; Σε περιμένουμε',
                'body'  => 'Επέστρεψε και ανακάλυψε τι νέο έχουμε για σένα.',
            ),
        );

        foreach ($defaults as $trigger_id => $copy) {
            $exists = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT template_id FROM {$tbl} WHERE trigger_id = %s LIMIT 1",
                $trigger_id
            ));
            if ($exists > 0) { continue; }

            $wpdb->insert($tbl, array(
                'trigger_id'     => $trigger_id,
                'segment_id'     => '*',
                'variant_label'  => 'A',
                'weight'         => 1,
                'title_template' => $copy['title'],
                'body_template'  => $copy['body'],
                'icon_url'       => '',
                'cta_label'      => '',
                'is_active'      => 1,
            ), array('%s','%s','%s','%d','%s','%s','%s','%s','%d'));
        }
    }
}

if (!function_exists('aieo_push_create_customer_intelligence_table')) {
    
    function aieo_push_create_customer_intelligence_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_customer_intelligence';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            eponymous_id bigint(20) UNSIGNED NOT NULL,
            wp_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            email varchar(190) NOT NULL DEFAULT '',
            first_order_at datetime NULL,
            last_order_at datetime NULL,
            orders_count int(10) UNSIGNED NOT NULL DEFAULT 0,
            ltv_total decimal(12,2) NOT NULL DEFAULT 0.00,
            aov decimal(10,2) NOT NULL DEFAULT 0.00,
            recency_days smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            monetary_band tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            frequency_band tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            recency_band tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            rfm_cell char(3) NOT NULL DEFAULT '',
            discount_share decimal(5,4) NOT NULL DEFAULT 0.0000,
            avg_item_price decimal(10,2) NOT NULL DEFAULT 0.00,
            brand_affinity_json longtext NULL,
            category_affinity_json longtext NULL,
            replenishment_json longtext NULL,
            price_band tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            top_brand_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            top_brand_share decimal(5,4) NOT NULL DEFAULT 0.0000,
            top_category_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            top_category_share decimal(5,4) NOT NULL DEFAULT 0.0000,
            segments_json longtext NULL,
            -- Reactivation intelligence (Phase 2.5) ------------------------
            -- dormancy_cohort: '' (not dormant) | drifting | lapsed | long_dormant | lost
            dormancy_cohort varchar(20) NOT NULL DEFAULT '',
            -- reactivation_score: 0-100. High score = recoverable VIP.
            -- See aieo_sp_assign_segments for the formula.
            reactivation_score tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            -- attempt tracking — populated by Phase 5+ win-back trigger.
            last_reactivation_attempt_at datetime NULL,
            reactivation_attempts_count smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            -- Set by the recompute sproc when a previously-dormant customer
            -- returns to active state. The success signal we measure.
            reactivated_at datetime NULL,
            -- ----------------------------------------------------------------
            taste_vector mediumblob NULL,
            taste_vector_updated_at datetime NULL,
            sole_order_share decimal(5,4) NOT NULL DEFAULT 0.0000,
            top_sole_product_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            top_first_product_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            top_last_product_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            avg_jlw decimal(5,4) NOT NULL DEFAULT 0.0000,
            avg_vlw decimal(5,4) NOT NULL DEFAULT 0.0000,
            core_needs_json longtext NULL,
            tnp_subscriber_id int(10) UNSIGNED NOT NULL DEFAULT 0,
            tnp_status char(1) NOT NULL DEFAULT '',
            emails_sent_lifetime int(10) UNSIGNED NOT NULL DEFAULT 0,
            emails_opened_lifetime int(10) UNSIGNED NOT NULL DEFAULT 0,
            emails_clicked_lifetime int(10) UNSIGNED NOT NULL DEFAULT 0,
            open_rate_lifetime decimal(5,4) NOT NULL DEFAULT 0.0000,
            click_rate_lifetime decimal(5,4) NOT NULL DEFAULT 0.0000,
            emails_sent_30d smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            emails_opened_30d smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            open_rate_30d decimal(5,4) NOT NULL DEFAULT 0.0000,
            last_email_open_at datetime NULL,
            last_email_click_at datetime NULL,
            days_since_last_engagement smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            inferred_top_priority varchar(40) NOT NULL DEFAULT '',
            inferred_priorities_json longtext NULL,
            inferred_priorities_source varchar(20) NOT NULL DEFAULT '',
            inferred_priorities_confidence decimal(3,2) NOT NULL DEFAULT 0.00,
            inferred_priorities_updated_at datetime NULL,
            source_table varchar(64) NOT NULL DEFAULT '',
            intelligence_version smallint(5) UNSIGNED NOT NULL DEFAULT 1,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (eponymous_id),
            KEY idx_wp_user (wp_user_id),
            KEY idx_recency (recency_days),
            KEY idx_last_order (last_order_at),
            KEY idx_top_brand (top_brand_id),
            KEY idx_top_category (top_category_id),
            KEY idx_rfm (rfm_cell),
            KEY idx_email (email),
            KEY idx_ltv (ltv_total),
            KEY idx_dormancy_cohort (dormancy_cohort),
            KEY idx_reactivation_score (reactivation_score),
            KEY idx_inferred_top_priority (inferred_top_priority),
            KEY idx_tnp_subscriber (tnp_subscriber_id),
            KEY idx_engagement_recency (days_since_last_engagement)
        ) {$charset};";

        aieo_dbdelta($sql);

        
        
        
        
        aieo_push_ensure_ci_columns();
    }
}

if (!function_exists('aieo_push_ensure_ci_columns')) {
    
    function aieo_push_ensure_ci_columns() {
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_customer_intelligence';
        if ((string) $wpdb->get_var("SHOW TABLES LIKE '{$tbl}'") !== $tbl) {
            return;
        }
        
        $cols = array(
            
            'sole_order_share'     => "DECIMAL(5,4) NOT NULL DEFAULT 0.0000",
            'top_sole_product_id'  => "BIGINT(20) UNSIGNED NOT NULL DEFAULT 0",
            'top_first_product_id' => "BIGINT(20) UNSIGNED NOT NULL DEFAULT 0",
            'top_last_product_id'  => "BIGINT(20) UNSIGNED NOT NULL DEFAULT 0",
            'avg_jlw'              => "DECIMAL(5,4) NOT NULL DEFAULT 0.0000",
            'avg_vlw'              => "DECIMAL(5,4) NOT NULL DEFAULT 0.0000",
            'core_needs_json'      => "LONGTEXT NULL",
            
            'inferred_top_priority'          => "VARCHAR(40) NOT NULL DEFAULT ''",
            'inferred_priorities_json'       => "LONGTEXT NULL",
            'inferred_priorities_source'     => "VARCHAR(20) NOT NULL DEFAULT ''",
            'inferred_priorities_confidence' => "DECIMAL(3,2) NOT NULL DEFAULT 0.00",
            'inferred_priorities_updated_at' => "DATETIME NULL",
            
            'discount_share'  => "DECIMAL(5,4) NOT NULL DEFAULT 0.0000",
            'avg_item_price'  => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
            'price_band'      => "TINYINT(3) UNSIGNED NOT NULL DEFAULT 0",
            
            'tnp_subscriber_id'        => "INT(10) UNSIGNED NOT NULL DEFAULT 0",
            'tnp_status'               => "CHAR(1) NOT NULL DEFAULT ''",
            'emails_sent_lifetime'     => "INT(10) UNSIGNED NOT NULL DEFAULT 0",
            'emails_opened_lifetime'   => "INT(10) UNSIGNED NOT NULL DEFAULT 0",
            'emails_clicked_lifetime'  => "INT(10) UNSIGNED NOT NULL DEFAULT 0",
            'open_rate_lifetime'       => "DECIMAL(5,4) NOT NULL DEFAULT 0.0000",
            'click_rate_lifetime'      => "DECIMAL(5,4) NOT NULL DEFAULT 0.0000",
            'emails_sent_30d'          => "SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0",
            'emails_opened_30d'        => "SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0",
            'open_rate_30d'            => "DECIMAL(5,4) NOT NULL DEFAULT 0.0000",
            'last_email_open_at'       => "DATETIME NULL",
            'last_email_click_at'      => "DATETIME NULL",
            'days_since_last_engagement' => "SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0",
            
            'taste_vector'              => "MEDIUMBLOB NULL",
            'taste_vector_updated_at'   => "DATETIME NULL",
            
            'dormancy_cohort'                => "VARCHAR(20) NOT NULL DEFAULT ''",
            'reactivation_score'             => "TINYINT(3) UNSIGNED NOT NULL DEFAULT 0",
            'last_reactivation_attempt_at'   => "DATETIME NULL",
            'reactivation_attempts_count'    => "SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0",
            'reactivated_at'                 => "DATETIME NULL",
            
            
            
            'sale_purchase_cnt'              => "INT(10) UNSIGNED NOT NULL DEFAULT 0",
            'new_product_purchase_cnt'       => "INT(10) UNSIGNED NOT NULL DEFAULT 0",
            'gift_completed_count'           => "INT(10) UNSIGNED NOT NULL DEFAULT 0",
            'monthly_offer_purchase_cnt'     => "INT(10) UNSIGNED NOT NULL DEFAULT 0",
        );
        foreach ($cols as $name => $def) {
            $exists = $wpdb->get_var("SHOW COLUMNS FROM {$tbl} LIKE '{$name}'");
            if (!$exists) {
                $wpdb->query("ALTER TABLE {$tbl} ADD COLUMN {$name} {$def}");
            }
        }
        
        $indexes = array(
            'idx_tnp_subscriber'       => 'tnp_subscriber_id',
            'idx_engagement_recency'   => 'days_since_last_engagement',
            'idx_dormancy_cohort'      => 'dormancy_cohort',
            'idx_reactivation_score'   => 'reactivation_score',
            'idx_inferred_top_priority'=> 'inferred_top_priority',
            
            'idx_sale_buyer'           => 'sale_purchase_cnt',
            'idx_new_buyer'            => 'new_product_purchase_cnt',
            'idx_gift_completer'       => 'gift_completed_count',
            'idx_monthly_responder'    => 'monthly_offer_purchase_cnt',
        );
        foreach ($indexes as $idx_name => $col) {
            $row = $wpdb->get_var("SHOW INDEX FROM {$tbl} WHERE Key_name = '{$idx_name}'");
            if (!$row) {
                $wpdb->query("ALTER TABLE {$tbl} ADD INDEX {$idx_name} ({$col})");
            }
        }
    }
}

if (!function_exists('aieo_push_create_subscriptions_table')) {
    
    function aieo_push_create_subscriptions_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_push_subscriptions';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            subscription_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            session_token varchar(64) NOT NULL DEFAULT '',
            endpoint_hash char(64) NOT NULL,
            endpoint text NOT NULL,
            p256dh varchar(255) NOT NULL DEFAULT '',
            auth_secret varchar(64) NOT NULL DEFAULT '',
            user_agent varchar(255) NOT NULL DEFAULT '',
            device_kind varchar(20) NOT NULL DEFAULT 'unknown',
            locale varchar(10) NOT NULL DEFAULT '',
            tz_offset_minutes smallint(6) NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'active',
            last_sent_at datetime NULL,
            last_clicked_at datetime NULL,
            subscribed_at datetime NOT NULL,
            bounced_at datetime NULL,
            identified_email varchar(190) NOT NULL DEFAULT '',
            identified_at datetime NULL,
            PRIMARY KEY  (subscription_id),
            UNIQUE KEY uniq_endpoint_hash (endpoint_hash),
            KEY idx_user_status (user_id, status),
            KEY idx_session_token (session_token),
            KEY idx_status_last_sent (status, last_sent_at)
        ) {$charset};";

        aieo_dbdelta($sql);

        
        aieo_push_ensure_subscription_columns();
    }
}

if (!function_exists('aieo_push_ensure_subscription_columns')) {
    
    function aieo_push_ensure_subscription_columns() {
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_push_subscriptions';
        if ((string) $wpdb->get_var("SHOW TABLES LIKE '{$tbl}'") !== $tbl) {
            return;
        }
        $cols = array(
            'identified_email' => "VARCHAR(190) NOT NULL DEFAULT ''",
            'identified_at'    => "DATETIME NULL",
        );
        foreach ($cols as $name => $def) {
            $exists = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM {$tbl} LIKE %s", $name));
            if (!$exists) {
                $wpdb->query("ALTER TABLE {$tbl} ADD COLUMN {$name} {$def}");
            }
        }
    }
}

if (!function_exists('aieo_push_create_dispatch_table')) {
    
    function aieo_push_create_dispatch_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_push_dispatch';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            dispatch_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            subscription_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            channel varchar(10) NOT NULL DEFAULT 'push',
            subject_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            recipient_email varchar(190) NOT NULL DEFAULT '',
            user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            trigger_id varchar(40) NOT NULL DEFAULT '',
            template_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            target_product_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            target_url varchar(500) NOT NULL DEFAULT '',
            payload_json longtext NULL,
            state varchar(20) NOT NULL DEFAULT 'queued',
            suppress_reason varchar(40) NOT NULL DEFAULT '',
            provider_status smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            provider_error varchar(200) NOT NULL DEFAULT '',
            queued_at datetime NULL,
            sent_at datetime NULL,
            clicked_at datetime NULL,
            dismissed_at datetime NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (dispatch_id),
            KEY idx_user_sent (user_id, sent_at),
            KEY idx_subscription_state (subscription_id, state),
            KEY idx_trigger_product_sent (trigger_id, target_product_id, sent_at),
            KEY idx_state_queued (state, queued_at)
        ) {$charset};";

        aieo_dbdelta($sql);
        aieo_push_ensure_dispatch_columns();
    }
}

if (!function_exists('aieo_push_ensure_dispatch_columns')) {
    
    function aieo_push_ensure_dispatch_columns() {
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_push_dispatch';
        if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl)) !== $tbl) {
            return;
        }
        $cols = array(
            'converted_order_id'     => "bigint(20) UNSIGNED NOT NULL DEFAULT 0",
            'converted_at'           => "datetime NULL",
            'revenue'                => "decimal(12,2) NOT NULL DEFAULT 0",
            'attribution_confidence' => "varchar(10) NOT NULL DEFAULT ''",
        );
        foreach ($cols as $name => $def) {
            
            $exists = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM `{$tbl}` LIKE %s", $name));
            if (!$exists) {
                
                $wpdb->query("ALTER TABLE `{$tbl}` ADD COLUMN `{$name}` {$def}");
            }
        }
        
        
        $indexes = array(
            'idx_template_sent' => '(template_id, sent_at)',
            'idx_sent_at'       => '(sent_at)',
            'idx_converted'     => '(converted_order_id)',
        );
        foreach ($indexes as $idx => $cols) {
            
            $have = $wpdb->get_var("SHOW INDEX FROM `{$tbl}` WHERE Key_name = '{$idx}'");
            if (!$have) {
                
                $wpdb->query("ALTER TABLE `{$tbl}` ADD INDEX `{$idx}` {$cols}");
            }
        }
    }
}

if (!function_exists('aieo_push_create_templates_table')) {
    
    function aieo_push_create_templates_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_push_templates';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            template_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            trigger_id varchar(40) NOT NULL DEFAULT '',
            channel varchar(10) NOT NULL DEFAULT 'push',
            segment_id varchar(40) NOT NULL DEFAULT '*',
            variant_label varchar(20) NOT NULL DEFAULT 'A',
            weight tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
            title_template varchar(120) NOT NULL DEFAULT '',
            subject_template varchar(190) NOT NULL DEFAULT '',
            body_template varchar(255) NOT NULL DEFAULT '',
            html_body longtext NULL,
            icon_url varchar(500) NOT NULL DEFAULT '',
            cta_label varchar(40) NOT NULL DEFAULT '',
            is_active tinyint(1) NOT NULL DEFAULT 1,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (template_id),
            KEY idx_trigger_segment_active (trigger_id, segment_id, is_active)
        ) {$charset};";

        aieo_dbdelta($sql);
    }
}

if (!function_exists('aieo_push_create_caps_table')) {
    
    function aieo_push_create_caps_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_push_caps';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            user_id bigint(20) UNSIGNED NOT NULL,
            window_kind varchar(8) NOT NULL,
            window_start date NOT NULL,
            sent_count smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            clicked_count smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (user_id, window_kind, window_start)
        ) {$charset};";

        aieo_dbdelta($sql);
    }
}

if (!function_exists('aieo_push_create_campaigns_table')) {
    
    function aieo_push_create_campaigns_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_push_campaigns';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            campaign_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name varchar(120) NOT NULL DEFAULT '',
            audience_filter_json longtext NULL,
            channels varchar(60) NOT NULL DEFAULT 'push',
            title_template varchar(120) NOT NULL DEFAULT '',
            body_template varchar(255) NOT NULL DEFAULT '',
            cta_label varchar(40) NOT NULL DEFAULT '',
            target_url varchar(500) NOT NULL DEFAULT '',
            status varchar(20) NOT NULL DEFAULT 'draft',
            audience_count int(10) UNSIGNED NOT NULL DEFAULT 0,
            sent_count int(10) UNSIGNED NOT NULL DEFAULT 0,
            failed_count int(10) UNSIGNED NOT NULL DEFAULT 0,
            scheduled_at datetime NULL,
            launched_at datetime NULL,
            completed_at datetime NULL,
            newsletter_provider varchar(40) NOT NULL DEFAULT '',
            newsletter_segment_id varchar(100) NOT NULL DEFAULT '',
            send_rate_per_minute smallint(5) UNSIGNED NOT NULL DEFAULT 200,
            created_by bigint(20) UNSIGNED 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  (campaign_id),
            KEY idx_status (status),
            KEY idx_scheduled (scheduled_at),
            KEY idx_created_at (created_at)
        ) {$charset};";

        aieo_dbdelta($sql);
    }
}

if (!function_exists('aieo_push_create_newsletter_sync_log_table')) {
    
    function aieo_push_create_newsletter_sync_log_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_newsletter_sync_log';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            sync_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            provider varchar(40) NOT NULL DEFAULT '',
            cohort varchar(40) NOT NULL DEFAULT '',
            audience_filter_json longtext NULL,
            audience_count int(10) UNSIGNED NOT NULL DEFAULT 0,
            synced_count int(10) UNSIGNED NOT NULL DEFAULT 0,
            failed_count int(10) UNSIGNED NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'pending',
            error_text varchar(500) NOT NULL DEFAULT '',
            provider_segment_id varchar(100) NOT NULL DEFAULT '',
            triggered_by bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            started_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            completed_at datetime NULL,
            PRIMARY KEY  (sync_id),
            KEY idx_provider_started (provider, started_at),
            KEY idx_cohort (cohort)
        ) {$charset};";

        aieo_dbdelta($sql);
    }
}