<?php

declare(strict_types=1);

namespace App\Modules\Reporting\Presentation\Controllers;

use App\Core\Auth;
use App\Core\Request;
use App\Core\Response;
use App\Modules\Reporting\Application\Services\ReportFileExportService;
use App\Modules\Reporting\Application\Support\ReportCatalog;
use PDO;
use Throwable;

final class GenericReportController
{
    public function __construct(private ?PDO $pdo = null, private ?ReportFileExportService $exporter = null)
    {
        $this->pdo ??= db();
        $this->exporter ??= new ReportFileExportService();
    }

    public function show(string $slug): void
    {
        Auth::requireLogin();
        $report = $this->authorize($slug);
        $payload = $this->payload($report, false);
        Response::view('pages/reports/generic', $payload + [
            'page_title' => (string)$report['title'],
            'hide_page_header' => true,
            'breadcrumbs' => [
                ['label'=>'لوحة التحكم','url'=>'/admin/dashboard'],
                ['label'=>'مركز التقارير','url'=>'/reports'],
                ['label'=>(string)$report['title']],
            ],
        ]);
    }

    public function print(string $slug): void
    {
        Auth::requireLogin();
        $report = $this->authorize($slug);
        $format = strtolower(trim((string)Request::get('format', 'a4')));
        if ($format !== 'a4') { $format = 'a4'; }
        $payload = $this->payload($report, true);
        print_view('report', 'generic_report', $payload + [
            'title'=>(string)$report['title'],
            'meta'=>['date'=>date('Y-m-d')],
            'company'=>company_profile($this->pdo),
            'footer'=>['note' => 'هذا التقرير صادر من مركز التقارير ويطابق نسخة الطباعة ونسخة PDF.'],
            'print_format'=>'a4',
            'print_options'=>['report_mode' => true, 'show_signatures' => false],
            'report_mode'=>true,
            'userName'=>current_user_name(),
            'reportDate'=>date('Y-m-d'),
        ]);
    }

    public function pdf(string $slug): void
    {
        Auth::requireLogin();
        $report = $this->authorize($slug);
        $payload = $this->payload($report, true);
        ob_start();
        print_view('report', 'generic_report', $payload + [
            'title'=>(string)$report['title'],
            'meta'=>['date'=>date('Y-m-d')],
            'company'=>company_profile($this->pdo),
            'footer'=>['note' => 'هذا التقرير صادر من مركز التقارير ويطابق نسخة الطباعة ونسخة PDF.'],
            'print_format'=>'a4',
            'print_options'=>['report_mode' => true, 'show_signatures' => false],
            'report_mode'=>true,
            'userName'=>current_user_name(),
            'reportDate'=>date('Y-m-d'),
        ]);
        $html = (string)ob_get_clean();
        $this->exporter->sendPdfFromHtml($html, (string)$report['slug']);
    }

    public function excel(string $slug): void
    {
        Auth::requireLogin();
        $report = $this->authorize($slug);
        $payload = $this->payload($report, true);
        $summary = [];
        foreach (($payload['summary'] ?? []) as $k => $v) { $summary[(string)$k] = (string)$v; }
        $summary['تاريخ التصدير'] = date('Y-m-d H:i');
        $summary['الفلاتر'] = $this->filtersText($payload['filters'] ?? []);
        $rows = [];
        foreach (($payload['rows'] ?? []) as $row) {
            $line = [];
            foreach (($payload['columns'] ?? []) as $key => $label) { $line[] = $row[$key] ?? ''; }
            $rows[] = $line;
        }
        $this->exporter->sendExcel((string)$report['slug'], (string)$report['title'], $summary, array_values($payload['columns'] ?? []), $rows);
    }



