<?php
if (!defined('ABSPATH')) {
    exit;
}

class VMO_Promotion_Data_Store {
    private static $instance = null;
    private $wpdb;
    private $connected_accounts_table;
    private $campaigns_table;
    private $content_items_table;
    private $jobs_table;
    private $ad_jobs_table;
    private $approvals_table;
    private $reply_policies_table;
    private $metrics_table;
    private $audit_logs_table;

    public static function get_instance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }

        return self::$instance;
    }

    private function __construct() {
        global $wpdb;

        $this->wpdb = $wpdb;
        $prefix = $wpdb->prefix;
        $this->connected_accounts_table = $prefix . 'vmo_promotion_connected_accounts';
        $this->campaigns_table = $prefix . 'vmo_promotion_campaigns';
        $this->content_items_table = $prefix . 'vmo_promotion_content_items';
        $this->jobs_table = $prefix . 'vmo_distribution_jobs';
        $this->ad_jobs_table = $prefix . 'vmo_promotion_ad_jobs';
        $this->approvals_table = $prefix . 'vmo_promotion_approvals';
        $this->reply_policies_table = $prefix . 'vmo_reply_policies';
        $this->metrics_table = $prefix . 'vmo_promotion_metrics';
        $this->audit_logs_table = $prefix . 'vmo_promotion_audit_logs';

        add_action('init', array($this, 'ensure_schema'), 6);
    }

    public function ensure_schema() {
        if (!function_exists('dbDelta')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }

        $charset = $this->wpdb->get_charset_collate();

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->connected_accounts_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            platform_key VARCHAR(80) NOT NULL,
            platform_label VARCHAR(120) NOT NULL,
            external_account_id VARCHAR(191) DEFAULT '',
            display_name VARCHAR(191) DEFAULT '',
            auth_status VARCHAR(50) DEFAULT 'not_connected',
            granted_scopes_json LONGTEXT NULL,
            access_token_encrypted LONGTEXT NULL,
            refresh_token_encrypted LONGTEXT NULL,
            token_expires_at DATETIME NULL,
            refresh_status VARCHAR(50) DEFAULT 'not_applicable',
            app_review_status VARCHAR(50) DEFAULT 'admin_setup_required',
            publish_enabled TINYINT(1) DEFAULT 0,
            reply_enabled TINYINT(1) DEFAULT 0,
            metrics_enabled TINYINT(1) DEFAULT 0,
            connection_metadata_json LONGTEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_user_platform (user_id, platform_key),
            KEY idx_auth_status (auth_status)
        ) {$charset};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->campaigns_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            campaign_type VARCHAR(50) DEFAULT 'social_posting',
            title VARCHAR(191) DEFAULT '',
            objective LONGTEXT NULL,
            strategy_summary LONGTEXT NULL,
            ai_strategy_json LONGTEXT NULL,
            settings_json LONGTEXT NULL,
            queue_json LONGTEXT NULL,
            approval_mode VARCHAR(50) DEFAULT 'manual',
            budget_mode VARCHAR(50) DEFAULT 'guided',
            status VARCHAR(50) DEFAULT 'draft',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_user_type (user_id, campaign_type),
            KEY idx_status (status)
        ) {$charset};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->content_items_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            campaign_id BIGINT UNSIGNED NOT NULL,
            content_type VARCHAR(50) DEFAULT 'social_post',
            title VARCHAR(191) DEFAULT '',
            caption_master LONGTEXT NULL,
            platform_variants_json LONGTEXT NULL,
            media_bundle_id VARCHAR(191) DEFAULT '',
            media_bundle_json LONGTEXT NULL,
            cta_type VARCHAR(50) DEFAULT 'ai_recommend',
            cta_url TEXT NULL,
            checkout_url TEXT NULL,
            product_id BIGINT UNSIGNED DEFAULT 0,
            status VARCHAR(50) DEFAULT 'draft',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_campaign (campaign_id),
            KEY idx_status (status)
        ) {$charset};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->jobs_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            campaign_id BIGINT UNSIGNED DEFAULT 0,
            content_item_id BIGINT UNSIGNED DEFAULT 0,
            connected_account_id BIGINT UNSIGNED DEFAULT 0,
            platform_key VARCHAR(80) DEFAULT '',
            platform_label VARCHAR(120) DEFAULT '',
            scheduled_for DATETIME NULL,
            schedule_label VARCHAR(191) DEFAULT '',
            publish_status VARCHAR(50) DEFAULT 'planned',
            provider_post_id VARCHAR(191) DEFAULT '',
            provider_response_json LONGTEXT NULL,
            job_payload_json LONGTEXT NULL,
            retry_count INT UNSIGNED DEFAULT 0,
            last_error_code VARCHAR(100) DEFAULT '',
            last_error_message TEXT NULL,
            published_at DATETIME NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_campaign_platform (campaign_id, platform_key),
            KEY idx_content_item (content_item_id),
            KEY idx_publish_status (publish_status)
        ) {$charset};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->ad_jobs_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            campaign_id BIGINT UNSIGNED DEFAULT 0,
            connected_account_id BIGINT UNSIGNED DEFAULT 0,
            platform_key VARCHAR(80) DEFAULT '',
            platform_label VARCHAR(120) DEFAULT '',
            ad_product VARCHAR(80) DEFAULT 'search_ads',
            objective VARCHAR(120) DEFAULT '',
            ad_account_id VARCHAR(191) DEFAULT '',
            budget_amount DECIMAL(12,2) DEFAULT 0.00,
            currency VARCHAR(20) DEFAULT 'USD',
            schedule_start DATETIME NULL,
            schedule_end DATETIME NULL,
            job_status VARCHAR(50) DEFAULT 'planned',
            provider_campaign_id VARCHAR(191) DEFAULT '',
            creative_payload_json LONGTEXT NULL,
            job_payload_json LONGTEXT NULL,
            provider_response_json LONGTEXT NULL,
            retry_count INT UNSIGNED DEFAULT 0,
            last_error_code VARCHAR(100) DEFAULT '',
            last_error_message TEXT NULL,
            launched_at DATETIME NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_user_platform (user_id, platform_key),
            KEY idx_job_status (job_status),
            KEY idx_schedule_start (schedule_start)
        ) {$charset};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->approvals_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            campaign_id BIGINT UNSIGNED DEFAULT 0,
            content_item_id BIGINT UNSIGNED DEFAULT 0,
            approval_type VARCHAR(50) DEFAULT 'publish',
            status VARCHAR(50) DEFAULT 'pending',
            reviewer_user_id BIGINT UNSIGNED DEFAULT 0,
            notes LONGTEXT NULL,
            approved_at DATETIME NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_campaign_status (campaign_id, status),
            KEY idx_content_item (content_item_id)
        ) {$charset};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->reply_policies_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            campaign_id BIGINT UNSIGNED DEFAULT 0,
            auto_reply_enabled TINYINT(1) DEFAULT 0,
            allowed_platforms_json LONGTEXT NULL,
            tone_profile VARCHAR(120) DEFAULT '',
            escalation_rules_json LONGTEXT NULL,
            restricted_topics_json LONGTEXT NULL,
            approval_mode VARCHAR(50) DEFAULT 'manual',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_user_campaign (user_id, campaign_id)
        ) {$charset};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->metrics_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            content_item_id BIGINT UNSIGNED NOT NULL,
            platform_key VARCHAR(80) DEFAULT '',
            platform_label VARCHAR(120) DEFAULT '',
            impressions BIGINT UNSIGNED DEFAULT 0,
            reach BIGINT UNSIGNED DEFAULT 0,
            clicks BIGINT UNSIGNED DEFAULT 0,
            comments BIGINT UNSIGNED DEFAULT 0,
            shares BIGINT UNSIGNED DEFAULT 0,
            saves BIGINT UNSIGNED DEFAULT 0,
            ctr DECIMAL(10,4) DEFAULT 0.0000,
            engagement_rate DECIMAL(10,4) DEFAULT 0.0000,
            snapshot_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_content_platform (content_item_id, platform_key),
            KEY idx_snapshot_time (snapshot_time)
        ) {$charset};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$this->audit_logs_table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED DEFAULT 0,
            campaign_id BIGINT UNSIGNED DEFAULT 0,
            content_item_id BIGINT UNSIGNED DEFAULT 0,
            job_id BIGINT UNSIGNED DEFAULT 0,
            platform_key VARCHAR(80) DEFAULT '',
            event_type VARCHAR(80) DEFAULT '',
            message TEXT NULL,
            details_json LONGTEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_user_event (user_id, event_type),
            KEY idx_job_event (job_id, event_type),
            KEY idx_platform_event (platform_key, event_type)
        ) {$charset};");
    }

    public function get_dashboard_state($user_id, array $defaults, array $default_queue) {
        $campaign = $this->get_primary_campaign($user_id, 'social_posting');
        if (!$campaign) {
            $legacy_settings = get_user_meta($user_id, 'vmo_promotion_settings', true);
            $legacy_queue = get_user_meta($user_id, 'vmo_promotion_queue', true);
            if (is_array($legacy_settings) || is_array($legacy_queue)) {
                return $this->save_dashboard_state(
                    $user_id,
                    is_array($legacy_settings) ? $legacy_settings : $defaults,
                    is_array($legacy_queue) ? $legacy_queue : $default_queue
                );
            }

            $campaign = $this->upsert_primary_campaign($user_id, 'social_posting', $defaults, $default_queue);
        }

        $settings = json_decode((string) ($campaign['settings_json'] ?? ''), true);
        $queue = json_decode((string) ($campaign['queue_json'] ?? ''), true);

        $settings = is_array($settings) ? array_merge($defaults, $settings) : $defaults;
        $queue = is_array($queue) ? $queue : $default_queue;

        if (function_exists('vmo_promotion_normalize_settings')) {
            $settings = vmo_promotion_normalize_settings($settings);
        }
        if (function_exists('vmo_promotion_normalize_queue')) {
            $queue = vmo_promotion_normalize_queue($queue);
        }

        if (empty($settings['latest_posts'])) {
            $settings['latest_posts'] = $this->load_content_items_for_settings((int) $campaign['id']);
        }

        return array(
            'campaign' => $campaign,
            'settings' => $settings,
            'queue' => $queue,
            'connected_accounts' => $this->get_connected_accounts($user_id),
            'reply_policy' => $this->get_reply_policy($user_id, (int) $campaign['id']),
        );
    }

    public function save_dashboard_state($user_id, array $settings, array $queue) {
        if (function_exists('vmo_promotion_normalize_settings')) {
            $settings = vmo_promotion_normalize_settings($settings);
        }
        if (function_exists('vmo_promotion_normalize_queue')) {
            $queue = vmo_promotion_normalize_queue($queue);
        }

        $campaign = $this->upsert_primary_campaign($user_id, 'social_posting', $settings, $queue);
        $campaign_id = (int) $campaign['id'];
        $content_map = $this->replace_content_items($campaign_id, $settings);
        $this->replace_distribution_jobs($campaign_id, $user_id, $queue, (array) ($settings['selected_platforms'] ?? array()), $content_map);
        $this->upsert_reply_policy($user_id, $campaign_id, $settings);

        update_user_meta($user_id, 'vmo_promotion_settings', $settings);
        update_user_meta($user_id, 'vmo_promotion_queue', $queue);

        return array(
            'campaign' => $this->get_primary_campaign($user_id, 'social_posting'),
            'settings' => $settings,
            'queue' => $queue,
            'connected_accounts' => $this->get_connected_accounts($user_id),
            'reply_policy' => $this->get_reply_policy($user_id, $campaign_id),
        );
    }

    public function get_connected_accounts($user_id) {
        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->connected_accounts_table} WHERE user_id = %d ORDER BY updated_at DESC, id DESC",
                (int) $user_id
            ),
            ARRAY_A
        );

        if (!is_array($rows)) {
            return array();
        }

        return array_map(function($row) {
            return $this->normalize_connected_account_row($row);
        }, $rows);
    }

    public function get_connected_account_by_id($account_id) {
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->connected_accounts_table} WHERE id = %d LIMIT 1",
                (int) $account_id
            ),
            ARRAY_A
        );

        if (!is_array($row)) {
            return array();
        }

        return $this->normalize_connected_account_row($row);
    }

    public function get_connected_account_auth_rows($user_id, $platform_key = '') {
        $user_id = (int) $user_id;
        if ($user_id <= 0) {
            return array();
        }

        if ($platform_key !== '') {
            $rows = $this->wpdb->get_results(
                $this->wpdb->prepare(
                    "SELECT * FROM {$this->connected_accounts_table} WHERE user_id = %d AND platform_key = %s ORDER BY updated_at DESC, id DESC",
                    $user_id,
                    sanitize_key($platform_key)
                ),
                ARRAY_A
            );
        } else {
            $rows = $this->wpdb->get_results(
                $this->wpdb->prepare(
                    "SELECT * FROM {$this->connected_accounts_table} WHERE user_id = %d ORDER BY updated_at DESC, id DESC",
                    $user_id
                ),
                ARRAY_A
            );
        }

        if (!is_array($rows)) {
            return array();
        }

        return array_map(function($row) {
            return $this->normalize_connected_account_row($row, true);
        }, $rows);
    }

    public function upsert_connected_account($user_id, array $account) {
        $user_id = (int) $user_id;
        $platform_key = sanitize_key($account['platform_key'] ?? '');
        if ($user_id <= 0 || $platform_key === '') {
            return array();
        }

        $external_account_id = sanitize_text_field($account['external_account_id'] ?? '');
        if ($external_account_id !== '') {
            $existing = $this->wpdb->get_row(
                $this->wpdb->prepare(
                    "SELECT id FROM {$this->connected_accounts_table} WHERE user_id = %d AND platform_key = %s AND external_account_id = %s LIMIT 1",
                    $user_id,
                    $platform_key,
                    $external_account_id
                ),
                ARRAY_A
            );
        } else {
            $existing = $this->wpdb->get_row(
                $this->wpdb->prepare(
                    "SELECT id FROM {$this->connected_accounts_table} WHERE user_id = %d AND platform_key = %s ORDER BY id DESC LIMIT 1",
                    $user_id,
                    $platform_key
                ),
                ARRAY_A
            );
        }

        $data = array(
            'user_id' => $user_id,
            'platform_key' => $platform_key,
            'platform_label' => sanitize_text_field($account['platform_label'] ?? ucwords(str_replace('_', ' ', $platform_key))),
            'external_account_id' => $external_account_id,
            'display_name' => sanitize_text_field($account['display_name'] ?? ''),
            'auth_status' => sanitize_key($account['auth_status'] ?? 'connected'),
            'granted_scopes_json' => wp_json_encode(array_values((array) ($account['granted_scopes'] ?? array()))),
            'access_token_encrypted' => (string) ($account['access_token'] ?? ''),
            'refresh_token_encrypted' => (string) ($account['refresh_token'] ?? ''),
            'token_expires_at' => sanitize_text_field($account['token_expires_at'] ?? ''),
            'refresh_status' => sanitize_key($account['refresh_status'] ?? 'available'),
            'app_review_status' => sanitize_key($account['app_review_status'] ?? 'approved'),
            'publish_enabled' => !empty($account['publish_enabled']) ? 1 : 0,
            'reply_enabled' => !empty($account['reply_enabled']) ? 1 : 0,
            'metrics_enabled' => !empty($account['metrics_enabled']) ? 1 : 0,
            'connection_metadata_json' => wp_json_encode((array) ($account['connection_metadata'] ?? array())),
            'updated_at' => current_time('mysql'),
        );

        if ($existing && !empty($existing['id'])) {
            $this->wpdb->update(
                $this->connected_accounts_table,
                $data,
                array('id' => (int) $existing['id']),
                array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s', '%s'),
                array('%d')
            );
            $account_id = (int) $existing['id'];
        } else {
            $data['created_at'] = current_time('mysql');
            $this->wpdb->insert(
                $this->connected_accounts_table,
                $data,
                array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s')
            );
            $account_id = (int) $this->wpdb->insert_id;
        }

        return $this->get_connected_account_by_id($account_id);
    }

    public function get_due_jobs($limit = 10) {
        $limit = max(1, min(100, (int) $limit));
        $now = current_time('mysql');
        $sql = $this->wpdb->prepare(
            "SELECT j.*, c.user_id
             FROM {$this->jobs_table} j
             LEFT JOIN {$this->campaigns_table} c ON c.id = j.campaign_id
             WHERE j.publish_status IN ('planned', 'scheduled')
               AND (j.scheduled_for IS NULL OR j.scheduled_for <= %s)
             ORDER BY COALESCE(j.scheduled_for, j.created_at) ASC
             LIMIT %d",
            $now,
            $limit
        );

        $rows = $this->wpdb->get_results($sql, ARRAY_A);
        return is_array($rows) ? $rows : array();
    }

    public function queue_ad_job($user_id, array $job) {
        $user_id = (int) $user_id;
        if ($user_id <= 0) {
            return array();
        }

        $data = array(
            'user_id' => $user_id,
            'campaign_id' => (int) ($job['campaign_id'] ?? 0),
            'connected_account_id' => (int) ($job['connected_account_id'] ?? 0),
            'platform_key' => sanitize_key($job['platform_key'] ?? ''),
            'platform_label' => sanitize_text_field($job['platform_label'] ?? ''),
            'ad_product' => sanitize_key($job['ad_product'] ?? 'search_ads'),
            'objective' => sanitize_text_field($job['objective'] ?? ''),
            'ad_account_id' => sanitize_text_field($job['ad_account_id'] ?? ''),
            'budget_amount' => round((float) ($job['budget_amount'] ?? 0), 2),
            'currency' => strtoupper(sanitize_text_field($job['currency'] ?? 'USD')),
            'schedule_start' => !empty($job['schedule_start']) ? sanitize_text_field($job['schedule_start']) : null,
            'schedule_end' => !empty($job['schedule_end']) ? sanitize_text_field($job['schedule_end']) : null,
            'job_status' => sanitize_key($job['job_status'] ?? 'planned'),
            'provider_campaign_id' => sanitize_text_field($job['provider_campaign_id'] ?? ''),
            'creative_payload_json' => wp_json_encode((array) ($job['creative_payload'] ?? array())),
            'job_payload_json' => wp_json_encode((array) ($job['job_payload'] ?? array())),
            'provider_response_json' => wp_json_encode((array) ($job['provider_response'] ?? array())),
            'retry_count' => max(0, (int) ($job['retry_count'] ?? 0)),
            'last_error_code' => sanitize_key($job['last_error_code'] ?? ''),
            'last_error_message' => sanitize_textarea_field($job['last_error_message'] ?? ''),
            'launched_at' => !empty($job['launched_at']) ? sanitize_text_field($job['launched_at']) : null,
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql'),
        );

        $this->wpdb->insert(
            $this->ad_jobs_table,
            $data,
            array('%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s')
        );

        return $this->get_ad_job((int) $this->wpdb->insert_id);
    }

    public function get_due_ad_jobs($limit = 10) {
        $limit = max(1, min(100, (int) $limit));
        $now = current_time('mysql');
        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->ad_jobs_table}
                 WHERE job_status IN ('planned', 'scheduled')
                   AND (schedule_start IS NULL OR schedule_start <= %s)
                 ORDER BY COALESCE(schedule_start, created_at) ASC
                 LIMIT %d",
                $now,
                $limit
            ),
            ARRAY_A
        );

        if (!is_array($rows)) {
            return array();
        }

        return array_map(array($this, 'normalize_ad_job_row'), $rows);
    }

    public function get_ad_job($job_id) {
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->ad_jobs_table} WHERE id = %d LIMIT 1",
                (int) $job_id
            ),
            ARRAY_A
        );

        return is_array($row) ? $this->normalize_ad_job_row($row) : array();
    }

    public function get_content_item($content_item_id) {
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->content_items_table} WHERE id = %d LIMIT 1",
                (int) $content_item_id
            ),
            ARRAY_A
        );

        if (!is_array($row)) {
            return array();
        }

        return array(
            'id' => (int) ($row['id'] ?? 0),
            'campaign_id' => (int) ($row['campaign_id'] ?? 0),
            'content_type' => sanitize_key($row['content_type'] ?? 'social_post'),
            'title' => sanitize_text_field($row['title'] ?? ''),
            'caption_master' => sanitize_textarea_field($row['caption_master'] ?? ''),
            'platform_variants' => json_decode((string) ($row['platform_variants_json'] ?? '[]'), true) ?: array(),
            'media_bundle_id' => sanitize_text_field($row['media_bundle_id'] ?? ''),
            'media_bundle' => json_decode((string) ($row['media_bundle_json'] ?? '[]'), true) ?: array(),
            'cta_type' => sanitize_key($row['cta_type'] ?? 'ai_recommend'),
            'cta_url' => esc_url_raw($row['cta_url'] ?? ''),
            'checkout_url' => esc_url_raw($row['checkout_url'] ?? ''),
            'product_id' => (int) ($row['product_id'] ?? 0),
            'status' => sanitize_key($row['status'] ?? 'draft'),
        );
    }

    public function mark_job_processing($job_id) {
        $this->wpdb->update(
            $this->jobs_table,
            array(
                'publish_status' => 'processing',
                'updated_at' => current_time('mysql'),
            ),
            array('id' => (int) $job_id),
            array('%s', '%s'),
            array('%d')
        );
    }

    public function mark_ad_job_processing($job_id) {
        $this->wpdb->update(
            $this->ad_jobs_table,
            array(
                'job_status' => 'processing',
                'updated_at' => current_time('mysql'),
            ),
            array('id' => (int) $job_id),
            array('%s', '%s'),
            array('%d')
        );
    }

    public function mark_job_result($job_id, array $result) {
        $data = array(
            'publish_status' => sanitize_key($result['publish_status'] ?? 'failed'),
            'provider_post_id' => sanitize_text_field($result['provider_post_id'] ?? ''),
            'provider_response_json' => wp_json_encode($result['provider_response_json'] ?? array()),
            'last_error_code' => sanitize_key($result['last_error_code'] ?? ''),
            'last_error_message' => sanitize_textarea_field($result['last_error_message'] ?? ''),
            'updated_at' => current_time('mysql'),
        );

        if (!empty($result['published_at'])) {
            $data['published_at'] = sanitize_text_field($result['published_at']);
        }

        $this->wpdb->update(
            $this->jobs_table,
            $data,
            array('id' => (int) $job_id),
            array_fill(0, count($data), '%s'),
            array('%d')
        );
    }

    public function mark_ad_job_result($job_id, array $result) {
        $data = array(
            'job_status' => sanitize_key($result['job_status'] ?? 'failed'),
            'provider_campaign_id' => sanitize_text_field($result['provider_campaign_id'] ?? ''),
            'provider_response_json' => wp_json_encode($result['provider_response_json'] ?? array()),
            'last_error_code' => sanitize_key($result['last_error_code'] ?? ''),
            'last_error_message' => sanitize_textarea_field($result['last_error_message'] ?? ''),
            'updated_at' => current_time('mysql'),
        );

        if (!empty($result['launched_at'])) {
            $data['launched_at'] = sanitize_text_field($result['launched_at']);
        }

        $this->wpdb->update(
            $this->ad_jobs_table,
            $data,
            array('id' => (int) $job_id),
            array_fill(0, count($data), '%s'),
            array('%d')
        );
    }

    public function record_metric_snapshot($content_item_id, $platform_key, $platform_label, array $metrics) {
        $this->wpdb->insert(
            $this->metrics_table,
            array(
                'content_item_id' => (int) $content_item_id,
                'platform_key' => sanitize_key($platform_key),
                'platform_label' => sanitize_text_field($platform_label),
                'impressions' => max(0, (int) ($metrics['impressions'] ?? 0)),
                'reach' => max(0, (int) ($metrics['reach'] ?? 0)),
                'clicks' => max(0, (int) ($metrics['clicks'] ?? 0)),
                'comments' => max(0, (int) ($metrics['comments'] ?? 0)),
                'shares' => max(0, (int) ($metrics['shares'] ?? 0)),
                'saves' => max(0, (int) ($metrics['saves'] ?? 0)),
                'ctr' => (float) ($metrics['ctr'] ?? 0),
                'engagement_rate' => (float) ($metrics['engagement_rate'] ?? 0),
                'snapshot_time' => sanitize_text_field($metrics['snapshot_time'] ?? current_time('mysql')),
                'created_at' => current_time('mysql'),
            ),
            array('%d', '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%d', '%f', '%f', '%s', '%s')
        );
    }

    public function log_event($user_id, $event_type, $message, array $details = array()) {
        $this->wpdb->insert(
            $this->audit_logs_table,
            array(
                'user_id' => (int) $user_id,
                'campaign_id' => (int) ($details['campaign_id'] ?? 0),
                'content_item_id' => (int) ($details['content_item_id'] ?? 0),
                'job_id' => (int) ($details['job_id'] ?? 0),
                'platform_key' => sanitize_key($details['platform_key'] ?? ''),
                'event_type' => sanitize_key($event_type),
                'message' => sanitize_textarea_field($message),
                'details_json' => wp_json_encode($details),
                'created_at' => current_time('mysql'),
            ),
            array('%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s')
        );
    }

    private function upsert_primary_campaign($user_id, $campaign_type, array $settings, array $queue) {
        $existing = $this->get_primary_campaign($user_id, $campaign_type);
        $strategy = is_array($settings['latest_strategy'] ?? null) ? $settings['latest_strategy'] : array();
        $strategy_summary = '';
        if (!empty($strategy[0]['text'])) {
            $strategy_summary = sanitize_textarea_field($strategy[0]['text']);
        } elseif (!empty($settings['goal'])) {
            $strategy_summary = sanitize_textarea_field($settings['goal']);
        }

        $data = array(
            'user_id' => (int) $user_id,
            'campaign_type' => sanitize_key($campaign_type),
            'title' => 'Social Media Posting Workspace',
            'objective' => sanitize_textarea_field($settings['goal'] ?? ''),
            'strategy_summary' => $strategy_summary,
            'ai_strategy_json' => wp_json_encode($strategy),
            'settings_json' => wp_json_encode($settings),
            'queue_json' => wp_json_encode($queue),
            'approval_mode' => !empty($settings['auto_post']) ? 'hybrid' : 'manual',
            'budget_mode' => 'guided',
            'status' => !empty($settings['latest_posts']) ? 'draft_ready' : 'draft',
            'updated_at' => current_time('mysql'),
        );

        if ($existing) {
            $this->wpdb->update(
                $this->campaigns_table,
                $data,
                array('id' => (int) $existing['id']),
                array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'),
                array('%d')
            );
        } else {
            $data['created_at'] = current_time('mysql');
            $this->wpdb->insert(
                $this->campaigns_table,
                $data,
                array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
            );
        }

        return $this->get_primary_campaign($user_id, $campaign_type);
    }

    private function get_primary_campaign($user_id, $campaign_type) {
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->campaigns_table} WHERE user_id = %d AND campaign_type = %s ORDER BY updated_at DESC, id DESC LIMIT 1",
                (int) $user_id,
                sanitize_key($campaign_type)
            ),
            ARRAY_A
        );

        return is_array($row) ? $row : array();
    }

    private function replace_content_items($campaign_id, array $settings) {
        $this->wpdb->delete($this->content_items_table, array('campaign_id' => (int) $campaign_id), array('%d'));

        $items = array();
        foreach ((array) ($settings['latest_posts'] ?? array()) as $index => $item) {
            $title = sanitize_text_field($item['title'] ?? '');
            if ($title === '') {
                $title = 'Draft Post ' . ($index + 1);
            }

            $this->wpdb->insert(
                $this->content_items_table,
                array(
                    'campaign_id' => (int) $campaign_id,
                    'content_type' => 'social_post',
                    'title' => $title,
                    'caption_master' => sanitize_textarea_field($item['caption'] ?? ''),
                    'platform_variants_json' => wp_json_encode((array) ($item['platform_variants'] ?? array())),
                    'media_bundle_id' => sanitize_text_field($item['media_bundle_id'] ?? ''),
                    'media_bundle_json' => wp_json_encode(array(
                        'asset_hint' => sanitize_text_field($item['asset_hint'] ?? ''),
                        'manual_assets' => array_values((array) ($settings['manual_assets'] ?? array())),
                    )),
                    'cta_type' => sanitize_key($settings['cta_destination'] ?? 'ai_recommend'),
                    'cta_url' => esc_url_raw($item['cta_url'] ?? ''),
                    'checkout_url' => esc_url_raw($item['checkout_url'] ?? ''),
                    'product_id' => (int) ($item['product_id'] ?? 0),
                    'status' => 'draft',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ),
                array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s')
            );

            $items[$this->normalize_title($title)] = (int) $this->wpdb->insert_id;
        }

        return $items;
    }

    private function replace_distribution_jobs($campaign_id, $user_id, array $queue, array $selected_platforms, array $content_map) {
        $this->wpdb->delete($this->jobs_table, array('campaign_id' => (int) $campaign_id), array('%d'));

        $registry = class_exists('VMO_Promotion_Provider_Registry') ? VMO_Promotion_Provider_Registry::get_instance() : null;
        $accounts = $this->get_connected_accounts($user_id);

        foreach ($queue as $queue_item) {
            $title = sanitize_text_field($queue_item['title'] ?? '');
            if ($title === '') {
                continue;
            }

            $content_item_id = $content_map[$this->normalize_title($title)] ?? 0;
            $status = sanitize_text_field($queue_item['status'] ?? 'Draft');
            $schedule_label = sanitize_text_field($queue_item['time'] ?? 'Pending schedule');

            foreach ($selected_platforms as $platform_label) {
                $platform_label = sanitize_text_field($platform_label);
                if ($platform_label === '') {
                    continue;
                }

                $platform_key = $registry ? $registry->normalize_platform_key($platform_label) : sanitize_key($platform_label);
                $account_id = 0;
                foreach ($accounts as $account) {
                    if (($account['platform_key'] ?? '') === $platform_key) {
                        $account_id = (int) ($account['id'] ?? 0);
                        break;
                    }
                }

                $this->wpdb->insert(
                    $this->jobs_table,
                    array(
                        'campaign_id' => (int) $campaign_id,
                        'content_item_id' => (int) $content_item_id,
                        'connected_account_id' => (int) $account_id,
                        'platform_key' => $platform_key,
                        'platform_label' => $platform_label,
                        'schedule_label' => $schedule_label,
                        'publish_status' => $this->map_queue_status($status),
                        'provider_response_json' => null,
                        'job_payload_json' => wp_json_encode($queue_item),
                        'retry_count' => 0,
                        'last_error_code' => '',
                        'last_error_message' => '',
                        'created_at' => current_time('mysql'),
                        'updated_at' => current_time('mysql'),
                    ),
                    array('%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s')
                );
            }
        }
    }

    private function upsert_reply_policy($user_id, $campaign_id, array $settings) {
        $existing = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT id FROM {$this->reply_policies_table} WHERE user_id = %d AND campaign_id = %d ORDER BY id DESC LIMIT 1",
                (int) $user_id,
                (int) $campaign_id
            ),
            ARRAY_A
        );

        $data = array(
            'user_id' => (int) $user_id,
            'campaign_id' => (int) $campaign_id,
            'auto_reply_enabled' => !empty($settings['reply_mode']) ? 1 : 0,
            'allowed_platforms_json' => wp_json_encode(array_values((array) ($settings['selected_platforms'] ?? array()))),
            'tone_profile' => '',
            'escalation_rules_json' => wp_json_encode(array(
                'confidence_threshold' => 0.85,
                'approval_required' => true,
                'business_hours_only' => true,
            )),
            'restricted_topics_json' => wp_json_encode(array()),
            'approval_mode' => 'manual',
            'updated_at' => current_time('mysql'),
        );

        if ($existing) {
            $this->wpdb->update(
                $this->reply_policies_table,
                $data,
                array('id' => (int) $existing['id']),
                array('%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s'),
                array('%d')
            );
        } else {
            $data['created_at'] = current_time('mysql');
            $this->wpdb->insert(
                $this->reply_policies_table,
                $data,
                array('%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
            );
        }
    }

    private function get_reply_policy($user_id, $campaign_id) {
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->reply_policies_table} WHERE user_id = %d AND campaign_id = %d ORDER BY id DESC LIMIT 1",
                (int) $user_id,
                (int) $campaign_id
            ),
            ARRAY_A
        );

        if (!is_array($row)) {
            return array();
        }

        return array(
            'id' => (int) ($row['id'] ?? 0),
            'auto_reply_enabled' => !empty($row['auto_reply_enabled']),
            'allowed_platforms' => json_decode((string) ($row['allowed_platforms_json'] ?? '[]'), true) ?: array(),
            'tone_profile' => sanitize_text_field($row['tone_profile'] ?? ''),
            'approval_mode' => sanitize_key($row['approval_mode'] ?? 'manual'),
        );
    }

    private function load_content_items_for_settings($campaign_id) {
        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->content_items_table} WHERE campaign_id = %d ORDER BY id ASC",
                (int) $campaign_id
            ),
            ARRAY_A
        );

        if (!is_array($rows)) {
            return array();
        }

        $items = array();
        foreach ($rows as $row) {
            $media_bundle = json_decode((string) ($row['media_bundle_json'] ?? ''), true);
            $items[] = array(
                'title' => sanitize_text_field($row['title'] ?? ''),
                'caption' => sanitize_textarea_field($row['caption_master'] ?? ''),
                'cta' => '',
                'asset_hint' => sanitize_text_field($media_bundle['asset_hint'] ?? ''),
            );
        }

        return $items;
    }

    private function normalize_ad_job_row(array $row) {
        return array(
            'id' => (int) ($row['id'] ?? 0),
            'user_id' => (int) ($row['user_id'] ?? 0),
            'campaign_id' => (int) ($row['campaign_id'] ?? 0),
            'connected_account_id' => (int) ($row['connected_account_id'] ?? 0),
            'platform_key' => sanitize_key($row['platform_key'] ?? ''),
            'platform_label' => sanitize_text_field($row['platform_label'] ?? ''),
            'ad_product' => sanitize_key($row['ad_product'] ?? 'search_ads'),
            'objective' => sanitize_text_field($row['objective'] ?? ''),
            'ad_account_id' => sanitize_text_field($row['ad_account_id'] ?? ''),
            'budget_amount' => (float) ($row['budget_amount'] ?? 0),
            'currency' => strtoupper(sanitize_text_field($row['currency'] ?? 'USD')),
            'schedule_start' => sanitize_text_field($row['schedule_start'] ?? ''),
            'schedule_end' => sanitize_text_field($row['schedule_end'] ?? ''),
            'job_status' => sanitize_key($row['job_status'] ?? 'planned'),
            'provider_campaign_id' => sanitize_text_field($row['provider_campaign_id'] ?? ''),
            'creative_payload' => json_decode((string) ($row['creative_payload_json'] ?? '[]'), true) ?: array(),
            'job_payload' => json_decode((string) ($row['job_payload_json'] ?? '[]'), true) ?: array(),
            'provider_response' => json_decode((string) ($row['provider_response_json'] ?? '[]'), true) ?: array(),
            'retry_count' => (int) ($row['retry_count'] ?? 0),
            'last_error_code' => sanitize_key($row['last_error_code'] ?? ''),
            'last_error_message' => sanitize_textarea_field($row['last_error_message'] ?? ''),
            'launched_at' => sanitize_text_field($row['launched_at'] ?? ''),
            'created_at' => sanitize_text_field($row['created_at'] ?? ''),
            'updated_at' => sanitize_text_field($row['updated_at'] ?? ''),
        );
    }

    private function normalize_connected_account_row(array $row, $include_tokens = false) {
        $normalized = array(
            'id' => (int) ($row['id'] ?? 0),
            'platform_key' => sanitize_key($row['platform_key'] ?? ''),
            'platform_label' => sanitize_text_field($row['platform_label'] ?? ''),
            'display_name' => sanitize_text_field($row['display_name'] ?? ''),
            'external_account_id' => sanitize_text_field($row['external_account_id'] ?? ''),
            'auth_status' => sanitize_key($row['auth_status'] ?? 'not_connected'),
            'refresh_status' => sanitize_key($row['refresh_status'] ?? 'not_applicable'),
            'app_review_status' => sanitize_key($row['app_review_status'] ?? 'admin_setup_required'),
            'publish_enabled' => !empty($row['publish_enabled']),
            'reply_enabled' => !empty($row['reply_enabled']),
            'metrics_enabled' => !empty($row['metrics_enabled']),
            'token_expires_at' => sanitize_text_field($row['token_expires_at'] ?? ''),
            'granted_scopes' => json_decode((string) ($row['granted_scopes_json'] ?? '[]'), true) ?: array(),
            'connection_metadata' => json_decode((string) ($row['connection_metadata_json'] ?? '[]'), true) ?: array(),
        );

        if ($include_tokens) {
            $normalized['access_token'] = (string) ($row['access_token_encrypted'] ?? '');
            $normalized['refresh_token'] = (string) ($row['refresh_token_encrypted'] ?? '');
        }

        return $normalized;
    }

    private function normalize_title($value) {
        $value = strtolower(trim((string) $value));
        $value = preg_replace('/\s+/', ' ', $value);
        return $value;
    }

    private function map_queue_status($status) {
        $status = sanitize_key(strtolower(str_replace(' ', '_', (string) $status)));
        $map = array(
            'awaiting_approval' => 'awaiting_approval',
            'scheduled' => 'scheduled',
            'draft' => 'draft',
        );

        return $map[$status] ?? 'planned';
    }
}
