<?php
/**
 * Inventory_Controller.php — ماژول انبارداری
 * محصولات درختی | انبارها | اسناد حرکت
 * قیمت‌گذاری: میانگین موزون + FIFO
 * ردیابی: بچ (Lot) + سریال نامبر
 */

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

class Inventory_Controller {

    /* ── ثبت مسیرها ─────────────────────────────────────────── */
    public static function register_routes(): void {
        $ns = 'acc/v1';
        $p  = 'acc_jwt_check_permission';

        // ── ACL-aware permission callbacks ────────────────────
        $pView    = function_exists('acl_permission_callback') ? acl_permission_callback('inv.view')              : $p;
        $pProd    = function_exists('acl_permission_callback') ? acl_permission_callback('inv.products.manage')   : $p;
        $pWh      = function_exists('acl_permission_callback') ? acl_permission_callback('inv.warehouses.manage') : $p;
        $pVcreate = function_exists('acl_permission_callback') ? acl_permission_callback('inv.vouchers.create')   : $p;
        $pVconf   = function_exists('acl_permission_callback') ? acl_permission_callback('inv.vouchers.confirm')  : $p;
        $pVcanc   = function_exists('acl_permission_callback') ? acl_permission_callback('inv.vouchers.cancel')   : $p;
        $pRep     = function_exists('acl_permission_callback') ? acl_permission_callback('inv.reports.view')      : $p;

        /* دسته‌بندی‌ها */
        register_rest_route($ns, '/inv/categories',           [['methods'=>'GET',  'callback'=>[self::class,'get_categories'],   'permission_callback'=>$pView],
                                                               ['methods'=>'POST', 'callback'=>[self::class,'create_category'],  'permission_callback'=>$pProd]]);
        register_rest_route($ns, '/inv/categories/(?P<id>\d+)', [['methods'=>'PUT',    'callback'=>[self::class,'update_category'], 'permission_callback'=>$pProd],
                                                                  ['methods'=>'DELETE', 'callback'=>[self::class,'delete_category'], 'permission_callback'=>$pProd]]);

        /* محصولات */
        register_rest_route($ns, '/inv/products',             [['methods'=>'GET',  'callback'=>[self::class,'get_products'],   'permission_callback'=>$pView],
                                                               ['methods'=>'POST', 'callback'=>[self::class,'create_product'], 'permission_callback'=>$pProd]]);
        register_rest_route($ns, '/inv/products/(?P<id>\d+)', [['methods'=>'GET',    'callback'=>[self::class,'get_product'],   'permission_callback'=>$pView],
                                                                ['methods'=>'PUT',    'callback'=>[self::class,'update_product'], 'permission_callback'=>$pProd],
                                                                ['methods'=>'DELETE', 'callback'=>[self::class,'delete_product'], 'permission_callback'=>$pProd]]);

        /* انبارها */
        register_rest_route($ns, '/inv/warehouses',             [['methods'=>'GET',  'callback'=>[self::class,'get_warehouses'],   'permission_callback'=>$pView],
                                                                  ['methods'=>'POST', 'callback'=>[self::class,'create_warehouse'], 'permission_callback'=>$pWh]]);
        register_rest_route($ns, '/inv/warehouses/(?P<id>\d+)', [['methods'=>'PUT',    'callback'=>[self::class,'update_warehouse'], 'permission_callback'=>$pWh],
                                                                   ['methods'=>'DELETE', 'callback'=>[self::class,'delete_warehouse'], 'permission_callback'=>$pWh]]);

        /* موجودی */
        register_rest_route($ns, '/inv/stock',                        ['methods'=>'GET', 'callback'=>[self::class,'get_stock'],         'permission_callback'=>$pView]);
        register_rest_route($ns, '/inv/stock/product/(?P<id>\d+)',    ['methods'=>'GET', 'callback'=>[self::class,'get_product_stock'], 'permission_callback'=>$pView]);
        register_rest_route($ns, '/inv/stock/warehouse/(?P<id>\d+)',  ['methods'=>'GET', 'callback'=>[self::class,'get_wh_stock'],      'permission_callback'=>$pView]);
        register_rest_route($ns, '/inv/stock/low',                    ['methods'=>'GET', 'callback'=>[self::class,'get_low_stock'],     'permission_callback'=>$pRep]);

        /* اسناد */
        register_rest_route($ns, '/inv/vouchers',             [['methods'=>'GET',  'callback'=>[self::class,'get_vouchers'],   'permission_callback'=>$pView],
                                                               ['methods'=>'POST', 'callback'=>[self::class,'create_voucher'], 'permission_callback'=>$pVcreate]]);
        register_rest_route($ns, '/inv/vouchers/(?P<id>\d+)', [['methods'=>'GET',    'callback'=>[self::class,'get_voucher'],    'permission_callback'=>$pView],
                                                                ['methods'=>'PUT',    'callback'=>[self::class,'update_voucher'], 'permission_callback'=>$pVcreate],
                                                                ['methods'=>'DELETE', 'callback'=>[self::class,'cancel_voucher'], 'permission_callback'=>$pVcanc]]);
        register_rest_route($ns, '/inv/vouchers/(?P<id>\d+)/confirm', ['methods'=>'POST', 'callback'=>[self::class,'confirm_voucher'], 'permission_callback'=>$pVconf]);

        /* بچ */
        register_rest_route($ns, '/inv/batches',             [['methods'=>'GET',  'callback'=>[self::class,'get_batches'],   'permission_callback'=>$p],
                                                              ['methods'=>'POST', 'callback'=>[self::class,'create_batch'],  'permission_callback'=>$p]]);
        register_rest_route($ns, '/inv/batches/(?P<id>\d+)', [['methods'=>'PUT',    'callback'=>[self::class,'update_batch'], 'permission_callback'=>$p],
                                                               ['methods'=>'DELETE', 'callback'=>[self::class,'delete_batch'], 'permission_callback'=>$p]]);

        /* سریال نامبر */
        register_rest_route($ns, '/inv/serials',             ['methods'=>'GET',  'callback'=>[self::class,'get_serials'],   'permission_callback'=>$p]);
        register_rest_route($ns, '/inv/serials/(?P<id>\d+)', ['methods'=>'PUT',  'callback'=>[self::class,'update_serial'], 'permission_callback'=>$p]);

        /* گزارش‌ها */
        register_rest_route($ns, '/inv/reports/card',       ['methods'=>'GET', 'callback'=>[self::class,'report_card'],       'permission_callback'=>$pRep]);
        register_rest_route($ns, '/inv/reports/valuation',  ['methods'=>'GET', 'callback'=>[self::class,'report_valuation'],  'permission_callback'=>$pRep]);
        register_rest_route($ns, '/inv/reports/expiring',   ['methods'=>'GET', 'callback'=>[self::class,'report_expiring'],   'permission_callback'=>$pRep]);
    }

