<?php
namespace App\Services;

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

class CommissionService
{
    private $ruleRepo;
    private $ledgerRepo;

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

    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
    {
        $ruleType = strtoupper(trim((string)($data['rule_type'] ?? 'TIERED_CASHFLOW')));
        $kpiSource = trim((string)($data['kpi_source'] ?? ''));
        if ($kpiSource === '' && $ruleType === 'TIERED_CASHFLOW') {
            // Tiered rules are not KPI-bound, but we keep a stable identifier for schema compatibility.
            $kpiSource = 'tiered_cashflow';
        }
        $targetUsersRaw = $data['target_users'] ?? [];
        $min = (float)($data['kpi_min_threshold'] ?? 0);
        $max = (float)($data['kpi_max_threshold'] ?? 0);
        $baseAmount = (float)($data['base_amount'] ?? 0);
        $rewardPercentage = (float)($data['reward_percentage'] ?? 0);
        $status = trim((string)($data['status'] ?? 'active'));
        $requestedServicesRaw = $data['requested_services'] ?? [];
        $tiersRaw = $data['tiers'] ?? '[]';
        $validFrom = $this->normalizeInputDate((string)($data['valid_from'] ?? ''));
        $validTo = $this->normalizeInputDate((string)($data['valid_to'] ?? ''));

        if ($kpiSource === '') {
            throw new \InvalidArgumentException('منبع KPI الزامی است');
        }
        if ($max <= $min) {
            throw new \InvalidArgumentException('حداکثر آستانه باید از حداقل بزرگ‌تر باشد');
        }
        if ($rewardPercentage < 0 || $rewardPercentage > 100) {
            throw new \InvalidArgumentException('درصد پاداش باید بین 0 تا 100 باشد');
        }

        if (is_string($targetUsersRaw)) {
            $decoded = json_decode($targetUsersRaw, true);
            $targetUsers = is_array($decoded) ? $decoded : [];
        } elseif (is_array($targetUsersRaw)) {
            $targetUsers = $targetUsersRaw;
        } else {
            $targetUsers = [];
        }
        $salespersonUserId = isset($data['salesperson_user_id']) ? (int)$data['salesperson_user_id'] : 0;
        if ($salespersonUserId > 0) {
            $targetUsers = [$salespersonUserId];
        }
        if (empty($targetUsers)) {
            $targetUsers = ['ALL'];
        }
        if (is_string($requestedServicesRaw)) {
            $decodedServices = json_decode($requestedServicesRaw, true);
            $requestedServices = is_array($decodedServices) ? $decodedServices : [];
        } else {
            $requestedServices = is_array($requestedServicesRaw) ? $requestedServicesRaw : [];
        }
        if (empty($requestedServices)) {
            $requestedServices = ['ALL'];
        }

        if (is_string($tiersRaw)) {
            $decodedTiers = json_decode($tiersRaw, true);
            $tiers = is_array($decodedTiers) ? $decodedTiers : [];
        } else {
            $tiers = is_array($tiersRaw) ? $tiersRaw : [];
        }

        $rule = new CommissionRule([
            'id' => isset($data['id']) ? (int)$data['id'] : null,
            'rule_type' => $ruleType,
            'kpi_source' => $kpiSource,
            'kpi_key' => $kpiSource,
            'salesperson_user_id' => $salespersonUserId > 0 ? $salespersonUserId : null,
            'valid_from' => $validFrom ?: date('Y-m-d'),
            'valid_to' => $validTo,
            'target_users' => $targetUsers,
            'requested_services' => $requestedServices,
            'kpi_min_threshold' => $min,
            'kpi_max_threshold' => $max,
            'base_amount' => $baseAmount,
            'reward_percentage' => $rewardPercentage,
            'status' => in_array($status, ['active', 'inactive'], true) ? $status : 'active',
        ]);
        $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 evaluateUserCommissions(int $userId, float $kpiScore, string $kpiSource): array
    {
        if ($userId <= 0) {
            throw new \InvalidArgumentException('شناسه کاربر نامعتبر است');
        }
        $kpiSource = trim($kpiSource);
        if ($kpiSource === '') {
            throw new \InvalidArgumentException('منبع KPI الزامی است');
        }

        $pdo = $this->ledgerRepo->getDb()->getPdo();
        $rules = $this->ruleRepo->findActiveByKpiSource($kpiSource);
        $results = [];

        try {
            $pdo->beginTransaction();
            foreach ($rules as $rule) {
                $targets = $rule->getTargetUsersArray();
                $appliesToAll = in_array('ALL', $targets, true);
                $appliesToUser = in_array($userId, array_map('intval', $targets), true);
                if (!$appliesToAll && !$appliesToUser) {
                    continue;
                }

                $min = (float)$rule->kpi_min_threshold;
                $max = (float)$rule->kpi_max_threshold;
                if ($kpiScore < $min || $kpiScore > $max) {
                    continue;
                }

                $baseAmount = (float)$rule->base_amount;
                $rewardPercentage = (float)$rule->reward_percentage;
                $finalAmount = round($baseAmount * ($rewardPercentage / 100), 2);

                $ledgerId = $this->ledgerRepo->store([
                    'user_id' => $userId,
                    'rule_id' => (int)$rule->id,
                    'kpi_score_recorded' => $kpiScore,
                    'calculated_amount' => $finalAmount,
                    'calculation_date' => date('Y-m-d H:i:s'),
                ]);

                $results[] = [
                    'ledger_id' => $ledgerId,
                    'rule_id' => (int)$rule->id,
                    'kpi_score_recorded' => $kpiScore,
                    'calculated_amount' => $finalAmount,
                ];
            }
            $pdo->commit();
            return $results;
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            Logger::logError('COMMISSION_EVALUATION_FAILED', $e, [
                'user_id' => $userId,
                'kpi_source' => $kpiSource,
                'kpi_score' => $kpiScore,
            ]);
            throw $e;
        }
    }

    public function getUserLedger(int $userId): array
    {
        return $this->ledgerRepo->getByUser($userId);
    }

    public function calculateCommissionTotal(array $rows, float $baseRatePercent, ?float $kpiMultiplier = null, float $deductionQc = 0.0): float
    {
        $rate = max(0.0, $baseRatePercent) / 100;
        $multiplier = $kpiMultiplier === null || $kpiMultiplier <= 0 ? 1.0 : $kpiMultiplier;
        $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 * $multiplier) - max(0.0, $deductionQc);
        return round($total, 2);
    }

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

