<?php
namespace App\Services;

use App\Models\CommissionPayoutCycle;
use App\Repositories\CommissionPayoutCycleRepository;
use App\Repositories\PaymentEventRepository;
use App\Repositories\UserRepository;
use App\Utils\DateNormalizer;

class PayrollIntegrationService
{
    private $cycleRepo;
    private $userRepo;
    private $paymentEventRepo;

    public function __construct(
        CommissionPayoutCycleRepository $cycleRepo,
        UserRepository $userRepo,
        ?PaymentEventRepository $paymentEventRepo = null
    ) {
        $this->cycleRepo = $cycleRepo;
        $this->userRepo = $userRepo;
        $this->paymentEventRepo = $paymentEventRepo;
    }

    public function aggregateMonthlySettledCommissions(string $billingMonth, int $managerId = 0, int $payrollDay = 30): array
    {
        $billingMonth = trim($billingMonth);
        $period = DateNormalizer::normalizeBillingMonthRange($billingMonth);
        $cycleMonth = (string)$period['input_month'];

        $rows = ((string)($period['calendar'] ?? '') === 'jalali')
            ? $this->cycleRepo->aggregateSettledByEffectiveDateRange((string)$period['date_from'], (string)$period['date_to'])
            : $this->cycleRepo->aggregateSettledByMonth($cycleMonth);
        $results = [];
        foreach ($rows as $row) {
            $userId = (int)($row['user_id'] ?? 0);
            if ($userId <= 0) {
                continue;
            }
            $rowCycleMonth = trim((string)($row['billing_month'] ?? ''));
            if (preg_match('/^(1[34]\d{2})[-\/](0?[1-9]|1[0-2])$/', $rowCycleMonth, $m)) {
                $cycleMonth = sprintf('%04d-%02d', (int)$m[1], (int)$m[2]);
            } else {
                $cycleMonth = (string)$period['input_month'];
            }
            $calculated = round((float)($row['calculated_amount'] ?? 0), 2);
            $lineIds = $this->parseLineIds((string)($row['commission_line_ids'] ?? ''));
            if (empty($lineIds)) {
                continue;
            }
            $approvalDeadline = $this->buildApprovalDeadlineForPeriod($period, $payrollDay);
            $cycle = $this->cycleRepo->findByUserAndMonth($userId, $cycleMonth);
            $existingStatus = strtoupper(trim((string)($cycle->status ?? '')));

            // Already-decided/finalized cycles stay untouched. A REJECTED cycle, however,
            // re-opens when a new settled payment arrives for the same month so the whole
            // month (new + previously-rejected payments) can be re-reviewed.
            if ($cycle && in_array($existingStatus, ['APPROVED', 'OVERRIDDEN', 'FINANCE_APPROVED', 'READY_FOR_PAYOUT', 'PAID', 'SETTLED', 'TRANSFERRED_TO_PAYROLL'], true)) {
                $results[] = $cycle->toArray();
                continue;
            }
            $reopenRejected = ($cycle && $existingStatus === 'REJECTED');

            $entity = new CommissionPayoutCycle([
                'id' => $cycle ? (int)$cycle->id : null,
                'user_id' => $userId,
                'billing_month' => $cycleMonth,
                'calculated_amount' => $calculated,
                'override_amount' => $reopenRejected ? null : ($cycle ? $cycle->override_amount : null),
                'final_payable_amount' => $cycle && !$reopenRejected ? (float)$cycle->final_payable_amount : $calculated,
                'manager_id' => $managerId > 0 ? $managerId : ($cycle ? (int)$cycle->manager_id : null),
                'status' => 'PENDING_APPROVAL',
                'override_reason' => $reopenRejected ? null : ($cycle ? $cycle->override_reason : null),
                'approval_deadline' => $approvalDeadline,
                'escalate_to_finance' => 0,
            ]);
            $id = $this->cycleRepo->save($entity);
            $this->cycleRepo->attachLinesToCycle($id, $lineIds);
            if ($reopenRejected) {
                $this->reopenRejectedCycleLines($id);
            }
            $saved = $this->cycleRepo->findById($id);
            if ($saved) {
                $results[] = $saved->toArray();
            }
        }
        return $results;
    }

