<?php

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

class AIEO_DMM_Product_Behavior_Metrics {

    const CACHE_GROUP = 'aieo_dmm';
    const CACHE_TTL   = HOUR_IN_SECONDS;

    public static function table() {
        global $wpdb;
        return $wpdb->prefix . 'aieo_dmm_product_behavior_metrics';
    }

    
    public static function get($product_id) {
        $product_id = (int) $product_id;
        if ($product_id <= 0) { return self::defaults($product_id); }

        $cache_key = 'aieo_pbm:' . $product_id;
        $cached = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (is_array($cached)) { return $cached; }

        global $wpdb;
        $row = $wpdb->get_row($wpdb->prepare(
            'SELECT * FROM ' . self::table() . ' WHERE product_id = %d LIMIT 1',
            $product_id
        ), ARRAY_A);
        if (!is_array($row)) { $row = self::defaults($product_id); }
        else {
            $row['product_id']          = (int)   $row['product_id'];
            $row['total_orders']        = (int)   $row['total_orders'];
            $row['total_cancellations'] = (int)   $row['total_cancellations'];
            $row['total_refund_lines']  = (int)   $row['total_refund_lines'];
            $row['cancellation_rate']   = (float) $row['cancellation_rate'];
            $row['refund_rate']         = (float) $row['refund_rate'];
        }
        wp_cache_set($cache_key, $row, self::CACHE_GROUP, self::CACHE_TTL);
        return $row;
    }

    
    public static function reco_penalty($product_id) {
        $row = self::get($product_id);
        $threshold = (float) apply_filters('aieo_dmm_pbm_penalty_threshold', 0.10);
        $rate      = (float) $row['cancellation_rate'];
        $penalty   = ($rate > $threshold) ? max(0.0, 1.0 - $rate) : 1.0;
        return (float) apply_filters('aieo_dmm_pbm_reco_penalty', $penalty, $product_id, $row);
    }

    
    public static function recompute($product_id) {
        $product_id = (int) $product_id;
        if ($product_id <= 0) { return; }
        global $wpdb;

        $oc = $wpdb->prefix . 'aieo_dmm_order_cancellations';
        $lr = $wpdb->prefix . 'aieo_dmm_order_line_refunds';

        
        
        
        
        
        
        
        
        
        
        
        
        
        $weights = (array) apply_filters('aieo_dmm_pbm_kind_weights', array(1 => 1.0, 2 => 0.5, 3 => 0.1));
        $w_returned = (float) ($weights[1] ?? 1.0);
        $w_kept     = (float) ($weights[2] ?? 0.5);
        $w_refused  = (float) ($weights[3] ?? 0.1);

        $total_cancellations = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$oc}
              WHERE event_kind > 0
                AND JSON_CONTAINS(product_ids, JSON_ARRAY(%d))",
            $product_id
        ));
        
        
        
        $weighted_score = (float) $wpdb->get_var($wpdb->prepare(
            "SELECT SUM(CASE event_kind WHEN 1 THEN %f WHEN 2 THEN %f WHEN 3 THEN %f ELSE 0 END)
               FROM {$oc}
              WHERE event_kind > 0
                AND JSON_CONTAINS(product_ids, JSON_ARRAY(%d))",
            $w_returned, $w_kept, $w_refused, $product_id
        ));
        $last_cancelled_at = $wpdb->get_var($wpdb->prepare(
            "SELECT MAX(cancelled_at) FROM {$oc}
              WHERE event_kind > 0
                AND JSON_CONTAINS(product_ids, JSON_ARRAY(%d))",
            $product_id
        ));
        
        $total_refund_lines = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$lr} WHERE product_id = %d",
            $product_id
        ));

        
        
        $total_orders = self::count_orders_with_product($product_id);

        
        
        
        
        $cancellation_rate = $total_orders > 0
            ? round((float) $weighted_score / (float) $total_orders, 4)
            : 0.0;
        $refund_rate = $total_orders > 0
            ? round((float) $total_refund_lines / (float) $total_orders, 4)
            : 0.0;

        
        
        
        $top_reason_slug = $wpdb->get_var($wpdb->prepare(
            "SELECT reason_slug
               FROM {$oc}
              WHERE event_kind > 0
                AND JSON_CONTAINS(product_ids, JSON_ARRAY(%d))
              GROUP BY reason_slug
              ORDER BY COUNT(*) DESC
              LIMIT 1",
            $product_id
        ));

        $row = array(
            'product_id'          => $product_id,
            'total_orders'        => $total_orders,
            'total_cancellations' => $total_cancellations,
            'total_refund_lines'  => $total_refund_lines,
            'cancellation_rate'   => $cancellation_rate,
            'refund_rate'         => $refund_rate,
            'last_cancelled_at'   => $last_cancelled_at ?: null,
            'top_reason_slug'     => $top_reason_slug ?: null,
            'computed_at'         => current_time('mysql', true),
        );

        $tbl = self::table();
        $exists = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT 1 FROM {$tbl} WHERE product_id = %d",
            $product_id
        ));
        if ($exists) {
            $wpdb->update($tbl, $row, array('product_id' => $product_id));
        } else {
            $wpdb->insert($tbl, $row);
        }
        wp_cache_delete('aieo_pbm:' . $product_id, self::CACHE_GROUP);
        return $row;
    }

    
    private static function count_orders_with_product($product_id) {
        global $wpdb;
        
        
        
        $tbl = $wpdb->prefix . 'wc_order_product_lookup';
        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl)) !== $tbl) {
            return 0;
        }
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT order_id) FROM {$tbl} WHERE product_id = %d",
            $product_id
        ));
    }

    
    public static function defaults($product_id) {
        return array(
            'product_id'          => (int) $product_id,
            'total_orders'        => 0,
            'total_cancellations' => 0,
            'total_refund_lines'  => 0,
            'cancellation_rate'   => 0.0,
            'refund_rate'         => 0.0,
            'last_cancelled_at'   => null,
            'top_reason_slug'     => null,
            'computed_at'         => null,
        );
    }

    
    public static function backfill_all() {
        global $wpdb;
        $oc = $wpdb->prefix . 'aieo_dmm_order_cancellations';
        $lr = $wpdb->prefix . 'aieo_dmm_order_line_refunds';

        $product_ids = array();

        
        
        $rows = $wpdb->get_col(
            "SELECT DISTINCT jt.pid
               FROM {$oc},
                    JSON_TABLE(product_ids, '$[*]' COLUMNS (pid BIGINT PATH '$')) AS jt
              WHERE product_ids IS NOT NULL"
        );
        foreach ((array) $rows as $pid) {
            $pid = (int) $pid;
            if ($pid > 0) { $product_ids[$pid] = true; }
        }

        
        $rows = $wpdb->get_col("SELECT DISTINCT product_id FROM {$lr}");
        foreach ((array) $rows as $pid) {
            $pid = (int) $pid;
            if ($pid > 0) { $product_ids[$pid] = true; }
        }

        $count = 0;
        foreach (array_keys($product_ids) as $pid) {
            self::recompute($pid);
            $count++;
        }
        return $count;
    }

    
    public static function recompute_stale($stale_hours = 24, $limit = 200) {
        global $wpdb;
        $tbl = self::table();
        $ids = $wpdb->get_col($wpdb->prepare(
            "SELECT product_id FROM {$tbl}
              WHERE computed_at < DATE_SUB(NOW(), INTERVAL %d HOUR)
              ORDER BY computed_at ASC
              LIMIT %d",
            (int) $stale_hours, (int) $limit
        ));
        $count = 0;
        foreach ((array) $ids as $pid) {
            self::recompute((int) $pid);
            $count++;
        }
        return $count;
    }

    
    public static function bootstrap() {
        static $done = false;
        if ($done) { return; }
        $done = true;
        add_action('aieo_dmm_recompute_behavior_metrics_for_product',
            array(__CLASS__, 'recompute'), 10, 1);
    }
}
