<?php

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

if (!function_exists('aieo_surveys_install_sprocs')) {
    function aieo_surveys_install_sprocs() {
        global $wpdb;
        $prev_show     = isset($wpdb->show_errors) ? (bool) $wpdb->show_errors : false;
        $prev_suppress = isset($wpdb->suppress_errors) ? (bool) $wpdb->suppress_errors : false;
        $wpdb->hide_errors();
        $wpdb->suppress_errors(true);

        $ok = true;
        try {
            aieo_surveys_create_sp_ingest();
            aieo_surveys_create_sp_extract();
        } catch (\Throwable $e) {
            $ok = false;
            if (function_exists('aieo_debug_log')) {
                aieo_debug_log('AIEO_Surveys sproc install failed: ' . $e->getMessage());
            }
        }

        $wpdb->suppress_errors($prev_suppress);
        if ($prev_show) { $wpdb->show_errors(); }
        return $ok;
    }
}

if (!function_exists('aieo_surveys_scale_case')) {
    function aieo_surveys_scale_case($col, $form_id, $field_id) {
        global $wpdb;
        $whens = '';

        $scores = array();
        if (class_exists('GFAPI')) {
            $form = GFAPI::get_form((int) $form_id);
            if (is_array($form) && !empty($form['fields'])) {
                foreach ($form['fields'] as $field) {
                    $fid = is_object($field) ? (int) $field->id : (int) (isset($field['id']) ? $field['id'] : 0);
                    if ($fid !== (int) $field_id) {
                        continue;
                    }
                    $choices = is_object($field) ? $field->choices : (isset($field['choices']) ? $field['choices'] : array());
                    $pos     = 0;
                    foreach ((array) $choices as $choice) {
                        $pos++;
                        $value = (string) (isset($choice['value']) ? $choice['value'] : '');
                        if ('' === $value) {
                            continue;
                        }
                        
                        
                        
                        
                        
                        
                        
                        
                        $score = (isset($choice['score']) && is_numeric($choice['score'])) ? (float) $choice['score'] : (float) $pos;
                        
                        
                        
                        
                        
                        
                        
                        
                        $whens   .= ' WHEN ' . $col . ' = ' . $wpdb->prepare('%s', $value) . ' THEN ' . (int) round($score);
                        $scores[] = (int) round($score);
                    }
                    break;
                }
            }
        }

        if (!$scores) {
            
            
            
            
            return "CASE WHEN {$col} REGEXP '^[1-5]\$' THEN CAST({$col} AS UNSIGNED) END";
        }

        
        
        
        
        
        
        
        return 'CASE' . $whens
            . " WHEN {$col} REGEXP '^[0-9]+\$'"
            . ' AND CAST(' . $col . ' AS UNSIGNED) BETWEEN ' . min($scores) . ' AND ' . max($scores)
            . ' THEN CAST(' . $col . ' AS UNSIGNED)'
            . ' END';
    }
}

if (!function_exists('aieo_surveys_yes_case')) {
    
    function aieo_surveys_yes_case($col) {
        global $wpdb;

        
        $whens = ' WHEN ' . $col . " = '1' THEN 1";

        $seen = array('1' => true);
        if (class_exists('AIEO_Survey_Choice_Register')) {
            foreach ((array) AIEO_Survey_Choice_Register::all(500) as $row) {
                if ('yes' !== (string) $row['choice_code']) {
                    continue;
                }
                $value = (string) $row['observed_value'];
                if ('' === $value || isset($seen[$value])) {
                    continue;
                }
                $seen[$value] = true;
                $whens .= ' WHEN ' . $col . ' = ' . $wpdb->prepare('%s', $value) . ' THEN 1';
            }
        }
        return 'CASE' . $whens . ' ELSE 0 END';
    }
}

