<?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_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}");

        $sql = "CREATE PROCEDURE {$sp_name}()
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,
        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_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),
        CASE WHEN f.is_pro = '1' OR f.is_pro = 'Ναι' THEN 1 ELSE 0 END,
        f.priorities_raw,
        IFNULL(f.disc_store,0), IFNULL(f.disc_google,0),
        IFNULL(f.disc_ad,0),    IFNULL(f.disc_wom,0),
        CASE WHEN f.eng_seminar    IN ('1','Ναι') THEN 1 ELSE 0 END,
        CASE WHEN f.eng_blog       IN ('1','Ναι') THEN 1 ELSE 0 END,
        CASE WHEN f.eng_fastfinder IN ('1','Ναι') THEN 1 ELSE 0 END,
        CASE WHEN f.social_login   IN ('1','Ναι') THEN 1 ELSE 0 END,
        CASE WHEN f.shipping_concern IN ('1','Ναι') THEN 1 ELSE 0 END,
        CASE WHEN f.will_followup  IN ('1','Ναι') THEN 1 ELSE 0 END,
        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_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),
        CASE WHEN f.items_clean     IN ('1','Ναι') THEN 1 ELSE 0 END,
        CASE WHEN f.will_repurchase IN ('1','Ναι') THEN 1 ELSE 0 END,
        CASE LOWER(IFNULL(f.gift_pref,''))
            WHEN 'πρόσωπο' THEN 'face'
            WHEN 'σώμα'    THEN 'body'
            WHEN 'μαλλιά'  THEN 'hair'
            WHEN 'νύχια'   THEN 'nails'
            WHEN 'αρώματα' THEN 'fragrance'
            ELSE ''
        END,
        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() {
        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}");

        $sql = "CREATE PROCEDURE {$sp_name}()
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;

    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
    )
    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
    UNION ALL
    -- F20-only customers (filled the post-delivery survey but the post-order
    -- F18 never landed — possible for older orders).
    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
    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();

    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);
    }
}