    /**
     * Re-open a previously rejected cycle for review: bring its rejected (voided) lines
     * back to CALCULATED so they re-enter the queue alongside the new payments, then
     * resync the cycle's stored amount to the full active line set.
     */
    private function reopenRejectedCycleLines(int $cycleId): void
    {
        if ($cycleId <= 0) {
            return;
        }
        $pdo = $this->cycleRepo->getDb()->getPdo();
        $pdo->prepare("
            UPDATE commission_lines cl
            INNER JOIN commission_payout_cycle_lines cpcl ON cpcl.commission_line_id = cl.id
            SET cl.status = 'CALCULATED', cl.updated_at = NOW()
            WHERE cpcl.payout_cycle_id = ?
              AND cl.status = 'VOID'
              AND COALESCE(cl.is_reversal, 0) = 0
        ")->execute([$cycleId]);

        $sumStmt = $pdo->prepare("
            SELECT ROUND(COALESCE(SUM(cl.calculated_commission), 0), 2)
            FROM commission_payout_cycle_lines cpcl
            INNER JOIN commission_lines cl ON cl.id = cpcl.commission_line_id
            WHERE cpcl.payout_cycle_id = ?
              AND COALESCE(cl.status, 'CALCULATED') <> 'VOID'
        ");
        $sumStmt->execute([$cycleId]);
        $total = round((float)$sumStmt->fetchColumn(), 2);
        $pdo->prepare("
            UPDATE commission_payout_cycles
            SET calculated_amount = ?, final_payable_amount = ?
            WHERE id = ?
        ")->execute([$total, $total, $cycleId]);
    }

    public function processDeadlineSla(string $billingMonth = ''): array
    {
        $queue = $this->cycleRepo->getManagerQueue($billingMonth, 'PENDING_APPROVAL');
        $now = date('Y-m-d H:i:s');
        $updated = [];
        foreach ($queue as $cycle) {
            $deadline = trim((string)($cycle->approval_deadline ?? ''));
            if ($deadline === '' || $deadline >= $now) {
                continue;
            }
            $cycle->status = 'TRANSFERRED_TO_PAYROLL';
            $cycle->final_payable_amount = (float)$cycle->calculated_amount;
            $cycle->override_amount = null;
            $cycle->override_reason = 'SLA deadline missed -> escalated to finance';
            $cycle->escalate_to_finance = 1;
            $this->cycleRepo->save($cycle);
            $this->cycleRepo->updateCycleLinesStatus((int)$cycle->id, 'READY_FOR_PAYOUT', ['CALCULATED']);
            $this->recordCycleLineEvents((int)$cycle->id, 'COMMISSION_PAYOUT_SLA_TRANSFERRED', 0, [
                'cycle_status' => $cycle->status,
                'line_status' => 'READY_FOR_PAYOUT',
                'reason' => $cycle->override_reason,
            ]);
            $updated[] = $cycle->toArray();
        }
        return $updated;
    }

    public function getManagerQueue(string $billingMonth = '', bool $applySla = true): array
    {
        if ($applySla) {
            $this->processDeadlineSla($billingMonth);
        }
        return $this->normalizeQueueCycles($this->cycleRepo->getManagerQueue($billingMonth, 'PENDING_APPROVAL'), false);
    }

    public function getManagerQueueAdvanced(array $filters = [], bool $applySla = true, bool $includeLines = false): array
    {
        if ($applySla && !empty($filters['billing_month'])) {
            $this->processDeadlineSla((string)$filters['billing_month']);
        }
        return $this->normalizeQueueCycles($this->cycleRepo->getQueueAdvanced($filters), $includeLines);
    }

    private function normalizeQueueCycles(array $cycles, bool $includeLines): array
    {
        $rows = array_map(function ($cycle) use ($includeLines) {
            $row = $cycle->toArray();
            $row['lines'] = $this->formatCycleLines((int)($row['id'] ?? 0));
            $row = $this->recalculateCycleAmountsFromLines($row);
            if (!$includeLines) {
                unset($row['lines']);
            }
            return $row;
        }, $cycles);
        return array_values(array_filter($rows, static function (array $row): bool {
            return (int)($row['commission_line_count'] ?? 0) > 0;
        }));
    }

    public function getCommissionDrilldown(array $filters = []): array
    {
        $cycleId = (int)($filters['cycle_id'] ?? 0);
        if ($cycleId <= 0) {
            throw new \InvalidArgumentException('شناسه چرخه پورسانت نامعتبر است');
        }

        $cycle = $this->cycleRepo->findById($cycleId);
        if (!$cycle) {
            throw new \RuntimeException('چرخه پورسانت یافت نشد');
        }

        $metric = strtolower(trim((string)($filters['metric'] ?? 'all')));
        $statusFilter = strtoupper(trim((string)($filters['line_status'] ?? '')));
        $beneficiaryType = strtoupper(trim((string)($filters['beneficiary_type'] ?? '')));
        $paymentKind = strtolower(trim((string)($filters['payment_kind'] ?? '')));
        $ruleId = (int)($filters['rule_id'] ?? 0);

        $allLines = $this->formatCycleLines($cycleId);
        $lines = array_values(array_filter($allLines, static function (array $line) use ($metric, $statusFilter, $beneficiaryType, $paymentKind, $ruleId) {
            $lineStatus = strtoupper((string)($line['status'] ?? ''));
            if ($statusFilter !== '' && $lineStatus !== $statusFilter) {
                return false;
            }
            // Hide voided lines by default (they are not part of the payout) so the
            // drilldown total matches the cycle amount. They remain reachable by
            // explicitly filtering on line_status = VOID.
            if ($statusFilter === '' && $lineStatus === 'VOID') {
                return false;
            }
            if ($beneficiaryType !== '' && strtoupper((string)($line['beneficiary_type'] ?? '')) !== $beneficiaryType) {
                return false;
            }
            if ($paymentKind !== '' && strtolower((string)($line['payment_kind'] ?? '')) !== $paymentKind) {
                return false;
            }
            if ($ruleId > 0 && (int)($line['commission_rule_id'] ?? 0) !== $ruleId) {
                return false;
            }
            if ($metric === 'sales' && (string)($line['payment_kind'] ?? '') !== 'sale') {
                return false;
            }
            if ($metric === 'balance' && (string)($line['payment_kind'] ?? '') !== 'balance') {
                return false;
            }
            if ($metric === 'cheque' && empty($line['is_cheque'])) {
                return false;
            }
            if ($metric === 'deposit' && !empty($line['is_cheque'])) {
                return false;
            }
            if ($metric === 'positive' && (float)($line['calculated_commission'] ?? 0) <= 0) {
                return false;
            }
            if ($metric === 'reversal' && empty($line['is_reversal'])) {
                return false;
            }
            return true;
        }));

        return [
            'cycle' => $cycle->toArray(),
            'summary' => $this->summarizeDrilldownLines($lines, $allLines),
            'rules' => $this->summarizeRules($lines),
            'lines' => $lines,
            'filters' => [
                'cycle_id' => $cycleId,
                'metric' => $metric,
                'line_status' => $statusFilter,
                'beneficiary_type' => $beneficiaryType,
                'payment_kind' => $paymentKind,
                'rule_id' => $ruleId,
            ],
        ];
    }

    public function decidePayoutCycle(int $cycleId, string $action, int $managerId, ?float $overrideAmount = null, string $reason = ''): CommissionPayoutCycle
    {
        if ($cycleId <= 0 || $managerId <= 0) {
            throw new \InvalidArgumentException('شناسه چرخه یا مدیر نامعتبر است');
        }

        $action = strtoupper(trim($action));
        if (!in_array($action, ['APPROVE', 'OVERRIDE', 'REJECT'], true)) {
            throw new \InvalidArgumentException('اقدام نامعتبر است');
        }

        $pdo = $this->cycleRepo->getDb()->getPdo();
        $pdo->beginTransaction();
        try {
            $cycle = $this->cycleRepo->findById($cycleId);
            if (!$cycle) {
                throw new \RuntimeException('چرخه پورسانت یافت نشد');
            }
            if (strtoupper(trim((string)$cycle->status)) !== 'PENDING_APPROVAL') {
                throw new \RuntimeException('این چرخه دیگر قابل تصمیم‌گیری مدیریتی نیست');
            }
            $this->assertCycleLineStatuses($cycleId, ['CALCULATED']);

            $cycle->manager_id = $managerId;

            if ($action === 'APPROVE') {
                $cycle->status = 'APPROVED';
                $cycle->override_amount = null;
                $cycle->final_payable_amount = (float)$cycle->calculated_amount;
                $cycle->override_reason = '';
                $cycle->escalate_to_finance = 0;
                $this->cycleRepo->updateCycleLinesStatus($cycleId, 'MANAGER_APPROVED', ['CALCULATED']);
                $this->recordCycleLineEvents($cycleId, 'COMMISSION_PAYOUT_MANAGER_APPROVED', $managerId, [
                    'action' => $action,
                    'cycle_status' => $cycle->status,
                    'line_status' => 'MANAGER_APPROVED',
                ]);
            } elseif ($action === 'OVERRIDE') {
                if ($overrideAmount === null || $overrideAmount < 0) {
                    throw new \InvalidArgumentException('مبلغ Override نامعتبر است');
                }
                if (trim($reason) === '') {
                    throw new \InvalidArgumentException('علت Override الزامی است');
                }
                $cycle->status = 'OVERRIDDEN';
                $cycle->override_amount = round((float)$overrideAmount, 2);
                $cycle->final_payable_amount = (float)$cycle->override_amount;
                $cycle->override_reason = trim($reason);
                $cycle->escalate_to_finance = 0;
                $this->cycleRepo->updateCycleLinesStatus($cycleId, 'MANAGER_APPROVED', ['CALCULATED']);
                $this->recordCycleLineEvents($cycleId, 'COMMISSION_PAYOUT_MANAGER_OVERRIDDEN', $managerId, [
                    'action' => $action,
                    'cycle_status' => $cycle->status,
                    'line_status' => 'MANAGER_APPROVED',
                    'override_amount' => $cycle->override_amount,
                    'reason' => $cycle->override_reason,
                ]);
            } else {
                if (trim($reason) === '') {
                    throw new \InvalidArgumentException('علت رد الزامی است');
                }
                $cycle->status = 'REJECTED';
                $cycle->override_amount = null;
                $cycle->final_payable_amount = 0;
                $cycle->override_reason = trim($reason);
                $cycle->escalate_to_finance = 0;
                $this->cycleRepo->updateCycleLinesStatus($cycleId, 'VOID', ['CALCULATED']);
                $this->recordCycleLineEvents($cycleId, 'COMMISSION_PAYOUT_REJECTED', $managerId, [
                    'action' => $action,
                    'cycle_status' => $cycle->status,
                    'line_status' => 'VOID',
                    'reason' => $cycle->override_reason,
                ]);
            }

            $this->cycleRepo->save($cycle);
            $pdo->commit();
            $saved = $this->cycleRepo->findById($cycleId);
            if (!$saved) {
                throw new \RuntimeException('خطا در بازیابی چرخه ذخیره‌شده');
            }
            return $saved;
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    public function advancePayoutCycle(int $cycleId, string $action, int $actorUserId, string $reason = ''): CommissionPayoutCycle
    {
        if ($cycleId <= 0 || $actorUserId <= 0) {
            throw new \InvalidArgumentException('شناسه چرخه یا کاربر نامعتبر است');
        }

        $action = strtoupper(trim($action));
        $map = [
            'FINANCE_APPROVE' => [
                'from_cycle' => ['APPROVED', 'OVERRIDDEN'],
                'to_cycle' => 'FINANCE_APPROVED',
                'from_line' => ['MANAGER_APPROVED'],
                'to_line' => 'FINANCE_APPROVED',
                'event' => 'COMMISSION_PAYOUT_FINANCE_APPROVED',
            ],
            'READY_FOR_PAYOUT' => [
                'from_cycle' => ['FINANCE_APPROVED'],
                'to_cycle' => 'READY_FOR_PAYOUT',
                'from_line' => ['FINANCE_APPROVED'],
                'to_line' => 'READY_FOR_PAYOUT',
                'event' => 'COMMISSION_PAYOUT_READY_FOR_PAYOUT',
            ],
            'MARK_PAID' => [
                'from_cycle' => ['READY_FOR_PAYOUT'],
                'to_cycle' => 'PAID',
                'from_line' => ['READY_FOR_PAYOUT'],
                'to_line' => 'PAID',
                'event' => 'COMMISSION_PAYOUT_PAID',
            ],
            'SETTLE' => [
                'from_cycle' => ['PAID'],
                'to_cycle' => 'SETTLED',
                'from_line' => ['PAID'],
                'to_line' => 'SETTLED',
                'event' => 'COMMISSION_PAYOUT_SETTLED',
            ],
        ];
        if (!isset($map[$action])) {
            throw new \InvalidArgumentException('اقدام نامعتبر است');
        }

        $pdo = $this->cycleRepo->getDb()->getPdo();
        $pdo->beginTransaction();
        try {
            $cycle = $this->cycleRepo->findById($cycleId);
            if (!$cycle) {
                throw new \RuntimeException('چرخه پورسانت یافت نشد');
            }
            $rule = $map[$action];
            $currentCycleStatus = strtoupper(trim((string)$cycle->status));
            if (!in_array($currentCycleStatus, $rule['from_cycle'], true)) {
                throw new \RuntimeException('انتقال وضعیت چرخه مجاز نیست');
            }
            $this->assertCycleLineStatuses($cycleId, $rule['from_line']);

            $cycle->status = $rule['to_cycle'];
            if ($action === 'MARK_PAID' || $action === 'SETTLE') {
                $cycle->escalate_to_finance = 0;
            }
            if (trim($reason) !== '') {
                $cycle->override_reason = trim($reason);
            }
            $this->cycleRepo->updateCycleLinesStatus($cycleId, $rule['to_line'], $rule['from_line']);
            $this->recordCycleLineEvents($cycleId, $rule['event'], $actorUserId, [
                'action' => $action,
                'cycle_status' => $rule['to_cycle'],
                'line_status' => $rule['to_line'],
                'reason' => trim($reason),
            ]);
            $this->cycleRepo->save($cycle);
            $pdo->commit();

            $saved = $this->cycleRepo->findById($cycleId);
            if (!$saved) {
                throw new \RuntimeException('خطا در بازیابی چرخه ذخیره‌شده');
            }
            return $saved;
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    private function buildApprovalDeadline(string $billingMonth, int $payrollDay): string
    {
        $payrollDay = max(1, min(31, $payrollDay));
        $year = (int)substr($billingMonth, 0, 4);
        $month = (int)substr($billingMonth, 5, 2);
        $daysInMonth = (int)date('t', strtotime(sprintf('%04d-%02d-01', $year, $month)));
        $effectivePayrollDay = min($payrollDay, $daysInMonth);
        $payrollDate = sprintf('%04d-%02d-%02d', $year, $month, $effectivePayrollDay);
        $deadline = date('Y-m-d', strtotime($payrollDate . ' -2 days'));
        return $deadline . ' 23:59:59';
    }

    private function buildApprovalDeadlineForPeriod(array $period, int $payrollDay): string
    {
        if ((string)($period['calendar'] ?? '') !== 'jalali') {
            return $this->buildApprovalDeadline((string)$period['input_month'], $payrollDay);
        }

        $payrollDay = max(1, min(31, $payrollDay));
        [$jy, $jm] = array_map('intval', explode('-', (string)$period['jalali_month']));
        $effectivePayrollDay = min($payrollDay, DateNormalizer::jalaliMonthLength($jy, $jm));
        [$gy, $gm, $gd] = DateNormalizer::jalaliToGregorian($jy, $jm, $effectivePayrollDay);
        $payrollDate = sprintf('%04d-%02d-%02d', $gy, $gm, $gd);
        $deadline = date('Y-m-d', strtotime($payrollDate . ' -2 days'));
        return $deadline . ' 23:59:59';
    }

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

    private function assertCycleLineStatuses(int $cycleId, array $allowedStatuses): void
    {
        $allowedStatuses = array_values(array_unique(array_map(static function ($status) {
            return strtoupper(trim((string)$status));
        }, $allowedStatuses)));
        $counts = $this->cycleRepo->getCycleLineStatusCounts($cycleId);
        if (empty($counts)) {
            throw new \RuntimeException('هیچ line پورسانتی به این چرخه وصل نیست');
        }
        foreach ($counts as $status => $count) {
            if ($count > 0 && !in_array(strtoupper((string)$status), $allowedStatuses, true)) {
                throw new \RuntimeException('وضعیت lineهای پورسانت با انتقال درخواستی سازگار نیست');
            }
        }
    }

    private function recordCycleLineEvents(int $cycleId, string $eventType, int $actorUserId, array $metadata): void
    {
        if (!$this->paymentEventRepo) {
            return;
        }
        foreach ($this->cycleRepo->getCycleLines($cycleId) as $line) {
            $paymentRecordId = (int)($line['payment_record_id'] ?? 0);
            if ($paymentRecordId <= 0) {
                continue;
            }
            $this->paymentEventRepo->recordEvent(
                $paymentRecordId,
                $eventType,
                [],
                [
                    'commission_line_id' => (int)($line['id'] ?? 0),
                    'commission_line_status' => (string)($line['status'] ?? ''),
                ],
                array_merge($metadata, [
                    'payout_cycle_id' => $cycleId,
                    'commission_line_id' => (int)($line['id'] ?? 0),
                    'beneficiary_user_id' => (int)($line['beneficiary_user_id'] ?? 0),
                    'beneficiary_type' => (string)($line['beneficiary_type'] ?? ''),
                    'calculated_commission' => (float)($line['calculated_commission'] ?? 0),
                ]),
                $actorUserId
            );
        }
    }

    private function recalculateCycleAmountsFromLines(array $row): array
    {
        $lines = array_values(array_filter($row['lines'] ?? [], static function (array $line): bool {
            return strtoupper(trim((string)($line['status'] ?? ''))) !== 'VOID';
        }));
        $calculated = 0.0;
        foreach ($lines as $line) {
            $calculated += (float)($line['calculated_commission'] ?? 0);
        }
        $calculated = round($calculated, 2);
        $status = strtoupper(trim((string)($row['status'] ?? '')));
        $overrideAmount = isset($row['override_amount']) && $row['override_amount'] !== '' && $row['override_amount'] !== null
            ? round((float)$row['override_amount'], 2)
            : null;

        $row['lines'] = $lines;
        $row['commission_line_count'] = count($lines);
        $row['calculated_amount'] = $calculated;
        if ($status === 'REJECTED') {
            $row['final_payable_amount'] = 0;
        } elseif ($overrideAmount !== null && in_array($status, ['OVERRIDDEN', 'FINANCE_APPROVED', 'READY_FOR_PAYOUT', 'PAID', 'SETTLED', 'TRANSFERRED_TO_PAYROLL'], true)) {
            $row['final_payable_amount'] = $overrideAmount;
        } else {
            $row['final_payable_amount'] = $calculated;
        }
        return $row;
    }

    private function formatCycleLines(int $cycleId): array
    {
        $rows = array_values(array_filter($this->cycleRepo->getCycleLines($cycleId), function (array $row): bool {
            return !$this->isOrphanBalanceCommissionLine($row);
        }));
        return array_map(function (array $row) {
            $customerName = trim((string)($row['customer_name'] ?? ''));
            $dealTitle = trim((string)($row['deal_title'] ?? ''));
            $paymentOwnerName = trim((string)($row['payment_owner_name_snapshot'] ?? ''));
            $amount = (float)($row['payment_amount'] ?? $row['payment_amount_snapshot'] ?? 0);
            $base = (float)($row['commission_base_amount'] ?? 0);
            $rate = (float)($row['applied_rate_percent'] ?? 0);
            $commission = (float)($row['calculated_commission'] ?? 0);
            $sequence = isset($row['payment_sequence']) ? (int)$row['payment_sequence'] : null;
            $isCheque = $this->isChequeLine($row);
            $paymentKind = ($sequence !== null && $sequence > 1) ? 'balance' : 'sale';
            return [
                'id' => (int)($row['id'] ?? 0),
                'commission_line_id' => (int)($row['id'] ?? 0),
                'payment_record_id' => (int)($row['payment_record_id'] ?? 0),
                'payment_uuid' => (string)($row['payment_uuid'] ?? ''),
                'payment_sequence' => $sequence,
                'payment_label' => $this->paymentLabel($row),
                'payment_method' => (string)($row['payment_method'] ?? ''),
                'payment_date' => (string)($row['payment_date'] ?? ''),
                'deposit_date' => (string)($row['deposit_date'] ?? ''),
                'payment_effective_date' => (string)($row['payment_effective_date'] ?? ''),
                'effective_date' => (string)($row['effective_date'] ?? ''),
                'payment_amount' => $amount,
                'payment_amount_snapshot' => (float)($row['payment_amount_snapshot'] ?? 0),
                'commission_base_amount' => $base,
                'applied_rate_percent' => $rate,
                'calculated_commission' => $commission,
                'calculation_formula' => $this->buildFormulaLabel($base, $rate, $commission),
                'beneficiary_type' => (string)($row['beneficiary_type'] ?? ''),
                'beneficiary_user_id' => (int)($row['beneficiary_user_id'] ?? 0),
                'payment_owner_user_id' => (int)($row['payment_owner_user_id'] ?? 0),
                'status' => (string)($row['status'] ?? ''),
                'customer_name' => $customerName,
                'customer_code' => (string)($row['customer_code'] ?? ''),
                'customer_mobile' => (string)($row['customer_mobile'] ?? ''),
                'person_id' => (int)($row['person_id'] ?? $row['deal_person_id'] ?? 0),
                'contact_didar_id' => (string)($row['contact_didar_id'] ?? $row['deal_contact_didar_id'] ?? ''),
                'current_lead_owner_name' => (string)($row['current_lead_owner_name'] ?? ''),
                'local_deal_id' => (int)($row['local_deal_id'] ?? 0),
                'deal_title' => $dealTitle,
                'deal_code' => (string)($row['deal_code'] ?? ''),
                'deal_invoice_code' => (string)($row['deal_invoice_code'] ?? ''),
                'deal_didar_id' => (string)($row['local_deal_didar_id'] ?? $row['deal_didar_id'] ?? ''),
                'payment_owner_name' => $paymentOwnerName,
                'product_name' => (string)($row['product_name'] ?? ''),
                'commission_rule_id' => (int)($row['commission_rule_id'] ?? 0),
                'commission_rule_assignment_id' => (int)($row['commission_rule_assignment_id'] ?? 0),
                'rule_title' => (string)($row['rule_title'] ?? ''),
                'rule_type' => (string)($row['rule_type'] ?? ''),
                'rule_valid_from' => (string)($row['rule_valid_from'] ?? ''),
                'rule_valid_to' => (string)($row['rule_valid_to'] ?? ''),
                'assignment_assigned_at' => (string)($row['assignment_assigned_at'] ?? ''),
                'assignment_disabled_at' => (string)($row['assignment_disabled_at'] ?? ''),
                'assignment_source_type' => strtoupper(trim((string)($row['assignment_source_type'] ?? 'INDIVIDUAL'))) ?: 'INDIVIDUAL',
                'assignment_source_team_supervisor_user_id' => (int)($row['assignment_source_team_supervisor_user_id'] ?? 0),
                'assignment_source_team_supervisor_name' => (string)($row['assignment_source_team_supervisor_name'] ?? ''),
                'cheque_status' => (string)($row['cheque_status'] ?? ''),
                'cheque_collection_status' => (string)($row['cheque_collection_status'] ?? ''),
                'cheque_collection_date' => (string)($row['cheque_collection_date'] ?? ''),
                'is_cheque' => $isCheque ? 1 : 0,
                'payment_kind' => $paymentKind,
                'payment_kind_label' => $paymentKind === 'balance' ? 'مانده حساب' : 'فروش',
                'is_reversal' => (int)($row['is_reversal'] ?? 0),
                'reversal_of_line_id' => isset($row['reversal_of_line_id']) && $row['reversal_of_line_id'] !== '' ? (int)$row['reversal_of_line_id'] : null,
                'source_hash' => (string)($row['source_hash'] ?? ''),
                'calculation_run_id' => (string)($row['calculation_run_id'] ?? ''),
                'rule_snapshot' => $this->decodeJsonObject($row['rule_snapshot_json'] ?? null),
                'tier_snapshot' => $this->decodeJsonObject($row['tier_snapshot_json'] ?? null),
                'payment_snapshot' => $this->decodeJsonObject($row['payment_snapshot_json'] ?? null),
                'hierarchy_snapshot' => $this->decodeJsonObject($row['hierarchy_snapshot_json'] ?? null),
                'source_title' => $customerName !== '' ? $customerName : ($dealTitle !== '' ? $dealTitle : (string)($row['deal_didar_id'] ?? '')),
            ];
        }, $rows);
    }

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

    private function commissionLineHasPriorSalePayment(array $row): bool
    {
        $pdo = $this->cycleRepo->getDb()->getPdo();
        $paymentRecordId = (int)($row['payment_record_id'] ?? 0);
        $commissionLineId = (int)($row['id'] ?? $row['commission_line_id'] ?? 0);
        $dealId = (int)($row['local_deal_id'] ?? 0);
        $dealDidarId = trim((string)($row['local_deal_didar_id'] ?? $row['deal_didar_id'] ?? ''));
        $effectiveDate = trim((string)($row['effective_date'] ?? $row['payment_effective_date'] ?? $row['deposit_date'] ?? $row['payment_date'] ?? ''));

        if (($dealId <= 0 && $dealDidarId === '') || $effectiveDate === '') {
            return false;
        }

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

        $sql = "
            SELECT 1
            FROM payment_records prev
            INNER JOIN commission_lines prior_line
              ON prior_line.payment_record_id = prev.id
             AND COALESCE(prior_line.status, 'CALCULATED') <> 'VOID'
             AND prior_line.calculated_commission > 0
            WHERE (" . implode(' OR ', $dealWhere) . ")
              AND prev.amount > 0
              AND UPPER(COALESCE(prev.refund_status, 'NONE')) <> 'FULL'
              AND COALESCE(prev.is_deleted_placeholder, 0) = 0
              AND COALESCE(prev.payment_sequence, 1) <= 1
              AND COALESCE(DATE(prev.effective_date), DATE(prev.deposit_date), DATE(prev.payment_date), DATE(prev.cheque_collection_date), DATE(prev.registered_at), DATE(prev.created_at)) <= ?
        ";
        $params[] = substr($effectiveDate, 0, 10);
        if ($paymentRecordId > 0) {
            $sql .= ' AND prev.id <> ?';
            $params[] = $paymentRecordId;
        }
        if ($commissionLineId > 0) {
            $sql .= ' AND prior_line.id < ?';
            $params[] = $commissionLineId;
        }
        $sql .= ' LIMIT 1';

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return (bool)$stmt->fetchColumn();
    }

    private function summarizeDrilldownLines(array $lines, array $allLines): array
    {
        $paymentIds = [];
        $dealIds = [];
        $includedPayments = 0;
        $chequeCount = 0;
        $saleCount = 0;
        $balanceCount = 0;
        $paymentTotal = 0.0;
        $commissionTotal = 0.0;
        foreach ($lines as $line) {
            $paymentId = (int)($line['payment_record_id'] ?? 0);
            if ($paymentId > 0 && !isset($paymentIds[$paymentId])) {
                $paymentIds[$paymentId] = true;
                $paymentTotal += (float)($line['payment_amount'] ?? $line['payment_amount_snapshot'] ?? 0);
                $includedPayments++;
            }
            $dealKey = (string)($line['local_deal_id'] ?? '') ?: (string)($line['deal_didar_id'] ?? '');
            if ($dealKey !== '') {
                $dealIds[$dealKey] = true;
            }
            if (!empty($line['is_cheque'])) {
                $chequeCount++;
            }
            if ((string)($line['payment_kind'] ?? '') === 'balance') {
                $balanceCount++;
            } else {
                $saleCount++;
            }
            $commissionTotal += (float)($line['calculated_commission'] ?? 0);
        }

        return [
            'line_count' => count($lines),
            'all_line_count' => count($allLines),
            'payment_count' => $includedPayments,
            'deal_count' => count($dealIds),
            'sale_payment_count' => $saleCount,
            'balance_payment_count' => $balanceCount,
            'cheque_line_count' => $chequeCount,
            'payment_total' => round($paymentTotal, 2),
            'commission_total' => round($commissionTotal, 2),
        ];
    }

    private function summarizeRules(array $lines): array
    {
        $rules = [];
        foreach ($lines as $line) {
            $id = (int)($line['commission_rule_id'] ?? 0);
            $key = $id > 0 ? (string)$id : 'none';
            if (!isset($rules[$key])) {
                $rules[$key] = [
                    'rule_id' => $id,
                    'rule_title' => (string)($line['rule_title'] ?? 'بدون قانون'),
                    'rule_type' => (string)($line['rule_type'] ?? ''),
                    'valid_from' => (string)($line['rule_valid_from'] ?? ''),
                    'valid_to' => (string)($line['rule_valid_to'] ?? ''),
                    'line_count' => 0,
                    'commission_total' => 0.0,
                    'payment_total' => 0.0,
                    'rates' => [],
                ];
            }
            $rules[$key]['line_count']++;
            $rules[$key]['commission_total'] += (float)($line['calculated_commission'] ?? 0);
            $rules[$key]['payment_total'] += (float)($line['payment_amount'] ?? 0);
            $rate = (float)($line['applied_rate_percent'] ?? 0);
            if ($rate > 0) {
                $rules[$key]['rates'][(string)$rate] = true;
            }
        }
        return array_values(array_map(static function (array $rule) {
            $rule['commission_total'] = round((float)$rule['commission_total'], 2);
            $rule['payment_total'] = round((float)$rule['payment_total'], 2);
            $rule['rates'] = array_keys($rule['rates']);
            return $rule;
        }, $rules));
    }

    private function isChequeLine(array $row): bool
    {
        $methodRaw = trim((string)($row['payment_method'] ?? ''));
        $method = function_exists('mb_strtolower') ? mb_strtolower($methodRaw, 'UTF-8') : strtolower($methodRaw);
        $methodIsCheque = strpos($method, 'cheque') !== false
            || strpos($method, 'check') !== false
            || strpos($method, 'چک') !== false;
        if ($methodRaw !== '') {
            return $methodIsCheque;
        }
        $chequeStatus = strtolower(trim((string)($row['cheque_status'] ?? '')));
        $collectionStatus = strtolower(trim((string)($row['cheque_collection_status'] ?? '')));
        return !in_array($chequeStatus, ['', 'not_cheque'], true)
            || !in_array($collectionStatus, ['', 'not_cheque'], true);
    }

    private function buildFormulaLabel(float $base, float $rate, float $commission): string
    {
        if ($base <= 0 || $rate <= 0) {
            return '';
        }
        return number_format($base, 0, '.', ',') . ' × ' . rtrim(rtrim(number_format($rate, 4, '.', ''), '0'), '.') . '% = ' . number_format($commission, 0, '.', ',');
    }

    private function decodeJsonObject($json): array
    {
        if (!is_string($json) || trim($json) === '') {
            return [];
        }
        $decoded = json_decode($json, true);
        return is_array($decoded) ? $decoded : [];
    }

    private function paymentLabel(array $row): string
    {
        $sequence = $row['payment_sequence'] ?? null;
        if ($sequence !== null && $sequence !== '') {
            return 'پرداخت ' . (int)$sequence;
        }
        $id = (int)($row['payment_record_id'] ?? 0);
        return $id > 0 ? 'پرداخت #' . $id : 'پرداخت';
    }
}
