<?php

if (!defined('ABSPATH')) {
    exit;
}

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

if (!function_exists('aieo_surveys_create_responses_table')) {
    function aieo_surveys_create_responses_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_survey_responses';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            response_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            form_id smallint(5) UNSIGNED NOT NULL,
            gf_entry_id bigint(20) UNSIGNED NOT NULL,

            -- Direct keys read from GF hidden fields (F18 field 15 / 16) ----
            -- Lets us link a response to its WC order + customer without an
            -- email round-trip. Email column is kept for display + guest
            -- fallback (customer_id = 0).
            order_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            customer_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,

            eponymous_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            wp_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            email varchar(190) NOT NULL DEFAULT '',
            submitted_at datetime NULL,

            -- Form 18 (post-order) raw extracted fields ----------------------
            f18_nps tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            f18_satisfaction tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            f18_site_ux_avg decimal(3,2) NOT NULL DEFAULT 0,
            f18_is_pro tinyint(1) NOT NULL DEFAULT 0,
            f18_priorities_raw text NULL,
            f18_disc_store tinyint(1) NOT NULL DEFAULT 0,
            f18_disc_google tinyint(1) NOT NULL DEFAULT 0,
            f18_disc_ad tinyint(1) NOT NULL DEFAULT 0,
            f18_disc_wom tinyint(1) NOT NULL DEFAULT 0,
            f18_eng_seminar tinyint(1) NOT NULL DEFAULT 0,
            f18_eng_blog tinyint(1) NOT NULL DEFAULT 0,
            f18_eng_fastfinder tinyint(1) NOT NULL DEFAULT 0,

            -- THE QUALITY BESIDE THE BOOLEAN -------------------------------
            -- The three columns above record whether somebody used the blog,
            -- the seminars and the finder. The survey has ALWAYS also asked how
            -- good each was, and for three years those answers landed nowhere:
            -- 1,158 ratings sitting in Gravity's tables against 3 booleans we
            -- did read. We kept whether it was used and threw away whether it
            -- was any good, which is the half that could change a decision.
            --
            -- 0 means NOT ANSWERED, exactly as f18_nps and f18_satisfaction
            -- already do — which is why no report ever averages one of these
            -- columns without excluding the zeros first.
            f18_rating_seminar tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            f18_rating_blog tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            f18_rating_fastfinder tinyint(3) UNSIGNED NOT NULL DEFAULT 0,

            -- 🚨 DIRECT CUSTOMER FEEDBACK ON THE RECOMMENDATION ENGINE, from
            -- 2,590 people, never once read. Its choices store opaque
            -- `glikertcol…` tokens rather than '1'..'5', so the pivot's
            -- REGEXP '^[1-5]$' guard dropped it silently — the guard did the
            -- right thing (a token cast to 0 would have poisoned the UX
            -- average) for the wrong reason, and nobody noticed it had also
            -- made the field invisible. The tokens are resolved through the
            -- field's own choices now; see aieo_surveys_scale_case().
            --
            -- It is deliberately NOT joined to f18_site_ux_avg. That column's
            -- composition is frozen: adding a thirteenth field to the average
            -- retroactively changes every historical value on a column both the
            -- report and the VoC score read.
            f18_rating_recommendations tinyint(3) UNSIGNED NOT NULL DEFAULT 0,

            f18_social_login tinyint(1) NOT NULL DEFAULT 0,
            f18_shipping_concern tinyint(1) NOT NULL DEFAULT 0,
            f18_will_followup tinyint(1) NOT NULL DEFAULT 0,
            f18_open_feedback text NULL,

            -- Form 20 (post-delivery) raw extracted fields -------------------
            f20_delivery_speed varchar(40) NOT NULL DEFAULT '',
            f20_packaging tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            f20_items_clean tinyint(1) NOT NULL DEFAULT 0,
            f20_will_repurchase tinyint(1) NOT NULL DEFAULT 0,
            f20_gift_pref varchar(40) NOT NULL DEFAULT '',
            f20_hair_color varchar(40) NOT NULL DEFAULT '',
            f20_eye_color varchar(40) NOT NULL DEFAULT '',
            f20_skin_type varchar(40) NOT NULL DEFAULT '',
            f20_silhouette varchar(40) NOT NULL DEFAULT '',
            f20_height varchar(40) NOT NULL DEFAULT '',

            ingested_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (response_id),
            UNIQUE KEY uk_form_entry (form_id, gf_entry_id),
            KEY idx_order (order_id),
            KEY idx_customer (customer_id),
            KEY idx_email (email),
            KEY idx_eponymous (eponymous_id),
            KEY idx_form_submitted (form_id, submitted_at)
        ) {$charset};";

        aieo_dbdelta($sql);
    }
}

