<?php
/**
 * TitanCart Schema — All table definitions.
 *
 * Each method returns the CREATE TABLE SQL for one table.
 * The Installer calls Schema::all() to get every definition
 * and runs them through dbDelta().
 *
 * Conventions:
 *  - All PKs: BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
 *  - Status:  ENUM (not single-char flags)
 *  - Dates:   DATETIME (not Unix timestamps)
 *  - Blobs:   JSON (not serialized PHP)
 *  - FKs:     indexed, no FK constraints (WordPress convention)
 *  - Prefix:  wp_tc_  (wpdb->prefix . 'tc_')
 *
 * @package TitanCart\DB
 */

namespace TitanCart\DB;

defined( 'ABSPATH' ) || exit;

class Schema {

    /**
     * Return all CREATE TABLE statements as an array.
     * Called by Installer::activate() via dbDelta().
     *
     * @return array<string> SQL strings.
     */
    public static function all(): array {
        global $wpdb;
        $p       = $wpdb->prefix . TITANCART_TABLE_PREFIX;   // e.g. wp_tc_
        $charset = $wpdb->get_charset_collate();

        return [
            // ── MODULE 1: Foundation ──────────────────────────────────────────
            self::settings( $p, $charset ),
            self::currencies( $p, $charset ),
            self::languages( $p, $charset ),
            self::sessions( $p, $charset ),
            self::jobs( $p, $charset ),
            self::logs( $p, $charset ),

            // ── MODULE 2: Products + Categories ──────────────────────────────
            self::categories( $p, $charset ),
            self::category_translations( $p, $charset ),
            self::brands( $p, $charset ),
            self::brand_translations( $p, $charset ),
            self::products( $p, $charset ),
            self::product_translations( $p, $charset ),
            self::product_prices( $p, $charset ),
            self::product_categories( $p, $charset ),
            self::tags( $p, $charset ),
            self::product_tags( $p, $charset ),
            self::product_images( $p, $charset ),
            self::product_features( $p, $charset ),
            self::product_feature_translations( $p, $charset ),
            self::product_feature_values( $p, $charset ),
            self::product_feature_variants( $p, $charset ),
            self::product_feature_variant_translations( $p, $charset ),
            self::product_feature_categories( $p, $charset ),
            self::product_options( $p, $charset ),
            self::product_option_translations( $p, $charset ),
            self::product_option_variants( $p, $charset ),
            self::product_option_variant_translations( $p, $charset ),
            self::product_option_exceptions( $p, $charset ),
            self::product_option_dependencies( $p, $charset ),
            self::product_variation_links( $p, $charset ),
            self::product_groups( $p, $charset ),
            self::product_group_products( $p, $charset ),
            self::product_waitlist( $p, $charset ),
            self::product_files( $p, $charset ),
            self::product_tabs( $p, $charset ),
            self::product_tab_translations( $p, $charset ),
            self::product_tab_overrides( $p, $charset ),
            self::product_sizing_charts( $p, $charset ),
            self::product_filters( $p, $charset ),
            self::product_filter_translations( $p, $charset ),
            self::product_filter_categories( $p, $charset ),
            self::product_bundles( $p, $charset ),
            self::product_bundle_translations( $p, $charset ),
            self::product_bundle_items( $p, $charset ),

            // ── MODULE 3: Cart ────────────────────────────────────────────────
            self::carts( $p, $charset ),
            self::cart_items( $p, $charset ),

            // ── MODULE 4: Checkout ────────────────────────────────────────────
            self::checkout_sessions( $p, $charset ),

            // ── MODULE 5: Orders + Email ──────────────────────────────────────
            self::orders( $p, $charset ),
            self::order_items( $p, $charset ),
            self::order_item_options( $p, $charset ),
            self::order_statuses( $p, $charset ),
            self::order_status_translations( $p, $charset ),
            self::order_notes( $p, $charset ),
            self::order_history( $p, $charset ),
            self::email_templates( $p, $charset ),
            self::email_template_translations( $p, $charset ),

            // ── MODULE 6: Payments ────────────────────────────────────────────
            self::payment_methods( $p, $charset ),
            self::payment_method_translations( $p, $charset ),
            self::payment_transactions( $p, $charset ),

            // ── MODULE 7: Shipping ────────────────────────────────────────────
            self::shipping_methods( $p, $charset ),
            self::shipping_method_translations( $p, $charset ),
            self::shipping_zones( $p, $charset ),
            self::shipping_zone_locations( $p, $charset ),
            self::shipping_rates( $p, $charset ),
            self::shipping_rate_conditions( $p, $charset ),
            self::shipments( $p, $charset ),
            self::shipment_items( $p, $charset ),

            // ── MODULE 8: Tax ─────────────────────────────────────────────────
            self::tax_classes( $p, $charset ),
            self::tax_class_translations( $p, $charset ),
            self::tax_zones( $p, $charset ),
            self::tax_zone_locations( $p, $charset ),
            self::tax_rates( $p, $charset ),

            // ── MODULE 9: Promotions ──────────────────────────────────────────
            self::promotions( $p, $charset ),
            self::promotion_translations( $p, $charset ),
            self::promotion_conditions( $p, $charset ),
            self::promotion_bonuses( $p, $charset ),
            self::coupon_codes( $p, $charset ),
            self::coupon_usage( $p, $charset ),
            self::gift_cards( $p, $charset ),
            self::gift_card_log( $p, $charset ),
            self::reward_point_balances( $p, $charset ),
            self::reward_point_log( $p, $charset ),
            self::reward_point_rules( $p, $charset ),

            // ── MODULE 10: Customers ──────────────────────────────────────────
            self::customers( $p, $charset ),
            self::customer_addresses( $p, $charset ),
            self::wishlists( $p, $charset ),
            self::wishlist_items( $p, $charset ),
            self::usergroups( $p, $charset ),
            self::usergroup_translations( $p, $charset ),
            self::customer_usergroups( $p, $charset ),
            self::customer_fields( $p, $charset ),
            self::customer_field_values( $p, $charset ),

            // ── MODULE 11: Reviews ────────────────────────────────────────────
            self::reviews( $p, $charset ),
            self::review_votes( $p, $charset ),

            // ── Comments & Discussions ───────────────────────────────────────
            self::comments( $p, $charset ),

            // ── SEO ──────────────────────────────────────────────────────────
            self::seo_redirects( $p, $charset ),

            // ── MODULE 12: Reports ────────────────────────────────────────────
            self::report_snapshots( $p, $charset ),

            // ── MODULE 13: Import/Export ──────────────────────────────────────
            self::import_jobs( $p, $charset ),
            self::export_jobs( $p, $charset ),
            self::import_presets( $p, $charset ),

            // ── MODULE 14: API ────────────────────────────────────────────────
            // ── Social Login ──────────────────────────────────────────────
            self::social_providers( $p, $charset ),

            // ── MODULE 14: API ────────────────────────────────────────────────
            self::api_keys( $p, $charset ),
            self::api_logs( $p, $charset ),
            self::webhooks( $p, $charset ),
            self::webhook_logs( $p, $charset ),

            // ── Foundation: Countries + States ───────────────────────────────
            self::countries( $p, $charset ),
            self::states( $p, $charset ),

            // ── Notifications ─────────────────────────────────────────────
            self::notification_settings( $p, $charset ),

            // ── Newsletters / Email Campaigns ────────────────────────────────
            self::subscribers( $p, $charset ),
            self::subscriber_lists( $p, $charset ),
            self::subscriber_list_members( $p, $charset ),
            self::newsletters( $p, $charset ),
            self::newsletter_tracking_campaigns( $p, $charset ),
            self::newsletter_sends( $p, $charset ),

            // ── Applications ─────────────────────────────────────────────────
            self::todos( $p, $charset ),
            self::calendar_events( $p, $charset ),

            // ── Notifications (admin bell — KI-26) ───────────────────────────
            self::notifications( $p, $charset ),

            // ── RMA (Returns) ────────────────────────────────────────────────
            self::returns( $p, $charset ),
            self::return_items( $p, $charset ),
            self::return_reasons( $p, $charset ),
            self::return_history( $p, $charset ),

            // ── Downloadable Products (Phase 1 — schema only) ────────────────
            self::downloadable_files( $p, $charset ),
            self::downloadable_keys( $p, $charset ),
            self::downloadable_log( $p, $charset ),
            self::downloadable_agreements( $p, $charset ),
            self::downloadable_acceptances( $p, $charset ),
        ];
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 1 — Foundation
    // ══════════════════════════════════════════════════════════════════════════

    private static function settings( string $p, string $charset ): string {
        return "CREATE TABLE {$p}settings (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            section     VARCHAR(64)  NOT NULL DEFAULT 'general',
            `key`       VARCHAR(128) NOT NULL,
            value       LONGTEXT     NULL,
            autoload    TINYINT(1)   NOT NULL DEFAULT 1,
            updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_section_key (section, `key`),
            KEY idx_autoload (autoload)
        ) {$charset};";
    }

