<?php

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

class AIEO_Competition_Analytics {

    
    const COLL = 'utf8mb4_uca1400_ai_ci';

    
    const STATS_CACHE_OPTION = 'aieo_competitions_stats_cache';
    const STATS_REFRESH_HOOK = 'aieo_competitions_stats_refresh';

    
    public static function get_cached_stats() {
        $c = get_option(self::STATS_CACHE_OPTION, null);
        return (is_array($c) && isset($c['aggregate']) && is_array($c['aggregate'])) ? $c : null;
    }

    
    public static function refresh_stats_cache($top_limit = 10) {
        $payload = array(
            'revenue_by_competition' => self::revenue_by_competition(),
            'aggregate'              => self::aggregate($top_limit),
            'generated_at'           => current_time('mysql', true), 
        );
        update_option(self::STATS_CACHE_OPTION, $payload, false);
        return $payload;
    }

    private static function table_exists($name) {
        global $wpdb;
        static $cache = array();
        if (!array_key_exists($name, $cache)) {
            $cache[$name] = ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $name)) === $name);
        }
        return $cache[$name];
    }

    
    public static function associated_product_ids($competition_id) {
        global $wpdb;
        $cid = (int) $competition_id;
        if (!class_exists('AIEO_Competition_CPT')) { return array(); }

        $brands = array_map('intval', (array) get_post_meta($cid, AIEO_Competition_CPT::META_BRANDS, true));
        $cats   = array_map('intval', (array) get_post_meta($cid, AIEO_Competition_CPT::META_CATEGORIES, true));
        $direct = array_map('intval', (array) get_post_meta($cid, AIEO_Competition_CPT::META_PRODUCTS, true));

        $term_ids = array_values(array_unique(array_filter(array_merge($brands, $cats))));
        $from_terms = array();
        if ($term_ids) {
            $brand_tax = (class_exists('AIEO_DMM_Brand_Discounts') && method_exists('AIEO_DMM_Brand_Discounts', 'get_brand_taxonomy'))
                ? (AIEO_DMM_Brand_Discounts::get_brand_taxonomy() ?: 'product_brand')
                : 'product_brand';
            $place = implode(',', array_fill(0, count($term_ids), '%d'));
            
            
            $sql = $wpdb->prepare(
                "SELECT DISTINCT tr.object_id
                   FROM {$wpdb->term_relationships} tr
                   JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
                   JOIN {$wpdb->posts} p ON p.ID = tr.object_id
                  WHERE tt.term_id IN ($place)
                    AND tt.taxonomy IN (%s, 'product_cat')
                    AND p.post_type = 'product'
                    AND p.post_status IN ('publish','private')",
                array_merge($term_ids, array($brand_tax))
            );
            $from_terms = array_map('intval', (array) $wpdb->get_col($sql));
        }

        $all = array_merge($from_terms, $direct);
        if (class_exists('AIEO_DMM_Product_Picker') && method_exists('AIEO_DMM_Product_Picker', 'roll_up_to_parents')) {
            $all = AIEO_DMM_Product_Picker::roll_up_to_parents($all);
        }
        return array_values(array_unique(array_filter(array_map('intval', $all))));
    }

    
    public static function insights($competition_id) {
        global $wpdb;
        $cid = (int) $competition_id;
        $out = array(
            'metrics'             => class_exists('AIEO_Competition_Participants')
                ? AIEO_Competition_Participants::metrics($cid)
                : array('entries' => 0, 'confirmed' => 0, 'pending' => 0, 'winners' => 0),
            'converting_entrants' => 0,
            'orders_placed'       => 0,
            'entrant_revenue'     => 0.0,
            'assoc_products'      => 0,
            'pull_units'          => 0,
            'pull_revenue'        => 0.0,
            'has_order_data'      => false,
        );
        if (!class_exists('AIEO_Competition_Participants')) { return $out; }

        $ptbl   = AIEO_Competition_Participants::table();
        $ostats = $wpdb->prefix . 'aieo_order_stats';
        $opa    = $wpdb->prefix . 'aieo_order_products_analytics';

        if (self::table_exists($ostats)) {
            $out['has_order_data'] = true;
            
            
            
            
            $row = $wpdb->get_row($wpdb->prepare(
                "SELECT COUNT(DISTINCT e.email) ce, COUNT(DISTINCT os.orderid) op, COALESCE(SUM(os.orderspend),0) rev
                   FROM (SELECT DISTINCT email FROM {$ptbl} WHERE competition_id = %d) e
                   JOIN {$ostats} os
                     ON os.customeremail COLLATE " . self::COLL . " = e.email COLLATE " . self::COLL . "",
                $cid
            ));
            if ($row) {
                $out['converting_entrants'] = (int) $row->ce;
                $out['orders_placed']       = (int) $row->op;
                $out['entrant_revenue']     = (float) $row->rev;
            }
        }

        $prod_ids = self::associated_product_ids($cid);
        $out['assoc_products'] = count($prod_ids);
        if ($prod_ids && self::table_exists($opa)) {
            $in  = implode(',', array_map('intval', $prod_ids));
            $row = $wpdb->get_row($wpdb->prepare(
                "SELECT COALESCE(SUM(opa.quantity),0) units, COALESCE(SUM(opa.price),0) revenue
                   FROM {$opa} opa
                   JOIN (SELECT DISTINCT email FROM {$ptbl} WHERE competition_id = %d) e
                     ON opa.customer_email COLLATE " . self::COLL . " = e.email COLLATE " . self::COLL . "
                  WHERE opa.product_id IN ({$in})",
                $cid
            ));
            if ($row) {
                $out['pull_units']   = (int) $row->units;
                $out['pull_revenue'] = (float) $row->revenue;
            }
        }
        return $out;
    }

    
    public static function revenue_by_competition() {
        global $wpdb;
        $out = array();
        if (!class_exists('AIEO_Competition_Participants')) { return $out; }
        $ptbl   = AIEO_Competition_Participants::table();
        $ostats = $wpdb->prefix . 'aieo_order_stats';
        if (!self::table_exists($ostats)) { return $out; }
        $rows = $wpdb->get_results(
            "SELECT e.competition_id cid,
                    COUNT(DISTINCT e.email)   ce,
                    COUNT(DISTINCT os.orderid) op,
                    COALESCE(SUM(os.orderspend),0) rev
               FROM (SELECT DISTINCT competition_id, email FROM {$ptbl}) e
               JOIN {$ostats} os
                 ON os.customeremail COLLATE " . self::COLL . " = e.email COLLATE " . self::COLL . "
              GROUP BY e.competition_id"
        );
        foreach ((array) $rows as $r) {
            $out[(int) $r->cid] = array(
                'revenue'    => (float) $r->rev,
                'converting' => (int) $r->ce,
                'orders'     => (int) $r->op,
            );
        }
        return $out;
    }

    
    public static function aggregate($top_limit = 10) {
        global $wpdb;
        $out = array(
            'total_participants'  => 0, 'unique_emails' => 0, 'identified' => 0, 'confirmed' => 0,
            'converting_entrants' => 0, 'entrant_revenue' => 0.0,
            'has_order_data'      => false, 'has_ci' => false, 'top_participants' => array(),
        );
        if (!class_exists('AIEO_Competition_Participants')) { return $out; }
        $ptbl = AIEO_Competition_Participants::table();

        $r = $wpdb->get_row(
            "SELECT COUNT(*) tot,
                    COUNT(DISTINCT email) uniq,
                    COUNT(DISTINCT CASE WHEN eponymous_id > 0 THEN eponymous_id END) ident,
                    SUM(CASE WHEN status='confirmed' THEN 1 ELSE 0 END) conf
               FROM {$ptbl}"
        );
        if ($r) {
            $out['total_participants'] = (int) $r->tot;
            $out['unique_emails']      = (int) $r->uniq;
            $out['identified']         = (int) $r->ident;
            $out['confirmed']          = (int) $r->conf;
        }

        $ostats = $wpdb->prefix . 'aieo_order_stats';
        if (self::table_exists($ostats)) {
            $out['has_order_data'] = true;
            $row = $wpdb->get_row(
                "SELECT COUNT(DISTINCT p.email) ce, COALESCE(SUM(os.orderspend),0) rev
                   FROM (SELECT DISTINCT email FROM {$ptbl}) p
                   JOIN {$ostats} os
                     ON os.customeremail COLLATE " . self::COLL . " = p.email COLLATE " . self::COLL . ""
            );
            if ($row) {
                $out['converting_entrants'] = (int) $row->ce;
                $out['entrant_revenue']     = (float) $row->rev;
            }
        }

        $ci = $wpdb->prefix . 'aieo_customer_intelligence';
        if (self::table_exists($ci)) {
            $out['has_ci'] = true;
            $lim = max(1, (int) $top_limit);
            
            
            
            
            $out['top_participants'] = (array) $wpdb->get_results($wpdb->prepare(
                "SELECT ci.eponymous_id, MAX(cp.email) email,
                        COUNT(DISTINCT cp.competition_id) entries,
                        COUNT(DISTINCT CASE WHEN cp.is_winner = 1 THEN cp.competition_id END) wins,
                        ci.ltv_total
                   FROM {$ptbl} cp
                   JOIN {$ci} ci ON ci.eponymous_id = cp.eponymous_id
                  WHERE cp.eponymous_id > 0
                  GROUP BY ci.eponymous_id, ci.ltv_total
                  ORDER BY entries DESC, ci.ltv_total DESC
                  LIMIT %d",
                $lim
            ));
        }
        return $out;
    }

    
    public static function customer_participation($eponymous_id) {
        global $wpdb;
        $eid = (int) $eponymous_id;
        $out = array('count' => 0, 'last_entry' => null, 'won' => 0, 'competitions' => array());
        if ($eid <= 0 || !class_exists('AIEO_Competition_Participants')) { return $out; }
        $ptbl = AIEO_Competition_Participants::table();
        
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT cp.competition_id, MAX(p.post_title) post_title, MAX(cp.created_at) created_at,
                    MAX(cp.status) status, MAX(cp.is_winner) is_winner, COUNT(*) entries
               FROM {$ptbl} cp
               LEFT JOIN {$wpdb->posts} p ON p.ID = cp.competition_id
              WHERE cp.eponymous_id = %d
              GROUP BY cp.competition_id
              ORDER BY created_at DESC",
            $eid
        ));
        foreach ((array) $rows as $row) {
            $out['count']++; 
            if ((int) $row->is_winner === 1) { $out['won']++; }
            if ($out['last_entry'] === null && $row->created_at) { $out['last_entry'] = $row->created_at; }
            $out['competitions'][] = array(
                'id'        => (int) $row->competition_id,
                'name'      => (string) $row->post_title,
                'date'      => $row->created_at,
                'status'    => (string) $row->status,
                'is_winner' => (int) $row->is_winner,
                'entries'   => (int) $row->entries,
            );
        }
        return $out;
    }
}