    /* ══════════════════════════════════════════════════════════
       دسته‌بندی‌ها
    ══════════════════════════════════════════════════════════ */
    public static function get_categories( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $rows = $wpdb->get_results(
            "SELECT c.*, COUNT(p.id) AS product_count
             FROM {$wpdb->prefix}inv_categories c
             LEFT JOIN {$wpdb->prefix}inv_products p ON p.category_id = c.id AND p.is_active = 1
             GROUP BY c.id
             ORDER BY c.path, c.name",
            ARRAY_A
        );
        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    public static function create_category( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;

        $parent_id = (int)($req->get_param('parent_id') ?? 0);
        $level     = 0;
        $path      = '';

        if ( $parent_id ) {
            $parent = $wpdb->get_row($wpdb->prepare("SELECT level, path, name FROM {$pre}inv_categories WHERE id=%d", $parent_id), ARRAY_A);
            if ( $parent ) {
                $level = $parent['level'] + 1;
                $path  = $parent['path'] . '/' . $parent['name'];
            }
        }

        $wpdb->insert("{$pre}inv_categories", [
            'parent_id' => $parent_id,
            'code'      => sanitize_text_field($req->get_param('code') ?? ''),
            'name'      => sanitize_text_field($req->get_param('name') ?? ''),
            'level'     => $level,
            'path'      => $path,
        ]);
        return new WP_REST_Response(['id' => $wpdb->insert_id, 'success' => true], 201);
    }

    public static function update_category( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $data = [];
        if ( $req->get_param('name') !== null ) $data['name'] = sanitize_text_field($req->get_param('name'));
        if ( $req->get_param('code') !== null ) $data['code'] = sanitize_text_field($req->get_param('code'));
        if ( $data ) $wpdb->update($wpdb->prefix.'inv_categories', $data, ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    public static function delete_category( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id = (int)$req->get_param('id');
        $has_products = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}inv_products WHERE category_id=%d", $id
        ));
        if ( $has_products ) return new WP_REST_Response(['message' => 'این دسته دارای محصول است'], 409);
        $wpdb->delete($wpdb->prefix.'inv_categories', ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    /* ══════════════════════════════════════════════════════════
       محصولات
    ══════════════════════════════════════════════════════════ */
    public static function get_products( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre    = $wpdb->prefix;
        $wheres = ['p.is_active = 1'];
        $args   = [];

        if ( $v = $req->get_param('category_id') ) { $wheres[] = 'p.category_id = %d'; $args[] = (int)$v; }
        if ( $v = $req->get_param('search') )       { $wheres[] = '(p.code LIKE %s OR p.name LIKE %s)'; $s="%{$v}%"; $args[]=$s; $args[]=$s; }
        if ( $req->get_param('active_only') === '0' ) { $wheres = array_filter($wheres, fn($w) => $w !== 'p.is_active = 1'); }

        $where = $wheres ? 'WHERE ' . implode(' AND ', $wheres) : '';
        $sql   = "SELECT p.*, c.name AS category_name,
                         COALESCE(SUM(s.qty),0) AS total_stock
                  FROM {$pre}inv_products p
                  LEFT JOIN {$pre}inv_categories c ON c.id = p.category_id
                  LEFT JOIN {$pre}inv_stock s ON s.product_id = p.id
                  {$where}
                  GROUP BY p.id
                  ORDER BY c.name, p.name";

        $rows = $args ? $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A)
                      : $wpdb->get_results($sql, ARRAY_A);

        return new WP_REST_Response(['data' => $rows ?? [], 'total' => count($rows ?? [])]);
    }

    public static function get_product( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $id  = (int)$req->get_param('id');

        $product = $wpdb->get_row($wpdb->prepare(
            "SELECT p.*, c.name AS category_name
             FROM {$pre}inv_products p
             LEFT JOIN {$pre}inv_categories c ON c.id = p.category_id
             WHERE p.id = %d", $id
        ), ARRAY_A);

        if ( ! $product ) return new WP_REST_Response(['message' => 'محصول یافت نشد'], 404);

        $product['stock'] = $wpdb->get_results($wpdb->prepare(
            "SELECT s.*, w.name AS warehouse_name, w.type AS warehouse_type
             FROM {$pre}inv_stock s
             JOIN {$pre}inv_warehouses w ON w.id = s.warehouse_id
             WHERE s.product_id = %d", $id
        ), ARRAY_A);

        $product['batches'] = $wpdb->get_results($wpdb->prepare(
            "SELECT b.*, COALESCE(SUM(bs.qty),0) AS total_qty
             FROM {$pre}inv_batches b
             LEFT JOIN {$pre}inv_batch_stock bs ON bs.batch_id = b.id
             WHERE b.product_id = %d
             GROUP BY b.id
             ORDER BY b.exp_date ASC", $id
        ), ARRAY_A);

        return new WP_REST_Response(['data' => $product]);
    }

    public static function create_product( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $data = [
            'category_id'       => $req->get_param('category_id') ? (int)$req->get_param('category_id') : null,
            'code'              => sanitize_text_field($req->get_param('code') ?? ''),
            'name'              => sanitize_text_field($req->get_param('name') ?? ''),
            'unit'              => sanitize_text_field($req->get_param('unit') ?? 'عدد'),
            'unit2'             => sanitize_text_field($req->get_param('unit2') ?? ''),
            'conversion_factor' => $req->get_param('conversion_factor') ? (float)$req->get_param('conversion_factor') : 1,
            'costing_method'    => in_array($req->get_param('costing_method'), ['avg','fifo']) ? $req->get_param('costing_method') : 'avg',
            'tracking'          => in_array($req->get_param('tracking'), ['none','batch','serial','both']) ? $req->get_param('tracking') : 'none',
            'min_stock'         => (float)($req->get_param('min_stock') ?? 0),
            'max_stock'         => (float)($req->get_param('max_stock') ?? 0),
            'reorder_qty'       => (float)($req->get_param('reorder_qty') ?? 0),
            'description'       => sanitize_textarea_field($req->get_param('description') ?? ''),
        ];
        if ( ! $data['name'] ) return new WP_REST_Response(['message' => 'نام محصول الزامی است'], 400);
        $wpdb->insert($wpdb->prefix.'inv_products', $data);
        return new WP_REST_Response(['id' => $wpdb->insert_id, 'success' => true], 201);
    }

    public static function update_product( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $keys = ['category_id','code','name','unit','unit2','conversion_factor','costing_method','tracking','min_stock','max_stock','reorder_qty','description','is_active'];
        $data = [];
        foreach ( $keys as $k ) {
            $v = $req->get_param($k);
            if ( $v === null ) continue;
            $data[$k] = in_array($k, ['category_id','is_active']) ? (int)$v
                      : (in_array($k, ['conversion_factor','min_stock','max_stock','reorder_qty']) ? (float)$v
                      : sanitize_textarea_field((string)$v));
        }
        if ( $data ) $wpdb->update($wpdb->prefix.'inv_products', $data, ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    public static function delete_product( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id  = (int)$req->get_param('id');
        $qty = $wpdb->get_var($wpdb->prepare(
            "SELECT COALESCE(SUM(qty),0) FROM {$wpdb->prefix}inv_stock WHERE product_id=%d", $id
        ));
        if ( $qty > 0 ) return new WP_REST_Response(['message' => 'موجودی انبار این محصول صفر نیست'], 409);
        $wpdb->update($wpdb->prefix.'inv_products', ['is_active' => 0], ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    /* ══════════════════════════════════════════════════════════
       انبارها
    ══════════════════════════════════════════════════════════ */
    public static function get_warehouses( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $uid    = acl_current_user_id();
        $filter = acl_scope_sql_filter( $uid, 'warehouse', 'w.id' );
        $sql    = "SELECT w.*,
                    COUNT(DISTINCT s.product_id) AS product_count,
                    COALESCE(SUM(s.qty * s.avg_cost), 0) AS total_value
             FROM {$wpdb->prefix}inv_warehouses w
             LEFT JOIN {$wpdb->prefix}inv_stock s ON s.warehouse_id = w.id AND s.qty > 0
             WHERE w.is_active = 1";
        if ( $filter ) {
            $sql .= $filter['sql'];
            $sql .= " GROUP BY w.id ORDER BY w.name";
            $rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$filter['args'] ), ARRAY_A );
        } else {
            $sql .= " GROUP BY w.id ORDER BY w.name";
            $rows = $wpdb->get_results( $sql, ARRAY_A );
        }
        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    public static function create_warehouse( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $type = $req->get_param('type');
        if ( ! in_array($type, ['main','scrap','consignment','project']) ) $type = 'main';
        $wpdb->insert($wpdb->prefix.'inv_warehouses', [
            'code'         => sanitize_text_field($req->get_param('code') ?? ''),
            'name'         => sanitize_text_field($req->get_param('name') ?? ''),
            'type'         => $type,
            'location'     => sanitize_text_field($req->get_param('location') ?? ''),
            'manager_name' => sanitize_text_field($req->get_param('manager_name') ?? ''),
        ]);
        return new WP_REST_Response(['id' => $wpdb->insert_id, 'success' => true], 201);
    }

    public static function update_warehouse( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $deny = acl_deny_scope( acl_current_user_id(), 'warehouse', $id, 'inv.warehouses.manage' );
        if ( $deny ) return $deny;
        $data = [];
        foreach (['code','name','type','location','manager_name','is_active'] as $k) {
            $v = $req->get_param($k);
            if ( $v !== null ) $data[$k] = ($k === 'is_active') ? (int)$v : sanitize_text_field((string)$v);
        }
        if ( $data ) $wpdb->update($wpdb->prefix.'inv_warehouses', $data, ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    public static function delete_warehouse( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id  = (int)$req->get_param('id');
        $deny = acl_deny_scope( acl_current_user_id(), 'warehouse', $id, 'inv.warehouses.manage' );
        if ( $deny ) return $deny;
        $qty = $wpdb->get_var($wpdb->prepare(
            "SELECT COALESCE(SUM(qty),0) FROM {$wpdb->prefix}inv_stock WHERE warehouse_id=%d", $id
        ));
        if ( $qty > 0 ) return new WP_REST_Response(['message' => 'انبار دارای موجودی است'], 409);
        $wpdb->update($wpdb->prefix.'inv_warehouses', ['is_active' => 0], ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    /* ══════════════════════════════════════════════════════════
       موجودی
    ══════════════════════════════════════════════════════════ */
    public static function get_stock( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre    = $wpdb->prefix;
        $uid    = acl_current_user_id();
        $filter = acl_scope_sql_filter( $uid, 'warehouse', 's.warehouse_id' );
        $sql    = "SELECT s.*, p.code AS product_code, p.name AS product_name,
                    p.unit, p.min_stock, p.costing_method, p.tracking,
                    w.name AS warehouse_name, w.type AS warehouse_type,
                    (s.qty * s.avg_cost) AS stock_value
             FROM {$pre}inv_stock s
             JOIN {$pre}inv_products  p ON p.id = s.product_id
             JOIN {$pre}inv_warehouses w ON w.id = s.warehouse_id
             WHERE s.qty > 0";
        if ( $filter ) {
            $sql .= $filter['sql'] . ' ORDER BY p.name, w.name';
            $rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$filter['args'] ), ARRAY_A );
        } else {
            $sql .= ' ORDER BY p.name, w.name';
            $rows = $wpdb->get_results( $sql, ARRAY_A );
        }
        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    public static function get_product_stock( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $pre  = $wpdb->prefix;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT s.*, w.name AS warehouse_name, w.type AS warehouse_type,
                    (s.qty * s.avg_cost) AS stock_value
             FROM {$pre}inv_stock s
             JOIN {$pre}inv_warehouses w ON w.id = s.warehouse_id
             WHERE s.product_id = %d
             ORDER BY w.name", $id
        ), ARRAY_A);
        $rows = acl_filter_rows_by_warehouse( $rows ?? [], acl_current_user_id(), 'warehouse_id' );
        return new WP_REST_Response(['data' => $rows]);
    }

    public static function get_wh_stock( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $deny = acl_deny_scope( acl_current_user_id(), 'warehouse', $id, 'inv.view' );
        if ( $deny ) return $deny;
        $pre  = $wpdb->prefix;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT s.*, p.code AS product_code, p.name AS product_name,
                    p.unit, p.min_stock, c.name AS category_name,
                    (s.qty * s.avg_cost) AS stock_value
             FROM {$pre}inv_stock s
             JOIN {$pre}inv_products p ON p.id = s.product_id
             LEFT JOIN {$pre}inv_categories c ON c.id = p.category_id
             WHERE s.warehouse_id = %d AND s.qty > 0
             ORDER BY c.name, p.name", $id
        ), ARRAY_A);
        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    public static function get_low_stock( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;
        $uid  = acl_current_user_id();
        $allowed = acl_get_allowed_scope_ids( $uid, 'warehouse' );
        $wh_join = '';
        $wh_cond = '';
        $args    = [];
        if ( $allowed !== null ) {
            if ( ! $allowed ) return new WP_REST_Response( ['data' => []] );
            $ph = implode( ',', array_fill( 0, count( $allowed ), '%d' ) );
            $wh_join = " INNER JOIN {$pre}inv_stock s ON s.product_id = p.id AND s.warehouse_id IN ({$ph}) ";
            $args    = $allowed;
        } else {
            $wh_join = " LEFT JOIN {$pre}inv_stock s ON s.product_id = p.id ";
        }
        $sql = "SELECT p.id, p.code, p.name, p.unit, p.min_stock, p.reorder_qty,
                        COALESCE(SUM(s.qty),0) AS total_qty
                 FROM {$pre}inv_products p
                 {$wh_join}
                 WHERE p.is_active = 1 AND p.min_stock > 0
                 GROUP BY p.id
                 HAVING total_qty <= p.min_stock
                 ORDER BY (total_qty / p.min_stock) ASC";
        $rows = $args
            ? $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A )
            : $wpdb->get_results( $sql, ARRAY_A );
        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    /* ══════════════════════════════════════════════════════════
       اسناد حرکت انبار
    ══════════════════════════════════════════════════════════ */
    public static function get_vouchers( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre    = $wpdb->prefix;
        $wheres = [];
        $args   = [];

        if ( $v = $req->get_param('type')   ) { $wheres[] = 'v.type = %s';     $args[] = sanitize_text_field($v); }
        if ( $v = $req->get_param('status') ) { $wheres[] = 'v.status = %s';   $args[] = sanitize_text_field($v); }
        if ( $v = $req->get_param('from')   ) { $wheres[] = 'v.date >= %s';    $args[] = sanitize_text_field($v); }
        if ( $v = $req->get_param('to')     ) { $wheres[] = 'v.date <= %s';    $args[] = sanitize_text_field($v); }

        $uid     = acl_current_user_id();
        $allowed = acl_get_allowed_scope_ids( $uid, 'warehouse' );
        if ( $allowed !== null ) {
            if ( ! $allowed ) {
                return new WP_REST_Response( ['data' => []] );
            }
            $ph = implode( ',', array_fill( 0, count( $allowed ), '%d' ) );
            $wheres[] = "v.id IN (
                SELECT DISTINCT vi.voucher_id FROM {$pre}inv_voucher_items vi
                WHERE vi.from_warehouse_id IN ({$ph}) OR vi.to_warehouse_id IN ({$ph})
            )";
            $args = array_merge( $args, $allowed, $allowed );
        }

        $where = $wheres ? 'WHERE ' . implode(' AND ', $wheres) : '';
        $sql   = "SELECT v.*, COUNT(i.id) AS item_count
                  FROM {$pre}inv_vouchers v
                  LEFT JOIN {$pre}inv_voucher_items i ON i.voucher_id = v.id
                  {$where}
                  GROUP BY v.id
                  ORDER BY v.date DESC, v.id DESC
                  LIMIT 200";

        $rows = $args ? $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A)
                      : $wpdb->get_results($sql, ARRAY_A);

        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    public static function get_voucher( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $id  = (int)$req->get_param('id');

        $voucher = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$pre}inv_vouchers WHERE id = %d", $id
        ), ARRAY_A);
        if ( ! $voucher ) return new WP_REST_Response(['message' => 'سند یافت نشد'], 404);

        $voucher['items'] = $wpdb->get_results($wpdb->prepare(
            "SELECT i.*,
                    p.name AS product_name, p.code AS product_code, p.unit,
                    fw.name AS from_warehouse_name,
                    tw.name AS to_warehouse_name,
                    b.batch_no
             FROM {$pre}inv_voucher_items i
             JOIN {$pre}inv_products p ON p.id = i.product_id
             LEFT JOIN {$pre}inv_warehouses fw ON fw.id = i.from_warehouse_id
             LEFT JOIN {$pre}inv_warehouses tw ON tw.id = i.to_warehouse_id
             LEFT JOIN {$pre}inv_batches    b  ON b.id  = i.batch_id
             WHERE i.voucher_id = %d
             ORDER BY i.id", $id
        ), ARRAY_A);

        $deny = acl_deny_warehouses_in_items( acl_current_user_id(), $voucher['items'] ?? [], 'inv.view' );
        if ( $deny ) return $deny;

        return new WP_REST_Response(['data' => $voucher]);
    }

