<?php
namespace App\Services;

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

class CommissionService
{
    private $ruleRepo;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    private function getEligibleDepositRows(int $dealInternalId, string $dealDidarId): array
    {
        $rows = [];
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if ($dealInternalId > 0 && $this->tableExists($pdo, 'transactions')) {
            $depositExpr = $this->buildCoalesceDateExpression('transactions', ['deposit_date', 'payment_date']);
            if ($depositExpr === null) {
                Logger::logWarning('COMMISSION_TRANSACTION_DEPOSIT_DATE_MISSING', [
                    'deal_id' => $dealInternalId,
                    'deal_didar_id' => $dealDidarId,
                ]);
            } else {
                $financeWhere = $this->columnExists($pdo, 'transactions', 'is_accepted_by_finance')
                    ? 'AND is_accepted_by_finance = 1'
                    : '';
                $statusWhere = $this->columnExists($pdo, 'transactions', 'status')
                    ? "AND LOWER(COALESCE(status, '')) IN ('confirmed','settled','cleared')"
                    : '';
                $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}
                      AND {$depositExpr} IS NOT NULL
                    ORDER BY DATE({$depositExpr}) ASC, id ASC
                ");
                $stmt->execute([$dealInternalId]);
                $rows = array_merge($rows, $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: []);
            }
        }