if (!function_exists('aieo_surveys_create_intelligence_table')) {
    function aieo_surveys_create_intelligence_table() {
        global $wpdb;
        $table   = $wpdb->prefix . 'aieo_survey_intelligence';
        $charset = aieo_get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            eponymous_id bigint(20) UNSIGNED NOT NULL,
            wp_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            email varchar(190) NOT NULL DEFAULT '',

            -- Form 18 (post-order) ---------------------------------------------
            f18_entry_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            f18_submitted_at datetime NULL,

            -- 7 high-signal metrics
            nps_score tinyint(3) UNSIGNED NOT NULL DEFAULT 0,                 -- 0=missing, 1-5
            nps_class varchar(12) NOT NULL DEFAULT '',                         -- detractor | passive | promoter
            overall_satisfaction tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            site_ux_avg decimal(3,2) NOT NULL DEFAULT 0,                       -- avg of UX 1-5 fields
            is_professional_verified tinyint(1) NOT NULL DEFAULT 0,            -- F18 q51
            personal_priorities longtext NULL,                                 -- JSON ordered array of priority slugs
            discovery_channel varchar(40) NOT NULL DEFAULT '',                 -- store|google|ad|wom|multi|unknown

            -- Engagement fingerprints from F18 booleans
            engagement_seminar tinyint(1) NOT NULL DEFAULT 0,                  -- F18 q35
            engagement_blog tinyint(1) NOT NULL DEFAULT 0,                     -- F18 q38
            engagement_fastfinder tinyint(1) NOT NULL DEFAULT 0,               -- F18 q36
            social_login tinyint(1) NOT NULL DEFAULT 0,                        -- F18 q37
            shipping_price_concern tinyint(1) NOT NULL DEFAULT 0,              -- F18 q39
            will_take_followup_survey tinyint(1) NOT NULL DEFAULT 0,           -- F18 q70
            open_feedback_f18 text NULL,                                       -- F18 q12 free text

            -- Form 20 (post-delivery) -------------------------------------------
            f20_entry_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
            f20_submitted_at datetime NULL,

            delivery_speed_bucket varchar(20) NOT NULL DEFAULT '',             -- F20 q21
            packaging_score tinyint(3) UNSIGNED NOT NULL DEFAULT 0,            -- F20 q101
            items_unscratched tinyint(1) NOT NULL DEFAULT 0,                   -- F20 q103
            will_repurchase tinyint(1) NOT NULL DEFAULT 0,                     -- F20 q109
            gift_category_preference varchar(20) NOT NULL DEFAULT '',          -- F20 q151
            personal_profile_json longtext NULL,                               -- F20 q123-128 → {hair,eyes,skin,silhouette,height}

            -- Composite VoC score 0-100 — read by triggers + recommendation engine
            voc_score tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
            voc_band varchar(12) NOT NULL DEFAULT '',                          -- low | mid | high

            -- Purchase correlation, anchored on f18_submitted_at -------------
            orders_before_survey smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            orders_after_survey  smallint(5) UNSIGNED NOT NULL DEFAULT 0,
            spend_before_survey  decimal(12,2) NOT NULL DEFAULT 0,
            spend_after_survey   decimal(12,2) NOT NULL DEFAULT 0,

            intelligence_version smallint(5) UNSIGNED NOT NULL DEFAULT 1,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (eponymous_id),
            KEY idx_email (email),
            KEY idx_wp_user (wp_user_id),
            KEY idx_nps_class (nps_class),
            KEY idx_voc_score (voc_score),
            KEY idx_is_pro (is_professional_verified),
            KEY idx_will_repurchase (will_repurchase),
            KEY idx_gift_pref (gift_category_preference),
            KEY idx_discovery (discovery_channel)
        ) {$charset};";

        aieo_dbdelta($sql);
    }
}