    public static function create_voucher( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;

        $type  = sanitize_text_field($req->get_param('type') ?? '');
        $valid = ['purchase_receipt','sale_issue','return_in','return_out',
                  'waste','consignment_out','consignment_return',
                  'transfer','adjustment','project_issue'];
        if ( ! in_array($type, $valid) ) return new WP_REST_Response(['message' => 'نوع سند نامعتبر است'], 400);

        $items = $req->get_param('items');
        if ( ! is_array($items) || empty($items) ) return new WP_REST_Response(['message' => 'ردیف‌های سند الزامی است'], 400);

        $deny = acl_deny_warehouses_in_items( acl_current_user_id(), $items, 'inv.vouchers.create' );
        if ( $deny ) return $deny;

        // شماره سند خودکار
        $last_no = $wpdb->get_var($wpdb->prepare(
            "SELECT voucher_no FROM {$pre}inv_vouchers WHERE type=%s ORDER BY id DESC LIMIT 1", $type
        ));
        $prefix   = strtoupper(substr($type, 0, 3));
        $next_num = $last_no ? (intval(substr($last_no, -5)) + 1) : 1;
        $voucher_no = $prefix . '-' . str_pad($next_num, 5, '0', STR_PAD_LEFT);

        $wpdb->insert("{$pre}inv_vouchers", [
            'voucher_no'  => $voucher_no,
            'type'        => $type,
            'status'      => 'draft',
            'ref_id'      => $req->get_param('ref_id')   ? (int)$req->get_param('ref_id') : null,
            'ref_type'    => sanitize_text_field($req->get_param('ref_type') ?? ''),
            'ref_no'      => sanitize_text_field($req->get_param('ref_no')   ?? ''),
            'date'        => sanitize_text_field($req->get_param('date')     ?? date('Y-m-d')),
            'description' => sanitize_textarea_field($req->get_param('description') ?? ''),
            'created_by'  => sanitize_text_field($req->get_param('created_by') ?? ''),
        ]);
        $voucher_id = $wpdb->insert_id;

        foreach ( $items as $item ) {
            $qty        = (float)($item['qty'] ?? 0);
            $unit_price = (float)($item['unit_price'] ?? 0);
            if ( $qty <= 0 ) continue;

            $wpdb->insert("{$pre}inv_voucher_items", [
                'voucher_id'        => $voucher_id,
                'product_id'        => (int)($item['product_id'] ?? 0),
                'from_warehouse_id' => ! empty($item['from_warehouse_id']) ? (int)$item['from_warehouse_id'] : null,
                'to_warehouse_id'   => ! empty($item['to_warehouse_id'])   ? (int)$item['to_warehouse_id']   : null,
                'qty'               => $qty,
                'unit_price'        => $unit_price,
                'total_price'       => $qty * $unit_price,
                'batch_id'          => ! empty($item['batch_id']) ? (int)$item['batch_id'] : null,
                'notes'             => sanitize_text_field($item['notes'] ?? ''),
            ]);
        }

        return new WP_REST_Response(['id' => $voucher_id, 'voucher_no' => $voucher_no, 'success' => true], 201);
    }

