<?php

declare(strict_types=1);
if ( ! defined( 'ABSPATH' ) ) { exit; }

/**
 * Migration 0001 — Initial Schema
 *
 * Baseline table definitions for all Instinctor tables as of 3.18.40.
 * Replaces all dbDelta() calls previously scattered across Activator and
 * individual service createTables() methods.
 *
 * All statements use CREATE TABLE IF NOT EXISTS — safe to run on existing installs.
 * {prefix} and {charset} are substituted by Migration::run() at execution time.
 */
return [
    'description' => 'Initial schema — all core Instinctor tables',

    'up' => [

        // ── Core ecommerce ────────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_products (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            slug varchar(255) NOT NULL,
            description longtext,
            short_description text,
            product_type varchar(50) NOT NULL DEFAULT 'physical',
            status varchar(20) NOT NULL DEFAULT 'draft',
            price decimal(19,4) DEFAULT NULL,
            sale_price decimal(19,4) DEFAULT NULL,
            sku varchar(100) DEFAULT NULL,
            stock_quantity int(11) DEFAULT NULL,
            stock_status varchar(20) DEFAULT 'instock',
            manage_stock tinyint(1) DEFAULT 0,
            weight decimal(10,3) DEFAULT NULL,
            length decimal(10,3) DEFAULT NULL,
            width decimal(10,3) DEFAULT NULL,
            height decimal(10,3) DEFAULT NULL,
            downloadable tinyint(1) DEFAULT 0,
            `virtual` tinyint(1) DEFAULT 0,
            featured_image bigint(20) unsigned DEFAULT NULL,
            images longtext,
            gallery_images longtext,
            attributes longtext,
            meta longtext,
            is_preorder tinyint(1) DEFAULT 0,
            preorder_date datetime DEFAULT NULL,
            is_bestseller tinyint(1) DEFAULT 0,
            is_featured tinyint(1) DEFAULT 0,
            custom_badge varchar(100) DEFAULT NULL,
            custom_badge_color varchar(20) DEFAULT NULL,
            gtin varchar(50) DEFAULT NULL,
            mpn varchar(100) DEFAULT NULL,
            brand varchar(200) DEFAULT NULL,
            membership_required tinyint(1) DEFAULT 0,
            membership_tiers longtext,
            vendor_id bigint(20) unsigned DEFAULT NULL,
            commission_rate decimal(5,2) DEFAULT NULL,
            sort_order int(11) DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY slug (slug),
            KEY status (status),
            KEY product_type (product_type),
            KEY sku (sku),
            KEY sort_order (sort_order),
            KEY vendor_id (vendor_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_categories (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            slug varchar(255) NOT NULL,
            description text,
            parent_id bigint(20) unsigned DEFAULT NULL,
            image bigint(20) unsigned DEFAULT NULL,
            commission_rate decimal(5,2) DEFAULT NULL,
            sort_order int(11) DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'active',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY parent_id (parent_id),
            KEY status (status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_product_categories (
            product_id bigint(20) unsigned NOT NULL,
            category_id bigint(20) unsigned NOT NULL,
            PRIMARY KEY (product_id, category_id),
            KEY category_id (category_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_customers (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned DEFAULT NULL,
            email varchar(255) NOT NULL,
            first_name varchar(100) DEFAULT NULL,
            last_name varchar(100) DEFAULT NULL,
            phone varchar(50) DEFAULT NULL,
            billing_address longtext,
            shipping_address longtext,
            meta longtext,
            total_spent decimal(19,4) DEFAULT 0,
            order_count int(11) DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY email (email),
            KEY user_id (user_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_orders (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_number varchar(50) NOT NULL,
            customer_id bigint(20) unsigned DEFAULT NULL,
            status varchar(50) NOT NULL DEFAULT 'pending',
            currency varchar(10) NOT NULL DEFAULT 'USD',
            subtotal decimal(19,4) NOT NULL DEFAULT 0,
            discount_total decimal(19,4) NOT NULL DEFAULT 0,
            shipping_total decimal(19,4) NOT NULL DEFAULT 0,
            tax_total decimal(19,4) NOT NULL DEFAULT 0,
            total decimal(19,4) NOT NULL DEFAULT 0,
            payment_method varchar(100) DEFAULT NULL,
            payment_status varchar(50) DEFAULT 'pending',
            payment_intent_id varchar(255) DEFAULT NULL,
            transaction_id varchar(255) DEFAULT NULL,
            billing_address longtext,
            shipping_address longtext,
            customer_note text,
            admin_note text,
            ip_address varchar(45) DEFAULT NULL,
            user_agent text,
            meta longtext,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            completed_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY order_number (order_number),
            KEY customer_id (customer_id),
            KEY status (status),
            KEY created_at (created_at)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_order_items (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            product_id bigint(20) unsigned DEFAULT NULL,
            variation_id bigint(20) unsigned DEFAULT NULL,
            name varchar(255) NOT NULL,
            sku varchar(100) DEFAULT NULL,
            quantity int(11) NOT NULL DEFAULT 1,
            price decimal(19,4) NOT NULL,
            subtotal decimal(19,4) NOT NULL,
            tax decimal(19,4) DEFAULT 0,
            total decimal(19,4) NOT NULL,
            meta longtext,
            PRIMARY KEY (id),
            KEY order_id (order_id),
            KEY product_id (product_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_order_notes (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            note text NOT NULL,
            is_customer_note tinyint(1) NOT NULL DEFAULT 0,
            created_by bigint(20) unsigned DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_refunds (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            amount decimal(19,4) NOT NULL,
            reason text,
            refunded_by bigint(20) unsigned DEFAULT NULL,
            payment_refund_id varchar(255) DEFAULT NULL,
            line_items longtext,
            status enum('pending','completed','failed') NOT NULL DEFAULT 'completed',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id)
        ) {charset}",

        // ── Products ──────────────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_product_variations (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            sku varchar(100) DEFAULT NULL,
            price decimal(19,4) DEFAULT NULL,
            sale_price decimal(19,4) DEFAULT NULL,
            stock_quantity int(11) DEFAULT NULL,
            stock_status varchar(20) DEFAULT 'instock',
            attributes longtext NOT NULL,
            image bigint(20) unsigned DEFAULT NULL,
            enabled tinyint(1) NOT NULL DEFAULT 1,
            sort_order int(11) DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY sku (sku)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_product_relationships (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            related_product_id bigint(20) unsigned NOT NULL,
            relationship_type enum('related','crosssell','upsell') NOT NULL DEFAULT 'related',
            sort_order int(11) DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY product_related_type (product_id, related_product_id, relationship_type),
            KEY product_id (product_id),
            KEY related_product_id (related_product_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_product_addons (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned DEFAULT NULL,
            name varchar(255) NOT NULL,
            description text,
            type varchar(20) DEFAULT 'checkbox',
            price decimal(10,2) DEFAULT 0,
            price_type varchar(20) DEFAULT 'flat',
            options longtext,
            required tinyint(1) DEFAULT 0,
            max_length int DEFAULT 0,
            is_active tinyint(1) DEFAULT 1,
            sort_order int DEFAULT 0,
            PRIMARY KEY (id),
            KEY product_id (product_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_product_bundles (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text,
            products longtext,
            bundle_type varchar(20) DEFAULT 'fixed',
            discount_type varchar(20) DEFAULT 'percent',
            discount_value decimal(10,2) DEFAULT 0,
            min_products int DEFAULT 0,
            max_products int DEFAULT 0,
            is_active tinyint(1) DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY (id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_product_seo (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            meta_title varchar(255) DEFAULT NULL,
            meta_description text,
            focus_keyword varchar(255) DEFAULT NULL,
            og_title varchar(255) DEFAULT NULL,
            og_description text,
            og_image bigint(20) unsigned DEFAULT NULL,
            canonical_url varchar(500) DEFAULT NULL,
            noindex tinyint(1) NOT NULL DEFAULT 0,
            schema_data longtext,
            PRIMARY KEY (id),
            UNIQUE KEY product_id (product_id)
        ) {charset}",

        // ── Downloads & Digital ───────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_downloads (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            name varchar(255) NOT NULL,
            file_url text NOT NULL,
            file_type varchar(100) DEFAULT NULL,
            download_limit int(11) DEFAULT NULL,
            expiry_days int(11) DEFAULT NULL,
            sort_order int(11) DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_download_permissions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            download_id bigint(20) unsigned NOT NULL,
            order_id bigint(20) unsigned NOT NULL,
            customer_id bigint(20) unsigned NOT NULL,
            download_count int(11) DEFAULT 0,
            access_granted datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            access_expires datetime DEFAULT NULL,
            PRIMARY KEY (id),
            KEY download_id (download_id),
            KEY order_id (order_id),
            KEY customer_id (customer_id)
        ) {charset}",

        // ── Subscriptions & Bookings ──────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_subscriptions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            customer_id bigint(20) unsigned NOT NULL,
            product_id bigint(20) unsigned NOT NULL,
            status varchar(50) NOT NULL DEFAULT 'active',
            price decimal(19,4) NOT NULL,
            period varchar(20) NOT NULL DEFAULT 'month',
            interval_count int(11) NOT NULL DEFAULT 1,
            trial_end datetime DEFAULT NULL,
            current_period_start datetime NOT NULL,
            current_period_end datetime NOT NULL,
            cancel_at_period_end tinyint(1) DEFAULT 0,
            canceled_at datetime DEFAULT NULL,
            payment_method varchar(100) DEFAULT NULL,
            external_id varchar(255) DEFAULT NULL,
            meta longtext,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY customer_id (customer_id),
            KEY product_id (product_id),
            KEY status (status),
            KEY external_id (external_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_bookings (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            order_id bigint(20) unsigned DEFAULT NULL,
            customer_id bigint(20) unsigned NOT NULL,
            status varchar(50) NOT NULL DEFAULT 'pending',
            start_time datetime NOT NULL,
            end_time datetime NOT NULL,
            timezone varchar(100) DEFAULT 'UTC',
            customer_note text,
            admin_note text,
            meta longtext,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY order_id (order_id),
            KEY customer_id (customer_id),
            KEY status (status),
            KEY start_time (start_time)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_services (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text,
            duration int NOT NULL DEFAULT 60,
            buffer_before int NOT NULL DEFAULT 0,
            buffer_after int NOT NULL DEFAULT 15,
            price decimal(10,2) NOT NULL DEFAULT 0.00,
            capacity int NOT NULL DEFAULT 1,
            color varchar(7) NOT NULL DEFAULT '#555555',
            availability text,
            status enum('active','inactive') NOT NULL DEFAULT 'active',
            sort_order int NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status)
        ) {charset}",

        // ── Commerce features ─────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_coupons (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            code varchar(50) NOT NULL,
            description text,
            discount_type enum('percent','fixed_cart','fixed_product') NOT NULL DEFAULT 'percent',
            amount decimal(10,2) NOT NULL DEFAULT 0,
            shipping tinyint(1) NOT NULL DEFAULT 0,
            minimum_amount decimal(10,2) DEFAULT NULL,
            maximum_amount decimal(10,2) DEFAULT NULL,
            usage_limit int(11) DEFAULT NULL,
            usage_limit_per_user int(11) DEFAULT NULL,
            usage_count int(11) NOT NULL DEFAULT 0,
            product_ids text,
            excluded_product_ids text,
            category_ids text,
            start_date datetime DEFAULT NULL,
            end_date datetime DEFAULT NULL,
            status enum('active','inactive','expired') NOT NULL DEFAULT 'active',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY code (code),
            KEY status (status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_gift_cards (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            code varchar(50) NOT NULL,
            initial_balance decimal(19,4) NOT NULL,
            current_balance decimal(19,4) NOT NULL,
            currency varchar(3) NOT NULL DEFAULT 'USD',
            sender_name varchar(255) DEFAULT NULL,
            sender_email varchar(255) DEFAULT NULL,
            recipient_name varchar(255) DEFAULT NULL,
            recipient_email varchar(255) DEFAULT NULL,
            message text,
            design varchar(50) DEFAULT 'default',
            order_id bigint(20) unsigned DEFAULT NULL,
            is_delivered tinyint(1) NOT NULL DEFAULT 0,
            deliver_at datetime DEFAULT NULL,
            expires_at datetime DEFAULT NULL,
            status enum('active','used','expired','disabled') NOT NULL DEFAULT 'active',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY code (code),
            KEY recipient_email (recipient_email),
            KEY order_id (order_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_gift_card_transactions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            gift_card_id bigint(20) unsigned NOT NULL,
            order_id bigint(20) unsigned DEFAULT NULL,
            amount decimal(19,4) NOT NULL,
            balance_after decimal(19,4) NOT NULL,
            type enum('credit','debit','refund') NOT NULL,
            note varchar(255) DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY gift_card_id (gift_card_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_gift_rules (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            rule_type varchar(30) DEFAULT 'spend_threshold',
            trigger_amount decimal(10,2) DEFAULT 0,
            trigger_quantity int DEFAULT 0,
            trigger_products longtext,
            gift_products longtext,
            gift_quantity int DEFAULT 1,
            allow_choice tinyint(1) DEFAULT 0,
            priority int DEFAULT 10,
            is_active tinyint(1) DEFAULT 1,
            start_date datetime DEFAULT NULL,
            end_date datetime DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY is_active (is_active),
            KEY priority (priority)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_volume_discounts (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned DEFAULT NULL,
            category_id bigint(20) unsigned DEFAULT NULL,
            min_quantity int NOT NULL DEFAULT 2,
            max_quantity int DEFAULT NULL,
            discount_type varchar(20) DEFAULT 'percent',
            discount_value decimal(10,2) DEFAULT 0,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY category_id (category_id)
        ) {charset}",

        // ── Loyalty ───────────────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_loyalty_points (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            customer_id bigint(20) unsigned NOT NULL,
            points int(11) NOT NULL DEFAULT 0,
            lifetime_points int(11) NOT NULL DEFAULT 0,
            tier varchar(50) DEFAULT 'bronze',
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY customer_id (customer_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_loyalty_transactions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            customer_id bigint(20) unsigned NOT NULL,
            points int(11) NOT NULL,
            type enum('earn','redeem','expire','adjust','bonus') NOT NULL,
            source varchar(50) DEFAULT NULL,
            reference_id bigint(20) unsigned DEFAULT NULL,
            description varchar(255) DEFAULT NULL,
            expires_at datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY customer_id (customer_id),
            KEY type (type)
        ) {charset}",

        // ── Membership ────────────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_membership_tiers (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            slug varchar(255) NOT NULL,
            description text,
            price decimal(10,2) DEFAULT 0,
            billing_period varchar(20) DEFAULT 'monthly',
            discount_percent int DEFAULT 0,
            restricted_categories longtext,
            restricted_products longtext,
            benefits longtext,
            is_active tinyint(1) DEFAULT 1,
            sort_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY slug (slug)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_user_memberships (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            tier_id bigint(20) unsigned NOT NULL,
            status varchar(20) DEFAULT 'active',
            started_at datetime DEFAULT CURRENT_TIMESTAMP,
            expires_at datetime DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY user_id (user_id),
            KEY tier_id (tier_id),
            KEY status (status)
        ) {charset}",

        // ── Shipping & Tax ────────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_shipping_zones (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            countries text,
            regions text,
            postcodes text,
            sort_order int(11) DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_shipping_methods (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            zone_id bigint(20) unsigned NOT NULL,
            name varchar(255) NOT NULL,
            method_type enum('flat_rate','shipping','local_pickup') NOT NULL DEFAULT 'flat_rate',
            cost decimal(10,2) DEFAULT 0,
            min_amount decimal(10,2) DEFAULT NULL,
            enabled tinyint(1) NOT NULL DEFAULT 1,
            sort_order int(11) DEFAULT 0,
            settings longtext,
            PRIMARY KEY (id),
            KEY zone_id (zone_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_tax_rates (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            country varchar(2) NOT NULL,
            state varchar(100) DEFAULT '',
            postcode varchar(100) DEFAULT '',
            city varchar(100) DEFAULT '',
            rate decimal(8,4) NOT NULL,
            name varchar(255) NOT NULL,
            priority int(11) NOT NULL DEFAULT 1,
            compound tinyint(1) NOT NULL DEFAULT 0,
            shipping tinyint(1) NOT NULL DEFAULT 1,
            sort_order int(11) DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY country_state (country, state)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_currencies (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            code varchar(3) NOT NULL,
            name varchar(100) NOT NULL,
            symbol varchar(10) NOT NULL,
            exchange_rate decimal(15,6) NOT NULL DEFAULT 1.000000,
            decimals tinyint(1) NOT NULL DEFAULT 2,
            symbol_position enum('left','right','left_space','right_space') NOT NULL DEFAULT 'left',
            thousand_separator varchar(5) DEFAULT ',',
            decimal_separator varchar(5) DEFAULT '.',
            enabled tinyint(1) NOT NULL DEFAULT 1,
            is_default tinyint(1) NOT NULL DEFAULT 0,
            auto_update tinyint(1) NOT NULL DEFAULT 0,
            sort_order int(11) DEFAULT 0,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY code (code)
        ) {charset}",

        // ── RMA & Tracking ────────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_rma (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            rma_number varchar(50) NOT NULL,
            order_id bigint(20) unsigned NOT NULL,
            customer_id bigint(20) unsigned NOT NULL,
            reason enum('defective','wrong_item','not_as_described','changed_mind','other') NOT NULL,
            reason_detail text,
            status enum('pending','approved','rejected','shipped','received','refunded','completed') NOT NULL DEFAULT 'pending',
            refund_type enum('original','store_credit','exchange') DEFAULT 'original',
            refund_amount decimal(19,4) DEFAULT NULL,
            restocking_fee decimal(19,4) DEFAULT 0,
            return_label_url varchar(500) DEFAULT NULL,
            tracking_number varchar(100) DEFAULT NULL,
            admin_notes text,
            processed_by bigint(20) unsigned DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY rma_number (rma_number),
            KEY order_id (order_id),
            KEY customer_id (customer_id),
            KEY status (status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_rma_items (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            rma_id bigint(20) unsigned NOT NULL,
            order_item_id bigint(20) unsigned NOT NULL,
            product_id bigint(20) unsigned NOT NULL,
            quantity int(11) NOT NULL,
            condition_received enum('new','opened','damaged','missing') DEFAULT NULL,
            restock tinyint(1) NOT NULL DEFAULT 1,
            PRIMARY KEY (id),
            KEY rma_id (rma_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_order_tracking (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            carrier varchar(50) NOT NULL,
            tracking_number varchar(100) NOT NULL,
            tracking_url varchar(500) DEFAULT NULL,
            status varchar(50) DEFAULT 'in_transit',
            status_detail text,
            estimated_delivery date DEFAULT NULL,
            shipped_at datetime DEFAULT NULL,
            delivered_at datetime DEFAULT NULL,
            last_update datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id),
            KEY tracking_number (tracking_number)
        ) {charset}",

        // ── Vendors & Dropshipping ────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_vendors (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            store_name varchar(200) NOT NULL,
            store_slug varchar(200) NOT NULL,
            email varchar(200) NOT NULL,
            phone varchar(50) DEFAULT NULL,
            description text,
            logo_url varchar(500) DEFAULT NULL,
            banner_url varchar(500) DEFAULT NULL,
            address varchar(500) DEFAULT NULL,
            city varchar(100) DEFAULT NULL,
            state varchar(100) DEFAULT NULL,
            country varchar(2) DEFAULT NULL,
            postal_code varchar(20) DEFAULT NULL,
            commission_rate decimal(5,2) DEFAULT NULL,
            payment_method varchar(50) DEFAULT NULL,
            payment_email varchar(200) DEFAULT NULL,
            status enum('pending','active','suspended','rejected') NOT NULL DEFAULT 'pending',
            verified tinyint(1) NOT NULL DEFAULT 0,
            verified_at datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY user_id (user_id),
            UNIQUE KEY store_slug (store_slug),
            KEY idx_status (status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_vendor_settings (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            vendor_id bigint(20) unsigned NOT NULL,
            setting_key varchar(100) NOT NULL,
            setting_value longtext,
            PRIMARY KEY (id),
            UNIQUE KEY vendor_setting (vendor_id, setting_key)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_vendor_commissions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            order_item_id bigint(20) unsigned NOT NULL,
            vendor_id bigint(20) unsigned NOT NULL,
            product_id bigint(20) unsigned NOT NULL,
            order_subtotal decimal(10,2) NOT NULL,
            commission_rate decimal(5,2) NOT NULL,
            platform_fee decimal(10,2) NOT NULL,
            vendor_amount decimal(10,2) NOT NULL,
            status enum('pending','available','paid','refunded') NOT NULL DEFAULT 'pending',
            payout_id bigint(20) unsigned DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_order (order_id),
            KEY idx_vendor (vendor_id),
            KEY idx_status (status),
            KEY idx_payout (payout_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_vendor_payouts (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            vendor_id bigint(20) unsigned NOT NULL,
            amount decimal(10,2) NOT NULL,
            payment_method varchar(50) NOT NULL,
            payment_email varchar(200) DEFAULT NULL,
            payment_details text,
            status enum('pending','processing','completed','rejected') NOT NULL DEFAULT 'pending',
            transaction_id varchar(200) DEFAULT NULL,
            requested_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            processed_at datetime DEFAULT NULL,
            processed_by bigint(20) unsigned DEFAULT NULL,
            rejection_reason text,
            PRIMARY KEY (id),
            KEY idx_vendor (vendor_id),
            KEY idx_status (status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_suppliers (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            type enum('generic','aliexpress','printful','printify') NOT NULL DEFAULT 'generic',
            api_key varchar(500) DEFAULT NULL,
            api_secret varchar(500) DEFAULT NULL,
            store_id varchar(100) DEFAULT NULL,
            settings longtext,
            status enum('active','inactive') NOT NULL DEFAULT 'active',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_type (type),
            KEY idx_status (status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_supplier_products (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            supplier_id bigint(20) unsigned NOT NULL,
            supplier_sku varchar(200) NOT NULL,
            supplier_url varchar(1000) DEFAULT NULL,
            supplier_price decimal(10,2) NOT NULL,
            our_price decimal(10,2) NOT NULL,
            auto_fulfill tinyint(1) NOT NULL DEFAULT 1,
            last_synced datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY product_supplier (product_id, supplier_id),
            KEY idx_supplier (supplier_id),
            KEY idx_product (product_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_dropship_suppliers (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(200) NOT NULL,
            type enum('generic','aliexpress','alibaba','printful','printify','cjdropshipping','custom') NOT NULL DEFAULT 'generic',
            api_key varchar(500) DEFAULT NULL,
            api_secret varchar(500) DEFAULT NULL,
            store_id varchar(100) DEFAULT NULL,
            webhook_url varchar(500) DEFAULT NULL,
            settings longtext,
            auto_order tinyint(1) NOT NULL DEFAULT 0,
            auto_sync tinyint(1) NOT NULL DEFAULT 0,
            status enum('active','inactive') NOT NULL DEFAULT 'active',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_type (type),
            KEY idx_status (status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_dropship_products (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            supplier_id bigint(20) unsigned NOT NULL,
            supplier_product_id varchar(200) DEFAULT NULL,
            supplier_sku varchar(200) NOT NULL,
            supplier_url varchar(1000) DEFAULT NULL,
            cost_price decimal(10,2) NOT NULL DEFAULT 0,
            shipping_cost decimal(10,2) NOT NULL DEFAULT 0,
            processing_days int(11) NOT NULL DEFAULT 3,
            variant_mapping longtext,
            auto_order tinyint(1) NOT NULL DEFAULT 1,
            last_synced datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY product_supplier (product_id, supplier_id),
            KEY idx_supplier (supplier_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_dropship_fulfillments (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            supplier_id bigint(20) unsigned NOT NULL,
            items longtext,
            status varchar(50) NOT NULL DEFAULT 'pending',
            external_id varchar(255) DEFAULT NULL,
            tracking_number varchar(100) DEFAULT NULL,
            tracking_url varchar(500) DEFAULT NULL,
            cost decimal(10,2) DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_order (order_id),
            KEY idx_supplier (supplier_id),
            KEY idx_status (status)
        ) {charset}",

        // ── Analytics & Social Proof ──────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_purchase_patterns (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            related_product_id bigint(20) unsigned NOT NULL,
            purchase_count int(11) NOT NULL DEFAULT 1,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY product_pair (product_id, related_product_id),
            KEY idx_product (product_id),
            KEY idx_count (purchase_count)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_product_views (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            customer_id bigint(20) unsigned DEFAULT NULL,
            session_id varchar(64) NOT NULL,
            viewed_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_product (product_id),
            KEY idx_customer (customer_id),
            KEY idx_session (session_id),
            KEY idx_viewed (viewed_at)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_analytics_events (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            event_type varchar(50) NOT NULL,
            session_id varchar(64) NOT NULL,
            customer_id bigint(20) unsigned DEFAULT NULL,
            product_id bigint(20) unsigned DEFAULT NULL,
            order_id bigint(20) unsigned DEFAULT NULL,
            event_data longtext,
            page_url varchar(500) DEFAULT NULL,
            referrer varchar(500) DEFAULT NULL,
            utm_source varchar(100) DEFAULT NULL,
            utm_medium varchar(100) DEFAULT NULL,
            utm_campaign varchar(100) DEFAULT NULL,
            user_agent varchar(500) DEFAULT NULL,
            ip_address varchar(45) DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_event_type (event_type),
            KEY idx_session (session_id),
            KEY idx_customer (customer_id),
            KEY idx_product (product_id),
            KEY idx_created (created_at)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_visitor_sessions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            session_id varchar(64) NOT NULL,
            product_id bigint(20) unsigned DEFAULT NULL,
            page_type varchar(50) NOT NULL DEFAULT 'other',
            ip_address varchar(45) DEFAULT NULL,
            last_activity datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY session_product (session_id, product_id),
            KEY idx_product (product_id),
            KEY idx_activity (last_activity)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_cart_activity (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            session_id varchar(64) NOT NULL,
            quantity int(11) NOT NULL DEFAULT 1,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY product_session (product_id, session_id),
            KEY idx_product (product_id),
            KEY idx_updated (updated_at)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_abandoned_carts (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            session_id varchar(100) NOT NULL,
            email varchar(255) DEFAULT NULL,
            cart_data longtext NOT NULL,
            cart_total decimal(10,2) NOT NULL DEFAULT 0,
            billing_data longtext DEFAULT NULL,
            status enum('active','abandoned','recovered','converted') NOT NULL DEFAULT 'active',
            emails_sent tinyint(1) NOT NULL DEFAULT 0,
            last_email_at datetime DEFAULT NULL,
            recovered_order_id bigint(20) unsigned DEFAULT NULL,
            ip_address varchar(45) DEFAULT NULL,
            user_agent text DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            abandoned_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            KEY session_id (session_id),
            KEY email (email),
            KEY status (status),
            KEY abandoned_at (abandoned_at)
        ) {charset}",

        // ── Software Licensing ────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_licenses (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            license_key varchar(50) NOT NULL,
            addon varchar(100) NOT NULL,
            customer_id bigint unsigned DEFAULT 0,
            customer_email varchar(200) DEFAULT NULL,
            order_id bigint unsigned DEFAULT 0,
            activation_limit int unsigned DEFAULT 1,
            status enum('active','expired','suspended','revoked') DEFAULT 'active',
            expires_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 idx_customer (customer_id),
            KEY idx_order (order_id),
            KEY idx_addon (addon),
            KEY idx_status (status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_license_activations (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            license_id bigint unsigned NOT NULL,
            site_url varchar(500) NOT NULL,
            activated_at datetime DEFAULT CURRENT_TIMESTAMP,
            last_check datetime DEFAULT NULL,
            status enum('active','deactivated') DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_license (license_id),
            KEY idx_site (site_url(191)),
            UNIQUE KEY license_site (license_id, site_url(191))
        ) {charset}",

        // ── Chat ──────────────────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_chat_conversations (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            visitor_id varchar(64) NOT NULL,
            visitor_name varchar(100) DEFAULT '',
            visitor_email varchar(200) DEFAULT '',
            visitor_phone varchar(30) DEFAULT '',
            status enum('active','closed') DEFAULT 'active',
            assigned_agent_id bigint unsigned DEFAULT NULL,
            cart_data longtext DEFAULT NULL,
            page_url text DEFAULT NULL,
            ip_address varchar(45) DEFAULT NULL,
            handoff_at datetime DEFAULT NULL,
            handoff_status enum('none','pending','followup_sent','resolved') DEFAULT 'none',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_visitor (visitor_id),
            KEY idx_status (status),
            KEY idx_handoff (handoff_status)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_chat_messages (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            conversation_id bigint unsigned NOT NULL,
            sender enum('visitor','admin') NOT NULL,
            message text NOT NULL,
            is_read tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_conversation (conversation_id),
            KEY idx_read (is_read)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_chat_agents (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint unsigned NOT NULL,
            display_name varchar(100) DEFAULT '',
            avatar varchar(500) DEFAULT '',
            email varchar(200) DEFAULT '',
            sms_number varchar(30) DEFAULT '',
            whatsapp_number varchar(30) DEFAULT '',
            is_active tinyint(1) DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY user_id (user_id),
            KEY idx_user (user_id)
        ) {charset}",

        // ── SEO ───────────────────────────────────────────────────────────────

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_seo_pages (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            post_id bigint(20) unsigned NOT NULL,
            meta_title varchar(255) DEFAULT NULL,
            meta_description text,
            focus_keyword varchar(255) DEFAULT NULL,
            og_title varchar(255) DEFAULT NULL,
            og_description text,
            og_type varchar(50) NOT NULL DEFAULT 'website',
            og_image_url varchar(500) DEFAULT NULL,
            twitter_title varchar(255) DEFAULT NULL,
            twitter_description text,
            twitter_image_url varchar(500) DEFAULT NULL,
            canonical_url varchar(500) DEFAULT NULL,
            noindex tinyint(1) NOT NULL DEFAULT 0,
            nofollow tinyint(1) NOT NULL DEFAULT 0,
            schema_type varchar(50) NOT NULL DEFAULT 'auto',
            created_at datetime DEFAULT NULL,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY post_id (post_id),
            KEY noindex (noindex)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_seo_redirects (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            source_url varchar(500) NOT NULL,
            target_url varchar(500) NOT NULL,
            redirect_type smallint(3) NOT NULL DEFAULT 301,
            note varchar(255) DEFAULT NULL,
            is_active tinyint(1) NOT NULL DEFAULT 1,
            hit_count bigint(20) NOT NULL DEFAULT 0,
            last_hit datetime DEFAULT NULL,
            created_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            KEY source_url (source_url(191)),
            KEY is_active (is_active)
        ) {charset}",

        // ── Cleanup: removed tables (idempotent) ──────────────────────────────

        "DROP TABLE IF EXISTS {prefix}instinctor_wishlist_items",
        "DROP TABLE IF EXISTS {prefix}instinctor_wishlists",
        "DROP TABLE IF EXISTS {prefix}instinctor_wholesale_applications",
        "DROP TABLE IF EXISTS {prefix}instinctor_wholesale_customers",
        "DROP TABLE IF EXISTS {prefix}instinctor_quotes",
        "DROP TABLE IF EXISTS {prefix}instinctor_quote_items",
        "DROP TABLE IF EXISTS {prefix}instinctor_quantity_pricing",
        "DROP TABLE IF EXISTS {prefix}instinctor_customer_pricing",

    ],

    'down' => [
        // Reverse order — drop in dependency-safe sequence
        "DROP TABLE IF EXISTS {prefix}instinctor_seo_redirects",
        "DROP TABLE IF EXISTS {prefix}instinctor_seo_pages",
        "DROP TABLE IF EXISTS {prefix}instinctor_chat_agents",
        "DROP TABLE IF EXISTS {prefix}instinctor_chat_messages",
        "DROP TABLE IF EXISTS {prefix}instinctor_chat_conversations",
        "DROP TABLE IF EXISTS {prefix}instinctor_license_activations",
        "DROP TABLE IF EXISTS {prefix}instinctor_licenses",
        "DROP TABLE IF EXISTS {prefix}instinctor_abandoned_carts",
        "DROP TABLE IF EXISTS {prefix}instinctor_cart_activity",
        "DROP TABLE IF EXISTS {prefix}instinctor_visitor_sessions",
        "DROP TABLE IF EXISTS {prefix}instinctor_analytics_events",
        "DROP TABLE IF EXISTS {prefix}instinctor_product_views",
        "DROP TABLE IF EXISTS {prefix}instinctor_purchase_patterns",
        "DROP TABLE IF EXISTS {prefix}instinctor_dropship_fulfillments",
        "DROP TABLE IF EXISTS {prefix}instinctor_dropship_products",
        "DROP TABLE IF EXISTS {prefix}instinctor_dropship_suppliers",
        "DROP TABLE IF EXISTS {prefix}instinctor_supplier_products",
        "DROP TABLE IF EXISTS {prefix}instinctor_suppliers",
        "DROP TABLE IF EXISTS {prefix}instinctor_vendor_payouts",
        "DROP TABLE IF EXISTS {prefix}instinctor_vendor_commissions",
        "DROP TABLE IF EXISTS {prefix}instinctor_vendor_settings",
        "DROP TABLE IF EXISTS {prefix}instinctor_vendors",
        "DROP TABLE IF EXISTS {prefix}instinctor_order_tracking",
        "DROP TABLE IF EXISTS {prefix}instinctor_rma_items",
        "DROP TABLE IF EXISTS {prefix}instinctor_rma",
        "DROP TABLE IF EXISTS {prefix}instinctor_currencies",
        "DROP TABLE IF EXISTS {prefix}instinctor_tax_rates",
        "DROP TABLE IF EXISTS {prefix}instinctor_shipping_methods",
        "DROP TABLE IF EXISTS {prefix}instinctor_shipping_zones",
        "DROP TABLE IF EXISTS {prefix}instinctor_user_memberships",
        "DROP TABLE IF EXISTS {prefix}instinctor_membership_tiers",
        "DROP TABLE IF EXISTS {prefix}instinctor_loyalty_transactions",
        "DROP TABLE IF EXISTS {prefix}instinctor_loyalty_points",
        "DROP TABLE IF EXISTS {prefix}instinctor_volume_discounts",
        "DROP TABLE IF EXISTS {prefix}instinctor_gift_rules",
        "DROP TABLE IF EXISTS {prefix}instinctor_gift_card_transactions",
        "DROP TABLE IF EXISTS {prefix}instinctor_gift_cards",
        "DROP TABLE IF EXISTS {prefix}instinctor_coupons",
        "DROP TABLE IF EXISTS {prefix}instinctor_services",
        "DROP TABLE IF EXISTS {prefix}instinctor_bookings",
        "DROP TABLE IF EXISTS {prefix}instinctor_subscriptions",
        "DROP TABLE IF EXISTS {prefix}instinctor_download_permissions",
        "DROP TABLE IF EXISTS {prefix}instinctor_downloads",
        "DROP TABLE IF EXISTS {prefix}instinctor_product_seo",
        "DROP TABLE IF EXISTS {prefix}instinctor_product_bundles",
        "DROP TABLE IF EXISTS {prefix}instinctor_product_addons",
        "DROP TABLE IF EXISTS {prefix}instinctor_product_relationships",
        "DROP TABLE IF EXISTS {prefix}instinctor_product_variations",
        "DROP TABLE IF EXISTS {prefix}instinctor_refunds",
        "DROP TABLE IF EXISTS {prefix}instinctor_order_notes",
        "DROP TABLE IF EXISTS {prefix}instinctor_order_items",
        "DROP TABLE IF EXISTS {prefix}instinctor_orders",
        "DROP TABLE IF EXISTS {prefix}instinctor_customers",
        "DROP TABLE IF EXISTS {prefix}instinctor_product_categories",
        "DROP TABLE IF EXISTS {prefix}instinctor_categories",
        "DROP TABLE IF EXISTS {prefix}instinctor_products",
    ],
];