    private static function currencies( string $p, string $charset ): string {
        return "CREATE TABLE {$p}currencies (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            code          CHAR(3)     NOT NULL,
            symbol        VARCHAR(8)  NOT NULL DEFAULT '',
            symbol_after  TINYINT(1)  NOT NULL DEFAULT 0,
            name          VARCHAR(64) NOT NULL,
            decimals      TINYINT     NOT NULL DEFAULT 2,
            thousands_sep VARCHAR(4)  NOT NULL DEFAULT ',',
            decimal_sep   VARCHAR(4)  NOT NULL DEFAULT '.',
            exchange_rate DECIMAL(12,6) NOT NULL DEFAULT 1.000000,
            is_primary    TINYINT(1)  NOT NULL DEFAULT 0,
            position      INT UNSIGNED NOT NULL DEFAULT 0,
            status        ENUM('active','disabled') NOT NULL DEFAULT 'active',
            updated_at    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_code (code)
        ) {$charset};";
    }

    private static function languages( string $p, string $charset ): string {
        return "CREATE TABLE {$p}languages (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            code        CHAR(2)     NOT NULL,
            name        VARCHAR(64) NOT NULL,
            native_name VARCHAR(64) NOT NULL DEFAULT '',
            is_default  TINYINT(1)  NOT NULL DEFAULT 0,
            status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            UNIQUE KEY uq_code (code)
        ) {$charset};";
    }

    private static function countries( string $p, string $charset ): string {
        return "CREATE TABLE {$p}countries (
            code   CHAR(2)      NOT NULL,
            code3  CHAR(3)      NOT NULL DEFAULT '',
            name   VARCHAR(128) NOT NULL,
            status ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (code),
            KEY idx_status (status)
        ) {$charset};";
    }

    private static function states( string $p, string $charset ): string {
        return "CREATE TABLE {$p}states (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            country_code CHAR(2)      NOT NULL,
            code         VARCHAR(10)  NOT NULL,
            name         VARCHAR(128) NOT NULL,
            status       ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_country_code (country_code),
            UNIQUE KEY uq_country_state (country_code, code)
        ) {$charset};";
    }

    private static function sessions( string $p, string $charset ): string {
        return "CREATE TABLE {$p}sessions (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            session_key  CHAR(64)    NOT NULL,
            customer_id  BIGINT UNSIGNED NULL,
            data         LONGTEXT    NOT NULL,
            expires_at   DATETIME    NOT NULL,
            created_at   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_session_key (session_key),
            KEY idx_customer_id (customer_id),
            KEY idx_expires_at  (expires_at)
        ) {$charset};";
    }

    private static function jobs( string $p, string $charset ): string {
        return "CREATE TABLE {$p}jobs (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            queue        VARCHAR(64)  NOT NULL DEFAULT 'default',
            job_class    VARCHAR(255) NOT NULL,
            payload      LONGTEXT     NOT NULL,
            attempts     TINYINT UNSIGNED NOT NULL DEFAULT 0,
            status       ENUM('pending','running','completed','failed') NOT NULL DEFAULT 'pending',
            available_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            started_at   DATETIME     NULL,
            completed_at DATETIME     NULL,
            error        TEXT         NULL,
            created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_queue_status (queue, status),
            KEY idx_available_at (available_at)
        ) {$charset};";
    }

    private static function logs( string $p, string $charset ): string {
        return "CREATE TABLE {$p}logs (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            level       ENUM('debug','info','notice','warning','error','critical') NOT NULL DEFAULT 'info',
            channel     VARCHAR(64)  NOT NULL DEFAULT 'system',
            action      VARCHAR(64)  NOT NULL DEFAULT '',
            message     TEXT         NOT NULL,
            context     JSON         NULL,
            object_type VARCHAR(64)  NOT NULL DEFAULT '',
            object_id   BIGINT UNSIGNED NOT NULL DEFAULT 0,
            user_id     BIGINT UNSIGNED NOT NULL DEFAULT 0,
            ip_address  VARCHAR(45)  NOT NULL DEFAULT '',
            user_agent  VARCHAR(512) NOT NULL DEFAULT '',
            created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_level        (level),
            KEY idx_channel      (channel),
            KEY idx_action       (action),
            KEY idx_user_id      (user_id),
            KEY idx_object       (object_type, object_id),
            KEY idx_created_at   (created_at)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 2 — Products + Categories
    // ══════════════════════════════════════════════════════════════════════════

    private static function categories( string $p, string $charset ): string {
        return "CREATE TABLE {$p}categories (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            parent_id    BIGINT UNSIGNED NOT NULL DEFAULT 0,
            lft          INT UNSIGNED    NOT NULL DEFAULT 0,
            rgt          INT UNSIGNED    NOT NULL DEFAULT 0,
            depth        TINYINT UNSIGNED NOT NULL DEFAULT 0,
            slug         VARCHAR(255)    NOT NULL DEFAULT '',
            image_id     BIGINT UNSIGNED NULL,
            position     INT UNSIGNED    NOT NULL DEFAULT 0,
            status       ENUM('active','disabled','hidden') NOT NULL DEFAULT 'active',
            age_restricted TINYINT(1)       NOT NULL DEFAULT 0,
            age_limit    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            deleted_at   DATETIME        NULL,
            PRIMARY KEY (id),
            KEY idx_parent_id (parent_id),
            KEY idx_lft_rgt   (lft, rgt),
            KEY idx_slug      (slug(191)),
            KEY idx_status    (status)
        ) {$charset};";
    }

    private static function category_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}category_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            category_id   BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            description   LONGTEXT        NULL,
            meta_title    VARCHAR(255)    NULL,
            meta_desc     VARCHAR(512)    NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_cat_lang (category_id, language_code),
            KEY idx_language_code (language_code)
        ) {$charset};";
    }

    private static function brands( string $p, string $charset ): string {
        return "CREATE TABLE {$p}brands (
            id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            slug       VARCHAR(255)   NOT NULL DEFAULT '',
            image_id   BIGINT UNSIGNED NULL,
            position   INT UNSIGNED   NOT NULL DEFAULT 0,
            status     ENUM('active','disabled') NOT NULL DEFAULT 'active',
            created_at DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_slug   (slug(191)),
            KEY idx_status (status)
        ) {$charset};";
    }

    private static function brand_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}brand_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            brand_id      BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            description   TEXT            NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_brand_lang (brand_id, language_code)
        ) {$charset};";
    }

    private static function products( string $p, string $charset ): string {
        return "CREATE TABLE {$p}products (
            id                  BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            parent_id           BIGINT UNSIGNED  NOT NULL DEFAULT 0,
            brand_id            BIGINT UNSIGNED  NULL,
            sku                 VARCHAR(64)      NOT NULL DEFAULT '',
            slug                VARCHAR(255)     NOT NULL DEFAULT '',
            price               DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            zero_price_action   ENUM('do_not_allow','allow','ask') NOT NULL DEFAULT 'do_not_allow',
            list_price          DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            cost_price          DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            weight              DECIMAL(10,4)   NOT NULL DEFAULT 0.0000,
            weight_unit         ENUM('kg','g','lb','oz') NOT NULL DEFAULT 'kg',
            unit_name           VARCHAR(64)      NOT NULL DEFAULT '',
            units_in_product    DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            show_price_per      DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            length              DECIMAL(10,4)   NULL,
            width               DECIMAL(10,4)   NULL,
            height              DECIMAL(10,4)   NULL,
            dimension_unit      ENUM('cm','mm','in') NOT NULL DEFAULT 'cm',
            stock_quantity      INT              NOT NULL DEFAULT 0,
            stock_status        ENUM('in_stock','out_of_stock','on_backorder') NOT NULL DEFAULT 'in_stock',
            out_of_stock_actions ENUM('none','buy','subscribe') NOT NULL DEFAULT 'none',
            track_inventory     TINYINT(1)       NOT NULL DEFAULT 1,
            allow_backorders    TINYINT(1)       NOT NULL DEFAULT 0,
            min_quantity        INT UNSIGNED     NOT NULL DEFAULT 1,
            max_quantity        INT UNSIGNED     NULL,
            quantity_step       INT UNSIGNED     NOT NULL DEFAULT 1,
            tax_class_id        BIGINT UNSIGNED  NULL,
            shipping_freight    DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            free_shipping       TINYINT(1)       NOT NULL DEFAULT 0,
            age_restricted      TINYINT(1)       NOT NULL DEFAULT 0,
            age_limit           SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            is_downloadable     TINYINT(1)       NOT NULL DEFAULT 0,
            download_ttl_hours  INT              NULL,
            download_max_count  INT              NULL,
            subscriptions_only  TINYINT(1)       NOT NULL DEFAULT 0,
            rp_allow_payment    TINYINT(1)       NOT NULL DEFAULT 0,
            rp_override_per     TINYINT(1)       NOT NULL DEFAULT 0,
            rp_price            INT UNSIGNED     NOT NULL DEFAULT 0,
            rp_override_earn    TINYINT(1)       NOT NULL DEFAULT 0,
            rp_earn_all         INT UNSIGNED     NOT NULL DEFAULT 0,
            rp_earn_all_type    ENUM('absolute','percent') NOT NULL DEFAULT 'absolute',
            rp_earn_guest       INT UNSIGNED     NOT NULL DEFAULT 0,
            rp_earn_guest_type  ENUM('absolute','percent') NOT NULL DEFAULT 'absolute',
            rp_earn_reg         INT UNSIGNED     NOT NULL DEFAULT 0,
            rp_earn_reg_type    ENUM('absolute','percent') NOT NULL DEFAULT 'absolute',
            requires_shipping   TINYINT(1)       NOT NULL DEFAULT 1,
            type                ENUM('simple','variable','downloadable','virtual') NOT NULL DEFAULT 'simple',
            options_type        ENUM('P','S','N') NOT NULL DEFAULT 'P',
            exceptions_type     ENUM('F','A')    NOT NULL DEFAULT 'F',
            status              ENUM('active','disabled','hidden') NOT NULL DEFAULT 'active',
            visibility          ENUM('visible','catalog','search','hidden') NOT NULL DEFAULT 'visible',
            featured            TINYINT(1)       NOT NULL DEFAULT 0,
            position            INT UNSIGNED     NOT NULL DEFAULT 0,
            rating_avg          DECIMAL(3,2)     NOT NULL DEFAULT 0.00,
            rating_count        INT UNSIGNED     NOT NULL DEFAULT 0,
            sales_count         INT UNSIGNED     NOT NULL DEFAULT 0,
            views_count         INT UNSIGNED     NOT NULL DEFAULT 0,
            details_view        VARCHAR(20)      NOT NULL DEFAULT '',
            search_words        TEXT             NULL,
            promo_text          TEXT             NULL,
            videos              JSON             NULL,
            created_at          DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            avail_since         DATETIME         NULL,
            updated_at          DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            deleted_at          DATETIME         NULL,
            PRIMARY KEY (id),
            KEY idx_parent_id    (parent_id),
            KEY idx_brand_id     (brand_id),
            KEY idx_sku          (sku),
            KEY idx_slug         (slug(191)),
            KEY idx_status       (status),
            KEY idx_type         (type),
            KEY idx_featured     (featured),
            KEY idx_stock_status (stock_status),
            KEY idx_created_at   (created_at)
        ) {$charset};";
    }

    private static function product_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_translations (
            id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id     BIGINT UNSIGNED NOT NULL,
            language_code  CHAR(2)         NOT NULL,
            name           VARCHAR(255)    NOT NULL DEFAULT '',
            description    LONGTEXT        NULL,
            short_desc     TEXT            NULL,
            meta_title     VARCHAR(255)    NULL,
            meta_desc      VARCHAR(512)    NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_prod_lang (product_id, language_code),
            KEY idx_language_code (language_code)
        ) {$charset};";
    }

    private static function product_prices( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_prices (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id      BIGINT UNSIGNED NOT NULL,
            usergroup_id    BIGINT UNSIGNED NOT NULL DEFAULT 0,
            min_quantity    INT UNSIGNED    NOT NULL DEFAULT 1,
            price           DECIMAL(12,4)  NOT NULL,
            discount_type   ENUM('absolute','percent') NOT NULL DEFAULT 'absolute',
            discount_value  DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            PRIMARY KEY (id),
            KEY idx_product_id   (product_id),
            KEY idx_usergroup_id (usergroup_id)
        ) {$charset};";
    }

    private static function product_categories( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_categories (
            product_id   BIGINT UNSIGNED NOT NULL,
            category_id  BIGINT UNSIGNED NOT NULL,
            is_main      TINYINT(1)      NOT NULL DEFAULT 0,
            position     INT UNSIGNED    NOT NULL DEFAULT 0,
            PRIMARY KEY (product_id, category_id),
            KEY idx_category_id (category_id)
        ) {$charset};";
    }

    private static function tags( string $p, string $charset ): string {
        return "CREATE TABLE {$p}tags (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name          VARCHAR(128)    NOT NULL,
            slug          VARCHAR(128)    NOT NULL,
            product_count INT UNSIGNED    NOT NULL DEFAULT 0,
            created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_slug (slug),
            KEY idx_name (name)
        ) {$charset};";
    }

    private static function product_tags( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_tags (
            product_id  BIGINT UNSIGNED NOT NULL,
            tag_id      BIGINT UNSIGNED NOT NULL,
            PRIMARY KEY (product_id, tag_id),
            KEY idx_tag_id (tag_id)
        ) {$charset};";
    }

    private static function product_images( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_images (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id  BIGINT UNSIGNED NOT NULL,
            wp_attachment_id BIGINT UNSIGNED NOT NULL,
            position    INT UNSIGNED    NOT NULL DEFAULT 0,
            is_main     TINYINT(1)      NOT NULL DEFAULT 0,
            alt_text    VARCHAR(255)    NULL,
            PRIMARY KEY (id),
            KEY idx_product_id        (product_id),
            KEY idx_wp_attachment_id  (wp_attachment_id)
        ) {$charset};";
    }

    private static function product_features( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_features (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            parent_id    BIGINT UNSIGNED NOT NULL DEFAULT 0,
            type         ENUM('G','C','M','S','N','E','T','O','D','I') NOT NULL DEFAULT 'T',
            filter_type  ENUM('checkbox','slider','color','range','none') NOT NULL DEFAULT 'checkbox',
            purpose      ENUM('filter','variation_split','variation_shared','brand','info') NOT NULL DEFAULT 'filter',
            feature_code VARCHAR(64) NOT NULL DEFAULT '',
            position     INT UNSIGNED    NOT NULL DEFAULT 0,
            filterable   TINYINT(1)      NOT NULL DEFAULT 0,
            compareable  TINYINT(1)      NOT NULL DEFAULT 0,
            show_on_pdp  TINYINT(1)      NOT NULL DEFAULT 1,
            sort_mode    VARCHAR(10)     NOT NULL DEFAULT 'manual',
            status       ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_parent_id (parent_id),
            KEY idx_type      (type)
        ) {$charset};";
        // sort_mode = how this attribute's choices are ordered wherever they are listed
        //   'manual' (default, position column) | 'alpha' (name A–Z, collation-aware).
        //   VARCHAR not ENUM so dbDelta can manage it; see Modules\Products\FeatureValueOrder.
        // type (Display Type) = how the value is entered/shown on the product page
        //   G=Group C=Yes/No M=MultiCheckbox S=Select N=NumSelect
        //   E=Color swatch T=TextField O=NumField D=Date
        // filter_type = how this attribute appears on product cards in category/search listings
        //   checkbox, slider, color (swatches), range (min-max), none
        // purpose = what role this attribute plays
        //   filter, variation_split (separate SKUs), variation_shared (shared SKU), brand, info
        // show_on_pdp = whether this attribute renders on the product detail page
        //   (specs table + visual-indicator swatches). Default 1 (visible). Set 0 to keep
        //   a pure-filter attribute (e.g. Color Family) out of the PDP entirely.
    }

    private static function product_feature_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_feature_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            feature_id    BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            description   TEXT            NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_feat_lang (feature_id, language_code)
        ) {$charset};";
    }

    private static function product_feature_values( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_feature_values (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id   BIGINT UNSIGNED NOT NULL,
            feature_id   BIGINT UNSIGNED NOT NULL,
            value_int    INT             NULL,
            value_float  DECIMAL(12,4)  NULL,
            value_str    VARCHAR(255)   NULL,
            value_text   TEXT           NULL,
            value_date   DATE           NULL,
            variant_id   BIGINT UNSIGNED NULL,
            image_id     BIGINT UNSIGNED NULL COMMENT 'Product image linked to this variant value for image-type display',
            PRIMARY KEY (id),
            KEY idx_product_id  (product_id),
            KEY idx_feature_id  (feature_id),
            KEY idx_variant_id  (variant_id)
        ) {$charset};";
    }

    private static function product_feature_variants( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_feature_variants (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            feature_id  BIGINT UNSIGNED NOT NULL,
            color_hex   VARCHAR(7)      NULL COMMENT 'Optional hex color for swatch display, e.g. #FF0000',
            position    INT UNSIGNED    NOT NULL DEFAULT 0,
            status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_feature_id (feature_id)
        ) {$charset};";
    }

    private static function product_feature_variant_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_feature_variant_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            variant_id    BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            UNIQUE KEY uq_fvar_lang (variant_id, language_code)
        ) {$charset};";
    }

    private static function product_feature_categories( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_feature_categories (
            feature_id   BIGINT UNSIGNED NOT NULL,
            category_id  BIGINT UNSIGNED NOT NULL,
            PRIMARY KEY (feature_id, category_id),
            KEY idx_category_id (category_id)
        ) {$charset};";
    }

    private static function product_filters( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_filters (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            filter_by    VARCHAR(64) NOT NULL DEFAULT '',
            display_type ENUM('checkbox','slider','color','range','size_pills') NOT NULL DEFAULT 'checkbox',
            round_to     DECIMAL(10,2) NOT NULL DEFAULT 0,
            position     INT UNSIGNED NOT NULL DEFAULT 0,
            start_expanded TINYINT(1) NOT NULL DEFAULT 0,
            status       ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_filter_by (filter_by)
        ) {$charset};";
        // filter_by: 'price', 'in_stock', 'free_shipping', 'rating' for built-in fields
        //            'feature:123' for attribute-based filters (feature ID)
    }

    private static function product_filter_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_filter_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            filter_id     BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2) NOT NULL,
            name          VARCHAR(255) NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            UNIQUE KEY uq_filter_lang (filter_id, language_code)
        ) {$charset};";
    }

    private static function product_filter_categories( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_filter_categories (
            filter_id    BIGINT UNSIGNED NOT NULL,
            category_id  BIGINT UNSIGNED NOT NULL,
            PRIMARY KEY (filter_id, category_id),
            KEY idx_category_id (category_id)
        ) {$charset};";
    }

    /**
     * Product bundles — "Buy Together" deals.
     * A bundle groups products together at a discount.
     */
    private static function product_bundles( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_bundles (
            id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            status     ENUM('active','disabled') NOT NULL DEFAULT 'active',
            date_from  DATETIME        NULL,
            date_to    DATETIME        NULL,
            created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) {$charset};";
    }

    private static function product_bundle_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_bundle_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            bundle_id     BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            description   TEXT            NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_bundle_lang (bundle_id, language_code)
        ) {$charset};";
    }

    /**
     * Products included in a bundle with per-item discount.
     */
    private static function product_bundle_items( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_bundle_items (
            id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            bundle_id      BIGINT UNSIGNED NOT NULL,
            product_id     BIGINT UNSIGNED NOT NULL,
            quantity       INT UNSIGNED    NOT NULL DEFAULT 1,
            discount_type  ENUM('none','percent','fixed') NOT NULL DEFAULT 'none',
            discount_value DECIMAL(12,4)   NOT NULL DEFAULT 0,
            show_on_page   TINYINT(1)      NOT NULL DEFAULT 1,
            position       INT UNSIGNED    NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_bundle_id  (bundle_id),
            KEY idx_product_id (product_id)
        ) {$charset};";
    }

    private static function product_options( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_options (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id  BIGINT UNSIGNED NOT NULL,
            type        ENUM('S','R','C','I','T','F') NOT NULL DEFAULT 'S',
            position    INT UNSIGNED    NOT NULL DEFAULT 0,
            required    TINYINT(1)      NOT NULL DEFAULT 0,
            compound_pricing TINYINT(1) NOT NULL DEFAULT 0,
            status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_product_id (product_id)
        ) {$charset};";
        // Types: S=Select R=Radio C=Checkbox I=Input T=Textarea F=File
        // compound_pricing (S140): when 1, percent modifiers on this option's
        // selected variant apply to the running effective_price (post-prior-
        // modifiers) instead of the base unit_price. Default 0 = current
        // additive behavior. Most relevant for sequential-options chains
        // where downstream tiers should compound off upstream choices.
    }

    private static function product_option_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_option_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            option_id     BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            comment       TEXT            NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_opt_lang (option_id, language_code)
        ) {$charset};";
    }

    private static function product_option_variants( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_option_variants (
            id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            option_id       BIGINT UNSIGNED  NOT NULL,
            position        INT UNSIGNED     NOT NULL DEFAULT 0,
            modifier        DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            modifier_type   ENUM('A','P') NOT NULL DEFAULT 'A',
            weight_modifier DECIMAL(10,4)   NOT NULL DEFAULT 0.0000,
            image_id        BIGINT UNSIGNED  NULL,
            status          ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_option_id (option_id)
        ) {$charset};";
        // modifier_type: A=Absolute, P=Percent
    }

    private static function product_option_variant_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_option_variant_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            variant_id    BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            UNIQUE KEY uq_var_lang (variant_id, language_code)
        ) {$charset};";
    }

    private static function product_option_exceptions( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_option_exceptions (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id  BIGINT UNSIGNED NOT NULL,
            combination JSON            NOT NULL,
            type        ENUM('forbidden','allowed') NOT NULL DEFAULT 'forbidden',
            PRIMARY KEY (id),
            KEY idx_product_id (product_id)
        ) {$charset};";
    }

    /**
     * Sequential-options dependency edges (S140).
     *
     * Each row says: "option_id is unlocked when the customer has selected
     * variant parent_variant_id on parent_option_id." Multiple rows for the
     * same option_id with different parent_variant_id values on the SAME
     * parent_option_id form an OR (any matching parent variant unlocks it).
     * Multiple rows for the same option_id across DIFFERENT parent_option_id
     * values form an AND (every parent option must have a satisfying selection).
     *
     * No row for an option_id at all = the option is always visible (flat
     * options behavior, current default for every existing product).
     *
     * Cascade cleanup happens in ProductRepository::delete_option +
     * delete_variant — there are no DB-level FKs (TitanCart's lean-schema
     * convention).
     */
    private static function product_option_dependencies( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_option_dependencies (
            id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            option_id         BIGINT UNSIGNED NOT NULL,
            parent_option_id  BIGINT UNSIGNED NOT NULL,
            parent_variant_id BIGINT UNSIGNED NOT NULL,
            created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_dep_edge (option_id, parent_option_id, parent_variant_id),
            KEY idx_option_id (option_id),
            KEY idx_parent (parent_option_id, parent_variant_id)
        ) {$charset};";
    }

    private static function product_variation_links( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_variation_links (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            parent_id    BIGINT UNSIGNED NOT NULL,
            child_id     BIGINT UNSIGNED NOT NULL,
            combination  JSON            NOT NULL,
            position     INT UNSIGNED    NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY uq_parent_child (parent_id, child_id),
            KEY idx_child_id (child_id)
        ) {$charset};";
    }

    private static function product_groups( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_groups (
            id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            group_code        VARCHAR(32) NOT NULL,
            feature_ids       JSON        NOT NULL,
            display_as_single TINYINT(1)  NOT NULL DEFAULT 0,
            display_name      VARCHAR(255) NOT NULL DEFAULT '',
            created_at        DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_group_code (group_code)
        ) {$charset};";
        // display_as_single (roadmap #15): when 1, the storefront shows ONE
        // representative card for the whole group instead of one card per member
        // (each member keeps its own SKU + PDP). display_name: optional clean
        // storefront name for the representative card (blank = derive from members).
        // feature_ids: JSON array of attribute IDs that define this group, e.g. [42] for Color
    }

    private static function product_group_products( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_group_products (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            group_id    BIGINT UNSIGNED NOT NULL,
            product_id  BIGINT UNSIGNED NOT NULL,
            combination JSON        NOT NULL,
            position    INT UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY uq_group_product (group_id, product_id),
            KEY idx_product_id (product_id)
        ) {$charset};";
        // combination: { "42": "15" } = feature_id 42 → variant_id 15 (e.g. Color → Red)
    }

    private static function product_waitlist( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_waitlist (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id      BIGINT UNSIGNED NOT NULL,
            user_id         BIGINT UNSIGNED NULL,
            email           VARCHAR(255) NOT NULL,
            name            VARCHAR(255) NOT NULL DEFAULT '',
            attributes_json JSON         NULL,
            status          ENUM('waiting','notified','cancelled') NOT NULL DEFAULT 'waiting',
            ip_address      VARCHAR(45)  NOT NULL DEFAULT '',
            created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            notified_at     DATETIME     NULL,
            PRIMARY KEY (id),
            KEY idx_product_id (product_id),
            KEY idx_email (email(191)),
            KEY idx_status (status),
            UNIQUE KEY uq_product_email (product_id, email)
        ) {$charset};";
        // attributes_json: stores selected attribute values at signup, e.g. {"Color":"Red","Size":"Large"}
        // status: waiting = awaiting stock, notified = email sent, cancelled = user removed
    }

    private static function product_files( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_files (
            id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id       BIGINT UNSIGNED NOT NULL,
            wp_attachment_id BIGINT UNSIGNED NOT NULL,
            name             VARCHAR(255)    NOT NULL DEFAULT '',
            max_downloads    INT UNSIGNED    NULL,
            expires_days     INT UNSIGNED    NULL,
            position         INT UNSIGNED    NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_product_id (product_id)
        ) {$charset};";
    }

    private static function product_tabs( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_tabs (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tab_type      ENUM('builtin','richtext','sizing_chart','template') NOT NULL DEFAULT 'richtext',
            tab_key       VARCHAR(64)     NOT NULL DEFAULT '',
            product_id    BIGINT UNSIGNED NULL COMMENT 'NULL = global tab, set = product-specific tab',
            template_path VARCHAR(255)    NULL COMMENT 'For template-type tabs registered by themes/extensions',
            position      INT UNSIGNED    NOT NULL DEFAULT 0,
            status        ENUM('active','disabled') NOT NULL DEFAULT 'active',
            is_primary    TINYINT(1)      NOT NULL DEFAULT 0 COMMENT 'Primary tabs cannot be deleted',
            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_product_id (product_id),
            KEY idx_tab_key    (tab_key)
        ) {$charset};";
    }

    private static function product_tab_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_tab_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tab_id        BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            title         VARCHAR(255)    NOT NULL DEFAULT '',
            content       LONGTEXT        NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_tab_lang (tab_id, language_code)
        ) {$charset};";
    }

    /**
     * Per-product overrides for global tabs.
     * Lets admin enable/disable a global tab for a specific product
     * and optionally replace the content for richtext tabs.
     */
    private static function product_tab_overrides( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_tab_overrides (
            id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tab_id           BIGINT UNSIGNED NOT NULL,
            product_id       BIGINT UNSIGNED NOT NULL,
            status           ENUM('active','disabled') NOT NULL DEFAULT 'active',
            content_override LONGTEXT        NULL COMMENT 'If set, replaces global content for this product',
            PRIMARY KEY (id),
            UNIQUE KEY uq_tab_product (tab_id, product_id),
            KEY idx_product_id (product_id)
        ) {$charset};";
    }

    /**
     * Per-product sizing chart data.
     * Structured JSON table (headers, rows, unit) plus optional notes.
     */
    private static function product_sizing_charts( string $p, string $charset ): string {
        return "CREATE TABLE {$p}product_sizing_charts (
            id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id BIGINT UNSIGNED NOT NULL,
            chart_data JSON            NOT NULL COMMENT '{headers:[...], rows:[[...]], unit:\"in\"|\"cm\"}',
            notes      TEXT            NULL COMMENT 'How to measure instructions',
            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 uq_product (product_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 3 — Cart
    // ══════════════════════════════════════════════════════════════════════════

    private static function carts( string $p, string $charset ): string {
        return "CREATE TABLE {$p}carts (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            session_key  CHAR(64)        NOT NULL,
            customer_id  BIGINT UNSIGNED NULL,
            currency     CHAR(3)         NOT NULL DEFAULT 'USD',
            coupon_code  VARCHAR(64)     NULL,
            notes        TEXT            NULL,
            meta         JSON            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 uq_session_key (session_key),
            KEY idx_customer_id (customer_id)
        ) {$charset};";
    }

    private static function cart_items( string $p, string $charset ): string {
        return "CREATE TABLE {$p}cart_items (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            cart_id      BIGINT UNSIGNED NOT NULL,
            product_id   BIGINT UNSIGNED NOT NULL,
            variation_id BIGINT UNSIGNED NULL,
            quantity     INT UNSIGNED    NOT NULL DEFAULT 1,
            unit_price   DECIMAL(12,4)  NOT NULL,
            options      JSON            NULL,
            meta         JSON            NULL,
            added_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_cart_id    (cart_id),
            KEY idx_product_id (product_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 4 — Checkout
    // ══════════════════════════════════════════════════════════════════════════

    private static function checkout_sessions( string $p, string $charset ): string {
        return "CREATE TABLE {$p}checkout_sessions (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            session_key     CHAR(64)        NOT NULL,
            cart_id         BIGINT UNSIGNED NULL,
            customer_id     BIGINT UNSIGNED NULL,
            step            TINYINT UNSIGNED NOT NULL DEFAULT 1,
            billing_data    JSON            NULL,
            shipping_data   JSON            NULL,
            shipping_method_id BIGINT UNSIGNED NULL,
            payment_method_id  BIGINT UNSIGNED NULL,
            payment_data    JSON            NULL,
            totals          JSON            NULL,
            expires_at      DATETIME        NOT 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 uq_session_key (session_key),
            KEY idx_cart_id     (cart_id),
            KEY idx_customer_id (customer_id),
            KEY idx_expires_at  (expires_at)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 5 — Orders + Email
    // ══════════════════════════════════════════════════════════════════════════

    private static function orders( string $p, string $charset ): string {
        return "CREATE TABLE {$p}orders (
            id                   BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            order_number         VARCHAR(32)      NOT NULL DEFAULT '',
            order_key            VARCHAR(64)      NOT NULL DEFAULT '',
            customer_id          BIGINT UNSIGNED  NULL,
            status_id            BIGINT UNSIGNED  NOT NULL DEFAULT 0,
            currency             CHAR(3)          NOT NULL DEFAULT 'USD',
            exchange_rate        DECIMAL(12,6)   NOT NULL DEFAULT 1.000000,
            subtotal             DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            discount             DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            shipping_cost        DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            tax_total            DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            payment_surcharge    DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            tip_total            DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            total                DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            coupon_code          VARCHAR(64)      NULL,
            coupon_discount      DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            gift_card_code       VARCHAR(64)      NULL,
            gift_card_discount   DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            points_redeemed      INT UNSIGNED     NOT NULL DEFAULT 0,
            points_discount      DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            stock_state          ENUM('none','reduced','restored') NOT NULL DEFAULT 'none',
            payment_method_id    BIGINT UNSIGNED  NULL,
            payment_info         JSON             NULL,
            shipping_method_id   BIGINT UNSIGNED  NULL,
            shipping_info        JSON             NULL,
            billing_first_name   VARCHAR(64)      NOT NULL DEFAULT '',
            billing_last_name    VARCHAR(64)      NOT NULL DEFAULT '',
            billing_company      VARCHAR(128)     NULL,
            billing_address_1    VARCHAR(255)     NOT NULL DEFAULT '',
            billing_address_2    VARCHAR(255)     NULL,
            billing_city         VARCHAR(128)     NOT NULL DEFAULT '',
            billing_state        VARCHAR(128)     NULL,
            billing_postcode     VARCHAR(32)      NOT NULL DEFAULT '',
            billing_country      CHAR(2)          NOT NULL DEFAULT '',
            billing_email        VARCHAR(255)     NOT NULL DEFAULT '',
            billing_phone        VARCHAR(32)      NULL,
            shipping_first_name  VARCHAR(64)      NOT NULL DEFAULT '',
            shipping_last_name   VARCHAR(64)      NOT NULL DEFAULT '',
            shipping_company     VARCHAR(128)     NULL,
            shipping_address_1   VARCHAR(255)     NOT NULL DEFAULT '',
            shipping_address_2   VARCHAR(255)     NULL,
            shipping_city        VARCHAR(128)     NOT NULL DEFAULT '',
            shipping_state       VARCHAR(128)     NULL,
            shipping_postcode    VARCHAR(32)      NOT NULL DEFAULT '',
            shipping_country     CHAR(2)          NOT NULL DEFAULT '',
            customer_note        TEXT             NULL,
            referral_source      VARCHAR(100)     NULL,
            ip_address           VARCHAR(45)      NULL,
            user_agent           VARCHAR(512)     NULL,
            taxes                JSON             NULL,
            promotions_applied   JSON             NULL,
            meta                 JSON             NULL,
            created_at           DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at           DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            deleted_at           DATETIME         NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_order_number  (order_number),
            KEY idx_customer_id        (customer_id),
            KEY idx_status_id          (status_id),
            KEY idx_billing_email      (billing_email(191)),
            KEY idx_referral_source    (referral_source),
            KEY idx_created_at         (created_at)
        ) {$charset};";
    }

    private static function order_items( string $p, string $charset ): string {
        return "CREATE TABLE {$p}order_items (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id     BIGINT UNSIGNED NOT NULL,
            product_id   BIGINT UNSIGNED NOT NULL,
            vendor_id    BIGINT UNSIGNED NULL,
            variation_id BIGINT UNSIGNED NULL,
            sku          VARCHAR(64)     NOT NULL DEFAULT '',
            name         VARCHAR(255)    NOT NULL DEFAULT '',
            quantity     INT UNSIGNED    NOT NULL DEFAULT 1,
            unit_price   DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            subtotal     DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            discount     DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            tax          DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            total        DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            meta         JSON            NULL,
            PRIMARY KEY (id),
            KEY idx_order_id   (order_id),
            KEY idx_product_id (product_id),
            KEY idx_vendor_id  (vendor_id)
        ) {$charset};";
    }

    private static function order_item_options( string $p, string $charset ): string {
        return "CREATE TABLE {$p}order_item_options (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_item_id   BIGINT UNSIGNED NOT NULL,
            option_name     VARCHAR(255)    NOT NULL DEFAULT '',
            variant_name    VARCHAR(255)    NOT NULL DEFAULT '',
            price_modifier  DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            PRIMARY KEY (id),
            KEY idx_order_item_id (order_item_id)
        ) {$charset};";
    }

    private static function order_statuses( string $p, string $charset ): string {
        return "CREATE TABLE {$p}order_statuses (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            handle      VARCHAR(64)     NOT NULL,
            type        ENUM('order','fulfillment','gift_card') NOT NULL DEFAULT 'order',
            color       CHAR(7)         NOT NULL DEFAULT '#71797E',
            position    INT UNSIGNED    NOT NULL DEFAULT 0,
            is_default  TINYINT(1)      NOT NULL DEFAULT 0,
            notify_customer TINYINT(1)  NOT NULL DEFAULT 0,
            stock_action ENUM('none','reduce','restore') NOT NULL DEFAULT 'none',
            PRIMARY KEY (id),
            UNIQUE KEY uq_handle (handle),
            KEY idx_type (type)
        ) {$charset};";
    }

    private static function order_status_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}order_status_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            status_id     BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(128)    NOT NULL DEFAULT '',
            description   TEXT            NULL,
            email_subject VARCHAR(255)    NULL,
            email_body    LONGTEXT        NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_status_lang (status_id, language_code)
        ) {$charset};";
    }

    private static function order_notes( string $p, string $charset ): string {
        return "CREATE TABLE {$p}order_notes (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id     BIGINT UNSIGNED NOT NULL,
            author_id    BIGINT UNSIGNED NULL,
            note         TEXT            NOT NULL,
            type         ENUM('internal','customer','system') NOT NULL DEFAULT 'internal',
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_order_id (order_id)
        ) {$charset};";
    }

    private static function order_history( string $p, string $charset ): string {
        return "CREATE TABLE {$p}order_history (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id      BIGINT UNSIGNED NOT NULL,
            from_status   VARCHAR(64)     NULL,
            to_status     VARCHAR(64)     NOT NULL,
            changed_by    BIGINT UNSIGNED NULL,
            comment       TEXT            NULL,
            created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_order_id (order_id)
        ) {$charset};";
    }

    private static function email_templates( string $p, string $charset ): string {
        return "CREATE TABLE {$p}email_templates (
            id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            slug       VARCHAR(64)  NOT NULL,
            receiver   ENUM('customer','admin') NOT NULL DEFAULT 'customer',
            name       VARCHAR(255) NOT NULL DEFAULT '',
            subject    VARCHAR(255) NOT NULL DEFAULT '',
            body_html  LONGTEXT,
            status     ENUM('active','disabled') 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),
            UNIQUE KEY uq_slug_receiver (slug, receiver)
        ) {$charset};";
    }

    private static function email_template_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}email_template_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            template_id   BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            subject       VARCHAR(255)    NOT NULL DEFAULT '',
            body          LONGTEXT        NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_tpl_lang (template_id, language_code)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 6 — Payments
    // ══════════════════════════════════════════════════════════════════════════

    private static function payment_methods( string $p, string $charset ): string {
        return "CREATE TABLE {$p}payment_methods (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            gateway     VARCHAR(64)     NOT NULL,
            position    INT UNSIGNED    NOT NULL DEFAULT 0,
            surcharge   DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            surcharge_type ENUM('A','P') NOT NULL DEFAULT 'A',
            settings    JSON            NULL,
            status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
            test_mode   TINYINT(1)      NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_gateway (gateway),
            KEY idx_status  (status)
        ) {$charset};";
    }

    private static function payment_method_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}payment_method_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            method_id     BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            description   TEXT            NULL,
            instructions  TEXT            NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_method_lang (method_id, language_code)
        ) {$charset};";
    }

    private static function payment_transactions( string $p, string $charset ): string {
        return "CREATE TABLE {$p}payment_transactions (
            id                BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            order_id          BIGINT UNSIGNED  NOT NULL,
            payment_method_id BIGINT UNSIGNED  NULL,
            gateway           VARCHAR(64)      NOT NULL,
            transaction_id    VARCHAR(255)     NOT NULL DEFAULT '',
            amount            DECIMAL(12,4)   NOT NULL DEFAULT 0.0000,
            currency          CHAR(3)          NOT NULL DEFAULT 'USD',
            type              ENUM('charge','refund','void','authorize','capture') NOT NULL DEFAULT 'charge',
            status            ENUM('pending','completed','failed','refunded','voided') NOT NULL DEFAULT 'pending',
            gateway_response  JSON             NULL,
            created_at        DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_order_id       (order_id),
            KEY idx_transaction_id (transaction_id),
            KEY idx_status         (status)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 7 — Shipping
    // ══════════════════════════════════════════════════════════════════════════

    private static function shipping_methods( string $p, string $charset ): string {
        return "CREATE TABLE {$p}shipping_methods (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            carrier     VARCHAR(64)     NOT NULL DEFAULT 'flat',
            position    INT UNSIGNED    NOT NULL DEFAULT 0,
            tax_class_id BIGINT UNSIGNED NULL,
            min_weight  DECIMAL(10,4)  NULL,
            max_weight  DECIMAL(10,4)  NULL,
            settings    JSON            NULL,
            status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_carrier (carrier),
            KEY idx_status  (status)
        ) {$charset};";
    }

    private static function shipping_method_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}shipping_method_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            method_id     BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(255)    NOT NULL DEFAULT '',
            description   TEXT            NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_ship_lang (method_id, language_code)
        ) {$charset};";
    }

    private static function shipping_zones( string $p, string $charset ): string {
        return "CREATE TABLE {$p}shipping_zones (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name        VARCHAR(128)    NOT NULL DEFAULT '',
            position    INT UNSIGNED    NOT NULL DEFAULT 0,
            status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id)
        ) {$charset};";
    }

    private static function shipping_zone_locations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}shipping_zone_locations (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            zone_id     BIGINT UNSIGNED NOT NULL,
            type        ENUM('country','state','postcode') NOT NULL DEFAULT 'country',
            code        VARCHAR(64)     NOT NULL,
            PRIMARY KEY (id),
            KEY idx_zone_id (zone_id),
            KEY idx_code    (code)
        ) {$charset};";
    }

    private static function shipping_rates( string $p, string $charset ): string {
        return "CREATE TABLE {$p}shipping_rates (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            method_id   BIGINT UNSIGNED NOT NULL,
            zone_id     BIGINT UNSIGNED NOT NULL,
            rate_type   ENUM('flat','by_weight','by_price','by_items','free') NOT NULL DEFAULT 'flat',
            position    INT UNSIGNED    NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_method_id (method_id),
            KEY idx_zone_id   (zone_id)
        ) {$charset};";
    }

    private static function shipping_rate_conditions( string $p, string $charset ): string {
        return "CREATE TABLE {$p}shipping_rate_conditions (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            rate_id     BIGINT UNSIGNED NOT NULL,
            from_value  DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            to_value    DECIMAL(12,4)  NULL,
            price       DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            price_type  ENUM('fixed','percent') NOT NULL DEFAULT 'fixed',
            PRIMARY KEY (id),
            KEY idx_rate_id (rate_id)
        ) {$charset};";
    }

    private static function shipments( string $p, string $charset ): string {
        return "CREATE TABLE {$p}shipments (
            id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id         BIGINT UNSIGNED NOT NULL,
            shipping_method_id BIGINT UNSIGNED NULL,
            status_id        BIGINT UNSIGNED NULL,
            tracking_number  VARCHAR(128)    NOT NULL DEFAULT '',
            carrier          VARCHAR(64)     NOT NULL DEFAULT '',
            comments         TEXT            NULL,
            shipped_at       DATETIME        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_id (order_id),
            KEY idx_status_id (status_id)
        ) {$charset};";
    }

    private static function shipment_items( string $p, string $charset ): string {
        return "CREATE TABLE {$p}shipment_items (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            shipment_id     BIGINT UNSIGNED NOT NULL,
            order_item_id   BIGINT UNSIGNED NOT NULL,
            product_id      BIGINT UNSIGNED NOT NULL,
            quantity        INT UNSIGNED    NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_shipment_id (shipment_id),
            KEY idx_order_item_id (order_item_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 8 — Tax
    // ══════════════════════════════════════════════════════════════════════════

    private static function tax_classes( string $p, string $charset ): string {
        return "CREATE TABLE {$p}tax_classes (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            handle              VARCHAR(64)     NOT NULL DEFAULT '',
            registration_number VARCHAR(64)     NOT NULL DEFAULT '',
            priority            INT UNSIGNED    NOT NULL DEFAULT 0,
            rates_depend_on     ENUM('shipping','billing') NOT NULL DEFAULT 'shipping',
            price_includes_tax  TINYINT(1)      NOT NULL DEFAULT 0,
            status              ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            UNIQUE KEY uq_handle (handle)
        ) {$charset};";
    }

    private static function tax_class_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}tax_class_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            class_id      BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(128)    NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            UNIQUE KEY uq_class_lang (class_id, language_code)
        ) {$charset};";
    }

    private static function tax_zones( string $p, string $charset ): string {
        return "CREATE TABLE {$p}tax_zones (
            id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name     VARCHAR(128)    NOT NULL DEFAULT '',
            status   ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id)
        ) {$charset};";
    }

    private static function tax_zone_locations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}tax_zone_locations (
            id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            zone_id  BIGINT UNSIGNED NOT NULL,
            type     ENUM('country','state','postcode') NOT NULL DEFAULT 'country',
            code     VARCHAR(64)     NOT NULL,
            PRIMARY KEY (id),
            KEY idx_zone_id (zone_id),
            KEY idx_code    (code)
        ) {$charset};";
    }

    private static function tax_rates( string $p, string $charset ): string {
        return "CREATE TABLE {$p}tax_rates (
            id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            class_id       BIGINT UNSIGNED NOT NULL,
            zone_id        BIGINT UNSIGNED NOT NULL,
            rate           DECIMAL(8,4)   NOT NULL DEFAULT 0.0000,
            is_compound    TINYINT(1)      NOT NULL DEFAULT 0,
            priority       INT UNSIGNED    NOT NULL DEFAULT 1,
            includes_tax   TINYINT(1)      NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_class_id (class_id),
            KEY idx_zone_id  (zone_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 9 — Promotions
    // ══════════════════════════════════════════════════════════════════════════

    private static function promotions( string $p, string $charset ): string {
        return "CREATE TABLE {$p}promotions (
            id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            type            ENUM('catalog','cart') NOT NULL DEFAULT 'cart',
            priority        INT UNSIGNED     NOT NULL DEFAULT 0,
            stop_other      TINYINT(1)       NOT NULL DEFAULT 0,
            uses_coupons    TINYINT(1)       NOT NULL DEFAULT 0,
            usage_limit     INT UNSIGNED     NULL,
            usage_count     INT UNSIGNED     NOT NULL DEFAULT 0,
            per_customer_limit INT UNSIGNED  NULL,
            valid_from      DATETIME         NULL,
            valid_to        DATETIME         NULL,
            status          ENUM('active','disabled') NOT NULL DEFAULT 'active',
            created_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_type   (type),
            KEY idx_status (status)
        ) {$charset};";
    }

    private static function promotion_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}promotion_translations (
            id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            promotion_id   BIGINT UNSIGNED NOT NULL,
            language_code  CHAR(2)         NOT NULL,
            name           VARCHAR(255)    NOT NULL DEFAULT '',
            description    TEXT            NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_promo_lang (promotion_id, language_code)
        ) {$charset};";
    }

    private static function promotion_conditions( string $p, string $charset ): string {
        return "CREATE TABLE {$p}promotion_conditions (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            promotion_id  BIGINT UNSIGNED NOT NULL,
            type          VARCHAR(64)     NOT NULL,
            operator      VARCHAR(16)     NOT NULL DEFAULT 'eq',
            value         JSON            NOT NULL,
            position      INT UNSIGNED    NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_promotion_id (promotion_id)
        ) {$charset};";
    }

    private static function promotion_bonuses( string $p, string $charset ): string {
        return "CREATE TABLE {$p}promotion_bonuses (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            promotion_id  BIGINT UNSIGNED NOT NULL,
            type          VARCHAR(64)     NOT NULL,
            value         JSON            NOT NULL,
            position      INT UNSIGNED    NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_promotion_id (promotion_id)
        ) {$charset};";
    }

    private static function coupon_codes( string $p, string $charset ): string {
        return "CREATE TABLE {$p}coupon_codes (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            promotion_id  BIGINT UNSIGNED NOT NULL,
            code          VARCHAR(64)     NOT NULL,
            usage_limit   INT UNSIGNED    NULL,
            usage_count   INT UNSIGNED    NOT NULL DEFAULT 0,
            expires_at    DATETIME        NULL,
            status        ENUM('active','disabled','expired') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            UNIQUE KEY uq_code  (code),
            KEY idx_promotion_id (promotion_id)
        ) {$charset};";
    }

    private static function coupon_usage( string $p, string $charset ): string {
        return "CREATE TABLE {$p}coupon_usage (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            coupon_id    BIGINT UNSIGNED NOT NULL,
            order_id     BIGINT UNSIGNED NOT NULL,
            customer_id  BIGINT UNSIGNED NULL,
            used_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_coupon_id   (coupon_id),
            KEY idx_order_id    (order_id),
            KEY idx_customer_id (customer_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // Gift Cards
    // ══════════════════════════════════════════════════════════════════════════

    private static function gift_cards( string $p, string $charset ): string {
        return "CREATE TABLE {$p}gift_cards (
            id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            code             VARCHAR(32)     NOT NULL,
            amount           DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
            balance          DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
            sender_name      VARCHAR(128)    NOT NULL DEFAULT '',
            sender_email     VARCHAR(255)    NOT NULL DEFAULT '',
            recipient_name   VARCHAR(128)    NOT NULL DEFAULT '',
            recipient_email  VARCHAR(255)    NOT NULL DEFAULT '',
            message          TEXT            NULL,
            send_via         ENUM('email','postal') NOT NULL DEFAULT 'email',
            status           ENUM('pending','active','used','disabled') NOT NULL DEFAULT 'pending',
            order_id         BIGINT UNSIGNED NULL,
            customer_id      BIGINT UNSIGNED NULL,
            expires_at       DATETIME        NULL,
            sent_at          DATETIME        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 uq_code (code),
            KEY idx_status (status),
            KEY idx_order_id (order_id),
            KEY idx_customer_id (customer_id)
        ) {$charset};";
    }

    private static function gift_card_log( string $p, string $charset ): string {
        return "CREATE TABLE {$p}gift_card_log (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            gift_card_id    BIGINT UNSIGNED NOT NULL,
            order_id        BIGINT UNSIGNED NULL,
            amount          DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
            balance_after   DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
            action          ENUM('created','redeemed','refunded','adjusted','disabled','emailed') NOT NULL,
            note            VARCHAR(255)    NOT NULL DEFAULT '',
            created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_gift_card_id (gift_card_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // Reward Points
    // ══════════════════════════════════════════════════════════════════════════

    private static function reward_point_balances( string $p, string $charset ): string {
        return "CREATE TABLE {$p}reward_point_balances (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            customer_id   BIGINT UNSIGNED NOT NULL,
            balance       INT             NOT NULL DEFAULT 0,
            lifetime      INT             NOT NULL DEFAULT 0,
            updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_customer (customer_id)
        ) {$charset};";
    }

    private static function reward_point_log( string $p, string $charset ): string {
        return "CREATE TABLE {$p}reward_point_log (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            customer_id   BIGINT UNSIGNED NOT NULL,
            order_id      BIGINT UNSIGNED NULL,
            points        INT             NOT NULL DEFAULT 0,
            action        ENUM('earned','redeemed','adjusted','expired','refunded') NOT NULL,
            reason        VARCHAR(255)    NOT NULL DEFAULT '',
            event         VARCHAR(64)     NULL,
            dedup_key     VARCHAR(191)    NULL,
            created_by    BIGINT UNSIGNED NULL,
            created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_customer_id (customer_id),
            KEY idx_order_id (order_id),
            KEY idx_action (action),
            UNIQUE KEY uq_dedup_key (dedup_key)
        ) {$charset};";
    }

    private static function reward_point_rules( string $p, string $charset ): string {
        return "CREATE TABLE {$p}reward_point_rules (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            event           VARCHAR(64)     NOT NULL,
            name            VARCHAR(255)    NOT NULL DEFAULT '',
            points_amount   INT             NOT NULL DEFAULT 0,
            points_type     ENUM('fixed','per_dollar') NOT NULL DEFAULT 'fixed',
            min_order       DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
            usergroup_id    BIGINT UNSIGNED NULL,
            status          ENUM('active','disabled') NOT NULL DEFAULT 'active',
            position        INT UNSIGNED    NOT NULL DEFAULT 0,
            created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_event (event),
            KEY idx_status (status)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 10 — Customers
    // ══════════════════════════════════════════════════════════════════════════

    private static function customers( string $p, string $charset ): string {
        return "CREATE TABLE {$p}customers (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            wp_user_id      BIGINT UNSIGNED NULL,
            email           VARCHAR(255)    NOT NULL,
            first_name      VARCHAR(64)     NOT NULL DEFAULT '',
            last_name       VARCHAR(64)     NOT NULL DEFAULT '',
            phone           VARCHAR(32)     NULL,
            date_of_birth   DATE            NULL,
            referral_code   VARCHAR(32)     NULL,
            referred_by     BIGINT UNSIGNED NULL,
            language        CHAR(2)         NOT NULL DEFAULT 'en',
            currency        CHAR(3)         NOT NULL DEFAULT 'USD',
            total_orders    INT UNSIGNED    NOT NULL DEFAULT 0,
            total_spent     DECIMAL(12,4)  NOT NULL DEFAULT 0.0000,
            last_ordered_at DATETIME        NULL,
            status          ENUM('active','disabled','banned') NOT NULL DEFAULT 'active',
            notes           TEXT            NULL,
            created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            deleted_at      DATETIME        NULL,
            erasure_scheduled_at DATETIME   NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_email     (email(191)),
            UNIQUE KEY uq_referral_code (referral_code),
            KEY idx_wp_user_id  (wp_user_id),
            KEY idx_status      (status),
            KEY idx_referred_by (referred_by),
            KEY idx_erasure_scheduled (erasure_scheduled_at)
        ) {$charset};";
    }

    private static function customer_addresses( string $p, string $charset ): string {
        return "CREATE TABLE {$p}customer_addresses (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            customer_id  BIGINT UNSIGNED NOT NULL,
            type         ENUM('billing','shipping') NOT NULL DEFAULT 'shipping',
            is_default   TINYINT(1)      NOT NULL DEFAULT 0,
            first_name   VARCHAR(64)     NOT NULL DEFAULT '',
            last_name    VARCHAR(64)     NOT NULL DEFAULT '',
            company      VARCHAR(128)    NULL,
            address_1    VARCHAR(255)    NOT NULL DEFAULT '',
            address_2    VARCHAR(255)    NULL,
            city         VARCHAR(128)    NOT NULL DEFAULT '',
            state        VARCHAR(128)    NULL,
            postcode     VARCHAR(32)     NOT NULL DEFAULT '',
            country      CHAR(2)         NOT NULL DEFAULT '',
            phone        VARCHAR(32)     NULL,
            PRIMARY KEY (id),
            KEY idx_customer_id (customer_id)
        ) {$charset};";
    }

    private static function wishlists( string $p, string $charset ): string {
        return "CREATE TABLE {$p}wishlists (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            customer_id  BIGINT UNSIGNED NOT NULL,
            name         VARCHAR(128)    NOT NULL DEFAULT 'Default',
            is_public    TINYINT(1)      NOT NULL DEFAULT 0,
            share_key    CHAR(32)        NULL,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_customer_id (customer_id),
            KEY idx_share_key   (share_key)
        ) {$charset};";
    }

    private static function wishlist_items( string $p, string $charset ): string {
        return "CREATE TABLE {$p}wishlist_items (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            wishlist_id  BIGINT UNSIGNED NOT NULL,
            product_id   BIGINT UNSIGNED NOT NULL,
            variation_id BIGINT UNSIGNED NULL,
            added_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_wishlist_id (wishlist_id),
            KEY idx_product_id  (product_id)
        ) {$charset};";
    }

    private static function usergroups( string $p, string $charset ): string {
        return "CREATE TABLE {$p}usergroups (
            id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            handle     VARCHAR(64)     NOT NULL DEFAULT '',
            status     ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            UNIQUE KEY uq_handle (handle)
        ) {$charset};";
    }

    private static function usergroup_translations( string $p, string $charset ): string {
        return "CREATE TABLE {$p}usergroup_translations (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            group_id      BIGINT UNSIGNED NOT NULL,
            language_code CHAR(2)         NOT NULL,
            name          VARCHAR(128)    NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            UNIQUE KEY uq_group_lang (group_id, language_code)
        ) {$charset};";
    }

    private static function customer_usergroups( string $p, string $charset ): string {
        return "CREATE TABLE {$p}customer_usergroups (
            customer_id  BIGINT UNSIGNED NOT NULL,
            group_id     BIGINT UNSIGNED NOT NULL,
            joined_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (customer_id, group_id),
            KEY idx_group_id (group_id)
        ) {$charset};";
    }

    private static function customer_fields( string $p, string $charset ): string {
        return "CREATE TABLE {$p}customer_fields (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            section      ENUM('shipping','contact','billing') NOT NULL,
            field_name   VARCHAR(64)  NOT NULL,
            field_type   VARCHAR(32)  NOT NULL DEFAULT 'input',
            description  VARCHAR(128) NOT NULL DEFAULT '',
            position     INT UNSIGNED NOT NULL DEFAULT 0,
            is_visible   TINYINT(1)   NOT NULL DEFAULT 0,
            is_required  TINYINT(1)   NOT NULL DEFAULT 0,
            is_system    TINYINT(1)   NOT NULL DEFAULT 0,
            status       ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            UNIQUE KEY uq_section_field (section, field_name),
            KEY idx_section (section)
        ) {$charset};";
    }

    /**
     * Per-customer values for non-system custom customer fields.
     *
     * System fields (first_name, last_name, email, phone, address_1, etc.) are
     * stored in tc_customers / tc_customer_addresses fixed columns. Custom
     * fields admins create via the Customer Fields page (e.g. "Tax ID") have
     * no fixed column — their values land here, keyed by (customer_id, field_id).
     *
     * Wired by S127.C-1.
     */
    private static function customer_field_values( string $p, string $charset ): string {
        return "CREATE TABLE {$p}customer_field_values (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            customer_id  BIGINT UNSIGNED NOT NULL,
            field_id     BIGINT UNSIGNED NOT NULL,
            value        TEXT            NOT 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 uq_customer_field (customer_id, field_id),
            KEY idx_customer_id (customer_id),
            KEY idx_field_id (field_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 11 — Reviews
    // ══════════════════════════════════════════════════════════════════════════

    private static function reviews( string $p, string $charset ): string {
        // vendor_id + vendor_rating columns added at S201 (Track B Module B9) for
        // composite vendor reviews. When a vendor-sold product is reviewed, the
        // customer may optionally rate the seller (1-5 stars) in addition to the
        // product. vendor_id is stamped at INSERT time via the
        // `titancart_review_data` filter (Pattern #158 ninth instance, marketplace
        // VendorReviewDataStamper). vendor_rating is NULL when:
        //   - product has no vendor (admin product)
        //   - customer chose not to rate the vendor
        // Vendor aggregate computed via AVG(vendor_rating) WHERE vendor_id=X AND
        // vendor_rating IS NOT NULL AND status='approved', maintained in the
        // denormalized tc_vendors.review_avg / review_count columns.
        // Migration: Mig 43 (Plugin.php) for existing installs.
        return "CREATE TABLE {$p}reviews (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id    BIGINT UNSIGNED NOT NULL,
            vendor_id     BIGINT UNSIGNED NULL,
            customer_id   BIGINT UNSIGNED NULL,
            order_id      BIGINT UNSIGNED NULL,
            rating        TINYINT UNSIGNED NOT NULL DEFAULT 5,
            vendor_rating TINYINT UNSIGNED NULL,
            title         VARCHAR(255)    NULL,
            body          TEXT            NULL,
            status        ENUM('pending','approved','rejected','spam') NOT NULL DEFAULT 'pending',
            helpful       INT UNSIGNED    NOT NULL DEFAULT 0,
            not_helpful   INT UNSIGNED    NOT NULL DEFAULT 0,
            ip_address    VARCHAR(45)     NULL,
            created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_product_id  (product_id),
            KEY idx_vendor_id   (vendor_id),
            KEY idx_customer_id (customer_id),
            KEY idx_status      (status)
        ) {$charset};";
    }

    private static function review_votes( string $p, string $charset ): string {
        return "CREATE TABLE {$p}review_votes (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            review_id    BIGINT UNSIGNED NOT NULL,
            customer_id  BIGINT UNSIGNED NULL,
            ip_address   VARCHAR(45)     NOT NULL,
            vote         TINYINT(1)      NOT NULL DEFAULT 1,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_review_id (review_id)
        ) {$charset};";
    }

    // MODULE 11 — Comments & Discussions
    // ══════════════════════════════════════════════════════════════════════════

    private static function comments( string $p, string $charset ): string {
        return "CREATE TABLE {$p}comments (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            object_type  ENUM('testimonial','page','category') NOT NULL DEFAULT 'testimonial',
            object_id    BIGINT UNSIGNED NULL,
            customer_id  BIGINT UNSIGNED NULL,
            author_name  VARCHAR(128)    NOT NULL DEFAULT '',
            author_email VARCHAR(255)    NULL,
            rating       TINYINT UNSIGNED NULL,
            title        VARCHAR(255)    NULL,
            body         TEXT            NULL,
            status       ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
            ip_address   VARCHAR(45)     NULL,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_object (object_type, object_id),
            KEY idx_status (status),
            KEY idx_customer (customer_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // SEO
    // ══════════════════════════════════════════════════════════════════════════

    private static function seo_redirects( string $p, string $charset ): string {
        return "CREATE TABLE {$p}seo_redirects (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            from_url    VARCHAR(512)    NOT NULL,
            to_url      VARCHAR(512)    NOT NULL,
            type        SMALLINT        NOT NULL DEFAULT 301,
            hits        INT UNSIGNED    NOT NULL DEFAULT 0,
            status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
            created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_from_url (from_url(191)),
            KEY idx_status (status)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 12 — Reports
    // ══════════════════════════════════════════════════════════════════════════

    private static function report_snapshots( string $p, string $charset ): string {
        return "CREATE TABLE {$p}report_snapshots (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            snapshot_date   DATE            NOT NULL,
            metric          VARCHAR(64)     NOT NULL,
            dimension       VARCHAR(64)     NOT NULL DEFAULT 'total',
            dimension_id    BIGINT UNSIGNED NULL,
            value           DECIMAL(16,4)  NOT NULL DEFAULT 0.0000,
            PRIMARY KEY (id),
            UNIQUE KEY uq_snapshot (snapshot_date, metric, dimension, dimension_id),
            KEY idx_metric (metric)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 13 — Import/Export
    // ══════════════════════════════════════════════════════════════════════════

    private static function import_jobs( string $p, string $charset ): string {
        return "CREATE TABLE {$p}import_jobs (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            type         VARCHAR(64)     NOT NULL DEFAULT 'products',
            filename     VARCHAR(255)    NOT NULL DEFAULT '',
            total_rows   INT UNSIGNED    NOT NULL DEFAULT 0,
            processed    INT UNSIGNED    NOT NULL DEFAULT 0,
            errors       INT UNSIGNED    NOT NULL DEFAULT 0,
            status       ENUM('queued','running','completed','failed') NOT NULL DEFAULT 'queued',
            log          LONGTEXT        NULL,
            settings     JSON            NULL,
            started_at   DATETIME        NULL,
            completed_at DATETIME        NULL,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_status (status)
        ) {$charset};";
    }

    private static function export_jobs( string $p, string $charset ): string {
        return "CREATE TABLE {$p}export_jobs (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            type         VARCHAR(64)     NOT NULL DEFAULT 'products',
            filename     VARCHAR(255)    NULL,
            status       ENUM('queued','running','completed','failed') NOT NULL DEFAULT 'queued',
            settings     JSON            NULL,
            file_path    VARCHAR(512)    NULL,
            started_at   DATETIME        NULL,
            completed_at DATETIME        NULL,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_status (status)
        ) {$charset};";
    }

    /**
     * Saved import presets (KI-34 — the Pro "Advanced Products Import" feature).
     *
     * A preset captures a reusable import configuration: a name, the entity
     * type it applies to, the CSV-column→field mapping (`column_map`), and the
     * upload options. Applying a preset pre-fills the importer's mapping step so
     * a recurring bulk update does not have to be re-mapped by hand each time.
     *
     * `column_map` / `options` are stored as JSON blobs matching exactly the
     * shapes the importer already exchanges (see ImportService::validate /
     * startJob), so a preset is format-agnostic — it stores whatever the UI
     * sends and hands it straight back.
     */
    private static function import_presets( string $p, string $charset ): string {
        return "CREATE TABLE {$p}import_presets (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name         VARCHAR(191)    NOT NULL DEFAULT '',
            type         VARCHAR(64)     NOT NULL DEFAULT 'products',
            column_map   JSON            NULL,
            options      JSON            NULL,
            created_by   BIGINT UNSIGNED NULL,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_type (type)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // MODULE 14 — API
    // ══════════════════════════════════════════════════════════════════════════

    private static function api_keys( string $p, string $charset ): string {
        return "CREATE TABLE {$p}api_keys (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id      BIGINT UNSIGNED NULL,
            description  VARCHAR(255)    NOT NULL DEFAULT '',
            public_key   CHAR(32)        NOT NULL,
            secret_hash  VARCHAR(255)    NOT NULL,
            permissions  ENUM('read','write','read_write') NOT NULL DEFAULT 'read',
            rate_limit   SMALLINT UNSIGNED NOT NULL DEFAULT 60,
            last_used_at DATETIME        NULL,
            status       ENUM('active','revoked') NOT NULL DEFAULT 'active',
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_public_key (public_key),
            KEY idx_status (status)
        ) {$charset};";
    }

    private static function api_logs( string $p, string $charset ): string {
        return "CREATE TABLE {$p}api_logs (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            key_id       BIGINT UNSIGNED NULL,
            method       CHAR(8)         NOT NULL,
            endpoint     VARCHAR(512)    NOT NULL,
            status_code  SMALLINT UNSIGNED NOT NULL,
            request      JSON            NULL,
            ip_address   VARCHAR(45)     NULL,
            duration_ms  SMALLINT UNSIGNED NULL,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_key_id    (key_id),
            KEY idx_created_at (created_at)
        ) {$charset};";
    }

    private static function webhooks( string $p, string $charset ): string {
        return "CREATE TABLE {$p}webhooks (
            id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            url             VARCHAR(512)     NOT NULL,
            secret          VARCHAR(64)      NOT NULL DEFAULT '',
            events          JSON             NOT NULL,
            status          ENUM('active','paused','failed') NOT NULL DEFAULT 'active',
            failure_count   TINYINT UNSIGNED NOT NULL DEFAULT 0,
            last_triggered  DATETIME         NULL,
            created_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_status (status)
        ) {$charset};";
    }

    private static function webhook_logs( string $p, string $charset ): string {
        return "CREATE TABLE {$p}webhook_logs (
            id           BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            webhook_id   BIGINT UNSIGNED  NOT NULL,
            event        VARCHAR(128)     NOT NULL,
            payload      JSON             NULL,
            response_code SMALLINT UNSIGNED NULL,
            response_body TEXT            NULL,
            attempt      TINYINT UNSIGNED NOT NULL DEFAULT 1,
            success      TINYINT(1)       NOT NULL DEFAULT 0,
            triggered_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_webhook_id  (webhook_id),
            KEY idx_triggered_at (triggered_at)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // SOCIAL LOGIN
    // ══════════════════════════════════════════════════════════════════════════

    private static function social_providers( string $p, string $charset ): string {
        return "CREATE TABLE {$p}social_providers (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            provider      VARCHAR(32)  NOT NULL,
            name          VARCHAR(128) NOT NULL DEFAULT '',
            client_id     VARCHAR(255) NOT NULL DEFAULT '',
            client_secret VARCHAR(255) NOT NULL DEFAULT '',
            status        ENUM('active','disabled') NOT NULL DEFAULT 'disabled',
            position      INT UNSIGNED NOT NULL DEFAULT 0,
            created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_provider (provider)
        ) {$charset};";
    }

    // ── Notification settings ──────────────────────────────────────────
    private static function notification_settings( string $p, string $charset ): string {
        return "CREATE TABLE {$p}notification_settings (
            event_id   VARCHAR(64)  NOT NULL,
            receiver   ENUM('customer','admin') NOT NULL DEFAULT 'customer',
            transport  VARCHAR(32)  NOT NULL DEFAULT 'email',
            enabled    TINYINT(1)   NOT NULL DEFAULT 1,
            PRIMARY KEY (event_id, receiver, transport)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════
    // NEWSLETTERS / EMAIL CAMPAIGNS
    // ══════════════════════════════════════════════════════════════════════

    private static function subscribers( string $p, string $charset ): string {
        return "CREATE TABLE {$p}subscribers (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            email         VARCHAR(255)  NOT NULL,
            first_name    VARCHAR(128)  NOT NULL DEFAULT '',
            last_name     VARCHAR(128)  NOT NULL DEFAULT '',
            status        ENUM('active','unsubscribed','bounced') NOT NULL DEFAULT 'active',
            confirmed     TINYINT(1)    NOT NULL DEFAULT 0,
            activation_token VARCHAR(64) NOT NULL DEFAULT '',
            unsubscribe_token VARCHAR(64) NOT NULL DEFAULT '',
            ip_address    VARCHAR(45)   NOT NULL DEFAULT '',
            source        VARCHAR(32)   NOT NULL DEFAULT 'manual',
            lang_code     VARCHAR(5)    NOT NULL DEFAULT 'en',
            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 uq_email (email),
            KEY idx_status (status)
        ) {$charset};";
    }

    private static function subscriber_lists( string $p, string $charset ): string {
        return "CREATE TABLE {$p}subscriber_lists (
            id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name                  VARCHAR(255) NOT NULL DEFAULT '',
            description           TEXT,
            status                ENUM('active','disabled') NOT NULL DEFAULT 'active',
            show_on_checkout      TINYINT(1)   NOT NULL DEFAULT 0,
            show_on_registration  TINYINT(1)   NOT NULL DEFAULT 0,
            autoresponder_id      BIGINT UNSIGNED NOT NULL DEFAULT 0,
            subscriber_count      INT UNSIGNED NOT NULL DEFAULT 0,
            created_at            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) {$charset};";
    }

    private static function subscriber_list_members( string $p, string $charset ): string {
        return "CREATE TABLE {$p}subscriber_list_members (
            subscriber_id  BIGINT UNSIGNED NOT NULL,
            list_id        BIGINT UNSIGNED NOT NULL,
            subscribed_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (subscriber_id, list_id),
            KEY idx_list (list_id)
        ) {$charset};";
    }

    private static function newsletters( string $p, string $charset ): string {
        return "CREATE TABLE {$p}newsletters (
            id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            type             ENUM('newsletter','template','autoresponder') NOT NULL DEFAULT 'newsletter',
            subject          VARCHAR(512) NOT NULL DEFAULT '',
            body_html        LONGTEXT,
            status           ENUM('draft','active','disabled','sent','sending','paused') NOT NULL DEFAULT 'draft',
            campaign_id      BIGINT UNSIGNED NOT NULL DEFAULT 0,
            list_ids         JSON,
            send_to_users    TEXT,
            recipient_count  INT UNSIGNED NOT NULL DEFAULT 0,
            sent_count       INT UNSIGNED NOT NULL DEFAULT 0,
            open_count       INT UNSIGNED NOT NULL DEFAULT 0,
            click_count      INT UNSIGNED NOT NULL DEFAULT 0,
            sent_at          DATETIME     DEFAULT NULL,
            scheduled_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),
            KEY idx_type_status (type, status)
        ) {$charset};";
    }

    private static function newsletter_tracking_campaigns( string $p, string $charset ): string {
        return "CREATE TABLE {$p}newsletter_tracking_campaigns (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            name        VARCHAR(255) NOT NULL DEFAULT '',
            status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
            position    INT UNSIGNED NOT NULL DEFAULT 0,
            created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) {$charset};";
    }

    // ── Newsletter per-recipient send log (Phase 5 / S239) ─────────────────
    //
    // One row per (newsletter, subscriber) pair when a send is dispatched.
    // Drives the WP-Cron drained queue (NewsletterDispatcher), powers the
    // admin progress bar, and serves as the cross-campaign suppression list
    // (any email with status='bounced' on any prior newsletter is skipped
    // on future sends).
    //
    // Status flow:
    //   queued  → sent          (success)
    //   queued  → failed        (3 attempts exhausted; error captured)
    //   queued  → skipped       (suppression hit: unsubscribed or prior bounce)
    //   sent    → bounced       (manual mark for now; future webhook entry)
    //
    // `attempts` increments on each cron drain pass that touches a queued
    // row. Dispatcher caps at 3. `error` captures the last wp_mail()
    // false-return reason (PHPMailer error or transport message).
    //
    // Indexes:
    //   - idx_newsletter_status: progress polling reads counts grouped by
    //     status for a given newsletter_id.
    //   - idx_email: suppression lookups across newsletters.
    //   - idx_queued: cron drain pulls the next batch by status='queued'
    //     ordered by id (FIFO within a campaign).
    private static function newsletter_sends( string $p, string $charset ): string {
        return "CREATE TABLE {$p}newsletter_sends (
            id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            newsletter_id  BIGINT UNSIGNED NOT NULL,
            subscriber_id  BIGINT UNSIGNED NOT NULL DEFAULT 0,
            email          VARCHAR(190) NOT NULL,
            status         ENUM('queued','sent','failed','bounced','skipped') NOT NULL DEFAULT 'queued',
            attempts       TINYINT UNSIGNED NOT NULL DEFAULT 0,
            error          VARCHAR(512) NOT NULL DEFAULT '',
            sent_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),
            KEY idx_newsletter_status (newsletter_id, status),
            KEY idx_email (email),
            KEY idx_queued (status, id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════
    // APPLICATIONS — To Do
    // ══════════════════════════════════════════════════════════════════════

    private static function todos( string $p, string $charset ): string {
        return "CREATE TABLE {$p}todos (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            title         VARCHAR(512)  NOT NULL DEFAULT '',
            description   TEXT,
            priority      ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
            status        ENUM('todo','in_progress','done') NOT NULL DEFAULT 'todo',
            due_date      DATE          DEFAULT NULL,
            assigned_to   BIGINT UNSIGNED NOT NULL DEFAULT 0,
            position      INT UNSIGNED  NOT NULL DEFAULT 0,
            created_by    BIGINT UNSIGNED NOT NULL DEFAULT 0,
            completed_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),
            KEY idx_status (status),
            KEY idx_assigned (assigned_to),
            KEY idx_due (due_date)
        ) {$charset};";
    }

    private static function calendar_events( string $p, string $charset ): string {
        return "CREATE TABLE {$p}calendar_events (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            title         VARCHAR(512)  NOT NULL DEFAULT '',
            description   TEXT,
            event_date    DATE          NOT NULL,
            start_time    TIME          DEFAULT NULL,
            end_time      TIME          DEFAULT NULL,
            all_day       TINYINT(1)    NOT NULL DEFAULT 0,
            color         VARCHAR(7)    NOT NULL DEFAULT '#2563EB',
            created_by    BIGINT UNSIGNED NOT NULL DEFAULT 0,
            created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_date (event_date)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // Notifications (admin notification bell — KI-26)
    // ══════════════════════════════════════════════════════════════════════════

    private static function notifications( string $p, string $charset ): string {
        return "CREATE TABLE {$p}notifications (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            type          VARCHAR(50)   NOT NULL DEFAULT 'general',
            title         VARCHAR(255)  NOT NULL DEFAULT '',
            body          VARCHAR(512)  NOT NULL DEFAULT '',
            link          VARCHAR(255)  NOT NULL DEFAULT '',
            object_type   VARCHAR(50)   NOT NULL DEFAULT '',
            object_id     BIGINT UNSIGNED NOT NULL DEFAULT 0,
            is_read       TINYINT(1)    NOT NULL DEFAULT 0,
            read_at       DATETIME      DEFAULT NULL,
            created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_unread (is_read, created_at),
            KEY idx_type (type),
            KEY idx_object (object_type, object_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // RMA (Returns)
    // ══════════════════════════════════════════════════════════════════════════

    private static function returns( string $p, string $charset ): string {
        return "CREATE TABLE {$p}returns (
            id            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            order_id      BIGINT UNSIGNED  NOT NULL,
            customer_id   BIGINT UNSIGNED  NULL,
            status        ENUM('requested','approved','declined','completed','cancelled') NOT NULL DEFAULT 'requested',
            reason_id     BIGINT UNSIGNED  NULL,
            action        ENUM('refund','exchange','store_credit') NOT NULL DEFAULT 'refund',
            comment       TEXT             NULL,
            admin_notes   TEXT             NULL,
            exchange_items JSON            NULL,
            replacement_order_id BIGINT UNSIGNED NULL,
            total_refund  DECIMAL(12,4)    NOT NULL DEFAULT 0.0000,
            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_id    (order_id),
            KEY idx_customer_id (customer_id),
            KEY idx_status      (status),
            KEY idx_created_at  (created_at),
            KEY idx_replacement_order_id (replacement_order_id)
        ) {$charset};";
    }

    private static function return_items( string $p, string $charset ): string {
        return "CREATE TABLE {$p}return_items (
            id             BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            return_id      BIGINT UNSIGNED  NOT NULL,
            order_item_id  BIGINT UNSIGNED  NOT NULL,
            product_id     BIGINT UNSIGNED  NOT NULL,
            product_name   VARCHAR(255)     NOT NULL DEFAULT '',
            quantity       INT UNSIGNED     NOT NULL DEFAULT 1,
            price          DECIMAL(12,4)    NOT NULL DEFAULT 0.0000,
            reason_id      BIGINT UNSIGNED  NULL,
            PRIMARY KEY (id),
            KEY idx_return_id     (return_id),
            KEY idx_order_item_id (order_item_id)
        ) {$charset};";
    }

    private static function return_reasons( string $p, string $charset ): string {
        return "CREATE TABLE {$p}return_reasons (
            id        BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            name      VARCHAR(255)     NOT NULL DEFAULT '',
            position  INT UNSIGNED     NOT NULL DEFAULT 0,
            status    ENUM('active','disabled') NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            KEY idx_status (status)
        ) {$charset};";
    }

    private static function return_history( string $p, string $charset ): string {
        return "CREATE TABLE {$p}return_history (
            id          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            return_id   BIGINT UNSIGNED  NOT NULL,
            old_status  VARCHAR(20)      NOT NULL DEFAULT '',
            new_status  VARCHAR(20)      NOT NULL DEFAULT '',
            changed_by  BIGINT UNSIGNED  NULL,
            note        TEXT             NULL,
            created_at  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_return_id (return_id)
        ) {$charset};";
    }

    // ══════════════════════════════════════════════════════════════════════════
    // Downloadable Products (Phase 1 — schema only, no UI/runtime yet)
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Files attached to downloadable products.
     *
     * One product can have N files. variation_id is set when the file is
     * specific to one variant (e.g. "MP3 mix" vs "FLAC mix" of the same album).
     * `file_path` is RELATIVE to wp-content/uploads/titancart/downloads/ so
     * deployments can move the upload root without touching the DB.
     */
    private static function downloadable_files( string $p, string $charset ): string {
        return "CREATE TABLE {$p}downloadable_files (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id      BIGINT UNSIGNED NOT NULL,
            variation_id    BIGINT UNSIGNED NULL,
            filename        VARCHAR(255)    NOT NULL DEFAULT '',
            original_name   VARCHAR(255)    NOT NULL DEFAULT '',
            file_path       VARCHAR(512)    NOT NULL DEFAULT '',
            file_size       BIGINT UNSIGNED NOT NULL DEFAULT 0,
            mime_type       VARCHAR(128)    NOT NULL DEFAULT '',
            preview_path    VARCHAR(512)    NULL,
            readme          TEXT            NULL,
            position        INT UNSIGNED    NOT NULL DEFAULT 0,
            status          ENUM('active','disabled') 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_product_id   (product_id),
            KEY idx_variation_id (variation_id),
            KEY idx_status       (status)
        ) {$charset};";
    }

    /**
     * Per-purchase download tokens.
     *
     * One row per (order_item × file). The raw token is shown ONCE to the
     * customer (via email and the My Account → Downloads page) and is never
     * stored — only its sha256 hash lives here. If this table leaks, the keys
     * are useless to attackers.
     *
     * status flow:
     *   active   → issued and currently downloadable
     *   expired  → cron flipped this when expires_at < NOW
     *   revoked  → admin or refund event killed it
     */
    private static function downloadable_keys( string $p, string $charset ): string {
        return "CREATE TABLE {$p}downloadable_keys (
            id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            key_hash           CHAR(64)        NOT NULL,
            order_id           BIGINT UNSIGNED NOT NULL,
            order_item_id      BIGINT UNSIGNED NOT NULL,
            product_id         BIGINT UNSIGNED NOT NULL,
            file_id            BIGINT UNSIGNED NOT NULL,
            customer_id        BIGINT UNSIGNED NULL,
            email              VARCHAR(255)    NOT NULL DEFAULT '',
            status             ENUM('active','expired','revoked') NOT NULL DEFAULT 'active',
            expires_at         DATETIME        NULL,
            download_count     INT UNSIGNED    NOT NULL DEFAULT 0,
            max_downloads      INT UNSIGNED    NULL,
            last_ip            VARCHAR(45)     NULL,
            last_downloaded_at DATETIME        NULL,
            created_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_key_hash (key_hash),
            KEY idx_order_id      (order_id),
            KEY idx_order_item_id (order_item_id),
            KEY idx_customer_id   (customer_id),
            KEY idx_email         (email),
            KEY idx_status        (status),
            KEY idx_expires_at    (expires_at)
        ) {$charset};";
    }

    /**
     * Download attempt log — analytics + abuse detection + chargeback evidence.
     *
     * Every interaction with a key produces one row: issued, downloaded,
     * expired, revoked, failed_auth (someone tried a bad/dead key), failed_quota
     * (someone exceeded max_downloads). Append-only — no UPDATE/DELETE in
     * normal operation. Cron prunes rows >12 months old.
     */
    private static function downloadable_log( string $p, string $charset ): string {
        return "CREATE TABLE {$p}downloadable_log (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            key_id        BIGINT UNSIGNED NULL,
            event         ENUM('issued','downloaded','expired','revoked','failed_auth','failed_quota') NOT NULL DEFAULT 'downloaded',
            ip_address    VARCHAR(45)     NULL,
            user_agent    VARCHAR(255)    NULL,
            bytes_served  BIGINT UNSIGNED NOT NULL DEFAULT 0,
            meta          JSON            NULL,
            created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_key_id     (key_id),
            KEY idx_event      (event),
            KEY idx_created_at (created_at)
        ) {$charset};";
    }

    /**
     * License agreements / Terms & Conditions for downloadable products.
     *
     * Versioned: editing content increments `version` and writes a new row;
     * old acceptances stay snapshotted to the version they actually saw.
     *
     * is_global=1 and product_id IS NULL → site-wide default that applies to
     * every downloadable product unless that product has its own row attached.
     * Off by default — admin opts in via the Phase 2 UI.
     */
    private static function downloadable_agreements( string $p, string $charset ): string {
        return "CREATE TABLE {$p}downloadable_agreements (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id      BIGINT UNSIGNED NULL,
            is_global       TINYINT(1)      NOT NULL DEFAULT 0,
            version         INT UNSIGNED    NOT NULL DEFAULT 1,
            title           VARCHAR(255)    NOT NULL DEFAULT '',
            content         LONGTEXT        NOT NULL,
            language_code   CHAR(2)         NOT NULL DEFAULT 'en',
            required        TINYINT(1)      NOT NULL DEFAULT 1,
            status          ENUM('active','archived') 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_product_id    (product_id),
            KEY idx_is_global     (is_global),
            KEY idx_status        (status),
            KEY idx_language_code (language_code)
        ) {$charset};";
    }

    /**
     * Customer acceptance records — the chargeback-evidence ledger.
     *
     * One row per (agreement × user/email × order). agreement_version is
     * snapshotted at acceptance time so updating the agreement doesn't
     * retroactively claim consent to new terms.
     *
     * accepted_content_hash is sha256 of the EXACT text the customer saw at
     * the moment they ticked the box — even if the agreement is later edited
     * back to look identical, the hash will mismatch and the integrity of
     * historical acceptances is preserved.
     */
    private static function downloadable_acceptances( string $p, string $charset ): string {
        return "CREATE TABLE {$p}downloadable_acceptances (
            id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            agreement_id           BIGINT UNSIGNED NOT NULL,
            agreement_version      INT UNSIGNED    NOT NULL DEFAULT 1,
            customer_id            BIGINT UNSIGNED NULL,
            email                  VARCHAR(255)    NOT NULL DEFAULT '',
            order_id               BIGINT UNSIGNED NULL,
            order_item_id          BIGINT UNSIGNED NULL,
            ip_address             VARCHAR(45)     NULL,
            user_agent             VARCHAR(255)    NULL,
            accepted_content_hash  CHAR(64)        NOT NULL,
            accepted_at            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_agreement_id  (agreement_id),
            KEY idx_customer_id   (customer_id),
            KEY idx_email         (email),
            KEY idx_order_id      (order_id),
            KEY idx_order_item_id (order_item_id),
            KEY idx_accepted_at   (accepted_at)
        ) {$charset};";
    }
}
