<?php
namespace App\Services;

use App\Models\CommissionRule;
use App\Repositories\CommissionRuleRepository;
use App\Utils\Logger;

class CommissionService
{
    private $ruleRepo;

    public function __construct(CommissionRuleRepository $ruleRepo)
    {
        $this->ruleRepo = $ruleRepo;
    }

    public function getRuleRepository(): CommissionRuleRepository
    {
        return $this->ruleRepo;
    }

    public function getRules(array $filters = []): array
    {
        return $this->ruleRepo->getAll($filters);
    }

    public function getRuleTiers(int $ruleId): array
    {
        if ($ruleId <= 0) {
            return [];
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_tiers')) {
            return [];
        }
        $stmt = $pdo->prepare("SELECT * FROM commission_rule_tiers WHERE rule_id = ? ORDER BY COALESCE(min_amount, min_realization_percent, 0) ASC, id ASC");
        $stmt->execute([$ruleId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    public function saveRule(array $data): int
    {
        $normalized = $this->validateRulePayload($data);
        $ruleType = $normalized['rule_type'];
        $tiers = $normalized['tiers'];

        $rule = new CommissionRule([
            'id' => $normalized['id'] > 0 ? $normalized['id'] : null,
            'rule_type' => $ruleType,
            'formula_type' => $normalized['formula_type'],
            'salesperson_user_id' => $normalized['salesperson_user_id'] > 0 ? $normalized['salesperson_user_id'] : null,
            'valid_from' => $normalized['valid_from'],
            'valid_to' => $normalized['valid_to'],
            'target_users' => $normalized['target_users'],
            'requested_services' => $normalized['requested_services'],
            'base_amount' => $normalized['base_amount'],
            'fixed_budget_amount' => $normalized['base_amount'],
            'base_rate_percent' => $normalized['reward_percentage'],
            'reward_percentage' => $normalized['reward_percentage'],
            'status' => $normalized['status'],
            'is_active' => $normalized['status'] === 'active' ? 1 : 0,
        ]);
        $pdo = $this->ruleRepo->getDb()->getPdo();
        try {
            $pdo->beginTransaction();
            $ruleId = $this->ruleRepo->save($rule);
            $this->saveRuleTiers($ruleId, $tiers, $ruleType);
            $this->syncRuleAssignments($ruleId, $normalized['target_users'], $normalized['product_ids']);
            $pdo->commit();
            $this->rebuildDealCommissionsForRule($ruleId);
            return $ruleId;
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    public function calculateCommissionTotal(array $rows, float $baseRatePercent, float $deductionQc = 0.0): float
    {
        $rate = max(0.0, $baseRatePercent) / 100;
        $ncrSum = 0.0;

        foreach ($rows as $row) {
            $status = strtoupper(trim((string)($row['status'] ?? '')));
            if (!in_array($status, ['SETTLED', 'CLEARED'], true)) {
                continue;
            }
            $ncr = (float)($row['ncr'] ?? 0);
            if ($ncr <= 0) {
                continue;
            }
            $ncrSum += $ncr * $rate;
        }

        $total = $ncrSum - max(0.0, $deductionQc);
        return round($total, 2);
    }

    private function validateRulePayload(array $data): array
    {
        $id = isset($data['id']) ? (int)$data['id'] : 0;
        $ruleType = strtoupper(trim((string)($data['rule_type'] ?? 'TIERED_RULE')));
        $allowedRuleTypes = ['FIXED_RULE', 'TIERED_RULE'];
        if (!in_array($ruleType, $allowedRuleTypes, true)) {
            throw new \InvalidArgumentException('نوع قانون پورسانت نامعتبر است');
        }

        $formulaType = $ruleType === 'FIXED_RULE' ? 'FIXED' : 'TIERED_MARGINAL';

        $validFrom = $this->normalizeInputDate((string)($data['valid_from'] ?? ''));
        $validTo = $this->normalizeInputDate((string)($data['valid_to'] ?? ''));
        if ($validFrom === null) {
            throw new \InvalidArgumentException('تاریخ شروع اعتبار قانون الزامی است');
        }
        if ($validTo !== null && $validTo < $validFrom) {
            throw new \InvalidArgumentException('تاریخ پایان اعتبار نمی‌تواند قبل از تاریخ شروع باشد');
        }

        $targetUsers = $this->decodeList($data['target_users'] ?? []);
        $salespersonUserId = isset($data['salesperson_user_id']) ? (int)$data['salesperson_user_id'] : 0;
        if ($salespersonUserId > 0) {
            $targetUsers = [$salespersonUserId];
        }
        if (empty($targetUsers)) {
            $targetUsers = ['ALL'];
        }
        $this->validateTargetUsers($targetUsers);

        $requestedServices = $this->decodeList($data['requested_services'] ?? []);
        if (empty($requestedServices)) {
            $requestedServices = ['ALL'];
        }
        $productIds = $this->extractProductScopeIds($requestedServices);
        $this->validateProductIds($productIds);

        $baseAmount = $this->toFloat($data['base_amount'] ?? 0);
        if ($baseAmount < 0) {
            throw new \InvalidArgumentException('مبلغ پایه نمی‌تواند منفی باشد');
        }
        $rewardPercentage = $this->toFloat($data['reward_percentage'] ?? 0);
        if ($rewardPercentage < 0 || $rewardPercentage > 100) {
            throw new \InvalidArgumentException('درصد پاداش باید بین 0 تا 100 باشد');
        }
        if ($ruleType === 'FIXED_RULE' && $baseAmount <= 0 && $rewardPercentage <= 0) {
            throw new \InvalidArgumentException('برای قانون ثابت، مبلغ ثابت یا درصد پورسانت الزامی است');
        }

        $status = strtolower(trim((string)($data['status'] ?? 'active')));
        if (!in_array($status, ['active', 'inactive'], true)) {
            throw new \InvalidArgumentException('وضعیت قانون نامعتبر است');
        }

        $tiers = $ruleType === 'TIERED_RULE'
            ? $this->validateAndNormalizeTiers($this->decodeList($data['tiers'] ?? []), $ruleType)
            : [];
        if ($this->tableExists($this->ruleRepo->getDb()->getPdo(), 'commission_rules')) {
            $this->assertNoRuleOverlap($id, $ruleType, $status, $validFrom, $validTo, $targetUsers, $productIds);
        }

        return [
            'id' => $id,
            'rule_type' => $ruleType,
            'formula_type' => $formulaType,
            'salesperson_user_id' => $salespersonUserId,
            'target_users' => $targetUsers,
            'requested_services' => $requestedServices,
            'product_ids' => $productIds,
            'valid_from' => $validFrom,
            'valid_to' => $validTo,
            'base_amount' => $baseAmount,
            'reward_percentage' => $rewardPercentage,
            'status' => $status,
            'tiers' => $tiers,
        ];
    }

    private function decodeList($raw): array
    {
        if (is_array($raw)) {
            return array_values($raw);
        }
        $raw = trim((string)$raw);
        if ($raw === '') {
            return [];
        }
        $decoded = json_decode($raw, true);
        return is_array($decoded) ? array_values($decoded) : [];
    }

    private function validateTargetUsers(array $targetUsers): void
    {
        if (in_array('ALL', $targetUsers, true)) {
            return;
        }
        $ids = array_values(array_unique(array_filter(array_map('intval', $targetUsers), static function ($id) {
            return $id > 0;
        })));
        if (empty($ids)) {
            throw new \InvalidArgumentException('حداقل یک کارشناس فروش معتبر انتخاب کنید');
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $pdo->prepare("SELECT id FROM users WHERE id IN ({$placeholders}) AND LOWER(COALESCE(role,'')) = 'agent'");
        $stmt->execute($ids);
        $found = array_map('intval', $stmt->fetchAll(\PDO::FETCH_COLUMN) ?: []);
        sort($found);
        sort($ids);
        if ($found !== $ids) {
            throw new \InvalidArgumentException('فقط کاربران با نقش کارشناس فروش قابل انتخاب هستند');
        }
    }

    private function validateProductIds(array $productIds): void
    {
        if (empty($productIds)) {
            return;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $placeholders = implode(',', array_fill(0, count($productIds), '?'));
        $stmt = $pdo->prepare("SELECT id FROM products WHERE id IN ({$placeholders})");
        $stmt->execute($productIds);
        $found = array_map('intval', $stmt->fetchAll(\PDO::FETCH_COLUMN) ?: []);
        sort($found);
        $expected = $productIds;
        sort($expected);
        if ($found !== $expected) {
            throw new \InvalidArgumentException('محصول انتخاب شده نامعتبر است');
        }
    }

    private function extractProductScopeIds(array $requestedServices): array
    {
        if (in_array('ALL', $requestedServices, true)) {
            return [];
        }
        $ids = [];
        foreach ($requestedServices as $service) {
            if (is_numeric($service) && (int)$service > 0) {
                $ids[] = (int)$service;
                continue;
            }
            if (!is_array($service)) {
                continue;
            }
            foreach (['product_id', 'id'] as $key) {
                if (isset($service[$key]) && is_numeric($service[$key]) && (int)$service[$key] > 0) {
                    $ids[] = (int)$service[$key];
                }
            }
            $rawIds = trim((string)($service['product_ids'] ?? ''));
            if ($rawIds !== '') {
                foreach (explode(',', $rawIds) as $rawId) {
                    $rawId = trim($rawId);
                    if (ctype_digit($rawId) && (int)$rawId > 0) {
                        $ids[] = (int)$rawId;
                    }
                }
            }
        }
        return array_values(array_unique($ids));
    }

    private function validateAndNormalizeTiers(array $tiers, string $ruleType): array
    {
        if (empty($tiers)) {
            throw new \InvalidArgumentException('حداقل یک پلکان پورسانت تعریف کنید');
        }
        return $this->validateCashflowTiers($tiers);
    }

    private function validateCashflowTiers(array $tiers): array
    {
        $normalized = [];
        foreach ($tiers as $tier) {
            if (!is_array($tier)) {
                continue;
            }
            $min = $this->nullableFloat($tier['min_amount'] ?? null);
            $max = $this->nullableFloat($tier['max_amount'] ?? null);
            $percent = $this->nullableFloat($tier['commission_percent'] ?? null);
            if ($min === null || $percent === null) {
                continue;
            }
            if ($min < 0 || ($max !== null && $max < 0)) {
                throw new \InvalidArgumentException('مقادیر پلکان نمی‌توانند منفی باشند');
            }
            if ($max !== null && $max > 0 && $max < $min) {
                throw new \InvalidArgumentException('سقف هر پلکان باید از کف آن بزرگ‌تر باشد');
            }
            if ($max !== null && $max == 0.0) {
                throw new \InvalidArgumentException('برای پلکان بدون سقف، سقف را خالی بگذارید');
            }
            if ($percent < 0 || $percent > 100) {
                throw new \InvalidArgumentException('درصد هر پلکان باید بین 0 تا 100 باشد');
            }
            $normalized[] = [
                'min_amount' => $min,
                'max_amount' => $max !== null && $max > 0 ? $max : null,
                'commission_percent' => $percent,
            ];
        }
        return $this->assertProgressiveBands($normalized, 'min_amount', 'max_amount', 'مبلغی');
    }

    private function assertProgressiveBands(array $tiers, string $minKey, string $maxKey, string $label): array
    {
        if (empty($tiers)) {
            throw new \InvalidArgumentException("حداقل یک پلکان {$label} معتبر تعریف کنید");
        }
        usort($tiers, static function ($a, $b) use ($minKey) {
            return $a[$minKey] <=> $b[$minKey];
        });

        $smallestUnit = 1.0;
        $openEndedCount = 0;
        $previousMax = null;
        foreach ($tiers as $index => $tier) {
            $min = (float)$tier[$minKey];
            $max = $tier[$maxKey] === null ? null : (float)$tier[$maxKey];
            if ($max === null) {
                $openEndedCount++;
                if ($index !== count($tiers) - 1) {
                    throw new \InvalidArgumentException("پلکان بدون سقف {$label} فقط باید آخرین پلکان باشد");
                }
            }
            if ($openEndedCount > 1) {
                throw new \InvalidArgumentException("فقط یک پلکان بدون سقف {$label} مجاز است");
            }
            if ($previousMax !== null) {
                $expectedMin = $previousMax + $smallestUnit;
                if (abs($min - $expectedMin) > 0.0001) {
                    throw new \InvalidArgumentException("پلکان‌های {$label} باید بدون فاصله و هم‌پوشانی باشند؛ کف پلکان بعدی باید برابر سقف قبلی + 1 باشد");
                }
            }
            if ($max !== null) {
                if ($max < $min) {
                    throw new \InvalidArgumentException("سقف هر پلکان {$label} باید از کف آن بزرگ‌تر باشد");
                }
                $previousMax = $max;
            }
        }
        return array_values($tiers);
    }

    private function assertNoRuleOverlap(
        int $id,
        string $ruleType,
        string $status,
        string $validFrom,
        ?string $validTo,
        array $targetUsers,
        array $productIds
    ): void {
        if ($status !== 'active') {
            return;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $where = ["rule_type = ?"];
        $params = [$ruleType];
        if ($this->columnExists($pdo, 'commission_rules', 'status')) {
            $where[] = "status = 'active'";
        } elseif ($this->columnExists($pdo, 'commission_rules', 'is_active')) {
            $where[] = "is_active = 1";
        }
        if ($this->columnExists($pdo, 'commission_rules', 'deleted_at')) {
            $where[] = "deleted_at IS NULL";
        }
        if ($id > 0) {
            $where[] = "id <> ?";
            $params[] = $id;
        }
        $where[] = "DATE(valid_from) <= ?";
        $params[] = $validTo ?: '2999-12-31';
        $where[] = "(valid_to IS NULL OR DATE(valid_to) >= ?)";
        $params[] = $validFrom;
        $stmt = $pdo->prepare("SELECT * FROM commission_rules WHERE " . implode(' AND ', $where));
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $newTargets = $this->normalizeTargetScope($targetUsers);
        $newProducts = empty($productIds) ? ['ALL'] : array_map('strval', $productIds);
        foreach ($rows as $row) {
            $existingTargets = $this->normalizeTargetScope($this->decodeList($row['target_users'] ?? '[]'));
            if (empty($existingTargets) && (int)($row['salesperson_user_id'] ?? 0) > 0) {
                $existingTargets = [(string)(int)$row['salesperson_user_id']];
            }
            if (empty($existingTargets)) {
                $existingTargets = ['ALL'];
            }
            $existingProducts = $this->extractProductScopeIds($this->decodeList($row['requested_services'] ?? '[]'));
            $existingProducts = empty($existingProducts) ? ['ALL'] : array_map('strval', $existingProducts);

            if ($this->scopesIntersect($newTargets, $existingTargets) && $this->scopesIntersect($newProducts, $existingProducts)) {
                throw new \InvalidArgumentException('برای همین کارشناس/محصول و بازه زمانی، قانون فعال هم‌پوشان وجود دارد');
            }
        }
    }

    private function normalizeTargetScope(array $targets): array
    {
        if (in_array('ALL', $targets, true)) {
            return ['ALL'];
        }
        $ids = array_values(array_unique(array_filter(array_map('intval', $targets), static function ($id) {
            return $id > 0;
        })));
        return array_map('strval', $ids);
    }

    private function scopesIntersect(array $left, array $right): bool
    {
        if (in_array('ALL', $left, true) || in_array('ALL', $right, true)) {
            return true;
        }
        return count(array_intersect($left, $right)) > 0;
    }

    private function toFloat($value): float
    {
        return (float)str_replace(',', '', (string)$value);
    }

    private function nullableFloat($value): ?float
    {
        if ($value === null || trim((string)$value) === '') {
            return null;
        }
        return $this->toFloat($value);
    }

    private function saveRuleTiers(int $ruleId, array $tiers, string $ruleType): void
    {
        if ($ruleId <= 0) {
            return;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_tiers')) {
            return;
        }
        $ownsTransaction = !$pdo->inTransaction();
        if ($ownsTransaction) {
            $pdo->beginTransaction();
        }
        try {
            $pdo->prepare("DELETE FROM commission_rule_tiers WHERE rule_id = ?")->execute([$ruleId]);
            foreach ($tiers as $tier) {
                $commission = isset($tier['commission_percent']) ? (float)$tier['commission_percent'] : null;
                if ($commission === null) {
                    continue;
                }

                $columns = ['rule_id'];
                $values = [$ruleId];

                $minAmount = isset($tier['min_amount']) && $tier['min_amount'] !== '' ? (float)$tier['min_amount'] : null;
                $maxAmount = isset($tier['max_amount']) && $tier['max_amount'] !== '' ? (float)$tier['max_amount'] : null;
                if ($minAmount === null) {
                    continue;
                }
                if ($this->columnExists($pdo, 'commission_rule_tiers', 'min_amount')) {
                    $columns[] = 'min_amount';
                    $values[] = $minAmount;
                }
                if ($maxAmount !== null && $this->columnExists($pdo, 'commission_rule_tiers', 'max_amount')) {
                    $columns[] = 'max_amount';
                    $values[] = $maxAmount;
                }

                if ($this->columnExists($pdo, 'commission_rule_tiers', 'allocated_commission_percent')) {
                    $columns[] = 'allocated_commission_percent';
                    $values[] = $commission;
                } elseif ($this->columnExists($pdo, 'commission_rule_tiers', 'commission_percent')) {
                    $columns[] = 'commission_percent';
                    $values[] = $commission;
                }

                if (count($columns) <= 1) {
                    continue;
                }

                $placeholders = implode(',', array_fill(0, count($columns), '?'));
                $sql = "INSERT INTO commission_rule_tiers (" . implode(',', $columns) . ") VALUES (" . $placeholders . ")";
                $stmt = $pdo->prepare($sql);
                $stmt->execute($values);
            }
            if ($ownsTransaction && $pdo->inTransaction()) {
                $pdo->commit();
            }
        } catch (\Throwable $e) {
            if ($ownsTransaction && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            Logger::logError('COMMISSION_TIER_SAVE_FAILED', $e, [
                'rule_id' => $ruleId,
                'rule_type' => $ruleType,
                'tiers' => $tiers,
            ]);
            throw $e;
        }
    }

    private function syncRuleAssignments(int $ruleId, array $targetUsers, array $productIds): void
    {
        if ($ruleId <= 0) {
            return;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_assignments')) {
            return;
        }

        $assignmentTargets = $this->normalizeTargetScope($targetUsers);
        if (empty($assignmentTargets)) {
            $assignmentTargets = ['ALL'];
        }

        $selectSql = "SELECT id, salesperson_user_id, scope_type FROM commission_rule_assignments WHERE rule_id = ?";
        if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_at')) {
            $selectSql .= " AND disabled_at IS NULL";
        }
        $stmt = $pdo->prepare($selectSql);
        $stmt->execute([$ruleId]);
        $existing = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $wantedKeys = [];
        foreach ($assignmentTargets as $target) {
            $scopeType = $target === 'ALL' ? 'ALL' : 'USER';
            $salespersonId = $scopeType === 'USER' ? (int)$target : null;
            $wantedKeys[$scopeType . ':' . (string)($salespersonId ?: 0)] = [
                'scope_type' => $scopeType,
                'salesperson_user_id' => $salespersonId,
            ];
        }

        $existingByKey = [];
        foreach ($existing as $row) {
            $scopeType = strtoupper(trim((string)($row['scope_type'] ?? 'USER'))) ?: 'USER';
            $salespersonId = (int)($row['salesperson_user_id'] ?? 0);
            $existingByKey[$scopeType . ':' . (string)$salespersonId] = (int)($row['id'] ?? 0);
        }

        foreach ($existingByKey as $key => $assignmentId) {
            if (isset($wantedKeys[$key]) || $assignmentId <= 0) {
                continue;
            }
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_at')) {
                $pdo->prepare("UPDATE commission_rule_assignments SET disabled_at = NOW() WHERE id = ?")->execute([$assignmentId]);
            } else {
                $pdo->prepare("DELETE FROM commission_rule_assignments WHERE id = ?")->execute([$assignmentId]);
            }
        }

        foreach ($wantedKeys as $key => $target) {
            $assignmentId = $existingByKey[$key] ?? 0;
            if ($assignmentId <= 0) {
                $columns = ['rule_id', 'salesperson_user_id', 'scope_type', 'assigned_at'];
                $values = [$ruleId, $target['salesperson_user_id'], $target['scope_type'], date('Y-m-d H:i:s')];
                if ($this->columnExists($pdo, 'commission_rule_assignments', 'assigned_by_user_id')) {
                    $columns[] = 'assigned_by_user_id';
                    $values[] = (int)($_SESSION['user_id'] ?? 0) ?: null;
                }
                $insert = $pdo->prepare("INSERT INTO commission_rule_assignments (" . implode(',', $columns) . ") VALUES (" . implode(',', array_fill(0, count($columns), '?')) . ")");
                $insert->execute($values);
                $assignmentId = (int)$pdo->lastInsertId();
            }
            $this->syncAssignmentProducts($assignmentId, $productIds);
        }
    }

    private function syncAssignmentProducts(int $assignmentId, array $productIds): void
    {
        if ($assignmentId <= 0) {
            return;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'assignment_products')) {
            return;
        }
        $pdo->prepare("DELETE FROM assignment_products WHERE assignment_id = ?")->execute([$assignmentId]);
        $productIds = array_values(array_unique(array_filter(array_map('intval', $productIds), static function ($id) {
            return $id > 0;
        })));
        if (empty($productIds)) {
            return;
        }
        $insert = $pdo->prepare("INSERT INTO assignment_products (assignment_id, product_id) VALUES (?, ?)");
        foreach ($productIds as $productId) {
            $insert->execute([$assignmentId, $productId]);
        }
    }

    public function getRulePerformanceRows(array $filters = []): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rules')) {
            return [];
        }

        $dateFrom = $this->normalizeInputDate((string)($filters['date_from'] ?? '')) ?: date('Y-m-01');
        $dateTo = $this->normalizeInputDate((string)($filters['date_to'] ?? '')) ?: date('Y-m-t');
        $filterUserId = $this->resolveCommissionFilterUserId($filters);
        $filterStatus = strtolower(trim((string)($filters['status'] ?? '')));
        $amountMin = isset($filters['amount_min']) && $filters['amount_min'] !== '' ? (float)$filters['amount_min'] : null;
        $amountMax = isset($filters['amount_max']) && $filters['amount_max'] !== '' ? (float)$filters['amount_max'] : null;

        $rules = $this->getActiveRulesForPerformance($dateFrom, $dateTo);
        $rows = [];
        foreach ($rules as $rule) {
            $assignments = $this->getRuleAssignmentsForPerformance($rule);
            foreach ($assignments as $assignment) {
                $salespersonIds = $this->resolveAssignmentSalespersonIds($assignment, $filterUserId);
                if (empty($salespersonIds)) {
                    continue;
                }
                $productIds = $this->resolveAssignmentProductIds($assignment, $rule);
                $payments = $this->getEligiblePaymentsForScope($salespersonIds, $productIds, $dateFrom, $dateTo, $rule, $assignment);
                if (empty($payments)) {
                    continue;
                }
                $paymentTotal = array_sum(array_map(static function ($payment) {
                    return (float)($payment['amount'] ?? 0);
                }, $payments));
                $commissionAmount = $this->calculateRuleCommission($rule, $paymentTotal);
                if ($amountMin !== null && $commissionAmount < $amountMin) {
                    continue;
                }
                if ($amountMax !== null && $commissionAmount > $amountMax) {
                    continue;
                }
                if ($filterStatus !== '' && $filterStatus !== 'calculated') {
                    continue;
                }
                $salespersonNames = $this->getUserNamesByIds($salespersonIds);
                $productNames = $this->getProductNamesByIds($productIds);
                $depositDates = array_values(array_filter(array_map(static function ($payment) {
                    return (string)($payment['deposit_date'] ?? '');
                }, $payments)));
                sort($depositDates);
                $rows[] = [
                    'id' => 'rule-' . (int)($rule['id'] ?? 0) . '-assignment-' . (int)($assignment['id'] ?? 0),
                    'row_type' => 'rule_performance',
                    'deal_didar_id' => 'تجمیع پرداخت‌ها',
                    'rule_id' => (int)($rule['id'] ?? 0),
                    'rule_title' => (string)($rule['title'] ?? ''),
                    'rule_type' => (string)($rule['rule_type'] ?? ''),
                    'assignment_id' => (int)($assignment['id'] ?? 0),
                    'salesperson_user_id' => count($salespersonIds) === 1 ? $salespersonIds[0] : null,
                    'salesperson_name' => implode('، ', $salespersonNames),
                    'product_id' => count($productIds) === 1 ? $productIds[0] : null,
                    'product_name' => empty($productIds) ? 'همه محصولات' : implode('، ', $productNames),
                    'ncr_amount' => round($paymentTotal, 2),
                    'payment_total' => round($paymentTotal, 2),
                    'payments_count' => count($payments),
                    'applied_rate_percent' => $this->deriveEffectiveRate($paymentTotal, $commissionAmount),
                    'calculated_commission' => $commissionAmount,
                    'calculated_amount' => $commissionAmount,
                    'status' => 'CALCULATED',
                    'deposit_date' => $depositDates[0] ?? '',
                    'deposit_date_to' => end($depositDates) ?: '',
                    'billing_month' => substr($dateFrom, 0, 7),
                    'source' => 'direct_payments',
                ];
            }
        }

        usort($rows, static function ($a, $b) {
            return (float)($b['calculated_commission'] ?? 0) <=> (float)($a['calculated_commission'] ?? 0);
        });
        return $rows;
    }

    private function summarizeRulePerformance(array $filters): array
    {
        $rows = $this->getRulePerformanceRows($filters);
        $paymentTotal = 0.0;
        $commissionTotal = 0.0;
        $paymentCount = 0;
        foreach ($rows as $row) {
            $paymentTotal += (float)($row['payment_total'] ?? $row['ncr_amount'] ?? 0);
            $commissionTotal += (float)($row['calculated_commission'] ?? 0);
            $paymentCount += (int)($row['payments_count'] ?? 0);
        }
        return [
            'payment_total' => round($paymentTotal, 2),
            'commission_total' => round($commissionTotal, 2),
            'payments_count' => $paymentCount,
            'rules_count' => count($rows),
        ];
    }

    private function resolveCommissionFilterUserId(array $filters): int
    {
        $explicit = (int)($filters['salesperson_user_id'] ?? 0);
        if ($explicit > 0) {
            return $explicit;
        }
        $ownerDidarId = trim((string)($filters['owner_didar_id'] ?? ''));
        if ($ownerDidarId === '') {
            return 0;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $stmt = $pdo->prepare("SELECT id FROM users WHERE didar_user_id = ? LIMIT 1");
        $stmt->execute([$ownerDidarId]);
        return (int)($stmt->fetchColumn() ?: 0);
    }

    private function getActiveRulesForPerformance(string $dateFrom, string $dateTo): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $where = ["UPPER(COALESCE(rule_type, '')) IN ('FIXED_RULE','TIERED_RULE')"];
        $params = [];
        if ($this->columnExists($pdo, 'commission_rules', 'status')) {
            $where[] = "status = 'active'";
        } elseif ($this->columnExists($pdo, 'commission_rules', 'is_active')) {
            $where[] = "is_active = 1";
        }
        if ($this->columnExists($pdo, 'commission_rules', 'deleted_at')) {
            $where[] = "deleted_at IS NULL";
        }
        if ($this->columnExists($pdo, 'commission_rules', 'valid_from')) {
            $where[] = "DATE(valid_from) <= ?";
            $params[] = $dateTo;
        }
        if ($this->columnExists($pdo, 'commission_rules', 'valid_to')) {
            $where[] = "(valid_to IS NULL OR DATE(valid_to) >= ?)";
            $params[] = $dateFrom;
        }
        $stmt = $pdo->prepare("SELECT * FROM commission_rules WHERE " . implode(' AND ', $where) . " ORDER BY valid_from ASC, id ASC");
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    private function getRuleAssignmentsForPerformance(array $rule): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $ruleId = (int)($rule['id'] ?? 0);
        if ($ruleId > 0 && $this->tableExists($pdo, 'commission_rule_assignments')) {
            $where = ["rule_id = ?"];
            $params = [$ruleId];
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_at')) {
                $where[] = "disabled_at IS NULL";
            }
            $stmt = $pdo->prepare("SELECT * FROM commission_rule_assignments WHERE " . implode(' AND ', $where) . " ORDER BY assigned_at ASC, id ASC");
            $stmt->execute($params);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
            if (!empty($rows)) {
                return $rows;
            }
        }

        $targets = $this->normalizeTargetScope($this->decodeList($rule['target_users'] ?? '[]'));
        if (empty($targets) && (int)($rule['salesperson_user_id'] ?? 0) > 0) {
            $targets = [(string)(int)$rule['salesperson_user_id']];
        }
        if (empty($targets)) {
            $targets = ['ALL'];
        }
        $rows = [];
        foreach ($targets as $target) {
            $rows[] = [
                'id' => 0,
                'rule_id' => $ruleId,
                'salesperson_user_id' => $target === 'ALL' ? null : (int)$target,
                'scope_type' => $target === 'ALL' ? 'ALL' : 'USER',
                'assigned_at' => $rule['valid_from'] ?? null,
                'disabled_at' => null,
            ];
        }
        return $rows;
    }

    private function resolveAssignmentSalespersonIds(array $assignment, int $filterUserId): array
    {
        $scopeType = strtoupper(trim((string)($assignment['scope_type'] ?? 'USER'))) ?: 'USER';
        $salespersonId = (int)($assignment['salesperson_user_id'] ?? 0);
        if ($filterUserId > 0) {
            if ($scopeType === 'ALL') {
                return [$filterUserId];
            }
            return $salespersonId === $filterUserId ? [$filterUserId] : [];
        }
        if ($scopeType === 'ALL') {
            return $this->getActiveAgentIds();
        }
        return $salespersonId > 0 ? [$salespersonId] : [];
    }

    private function resolveAssignmentProductIds(array $assignment, array $rule): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $assignmentId = (int)($assignment['id'] ?? 0);
        if ($assignmentId > 0 && $this->tableExists($pdo, 'assignment_products')) {
            $stmt = $pdo->prepare("SELECT product_id FROM assignment_products WHERE assignment_id = ? ORDER BY product_id ASC");
            $stmt->execute([$assignmentId]);
            $ids = array_values(array_unique(array_filter(array_map('intval', $stmt->fetchAll(\PDO::FETCH_COLUMN) ?: []))));
            if (!empty($ids)) {
                return $ids;
            }
        }
        $ids = $this->extractProductScopeIds($this->decodeList($rule['requested_services'] ?? '[]'));
        if (empty($ids) && (int)($rule['product_id'] ?? 0) > 0) {
            $ids = [(int)$rule['product_id']];
        }
        return array_values(array_unique(array_filter(array_map('intval', $ids))));
    }

    private function getActiveAgentIds(): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $where = ["LOWER(COALESCE(role,'')) = 'agent'"];
        if ($this->columnExists($pdo, 'users', 'is_active')) {
            $where[] = "COALESCE(is_active, 1) = 1";
        }
        $stmt = $pdo->query("SELECT id FROM users WHERE " . implode(' AND ', $where) . " ORDER BY id ASC");
        return array_values(array_unique(array_filter(array_map('intval', $stmt->fetchAll(\PDO::FETCH_COLUMN) ?: []))));
    }

    private function getEligiblePaymentsForScope(array $salespersonIds, array $productIds, string $dateFrom, string $dateTo, array $rule, array $assignment): array
    {
        $payments = [];
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $validFrom = $this->normalizeInputDate((string)($rule['valid_from'] ?? '')) ?: '1970-01-01';
        $validTo = $this->normalizeInputDate((string)($rule['valid_to'] ?? '')) ?: '2999-12-31';
        $assignedAt = $this->normalizeInputDate((string)($assignment['assigned_at'] ?? '')) ?: $validFrom;
        $disabledAt = $this->normalizeInputDate((string)($assignment['disabled_at'] ?? '')) ?: '2999-12-31';
        $scopeFrom = max($dateFrom, $validFrom, $assignedAt);
        $scopeTo = min($dateTo, $validTo, $disabledAt);
        if ($scopeFrom > $scopeTo || empty($salespersonIds)) {
            return [];
        }

        if ($this->tableExists($pdo, 'deal_payments')) {
            $payments = array_merge($payments, $this->queryDealPaymentRowsForScope($salespersonIds, $productIds, $scopeFrom, $scopeTo));
        }
        if (empty($payments) && $this->tableExists($pdo, 'transactions')) {
            $payments = array_merge($payments, $this->queryTransactionRowsForScope($salespersonIds, $productIds, $scopeFrom, $scopeTo));
        }
        return $this->dedupeEligibleDepositRows($payments);
    }

    private function queryDealPaymentRowsForScope(array $salespersonIds, array $productIds, string $dateFrom, string $dateTo): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $depositExpr = $this->buildCoalesceDateExpression('deal_payments', ['deposit_date', 'cheque_cashed_date', 'receipt_date']);
        if ($depositExpr === null) {
            return [];
        }
        $userPlaceholders = implode(',', array_fill(0, count($salespersonIds), '?'));
        $params = array_merge($salespersonIds, [$dateFrom, $dateTo]);
        $financeAcceptedExpr = $this->columnExists($pdo, 'deal_payments', 'is_accepted_by_finance') ? 'AND dp.is_accepted_by_finance = 1' : '';
        $chequeWhere = ($this->columnExists($pdo, 'deal_payments', 'payment_type') && $this->columnExists($pdo, 'deal_payments', 'payment_status') && $this->columnExists($pdo, 'deal_payments', 'cheque_cashed_date'))
            ? "AND (UPPER(COALESCE(dp.payment_type, '')) <> 'CHEQUE' OR (UPPER(COALESCE(dp.payment_type, '')) = 'CHEQUE' AND UPPER(COALESCE(dp.payment_status, '')) = 'CHEQUE_CASHED' AND dp.cheque_cashed_date IS NOT NULL {$financeAcceptedExpr}))"
            : '';
        $productJoin = '';
        $productWhere = '';
        if (!empty($productIds)) {
            $productJoin = 'INNER JOIN deal_products dpr ON dpr.deal_id = d.id';
            $productWhere = 'AND dpr.product_id IN (' . implode(',', array_fill(0, count($productIds), '?')) . ')';
            $params = array_merge($params, $productIds);
        }
        $sql = "
            SELECT dp.id, dp.payment_amount AS amount, DATE({$depositExpr}) AS deposit_date, 'deal_payment' AS source,
                   u.id AS salesperson_user_id, d.didar_deal_id, " . (!empty($productIds) ? 'dpr.product_id' : 'NULL AS product_id') . "
            FROM deal_payments dp
            INNER JOIN deals d ON d.didar_deal_id = dp.deal_didar_id
            INNER JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$productJoin}
            WHERE u.id IN ({$userPlaceholders})
              AND dp.payment_amount > 0
              AND DATE({$depositExpr}) BETWEEN ? AND ?
              {$chequeWhere}
              {$productWhere}
            ORDER BY DATE({$depositExpr}) ASC, dp.id ASC
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    private function queryTransactionRowsForScope(array $salespersonIds, array $productIds, string $dateFrom, string $dateTo): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $depositExpr = $this->buildCoalesceDateExpression('transactions', ['deposit_date', 'payment_date']);
        if ($depositExpr === null) {
            return [];
        }
        $userPlaceholders = implode(',', array_fill(0, count($salespersonIds), '?'));
        $params = array_merge($salespersonIds, [$dateFrom, $dateTo]);
        $financeWhere = $this->columnExists($pdo, 'transactions', 'is_accepted_by_finance') ? 'AND t.is_accepted_by_finance = 1' : '';
        $statusWhere = $this->columnExists($pdo, 'transactions', 'status') ? "AND LOWER(COALESCE(t.status, '')) IN ('confirmed','settled','cleared')" : '';
        $nonChequeWhere = $this->columnExists($pdo, 'transactions', 'method')
            ? "AND LOWER(COALESCE(t.method, '')) NOT LIKE '%check%' AND LOWER(COALESCE(t.method, '')) NOT LIKE '%cheque%' AND COALESCE(t.method, '') NOT LIKE '%چک%'"
            : '';
        $productJoin = '';
        $productWhere = '';
        if (!empty($productIds)) {
            $productJoin = 'INNER JOIN deal_products dpr ON dpr.deal_id = d.id';
            $productWhere = 'AND dpr.product_id IN (' . implode(',', array_fill(0, count($productIds), '?')) . ')';
            $params = array_merge($params, $productIds);
        }
        $sql = "
            SELECT t.id, t.amount, DATE({$depositExpr}) AS deposit_date, 'transaction' AS source,
                   u.id AS salesperson_user_id, d.didar_deal_id, " . (!empty($productIds) ? 'dpr.product_id' : 'NULL AS product_id') . "
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            INNER JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$productJoin}
            WHERE u.id IN ({$userPlaceholders})
              AND t.amount > 0
              AND DATE({$depositExpr}) BETWEEN ? AND ?
              {$statusWhere}
              {$financeWhere}
              {$nonChequeWhere}
              {$productWhere}
            ORDER BY DATE({$depositExpr}) ASC, t.id ASC
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    private function getUserNamesByIds(array $ids): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
        if (empty($ids)) {
            return [];
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $stmt = $pdo->prepare("SELECT id, COALESCE(NULLIF(display_name,''), CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,'')), CONCAT('User #', id)) AS name FROM users WHERE id IN (" . implode(',', array_fill(0, count($ids), '?')) . ")");
        $stmt->execute($ids);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $names = [];
        foreach ($rows as $row) {
            $name = trim((string)($row['name'] ?? ''));
            $names[] = $name !== '' ? $name : ('User #' . (int)($row['id'] ?? 0));
        }
        return $names;
    }

    private function getProductNamesByIds(array $ids): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
        if (empty($ids)) {
            return [];
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $stmt = $pdo->prepare("SELECT id, name FROM products WHERE id IN (" . implode(',', array_fill(0, count($ids), '?')) . ") ORDER BY name ASC");
        $stmt->execute($ids);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $names = [];
        foreach ($rows as $row) {
            $name = trim((string)($row['name'] ?? ''));
            $names[] = $name !== '' ? $name : ('Product #' . (int)($row['id'] ?? 0));
        }
        return $names;
    }

    private function tableExists(\PDO $pdo, string $tableName): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*) AS cnt
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = ?
        ");
        $stmt->execute([$tableName]);
        return ((int)($stmt->fetch(\PDO::FETCH_ASSOC)['cnt'] ?? 0)) > 0;
    }

    private function columnExists(\PDO $pdo, string $tableName, string $column): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*) AS cnt
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = ?
              AND COLUMN_NAME = ?
        ");
        $stmt->execute([$tableName, $column]);
        return ((int)($stmt->fetch(\PDO::FETCH_ASSOC)['cnt'] ?? 0)) > 0;
    }

    // Compatibility no-op methods to avoid breaking existing call sites during migration window.
    public function calculateAndStoreForWonDeal(string $dealDidarId)
    {
        return $this->calculateAndCreateForDeal($dealDidarId);
    }

    public function calculateAndCreateForDeal(string $dealDidarId)
    {
        $dealDidarId = trim($dealDidarId);
        if ($dealDidarId === '') {
            return null;
        }

        $pdo = $this->ruleRepo->getDb()->getPdo();
        $dealStmt = $pdo->prepare("SELECT id, didar_deal_id, owner_didar_id FROM deals WHERE didar_deal_id = ? LIMIT 1");
        $dealStmt->execute([$dealDidarId]);
        $deal = $dealStmt->fetch(\PDO::FETCH_ASSOC);
        if (!$deal) {
            return null;
        }

        $ownerDidarId = trim((string)($deal['owner_didar_id'] ?? ''));
        if ($ownerDidarId === '') {
            return null;
        }

        $userStmt = $pdo->prepare("SELECT id FROM users WHERE didar_user_id = ? LIMIT 1");
        $userStmt->execute([$ownerDidarId]);
        $salespersonUserId = (int)($userStmt->fetchColumn() ?: 0);
        if ($salespersonUserId <= 0) {
            return null;
        }

        $dealInternalId = (int)($deal['id'] ?? 0);
        $payments = $this->getEligibleDepositRows($dealInternalId, $dealDidarId);
        if (empty($payments)) {
            return null;
        }

        $created = [];
        $productIds = $this->getDealProductIds($dealInternalId);
        foreach ($payments as $payment) {
            $depositDate = (string)($payment['deposit_date'] ?? '');
            $amount = (float)($payment['amount'] ?? 0);
            if ($depositDate === '' || $amount <= 0) {
                continue;
            }
            $rule = $this->findBestRuleForDeposit($salespersonUserId, $depositDate, $productIds);
            if (!$rule) {
                Logger::logWarning('COMMISSION_RULE_NOT_MATCHED', [
                    'deal_didar_id' => $dealDidarId,
                    'salesperson_user_id' => $salespersonUserId,
                    'deposit_date' => $depositDate,
                ]);
                continue;
            }
            $commissionAmount = $this->calculateRuleCommission($rule, $amount);
            $rate = $this->deriveEffectiveRate($amount, $commissionAmount);
            $productId = $this->selectMatchedProductId($rule, $productIds);
            $this->upsertDepositCommission($dealDidarId, $salespersonUserId, $payment, $rule, $productId, $rate, $commissionAmount);
            $created[] = [
                'deal_didar_id' => $dealDidarId,
                'commission_rule_id' => (int)($rule['id'] ?? 0),
                'ncr_amount' => $amount,
                'applied_rate_percent' => $rate,
                'calculated_commission' => $commissionAmount,
                'deposit_date' => $depositDate,
            ];
        }

        return empty($created) ? null : $created;
    }

    private function findBestRuleForDeposit(int $salespersonUserId, string $depositDate, array $dealProductIds = []): ?array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $hasStatus = $this->columnExists($pdo, 'commission_rules', 'status');
        $hasIsActive = $this->columnExists($pdo, 'commission_rules', 'is_active');
        $hasValidFrom = $this->columnExists($pdo, 'commission_rules', 'valid_from');
        $hasValidTo = $this->columnExists($pdo, 'commission_rules', 'valid_to');

        $where = ["(salesperson_user_id = ? OR salesperson_user_id IS NULL)"];
        $params = [$salespersonUserId];
        if ($hasStatus) {
            $where[] = "status = 'active'";
        } elseif ($hasIsActive) {
            $where[] = "is_active = 1";
        }
        if ($hasValidFrom) {
            $where[] = "DATE(valid_from) <= ?";
            $params[] = $depositDate;
        }
        if ($hasValidTo) {
            $where[] = "(valid_to IS NULL OR DATE(valid_to) >= ?)";
            $params[] = $depositDate;
        }
        if ($this->columnExists($pdo, 'commission_rules', 'deleted_at')) {
            $where[] = "deleted_at IS NULL";
        }

        $sql = "SELECT * FROM commission_rules WHERE " . implode(' AND ', $where) . " ORDER BY salesperson_user_id DESC, valid_from DESC, id DESC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        foreach ($rows as $row) {
            if ($this->ruleMatchesProducts($row, $dealProductIds)) {
                return $row;
            }
        }
        return null;
    }

    private function calculateRuleCommission(array $rule, float $salesAmount): float
    {
        $salesAmount = max(0.0, $salesAmount);
        if ($salesAmount <= 0) {
            return 0.0;
        }
        $type = strtoupper(trim((string)($rule['rule_type'] ?? 'FIXED_RULE')));
        if ($type === 'TIERED_RULE') {
            return $this->calculateMarginalTierCommission((int)($rule['id'] ?? 0), $salesAmount);
        }

        $fixedAmount = max(0.0, (float)($rule['base_amount'] ?? $rule['fixed_budget_amount'] ?? 0));
        if ($fixedAmount > 0) {
            return round($fixedAmount, 2);
        }
        $rate = max(0.0, min(100.0, (float)($rule['base_rate_percent'] ?? $rule['reward_percentage'] ?? 0)));
        return round($salesAmount * ($rate / 100), 2);
    }

    private function calculateMarginalTierCommission(int $ruleId, float $salesAmount): float
    {
        if ($ruleId <= 0) {
            return 0.0;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_tiers')) {
            return 0.0;
        }
        $hasMinAmount = $this->columnExists($pdo, 'commission_rule_tiers', 'min_amount');
        $hasMaxAmount = $this->columnExists($pdo, 'commission_rule_tiers', 'max_amount');
        $rateColumn = $this->columnExists($pdo, 'commission_rule_tiers', 'allocated_commission_percent')
            ? 'allocated_commission_percent'
            : ($this->columnExists($pdo, 'commission_rule_tiers', 'commission_percent') ? 'commission_percent' : null);
        if (!$hasMinAmount || $rateColumn === null) {
            return 0.0;
        }
        $stmt = $pdo->prepare("
            SELECT min_amount, " . ($hasMaxAmount ? 'max_amount' : 'NULL AS max_amount') . ", {$rateColumn} AS rate
            FROM commission_rule_tiers
            WHERE rule_id = ?
            ORDER BY min_amount ASC, id ASC
        ");
        $stmt->execute([$ruleId]);
        $tiers = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $tiers = $this->normalizePersistedTierRows($tiers);
        $total = 0.0;
        foreach ($tiers as $tier) {
            $min = (float)($tier['min_amount'] ?? 0);
            $max = $tier['max_amount'] === null || (float)$tier['max_amount'] <= 0 ? null : (float)$tier['max_amount'];
            $rate = max(0.0, min(100.0, (float)($tier['rate'] ?? 0)));
            if ($salesAmount < $min) {
                continue;
            }
            $effectiveMin = max(1.0, $min);
            $upper = $max === null ? $salesAmount : min($salesAmount, $max);
            $bandAmount = max(0.0, $upper - $effectiveMin + 1);
            $total += $bandAmount * ($rate / 100);
            if ($max === null || $salesAmount <= $max) {
                break;
            }
        }
        return round($total, 2);
    }

    private function normalizePersistedTierRows(array $tiers): array
    {
        $normalized = [];
        foreach ($tiers as $tier) {
            $min = $this->nullableFloat($tier['min_amount'] ?? null);
            $rate = $this->nullableFloat($tier['rate'] ?? null);
            if ($min === null || $rate === null) {
                continue;
            }
            $max = $this->nullableFloat($tier['max_amount'] ?? null);
            $normalized[] = [
                'min_amount' => $min,
                'max_amount' => $max !== null && $max > 0 ? $max : null,
                'rate' => $rate,
            ];
        }
        if (empty($normalized)) {
            return [];
        }
        try {
            return $this->assertProgressiveBands($normalized, 'min_amount', 'max_amount', 'مبلغی');
        } catch (\InvalidArgumentException $e) {
            Logger::logWarning('COMMISSION_PERSISTED_TIER_VALIDATION_FAILED', [
                'error' => $e->getMessage(),
            ]);
            return [];
        }
    }

    private function getEligibleDepositRows(int $dealInternalId, string $dealDidarId): array
    {
        $rows = [];
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if ($dealInternalId > 0 && $this->tableExists($pdo, 'transactions')) {
            $depositExpr = $this->buildCoalesceDateExpression('transactions', ['deposit_date', 'payment_date']);
            if ($depositExpr === null) {
                Logger::logWarning('COMMISSION_TRANSACTION_DEPOSIT_DATE_MISSING', [
                    'deal_id' => $dealInternalId,
                    'deal_didar_id' => $dealDidarId,
                ]);
            } else {
                $financeWhere = $this->columnExists($pdo, 'transactions', 'is_accepted_by_finance')
                    ? 'AND is_accepted_by_finance = 1'
                    : '';
                $statusWhere = $this->columnExists($pdo, 'transactions', 'status')
                    ? "AND LOWER(COALESCE(status, '')) IN ('confirmed','settled','cleared')"
                    : '';
                $nonChequeWhere = $this->columnExists($pdo, 'transactions', 'method')
                    ? "AND LOWER(COALESCE(method, '')) NOT LIKE '%check%' AND LOWER(COALESCE(method, '')) NOT LIKE '%cheque%' AND COALESCE(method, '') NOT LIKE '%چک%'"
                    : '';
                $stmt = $pdo->prepare("
                    SELECT id, amount, DATE({$depositExpr}) AS deposit_date, 'transaction' AS source, NULL AS payment_type
                    FROM transactions
                    WHERE deal_id = ?
                      AND amount > 0
                      {$statusWhere}
                      {$financeWhere}
                      {$nonChequeWhere}
                      AND {$depositExpr} IS NOT NULL
                    ORDER BY DATE({$depositExpr}) ASC, id ASC
                ");
                $stmt->execute([$dealInternalId]);
                $rows = array_merge($rows, $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: []);
            }
        }

        if ($this->tableExists($pdo, 'deal_payments')) {
            $depositExpr = $this->buildCoalesceDateExpression('deal_payments', ['deposit_date', 'cheque_cashed_date', 'receipt_date']);
            if ($depositExpr === null) {
                Logger::logWarning('COMMISSION_DEAL_PAYMENT_DEPOSIT_DATE_MISSING', [
                    'deal_didar_id' => $dealDidarId,
                ]);
            } else {
                $financeAcceptedExpr = $this->columnExists($pdo, 'deal_payments', 'is_accepted_by_finance')
                    ? 'AND is_accepted_by_finance = 1'
                    : '';
                $hasPaymentType = $this->columnExists($pdo, 'deal_payments', 'payment_type');
                $hasPaymentStatus = $this->columnExists($pdo, 'deal_payments', 'payment_status');
                $hasChequeCashedDate = $this->columnExists($pdo, 'deal_payments', 'cheque_cashed_date');
                $chequeWhere = ($hasPaymentType && $hasPaymentStatus && $hasChequeCashedDate)
                    ? "AND (
                          UPPER(COALESCE(payment_type, '')) <> 'CHEQUE'
                          OR (UPPER(COALESCE(payment_type, '')) = 'CHEQUE' AND UPPER(COALESCE(payment_status, '')) = 'CHEQUE_CASHED' AND cheque_cashed_date IS NOT NULL {$financeAcceptedExpr})
                      )"
                    : '';
                $paymentTypeSelect = $hasPaymentType ? 'payment_type' : 'NULL';
                $stmt = $pdo->prepare("
                    SELECT id, payment_amount AS amount, DATE({$depositExpr}) AS deposit_date, 'deal_payment' AS source, {$paymentTypeSelect} AS payment_type
                    FROM deal_payments
                    WHERE deal_didar_id = ?
                      AND payment_amount > 0
                      {$chequeWhere}
                      AND {$depositExpr} IS NOT NULL
                    ORDER BY DATE({$depositExpr}) ASC, id ASC
                ");
                $stmt->execute([$dealDidarId]);
                $rows = array_merge($rows, $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: []);
            }
        }

        return $this->dedupeEligibleDepositRows($rows);
    }

    private function buildCoalesceDateExpression(string $tableName, array $candidateColumns): ?string
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $parts = [];
        foreach ($candidateColumns as $column) {
            if ($this->columnExists($pdo, $tableName, $column)) {
                $parts[] = "DATE({$column})";
            }
        }
        if (empty($parts)) {
            return null;
        }
        return count($parts) === 1 ? $parts[0] : 'COALESCE(' . implode(', ', $parts) . ')';
    }

    private function dedupeEligibleDepositRows(array $rows): array
    {
        $seen = [];
        $deduped = [];
        foreach ($rows as $row) {
            $source = (string)($row['source'] ?? '');
            $id = (int)($row['id'] ?? 0);
            if ($source === '' || $id <= 0) {
                continue;
            }
            $key = $source . ':' . $id;
            if (isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $deduped[] = $row;
        }
        usort($deduped, static function ($a, $b) {
            return strcmp((string)($a['deposit_date'] ?? ''), (string)($b['deposit_date'] ?? ''))
                ?: strcmp((string)($a['source'] ?? ''), (string)($b['source'] ?? ''))
                ?: ((int)($a['id'] ?? 0) <=> (int)($b['id'] ?? 0));
        });
        return $deduped;
    }

    private function upsertDepositCommission(
        string $dealDidarId,
        int $salespersonUserId,
        array $payment,
        array $rule,
        ?int $productId,
        float $appliedRate,
        float $commissionAmount
    ): void {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $paymentSource = (string)($payment['source'] ?? '');
        $transactionId = $paymentSource === 'transaction' ? (int)($payment['id'] ?? 0) : null;
        $dealPaymentId = $paymentSource === 'deal_payment' ? (int)($payment['id'] ?? 0) : null;
        $depositDate = (string)($payment['deposit_date'] ?? '');
        $amount = (float)($payment['amount'] ?? 0);
        if ($depositDate === '' || $amount <= 0 || ($transactionId === null && $dealPaymentId === null)) {
            return;
        }
        $columns = ['deal_didar_id', 'salesperson_user_id', 'product_id', 'commission_rule_id', 'ncr_amount', 'applied_rate_percent', 'calculated_commission', 'status', 'notes'];
        $values = [$dealDidarId, $salespersonUserId, $productId, (int)($rule['id'] ?? 0), $amount, $appliedRate, $commissionAmount, 'PENDING_MANAGER_REVIEW', 'auto-calculated by deposit date'];
        if ($this->columnExists($pdo, 'deal_commissions', 'transaction_id')) {
            $columns[] = 'transaction_id';
            $values[] = $transactionId ?: null;
        }
        if ($this->columnExists($pdo, 'deal_commissions', 'deal_payment_id')) {
            $columns[] = 'deal_payment_id';
            $values[] = $dealPaymentId ?: null;
        }
        if ($this->columnExists($pdo, 'deal_commissions', 'deposit_date')) {
            $columns[] = 'deposit_date';
            $values[] = $depositDate;
        }
        if ($this->columnExists($pdo, 'deal_commissions', 'billing_month')) {
            $columns[] = 'billing_month';
            $values[] = substr($depositDate, 0, 7);
        }

        $updates = $this->buildCommissionUpsertAssignments($columns);
        $sql = "
            INSERT INTO deal_commissions (" . implode(',', $columns) . ")
            VALUES (" . implode(',', array_fill(0, count($columns), '?')) . ")";
        if (!empty($updates)) {
            $sql .= " ON DUPLICATE KEY UPDATE " . implode(', ', $updates);
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($values);
    }

    private function buildCommissionUpsertAssignments(array $columns): array
    {
        $mutableColumns = array_filter($columns, static function ($column) {
            return !in_array($column, ['deal_didar_id', 'transaction_id', 'deal_payment_id'], true);
        });
        $assignments = [];
        foreach ($mutableColumns as $column) {
            if ($column === 'status') {
                $assignments[] = "{$column} = IF(status = 'PENDING_MANAGER_REVIEW', VALUES({$column}), {$column})";
                continue;
            }
            $assignments[] = "{$column} = VALUES({$column})";
        }
        return $assignments;
    }

    private function getDealProductIds(int $dealInternalId): array
    {
        if ($dealInternalId <= 0) {
            return [];
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'deal_products')) {
            return [];
        }
        $stmt = $pdo->prepare("SELECT product_id FROM deal_products WHERE deal_id = ?");
        $stmt->execute([$dealInternalId]);
        return array_values(array_unique(array_filter(array_map('intval', $stmt->fetchAll(\PDO::FETCH_COLUMN) ?: []))));
    }

    private function ruleMatchesProducts(array $rule, array $dealProductIds): bool
    {
        $ruleProductIds = $this->extractProductScopeIds($this->decodeList($rule['requested_services'] ?? '[]'));
        if (empty($ruleProductIds) && (int)($rule['product_id'] ?? 0) > 0) {
            $ruleProductIds = [(int)$rule['product_id']];
        }
        if (empty($ruleProductIds)) {
            return true;
        }
        if (empty($dealProductIds)) {
            return false;
        }
        return count(array_intersect($ruleProductIds, $dealProductIds)) > 0;
    }

    private function selectMatchedProductId(array $rule, array $dealProductIds): ?int
    {
        $ruleProductIds = $this->extractProductScopeIds($this->decodeList($rule['requested_services'] ?? '[]'));
        if (empty($ruleProductIds) && (int)($rule['product_id'] ?? 0) > 0) {
            $ruleProductIds = [(int)$rule['product_id']];
        }
        if (empty($ruleProductIds)) {
            return $dealProductIds[0] ?? null;
        }
        $matches = array_values(array_intersect($ruleProductIds, $dealProductIds));
        return $matches[0] ?? ($ruleProductIds[0] ?? null);
    }

    private function deriveEffectiveRate(float $amount, float $commissionAmount): float
    {
        return $amount > 0 ? round(($commissionAmount / $amount) * 100, 4) : 0.0;
    }

    private function normalizeInputDate(string $date): ?string
    {
        $date = trim($date);
        if ($date === '') {
            return null;
        }
        $date = str_replace(['.', '-'], '/', $date);
        $date = strtr($date, ['۰'=>'0','۱'=>'1','۲'=>'2','۳'=>'3','۴'=>'4','۵'=>'5','۶'=>'6','۷'=>'7','۸'=>'8','۹'=>'9']);
        if (preg_match('/^(\d{4})\/(\d{1,2})\/(\d{1,2})$/', $date, $m)) {
            $y = (int)$m[1];
            $mo = (int)$m[2];
            $d = (int)$m[3];
            if ($y > 1600) {
                return sprintf('%04d-%02d-%02d', $y, $mo, $d);
            }
            if ($y >= 1300 && $y <= 1600) {
                [$gy, $gm, $gd] = $this->jalaliToGregorian($y, $mo, $d);
                return sprintf('%04d-%02d-%02d', $gy, $gm, $gd);
            }
        }
        $ts = strtotime($date);
        return $ts ? date('Y-m-d', $ts) : null;
    }

    private function jalaliToGregorian(int $jy, int $jm, int $jd): array
    {
        $jy += 1595;
        $days = -355668 + (365 * $jy) + intdiv($jy, 33) * 8 + intdiv((($jy % 33) + 3), 4) + $jd
            + (($jm < 7) ? (($jm - 1) * 31) : ((($jm - 7) * 30) + 186));
        $gy = 400 * intdiv($days, 146097);
        $days %= 146097;
        if ($days > 36524) {
            $gy += 100 * intdiv(--$days, 36524);
            $days %= 36524;
            if ($days >= 365) {
                $days++;
            }
        }
        $gy += 4 * intdiv($days, 1461);
        $days %= 1461;
        if ($days > 365) {
            $gy += intdiv(($days - 1), 365);
            $days = ($days - 1) % 365;
        }
        $gd = $days + 1;
        $sal_a = [0,31,($gy % 4 == 0 && $gy % 100 != 0) || ($gy % 400 == 0) ? 29 : 28,31,30,31,30,31,31,30,31,30,31];
        $gm = 0;
        for ($i = 1; $i <= 12; $i++) {
            if ($gd <= $sal_a[$i]) { $gm = $i; break; }
            $gd -= $sal_a[$i];
        }
        return [$gy, $gm, $gd];
    }

    public function calculateRepCommissionKpis(array $filters): array
    {
        $row = $this->summarizeRulePerformance($filters);
        return [
            'tce' => (float)($row['commission_total'] ?? 0),
            'pcp' => (float)($row['commission_total'] ?? 0),
        ];
    }

    public function calculateManagerCommissionKpis(array $filters): array
    {
        $row = $this->summarizeRulePerformance($filters);
        $tcp = (float)($row['commission_total'] ?? 0);
        $totalNcr = (float)($row['payment_total'] ?? 0);
        return [
            'tcp' => $tcp,
            'roci' => $tcp > 0 ? round($totalNcr / $tcp, 4) : 0.0,
            'ecr' => $totalNcr > 0 ? round(($tcp / $totalNcr) * 100, 4) : 0.0,
            'cpar' => $tcp,
        ];
    }

    public function getDealCommissions(array $filters = []): array
    {
        return $this->getRulePerformanceRows($filters);
    }

    public function updateDealCommissionStatus(int $id, string $status, int $actorUserId = 0): bool
    {
        if ($id <= 0 || trim($status) === '') {
            return false;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $status = strtoupper(trim($status));
        $set = ['status = ?'];
        $params = [$status];
        if ($status === 'PENDING_FINANCE') {
            $set[] = 'manager_reviewed_by_user_id = ?';
            $set[] = 'manager_reviewed_at = NOW()';
            $params[] = $actorUserId > 0 ? $actorUserId : null;
        } elseif ($status === 'READY_FOR_PAYOUT') {
            $set[] = 'finance_reviewed_by_user_id = ?';
            $set[] = 'finance_reviewed_at = NOW()';
            $params[] = $actorUserId > 0 ? $actorUserId : null;
        } elseif ($status === 'SETTLED') {
            $set[] = 'settled_by_user_id = ?';
            $set[] = 'settled_at = NOW()';
            $params[] = $actorUserId > 0 ? $actorUserId : null;
        }
        $params[] = $id;
        $stmt = $pdo->prepare("UPDATE deal_commissions SET " . implode(', ', $set) . " WHERE id = ?");
        return $stmt->execute($params);
    }

    public function deleteCommissionRulesBulk(array $ruleIds, string $actorRole): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $ruleIds), static function ($v) {
            return $v > 0;
        })));
        if (empty($ids)) {
            return ['deleted' => [], 'skipped' => []];
        }

        $role = strtolower(trim($actorRole));
        $isSystem = ($role === 'system' || $role === 'super_admin');
        $isAdmin = ($role === 'admin' || $role === 'super_admin');
        $isCrmSpecialist = ($role === 'crm_specialist');
        if (!$isSystem && !$isAdmin && !$isCrmSpecialist) {
            throw new \RuntimeException('FORBIDDEN_DELETE');
        }

        $pdo = $this->ruleRepo->getDb()->getPdo();
        $deleted = [];
        $skipped = [];
        $hasDeletedAt = $this->columnExists($pdo, 'commission_rules', 'deleted_at');
        $pdo->beginTransaction();
        try {
            foreach ($ids as $id) {
                if (!$isSystem && !$isAdmin && !$this->canAdminDeleteRule($pdo, $id)) {
                    $skipped[] = ['id' => $id, 'reason' => 'unauthorized_or_historical'];
                    continue;
                }
                $sql = $hasDeletedAt
                    ? "UPDATE commission_rules SET deleted_at = NOW(), updated_at = NOW() WHERE id = ? AND deleted_at IS NULL"
                    : "DELETE FROM commission_rules WHERE id = ?";
                $stmt = $pdo->prepare($sql);
                $ok = $stmt->execute([$id]);
                if ($ok && $stmt->rowCount() > 0) {
                    $deleted[] = $id;
                } else {
                    $skipped[] = ['id' => $id, 'reason' => 'not_found_or_locked'];
                }
            }
            $pdo->commit();
            return ['deleted' => $deleted, 'skipped' => $skipped];
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    public function deleteCommissionRule(int $ruleId, string $actorRole): void
    {
        $result = $this->deleteCommissionRulesBulk([$ruleId], $actorRole);
        if (!in_array($ruleId, $result['deleted'], true)) {
            $reason = $result['skipped'][0]['reason'] ?? 'FORBIDDEN_DELETE';
            if ($reason === 'unauthorized_or_historical') {
                throw new \RuntimeException('FORBIDDEN_DELETE');
            }
            throw new \RuntimeException('قانون موردنظر قابل حذف نیست');
        }
    }

    private function canAdminDeleteRule(\PDO $pdo, int $ruleId): bool
    {
        $hasDeletedAt = $this->columnExists($pdo, 'commission_rules', 'deleted_at');
        $ruleSql = "SELECT id, valid_from, valid_to FROM commission_rules WHERE id = ?";
        if ($hasDeletedAt) {
            $ruleSql .= " AND deleted_at IS NULL";
        }
        $ruleSql .= " LIMIT 1";
        $ruleStmt = $pdo->prepare($ruleSql);
        $ruleStmt->execute([$ruleId]);
        $rule = $ruleStmt->fetch(\PDO::FETCH_ASSOC);
        if (!$rule) {
            return false;
        }

        $currentMonth = date('Y-m');
        $validFrom = trim((string)($rule['valid_from'] ?? ''));
        if ($validFrom !== '' && substr($validFrom, 0, 7) < $currentMonth) {
            return false;
        }

        $finalizedStmt = $pdo->prepare("
            SELECT COUNT(*) FROM deal_commissions
            WHERE commission_rule_id = ?
              AND status IN ('APPROVED','READY_FOR_PAYOUT','SETTLED','REJECTED','TRANSFERRED_TO_PAYROLL')
        ");
        $finalizedStmt->execute([$ruleId]);
        $hasFinalized = ((int)$finalizedStmt->fetchColumn()) > 0;
        return !$hasFinalized;
    }

    public function getDealCommissionById(int $id): ?array
    {
        if ($id <= 0) {
            return null;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM deal_commissions WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function deleteDealCommission(int $id, string $actorRole, int $actorUserId): void
    {
        $row = $this->getDealCommissionById($id);
        if (!$row) {
            throw new \RuntimeException('رکورد پورسانت یافت نشد');
        }

        $role = strtolower(trim($actorRole));
        $status = strtoupper(trim((string)($row['status'] ?? '')));
        $isSystem = ($role === 'system' || $role === 'super_admin');
        $isAdmin = ($role === 'admin' || $role === 'super_admin');
        if (!$isSystem && !$isAdmin) {
            throw new \RuntimeException('FORBIDDEN_DELETE');
        }

        $pdo = $this->ruleRepo->getDb()->getPdo();
        $pdo->beginTransaction();
        try {
            $stmt = $pdo->prepare("DELETE FROM deal_commissions WHERE id = ?");
            $stmt->execute([$id]);
            $pdo->commit();
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    private function rebuildDealCommissionsForRule(int $ruleId): void
    {
        if ($ruleId <= 0) {
            return;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $ruleStmt = $pdo->prepare("SELECT * FROM commission_rules WHERE id = ? LIMIT 1");
        $ruleStmt->execute([$ruleId]);
        $rule = $ruleStmt->fetch(\PDO::FETCH_ASSOC);
        if (!$rule) {
            return;
        }

        $validFrom = $this->normalizeInputDate((string)($rule['valid_from'] ?? '')) ?: '1970-01-01';
        $validTo = $this->normalizeInputDate((string)($rule['valid_to'] ?? '')) ?: '2999-12-31';
        $transactionDateExpr = $this->buildCoalesceDateExpression('transactions', ['deposit_date', 'payment_date']);
        $dealPaymentDateExpr = $this->buildCoalesceDateExpression('deal_payments', ['deposit_date', 'cheque_cashed_date', 'receipt_date']);
        $transactionDatePredicate = $transactionDateExpr !== null
            ? "DATE({$transactionDateExpr}) BETWEEN ? AND ?"
            : '0 = 1';
        $dealPaymentDatePredicate = $dealPaymentDateExpr !== null
            ? "DATE({$dealPaymentDateExpr}) BETWEEN ? AND ?"
            : '0 = 1';
        $sql = "SELECT DISTINCT d.didar_deal_id
                FROM deals d
                INNER JOIN users u ON u.didar_user_id = d.owner_didar_id
                LEFT JOIN transactions t ON t.deal_id = d.id
                LEFT JOIN deal_payments dp ON dp.deal_didar_id = d.didar_deal_id
                WHERE u.id = COALESCE(?, u.id)
                  AND (
                      {$transactionDatePredicate}
                      OR {$dealPaymentDatePredicate}
                  )";
        $params = [
            (int)($rule['salesperson_user_id'] ?? 0) > 0 ? (int)$rule['salesperson_user_id'] : null,
        ];
        if ($transactionDateExpr !== null) {
            $params[] = $validFrom;
            $params[] = $validTo;
        }
        if ($dealPaymentDateExpr !== null) {
            $params[] = $validFrom;
            $params[] = $validTo;
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        foreach ($rows as $row) {
            $dealDidarId = (string)($row['didar_deal_id'] ?? '');
            if ($dealDidarId === '') {
                continue;
            }
            $this->calculateAndCreateForDeal($dealDidarId);
        }
    }
}