    public static function update_voucher( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $id  = (int)$req->get_param('id');

        $v = $wpdb->get_row($wpdb->prepare("SELECT status FROM {$pre}inv_vouchers WHERE id=%d", $id), ARRAY_A);
        if ( ! $v ) return new WP_REST_Response(['message' => 'سند یافت نشد'], 404);
        if ( $v['status'] === 'confirmed' ) return new WP_REST_Response(['message' => 'سند تأیید شده قابل ویرایش نیست'], 409);

        $existing_items = $wpdb->get_results($wpdb->prepare(
            "SELECT from_warehouse_id, to_warehouse_id FROM {$pre}inv_voucher_items WHERE voucher_id=%d", $id
        ), ARRAY_A);
        $deny = acl_deny_warehouses_in_items( acl_current_user_id(), $existing_items ?: [], 'inv.vouchers.create' );
        if ( $deny ) return $deny;

        $data = [];
        foreach (['date','description','ref_no','created_by'] as $k) {
            $val = $req->get_param($k);
            if ( $val !== null ) $data[$k] = sanitize_textarea_field((string)$val);
        }
        if ( $data ) $wpdb->update("{$pre}inv_vouchers", $data, ['id' => $id]);

        // به‌روزرسانی ردیف‌ها
        $items = $req->get_param('items');
        if ( is_array($items) ) {
            $deny = acl_deny_warehouses_in_items( acl_current_user_id(), $items, 'inv.vouchers.create' );
            if ( $deny ) return $deny;
            $wpdb->delete("{$pre}inv_voucher_items", ['voucher_id' => $id]);
            foreach ( $items as $item ) {
                $qty = (float)($item['qty'] ?? 0);
                if ( $qty <= 0 ) continue;
                $unit_price = (float)($item['unit_price'] ?? 0);
                $wpdb->insert("{$pre}inv_voucher_items", [
                    'voucher_id'        => $id,
                    'product_id'        => (int)($item['product_id'] ?? 0),
                    'from_warehouse_id' => ! empty($item['from_warehouse_id']) ? (int)$item['from_warehouse_id'] : null,
                    'to_warehouse_id'   => ! empty($item['to_warehouse_id'])   ? (int)$item['to_warehouse_id']   : null,
                    'qty'               => $qty,
                    'unit_price'        => $unit_price,
                    'total_price'       => $qty * $unit_price,
                    'batch_id'          => ! empty($item['batch_id']) ? (int)$item['batch_id'] : null,
                    'notes'             => sanitize_text_field($item['notes'] ?? ''),
                ]);
            }
        }
        return new WP_REST_Response(['success' => true]);
    }

