<?php
namespace App\Services;

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

class CommissionService
{
    private $ruleRepo;
    private $paymentEligibility;
    private $commissionLineRepo;
    private $priorSalePaymentCache = [];
    private $priorCommissionableSaleCache = [];

    public function __construct(CommissionRuleRepository $ruleRepo, ?PaymentEligibilityService $paymentEligibility = null, ?CommissionLineRepository $commissionLineRepo = null)
    {
        $this->ruleRepo = $ruleRepo;
        $this->paymentEligibility = $paymentEligibility ?: new PaymentEligibilityService();
        $this->commissionLineRepo = $commissionLineRepo;
    }

    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'],
            'title' => $normalized['title'] ?? '',
            'salesperson_user_id' => null,
            'product_id' => !empty($normalized['product_ids']) ? (int)$normalized['product_ids'][0] : null,
            'valid_from' => $normalized['valid_from'] ?? null,
            'valid_to' => $normalized['valid_to'] ?? null,
            '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' => 1,
        ]);
        $pdo = $this->ruleRepo->getDb()->getPdo();
        try {
            $pdo->beginTransaction();
            $ruleId = $this->ruleRepo->save($rule);
            $this->saveRuleTiers($ruleId, $tiers, $ruleType);
            if (!empty($normalized['target_users']) || !empty($normalized['product_ids'])) {
                $this->syncRuleAssignments($ruleId, $normalized['target_users'], $normalized['product_ids'], $normalized['valid_from'] ?? null);
            }
            $pdo->commit();
            return $ruleId;
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    public function setRuleActive(int $ruleId, bool $active): void
    {
        if ($ruleId <= 0) {
            throw new \InvalidArgumentException('شناسه قانون نامعتبر است');
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $stmt = $pdo->prepare('UPDATE commission_rules SET is_active = ?, status = ? WHERE id = ?');
        $stmt->execute([$active ? 1 : 0, $active ? 'active' : 'inactive', $ruleId]);
    }

    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';

        $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)) {
            $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 = 'active';

        $tiers = $ruleType === 'TIERED_RULE'
            ? $this->validateAndNormalizeTiers($this->decodeList($data['tiers'] ?? []), $ruleType)
            : [];

        $title = trim((string)($data['title'] ?? ''));
        $validFrom = $this->normalizeDateString($data['valid_from'] ?? '');
        $validTo = $this->normalizeDateString($data['valid_to'] ?? '');

        return [
            'id' => $id,
            'rule_type' => $ruleType,
            'formula_type' => $formulaType,
            'salesperson_user_id' => $salespersonUserId,
            'target_users' => $targetUsers,
            'requested_services' => $requestedServices,
            'product_ids' => $productIds,
            'title' => $title,
            'valid_from' => $validFrom !== '' ? $validFrom : null,
            'valid_to' => $validTo !== '' ? $validTo : null,
            '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), '?'));
        $sql = "SELECT id FROM users WHERE id IN ({$placeholders})";
        if ($this->columnExists($pdo, 'users', 'is_active')) {
            $sql .= " AND COALESCE(is_active, 1) = 1";
        }
        $stmt = $pdo->prepare($sql);
        $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, ?string $effectiveFrom = null): 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'], $this->assignmentStartDateTime($effectiveFrom)];
                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 getRuleAssignments(array $filters = []): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_assignments')) {
            return [];
        }
        $where = ['1=1'];
        $params = [];
        if (!empty($filters['rule_id'])) {
            $where[] = 'cra.rule_id = ?';
            $params[] = (int)$filters['rule_id'];
        }
        if (!empty($filters['salesperson_user_id'])) {
            $where[] = 'cra.salesperson_user_id = ?';
            $params[] = (int)$filters['salesperson_user_id'];
        }
        $sql = "
            SELECT cra.*, cr.title AS rule_title, cr.rule_type, cr.base_amount, cr.fixed_budget_amount,
                   cr.valid_from AS rule_valid_from, cr.valid_to AS rule_valid_to,
                   cr.base_rate_percent, cr.reward_percentage,
                   COALESCE(NULLIF(u.display_name,''), CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,'')), CONCAT('User #', u.id)) AS salesperson_name,
                   GROUP_CONCAT(DISTINCT p.name ORDER BY p.name SEPARATOR '، ') AS product_names,
                   GROUP_CONCAT(DISTINCT ap.product_id ORDER BY ap.product_id SEPARATOR ',') AS product_ids
            FROM commission_rule_assignments cra
            INNER JOIN commission_rules cr ON cr.id = cra.rule_id
            LEFT JOIN users u ON u.id = cra.salesperson_user_id
            LEFT JOIN assignment_products ap ON ap.assignment_id = cra.id
            LEFT JOIN products p ON p.id = ap.product_id
            WHERE " . implode(' AND ', $where);
        if ($this->columnExists($pdo, 'commission_rules', 'deleted_at')) {
            $sql .= " AND cr.deleted_at IS NULL";
        }
        $sql .= " GROUP BY cra.id ORDER BY cra.disabled_at IS NOT NULL ASC, cra.updated_at DESC, cra.id DESC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        foreach ($rows as &$row) {
            $row['is_active'] = empty($row['disabled_at']) ? 1 : 0;
            $assignmentValidFrom = $this->normalizeDateString($row['valid_from'] ?? '');
            $assignmentValidTo = $this->normalizeDateString($row['valid_to'] ?? '');
            $row['assignment_valid_from'] = $assignmentValidFrom !== '' ? $assignmentValidFrom : null;
            $row['assignment_valid_to'] = $assignmentValidTo !== '' ? $assignmentValidTo : null;
            $row['rule_valid_from'] = $this->normalizeDateString($row['rule_valid_from'] ?? '') ?: null;
            $row['rule_valid_to'] = $this->normalizeDateString($row['rule_valid_to'] ?? '') ?: null;
            $row['valid_from'] = $row['assignment_valid_from'] ?: $row['rule_valid_from'];
            $row['valid_to'] = $row['assignment_valid_to'] ?: $row['rule_valid_to'];
            $ruleProductIds = $this->resolveAssignmentProductIds($row, $row);
            $row['product_ids'] = implode(',', $ruleProductIds);
            $row['product_names'] = empty($ruleProductIds) ? '' : implode('، ', $this->getProductNamesByIds($ruleProductIds));
            $row['product_scope_label'] = trim((string)($row['product_names'] ?? '')) ?: 'همه محصولات';
        }
        unset($row);
        return $rows;
    }

    public function assignRules(array $ruleIds, array $agentIds, array $productIds = [], int $actorUserId = 0): array
    {
        $ruleIds = array_values(array_unique(array_filter(array_map('intval', $ruleIds), static function ($id) {
            return $id > 0;
        })));
        $agentIds = array_values(array_unique(array_filter(array_map('intval', $agentIds), static function ($id) {
            return $id > 0;
        })));
        $productIds = array_values(array_unique(array_filter(array_map('intval', $productIds), static function ($id) {
            return $id > 0;
        })));
        if (empty($ruleIds) || empty($agentIds)) {
            throw new \InvalidArgumentException('حداقل یک قانون و یک کارشناس فروش انتخاب کنید');
        }
        foreach ($agentIds as $agentId) {
            $this->validateTargetUsers([$agentId]);
        }
        $this->validateProductIds($productIds);

        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_assignments')) {
            throw new \RuntimeException('جدول تخصیص قوانین پورسانت وجود ندارد');
        }
        $created = [];
        $reactivated = [];
        $pdo->beginTransaction();
        try {
            foreach ($ruleIds as $ruleId) {
                $ruleModel = $this->ruleRepo->findById($ruleId);
                if (!$ruleModel) {
                    continue;
                }
                $rulePayload = $ruleModel->toArray();
                $assignmentStart = $this->assignmentStartDateTime($rulePayload['valid_from'] ?? null);
                $effectiveProductIds = !empty($productIds) ? $productIds : $this->resolveAssignmentProductIds([], $rulePayload);
                $this->assertNoActiveAssignmentOverlap([$ruleId], $agentIds, $effectiveProductIds);
                foreach ($agentIds as $agentId) {
                    $stmt = $pdo->prepare("
                        SELECT id, disabled_at
                        FROM commission_rule_assignments
                        WHERE rule_id = ? AND salesperson_user_id = ?
                        ORDER BY id DESC
                        LIMIT 1
                    ");
                    $stmt->execute([$ruleId, $agentId]);
                    $existing = $stmt->fetch(\PDO::FETCH_ASSOC);
                    if ($existing && empty($existing['disabled_at'])) {
                        $assignmentId = (int)$existing['id'];
                    } elseif ($existing) {
                        $assignmentId = (int)$existing['id'];
                        $columns = ['disabled_at = NULL'];
                        $params = [];
                        if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_by_user_id')) {
                            $columns[] = 'disabled_by_user_id = NULL';
                        }
                        if ($this->columnExists($pdo, 'commission_rule_assignments', 'disable_reason')) {
                            $columns[] = 'disable_reason = NULL';
                        }
                        if ($this->columnExists($pdo, 'commission_rule_assignments', 'assigned_at')) {
                            $columns[] = 'assigned_at = ?';
                            $params[] = $assignmentStart;
                        }
                        if ($this->columnExists($pdo, 'commission_rule_assignments', 'assigned_by_user_id')) {
                            $columns[] = 'assigned_by_user_id = ?';
                            $params[] = $actorUserId > 0 ? $actorUserId : null;
                        }
                        $params[] = $assignmentId;
                        $pdo->prepare("UPDATE commission_rule_assignments SET " . implode(', ', $columns) . " WHERE id = ?")->execute($params);
                        $reactivated[] = $assignmentId;
                    } else {
                        $columns = ['rule_id', 'salesperson_user_id', 'scope_type', 'assigned_at'];
                        $values = [$ruleId, $agentId, 'USER', $assignmentStart];
                        if ($this->columnExists($pdo, 'commission_rule_assignments', 'assigned_by_user_id')) {
                            $columns[] = 'assigned_by_user_id';
                            $values[] = $actorUserId > 0 ? $actorUserId : null;
                        }
                        $pdo->prepare("INSERT INTO commission_rule_assignments (" . implode(',', $columns) . ") VALUES (" . implode(',', array_fill(0, count($columns), '?')) . ")")->execute($values);
                        $assignmentId = (int)$pdo->lastInsertId();
                        $created[] = $assignmentId;
                    }
                    $this->syncAssignmentProducts($assignmentId, $productIds);
                }
            }
            $pdo->commit();
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
        return ['created' => $created, 'reactivated' => $reactivated];
    }

    private function assertNoActiveAssignmentOverlap(array $ruleIds, array $agentIds, array $productIds, int $excludeAssignmentId = 0): void
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_assignments')) {
            return;
        }
        if (count($ruleIds) > 1) {
            throw new \InvalidArgumentException('برای جلوگیری از محاسبه تکراری، چند قانون فعال را همزمان روی یک دامنه محصول/کارشناس تخصیص ندهید');
        }
        $newProductScope = empty($productIds) ? ['ALL'] : array_map('strval', $productIds);
        foreach ($agentIds as $agentId) {
            $where = [
                'cra.salesperson_user_id = ?',
                'cra.disabled_at IS NULL',
            ];
            $params = [(int)$agentId];
            if ($excludeAssignmentId > 0) {
                $where[] = 'cra.id <> ?';
                $params[] = $excludeAssignmentId;
            }
            if ($this->columnExists($pdo, 'commission_rules', 'deleted_at')) {
                $where[] = 'cr.deleted_at IS NULL';
            }
            if ($this->columnExists($pdo, 'commission_rules', 'status')) {
                $where[] = "cr.status = 'active'";
            } elseif ($this->columnExists($pdo, 'commission_rules', 'is_active')) {
                $where[] = 'COALESCE(cr.is_active, 1) = 1';
            }
            $stmt = $pdo->prepare("
                SELECT cra.id, cra.rule_id, cr.title AS rule_title, cr.requested_services, cr.product_id
                FROM commission_rule_assignments cra
                INNER JOIN commission_rules cr ON cr.id = cra.rule_id
                WHERE " . implode(' AND ', $where) . "
            ");
            $stmt->execute($params);
            $existingRows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
            foreach ($existingRows as $existing) {
                if (in_array((int)($existing['rule_id'] ?? 0), $ruleIds, true)) {
                    continue;
                }
                $existingProductIds = $this->resolveAssignmentProductIds(['id' => (int)($existing['id'] ?? 0)], $existing);
                $existingScope = empty($existingProductIds) ? ['ALL'] : array_map('strval', $existingProductIds);
                if ($this->scopesIntersect($newProductScope, $existingScope)) {
                    $ruleTitle = trim((string)($existing['rule_title'] ?? ''));
                    throw new \InvalidArgumentException(
                        'این تخصیص با قانون فعال دیگری برای همین کارشناس/محصول هم‌پوشانی دارد: ' . ($ruleTitle !== '' ? $ruleTitle : ('Rule #' . (int)$existing['rule_id']))
                    );
                }
            }
        }
    }

    public function setAssignmentActive(int $assignmentId, bool $active, int $actorUserId = 0): bool
    {
        if ($assignmentId <= 0) {
            return false;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_assignments')) {
            return false;
        }
        if ($active) {
            $set = ['disabled_at = NULL'];
            $params = [];
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_by_user_id')) {
                $set[] = 'disabled_by_user_id = NULL';
            }
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'disable_reason')) {
                $set[] = 'disable_reason = NULL';
            }
        } else {
            $set = ['disabled_at = NOW()'];
            $params = [];
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_by_user_id')) {
                $set[] = 'disabled_by_user_id = ?';
                $params[] = $actorUserId > 0 ? $actorUserId : null;
            }
        }
        $params[] = $assignmentId;
        $stmt = $pdo->prepare("UPDATE commission_rule_assignments SET " . implode(', ', $set) . " WHERE id = ?");
        return $stmt->execute($params);
    }

    public function updateAssignmentRule(int $assignmentId, int $ruleId, array $productIds = [], int $actorUserId = 0, ?string $validFrom = null, ?string $validTo = null): bool
    {
        if ($assignmentId <= 0 || $ruleId <= 0) {
            throw new \InvalidArgumentException('شناسه تخصیص یا قانون نامعتبر است');
        }
        $productIds = array_values(array_unique(array_filter(array_map('intval', $productIds), static function ($id) {
            return $id > 0;
        })));
        $this->validateProductIds($productIds);
        $validFrom = $this->normalizeDateString((string)($validFrom ?? ''));
        $validTo = $this->normalizeDateString((string)($validTo ?? ''));
        if ($validFrom !== '' && $validTo !== '' && $validTo < $validFrom) {
            throw new \InvalidArgumentException('تاریخ پایان اعتبار نمی‌تواند قبل از تاریخ شروع باشد');
        }

        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_assignments')) {
            throw new \RuntimeException('جدول تخصیص قوانین پورسانت وجود ندارد');
        }
        $ruleModel = $this->ruleRepo->findById($ruleId);
        if (!$ruleModel) {
            throw new \InvalidArgumentException('قانون انتخاب‌شده معتبر نیست');
        }
        $rulePayload = $ruleModel->toArray();

        $stmt = $pdo->prepare('SELECT * FROM commission_rule_assignments WHERE id = ? LIMIT 1');
        $stmt->execute([$assignmentId]);
        $assignment = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (!$assignment) {
            throw new \InvalidArgumentException('تخصیص پیدا نشد');
        }
        $agentId = (int)($assignment['salesperson_user_id'] ?? 0);
        if ($agentId <= 0) {
            throw new \InvalidArgumentException('فقط تخصیص‌های کارشناس قابل تغییر هستند');
        }
        $this->validateTargetUsers([$agentId]);
        $this->assertNoActiveAssignmentOverlap([$ruleId], [$agentId], $productIds, $assignmentId);

        $sets = ['rule_id = ?', 'scope_type = ?', 'disabled_at = NULL'];
        $params = [$ruleId, 'USER'];
        if ($this->columnExists($pdo, 'commission_rule_assignments', 'assigned_at')) {
            $sets[] = 'assigned_at = ?';
            $params[] = $this->assignmentStartDateTime($validFrom !== '' ? $validFrom : ($rulePayload['valid_from'] ?? null));
        }
        if ($this->columnExists($pdo, 'commission_rule_assignments', 'valid_from')) {
            $sets[] = 'valid_from = ?';
            $params[] = $validFrom !== '' ? $validFrom : null;
        }
        if ($this->columnExists($pdo, 'commission_rule_assignments', 'valid_to')) {
            $sets[] = 'valid_to = ?';
            $params[] = $validTo !== '' ? $validTo : null;
        }
        if ($this->columnExists($pdo, 'commission_rule_assignments', 'assigned_by_user_id')) {
            $sets[] = 'assigned_by_user_id = ?';
            $params[] = $actorUserId > 0 ? $actorUserId : null;
        }
        if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_by_user_id')) {
            $sets[] = 'disabled_by_user_id = NULL';
        }
        if ($this->columnExists($pdo, 'commission_rule_assignments', 'disable_reason')) {
            $sets[] = 'disable_reason = NULL';
        }
        $params[] = $assignmentId;
        $ok = $pdo->prepare('UPDATE commission_rule_assignments SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute($params);
        if ($ok) {
            $this->syncAssignmentProducts($assignmentId, $productIds);
        }
        return $ok;
    }

    public function deleteAssignment(int $assignmentId): bool
    {
        if ($assignmentId <= 0) {
            return false;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_assignments')) {
            return false;
        }
        $stmt = $pdo->prepare('DELETE FROM commission_rule_assignments WHERE id = ?');
        return $stmt->execute([$assignmentId]);
    }

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

        [$dateFrom, $dateTo] = $this->resolveCommissionDateRange($filters);
        $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 = [];
        $claimedPaymentKeys = [];
        foreach ($this->buildRuleAssignmentCandidates($rules, $dateFrom, $dateTo) as $candidate) {
            $rule = $candidate['rule'];
            $assignment = $candidate['assignment'];
            $salespersonIds = $this->resolveAssignmentSalespersonIds($assignment, $filterUserId);
            if (empty($salespersonIds)) {
                continue;
            }
            $productIds = $candidate['product_ids'];
            $payments = $this->getEligiblePaymentsForScope($salespersonIds, $productIds, $dateFrom, $dateTo, $rule, $assignment);
            $payments = $this->claimUniqueCommissionPayments($payments, $claimedPaymentKeys);
            if (empty($payments)) {
                continue;
            }
            $paymentTotal = array_sum(array_map(static function ($payment) {
                return (float)($payment['amount'] ?? 0);
            }, $payments));
            $commissionAmount = $this->calculateRuleCommission($rule, $paymentTotal);
            if ($commissionAmount <= 0) {
                continue;
            }
            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 claimUniqueCommissionPayments(array $payments, array &$claimedPaymentKeys): array
    {
        $unique = [];
        foreach ($payments as $payment) {
            $key = $this->buildCommissionCoverageKey($payment);
            if ($key === '' || isset($claimedPaymentKeys[$key])) {
                continue;
            }
            $claimedPaymentKeys[$key] = true;
            $unique[] = $payment;
        }
        return $unique;
    }

    private function buildCommissionCoverageKey(array $payment): string
    {
        $source = trim((string)($payment['source'] ?? ''));
        $sourceId = (int)($payment['id'] ?? 0);
        if ($source !== '' && $sourceId > 0) {
            return $source . ':' . $sourceId;
        }
        $dealId = trim((string)($payment['didar_deal_id'] ?? ''));
        $agentId = (int)($payment['salesperson_user_id'] ?? 0);
        $productId = (int)($payment['product_id'] ?? 0);
        if ($dealId === '' || $agentId <= 0) {
            return '';
        }
        return $dealId . ':' . $agentId . ':' . $productId;
    }

    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 = [];
        $this->appendRuleActiveWhere($pdo, $where, $params);
        $stmt = $pdo->prepare("SELECT * FROM commission_rules WHERE " . implode(' AND ', $where) . " ORDER BY id ASC");
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $active = [];
        foreach ($rows as $row) {
            $validFrom = $this->normalizeDateString($row['valid_from'] ?? '');
            $validTo = $this->normalizeDateString($row['valid_to'] ?? '');
            if ($validFrom !== '' && $validFrom > $dateTo) {
                continue;
            }
            if ($validTo !== '' && $validTo < $dateFrom) {
                continue;
            }
            if (array_key_exists('valid_from', $row)) {
                $row['valid_from'] = $validFrom !== '' ? $validFrom : null;
            }
            if (array_key_exists('valid_to', $row)) {
                $row['valid_to'] = $validTo !== '' ? $validTo : null;
            }
            $active[] = $row;
        }
        return $active;
    }

    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];
            $dateFrom = (string)($rule['_performance_date_from'] ?? '');
            $dateTo = (string)($rule['_performance_date_to'] ?? '');
            $this->appendAssignmentWindowWhere($pdo, $where, $params, '', null, $dateFrom, $dateTo);
            $stmt = $pdo->prepare("SELECT * FROM commission_rule_assignments WHERE " . implode(' AND ', $where) . " ORDER BY assigned_at ASC, id ASC");
            $stmt->execute($params);
            return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        }

        $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 buildRuleAssignmentCandidates(array $rules, string $dateFrom, string $dateTo): array
    {
        $candidates = [];
        foreach ($rules as $rule) {
            $rule['_performance_date_from'] = $dateFrom;
            $rule['_performance_date_to'] = $dateTo;
            foreach ($this->getRuleAssignmentsForPerformance($rule) as $assignment) {
                $candidates[] = [
                    'rule' => $rule,
                    'assignment' => $assignment,
                    'product_ids' => $this->resolveAssignmentProductIds($assignment, $rule),
                    'specificity' => $this->assignmentSpecificityRank($assignment),
                ];
            }
        }
        usort($candidates, static function (array $a, array $b) {
            $bySpecificity = (int)$b['specificity'] <=> (int)$a['specificity'];
            if ($bySpecificity !== 0) {
                return $bySpecificity;
            }
            $byAssignedAt = strcmp((string)($a['assignment']['assigned_at'] ?? ''), (string)($b['assignment']['assigned_at'] ?? ''));
            if ($byAssignedAt !== 0) {
                return $byAssignedAt;
            }
            return (int)($a['rule']['id'] ?? 0) <=> (int)($b['rule']['id'] ?? 0);
        });
        return $candidates;
    }

    private function assignmentSpecificityRank(array $assignment): int
    {
        $scopeType = strtoupper(trim((string)($assignment['scope_type'] ?? 'USER'))) ?: 'USER';
        if ($scopeType === 'USER' && (int)($assignment['salesperson_user_id'] ?? 0) > 0) {
            return 30;
        }
        if ($scopeType === 'ROLE') {
            return 20;
        }
        return 10;
    }

    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
    {
        $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();
        $roles = ['agent', 'sales_manager', 'sales_supervisor', 'supervisor', 'manager', 'management'];
        $where = ["LOWER(COALESCE(role,'')) IN (" . implode(',', array_fill(0, count($roles), '?')) . ")"];
        if ($this->columnExists($pdo, 'users', 'is_active')) {
            $where[] = "COALESCE(is_active, 1) = 1";
        }
        $stmt = $pdo->prepare("SELECT id FROM users WHERE " . implode(' AND ', $where) . " ORDER BY id ASC");
        $stmt->execute($roles);
        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->normalizeDateString($rule['valid_from'] ?? '') ?: '1970-01-01';
        $validTo = $this->normalizeDateString($rule['valid_to'] ?? '') ?: '2999-12-31';
        $assignmentValidFrom = $this->normalizeDateString($assignment['valid_from'] ?? '');
        if ($assignmentValidFrom !== '') {
            $validFrom = max($validFrom, $assignmentValidFrom);
        }
        $assignmentValidTo = $this->normalizeDateString($assignment['valid_to'] ?? '');
        if ($assignmentValidTo !== '') {
            $validTo = min($validTo, $assignmentValidTo);
        }
        $assignedAt = $this->resolveAssignmentEffectiveStart($rule, $assignment);
        if ($assignedAt !== '') {
            $validFrom = max($validFrom, $assignedAt);
        }
        $disabledAt = $this->normalizeDateString($assignment['disabled_at'] ?? '');
        if ($disabledAt !== '') {
            $validTo = min($validTo, $this->previousDate($disabledAt));
        }
        $scopeFrom = max($dateFrom, $validFrom);
        $scopeTo = min($dateTo, $validTo);
        if ($scopeFrom > $scopeTo || empty($salespersonIds)) {
            return [];
        }

        if ($this->shouldUsePaymentRecords($pdo)) {
            $this->ensureTransactionPaymentRecordProjections($salespersonIds, $productIds, $scopeFrom, $scopeTo);
            return $this->filterCommissionablePaymentRows(
                $this->dedupeEligibleDepositRows($this->queryPaymentRecordRowsForScope($salespersonIds, $productIds, $scopeFrom, $scopeTo))
            );
        }

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

    private function normalizeDateString($value): string
    {
        return DateNormalizer::normalizeGregorianOrJalaliDate($value) ?: '';
    }

    private function filterCommissionablePaymentRows(array $rows): array
    {
        return array_values(array_filter($rows, function (array $row): bool {
            return !$this->isBalancePaymentWithoutPriorSale($row);
        }));
    }

    private function isBalancePaymentWithoutPriorSale(array $payment): bool
    {
        $sequence = (int)($payment['payment_sequence'] ?? 0);
        if ($sequence <= 1) {
            return false;
        }
        return !$this->paymentHasPriorSale($payment);
    }

    private function paymentHasPriorSale(array $payment): bool
    {
        $sequence = (int)($payment['payment_sequence'] ?? 0);
        if ($sequence <= 1) {
            return true;
        }

        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'payment_records')) {
            return false;
        }

        $paymentRecordId = (int)($payment['id'] ?? $payment['payment_record_id'] ?? 0);
        $dealId = (int)($payment['deal_id'] ?? 0);
        $dealDidarId = trim((string)($payment['deal_didar_id'] ?? $payment['didar_deal_id'] ?? ''));
        if ($dealId <= 0 && $dealDidarId === '') {
            return false;
        }

        $effectiveDate = $this->normalizeDateString(
            $payment['deposit_date']
                ?? $payment['effective_date']
                ?? $payment['payment_date']
                ?? $payment['cheque_collection_date']
                ?? ''
        );
        if ($effectiveDate === '') {
            return false;
        }
        $priorDateExpr = $this->buildCoalesceDateExpression('payment_records', [
            'effective_date',
            'deposit_date',
            'payment_date',
            'cheque_collection_date',
            'registered_at',
            'created_at',
        ]);
        if ($priorDateExpr === null) {
            return false;
        }

        $cacheKey = implode('|', [$paymentRecordId, $dealId, $dealDidarId, $effectiveDate]);
        if (array_key_exists($cacheKey, $this->priorSalePaymentCache)) {
            return $this->priorSalePaymentCache[$cacheKey];
        }

        $dealWhere = [];
        $params = [];
        if ($dealId > 0) {
            $dealWhere[] = 'prev.deal_id = ?';
            $params[] = $dealId;
        }
        if ($dealDidarId !== '') {
            $dealWhere[] = 'prev.deal_didar_id = ?';
            $params[] = $dealDidarId;
        }

        $sql = "
            SELECT 1
            FROM payment_records prev
            WHERE (" . implode(' OR ', $dealWhere) . ")
              AND prev.amount > 0
              AND UPPER(COALESCE(prev.refund_status, 'NONE')) <> 'FULL'
              AND COALESCE(prev.is_deleted_placeholder, 0) = 0
              AND COALESCE(prev.payment_sequence, 1) <= 1
              AND {$priorDateExpr} <= ?
        ";
        $params[] = $effectiveDate;
        if ($paymentRecordId > 0) {
            $sql .= ' AND prev.id <> ?';
            $params[] = $paymentRecordId;
        }
        $sql .= ' LIMIT 1';

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $hasPriorSale = (bool)$stmt->fetchColumn();
        $this->priorSalePaymentCache[$cacheKey] = $hasPriorSale;
        return $hasPriorSale;
    }

    private function isBalancePaymentWithoutPriorCommissionableSale(array $payment, array $rule): bool
    {
        $sequence = (int)($payment['payment_sequence'] ?? 0);
        if ($sequence <= 1) {
            return false;
        }
        return !$this->paymentHasPriorCommissionableSale($payment, $rule);
    }

    private function paymentHasPriorCommissionableSale(array $payment, array $rule): bool
    {
        $sequence = (int)($payment['payment_sequence'] ?? 0);
        if ($sequence <= 1) {
            return true;
        }

        $ruleId = (int)($rule['id'] ?? 0);
        $paymentRecordId = (int)($payment['id'] ?? $payment['payment_record_id'] ?? 0);
        $dealId = (int)($payment['deal_id'] ?? 0);
        $dealDidarId = trim((string)($payment['deal_didar_id'] ?? $payment['didar_deal_id'] ?? ''));
        $effectiveDate = $this->normalizeDateString(
            $payment['deposit_date']
                ?? $payment['effective_date']
                ?? $payment['payment_date']
                ?? $payment['cheque_collection_date']
                ?? ''
        );
        if (($dealId <= 0 && $dealDidarId === '') || $effectiveDate === '') {
            return false;
        }

        $cacheKey = implode('|', [$paymentRecordId, $dealId, $dealDidarId, $effectiveDate, $ruleId]);
        if (array_key_exists($cacheKey, $this->priorCommissionableSaleCache)) {
            return $this->priorCommissionableSaleCache[$cacheKey];
        }

        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'payment_records')) {
            $this->priorCommissionableSaleCache[$cacheKey] = false;
            return false;
        }

        $priorDateExpr = $this->buildCoalesceDateExpression('payment_records', [
            'effective_date',
            'deposit_date',
            'payment_date',
            'cheque_collection_date',
            'registered_at',
            'created_at',
        ]);
        if ($priorDateExpr === null) {
            $this->priorCommissionableSaleCache[$cacheKey] = false;
            return false;
        }

        $dealWhere = [];
        $params = [];
        if ($dealId > 0) {
            $dealWhere[] = 'prev.deal_id = ?';
            $params[] = $dealId;
        }
        if ($dealDidarId !== '') {
            $dealWhere[] = 'prev.deal_didar_id = ?';
            $params[] = $dealDidarId;
        }

        $sql = "
            SELECT prev.*
            FROM payment_records prev
            WHERE (" . implode(' OR ', $dealWhere) . ")
              AND prev.amount > 0
              AND UPPER(COALESCE(prev.refund_status, 'NONE')) <> 'FULL'
              AND COALESCE(prev.is_deleted_placeholder, 0) = 0
              AND COALESCE(prev.payment_sequence, 1) <= 1
              AND {$priorDateExpr} <= ?
        ";
        $params[] = $effectiveDate;
        if ($paymentRecordId > 0) {
            $sql .= ' AND prev.id <> ?';
            $params[] = $paymentRecordId;
        }
        $sql .= ' ORDER BY COALESCE(prev.payment_sequence, 999999), prev.id LIMIT 10';

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $requiresSepidar = $this->commissionRequiresSepidar();
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $priorPayment) {
            $eligibility = $this->paymentEligibility->resolveCommissionEligibility($priorPayment, $requiresSepidar);
            if (empty($eligibility['eligible'])) {
                continue;
            }
            $priorAmount = (float)($priorPayment['amount'] ?? 0);
            if ($priorAmount > 0 && $this->calculateRuleCommission($rule, $priorAmount) > 0) {
                $this->priorCommissionableSaleCache[$cacheKey] = true;
                return true;
            }
        }

        $this->priorCommissionableSaleCache[$cacheKey] = false;
        return false;
    }

    private function assignmentStartDateTime(?string $effectiveFrom): string
    {
        $date = $this->normalizeDateString($effectiveFrom ?? '');
        return $date !== '' ? $date . ' 00:00:00' : date('Y-m-d H:i:s');
    }

    private function previousDate(string $date): string
    {
        $timestamp = strtotime($date . ' -1 day');
        if ($timestamp === false) {
            return $date;
        }
        return date('Y-m-d', $timestamp);
    }

    private function queryPaymentRecordRowsForScope(array $salespersonIds, array $productIds, string $dateFrom, string $dateTo): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (empty($salespersonIds) || !$this->tableExists($pdo, 'payment_records')) {
            return [];
        }
        $userPlaceholders = implode(',', array_fill(0, count($salespersonIds), '?'));
        $params = array_merge($salespersonIds, [$dateFrom, $dateTo, $dateFrom, $dateTo]);
        $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);
        }
        $requiresSepidar = $this->commissionRequiresSepidar();
        $sql = "
            SELECT pr.*,
                   pr.id AS id,
                   pr.effective_date AS deposit_date,
                   'payment_record' AS source,
                   pr.payment_owner_user_id AS salesperson_user_id,
                   pr.deal_didar_id AS didar_deal_id,
                   CAST(ltx.linked_id AS UNSIGNED) AS transaction_id,
                   " . (!empty($productIds) ? 'dpr.product_id' : 'NULL AS product_id') . "
            FROM payment_records pr
            LEFT JOIN deals d ON d.id = pr.deal_id
            LEFT JOIN payment_record_links ltx
              ON ltx.payment_record_id = pr.id
             AND ltx.linked_table = 'transactions'
             AND ltx.link_type = 'PROJECTION'
            {$productJoin}
            WHERE pr.payment_owner_user_id IN ({$userPlaceholders})
              AND pr.amount > 0
              AND COALESCE(pr.is_deleted_placeholder, 0) = 0
              AND (
                    pr.effective_date BETWEEN ? AND ?
                 OR (pr.effective_date IS NULL AND COALESCE(pr.deposit_date, pr.payment_date) BETWEEN ? AND ?)
              )
              {$productWhere}
            ORDER BY COALESCE(pr.effective_date, pr.deposit_date, pr.payment_date) ASC, pr.id ASC
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
            $eligibility = $this->paymentEligibility->resolveCommissionEligibility($row, $requiresSepidar);
            if (empty($eligibility['eligible'])) {
                continue;
            }
            $effectiveDate = (string)($eligibility['effective_date'] ?? '');
            if ($effectiveDate < $dateFrom || $effectiveDate > $dateTo) {
                continue;
            }
            $row['deposit_date'] = $effectiveDate;
            $row['source'] = 'payment_record';
            $rows[] = $row;
        }
        return $rows;
    }

    private function ensureTransactionPaymentRecordProjections(array $salespersonIds, array $productIds, string $dateFrom, string $dateTo): void
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (empty($salespersonIds) || !$this->tableExists($pdo, 'transactions') || !$this->tableExists($pdo, 'payment_records')) {
            return;
        }
        $dateParts = [];
        foreach (['deposit_date', 'payment_date'] as $column) {
            if ($this->columnExists($pdo, 'transactions', $column)) {
                $dateParts[] = 'DATE(t.' . $column . ')';
            }
        }
        if (empty($dateParts)) {
            return;
        }
        $dateExpr = count($dateParts) === 1 ? $dateParts[0] : 'COALESCE(' . implode(', ', $dateParts) . ')';
        $userPlaceholders = implode(',', array_fill(0, count($salespersonIds), '?'));
        $params = array_merge($salespersonIds, [$dateFrom, $dateTo]);
        $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);
        }
        $financeReviewJoin = $this->tableExists($pdo, 'payment_reviews')
            ? 'LEFT JOIN payment_reviews pr_review ON pr_review.transaction_id = t.id'
            : '';
        $financeReviewSelect = $this->tableExists($pdo, 'payment_reviews')
            ? 'pr_review.sepidar_registered_at AS review_sepidar_registered_at,'
            : 'NULL AS review_sepidar_registered_at,';
        $chequeReviewJoin = $this->tableExists($pdo, 'cheque_reviews')
            ? 'LEFT JOIN cheque_reviews cr_review ON cr_review.transaction_id = t.id'
            : '';
        $chequeReviewSelect = $this->tableExists($pdo, 'cheque_reviews')
            ? 'cr_review.check_status AS review_check_status, cr_review.collection_status AS review_collection_status, cr_review.collection_date AS review_collection_date,'
            : 'NULL AS review_check_status, NULL AS review_collection_status, NULL AS review_collection_date,';

        $stmt = $pdo->prepare("
            SELECT t.*,
                   d.didar_deal_id,
                   d.contact_didar_id,
                   d.owner_didar_id,
                   {$financeReviewSelect}
                   {$chequeReviewSelect}
                   existing_pr.source_type AS existing_payment_record_source_type,
                   owner_user.id AS deal_owner_user_id,
                   COALESCE(NULLIF(owner_user.display_name,''), CONCAT(COALESCE(owner_user.first_name,''), ' ', COALESCE(owner_user.last_name,''))) AS deal_owner_name,
                   registered_user.id AS registered_user_id,
                   COALESCE(NULLIF(registered_user.display_name,''), CONCAT(COALESCE(registered_user.first_name,''), ' ', COALESCE(registered_user.last_name,''))) AS registered_user_name
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            LEFT JOIN users owner_user ON owner_user.didar_user_id = d.owner_didar_id
            LEFT JOIN users registered_user ON registered_user.didar_user_id = t.registered_by_didar_id
            LEFT JOIN payment_records existing_pr ON existing_pr.id = t.payment_record_id
            {$financeReviewJoin}
            {$chequeReviewJoin}
            {$productJoin}
            WHERE (COALESCE(t.payment_record_id, 0) = 0 OR existing_pr.source_type = 'TRANSACTION')
              AND COALESCE(t.payment_owner_user_id, registered_user.id, owner_user.id, 0) IN ({$userPlaceholders})
              AND t.amount > 0
              AND DATE({$dateExpr}) BETWEEN ? AND ?
              AND LOWER(COALESCE(t.status, 'confirmed')) IN ('confirmed','settled','cleared')
              {$productWhere}
            ORDER BY DATE({$dateExpr}) ASC, t.id ASC
            LIMIT 500
        ");
        $stmt->execute($params);
        $repo = new \App\Repositories\PaymentRecordRepository($this->ruleRepo->getDb());
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
            $this->projectTransactionIntoPaymentRecord($repo, $row);
        }
    }

    private function projectTransactionIntoPaymentRecord(\App\Repositories\PaymentRecordRepository $repo, array $row): void
    {
        $transactionId = (int)($row['id'] ?? 0);
        $amount = (float)($row['amount'] ?? 0);
        $paymentDate = $this->normalizeDateString($row['payment_date'] ?? '');
        if ($transactionId <= 0 || $amount <= 0 || $paymentDate === '') {
            return;
        }
        $method = trim((string)($row['method'] ?? ''));
        $isCheque = ((int)($row['is_cheque_payment'] ?? 0) === 1)
            || stripos($method, 'cheque') !== false
            || stripos($method, 'check') !== false
            || strpos($method, 'چک') !== false;
        $depositDate = $this->normalizeDateString($row['deposit_date'] ?? '');
        $collectionDate = $this->normalizeDateString($row['review_collection_date'] ?? $row['collection_date'] ?? '');
        $effectiveDate = $isCheque ? $collectionDate : ($depositDate ?: $paymentDate);
        $sourceHash = trim((string)($row['payment_source_hash'] ?? ''));
        if ($sourceHash === '') {
            $sourceHash = hash('sha256', 'TRANSACTION|' . $transactionId);
        }
        $paymentUuid = trim((string)($row['payment_uuid'] ?? ''));
        if ($paymentUuid === '') {
            $paymentUuid = $this->uuidFromHash($sourceHash);
        }
        $ownerUserId = (int)($row['payment_owner_user_id'] ?? 0);
        $ownerDidarId = trim((string)($row['payment_owner_didar_id'] ?? ''));
        $ownerName = trim((string)($row['payment_owner_name_snapshot'] ?? ''));
        $ownerSource = 'SESSION';
        if ($ownerUserId <= 0) {
            $ownerUserId = (int)($row['registered_user_id'] ?? 0);
            $ownerDidarId = trim((string)($row['registered_by_didar_id'] ?? ''));
            $ownerName = trim((string)($row['registered_by_name'] ?? $row['registered_user_name'] ?? ''));
        }
        if ($ownerUserId <= 0) {
            $ownerUserId = (int)($row['deal_owner_user_id'] ?? 0);
            $ownerDidarId = trim((string)($row['owner_didar_id'] ?? ''));
            $ownerName = trim((string)($row['deal_owner_name'] ?? ''));
            $ownerSource = 'DEAL_OWNER_BACKFILL';
        }
        $refundAmount = max(0.0, (float)($row['refund_amount'] ?? 0));
        $refundStatus = $refundAmount <= 0 ? 'NONE' : 'PARTIAL';
        if ($amount <= 0 && $refundAmount > 0) {
            $refundStatus = 'FULL';
        }
        $id = $repo->upsertFromLegacySource([
            'source_type' => 'TRANSACTION',
            'source_id' => (string)$transactionId,
            'source_hash' => $sourceHash,
            'deal_id' => (int)($row['deal_id'] ?? 0),
            'deal_didar_id' => (string)($row['didar_deal_id'] ?? ''),
            'contact_didar_id' => (string)($row['contact_didar_id'] ?? ''),
            'payment_uuid' => $paymentUuid,
            'payment_sequence' => isset($row['payment_sequence']) ? (int)$row['payment_sequence'] : null,
            'amount' => $amount,
            'currency' => 'IRR',
            'method' => $method,
            'payment_date' => $paymentDate,
            'deposit_date' => $depositDate ?: null,
            'effective_date' => $effectiveDate ?: null,
            'registered_at' => (string)($row['payment_screen_at'] ?? $row['payment_time'] ?? $paymentDate . ' 00:00:00'),
            'is_deleted_placeholder' => 0,
            'is_refunded' => $refundAmount > 0 ? 1 : 0,
            'refunded_amount' => $refundAmount,
            'refund_status' => $refundStatus,
            'payment_owner_user_id' => $ownerUserId > 0 ? $ownerUserId : null,
            'payment_owner_didar_id' => $ownerDidarId,
            'payment_owner_name_snapshot' => $ownerName,
            'payment_owner_locked_at' => (string)($row['payment_screen_at'] ?? $row['payment_time'] ?? $paymentDate . ' 00:00:00'),
            'payment_owner_source' => $ownerSource,
            'cheque_status' => $isCheque ? strtolower((string)($row['review_check_status'] ?? $row['check_status'] ?? 'pending')) : 'not_cheque',
            'cheque_collection_status' => $isCheque ? strtolower((string)($row['review_collection_status'] ?? $row['collection_status'] ?? 'pending')) : 'not_cheque',
            'cheque_due_date' => $this->normalizeDateString($row['check_due_date'] ?? $row['check_date'] ?? '') ?: null,
            'cheque_collection_date' => $collectionDate ?: null,
            'finance_status' => !empty($row['review_sepidar_registered_at']) ? 'SEPIDAR_REGISTERED' : 'NONE',
            'sepidar_registered_at' => !empty($row['review_sepidar_registered_at']) ? (string)$row['review_sepidar_registered_at'] : null,
            'commission_status' => 'PENDING',
            'raw_snapshot' => ['source' => 'transactions', 'transaction_id' => $transactionId],
        ]);
        if ($id <= 0) {
            return;
        }
        $repo->link($id, 'transactions', (string)$transactionId, 'PROJECTION');
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $stmt = $pdo->prepare("
            UPDATE transactions
            SET payment_record_id = ?,
                payment_uuid = ?,
                payment_owner_user_id = ?,
                payment_owner_didar_id = ?,
                payment_owner_name_snapshot = ?,
                payment_owner_locked_at = ?
            WHERE id = ?
        ");
        $stmt->execute([
            $id,
            $paymentUuid,
            $ownerUserId > 0 ? $ownerUserId : null,
            $ownerDidarId ?: null,
            $ownerName ?: null,
            (string)($row['payment_screen_at'] ?? $row['payment_time'] ?? $paymentDate . ' 00:00:00'),
            $transactionId,
        ]);
    }

    private function uuidFromHash(string $seed): string
    {
        $hex = substr(hash('sha256', $seed), 0, 32);
        return substr($hex, 0, 8) . '-' . substr($hex, 8, 4) . '-4' . substr($hex, 12, 3) . '-8' . substr($hex, 15, 3) . '-' . substr($hex, 18, 12);
    }

    public function getCommissionReadModelMode(): string
    {
        $mode = strtolower(trim((string)(getenv('COMMISSION_READ_MODEL_MODE') ?: 'legacy')));
        return in_array($mode, ['legacy', 'ledger', 'dual_compare'], true) ? $mode : 'legacy';
    }

    public function getCommissionReadModelDiagnostics(array $filters = []): array
    {
        $legacyRows = $this->getRulePerformanceRows($filters);
        $ledgerRows = $this->getLedgerCommissionRows($filters);
        $legacy = $this->summarizeCommissionRows($legacyRows);
        $ledger = $this->summarizeCommissionRows($ledgerRows);
        return [
            'mode' => $this->getCommissionReadModelMode(),
            'legacy' => $legacy,
            'ledger' => $ledger,
            'delta' => [
                'rows_count' => (int)$ledger['rows_count'] - (int)$legacy['rows_count'],
                'payment_total' => round((float)$ledger['payment_total'] - (float)$legacy['payment_total'], 2),
                'commission_total' => round((float)$ledger['commission_total'] - (float)$legacy['commission_total'], 2),
            ],
            'parity' => [
                'payment_total_match' => abs((float)$ledger['payment_total'] - (float)$legacy['payment_total']) < 0.01,
                'commission_total_match' => abs((float)$ledger['commission_total'] - (float)$legacy['commission_total']) < 0.01,
            ],
        ];
    }

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

        [$dateFrom, $dateTo] = $this->resolveCommissionDateRange($filters);
        $filterUserId = $this->resolveCommissionFilterUserId($filters);
        $filterStatus = $this->normalizeLedgerStatusFilter((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;

        $where = [
            'cl.effective_date BETWEEN ? AND ?',
            "COALESCE(cl.status, 'CALCULATED') <> 'VOID'",
        ];
        $params = [$dateFrom, $dateTo];

        if ($filterUserId > 0) {
            $where[] = 'cl.beneficiary_user_id = ?';
            $params[] = $filterUserId;
        }
        if ($filterStatus !== '') {
            $where[] = 'UPPER(cl.status) = ?';
            $params[] = $filterStatus;
        }
        if ($amountMin !== null) {
            $where[] = 'ABS(cl.calculated_commission) >= ?';
            $params[] = $amountMin;
        }
        if ($amountMax !== null) {
            $where[] = 'ABS(cl.calculated_commission) <= ?';
            $params[] = $amountMax;
        }

        $nameExpr = $this->columnExists($pdo, 'users', 'display_name')
            ? "COALESCE(NULLIF(u.display_name,''), CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,'')), CONCAT('User #', u.id))"
            : "COALESCE(CONCAT(u.first_name,' ',u.last_name), CONCAT('User #', u.id))";
        $ownerNameExpr = $this->columnExists($pdo, 'users', 'display_name')
            ? "COALESCE(NULLIF(owner.display_name,''), CONCAT(COALESCE(owner.first_name,''), ' ', COALESCE(owner.last_name,'')), CONCAT('User #', owner.id))"
            : "COALESCE(CONCAT(owner.first_name,' ',owner.last_name), CONCAT('User #', owner.id))";
        $productJoin = $this->tableExists($pdo, 'products') ? 'LEFT JOIN products p ON p.id = cl.product_id' : '';
        $productSelect = $this->tableExists($pdo, 'products') ? 'p.name AS product_name,' : "NULL AS product_name,";

        $sql = "
            SELECT cl.*,
                   cl.id AS commission_line_id,
                   {$nameExpr} AS beneficiary_name,
                   {$ownerNameExpr} AS payment_owner_name,
                   {$productSelect}
                   cr.title AS rule_title,
                   pr.payment_uuid,
                   pr.source_type AS payment_source_type
            FROM commission_lines cl
            LEFT JOIN users u ON u.id = cl.beneficiary_user_id
            LEFT JOIN users owner ON owner.id = cl.payment_owner_user_id
            LEFT JOIN commission_rules cr ON cr.id = cl.commission_rule_id
            LEFT JOIN payment_records pr ON pr.id = cl.payment_record_id
            {$productJoin}
            WHERE " . implode(' AND ', $where) . "
            ORDER BY cl.effective_date ASC, cl.payment_record_id ASC, cl.id ASC
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
            $effectiveDate = (string)($row['effective_date'] ?? '');
            $amount = (float)($row['payment_amount_snapshot'] ?? $row['commission_base_amount'] ?? 0);
            $beneficiaryType = (string)($row['beneficiary_type'] ?? '');
            $rows[] = [
                'id' => (int)($row['id'] ?? 0),
                'row_type' => 'ledger_commission_line',
                'source' => 'commission_lines',
                'commission_line_id' => (int)($row['commission_line_id'] ?? 0),
                'payment_record_id' => (int)($row['payment_record_id'] ?? 0),
                'payment_uuid' => (string)($row['payment_uuid'] ?? ''),
                'deal_didar_id' => (string)($row['deal_didar_id'] ?? ''),
                'rule_id' => (int)($row['commission_rule_id'] ?? 0),
                'rule_title' => (string)($row['rule_title'] ?? ''),
                'assignment_id' => (int)($row['commission_rule_assignment_id'] ?? 0),
                'salesperson_user_id' => (int)($row['beneficiary_user_id'] ?? 0),
                'salesperson_name' => trim((string)($row['beneficiary_name'] ?? '')),
                'beneficiary_user_id' => (int)($row['beneficiary_user_id'] ?? 0),
                'beneficiary_name' => trim((string)($row['beneficiary_name'] ?? '')),
                'beneficiary_type' => $beneficiaryType,
                'payment_owner_user_id' => (int)($row['payment_owner_user_id'] ?? 0),
                'payment_owner_name' => trim((string)($row['payment_owner_name'] ?? '')),
                'product_id' => isset($row['product_id']) && $row['product_id'] !== '' ? (int)$row['product_id'] : null,
                'product_name' => (string)($row['product_name'] ?? ''),
                'ncr_amount' => round($amount, 2),
                'payment_total' => round($amount, 2),
                'payments_count' => 1,
                'applied_rate_percent' => (float)($row['applied_rate_percent'] ?? 0),
                'calculated_commission' => (float)($row['calculated_commission'] ?? 0),
                'calculated_amount' => (float)($row['calculated_commission'] ?? 0),
                'status' => (string)($row['status'] ?? 'CALCULATED'),
                'deposit_date' => $effectiveDate,
                'deposit_date_to' => $effectiveDate,
                'billing_month' => (string)($row['billing_month'] ?? substr($effectiveDate, 0, 7)),
                'is_reversal' => (int)($row['is_reversal'] ?? 0),
                'reversal_of_line_id' => isset($row['reversal_of_line_id']) && $row['reversal_of_line_id'] !== '' ? (int)$row['reversal_of_line_id'] : null,
                'created_at' => (string)($row['created_at'] ?? ''),
                'updated_at' => (string)($row['updated_at'] ?? ''),
            ];
        }
        return $rows;
    }

    private function summarizeCommissionRows(array $rows): array
    {
        $paymentTotal = 0.0;
        $commissionTotal = 0.0;
        $paymentKeys = [];
        $paymentAmountsByKey = [];
        foreach ($rows as $row) {
            $commissionTotal += (float)($row['calculated_commission'] ?? $row['calculated_amount'] ?? 0);
            $paymentAmount = (float)($row['payment_total'] ?? $row['ncr_amount'] ?? 0);
            $paymentRecordId = (int)($row['payment_record_id'] ?? 0);
            if ($paymentRecordId > 0) {
                $key = 'payment_record:' . $paymentRecordId;
                $paymentKeys[$key] = true;
                if (!isset($paymentAmountsByKey[$key])) {
                    $paymentAmountsByKey[$key] = $paymentAmount;
                }
            } elseif (isset($row['payments_count'])) {
                $paymentTotal += $paymentAmount;
            }
        }
        if (!empty($paymentAmountsByKey)) {
            $paymentTotal += array_sum($paymentAmountsByKey);
        }
        $explicitCount = 0;
        foreach ($rows as $row) {
            $explicitCount += (int)($row['payments_count'] ?? 0);
        }
        return [
            'rows_count' => count($rows),
            'payment_total' => round($paymentTotal, 2),
            'commission_total' => round($commissionTotal, 2),
            'payments_count' => !empty($paymentKeys) ? count($paymentKeys) : $explicitCount,
        ];
    }

    private function normalizeLedgerStatusFilter(string $status): string
    {
        $status = strtoupper(trim($status));
        if ($status === '') {
            return '';
        }
        $map = [
            'PENDING_MANAGER_REVIEW' => 'CALCULATED',
            'PENDING_FINANCE' => 'MANAGER_APPROVED',
            'PENDING_FINANCE_APPROVAL' => 'FINANCE_APPROVED',
            'APPROVED' => 'READY_FOR_PAYOUT',
            'PAID' => 'SETTLED',
        ];
        return $map[$status] ?? $status;
    }

    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 shouldUsePaymentRecords(\PDO $pdo): bool
    {
        $mode = strtolower(trim((string)(getenv('COMMISSION_PAYMENT_SOURCE') ?: 'ledger')));
        if ($mode === 'legacy') {
            return false;
        }
        return $this->tableExists($pdo, 'payment_records');
    }

    private function legacyCommissionSourcesEnabled(): bool
    {
        $value = getenv('COMMISSION_LEGACY_DEAL_PAYMENTS_ENABLED');
        if ($value === false || $value === '') {
            return false;
        }
        return in_array(strtolower(trim((string)$value)), ['1', 'true', 'yes', 'on'], true);
    }

    private function commissionRequiresSepidar(): bool
    {
        $value = getenv('COMMISSION_REQUIRES_SEPIDAR');
        if ($value === false || $value === '') {
            return false;
        }
        return in_array(strtolower(trim((string)$value)), ['1', 'true', 'yes', 'on'], true);
    }

    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 calculateLedgerForBillingMonth(string $billingMonth): array
    {
        $period = DateNormalizer::normalizeBillingMonthRange($billingMonth);
        $billingMonth = (string)$period['input_month'];
        $dateFrom = (string)$period['date_from'];
        $dateTo = (string)$period['date_to'];
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'payment_records') || !$this->tableExists($pdo, 'commission_lines')) {
            return [
                'billing_month' => $billingMonth,
                'billing_calendar' => (string)($period['calendar'] ?? ''),
                'date_from' => $dateFrom,
                'date_to' => $dateTo,
                'payments_scanned' => 0,
                'payments_with_rule' => 0,
                'commission_lines_count' => 0,
                'direct_lines_count' => 0,
                'message' => 'جدول‌های payment_records یا commission_lines در دسترس نیستند',
            ];
        }

        $rules = $this->getActiveRulesForPerformance($dateFrom, $dateTo);
        $claimedPaymentKeys = [];
        $paymentsScanned = 0;
        $paymentsWithRule = 0;
        $lineIds = [];
        $directLineIds = [];

        foreach ($this->buildRuleAssignmentCandidates($rules, $dateFrom, $dateTo) as $candidate) {
            $rule = $candidate['rule'];
            $assignment = $candidate['assignment'];
            $salespersonIds = $this->resolveAssignmentSalespersonIds($assignment, 0);
            if (empty($salespersonIds)) {
                continue;
            }

            $productIds = $candidate['product_ids'];
            $payments = $this->getEligiblePaymentsForScope($salespersonIds, $productIds, $dateFrom, $dateTo, $rule, $assignment);
            $payments = $this->claimUniqueCommissionPayments($payments, $claimedPaymentKeys);
            $paymentsScanned += count($payments);

            foreach ($payments as $payment) {
                $effectiveDate = (string)($payment['deposit_date'] ?? $payment['effective_date'] ?? '');
                $amount = (float)($payment['amount'] ?? 0);
                $salespersonUserId = (int)($payment['salesperson_user_id'] ?? $payment['payment_owner_user_id'] ?? 0);
                $paymentRecordId = (int)($payment['id'] ?? 0);
                $dealDidarId = (string)($payment['didar_deal_id'] ?? $payment['deal_didar_id'] ?? '');
                if ($paymentRecordId <= 0 || $effectiveDate === '' || $amount <= 0 || $salespersonUserId <= 0 || $dealDidarId === '') {
                    continue;
                }

                $ruleWithAssignment = $rule;
                $ruleWithAssignment['commission_rule_assignment_id'] = (int)($assignment['id'] ?? 0);
                $commissionAmount = $this->calculateRuleCommission($ruleWithAssignment, $amount);
                if ($commissionAmount <= 0) {
                    continue;
                }
                if ($this->isBalancePaymentWithoutPriorCommissionableSale($payment, $ruleWithAssignment)) {
                    continue;
                }

                $productId = null;
                if ((int)($payment['product_id'] ?? 0) > 0) {
                    $productId = (int)$payment['product_id'];
                } elseif (!empty($productIds)) {
                    $productId = (int)$productIds[0];
                }
                $rate = $this->deriveEffectiveRate($amount, $commissionAmount);
                $created = $this->createCommissionLinesForPayment($dealDidarId, $salespersonUserId, $payment, $ruleWithAssignment, $productId, $rate, $commissionAmount);
                if (!empty($created)) {
                    $paymentsWithRule++;
                    $this->markPaymentRecordCommissionCalculated($paymentRecordId);
                }
                foreach ($created as $beneficiaryType => $lineId) {
                    $lineId = (int)$lineId;
                    if ($lineId <= 0) {
                        continue;
                    }
                    $lineIds[$lineId] = true;
                    if ($beneficiaryType === 'DIRECT_PAYMENT_OWNER') {
                        $directLineIds[$lineId] = true;
                    }
                }
            }
        }

        $result = [
            'billing_month' => $billingMonth,
            'billing_calendar' => (string)($period['calendar'] ?? ''),
            'jalali_month' => (string)($period['jalali_month'] ?? ''),
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'payments_scanned' => $paymentsScanned,
            'payments_with_rule' => $paymentsWithRule,
            'commission_lines_count' => count($lineIds),
            'direct_lines_count' => count($directLineIds),
        ];

        if (count($lineIds) === 0) {
            $result['diagnostics'] = $this->buildLedgerCalculationDiagnostics($dateFrom, $dateTo);
        }

        return $result;
    }

    private function buildLedgerCalculationDiagnostics(string $dateFrom, string $dateTo): array
    {
        $preview = $this->previewLedgerForDateRange($dateFrom, $dateTo, 1000);
        $people = array_map(static function (array $person): array {
            return [
                'owner_user_id' => (int)($person['owner_user_id'] ?? 0),
                'owner_name' => (string)($person['owner_name'] ?? ''),
                'total_payments' => (int)($person['total_payments'] ?? 0),
                'included_payments' => (int)($person['included_payments'] ?? 0),
                'excluded_payments' => (int)($person['excluded_payments'] ?? 0),
                'top_exclusion_reason' => (string)($person['top_exclusion_reason'] ?? ''),
                'top_exclusion_reason_count' => (int)($person['top_exclusion_reason_count'] ?? 0),
                'reason_summary' => array_slice($person['reason_summary'] ?? [], 0, 3),
            ];
        }, array_slice($preview['people'] ?? [], 0, 8));

        return [
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'summary' => $preview['summary'] ?? [],
            'reason_summary' => $preview['reason_summary'] ?? [],
            'people' => $people,
        ];
    }

    public function previewLedgerForDateRange(string $dateFrom, string $dateTo, int $limit = 500): array
    {
        [$dateFrom, $dateTo] = DateNormalizer::normalizeDateRange($dateFrom, $dateTo, DateNormalizer::currentJalaliMonthRange());
        $limit = max(50, min(1000, $limit));
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'payment_records')) {
            return [
                'date_from' => $dateFrom,
                'date_to' => $dateTo,
                'summary' => [
                    'total_payments' => 0,
                    'included_payments' => 0,
                    'excluded_payments' => 0,
                    'estimated_commission' => 0,
                    'coverage_percent' => 0,
                ],
                'reason_summary' => [],
                'payments' => [],
            ];
        }

        $rules = $this->getActiveRulesForPerformance($dateFrom, $dateTo);
        foreach ($rules as &$rule) {
            $rule['_performance_date_from'] = $dateFrom;
            $rule['_performance_date_to'] = $dateTo;
        }
        unset($rule);

        $ruleAssignments = $this->buildRuleAssignmentCandidates($rules, $dateFrom, $dateTo);
        $syncSalespersonIds = [];
        foreach ($ruleAssignments as $candidate) {
            foreach ($this->resolveAssignmentSalespersonIds($candidate['assignment'] ?? [], 0) as $id) {
                $syncSalespersonIds[(int)$id] = true;
            }
        }
        if (!empty($syncSalespersonIds)) {
            $this->ensureTransactionPaymentRecordProjections(array_keys($syncSalespersonIds), [], $dateFrom, $dateTo);
        }

        $rows = $this->queryPaymentRecordsForLedgerPreview($dateFrom, $dateTo, $limit);
        $requiresSepidar = $this->commissionRequiresSepidar();
        $claimedPaymentKeys = [];
        $previewRows = [];
        $included = 0;
        $estimatedCommission = 0.0;
        $reasonCounts = [];

        foreach ($rows as $payment) {
            $paymentRecordId = (int)($payment['id'] ?? 0);
            $paymentSequence = (int)($payment['payment_sequence'] ?? 0);
            $isFirstPayment = $paymentSequence <= 1;
            $isChequePayment = $this->paymentEligibility->isCheque($payment);
            $amount = (float)($payment['amount'] ?? 0);
            $ownerId = (int)($payment['payment_owner_user_id'] ?? 0);
            $dealProductIds = $this->parseIdCsv((string)($payment['deal_product_ids'] ?? ''));
            $eligibility = $this->paymentEligibility->resolveCommissionEligibility($payment, $requiresSepidar);
            $effectiveDate = (string)($eligibility['effective_date'] ?? '');
            $reason = '';
            $matchedRule = null;
            $matchedAssignment = null;
            $inspectedRule = null;
            $inspectedAssignment = null;
            $commissionAmount = 0.0;
            $flags = [
                'owner_match' => false,
                'date_match' => false,
                'product_mismatch' => false,
                'non_positive_commission' => false,
            ];

            if (empty($eligibility['eligible'])) {
                $reason = (string)($eligibility['reason'] ?? 'not_eligible');
            } elseif ($effectiveDate < $dateFrom || $effectiveDate > $dateTo) {
                $reason = 'outside_selected_range';
            } elseif ($paymentRecordId <= 0 || $ownerId <= 0 || $amount <= 0 || trim((string)($payment['deal_didar_id'] ?? '')) === '') {
                $reason = 'missing_required_payment_data';
            } elseif (!$isFirstPayment && $this->isBalancePaymentWithoutPriorSale($payment)) {
                $reason = 'balance_without_prior_sale';
            } else {
                foreach ($ruleAssignments as $candidate) {
                    $rule = $candidate['rule'];
                    $assignment = $candidate['assignment'];
                    $salespersonIds = $this->resolveAssignmentSalespersonIds($assignment, $ownerId);
                    if (empty($salespersonIds)) {
                        continue;
                    }
                    $flags['owner_match'] = true;
                    [$scopeFrom, $scopeTo] = $this->resolveRuleAssignmentWindow($rule, $assignment, $dateFrom, $dateTo);
                    if ($effectiveDate < $scopeFrom || $effectiveDate > $scopeTo) {
                        continue;
                    }
                    $flags['date_match'] = true;
                    $productIds = $candidate['product_ids'];
                    if (!empty($productIds) && (empty($dealProductIds) || !array_intersect($productIds, $dealProductIds))) {
                        $flags['product_mismatch'] = true;
                        continue;
                    }
                    $ruleWithAssignment = $rule;
                    $ruleWithAssignment['commission_rule_assignment_id'] = (int)($assignment['id'] ?? 0);
                    $commissionAmount = $this->calculateRuleCommission($ruleWithAssignment, $amount);
                    if ($commissionAmount <= 0) {
                        $flags['non_positive_commission'] = true;
                        if ((int)($candidate['specificity'] ?? 0) >= 30) {
                            $inspectedRule = $ruleWithAssignment;
                            $inspectedAssignment = $assignment;
                            $coverageKey = $this->buildCommissionCoverageKey([
                                'source' => 'payment_record',
                                'id' => $paymentRecordId,
                            ]);
                            if ($coverageKey !== '') {
                                $claimedPaymentKeys[$coverageKey] = true;
                            }
                            $reason = 'commission_amount_zero';
                            break;
                        }
                        continue;
                    }
                    if (!$isFirstPayment && $this->isBalancePaymentWithoutPriorCommissionableSale($payment, $ruleWithAssignment)) {
                        $reason = 'balance_without_prior_sale';
                        $matchedRule = null;
                        $matchedAssignment = null;
                        break;
                    }
                    $coverageKey = $this->buildCommissionCoverageKey([
                        'source' => 'payment_record',
                        'id' => $paymentRecordId,
                    ]);
                    if ($coverageKey !== '' && isset($claimedPaymentKeys[$coverageKey])) {
                        $reason = 'already_claimed_by_earlier_rule';
                        break;
                    }
                    if ($coverageKey !== '') {
                        $claimedPaymentKeys[$coverageKey] = true;
                    }
                    $matchedRule = $ruleWithAssignment;
                    $matchedAssignment = $assignment;
                    break;
                }
                if ($matchedRule === null && $reason === '') {
                    if (!$flags['owner_match']) {
                        $reason = 'no_assignment_for_payment_owner';
                    } elseif (!$flags['date_match']) {
                        $reason = 'outside_rule_or_assignment_dates';
                    } elseif ($flags['product_mismatch']) {
                        $reason = 'product_scope_mismatch';
                    } elseif ($flags['non_positive_commission']) {
                        $reason = 'commission_amount_zero';
                    } else {
                        $reason = 'no_usable_rule';
                    }
                }
            }

            $status = $matchedRule !== null ? 'included' : 'excluded';
            if ($status === 'included') {
                $included++;
                $estimatedCommission += $commissionAmount;
                $reason = 'included';
            }
            $reasonCounts[$reason] = ($reasonCounts[$reason] ?? 0) + 1;
            $previewRows[] = [
                'payment_id' => $paymentRecordId,
                'payment_label' => 'پرداخت ' . ($payment['payment_sequence'] ?: $paymentRecordId),
                'payment_sequence' => $paymentSequence > 0 ? $paymentSequence : null,
                'payment_kind' => $isFirstPayment ? 'sale' : 'balance',
                'payment_kind_label' => $isFirstPayment ? 'فروش' : 'مانده حساب',
                'payment_method' => trim((string)($payment['method'] ?? '')),
                'is_cheque' => $isChequePayment ? 1 : 0,
                'balance_payment_type' => $isFirstPayment ? '' : ($isChequePayment ? 'cheque' : 'deposit'),
                'balance_payment_type_label' => $isFirstPayment ? '' : ($isChequePayment ? 'چک' : 'واریزی'),
                'status' => $status,
                'status_label' => $status === 'included' ? 'محاسبه می‌شود' : 'جا مانده',
                'reason' => $reason,
                'reason_label' => $this->commissionPreviewReasonLabel($reason),
                'amount' => round($amount, 2),
                'effective_date' => $effectiveDate,
                'effective_date_jalali' => $this->gregorianToJalaliSafe($effectiveDate),
                'owner_user_id' => $ownerId,
                'owner_key' => $ownerId > 0 ? ('user:' . $ownerId) : ('unknown:' . md5(trim((string)($payment['owner_name'] ?? $payment['payment_owner_name_snapshot'] ?? '')))),
                'owner_name' => trim((string)($payment['owner_name'] ?? $payment['payment_owner_name_snapshot'] ?? '')),
                'deal_id' => (int)($payment['deal_id'] ?? 0),
                'deal_didar_id' => (string)($payment['deal_didar_id'] ?? ''),
                'deal_title' => (string)($payment['deal_title'] ?? ''),
                'person_id' => (int)($payment['person_id'] ?? $payment['deal_person_id'] ?? 0),
                'contact_didar_id' => (string)($payment['contact_didar_id'] ?? $payment['deal_contact_didar_id'] ?? ''),
                'customer_name' => trim((string)($payment['customer_name'] ?? '')),
                'customer_mobile' => (string)($payment['customer_mobile'] ?? ''),
                'current_lead_owner_name' => (string)($payment['current_lead_owner_name'] ?? ''),
                'product_names' => trim((string)($payment['product_names'] ?? '')),
                'rule_id' => ($matchedRule ?: $inspectedRule) ? (int)(($matchedRule ?: $inspectedRule)['id'] ?? 0) : null,
                'rule_title' => ($matchedRule ?: $inspectedRule) ? (string)(($matchedRule ?: $inspectedRule)['title'] ?? ('Rule #' . (int)(($matchedRule ?: $inspectedRule)['id'] ?? 0))) : '',
                'assignment_id' => ($matchedAssignment ?: $inspectedAssignment) ? (int)(($matchedAssignment ?: $inspectedAssignment)['id'] ?? 0) : null,
                'estimated_commission' => round($commissionAmount, 2),
            ];
        }

        $reasonSummary = [];
        foreach ($reasonCounts as $reason => $count) {
            $reasonSummary[] = [
                'reason' => $reason,
                'label' => $this->commissionPreviewReasonLabel($reason),
                'count' => (int)$count,
            ];
        }
        usort($reasonSummary, static function ($a, $b) {
            return (int)$b['count'] <=> (int)$a['count'];
        });

        $total = count($previewRows);
        return [
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'summary' => [
                'total_payments' => $total,
                'included_payments' => $included,
                'excluded_payments' => max(0, $total - $included),
                'estimated_commission' => round($estimatedCommission, 2),
                'coverage_percent' => $total > 0 ? round(($included / $total) * 100, 1) : 0,
            ],
            'reason_summary' => $reasonSummary,
            'people' => $this->buildCommissionPreviewPersonSummaries($previewRows),
            'payments' => $previewRows,
        ];
    }

    private function markPaymentRecordCommissionCalculated(int $paymentRecordId): void
    {
        if ($paymentRecordId <= 0) {
            return;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        try {
            $stmt = $pdo->prepare("
                UPDATE payment_records
                SET commission_status = 'CALCULATED', updated_at = NOW()
                WHERE id = ?
                  AND commission_status IN ('NOT_ELIGIBLE','PENDING','CALCULATED')
            ");
            $stmt->execute([$paymentRecordId]);
            if ($this->tableExists($pdo, 'transactions') && $this->columnExists($pdo, 'transactions', 'payment_record_id') && $this->columnExists($pdo, 'transactions', 'commission_status')) {
                $sync = $pdo->prepare("
                    UPDATE transactions
                    SET commission_status = 'CALCULATED'
                    WHERE payment_record_id = ?
                      AND (commission_status IS NULL OR commission_status IN ('NOT_ELIGIBLE','PENDING','CALCULATED'))
                ");
                $sync->execute([$paymentRecordId]);
            }
        } catch (\Throwable $e) {
            Logger::logWarning('COMMISSION_PAYMENT_STATUS_SYNC_FAILED', [
                'payment_record_id' => $paymentRecordId,
                'error' => $e->getMessage(),
            ]);
        }
    }

    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;
        }

        $dealInternalId = (int)($deal['id'] ?? 0);
        $created = [];
        $productIds = $this->getDealProductIds($dealInternalId);
        $payments = $this->getEligibleDepositRows($dealInternalId, $dealDidarId);
        foreach ($payments as $payment) {
            $depositDate = (string)($payment['deposit_date'] ?? '');
            $amount = (float)($payment['amount'] ?? 0);
            if ($depositDate === '' || $amount <= 0) {
                continue;
            }
            $salespersonUserId = (int)($payment['salesperson_user_id'] ?? $payment['payment_owner_user_id'] ?? 0);
            if ($salespersonUserId <= 0) {
                Logger::logWarning('COMMISSION_PAYMENT_OWNER_MISSING', [
                    'deal_didar_id' => $dealDidarId,
                    'payment_source' => (string)($payment['source'] ?? ''),
                    'payment_id' => (int)($payment['id'] ?? 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);
            $commissionLineIds = $this->createCommissionLinesForPayment($dealDidarId, $salespersonUserId, $payment, $rule, $productId, $rate, $commissionAmount);
            $this->upsertDepositCommission($dealDidarId, $salespersonUserId, $payment, $rule, $productId, $rate, $commissionAmount);
            $created[] = [
                'deal_didar_id' => $dealDidarId,
                'commission_line_id' => (int)($commissionLineIds['DIRECT_PAYMENT_OWNER'] ?? 0),
                'commission_line_ids' => $commissionLineIds,
                'commission_rule_id' => (int)($rule['id'] ?? 0),
                'ncr_amount' => $amount,
                'applied_rate_percent' => $rate,
                'calculated_commission' => $commissionAmount,
                'deposit_date' => $depositDate,
            ];
        }
        $created = array_merge($created, $this->syncRefundClawbacksForDeal($dealInternalId, $dealDidarId));

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

    private function findBestRuleForDeposit(int $salespersonUserId, string $depositDate, array $dealProductIds = []): ?array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if ($this->tableExists($pdo, 'commission_rule_assignments')) {
            $sql = "
                SELECT cr.*, cra.id AS commission_rule_assignment_id
                FROM commission_rule_assignments cra
                INNER JOIN commission_rules cr ON cr.id = cra.rule_id
                WHERE cra.salesperson_user_id = ?
            ";
            $params = [$salespersonUserId];
            $where = [];
            $this->appendRuleActiveWhere($pdo, $where, $params, 'cr.', $depositDate);
            $this->appendAssignmentWindowWhere($pdo, $where, $params, 'cra.', $depositDate);
            if (!empty($where)) {
                $sql .= " AND " . implode(' AND ', $where);
            }
            $sql .= " ORDER BY cra.assigned_at DESC, cra.id DESC";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
            foreach ($rows as $row) {
                $assignmentProductIds = $this->resolveAssignmentProductIds(['id' => (int)($row['commission_rule_assignment_id'] ?? 0)], $row);
                if (empty($assignmentProductIds) || count(array_intersect($assignmentProductIds, $dealProductIds)) > 0) {
                    return $row;
                }
            }
            return null;
        }

        $where = ["(salesperson_user_id = ? OR salesperson_user_id IS NULL)"];
        $params = [$salespersonUserId];
        $this->appendRuleActiveWhere($pdo, $where, $params, '', $depositDate);

        $sql = "SELECT * FROM commission_rules WHERE " . implode(' AND ', $where) . " ORDER BY salesperson_user_id 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 appendRuleActiveWhere(\PDO $pdo, array &$where, array &$params, string $alias = '', ?string $asOfDate = null, ?string $dateFrom = null, ?string $dateTo = null): void
    {
        $columnPrefix = $alias;
        if ($this->columnExists($pdo, 'commission_rules', 'deleted_at')) {
            $where[] = $columnPrefix . "deleted_at IS NULL";
        }
        if ($this->columnExists($pdo, 'commission_rules', 'status')) {
            $where[] = "LOWER(COALESCE(" . $columnPrefix . "status, 'active')) = 'active'";
        }
        if ($this->columnExists($pdo, 'commission_rules', 'is_active')) {
            $where[] = "COALESCE(" . $columnPrefix . "is_active, 1) = 1";
        }
        if ($asOfDate !== null && $asOfDate !== '') {
            if ($this->columnExists($pdo, 'commission_rules', 'valid_from')) {
                $where[] = "(" . $columnPrefix . "valid_from IS NULL OR " . $columnPrefix . "valid_from <= ?)";
                $params[] = $asOfDate;
            }
            if ($this->columnExists($pdo, 'commission_rules', 'valid_to')) {
                $where[] = "(" . $columnPrefix . "valid_to IS NULL OR " . $columnPrefix . "valid_to >= ?)";
                $params[] = $asOfDate;
            }
            return;
        }
        if ($dateFrom !== null && $dateFrom !== '' && $dateTo !== null && $dateTo !== '') {
            if ($this->columnExists($pdo, 'commission_rules', 'valid_from')) {
                $where[] = "(" . $columnPrefix . "valid_from IS NULL OR " . $columnPrefix . "valid_from <= ?)";
                $params[] = $dateTo;
            }
            if ($this->columnExists($pdo, 'commission_rules', 'valid_to')) {
                $where[] = "(" . $columnPrefix . "valid_to IS NULL OR " . $columnPrefix . "valid_to >= ?)";
                $params[] = $dateFrom;
            }
        }
    }

    private function appendAssignmentWindowWhere(\PDO $pdo, array &$where, array &$params, string $alias = '', ?string $asOfDate = null, ?string $dateFrom = null, ?string $dateTo = null): void
    {
        $columnPrefix = $alias;
        if ($asOfDate !== null && $asOfDate !== '') {
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'valid_from')) {
                $where[] = "(" . $columnPrefix . "valid_from IS NULL OR " . $columnPrefix . "valid_from <= ?)";
                $params[] = $asOfDate;
            }
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'valid_to')) {
                $where[] = "(" . $columnPrefix . "valid_to IS NULL OR " . $columnPrefix . "valid_to >= ?)";
                $params[] = $asOfDate;
            }
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_at')) {
                $where[] = "(" . $columnPrefix . "disabled_at IS NULL OR DATE(" . $columnPrefix . "disabled_at) > ?)";
                $params[] = $asOfDate;
            }
            return;
        }
        if ($dateFrom !== null && $dateFrom !== '' && $dateTo !== null && $dateTo !== '') {
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'valid_from')) {
                $where[] = "(" . $columnPrefix . "valid_from IS NULL OR " . $columnPrefix . "valid_from <= ?)";
                $params[] = $dateTo;
            }
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'valid_to')) {
                $where[] = "(" . $columnPrefix . "valid_to IS NULL OR " . $columnPrefix . "valid_to >= ?)";
                $params[] = $dateFrom;
            }
            if ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_at')) {
                $where[] = "(" . $columnPrefix . "disabled_at IS NULL OR DATE(" . $columnPrefix . "disabled_at) >= ?)";
                $params[] = $dateFrom;
            }
        } elseif ($this->columnExists($pdo, 'commission_rule_assignments', 'disabled_at')) {
            $where[] = $columnPrefix . "disabled_at IS 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);
        if (empty($tiers)) {
            return 0.0;
        }
        $lastTier = $tiers[count($tiers) - 1];
        $lastMax = $lastTier['max_amount'] === null ? null : (float)$lastTier['max_amount'];
        if ($lastMax !== null && $salesAmount > $lastMax) {
            return 0.0;
        }
        $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 ($this->shouldUsePaymentRecords($pdo)) {
            $ledgerRows = $this->queryPaymentRecordRowsForDeal($dealInternalId, $dealDidarId);
            return $this->filterCommissionablePaymentRows($this->dedupeEligibleDepositRows($ledgerRows));
        }

        if ($this->legacyCommissionSourcesEnabled() && $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->legacyCommissionSourcesEnabled() && $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->filterCommissionablePaymentRows($this->dedupeEligibleDepositRows($rows));
    }

    private function queryPaymentRecordRowsForDeal(int $dealInternalId, string $dealDidarId): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'payment_records')) {
            return [];
        }
        $where = [];
        $params = [];
        if ($dealInternalId > 0) {
            $where[] = 'pr.deal_id = ?';
            $params[] = $dealInternalId;
        }
        if (trim($dealDidarId) !== '') {
            $where[] = 'pr.deal_didar_id = ?';
            $params[] = $dealDidarId;
        }
        if (empty($where)) {
            return [];
        }
        $sql = "
            SELECT pr.*,
                   pr.id AS id,
                   pr.effective_date AS deposit_date,
                   'payment_record' AS source,
                   pr.payment_owner_user_id AS salesperson_user_id,
                   pr.deal_didar_id AS didar_deal_id,
                   CAST(ltx.linked_id AS UNSIGNED) AS transaction_id,
                   NULL AS product_id
            FROM payment_records pr
            LEFT JOIN payment_record_links ltx
              ON ltx.payment_record_id = pr.id
             AND ltx.linked_table = 'transactions'
             AND ltx.link_type = 'PROJECTION'
            WHERE (" . implode(' OR ', $where) . ")
              AND pr.amount > 0
              AND COALESCE(pr.is_deleted_placeholder, 0) = 0
            ORDER BY COALESCE(pr.effective_date, pr.deposit_date, pr.payment_date) ASC, pr.id ASC
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = [];
        $requiresSepidar = $this->commissionRequiresSepidar();
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
            $eligibility = $this->paymentEligibility->resolveCommissionEligibility($row, $requiresSepidar);
            if (empty($eligibility['eligible'])) {
                continue;
            }
            $row['deposit_date'] = (string)($eligibility['effective_date'] ?? '');
            $row['source'] = 'payment_record';
            $rows[] = $row;
        }
        return $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 createCommissionLinesForPayment(
        string $dealDidarId,
        int $salespersonUserId,
        array $payment,
        array $rule,
        ?int $productId,
        float $appliedRate,
        float $commissionAmount
    ): array {
        $lineIds = [];
        $directLineId = $this->createDirectCommissionLine($dealDidarId, $salespersonUserId, $payment, $rule, $productId, $appliedRate, $commissionAmount);
        if ($directLineId > 0) {
            $lineIds['DIRECT_PAYMENT_OWNER'] = $directLineId;
        }

        $amount = (float)($payment['amount'] ?? 0);
        $supervisorUserId = isset($payment['supervisor_user_id_snapshot']) && $payment['supervisor_user_id_snapshot'] !== '' ? (int)$payment['supervisor_user_id_snapshot'] : 0;
        $managerUserId = isset($payment['manager_user_id_snapshot']) && $payment['manager_user_id_snapshot'] !== '' ? (int)$payment['manager_user_id_snapshot'] : 0;
        $effectiveDate = (string)($payment['deposit_date'] ?? $payment['effective_date'] ?? '');
        $paymentOwnerUserId = (int)($payment['payment_owner_user_id'] ?? $salespersonUserId);
        $supervisorAmount = 0.0;

        if ($supervisorUserId > 0) {
            $supervisorRate = $this->getSupervisorCommissionRatePercent($paymentOwnerUserId, $supervisorUserId, $effectiveDate);
            $supervisorAmount = round($amount * ($supervisorRate / 100), 2);
            $lineId = $this->createHierarchyCommissionLine($dealDidarId, $supervisorUserId, 'SUPERVISOR_OF_PAYMENT_OWNER', $payment, $rule, $productId, $supervisorRate, $supervisorAmount);
            if ($lineId > 0) {
                $lineIds['SUPERVISOR_OF_PAYMENT_OWNER'] = $lineId;
            }
        }

        if ($managerUserId <= 0 && $supervisorUserId > 0) {
            $managerUserId = $this->getManagerUserIdForSupervisor($supervisorUserId, $effectiveDate);
        }

        if ($managerUserId > 0 && $supervisorUserId > 0 && $supervisorAmount > 0) {
            $managerRate = $this->getManagerCommissionRatePercent($supervisorUserId, $managerUserId, $effectiveDate);
            $managerAmount = round($amount * ($managerRate / 100), 2);
            $lineId = $this->createHierarchyCommissionLine($dealDidarId, $managerUserId, 'MANAGER_OF_PAYMENT_OWNER', $payment, $rule, $productId, $managerRate, $managerAmount, $amount);
            if ($lineId > 0) {
                $lineIds['MANAGER_OF_PAYMENT_OWNER'] = $lineId;
            }
        }

        return $lineIds;
    }

    private function createDirectCommissionLine(
        string $dealDidarId,
        int $salespersonUserId,
        array $payment,
        array $rule,
        ?int $productId,
        float $appliedRate,
        float $commissionAmount,
        ?float $commissionBaseAmount = null
    ): int {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->commissionLineRepo || !$this->tableExists($pdo, 'commission_lines')) {
            return 0;
        }
        $paymentRecordId = (string)($payment['source'] ?? '') === 'payment_record' ? (int)($payment['id'] ?? 0) : 0;
        $effectiveDate = (string)($payment['deposit_date'] ?? $payment['effective_date'] ?? '');
        $amount = (float)($payment['amount'] ?? 0);
        if ($paymentRecordId <= 0 || $salespersonUserId <= 0 || $effectiveDate === '' || $amount <= 0) {
            return 0;
        }

        $beneficiaryType = 'DIRECT_PAYMENT_OWNER';
        $ruleId = (int)($rule['id'] ?? 0);
        $sourceHash = hash('sha256', implode('|', [
            'commission_line',
            $paymentRecordId,
            $beneficiaryType,
            $salespersonUserId,
            $ruleId,
            $productId ?: 0,
            $effectiveDate,
        ]));

        $hierarchySnapshot = [
            'supervisor_user_id_snapshot' => isset($payment['supervisor_user_id_snapshot']) && $payment['supervisor_user_id_snapshot'] !== '' ? (int)$payment['supervisor_user_id_snapshot'] : null,
            'manager_user_id_snapshot' => isset($payment['manager_user_id_snapshot']) && $payment['manager_user_id_snapshot'] !== '' ? (int)$payment['manager_user_id_snapshot'] : null,
            'hierarchy_snapshot_at' => (string)($payment['hierarchy_snapshot_at'] ?? $effectiveDate),
        ];

        try {
            return $this->commissionLineRepo->upsertImmutable([
                'payment_record_id' => $paymentRecordId,
                'deal_didar_id' => $dealDidarId,
                'beneficiary_user_id' => $salespersonUserId,
                'beneficiary_type' => $beneficiaryType,
                'payment_owner_user_id' => (int)($payment['payment_owner_user_id'] ?? $salespersonUserId),
                'product_id' => $productId,
                'commission_rule_id' => $ruleId ?: null,
                'commission_rule_assignment_id' => isset($rule['commission_rule_assignment_id']) && $rule['commission_rule_assignment_id'] !== '' ? (int)$rule['commission_rule_assignment_id'] : null,
                'payment_amount_snapshot' => $amount,
                'commission_base_amount' => $amount,
                'applied_rate_percent' => $appliedRate,
                'calculated_commission' => $commissionAmount,
                'effective_date' => $effectiveDate,
                'billing_month' => substr($effectiveDate, 0, 7),
                'rule_snapshot' => $rule,
                'tier_snapshot' => $this->getRuleTiers($ruleId),
                'payment_snapshot' => $payment,
                'product_snapshot' => $this->getProductSnapshot($productId),
                'hierarchy_snapshot' => $hierarchySnapshot,
                'status' => 'CALCULATED',
                'source_hash' => $sourceHash,
            ]);
        } catch (\Throwable $e) {
            Logger::logWarning('COMMISSION_LINE_CREATE_FAILED', [
                'payment_record_id' => $paymentRecordId,
                'deal_didar_id' => $dealDidarId,
                'error' => $e->getMessage(),
            ]);
            return 0;
        }
    }

    private function createHierarchyCommissionLine(
        string $dealDidarId,
        int $beneficiaryUserId,
        string $beneficiaryType,
        array $payment,
        array $rule,
        ?int $productId,
        float $appliedRate,
        float $commissionAmount,
        ?float $commissionBaseAmount = null
    ): int {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->commissionLineRepo || !$this->tableExists($pdo, 'commission_lines')) {
            return 0;
        }
        $paymentRecordId = (string)($payment['source'] ?? '') === 'payment_record' ? (int)($payment['id'] ?? 0) : 0;
        $effectiveDate = (string)($payment['deposit_date'] ?? $payment['effective_date'] ?? '');
        $amount = (float)($payment['amount'] ?? 0);
        $paymentOwnerUserId = (int)($payment['payment_owner_user_id'] ?? 0);
        if ($paymentRecordId <= 0 || $beneficiaryUserId <= 0 || $paymentOwnerUserId <= 0 || $effectiveDate === '' || $amount <= 0 || $commissionAmount <= 0) {
            return 0;
        }

        $beneficiaryType = strtoupper(trim($beneficiaryType));
        if (!in_array($beneficiaryType, ['SUPERVISOR_OF_PAYMENT_OWNER', 'MANAGER_OF_PAYMENT_OWNER', 'MANAGER_OF_SUPERVISOR_COMMISSION'], true)) {
            return 0;
        }

        $ruleId = (int)($rule['id'] ?? 0);
        $sourceHash = hash('sha256', implode('|', [
            'commission_line',
            $paymentRecordId,
            $beneficiaryType,
            $ruleId,
            $productId ?: 0,
            $effectiveDate,
        ]));

        $hierarchySnapshot = [
            'supervisor_user_id_snapshot' => isset($payment['supervisor_user_id_snapshot']) && $payment['supervisor_user_id_snapshot'] !== '' ? (int)$payment['supervisor_user_id_snapshot'] : null,
            'manager_user_id_snapshot' => isset($payment['manager_user_id_snapshot']) && $payment['manager_user_id_snapshot'] !== '' ? (int)$payment['manager_user_id_snapshot'] : null,
            'hierarchy_snapshot_at' => (string)($payment['hierarchy_snapshot_at'] ?? $effectiveDate),
            'hierarchy_commission_rate_percent' => $appliedRate,
            'hierarchy_commission_base_type' => $beneficiaryType === 'MANAGER_OF_SUPERVISOR_COMMISSION' ? 'SUPERVISOR_COMMISSION' : 'PAYMENT_AMOUNT',
        ];

        try {
            return $this->commissionLineRepo->upsertImmutable([
                'payment_record_id' => $paymentRecordId,
                'deal_didar_id' => $dealDidarId,
                'beneficiary_user_id' => $beneficiaryUserId,
                'beneficiary_type' => $beneficiaryType,
                'payment_owner_user_id' => $paymentOwnerUserId,
                'product_id' => $productId,
                'commission_rule_id' => $ruleId ?: null,
                'commission_rule_assignment_id' => isset($rule['commission_rule_assignment_id']) && $rule['commission_rule_assignment_id'] !== '' ? (int)$rule['commission_rule_assignment_id'] : null,
                'payment_amount_snapshot' => $amount,
                'commission_base_amount' => $commissionBaseAmount !== null ? (float)$commissionBaseAmount : $amount,
                'applied_rate_percent' => $appliedRate,
                'calculated_commission' => $commissionAmount,
                'effective_date' => $effectiveDate,
                'billing_month' => substr($effectiveDate, 0, 7),
                'rule_snapshot' => $rule,
                'tier_snapshot' => $this->getRuleTiers($ruleId),
                'payment_snapshot' => $payment,
                'product_snapshot' => $this->getProductSnapshot($productId),
                'hierarchy_snapshot' => $hierarchySnapshot,
                'status' => 'CALCULATED',
                'source_hash' => $sourceHash,
            ]);
        } catch (\Throwable $e) {
            Logger::logWarning('COMMISSION_HIERARCHY_LINE_CREATE_FAILED', [
                'payment_record_id' => $paymentRecordId,
                'deal_didar_id' => $dealDidarId,
                'beneficiary_type' => $beneficiaryType,
                'error' => $e->getMessage(),
            ]);
            return 0;
        }
    }

    private function getHierarchyCommissionRatePercent(string $beneficiaryType): float
    {
        $beneficiaryType = strtoupper(trim($beneficiaryType));
        $isManagerCommission = in_array($beneficiaryType, ['MANAGER_OF_PAYMENT_OWNER', 'MANAGER_OF_SUPERVISOR_COMMISSION'], true);
        $envName = $isManagerCommission
            ? 'COMMISSION_MANAGER_RATE_PERCENT'
            : 'COMMISSION_SUPERVISOR_RATE_PERCENT';
        $raw = getenv($envName);
        if ($raw !== false && trim((string)$raw) !== '') {
            return max(0.0, min(100.0, (float)$raw));
        }
        return $isManagerCommission ? 0.3 : 1.0;
    }

    private function getSupervisorCommissionRatePercent(int $paymentOwnerUserId, int $supervisorUserId, string $effectiveDate): float
    {
        $fallback = $this->getHierarchyCommissionRatePercent('SUPERVISOR_OF_PAYMENT_OWNER');
        if ($paymentOwnerUserId <= 0 || $supervisorUserId <= 0 || $effectiveDate === '') {
            return $fallback;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'user_hierarchy_assignments')
            || !$this->columnExists($pdo, 'user_hierarchy_assignments', 'supervisor_commission_rate_percent')) {
            return $fallback;
        }
        $date = substr($effectiveDate, 0, 10);
        $stmt = $pdo->prepare("
            SELECT supervisor_commission_rate_percent
            FROM user_hierarchy_assignments
            WHERE user_id = ?
              AND supervisor_user_id = ?
              AND effective_from <= ?
              AND (effective_to IS NULL OR effective_to >= ?)
            ORDER BY effective_from DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute([$paymentOwnerUserId, $supervisorUserId, $date, $date]);
        $value = $stmt->fetchColumn();
        if ($value === false || $value === null || $value === '') {
            return $fallback;
        }
        return max(0.0, min(100.0, (float)$value));
    }

    private function getManagerUserIdForSupervisor(int $supervisorUserId, string $effectiveDate): int
    {
        if ($supervisorUserId <= 0 || $effectiveDate === '') {
            return 0;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'user_hierarchy_assignments')) {
            return 0;
        }
        $date = substr($effectiveDate, 0, 10);
        $stmt = $pdo->prepare("
            SELECT manager_user_id
            FROM user_hierarchy_assignments
            WHERE user_id = ?
              AND manager_user_id IS NOT NULL
              AND manager_user_id > 0
              AND effective_from <= ?
              AND (effective_to IS NULL OR effective_to >= ?)
            ORDER BY effective_from DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute([$supervisorUserId, $date, $date]);
        return (int)($stmt->fetchColumn() ?: 0);
    }

    private function getManagerCommissionRatePercent(int $supervisorUserId, int $managerUserId, string $effectiveDate): float
    {
        $fallback = $this->getHierarchyCommissionRatePercent('MANAGER_OF_SUPERVISOR_COMMISSION');
        if ($supervisorUserId <= 0 || $managerUserId <= 0 || $effectiveDate === '') {
            return $fallback;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'user_hierarchy_assignments')
            || !$this->columnExists($pdo, 'user_hierarchy_assignments', 'manager_commission_rate_percent')) {
            return $fallback;
        }
        $date = substr($effectiveDate, 0, 10);
        $stmt = $pdo->prepare("
            SELECT manager_commission_rate_percent
            FROM user_hierarchy_assignments
            WHERE user_id = ?
              AND manager_user_id = ?
              AND effective_from <= ?
              AND (effective_to IS NULL OR effective_to >= ?)
            ORDER BY effective_from DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute([$supervisorUserId, $managerUserId, $date, $date]);
        $value = $stmt->fetchColumn();
        if ($value === false || $value === null || $value === '') {
            return $fallback;
        }
        return max(0.0, min(100.0, (float)$value));
    }

    private function syncRefundClawbacksForDeal(int $dealInternalId, string $dealDidarId): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->commissionLineRepo || !$this->tableExists($pdo, 'commission_lines') || !$this->tableExists($pdo, 'payment_records')) {
            return [];
        }
        $where = [];
        $params = [];
        if ($dealInternalId > 0) {
            $where[] = 'deal_id = ?';
            $params[] = $dealInternalId;
        }
        if (trim($dealDidarId) !== '') {
            $where[] = 'deal_didar_id = ?';
            $params[] = $dealDidarId;
        }
        if (empty($where)) {
            return [];
        }
        $stmt = $pdo->prepare("
            SELECT *
            FROM payment_records
            WHERE (" . implode(' OR ', $where) . ")
              AND amount > 0
              AND COALESCE(is_deleted_placeholder, 0) = 0
              AND (
                    COALESCE(refunded_amount, 0) > 0
                    OR UPPER(COALESCE(refund_status, 'NONE')) IN ('PARTIAL','FULL')
                  )
            ORDER BY id ASC
        ");
        $stmt->execute($params);
        $created = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $paymentRecord) {
            $paymentRecordId = (int)($paymentRecord['id'] ?? 0);
            $amount = (float)($paymentRecord['amount'] ?? 0);
            $refundedAmount = min($amount, max(0.0, (float)($paymentRecord['refunded_amount'] ?? 0)));
            if ($paymentRecordId <= 0 || $amount <= 0 || $refundedAmount <= 0) {
                continue;
            }
            $lines = $this->commissionLineRepo->listByPaymentRecordId($paymentRecordId);
            foreach ($lines as $line) {
                if ((string)($line['beneficiary_type'] ?? '') === 'CLAWBACK' || !empty($line['is_reversal'])) {
                    continue;
                }
                $originalLineId = (int)($line['id'] ?? 0);
                $originalCommission = max(0.0, (float)($line['calculated_commission'] ?? 0));
                if ($originalLineId <= 0 || $originalCommission <= 0) {
                    continue;
                }
                $targetClawback = round($originalCommission * ($refundedAmount / $amount), 2);
                $existingClawback = $this->sumClawbacksForLine($originalLineId);
                $delta = round($targetClawback - $existingClawback, 2);
                if ($delta <= 0.01) {
                    continue;
                }
                $effectiveDate = (string)($paymentRecord['effective_date'] ?? $paymentRecord['deposit_date'] ?? $paymentRecord['payment_date'] ?? date('Y-m-d'));
                $sourceHash = hash('sha256', implode('|', [
                    'commission_clawback',
                    $paymentRecordId,
                    $originalLineId,
                    number_format($targetClawback, 2, '.', ''),
                ]));
                $lineId = $this->commissionLineRepo->upsertImmutable([
                    'payment_record_id' => $paymentRecordId,
                    'deal_didar_id' => (string)($paymentRecord['deal_didar_id'] ?? $dealDidarId),
                    'beneficiary_user_id' => (int)($line['beneficiary_user_id'] ?? 0),
                    'beneficiary_type' => 'CLAWBACK',
                    'payment_owner_user_id' => (int)($line['payment_owner_user_id'] ?? $paymentRecord['payment_owner_user_id'] ?? 0),
                    'product_id' => $line['product_id'] ?? null,
                    'commission_rule_id' => $line['commission_rule_id'] ?? null,
                    'commission_rule_assignment_id' => $line['commission_rule_assignment_id'] ?? null,
                    'payment_amount_snapshot' => -$refundedAmount,
                    'commission_base_amount' => -$refundedAmount,
                    'applied_rate_percent' => (float)($line['applied_rate_percent'] ?? 0),
                    'calculated_commission' => -$delta,
                    'effective_date' => $effectiveDate,
                    'billing_month' => substr($effectiveDate, 0, 7),
                    'rule_snapshot_json' => $line['rule_snapshot_json'] ?? null,
                    'tier_snapshot_json' => $line['tier_snapshot_json'] ?? null,
                    'payment_snapshot' => $paymentRecord,
                    'product_snapshot_json' => $line['product_snapshot_json'] ?? null,
                    'hierarchy_snapshot_json' => $line['hierarchy_snapshot_json'] ?? null,
                    'status' => 'CALCULATED',
                    'is_reversal' => 1,
                    'reversal_of_line_id' => $originalLineId,
                    'source_hash' => $sourceHash,
                ]);
                $created[] = [
                    'commission_line_id' => $lineId,
                    'reversal_of_line_id' => $originalLineId,
                    'payment_record_id' => $paymentRecordId,
                    'beneficiary_type' => 'CLAWBACK',
                    'calculated_commission' => -$delta,
                ];
            }
        }
        return $created;
    }

    private function sumClawbacksForLine(int $originalLineId): float
    {
        if ($originalLineId <= 0) {
            return 0.0;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(ABS(calculated_commission)), 0)
            FROM commission_lines
            WHERE reversal_of_line_id = ?
              AND beneficiary_type = 'CLAWBACK'
              AND COALESCE(is_reversal, 0) = 1
        ");
        $stmt->execute([$originalLineId]);
        return round((float)$stmt->fetchColumn(), 2);
    }

    private function getProductSnapshot(?int $productId): ?array
    {
        if (!$productId || $productId <= 0) {
            return null;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'products')) {
            return null;
        }
        $stmt = $pdo->prepare('SELECT * FROM products WHERE id = ? LIMIT 1');
        $stmt->execute([$productId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    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'] ?? '');
        $paymentRecordId = $paymentSource === 'payment_record' ? (int)($payment['id'] ?? 0) : null;
        $transactionId = $paymentSource === 'transaction' ? (int)($payment['id'] ?? 0) : (int)($payment['transaction_id'] ?? 0);
        if ($transactionId <= 0) {
            $transactionId = null;
        }
        $dealPaymentId = $paymentSource === 'deal_payment' ? (int)($payment['id'] ?? 0) : null;
        $depositDate = (string)($payment['deposit_date'] ?? '');
        $amount = (float)($payment['amount'] ?? 0);
        if ($depositDate === '' || $amount <= 0 || ($paymentRecordId === null && $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', 'payment_record_id')) {
            $columns[] = 'payment_record_id';
            $values[] = $paymentRecordId ?: null;
        }
        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 resolveCommissionDateRange(array $filters): array
    {
        $dateFrom = DateNormalizer::normalizeGregorianOrJalaliDate((string)($filters['date_from'] ?? ''));
        $dateTo = DateNormalizer::normalizeGregorianOrJalaliDate((string)($filters['date_to'] ?? ''));
        $billingMonth = trim((string)($filters['billing_month'] ?? ''));

        if (($dateFrom === null || $dateTo === null) && $billingMonth !== '') {
            $period = DateNormalizer::normalizeBillingMonthRange($billingMonth);
            $dateFrom = $dateFrom ?: (string)$period['date_from'];
            $dateTo = $dateTo ?: (string)$period['date_to'];
        }

        return [$dateFrom ?: '1970-01-01', $dateTo ?: '2999-12-31'];
    }

    private function normalizeInputDate(string $date): ?string
    {
        return DateNormalizer::normalizeGregorianOrJalaliDate($date);
    }

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

    public function calculateManagerCommissionKpis(array $filters): array
    {
        unset($filters['status']);
        $row = $this->getCommissionReadModelMode() === 'ledger'
            ? $this->summarizeCommissionRows($this->getLedgerCommissionRows($filters))
            : $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
    {
        if ($this->getCommissionReadModelMode() === 'ledger') {
            return $this->getLedgerCommissionRows($filters);
        }
        return $this->getRulePerformanceRows($filters);
    }

    public function getDealCommissionByDeal(string $dealDidarId): ?array
    {
        $dealDidarId = trim($dealDidarId);
        if ($dealDidarId === '') {
            return null;
        }

        $pdo = $this->ruleRepo->getDb()->getPdo();
        if ($this->tableExists($pdo, 'commission_lines')) {
            $row = $this->getLatestLedgerCommissionForDeal($pdo, $dealDidarId);
            if ($row) {
                return $row;
            }
        }

        if (!$this->tableExists($pdo, 'deal_commissions')) {
            return null;
        }

        $deletedWhere = $this->columnExists($pdo, 'deal_commissions', 'deleted_at') ? ' AND dc.deleted_at IS NULL' : '';
        $nameExpr = $this->columnExists($pdo, 'users', 'display_name')
            ? "COALESCE(NULLIF(su.display_name,''), CONCAT(COALESCE(su.first_name,''), ' ', COALESCE(su.last_name,'')), CONCAT('User #', su.id))"
            : "COALESCE(CONCAT(COALESCE(su.first_name,''), ' ', COALESCE(su.last_name,'')), CONCAT('User #', su.id))";
        $productJoin = $this->tableExists($pdo, 'products') ? 'LEFT JOIN products p ON p.id = dc.product_id' : '';
        $productSelect = $this->tableExists($pdo, 'products') ? 'p.name AS product_name,' : 'NULL AS product_name,';
        $stmt = $pdo->prepare("
            SELECT dc.*,
                   {$nameExpr} AS salesperson_name,
                   {$productSelect}
                   cr.title AS rule_title
            FROM deal_commissions dc
            LEFT JOIN users su ON su.id = dc.salesperson_user_id
            LEFT JOIN commission_rules cr ON cr.id = dc.commission_rule_id
            {$productJoin}
            WHERE dc.deal_didar_id = ?{$deletedWhere}
            ORDER BY COALESCE(dc.deposit_date, DATE(dc.created_at)) DESC, dc.id DESC
            LIMIT 1
        ");
        $stmt->execute([$dealDidarId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (!$row) {
            return null;
        }

        return [
            'id' => (int)($row['id'] ?? 0),
            'row_type' => 'deal_commission',
            'source' => 'deal_commissions',
            'deal_didar_id' => (string)($row['deal_didar_id'] ?? ''),
            'transaction_id' => (int)($row['transaction_id'] ?? 0),
            'deal_payment_id' => (int)($row['deal_payment_id'] ?? 0),
            'rule_id' => (int)($row['commission_rule_id'] ?? 0),
            'rule_title' => (string)($row['rule_title'] ?? ''),
            'salesperson_user_id' => (int)($row['salesperson_user_id'] ?? 0),
            'salesperson_name' => trim((string)($row['salesperson_name'] ?? '')),
            'product_id' => isset($row['product_id']) && $row['product_id'] !== '' ? (int)$row['product_id'] : null,
            'product_name' => (string)($row['product_name'] ?? ''),
            'ncr_amount' => (float)($row['ncr_amount'] ?? 0),
            'payment_total' => (float)($row['ncr_amount'] ?? 0),
            'applied_rate_percent' => (float)($row['applied_rate_percent'] ?? 0),
            'calculated_commission' => (float)($row['calculated_commission'] ?? 0),
            'calculated_amount' => (float)($row['calculated_commission'] ?? 0),
            'status' => (string)($row['status'] ?? ''),
            'deposit_date' => (string)($row['deposit_date'] ?? ''),
            'billing_month' => (string)($row['billing_month'] ?? ''),
            'created_at' => (string)($row['created_at'] ?? ''),
            'updated_at' => (string)($row['updated_at'] ?? ''),
        ];
    }

    public function getPaymentCommissionStatusesForDeal(string $dealDidarId): array
    {
        $dealDidarId = trim($dealDidarId);
        if ($dealDidarId === '') {
            return [];
        }

        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'payment_records')) {
            return [];
        }

        $hasLines = $this->tableExists($pdo, 'commission_lines');
        $lineSelect = $hasLines
            ? "COUNT(cl.id) AS line_count,
               COALESCE(SUM(CASE WHEN COALESCE(cl.is_reversal, 0) = 0 THEN cl.calculated_commission ELSE 0 END), 0) AS calculated_commission,
               MIN(cl.created_at) AS calculated_at,
               GROUP_CONCAT(DISTINCT cr.title ORDER BY cr.title SEPARATOR '، ') AS rule_titles"
            : "0 AS line_count,
               0 AS calculated_commission,
               NULL AS calculated_at,
               NULL AS rule_titles";
        $lineJoin = $hasLines
            ? "LEFT JOIN commission_lines cl ON cl.payment_record_id = pr.id AND COALESCE(cl.status, 'CALCULATED') <> 'VOID'
               LEFT JOIN commission_rules cr ON cr.id = cl.commission_rule_id"
            : '';

        $stmt = $pdo->prepare("
            SELECT pr.id,
                   pr.payment_uuid,
                   pr.payment_sequence,
                   pr.commission_status,
                   pr.effective_date,
                   pr.updated_at,
                   {$lineSelect}
            FROM payment_records pr
            {$lineJoin}
            WHERE pr.deal_didar_id = ?
            GROUP BY pr.id, pr.payment_uuid, pr.payment_sequence, pr.commission_status, pr.effective_date, pr.updated_at
            ORDER BY COALESCE(pr.payment_sequence, 999999), pr.id
        ");
        $stmt->execute([$dealDidarId]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $row) {
            $sequence = (int)($row['payment_sequence'] ?? 0);
            $lineCount = (int)($row['line_count'] ?? 0);
            $status = strtoupper(trim((string)($row['commission_status'] ?? 'PENDING')));
            $calculated = $lineCount > 0 || in_array($status, ['CALCULATED', 'APPROVED', 'READY_FOR_PAYOUT', 'PAID'], true);
            $calculatedAt = trim((string)($row['calculated_at'] ?? ''));
            if ($calculatedAt === '' && $calculated) {
                $calculatedAt = trim((string)($row['updated_at'] ?? ''));
            }
            $payload = [
                'payment_record_id' => (int)($row['id'] ?? 0),
                'payment_uuid' => (string)($row['payment_uuid'] ?? ''),
                'payment_sequence' => $sequence > 0 ? $sequence : null,
                'commission_status' => $status ?: 'PENDING',
                'commission_calculated' => $calculated ? 1 : 0,
                'commission_calculated_at' => $calculatedAt,
                'commission_calculated_at_jalali' => $calculatedAt !== '' ? $this->gregorianToJalaliSafe(substr($calculatedAt, 0, 10)) : '',
                'commission_amount' => round((float)($row['calculated_commission'] ?? 0), 2),
                'commission_rule_titles' => (string)($row['rule_titles'] ?? ''),
            ];
            if ($sequence > 0) {
                $out['seq:' . $sequence] = $payload;
            }
            if ($payload['payment_uuid'] !== '') {
                $out['uuid:' . $payload['payment_uuid']] = $payload;
            }
        }
        return $out;
    }

    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];
        $allowedStatuses = ['PENDING_MANAGER_REVIEW', 'PENDING_FINANCE', 'PENDING_FINANCE_APPROVAL', 'READY_FOR_PAYOUT', 'APPROVED', 'SETTLED', 'REJECTED'];
        if (!in_array($status, $allowedStatuses, true)) {
            return false;
        }
        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' || $status === 'APPROVED') {
            $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);
    }

    private function getLatestLedgerCommissionForDeal(\PDO $pdo, string $dealDidarId): ?array
    {
        $nameExpr = $this->columnExists($pdo, 'users', 'display_name')
            ? "COALESCE(NULLIF(u.display_name,''), CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,'')), CONCAT('User #', u.id))"
            : "COALESCE(CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,'')), CONCAT('User #', u.id))";
        $ownerNameExpr = $this->columnExists($pdo, 'users', 'display_name')
            ? "COALESCE(NULLIF(owner.display_name,''), CONCAT(COALESCE(owner.first_name,''), ' ', COALESCE(owner.last_name,'')), CONCAT('User #', owner.id))"
            : "COALESCE(CONCAT(COALESCE(owner.first_name,''), ' ', COALESCE(owner.last_name,'')), CONCAT('User #', owner.id))";
        $productJoin = $this->tableExists($pdo, 'products') ? 'LEFT JOIN products p ON p.id = cl.product_id' : '';
        $productSelect = $this->tableExists($pdo, 'products') ? 'p.name AS product_name,' : 'NULL AS product_name,';
        $stmt = $pdo->prepare("
            SELECT cl.*,
                   {$nameExpr} AS beneficiary_name,
                   {$ownerNameExpr} AS payment_owner_name,
                   {$productSelect}
                   cr.title AS rule_title,
                   pr.payment_uuid
            FROM commission_lines cl
            LEFT JOIN users u ON u.id = cl.beneficiary_user_id
            LEFT JOIN users owner ON owner.id = cl.payment_owner_user_id
            LEFT JOIN commission_rules cr ON cr.id = cl.commission_rule_id
            LEFT JOIN payment_records pr ON pr.id = cl.payment_record_id
            {$productJoin}
            WHERE cl.deal_didar_id = ?
              AND COALESCE(cl.status, 'CALCULATED') <> 'VOID'
            ORDER BY cl.effective_date DESC, cl.id DESC
            LIMIT 1
        ");
        $stmt->execute([$dealDidarId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (!$row) {
            return null;
        }

        $effectiveDate = (string)($row['effective_date'] ?? '');
        $amount = (float)($row['payment_amount_snapshot'] ?? $row['commission_base_amount'] ?? 0);
        return [
            'id' => (int)($row['id'] ?? 0),
            'row_type' => 'ledger_commission_line',
            'source' => 'commission_lines',
            'commission_line_id' => (int)($row['id'] ?? 0),
            'payment_record_id' => (int)($row['payment_record_id'] ?? 0),
            'payment_uuid' => (string)($row['payment_uuid'] ?? ''),
            'deal_didar_id' => (string)($row['deal_didar_id'] ?? ''),
            'rule_id' => (int)($row['commission_rule_id'] ?? 0),
            'rule_title' => (string)($row['rule_title'] ?? ''),
            'salesperson_user_id' => (int)($row['beneficiary_user_id'] ?? 0),
            'salesperson_name' => trim((string)($row['beneficiary_name'] ?? '')),
            'beneficiary_user_id' => (int)($row['beneficiary_user_id'] ?? 0),
            'beneficiary_name' => trim((string)($row['beneficiary_name'] ?? '')),
            'beneficiary_type' => (string)($row['beneficiary_type'] ?? ''),
            'payment_owner_user_id' => (int)($row['payment_owner_user_id'] ?? 0),
            'payment_owner_name' => trim((string)($row['payment_owner_name'] ?? '')),
            'product_id' => isset($row['product_id']) && $row['product_id'] !== '' ? (int)$row['product_id'] : null,
            'product_name' => (string)($row['product_name'] ?? ''),
            'ncr_amount' => round($amount, 2),
            'payment_total' => round($amount, 2),
            'applied_rate_percent' => (float)($row['applied_rate_percent'] ?? 0),
            'calculated_commission' => (float)($row['calculated_commission'] ?? 0),
            'calculated_amount' => (float)($row['calculated_commission'] ?? 0),
            'status' => (string)($row['status'] ?? 'CALCULATED'),
            'deposit_date' => $effectiveDate,
            'billing_month' => (string)($row['billing_month'] ?? substr($effectiveDate, 0, 7)),
            'is_reversal' => (int)($row['is_reversal'] ?? 0),
            'created_at' => (string)($row['created_at'] ?? ''),
            'updated_at' => (string)($row['updated_at'] ?? ''),
        ];
    }

    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 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;
        }

        $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;
    }

    private function queryPaymentRecordsForLedgerPreview(string $dateFrom, string $dateTo, int $limit): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $stmt = $pdo->prepare("
            SELECT pr.*,
                   COALESCE(NULLIF(u.display_name, ''), CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, '')), pr.payment_owner_name_snapshot, CONCAT('User #', pr.payment_owner_user_id)) AS owner_name,
                   d.person_id AS deal_person_id,
                   d.contact_didar_id AS deal_contact_didar_id,
                   d.title AS deal_title,
                   CONCAT(COALESCE(pe.first_name, ''), ' ', COALESCE(pe.last_name, '')) AS customer_name,
                   pe.id AS person_id,
                   pe.didar_contact_id AS contact_didar_id,
                   pe.mobile_phone AS customer_mobile,
                   COALESCE(
                       NULLIF(lead_owner.display_name, ''),
                       NULLIF(TRIM(CONCAT(COALESCE(lead_owner.first_name, ''), ' ', COALESCE(lead_owner.last_name, ''))), ''),
                       pe.owner_didar_id,
                       ''
                   ) AS current_lead_owner_name,
                   (
                       SELECT GROUP_CONCAT(DISTINCT dp.product_id ORDER BY dp.product_id SEPARATOR ',')
                       FROM deal_products dp
                       WHERE dp.deal_id = pr.deal_id
                   ) AS deal_product_ids,
                   (
                       SELECT GROUP_CONCAT(DISTINCT p.name ORDER BY p.name SEPARATOR '، ')
                       FROM deal_products dp
                       INNER JOIN products p ON p.id = dp.product_id
                       WHERE dp.deal_id = pr.deal_id
                   ) AS product_names,
                   COALESCE(DATE(pr.effective_date), DATE(pr.deposit_date), DATE(pr.payment_date), DATE(pr.cheque_collection_date)) AS preview_date
            FROM payment_records pr
            LEFT JOIN users u ON u.id = pr.payment_owner_user_id
            LEFT JOIN deals d ON d.id = pr.deal_id
            LEFT JOIN persons pe ON pe.id = d.person_id
            LEFT JOIN users lead_owner ON lead_owner.didar_user_id = pe.owner_didar_id
            WHERE COALESCE(DATE(pr.effective_date), DATE(pr.deposit_date), DATE(pr.payment_date), DATE(pr.cheque_collection_date)) BETWEEN ? AND ?
            ORDER BY preview_date ASC, pr.id ASC
            LIMIT " . (int)$limit
        );
        $stmt->execute([$dateFrom, $dateTo]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    private function buildCommissionPreviewPersonSummaries(array $rows): array
    {
        $people = [];
        foreach ($rows as $row) {
            $ownerKey = (string)($row['owner_key'] ?? '');
            if ($ownerKey === '') {
                $ownerKey = 'unknown:' . md5((string)($row['owner_name'] ?? ''));
            }
            if (!isset($people[$ownerKey])) {
                $people[$ownerKey] = [
                    'owner_key' => $ownerKey,
                    'owner_user_id' => (int)($row['owner_user_id'] ?? 0),
                    'owner_name' => trim((string)($row['owner_name'] ?? 'بدون مسئول')),
                    'total_deals' => 0,
                    'sale_deals' => 0,
                    'balance_deals' => 0,
                    'total_payments' => 0,
                    'included_payments' => 0,
                    'excluded_payments' => 0,
                    'sale_payment_count' => 0,
                    'balance_payment_count' => 0,
                    'balance_cheque_count' => 0,
                    'balance_deposit_count' => 0,
                    'estimated_commission' => 0,
                    'top_exclusion_reason' => '',
                    'top_exclusion_reason_count' => 0,
                    'reason_summary' => [],
                    '_deals' => [],
                    '_sale_deals' => [],
                    '_balance_deals' => [],
                    '_reasons' => [],
                ];
            }

            $dealKey = '';
            if ((int)($row['deal_id'] ?? 0) > 0) {
                $dealKey = 'deal:' . (int)$row['deal_id'];
            } elseif (trim((string)($row['deal_didar_id'] ?? '')) !== '') {
                $dealKey = 'didar:' . trim((string)$row['deal_didar_id']);
            } elseif ((int)($row['payment_id'] ?? 0) > 0) {
                $dealKey = 'payment:' . (int)$row['payment_id'];
            }

            $people[$ownerKey]['total_payments']++;
            if ((string)($row['status'] ?? '') === 'included') {
                $people[$ownerKey]['included_payments']++;
                $people[$ownerKey]['estimated_commission'] += (float)($row['estimated_commission'] ?? 0);
            } else {
                $people[$ownerKey]['excluded_payments']++;
                $reason = (string)($row['reason'] ?? 'not_eligible');
                $people[$ownerKey]['_reasons'][$reason] = ($people[$ownerKey]['_reasons'][$reason] ?? 0) + 1;
            }

            $isBalance = (string)($row['payment_kind'] ?? '') === 'balance';
            if ($isBalance) {
                $people[$ownerKey]['balance_payment_count']++;
                if ((string)($row['balance_payment_type'] ?? '') === 'cheque') {
                    $people[$ownerKey]['balance_cheque_count']++;
                } else {
                    $people[$ownerKey]['balance_deposit_count']++;
                }
                if ($dealKey !== '') {
                    $people[$ownerKey]['_balance_deals'][$dealKey] = true;
                }
            } else {
                $people[$ownerKey]['sale_payment_count']++;
                if ($dealKey !== '') {
                    $people[$ownerKey]['_sale_deals'][$dealKey] = true;
                }
            }
            if ($dealKey !== '') {
                $people[$ownerKey]['_deals'][$dealKey] = true;
            }
        }

        $out = [];
        foreach ($people as $person) {
            $person['total_deals'] = count($person['_deals']);
            $person['sale_deals'] = count($person['_sale_deals']);
            $person['balance_deals'] = count($person['_balance_deals']);
            $person['estimated_commission'] = round((float)$person['estimated_commission'], 2);
            arsort($person['_reasons']);
            foreach ($person['_reasons'] as $reason => $count) {
                $item = [
                    'reason' => (string)$reason,
                    'label' => $this->commissionPreviewReasonLabel((string)$reason),
                    'count' => (int)$count,
                ];
                $person['reason_summary'][] = $item;
                if ($person['top_exclusion_reason'] === '') {
                    $person['top_exclusion_reason'] = $item['label'];
                    $person['top_exclusion_reason_count'] = $item['count'];
                }
            }
            unset($person['_deals'], $person['_sale_deals'], $person['_balance_deals'], $person['_reasons']);
            $out[] = $person;
        }
        usort($out, static function ($a, $b) {
            $byPayments = (int)$b['total_payments'] <=> (int)$a['total_payments'];
            if ($byPayments !== 0) {
                return $byPayments;
            }
            return strcmp((string)$a['owner_name'], (string)$b['owner_name']);
        });
        return $out;
    }

    private function resolveRuleAssignmentWindow(array $rule, array $assignment, string $dateFrom, string $dateTo): array
    {
        $validFrom = $this->normalizeDateString($rule['valid_from'] ?? '') ?: '1970-01-01';
        $validTo = $this->normalizeDateString($rule['valid_to'] ?? '') ?: '2999-12-31';
        $assignmentValidFrom = $this->normalizeDateString($assignment['valid_from'] ?? '');
        if ($assignmentValidFrom !== '') {
            $validFrom = max($validFrom, $assignmentValidFrom);
        }
        $assignmentValidTo = $this->normalizeDateString($assignment['valid_to'] ?? '');
        if ($assignmentValidTo !== '') {
            $validTo = min($validTo, $assignmentValidTo);
        }
        $assignedAt = $this->resolveAssignmentEffectiveStart($rule, $assignment);
        if ($assignedAt !== '') {
            $validFrom = max($validFrom, $assignedAt);
        }
        $disabledAt = $this->normalizeDateString($assignment['disabled_at'] ?? '');
        if ($disabledAt !== '') {
            $validTo = min($validTo, $this->previousDate($disabledAt));
        }
        return [max($dateFrom, $validFrom), min($dateTo, $validTo)];
    }

    private function resolveAssignmentEffectiveStart(array $rule, array $assignment): string
    {
        $assignedAt = $this->normalizeDateString($assignment['assigned_at'] ?? '');
        $ruleValidFrom = $this->normalizeDateString($rule['valid_from'] ?? '');
        if ($assignedAt !== '' && $ruleValidFrom !== '' && $assignedAt > $ruleValidFrom) {
            return $ruleValidFrom;
        }
        return $assignedAt;
    }

    private function parseIdCsv(string $value): array
    {
        if (trim($value) === '') {
            return [];
        }
        return array_values(array_unique(array_filter(array_map('intval', explode(',', $value)), static function ($id) {
            return $id > 0;
        })));
    }

    private function commissionPreviewReasonLabel(string $reason): string
    {
        $map = [
            'included' => 'وارد محاسبه می‌شود',
            'amount_not_positive' => 'مبلغ پرداخت مثبت نیست',
            'deleted_placeholder' => 'پرداخت حذف‌شده است',
            'fully_refunded' => 'پرداخت کامل عودت شده',
            'commission_status_paid' => 'پورسانت این پرداخت قبلاً پرداخت شده',
            'commission_status_void' => 'پرداخت برای پورسانت باطل شده',
            'commission_status_clawed_back' => 'پورسانت این پرداخت برگشت خورده',
            'sepidar_required' => 'ثبت سپیدار برای پرداخت لازم است',
            'cheque_not_approved' => 'چک هنوز تأیید نشده',
            'cheque_not_collected' => 'چک هنوز وصول نشده',
            'effective_date_missing' => 'تاریخ ملاک محاسبه پرداخت مشخص نیست',
            'outside_selected_range' => 'خارج از بازه انتخاب‌شده',
            'missing_required_payment_data' => 'اطلاعات ضروری پرداخت ناقص است',
            'balance_without_prior_sale' => 'مانده حساب بدون فروش قبلی',
            'no_assignment_for_payment_owner' => 'برای مسئول پرداخت قانون تخصیص نشده',
            'outside_rule_or_assignment_dates' => 'تاریخ ملاک محاسبه با اعتبار قانون یا تاریخ تخصیص نمی‌خواند',
            'product_scope_mismatch' => 'محصول معامله در دامنه قانون نیست',
            'commission_amount_zero' => 'خروجی قانون پورسانت صفر است',
            'already_claimed_by_earlier_rule' => 'قبلاً با قانون دیگری پوشش داده شده',
            'no_usable_rule' => 'قانون قابل استفاده پیدا نشد',
            'not_eligible' => 'پرداخت واجد شرایط نیست',
        ];
        return $map[$reason] ?? $reason;
    }

    private function gregorianToJalaliSafe(string $date): string
    {
        if (!preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $date, $m)) {
            return $date;
        }
        [$jy, $jm, $jd] = $this->gregorianToJalali((int)$m[1], (int)$m[2], (int)$m[3]);
        return sprintf('%04d/%02d/%02d', $jy, $jm, $jd);
    }

    private function gregorianToJalali(int $gy, int $gm, int $gd): array
    {
        $gDaysInMonth = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
        $jDaysInMonth = [31, 31, 31, 31, 31, 31, 30, 30, 30, 30, 30, 29];
        $gy -= 1600;
        $gm -= 1;
        $gd -= 1;
        $gDayNo = 365 * $gy + (int)(($gy + 3) / 4) - (int)(($gy + 99) / 100) + (int)(($gy + 399) / 400);
        for ($i = 0; $i < $gm; ++$i) {
            $gDayNo += $gDaysInMonth[$i];
        }
        if ($gm > 1 && (($gy % 4 === 0 && $gy % 100 !== 0) || ($gy % 400 === 0))) {
            ++$gDayNo;
        }
        $gDayNo += $gd;
        $jDayNo = $gDayNo - 79;
        $jNp = (int)($jDayNo / 12053);
        $jDayNo %= 12053;
        $jy = 979 + 33 * $jNp + 4 * (int)($jDayNo / 1461);
        $jDayNo %= 1461;
        if ($jDayNo >= 366) {
            $jy += (int)(($jDayNo - 1) / 365);
            $jDayNo = ($jDayNo - 1) % 365;
        }
        for ($i = 0; $i < 11 && $jDayNo >= $jDaysInMonth[$i]; ++$i) {
            $jDayNo -= $jDaysInMonth[$i];
        }
        return [$jy, $i + 1, $jDayNo + 1];
    }

    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
    {
        return;
    }
}
