<?php
/**
 * GGM Commerce Database Class
 * Handles all database operations and table management
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange

class GGM_Commerce_Database {

    public function __construct() {
        // NOTE: Activation hook will be registered from main plugin file
        // This class just provides the method
    }

    /**
     * Generate a unique slug from a product name
     *
     * @param string $name The product name
     * @param int $product_id Optional product ID (for updates to exclude self)
     * @return string The unique slug
     */
    public static function generate_unique_slug($name, $product_id = 0) {
        global $wpdb;

        // Create base slug from name
        $slug = sanitize_title($name);

        // If empty, use a fallback
        if (empty($slug)) {
            $slug = 'product';
        }

        // Check if slug exists (excluding current product if updating)
        $original_slug = $slug;
        $counter = 1;

        while (true) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $existing = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}ggmc_products WHERE slug = %s AND id != %d",
                $slug,
                $product_id
            ));

            if (!$existing) {
                break;
            }

            $slug = $original_slug . '-' . $counter;
            $counter++;
        }

        return $slug;
    }


    /**
     * Create all plugin database tables
     */
    public function create_tables() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        // Categories table
        $categories_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_categories (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            slug varchar(200) NOT NULL,
            description text,
            parent_id mediumint(9) DEFAULT 0,
            display_order int DEFAULT 0,
            image_id mediumint(9) DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY parent_id (parent_id),
            KEY status (status)
        ) $charset_collate;";

        // Tags table
        $tags_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_tags (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            slug varchar(200) NOT NULL,
            description text,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY status (status)
        ) $charset_collate;";

        // Products table with tag_ids, slug, and rating aggregates
        $products_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_products (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL DEFAULT '',
            slug varchar(255) DEFAULT '',
            sku varchar(100) DEFAULT '',
            short_description text,
            description text,
            price decimal(10,2) NOT NULL DEFAULT 0.00,
            sale_price decimal(10,2) DEFAULT NULL,
            stock int DEFAULT 0,
            stock_status varchar(20) DEFAULT 'instock',
            low_stock_threshold int DEFAULT 10,
            restock_date date DEFAULT NULL,
            weight decimal(8,2) DEFAULT NULL,
            length decimal(8,2) DEFAULT NULL,
            width decimal(8,2) DEFAULT NULL,
            height decimal(8,2) DEFAULT NULL,
            image_id mediumint(9) DEFAULT 0,
            gallery_ids text,
            tag_ids varchar(255) DEFAULT '',
            manage_stock tinyint(1) DEFAULT 1,
            featured tinyint(1) DEFAULT 0,
            average_rating decimal(3,2) DEFAULT 0.00,
            rating_count int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status),
            KEY sku (sku),
            KEY featured (featured),
            KEY average_rating (average_rating),
            UNIQUE KEY slug (slug)
        ) $charset_collate;";

        // Product-Category relationship table
        $product_categories_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_categories (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            category_id mediumint(9) NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY product_category (product_id, category_id),
            KEY product_id (product_id),
            KEY category_id (category_id)
        ) $charset_collate;";

        // Product-Tag relationship table
        $product_tags_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_tags (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            tag_id mediumint(9) NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY product_tag (product_id, tag_id),
            KEY product_id (product_id),
            KEY tag_id (tag_id)
        ) $charset_collate;";

        // Customers table
        $customers_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_customers (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) DEFAULT 0,
            email varchar(100) NOT NULL,
            first_name varchar(100) NOT NULL,
            last_name varchar(100) NOT NULL,
            phone varchar(20),
            company varchar(200),
            tax_id varchar(50) DEFAULT '',
            is_tax_exempt tinyint(1) DEFAULT 0,
            tax_exempt_status varchar(20) DEFAULT 'none',
            total_orders int DEFAULT 0,
            total_spent decimal(10,2) DEFAULT 0.00,
            last_order_date datetime,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY email (email),
            KEY user_id (user_id),
            KEY is_tax_exempt (is_tax_exempt)
        ) $charset_collate;";

        // Customer Addresses table (for multiple addresses)
        $addresses_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_customer_addresses (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            customer_id mediumint(9) NOT NULL,
            address_type varchar(20) NOT NULL DEFAULT 'billing',
            is_default tinyint(1) DEFAULT 0,
            address_label varchar(100) DEFAULT '',
            first_name varchar(100),
            last_name varchar(100),
            company varchar(200),
            address_1 varchar(255),
            address_2 varchar(255),
            city varchar(100),
            state varchar(100),
            postcode varchar(20),
            country varchar(2),
            phone varchar(20),
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY customer_id (customer_id),
            KEY address_type (address_type),
            KEY is_default (is_default)
        ) $charset_collate;";

        // Orders table (enhanced)
        $orders_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_orders (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            order_number varchar(50) NOT NULL,
            user_id bigint(20) DEFAULT 0,
            customer_id mediumint(9) DEFAULT 0,
            customer_email varchar(100) NOT NULL,
            customer_first_name varchar(100) NOT NULL,
            customer_last_name varchar(100) NOT NULL,
            customer_phone varchar(20),
            billing_address_1 varchar(255),
            billing_address_2 varchar(255),
            billing_city varchar(100),
            billing_state varchar(100),
            billing_country varchar(2),
            billing_postcode varchar(20),
            shipping_address_1 varchar(255),
            shipping_address_2 varchar(255),
            shipping_city varchar(100),
            shipping_state varchar(100),
            shipping_country varchar(2),
            shipping_postcode varchar(20),
            subtotal decimal(10,2) NOT NULL DEFAULT 0.00,
            tax_amount decimal(10,2) DEFAULT 0.00,
            tax_rate decimal(5,2) DEFAULT 0.00,
            tax_label varchar(50) DEFAULT '',
            prices_include_tax tinyint(1) DEFAULT 0,
            is_tax_exempt tinyint(1) DEFAULT 0,
            customer_tax_id varchar(50) DEFAULT '',
            shipping_amount decimal(10,2) DEFAULT 0.00,
            total_amount decimal(10,2) NOT NULL,
            status varchar(20) DEFAULT 'pending',
            payment_method varchar(50),
            payment_status varchar(20) DEFAULT 'pending',
            payment_transaction_id varchar(255),
            notes text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY order_number (order_number),
            KEY customer_email (customer_email),
            KEY customer_id (customer_id),
            KEY status (status),
            KEY payment_status (payment_status)
        ) $charset_collate;";

        // Order Items table
        $order_items_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_order_items (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            order_id mediumint(9) NOT NULL,
            product_id mediumint(9) NOT NULL,
            product_name varchar(255) NOT NULL,
            product_sku varchar(100),
            quantity int NOT NULL DEFAULT 1,
            price decimal(10,2) NOT NULL,
            total decimal(10,2) NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id),
            KEY product_id (product_id)
        ) $charset_collate;";

        // Cart table
        $cart_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_cart (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            session_id varchar(255) NOT NULL,
            product_id mediumint(9) NOT NULL,
            quantity int NOT NULL DEFAULT 1,
            cart_key varchar(32) NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY session_id (session_id),
            KEY product_id (product_id),
            UNIQUE KEY cart_key (cart_key)
        ) $charset_collate;";

        // Product Barcodes table (normalized - up to 3 barcodes per product)
        $barcodes_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_barcodes (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            barcode_value varchar(255) NOT NULL,
            barcode_type varchar(50) DEFAULT 'EAN-13',
            is_primary tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY barcode_value (barcode_value),
            KEY product_id (product_id),
            KEY is_primary (is_primary)
        ) $charset_collate;";

        // Product Reviews table
        $reviews_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_reviews (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            customer_id mediumint(9) DEFAULT NULL,
            author_name varchar(100) NOT NULL,
            author_email varchar(100) NOT NULL,
            rating tinyint(1) NOT NULL,
            title varchar(255) DEFAULT '',
            content text,
            status varchar(20) DEFAULT 'pending',
            verified_purchase tinyint(1) DEFAULT 0,
            helpful_votes int DEFAULT 0,
            ip_address varchar(45) DEFAULT '',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY customer_id (customer_id),
            KEY status (status),
            KEY rating (rating),
            KEY created_at (created_at)
        ) $charset_collate;";

        // Wishlists master table (multiple wishlists per user)
        $wishlists_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_wishlists (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            name varchar(100) NOT NULL,
            slug varchar(100) NOT NULL,
            is_default tinyint(1) NOT NULL DEFAULT 0,
            is_public tinyint(1) NOT NULL DEFAULT 0,
            share_token varchar(32) DEFAULT NULL,
            sort_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY user_slug (user_id, slug),
            UNIQUE KEY share_token (share_token),
            KEY user_id (user_id),
            KEY is_default (is_default)
        ) $charset_collate;";

        // Wishlist items table (products in wishlists)
        $wishlist_items_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_wishlist_items (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            wishlist_id bigint(20) UNSIGNED NOT NULL,
            product_id mediumint(9) NOT NULL,
            notes text DEFAULT NULL,
            priority int DEFAULT 0,
            date_added datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY wishlist_product (wishlist_id, product_id),
            KEY wishlist_id (wishlist_id),
            KEY product_id (product_id)
        ) $charset_collate;";

        // Legacy wishlist table (kept for migration, will be removed after migration)
        $wishlist_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_wishlist (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            product_id mediumint(9) NOT NULL,
            date_added datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY user_product (user_id, product_id),
            KEY user_id (user_id),
            KEY product_id (product_id),
            KEY date_added (date_added)
        ) $charset_collate;";

        // Attribute Groups table (templates for quick attribute addition)
        $attribute_groups_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_attribute_groups (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            description text,
            display_order int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status),
            KEY display_order (display_order)
        ) $charset_collate;";

        // Attributes table (master list of available attributes)
        $attributes_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_attributes (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            group_id mediumint(9) DEFAULT NULL,
            name varchar(200) NOT NULL,
            attribute_type varchar(50) DEFAULT 'text',
            options text,
            unit varchar(50) DEFAULT '',
            icon varchar(100) DEFAULT '',
            filterable tinyint(1) DEFAULT 0,
            display_order int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY group_id (group_id),
            KEY status (status),
            KEY display_order (display_order),
            KEY filterable (filterable)
        ) $charset_collate;";

        // Attribute Group Members table (many-to-many: attributes can belong to multiple groups)
        $attribute_group_members_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_attribute_group_members (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            group_id mediumint(9) NOT NULL,
            attribute_id mediumint(9) NOT NULL,
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY group_attribute (group_id, attribute_id),
            KEY group_id (group_id),
            KEY attribute_id (attribute_id)
        ) $charset_collate;";

        // Product Attribute Values table (which attributes a product has + their values)
        $product_attribute_values_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_attribute_values (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            attribute_id mediumint(9) NOT NULL,
            attribute_value text,
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY product_attribute (product_id, attribute_id),
            KEY product_id (product_id),
            KEY attribute_id (attribute_id)
        ) $charset_collate;";

        // Product Kits table (links a product to kit/build definition)
        $product_kits_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_kits (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            kit_type varchar(20) DEFAULT 'kit',
            auto_calculate_price tinyint(1) DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY product_id (product_id),
            KEY kit_type (kit_type)
        ) $charset_collate;";

        // Kit Components table (which products make up a kit)
        $kit_components_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_kit_components (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            kit_id mediumint(9) NOT NULL,
            component_product_id mediumint(9) NOT NULL,
            quantity int DEFAULT 1,
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY kit_component (kit_id, component_product_id),
            KEY kit_id (kit_id),
            KEY component_product_id (component_product_id)
        ) $charset_collate;";

        // Stock Movements audit trail table
        $stock_movements_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_stock_movements (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            quantity_change int NOT NULL,
            previous_stock int NOT NULL,
            new_stock int NOT NULL,
            movement_type varchar(50) NOT NULL,
            reference_id mediumint(9) DEFAULT NULL,
            notes text,
            user_id bigint(20) DEFAULT 0,
            override_enabled tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY movement_type (movement_type),
            KEY reference_id (reference_id),
            KEY created_at (created_at)
        ) $charset_collate;";

        // Tax Rates table (per-product tax rates)
        $tax_rates_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_tax_rates (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(100) NOT NULL,
            slug varchar(100) NOT NULL,
            rate decimal(5,2) NOT NULL DEFAULT 0.00,
            description text,
            is_default tinyint(1) DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY is_default (is_default),
            KEY status (status),
            KEY display_order (display_order)
        ) $charset_collate;";

        // Payment Logs table (for tracking payment gateway transactions)
        $payment_logs_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_payment_logs (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            order_id mediumint(9) NOT NULL,
            gateway varchar(50) NOT NULL,
            transaction_id varchar(255) DEFAULT NULL,
            event_type varchar(50) NOT NULL,
            amount decimal(10,2) DEFAULT NULL,
            currency varchar(3) DEFAULT NULL,
            status varchar(20) DEFAULT NULL,
            raw_response longtext,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id),
            KEY gateway (gateway),
            KEY transaction_id (transaction_id),
            KEY event_type (event_type),
            KEY created_at (created_at)
        ) $charset_collate;";

        // Shipping Zones table
        $shipping_zones_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_shipping_zones (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            slug varchar(200) NOT NULL,
            description text,
            countries text,
            regions text,
            postcodes text,
            is_default tinyint(1) DEFAULT 0,
            priority int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY is_default (is_default),
            KEY status (status),
            KEY priority (priority)
        ) $charset_collate;";

        // Shipping Methods table
        $shipping_methods_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_shipping_methods (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            zone_id mediumint(9) NOT NULL,
            name varchar(200) NOT NULL,
            slug varchar(200) NOT NULL,
            method_type varchar(50) NOT NULL,
            description text,
            cost decimal(10,2) DEFAULT 0.00,
            free_threshold decimal(10,2) DEFAULT NULL,
            settings text,
            is_taxable tinyint(1) DEFAULT 1,
            estimated_days_min int DEFAULT NULL,
            estimated_days_max int DEFAULT NULL,
            status varchar(20) DEFAULT 'active',
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY zone_id (zone_id),
            KEY method_type (method_type),
            KEY status (status),
            KEY display_order (display_order)
        ) $charset_collate;";

        // Shipping Rates table (for weight-based tiers)
        $shipping_rates_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_shipping_rates (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            method_id mediumint(9) NOT NULL,
            rate_type varchar(50) NOT NULL,
            min_value decimal(10,3) DEFAULT 0,
            max_value decimal(10,3) DEFAULT NULL,
            cost decimal(10,2) NOT NULL,
            per_unit_cost decimal(10,2) DEFAULT 0.00,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY method_id (method_id),
            KEY rate_type (rate_type)
        ) $charset_collate;";

        // =====================================================
        // PRODUCT VARIATIONS TABLES
        // =====================================================

        // Variation Type Groups (templates for quick variation setup, e.g., "Clothing" = Size + Color)
        $variation_type_groups_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_variation_type_groups (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            slug varchar(200) NOT NULL,
            description text,
            is_suggestion tinyint(1) DEFAULT 0,
            display_order int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY is_suggestion (is_suggestion),
            KEY status (status),
            KEY display_order (display_order)
        ) $charset_collate;";

        // Variation Type Group Members (many-to-many: types can belong to multiple groups)
        $variation_type_group_members_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_variation_type_group_members (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            group_id mediumint(9) NOT NULL,
            variation_type_id mediumint(9) NOT NULL,
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY group_type (group_id, variation_type_id),
            KEY group_id (group_id),
            KEY variation_type_id (variation_type_id)
        ) $charset_collate;";

        // Variation Types (Color, Size, Material, etc. - the dimensions along which a product varies)
        $variation_types_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_variation_types (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            slug varchar(200) NOT NULL,
            input_type varchar(50) DEFAULT 'select',
            options text,
            display_order int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY status (status),
            KEY display_order (display_order)
        ) $charset_collate;";

        // Product Variation Types (which variation types a product uses, e.g., product 5 uses Size and Color)
        $product_variation_types_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_variation_types (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            variation_type_id mediumint(9) NOT NULL,
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY product_type (product_id, variation_type_id),
            KEY product_id (product_id),
            KEY variation_type_id (variation_type_id)
        ) $charset_collate;";

        // Individual Product Variations (Combined mode: each row is a purchasable SKU like "Large Red")
        $product_variations_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_variations (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            sku varchar(100) DEFAULT '',
            price decimal(10,2) DEFAULT NULL,
            sale_price decimal(10,2) DEFAULT NULL,
            stock int DEFAULT NULL,
            stock_status varchar(20) DEFAULT 'instock',
            manage_stock tinyint(1) DEFAULT 0,
            image_id mediumint(9) DEFAULT NULL,
            gallery_ids text,
            weight decimal(8,2) DEFAULT NULL,
            is_default tinyint(1) DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY sku (sku),
            KEY status (status),
            KEY is_default (is_default)
        ) $charset_collate;";

        // Variation Type Values (links a variation to its type values, e.g., variation 10 has Size=Large, Color=Red)
        $variation_type_values_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_variation_type_values (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            variation_id mediumint(9) NOT NULL,
            variation_type_id mediumint(9) NOT NULL,
            option_value varchar(255) NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY variation_type (variation_id, variation_type_id),
            KEY variation_id (variation_id),
            KEY variation_type_id (variation_type_id),
            KEY option_value (option_value(100))
        ) $charset_collate;";

        // Product Variation Options (Independent mode: per-product option config with price modifiers)
        $product_variation_options_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_variation_options (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            variation_type_id mediumint(9) NOT NULL,
            option_value varchar(255) NOT NULL,
            price_modifier decimal(10,2) DEFAULT 0.00,
            is_enabled tinyint(1) DEFAULT 1,
            is_default tinyint(1) DEFAULT 0,
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY product_type_option (product_id, variation_type_id, option_value(100)),
            KEY product_id (product_id),
            KEY variation_type_id (variation_type_id),
            KEY is_enabled (is_enabled),
            KEY is_default (is_default)
        ) $charset_collate;";

        // =====================================================
        // NON-SHIPPED PRODUCTS / DOWNLOADS TABLES
        // =====================================================

        // Product Download Files
        $product_downloads_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_downloads (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            name varchar(255) NOT NULL,
            file_url text NOT NULL,
            file_type varchar(50) DEFAULT '',
            file_size bigint(20) DEFAULT 0,
            download_limit int DEFAULT -1,
            access_expiry_days int DEFAULT -1,
            display_order int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY status (status)
        ) $charset_collate;";

        // Download Permissions (granted after purchase)
        $download_permissions_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_download_permissions (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            order_id mediumint(9) NOT NULL,
            order_item_id mediumint(9) NOT NULL,
            product_id mediumint(9) NOT NULL,
            download_id mediumint(9) NOT NULL,
            customer_id mediumint(9) DEFAULT NULL,
            customer_email varchar(100) NOT NULL,
            access_granted_at datetime NOT NULL,
            access_expires_at datetime DEFAULT NULL,
            download_count int DEFAULT 0,
            download_limit int DEFAULT -1,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY order_download (order_id, download_id),
            KEY order_id (order_id),
            KEY product_id (product_id),
            KEY customer_email (customer_email),
            KEY customer_id (customer_id),
            KEY status (status),
            KEY access_expires_at (access_expires_at)
        ) $charset_collate;";

        // Download Logs (audit trail)
        $download_logs_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_download_logs (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            permission_id bigint(20) NOT NULL,
            customer_id mediumint(9) DEFAULT NULL,
            ip_address varchar(45) DEFAULT '',
            user_agent text,
            downloaded_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY permission_id (permission_id),
            KEY customer_id (customer_id),
            KEY downloaded_at (downloaded_at)
        ) $charset_collate;";

        // License Keys for digital products
        $product_licenses_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_product_licenses (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            product_id mediumint(9) NOT NULL,
            license_key varchar(255) NOT NULL,
            order_id mediumint(9) DEFAULT NULL,
            order_item_id mediumint(9) DEFAULT NULL,
            customer_email varchar(100) DEFAULT '',
            status varchar(20) DEFAULT 'available',
            activations_count int DEFAULT 0,
            max_activations int DEFAULT 1,
            expires_at datetime DEFAULT NULL,
            assigned_at datetime DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY license_key (license_key),
            KEY product_id (product_id),
            KEY order_id (order_id),
            KEY customer_email (customer_email),
            KEY status (status)
        ) $charset_collate;";

        // Order Notes table (structured notes with visibility control)
        $order_notes_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_order_notes (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            order_id mediumint(9) NOT NULL,
            note_type varchar(20) NOT NULL DEFAULT 'private',
            content text NOT NULL,
            is_system tinyint(1) DEFAULT 0,
            created_by bigint(20) DEFAULT NULL,
            created_by_name varchar(100) DEFAULT '',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id),
            KEY note_type (note_type),
            KEY is_system (is_system),
            KEY created_at (created_at)
        ) $charset_collate;";

        // Shipments table (supports multiple shipments per order for split shipping)
        $shipments_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_shipments (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            order_id mediumint(9) NOT NULL,
            shipment_number varchar(50) NOT NULL,
            carrier varchar(50) NOT NULL DEFAULT '',
            carrier_service varchar(100) DEFAULT '',
            tracking_number varchar(100) DEFAULT '',
            tracking_url varchar(500) DEFAULT '',
            status varchar(20) NOT NULL DEFAULT 'pending',
            label_url varchar(500) DEFAULT '',
            label_data longtext DEFAULT NULL,
            weight decimal(10,3) DEFAULT NULL,
            weight_unit varchar(10) DEFAULT 'kg',
            dimensions_length decimal(10,2) DEFAULT NULL,
            dimensions_width decimal(10,2) DEFAULT NULL,
            dimensions_height decimal(10,2) DEFAULT NULL,
            dimensions_unit varchar(10) DEFAULT 'cm',
            shipping_cost decimal(10,2) DEFAULT 0.00,
            insurance_amount decimal(10,2) DEFAULT 0.00,
            carrier_response longtext DEFAULT NULL,
            shipped_at datetime DEFAULT NULL,
            estimated_delivery datetime DEFAULT NULL,
            delivered_at datetime DEFAULT NULL,
            created_by bigint(20) DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY shipment_number (shipment_number),
            KEY order_id (order_id),
            KEY carrier (carrier),
            KEY tracking_number (tracking_number),
            KEY status (status),
            KEY shipped_at (shipped_at)
        ) $charset_collate;";

        // Shipment Items table (tracks which order items are in each shipment)
        $shipment_items_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_shipment_items (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            shipment_id bigint(20) NOT NULL,
            order_item_id bigint(20) NOT NULL,
            quantity int(11) NOT NULL DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY shipment_item (shipment_id, order_item_id),
            KEY shipment_id (shipment_id),
            KEY order_item_id (order_item_id)
        ) $charset_collate;";

        // Shipment Events table (tracking history from carrier APIs)
        $shipment_events_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_shipment_events (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            shipment_id bigint(20) NOT NULL,
            event_code varchar(50) DEFAULT '',
            event_description varchar(500) NOT NULL,
            location varchar(255) DEFAULT '',
            event_time datetime NOT NULL,
            raw_data longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY shipment_id (shipment_id),
            KEY event_time (event_time)
        ) $charset_collate;";

        // Email Templates table (stores customized email templates)
        $email_templates_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_email_templates (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            email_type varchar(50) NOT NULL,
            subject varchar(255) NOT NULL,
            heading varchar(255) DEFAULT '',
            content longtext DEFAULT NULL,
            enabled tinyint(1) DEFAULT 1,
            recipient_type varchar(20) DEFAULT 'customer',
            additional_recipients text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY email_type (email_type)
        ) $charset_collate;";

        // Email Log table (tracks sent emails)
        $email_log_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_email_log (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            email_type varchar(50) NOT NULL,
            order_id mediumint(9) DEFAULT NULL,
            recipient varchar(255) NOT NULL,
            subject varchar(255) NOT NULL,
            status varchar(20) DEFAULT 'sent',
            error_message text DEFAULT NULL,
            sent_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id),
            KEY email_type (email_type),
            KEY sent_at (sent_at)
        ) $charset_collate;";

        // =====================================================
        // TRANSACTION EXPORT TABLES
        // =====================================================

        // Export Batches table (tracks transaction exports for bookkeepers)
        $export_batches_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_export_batches (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            batch_number varchar(50) NOT NULL,
            export_type varchar(20) NOT NULL DEFAULT 'transactions',
            date_from datetime NOT NULL,
            date_to datetime NOT NULL,
            order_count int NOT NULL DEFAULT 0,
            refund_count int NOT NULL DEFAULT 0,
            total_sales decimal(10,2) NOT NULL DEFAULT 0.00,
            total_refunds decimal(10,2) NOT NULL DEFAULT 0.00,
            total_tax decimal(10,2) NOT NULL DEFAULT 0.00,
            total_shipping decimal(10,2) NOT NULL DEFAULT 0.00,
            net_total decimal(10,2) NOT NULL DEFAULT 0.00,
            payment_method_breakdown text,
            export_format varchar(10) NOT NULL DEFAULT 'csv',
            file_path varchar(500) DEFAULT '',
            notes text,
            created_by bigint(20) NOT NULL,
            created_by_name varchar(100) DEFAULT '',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY batch_number (batch_number),
            KEY export_type (export_type),
            KEY date_from (date_from),
            KEY date_to (date_to),
            KEY created_at (created_at)
        ) $charset_collate;";

        // =====================================================
        // DISCOUNTS & VOUCHERS TABLES (Premium)
        // =====================================================

        // Discounts table (discount codes and vouchers)
        $discounts_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_discounts (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            code varchar(50) NOT NULL,
            name varchar(200) NOT NULL,
            description text,
            discount_type varchar(20) NOT NULL DEFAULT 'percentage',
            amount decimal(10,2) NOT NULL DEFAULT 0.00,
            initial_value decimal(10,2) DEFAULT NULL,
            current_balance decimal(10,2) DEFAULT NULL,
            single_use_voucher tinyint(1) DEFAULT 0,
            usage_limit int DEFAULT NULL,
            usage_limit_per_user int DEFAULT NULL,
            usage_count int DEFAULT 0,
            minimum_order_amount decimal(10,2) DEFAULT NULL,
            maximum_discount_amount decimal(10,2) DEFAULT NULL,
            applies_to varchar(20) DEFAULT 'all',
            product_ids text,
            category_ids text,
            exclude_product_ids text,
            exclude_category_ids text,
            valid_from datetime DEFAULT NULL,
            valid_to datetime DEFAULT NULL,
            free_shipping tinyint(1) DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_by bigint(20) DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY code (code),
            KEY discount_type (discount_type),
            KEY status (status),
            KEY valid_from (valid_from),
            KEY valid_to (valid_to)
        ) $charset_collate;";

        // Discount Usage table (tracks each use of a discount)
        $discount_usage_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_discount_usage (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            discount_id mediumint(9) NOT NULL,
            order_id mediumint(9) NOT NULL,
            customer_id mediumint(9) DEFAULT NULL,
            customer_email varchar(100) NOT NULL,
            discount_amount decimal(10,2) NOT NULL,
            voucher_balance_before decimal(10,2) DEFAULT NULL,
            voucher_balance_after decimal(10,2) DEFAULT NULL,
            used_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY discount_id (discount_id),
            KEY order_id (order_id),
            KEY customer_id (customer_id),
            KEY customer_email (customer_email),
            KEY used_at (used_at)
        ) $charset_collate;";

        // Subscriptions table
        $subscriptions_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_subscriptions (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            subscription_number varchar(50) NOT NULL,
            customer_id mediumint(9) NOT NULL,
            customer_email varchar(100) NOT NULL,
            parent_order_id mediumint(9) NOT NULL,
            product_id mediumint(9) NOT NULL,
            variation_id mediumint(9) DEFAULT NULL,
            quantity int NOT NULL DEFAULT 1,
            billing_interval int NOT NULL DEFAULT 1,
            billing_period varchar(20) NOT NULL DEFAULT 'month',
            recurring_amount decimal(10,2) NOT NULL,
            tax_amount decimal(10,2) DEFAULT 0.00,
            shipping_amount decimal(10,2) DEFAULT 0.00,
            recurring_total decimal(10,2) NOT NULL,
            currency varchar(3) DEFAULT 'GBP',
            trial_length int DEFAULT 0,
            trial_period varchar(20) DEFAULT 'day',
            trial_end_date datetime DEFAULT NULL,
            payment_gateway varchar(50) NOT NULL,
            gateway_subscription_id varchar(255) DEFAULT NULL,
            gateway_customer_id varchar(255) DEFAULT NULL,
            payment_method_token varchar(255) DEFAULT NULL,
            start_date datetime NOT NULL,
            next_payment_date datetime DEFAULT NULL,
            end_date datetime DEFAULT NULL,
            last_payment_date datetime DEFAULT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            cancellation_reason text DEFAULT NULL,
            cancelled_at datetime DEFAULT NULL,
            paused_at datetime DEFAULT NULL,
            requires_manual_renewal tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY subscription_number (subscription_number),
            KEY customer_id (customer_id),
            KEY customer_email (customer_email),
            KEY parent_order_id (parent_order_id),
            KEY product_id (product_id),
            KEY status (status),
            KEY next_payment_date (next_payment_date),
            KEY payment_gateway (payment_gateway),
            KEY gateway_subscription_id (gateway_subscription_id)
        ) $charset_collate;";

        // Subscription Items table
        $subscription_items_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_subscription_items (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            subscription_id bigint(20) NOT NULL,
            product_id mediumint(9) NOT NULL,
            product_name varchar(255) NOT NULL,
            product_sku varchar(100) DEFAULT '',
            variation_id mediumint(9) DEFAULT NULL,
            variation_data text DEFAULT NULL,
            quantity int NOT NULL DEFAULT 1,
            recurring_price decimal(10,2) NOT NULL,
            recurring_total decimal(10,2) NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY subscription_id (subscription_id),
            KEY product_id (product_id)
        ) $charset_collate;";

        // Subscription Log table (audit trail)
        $subscription_log_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_subscription_log (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            subscription_id bigint(20) NOT NULL,
            event_type varchar(50) NOT NULL,
            description text,
            order_id mediumint(9) DEFAULT NULL,
            amount decimal(10,2) DEFAULT NULL,
            initiated_by varchar(20) DEFAULT 'system',
            user_id bigint(20) DEFAULT NULL,
            metadata text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY subscription_id (subscription_id),
            KEY event_type (event_type),
            KEY order_id (order_id),
            KEY created_at (created_at)
        ) $charset_collate;";

        // Subscription Payment Retries table
        $subscription_retries_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_subscription_payment_retries (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            subscription_id bigint(20) NOT NULL,
            retry_number int NOT NULL DEFAULT 1,
            scheduled_at datetime NOT NULL,
            attempted_at datetime DEFAULT NULL,
            result varchar(20) DEFAULT 'pending',
            order_id mediumint(9) DEFAULT NULL,
            error_message text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY subscription_id (subscription_id),
            KEY scheduled_at (scheduled_at),
            KEY result (result)
        ) $charset_collate;";

        // Checkout Field Groups table
        $checkout_field_groups_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_checkout_field_groups (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            description text,
            position varchar(50) DEFAULT 'after_billing',
            display_order int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status),
            KEY position (position),
            KEY display_order (display_order)
        ) $charset_collate;";

        // Checkout Fields table
        $checkout_fields_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_checkout_fields (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            group_id mediumint(9) DEFAULT NULL,
            name varchar(200) NOT NULL,
            label varchar(200) NOT NULL,
            field_type varchar(50) DEFAULT 'text',
            placeholder varchar(200) DEFAULT '',
            help_text text,
            options text,
            default_value varchar(200) DEFAULT '',
            required tinyint(1) DEFAULT 0,
            validation varchar(50) DEFAULT '',
            position varchar(50) DEFAULT 'after_billing',
            width varchar(20) DEFAULT 'full',
            admin_editable tinyint(1) DEFAULT 0,
            show_in_email tinyint(1) DEFAULT 1,
            display_order int DEFAULT 0,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY group_id (group_id),
            KEY status (status),
            KEY position (position),
            KEY display_order (display_order)
        ) $charset_collate;";

        // Checkout Field Group Members table
        $checkout_field_group_members_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_checkout_field_group_members (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            group_id mediumint(9) NOT NULL,
            field_id mediumint(9) NOT NULL,
            display_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY group_field (group_id, field_id),
            KEY group_id (group_id),
            KEY field_id (field_id)
        ) $charset_collate;";

        // Order Checkout Field Values table
        $order_checkout_field_values_sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ggmc_order_checkout_field_values (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            order_id mediumint(9) NOT NULL,
            field_id mediumint(9) NOT NULL,
            field_name varchar(200) NOT NULL,
            field_value text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY order_field (order_id, field_id),
            KEY order_id (order_id),
            KEY field_id (field_id)
        ) $charset_collate;";

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

        // Create all tables
        $wpdb->query($categories_sql);
        $wpdb->query($tags_sql);
        $wpdb->query($products_sql);
        $wpdb->query($product_categories_sql);
        $wpdb->query($product_tags_sql);
        $wpdb->query($customers_sql);
        $wpdb->query($addresses_sql);
        $wpdb->query($orders_sql);
        $wpdb->query($order_items_sql);
        $wpdb->query($cart_sql);
        $wpdb->query($barcodes_sql);
        $wpdb->query($reviews_sql);
        $wpdb->query($wishlists_sql);
        $wpdb->query($wishlist_items_sql);
        $wpdb->query($wishlist_sql);
        $wpdb->query($attribute_groups_sql);
        $wpdb->query($attributes_sql);
        $wpdb->query($attribute_group_members_sql);
        $wpdb->query($product_attribute_values_sql);
        $wpdb->query($product_kits_sql);
        $wpdb->query($kit_components_sql);
        $wpdb->query($stock_movements_sql);
        $wpdb->query($tax_rates_sql);
        $wpdb->query($payment_logs_sql);
        $wpdb->query($shipping_zones_sql);
        $wpdb->query($shipping_methods_sql);
        $wpdb->query($shipping_rates_sql);

        // Product Variations tables
        $wpdb->query($variation_type_groups_sql);
        $wpdb->query($variation_type_group_members_sql);
        $wpdb->query($variation_types_sql);
        $wpdb->query($product_variation_types_sql);
        $wpdb->query($product_variations_sql);
        $wpdb->query($variation_type_values_sql);
        $wpdb->query($product_variation_options_sql);

        // Non-Shipped Products / Downloads tables
        $wpdb->query($product_downloads_sql);
        $wpdb->query($download_permissions_sql);
        $wpdb->query($download_logs_sql);
        $wpdb->query($product_licenses_sql);

        // Order Notes table
        $wpdb->query($order_notes_sql);

        // Shipments tables
        $wpdb->query($shipments_sql);
        $wpdb->query($shipment_items_sql);
        $wpdb->query($shipment_events_sql);

        // Email tables
        $wpdb->query($email_templates_sql);
        $wpdb->query($email_log_sql);

        // Transaction Export tables
        $wpdb->query($export_batches_sql);

        // Discounts & Vouchers tables
        $wpdb->query($discounts_sql);
        $wpdb->query($discount_usage_sql);

        // Subscription tables
        $wpdb->query($subscriptions_sql);
        $wpdb->query($subscription_items_sql);
        $wpdb->query($subscription_log_sql);
        $wpdb->query($subscription_retries_sql);

        // Checkout Fields tables
        $wpdb->query($checkout_field_groups_sql);
        $wpdb->query($checkout_fields_sql);
        $wpdb->query($checkout_field_group_members_sql);
        $wpdb->query($order_checkout_field_values_sql);

        // Add product_type column to products table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $column_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'product_type'"
        );
        if ( empty( $column_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN product_type varchar(20) DEFAULT 'simple' AFTER status"
            );
        }

        // Add tax columns to orders table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $tax_rate_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_orders LIKE 'tax_rate'"
        );
        if ( empty( $tax_rate_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN tax_rate decimal(5,2) DEFAULT 0.00 AFTER tax_amount"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN tax_label varchar(50) DEFAULT '' AFTER tax_rate"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN prices_include_tax tinyint(1) DEFAULT 0 AFTER tax_label"
            );
        }

        // Add B2B tax columns to customers table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $tax_id_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_customers LIKE 'tax_id'"
        );
        if ( empty( $tax_id_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_customers ADD COLUMN tax_id varchar(50) DEFAULT '' AFTER company"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_customers ADD COLUMN is_tax_exempt tinyint(1) DEFAULT 0 AFTER tax_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_customers ADD COLUMN tax_exempt_status varchar(20) DEFAULT 'none' AFTER is_tax_exempt"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_customers ADD INDEX is_tax_exempt (is_tax_exempt)"
            );
        }

        // Add B2B tax columns to orders table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $order_tax_exempt_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_orders LIKE 'is_tax_exempt'"
        );
        if ( empty( $order_tax_exempt_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN is_tax_exempt tinyint(1) DEFAULT 0 AFTER prices_include_tax"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN customer_tax_id varchar(50) DEFAULT '' AFTER is_tax_exempt"
            );
        }

        // Add tax_rate_id column to products table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $tax_rate_id_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'tax_rate_id'"
        );
        if ( empty( $tax_rate_id_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN tax_rate_id mediumint(9) DEFAULT NULL AFTER status"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD INDEX tax_rate_id (tax_rate_id)"
            );
        }

        // Add account_type column to customers table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $account_type_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_customers LIKE 'account_type'"
        );
        if ( empty( $account_type_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_customers ADD COLUMN account_type varchar(20) DEFAULT 'individual' AFTER tax_exempt_status"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_customers ADD INDEX account_type (account_type)"
            );
        }

        // Add payment gateway columns to orders table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $payment_intent_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_orders LIKE 'payment_intent_id'"
        );
        if ( empty( $payment_intent_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN payment_intent_id varchar(255) DEFAULT NULL AFTER payment_transaction_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN payment_metadata text DEFAULT NULL AFTER payment_intent_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN payment_error_message text DEFAULT NULL AFTER payment_metadata"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD INDEX payment_intent_id (payment_intent_id)"
            );
        }

        // Add shipping method columns to orders table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $shipping_method_id_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_orders LIKE 'shipping_method_id'"
        );
        if ( empty( $shipping_method_id_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipping_method_id mediumint(9) DEFAULT NULL AFTER shipping_amount"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipping_method_name varchar(200) DEFAULT '' AFTER shipping_method_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipping_method_type varchar(50) DEFAULT '' AFTER shipping_method_name"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipping_zone_id mediumint(9) DEFAULT NULL AFTER shipping_method_type"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipping_calculated_at datetime DEFAULT NULL AFTER shipping_zone_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD INDEX shipping_method_id (shipping_method_id)"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD INDEX shipping_zone_id (shipping_zone_id)"
            );
        }

        // Add variation columns to products table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $has_variations_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'has_variations'"
        );
        if ( empty( $has_variations_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN has_variations tinyint(1) DEFAULT 0 AFTER product_type"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN variation_stock_mode varchar(20) DEFAULT 'parent' AFTER has_variations"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD INDEX has_variations (has_variations)"
            );
        }

        // Add non-shipped product columns to products table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $requires_shipping_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'requires_shipping'"
        );
        if ( empty( $requires_shipping_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN requires_shipping tinyint(1) DEFAULT 1 AFTER variation_stock_mode"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN is_virtual tinyint(1) DEFAULT 0 AFTER requires_shipping"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN is_downloadable tinyint(1) DEFAULT 0 AFTER is_virtual"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN download_access_mode varchar(20) DEFAULT 'unlimited' AFTER is_downloadable"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD INDEX requires_shipping (requires_shipping)"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD INDEX is_virtual (is_virtual)"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD INDEX is_downloadable (is_downloadable)"
            );
        }

        // Add variation_mode column to products table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $variation_mode_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'variation_mode'"
        );
        if ( empty( $variation_mode_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN variation_mode varchar(20) DEFAULT 'combined' AFTER variation_stock_mode"
            );
        }

        // Add price_type column to product_variations table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $price_type_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_product_variations LIKE 'price_type'"
        );
        if ( empty( $price_type_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_product_variations ADD COLUMN price_type varchar(20) DEFAULT 'absolute' AFTER sale_price"
            );
        }

        // Add variation columns to order_items table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $variation_id_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_order_items LIKE 'variation_id'"
        );
        if ( empty( $variation_id_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD COLUMN variation_id mediumint(9) DEFAULT NULL AFTER product_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD COLUMN variation_data text DEFAULT NULL AFTER variation_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD INDEX variation_id (variation_id)"
            );
        }

        // Add digital product columns to order_items table (check independently)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $requires_shipping_item_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_order_items LIKE 'requires_shipping'"
        );
        if ( empty( $requires_shipping_item_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD COLUMN requires_shipping tinyint(1) DEFAULT 1 AFTER variation_data"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD COLUMN is_downloadable tinyint(1) DEFAULT 0 AFTER requires_shipping"
            );
        }

        // Add shipping fulfillment columns to orders table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $shipping_carrier_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_orders LIKE 'shipping_carrier'"
        );
        if ( empty( $shipping_carrier_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipping_carrier varchar(100) DEFAULT '' AFTER shipping_calculated_at"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipping_tracking_number varchar(100) DEFAULT '' AFTER shipping_carrier"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipping_tracking_url varchar(500) DEFAULT '' AFTER shipping_tracking_number"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN shipped_at datetime DEFAULT NULL AFTER shipping_tracking_url"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN delivered_at datetime DEFAULT NULL AFTER shipped_at"
            );
        }

        // Add low_stock_threshold column to products table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $low_stock_threshold_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'low_stock_threshold'"
        );
        if ( empty( $low_stock_threshold_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN low_stock_threshold int DEFAULT 10 AFTER stock_status"
            );
        }

        // Add restock_date column to products table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $restock_date_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'restock_date'"
        );
        if ( empty( $restock_date_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN restock_date date DEFAULT NULL AFTER low_stock_threshold"
            );
        }

        // Add export_batch_id column to orders table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $export_batch_id_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_orders LIKE 'export_batch_id'"
        );
        if ( empty( $export_batch_id_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN export_batch_id mediumint(9) DEFAULT NULL AFTER delivered_at"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD INDEX export_batch_id (export_batch_id)"
            );
        }

        // Add discount columns to orders table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $discount_code_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_orders LIKE 'discount_code'"
        );
        if ( empty( $discount_code_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN discount_code varchar(50) DEFAULT '' AFTER customer_tax_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN discount_id mediumint(9) DEFAULT NULL AFTER discount_code"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN discount_amount decimal(10,2) DEFAULT 0.00 AFTER discount_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN discount_type varchar(20) DEFAULT '' AFTER discount_amount"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD INDEX discount_id (discount_id)"
            );
        }

        // Add gift card columns to products table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $gift_card_value_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'gift_card_value'"
        );
        if ( empty( $gift_card_value_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN gift_card_value decimal(10,2) DEFAULT NULL AFTER download_access_mode"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN gift_card_type varchar(20) DEFAULT 'fixed' AFTER gift_card_value"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN gift_card_single_use tinyint(1) DEFAULT 0 AFTER gift_card_type"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN gift_card_valid_days int DEFAULT NULL AFTER gift_card_single_use"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN gift_card_code_prefix varchar(10) DEFAULT 'GC' AFTER gift_card_valid_days"
            );
        }

        // Add gift card tracking columns to order_items table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $generated_voucher_id_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_order_items LIKE 'generated_voucher_id'"
        );
        if ( empty( $generated_voucher_id_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD COLUMN generated_voucher_id mediumint(9) DEFAULT NULL AFTER is_downloadable"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD COLUMN generated_voucher_code varchar(50) DEFAULT '' AFTER generated_voucher_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD COLUMN voucher_recipient_email varchar(100) DEFAULT '' AFTER generated_voucher_code"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD INDEX generated_voucher_id (generated_voucher_id)"
            );
        }

        // Add subscription columns to products table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $is_subscription_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products LIKE 'is_subscription'"
        );
        if ( empty( $is_subscription_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN is_subscription tinyint(1) DEFAULT 0 AFTER gift_card_code_prefix"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN subscription_period varchar(20) DEFAULT 'month' AFTER is_subscription"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN subscription_interval int DEFAULT 1 AFTER subscription_period"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN subscription_trial_length int DEFAULT 0 AFTER subscription_interval"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN subscription_trial_period varchar(20) DEFAULT 'day' AFTER subscription_trial_length"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN subscription_sign_up_fee decimal(10,2) DEFAULT 0.00 AFTER subscription_trial_period"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD COLUMN subscription_length int DEFAULT 0 AFTER subscription_sign_up_fee"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_products ADD INDEX is_subscription (is_subscription)"
            );
        }

        // Add subscription columns to orders table if they don't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $subscription_id_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_orders LIKE 'subscription_id'"
        );
        if ( empty( $subscription_id_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN subscription_id bigint(20) DEFAULT NULL AFTER discount_type"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD COLUMN is_renewal tinyint(1) DEFAULT 0 AFTER subscription_id"
            );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_orders ADD INDEX subscription_id (subscription_id)"
            );
        }

        // Add subscription flag to order_items table if it doesn't exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $is_subscription_item_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_order_items LIKE 'is_subscription_item'"
        );
        if ( empty( $is_subscription_item_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->query(
                "ALTER TABLE {$wpdb->prefix}ggmc_order_items ADD COLUMN is_subscription_item tinyint(1) DEFAULT 0 AFTER voucher_recipient_email"
            );
        }

        // Create default "Uncategorised" category
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $uncategorised_exists = $wpdb->get_var("SELECT id FROM {$wpdb->prefix}ggmc_categories WHERE slug = 'uncategorised'");
        if (!$uncategorised_exists) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_categories",
                [
                    'name' => 'Uncategorised',
                    'slug' => 'uncategorised',
                    'description' => 'Default category for products without a category',
                    'status' => 'active'
                ]
            );
        }

        // Create default tax rates if none exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $tax_rates_exist = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}ggmc_tax_rates");
        if (!$tax_rates_exist) {
            // Standard Rate (default) - 20%
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_tax_rates",
                [
                    'name' => 'Standard Rate',
                    'slug' => 'standard',
                    'rate' => 20.00,
                    'description' => 'Standard VAT rate',
                    'is_default' => 1,
                    'status' => 'active',
                    'display_order' => 1
                ]
            );

            // Reduced Rate - 5%
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_tax_rates",
                [
                    'name' => 'Reduced Rate',
                    'slug' => 'reduced',
                    'rate' => 5.00,
                    'description' => 'Reduced VAT rate for certain goods and services',
                    'is_default' => 0,
                    'status' => 'active',
                    'display_order' => 2
                ]
            );

            // Zero Rate - 0%
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_tax_rates",
                [
                    'name' => 'Zero Rate',
                    'slug' => 'zero',
                    'rate' => 0.00,
                    'description' => 'Zero-rated goods (e.g., most food, children\'s clothing)',
                    'is_default' => 0,
                    'status' => 'active',
                    'display_order' => 3
                ]
            );
        }

        // Create default shipping zones if none exist
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $shipping_zones_exist = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}ggmc_shipping_zones");
        if (!$shipping_zones_exist) {
            // Domestic Zone (UK)
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_shipping_zones",
                [
                    'name' => 'United Kingdom',
                    'slug' => 'uk-domestic',
                    'description' => 'Domestic shipping within the UK',
                    'countries' => '["GB"]',
                    'regions' => '[]',
                    'postcodes' => '[]',
                    'is_default' => 0,
                    'priority' => 10,
                    'status' => 'active',
                    'display_order' => 1
                ]
            );
            $uk_zone_id = $wpdb->insert_id;

            // Add default methods for UK zone
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_shipping_methods",
                [
                    'zone_id' => $uk_zone_id,
                    'name' => 'Standard Delivery',
                    'slug' => 'uk-standard',
                    'method_type' => 'flat_rate',
                    'cost' => 4.99,
                    'is_taxable' => 1,
                    'estimated_days_min' => 3,
                    'estimated_days_max' => 5,
                    'status' => 'active',
                    'display_order' => 1
                ]
            );

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_shipping_methods",
                [
                    'zone_id' => $uk_zone_id,
                    'name' => 'Free Shipping',
                    'slug' => 'uk-free',
                    'method_type' => 'free_shipping',
                    'cost' => 0.00,
                    'free_threshold' => 50.00,
                    'is_taxable' => 0,
                    'estimated_days_min' => 3,
                    'estimated_days_max' => 5,
                    'status' => 'active',
                    'display_order' => 2
                ]
            );

            // Rest of World Zone (default fallback)
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_shipping_zones",
                [
                    'name' => 'Rest of World',
                    'slug' => 'rest-of-world',
                    'description' => 'International shipping to all other countries',
                    'countries' => '[]',
                    'regions' => '[]',
                    'postcodes' => '[]',
                    'is_default' => 1,
                    'priority' => 0,
                    'status' => 'active',
                    'display_order' => 99
                ]
            );
            $row_zone_id = $wpdb->insert_id;

            // Add default method for Rest of World
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert(
                "{$wpdb->prefix}ggmc_shipping_methods",
                [
                    'zone_id' => $row_zone_id,
                    'name' => 'International Shipping',
                    'slug' => 'international-standard',
                    'method_type' => 'flat_rate',
                    'cost' => 14.99,
                    'is_taxable' => 0,
                    'estimated_days_min' => 7,
                    'estimated_days_max' => 21,
                    'status' => 'active',
                    'display_order' => 1
                ]
            );
        }

        // Create customer role if it doesn't exist
        if (!get_role('ggmc_customer')) {
            add_role(
                'ggmc_customer',
                'Customer',
                [
                    'read' => true,
                    'view_orders' => true,
                    'manage_addresses' => true,
                ]
            );
        }
    }

    /**
     * Recalculate ratings for all products with reviews
     * Called during plugin activation to ensure aggregates are up to date
     */
    public static function recalculate_all_product_ratings() {
        global $wpdb;

        // Check if reviews table exists
        $reviews_table = $wpdb->prefix . 'ggmc_product_reviews';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $reviews_table)) == $reviews_table;

        if (!$table_exists) {
            return;
        }

        // Get all products that have reviews
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed
        $products_with_reviews = $wpdb->get_col(
            "SELECT DISTINCT product_id FROM {$reviews_table}"
        );

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

        // Recalculate rating for each product
        foreach ($products_with_reviews as $product_id) {
            // Calculate average rating and count from approved reviews
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed
            $stats = $wpdb->get_row($wpdb->prepare(
                "SELECT AVG(rating) as average_rating, COUNT(*) as rating_count
                 FROM {$reviews_table}
                 WHERE product_id = %d AND status = 'approved'",
                $product_id
            ));

            $average_rating = $stats && $stats->average_rating ? round($stats->average_rating, 2) : 0.00;
            $rating_count = $stats ? (int) $stats->rating_count : 0;

            // Update product table
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update(
                "{$wpdb->prefix}ggmc_products",
                [
                    'average_rating' => $average_rating,
                    'rating_count' => $rating_count,
                ],
                ['id' => $product_id],
                ['%f', '%d'],
                ['%d']
            );
        }
    }

    /**
     * Get list of all plugin tables
     *
     * @return array List of table names (without prefix)
     */
    public static function get_plugin_tables() {
        return [
            'ggmc_categories',
            'ggmc_tags',
            'ggmc_products',
            'ggmc_product_categories',
            'ggmc_product_tags',
            'ggmc_customers',
            'ggmc_customer_addresses',
            'ggmc_orders',
            'ggmc_order_items',
            'ggmc_cart',
            'ggmc_product_barcodes',
            'ggmc_product_reviews',
            'ggmc_wishlists',
            'ggmc_wishlist_items',
            'ggmc_wishlist',
            'ggmc_attribute_groups',
            'ggmc_attributes',
            'ggmc_attribute_group_members',
            'ggmc_product_attribute_values',
            'ggmc_product_kits',
            'ggmc_kit_components',
            'ggmc_stock_movements',
            'ggmc_tax_rates',
            'ggmc_payment_logs',
            'ggmc_shipping_zones',
            'ggmc_shipping_methods',
            'ggmc_shipping_rates',
            'ggmc_variation_type_groups',
            'ggmc_variation_type_group_members',
            'ggmc_variation_types',
            'ggmc_product_variation_types',
            'ggmc_product_variations',
            'ggmc_variation_type_values',
            'ggmc_product_variation_options',
            'ggmc_product_downloads',
            'ggmc_download_permissions',
            'ggmc_download_logs',
            'ggmc_product_licenses',
            'ggmc_order_notes',
            'ggmc_shipments',
            'ggmc_shipment_items',
            'ggmc_shipment_events',
            'ggmc_email_templates',
            'ggmc_email_log',
            'ggmc_export_batches',
            'ggmc_discounts',
            'ggmc_discount_usage',
        ];
    }

    /**
     * Drop all plugin tables
     * WARNING: This permanently deletes all plugin data!
     */
    public static function drop_all_tables() {
        global $wpdb;

        $tables = self::get_plugin_tables();

        foreach ( $tables as $table ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}{$table}" );
        }

        // Remove stored options
        delete_option( 'ggmc_db_version' );
    }

    /**
     * Remove all plugin options
     */
    public static function delete_all_options() {
        global $wpdb;

        // Delete all options starting with ggmc_ or ggm_commerce_
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->query(
            "DELETE FROM {$wpdb->options} WHERE option_name LIKE 'ggmc_%' OR option_name LIKE 'ggm_commerce_%'"
        );
    }

    /**
     * Full uninstall - removes all data, tables, and options
     * Only call this if the user has explicitly chosen to remove all data
     */
    public static function uninstall() {
        // Drop all tables
        self::drop_all_tables();

        // Delete all options
        self::delete_all_options();

        // Remove custom role
        remove_role( 'ggmc_customer' );

        // Clear any scheduled events
        wp_clear_scheduled_hook( 'ggmc_daily_cleanup' );
        wp_clear_scheduled_hook( 'ggmc_hourly_tasks' );
    }

    /**
     * Verify database integrity - checks all tables exist with required columns
     *
     * @return array Array with 'status' (ok/error) and 'issues' (array of problems found)
     */
    public static function verify_database_integrity() {
        global $wpdb;

        $issues = [];
        $tables = self::get_plugin_tables();

        foreach ( $tables as $table ) {
            $full_table_name = $wpdb->prefix . $table;

            // Check if table exists
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $table_exists = $wpdb->get_var(
                $wpdb->prepare( "SHOW TABLES LIKE %s", $full_table_name )
            );

            if ( ! $table_exists ) {
                $issues[] = sprintf( 'Table missing: %s', $table );
            }
        }

        // Check critical columns in products table
        $required_product_columns = [
            'id', 'name', 'slug', 'sku', 'price', 'stock', 'stock_status',
            'low_stock_threshold', 'status', 'created_at', 'updated_at'
        ];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $product_columns = $wpdb->get_col(
            "SHOW COLUMNS FROM {$wpdb->prefix}ggmc_products"
        );

        if ( $product_columns ) {
            foreach ( $required_product_columns as $col ) {
                if ( ! in_array( $col, $product_columns, true ) ) {
                    $issues[] = sprintf( 'Column missing in products table: %s', $col );
                }
            }
        }

        return [
            'status' => empty( $issues ) ? 'ok' : 'error',
            'issues' => $issues,
        ];
    }

    /**
     * Repair database - runs create_tables to add any missing structures
     *
     * @return array Result of repair operation
     */
    public static function repair_database() {
        $db = new self();
        $db->create_tables();

        // Verify after repair
        return self::verify_database_integrity();
    }

    /**
     * Migrate old single wishlist to multiple wishlists schema
     *
     * This method:
     * 1. Creates the new wishlists table if it doesn't exist
     * 2. Creates the new wishlist_items table if it doesn't exist
     * 3. Migrates data from old ggmc_wishlist to new tables
     * 4. Preserves all existing wishlist data
     *
     * @return array Migration result with status and message
     */
    public static function migrate_wishlist_to_multi_list() {
        global $wpdb;

        $old_table   = $wpdb->prefix . 'ggmc_wishlist';
        $new_lists   = $wpdb->prefix . 'ggmc_wishlists';
        $new_items   = $wpdb->prefix . 'ggmc_wishlist_items';

        // Check if migration is needed (old table exists with data, new tables empty or not migrated)
        $db_version = get_option( 'ggmc_wishlist_db_version', '1.0' );

        if ( version_compare( $db_version, '2.0', '>=' ) ) {
            return array(
                'status'  => 'skipped',
                'message' => __( 'Wishlist migration already completed.', 'ggm-commerce' ),
            );
        }

        // Check if old table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $old_table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $old_table )
        );

        if ( ! $old_table_exists ) {
            // No old table, just update version
            update_option( 'ggmc_wishlist_db_version', '2.0' );
            return array(
                'status'  => 'success',
                'message' => __( 'No legacy wishlist data to migrate.', 'ggm-commerce' ),
            );
        }

        // Get distinct user IDs from old table
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $user_ids = $wpdb->get_col(
            "SELECT DISTINCT user_id FROM {$old_table}"
        );

        if ( empty( $user_ids ) ) {
            update_option( 'ggmc_wishlist_db_version', '2.0' );
            return array(
                'status'  => 'success',
                'message' => __( 'No wishlist data to migrate.', 'ggm-commerce' ),
            );
        }

        $migrated_users = 0;
        $migrated_items = 0;

        foreach ( $user_ids as $user_id ) {
            $user_id = absint( $user_id );

            // Check if user already has a wishlist in new table
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $existing_wishlist = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM {$new_lists} WHERE user_id = %d AND is_default = 1",
                    $user_id
                )
            );

            if ( ! $existing_wishlist ) {
                // Create default wishlist for user
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->insert(
                    $new_lists,
                    array(
                        'user_id'    => $user_id,
                        'name'       => __( 'My Wishlist', 'ggm-commerce' ),
                        'slug'       => 'my-wishlist',
                        'is_default' => 1,
                        'is_public'  => 0,
                        'sort_order' => 0,
                        'created_at' => current_time( 'mysql' ),
                        'updated_at' => current_time( 'mysql' ),
                    ),
                    array( '%d', '%s', '%s', '%d', '%d', '%d', '%s', '%s' )
                );
                $wishlist_id = $wpdb->insert_id;
                $migrated_users++;
            } else {
                $wishlist_id = $existing_wishlist;
            }

            // Get old wishlist items for this user
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $old_items = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT product_id, date_added FROM {$old_table} WHERE user_id = %d",
                    $user_id
                )
            );

            foreach ( $old_items as $item ) {
                // Check if item already exists in new table
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $item_exists = $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT id FROM {$new_items} WHERE wishlist_id = %d AND product_id = %d",
                        $wishlist_id,
                        $item->product_id
                    )
                );

                if ( ! $item_exists ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $wpdb->insert(
                        $new_items,
                        array(
                            'wishlist_id' => $wishlist_id,
                            'product_id'  => $item->product_id,
                            'priority'    => 0,
                            'date_added'  => $item->date_added,
                        ),
                        array( '%d', '%d', '%d', '%s' )
                    );
                    $migrated_items++;
                }
            }
        }

        // Mark migration as complete
        update_option( 'ggmc_wishlist_db_version', '2.0' );

        return array(
            'status'  => 'success',
            'message' => sprintf(
                /* translators: 1: number of users, 2: number of items */
                __( 'Wishlist migration complete. Migrated %1$d users and %2$d items.', 'ggm-commerce' ),
                $migrated_users,
                $migrated_items
            ),
        );
    }

    /**
     * Generate a unique slug for a wishlist name within a user's wishlists
     *
     * @param string $name       The wishlist name.
     * @param int    $user_id    The user ID.
     * @param int    $exclude_id Optional wishlist ID to exclude (for updates).
     * @return string The unique slug.
     */
    public static function generate_unique_wishlist_slug( $name, $user_id, $exclude_id = 0 ) {
        global $wpdb;

        $table = $wpdb->prefix . 'ggmc_wishlists';
        $slug  = sanitize_title( $name );

        if ( empty( $slug ) ) {
            $slug = 'wishlist';
        }

        $original_slug = $slug;
        $counter       = 1;

        while ( true ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $existing = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM {$table} WHERE user_id = %d AND slug = %s AND id != %d",
                    $user_id,
                    $slug,
                    $exclude_id
                )
            );

            if ( ! $existing ) {
                break;
            }

            $slug = $original_slug . '-' . $counter;
            $counter++;
        }

        return $slug;
    }

}

// Initialize database class and store global reference
global $ggm_commerce_database;
$ggm_commerce_database = new GGM_Commerce_Database();