    public static function cancel_voucher( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $id  = (int)$req->get_param('id');
        $v   = $wpdb->get_row($wpdb->prepare("SELECT status FROM {$pre}inv_vouchers WHERE id=%d", $id), ARRAY_A);
        if ( ! $v ) return new WP_REST_Response(['message' => 'سند یافت نشد'], 404);
        if ( $v['status'] === 'confirmed' ) return new WP_REST_Response(['message' => 'برای لغو سند تأیید شده، ابتدا برگشت ثبت کنید'], 409);
        $wpdb->update("{$pre}inv_vouchers", ['status' => 'cancelled'], ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    /* ── تأیید سند → اعمال روی موجودی ─────────────────────── */
    public static function confirm_voucher( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $id  = (int)$req->get_param('id');

        $voucher = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$pre}inv_vouchers WHERE id=%d", $id), ARRAY_A);
        if ( ! $voucher ) return new WP_REST_Response(['message' => 'سند یافت نشد'], 404);
        if ( $voucher['status'] === 'confirmed' ) return new WP_REST_Response(['message' => 'سند قبلاً تأیید شده'], 409);
        if ( $voucher['status'] === 'cancelled' ) return new WP_REST_Response(['message' => 'سند لغو شده است'], 409);

        $items = $wpdb->get_results($wpdb->prepare(
            "SELECT i.*, p.costing_method, p.tracking
             FROM {$pre}inv_voucher_items i
             JOIN {$pre}inv_products p ON p.id = i.product_id
             WHERE i.voucher_id = %d", $id
        ), ARRAY_A);

        if ( empty($items) ) return new WP_REST_Response(['message' => 'سند ردیف ندارد'], 400);

        $deny = acl_deny_warehouses_in_items( acl_current_user_id(), $items, 'inv.vouchers.confirm' );
        if ( $deny ) return $deny;

        // اعتبارسنجی موجودی قبل از تأیید
        $errors = [];
        foreach ( $items as $item ) {
            $needs_out = ! empty($item['from_warehouse_id']);
            if ( $needs_out ) {
                $stock = $wpdb->get_var($wpdb->prepare(
                    "SELECT qty FROM {$pre}inv_stock WHERE product_id=%d AND warehouse_id=%d",
                    $item['product_id'], $item['from_warehouse_id']
                ));
                if ( (float)($stock ?? 0) < (float)$item['qty'] ) {
                    $pname = $wpdb->get_var($wpdb->prepare("SELECT name FROM {$pre}inv_products WHERE id=%d", $item['product_id']));
                    $errors[] = "موجودی «{$pname}» کافی نیست";
                }
            }
        }
        if ( $errors ) return new WP_REST_Response(['message' => implode(' | ', $errors)], 422);

        // اعمال روی موجودی
        foreach ( $items as $item ) {
            $pid        = (int)$item['product_id'];
            $qty        = (float)$item['qty'];
            $unit_price = (float)$item['unit_price'];
            $from_wh    = $item['from_warehouse_id'] ? (int)$item['from_warehouse_id'] : null;
            $to_wh      = $item['to_warehouse_id']   ? (int)$item['to_warehouse_id']   : null;

            // خروج از انبار مبدأ
            if ( $from_wh ) {
                self::_decrease_stock($pid, $from_wh, $qty, $item['costing_method'], (int)$item['id']);
                if ( $item['batch_id'] ) self::_decrease_batch_stock((int)$item['batch_id'], $from_wh, $qty);
            }

            // ورود به انبار مقصد
            if ( $to_wh ) {
                $cost = $unit_price > 0 ? $unit_price : self::_get_avg_cost($pid);
                self::_increase_stock($pid, $to_wh, $qty, $cost, $item['costing_method'], (int)$item['id']);
                if ( $item['batch_id'] ) self::_increase_batch_stock((int)$item['batch_id'], $to_wh, $qty);
            }

            // سریال نامبر
            if ( $item['tracking'] === 'serial' || $item['tracking'] === 'both' ) {
                $serial_nos = $req->get_param("serials_{$item['id']}") ?? [];
                foreach ( (array)$serial_nos as $sno ) {
                    if ( ! $sno ) continue;
                    if ( $from_wh ) {
                        $wpdb->update("{$pre}inv_serials", ['status' => 'issued', 'warehouse_id' => null, 'voucher_item_id' => (int)$item['id']], ['product_id' => $pid, 'serial_no' => $sno]);
                    }
                    if ( $to_wh ) {
                        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$pre}inv_serials WHERE product_id=%d AND serial_no=%s", $pid, $sno));
                        if ( ! $exists ) {
                            $wpdb->insert("{$pre}inv_serials", ['product_id' => $pid, 'serial_no' => $sno, 'status' => 'in_stock', 'warehouse_id' => $to_wh, 'batch_id' => $item['batch_id'] ?: null, 'voucher_item_id' => (int)$item['id']]);
                        } else {
                            $wpdb->update("{$pre}inv_serials", ['status' => 'in_stock', 'warehouse_id' => $to_wh], ['id' => $exists]);
                        }
                    }
                }
            }
        }

        $wpdb->update("{$pre}inv_vouchers", [
            'status'       => 'confirmed',
            'confirmed_by' => sanitize_text_field($req->get_param('confirmed_by') ?? ''),
            'confirmed_at' => current_time('mysql'),
        ], ['id' => $id]);

        return new WP_REST_Response(['success' => true, 'message' => 'سند با موفقیت تأیید شد']);
    }

