<?php

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

if (!class_exists('AIEO_Form_Entries')) {

    class AIEO_Form_Entries {

        
        public static function record($form_id, array $values, array $context = array()) {
            $form_id = (int) $form_id;
            $post    = $form_id ? get_post($form_id) : null;
            if (!$post || AIEO_Form_CPT::POST_TYPE !== $post->post_type) {
                return new WP_Error('aieo_form_missing', 'Unknown form.');
            }

            $fields = AIEO_Form_CPT::fields($form_id);
            if (empty($fields)) {
                return new WP_Error('aieo_form_empty', 'Form has no fields.');
            }

            global $wpdb;

            $ip = isset($context['ip']) ? (string) $context['ip'] : '';
            $ok = $wpdb->insert(AIEO_Form_CPT::entries_table(), array(
                'form_id'  => $form_id,
                'user_id'  => isset($context['user_id']) ? (int) $context['user_id'] : get_current_user_id(),
                'order_id' => isset($context['order_id']) ? (int) $context['order_id'] : 0,
                
                
                
                
                
                'eponymous_id' => isset($context['eponymous_id']) ? (int) $context['eponymous_id'] : 0,
                'run_id'       => isset($context['run_id']) ? (int) $context['run_id'] : 0,
                'source'   => isset($context['source']) ? sanitize_key((string) $context['source']) : '',
                
                
                
                'ip_hash'  => '' === $ip ? '' : hash('sha256', $ip . '|' . wp_salt('auth')),
                'status'   => 'new',
                'created_at' => current_time('mysql', true),
            ) + self::trail_columns($context));
            if (false === $ok) {
                return new WP_Error('aieo_form_db', 'Could not store the entry.');
            }
            $entry_id = (int) $wpdb->insert_id;

            
            
            
            $not_shown = isset($context['not_shown']) && is_array($context['not_shown'])
                ? array_flip(array_map('sanitize_key', $context['not_shown']))
                : array();

            
            
            
            
            
            
            foreach ($fields as $f) {
                if (isset($not_shown[$f['key']])) {
                    self::store($entry_id, $f['key'], 'not_shown', null, null, null);
                    continue;
                }

                $given  = array_key_exists($f['key'], $values) ? $values[$f['key']] : null;
                $stored = false;

                if (AIEO_Form_CPT::is_multi_type($f['type'])) {
                    foreach ((array) $given as $one) {
                        $ck = sanitize_key((string) $one);
                        if (isset($f['choices'][$ck])) {
                            self::store($entry_id, $f['key'], 'answered', $ck, null, $f['choices'][$ck]);
                            $stored = true;
                        }
                    }
                } elseif (AIEO_Form_CPT::is_choice_type($f['type'])) {
                    $ck = sanitize_key((string) $given);
                    if (isset($f['choices'][$ck])) {
                        self::store($entry_id, $f['key'], 'answered', $ck, null, $f['choices'][$ck]);
                        $stored = true;
                    }
                } else {
                    $txt = trim((string) $given);
                    if ('' !== $txt) {
                        
                        
                        
                        $num = is_numeric($txt) ? (float) $txt : null;
                        self::store($entry_id, $f['key'], 'answered', null, $num, mb_substr(wp_check_invalid_utf8($txt), 0, 5000));
                        $stored = true;
                    }
                }

                if (!$stored) {
                    
                    self::store($entry_id, $f['key'], 'skipped', null, null, null);
                }
            }

            
            do_action('aieo_form_entry_recorded', $entry_id, $form_id, $values);

            return $entry_id;
        }

        
        public static function record_external($surface, $external_id, array $rows, array $context = array()) {
            global $wpdb;

            $surface     = (string) $surface;
            $external_id = (int) $external_id;
            if ('' === $surface || $external_id <= 0) {
                return new WP_Error('aieo_form_surface', 'A surface key and an external id are both required.');
            }

            
            
            
            $rows = array_values(array_filter($rows, static function ($r) {
                return is_array($r) && !empty($r['field_key']);
            }));
            if (!$rows) {
                return new WP_Error('aieo_form_no_values', 'Refusing to record an entry with no answers.');
            }

            $entries = AIEO_Form_CPT::entries_table();
            $values  = AIEO_Form_CPT::entry_values_table();

            
            
            
            
            
            
            
            
            
            
            
            
            
            $wpdb->query('START TRANSACTION');

            
            
            
            
            $ok = $wpdb->query($wpdb->prepare(
                "INSERT INTO {$entries} (form_id, user_id, order_id, eponymous_id, run_id, source, surface, external_id, ip_hash, status, created_at)
                 VALUES (0, %d, %d, %d, %d, %s, %s, %d, '', %s, %s)
                 ON DUPLICATE KEY UPDATE
                     id         = LAST_INSERT_ID(id),
                     user_id    = VALUES(user_id),
                     order_id   = VALUES(order_id),
                     source     = VALUES(source),
                     created_at = VALUES(created_at),
                     -- 🚨 GREATEST, not VALUES(), on both identity columns. A
                     -- re-import runs against whatever the resolver can see
                     -- TODAY, and an entry whose customer was resolved on the
                     -- first pass must not be un-resolved by a later pass that
                     -- happened to run before the identity substrate was built
                     -- (or after the customer's email changed). A re-run may add
                     -- an identity; it may never take one away.
                     eponymous_id = GREATEST(eponymous_id, VALUES(eponymous_id)),
                     run_id       = GREATEST(run_id, VALUES(run_id))", 
                isset($context['user_id']) ? (int) $context['user_id'] : 0,
                isset($context['order_id']) ? (int) $context['order_id'] : 0,
                isset($context['eponymous_id']) ? (int) $context['eponymous_id'] : 0,
                isset($context['run_id']) ? (int) $context['run_id'] : 0,
                isset($context['source']) ? sanitize_key((string) $context['source']) : '',
                $surface,
                $external_id,
                isset($context['status']) ? (string) $context['status'] : 'new',
                isset($context['created_at']) ? (string) $context['created_at'] : current_time('mysql', true)
            ));
            if (false === $ok) {
                $wpdb->query('ROLLBACK');
                return new WP_Error('aieo_form_db', 'Could not store the entry.');
            }
            $entry_id = (int) $wpdb->insert_id;
            if ($entry_id <= 0) {
                $wpdb->query('ROLLBACK');
                return new WP_Error('aieo_form_db', 'Could not identify the entry.');
            }

            $wpdb->query($wpdb->prepare("DELETE FROM {$values} WHERE entry_id = %d", $entry_id)); 

            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            $tuples = array();
            $args   = array();
            foreach ($rows as $r) {
                $disposition = isset($r['disposition']) ? (string) $r['disposition'] : 'answered';
                $choice_key  = (isset($r['choice_key']) && null !== $r['choice_key']) ? mb_substr((string) $r['choice_key'], 0, 64) : null;
                $value_num   = (isset($r['value_num']) && null !== $r['value_num'] && '' !== $r['value_num']) ? (float) $r['value_num'] : null;
                
                
                
                
                
                
                
                if (null !== $value_num && abs($value_num) >= 1e10) {
                    $value_num = null;
                }
                $value_text  = (isset($r['value_text']) && null !== $r['value_text']) ? (string) $r['value_text'] : null;

                $tuples[] = '(%d, %s, %s, '
                    . (null === $choice_key ? 'NULL' : '%s') . ', '
                    . (null === $value_num  ? 'NULL' : '%f') . ', '
                    . (null === $value_text ? 'NULL' : '%s') . ')';

                $args[] = $entry_id;
                $args[] = mb_substr((string) $r['field_key'], 0, 64);
                $args[] = in_array($disposition, array('answered', 'skipped', 'not_shown'), true) ? $disposition : 'answered';
                if (null !== $choice_key) { $args[] = $choice_key; }
                if (null !== $value_num)  { $args[] = $value_num; }
                if (null !== $value_text) { $args[] = $value_text; }
            }
            $sql = "INSERT INTO {$values} (entry_id, field_key, disposition, choice_key, value_num, value_text) VALUES "
                . implode(', ', $tuples);
            
            if (false === $wpdb->query($wpdb->prepare($sql, $args))) {
                $error = $wpdb->last_error;
                $wpdb->query('ROLLBACK');
                return new WP_Error('aieo_form_values', 'Stored the entry but not its answers: ' . $error);
            }
            $wpdb->query('COMMIT');

            return $entry_id;
        }

        
        public static function surface_distribution($surface, $field_key) {
            global $wpdb;
            $e = AIEO_Form_CPT::entries_table();
            $v = AIEO_Form_CPT::entry_values_table();
            $rows = $wpdb->get_results($wpdb->prepare(
                "SELECT v.choice_key, COUNT(DISTINCT e.id) AS n
                   FROM {$v} v
                   JOIN {$e} e ON e.id = v.entry_id
                  WHERE e.surface = %s AND v.field_key = %s
                    AND v.disposition = 'answered' AND v.choice_key IS NOT NULL
               GROUP BY v.choice_key
               ORDER BY n DESC", 
                (string) $surface,
                (string) $field_key
            ), ARRAY_A);
            $out = array();
            foreach ((array) $rows as $r) { $out[(string) $r['choice_key']] = (int) $r['n']; }
            return $out;
        }

        
        public static function surface_mean($surface, $field_key) {
            global $wpdb;
            $e = AIEO_Form_CPT::entries_table();
            $v = AIEO_Form_CPT::entry_values_table();
            $row = $wpdb->get_row($wpdb->prepare(
                "SELECT AVG(v.value_num) AS m, COUNT(v.value_num) AS n, COUNT(DISTINCT e.id) AS entries
                   FROM {$v} v
                   JOIN {$e} e ON e.id = v.entry_id
                  WHERE e.surface = %s AND v.field_key = %s AND v.disposition = 'answered'", 
                (string) $surface,
                (string) $field_key
            ), ARRAY_A);
            $n       = isset($row['n']) ? (int) $row['n'] : 0;
            $entries = isset($row['entries']) ? (int) $row['entries'] : 0;
            $multi   = $entries > 0 && $n > $entries;
            return array(
                'mean'    => ($n > 0 && !$multi) ? round((float) $row['m'], 4) : null,
                'n'       => $n,
                'entries' => $entries,
                'multi'   => $multi,
            );
        }

        
        
        public static function question_stats( array $surfaces, $cut_gmt ) {
            global $wpdb;
            $surfaces = array_values( array_filter( array_map( 'sanitize_key', $surfaces ) ) );
            if ( ! $surfaces ) {
                return array();
            }
            $e  = AIEO_Form_CPT::entries_table();
            $v  = AIEO_Form_CPT::entry_values_table();
            $in = implode( ',', array_fill( 0, count( $surfaces ), '%s' ) );

            
            $args = array_merge( array_fill( 0, 14, (string) $cut_gmt ), $surfaces );

            $sql = "SELECT v.field_key,
                COUNT(DISTINCT CASE WHEN v.disposition IN ('answered','skipped') AND e.created_at >= %s THEN v.entry_id END) AS asked_now,
                COUNT(DISTINCT CASE WHEN v.disposition IN ('answered','skipped') AND e.created_at <  %s THEN v.entry_id END) AS asked_before,
                COUNT(DISTINCT CASE WHEN v.disposition = 'answered' AND e.created_at >= %s THEN v.entry_id END) AS n_now,
                COUNT(DISTINCT CASE WHEN v.disposition = 'answered' AND e.created_at <  %s THEN v.entry_id END) AS n_before,
                SUM(CASE WHEN v.disposition = 'answered' AND e.created_at >= %s THEN 1 ELSE 0 END) AS rows_now,
                SUM(CASE WHEN v.disposition = 'answered' AND e.created_at <  %s THEN 1 ELSE 0 END) AS rows_before,
                AVG(CASE WHEN v.disposition = 'answered' AND e.created_at >= %s THEN v.value_num END) AS mean_now,
                AVG(CASE WHEN v.disposition = 'answered' AND e.created_at <  %s THEN v.value_num END) AS mean_before,
                MIN(CASE WHEN v.disposition = 'answered' AND e.created_at >= %s THEN v.value_num END) AS min_now,
                MAX(CASE WHEN v.disposition = 'answered' AND e.created_at >= %s THEN v.value_num END) AS max_now,
                MIN(CASE WHEN v.disposition = 'answered' AND e.created_at <  %s THEN v.value_num END) AS min_before,
                MAX(CASE WHEN v.disposition = 'answered' AND e.created_at <  %s THEN v.value_num END) AS max_before,
                COUNT(DISTINCT CASE WHEN v.disposition = 'answered' AND v.choice_key IS NULL AND v.value_num IS NULL AND v.value_text <> '' AND e.created_at >= %s THEN v.entry_id END) AS text_now,
                COUNT(DISTINCT CASE WHEN v.disposition = 'answered' AND v.choice_key IS NULL AND v.value_num IS NULL AND v.value_text <> '' AND e.created_at <  %s THEN v.entry_id END) AS text_before
              FROM `{$v}` v
              JOIN `{$e}` e ON e.id = v.entry_id
              WHERE e.surface IN ({$in})
              GROUP BY v.field_key";

            $out = array();
            
            foreach ( (array) $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A ) as $r ) {
                $out[ (string) $r['field_key'] ] = $r;
            }
            return $out;
        }

        
        public static function answer_breakdown( array $surfaces, $cut_gmt, array $codes, $bucket = 'choice' ) {
            global $wpdb;
            $surfaces = array_values( array_filter( array_map( 'sanitize_key', $surfaces ) ) );
            $codes    = array_values( array_filter( array_map( 'sanitize_key', $codes ) ) );
            if ( ! $surfaces || ! $codes ) {
                return array();
            }
            $e   = AIEO_Form_CPT::entries_table();
            $v   = AIEO_Form_CPT::entry_values_table();
            $ins = implode( ',', array_fill( 0, count( $surfaces ), '%s' ) );
            $inc = implode( ',', array_fill( 0, count( $codes ), '%s' ) );

            if ( 'value' === $bucket ) {
                $col  = 'v.value_num';
                $cond = 'v.value_num IS NOT NULL';
            } else {
                $col  = 'v.choice_key';
                $cond = "v.choice_key IS NOT NULL AND v.choice_key <> ''";
            }

            $args = array_merge( array( (string) $cut_gmt ), $surfaces, $codes );

            $sql = "SELECT v.field_key,
                       CASE WHEN e.created_at >= %s THEN 1 ELSE 0 END AS win,
                       {$col} AS bucket,
                       COUNT(DISTINCT v.entry_id) AS n,
                       AVG(v.value_num) AS mean_position,
                       SUM(CASE WHEN v.value_num = 1 THEN 1 ELSE 0 END) AS firsts
                    FROM `{$v}` v
                    JOIN `{$e}` e ON e.id = v.entry_id
                    WHERE e.surface IN ({$ins})
                      AND v.field_key IN ({$inc})
                      AND v.disposition = 'answered'
                      AND {$cond}
                    GROUP BY v.field_key, win, bucket";

            $out = array();
            
            foreach ( (array) $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A ) as $r ) {
                $out[ (string) $r['field_key'] ][] = $r;
            }
            return $out;
        }

        
        public static function surface_responses( array $surfaces, $limit = 500 ) {
            global $wpdb;
            $surfaces = array_values( array_filter( array_map( 'sanitize_key', $surfaces ) ) );
            if ( ! $surfaces ) {
                return array();
            }
            $e     = AIEO_Form_CPT::entries_table();
            $v     = AIEO_Form_CPT::entry_values_table();
            $in    = implode( ',', array_fill( 0, count( $surfaces ), '%s' ) );
            $limit = max( 1, min( 5000, (int) $limit ) );

            $args = array_merge( $surfaces, array( $limit ) );
            $sql  = "SELECT e.id, e.surface, e.external_id, e.order_id, e.eponymous_id, e.user_id, e.run_id, e.created_at,
                        COUNT(DISTINCT CASE WHEN v.disposition = 'answered' THEN v.field_key END) AS answered,
                        COUNT(DISTINCT CASE WHEN v.disposition = 'skipped'  THEN v.field_key END) AS skipped
                     FROM `{$e}` e
                     LEFT JOIN `{$v}` v ON v.entry_id = e.id
                     WHERE e.surface IN ({$in})
                     GROUP BY e.id
                     ORDER BY e.created_at DESC, e.id DESC
                     LIMIT %d";
            
            return (array) $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );
        }

        
        public static function entry_answers( $entry_id ) {
            global $wpdb;
            $v = AIEO_Form_CPT::entry_values_table();
            
            return (array) $wpdb->get_results( $wpdb->prepare(
                "SELECT field_key, disposition, choice_key, value_num, value_text FROM `{$v}` WHERE entry_id = %d ORDER BY id ASC",
                (int) $entry_id
            ), ARRAY_A );
        }

        public static function surface_entry_count($surface) {
            global $wpdb;
            return (int) $wpdb->get_var($wpdb->prepare(
                'SELECT COUNT(*) FROM ' . AIEO_Form_CPT::entries_table() . ' WHERE surface = %s', 
                (string) $surface
            ));
        }

        private static function store($entry_id, $field_key, $disposition, $choice_key, $value_num, $value_text) {
            global $wpdb;
            $wpdb->insert(AIEO_Form_CPT::entry_values_table(), array(
                'entry_id'    => (int) $entry_id,
                'field_key'   => $field_key,
                'disposition' => in_array($disposition, array('answered', 'skipped', 'not_shown'), true) ? $disposition : 'answered',
                'choice_key'  => $choice_key,
                'value_num'   => $value_num,
                'value_text'  => $value_text,
            ));
        }

        
        public static function distribution($form_id, $field_key) {
            global $wpdb;
            $e = AIEO_Form_CPT::entries_table();
            $v = AIEO_Form_CPT::entry_values_table();

            $rows = $wpdb->get_results($wpdb->prepare(
                "SELECT v.choice_key, COUNT(DISTINCT e.id) AS n
                   FROM {$v} v
                   JOIN {$e} e ON e.id = v.entry_id
                  WHERE e.form_id = %d AND v.field_key = %s AND v.disposition = 'answered' AND v.choice_key IS NOT NULL
               GROUP BY v.choice_key
               ORDER BY n DESC", 
                (int) $form_id,
                $field_key
            ), ARRAY_A);

            $out = array();
            foreach ((array) $rows as $r) { $out[$r['choice_key']] = (int) $r['n']; }
            return $out;
        }

        
        public static function asked_count($form_id, $field_key) {
            global $wpdb;
            $e = AIEO_Form_CPT::entries_table();
            $v = AIEO_Form_CPT::entry_values_table();
            return (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT e.id)
                   FROM {$v} v
                   JOIN {$e} e ON e.id = v.entry_id
                  WHERE e.form_id = %d AND v.field_key = %s
                    AND v.disposition IN ('answered','skipped')", 
                (int) $form_id,
                $field_key
            ));
        }

        
        public static function latest($form_id, $limit = 20) {
            global $wpdb;
            $rows = (array) $wpdb->get_results($wpdb->prepare(
                'SELECT id, created_at, source' . (self::has_trail() ? ', page_url, seen_before' : '') . ' FROM %i WHERE form_id = %d ORDER BY id DESC LIMIT %d',
                AIEO_Form_CPT::entries_table(),
                (int) $form_id,
                max(1, (int) $limit)
            ), ARRAY_A);
            $out = array();
            foreach ($rows as $r) {
                $answers = array();
                foreach (self::entry_answers((int) $r['id']) as $v) {
                    if ('answered' !== (string) $v['disposition']) { continue; }
                    $k             = (string) $v['field_key'];
                    $answers[$k]   = isset($answers[$k]) ? $answers[$k] . ', ' . (string) $v['value_text'] : (string) $v['value_text'];
                }
                $out[] = array('id' => (int) $r['id'], 'created_at' => (string) $r['created_at'], 'source' => (string) $r['source'], 'answers' => $answers, 'page_url' => (string) ($r['page_url'] ?? ''), 'seen_before' => self::seen_ids((string) ($r['seen_before'] ?? '')));
            }
            return $out;
        }

        

        
        public static function has_trail() {
            return (int) get_option(AIEO_Form_CPT::DB_VERSION_OPTION, 0) >= 4;
        }

        
        public static function has_location() {
            return (int) get_option(AIEO_Form_CPT::DB_VERSION_OPTION, 0) >= 5;
        }

        
        private static function trail_columns(array $context) {
            if (!self::has_trail()) {
                return array();
            }
            $seen = self::seen_snapshot(isset($context['seen_before']) ? (string) $context['seen_before'] : '');
            $cols = array(
                'page_url'    => self::page_url(isset($context['page']) ? (string) $context['page'] : ''),
                'seen_before' => $seen ? (string) wp_json_encode($seen) : null,
            );
            if (self::has_location()) {
                $where = sanitize_key(isset($context['location']) ? (string) $context['location'] : '');
                $cols['location'] = '' !== $where ? $where : null;
            }
            return $cols;
        }

        
        public static function page_url($url) {
            $url = esc_url_raw(trim((string) $url));
            if ('' === $url || strtolower((string) wp_parse_url($url, PHP_URL_HOST)) !== strtolower((string) wp_parse_url(home_url(), PHP_URL_HOST))) {
                return null;
            }
            $query = (string) wp_parse_url($url, PHP_URL_QUERY);
            if ('' !== $query) {
                parse_str($query, $args);
                foreach (array_keys($args) as $k) {
                    if (preg_match('/token|key|nonce|pass|secret|code|auth|login|hash|sig/i', (string) $k)) {
                        $url = remove_query_arg((string) $k, $url);
                    }
                }
            }
            return substr($url, 0, 2048);
        }

        
        public static function seen_snapshot($client) {
            $by_id = array();
            if (!empty($_COOKIE['woocommerce_recently_viewed'])) {
                foreach (array_filter(array_map('absint', explode('|', (string) wp_unslash($_COOKIE['woocommerce_recently_viewed'])))) as $pid) {
                    $by_id[$pid] = 0;
                }
            }
            $list = json_decode(substr((string) $client, 0, 4096), true);
            if (is_array($list) && array_values($list) === $list) {
                foreach ($list as $e) {
                    $pid = is_array($e) && isset($e['id']) && (is_int($e['id']) || (is_string($e['id']) && ctype_digit($e['id']))) ? (int) $e['id'] : 0;
                    if ($pid > 0) {
                        unset($by_id[$pid]);
                        $by_id[$pid] = is_array($e) && isset($e['t']) && is_numeric($e['t']) ? max(0, (int) $e['t']) : 0;
                    }
                }
            }
            $by_id = array_slice($by_id, -30, null, true);
            if ($by_id) {
                _prime_post_caches(array_keys($by_id), false, false);
            }
            $out = array();
            foreach ($by_id as $pid => $t) {
                if ('product' === get_post_type($pid) && 'publish' === get_post_status($pid)) {
                    $out[] = array('id' => (int) $pid, 't' => (int) $t);
                }
            }
            return $out;
        }

        
        public static function seen_ids($json) {
            $list = json_decode((string) $json, true);
            return is_array($list) ? array_values(array_filter(array_map(static function ($e) { return is_array($e) ? (int) ($e['id'] ?? 0) : 0; }, $list))) : array();
        }

        
        public static function entry_count($form_id) {
            global $wpdb;
            return (int) $wpdb->get_var($wpdb->prepare(
                'SELECT COUNT(*) FROM ' . AIEO_Form_CPT::entries_table() . ' WHERE form_id = %d', 
                (int) $form_id
            ));
        }

        
        public static function free_text($form_id, $field_key, $limit = 100) {
            global $wpdb;
            $e = AIEO_Form_CPT::entries_table();
            $v = AIEO_Form_CPT::entry_values_table();
            return (array) $wpdb->get_col($wpdb->prepare(
                "SELECT v.value_text
                   FROM {$v} v
                   JOIN {$e} e ON e.id = v.entry_id
                  WHERE e.form_id = %d AND v.field_key = %s
                    AND v.disposition = 'answered' AND v.choice_key IS NULL AND v.value_text <> ''
               ORDER BY e.created_at DESC
                  LIMIT %d", 
                (int) $form_id,
                $field_key,
                (int) $limit
            ));
        }
    }
}