if (!function_exists('aieo_surveys_code_case')) {
    
    function aieo_surveys_code_case($col, $question_code) {
        global $wpdb;
        $whens = '';
        $seen  = array();

        $allowed = array();
        if (class_exists('AIEO_Survey_Blueprint')) {
            $question = AIEO_Survey_Blueprint::question($question_code);
            if ($question) {
                $allowed = $question['choices'];
            }
        }
        if ($allowed && class_exists('AIEO_Survey_Choice_Register')) {
            foreach ((array) AIEO_Survey_Choice_Register::all(500) as $row) {
                $code  = (string) $row['choice_code'];
                $value = (string) $row['observed_value'];
                
                
                
                
                if ('' === $value || !isset($allowed[$code]) || isset($seen[$value])) {
                    continue;
                }
                $seen[$value] = true;
                $whens .= ' WHEN LOWER(' . $col . ') = LOWER(' . $wpdb->prepare('%s', $value) . ') THEN ' . $wpdb->prepare('%s', $code);
            }
        }
        return $whens ? ('CASE' . $whens . " ELSE '' END") : "''";
    }
}

if (!function_exists('aieo_surveys_create_sp_ingest')) {
    function aieo_surveys_create_sp_ingest() {
        global $wpdb;
        $sp_name = 'aieo_sp_ingest_survey_responses';

        $tbl_sr  = $wpdb->prefix . 'aieo_survey_responses';
        $tbl_ci  = $wpdb->prefix . 'aieo_customer_intelligence';
        $tbl_e   = $wpdb->prefix . 'gf_entry';
        $tbl_em  = $wpdb->prefix . 'gf_entry_meta';

        $wpdb->query("DROP PROCEDURE IF EXISTS {$sp_name}");

        
        
        
        $case_32 = aieo_surveys_scale_case('em.meta_value', 18, 32);
        $case_62 = aieo_surveys_scale_case('em.meta_value', 18, 62);
        $case_63 = aieo_surveys_scale_case('em.meta_value', 18, 63);
        $case_64 = aieo_surveys_scale_case('em.meta_value', 18, 64);

        $sql = "CREATE PROCEDURE {$sp_name}()
SQL SECURITY INVOKER
BEGIN
    -- =====================================================================
    -- This sproc does the pivot + raw INSERT only.  eponymous_id and
    -- wp_user_id are deliberately set to 0 here — the PHP wrapper
    -- (AIEO_DMM_Surveys::backfill_eponymous_via_order_id) resolves them
    -- afterwards via a JOIN against the AIEO order snapshots, which is
    -- both faster than the email path and architecturally cleaner: GF
    -- field 15 (order_id) is the canonical primary key for a survey
    -- response, not the email.
    -- =====================================================================

    -- =====================================================================
    -- Step 1: Form 18 — per-entry pivot
    --
    -- Field IDs:
    --   15 — WC order_id    (hidden, prefilled at thank-you page)
    --   16 — WC customer_id (hidden, prefilled — = wp_users.ID)
    --   92 — billing email  (kept for display + guest fallback)
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _f18;
    CREATE TEMPORARY TABLE _f18 AS
    SELECT
        e.id                                                          AS gf_entry_id,
        e.date_created                                                AS submitted_at,
        MAX(CASE WHEN em.meta_key = '15' THEN em.meta_value END)      AS order_id,
        MAX(CASE WHEN em.meta_key = '16' THEN em.meta_value END)      AS customer_id,
        MAX(CASE WHEN em.meta_key = '92' THEN em.meta_value END)      AS email,
        MAX(CASE WHEN em.meta_key = '18' THEN em.meta_value END)      AS nps,
        MAX(CASE WHEN em.meta_key = '7'  THEN em.meta_value END)      AS satisfaction,
        AVG(CASE
              WHEN em.meta_key IN ('21','22','23','24','25','26','27','29','30','31','33','47')
                AND em.meta_value REGEXP '^[1-5]\$'
              THEN CAST(em.meta_value AS UNSIGNED) END)               AS site_ux_avg,
        MAX(CASE WHEN em.meta_key = '51' THEN em.meta_value END)      AS is_pro,
        MAX(CASE WHEN em.meta_key = '8'  THEN em.meta_value END)      AS priorities_raw,
        MAX(CASE WHEN em.meta_key LIKE '44%' AND em.meta_value <> '' THEN 1 ELSE 0 END) AS disc_store,
        MAX(CASE WHEN em.meta_key LIKE '45%' AND em.meta_value <> '' THEN 1 ELSE 0 END) AS disc_google,
        MAX(CASE WHEN em.meta_key LIKE '73%' AND em.meta_value <> '' THEN 1 ELSE 0 END) AS disc_ad,
        MAX(CASE WHEN em.meta_key LIKE '43%' AND em.meta_value <> '' THEN 1 ELSE 0 END) AS disc_wom,
        MAX(CASE WHEN em.meta_key = '35' THEN em.meta_value END)      AS eng_seminar,
        MAX(CASE WHEN em.meta_key = '38' THEN em.meta_value END)      AS eng_blog,
        MAX(CASE WHEN em.meta_key = '36' THEN em.meta_value END)      AS eng_fastfinder,
        -- The quality beside each of those three booleans, plus the one about
        -- our own recommendations. Every one resolves through its field's
        -- choices, so a token-valued scale reads as a number instead of being
        -- dropped by the guard. NULL stays NULL and lands as the 0 that means
        -- \"not answered\" — never averaged anywhere without excluding it.
        MAX(CASE WHEN em.meta_key = '62' THEN {$case_62} END)         AS rating_seminar,
        MAX(CASE WHEN em.meta_key = '63' THEN {$case_63} END)         AS rating_blog,
        MAX(CASE WHEN em.meta_key = '64' THEN {$case_64} END)         AS rating_fastfinder,
        MAX(CASE WHEN em.meta_key = '32' THEN {$case_32} END)         AS rating_recommendations,
        MAX(CASE WHEN em.meta_key = '37' THEN em.meta_value END)      AS social_login,
        MAX(CASE WHEN em.meta_key = '39' THEN em.meta_value END)      AS shipping_concern,
        MAX(CASE WHEN em.meta_key = '70' THEN em.meta_value END)      AS will_followup,
        MAX(CASE WHEN em.meta_key = '12' THEN em.meta_value END)      AS open_feedback
    FROM {$tbl_e} e
    JOIN {$tbl_em} em ON em.entry_id = e.id
    WHERE e.form_id = 18 AND e.status = 'active'
    GROUP BY e.id, e.date_created;

    -- =====================================================================
    -- Step 2: Upsert F18 rows into responses table.
    --
    -- Resolution waterfall:
    --   1. Try ci_wp.wp_user_id = customer_id  (indexed integer JOIN — fast)
    --   2. Fall back to ci_em.email = email    (indexed via _ci_email_idx)
    --
    -- The wp_user_id JOIN handles registered customers (the common case);
    -- the email JOIN catches guests where customer_id = 0.
    -- =====================================================================
    INSERT INTO {$tbl_sr} (
        form_id, gf_entry_id, order_id, customer_id,
        eponymous_id, wp_user_id, email, submitted_at,
        f18_nps, f18_satisfaction, f18_site_ux_avg, f18_is_pro,
        f18_priorities_raw,
        f18_disc_store, f18_disc_google, f18_disc_ad, f18_disc_wom,
        f18_eng_seminar, f18_eng_blog, f18_eng_fastfinder,
        f18_rating_seminar, f18_rating_blog, f18_rating_fastfinder,
        f18_rating_recommendations,
        f18_social_login, f18_shipping_concern, f18_will_followup,
        f18_open_feedback
    )
    SELECT
        18, f.gf_entry_id,
        IFNULL(CAST(NULLIF(f.order_id,'')    AS UNSIGNED), 0),
        IFNULL(CAST(NULLIF(f.customer_id,'') AS UNSIGNED), 0),
        0 AS eponymous_id,                          -- resolved by PHP backfill
        IFNULL(CAST(NULLIF(f.customer_id,'') AS UNSIGNED), 0),
        IFNULL(LOWER(TRIM(f.email)), ''),
        f.submitted_at,
        IFNULL(CAST(NULLIF(f.nps,'') AS UNSIGNED), 0),
        IFNULL(CAST(NULLIF(f.satisfaction,'') AS UNSIGNED), 0),
        IFNULL(ROUND(f.site_ux_avg, 2), 0),
        " . aieo_surveys_yes_case('f.is_pro') . ",
        f.priorities_raw,
        IFNULL(f.disc_store,0), IFNULL(f.disc_google,0),
        IFNULL(f.disc_ad,0),    IFNULL(f.disc_wom,0),
        " . aieo_surveys_yes_case('f.eng_seminar') . ",
        " . aieo_surveys_yes_case('f.eng_blog') . ",
        " . aieo_surveys_yes_case('f.eng_fastfinder') . ",
        IFNULL(f.rating_seminar, 0),
        IFNULL(f.rating_blog, 0),
        IFNULL(f.rating_fastfinder, 0),
        IFNULL(f.rating_recommendations, 0),
        " . aieo_surveys_yes_case('f.social_login') . ",
        " . aieo_surveys_yes_case('f.shipping_concern') . ",
        " . aieo_surveys_yes_case('f.will_followup') . ",
        f.open_feedback
    FROM _f18 f
    WHERE f.gf_entry_id IS NOT NULL
    ON DUPLICATE KEY UPDATE
        order_id            = VALUES(order_id),
        customer_id         = VALUES(customer_id),
        -- eponymous_id + wp_user_id deliberately NOT updated here — the
        -- PHP backfill owns them. Re-running ingest must not clobber a
        -- successful resolution back to 0.
        email               = VALUES(email),
        submitted_at        = VALUES(submitted_at),
        f18_nps             = VALUES(f18_nps),
        f18_satisfaction    = VALUES(f18_satisfaction),
        f18_site_ux_avg     = VALUES(f18_site_ux_avg),
        f18_is_pro          = VALUES(f18_is_pro),
        f18_priorities_raw  = VALUES(f18_priorities_raw),
        f18_disc_store      = VALUES(f18_disc_store),
        f18_disc_google     = VALUES(f18_disc_google),
        f18_disc_ad         = VALUES(f18_disc_ad),
        f18_disc_wom        = VALUES(f18_disc_wom),
        f18_eng_seminar     = VALUES(f18_eng_seminar),
        f18_eng_blog        = VALUES(f18_eng_blog),
        f18_eng_fastfinder  = VALUES(f18_eng_fastfinder),
        f18_rating_seminar  = VALUES(f18_rating_seminar),
        f18_rating_blog     = VALUES(f18_rating_blog),
        f18_rating_fastfinder = VALUES(f18_rating_fastfinder),
        f18_rating_recommendations = VALUES(f18_rating_recommendations),
        f18_social_login    = VALUES(f18_social_login),
        f18_shipping_concern= VALUES(f18_shipping_concern),
        f18_will_followup   = VALUES(f18_will_followup),
        f18_open_feedback   = VALUES(f18_open_feedback),
        updated_at          = UTC_TIMESTAMP();

    -- =====================================================================
    -- Step 3: Form 20 — per-entry pivot
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _f20;
    CREATE TEMPORARY TABLE _f20 AS
    SELECT
        e.id                                                          AS gf_entry_id,
        e.date_created                                                AS submitted_at,
        MAX(CASE WHEN em.meta_key = '17' THEN em.meta_value END)      AS email,
        MAX(CASE WHEN em.meta_key = '21' THEN em.meta_value END)      AS delivery_speed,
        MAX(CASE WHEN em.meta_key = '101' THEN em.meta_value END)     AS packaging,
        MAX(CASE WHEN em.meta_key = '103' THEN em.meta_value END)     AS items_clean,
        MAX(CASE WHEN em.meta_key = '109' THEN em.meta_value END)     AS will_repurchase,
        MAX(CASE WHEN em.meta_key = '151' THEN em.meta_value END)     AS gift_pref,
        MAX(CASE WHEN em.meta_key = '123' THEN em.meta_value END)     AS hair_color,
        MAX(CASE WHEN em.meta_key = '124' THEN em.meta_value END)     AS eye_color,
        MAX(CASE WHEN em.meta_key = '125' THEN em.meta_value END)     AS skin_type,
        MAX(CASE WHEN em.meta_key = '126' THEN em.meta_value END)     AS silhouette,
        MAX(CASE WHEN em.meta_key = '128' THEN em.meta_value END)     AS height
    FROM {$tbl_e} e
    JOIN {$tbl_em} em ON em.entry_id = e.id
    WHERE e.form_id = 20 AND e.status = 'active'
    GROUP BY e.id, e.date_created;

    -- =====================================================================
    -- Step 4: Upsert F20 rows.
    -- =====================================================================
    INSERT INTO {$tbl_sr} (
        form_id, gf_entry_id, eponymous_id, wp_user_id, email, submitted_at,
        f20_delivery_speed, f20_packaging, f20_items_clean, f20_will_repurchase,
        f20_gift_pref, f20_hair_color, f20_eye_color, f20_skin_type,
        f20_silhouette, f20_height
    )
    SELECT
        20, f.gf_entry_id,
        0 AS eponymous_id,                          -- resolved by PHP backfill
        0 AS wp_user_id,
        IFNULL(LOWER(TRIM(f.email)), ''),
        f.submitted_at,
        IFNULL(f.delivery_speed, ''),
        IFNULL(CAST(NULLIF(f.packaging,'') AS UNSIGNED), 0),
        " . aieo_surveys_yes_case('f.items_clean') . ",
        " . aieo_surveys_yes_case('f.will_repurchase') . ",
        " . aieo_surveys_code_case("IFNULL(f.gift_pref,'')", 'product_purpose') . ",
        IFNULL(f.hair_color,''),
        IFNULL(f.eye_color,''),
        IFNULL(f.skin_type,''),
        IFNULL(f.silhouette,''),
        IFNULL(f.height,'')
    FROM _f20 f
    WHERE f.gf_entry_id IS NOT NULL
    ON DUPLICATE KEY UPDATE
        -- eponymous_id + wp_user_id owned by PHP backfill — see F18 note.
        email                = VALUES(email),
        submitted_at         = VALUES(submitted_at),
        f20_delivery_speed   = VALUES(f20_delivery_speed),
        f20_packaging        = VALUES(f20_packaging),
        f20_items_clean      = VALUES(f20_items_clean),
        f20_will_repurchase  = VALUES(f20_will_repurchase),
        f20_gift_pref        = VALUES(f20_gift_pref),
        f20_hair_color       = VALUES(f20_hair_color),
        f20_eye_color        = VALUES(f20_eye_color),
        f20_skin_type        = VALUES(f20_skin_type),
        f20_silhouette       = VALUES(f20_silhouette),
        f20_height           = VALUES(f20_height),
        updated_at           = UTC_TIMESTAMP();

    DROP TEMPORARY TABLE IF EXISTS _f18;
    DROP TEMPORARY TABLE IF EXISTS _f20;
END";

        $wpdb->query($sql);
    }
}