    /* ══════════════════════════════════════════════════════════
       بچ
    ══════════════════════════════════════════════════════════ */
    public static function get_batches( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $pid = $req->get_param('product_id') ? (int)$req->get_param('product_id') : 0;
        $sql = "SELECT b.*, p.name AS product_name, p.unit,
                       COALESCE(SUM(bs.qty),0) AS total_qty
                FROM {$pre}inv_batches b
                JOIN {$pre}inv_products p ON p.id = b.product_id
                LEFT JOIN {$pre}inv_batch_stock bs ON bs.batch_id = b.id" .
               ($pid ? $wpdb->prepare(" WHERE b.product_id = %d", $pid) : '') .
               " GROUP BY b.id ORDER BY b.exp_date ASC, b.id DESC";
        return new WP_REST_Response(['data' => $wpdb->get_results($sql, ARRAY_A) ?? []]);
    }

    public static function create_batch( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $wpdb->insert($wpdb->prefix.'inv_batches', [
            'product_id' => (int)($req->get_param('product_id') ?? 0),
            'batch_no'   => sanitize_text_field($req->get_param('batch_no')  ?? ''),
            'mfg_date'   => sanitize_text_field($req->get_param('mfg_date')  ?? '') ?: null,
            'exp_date'   => sanitize_text_field($req->get_param('exp_date')  ?? '') ?: null,
            'cost_price' => (float)($req->get_param('cost_price') ?? 0),
            'notes'      => sanitize_text_field($req->get_param('notes')     ?? ''),
        ]);
        return new WP_REST_Response(['id' => $wpdb->insert_id, 'success' => true], 201);
    }

