<?php

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

class AIEO_Push_Trigger_Replenishment extends AIEO_Push_Trigger_Base {

    public function get_id()    { return 'replenishment'; }
    public function get_label() { return __('Replenishment cadence reminder', 'ai-eshop-optimizer'); }
    public function dedup_days() { return 21; }
    public function cap_per_day() { return 1; }
    public function cap_per_week() { return 2; }

    public function produce_candidates($limit = 200) {
        global $wpdb;
        $tbl_ci  = $wpdb->prefix . 'aieo_customer_intelligence';
        $tbl_sub = $wpdb->prefix . 'aieo_push_subscriptions';
        $tbl_p   = $wpdb->posts;

        $limit = max(1, (int) $limit);

        
        
        
        $rows = $wpdb->get_results(
            "SELECT
                 sub.subscription_id,
                 sub.user_id,
                 ci.top_last_product_id AS target_product_id,
                 DATEDIFF(UTC_TIMESTAMP(), ci.last_order_at) AS days_since_last,
                 ci.ltv_total
             FROM {$tbl_sub} sub
             JOIN {$tbl_ci} ci ON ci.wp_user_id = sub.user_id
             WHERE sub.status = 'active'
               AND sub.user_id > 0
               AND JSON_SEARCH(ci.segments_json, 'one', 'replenisher') IS NOT NULL
               AND ci.orders_count >= 2
               AND ci.first_order_at IS NOT NULL
               AND ci.last_order_at IS NOT NULL
               AND ci.top_last_product_id > 0
               AND DATEDIFF(UTC_TIMESTAMP(), ci.last_order_at) >
                   (DATEDIFF(ci.last_order_at, ci.first_order_at) / NULLIF(ci.orders_count - 1, 0)) + 5
               AND DATEDIFF(UTC_TIMESTAMP(), ci.last_order_at) < 365
             LIMIT 5000",
            ARRAY_A
        );
        if (empty($rows)) { return array(); }

        
        $pids = array_values(array_unique(array_map(
            static function ($r) { return (int) $r['target_product_id']; },
            $rows
        )));
        if (empty($pids)) { return array(); }
        $in = implode(',', $pids);

        $titles = $wpdb->get_results(
            "SELECT ID, post_title FROM {$tbl_p}
             WHERE post_status = 'publish'
               AND post_type IN ('product', 'product_variation')
               AND ID IN ({$in})",
            ARRAY_A
        );
        $title_by_pid = array();
        foreach ($titles as $t) {
            $title_by_pid[(int) $t['ID']] = (string) $t['post_title'];
        }

        
        
        $out = array();
        foreach ($rows as $r) {
            $pid = (int) $r['target_product_id'];
            if (!isset($title_by_pid[$pid])) { continue; }
            $url = (string) get_permalink($pid);
            $out[] = array(
                'subscription_id'   => (int) $r['subscription_id'],
                'user_id'           => (int) $r['user_id'],
                'target_product_id' => $pid,
                'target_url'        => $url ?: home_url('/'),
                'payload_data'      => array(
                    'product_name'    => (string) $title_by_pid[$pid],
                    'days_since_last' => (int) $r['days_since_last'],
                ),
                '_ltv'              => (float) $r['ltv_total'],
            );
        }
        usort($out, static function ($a, $b) {
            return $b['_ltv'] <=> $a['_ltv'];
        });
        if (count($out) > $limit) {
            $out = array_slice($out, 0, $limit);
        }
        foreach ($out as &$o) { unset($o['_ltv']); }
        unset($o);

        return $out;
    }
}
