<?php

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

class AIEO_Push_Trigger_PriceDropWishlist extends AIEO_Push_Trigger_Base {

    const OPTION_THRESHOLD = 'aieo_push_trigger_price_drop_threshold';

    public function get_id()    { return 'price_drop_wishlist'; }
    public function get_label() { return __('Price drop on wishlisted product', 'ai-eshop-optimizer'); }

    public function dedup_days() { return 7; }
    public function cap_per_day() { return 1; }
    public function cap_per_week() { return 3; }

    
    public function get_threshold() {
        $opt = (float) get_option(self::OPTION_THRESHOLD, 0.05);
        if ($opt <= 0 || $opt > 0.9) { return 0.05; }
        return $opt;
    }

    
    public function register_hooks() {
        add_action('plugins_loaded', array($this, 'maybe_backfill_snapshots'), 28);
    }

    public function maybe_backfill_snapshots() {
        global $wpdb;
        $tbl = $wpdb->prefix . 'aieo_wishlist';
        
        $col_exists = (string) $wpdb->get_var(
            "SHOW COLUMNS FROM {$tbl} LIKE 'price_at_wishlist'"
        );
        if ($col_exists === '') { return; }

        $missing = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$tbl} WHERE price_at_wishlist IS NULL AND item_type='product'"
        );
        if ($missing === 0) { return; }

        
        
        $wpdb->query(
            "UPDATE {$tbl} w
             JOIN {$wpdb->postmeta} pm
                  ON pm.post_id = w.item_id AND pm.meta_key = '_price'
             SET w.price_at_wishlist = ROUND(CAST(NULLIF(pm.meta_value, '') AS DECIMAL(12,2)), 2)
             WHERE w.price_at_wishlist IS NULL
               AND w.item_type = 'product'
               AND CAST(NULLIF(pm.meta_value, '') AS DECIMAL(12,2)) > 0"
        );
    }

    
    public function produce_candidates($limit = 200) {
        global $wpdb;
        $tbl_w   = $wpdb->prefix . 'aieo_wishlist';
        $tbl_sub = $wpdb->prefix . 'aieo_push_subscriptions';
        $tbl_pm  = $wpdb->postmeta;
        $tbl_p   = $wpdb->posts;

        $limit      = max(1, (int) $limit);
        $threshold  = $this->get_threshold();
        $multiplier = (float) (1.0 - $threshold);

        
        
        
        
        
        
        
        $subs = $wpdb->get_results(
            "SELECT subscription_id, user_id, session_token
             FROM {$tbl_sub}
             WHERE status = 'active'
             LIMIT 5000",
            ARRAY_A
        );
        if (empty($subs)) { return array(); }

        $subs_by_user  = array();
        $subs_by_token = array();
        foreach ($subs as $s) {
            $uid = (int) $s['user_id'];
            $tok = (string) $s['session_token'];
            $sub_row = array('subscription_id' => (int) $s['subscription_id'], 'user_id' => $uid);
            if ($uid > 0) {
                $subs_by_user[$uid][] = $sub_row;
            } elseif ($tok !== '') {
                $subs_by_token[$tok][] = $sub_row;
            }
        }
        if (empty($subs_by_user) && empty($subs_by_token)) { return array(); }

        
        
        $candidates = array();

        if (!empty($subs_by_user)) {
            $user_ids = array_keys($subs_by_user);
            $in_users = implode(',', array_map('intval', $user_ids));
            $logged_in = $wpdb->get_results($wpdb->prepare(
                "SELECT w.user_id, w.id AS wid, w.item_id,
                        w.price_at_wishlist AS snapshot,
                        ROUND(CAST(pm.meta_value AS DECIMAL(12,2)), 2) AS current_price,
                        ROUND(100 * (w.price_at_wishlist - CAST(pm.meta_value AS DECIMAL(12,2)))
                              / NULLIF(w.price_at_wishlist, 0), 0) AS drop_pct
                 FROM {$tbl_w} w
                 JOIN {$tbl_pm} pm ON pm.post_id = w.item_id AND pm.meta_key = '_price'
                 WHERE w.user_id IN ({$in_users})
                   AND w.item_type = 'product'
                   AND w.price_at_wishlist IS NOT NULL
                   AND w.price_at_wishlist > 0
                   AND CAST(pm.meta_value AS DECIMAL(12,2)) > 0
                   AND CAST(pm.meta_value AS DECIMAL(12,2)) <= w.price_at_wishlist * %f",
                $multiplier
            ), ARRAY_A);
            foreach ($logged_in as $r) {
                $uid = (int) $r['user_id'];
                $candidates[] = array(
                    'sub_rows' => isset($subs_by_user[$uid]) ? $subs_by_user[$uid] : array(),
                    'item_id'  => (int) $r['item_id'],
                    'snapshot' => (float) $r['snapshot'],
                    'current'  => (float) $r['current_price'],
                    'drop_pct' => (int) $r['drop_pct'],
                    'wid'      => (int) $r['wid'],
                );
            }
        }

        if (!empty($subs_by_token)) {
            $tokens = array_keys($subs_by_token);
            $placeholders = implode(',', array_fill(0, count($tokens), '%s'));
            $args = array_merge($tokens, array($multiplier));
            $anon = $wpdb->get_results($wpdb->prepare(
                "SELECT w.session_id, w.id AS wid, w.item_id,
                        w.price_at_wishlist AS snapshot,
                        ROUND(CAST(pm.meta_value AS DECIMAL(12,2)), 2) AS current_price,
                        ROUND(100 * (w.price_at_wishlist - CAST(pm.meta_value AS DECIMAL(12,2)))
                              / NULLIF(w.price_at_wishlist, 0), 0) AS drop_pct
                 FROM {$tbl_w} w
                 JOIN {$tbl_pm} pm ON pm.post_id = w.item_id AND pm.meta_key = '_price'
                 WHERE w.user_id   = 0
                   AND w.session_id IN ({$placeholders})
                   AND w.item_type = 'product'
                   AND w.price_at_wishlist IS NOT NULL
                   AND w.price_at_wishlist > 0
                   AND CAST(pm.meta_value AS DECIMAL(12,2)) > 0
                   AND CAST(pm.meta_value AS DECIMAL(12,2)) <= w.price_at_wishlist * %f",
                $args
            ), ARRAY_A);
            foreach ($anon as $r) {
                $tok = (string) $r['session_id'];
                $candidates[] = array(
                    'sub_rows' => isset($subs_by_token[$tok]) ? $subs_by_token[$tok] : array(),
                    'item_id'  => (int) $r['item_id'],
                    'snapshot' => (float) $r['snapshot'],
                    'current'  => (float) $r['current_price'],
                    'drop_pct' => (int) $r['drop_pct'],
                    'wid'      => (int) $r['wid'],
                );
            }
        }

        if (empty($candidates)) { return array(); }

        
        $pids = array_values(array_unique(array_map(
            static function ($c) { return (int) $c['item_id']; },
            $candidates
        )));
        if (empty($pids)) { return array(); }
        $in_pids = 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_pids})",
            ARRAY_A
        );
        $title_by_pid = array();
        foreach ($titles as $t) {
            $title_by_pid[(int) $t['ID']] = (string) $t['post_title'];
        }

        
        
        usort($candidates, static function ($a, $b) {
            if ($a['drop_pct'] !== $b['drop_pct']) {
                return $b['drop_pct'] <=> $a['drop_pct'];
            }
            return $a['wid'] <=> $b['wid'];
        });

        
        $out = array();
        $seen = array();
        foreach ($candidates as $c) {
            $pid = $c['item_id'];
            if (!isset($title_by_pid[$pid])) { continue; }
            foreach ($c['sub_rows'] as $sub_row) {
                $key = $sub_row['subscription_id'] . ':' . $pid;
                if (isset($seen[$key])) { continue; }
                $seen[$key] = true;

                $url = (string) get_permalink($pid);
                $out[] = array(
                    'subscription_id'   => $sub_row['subscription_id'],
                    'user_id'           => $sub_row['user_id'],
                    'target_product_id' => $pid,
                    'target_url'        => $url ?: home_url('/'),
                    'payload_data'      => array(
                        'product_name'  => (string) $title_by_pid[$pid],
                        'snapshot_price'=> $c['snapshot'],
                        'current_price' => $c['current'],
                        'drop_pct'      => $c['drop_pct'],
                    ),
                );
                if (count($out) >= $limit) { break 2; }
            }
        }

        return $out;
    }
}
