<?php
namespace App\Services;

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

class CommissionService
{
    private $ruleRepo;
    private $paymentEligibility;
    private $commissionLineRepo;

    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']);
            }
            $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): void
    {
        if ($ruleId <= 0) {
            return;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_assignments')) {
            return;
        }

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

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

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

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

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

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

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

    public function 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, cr.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;
            $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 {
            $this->assertNoActiveAssignmentOverlap($ruleIds, $agentIds, $productIds);
            foreach ($ruleIds as $ruleId) {
                if (!$this->ruleRepo->findById($ruleId)) {
                    continue;
                }
                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 = NOW()';
                        }
                        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', date('Y-m-d H:i:s')];
                        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
                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)], []);
                $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): 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);

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

        $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 = ?', 'assigned_at = NOW()', 'disabled_at = NULL'];
        $params = [$ruleId, 'USER'];
        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 ($rules as $rule) {
            $rule['_performance_date_from'] = $dateFrom;
            $rule['_performance_date_to'] = $dateTo;
            $assignments = $this->getRuleAssignmentsForPerformance($rule);
            foreach ($assignments as $assignment) {
                $salespersonIds = $this->resolveAssignmentSalespersonIds($assignment, $filterUserId);
                if (empty($salespersonIds)) {
                    continue;
                }
                $productIds = $this->resolveAssignmentProductIds($assignment, $rule);
                $payments = $this->getEligiblePaymentsForScope($salespersonIds, $productIds, $dateFrom, $dateTo, $rule, $assignment);
                $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, '', null, $dateFrom, $dateTo);
        $stmt = $pdo->prepare("SELECT * FROM commission_rules WHERE " . implode(' AND ', $where) . " ORDER BY id ASC");
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    private function getRuleAssignmentsForPerformance(array $rule): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $ruleId = (int)($rule['id'] ?? 0);
        if ($ruleId > 0 && $this->tableExists($pdo, 'commission_rule_assignments')) {
            $where = ["rule_id = ?"];
            $params = [$ruleId];
            $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 resolveAssignmentSalespersonIds(array $assignment, int $filterUserId): array
    {
        $scopeType = strtoupper(trim((string)($assignment['scope_type'] ?? 'USER'))) ?: 'USER';
        $salespersonId = (int)($assignment['salesperson_user_id'] ?? 0);
        if ($filterUserId > 0) {
            if ($scopeType === 'ALL') {
                return [$filterUserId];
            }
            return $salespersonId === $filterUserId ? [$filterUserId] : [];
        }
        if ($scopeType === 'ALL') {
            return $this->getActiveAgentIds();
        }
        return $salespersonId > 0 ? [$salespersonId] : [];
    }

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

    private function getActiveAgentIds(): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $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';
        $assignedAt = $this->normalizeDateString($assignment['assigned_at'] ?? '');
        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)) {
            return $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
    {
        $date = trim((string)$value);
        if ($date === '' || $date === '0000-00-00' || $date === '0000-00-00 00:00:00') {
            return '';
        }
        return substr($date, 0, 10);
    }

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

    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
    {
        $billingMonth = trim($billingMonth);
        if (!preg_match('/^\d{4}-\d{2}$/', $billingMonth)) {
            throw new \InvalidArgumentException('ماه محاسبه نامعتبر است');
        }

        $dateFrom = $billingMonth . '-01';
        $dateTo = date('Y-m-t', strtotime($dateFrom));
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'payment_records') || !$this->tableExists($pdo, 'commission_lines')) {
            return [
                'billing_month' => $billingMonth,
                '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 ($rules as $rule) {
            $rule['_performance_date_from'] = $dateFrom;
            $rule['_performance_date_to'] = $dateTo;
            $assignments = $this->getRuleAssignmentsForPerformance($rule);
            foreach ($assignments as $assignment) {
                $salespersonIds = $this->resolveAssignmentSalespersonIds($assignment, 0);
                if (empty($salespersonIds)) {
                    continue;
                }

                $productIds = $this->resolveAssignmentProductIds($assignment, $rule);
                $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;
                    }

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

        return [
            'billing_month' => $billingMonth,
            'payments_scanned' => $paymentsScanned,
            'payments_with_rule' => $paymentsWithRule,
            'commission_lines_count' => count($lineIds),
            'direct_lines_count' => count($directLineIds),
        ];
    }

    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', 'assigned_at')) {
                $where[] = "(DATE(" . $columnPrefix . "assigned_at) <= ?)";
                $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', 'assigned_at')) {
                $where[] = "(DATE(" . $columnPrefix . "assigned_at) <= ?)";
                $params[] = $dateTo;
            }
            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->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->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($supervisorAmount * ($managerRate / 100), 2);
            $lineId = $this->createHierarchyCommissionLine($dealDidarId, $managerUserId, 'MANAGER_OF_SUPERVISOR_COMMISSION', $payment, $rule, $productId, $managerRate, $managerAmount, $supervisorAmount);
            if ($lineId > 0) {
                $lineIds['MANAGER_OF_SUPERVISOR_COMMISSION'] = $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
    ): 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));
        $envName = $beneficiaryType === 'MANAGER_OF_PAYMENT_OWNER'
            ? '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 $beneficiaryType === 'MANAGER_OF_PAYMENT_OWNER' ? 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
    {
        $rawFallback = getenv('COMMISSION_MANAGER_RATE_PERCENT');
        $fallback = ($rawFallback !== false && trim((string)$rawFallback) !== '')
            ? max(0.0, min(100.0, (float)$rawFallback))
            : 0.0;
        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 = $this->normalizeInputDate((string)($filters['date_from'] ?? ''));
        $dateTo = $this->normalizeInputDate((string)($filters['date_to'] ?? ''));
        $billingMonth = trim((string)($filters['billing_month'] ?? ''));

        if (($dateFrom === null || $dateTo === null) && preg_match('/^(\d{4})[-\/](\d{1,2})$/', $billingMonth, $m)) {
            $year = (int)$m[1];
            $month = (int)$m[2];
            if ($year >= 1300 && $year <= 1600) {
                $dateFrom = $dateFrom ?: $this->normalizeInputDate(sprintf('%04d/%02d/01', $year, $month));
                $nextYear = $month === 12 ? $year + 1 : $year;
                $nextMonth = $month === 12 ? 1 : $month + 1;
                $nextStart = $this->normalizeInputDate(sprintf('%04d/%02d/01', $nextYear, $nextMonth));
                $dateTo = $dateTo ?: ($nextStart ? date('Y-m-d', strtotime($nextStart . ' -1 day')) : null);
            } elseif ($year > 1600 && $month >= 1 && $month <= 12) {
                $dateFrom = $dateFrom ?: sprintf('%04d-%02d-01', $year, $month);
                $dateTo = $dateTo ?: date('Y-m-t', strtotime($dateFrom));
            }
        }

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

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

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

    public function calculateRepCommissionKpis(array $filters): array
    {
        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 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;
    }

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