<?php
/**
 * Bizmart — Unified inventory metrics engine.
 *
 * @package Bizmart
 * @license GPL-2.0-or-later
 */
if (!defined('ABSPATH')) exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

/**
 * Unified inventory metrics engine.
 *
 * Stock value = Σ (stock_qty × price) for every item that manages stock.
 *
 * Rules:
 *  1. Products with stock management ON: use their actual stock quantity.
 *  2. Products with stock management OFF but stock_status = 'instock': count as qty = 1.
 *     This avoids confusing users who see a published in-stock product but no inventory value.
 *  3. Simple / External / Grouped: count the product directly.
 *  4. Variable (parent manages stock): count the parent once, skip its variations.
 *  5. Variable (variations manage stock): count each variation, skip the parent.
 *  6. Variable (nobody manages stock, but in-stock variations): count each instock variation as qty=1.
 *  7. Purchase price lookup chain: variation _purchase_price → parent _purchase_price
 *     → variation _alg_wc_cog_cost → parent _alg_wc_cog_cost → 0.
 *  8. Negative stock is included (backorders) — reflects real liability.
 *
 * Results are cached in a transient (10 min) and invalidated by cache.php hooks.
 */

if (!function_exists('bizmart_metrics_get_inventory_totals')) {

    /**
     * Resolve purchase/cost price for a product or variation.
     *
     * @param int $id        Product or variation ID
     * @param int $parent_id Parent product ID (0 for non-variations)
     * @return float
     */
    function bizmart_metrics_resolve_purchase_price(int $id, int $parent_id = 0): float {
        // v3.0 — WAC mode: try weighted average cost first
        if (function_exists('bizmart_get_valuation_method') && bizmart_get_valuation_method() === 'wac') {
            if (function_exists('bizmart_get_wac')) {
                $wac = bizmart_get_wac($id);
                if ($wac > 0) return $wac;
                if ($parent_id > 0) {
                    $wac = bizmart_get_wac($parent_id);
                    if ($wac > 0) return $wac;
                }
            }
        }

        // 1. Own _purchase_price
        $pp = get_post_meta($id, '_purchase_price', true);
        if ($pp !== '' && $pp !== null && $pp !== false) {
            $clean = bizmart_metrics_clean_price($pp);
            if ($clean !== null) return $clean;
        }

        // 2. Parent _purchase_price (variations inherit from parent)
        if ($parent_id > 0) {
            $pp = get_post_meta($parent_id, '_purchase_price', true);
            if ($pp !== '' && $pp !== null && $pp !== false) {
                $clean = bizmart_metrics_clean_price($pp);
                if ($clean !== null) return $clean;
            }
        }

        // 3. Cost of Goods compatibility (_alg_wc_cog_cost)
        $cog = get_post_meta($id, '_alg_wc_cog_cost', true);
        if ($cog !== '' && $cog !== null && $cog !== false) {
            $clean = bizmart_metrics_clean_price($cog);
            if ($clean !== null) return $clean;
        }

        // 4. Parent CoG
        if ($parent_id > 0) {
            $cog = get_post_meta($parent_id, '_alg_wc_cog_cost', true);
            if ($cog !== '' && $cog !== null && $cog !== false) {
                $clean = bizmart_metrics_clean_price($cog);
                if ($clean !== null) return $clean;
            }
        }

        return 0.0;
    }

    /**
     * Normalise a raw price string (handles commas, spaces, currency symbols).
     *
     * @return float|null  null when the value is not numeric
     */
    function bizmart_metrics_clean_price($raw): ?float {
        if (is_numeric($raw)) return (float) $raw;
        if (!is_string($raw)) return null;

        $clean = preg_replace('/[^\d\.,-]/', '', $raw);
        // "1.234,56" → European format
        if (substr_count($clean, ',') > 0 && substr_count($clean, '.') === 0) {
            $clean = str_replace(',', '.', $clean);
        } else {
            $clean = str_replace(',', '', $clean);
        }
        return is_numeric($clean) ? (float) $clean : null;
    }

    /**
     * Calculate inventory totals: stock qty, sale value, purchase value.
     *
     * @param bool $use_cache  Whether to use/set the transient cache.
     * @return array{stock_sales_value: float, stock_purchase_value: float, total_stock_qty: float, generated_at: string}
     */
    function bizmart_metrics_get_inventory_totals(bool $use_cache = true): array {
        $blog_id   = (int) get_current_blog_id();
        $cache_key = 'bizmart_inventory_totals_v9_' . $blog_id;
        $ttl       = 10 * MINUTE_IN_SECONDS;

        if ($use_cache) {
            $cached = get_transient($cache_key);
            if (is_array($cached)
                && isset($cached['stock_sales_value'], $cached['stock_purchase_value'], $cached['total_stock_qty'])
            ) {
                return $cached;
            }
        }

        global $wpdb;

        $posts = $wpdb->get_results("
            SELECT ID, post_type, post_parent, post_status 
            FROM {$wpdb->posts} 
            WHERE (post_type = 'product' AND post_status IN ('publish', 'private'))
               OR (post_type = 'product_variation' AND post_status IN ('publish', 'private', 'inherit'))
        ", ARRAY_A);

        if (empty($posts)) {
            return [
                'stock_sales_value'    => 0.0,
                'stock_purchase_value' => 0.0,
                'total_stock_qty'      => 0.0,
                'generated_at'         => current_time('mysql'),
            ];
        }

        $ids = array_column($posts, 'ID');
        $meta_map = [];
        $chunks = array_chunk($ids, 1000);
        foreach ($chunks as $chunk) {
            $chunk_ids = implode(',', array_map('intval', $chunk));
            $meta_rows = $wpdb->get_results("
                SELECT post_id, meta_key, meta_value 
                FROM {$wpdb->postmeta} 
                WHERE post_id IN ($chunk_ids) 
                  AND meta_key IN ('_manage_stock', '_stock', '_stock_status', '_price', '_purchase_price', '_alg_wc_cog_cost')
            ", ARRAY_A);
            
            foreach ($meta_rows as $row) {
                $pid = (int) $row['post_id'];
                $meta_map[$pid][$row['meta_key']] = $row['meta_value'];
            }
        }

        $variations_by_parent = [];
        $parent_ids = [];

        foreach ($posts as $p) {
            if ($p['post_type'] === 'product_variation') {
                $parent_id = (int) $p['post_parent'];
                if ($parent_id > 0) {
                    $variations_by_parent[$parent_id][] = (int) $p['ID'];
                    $parent_ids[$parent_id] = true;
                }
            }
        }

        $stock_sales_value    = 0.0;
        $stock_purchase_value = 0.0;
        $total_stock_qty      = 0.0;

        foreach ($posts as $p) {
            $pid = (int) $p['ID'];

            if ($p['post_type'] === 'product_variation') {
                continue;
            }

            $is_variable = isset($parent_ids[$pid]);
            $manage_stock = isset($meta_map[$pid]['_manage_stock']) ? $meta_map[$pid]['_manage_stock'] : 'no';
            $managing_stock = ($manage_stock === 'yes' || $manage_stock === '1');

            if ($is_variable) {
                if ($managing_stock) {
                    $qty = isset($meta_map[$pid]['_stock']) ? (float)$meta_map[$pid]['_stock'] : 0.0;
                    if ($qty == 0) continue;

                    $sell = isset($meta_map[$pid]['_price']) ? (float)$meta_map[$pid]['_price'] : 0.0;
                    $pp = bizmart_metrics_resolve_purchase_price($pid);

                    $total_stock_qty      += $qty;
                    $stock_sales_value    += $qty * $sell;
                    $stock_purchase_value += $qty * $pp;
                } else {
                    if (isset($variations_by_parent[$pid])) {
                        foreach ($variations_by_parent[$pid] as $var_id) {
                            $v_manage = isset($meta_map[$var_id]['_manage_stock']) ? $meta_map[$var_id]['_manage_stock'] : 'no';
                            $v_managing = ($v_manage === 'yes' || $v_manage === '1');
                            $v_status = isset($meta_map[$var_id]['_stock_status']) ? $meta_map[$var_id]['_stock_status'] : 'instock';

                            if ($v_managing) {
                                $qty = isset($meta_map[$var_id]['_stock']) ? (float)$meta_map[$var_id]['_stock'] : 0.0;
                            } elseif ($v_status === 'instock') {
                                $qty = 1.0;
                            } else {
                                continue;
                            }
                            if ($qty == 0) continue;

                            $sell = isset($meta_map[$var_id]['_price']) ? (float)$meta_map[$var_id]['_price'] : 0.0;
                            
                            $pp = 0.0;
                            if (function_exists('bizmart_get_valuation_method') && bizmart_get_valuation_method() === 'wac' && function_exists('bizmart_get_wac')) {
                                $wac = bizmart_get_wac($var_id);
                                if ($wac > 0) {
                                    $pp = $wac;
                                } else {
                                    $wac_parent = bizmart_get_wac($pid);
                                    if ($wac_parent > 0) $pp = $wac_parent;
                                }
                            }
                            if ($pp <= 0.0) {
                                $v_pp = isset($meta_map[$var_id]['_purchase_price']) ? $meta_map[$var_id]['_purchase_price'] : '';
                                $v_pp_clean = bizmart_metrics_clean_price($v_pp);
                                if ($v_pp_clean !== null) {
                                    $pp = $v_pp_clean;
                                } else {
                                    $p_pp = isset($meta_map[$pid]['_purchase_price']) ? $meta_map[$pid]['_purchase_price'] : '';
                                    $p_pp_clean = bizmart_metrics_clean_price($p_pp);
                                    if ($p_pp_clean !== null) {
                                        $pp = $p_pp_clean;
                                    } else {
                                        $v_cog = isset($meta_map[$var_id]['_alg_wc_cog_cost']) ? $meta_map[$var_id]['_alg_wc_cog_cost'] : '';
                                        $v_cog_clean = bizmart_metrics_clean_price($v_cog);
                                        if ($v_cog_clean !== null) {
                                            $pp = $v_cog_clean;
                                        } else {
                                            $p_cog = isset($meta_map[$pid]['_alg_wc_cog_cost']) ? $meta_map[$pid]['_alg_wc_cog_cost'] : '';
                                            $p_cog_clean = bizmart_metrics_clean_price($p_cog);
                                            if ($p_cog_clean !== null) $pp = $p_cog_clean;
                                        }
                                    }
                                }
                            }

                            $total_stock_qty      += $qty;
                            $stock_sales_value    += $qty * $sell;
                            $stock_purchase_value += $qty * $pp;
                        }
                    }
                }
            } else {
                $status = isset($meta_map[$pid]['_stock_status']) ? $meta_map[$pid]['_stock_status'] : 'instock';
                if ($managing_stock) {
                    $qty = isset($meta_map[$pid]['_stock']) ? (float)$meta_map[$pid]['_stock'] : 0.0;
                } elseif ($status === 'instock') {
                    $qty = 1.0;
                } else {
                    continue;
                }
                if ($qty == 0) continue;

                $sell = isset($meta_map[$pid]['_price']) ? (float)$meta_map[$pid]['_price'] : 0.0;
                
                $pp = 0.0;
                if (function_exists('bizmart_get_valuation_method') && bizmart_get_valuation_method() === 'wac' && function_exists('bizmart_get_wac')) {
                    $wac = bizmart_get_wac($pid);
                    if ($wac > 0) $pp = $wac;
                }
                if ($pp <= 0.0) {
                    $v_pp = isset($meta_map[$pid]['_purchase_price']) ? $meta_map[$pid]['_purchase_price'] : '';
                    $v_pp_clean = bizmart_metrics_clean_price($v_pp);
                    if ($v_pp_clean !== null) {
                        $pp = $v_pp_clean;
                    } else {
                        $v_cog = isset($meta_map[$pid]['_alg_wc_cog_cost']) ? $meta_map[$pid]['_alg_wc_cog_cost'] : '';
                        $v_cog_clean = bizmart_metrics_clean_price($v_cog);
                        if ($v_cog_clean !== null) $pp = $v_cog_clean;
                    }
                }

                $total_stock_qty      += $qty;
                $stock_sales_value    += $qty * $sell;
                $stock_purchase_value += $qty * $pp;
            }
        }

        $totals = [
            'stock_sales_value'    => round($stock_sales_value, 2),
            'stock_purchase_value' => round($stock_purchase_value, 2),
            'total_stock_qty'      => round($total_stock_qty, 0),
            'generated_at'         => current_time('mysql'),
        ];

        set_transient($cache_key, $totals, $ttl);
        return $totals;
    }

    function bizmart_metrics_get_inventory_rows(): array {
        global $wpdb;

        $posts = $wpdb->get_results("
            SELECT ID, post_title, post_type, post_parent, post_status 
            FROM {$wpdb->posts} 
            WHERE (post_type = 'product' AND post_status IN ('publish', 'private'))
               OR (post_type = 'product_variation' AND post_status IN ('publish', 'private', 'inherit'))
        ", ARRAY_A);

        if (empty($posts)) {
            return [];
        }

        $ids = array_column($posts, 'ID');
        $meta_map = [];
        $chunks = array_chunk($ids, 1000);
        foreach ($chunks as $chunk) {
            $chunk_ids = implode(',', array_map('intval', $chunk));
            $meta_rows = $wpdb->get_results("
                SELECT post_id, meta_key, meta_value 
                FROM {$wpdb->postmeta} 
                WHERE post_id IN ($chunk_ids) 
                  AND meta_key IN ('_manage_stock', '_stock', '_stock_status', '_price', '_purchase_price', '_alg_wc_cog_cost', '_sku')
            ", ARRAY_A);
            
            foreach ($meta_rows as $row) {
                $pid = (int) $row['post_id'];
                $meta_map[$pid][$row['meta_key']] = $row['meta_value'];
            }
        }

        $variations_by_parent = [];
        $parent_ids = [];

        foreach ($posts as $p) {
            if ($p['post_type'] === 'product_variation') {
                $parent_id = (int) $p['post_parent'];
                if ($parent_id > 0) {
                    $variations_by_parent[$parent_id][] = $p;
                    $parent_ids[$parent_id] = true;
                }
            }
        }

        $rows = [];

        foreach ($posts as $p) {
            $pid = (int) $p['ID'];

            if ($p['post_type'] === 'product_variation') {
                continue;
            }

            $is_variable = isset($parent_ids[$pid]);
            $manage_stock = isset($meta_map[$pid]['_manage_stock']) ? $meta_map[$pid]['_manage_stock'] : 'no';
            $managing_stock = ($manage_stock === 'yes' || $manage_stock === '1');

            if ($is_variable) {
                if ($managing_stock) {
                    $qty = isset($meta_map[$pid]['_stock']) ? (float)$meta_map[$pid]['_stock'] : 0.0;
                    if ($qty == 0) continue;
                    $rows[] = [
                        'id'     => $pid,
                        'name'   => $p['post_title'] . ' (parent-managed)',
                        'type'   => 'variable',
                        'sku'    => isset($meta_map[$pid]['_sku']) ? $meta_map[$pid]['_sku'] : '',
                        'qty'    => $qty,
                        'sell'   => isset($meta_map[$pid]['_price']) ? (float)$meta_map[$pid]['_price'] : 0.0,
                        'pp'     => bizmart_metrics_resolve_purchase_price($pid),
                        'managed'=> true,
                    ];
                } else {
                    if (isset($variations_by_parent[$pid])) {
                        foreach ($variations_by_parent[$pid] as $var_p) {
                            $var_id = (int) $var_p['ID'];
                            $v_manage = isset($meta_map[$var_id]['_manage_stock']) ? $meta_map[$var_id]['_manage_stock'] : 'no';
                            $v_managing = ($v_manage === 'yes' || $v_manage === '1');
                            $v_status = isset($meta_map[$var_id]['_stock_status']) ? $meta_map[$var_id]['_stock_status'] : 'instock';

                            if ($v_managing) {
                                $qty = isset($meta_map[$var_id]['_stock']) ? (float)$meta_map[$var_id]['_stock'] : 0.0;
                            } elseif ($v_status === 'instock') {
                                $qty = 1.0;
                            } else {
                                continue;
                            }
                            if ($qty == 0) continue;

                            // Resolve variation purchase price fast
                            $pp = 0.0;
                            if (function_exists('bizmart_get_valuation_method') && bizmart_get_valuation_method() === 'wac' && function_exists('bizmart_get_wac')) {
                                $wac = bizmart_get_wac($var_id);
                                if ($wac > 0) {
                                    $pp = $wac;
                                } else {
                                    $wac_parent = bizmart_get_wac($pid);
                                    if ($wac_parent > 0) $pp = $wac_parent;
                                }
                            }
                            if ($pp <= 0.0) {
                                $v_pp = isset($meta_map[$var_id]['_purchase_price']) ? $meta_map[$var_id]['_purchase_price'] : '';
                                $v_pp_clean = bizmart_metrics_clean_price($v_pp);
                                if ($v_pp_clean !== null) {
                                    $pp = $v_pp_clean;
                                } else {
                                    $p_pp = isset($meta_map[$pid]['_purchase_price']) ? $meta_map[$pid]['_purchase_price'] : '';
                                    $p_pp_clean = bizmart_metrics_clean_price($p_pp);
                                    if ($p_pp_clean !== null) {
                                        $pp = $p_pp_clean;
                                    } else {
                                        $v_cog = isset($meta_map[$var_id]['_alg_wc_cog_cost']) ? $meta_map[$var_id]['_alg_wc_cog_cost'] : '';
                                        $v_cog_clean = bizmart_metrics_clean_price($v_cog);
                                        if ($v_cog_clean !== null) {
                                            $pp = $v_cog_clean;
                                        } else {
                                            $p_cog = isset($meta_map[$pid]['_alg_wc_cog_cost']) ? $meta_map[$pid]['_alg_wc_cog_cost'] : '';
                                            $p_cog_clean = bizmart_metrics_clean_price($p_cog);
                                            if ($p_cog_clean !== null) $pp = $p_cog_clean;
                                        }
                                    }
                                }
                            }

                            $rows[] = [
                                'id'     => $var_id,
                                'name'   => $var_p['post_title'],
                                'type'   => 'variation',
                                'sku'    => isset($meta_map[$var_id]['_sku']) ? $meta_map[$var_id]['_sku'] : '',
                                'qty'    => $qty,
                                'sell'   => isset($meta_map[$var_id]['_price']) ? (float)$meta_map[$var_id]['_price'] : 0.0,
                                'pp'     => $pp,
                                'managed'=> $v_managing,
                            ];
                        }
                    }
                }
            } else {
                $status = isset($meta_map[$pid]['_stock_status']) ? $meta_map[$pid]['_stock_status'] : 'instock';
                if ($managing_stock) {
                    $qty = isset($meta_map[$pid]['_stock']) ? (float)$meta_map[$pid]['_stock'] : 0.0;
                } elseif ($status === 'instock') {
                    $qty = 1.0;
                } else {
                    continue;
                }
                if ($qty == 0) continue;

                $pp = 0.0;
                if (function_exists('bizmart_get_valuation_method') && bizmart_get_valuation_method() === 'wac' && function_exists('bizmart_get_wac')) {
                    $wac = bizmart_get_wac($pid);
                    if ($wac > 0) $pp = $wac;
                }
                if ($pp <= 0.0) {
                    $v_pp = isset($meta_map[$pid]['_purchase_price']) ? $meta_map[$pid]['_purchase_price'] : '';
                    $v_pp_clean = bizmart_metrics_clean_price($v_pp);
                    if ($v_pp_clean !== null) {
                        $pp = $v_pp_clean;
                    } else {
                        $v_cog = isset($meta_map[$pid]['_alg_wc_cog_cost']) ? $meta_map[$pid]['_alg_wc_cog_cost'] : '';
                        $v_cog_clean = bizmart_metrics_clean_price($v_cog);
                        if ($v_cog_clean !== null) $pp = $v_cog_clean;
                    }
                }

                $rows[] = [
                    'id'     => $pid,
                    'name'   => $p['post_title'],
                    'type'   => 'simple',
                    'sku'    => isset($meta_map[$pid]['_sku']) ? $meta_map[$pid]['_sku'] : '',
                    'qty'    => $qty,
                    'sell'   => isset($meta_map[$pid]['_price']) ? (float)$meta_map[$pid]['_price'] : 0.0,
                    'pp'     => $pp,
                    'managed'=> $managing_stock,
                ];
            }
        }

        return $rows;
    }
}