        if ($this->tableExists($pdo, 'deal_payments')) {
            $depositExpr = $this->buildCoalesceDateExpression('deal_payments', ['deposit_date', 'cheque_cashed_date', 'receipt_date']);
            if ($depositExpr === null) {
                Logger::logWarning('COMMISSION_DEAL_PAYMENT_DEPOSIT_DATE_MISSING', [
                    'deal_didar_id' => $dealDidarId,
                ]);
            } else {
                $financeWhere = $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)
                      )"
                    : '';
                $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
                      {$financeWhere}
                      {$chequeWhere}
                      AND {$depositExpr} IS NOT NULL
                    ORDER BY DATE({$depositExpr}) ASC, id ASC
                ");
                $stmt->execute([$dealDidarId]);
                $rows = array_merge($rows, $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: []);
            }
        }

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

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

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

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

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

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

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

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

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

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

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

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

    public function calculateRepCommissionKpis(array $filters): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $dateFrom = $this->normalizeInputDate((string)($filters['date_from'] ?? '')) ?: date('Y-m-01');
        $dateTo = $this->normalizeInputDate((string)($filters['date_to'] ?? '')) ?: date('Y-m-t');
        $ownerDidarId = trim((string)($filters['owner_didar_id'] ?? ''));

        $userId = 0;
        if ($ownerDidarId !== '') {
            $u = $pdo->prepare("SELECT id FROM users WHERE didar_user_id = ? LIMIT 1");
            $u->execute([$ownerDidarId]);
            $userId = (int)($u->fetchColumn() ?: 0);
        }

        $where = ["pd.last_payment_date >= ?", "pd.last_payment_date <= ?"];
        $params = [$dateFrom, $dateTo];
        if ($userId > 0) {
            $where[] = "dc.salesperson_user_id = ?";
            $params[] = $userId;
        }

        $sql = "
            SELECT
                COALESCE(SUM(CASE WHEN dc.status IN ('APPROVED','READY_FOR_PAYOUT','SETTLED') THEN dc.calculated_commission ELSE 0 END),0) AS tce,
                COALESCE(SUM(CASE WHEN dc.status IN ('PENDING_MANAGER_REVIEW','PENDING_FINANCE','PENDING_FINANCE_APPROVAL') THEN dc.calculated_commission ELSE 0 END),0) AS pcp
            FROM deal_commissions dc
            INNER JOIN deals d ON d.didar_deal_id = dc.deal_didar_id
            INNER JOIN (
                SELECT deal_id, MAX(DATE(COALESCE(payment_date, DATE(payment_time)))) AS last_payment_date
                FROM transactions
                GROUP BY deal_id
            ) pd ON pd.deal_id = d.id
            WHERE " . implode(' AND ', $where);
        $st = $pdo->prepare($sql);
        $st->execute($params);
        $row = $st->fetch(\PDO::FETCH_ASSOC) ?: [];
        return [
            'tce' => (float)($row['tce'] ?? 0),
            'pcp' => (float)($row['pcp'] ?? 0),
        ];
    }

    public function calculateManagerCommissionKpis(array $filters): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $dateFrom = $this->normalizeInputDate((string)($filters['date_from'] ?? '')) ?: date('Y-m-01');
        $dateTo = $this->normalizeInputDate((string)($filters['date_to'] ?? '')) ?: date('Y-m-t');

        $sql = "
            SELECT
                COALESCE(SUM(CASE WHEN dc.status IN ('APPROVED','READY_FOR_PAYOUT','SETTLED') THEN dc.calculated_commission ELSE 0 END),0) AS tcp,
                COALESCE(SUM(CASE WHEN dc.status IN ('PENDING_MANAGER_REVIEW','PENDING_FINANCE','PENDING_FINANCE_APPROVAL') THEN dc.calculated_commission ELSE 0 END),0) AS cpar,
                COALESCE(SUM(pd.total_amount),0) AS total_ncr
            FROM deal_commissions dc
            INNER JOIN deals d ON d.didar_deal_id = dc.deal_didar_id
            INNER JOIN (
                SELECT
                    deal_id,
                    MAX(DATE(COALESCE(payment_date, DATE(payment_time)))) AS last_payment_date,
                    COALESCE(SUM(amount),0) AS total_amount
                FROM transactions
                GROUP BY deal_id
            ) pd ON pd.deal_id = d.id
            WHERE pd.last_payment_date >= ?
              AND pd.last_payment_date <= ?
        ";
        $st = $pdo->prepare($sql);
        $st->execute([$dateFrom, $dateTo]);
        $row = $st->fetch(\PDO::FETCH_ASSOC) ?: [];
        $tcp = (float)($row['tcp'] ?? 0);
        $totalNcr = (float)($row['total_ncr'] ?? 0);
        return [
            'tcp' => $tcp,
            'roci' => $tcp > 0 ? round($totalNcr / $tcp, 4) : 0.0,
            'ecr' => $totalNcr > 0 ? round(($tcp / $totalNcr) * 100, 4) : 0.0,
            'cpar' => (float)($row['cpar'] ?? 0),
        ];
    }

    public function getDealCommissions(array $filters = []): array
    {
        $pdo = $this->ruleRepo->getDb()->getPdo();
        $sql = "
            SELECT dc.*,
                   COALESCE(su.display_name, CONCAT(COALESCE(su.first_name,''), ' ', COALESCE(su.last_name,''))) AS salesperson_name
            FROM deal_commissions dc
            LEFT JOIN users su ON su.id = dc.salesperson_user_id
            WHERE 1=1
        ";
        $params = [];
        if (!empty($filters['status'])) {
            $sql .= " AND dc.status = ?";
            $params[] = trim((string)$filters['status']);
        }
        if (!empty($filters['salesperson_user_id']) && (int)$filters['salesperson_user_id'] > 0) {
            $sql .= " AND dc.salesperson_user_id = ?";
            $params[] = (int)$filters['salesperson_user_id'];
        }
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(COALESCE(dc.settled_at, dc.updated_at, dc.created_at)) >= ?";
            $params[] = $this->normalizeInputDate((string)$filters['date_from']) ?: (string)$filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(COALESCE(dc.settled_at, dc.updated_at, dc.created_at)) <= ?";
            $params[] = $this->normalizeInputDate((string)$filters['date_to']) ?: (string)$filters['date_to'];
        }
        if (isset($filters['amount_min']) && $filters['amount_min'] !== '') {
            $sql .= " AND dc.calculated_commission >= ?";
            $params[] = (float)$filters['amount_min'];
        }
        if (isset($filters['amount_max']) && $filters['amount_max'] !== '') {
            $sql .= " AND dc.calculated_commission <= ?";
            $params[] = (float)$filters['amount_max'];
        }
        $sql .= " ORDER BY dc.created_at DESC, dc.id DESC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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