<?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 = $wpdb->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,
            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 = $wpdb->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);
    }
}