                if ($ruleType === 'TIERED_CASHFLOW') {
                    $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;
                    } elseif ($this->columnExists($pdo, 'commission_rule_tiers', 'min_realization_percent')) {
                        $columns[] = 'min_realization_percent';
                        $values[] = $minAmount;
                    }

                    if ($maxAmount !== null) {
                        if ($this->columnExists($pdo, 'commission_rule_tiers', 'max_amount')) {
                            $columns[] = 'max_amount';
                            $values[] = $maxAmount;
                        } elseif ($this->columnExists($pdo, 'commission_rule_tiers', 'max_realization_percent')) {
                            $columns[] = 'max_realization_percent';
                            $values[] = $maxAmount;
                        }
                    }
                } else {
                    $min = isset($tier['min_realization']) && $tier['min_realization'] !== '' ? (float)$tier['min_realization'] : null;
                    $max = isset($tier['max_realization']) && $tier['max_realization'] !== '' ? (float)$tier['max_realization'] : null;
                    if ($min === null || $max === null) {
                        continue;
                    }
                    if ($this->columnExists($pdo, 'commission_rule_tiers', 'min_realization_percent')) {
                        $columns[] = 'min_realization_percent';
                        $values[] = $min;
                    }
                    if ($this->columnExists($pdo, 'commission_rule_tiers', 'max_realization_percent')) {
                        $columns[] = 'max_realization_percent';
                        $values[] = $max;
                    }
                }

                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, register_time, COALESCE(won_time, updated_at, created_at) AS won_at 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);
        $ncrStmt = $pdo->prepare("
            SELECT COALESCE(SUM(amount),0) AS ncr_amount, MAX(COALESCE(payment_date, DATE(payment_time))) AS last_payment_date
            FROM transactions
            WHERE deal_id = ?
        ");
        $ncrStmt->execute([$dealInternalId]);
        $ncrRow = $ncrStmt->fetch(\PDO::FETCH_ASSOC) ?: [];
        $ncrAmount = (float)($ncrRow['ncr_amount'] ?? 0);
        if ($ncrAmount <= 0) {
            return null;
        }

        $triggerDate = $this->normalizeInputDate((string)($ncrRow['last_payment_date'] ?? ''));
        if ($triggerDate === null) {
            $triggerDate = $this->normalizeInputDate((string)($deal['won_at'] ?? '')) ?: date('Y-m-d');
        }

        $rule = $this->findBestRuleForDeal($salespersonUserId, $triggerDate);
        if (!$rule) {
            Logger::logWarning('COMMISSION_RULE_NOT_MATCHED', [
                'deal_didar_id' => $dealDidarId,
                'salesperson_user_id' => $salespersonUserId,
                'trigger_date' => $triggerDate,
            ]);
            return null;
        }

        $ruleId = (int)($rule['id'] ?? 0);
        $ruleType = strtoupper(trim((string)($rule['rule_type'] ?? 'TIERED_CASHFLOW')));
        $appliedRate = (float)($rule['base_rate_percent'] ?? $rule['reward_percentage'] ?? 0);
        if ($ruleType === 'TIERED_CASHFLOW') {
            $tierRate = $this->resolveTierRatePercent($ruleId, $ncrAmount);
            if ($tierRate !== null) {
                $appliedRate = $tierRate;
            }
        }
        $kpiMultiplier = 1.0;
        $commissionAmount = round(($ncrAmount * ($appliedRate / 100.0)) * $kpiMultiplier, 2);
        $productId = isset($rule['product_id']) && (int)$rule['product_id'] > 0 ? (int)$rule['product_id'] : null;

        $upsert = $pdo->prepare("
            INSERT INTO deal_commissions
                (deal_didar_id, salesperson_user_id, product_id, commission_rule_id, ncr_amount, applied_rate_percent, kpi_multiplier, calculated_commission, status, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'PENDING_MANAGER_REVIEW', ?)
            ON DUPLICATE KEY UPDATE
                salesperson_user_id = VALUES(salesperson_user_id),
                product_id = VALUES(product_id),
                commission_rule_id = VALUES(commission_rule_id),
                ncr_amount = VALUES(ncr_amount),
                applied_rate_percent = VALUES(applied_rate_percent),
                kpi_multiplier = VALUES(kpi_multiplier),
                calculated_commission = VALUES(calculated_commission),
                status = 'PENDING_MANAGER_REVIEW',
                notes = VALUES(notes)
        ");
        $upsert->execute([
            $dealDidarId,
            $salespersonUserId,
            $productId,
            $ruleId > 0 ? $ruleId : null,
            $ncrAmount,
            $appliedRate,
            $kpiMultiplier,
            $commissionAmount,
            'auto-calculated by CommissionService on won/settled trigger',
        ]);

        return [
            'deal_didar_id' => $dealDidarId,
            'commission_rule_id' => $ruleId,
            'ncr_amount' => $ncrAmount,
            'applied_rate_percent' => $appliedRate,
            'calculated_commission' => $commissionAmount,
            'trigger_date' => $triggerDate,
        ];
    }

    private function findBestRuleForDeal(int $salespersonUserId, string $triggerDate): ?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[] = $triggerDate;
        }
        if ($hasValidTo) {
            $where[] = "(valid_to IS NULL OR DATE(valid_to) >= ?)";
            $params[] = $triggerDate;
        }

        $sql = "SELECT * FROM commission_rules WHERE " . implode(' AND ', $where) . " ORDER BY salesperson_user_id DESC, id DESC LIMIT 1";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function resolveTierRatePercent(int $ruleId, float $ncrAmount): ?float
    {
        if ($ruleId <= 0) {
            return null;
        }
        $pdo = $this->ruleRepo->getDb()->getPdo();
        if (!$this->tableExists($pdo, 'commission_rule_tiers')) {
            return null;
        }
        $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 null;
        }
        $maxExpr = $hasMaxAmount ? 'max_amount' : 'NULL';
        $stmt = $pdo->prepare("
            SELECT {$rateColumn} AS rate
            FROM commission_rule_tiers
            WHERE rule_id = ?
              AND min_amount <= ?
              AND ({$maxExpr} IS NULL OR {$maxExpr} = 0 OR {$maxExpr} >= ?)
            ORDER BY min_amount DESC
            LIMIT 1
        ");
        $stmt->execute([$ruleId, $ncrAmount, $ncrAmount]);
        $rate = $stmt->fetchColumn();
        return $rate === false ? null : (float)$rate;
    }

    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,
                COALESCE(AVG(NULLIF(dc.kpi_multiplier,0)),1) AS kma
            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),
            'kma' => (float)($row['kma'] ?? 1),
        ];
    }

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

        $salespersonId = (int)($rule['salesperson_user_id'] ?? 0);
        $validFrom = $this->normalizeInputDate((string)($rule['valid_from'] ?? '')) ?: '1970-01-01';
        $validTo = $this->normalizeInputDate((string)($rule['valid_to'] ?? '')) ?: '2999-12-31';
        $ruleType = strtoupper((string)($rule['rule_type'] ?? 'TIERED_CASHFLOW'));
        $baseRate = (float)($rule['base_rate_percent'] ?? $rule['reward_percentage'] ?? 0);

        $sql = "
            SELECT d.didar_deal_id, u.id AS user_id, pd.total_amount
            FROM deals d
            INNER JOIN users u ON u.didar_user_id = d.owner_didar_id
            INNER JOIN (
                SELECT
                    t.deal_id,
                    MAX(DATE(COALESCE(t.payment_date, DATE(t.payment_time)))) AS last_payment_date,
                    COALESCE(SUM(t.amount),0) AS total_amount
                FROM transactions t
                GROUP BY t.deal_id
            ) pd ON pd.deal_id = d.id
            WHERE LOWER(COALESCE(d.status,'')) = 'won'
              AND pd.total_amount > 0
              AND pd.last_payment_date >= ?
              AND pd.last_payment_date <= ?
        ";
        $params = [$validFrom, $validTo];
        if ($salespersonId > 0) {
            $sql .= " AND u.id = ?";
            $params[] = $salespersonId;
        }
        $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;
            }
            $ncr = (float)($row['total_amount'] ?? 0);
            $rate = $baseRate;
            if ($ruleType === 'TIERED_CASHFLOW') {
                $tierRate = $this->resolveTierRatePercent($ruleId, $ncr);
                if ($tierRate !== null) {
                    $rate = $tierRate;
                }
            }
            $commission = round(($ncr * $rate) / 100, 2);
            $upsert = $pdo->prepare("
                INSERT INTO deal_commissions
                    (deal_didar_id, salesperson_user_id, product_id, commission_rule_id, ncr_amount, applied_rate_percent, kpi_multiplier, calculated_commission, status, notes)
                VALUES (?, ?, ?, ?, ?, ?, 1, ?, 'PENDING_MANAGER_REVIEW', ?)
                ON DUPLICATE KEY UPDATE
                    salesperson_user_id = VALUES(salesperson_user_id),
                    commission_rule_id = VALUES(commission_rule_id),
                    ncr_amount = VALUES(ncr_amount),
                    applied_rate_percent = VALUES(applied_rate_percent),
                    kpi_multiplier = 1,
                    calculated_commission = VALUES(calculated_commission),
                    status = VALUES(status),
                    notes = VALUES(notes)
            ");
            $upsert->execute([
                $dealDidarId,
                (int)($row['user_id'] ?? 0),
                isset($rule['product_id']) && (int)$rule['product_id'] > 0 ? (int)$rule['product_id'] : null,
                $ruleId,
                $ncr,
                $rate,
                $commission,
                'rebuild by rule save',
            ]);
        }
    }
}