    public function lookup(): void
    {
        Auth::requireLogin();
        header('Content-Type: application/json; charset=utf-8');
        $type = trim((string)Request::get('type', ''));
        $q = trim((string)Request::get('q', ''));
        if (mb_strlen($q) < 2) {
            echo json_encode([], JSON_UNESCAPED_UNICODE);
            return;
        }
        $like = '%' . $q . '%';
        try {
            if ($type === 'party') {
                $stmt = $this->pdo->prepare("SELECT id, CONCAT(COALESCE(NULLIF(code,''), id), ' - ', name) AS text FROM parties WHERE name LIKE :q OR code LIKE :q OR phone LIKE :q ORDER BY name LIMIT 20");
            } elseif ($type === 'customer') {
                $stmt = $this->pdo->prepare("SELECT id, CONCAT(COALESCE(NULLIF(code,''), id), ' - ', name) AS text FROM parties WHERE COALESCE(is_customer,0)=1 AND (name LIKE :q OR code LIKE :q OR phone LIKE :q) ORDER BY name LIMIT 20");
            } elseif ($type === 'supplier') {
                $stmt = $this->pdo->prepare("SELECT id, CONCAT(COALESCE(NULLIF(code,''), id), ' - ', name) AS text FROM parties WHERE COALESCE(is_supplier,0)=1 AND (name LIKE :q OR code LIKE :q OR phone LIKE :q) ORDER BY name LIMIT 20");
            } elseif ($type === 'item') {
                $stmt = $this->pdo->prepare("SELECT id, CONCAT(COALESCE(NULLIF(barcode,''), id), ' - ', name) AS text FROM items WHERE name LIKE :q OR barcode LIKE :q ORDER BY name LIMIT 20");
            } elseif ($type === 'warehouse') {
                $stmt = $this->pdo->prepare("SELECT id, name AS text FROM warehouses WHERE name LIKE :q ORDER BY name LIMIT 20");
            } elseif ($type === 'cashbox') {
                $stmt = $this->pdo->prepare("SELECT id, name AS text FROM cashboxes WHERE COALESCE(is_active,1)=1 AND name LIKE :q ORDER BY name LIMIT 20");
            } elseif ($type === 'bank_account') {
                $stmt = $this->pdo->prepare("SELECT id, CONCAT(name, COALESCE(CONCAT(' - ', bank_name), '')) AS text FROM bank_accounts WHERE COALESCE(is_active,1)=1 AND (name LIKE :q OR bank_name LIKE :q OR account_no LIKE :q OR iban LIKE :q) ORDER BY name LIMIT 20");
            } elseif ($type === 'user') {
                $stmt = $this->pdo->prepare("SELECT id, COALESCE(full_name, username) AS text FROM users WHERE full_name LIKE :q OR username LIKE :q ORDER BY username LIMIT 20");
            } else {
                echo json_encode([], JSON_UNESCAPED_UNICODE);
                return;
            }
            $stmt->execute([':q' => $like]);
            echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [], JSON_UNESCAPED_UNICODE);
        } catch (Throwable $e) {
            echo json_encode([], JSON_UNESCAPED_UNICODE);
        }
    }

    private function authorize(string $slug): array
    {
        $report = ReportCatalog::find($slug);
        if ($report === null) { http_response_code(404); echo '404 Not Found'; exit; }
        $permission = (string)($report['permission'] ?? '');
        if ($permission !== '') { require_permission($permission); }
        return $report;
    }

    private function payload(array $report, bool $forExport): array
    {
        $filters = [
            'from' => trim((string)Request::get('from', date('Y-m-01'))),
            'to' => trim((string)Request::get('to', date('Y-m-d'))),
            'q' => trim((string)Request::get('q', '')),
            'party_id' => max(0, (int)Request::get('party_id', 0)),
            'item_id' => max(0, (int)Request::get('item_id', 0)),
            'warehouse_id' => max(0, (int)Request::get('warehouse_id', 0)),
            'cashbox_id' => max(0, (int)Request::get('cashbox_id', 0)),
            'bank_account_id' => max(0, (int)Request::get('bank_account_id', 0)),
            'user_id' => max(0, (int)Request::get('user_id', 0)),
            'status' => trim((string)Request::get('status', '')),
            'payment_mode' => trim((string)Request::get('payment_mode', '')),
            'amount_min' => trim((string)Request::get('amount_min', '')),
            'amount_max' => trim((string)Request::get('amount_max', '')),
            'diff_min' => trim((string)Request::get('diff_min', '')),
            'non_zero' => (int)Request::get('non_zero', 0),
            'negative_only' => (int)Request::get('negative_only', 0),
            'below_reorder' => (int)Request::get('below_reorder', 0),
            'inactive_days' => trim((string)Request::get('inactive_days', '')),
            'movement_type' => trim((string)Request::get('movement_type', '')),
            'movement_sort' => trim((string)Request::get('movement_sort', 'total')),
            'show_cost' => (int)Request::get('show_cost', 0),
            'margin_max' => trim((string)Request::get('margin_max', '')),
            'value_min' => trim((string)Request::get('value_min', '')),
            'value_max' => trim((string)Request::get('value_max', '')),
        ];
        $perPage = in_array((int)Request::get('per_page', 25), [25,50,100], true) ? (int)Request::get('per_page',25) : 25;
        $page = max(1, (int)Request::get('page',1));
        $limit = $forExport ? 5000 : $perPage;
        $offset = $forExport ? 0 : (($page-1)*$perPage);
        $dataset = $this->dataset((string)$report['slug'], (string)$report['group'], $filters, $limit, $offset);
        $totalPages = max(1, (int)ceil(($dataset['total_rows'] ?? 0) / $perPage));
        return [
            'report'=>$report,
            'filters'=>$filters,
            'lookups'=>$this->lookups(),
            'columns'=>$dataset['columns'],
            'rows'=>$dataset['rows'],
            'summary'=>$dataset['summary'],
            'page'=>$page,
            'per_page'=>$perPage,
            'total_pages'=>$totalPages,
            'total_rows'=>(int)$dataset['total_rows'],
            'from_row'=>((int)$dataset['total_rows'] > 0 ? $offset+1 : 0),
            'to_row'=>min($offset+$perPage, (int)$dataset['total_rows']),
            'export_limit'=>5000,
            'filter_config'=>$this->filterConfig((string)$report['slug']),
        ];
    }

    private function dataset(string $slug, string $group, array $f, int $limit, int $offset): array
    {
        $analyticsReport = $this->analyticsReport($slug, $group, $f, $limit, $offset);
        if ($analyticsReport !== null) { return $analyticsReport; }
        $salesReport = $this->salesReport($slug, $f, $limit, $offset);
        if ($salesReport !== null) { return $salesReport; }
        $financeReport = $this->financeReport($slug, $group, $f, $limit, $offset);
        if ($financeReport !== null) { return $financeReport; }
        $purchaseReport = $this->purchaseReport($slug, $f, $limit, $offset);
        if ($purchaseReport !== null) { return $purchaseReport; }
        $partyReport = $this->partyReport($slug, $f, $limit, $offset);
        if ($partyReport !== null) { return $partyReport; }
        if (str_contains($slug, 'return') && str_contains($slug, 'sales')) { return $this->salesReturns($f, $limit, $offset); }
        if (str_contains($slug, 'return') && str_contains($slug, 'purchase')) { return $this->purchaseReturns($f, $limit, $offset); }
        if (str_starts_with($slug, 'sales') || in_array($slug, ['top-customers','customer-activity','customer-profit','invoice-profit','sales-tax','discounts','daily-management-summary','monthly-summary','kpi','period-comparison','sales-growth','net-sales-after-returns'], true)) {
            return $this->salesInvoices($f, $limit, $offset, $slug);
        }
        if (str_contains($slug, 'purchase') || str_contains($slug, 'supplier') || $group === 'المشتريات') {
            return $this->purchaseInvoices($f, $limit, $offset, $slug);
        }
        if ($group === 'المخزون' || str_contains($slug, 'stock') || str_contains($slug, 'item') || in_array($slug, ['inactive-items','most-moving-items','reorder-items','negative-items'], true)) {
            $stockReport = $this->stockReport($slug, $f, $limit, $offset);
            if ($stockReport !== null) { return $stockReport; }
            return $this->stock($f, $limit, $offset);
        }
        if (str_contains($slug, 'expense') || $group === 'المصروفات') { return $this->expensesFallback($f, $limit, $offset); }
        if (in_array($slug, ['user-activity','audit-log','sensitive-actions','failed-logins','user-permissions','price-changes','stock-changes'], true)) { return $this->auditReport($slug, $f, $limit, $offset); }
        return $this->salesInvoices($f, $limit, $offset, $slug);
    }

    private function salesInvoices(array $f, int $limit, int $offset, string $slug): array
    {
        $where = ['1=1']; $p=[];
        $this->dateWhere($where,$p,'si.invoice_date',$f);
        $this->eqWhere($where,$p,'si.party_id','party_id',$f); $this->eqWhere($where,$p,'si.warehouse_id','warehouse_id',$f); $this->eqWhere($where,$p,'si.user_id','user_id',$f);
        if ($f['status'] !== '') { $where[]='si.status=:status'; $p[':status']=$f['status']; }
        if ($f['payment_mode'] !== '') { $where[]='si.payment_mode=:pm'; $p[':pm']=$f['payment_mode']; }
        $this->amountWhere($where,$p,'si.grand_total',$f);
        if ($f['q'] !== '') { $where[]='(si.invoice_no LIKE :q OR p.name LIKE :q OR si.notes LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        if ($slug === 'sales-unpaid') { $where[]='si.due_amount > 0'; }
        if ($slug === 'sales-discounts') { $where[]='si.discount_amount > 0'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT si.invoice_date AS doc_date, si.invoice_no AS doc_no, COALESCE(p.name,'عميل نقدي') AS party, COALESCE(w.name,'-') AS warehouse, COALESCE(u.full_name,u.username,'-') AS user_name, si.payment_mode, si.status, si.subtotal, si.discount_amount, si.vat_amount, si.grand_total, si.paid_amount, si.due_amount FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id LEFT JOIN warehouses w ON w.id=si.warehouse_id LEFT JOIN users u ON u.id=si.user_id WHERE $w ORDER BY si.invoice_date DESC, si.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(si.grand_total),0) total, COALESCE(SUM(si.paid_amount),0) paid, COALESCE(SUM(si.due_amount),0) due, COALESCE(SUM(si.discount_amount),0) discount FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w",
            $p,
            ['doc_date'=>'التاريخ','doc_no'=>'رقم الفاتورة','party'=>'العميل','payment_mode'=>'الدفع','grand_total'=>'الإجمالي','due_amount'=>'المتبقي'],
            ['عدد الفواتير'=>'c','إجمالي المبيعات'=>'total','المدفوع'=>'paid','المتبقي'=>'due','إجمالي الخصم'=>'discount']
        );
    }


    private function salesReport(string $slug, array $f, int $limit, int $offset): ?array
    {
        return match ($slug) {
            'sales-customers' => $this->salesByCustomers($f, $limit, $offset),
            'sales-items' => $this->salesByItems($f, $limit, $offset),
            'sales-users' => $this->salesByUsers($f, $limit, $offset),
            'sales-daily' => $this->salesDaily($f, $limit, $offset),
            'sales-warehouses' => $this->salesByWarehouses($f, $limit, $offset),
            'sales-payment-methods' => $this->salesByPaymentMethods($f, $limit, $offset),
            'sales-unpaid' => $this->salesInvoices($f, $limit, $offset, $slug),
            'sales-changes' => $this->salesChanges($f, $limit, $offset),
            'sales-discounts' => $this->salesDiscounts($f, $limit, $offset),
            'sales-returns-period' => $this->salesReturns($f, $limit, $offset),
            'sales-return-reasons' => $this->salesReturnReasons($f, $limit, $offset),
            'most-returned-sales-items' => $this->mostReturnedSalesItems($f, $limit, $offset),
            'customer-returns' => $this->customerReturns($f, $limit, $offset),
            'net-sales-after-returns' => $this->netSalesAfterReturns($f, $limit, $offset),
            default => null,
        };
    }

    private function salesBaseWhere(array $f, string $alias = 'si'): array
    {
        $where = ['1=1']; $p=[];
        $this->dateWhere($where,$p,$alias.'.invoice_date',$f);
        $this->eqWhere($where,$p,$alias.'.party_id','party_id',$f);
        $this->eqWhere($where,$p,$alias.'.warehouse_id','warehouse_id',$f);
        $this->eqWhere($where,$p,$alias.'.user_id','user_id',$f);
        if ($f['status'] !== '') { $where[]=$alias.'.status=:status'; $p[':status']=$f['status']; }
        if ($f['payment_mode'] !== '') { $where[]=$alias.'.payment_mode=:pm'; $p[':pm']=$f['payment_mode']; }
        $this->amountWhere($where,$p,$alias.'.grand_total',$f);
        return [$where,$p];
    }

    private function salesByCustomers(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->salesBaseWhere($f);
        if ($f['q'] !== '') { $where[]='(p.name LIKE :q OR p.code LIKE :q OR p.phone LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(p.code,'') AS party_code, COALESCE(p.name,'عميل نقدي') AS party, COUNT(si.id) AS invoices_count, COALESCE(SUM(si.grand_total),0) AS sales_total, COALESCE(SUM(si.paid_amount),0) AS paid_total, COALESCE(SUM(si.due_amount),0) AS due_total, COALESCE(SUM(si.discount_amount),0) AS discount_total, MAX(si.invoice_date) AS last_invoice_date FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id, p.code, p.name ORDER BY sales_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) total, COALESCE(SUM(x.paid_total),0) paid, COALESCE(SUM(x.due_total),0) due FROM (SELECT si.party_id, SUM(si.grand_total) sales_total, SUM(si.paid_amount) paid_total, SUM(si.due_amount) due_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id) x",
            $p,
            ['party'=>'العميل','invoices_count'=>'عدد الفواتير','sales_total'=>'إجمالي المبيعات','due_total'=>'المتبقي','last_invoice_date'=>'آخر تعامل'],
            ['عدد العملاء'=>'c','إجمالي المبيعات'=>'total','المدفوع'=>'paid','المتبقي'=>'due']
        );
    }

    private function salesByItems(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->salesBaseWhere($f);
        $this->eqWhere($where,$p,'sii.item_id','item_id',$f);
        if ($f['q'] !== '') { $where[]='(i.name LIKE :q OR i.barcode LIKE :q OR sii.item_name LIKE :q OR p.name LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(i.barcode,'') AS barcode, COALESCE(i.name,sii.item_name,'-') AS item_name, COALESCE(c.name,'-') AS category, COUNT(DISTINCT si.id) AS invoices_count, COALESCE(SUM(sii.qty),0) AS qty_total, COALESCE(SUM(sii.line_total),0) AS sales_total, COALESCE(SUM(sii.qty*sii.cost),0) AS cost_total, COALESCE(SUM(sii.line_total-(sii.qty*sii.cost)),0) AS profit_total FROM sales_items sii JOIN sales_invoices si ON si.id=sii.invoice_id LEFT JOIN items i ON i.id=sii.item_id LEFT JOIN categories c ON c.id=i.category_id LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY sii.item_id, i.barcode, i.name, sii.item_name, c.name ORDER BY sales_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.qty_total),0) qty, COALESCE(SUM(x.sales_total),0) total, COALESCE(SUM(x.profit_total),0) profit FROM (SELECT sii.item_id, SUM(sii.qty) qty_total, SUM(sii.line_total) sales_total, SUM(sii.line_total-(sii.qty*sii.cost)) profit_total FROM sales_items sii JOIN sales_invoices si ON si.id=sii.invoice_id LEFT JOIN items i ON i.id=sii.item_id LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY sii.item_id) x",
            $p,
            ['item_name'=>'الصنف','qty_total'=>'الكمية','sales_total'=>'قيمة المبيعات','profit_total'=>'الربح'],
            ['عدد الأصناف'=>'c','إجمالي الكمية'=>'qty','إجمالي المبيعات'=>'total','إجمالي الربح'=>'profit']
        );
    }

    private function salesByUsers(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->salesBaseWhere($f);
        if ($f['q'] !== '') { $where[]='(u.full_name LIKE :q OR u.username LIKE :q OR p.name LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(u.full_name,u.username,'-') AS user_name, COUNT(si.id) AS invoices_count, COALESCE(SUM(si.grand_total),0) AS sales_total, COALESCE(SUM(si.paid_amount),0) AS paid_total, COALESCE(SUM(si.due_amount),0) AS due_total, COALESCE(SUM(si.discount_amount),0) AS discount_total FROM sales_invoices si LEFT JOIN users u ON u.id=si.user_id LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.user_id, u.full_name, u.username ORDER BY sales_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) total, COALESCE(SUM(x.paid_total),0) paid, COALESCE(SUM(x.due_total),0) due FROM (SELECT si.user_id, SUM(si.grand_total) sales_total, SUM(si.paid_amount) paid_total, SUM(si.due_amount) due_total FROM sales_invoices si LEFT JOIN users u ON u.id=si.user_id LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.user_id) x",
            $p,
            ['user_name'=>'المستخدم','invoices_count'=>'عدد الفواتير','sales_total'=>'إجمالي المبيعات','due_total'=>'المتبقي'],
            ['عدد المستخدمين'=>'c','إجمالي المبيعات'=>'total','المدفوع'=>'paid','المتبقي'=>'due']
        );
    }

    private function salesDaily(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->salesBaseWhere($f);
        if ($f['q'] !== '') { $where[]='(si.invoice_no LIKE :q OR p.name LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT DATE(si.invoice_date) AS sales_day, COUNT(si.id) AS invoices_count, COALESCE(SUM(si.grand_total),0) AS sales_total, COALESCE(SUM(si.paid_amount),0) AS paid_total, COALESCE(SUM(si.due_amount),0) AS due_total, COALESCE(SUM(si.discount_amount),0) AS discount_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY DATE(si.invoice_date) ORDER BY sales_day DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) total, COALESCE(SUM(x.paid_total),0) paid, COALESCE(SUM(x.due_total),0) due FROM (SELECT DATE(si.invoice_date) d, SUM(si.grand_total) sales_total, SUM(si.paid_amount) paid_total, SUM(si.due_amount) due_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY DATE(si.invoice_date)) x",
            $p,
            ['sales_day'=>'اليوم','invoices_count'=>'عدد الفواتير','sales_total'=>'إجمالي المبيعات','due_total'=>'المتبقي'],
            ['عدد الأيام'=>'c','إجمالي المبيعات'=>'total','المدفوع'=>'paid','المتبقي'=>'due']
        );
    }

    private function salesByWarehouses(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->salesBaseWhere($f);
        if ($f['q'] !== '') { $where[]='(w.name LIKE :q OR p.name LIKE :q OR si.invoice_no LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $whr=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(w.name,'-') AS warehouse, COUNT(si.id) AS invoices_count, COALESCE(SUM(si.grand_total),0) AS sales_total, COALESCE(SUM(si.paid_amount),0) AS paid_total, COALESCE(SUM(si.due_amount),0) AS due_total FROM sales_invoices si LEFT JOIN warehouses w ON w.id=si.warehouse_id LEFT JOIN parties p ON p.id=si.party_id WHERE $whr GROUP BY si.warehouse_id, w.name ORDER BY sales_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) total, COALESCE(SUM(x.paid_total),0) paid, COALESCE(SUM(x.due_total),0) due FROM (SELECT si.warehouse_id, SUM(si.grand_total) sales_total, SUM(si.paid_amount) paid_total, SUM(si.due_amount) due_total FROM sales_invoices si LEFT JOIN warehouses w ON w.id=si.warehouse_id LEFT JOIN parties p ON p.id=si.party_id WHERE $whr GROUP BY si.warehouse_id) x",
            $p,
            ['warehouse'=>'المخزن','invoices_count'=>'عدد الفواتير','sales_total'=>'إجمالي المبيعات','due_total'=>'المتبقي'],
            ['عدد المخازن'=>'c','إجمالي المبيعات'=>'total','المدفوع'=>'paid','المتبقي'=>'due']
        );
    }

    private function salesByPaymentMethods(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->salesBaseWhere($f);
        if ($f['q'] !== '') { $where[]='(si.payment_mode LIKE :q OR p.name LIKE :q OR si.invoice_no LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT si.payment_mode, COUNT(si.id) AS invoices_count, COALESCE(SUM(si.grand_total),0) AS sales_total, COALESCE(SUM(si.paid_amount),0) AS paid_total, COALESCE(SUM(si.due_amount),0) AS due_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.payment_mode ORDER BY sales_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) total, COALESCE(SUM(x.paid_total),0) paid, COALESCE(SUM(x.due_total),0) due FROM (SELECT si.payment_mode, SUM(si.grand_total) sales_total, SUM(si.paid_amount) paid_total, SUM(si.due_amount) due_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.payment_mode) x",
            $p,
            ['payment_mode'=>'طريقة الدفع','invoices_count'=>'عدد الفواتير','sales_total'=>'إجمالي المبيعات','due_total'=>'المتبقي'],
            ['عدد طرق الدفع'=>'c','إجمالي المبيعات'=>'total','المدفوع'=>'paid','المتبقي'=>'due']
        );
    }

    private function salesChanges(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->salesBaseWhere($f);
        if ($f['status'] === '') { $where[]="si.status <> 'POSTED'"; }
        if ($f['q'] !== '') { $where[]='(si.invoice_no LIKE :q OR p.name LIKE :q OR si.notes LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT si.invoice_date AS doc_date, si.invoice_no AS doc_no, COALESCE(p.name,'عميل نقدي') AS party, COALESCE(u.full_name,u.username,'-') AS user_name, si.status, si.grand_total, si.notes, si.created_at FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id LEFT JOIN users u ON u.id=si.user_id WHERE $w ORDER BY si.created_at DESC, si.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(si.grand_total),0) total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w",
            $p,
            ['doc_date'=>'التاريخ','doc_no'=>'رقم الفاتورة','party'=>'العميل','status'=>'الحالة','grand_total'=>'الإجمالي'],
            ['عدد الفواتير'=>'c','إجمالي الفواتير'=>'total']
        );
    }

    private function salesDiscounts(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->salesBaseWhere($f);
        $where[]='si.discount_amount > 0';
        if ($f['q'] !== '') { $where[]='(si.invoice_no LIKE :q OR p.name LIKE :q OR si.discount_note LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT si.invoice_date AS doc_date, si.invoice_no AS doc_no, COALESCE(p.name,'عميل نقدي') AS party, COALESCE(u.full_name,u.username,'-') AS user_name, si.subtotal, si.discount_type, si.discount_value, si.discount_amount, si.net_after_discount, si.grand_total, si.discount_note FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id LEFT JOIN users u ON u.id=si.user_id WHERE $w ORDER BY si.invoice_date DESC, si.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(si.subtotal),0) subtotal, COALESCE(SUM(si.discount_amount),0) discount, COALESCE(SUM(si.grand_total),0) total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w",
            $p,
            ['doc_date'=>'التاريخ','doc_no'=>'رقم الفاتورة','party'=>'العميل','subtotal'=>'قبل الخصم','discount_amount'=>'الخصم','grand_total'=>'بعد الخصم'],
            ['عدد فواتير الخصم'=>'c','الإجمالي قبل الخصم'=>'subtotal','إجمالي الخصم'=>'discount','الإجمالي بعد الخصم'=>'total']
        );
    }

    private function salesReturnReasons(array $f, int $limit, int $offset): array
    {
        $where=['1=1'];$p=[];$this->dateWhere($where,$p,'sr.return_date',$f);$this->eqWhere($where,$p,'sr.party_id','party_id',$f);$this->eqWhere($where,$p,'sr.warehouse_id','warehouse_id',$f);$this->eqWhere($where,$p,'sri.item_id','item_id',$f);
        if($f['q']!==''){$where[]='(sr.reason LIKE :q OR p.name LIKE :q OR i.name LIKE :q OR sr.return_no LIKE :q)';$p[':q']='%'.$f['q'].'%';}
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(NULLIF(sr.reason,''),'بدون سبب') AS reason, COUNT(DISTINCT sr.id) AS returns_count, COALESCE(SUM(sri.qty),0) AS qty_total, COALESCE(SUM(sri.line_total),0) AS return_total FROM sales_returns sr LEFT JOIN sales_return_items sri ON sri.sales_return_id=sr.id LEFT JOIN items i ON i.id=sri.item_id LEFT JOIN parties p ON p.id=sr.party_id WHERE $w GROUP BY COALESCE(NULLIF(sr.reason,''),'بدون سبب') ORDER BY return_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.return_total),0) total, COALESCE(SUM(x.qty_total),0) qty FROM (SELECT COALESCE(NULLIF(sr.reason,''),'بدون سبب') reason_key, SUM(COALESCE(sri.line_total,0)) return_total, SUM(COALESCE(sri.qty,0)) qty_total FROM sales_returns sr LEFT JOIN sales_return_items sri ON sri.sales_return_id=sr.id LEFT JOIN items i ON i.id=sri.item_id LEFT JOIN parties p ON p.id=sr.party_id WHERE $w GROUP BY reason_key) x",
            $p,
            ['reason'=>'السبب','returns_count'=>'عدد المرتجعات','qty_total'=>'الكمية المرتجعة','return_total'=>'قيمة المرتجعات'],
            ['عدد الأسباب'=>'c','إجمالي المرتجعات'=>'total','إجمالي الكمية'=>'qty']
        );
    }

    private function mostReturnedSalesItems(array $f, int $limit, int $offset): array
    {
        $where=['1=1'];$p=[];$this->dateWhere($where,$p,'sr.return_date',$f);$this->eqWhere($where,$p,'sr.party_id','party_id',$f);$this->eqWhere($where,$p,'sr.warehouse_id','warehouse_id',$f);$this->eqWhere($where,$p,'sri.item_id','item_id',$f);
        if($f['q']!==''){$where[]='(i.name LIKE :q OR i.barcode LIKE :q OR p.name LIKE :q OR sr.return_no LIKE :q)';$p[':q']='%'.$f['q'].'%';}
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(i.barcode,'') AS barcode, COALESCE(i.name,'-') AS item_name, COALESCE(c.name,'-') AS category, COUNT(DISTINCT sr.id) AS returns_count, COALESCE(SUM(sri.qty),0) AS qty_total, COALESCE(SUM(sri.line_total),0) AS return_total FROM sales_return_items sri JOIN sales_returns sr ON sr.id=sri.sales_return_id LEFT JOIN items i ON i.id=sri.item_id LEFT JOIN categories c ON c.id=i.category_id LEFT JOIN parties p ON p.id=sr.party_id WHERE $w GROUP BY sri.item_id, i.barcode, i.name, c.name ORDER BY qty_total DESC, return_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.qty_total),0) qty, COALESCE(SUM(x.return_total),0) total FROM (SELECT sri.item_id, SUM(sri.qty) qty_total, SUM(sri.line_total) return_total FROM sales_return_items sri JOIN sales_returns sr ON sr.id=sri.sales_return_id LEFT JOIN items i ON i.id=sri.item_id LEFT JOIN parties p ON p.id=sr.party_id WHERE $w GROUP BY sri.item_id) x",
            $p,
            ['item_name'=>'الصنف','returns_count'=>'عدد المرتجعات','qty_total'=>'الكمية المرتجعة','return_total'=>'قيمة المرتجعات'],
            ['عدد الأصناف'=>'c','إجمالي الكمية'=>'qty','إجمالي المرتجعات'=>'total']
        );
    }

    private function customerReturns(array $f, int $limit, int $offset): array
    {
        $where=['1=1'];$p=[];$this->dateWhere($where,$p,'sr.return_date',$f);$this->eqWhere($where,$p,'sr.party_id','party_id',$f);$this->eqWhere($where,$p,'sr.warehouse_id','warehouse_id',$f);
        if($f['q']!==''){$where[]='(p.name LIKE :q OR p.code LIKE :q OR sr.return_no LIKE :q)';$p[':q']='%'.$f['q'].'%';}
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(p.code,'') AS party_code, COALESCE(p.name,'-') AS party, COUNT(sr.id) AS returns_count, COALESCE(SUM(sr.total_amount),0) AS return_total, MAX(sr.return_date) AS last_return_date FROM sales_returns sr LEFT JOIN parties p ON p.id=sr.party_id WHERE $w GROUP BY sr.party_id, p.code, p.name ORDER BY return_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.return_total),0) total FROM (SELECT sr.party_id, SUM(sr.total_amount) return_total FROM sales_returns sr LEFT JOIN parties p ON p.id=sr.party_id WHERE $w GROUP BY sr.party_id) x",
            $p,
            ['party'=>'العميل','returns_count'=>'عدد المرتجعات','return_total'=>'إجمالي المرتجعات','last_return_date'=>'آخر مرتجع'],
            ['عدد العملاء'=>'c','إجمالي المرتجعات'=>'total']
        );
    }

    private function netSalesAfterReturns(array $f, int $limit, int $offset): array
    {
        [$whereSales,$p] = $this->salesBaseWhere($f);
        if ($f['q'] !== '') { $whereSales[]='(p.name LIKE :q OR p.code LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $ws=implode(' AND ',$whereSales);
        $returnWhere=['1=1']; $rp=[]; $this->dateWhere($returnWhere,$rp,'sr.return_date',$f); $this->eqWhere($returnWhere,$rp,'sr.party_id','party_id',$f); $this->eqWhere($returnWhere,$rp,'sr.warehouse_id','warehouse_id',$f); if($f['q']!==''){$returnWhere[]='(p.name LIKE :q OR p.code LIKE :q)';}
        $wr=implode(' AND ',$returnWhere);
        $params=$p + array_diff_key($rp,$p);
        return $this->queryDataset(
            "SELECT COALESCE(s.party,'-') AS party, COALESCE(s.sales_total,0) AS sales_total, COALESCE(r.return_total,0) AS return_total, (COALESCE(s.sales_total,0)-COALESCE(r.return_total,0)) AS net_total, COALESCE(s.invoices_count,0) AS invoices_count, COALESCE(r.returns_count,0) AS returns_count FROM (SELECT si.party_id, COALESCE(p.name,'عميل نقدي') AS party, COUNT(si.id) invoices_count, SUM(si.grand_total) sales_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $ws GROUP BY si.party_id, p.name) s LEFT JOIN (SELECT sr.party_id, COUNT(sr.id) returns_count, SUM(sr.total_amount) return_total FROM sales_returns sr LEFT JOIN parties p ON p.id=sr.party_id WHERE $wr GROUP BY sr.party_id) r ON r.party_id <=> s.party_id ORDER BY net_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(z.sales_total),0) sales, COALESCE(SUM(z.return_total),0) returns, COALESCE(SUM(z.net_total),0) net FROM (SELECT COALESCE(s.party_id,r.party_id) pid, COALESCE(s.sales_total,0) sales_total, COALESCE(r.return_total,0) return_total, (COALESCE(s.sales_total,0)-COALESCE(r.return_total,0)) net_total FROM (SELECT si.party_id, SUM(si.grand_total) sales_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $ws GROUP BY si.party_id) s LEFT JOIN (SELECT sr.party_id, SUM(sr.total_amount) return_total FROM sales_returns sr LEFT JOIN parties p ON p.id=sr.party_id WHERE $wr GROUP BY sr.party_id) r ON r.party_id <=> s.party_id) z",
            $params,
            ['party'=>'العميل','sales_total'=>'المبيعات','return_total'=>'المرتجعات','net_total'=>'الصافي'],
            ['عدد العملاء'=>'c','إجمالي المبيعات'=>'sales','إجمالي المرتجعات'=>'returns','الصافي'=>'net']
        );
    }


    private function purchaseReport(string $slug, array $f, int $limit, int $offset): ?array
    {
        return match ($slug) {
            'purchases-period' => $this->purchaseInvoices($f, $limit, $offset, $slug),
            'purchases-by-supplier' => $this->purchaseSuppliers($f, $limit, $offset),
            'purchase-items' => $this->purchaseItems($f, $limit, $offset),
            'purchase-price-changes' => $this->purchasePriceChanges($f, $limit, $offset),
            'purchase-unpaid' => $this->purchaseInvoices($f, $limit, $offset, $slug),
            'purchases-warehouses' => $this->purchaseWarehouses($f, $limit, $offset),
            'purchases-users' => $this->purchaseUsers($f, $limit, $offset),
            'purchase-returns-period' => $this->purchaseReturns($f, $limit, $offset),
            'supplier-returned-items' => $this->supplierReturnedItems($f, $limit, $offset),
            'supplier-returns' => $this->supplierReturns($f, $limit, $offset),
            'net-purchases-after-returns' => $this->netPurchasesAfterReturns($f, $limit, $offset),
            default => null,
        };
    }

    private function purchaseBaseWhere(array $f, string $alias = 'pi'): array
    {
        $where = ['1=1'];
        $p = [];
        $this->dateWhere($where, $p, $alias . '.inv_date', $f);
        if ((int)($f['party_id'] ?? 0) > 0) { $where[] = 'COALESCE(' . $alias . '.party_id, ' . $alias . '.supplier_id)=:party_id'; $p[':party_id'] = (int)$f['party_id']; }
        $this->eqWhere($where, $p, $alias . '.warehouse_id', 'warehouse_id', $f);
        $this->eqWhere($where, $p, $alias . '.created_by', 'user_id', $f);
        $this->amountWhere($where, $p, $alias . '.total', $f);
        if ($f['status'] !== '') { $where[] = $alias . '.status=:status'; $p[':status'] = $f['status']; }
        if ($f['payment_mode'] !== '') { $where[] = $alias . '.payment_mode=:pm'; $p[':pm'] = $f['payment_mode']; }
        if ($f['q'] !== '') { $where[] = '(' . $alias . '.inv_no LIKE :q OR p.name LIKE :q OR ' . $alias . '.notes LIKE :q)'; $p[':q'] = '%' . $f['q'] . '%'; }
        return [$where, $p];
    }

    private function purchaseReturnBaseWhere(array $f, string $alias = 'pr'): array
    {
        $where = ['1=1'];
        $p = [];
        $this->dateWhere($where, $p, $alias . '.ret_date', $f);
        if ((int)($f['party_id'] ?? 0) > 0) { $where[] = 'COALESCE(' . $alias . '.party_id, ' . $alias . '.supplier_id)=:party_id'; $p[':party_id'] = (int)$f['party_id']; }
        $this->eqWhere($where, $p, $alias . '.warehouse_id', 'warehouse_id', $f);
        if ($f['status'] !== '') { $where[] = $alias . '.status=:status'; $p[':status'] = $f['status']; }
        if ($f['q'] !== '') { $where[] = '(' . $alias . '.ret_no LIKE :q OR p.name LIKE :q OR ' . $alias . '.notes LIKE :q)'; $p[':q'] = '%' . $f['q'] . '%'; }
        return [$where, $p];
    }

    private function purchaseInvoices(array $f, int $limit, int $offset, string $slug): array
    {
        [$where, $p] = $this->purchaseBaseWhere($f);
        if ($slug === 'purchase-unpaid') { $where[] = 'pi.due_amount > 0'; }
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT pi.inv_date AS doc_date, pi.inv_no AS doc_no, COALESCE(p.name,'-') AS party, pi.payment_mode, pi.total, pi.paid_amount, pi.due_amount FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w ORDER BY pi.inv_date DESC, pi.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(pi.total),0) total, COALESCE(SUM(pi.paid_amount),0) paid, COALESCE(SUM(pi.due_amount),0) due FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w",
            $p,
            ['doc_date'=>'التاريخ','doc_no'=>'رقم الفاتورة','party'=>'المورد','payment_mode'=>'الدفع','total'=>'الإجمالي','paid_amount'=>'المدفوع','due_amount'=>'المتبقي'],
            ['عدد الفواتير'=>'c','إجمالي المشتريات'=>'total','المدفوع'=>'paid','المتبقي'=>'due']
        );
    }

    private function purchaseSuppliers(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->purchaseBaseWhere($f);
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'-') AS party, COUNT(pi.id) AS invoices_count, COALESCE(SUM(pi.total),0) AS purchase_total, COALESCE(SUM(pi.due_amount),0) AS due_total, MAX(pi.inv_date) AS last_invoice_date FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY COALESCE(pi.party_id, pi.supplier_id), p.name ORDER BY purchase_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.purchase_total),0) total, COALESCE(SUM(x.due_total),0) due FROM (SELECT pi.party_id, SUM(pi.total) purchase_total, SUM(pi.due_amount) due_total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pi.party_id) x",
            $p,
            ['party'=>'المورد','invoices_count'=>'عدد الفواتير','purchase_total'=>'إجمالي المشتريات','due_total'=>'المتبقي','last_invoice_date'=>'آخر فاتورة'],
            ['عدد الموردين'=>'c','إجمالي المشتريات'=>'total','المتبقي'=>'due']
        );
    }

    private function purchaseItems(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->purchaseBaseWhere($f);
        $this->eqWhere($where, $p, 'pil.item_id', 'item_id', $f);
        if ($f['q'] !== '') { $where[] = '(i.name LIKE :q OR i.barcode LIKE :q OR p.name LIKE :q OR pi.inv_no LIKE :q)'; $p[':q'] = '%' . $f['q'] . '%'; }
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(c.name,'-') AS category, COALESCE(SUM(pil.qty),0) AS qty_total, COALESCE(SUM(pil.line_total),0) AS purchase_total, COALESCE(AVG(pil.unit_price),0) AS avg_price FROM purchase_invoice_lines pil JOIN purchase_invoices pi ON pi.id=pil.invoice_id LEFT JOIN items i ON i.id=pil.item_id LEFT JOIN categories c ON c.id=i.category_id LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pil.item_id, i.name, c.name ORDER BY purchase_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.qty_total),0) qty, COALESCE(SUM(x.purchase_total),0) total FROM (SELECT pil.item_id, SUM(pil.qty) qty_total, SUM(pil.line_total) purchase_total FROM purchase_invoice_lines pil JOIN purchase_invoices pi ON pi.id=pil.invoice_id LEFT JOIN items i ON i.id=pil.item_id LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pil.item_id) x",
            $p,
            ['item_name'=>'الصنف','category'=>'الفئة','qty_total'=>'الكمية','purchase_total'=>'قيمة الشراء','avg_price'=>'متوسط السعر'],
            ['عدد الأصناف'=>'c','إجمالي الكمية'=>'qty','إجمالي الشراء'=>'total']
        );
    }

    private function purchasePriceChanges(array $f, int $limit, int $offset): array
    {
        $where = ['1=1']; $p = [];
        $this->dateWhere($where, $p, 'pi.inv_date', $f);
        $this->eqWhere($where, $p, 'pi.party_id', 'party_id', $f);
        $this->eqWhere($where, $p, 'pil.item_id', 'item_id', $f);
        if ($f['q'] !== '') { $where[] = '(i.name LIKE :q OR i.barcode LIKE :q OR p.name LIKE :q)'; $p[':q'] = '%' . $f['q'] . '%'; }
        $having = 'price_diff <> 0';
        if (($f['diff_min'] ?? '') !== '') { $having .= ' AND price_diff >= :diff_min'; $p[':diff_min'] = (float)$f['diff_min']; }
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(p.name,'-') AS party, COUNT(*) AS purchases_count, MIN(pil.unit_price) AS min_price, MAX(pil.unit_price) AS max_price, (MAX(pil.unit_price)-MIN(pil.unit_price)) AS price_diff FROM purchase_invoice_lines pil JOIN purchase_invoices pi ON pi.id=pil.invoice_id LEFT JOIN items i ON i.id=pil.item_id LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pil.item_id, pi.party_id, i.name, p.name HAVING $having ORDER BY price_diff DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(MAX(x.price_diff),0) max_diff FROM (SELECT pil.item_id, pi.party_id, (MAX(pil.unit_price)-MIN(pil.unit_price)) price_diff FROM purchase_invoice_lines pil JOIN purchase_invoices pi ON pi.id=pil.invoice_id LEFT JOIN items i ON i.id=pil.item_id LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pil.item_id, pi.party_id HAVING $having) x",
            $p,
            ['item_name'=>'الصنف','party'=>'المورد','purchases_count'=>'مرات الشراء','min_price'=>'أقل سعر','max_price'=>'أعلى سعر','price_diff'=>'فرق السعر'],
            ['عدد التغيرات'=>'c','أكبر فرق سعر'=>'max_diff']
        );
    }

    private function purchaseWarehouses(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->purchaseBaseWhere($f);
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(w.name,'-') AS warehouse, COUNT(pi.id) AS invoices_count, COALESCE(SUM(pi.total),0) AS purchase_total, COALESCE(SUM(pi.due_amount),0) AS due_total FROM purchase_invoices pi LEFT JOIN warehouses w ON w.id=pi.warehouse_id LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pi.warehouse_id, w.name ORDER BY purchase_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.purchase_total),0) total, COALESCE(SUM(x.due_total),0) due FROM (SELECT pi.warehouse_id, SUM(pi.total) purchase_total, SUM(pi.due_amount) due_total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pi.warehouse_id) x",
            $p,
            ['warehouse'=>'المخزن','invoices_count'=>'عدد الفواتير','purchase_total'=>'إجمالي المشتريات','due_total'=>'المتبقي'],
            ['عدد المخازن'=>'c','إجمالي المشتريات'=>'total','المتبقي'=>'due']
        );
    }

    private function purchaseUsers(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->purchaseBaseWhere($f);
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(u.full_name,u.username,'-') AS user_name, COUNT(pi.id) AS invoices_count, COALESCE(SUM(pi.total),0) AS purchase_total, COALESCE(SUM(pi.due_amount),0) AS due_total FROM purchase_invoices pi LEFT JOIN users u ON u.id=pi.created_by LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pi.created_by, u.full_name, u.username ORDER BY purchase_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.purchase_total),0) total, COALESCE(SUM(x.due_total),0) due FROM (SELECT pi.created_by, SUM(pi.total) purchase_total, SUM(pi.due_amount) due_total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $w GROUP BY pi.created_by) x",
            $p,
            ['user_name'=>'المستخدم','invoices_count'=>'عدد الفواتير','purchase_total'=>'إجمالي المشتريات','due_total'=>'المتبقي'],
            ['عدد المستخدمين'=>'c','إجمالي المشتريات'=>'total','المتبقي'=>'due']
        );
    }

    private function purchaseReturns(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->purchaseReturnBaseWhere($f);
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT pr.ret_date AS doc_date, pr.ret_no AS doc_no, COALESCE(p.name,'-') AS party, COALESCE(w.name,'-') AS warehouse, pr.total FROM purchase_returns pr LEFT JOIN parties p ON p.id=COALESCE(pr.party_id, pr.supplier_id) LEFT JOIN warehouses w ON w.id=pr.warehouse_id WHERE $w ORDER BY pr.ret_date DESC, pr.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(pr.total),0) total FROM purchase_returns pr LEFT JOIN parties p ON p.id=COALESCE(pr.party_id, pr.supplier_id) WHERE $w",
            $p,
            ['doc_date'=>'التاريخ','doc_no'=>'رقم المرتجع','party'=>'المورد','warehouse'=>'المخزن','total'=>'الإجمالي'],
            ['عدد المرتجعات'=>'c','إجمالي المرتجعات'=>'total']
        );
    }

    private function supplierReturnedItems(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->purchaseReturnBaseWhere($f);
        $this->eqWhere($where, $p, 'prl.item_id', 'item_id', $f);
        if ($f['q'] !== '') { $where[] = '(i.name LIKE :q OR i.barcode LIKE :q OR p.name LIKE :q OR pr.ret_no LIKE :q)'; $p[':q'] = '%' . $f['q'] . '%'; }
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(p.name,'-') AS party, COALESCE(SUM(prl.qty),0) AS qty_total, COALESCE(SUM(prl.line_total),0) AS return_total FROM purchase_return_lines prl JOIN purchase_returns pr ON pr.id=prl.return_id LEFT JOIN items i ON i.id=prl.item_id LEFT JOIN parties p ON p.id=COALESCE(pr.party_id, pr.supplier_id) WHERE $w GROUP BY prl.item_id, pr.party_id, i.name, p.name ORDER BY qty_total DESC, return_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.qty_total),0) qty, COALESCE(SUM(x.return_total),0) total FROM (SELECT prl.item_id, pr.party_id, SUM(prl.qty) qty_total, SUM(prl.line_total) return_total FROM purchase_return_lines prl JOIN purchase_returns pr ON pr.id=prl.return_id LEFT JOIN items i ON i.id=prl.item_id LEFT JOIN parties p ON p.id=COALESCE(pr.party_id, pr.supplier_id) WHERE $w GROUP BY prl.item_id, pr.party_id) x",
            $p,
            ['item_name'=>'الصنف','party'=>'المورد','qty_total'=>'الكمية المرتجعة','return_total'=>'قيمة المرتجعات'],
            ['عدد الأصناف'=>'c','إجمالي الكمية'=>'qty','إجمالي المرتجعات'=>'total']
        );
    }

    private function supplierReturns(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->purchaseReturnBaseWhere($f);
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'-') AS party, COUNT(pr.id) AS returns_count, COALESCE(SUM(pr.total),0) AS return_total, MAX(pr.ret_date) AS last_return_date FROM purchase_returns pr LEFT JOIN parties p ON p.id=COALESCE(pr.party_id, pr.supplier_id) WHERE $w GROUP BY COALESCE(pr.party_id, pr.supplier_id), p.name ORDER BY return_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.return_total),0) total FROM (SELECT pr.party_id, SUM(pr.total) return_total FROM purchase_returns pr LEFT JOIN parties p ON p.id=COALESCE(pr.party_id, pr.supplier_id) WHERE $w GROUP BY pr.party_id) x",
            $p,
            ['party'=>'المورد','returns_count'=>'عدد المرتجعات','return_total'=>'إجمالي المرتجعات','last_return_date'=>'آخر مرتجع'],
            ['عدد الموردين'=>'c','إجمالي المرتجعات'=>'total']
        );
    }

    private function netPurchasesAfterReturns(array $f, int $limit, int $offset): array
    {
        [$wherePurchase, $p] = $this->purchaseBaseWhere($f);
        $wp = implode(' AND ', $wherePurchase);
        [$whereReturn, $rp] = $this->purchaseReturnBaseWhere($f);
        $wr = implode(' AND ', $whereReturn);
        $params = $p + array_diff_key($rp, $p);
        return $this->queryDataset(
            "SELECT COALESCE(pu.party,'-') AS party, COALESCE(pu.purchase_total,0) AS purchase_total, COALESCE(r.return_total,0) AS return_total, (COALESCE(pu.purchase_total,0)-COALESCE(r.return_total,0)) AS net_total FROM (SELECT pi.party_id, COALESCE(p.name,'-') AS party, SUM(pi.total) purchase_total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $wp GROUP BY COALESCE(pi.party_id, pi.supplier_id), p.name) pu LEFT JOIN (SELECT pr.party_id, SUM(pr.total) return_total FROM purchase_returns pr LEFT JOIN parties p ON p.id=COALESCE(pr.party_id, pr.supplier_id) WHERE $wr GROUP BY pr.party_id) r ON r.party_id <=> pu.party_id ORDER BY net_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(z.purchase_total),0) purchases, COALESCE(SUM(z.return_total),0) returns, COALESCE(SUM(z.net_total),0) net FROM (SELECT COALESCE(pu.party_id,r.party_id) pid, COALESCE(pu.purchase_total,0) purchase_total, COALESCE(r.return_total,0) return_total, (COALESCE(pu.purchase_total,0)-COALESCE(r.return_total,0)) net_total FROM (SELECT pi.party_id, SUM(pi.total) purchase_total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id, pi.supplier_id) WHERE $wp GROUP BY pi.party_id) pu LEFT JOIN (SELECT pr.party_id, SUM(pr.total) return_total FROM purchase_returns pr LEFT JOIN parties p ON p.id=COALESCE(pr.party_id, pr.supplier_id) WHERE $wr GROUP BY pr.party_id) r ON r.party_id <=> pu.party_id) z",
            $params,
            ['party'=>'المورد','purchase_total'=>'المشتريات','return_total'=>'المرتجعات','net_total'=>'الصافي'],
            ['عدد الموردين'=>'c','إجمالي المشتريات'=>'purchases','إجمالي المرتجعات'=>'returns','الصافي'=>'net']
        );
    }

    private function salesReturns(array $f, int $limit, int $offset): array
    {
        $where=['1=1'];$p=[];$this->dateWhere($where,$p,'sr.return_date',$f);$this->eqWhere($where,$p,'sr.party_id','party_id',$f);$this->eqWhere($where,$p,'sr.warehouse_id','warehouse_id',$f);if($f['q']!==''){$where[]='(sr.return_no LIKE :q OR p.name LIKE :q OR sr.reason LIKE :q)';$p[':q']='%'.$f['q'].'%';}$w=implode(' AND ',$where);
        return $this->queryDataset("SELECT sr.return_date AS doc_date, sr.return_no AS doc_no, COALESCE(p.name,'-') AS party, COALESCE(w.name,'-') AS warehouse, sr.status, sr.settlement_type, sr.reason, sr.total_amount FROM sales_returns sr LEFT JOIN parties p ON p.id=sr.party_id LEFT JOIN warehouses w ON w.id=sr.warehouse_id WHERE $w ORDER BY sr.return_date DESC LIMIT $limit OFFSET $offset", "SELECT COUNT(*) c, COALESCE(SUM(sr.total_amount),0) total FROM sales_returns sr LEFT JOIN parties p ON p.id=sr.party_id WHERE $w", $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم المرتجع','party'=>'العميل','reason'=>'السبب','total_amount'=>'الإجمالي'], ['عدد المرتجعات'=>'c','إجمالي المرتجعات'=>'total']);
    }


    private function partyReport(string $slug, array $f, int $limit, int $offset): ?array
    {
        return match ($slug) {
            'customer-balances' => $this->customerBalances($f, $limit, $offset),
            'customer-aging' => $this->customerAging($f, $limit, $offset),
            'late-customers' => $this->lateCustomers($f, $limit, $offset),
            'customer-activity' => $this->customerActivity($f, $limit, $offset),
            'inactive-customers' => $this->inactiveCustomers($f, $limit, $offset),
            'top-customers' => $this->topCustomers($f, $limit, $offset),
            'credit-limit' => $this->creditLimitReport($f, $limit, $offset),
            'supplier-balances' => $this->supplierBalances($f, $limit, $offset),
            'supplier-debt-aging' => $this->supplierDebtAging($f, $limit, $offset),
            'top-suppliers' => $this->topSuppliers($f, $limit, $offset),
            'supplier-item-prices' => $this->supplierItemPrices($f, $limit, $offset),
            'inactive-suppliers' => $this->inactiveSuppliers($f, $limit, $offset),
            default => null,
        };
    }

    private function partyBaseWhere(array $f, string $kind): array
    {
        $where = [$kind === 'supplier' ? 'COALESCE(p.is_supplier,0)=1' : 'COALESCE(p.is_customer,0)=1'];
        $params = [];
        if ((int)($f['party_id'] ?? 0) > 0) { $where[] = 'p.id = :party_id'; $params[':party_id'] = (int)$f['party_id']; }
        if (($f['q'] ?? '') !== '') { $where[] = '(p.name LIKE :q OR p.code LIKE :q OR p.phone LIKE :q)'; $params[':q'] = '%'.$f['q'].'%'; }
        return [$where, $params];
    }

    private function customerBalances(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->partyBaseWhere($f, 'customer');
        $w = implode(' AND ', $where);
        $invoiceWhere = ['1=1']; $ip=[]; $iw=implode(' AND ', $invoiceWhere);
        $returnWhere = ['1=1']; $rp=[]; $rw=implode(' AND ', $returnWhere);
        $params = $p + $ip + $rp;
        return $this->queryDataset(
            "SELECT p.name AS party, COALESCE(p.phone,'-') AS phone, COALESCE(p.opening_balance,0) AS opening_balance, COALESCE(s.sales_total,0) AS sales_total, COALESCE(s.paid_total,0) AS paid_total, COALESCE(s.due_total,0) AS due_total, COALESCE(r.returns_total,0) AS returns_total, (COALESCE(p.opening_balance,0)+COALESCE(s.due_total,0)-COALESCE(r.returns_total,0)) AS balance FROM parties p LEFT JOIN (SELECT party_id, SUM(grand_total) sales_total, SUM(paid_amount) paid_total, SUM(due_amount) due_total FROM sales_invoices si WHERE $iw GROUP BY party_id) s ON s.party_id=p.id LEFT JOIN (SELECT party_id, SUM(total_amount) returns_total FROM sales_returns sr WHERE $rw GROUP BY party_id) r ON r.party_id=p.id WHERE $w ORDER BY balance DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.balance),0) balance, COALESCE(SUM(x.due_total),0) due, COALESCE(SUM(x.sales_total),0) sales FROM (SELECT p.id, COALESCE(s.sales_total,0) sales_total, COALESCE(s.due_total,0) due_total, (COALESCE(p.opening_balance,0)+COALESCE(s.due_total,0)-COALESCE(r.returns_total,0)) balance FROM parties p LEFT JOIN (SELECT party_id, SUM(grand_total) sales_total, SUM(due_amount) due_total FROM sales_invoices si WHERE $iw GROUP BY party_id) s ON s.party_id=p.id LEFT JOIN (SELECT party_id, SUM(total_amount) returns_total FROM sales_returns sr WHERE $rw GROUP BY party_id) r ON r.party_id=p.id WHERE $w) x",
            $params,
            ['party'=>'العميل','opening_balance'=>'رصيد افتتاحي','sales_total'=>'إجمالي المبيعات','due_total'=>'المتبقي','balance'=>'الرصيد'],
            ['عدد العملاء'=>'c','إجمالي المبيعات'=>'sales','إجمالي المتبقي'=>'due','إجمالي الأرصدة'=>'balance']
        );
    }

    private function customerAging(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->partyBaseWhere($f, 'customer');
        $this->dateWhere($where,$p,'si.invoice_date',$f);
        $where[] = 'si.due_amount > 0';
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'عميل نقدي') AS party, COALESCE(SUM(si.due_amount),0) AS total_due, SUM(CASE WHEN DATEDIFF(CURDATE(), si.invoice_date) <= 30 THEN si.due_amount ELSE 0 END) AS d_0_30, SUM(CASE WHEN DATEDIFF(CURDATE(), si.invoice_date) BETWEEN 31 AND 60 THEN si.due_amount ELSE 0 END) AS d_31_60, SUM(CASE WHEN DATEDIFF(CURDATE(), si.invoice_date) BETWEEN 61 AND 90 THEN si.due_amount ELSE 0 END) AS d_61_90, SUM(CASE WHEN DATEDIFF(CURDATE(), si.invoice_date) > 90 THEN si.due_amount ELSE 0 END) AS d_over_90, MAX(si.invoice_date) AS last_invoice FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id, p.name ORDER BY total_due DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.total_due),0) total_due, COALESCE(SUM(x.d_0_30),0) d_0_30, COALESCE(SUM(x.d_over_90),0) d_over_90 FROM (SELECT si.party_id, SUM(si.due_amount) total_due, SUM(CASE WHEN DATEDIFF(CURDATE(), si.invoice_date) <= 30 THEN si.due_amount ELSE 0 END) d_0_30, SUM(CASE WHEN DATEDIFF(CURDATE(), si.invoice_date) > 90 THEN si.due_amount ELSE 0 END) d_over_90 FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id) x",
            $p,
            ['party'=>'العميل','total_due'=>'إجمالي الدين','d_0_30'=>'0-30 يوم','d_31_60'=>'31-60 يوم','d_61_90'=>'61-90 يوم','d_over_90'=>'أكثر من 90','last_invoice'=>'آخر فاتورة'],
            ['عدد العملاء'=>'c','إجمالي الدين'=>'total_due','دين حديث 0-30'=>'d_0_30','أكثر من 90 يوم'=>'d_over_90']
        );
    }

    private function lateCustomers(array $f, int $limit, int $offset): array
    {
        $days = (int)($f['inactive_days'] !== '' ? $f['inactive_days'] : 30);
        [$where,$p] = $this->partyBaseWhere($f, 'customer');
        $this->dateWhere($where,$p,'si.invoice_date',$f);
        $where[]='si.due_amount > 0'; $where[]='DATEDIFF(CURDATE(), si.invoice_date) >= :late_days'; $p[':late_days']=$days;
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'عميل نقدي') AS party, COUNT(si.id) AS invoices_count, SUM(si.due_amount) AS due_total, MIN(si.invoice_date) AS oldest_invoice, MAX(si.invoice_date) AS last_invoice, MAX(DATEDIFF(CURDATE(), si.invoice_date)) AS max_delay_days FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id, p.name ORDER BY due_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.due_total),0) due_total, COALESCE(MAX(x.max_delay_days),0) max_delay FROM (SELECT si.party_id, SUM(si.due_amount) due_total, MAX(DATEDIFF(CURDATE(), si.invoice_date)) max_delay_days FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id) x",
            $p,
            ['party'=>'العميل','invoices_count'=>'فواتير متأخرة','due_total'=>'المتأخر','oldest_invoice'=>'أقدم فاتورة','max_delay_days'=>'أكبر تأخير/يوم'],
            ['عدد العملاء'=>'c','إجمالي المتأخر'=>'due_total','أكبر تأخير'=>'max_delay']
        );
    }

    private function customerActivity(array $f, int $limit, int $offset): array { return $this->topCustomers($f, $limit, $offset); }

    private function topCustomers(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->partyBaseWhere($f, 'customer');
        $this->dateWhere($where,$p,'si.invoice_date',$f);
        $this->amountWhere($where,$p,'si.grand_total',$f);
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'عميل نقدي') AS party, COUNT(si.id) AS invoices_count, SUM(si.grand_total) AS sales_total, SUM(si.paid_amount) AS paid_total, SUM(si.due_amount) AS due_total, MAX(si.invoice_date) AS last_invoice FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id, p.name ORDER BY sales_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) sales_total, COALESCE(SUM(x.due_total),0) due_total FROM (SELECT si.party_id, SUM(si.grand_total) sales_total, SUM(si.due_amount) due_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id) x",
            $p,
            ['party'=>'العميل','invoices_count'=>'عدد الفواتير','sales_total'=>'إجمالي المبيعات','due_total'=>'المتبقي','last_invoice'=>'آخر تعامل'],
            ['عدد العملاء'=>'c','إجمالي المبيعات'=>'sales_total','إجمالي المتبقي'=>'due_total']
        );
    }

    private function inactiveCustomers(array $f, int $limit, int $offset): array
    {
        $days = (int)($f['inactive_days'] !== '' ? $f['inactive_days'] : 90);
        [$where,$p] = $this->partyBaseWhere($f, 'customer');
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT p.name AS party, COALESCE(p.phone,'-') AS phone, COALESCE(s.invoices_count,0) AS invoices_count, COALESCE(s.sales_total,0) AS sales_total, s.last_invoice, CASE WHEN s.last_invoice IS NULL THEN NULL ELSE DATEDIFF(CURDATE(), s.last_invoice) END AS inactive_days FROM parties p LEFT JOIN (SELECT party_id, COUNT(*) invoices_count, SUM(grand_total) sales_total, MAX(invoice_date) last_invoice FROM sales_invoices GROUP BY party_id) s ON s.party_id=p.id WHERE $w AND (s.last_invoice IS NULL OR s.last_invoice < DATE_SUB(CURDATE(), INTERVAL :days DAY)) ORDER BY s.last_invoice IS NULL DESC, s.last_invoice ASC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(COALESCE(s.sales_total,0)),0) sales_total FROM parties p LEFT JOIN (SELECT party_id, SUM(grand_total) sales_total, MAX(invoice_date) last_invoice FROM sales_invoices GROUP BY party_id) s ON s.party_id=p.id WHERE $w AND (s.last_invoice IS NULL OR s.last_invoice < DATE_SUB(CURDATE(), INTERVAL :days DAY))",
            $p + [':days'=>$days],
            ['party'=>'العميل','phone'=>'الهاتف','invoices_count'=>'عدد الفواتير','sales_total'=>'إجمالي سابق','last_invoice'=>'آخر تعامل','inactive_days'=>'أيام عدم النشاط'],
            ['عدد العملاء غير النشطين'=>'c','إجمالي تعاملات سابقة'=>'sales_total']
        );
    }

    private function creditLimitReport(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->partyBaseWhere($f, 'customer');
        $where[]='COALESCE(p.credit_limit,0) > 0';
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT p.name AS party, p.credit_limit, COALESCE(s.due_total,0)+COALESCE(p.opening_balance,0) AS balance, (COALESCE(s.due_total,0)+COALESCE(p.opening_balance,0)-p.credit_limit) AS exceeded_by, s.last_invoice FROM parties p LEFT JOIN (SELECT party_id, SUM(due_amount) due_total, MAX(invoice_date) last_invoice FROM sales_invoices GROUP BY party_id) s ON s.party_id=p.id WHERE $w AND (COALESCE(s.due_total,0)+COALESCE(p.opening_balance,0)) > p.credit_limit ORDER BY exceeded_by DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.balance),0) balance, COALESCE(SUM(x.exceeded_by),0) exceeded FROM (SELECT p.id, COALESCE(s.due_total,0)+COALESCE(p.opening_balance,0) balance, (COALESCE(s.due_total,0)+COALESCE(p.opening_balance,0)-p.credit_limit) exceeded_by FROM parties p LEFT JOIN (SELECT party_id, SUM(due_amount) due_total FROM sales_invoices GROUP BY party_id) s ON s.party_id=p.id WHERE $w AND (COALESCE(s.due_total,0)+COALESCE(p.opening_balance,0)) > p.credit_limit) x",
            $p,
            ['party'=>'العميل','credit_limit'=>'حد الائتمان','balance'=>'الرصيد','exceeded_by'=>'التجاوز','last_invoice'=>'آخر تعامل'],
            ['عدد العملاء المتجاوزين'=>'c','إجمالي الأرصدة'=>'balance','إجمالي التجاوز'=>'exceeded']
        );
    }

    private function supplierBalances(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->partyBaseWhere($f, 'supplier');
        $w=implode(' AND ',$where);
        $invoiceWhere=['1=1']; $ip=[]; $iw=implode(' AND ',$invoiceWhere);
        $returnWhere=['1=1']; $rp=[]; $rw=implode(' AND ',$returnWhere);
        $params=$p+$ip+$rp;
        return $this->queryDataset(
            "SELECT p.name AS party, COALESCE(p.phone,'-') AS phone, COALESCE(p.opening_balance,0) AS opening_balance, COALESCE(pi.purchase_total,0) AS purchase_total, COALESCE(pi.due_total,0) AS due_total, COALESCE(pr.return_total,0) AS return_total, (COALESCE(p.opening_balance,0)+COALESCE(pi.due_total,0)-COALESCE(pr.return_total,0)) AS balance FROM parties p LEFT JOIN (SELECT COALESCE(party_id,supplier_id) party_id, SUM(total) purchase_total, SUM(due_amount) due_total FROM purchase_invoices pi WHERE $iw GROUP BY COALESCE(party_id,supplier_id)) pi ON pi.party_id=p.id LEFT JOIN (SELECT COALESCE(party_id,supplier_id) party_id, SUM(total) return_total FROM purchase_returns pr WHERE $rw GROUP BY COALESCE(party_id,supplier_id)) pr ON pr.party_id=p.id WHERE $w ORDER BY balance DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.balance),0) balance, COALESCE(SUM(x.due_total),0) due, COALESCE(SUM(x.purchase_total),0) purchase_total FROM (SELECT p.id, COALESCE(pi.purchase_total,0) purchase_total, COALESCE(pi.due_total,0) due_total, (COALESCE(p.opening_balance,0)+COALESCE(pi.due_total,0)-COALESCE(pr.return_total,0)) balance FROM parties p LEFT JOIN (SELECT COALESCE(party_id,supplier_id) party_id, SUM(total) purchase_total, SUM(due_amount) due_total FROM purchase_invoices pi WHERE $iw GROUP BY COALESCE(party_id,supplier_id)) pi ON pi.party_id=p.id LEFT JOIN (SELECT COALESCE(party_id,supplier_id) party_id, SUM(total) return_total FROM purchase_returns pr WHERE $rw GROUP BY COALESCE(party_id,supplier_id)) pr ON pr.party_id=p.id WHERE $w) x",
            $params,
            ['party'=>'المورد','opening_balance'=>'رصيد افتتاحي','purchase_total'=>'إجمالي المشتريات','due_total'=>'المتبقي','balance'=>'الرصيد'],
            ['عدد الموردين'=>'c','إجمالي المشتريات'=>'purchase_total','إجمالي المتبقي'=>'due','إجمالي الأرصدة'=>'balance']
        );
    }

    private function supplierDebtAging(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->partyBaseWhere($f, 'supplier');
        $this->dateWhere($where,$p,'pi.inv_date',$f); $where[]='pi.due_amount > 0';
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'-') AS party, SUM(pi.due_amount) AS total_due, SUM(CASE WHEN DATEDIFF(CURDATE(), pi.inv_date) <= 30 THEN pi.due_amount ELSE 0 END) AS d_0_30, SUM(CASE WHEN DATEDIFF(CURDATE(), pi.inv_date) BETWEEN 31 AND 60 THEN pi.due_amount ELSE 0 END) AS d_31_60, SUM(CASE WHEN DATEDIFF(CURDATE(), pi.inv_date) BETWEEN 61 AND 90 THEN pi.due_amount ELSE 0 END) AS d_61_90, SUM(CASE WHEN DATEDIFF(CURDATE(), pi.inv_date) > 90 THEN pi.due_amount ELSE 0 END) AS d_over_90, MAX(pi.inv_date) AS last_invoice FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $w GROUP BY COALESCE(pi.party_id,pi.supplier_id), p.name ORDER BY total_due DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.total_due),0) total_due, COALESCE(SUM(x.d_over_90),0) d_over_90 FROM (SELECT COALESCE(pi.party_id,pi.supplier_id) party_id, SUM(pi.due_amount) total_due, SUM(CASE WHEN DATEDIFF(CURDATE(), pi.inv_date) > 90 THEN pi.due_amount ELSE 0 END) d_over_90 FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $w GROUP BY COALESCE(pi.party_id,pi.supplier_id)) x",
            $p,
            ['party'=>'المورد','total_due'=>'إجمالي الدين','d_0_30'=>'0-30 يوم','d_31_60'=>'31-60 يوم','d_61_90'=>'61-90 يوم','d_over_90'=>'أكثر من 90','last_invoice'=>'آخر فاتورة'],
            ['عدد الموردين'=>'c','إجمالي الدين'=>'total_due','أكثر من 90 يوم'=>'d_over_90']
        );
    }

    private function topSuppliers(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->partyBaseWhere($f, 'supplier');
        $this->dateWhere($where,$p,'pi.inv_date',$f); $this->amountWhere($where,$p,'pi.total',$f);
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'-') AS party, COUNT(pi.id) AS invoices_count, SUM(pi.total) AS purchase_total, SUM(pi.due_amount) AS due_total, MAX(pi.inv_date) AS last_invoice FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $w GROUP BY COALESCE(pi.party_id,pi.supplier_id), p.name ORDER BY purchase_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.purchase_total),0) purchase_total, COALESCE(SUM(x.due_total),0) due_total FROM (SELECT COALESCE(pi.party_id,pi.supplier_id) party_id, SUM(pi.total) purchase_total, SUM(pi.due_amount) due_total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $w GROUP BY COALESCE(pi.party_id,pi.supplier_id)) x",
            $p,
            ['party'=>'المورد','invoices_count'=>'عدد الفواتير','purchase_total'=>'إجمالي المشتريات','due_total'=>'المتبقي','last_invoice'=>'آخر تعامل'],
            ['عدد الموردين'=>'c','إجمالي المشتريات'=>'purchase_total','إجمالي المتبقي'=>'due_total']
        );
    }

    private function supplierItemPrices(array $f, int $limit, int $offset): array
    {
        [$where,$p] = $this->partyBaseWhere($f, 'supplier');
        $this->dateWhere($where,$p,'pi.inv_date',$f); $this->eqWhere($where,$p,'pil.item_id','item_id',$f);
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'-') AS party, COALESCE(i.name,'-') AS item_name, MIN(pil.unit_price) AS min_price, MAX(pil.unit_price) AS max_price, AVG(pil.unit_price) AS avg_price, SUBSTRING_INDEX(GROUP_CONCAT(pil.unit_price ORDER BY pi.inv_date DESC SEPARATOR ','), ',', 1) AS last_price, MAX(pi.inv_date) AS last_purchase FROM purchase_invoice_lines pil JOIN purchase_invoices pi ON pi.id=pil.invoice_id LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) LEFT JOIN items i ON i.id=pil.item_id WHERE $w GROUP BY COALESCE(pi.party_id,pi.supplier_id), p.name, pil.item_id, i.name ORDER BY last_purchase DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(AVG(x.avg_price),0) avg_price, COALESCE(MAX(x.max_price-x.min_price),0) max_diff FROM (SELECT COALESCE(pi.party_id,pi.supplier_id) party_id, pil.item_id, MIN(pil.unit_price) min_price, MAX(pil.unit_price) max_price, AVG(pil.unit_price) avg_price FROM purchase_invoice_lines pil JOIN purchase_invoices pi ON pi.id=pil.invoice_id LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) LEFT JOIN items i ON i.id=pil.item_id WHERE $w GROUP BY COALESCE(pi.party_id,pi.supplier_id), pil.item_id) x",
            $p,
            ['party'=>'المورد','item_name'=>'الصنف','min_price'=>'أقل سعر','max_price'=>'أعلى سعر','avg_price'=>'متوسط السعر','last_price'=>'آخر سعر','last_purchase'=>'آخر شراء'],
            ['عدد العلاقات'=>'c','متوسط الأسعار'=>'avg_price','أكبر فرق سعر'=>'max_diff']
        );
    }

    private function inactiveSuppliers(array $f, int $limit, int $offset): array
    {
        $days = (int)($f['inactive_days'] !== '' ? $f['inactive_days'] : 90);
        [$where,$p] = $this->partyBaseWhere($f, 'supplier');
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT p.name AS party, COALESCE(p.phone,'-') AS phone, COALESCE(pi.invoices_count,0) AS invoices_count, COALESCE(pi.purchase_total,0) AS purchase_total, pi.last_invoice, CASE WHEN pi.last_invoice IS NULL THEN NULL ELSE DATEDIFF(CURDATE(), pi.last_invoice) END AS inactive_days FROM parties p LEFT JOIN (SELECT COALESCE(party_id,supplier_id) party_id, COUNT(*) invoices_count, SUM(total) purchase_total, MAX(inv_date) last_invoice FROM purchase_invoices GROUP BY COALESCE(party_id,supplier_id)) pi ON pi.party_id=p.id WHERE $w AND (pi.last_invoice IS NULL OR pi.last_invoice < DATE_SUB(CURDATE(), INTERVAL :days DAY)) ORDER BY pi.last_invoice IS NULL DESC, pi.last_invoice ASC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(COALESCE(pi.purchase_total,0)),0) purchase_total FROM parties p LEFT JOIN (SELECT COALESCE(party_id,supplier_id) party_id, SUM(total) purchase_total, MAX(inv_date) last_invoice FROM purchase_invoices GROUP BY COALESCE(party_id,supplier_id)) pi ON pi.party_id=p.id WHERE $w AND (pi.last_invoice IS NULL OR pi.last_invoice < DATE_SUB(CURDATE(), INTERVAL :days DAY))",
            $p + [':days'=>$days],
            ['party'=>'المورد','phone'=>'الهاتف','invoices_count'=>'عدد الفواتير','purchase_total'=>'إجمالي سابق','last_invoice'=>'آخر تعامل','inactive_days'=>'أيام عدم النشاط'],
            ['عدد الموردين غير النشطين'=>'c','إجمالي تعاملات سابقة'=>'purchase_total']
        );
    }

    private function stockReport(string $slug, array $f, int $limit, int $offset): ?array
    {
        return match ($slug) {
            'stock-balances' => $this->stockBalances($f, $limit, $offset),
            'stock-movement' => $this->stockMovement($f, $limit, $offset, false),
            'item-card' => $this->stockMovement($f, $limit, $offset, true),
            'stock-by-warehouse' => $this->stockByWarehouse($f, $limit, $offset),
            'inactive-items' => $this->inactiveItems($f, $limit, $offset),
            'most-moving-items' => $this->mostMovingItems($f, $limit, $offset),
            'reorder-items' => $this->reorderItems($f, $limit, $offset),
            'negative-items' => $this->negativeItems($f, $limit, $offset),
            'stock-adjustments' => $this->stockMovesByRef($f, $limit, $offset, 'ADJUST'),
            'stock-transfers' => $this->stockTransfers($f, $limit, $offset),
            'stock-cost-value' => $this->stockValue($f, $limit, $offset, 'cost'),
            'stock-sale-value' => $this->stockValue($f, $limit, $offset, 'sale'),
            'stock-margin' => $this->stockMargin($f, $limit, $offset),
            'stock-by-supplier' => $this->stockBySupplier($f, $limit, $offset),
            default => null,
        };
    }

    private function itemSupplierColumn(): ?string
    {
        return table_has_column($this->pdo, 'items', 'default_supplier_id') ? 'default_supplier_id' : null;
    }

    private function itemSupplierExpr(string $itemAlias = 'i'): string
    {
        $column = $this->itemSupplierColumn();
        return $column ? $itemAlias . '.' . $column : 'NULL';
    }

    /**
     * items.default_supplier_id is the canonical direct relation to parties.id where parties.is_supplier=1.
     * Keep report queries index-friendly; no name/phone/email fallback joins.
     */
    private function itemSupplierJoin(string $itemAlias = 'i'): string
    {
        $expr = $this->itemSupplierExpr($itemAlias);
        return " LEFT JOIN parties p ON p.id=$expr AND COALESCE(p.is_supplier,0)=1";
    }

    private function itemSupplierNameExpr(): string
    {
        return "COALESCE(p.name, 'بدون مورد محدد')";
    }

    private function stockSupplierFilterSql(string $param = ':party_id'): string
    {
        $expr = $this->itemSupplierExpr('i');
        if ($expr === 'NULL') {
            return '1=0';
        }
        return "$expr = $param";
    }

    private function stockBalanceWhere(array $f, string $alias = 'sb'): array
    {
        $where = ['1=1']; $p = [];
        $this->eqWhere($where, $p, $alias . '.warehouse_id', 'warehouse_id', $f);
        $this->eqWhere($where, $p, $alias . '.item_id', 'item_id', $f);
        if ((int)($f['party_id'] ?? 0) > 0) { $where[] = $this->stockSupplierFilterSql(':party_id'); $p[':party_id'] = (int)$f['party_id']; }
        if ((int)($f['non_zero'] ?? 0) === 1) { $where[] = $alias . '.qty <> 0'; }
        if ((int)($f['negative_only'] ?? 0) === 1) { $where[] = $alias . '.qty < 0'; }
        if ((int)($f['below_reorder'] ?? 0) === 1) { $where[] = $alias . '.qty <= COALESCE(i.reorder_point,0)'; }
        if (($f['value_min'] ?? '') !== '') { $where[] = '(' . $alias . '.qty * i.cost) >= :value_min'; $p[':value_min'] = (float)$f['value_min']; }
        if (($f['value_max'] ?? '') !== '') { $where[] = '(' . $alias . '.qty * i.cost) <= :value_max'; $p[':value_max'] = (float)$f['value_max']; }
        if ($f['q'] !== '') { $where[] = '(i.name LIKE :q OR i.barcode LIKE :q OR i.brand LIKE :q)'; $p[':q'] = '%' . $f['q'] . '%'; }
        return [$where, $p];
    }

    private function stockBalances(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->stockBalanceWhere($f); $w = implode(' AND ', $where);
        $supplierJoin = $this->itemSupplierJoin('i');
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(w.name,'-') AS warehouse, sb.qty, i.cost, (sb.qty*i.cost) AS cost_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id LEFT JOIN warehouses w ON w.id=sb.warehouse_id $supplierJoin WHERE $w ORDER BY i.name ASC, w.name ASC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(sb.qty),0) qty, COALESCE(SUM(sb.qty*i.cost),0) cost_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id $supplierJoin WHERE $w",
            $p, ['item_name'=>'الصنف','warehouse'=>'المخزن','qty'=>'الرصيد','cost'=>'التكلفة','cost_value'=>'قيمة التكلفة'], ['عدد السطور'=>'c','إجمالي الكمية'=>'qty','قيمة المخزون'=>'cost_value']
        );
    }

    private function stockBySupplier(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->stockBalanceWhere($f); $w = implode(' AND ', $where);
        $supplierJoin = $this->itemSupplierJoin('i');
        $supplierExpr = $this->itemSupplierExpr('i');
        $supplierName = $this->itemSupplierNameExpr();
        return $this->queryDataset(
            "SELECT $supplierName AS party, COUNT(DISTINCT sb.item_id) AS items_count, COALESCE(SUM(sb.qty),0) AS qty_total, COALESCE(SUM(sb.qty*i.cost),0) AS cost_value, MAX(m.last_move_at) AS last_move_at FROM stock_balances sb JOIN items i ON i.id=sb.item_id $supplierJoin LEFT JOIN (SELECT item_id, warehouse_id, MAX(created_at) last_move_at FROM stock_moves GROUP BY item_id, warehouse_id) m ON m.item_id=sb.item_id AND m.warehouse_id=sb.warehouse_id WHERE $w GROUP BY $supplierExpr, party ORDER BY cost_value DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.items_count),0) items, COALESCE(SUM(x.qty_total),0) qty, COALESCE(SUM(x.cost_value),0) cost_value FROM (SELECT $supplierExpr AS supplier_key, COUNT(DISTINCT sb.item_id) items_count, SUM(sb.qty) qty_total, SUM(sb.qty*i.cost) cost_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id $supplierJoin WHERE $w GROUP BY $supplierExpr) x",
            $p, ['party'=>'المورد','items_count'=>'عدد الأصناف','qty_total'=>'إجمالي الرصيد','cost_value'=>'قيمة التكلفة','last_move_at'=>'آخر حركة'], ['عدد الموردين'=>'c','عدد الأصناف'=>'items','إجمالي الرصيد'=>'qty','قيمة المخزون'=>'cost_value']
        );
    }

    private function stockByWarehouse(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->stockBalanceWhere($f); $w = implode(' AND ', $where);
        $supplierJoin = $this->itemSupplierJoin('i');
        return $this->queryDataset(
            "SELECT COALESCE(w.name,'-') AS warehouse, COUNT(DISTINCT sb.item_id) AS items_count, COALESCE(SUM(sb.qty),0) AS qty_total, COALESCE(SUM(sb.qty*i.cost),0) AS cost_value, COALESCE(SUM(sb.qty*i.price),0) AS sale_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id LEFT JOIN warehouses w ON w.id=sb.warehouse_id $supplierJoin WHERE $w GROUP BY sb.warehouse_id, w.name ORDER BY cost_value DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.cost_value),0) cost_value, COALESCE(SUM(x.sale_value),0) sale_value FROM (SELECT sb.warehouse_id, SUM(sb.qty*i.cost) cost_value, SUM(sb.qty*i.price) sale_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id $supplierJoin WHERE $w GROUP BY sb.warehouse_id) x",
            $p, ['warehouse'=>'المخزن','items_count'=>'عدد الأصناف','qty_total'=>'إجمالي الكمية','cost_value'=>'قيمة التكلفة','sale_value'=>'قيمة البيع'], ['عدد المخازن'=>'c','قيمة التكلفة'=>'cost_value','قيمة البيع'=>'sale_value']
        );
    }

    private function stockMovement(array $f, int $limit, int $offset, bool $card): array
    {
        if ((int)($f['item_id'] ?? 0) <= 0) { return ['columns'=>['message'=>'تنبيه'],'rows'=>[['message'=>'اختر الصنف أولًا لعرض '.($card ? 'كارت الصنف' : 'حركة الصنف').'.']],'summary'=>['الحالة'=>'الصنف مطلوب'],'total_rows'=>1]; }
        $where = ['sm.item_id = :item_id']; $p = [':item_id'=>(int)$f['item_id']];
        $this->eqWhere($where, $p, 'sm.warehouse_id', 'warehouse_id', $f); $this->dateWhere($where, $p, 'sm.created_at', $f);
        if (($f['movement_type'] ?? '') !== '') { $where[] = 'sm.ref_type = :movement_type'; $p[':movement_type'] = $f['movement_type']; }
        if ($f['q'] !== '') { $where[] = '(sm.ref_type LIKE :q OR sm.ref_id LIKE :q OR w.name LIKE :q)'; $p[':q'] = '%' . $f['q'] . '%'; }
        $w = implode(' AND ', $where);
        $whFilter = (int)($f['warehouse_id'] ?? 0);
        $cols = $card && (int)($f['show_cost'] ?? 0) === 1 ? ['doc_date'=>'التاريخ','description'=>'البيان','qty_in'=>'وارد','qty_out'=>'منصرف','balance_after'=>'الرصيد','cost'=>'التكلفة'] : ['doc_date'=>'التاريخ','description'=>'البيان','qty_in'=>'وارد','qty_out'=>'منصرف','balance_after'=>'الرصيد'];
        return $this->queryDataset(
            "SELECT sm.created_at AS doc_date, CONCAT(sm.ref_type, ' #', sm.ref_id, ' - ', COALESCE(w.name,'-')) AS description, sm.qty_in, sm.qty_out, (SELECT COALESCE(SUM(s2.qty_in-s2.qty_out),0) FROM stock_moves s2 WHERE s2.item_id=sm.item_id AND s2.created_at <= sm.created_at AND ($whFilter = 0 OR s2.warehouse_id = $whFilter)) AS balance_after, sm.cost FROM stock_moves sm LEFT JOIN warehouses w ON w.id=sm.warehouse_id WHERE $w ORDER BY sm.created_at ASC, sm.id ASC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(sm.qty_in),0) total_in, COALESCE(SUM(sm.qty_out),0) total_out, COALESCE(SUM(sm.qty_in-sm.qty_out),0) net_qty FROM stock_moves sm LEFT JOIN warehouses w ON w.id=sm.warehouse_id WHERE $w",
            $p, $cols, ['عدد الحركات'=>'c','إجمالي الوارد'=>'total_in','إجمالي المنصرف'=>'total_out','صافي الحركة'=>'net_qty']
        );
    }

    private function inactiveItems(array $f, int $limit, int $offset): array
    {
        $days = max(1, (int)($f['inactive_days'] !== '' ? $f['inactive_days'] : 90)); [$where, $p] = $this->stockBalanceWhere($f);
        $supplierJoin = $this->itemSupplierJoin('i');
        $where[] = '(lm.last_move_at IS NULL OR lm.last_move_at < DATE_SUB(NOW(), INTERVAL '.$days.' DAY))'; $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(w.name,'-') AS warehouse, sb.qty, COALESCE(DATE(lm.last_move_at),'-') AS last_move_at, DATEDIFF(CURDATE(), COALESCE(DATE(lm.last_move_at), DATE(i.created_at))) AS inactive_days, (sb.qty*i.cost) AS cost_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id LEFT JOIN warehouses w ON w.id=sb.warehouse_id $supplierJoin LEFT JOIN (SELECT item_id, warehouse_id, MAX(created_at) last_move_at FROM stock_moves GROUP BY item_id, warehouse_id) lm ON lm.item_id=sb.item_id AND lm.warehouse_id=sb.warehouse_id WHERE $w ORDER BY inactive_days DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(sb.qty*i.cost),0) cost_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id $supplierJoin LEFT JOIN (SELECT item_id, warehouse_id, MAX(created_at) last_move_at FROM stock_moves GROUP BY item_id, warehouse_id) lm ON lm.item_id=sb.item_id AND lm.warehouse_id=sb.warehouse_id WHERE $w",
            $p, ['item_name'=>'الصنف','warehouse'=>'المخزن','qty'=>'الرصيد','last_move_at'=>'آخر حركة','inactive_days'=>'أيام الركود'], ['عدد الأصناف الراكدة'=>'c','قيمة المخزون الراكد'=>'cost_value']
        );
    }

    private function mostMovingItems(array $f, int $limit, int $offset): array
    {
        $where = ['1=1']; $p = []; $this->dateWhere($where, $p, 'sm.created_at', $f); $this->eqWhere($where, $p, 'sm.warehouse_id', 'warehouse_id', $f); $this->eqWhere($where, $p, 'sm.item_id', 'item_id', $f);
        if ($f['q'] !== '') { $where[] = '(i.name LIKE :q OR i.barcode LIKE :q OR sm.ref_type LIKE :q)'; $p[':q'] = '%' . $f['q'] . '%'; }
        $order = match (($f['movement_sort'] ?? 'total')) { 'in' => 'qty_in DESC', 'out' => 'qty_out DESC', default => 'movement_total DESC' };
        $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(SUM(sm.qty_in),0) AS qty_in, COALESCE(SUM(sm.qty_out),0) AS qty_out, COALESCE(SUM(sm.qty_in+sm.qty_out),0) AS movement_total, MAX(sm.created_at) AS last_move_at FROM stock_moves sm JOIN items i ON i.id=sm.item_id WHERE $w GROUP BY sm.item_id, i.name ORDER BY $order LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.movement_total),0) movement_total FROM (SELECT sm.item_id, SUM(sm.qty_in+sm.qty_out) movement_total FROM stock_moves sm JOIN items i ON i.id=sm.item_id WHERE $w GROUP BY sm.item_id) x",
            $p, ['item_name'=>'الصنف','qty_in'=>'الوارد','qty_out'=>'المنصرف','movement_total'=>'إجمالي الحركة','last_move_at'=>'آخر حركة'], ['عدد الأصناف'=>'c','إجمالي الحركة'=>'movement_total']
        );
    }

    private function reorderItems(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->stockBalanceWhere($f);
        $supplierJoin = $this->itemSupplierJoin('i'); $where[] = 'sb.qty <= COALESCE(i.reorder_point,0)'; $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(w.name,'-') AS warehouse, sb.qty, i.reorder_point, GREATEST(i.reorder_point - sb.qty, 0) AS required_qty FROM stock_balances sb JOIN items i ON i.id=sb.item_id LEFT JOIN warehouses w ON w.id=sb.warehouse_id $supplierJoin WHERE $w ORDER BY required_qty DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(GREATEST(i.reorder_point - sb.qty,0)),0) shortage FROM stock_balances sb JOIN items i ON i.id=sb.item_id $supplierJoin WHERE $w",
            $p, ['item_name'=>'الصنف','warehouse'=>'المخزن','qty'=>'الرصيد','reorder_point'=>'حد إعادة الطلب','required_qty'=>'الكمية المطلوبة'], ['عدد الأصناف الناقصة'=>'c','إجمالي العجز'=>'shortage']
        );
    }

    private function negativeItems(array $f, int $limit, int $offset): array
    {
        $f['negative_only'] = 1; [$where, $p] = $this->stockBalanceWhere($f);
        $supplierJoin = $this->itemSupplierJoin('i'); $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(w.name,'-') AS warehouse, sb.qty AS negative_qty, ABS(sb.qty*i.cost) AS shortage_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id LEFT JOIN warehouses w ON w.id=sb.warehouse_id $supplierJoin WHERE $w ORDER BY sb.qty ASC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(sb.qty),0) negative_qty, COALESCE(SUM(ABS(sb.qty*i.cost)),0) shortage_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id $supplierJoin WHERE $w",
            $p, ['item_name'=>'الصنف','warehouse'=>'المخزن','negative_qty'=>'الرصيد السالب','shortage_value'=>'قيمة العجز'], ['عدد الأصناف السالبة'=>'c','إجمالي الرصيد السالب'=>'negative_qty','قيمة العجز'=>'shortage_value']
        );
    }

    private function stockMovesByRef(array $f, int $limit, int $offset, string $refLike): array
    {
        $where = ['sm.ref_type LIKE :ref_like']; $p = [':ref_like'=>'%'.$refLike.'%']; $this->dateWhere($where, $p, 'sm.created_at', $f); $this->eqWhere($where, $p, 'sm.warehouse_id', 'warehouse_id', $f); $this->eqWhere($where, $p, 'sm.item_id', 'item_id', $f); $this->eqWhere($where, $p, 'sm.user_id', 'user_id', $f);
        if ($f['q'] !== '') { $where[]='(sm.ref_type LIKE :q OR sm.ref_id LIKE :q OR i.name LIKE :q OR w.name LIKE :q)'; $p[':q']='%'.$f['q'].'%'; } $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT sm.created_at AS doc_date, CONCAT(sm.ref_type,' #',sm.ref_id) AS doc_no, COALESCE(w.name,'-') AS warehouse, COALESCE(u.full_name,u.username,'-') AS user_name, COUNT(DISTINCT sm.item_id) AS items_count, COALESCE(SUM(sm.qty_in-sm.qty_out),0) AS net_qty FROM stock_moves sm LEFT JOIN warehouses w ON w.id=sm.warehouse_id LEFT JOIN users u ON u.id=sm.user_id LEFT JOIN items i ON i.id=sm.item_id WHERE $w GROUP BY sm.ref_type, sm.ref_id, sm.created_at, w.name, u.full_name, u.username ORDER BY sm.created_at DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.net_qty),0) net_qty FROM (SELECT sm.ref_type, sm.ref_id, SUM(sm.qty_in-sm.qty_out) net_qty FROM stock_moves sm LEFT JOIN items i ON i.id=sm.item_id LEFT JOIN warehouses w ON w.id=sm.warehouse_id WHERE $w GROUP BY sm.ref_type, sm.ref_id) x",
            $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم المستند','warehouse'=>'المخزن','user_name'=>'المستخدم','items_count'=>'عدد الأصناف','net_qty'=>'صافي التسوية'], ['عدد التسويات'=>'c','صافي الكمية'=>'net_qty']
        );
    }

    private function stockTransfers(array $f, int $limit, int $offset): array
    {
        return ['columns'=>['message'=>'تنبيه'],'rows'=>[['message'=>'لا توجد جداول تحويلات مخازن مستقلة في الدامب الحالي. يمكن تفعيل التقرير عند إضافة جدول تحويلات أو تمييز ref_type للتحويلات في stock_moves.']],'summary'=>['الحالة'=>'يحتاج جدول تحويلات'],'total_rows'=>1];
    }

    private function stockValue(array $f, int $limit, int $offset, string $mode): array
    {
        [$where, $p] = $this->stockBalanceWhere($f);
        $supplierJoin = $this->itemSupplierJoin('i'); $w = implode(' AND ', $where); $unit = $mode === 'sale' ? 'i.price' : 'i.cost'; $value = "(sb.qty*$unit)";
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, COALESCE(w.name,'-') AS warehouse, sb.qty, $unit AS unit_value, $value AS total_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id LEFT JOIN warehouses w ON w.id=sb.warehouse_id $supplierJoin WHERE $w ORDER BY total_value DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM($value),0) total_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id $supplierJoin WHERE $w",
            $p, ['item_name'=>'الصنف','warehouse'=>'المخزن','qty'=>'الرصيد','unit_value'=>($mode === 'sale' ? 'سعر البيع' : 'تكلفة الوحدة'),'total_value'=>($mode === 'sale' ? 'قيمة البيع' : 'قيمة التكلفة')], [($mode === 'sale' ? 'إجمالي قيمة البيع' : 'إجمالي قيمة التكلفة')=>'total_value','عدد السطور'=>'c']
        );
    }

    private function stockMargin(array $f, int $limit, int $offset): array
    {
        [$where, $p] = $this->stockBalanceWhere($f);
        $supplierJoin = $this->itemSupplierJoin('i');
        if (($f['margin_max'] ?? '') !== '') { $where[] = '(i.price - i.cost) <= :margin_max'; $p[':margin_max'] = (float)$f['margin_max']; } $w = implode(' AND ', $where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,'-') AS item_name, i.cost, i.price, (i.price-i.cost) AS margin, CASE WHEN i.price<>0 THEN ROUND(((i.price-i.cost)/i.price)*100,2) ELSE 0 END AS margin_percent FROM items i LEFT JOIN stock_balances sb ON sb.item_id=i.id $supplierJoin WHERE $w GROUP BY i.id, i.name, i.cost, i.price ORDER BY margin ASC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(AVG(i.price-i.cost),0) avg_margin, COALESCE(MIN(i.price-i.cost),0) min_margin FROM items i LEFT JOIN stock_balances sb ON sb.item_id=i.id $supplierJoin WHERE $w",
            $p, ['item_name'=>'الصنف','cost'=>'التكلفة','price'=>'سعر البيع','margin'=>'الهامش','margin_percent'=>'نسبة الهامش %'], ['عدد الأصناف'=>'c','متوسط الهامش'=>'avg_margin','أقل هامش'=>'min_margin']
        );
    }

    private function stock(array $f, int $limit, int $offset): array
    {
        $where=['1=1'];$p=[];$this->eqWhere($where,$p,'sb.warehouse_id','warehouse_id',$f);$this->eqWhere($where,$p,'sb.item_id','item_id',$f);if($f['q']!==''){$where[]='(i.name LIKE :q OR i.barcode LIKE :q OR i.brand LIKE :q)';$p[':q']='%'.$f['q'].'%';}$w=implode(' AND ',$where);
        return $this->queryDataset("SELECT COALESCE(w.name,'-') AS warehouse, i.name AS item_name, i.barcode, i.cost, i.price, sb.qty, (sb.qty*i.cost) AS cost_value, (sb.qty*i.price) AS sale_value, (i.price-i.cost) AS margin FROM stock_balances sb JOIN items i ON i.id=sb.item_id LEFT JOIN warehouses w ON w.id=sb.warehouse_id WHERE $w ORDER BY i.name ASC LIMIT $limit OFFSET $offset", "SELECT COUNT(*) c, COALESCE(SUM(sb.qty),0) qty, COALESCE(SUM(sb.qty*i.cost),0) cost_value, COALESCE(SUM(sb.qty*i.price),0) sale_value FROM stock_balances sb JOIN items i ON i.id=sb.item_id WHERE $w", $p, ['warehouse'=>'المخزن','item_name'=>'الصنف','barcode'=>'الباركود','cost'=>'التكلفة','price'=>'سعر البيع','qty'=>'الرصيد','cost_value'=>'قيمة التكلفة','sale_value'=>'قيمة البيع','margin'=>'الهامش'], ['عدد السطور'=>'c','إجمالي الكمية'=>'qty','قيمة التكلفة'=>'cost_value','قيمة البيع'=>'sale_value']);
    }

    private function financeReport(string $slug, string $group, array $f, int $limit, int $offset): ?array
    {
        return match ($slug) {
            'cashbox-ledger' => $this->cashboxLedger($f, $limit, $offset),
            'cashbox-balances' => $this->cashboxBalances($f, $limit, $offset),
            'customer-collections' => $this->customerCollections($f, $limit, $offset),
            'supplier-payments' => $this->supplierPaymentsReport($f, $limit, $offset),
            'cashbox-expenses' => $this->cashboxExpenses($f, $limit, $offset),
            'cashbox-transfers' => $this->cashboxTransfers($f, $limit, $offset),
            'cashbox-daily' => $this->cashboxDaily($f, $limit, $offset),
            'cashbox-shortage-surplus' => $this->notAvailableReport('تقرير عجز/زيادة الخزنة يحتاج جدول جرد/تسوية خزنة مستقل غير موجود في الدامب الحالي.'),
            'bank-balances' => $this->bankBalances($f, $limit, $offset),
            'bank-statement' => $this->bankStatement($f, $limit, $offset),
            'bank-deposits' => $this->bankTransactionsByType($f, $limit, $offset, 'in'),
            'bank-withdrawals' => $this->bankTransactionsByType($f, $limit, $offset, 'out'),
            'bank-cashbox-transfers' => $this->bankCashboxTransfers($f, $limit, $offset),
            'bank-daily' => $this->bankDaily($f, $limit, $offset),
            'received-checks','issued-checks','checks-due-today','checks-due-period','overdue-checks','returned-checks' => $this->checksReport($slug, $f, $limit, $offset),
            'check-lifecycle' => $this->checkLifecycle($f, $limit, $offset),
            'checks-by-status' => $this->checksByStatus($f, $limit, $offset),
            default => null,
        };
    }

    private function cashboxLedger(array $f, int $limit, int $offset): array
    {
        $where=['1=1']; $p=[];
        $this->dateWhere($where,$p,'ct.trx_date',$f); $this->eqWhere($where,$p,'ct.cashbox_id','cashbox_id',$f); $this->eqWhere($where,$p,'ct.user_id','user_id',$f); $this->amountWhere($where,$p,'ct.amount',$f);
        if ($f['q'] !== '') { $where[]='(cb.name LIKE :q OR ct.ref_type LIKE :q OR ct.notes LIKE :q OR CAST(ct.ref_id AS CHAR) LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT ct.trx_date AS doc_date, cb.name AS cashbox, CASE WHEN ct.trx_type='in' THEN 'وارد' ELSE 'منصرف' END AS trx_type, ct.amount, CONCAT(COALESCE(ct.ref_type,'-'),' #',COALESCE(ct.ref_id,'')) AS ref_doc, COALESCE(u.full_name,u.username,'-') AS user_name FROM cashbox_transactions ct LEFT JOIN cashboxes cb ON cb.id=ct.cashbox_id LEFT JOIN users u ON u.id=ct.user_id WHERE $w ORDER BY ct.trx_date DESC, ct.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(CASE WHEN ct.trx_type='in' THEN ct.amount ELSE 0 END),0) total_in, COALESCE(SUM(CASE WHEN ct.trx_type='out' THEN ct.amount ELSE 0 END),0) total_out FROM cashbox_transactions ct LEFT JOIN cashboxes cb ON cb.id=ct.cashbox_id WHERE $w",
            $p, ['doc_date'=>'التاريخ','cashbox'=>'الخزنة','trx_type'=>'النوع','amount'=>'المبلغ','ref_doc'=>'المستند','user_name'=>'المستخدم'], ['عدد الحركات'=>'c','إجمالي الوارد'=>'total_in','إجمالي المنصرف'=>'total_out']
        );
    }

    private function cashboxBalances(array $f, int $limit, int $offset): array
    {
        $where=['COALESCE(cb.is_active,1)=1']; $p=[]; $this->eqWhere($where,$p,'cb.id','cashbox_id',$f); if($f['q']!==''){$where[]='cb.name LIKE :q';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT cb.name AS cashbox, cb.opening_balance, COALESCE(SUM(CASE WHEN ct.trx_type='in' THEN ct.amount ELSE 0 END),0) AS total_in, COALESCE(SUM(CASE WHEN ct.trx_type='out' THEN ct.amount ELSE 0 END),0) AS total_out, (cb.opening_balance + COALESCE(SUM(CASE WHEN ct.trx_type='in' THEN ct.amount ELSE -ct.amount END),0)) AS balance FROM cashboxes cb LEFT JOIN cashbox_transactions ct ON ct.cashbox_id=cb.id WHERE $w GROUP BY cb.id, cb.name, cb.opening_balance ORDER BY cb.name LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.balance),0) total_balance FROM (SELECT cb.id, (cb.opening_balance + COALESCE(SUM(CASE WHEN ct.trx_type='in' THEN ct.amount ELSE -ct.amount END),0)) AS balance FROM cashboxes cb LEFT JOIN cashbox_transactions ct ON ct.cashbox_id=cb.id WHERE $w GROUP BY cb.id, cb.opening_balance) x",
            $p, ['cashbox'=>'الخزنة','opening_balance'=>'الرصيد الافتتاحي','total_in'=>'وارد','total_out'=>'منصرف','balance'=>'الرصيد الحالي'], ['عدد الخزائن'=>'c','إجمالي الأرصدة'=>'total_balance']
        );
    }

    private function customerCollections(array $f, int $limit, int $offset): array
    {
        $where=["cr.status='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'cr.receipt_date',$f); $this->eqWhere($where,$p,'cr.cashbox_id','cashbox_id',$f); $this->eqWhere($where,$p,'cr.bank_account_id','bank_account_id',$f); $this->eqWhere($where,$p,'COALESCE(cr.party_id,cr.customer_id)','party_id',$f); $this->amountWhere($where,$p,'cr.amount',$f); if($f['q']!==''){$where[]='(cr.receipt_no LIKE :q OR p.name LIKE :q OR cr.ref_no LIKE :q OR cr.notes LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT cr.receipt_date AS doc_date, cr.receipt_no AS doc_no, COALESCE(p.name,'-') AS party, COALESCE(cb.name,ba.name,'-') AS account_name, cr.pay_method, cr.amount FROM customer_receipts cr LEFT JOIN parties p ON p.id=COALESCE(cr.party_id,cr.customer_id) LEFT JOIN cashboxes cb ON cb.id=cr.cashbox_id LEFT JOIN bank_accounts ba ON ba.id=cr.bank_account_id WHERE $w ORDER BY cr.receipt_date DESC, cr.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(cr.amount),0) total FROM customer_receipts cr LEFT JOIN parties p ON p.id=COALESCE(cr.party_id,cr.customer_id) WHERE $w",
            $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم الإيصال','party'=>'العميل','account_name'=>'الخزنة/البنك','pay_method'=>'الطريقة','amount'=>'المبلغ'], ['عدد التحصيلات'=>'c','إجمالي التحصيلات'=>'total']
        );
    }

    private function supplierPaymentsReport(array $f, int $limit, int $offset): array
    {
        $where=["cp.status='POSTED'", "COALESCE(cp.supplier_id,cp.party_id,0) > 0"]; $p=[]; $this->dateWhere($where,$p,'cp.voucher_date',$f); $this->eqWhere($where,$p,'cp.cashbox_id','cashbox_id',$f); $this->eqWhere($where,$p,'cp.bank_account_id','bank_account_id',$f); $this->eqWhere($where,$p,'COALESCE(cp.supplier_id,cp.party_id)','party_id',$f); $this->amountWhere($where,$p,'cp.amount',$f); if($f['q']!==''){$where[]='(cp.voucher_no LIKE :q OR p.name LIKE :q OR cp.payee_name LIKE :q OR cp.ref_no LIKE :q OR cp.purpose LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT cp.voucher_date AS doc_date, cp.voucher_no AS doc_no, COALESCE(p.name,cp.payee_name,'-') AS party, COALESCE(cb.name,ba.name,'-') AS account_name, cp.pay_method, cp.amount FROM cash_payments cp LEFT JOIN parties p ON p.id=COALESCE(cp.supplier_id,cp.party_id) LEFT JOIN cashboxes cb ON cb.id=cp.cashbox_id LEFT JOIN bank_accounts ba ON ba.id=cp.bank_account_id WHERE $w ORDER BY cp.voucher_date DESC, cp.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(cp.amount),0) total FROM cash_payments cp LEFT JOIN parties p ON p.id=COALESCE(cp.supplier_id,cp.party_id) WHERE $w",
            $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم السند','party'=>'المورد','account_name'=>'الخزنة/البنك','pay_method'=>'الطريقة','amount'=>'المبلغ'], ['عدد المدفوعات'=>'c','إجمالي المدفوعات'=>'total']
        );
    }

    private function cashboxExpenses(array $f, int $limit, int $offset): array
    {
        $where=["cp.status='POSTED'", "(cp.payment_type='expense' OR cp.expense_item_id IS NOT NULL)", "cp.account_source_type='cashbox'"]; $p=[]; $this->dateWhere($where,$p,'cp.voucher_date',$f); $this->eqWhere($where,$p,'cp.cashbox_id','cashbox_id',$f); $this->eqWhere($where,$p,'cp.user_id','user_id',$f); $this->amountWhere($where,$p,'cp.amount',$f); if($f['q']!==''){$where[]='(cp.voucher_no LIKE :q OR ei.name LIKE :q OR cp.payee_name LIKE :q OR cp.purpose LIKE :q OR cp.notes LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT cp.voucher_date AS doc_date, cp.voucher_no AS doc_no, COALESCE(ei.name,cp.purpose,'-') AS expense_item, cb.name AS cashbox, cp.amount, COALESCE(u.full_name,u.username,'-') AS user_name FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN cashboxes cb ON cb.id=cp.cashbox_id LEFT JOIN users u ON u.id=cp.user_id WHERE $w ORDER BY cp.voucher_date DESC, cp.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(cp.amount),0) total FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id WHERE $w",
            $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم السند','expense_item'=>'البند','cashbox'=>'الخزنة','amount'=>'المبلغ','user_name'=>'المستخدم'], ['عدد المصروفات'=>'c','إجمالي المصروفات'=>'total']
        );
    }

    private function cashboxTransfers(array $f, int $limit, int $offset): array
    {
        $where=["ct.ref_type LIKE '%transfer%'"]; $p=[]; $this->dateWhere($where,$p,'ct.trx_date',$f); $this->eqWhere($where,$p,'ct.cashbox_id','cashbox_id',$f); if($f['q']!==''){$where[]='(cb.name LIKE :q OR ct.notes LIKE :q OR CAST(ct.ref_id AS CHAR) LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT ct.trx_date AS doc_date, CONCAT('TR-',ct.ref_id) AS doc_no, cb.name AS cashbox, CASE WHEN ct.trx_type='in' THEN 'وارد' ELSE 'منصرف' END AS trx_type, ct.amount, ct.notes FROM cashbox_transactions ct LEFT JOIN cashboxes cb ON cb.id=ct.cashbox_id WHERE $w ORDER BY ct.trx_date DESC, ct.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(ct.amount),0) total FROM cashbox_transactions ct LEFT JOIN cashboxes cb ON cb.id=ct.cashbox_id WHERE $w",
            $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم المستند','cashbox'=>'الخزنة','trx_type'=>'النوع','amount'=>'المبلغ','notes'=>'ملاحظات'], ['عدد التحويلات'=>'c','إجمالي المبالغ'=>'total']
        );
    }

    private function cashboxDaily(array $f, int $limit, int $offset): array
    {
        $where=['1=1']; $p=[]; $this->dateWhere($where,$p,'ct.trx_date',$f); $this->eqWhere($where,$p,'ct.cashbox_id','cashbox_id',$f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT DATE(ct.trx_date) AS doc_date, cb.name AS cashbox, COALESCE(SUM(CASE WHEN ct.trx_type='in' THEN ct.amount ELSE 0 END),0) AS total_in, COALESCE(SUM(CASE WHEN ct.trx_type='out' THEN ct.amount ELSE 0 END),0) AS total_out, COALESCE(SUM(CASE WHEN ct.trx_type='in' THEN ct.amount ELSE -ct.amount END),0) AS net_amount FROM cashbox_transactions ct LEFT JOIN cashboxes cb ON cb.id=ct.cashbox_id WHERE $w GROUP BY DATE(ct.trx_date), cb.name ORDER BY doc_date DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.net_amount),0) net_amount FROM (SELECT DATE(ct.trx_date), ct.cashbox_id, SUM(CASE WHEN ct.trx_type='in' THEN ct.amount ELSE -ct.amount END) net_amount FROM cashbox_transactions ct WHERE $w GROUP BY DATE(ct.trx_date), ct.cashbox_id) x",
            $p, ['doc_date'=>'اليوم','cashbox'=>'الخزنة','total_in'=>'وارد','total_out'=>'منصرف','net_amount'=>'الصافي'], ['عدد الأيام/الخزائن'=>'c','صافي الحركة'=>'net_amount']
        );
    }

    private function bankBalances(array $f, int $limit, int $offset): array
    {
        $where=['COALESCE(ba.is_active,1)=1']; $p=[]; $this->eqWhere($where,$p,'ba.id','bank_account_id',$f); if($f['q']!==''){$where[]='(ba.name LIKE :q OR ba.bank_name LIKE :q OR ba.account_no LIKE :q OR ba.iban LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT ba.name AS account_name, ba.bank_name, ba.account_no, ba.opening_balance, COALESCE(SUM(CASE WHEN bt.trx_type='in' THEN bt.amount ELSE 0 END),0) AS total_in, COALESCE(SUM(CASE WHEN bt.trx_type='out' THEN bt.amount ELSE 0 END),0) AS total_out, (ba.opening_balance + COALESCE(SUM(CASE WHEN bt.trx_type='in' THEN bt.amount ELSE -bt.amount END),0)) AS balance FROM bank_accounts ba LEFT JOIN bank_transactions bt ON bt.bank_account_id=ba.id WHERE $w GROUP BY ba.id, ba.name, ba.bank_name, ba.account_no, ba.opening_balance ORDER BY ba.name LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.balance),0) total_balance FROM (SELECT ba.id, (ba.opening_balance + COALESCE(SUM(CASE WHEN bt.trx_type='in' THEN bt.amount ELSE -bt.amount END),0)) balance FROM bank_accounts ba LEFT JOIN bank_transactions bt ON bt.bank_account_id=ba.id WHERE $w GROUP BY ba.id, ba.opening_balance) x",
            $p, ['account_name'=>'الحساب','bank_name'=>'البنك','account_no'=>'رقم الحساب','opening_balance'=>'الافتتاحي','total_in'=>'إيداعات','total_out'=>'مسحوبات','balance'=>'الرصيد'], ['عدد الحسابات'=>'c','إجمالي الأرصدة'=>'total_balance']
        );
    }

    private function bankStatement(array $f, int $limit, int $offset): array
    { return $this->bankTransactionsByType($f, $limit, $offset, ''); }

    private function bankTransactionsByType(array $f, int $limit, int $offset, string $type): array
    {
        $where=['1=1']; $p=[]; if($type!==''){$where[]='bt.trx_type=:bt_type';$p[':bt_type']=$type;} $this->dateWhere($where,$p,'bt.trx_date',$f); $this->eqWhere($where,$p,'bt.bank_account_id','bank_account_id',$f); $this->eqWhere($where,$p,'bt.user_id','user_id',$f); $this->amountWhere($where,$p,'bt.amount',$f); if($f['q']!==''){$where[]='(ba.name LIKE :q OR ba.bank_name LIKE :q OR bt.ref_no LIKE :q OR bt.notes LIKE :q OR bt.source_type LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT bt.trx_date AS doc_date, ba.name AS account_name, ba.bank_name, CASE WHEN bt.trx_type='in' THEN 'إيداع' ELSE 'سحب' END AS trx_type, bt.amount, bt.ref_no, bt.source_type FROM bank_transactions bt LEFT JOIN bank_accounts ba ON ba.id=bt.bank_account_id WHERE $w ORDER BY bt.trx_date DESC, bt.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(CASE WHEN bt.trx_type='in' THEN bt.amount ELSE 0 END),0) total_in, COALESCE(SUM(CASE WHEN bt.trx_type='out' THEN bt.amount ELSE 0 END),0) total_out FROM bank_transactions bt LEFT JOIN bank_accounts ba ON ba.id=bt.bank_account_id WHERE $w",
            $p, ['doc_date'=>'التاريخ','account_name'=>'الحساب','bank_name'=>'البنك','trx_type'=>'النوع','amount'=>'المبلغ','ref_no'=>'المرجع','source_type'=>'المصدر'], ['عدد الحركات'=>'c','إجمالي الإيداعات'=>'total_in','إجمالي المسحوبات'=>'total_out']
        );
    }

    private function bankCashboxTransfers(array $f, int $limit, int $offset): array
    {
        $where=["bt.source_type LIKE '%transfer%'"]; $p=[]; $this->dateWhere($where,$p,'bt.trx_date',$f); $this->eqWhere($where,$p,'bt.bank_account_id','bank_account_id',$f); if($f['q']!==''){$where[]='(ba.name LIKE :q OR ba.bank_name LIKE :q OR bt.ref_no LIKE :q OR bt.notes LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT bt.trx_date AS doc_date, ba.name AS account_name, CASE WHEN bt.trx_type='in' THEN 'إلى البنك' ELSE 'من البنك' END AS direction, bt.amount, bt.ref_no, bt.notes FROM bank_transactions bt LEFT JOIN bank_accounts ba ON ba.id=bt.bank_account_id WHERE $w ORDER BY bt.trx_date DESC, bt.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(bt.amount),0) total FROM bank_transactions bt LEFT JOIN bank_accounts ba ON ba.id=bt.bank_account_id WHERE $w",
            $p, ['doc_date'=>'التاريخ','account_name'=>'الحساب البنكي','direction'=>'الاتجاه','amount'=>'المبلغ','ref_no'=>'المرجع','notes'=>'ملاحظات'], ['عدد التحويلات'=>'c','إجمالي المبالغ'=>'total']
        );
    }

    private function bankDaily(array $f, int $limit, int $offset): array
    {
        $where=['1=1']; $p=[]; $this->dateWhere($where,$p,'bt.trx_date',$f); $this->eqWhere($where,$p,'bt.bank_account_id','bank_account_id',$f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT DATE(bt.trx_date) AS doc_date, ba.name AS account_name, COALESCE(SUM(CASE WHEN bt.trx_type='in' THEN bt.amount ELSE 0 END),0) AS deposits, COALESCE(SUM(CASE WHEN bt.trx_type='out' THEN bt.amount ELSE 0 END),0) AS withdrawals, COALESCE(SUM(CASE WHEN bt.trx_type='in' THEN bt.amount ELSE -bt.amount END),0) AS net_amount FROM bank_transactions bt LEFT JOIN bank_accounts ba ON ba.id=bt.bank_account_id WHERE $w GROUP BY DATE(bt.trx_date), ba.name ORDER BY doc_date DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.net_amount),0) net_amount FROM (SELECT DATE(bt.trx_date), bt.bank_account_id, SUM(CASE WHEN bt.trx_type='in' THEN bt.amount ELSE -bt.amount END) net_amount FROM bank_transactions bt WHERE $w GROUP BY DATE(bt.trx_date), bt.bank_account_id) x",
            $p, ['doc_date'=>'اليوم','account_name'=>'الحساب','deposits'=>'إيداعات','withdrawals'=>'مسحوبات','net_amount'=>'الصافي'], ['عدد الأيام/الحسابات'=>'c','صافي الحركة'=>'net_amount']
        );
    }

    private function checksReport(string $slug, array $f, int $limit, int $offset): array
    {
        $where=['1=1']; $p=[]; $dateField='c.receive_date';
        if (in_array($slug, ['checks-due-today','checks-due-period','overdue-checks'], true)) { $dateField='c.due_date'; }
        if ($slug === 'checks-due-today') { $where[]='c.due_date = :today'; $p[':today']=date('Y-m-d'); }
        elseif ($slug === 'overdue-checks') { $where[]='c.due_date < :today'; $p[':today']=date('Y-m-d'); $where[]="c.current_status IN ('received','endorsed')"; }
        else { $this->dateWhere($where,$p,$dateField,$f); }
        if ($slug === 'received-checks') { $where[]="c.current_holder_type IN ('company','customer')"; }
        if ($slug === 'issued-checks') { $where[]="(c.current_holder_type='supplier' OR c.current_status='endorsed')"; }
        if ($slug === 'returned-checks') { $where[]="c.current_status='returned'"; }
        if ($slug === 'checks-due-period') { $where[]="c.current_status IN ('received','endorsed')"; }
        $this->eqWhere($where,$p,'c.drawer_party_id','party_id',$f); $this->amountWhere($where,$p,'c.amount',$f); if($f['status']!==''){$where[]='c.current_status=:status';$p[':status']=$f['status'];} if($f['q']!==''){$where[]='(c.check_no LIKE :q OR c.bank_name LIKE :q OR p.name LIKE :q OR c.notes LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT c.receive_date AS doc_date, c.check_no, COALESCE(p.name,'-') AS party, c.bank_name, c.due_date, c.current_status, c.current_holder_type, c.amount FROM checks c LEFT JOIN parties p ON p.id=c.drawer_party_id WHERE $w ORDER BY c.due_date ASC, c.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(c.amount),0) total FROM checks c LEFT JOIN parties p ON p.id=c.drawer_party_id WHERE $w",
            $p, ['doc_date'=>'تاريخ الاستلام','check_no'=>'رقم الشيك','party'=>'الطرف','bank_name'=>'البنك','due_date'=>'الاستحقاق','current_status'=>'الحالة','current_holder_type'=>'الحائز','amount'=>'المبلغ'], ['عدد الشيكات'=>'c','إجمالي الشيكات'=>'total']
        );
    }

    private function checkLifecycle(array $f, int $limit, int $offset): array
    {
        $where=['1=1']; $p=[]; $this->dateWhere($where,$p,'cm.movement_date',$f); $this->eqWhere($where,$p,'cm.party_id','party_id',$f); if($f['q']!==''){$where[]='(c.check_no LIKE :q OR p.name LIKE :q OR cm.notes LIKE :q OR c.bank_name LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT cm.movement_date AS doc_date, c.check_no, cm.movement_type, COALESCE(p.name,'-') AS party, cm.amount, COALESCE(u.full_name,u.username,'-') AS user_name FROM check_movements cm JOIN checks c ON c.id=cm.check_id LEFT JOIN parties p ON p.id=cm.party_id LEFT JOIN users u ON u.id=cm.user_id WHERE $w ORDER BY cm.movement_date DESC, cm.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(cm.amount),0) total FROM check_movements cm JOIN checks c ON c.id=cm.check_id LEFT JOIN parties p ON p.id=cm.party_id WHERE $w",
            $p, ['doc_date'=>'التاريخ','check_no'=>'رقم الشيك','movement_type'=>'الحركة','party'=>'الطرف','amount'=>'المبلغ','user_name'=>'المستخدم'], ['عدد الحركات'=>'c','إجمالي المبالغ'=>'total']
        );
    }

    private function checksByStatus(array $f, int $limit, int $offset): array
    {
        $where=['1=1']; $p=[]; $this->dateWhere($where,$p,'c.receive_date',$f); if($f['q']!==''){$where[]='(c.bank_name LIKE :q OR c.check_no LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT c.current_status, COUNT(*) AS checks_count, COALESCE(SUM(c.amount),0) AS total_amount, MIN(c.due_date) AS nearest_due, MAX(c.due_date) AS last_due FROM checks c WHERE $w GROUP BY c.current_status ORDER BY total_amount DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.total_amount),0) total FROM (SELECT c.current_status, SUM(c.amount) total_amount FROM checks c WHERE $w GROUP BY c.current_status) x",
            $p, ['current_status'=>'الحالة','checks_count'=>'عدد الشيكات','total_amount'=>'الإجمالي','nearest_due'=>'أقرب استحقاق','last_due'=>'آخر استحقاق'], ['عدد الحالات'=>'c','إجمالي الشيكات'=>'total']
        );
    }

    private function notAvailableReport(string $message): array
    { return ['columns'=>['message'=>'تنبيه'],'rows'=>[['message'=>$message]],'summary'=>['الحالة'=>'يحتاج بيانات/جدول مستقل'],'total_rows'=>1]; }


    private function analyticsReport(string $slug, string $group, array $f, int $limit, int $offset): ?array
    {
        return match ($slug) {
            'expenses-period' => $this->expensesPeriod($f, $limit, $offset),
            'expenses-by-category' => $this->expensesByCategory($f, $limit, $offset),
            'expenses-by-user' => $this->expensesByUser($f, $limit, $offset),
            'expenses-daily-monthly' => $this->expensesDailyMonthly($f, $limit, $offset),
            'expenses-vs-sales' => $this->expensesVsSales($f, $limit, $offset),
            'gross-profit' => $this->grossProfit($f, $limit, $offset),
            'item-profit' => $this->itemProfit($f, $limit, $offset),
            'customer-profit' => $this->customerProfit($f, $limit, $offset),
            'invoice-profit' => $this->invoiceProfit($f, $limit, $offset),
            'warehouse-profit' => $this->warehouseProfit($f, $limit, $offset),
            'profit-after-expenses' => $this->profitAfterExpenses($f, $limit, $offset),
            'low-margin' => $this->lowMargin($f, $limit, $offset),
            'sales-tax' => $this->salesTax($f, $limit, $offset),
            'purchase-tax' => $this->purchaseTax($f, $limit, $offset),
            'net-tax' => $this->netTax($f, $limit, $offset),
            'discounts' => $this->discountsReport($f, $limit, $offset),
            'withholding-tax' => $this->withholdingTax($f, $limit, $offset),
            'daily-management-summary' => $this->dailyManagementSummary($f, $limit, $offset),
            'monthly-summary' => $this->monthlySummary($f, $limit, $offset),
            'kpi' => $this->kpiReport($f, $limit, $offset),
            'period-comparison' => $this->periodComparison($f, $limit, $offset),
            'sales-growth' => $this->salesGrowth($f, $limit, $offset),
            default => null,
        };
    }

    private function expenseBaseWhere(array $f, string $alias='cp'): array
    {
        $where=["$alias.payment_type='expense'", "UPPER(COALESCE($alias.status,'POSTED'))='POSTED'"]; $p=[];
        $this->dateWhere($where,$p,"$alias.voucher_date",$f);
        $this->eqWhere($where,$p,"$alias.cashbox_id",'cashbox_id',$f);
        $this->eqWhere($where,$p,"$alias.bank_account_id",'bank_account_id',$f);
        $this->eqWhere($where,$p,"$alias.user_id",'user_id',$f);
        $this->amountWhere($where,$p,"$alias.amount",$f);
        if ($f['q'] !== '') { $where[]="($alias.voucher_no LIKE :q OR $alias.payee_name LIKE :q OR $alias.purpose LIKE :q OR $alias.notes LIKE :q OR ei.name LIKE :q OR ec.name LIKE :q)"; $p[':q']='%'.$f['q'].'%'; }
        return [$where,$p];
    }

    private function expensesPeriod(array $f, int $limit, int $offset): array
    {
        [$where,$p]=$this->expenseBaseWhere($f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT cp.voucher_date AS doc_date, cp.voucher_no AS doc_no, COALESCE(ec.name,'غير مصنف') AS category, COALESCE(ei.name,cp.purpose,'-') AS expense_item, COALESCE(cb.name,ba.name,'-') AS account_name, cp.amount, COALESCE(u.full_name,u.username,'-') AS user_name FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN expense_categories ec ON ec.id=ei.category_id LEFT JOIN cashboxes cb ON cb.id=cp.cashbox_id LEFT JOIN bank_accounts ba ON ba.id=cp.bank_account_id LEFT JOIN users u ON u.id=cp.user_id WHERE $w ORDER BY cp.voucher_date DESC, cp.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(cp.amount),0) total, COALESCE(AVG(cp.amount),0) avg_amount FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN expense_categories ec ON ec.id=ei.category_id LEFT JOIN cashboxes cb ON cb.id=cp.cashbox_id LEFT JOIN bank_accounts ba ON ba.id=cp.bank_account_id WHERE $w",
            $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم السند','category'=>'البند','expense_item'=>'المصروف','account_name'=>'الحساب','amount'=>'المبلغ','user_name'=>'المستخدم'], ['عدد السندات'=>'c','إجمالي المصروفات'=>'total','متوسط المصروف'=>'avg_amount']
        );
    }

    private function expensesByCategory(array $f, int $limit, int $offset): array
    {
        [$where,$p]=$this->expenseBaseWhere($f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(ec.name,'غير مصنف') AS category, COUNT(cp.id) AS vouchers_count, COALESCE(SUM(cp.amount),0) AS total_amount, COALESCE(AVG(cp.amount),0) AS avg_amount, MAX(cp.voucher_date) AS last_expense FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN expense_categories ec ON ec.id=ei.category_id WHERE $w GROUP BY ec.id, ec.name ORDER BY total_amount DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.total_amount),0) total FROM (SELECT COALESCE(ec.id,0) id, SUM(cp.amount) total_amount FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN expense_categories ec ON ec.id=ei.category_id WHERE $w GROUP BY COALESCE(ec.id,0)) x",
            $p, ['category'=>'البند','vouchers_count'=>'عدد السندات','total_amount'=>'إجمالي المصروف','avg_amount'=>'متوسط السند','last_expense'=>'آخر مصروف'], ['عدد البنود'=>'c','إجمالي المصروفات'=>'total']
        );
    }

    private function expensesByUser(array $f, int $limit, int $offset): array
    {
        [$where,$p]=$this->expenseBaseWhere($f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(u.full_name,u.username,'-') AS user_name, COUNT(cp.id) AS vouchers_count, COALESCE(SUM(cp.amount),0) AS total_amount, MAX(cp.voucher_date) AS last_expense FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN expense_categories ec ON ec.id=ei.category_id LEFT JOIN users u ON u.id=cp.user_id WHERE $w GROUP BY cp.user_id, u.full_name, u.username ORDER BY total_amount DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.total_amount),0) total FROM (SELECT cp.user_id, SUM(cp.amount) total_amount FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN expense_categories ec ON ec.id=ei.category_id WHERE $w GROUP BY cp.user_id) x",
            $p, ['user_name'=>'المستخدم','vouchers_count'=>'عدد السندات','total_amount'=>'إجمالي المصروف','last_expense'=>'آخر مصروف'], ['عدد المستخدمين'=>'c','إجمالي المصروفات'=>'total']
        );
    }

    private function expensesDailyMonthly(array $f, int $limit, int $offset): array
    {
        [$where,$p]=$this->expenseBaseWhere($f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT DATE(cp.voucher_date) AS period_name, COUNT(cp.id) AS vouchers_count, COALESCE(SUM(cp.amount),0) AS total_amount FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN expense_categories ec ON ec.id=ei.category_id WHERE $w GROUP BY DATE(cp.voucher_date) ORDER BY period_name DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.total_amount),0) total FROM (SELECT DATE(cp.voucher_date), SUM(cp.amount) total_amount FROM cash_payments cp LEFT JOIN expense_items ei ON ei.id=cp.expense_item_id LEFT JOIN expense_categories ec ON ec.id=ei.category_id WHERE $w GROUP BY DATE(cp.voucher_date)) x",
            $p, ['period_name'=>'اليوم','vouchers_count'=>'عدد السندات','total_amount'=>'إجمالي المصروفات'], ['عدد الأيام'=>'c','إجمالي المصروفات'=>'total']
        );
    }

    private function expensesVsSales(array $f, int $limit, int $offset): array
    {
        $whereS=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($whereS,$p,'si.invoice_date',$f); $ws=implode(' AND ',$whereS);
        $whereE=["cp.payment_type='expense'", "UPPER(COALESCE(cp.status,'POSTED'))='POSTED'"]; $pe=[]; $this->dateWhere($whereE,$pe,'cp.voucher_date',$f); $we=implode(' AND ',$whereE);
        return $this->queryDataset(
            "SELECT d.period_name, COALESCE(s.sales_total,0) AS sales_total, COALESCE(e.expenses_total,0) AS expenses_total, (COALESCE(s.sales_total,0)-COALESCE(e.expenses_total,0)) AS difference_amount, CASE WHEN COALESCE(s.sales_total,0)>0 THEN ROUND((COALESCE(e.expenses_total,0)/s.sales_total)*100,2) ELSE 0 END AS expense_ratio FROM (SELECT DATE(si.invoice_date) period_name FROM sales_invoices si WHERE $ws UNION SELECT DATE(cp.voucher_date) period_name FROM cash_payments cp WHERE $we) d LEFT JOIN (SELECT DATE(si.invoice_date) period_name, SUM(si.grand_total) sales_total FROM sales_invoices si WHERE $ws GROUP BY DATE(si.invoice_date)) s ON s.period_name=d.period_name LEFT JOIN (SELECT DATE(cp.voucher_date) period_name, SUM(cp.amount) expenses_total FROM cash_payments cp WHERE $we GROUP BY DATE(cp.voucher_date)) e ON e.period_name=d.period_name ORDER BY d.period_name DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(sales_total),0) sales_total, COALESCE(SUM(expenses_total),0) expenses_total FROM (SELECT d.period_name, COALESCE(s.sales_total,0) sales_total, COALESCE(e.expenses_total,0) expenses_total FROM (SELECT DATE(si.invoice_date) period_name FROM sales_invoices si WHERE $ws UNION SELECT DATE(cp.voucher_date) period_name FROM cash_payments cp WHERE $we) d LEFT JOIN (SELECT DATE(si.invoice_date) period_name, SUM(si.grand_total) sales_total FROM sales_invoices si WHERE $ws GROUP BY DATE(si.invoice_date)) s ON s.period_name=d.period_name LEFT JOIN (SELECT DATE(cp.voucher_date) period_name, SUM(cp.amount) expenses_total FROM cash_payments cp WHERE $we GROUP BY DATE(cp.voucher_date)) e ON e.period_name=d.period_name) x",
            $p + $pe, ['period_name'=>'اليوم','sales_total'=>'المبيعات','expenses_total'=>'المصروفات','difference_amount'=>'الفرق','expense_ratio'=>'نسبة المصروف %'], ['عدد الأيام'=>'c','إجمالي المبيعات'=>'sales_total','إجمالي المصروفات'=>'expenses_total']
        );
    }

    private function grossProfit(array $f, int $limit, int $offset): array
    { return $this->profitByPeriod($f, $limit, $offset, 'day'); }

    private function profitByPeriod(array $f, int $limit, int $offset, string $mode): array
    {
        $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'si.invoice_date',$f); $this->eqWhere($where,$p,'si.warehouse_id','warehouse_id',$f); $w=implode(' AND ',$where);
        $period = $mode === 'month' ? "DATE_FORMAT(si.invoice_date,'%Y-%m')" : 'DATE(si.invoice_date)';
        return $this->queryDataset(
            "SELECT $period AS period_name, COUNT(DISTINCT si.id) AS invoices_count, COALESCE(SUM(sii.line_total),0) AS sales_total, COALESCE(SUM(sii.qty*sii.cost),0) AS cost_total, COALESCE(SUM(sii.line_total-(sii.qty*sii.cost)),0) AS profit_total, CASE WHEN SUM(sii.line_total)>0 THEN ROUND((SUM(sii.line_total-(sii.qty*sii.cost))/SUM(sii.line_total))*100,2) ELSE 0 END AS margin_percent FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id WHERE $w GROUP BY $period ORDER BY period_name DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) sales_total, COALESCE(SUM(x.cost_total),0) cost_total, COALESCE(SUM(x.profit_total),0) profit_total FROM (SELECT $period, SUM(sii.line_total) sales_total, SUM(sii.qty*sii.cost) cost_total, SUM(sii.line_total-(sii.qty*sii.cost)) profit_total FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id WHERE $w GROUP BY $period) x",
            $p, ['period_name'=>($mode === 'month' ? 'الشهر' : 'اليوم'),'invoices_count'=>'عدد الفواتير','sales_total'=>'المبيعات','cost_total'=>'التكلفة','profit_total'=>'الربح','margin_percent'=>'هامش %'], ['عدد الفترات'=>'c','إجمالي المبيعات'=>'sales_total','إجمالي التكلفة'=>'cost_total','إجمالي الربح'=>'profit_total']
        );
    }

    private function itemProfit(array $f, int $limit, int $offset): array
    {
        $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'si.invoice_date',$f); $this->eqWhere($where,$p,'sii.item_id','item_id',$f); if($f['q']!==''){$where[]='(i.name LIKE :q OR i.barcode LIKE :q)';$p[':q']='%'.$f['q'].'%';} $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,sii.item_name,'-') AS item_name, COALESCE(SUM(sii.qty),0) AS qty_total, COALESCE(SUM(sii.line_total),0) AS sales_total, COALESCE(SUM(sii.qty*sii.cost),0) AS cost_total, COALESCE(SUM(sii.line_total-(sii.qty*sii.cost)),0) AS profit_total, CASE WHEN SUM(sii.line_total)>0 THEN ROUND((SUM(sii.line_total-(sii.qty*sii.cost))/SUM(sii.line_total))*100,2) ELSE 0 END AS margin_percent FROM sales_items sii JOIN sales_invoices si ON si.id=sii.invoice_id LEFT JOIN items i ON i.id=sii.item_id WHERE $w GROUP BY sii.item_id, i.name, sii.item_name ORDER BY profit_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) sales_total, COALESCE(SUM(x.profit_total),0) profit_total FROM (SELECT sii.item_id, SUM(sii.line_total) sales_total, SUM(sii.line_total-(sii.qty*sii.cost)) profit_total FROM sales_items sii JOIN sales_invoices si ON si.id=sii.invoice_id LEFT JOIN items i ON i.id=sii.item_id WHERE $w GROUP BY sii.item_id) x",
            $p, ['item_name'=>'الصنف','qty_total'=>'الكمية','sales_total'=>'المبيعات','cost_total'=>'التكلفة','profit_total'=>'الربح','margin_percent'=>'هامش %'], ['عدد الأصناف'=>'c','إجمالي المبيعات'=>'sales_total','إجمالي الربح'=>'profit_total']
        );
    }

    private function customerProfit(array $f, int $limit, int $offset): array
    {
        $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'si.invoice_date',$f); $this->eqWhere($where,$p,'si.party_id','party_id',$f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(p.name,'عميل نقدي') AS customer, COUNT(DISTINCT si.id) AS invoices_count, COALESCE(SUM(sii.line_total),0) AS sales_total, COALESCE(SUM(sii.qty*sii.cost),0) AS cost_total, COALESCE(SUM(sii.line_total-(sii.qty*sii.cost)),0) AS profit_total FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id, p.name ORDER BY profit_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) sales_total, COALESCE(SUM(x.profit_total),0) profit_total FROM (SELECT si.party_id, SUM(sii.line_total) sales_total, SUM(sii.line_total-(sii.qty*sii.cost)) profit_total FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.party_id) x",
            $p, ['customer'=>'العميل','invoices_count'=>'عدد الفواتير','sales_total'=>'المبيعات','cost_total'=>'التكلفة','profit_total'=>'الربح'], ['عدد العملاء'=>'c','إجمالي المبيعات'=>'sales_total','إجمالي الربح'=>'profit_total']
        );
    }

    private function invoiceProfit(array $f, int $limit, int $offset): array
    {
        $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'si.invoice_date',$f); $this->eqWhere($where,$p,'si.party_id','party_id',$f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT si.invoice_date AS doc_date, si.invoice_no AS doc_no, COALESCE(p.name,'عميل نقدي') AS customer, si.grand_total AS sales_total, COALESCE(SUM(sii.qty*sii.cost),0) AS cost_total, (si.grand_total-COALESCE(SUM(sii.qty*sii.cost),0)) AS profit_total FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.id, si.invoice_date, si.invoice_no, p.name, si.grand_total ORDER BY si.invoice_date DESC, si.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) sales_total, COALESCE(SUM(x.profit_total),0) profit_total FROM (SELECT si.id, si.grand_total sales_total, (si.grand_total-COALESCE(SUM(sii.qty*sii.cost),0)) profit_total FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id LEFT JOIN parties p ON p.id=si.party_id WHERE $w GROUP BY si.id, si.grand_total) x",
            $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم الفاتورة','customer'=>'العميل','sales_total'=>'المبيعات','cost_total'=>'التكلفة','profit_total'=>'الربح'], ['عدد الفواتير'=>'c','إجمالي المبيعات'=>'sales_total','إجمالي الربح'=>'profit_total']
        );
    }

    private function warehouseProfit(array $f, int $limit, int $offset): array
    {
        $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'si.invoice_date',$f); $this->eqWhere($where,$p,'si.warehouse_id','warehouse_id',$f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(w.name,'-') AS warehouse, COUNT(DISTINCT si.id) AS invoices_count, COALESCE(SUM(sii.line_total),0) AS sales_total, COALESCE(SUM(sii.qty*sii.cost),0) AS cost_total, COALESCE(SUM(sii.line_total-(sii.qty*sii.cost)),0) AS profit_total FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id LEFT JOIN warehouses w ON w.id=si.warehouse_id WHERE $w GROUP BY si.warehouse_id, w.name ORDER BY profit_total DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) sales_total, COALESCE(SUM(x.profit_total),0) profit_total FROM (SELECT si.warehouse_id, SUM(sii.line_total) sales_total, SUM(sii.line_total-(sii.qty*sii.cost)) profit_total FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id LEFT JOIN warehouses w ON w.id=si.warehouse_id WHERE $w GROUP BY si.warehouse_id) x",
            $p, ['warehouse'=>'المخزن','invoices_count'=>'عدد الفواتير','sales_total'=>'المبيعات','cost_total'=>'التكلفة','profit_total'=>'الربح'], ['عدد المخازن'=>'c','إجمالي المبيعات'=>'sales_total','إجمالي الربح'=>'profit_total']
        );
    }

    private function profitAfterExpenses(array $f, int $limit, int $offset): array
    {
        $base = $this->grossProfit($f, $limit, $offset); $expenses = $this->expenseTotal($f); $rows=[];
        foreach ($base['rows'] as $r) { $rows[] = $r + ['expenses_total'=>0, 'net_profit'=>(float)($r['profit_total'] ?? 0)]; }
        if ($rows) { $rows[0]['expenses_total']=$expenses; $rows[0]['net_profit']=(float)$rows[0]['profit_total']-$expenses; }
        $summary=$base['summary']; $summary['إجمالي المصروفات']=nf($expenses); $summary['صافي الربح بعد المصروفات']=nf((float)str_replace(',','',$summary['إجمالي الربح'] ?? '0')-$expenses);
        return ['columns'=>['period_name'=>'اليوم','sales_total'=>'المبيعات','cost_total'=>'التكلفة','profit_total'=>'الربح الإجمالي','expenses_total'=>'المصروفات','net_profit'=>'صافي الربح'],'rows'=>$rows,'summary'=>$summary,'total_rows'=>$base['total_rows']];
    }

    private function lowMargin(array $f, int $limit, int $offset): array
    {
        $max = ($f['margin_max'] ?? '') !== '' ? (float)$f['margin_max'] : 10.0;
        $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[':max_margin'=>$max];
        $this->dateWhere($where,$p,'si.invoice_date',$f); $this->eqWhere($where,$p,'sii.item_id','item_id',$f);
        if($f['q']!==''){$where[]='(i.name LIKE :q OR i.barcode LIKE :q)';$p[':q']='%'.$f['q'].'%';}
        $where[] = "CASE WHEN SUM(sii.line_total)>0 THEN ((SUM(sii.line_total-(sii.qty*sii.cost))/SUM(sii.line_total))*100) ELSE 0 END <= :max_margin";
        $baseWhere = $where; $having = array_pop($baseWhere); $w=implode(' AND ',$baseWhere);
        return $this->queryDataset(
            "SELECT COALESCE(i.name,sii.item_name,'-') AS item_name, COALESCE(SUM(sii.line_total),0) AS sales_total, COALESCE(SUM(sii.qty*sii.cost),0) AS cost_total, COALESCE(SUM(sii.line_total-(sii.qty*sii.cost)),0) AS profit_total, CASE WHEN SUM(sii.line_total)>0 THEN ROUND((SUM(sii.line_total-(sii.qty*sii.cost))/SUM(sii.line_total))*100,2) ELSE 0 END AS margin_percent FROM sales_items sii JOIN sales_invoices si ON si.id=sii.invoice_id LEFT JOIN items i ON i.id=sii.item_id WHERE $w GROUP BY sii.item_id, i.name, sii.item_name HAVING $having ORDER BY margin_percent ASC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.profit_total),0) profit_total, COALESCE(AVG(x.margin_percent),0) avg_margin FROM (SELECT sii.item_id, SUM(sii.line_total-(sii.qty*sii.cost)) profit_total, CASE WHEN SUM(sii.line_total)>0 THEN ((SUM(sii.line_total-(sii.qty*sii.cost))/SUM(sii.line_total))*100) ELSE 0 END margin_percent FROM sales_items sii JOIN sales_invoices si ON si.id=sii.invoice_id LEFT JOIN items i ON i.id=sii.item_id WHERE $w GROUP BY sii.item_id HAVING margin_percent <= :max_margin) x",
            $p, ['item_name'=>'الصنف','sales_total'=>'المبيعات','cost_total'=>'التكلفة','profit_total'=>'الربح','margin_percent'=>'هامش %'], ['عدد الأصناف'=>'c','إجمالي الربح'=>'profit_total','متوسط الهامش'=>'avg_margin']
        );
    }

    private function salesTax(array $f, int $limit, int $offset): array
    {
        $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'", 'COALESCE(si.vat_amount,0)<>0']; $p=[]; $this->dateWhere($where,$p,'si.invoice_date',$f); $this->eqWhere($where,$p,'si.party_id','party_id',$f); $w=implode(' AND ',$where);
        return $this->queryDataset("SELECT si.invoice_date AS doc_date, si.invoice_no AS doc_no, COALESCE(p.name,'عميل نقدي') AS party, si.subtotal, si.discount_amount, si.vat_amount, si.grand_total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w ORDER BY si.invoice_date DESC LIMIT $limit OFFSET $offset", "SELECT COUNT(*) c, COALESCE(SUM(si.vat_amount),0) tax_total, COALESCE(SUM(si.grand_total),0) total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $w", $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم الفاتورة','party'=>'العميل','subtotal'=>'قبل الضريبة','discount_amount'=>'الخصم','vat_amount'=>'الضريبة','grand_total'=>'الإجمالي'], ['عدد الفواتير'=>'c','إجمالي الضريبة'=>'tax_total','إجمالي الفواتير'=>'total']);
    }

    private function purchaseTax(array $f, int $limit, int $offset): array
    {
        $where=["UPPER(COALESCE(pi.status,'POSTED'))='POSTED'", 'COALESCE(pi.tax_amount,0)<>0']; $p=[]; $this->dateWhere($where,$p,'pi.inv_date',$f); $this->eqWhere($where,$p,'COALESCE(pi.party_id,pi.supplier_id)','party_id',$f); $w=implode(' AND ',$where);
        return $this->queryDataset("SELECT pi.inv_date AS doc_date, pi.inv_no AS doc_no, COALESCE(p.name,'-') AS party, pi.subtotal, pi.discount_amount, pi.tax_amount, pi.total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $w ORDER BY pi.inv_date DESC LIMIT $limit OFFSET $offset", "SELECT COUNT(*) c, COALESCE(SUM(pi.tax_amount),0) tax_total, COALESCE(SUM(pi.total),0) total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $w", $p, ['doc_date'=>'التاريخ','doc_no'=>'رقم الفاتورة','party'=>'المورد','subtotal'=>'قبل الضريبة','discount_amount'=>'الخصم','tax_amount'=>'الضريبة','total'=>'الإجمالي'], ['عدد الفواتير'=>'c','إجمالي الضريبة'=>'tax_total','إجمالي الفواتير'=>'total']);
    }

    private function netTax(array $f, int $limit, int $offset): array
    {
        $rows=[]; $sales=$this->scalarSum("sales_invoices si", "si.vat_amount", "si.invoice_date", $f, "UPPER(COALESCE(si.status,'POSTED'))='POSTED'"); $purch=$this->scalarSum("purchase_invoices pi", "pi.tax_amount", "pi.inv_date", $f, "UPPER(COALESCE(pi.status,'POSTED'))='POSTED'"); $rows[]=['statement'=>'ضريبة المبيعات','amount'=>$sales]; $rows[]=['statement'=>'ضريبة المشتريات','amount'=>$purch]; $rows[]=['statement'=>'صافي الضريبة','amount'=>$sales-$purch]; return ['columns'=>['statement'=>'البيان','amount'=>'القيمة'],'rows'=>$rows,'summary'=>['ضريبة المبيعات'=>nf($sales),'ضريبة المشتريات'=>nf($purch),'صافي الضريبة'=>nf($sales-$purch)],'total_rows'=>3];
    }

    private function discountsReport(array $f, int $limit, int $offset): array
    {
        $whereS=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'", 'COALESCE(si.discount_amount,0)>0']; $p=[];
        $this->dateWhere($whereS,$p,'si.invoice_date',$f); $this->eqWhere($whereS,$p,'si.party_id','party_id',$f); $ws=implode(' AND ',$whereS);
        $whereP=["UPPER(COALESCE(pi.status,'POSTED'))='POSTED'", 'COALESCE(pi.discount_amount,0)>0'];
        $this->dateWhere($whereP,$p,'pi.inv_date',$f); $this->eqWhere($whereP,$p,'COALESCE(pi.party_id,pi.supplier_id)','party_id',$f); $wp=implode(' AND ',$whereP);
        return $this->queryDataset(
            "SELECT * FROM (SELECT si.invoice_date AS doc_date, si.invoice_no AS doc_no, 'خصم مبيعات' AS discount_type, COALESCE(p.name,'عميل نقدي') AS party, si.discount_amount, si.grand_total AS total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $ws UNION ALL SELECT pi.inv_date AS doc_date, pi.inv_no AS doc_no, 'خصم مشتريات' AS discount_type, COALESCE(pp.name,'-') AS party, pi.discount_amount, pi.total AS total FROM purchase_invoices pi LEFT JOIN parties pp ON pp.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $wp) x ORDER BY doc_date DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.discount_amount),0) discount_total, COALESCE(SUM(x.total),0) total FROM (SELECT si.discount_amount, si.grand_total AS total FROM sales_invoices si LEFT JOIN parties p ON p.id=si.party_id WHERE $ws UNION ALL SELECT pi.discount_amount, pi.total AS total FROM purchase_invoices pi LEFT JOIN parties pp ON pp.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $wp) x",
            $p, ['doc_date'=>'التاريخ','doc_no'=>'المستند','discount_type'=>'النوع','party'=>'الطرف','discount_amount'=>'الخصم','total'=>'الإجمالي'], ['عدد المستندات'=>'c','إجمالي الخصومات'=>'discount_total','إجمالي المستندات'=>'total']
        );
    }

    private function withholdingTax(array $f, int $limit, int $offset): array
    {
        $where=["UPPER(COALESCE(pi.status,'POSTED'))='POSTED'", 'COALESCE(pi.wht_amount,0)>0']; $p=[]; $this->dateWhere($where,$p,'pi.inv_date',$f); $this->eqWhere($where,$p,'COALESCE(pi.party_id,pi.supplier_id)','party_id',$f); $w=implode(' AND ',$where);
        return $this->queryDataset("SELECT pi.inv_date AS doc_date, pi.inv_no AS doc_no, COALESCE(p.name,'-') AS supplier, pi.wht_rate, pi.wht_amount, pi.wht_notice_no, pi.total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $w ORDER BY pi.inv_date DESC LIMIT $limit OFFSET $offset", "SELECT COUNT(*) c, COALESCE(SUM(pi.wht_amount),0) wht_total, COALESCE(SUM(pi.total),0) total FROM purchase_invoices pi LEFT JOIN parties p ON p.id=COALESCE(pi.party_id,pi.supplier_id) WHERE $w", $p, ['doc_date'=>'التاريخ','doc_no'=>'فاتورة الشراء','supplier'=>'المورد','wht_rate'=>'النسبة','wht_amount'=>'خصم المنبع','wht_notice_no'=>'إشعار الخصم','total'=>'الإجمالي'], ['عدد الفواتير'=>'c','إجمالي خصم المنبع'=>'wht_total','إجمالي الفواتير'=>'total']);
    }

    private function dailyManagementSummary(array $f, int $limit, int $offset): array
    { return $this->managementSummaryByPeriod($f, $limit, $offset, 'day'); }
    private function monthlySummary(array $f, int $limit, int $offset): array
    { return $this->managementSummaryByPeriod($f, $limit, $offset, 'month'); }

    private function managementSummaryByPeriod(array $f, int $limit, int $offset, string $mode): array
    {
        $periodS = $mode==='month' ? "DATE_FORMAT(si.invoice_date,'%Y-%m')" : 'DATE(si.invoice_date)';
        $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'si.invoice_date',$f); $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT $periodS AS period_name, COUNT(DISTINCT si.id) AS invoices_count, COALESCE(SUM(si.grand_total),0) AS sales_total, COALESCE(SUM(si.paid_amount),0) AS collections, COALESCE(SUM(si.due_amount),0) AS receivables FROM sales_invoices si WHERE $w GROUP BY $periodS ORDER BY period_name DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.sales_total),0) sales_total, COALESCE(SUM(x.collections),0) collections, COALESCE(SUM(x.receivables),0) receivables FROM (SELECT $periodS, SUM(si.grand_total) sales_total, SUM(si.paid_amount) collections, SUM(si.due_amount) receivables FROM sales_invoices si WHERE $w GROUP BY $periodS) x",
            $p, ['period_name'=>($mode==='month'?'الشهر':'اليوم'),'invoices_count'=>'عدد الفواتير','sales_total'=>'المبيعات','collections'=>'التحصيل','receivables'=>'آجل/متبقي'], ['عدد الفترات'=>'c','إجمالي المبيعات'=>'sales_total','إجمالي التحصيل'=>'collections','إجمالي المتبقي'=>'receivables']
        );
    }

    private function kpiReport(array $f, int $limit, int $offset): array
    {
        $sales=$this->scalarSum('sales_invoices si','si.grand_total','si.invoice_date',$f,"UPPER(COALESCE(si.status,'POSTED'))='POSTED'"); $profit=$this->scalarSalesProfit($f); $expenses=$this->expenseTotal($f); $purch=$this->scalarSum('purchase_invoices pi','pi.total','pi.inv_date',$f,"UPPER(COALESCE(pi.status,'POSTED'))='POSTED'");
        $rows=[['metric'=>'إجمالي المبيعات','value'=>$sales],['metric'=>'إجمالي المشتريات','value'=>$purch],['metric'=>'الربح الإجمالي','value'=>$profit],['metric'=>'المصروفات','value'=>$expenses],['metric'=>'صافي الربح','value'=>$profit-$expenses],['metric'=>'هامش الربح %','value'=>$sales>0?round(($profit/$sales)*100,2):0]];
        return ['columns'=>['metric'=>'المؤشر','value'=>'القيمة'],'rows'=>$rows,'summary'=>['إجمالي المبيعات'=>nf($sales),'الربح الإجمالي'=>nf($profit),'المصروفات'=>nf($expenses),'صافي الربح'=>nf($profit-$expenses)],'total_rows'=>count($rows)];
    }

    private function periodComparison(array $f, int $limit, int $offset): array
    {
        $from=strtotime($f['from'] ?: date('Y-m-01')); $to=strtotime($f['to'] ?: date('Y-m-d')); $days=max(1,(int)(($to-$from)/86400)+1); $prevTo=date('Y-m-d',$from-86400); $prevFrom=date('Y-m-d',strtotime($prevTo.' -'.($days-1).' days')); $cur=$this->scalarSum('sales_invoices si','si.grand_total','si.invoice_date',$f,"UPPER(COALESCE(si.status,'POSTED'))='POSTED'"); $prevF=$f; $prevF['from']=$prevFrom; $prevF['to']=$prevTo; $prev=$this->scalarSum('sales_invoices si','si.grand_total','si.invoice_date',$prevF,"UPPER(COALESCE(si.status,'POSTED'))='POSTED'"); $diff=$cur-$prev;
        return ['columns'=>['period'=>'الفترة','sales_total'=>'المبيعات','difference'=>'الفرق'],'rows'=>[['period'=>'الفترة الحالية','sales_total'=>$cur,'difference'=>$diff],['period'=>'الفترة السابقة '.$prevFrom.' - '.$prevTo,'sales_total'=>$prev,'difference'=>0]],'summary'=>['مبيعات الفترة الحالية'=>nf($cur),'مبيعات الفترة السابقة'=>nf($prev),'الفرق'=>nf($diff),'نسبة النمو %'=>nf($prev>0?($diff/$prev)*100:0)],'total_rows'=>2];
    }

    private function salesGrowth(array $f, int $limit, int $offset): array
    { return $this->managementSummaryByPeriod($f, $limit, $offset, 'month'); }

    private function scalarSum(string $fromTable, string $expr, string $dateField, array $f, string $extraWhere='1=1'): float
    { $where=[$extraWhere]; $p=[]; $this->dateWhere($where,$p,$dateField,$f); $st=$this->pdo->prepare('SELECT COALESCE(SUM('.$expr.'),0) FROM '.$fromTable.' WHERE '.implode(' AND ',$where)); $st->execute($p); return (float)($st->fetchColumn() ?: 0); }
    private function scalarSalesProfit(array $f): float
    { $where=["UPPER(COALESCE(si.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'si.invoice_date',$f); $st=$this->pdo->prepare('SELECT COALESCE(SUM(sii.line_total-(sii.qty*sii.cost)),0) FROM sales_invoices si JOIN sales_items sii ON sii.invoice_id=si.id WHERE '.implode(' AND ',$where)); $st->execute($p); return (float)($st->fetchColumn() ?: 0); }
    private function expenseTotal(array $f): float
    { $where=["cp.payment_type='expense'", "UPPER(COALESCE(cp.status,'POSTED'))='POSTED'"]; $p=[]; $this->dateWhere($where,$p,'cp.voucher_date',$f); $st=$this->pdo->prepare('SELECT COALESCE(SUM(cp.amount),0) FROM cash_payments cp WHERE '.implode(' AND ',$where)); $st->execute($p); return (float)($st->fetchColumn() ?: 0); }

    private function expensesFallback(array $f, int $limit, int $offset): array { return $this->cashbox($f,$limit,$offset); }

    private function auditReport(string $slug, array $f, int $limit, int $offset): array
    {
        return match ($slug) {
            'user-activity' => $this->userActivityReport($f, $limit, $offset),
            'audit-log' => $this->auditLogReport($f, $limit, $offset),
            'sensitive-actions' => $this->sensitiveActionsReport($f, $limit, $offset),
            'failed-logins' => $this->failedLoginsReport($f, $limit, $offset),
            'user-permissions' => $this->userPermissionsReport($f, $limit, $offset),
            'price-changes' => $this->priceChangesReport($f, $limit, $offset),
            'stock-changes' => $this->stockChangesReport($f, $limit, $offset),
            default => $this->unavailableReport('تقرير غير معروف في مجموعة التشغيل والرقابة.'),
        };
    }

    private function auditLogReport(array $f, int $limit, int $offset): array
    {
        if (!$this->tableExists('audit_logs')) { return $this->unavailableReport('جدول audit_logs غير موجود. يحتاج التقرير إلى تفعيل سجل العمليات أولًا.'); }
        $where=['1=1']; $p=[];
        $this->dateWhere($where,$p,'al.created_at',$f); $this->eqWhere($where,$p,'al.user_id','user_id',$f);
        if ($f['status'] !== '') { $where[]='al.action = :action_status'; $p[':action_status']=$f['status']; }
        if ($f['q'] !== '') { $where[]='(al.action LIKE :q OR al.entity LIKE :q OR al.ip_address LIKE :q OR al.meta_json LIKE :q OR COALESCE(u.full_name,u.username,\'\') LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT al.created_at AS action_date, COALESCE(u.full_name,u.username,'النظام') AS user_name, al.action, COALESCE(al.entity,'-') AS entity_name, COALESCE(al.entity_id,'-') AS entity_id, COALESCE(al.ip_address,'-') AS ip_address, LEFT(REPLACE(REPLACE(COALESCE(al.meta_json,''), CHAR(10), ' '), CHAR(13), ' '), 180) AS details FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w ORDER BY al.created_at DESC, al.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COUNT(DISTINCT al.user_id) users_count, COUNT(DISTINCT al.action) actions_count FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w",
            $p,
            ['action_date'=>'التاريخ','user_name'=>'المستخدم','action'=>'العملية','entity_name'=>'الجدول/الكيان','entity_id'=>'رقم المرجع','ip_address'=>'IP','details'=>'تفاصيل مختصرة'],
            ['عدد العمليات'=>'c','عدد المستخدمين'=>'users_count','أنواع العمليات'=>'actions_count']
        );
    }

    private function userActivityReport(array $f, int $limit, int $offset): array
    {
        if (!$this->tableExists('audit_logs')) { return $this->unavailableReport('جدول audit_logs غير موجود. يحتاج التقرير إلى تفعيل سجل العمليات أولًا.'); }
        $where=['1=1']; $p=[]; $this->dateWhere($where,$p,'al.created_at',$f); $this->eqWhere($where,$p,'al.user_id','user_id',$f);
        if ($f['q'] !== '') { $where[]='(al.action LIKE :q OR COALESCE(u.full_name,u.username,\'\') LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(u.full_name,u.username,'النظام') AS user_name, COUNT(*) AS actions_count, SUM(CASE WHEN al.action LIKE 'LOGIN%' THEN 1 ELSE 0 END) AS login_count, SUM(CASE WHEN al.action REGEXP 'DELETE|CANCEL|WIPE|ROLE|PERMS|BACKUP' THEN 1 ELSE 0 END) AS sensitive_count, MAX(al.created_at) AS last_action FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w GROUP BY al.user_id, user_name ORDER BY actions_count DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(x.actions_count),0) total_actions, COALESCE(SUM(x.sensitive_count),0) total_sensitive FROM (SELECT al.user_id, COUNT(*) actions_count, SUM(CASE WHEN al.action REGEXP 'DELETE|CANCEL|WIPE|ROLE|PERMS|BACKUP' THEN 1 ELSE 0 END) sensitive_count FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w GROUP BY al.user_id) x",
            $p,
            ['user_name'=>'المستخدم','actions_count'=>'عدد العمليات','login_count'=>'دخول','sensitive_count'=>'عمليات حساسة','last_action'=>'آخر عملية'],
            ['عدد المستخدمين'=>'c','إجمالي العمليات'=>'total_actions','عمليات حساسة'=>'total_sensitive']
        );
    }

    private function sensitiveActionsReport(array $f, int $limit, int $offset): array
    {
        if (!$this->tableExists('audit_logs')) { return $this->unavailableReport('جدول audit_logs غير موجود. يحتاج التقرير إلى تفعيل سجل العمليات أولًا.'); }
        $where=["al.action REGEXP 'DELETE|CANCEL|WIPE|ROLE|PERMS|BACKUP|RETURN|SETTLE|REPLACE'"]; $p=[];
        $this->dateWhere($where,$p,'al.created_at',$f); $this->eqWhere($where,$p,'al.user_id','user_id',$f);
        if ($f['q'] !== '') { $where[]='(al.action LIKE :q OR al.entity LIKE :q OR al.meta_json LIKE :q OR COALESCE(u.full_name,u.username,\'\') LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT al.created_at AS action_date, COALESCE(u.full_name,u.username,'النظام') AS user_name, al.action, COALESCE(al.entity,'-') AS entity_name, COALESCE(al.entity_id,'-') AS entity_id, COALESCE(al.ip_address,'-') AS ip_address FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w ORDER BY al.created_at DESC, al.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COUNT(DISTINCT al.user_id) users_count FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w",
            $p,
            ['action_date'=>'التاريخ','user_name'=>'المستخدم','action'=>'العملية','entity_name'=>'الكيان','entity_id'=>'رقم المرجع','ip_address'=>'IP'],
            ['عدد العمليات'=>'c','عدد المستخدمين'=>'users_count']
        );
    }

    private function failedLoginsReport(array $f, int $limit, int $offset): array
    {
        if (!$this->tableExists('audit_logs') && !$this->tableExists('login_attempts')) { return $this->unavailableReport('لا توجد جداول تسجيل دخول فاشل. يحتاج التقرير إلى audit_logs أو login_attempts.'); }
        $parts=[]; $p=[];
        if ($this->tableExists('audit_logs')) {
            $where=["al.action IN ('LOGIN_FAIL','LOGIN_BLOCKED')"]; $this->dateWhere($where,$p,'al.created_at',$f);
            if ($f['q'] !== '') { $where[]='(al.meta_json LIKE :q OR al.ip_address LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
            $parts[]="SELECT al.created_at AS action_date, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(al.meta_json,'$.username')),'-') AS username, COALESCE(al.ip_address,'-') AS ip_address, al.action AS status_text, 1 AS attempts_count FROM audit_logs al WHERE ".implode(' AND ',$where);
        }
        if ($this->tableExists('login_attempts')) {
            $where=['1=1'];
            if ($f['from']!=='') { $where[]='la.last_attempt_at >= :from'; $p[':from']=$f['from'].' 00:00:00'; }
            if ($f['to']!=='') { $where[]='la.last_attempt_at <= :to'; $p[':to']=$f['to'].' 23:59:59'; }
            if ($f['q'] !== '') { $where[]='(la.username LIKE :q OR la.ip_address LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
            $parts[]="SELECT la.last_attempt_at AS action_date, la.username, la.ip_address, CASE WHEN la.locked_until IS NOT NULL AND la.locked_until > NOW() THEN 'LOCKED' ELSE 'FAILED' END AS status_text, la.attempts AS attempts_count FROM login_attempts la WHERE ".implode(' AND ',$where);
        }
        $union=implode(' UNION ALL ', $parts);
        return $this->queryDataset(
            "SELECT * FROM ($union) x ORDER BY action_date DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(attempts_count),0) attempts_total, COUNT(DISTINCT ip_address) ip_count FROM ($union) x",
            $p,
            ['action_date'=>'آخر محاولة','username'=>'اسم المستخدم','ip_address'=>'IP','status_text'=>'الحالة','attempts_count'=>'عدد المحاولات'],
            ['عدد السجلات'=>'c','إجمالي المحاولات'=>'attempts_total','عدد عناوين IP'=>'ip_count']
        );
    }

    private function userPermissionsReport(array $f, int $limit, int $offset): array
    {
        foreach (['users','user_roles','roles','role_permissions','permissions'] as $t) { if (!$this->tableExists($t)) { return $this->unavailableReport('تقرير صلاحيات المستخدمين يحتاج جداول users و roles و permissions.'); } }
        $where=['1=1']; $p=[]; $this->eqWhere($where,$p,'u.id','user_id',$f);
        if ($f['q'] !== '') { $where[]='(u.username LIKE :q OR u.full_name LIKE :q OR r.name LIKE :q OR p.name LIKE :q OR p.code LIKE :q OR p.module LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT COALESCE(u.full_name,u.username) AS user_name, CASE WHEN COALESCE(u.is_active,1)=1 THEN 'نشط' ELSE 'موقوف' END AS user_status, COALESCE(r.name,'بدون دور') AS role_name, COALESCE(p.module,'-') AS module_name, COUNT(DISTINCT p.id) AS permissions_count, LEFT(GROUP_CONCAT(DISTINCT p.name ORDER BY p.name SEPARATOR '، '), 220) AS permissions_sample FROM users u LEFT JOIN user_roles ur ON ur.user_id=u.id LEFT JOIN roles r ON r.id=ur.role_id LEFT JOIN role_permissions rp ON rp.role_id=r.id LEFT JOIN permissions p ON p.id=rp.permission_id WHERE $w GROUP BY u.id, user_name, user_status, r.id, role_name, module_name ORDER BY user_name, role_name, module_name LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COUNT(DISTINCT x.user_id) users_count, COALESCE(SUM(x.permissions_count),0) permissions_total FROM (SELECT u.id user_id, r.id role_id, p.module, COUNT(DISTINCT p.id) permissions_count FROM users u LEFT JOIN user_roles ur ON ur.user_id=u.id LEFT JOIN roles r ON r.id=ur.role_id LEFT JOIN role_permissions rp ON rp.role_id=r.id LEFT JOIN permissions p ON p.id=rp.permission_id WHERE $w GROUP BY u.id, r.id, p.module) x",
            $p,
            ['user_name'=>'المستخدم','user_status'=>'الحالة','role_name'=>'الدور','module_name'=>'الموديول','permissions_count'=>'عدد الصلاحيات','permissions_sample'=>'أمثلة صلاحيات'],
            ['عدد السطور'=>'c','عدد المستخدمين'=>'users_count','إجمالي الصلاحيات'=>'permissions_total']
        );
    }

    private function priceChangesReport(array $f, int $limit, int $offset): array
    {
        if (!$this->tableExists('audit_logs')) { return $this->unavailableReport('لا يوجد سجل عمليات لتتبع تعديلات الأسعار.'); }
        $where=["(al.action LIKE '%PRICE%' OR (al.entity='items' AND al.action LIKE '%UPDATE%'))"]; $p=[];
        $this->dateWhere($where,$p,'al.created_at',$f); $this->eqWhere($where,$p,'al.user_id','user_id',$f);
        if ($f['q'] !== '') { $where[]='(al.action LIKE :q OR al.meta_json LIKE :q OR COALESCE(u.full_name,u.username,\'\') LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        $countStmt=$this->pdo->prepare("SELECT COUNT(*) FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w"); $countStmt->execute($p);
        if ((int)$countStmt->fetchColumn() === 0) { return $this->unavailableReport('لا توجد بيانات لتعديلات الأسعار في سجل العمليات الحالي. يحتاج النظام إلى تسجيل تغييرات سعر البيع/التكلفة عند تعديل كارت الصنف.'); }
        return $this->queryDataset(
            "SELECT al.created_at AS action_date, COALESCE(u.full_name,u.username,'النظام') AS user_name, al.action, COALESCE(al.entity_id,'-') AS item_id, LEFT(REPLACE(REPLACE(COALESCE(al.meta_json,''), CHAR(10), ' '), CHAR(13), ' '), 220) AS details FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w ORDER BY al.created_at DESC, al.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COUNT(DISTINCT al.user_id) users_count FROM audit_logs al LEFT JOIN users u ON u.id=al.user_id WHERE $w",
            $p,
            ['action_date'=>'التاريخ','user_name'=>'المستخدم','action'=>'العملية','item_id'=>'رقم الصنف','details'=>'تفاصيل التغيير'],
            ['عدد التغييرات'=>'c','عدد المستخدمين'=>'users_count']
        );
    }

    private function stockChangesReport(array $f, int $limit, int $offset): array
    {
        if (!$this->tableExists('stock_moves')) { return $this->unavailableReport('جدول stock_moves غير موجود. يحتاج التقرير إلى سجل حركات مخزون.'); }
        $where=['1=1']; $p=[]; $this->dateWhere($where,$p,'sm.created_at',$f); $this->eqWhere($where,$p,'sm.user_id','user_id',$f); $this->eqWhere($where,$p,'sm.item_id','item_id',$f); $this->eqWhere($where,$p,'sm.warehouse_id','warehouse_id',$f);
        if ($f['movement_type'] !== '') { $where[]='sm.ref_type = :movement_type'; $p[':movement_type']=$f['movement_type']; }
        if ($f['q'] !== '') { $where[]='(sm.ref_type LIKE :q OR i.name LIKE :q OR i.barcode LIKE :q OR w.name LIKE :q OR COALESCE(u.full_name,u.username,\'\') LIKE :q)'; $p[':q']='%'.$f['q'].'%'; }
        $w=implode(' AND ',$where);
        return $this->queryDataset(
            "SELECT sm.created_at AS move_date, COALESCE(i.name,'-') AS item_name, COALESCE(w.name,'-') AS warehouse, sm.ref_type, sm.ref_id, sm.qty_in, sm.qty_out, (sm.qty_in-sm.qty_out) AS net_qty, COALESCE(u.full_name,u.username,'-') AS user_name FROM stock_moves sm LEFT JOIN items i ON i.id=sm.item_id LEFT JOIN warehouses w ON w.id=sm.warehouse_id LEFT JOIN users u ON u.id=sm.user_id WHERE $w ORDER BY sm.created_at DESC, sm.id DESC LIMIT $limit OFFSET $offset",
            "SELECT COUNT(*) c, COALESCE(SUM(sm.qty_in),0) total_in, COALESCE(SUM(sm.qty_out),0) total_out, COUNT(DISTINCT sm.item_id) items_count FROM stock_moves sm LEFT JOIN items i ON i.id=sm.item_id LEFT JOIN warehouses w ON w.id=sm.warehouse_id LEFT JOIN users u ON u.id=sm.user_id WHERE $w",
            $p,
            ['move_date'=>'التاريخ','item_name'=>'الصنف','warehouse'=>'المخزن','ref_type'=>'نوع الحركة','ref_id'=>'رقم المرجع','qty_in'=>'وارد','qty_out'=>'منصرف','net_qty'=>'الصافي','user_name'=>'المستخدم'],
            ['عدد الحركات'=>'c','إجمالي الوارد'=>'total_in','إجمالي المنصرف'=>'total_out','عدد الأصناف'=>'items_count']
        );
    }

    private function unavailableReport(string $message): array
    {
        return ['columns'=>['message'=>'البيان'],'rows'=>[['message'=>$message]],'summary'=>['الحالة'=>'يحتاج بيانات / تفعيل تسجيل'],'total_rows'=>1];
    }

    private function tableExists(string $table): bool
    {
        try { $st=$this->pdo->prepare('SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?'); $st->execute([$table]); return (bool)$st->fetchColumn(); }
        catch (Throwable $e) { return false; }
    }

    private function queryDataset(string $sql, string $summarySql, array $params, array $columns, array $summaryMap): array
    {
        try {
            $stmt=$this->pdo->prepare($sql); $stmt->execute($params); $rows=$stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            $sumStmt=$this->pdo->prepare($summarySql); $sumStmt->execute($params); $sum=$sumStmt->fetch(PDO::FETCH_ASSOC) ?: [];
            $summary=[]; foreach($summaryMap as $label=>$key){ $summary[$label]=is_numeric($sum[$key] ?? null) ? nf((float)$sum[$key]) : (string)($sum[$key] ?? '0'); }
            return ['columns'=>$columns,'rows'=>$rows,'summary'=>$summary,'total_rows'=>(int)($sum['c'] ?? count($rows))];
        } catch (Throwable $e) {
            return ['columns'=>['error'=>'تنبيه'],'rows'=>[['error'=>'تعذر تحميل بيانات التقرير بسبب اختلاف في بنية قاعدة البيانات: '.$e->getMessage()]],'summary'=>['الحالة'=>'تحتاج مراجعة بنية الجداول'],'total_rows'=>1];
        }
    }
    private function dateWhere(array &$where,array &$p,string $field,array $f): void { if($f['from']!==''){$where[]="$field >= :from";$p[':from']=$f['from'].' 00:00:00';} if($f['to']!==''){$where[]="$field <= :to";$p[':to']=$f['to'].' 23:59:59';} }
    private function eqWhere(array &$where,array &$p,string $field,string $key,array $f): void { if((int)($f[$key]??0)>0){$where[]="$field = :$key";$p[':'.$key]=(int)$f[$key];} }
    private function amountWhere(array &$where,array &$p,string $field,array $f): void { if($f['amount_min']!==''){$where[]="$field >= :amount_min";$p[':amount_min']=(float)$f['amount_min'];} if($f['amount_max']!==''){$where[]="$field <= :amount_max";$p[':amount_max']=(float)$f['amount_max'];} }
    private function lookups(): array
    {
        $one = function(string $sql, int $id): array {
            if ($id <= 0) { return []; }
            try { $stmt = $this->pdo->prepare($sql); $stmt->execute([':id'=>$id]); return $stmt->fetch(PDO::FETCH_ASSOC) ?: []; } catch(Throwable $e){ return []; }
        };
        $partyId = max(0, (int)Request::get('party_id', 0));
        $itemId = max(0, (int)Request::get('item_id', 0));
        $warehouseId = max(0, (int)Request::get('warehouse_id', 0));
        $cashboxId = max(0, (int)Request::get('cashbox_id', 0));
        $bankAccountId = max(0, (int)Request::get('bank_account_id', 0));
        $userId = max(0, (int)Request::get('user_id', 0));
        return [
            'selected_party'=>$one("SELECT id, CONCAT(COALESCE(NULLIF(code,''), id), ' - ', name) AS text FROM parties WHERE id=:id", $partyId),
            'selected_item'=>$one("SELECT id, CONCAT(COALESCE(NULLIF(barcode,''), id), ' - ', name) AS text FROM items WHERE id=:id", $itemId),
            'selected_warehouse'=>$one("SELECT id, name AS text FROM warehouses WHERE id=:id", $warehouseId),
            'selected_cashbox'=>$one("SELECT id, name AS text FROM cashboxes WHERE id=:id", $cashboxId),
            'selected_bank_account'=>$one("SELECT id, CONCAT(name, COALESCE(CONCAT(' - ', bank_name), '')) AS text FROM bank_accounts WHERE id=:id", $bankAccountId),
            'selected_user'=>$one("SELECT id, COALESCE(full_name, username) AS text FROM users WHERE id=:id", $userId),
        ];
    }

    /** @return array<string,mixed> */
    private function filterConfig(string $slug): array
    {
        $base = ['from','to'];
        $cfg = [
            'purchases-period' => ['fields'=>array_merge($base, ['party','warehouse','status','q','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض فواتير الشراء خلال فترة مع المورد، المخزن، الإجمالي والمتبقي فقط.'],
            'purchases-by-supplier' => ['fields'=>array_merge($base, ['party','amount','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'amount_label'=>'إجمالي مشتريات من / إلى', 'hint'=>'يعرض ملخص كل مورد: عدد الفواتير، إجمالي المشتريات، المتبقي وآخر فاتورة.'],
            'purchase-items' => ['fields'=>array_merge($base, ['item','party','warehouse','q','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض الأصناف المشتراة فقط: الكمية، قيمة الشراء ومتوسط السعر.'],
            'purchase-price-changes' => ['fields'=>array_merge($base, ['item','party','diff_min','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يركز على تغير سعر شراء الصنف حسب المورد ويعرض أقل سعر، أعلى سعر وفرق السعر.'],
            'purchase-unpaid' => ['fields'=>array_merge($base, ['party','amount','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'amount_label'=>'متبقي من / إلى', 'hint'=>'يعرض فقط فواتير الشراء التي ما زال عليها متبقي.'],
            'purchases-warehouses' => ['fields'=>array_merge($base, ['warehouse','per_page']), 'hint'=>'يعرض تجميع المشتريات حسب المخزن فقط. لا توجد فلاتر مورد أو صنف لأنها لا تخدم هدف التقرير هنا.'],
            'purchases-users' => ['fields'=>array_merge($base, ['user','warehouse','per_page']), 'hint'=>'يعرض تجميع المشتريات حسب المستخدم الذي أنشأ فواتير الشراء.'],
            'purchase-returns-period' => ['fields'=>array_merge($base, ['party','warehouse','q','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض مرتجعات الشراء خلال فترة مع المورد والمخزن والإجمالي.'],
            'supplier-returned-items' => ['fields'=>array_merge($base, ['item','party','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض أكثر الأصناف المرتجعة للموردين حسب الكمية وقيمة المرتجعات.'],
            'supplier-returns' => ['fields'=>array_merge($base, ['party','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض ملخص مرتجعات كل مورد.'],
            'net-purchases-after-returns' => ['fields'=>array_merge($base, ['party','warehouse','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يقارن إجمالي المشتريات بالمرتجعات ويعرض صافي المشتريات لكل مورد.'],
            'customer-balances' => ['fields'=>['party','q','per_page'], 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض أرصدة العملاء الحالية من الرصيد الافتتاحي والمتبقي من فواتير البيع والمرتجعات.'],
            'customer-aging' => ['fields'=>array_merge($base, ['party','q','per_page']), 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض أعمار ديون العملاء حسب شرائح 0-30، 31-60، 61-90، وأكثر من 90 يوم.'],
            'late-customers' => ['fields'=>array_merge($base, ['party','inactive_days','q','per_page']), 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض العملاء المتأخرين في السداد. استخدم حقل الأيام لتحديد حد التأخير، والافتراضي 30 يوم.'],
            'customer-activity' => ['fields'=>array_merge($base, ['party','amount','q','per_page']), 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'amount_label'=>'إجمالي مبيعات من / إلى', 'hint'=>'يعرض نشاط العملاء خلال الفترة: عدد الفواتير، إجمالي المبيعات، المتبقي وآخر تعامل.'],
            'inactive-customers' => ['fields'=>['party','inactive_days','q','per_page'], 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض العملاء الذين لا توجد لهم مبيعات منذ عدد أيام محدد، والافتراضي 90 يوم.'],
            'top-customers' => ['fields'=>array_merge($base, ['party','amount','q','per_page']), 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'amount_label'=>'إجمالي مبيعات من / إلى', 'hint'=>'يعرض أفضل العملاء حسب إجمالي المبيعات خلال الفترة.'],
            'credit-limit' => ['fields'=>['party','q','per_page'], 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض العملاء الذين تجاوزوا حد الائتمان المسجل على كارت العميل.'],
            'supplier-balances' => ['fields'=>['party','q','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض أرصدة الموردين الحالية من الرصيد الافتتاحي والمتبقي من فواتير الشراء والمرتجعات.'],
            'supplier-debt-aging' => ['fields'=>array_merge($base, ['party','q','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض أعمار ديون الموردين حسب شرائح التأخير.'],
            'top-suppliers' => ['fields'=>array_merge($base, ['party','amount','q','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'amount_label'=>'إجمالي مشتريات من / إلى', 'hint'=>'يعرض الموردين الأكثر تعاملًا حسب إجمالي المشتريات.'],
            'supplier-item-prices' => ['fields'=>array_merge($base, ['party','item','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض أسعار الموردين للأصناف: أقل سعر، أعلى سعر، المتوسط، وآخر سعر شراء.'],
            'inactive-suppliers' => ['fields'=>['party','inactive_days','q','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض الموردين غير النشطين الذين لا توجد لهم مشتريات منذ عدد أيام محدد، والافتراضي 90 يوم.'],
            'stock-balances' => ['fields'=>['warehouse','item','stock_flags','per_page'], 'hint'=>'يعرض أرصدة الأصناف بالمخازن مع التكلفة وقيمة المخزون.'],
            'stock-movement' => ['fields'=>array_merge($base, ['item','warehouse','movement_type','q','per_page']), 'hint'=>'اختر صنفًا لعرض حركاته: وارد، منصرف، ورصيد بعد كل حركة.'],
            'item-card' => ['fields'=>array_merge($base, ['item','warehouse','movement_type','stock_flags','q','per_page']), 'hint'=>'كارت صنف مختصر. اختر الصنف أولًا، ويمكن تفعيل عرض التكلفة.'],
            'stock-by-warehouse' => ['fields'=>['warehouse','stock_flags','per_page'], 'hint'=>'يعرض إجمالي الكميات وقيم التكلفة والبيع مجمعة حسب المخزن.'],
            'inactive-items' => ['fields'=>['warehouse','item','party','inactive_days','stock_flags','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض الأصناف التي لم تتحرك منذ عدد الأيام المحدد. الافتراضي 90 يوم.'],
            'most-moving-items' => ['fields'=>array_merge($base, ['warehouse','item','movement_sort','q','per_page']), 'hint'=>'يعرض الأصناف الأكثر حركة حسب الوارد أو المنصرف أو إجمالي الحركة.'],
            'reorder-items' => ['fields'=>['warehouse','item','party','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض الأصناف التي وصلت أو قلت عن حد إعادة الطلب.'],
            'negative-items' => ['fields'=>['warehouse','item','party','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض الأصناف ذات الرصيد السالب فقط.'],
            'stock-adjustments' => ['fields'=>array_merge($base, ['warehouse','user','item','q','per_page']), 'hint'=>'يعرض تسويات المخزون إذا كانت مسجلة داخل stock_moves برمز حركة يحتوي ADJUST.'],
            'stock-transfers' => ['fields'=>array_merge($base, ['warehouse','q','per_page']), 'hint'=>'يتطلب جدول تحويلات مستقل أو ref_type واضح للتحويلات.'],
            'stock-cost-value' => ['fields'=>['warehouse','item','party','value','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض قيمة المخزون بالتكلفة حسب الرصيد الحالي.'],
            'stock-sale-value' => ['fields'=>['warehouse','item','party','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض قيمة المخزون بسعر البيع حسب الرصيد الحالي.'],
            'stock-margin' => ['fields'=>['item','party','margin_max','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض فرق السعر بين التكلفة وسعر البيع ونسبة الهامش.'],
            'stock-by-supplier' => ['fields'=>['party','warehouse','item','stock_flags','per_page'], 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض أرصدة الأصناف مجمعة حسب المورد الافتراضي المسجل على كارت الصنف.'],
            'expenses-period' => ['fields'=>array_merge($base, ['cashbox','bank_account','user','amount','q','per_page']), 'hint'=>'يعرض المصروفات خلال فترة من سندات الصرف المصنفة كمصروفات.'],
            'expenses-by-category' => ['fields'=>array_merge($base, ['amount','q','per_page']), 'hint'=>'يعرض إجمالي المصروفات مجمعة حسب البند.'],
            'expenses-by-user' => ['fields'=>array_merge($base, ['user','amount','q','per_page']), 'hint'=>'يعرض المصروفات مجمعة حسب المستخدم.'],
            'expenses-daily-monthly' => ['fields'=>array_merge($base, ['amount','q','per_page']), 'hint'=>'يعرض المصروفات اليومية خلال الفترة.'],
            'expenses-vs-sales' => ['fields'=>array_merge($base, ['per_page']), 'hint'=>'يقارن إجمالي المصروفات بالمبيعات يوميًا.'],
            'gross-profit' => ['fields'=>array_merge($base, ['warehouse','per_page']), 'hint'=>'يعرض المبيعات والتكلفة والربح الإجمالي حسب اليوم.'],
            'item-profit' => ['fields'=>array_merge($base, ['item','q','per_page']), 'hint'=>'يعرض ربحية الأصناف من المبيعات والتكلفة المسجلة على بنود البيع.'],
            'customer-profit' => ['fields'=>array_merge($base, ['party','per_page']), 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض ربحية العملاء حسب المبيعات والتكلفة.'],
            'invoice-profit' => ['fields'=>array_merge($base, ['party','per_page']), 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض ربح كل فاتورة بيع بشكل مختصر.'],
            'warehouse-profit' => ['fields'=>array_merge($base, ['warehouse','per_page']), 'hint'=>'يعرض ربحية المبيعات حسب المخزن.'],
            'profit-after-expenses' => ['fields'=>array_merge($base, ['warehouse','per_page']), 'hint'=>'يعرض الربح الإجمالي ثم يخصم المصروفات خلال نفس الفترة.'],
            'low-margin' => ['fields'=>array_merge($base, ['item','margin_max','q','per_page']), 'hint'=>'يعرض الأصناف ذات الهامش المنخفض. الافتراضي أقل من أو يساوي 10%.'],
            'sales-tax' => ['fields'=>array_merge($base, ['party','per_page']), 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض فواتير البيع التي بها ضريبة مبيعات.'],
            'purchase-tax' => ['fields'=>array_merge($base, ['party','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض فواتير الشراء التي بها ضريبة مشتريات.'],
            'net-tax' => ['fields'=>array_merge($base, ['per_page']), 'hint'=>'يعرض صافي الضريبة: ضريبة المبيعات ناقص ضريبة المشتريات.'],
            'discounts' => ['fields'=>array_merge($base, ['party','per_page']), 'party_label'=>'بحث الطرف', 'party_lookup_type'=>'party', 'hint'=>'يعرض خصومات فواتير البيع.'],
            'withholding-tax' => ['fields'=>array_merge($base, ['party','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض خصم المنبع المسجل على فواتير الشراء.'],
            'daily-management-summary' => ['fields'=>array_merge($base, ['per_page']), 'hint'=>'ملخص يومي للإدارة: عدد الفواتير، المبيعات، التحصيل والمتبقي.'],
            'monthly-summary' => ['fields'=>array_merge($base, ['per_page']), 'hint'=>'ملخص شهري للإدارة.'],
            'kpi' => ['fields'=>array_merge($base, ['per_page']), 'hint'=>'مؤشرات أداء مختصرة للفترة المحددة.'],
            'period-comparison' => ['fields'=>array_merge($base, ['per_page']), 'hint'=>'يقارن الفترة الحالية بفترة سابقة مساوية لها في عدد الأيام.'],
            'sales-growth' => ['fields'=>array_merge($base, ['per_page']), 'hint'=>'يعرض نمو المبيعات شهريًا خلال الفترة.'],
            'cashbox-ledger' => ['fields'=>array_merge($base, ['cashbox','user','amount','q','per_page']), 'hint'=>'يعرض حركة الخزنة وارد ومنصرف مع المستند والمستخدم.'],
            'cashbox-balances' => ['fields'=>['cashbox','q','per_page'], 'hint'=>'يعرض أرصدة الخزائن الحالية من الرصيد الافتتاحي وحركات الخزنة.'],
            'customer-collections' => ['fields'=>array_merge($base, ['party','cashbox','bank_account','amount','q','per_page']), 'party_label'=>'بحث العميل', 'party_lookup_type'=>'customer', 'hint'=>'يعرض تحصيلات العملاء فقط مع الخزنة أو الحساب البنكي.'],
            'supplier-payments' => ['fields'=>array_merge($base, ['party','cashbox','bank_account','amount','q','per_page']), 'party_label'=>'بحث المورد', 'party_lookup_type'=>'supplier', 'hint'=>'يعرض مدفوعات الموردين من سندات الصرف المسجلة.'],
            'cashbox-expenses' => ['fields'=>array_merge($base, ['cashbox','user','amount','q','per_page']), 'hint'=>'يعرض المصروفات المصروفة من الخزائن فقط.'],
            'cashbox-transfers' => ['fields'=>array_merge($base, ['cashbox','q','per_page']), 'hint'=>'يعرض تحويلات الخزائن إذا كانت مسجلة في حركات الخزنة برمز transfer.'],
            'cashbox-daily' => ['fields'=>array_merge($base, ['cashbox','per_page']), 'hint'=>'يعرض يومية الخزنة مجمعة حسب اليوم والخزنة.'],
            'cashbox-shortage-surplus' => ['fields'=>['cashbox','per_page'], 'hint'=>'يتطلب جدول جرد أو تسويات خزنة مستقل حتى يعرض العجز والزيادة بدقة.'],
            'bank-balances' => ['fields'=>['bank_account','q','per_page'], 'hint'=>'يعرض أرصدة الحسابات البنكية الحالية.'],
            'bank-statement' => ['fields'=>array_merge($base, ['bank_account','user','amount','q','per_page']), 'hint'=>'يعرض كشف حركات الحساب البنكي.'],
            'bank-deposits' => ['fields'=>array_merge($base, ['bank_account','amount','q','per_page']), 'hint'=>'يعرض إيداعات البنوك فقط.'],
            'bank-withdrawals' => ['fields'=>array_merge($base, ['bank_account','amount','q','per_page']), 'hint'=>'يعرض مسحوبات البنوك فقط.'],
            'bank-cashbox-transfers' => ['fields'=>array_merge($base, ['bank_account','q','per_page']), 'hint'=>'يعرض تحويلات بنك/خزنة إذا كانت مسجلة برمز transfer في حركات البنك.'],
            'bank-daily' => ['fields'=>array_merge($base, ['bank_account','per_page']), 'hint'=>'يعرض يومية البنك مجمعة حسب اليوم والحساب.'],
            'received-checks' => ['fields'=>array_merge($base, ['party','status','amount','q','per_page']), 'party_label'=>'بحث الطرف', 'party_lookup_type'=>'party', 'hint'=>'يعرض الشيكات المستلمة.'],
            'issued-checks' => ['fields'=>array_merge($base, ['party','status','amount','q','per_page']), 'party_label'=>'بحث الطرف', 'party_lookup_type'=>'party', 'hint'=>'يعرض الشيكات المسلمة أو المظهرة للموردين.'],
            'checks-due-today' => ['fields'=>['party','amount','q','per_page'], 'party_label'=>'بحث الطرف', 'party_lookup_type'=>'party', 'hint'=>'يعرض الشيكات المستحقة اليوم.'],
            'checks-due-period' => ['fields'=>array_merge($base, ['party','amount','q','per_page']), 'party_label'=>'بحث الطرف', 'party_lookup_type'=>'party', 'hint'=>'يعرض الشيكات المستحقة خلال الفترة.'],
            'overdue-checks' => ['fields'=>['party','amount','q','per_page'], 'party_label'=>'بحث الطرف', 'party_lookup_type'=>'party', 'hint'=>'يعرض الشيكات المتأخرة التي تجاوزت تاريخ الاستحقاق ولم تسوَ بعد.'],
            'returned-checks' => ['fields'=>array_merge($base, ['party','amount','q','per_page']), 'party_label'=>'بحث الطرف', 'party_lookup_type'=>'party', 'hint'=>'يعرض الشيكات المرتجعة.'],
            'check-lifecycle' => ['fields'=>array_merge($base, ['party','q','per_page']), 'party_label'=>'بحث الطرف', 'party_lookup_type'=>'party', 'hint'=>'يعرض دورة حياة الشيك من جدول حركات الشيكات.'],
            'checks-by-status' => ['fields'=>array_merge($base, ['q','per_page']), 'hint'=>'يعرض تجميع الشيكات حسب الحالة.'],
            'user-activity' => ['fields'=>array_merge($base, ['user','q','per_page']), 'hint'=>'يعرض ملخص نشاط كل مستخدم من سجل العمليات: عدد العمليات، الدخول، العمليات الحساسة وآخر عملية.'],
            'audit-log' => ['fields'=>array_merge($base, ['user','status','q','per_page']), 'status_label'=>'كود العملية', 'hint'=>'يعرض سجل العمليات الخام من audit_logs مع المستخدم والكيان والـ IP والتفاصيل المختصرة.'],
            'sensitive-actions' => ['fields'=>array_merge($base, ['user','q','per_page']), 'hint'=>'يعرض العمليات الحساسة مثل الحذف، الإلغاء، تعديل الصلاحيات، النسخ الاحتياطي، التسوية أو الاستبدال.'],
            'failed-logins' => ['fields'=>array_merge($base, ['q','per_page']), 'hint'=>'يعرض محاولات تسجيل الدخول الفاشلة أو المحظورة من audit_logs و login_attempts حسب المتاح.'],
            'user-permissions' => ['fields'=>['user','q','per_page'], 'hint'=>'يعرض أدوار وصلاحيات المستخدمين مجمعة حسب الموديول بدون تحميل كل التفاصيل الثقيلة.'],
            'price-changes' => ['fields'=>array_merge($base, ['user','q','per_page']), 'hint'=>'يعرض تعديلات الأسعار فقط إذا كانت مسجلة في audit_logs. إن لم توجد بيانات سيظهر تنبيه واضح.'],
            'stock-changes' => ['fields'=>array_merge($base, ['user','warehouse','item','movement_type','q','per_page']), 'hint'=>'يعرض التعديلات/الحركات المسجلة في stock_moves حسب المستخدم والصنف والمخزن.'],
        ];
        return $cfg[$slug] ?? ['fields'=>array_merge($base, ['party','item','warehouse','user','payment_mode','status','amount','q','per_page']), 'party_label'=>'بحث العميل / الطرف', 'hint'=>'استخدم الفلاتر المناسبة ثم اضغط تطبيق.'];
    }

    private function filtersText(array $f): string { $parts=[]; foreach($f as $k=>$v){ if((string)$v!==''){$parts[]=$k.'='.$v;} } return implode(' | ',$parts); }
}