if (!function_exists('aieo_surveys_create_sp_extract')) {
    function aieo_surveys_create_sp_extract() {
        if (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) {
            return; 
        }
        global $wpdb;
        $sp_name = 'aieo_sp_extract_survey_intelligence';

        $tbl_si  = $wpdb->prefix . 'aieo_survey_intelligence';
        $tbl_sr  = $wpdb->prefix . 'aieo_survey_responses';

        $wpdb->query("DROP PROCEDURE IF EXISTS {$sp_name}");

        
        
        
        $si_insert = "INSERT INTO {$tbl_si} (
        eponymous_id, wp_user_id, email,
        f18_entry_id, f18_submitted_at,
        nps_score, nps_class, overall_satisfaction, site_ux_avg,
        is_professional_verified, personal_priorities, discovery_channel,
        engagement_seminar, engagement_blog, engagement_fastfinder,
        social_login, shipping_price_concern, will_take_followup_survey, open_feedback_f18,
        f20_entry_id, f20_submitted_at,
        delivery_speed_bucket, packaging_score, items_unscratched,
        will_repurchase, gift_category_preference, personal_profile_json,
        voc_score, voc_band, intelligence_version, updated_at
    )";
        $si_upsert = "ON DUPLICATE KEY UPDATE
        wp_user_id              = VALUES(wp_user_id),
        email                   = VALUES(email),
        f18_entry_id            = VALUES(f18_entry_id),
        f18_submitted_at        = VALUES(f18_submitted_at),
        nps_score               = VALUES(nps_score),
        nps_class               = VALUES(nps_class),
        overall_satisfaction    = VALUES(overall_satisfaction),
        site_ux_avg             = VALUES(site_ux_avg),
        is_professional_verified= VALUES(is_professional_verified),
        personal_priorities     = VALUES(personal_priorities),
        discovery_channel       = VALUES(discovery_channel),
        engagement_seminar      = VALUES(engagement_seminar),
        engagement_blog         = VALUES(engagement_blog),
        engagement_fastfinder   = VALUES(engagement_fastfinder),
        social_login            = VALUES(social_login),
        shipping_price_concern  = VALUES(shipping_price_concern),
        will_take_followup_survey = VALUES(will_take_followup_survey),
        open_feedback_f18       = VALUES(open_feedback_f18),
        f20_entry_id            = VALUES(f20_entry_id),
        f20_submitted_at        = VALUES(f20_submitted_at),
        delivery_speed_bucket   = VALUES(delivery_speed_bucket),
        packaging_score         = VALUES(packaging_score),
        items_unscratched       = VALUES(items_unscratched),
        will_repurchase         = VALUES(will_repurchase),
        gift_category_preference= VALUES(gift_category_preference),
        personal_profile_json   = VALUES(personal_profile_json),
        voc_score               = VALUES(voc_score),
        intelligence_version    = 1,
        updated_at              = UTC_TIMESTAMP()";

        $sql = "CREATE PROCEDURE {$sp_name}()
SQL SECURITY INVOKER
BEGIN
    -- Latest F18 row per eponymous_id (skip unresolved emails).
    DROP TEMPORARY TABLE IF EXISTS _f18_latest;
    CREATE TEMPORARY TABLE _f18_latest AS
    SELECT * FROM (
        SELECT sr.*,
               ROW_NUMBER() OVER (
                   PARTITION BY eponymous_id
                   ORDER BY submitted_at DESC, response_id DESC
               ) AS rn
        FROM {$tbl_sr} sr
        WHERE form_id = 18 AND eponymous_id > 0
    ) ranked
    WHERE rn = 1;

    -- Latest F20 row per eponymous_id.
    DROP TEMPORARY TABLE IF EXISTS _f20_latest;
    CREATE TEMPORARY TABLE _f20_latest AS
    SELECT * FROM (
        SELECT sr.*,
               ROW_NUMBER() OVER (
                   PARTITION BY eponymous_id
                   ORDER BY submitted_at DESC, response_id DESC
               ) AS rn
        FROM {$tbl_sr} sr
        WHERE form_id = 20 AND eponymous_id > 0
    ) ranked
    WHERE rn = 1;

    {$si_insert}
    SELECT
        COALESCE(f18.eponymous_id, f20.eponymous_id) AS eponymous_id,
        COALESCE(f18.wp_user_id,   f20.wp_user_id, 0),
        COALESCE(f18.email,        f20.email, ''),

        IFNULL(f18.gf_entry_id, 0), f18.submitted_at,
        IFNULL(f18.f18_nps, 0),
        CASE
            WHEN f18.f18_nps BETWEEN 4 AND 5 THEN 'promoter'
            WHEN f18.f18_nps = 3              THEN 'passive'
            WHEN f18.f18_nps BETWEEN 1 AND 2 THEN 'detractor'
            ELSE ''
        END,
        IFNULL(f18.f18_satisfaction, 0),
        IFNULL(f18.f18_site_ux_avg, 0),
        IFNULL(f18.f18_is_pro, 0),
        f18.f18_priorities_raw,
        CASE
            WHEN (IFNULL(f18.f18_disc_store,0) + IFNULL(f18.f18_disc_google,0)
                  + IFNULL(f18.f18_disc_ad,0) + IFNULL(f18.f18_disc_wom,0)) > 1 THEN 'multi'
            WHEN f18.f18_disc_store  = 1 THEN 'store'
            WHEN f18.f18_disc_google = 1 THEN 'google'
            WHEN f18.f18_disc_ad     = 1 THEN 'ad'
            WHEN f18.f18_disc_wom    = 1 THEN 'wom'
            WHEN f18.gf_entry_id IS NOT NULL THEN 'unknown'
            ELSE ''
        END,
        IFNULL(f18.f18_eng_seminar, 0),
        IFNULL(f18.f18_eng_blog, 0),
        IFNULL(f18.f18_eng_fastfinder, 0),
        IFNULL(f18.f18_social_login, 0),
        IFNULL(f18.f18_shipping_concern, 0),
        IFNULL(f18.f18_will_followup, 0),
        f18.f18_open_feedback,

        IFNULL(f20.gf_entry_id, 0), f20.submitted_at,
        IFNULL(f20.f20_delivery_speed, ''),
        IFNULL(f20.f20_packaging, 0),
        IFNULL(f20.f20_items_clean, 0),
        IFNULL(f20.f20_will_repurchase, 0),
        IFNULL(f20.f20_gift_pref, ''),
        CASE WHEN f20.gf_entry_id IS NOT NULL THEN
            CONCAT('{',
                '\"hair_color\":\"', IFNULL(f20.f20_hair_color,''), '\",',
                '\"eye_color\":\"',  IFNULL(f20.f20_eye_color,''),  '\",',
                '\"skin_type\":\"',  IFNULL(f20.f20_skin_type,''),  '\",',
                '\"silhouette\":\"', IFNULL(f20.f20_silhouette,''), '\",',
                '\"height\":\"',     IFNULL(f20.f20_height,''),     '\"',
            '}')
            ELSE NULL
        END,

        -- VoC composite 0-100: 40% NPS + 30% satisfaction + 20% UX avg
        --                    + 10% will-repurchase (when F20 present)
        LEAST(100, GREATEST(0, ROUND(
            (IFNULL((CAST(f18.f18_nps          AS DECIMAL(3,1)) - 1) / 4 * 40, 0))
          + (IFNULL((CAST(f18.f18_satisfaction AS DECIMAL(3,1)) - 1) / 4 * 30, 0))
          + (IFNULL((f18.f18_site_ux_avg                       - 1) / 4 * 20, 0))
          + CASE WHEN f20.f20_will_repurchase = 1 THEN 10 ELSE 0 END
        ))),
        '',
        1,
        UTC_TIMESTAMP()
    FROM _f18_latest f18
    LEFT JOIN _f20_latest f20 ON f20.eponymous_id = f18.eponymous_id
    WHERE f18.eponymous_id > 0
    {$si_upsert};

    -- F20-only customers (filled the post-delivery survey but the post-order
    -- F18 never landed — possible for older orders).
    {$si_insert}
    SELECT
        f20.eponymous_id, f20.wp_user_id, f20.email,
        0, NULL,
        0, '', 0, 0,
        0, NULL, '',
        0, 0, 0,
        0, 0, 0, NULL,
        f20.gf_entry_id, f20.submitted_at,
        IFNULL(f20.f20_delivery_speed, ''),
        IFNULL(f20.f20_packaging, 0),
        IFNULL(f20.f20_items_clean, 0),
        IFNULL(f20.f20_will_repurchase, 0),
        IFNULL(f20.f20_gift_pref, ''),
        CONCAT('{',
            '\"hair_color\":\"', IFNULL(f20.f20_hair_color,''), '\",',
            '\"eye_color\":\"',  IFNULL(f20.f20_eye_color,''),  '\",',
            '\"skin_type\":\"',  IFNULL(f20.f20_skin_type,''),  '\",',
            '\"silhouette\":\"', IFNULL(f20.f20_silhouette,''), '\",',
            '\"height\":\"',     IFNULL(f20.f20_height,''),     '\"',
        '}'),
        LEAST(100, GREATEST(0, ROUND(
            CASE WHEN f20.f20_will_repurchase = 1 THEN 10 ELSE 0 END
        ))),
        '',
        1,
        UTC_TIMESTAMP()
    FROM _f20_latest f20
    LEFT JOIN _f18_latest f18 ON f18.eponymous_id = f20.eponymous_id
    WHERE f20.eponymous_id > 0 AND f18.eponymous_id IS NULL
    {$si_upsert};

    UPDATE {$tbl_si}
    SET voc_band = CASE
        WHEN voc_score >= 80 THEN 'high'
        WHEN voc_score >= 50 THEN 'mid'
        ELSE 'low'
    END;

    DROP TEMPORARY TABLE IF EXISTS _f18_latest;
    DROP TEMPORARY TABLE IF EXISTS _f20_latest;
END";

        $wpdb->query($sql);
    }
}