<?php

defined('ABSPATH') || exit;

class ShopCentral_DB
{
    /**
     * Current schema version. Bump this when you need to apply a migration.
     */
    const SCHEMA_VERSION = '3.5.0';

    public static function init()
    {
        self::maybe_upgrade();
    }

    public static function create_tables()
    {
        global $wpdb;

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

        $charset_collate = $wpdb->get_charset_collate();

        $table_stores = $wpdb->prefix . 'shopcentral_stores';
        $sql_stores = "CREATE TABLE $table_stores (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            url varchar(255) NOT NULL,
            consumer_key varchar(255) NOT NULL,
            consumer_secret varchar(255) NOT NULL,
            is_current tinyint(1) NOT NULL DEFAULT 0,
            maintenance_mode tinyint(1) DEFAULT 0,
            order_import_enabled tinyint(1) NOT NULL DEFAULT 0,
            customer_sync_enabled tinyint(1) NOT NULL DEFAULT 1,
            last_order_sync datetime DEFAULT NULL,
            sync_progress text DEFAULT NULL,
            shared_secret varchar(64) DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id)
        ) $charset_collate;";
        dbDelta($sql_stores);

        $table_logs = $wpdb->prefix . 'shopcentral_logs';
        $sql_logs = "CREATE TABLE $table_logs (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            feature varchar(100) NOT NULL DEFAULT '',
            level varchar(20) NOT NULL DEFAULT 'info',
            message text NOT NULL,
            context text DEFAULT NULL,
            store_id bigint(20) UNSIGNED DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY idx_feature (feature),
            KEY idx_level (level),
            KEY idx_created_at (created_at),
            KEY idx_store_id (store_id)
        ) $charset_collate;";
        dbDelta($sql_logs);

        // Smart Creator Logs table
        $table_creator_logs = $wpdb->prefix . 'smart_creator_logs';
        $sql_creator_logs = "CREATE TABLE $table_creator_logs (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            store_id bigint(20) UNSIGNED NOT NULL,
            store_name varchar(255) NOT NULL,
            product_data longtext NOT NULL,
            variations longtext NOT NULL,
            adjustment decimal(10,2) NOT NULL DEFAULT 0,
            error text DEFAULT NULL,
            status varchar(20) NOT NULL DEFAULT 'failed',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            retry_at datetime DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY idx_status (status),
            KEY idx_store_id (store_id)
        ) $charset_collate;";
        dbDelta($sql_creator_logs);
    }

    /**
     * Check stored schema version and run incremental migrations when needed.
     * Hooks into admin_init so upgrade runs when plugin is updated
     * without re-activation.
     */
    public static function maybe_upgrade()
    {
        $installed = get_option('shopcentral_schema_version', '0');

        if (version_compare($installed, self::SCHEMA_VERSION, '>=')) {
            return;
        }

        global $wpdb;

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

        $charset_collate = $wpdb->get_charset_collate();

        $table = $wpdb->prefix . 'shopcentral_stores';
        $table_logs = $wpdb->prefix . 'shopcentral_logs';

        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table'");

        if (!$table_exists) {
            self::create_tables();
            update_option('shopcentral_schema_version', self::SCHEMA_VERSION);
            return;
        }

        // ── Migration 2.6.0: add order sync columns ──
        if (version_compare($installed, '2.6.0', '<')) {
            $columns = $wpdb->get_col("SHOW COLUMNS FROM $table");

            if (!in_array('order_import_enabled', $columns)) {
                $wpdb->query("ALTER TABLE $table ADD COLUMN order_import_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER maintenance_mode");
            }
            if (!in_array('last_order_sync', $columns)) {
                $wpdb->query("ALTER TABLE $table ADD COLUMN last_order_sync datetime DEFAULT NULL AFTER order_import_enabled");
            }

            update_option('shopcentral_schema_version', '2.6.0');
        }

        // ── Migration 2.7.0: add sync progress tracking ──
        if (version_compare($installed, '2.7.0', '<')) {
            $columns = $wpdb->get_col("SHOW COLUMNS FROM $table");

            if (!in_array('sync_progress', $columns)) {
                $wpdb->query("ALTER TABLE $table ADD COLUMN sync_progress text DEFAULT NULL AFTER last_order_sync");
            }

            update_option('shopcentral_schema_version', '2.7.0');
        }

        // ── Migration 2.8.0: add customer sync toggle ──
        if (version_compare($installed, '2.8.0', '<')) {
            $columns = $wpdb->get_col("SHOW COLUMNS FROM $table");

            if (!in_array('customer_sync_enabled', $columns)) {
                $wpdb->query("ALTER TABLE $table ADD COLUMN customer_sync_enabled tinyint(1) NOT NULL DEFAULT 1 AFTER order_import_enabled");
            }

            update_option('shopcentral_schema_version', '2.8.0');
        }

        // ── Migration 2.9.0: add shared_secret for firewall bypass ──
        if (version_compare($installed, '2.9.0', '<')) {
            $columns = $wpdb->get_col("SHOW COLUMNS FROM $table");

            if (!in_array('shared_secret', $columns)) {
                $wpdb->query("ALTER TABLE $table ADD COLUMN shared_secret varchar(64) DEFAULT NULL AFTER customer_sync_enabled");
            }

            // Back-fill secret for any existing stores that don't have one
            $stores_without_secret = $wpdb->get_col("SELECT id FROM $table WHERE shared_secret IS NULL OR shared_secret = ''");
            foreach ($stores_without_secret as $sid) {
                $secret = bin2hex(random_bytes(32));
                $wpdb->update($table, ['shared_secret' => $secret], ['id' => $sid]);
            }

            update_option('shopcentral_schema_version', '2.9.0');
        }

        // ── Migration 3.0.0: add logs table ──
        if (version_compare($installed, '3.0.0', '<')) {
            $logs_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_logs'");

            if (!$logs_exists) {
                $sql_logs = "CREATE TABLE $table_logs (
                    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                    feature varchar(100) NOT NULL DEFAULT '',
                    level varchar(20) NOT NULL DEFAULT 'info',
                    message text NOT NULL,
                    context text DEFAULT NULL,
                    store_id bigint(20) UNSIGNED DEFAULT NULL,
                    created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY  (id),
                    KEY idx_feature (feature),
                    KEY idx_level (level),
                    KEY idx_created_at (created_at),
                    KEY idx_store_id (store_id)
                ) $charset_collate;";
                dbDelta($sql_logs);
            }

            update_option('shopcentral_schema_version', '3.0.0');
        }

        // ── Migration 3.0.1: ensure logs table exists (safety check) ──
        if (version_compare($installed, '3.0.1', '<')) {
            $logs_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_logs'");

            if (!$logs_exists) {
                require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
                $charset_collate = $wpdb->get_charset_collate();

                $sql_logs = "CREATE TABLE $table_logs (
                    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                    feature varchar(100) NOT NULL DEFAULT '',
                    level varchar(20) NOT NULL DEFAULT 'info',
                    message text NOT NULL,
                    context text DEFAULT NULL,
                    store_id bigint(20) UNSIGNED DEFAULT NULL,
                    created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY  (id),
                    KEY idx_feature (feature),
                    KEY idx_level (level),
                    KEY idx_created_at (created_at),
                    KEY idx_store_id (store_id)
                ) $charset_collate;";
                dbDelta($sql_logs);
            }

            update_option('shopcentral_schema_version', '3.0.1');
        }

        // ── Migration 3.3.0: add global product editor tables ──
        if (version_compare($installed, '3.3.0', '<')) {
            $table_versions = $wpdb->prefix . 'shopcentral_product_versions';
            $table_pending = $wpdb->prefix . 'shopcentral_pending_changes';
            $table_deploy_queue = $wpdb->prefix . 'shopcentral_deploy_queue';
            $table_activity_log = $wpdb->prefix . 'shopcentral_activity_log';

            $sql_versions = "CREATE TABLE $table_versions (
                id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                product_uuid varchar(36) NOT NULL,
                store_id bigint(20) UNSIGNED NOT NULL,
                product_id bigint(20) UNSIGNED NOT NULL,
                version_number int(11) NOT NULL DEFAULT 1,
                product_data longtext NOT NULL,
                changes_summary text DEFAULT NULL,
                created_by bigint(20) UNSIGNED DEFAULT NULL,
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY uk_product_version (product_uuid, store_id, version_number),
                KEY idx_product_uuid (product_uuid),
                KEY idx_store_id (store_id),
                KEY idx_product_id (product_id),
                KEY idx_created_at (created_at)
            ) $charset_collate;";
            dbDelta($sql_versions);

            $sql_pending = "CREATE TABLE $table_pending (
                id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                pending_uuid varchar(36) NOT NULL,
                product_uuid varchar(36) NOT NULL,
                store_id bigint(20) UNSIGNED NOT NULL,
                product_id bigint(20) UNSIGNED NOT NULL,
                field_name varchar(100) NOT NULL,
                old_value text DEFAULT NULL,
                new_value text DEFAULT NULL,
                change_type varchar(30) NOT NULL DEFAULT 'update',
                status varchar(20) NOT NULL DEFAULT 'pending',
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_product_uuid (product_uuid),
                KEY idx_store_id (store_id),
                KEY idx_status (status),
                KEY idx_created_at (created_at)
            ) $charset_collate;";
            dbDelta($sql_pending);

            $sql_deploy_queue = "CREATE TABLE $table_deploy_queue (
                id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                queue_uuid varchar(36) NOT NULL,
                job_type varchar(30) NOT NULL DEFAULT 'deploy',
                source_store_id bigint(20) UNSIGNED DEFAULT NULL,
                target_store_ids text NOT NULL,
                product_ids text DEFAULT NULL,
                changes_data longtext DEFAULT NULL,
                conflict_strategy varchar(30) NOT NULL DEFAULT 'keep_source',
                status varchar(20) NOT NULL DEFAULT 'queued',
                total_items int(11) NOT NULL DEFAULT 0,
                processed_items int(11) NOT NULL DEFAULT 0,
                failed_items int(11) NOT NULL DEFAULT 0,
                progress_percent int(3) NOT NULL DEFAULT 0,
                current_store_id bigint(20) UNSIGNED DEFAULT NULL,
                current_product varchar(255) DEFAULT NULL,
                error_message text DEFAULT NULL,
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                started_at datetime DEFAULT NULL,
                completed_at datetime DEFAULT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY uk_queue_uuid (queue_uuid),
                KEY idx_status (status),
                KEY idx_job_type (job_type),
                KEY idx_created_at (created_at)
            ) $charset_collate;";
            dbDelta($sql_deploy_queue);

            $sql_activity_log = "CREATE TABLE $table_activity_log (
                id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                queue_id bigint(20) UNSIGNED DEFAULT NULL,
                action varchar(50) NOT NULL,
                entity_type varchar(30) NOT NULL DEFAULT 'product',
                entity_id bigint(20) UNSIGNED DEFAULT NULL,
                store_id bigint(20) UNSIGNED DEFAULT NULL,
                user_id bigint(20) UNSIGNED DEFAULT NULL,
                details text DEFAULT NULL,
                ip_address varchar(45) DEFAULT NULL,
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_action (action),
                KEY idx_entity (entity_type, entity_id),
                KEY idx_store_id (store_id),
                KEY idx_user_id (user_id),
                KEY idx_created_at (created_at)
            ) $charset_collate;";
            dbDelta($sql_activity_log);

            update_option('shopcentral_schema_version', '3.3.0');
        }

        // ── Migration 3.4.0: add chunk_offset for Action Scheduler chunk processing ──
        if (version_compare($installed, '3.4.0', '<')) {
            $table_deploy_queue = $wpdb->prefix . 'shopcentral_deploy_queue';
            $columns = $wpdb->get_col("SHOW COLUMNS FROM $table_deploy_queue");

            if (!in_array('chunk_offset', $columns)) {
                $wpdb->query("ALTER TABLE $table_deploy_queue ADD COLUMN chunk_offset int(11) NOT NULL DEFAULT 0 AFTER progress_percent");
            }

            update_option('shopcentral_schema_version', '3.4.0');
        }

        // ── Migration 3.5.0: add smart creator logs table ──
        if (version_compare($installed, '3.5.0', '<')) {
            $table_creator_logs = $wpdb->prefix . 'smart_creator_logs';
            $logs_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_creator_logs'");

            if (!$logs_exists) {
                $sql_creator_logs = "CREATE TABLE $table_creator_logs (
                    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                    store_id bigint(20) UNSIGNED NOT NULL,
                    store_name varchar(255) NOT NULL,
                    product_data longtext NOT NULL,
                    variations longtext NOT NULL,
                    adjustment decimal(10,2) NOT NULL DEFAULT 0,
                    error text DEFAULT NULL,
                    status varchar(20) NOT NULL DEFAULT 'failed',
                    created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    retry_at datetime DEFAULT NULL,
                    PRIMARY KEY  (id),
                    KEY idx_status (status),
                    KEY idx_store_id (store_id)
                ) $charset_collate;";
                dbDelta($sql_creator_logs);
            }

            update_option('shopcentral_schema_version', '3.5.0');
        }

        // ── Safety check: ensure chunk_offset column exists ──
        self::ensure_chunk_offset_column();
    }

    /**
     * Ensure chunk_offset column exists in deploy queue table
     * Can be called manually to fix missing column
     */
    public static function ensure_chunk_offset_column()
    {
        global $wpdb;
        $table_deploy_queue = $wpdb->prefix . 'shopcentral_deploy_queue';

        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_deploy_queue'");
        if (!$table_exists) {
            return;
        }

        // Check if column exists
        $columns = $wpdb->get_col("SHOW COLUMNS FROM $table_deploy_queue");
        if (!in_array('chunk_offset', $columns)) {
            $wpdb->query("ALTER TABLE $table_deploy_queue ADD COLUMN chunk_offset int(11) NOT NULL DEFAULT 0 AFTER progress_percent");
        }
    }

    /**
     * Force run migration 3.4.0 to add chunk_offset column
     * Call this if column is missing
     */
    public static function fix_chunk_offset_column()
    {
        // Reset schema version to force migration
        update_option('shopcentral_schema_version', '3.3.0');
        // Run upgrade
        self::maybe_upgrade();
    }
}