    public static function update_batch( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $data = [];
        foreach (['batch_no','mfg_date','exp_date','notes'] as $k) {
            $v = $req->get_param($k);
            if ( $v !== null ) $data[$k] = sanitize_text_field((string)$v) ?: null;
        }
        if ( $req->get_param('cost_price') !== null ) $data['cost_price'] = (float)$req->get_param('cost_price');
        if ( $data ) $wpdb->update($wpdb->prefix.'inv_batches', $data, ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    public static function delete_batch( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id  = (int)$req->get_param('id');
        $qty = $wpdb->get_var($wpdb->prepare("SELECT COALESCE(SUM(qty),0) FROM {$wpdb->prefix}inv_batch_stock WHERE batch_id=%d", $id));
        if ( $qty > 0 ) return new WP_REST_Response(['message' => 'بچ دارای موجودی است'], 409);
        $wpdb->delete($wpdb->prefix.'inv_batches', ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    /* ══════════════════════════════════════════════════════════
       سریال نامبر
    ══════════════════════════════════════════════════════════ */
    public static function get_serials( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre    = $wpdb->prefix;
        $wheres = [];
        $args   = [];
        if ( $v = $req->get_param('product_id') )   { $wheres[] = 's.product_id = %d';   $args[] = (int)$v; }
        if ( $v = $req->get_param('warehouse_id') )  { $wheres[] = 's.warehouse_id = %d'; $args[] = (int)$v; }
        $uid = acl_current_user_id();
        if ( ! acl_append_scope_where( $uid, 'warehouse', 's.warehouse_id', $wheres, $args ) ) {
            return new WP_REST_Response( ['data' => []] );
        }
        if ( $v = $req->get_param('status') )         { $wheres[] = 's.status = %s';       $args[] = sanitize_text_field($v); }
        if ( $v = $req->get_param('search') )         { $wheres[] = 's.serial_no LIKE %s'; $args[] = "%{$v}%"; }
        $where = $wheres ? 'WHERE ' . implode(' AND ', $wheres) : '';
        $sql   = "SELECT s.*, p.name AS product_name, p.code AS product_code,
                         w.name AS warehouse_name, b.batch_no
                  FROM {$pre}inv_serials s
                  JOIN {$pre}inv_products p ON p.id = s.product_id
                  LEFT JOIN {$pre}inv_warehouses w ON w.id = s.warehouse_id
                  LEFT JOIN {$pre}inv_batches    b ON b.id  = s.batch_id
                  {$where}
                  ORDER BY s.updated_at DESC LIMIT 500";
        $rows = $args ? $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A)
                      : $wpdb->get_results($sql, ARRAY_A);
        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    public static function update_serial( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $data = [];
        $valid_status = ['in_stock','issued','returned','scrapped','lost'];
        if ( $v = $req->get_param('status') ) {
            if ( in_array($v, $valid_status) ) $data['status'] = $v;
        }
        if ( $req->get_param('issued_to') !== null ) $data['issued_to'] = sanitize_text_field($req->get_param('issued_to'));
        if ( $req->get_param('notes')     !== null ) $data['notes']     = sanitize_text_field($req->get_param('notes'));
        if ( $data ) $wpdb->update($wpdb->prefix.'inv_serials', $data, ['id' => $id]);
        return new WP_REST_Response(['success' => true]);
    }

    /* ══════════════════════════════════════════════════════════
       گزارش‌ها
    ══════════════════════════════════════════════════════════ */

    /** کاردکس محصول */
    public static function report_card( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $pid = (int)($req->get_param('product_id') ?? 0);
        $wid = $req->get_param('warehouse_id') ? (int)$req->get_param('warehouse_id') : null;
        if ( ! $pid ) return new WP_REST_Response(['message' => 'product_id الزامی است'], 400);

        $uid = acl_current_user_id();
        if ( $wid ) {
            $deny = acl_deny_scope( $uid, 'warehouse', $wid, 'inv.view' );
            if ( $deny ) return $deny;
        } elseif ( ! acl_is_scope_unrestricted( $uid, 'warehouse' ) ) {
            return new WP_REST_Response( ['message' => 'warehouse_id الزامی است'], 400 );
        }

        $wh_cond = $wid ? $wpdb->prepare(" AND (i.from_warehouse_id = %d OR i.to_warehouse_id = %d)", $wid, $wid) : '';

        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT v.voucher_no, v.type, v.date, v.description,
                    i.qty, i.unit_price, i.total_price,
                    i.from_warehouse_id, i.to_warehouse_id,
                    fw.name AS from_wh, tw.name AS to_wh,
                    b.batch_no
             FROM {$pre}inv_voucher_items i
             JOIN {$pre}inv_vouchers v ON v.id = i.voucher_id AND v.status = 'confirmed'
             LEFT JOIN {$pre}inv_warehouses fw ON fw.id = i.from_warehouse_id
             LEFT JOIN {$pre}inv_warehouses tw ON tw.id = i.to_warehouse_id
             LEFT JOIN {$pre}inv_batches    b  ON b.id  = i.batch_id
             WHERE i.product_id = %d {$wh_cond}
             ORDER BY v.date ASC, v.id ASC",
            $pid
        ), ARRAY_A);

        // محاسبه موجودی تجمعی
        $running = 0;
        foreach ( $rows as &$row ) {
            $is_in  = ! empty($row['to_warehouse_id']) && ( ! $wid || (int)$row['to_warehouse_id'] === $wid );
            $is_out = ! empty($row['from_warehouse_id']) && ( ! $wid || (int)$row['from_warehouse_id'] === $wid );
            $row['in_qty']  = $is_in  ? (float)$row['qty'] : 0;
            $row['out_qty'] = $is_out ? (float)$row['qty'] : 0;
            $running += $row['in_qty'] - $row['out_qty'];
            $row['balance'] = $running;
        }
        unset($row);

        if ( ! $wid ) {
            $rows = array_values( array_filter( $rows, function( $row ) use ( $uid ) {
                foreach ( ['from_warehouse_id', 'to_warehouse_id'] as $k ) {
                    if ( empty( $row[ $k ] ) ) continue;
                    if ( acl_deny_scope( $uid, 'warehouse', (int) $row[ $k ], '' ) ) return false;
                }
                return true;
            } ) );
        }

        return new WP_REST_Response(['data' => $rows, 'final_balance' => $running]);
    }

    /** ارزش‌گذاری انبار */
    public static function report_valuation( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;
        $uid  = acl_current_user_id();
        $wheres = ['s.qty > 0'];
        $args   = [];
        if ( ! acl_append_scope_where( $uid, 'warehouse', 's.warehouse_id', $wheres, $args ) ) {
            return new WP_REST_Response( ['data' => [], 'total_value' => 0] );
        }
        $where = 'WHERE ' . implode( ' AND ', $wheres );
        $sql   = "SELECT s.product_id, s.warehouse_id, s.qty, s.avg_cost,
                    (s.qty * s.avg_cost) AS value,
                    p.code, p.name, p.unit, p.costing_method,
                    w.name AS warehouse_name, w.type AS warehouse_type
             FROM {$pre}inv_stock s
             JOIN {$pre}inv_products  p ON p.id = s.product_id
             JOIN {$pre}inv_warehouses w ON w.id = s.warehouse_id
             {$where}
             ORDER BY w.name, p.name";
        $rows  = $args ? $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A )
                       : $wpdb->get_results( $sql, ARRAY_A );
        $total = array_sum( array_column( $rows ?? [], 'value' ) );
        return new WP_REST_Response( ['data' => $rows ?? [], 'total_value' => $total] );
    }

    /** بچ‌های در حال انقضا */
    public static function report_expiring( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;
        $days = (int)($req->get_param('days') ?? 30);
        $uid  = acl_current_user_id();
        $allowed = acl_get_allowed_scope_ids( $uid, 'warehouse' );
        $wh_cond = '';
        $args    = [ $days ];
        if ( $allowed !== null ) {
            if ( ! $allowed ) return new WP_REST_Response( ['data' => []] );
            $ph = implode( ',', array_fill( 0, count( $allowed ), '%d' ) );
            $wh_cond = " AND bs.warehouse_id IN ({$ph}) ";
            $args    = array_merge( $args, $allowed );
        }
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT b.*, p.name AS product_name, p.unit,
                    COALESCE(SUM(bs.qty),0) AS total_qty,
                    DATEDIFF(b.exp_date, CURDATE()) AS days_left
             FROM {$pre}inv_batches b
             JOIN {$pre}inv_products p ON p.id = b.product_id
             LEFT JOIN {$pre}inv_batch_stock bs ON bs.batch_id = b.id
             WHERE b.exp_date IS NOT NULL
               AND b.exp_date <= DATE_ADD(CURDATE(), INTERVAL %d DAY)
               AND b.exp_date >= CURDATE()
               {$wh_cond}
             GROUP BY b.id
             HAVING total_qty > 0
             ORDER BY b.exp_date ASC", ...$args
        ), ARRAY_A);
        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    /* ══════════════════════════════════════════════════════════
       توابع کمکی موجودی
    ══════════════════════════════════════════════════════════ */

    /** افزایش موجودی + به‌روزرسانی بهای تمام شده */
    private static function _increase_stock( int $pid, int $wid, float $qty, float $cost, string $method, int $item_id ): void {
        global $wpdb;
        $pre = $wpdb->prefix;

        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT qty, avg_cost FROM {$pre}inv_stock WHERE product_id=%d AND warehouse_id=%d", $pid, $wid
        ), ARRAY_A);

        if ( $row ) {
            if ( $method === 'avg' ) {
                $new_qty  = (float)$row['qty'] + $qty;
                $new_cost = $new_qty > 0
                    ? ( (float)$row['qty'] * (float)$row['avg_cost'] + $qty * $cost ) / $new_qty
                    : $cost;
                $wpdb->update("{$pre}inv_stock", ['qty' => $new_qty, 'avg_cost' => $new_cost], ['product_id' => $pid, 'warehouse_id' => $wid]);
            } else {
                // FIFO — فقط qty بالا می‌رود
                $wpdb->update("{$pre}inv_stock", ['qty' => (float)$row['qty'] + $qty], ['product_id' => $pid, 'warehouse_id' => $wid]);
            }
        } else {
            $wpdb->insert("{$pre}inv_stock", ['product_id' => $pid, 'warehouse_id' => $wid, 'qty' => $qty, 'avg_cost' => $cost]);
        }

        // لایه FIFO
        if ( $method === 'fifo' ) {
            $wpdb->insert("{$pre}inv_fifo_layers", [
                'product_id'      => $pid,
                'warehouse_id'    => $wid,
                'voucher_item_id' => $item_id,
                'qty_original'    => $qty,
                'qty_remaining'   => $qty,
                'unit_cost'       => $cost,
            ]);
        }
    }

    /** کاهش موجودی */
    private static function _decrease_stock( int $pid, int $wid, float $qty, string $method, int $item_id ): void {
        global $wpdb;
        $pre = $wpdb->prefix;

        $wpdb->query($wpdb->prepare(
            "UPDATE {$pre}inv_stock SET qty = qty - %f WHERE product_id=%d AND warehouse_id=%d",
            $qty, $pid, $wid
        ));

        // مصرف لایه‌های FIFO
        if ( $method === 'fifo' ) {
            $remaining = $qty;
            $layers = $wpdb->get_results($wpdb->prepare(
                "SELECT id, qty_remaining FROM {$pre}inv_fifo_layers
                 WHERE product_id=%d AND warehouse_id=%d AND qty_remaining > 0
                 ORDER BY id ASC", $pid, $wid
            ), ARRAY_A);

            foreach ( $layers as $layer ) {
                if ( $remaining <= 0 ) break;
                $consume = min($remaining, (float)$layer['qty_remaining']);
                $wpdb->update("{$pre}inv_fifo_layers",
                    ['qty_remaining' => (float)$layer['qty_remaining'] - $consume],
                    ['id' => $layer['id']]
                );
                $remaining -= $consume;
            }
        }
    }

    private static function _increase_batch_stock( int $bid, int $wid, float $qty ): void {
        global $wpdb;
        $pre = $wpdb->prefix;
        $row = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$pre}inv_batch_stock WHERE batch_id=%d AND warehouse_id=%d", $bid, $wid));
        if ( $row ) $wpdb->query($wpdb->prepare("UPDATE {$pre}inv_batch_stock SET qty=qty+%f WHERE batch_id=%d AND warehouse_id=%d", $qty, $bid, $wid));
        else $wpdb->insert("{$pre}inv_batch_stock", ['batch_id' => $bid, 'warehouse_id' => $wid, 'qty' => $qty]);
    }

    private static function _decrease_batch_stock( int $bid, int $wid, float $qty ): void {
        global $wpdb;
        $wpdb->query($wpdb->prepare(
            "UPDATE {$wpdb->prefix}inv_batch_stock SET qty=qty-%f WHERE batch_id=%d AND warehouse_id=%d",
            $qty, $bid, $wid
        ));
    }

    private static function _get_avg_cost( int $pid ): float {
        global $wpdb;
        $cost = $wpdb->get_var($wpdb->prepare(
            "SELECT avg_cost FROM {$wpdb->prefix}inv_stock WHERE product_id=%d AND qty>0 ORDER BY qty DESC LIMIT 1", $pid
        ));
        return (float)($cost ?? 0);
    }
}
