<?php
namespace App\Services;

use App\Database\Connection;
use App\Utils\Logger;
use PDO;

class ExportService
{
    private Connection $db;
    private array $lookupCache = [];
    private array $virtualStageMap = [
        'new' => 'جدید',
        'contact_no_answer' => 'تماس عدم پاسخ',
        'contact_followup' => 'تماس پیگیری',
        'deal_registered' => 'ثبت معامله و لینک پرداخت',
        'payment' => 'پرداخت',
        'payment_followup' => 'پیگیری مانده حساب',
        'course_delivered' => 'برگزاری دوره',
        'support' => 'پشتیبانی',
        'with_purchase' => 'با خرید',
        'without_purchase' => 'بدون خرید',
        'refer_crm' => 'ارجاع به CRM',
    ];

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    private function applyOwnerWhere(array &$where, array &$params, array $filters, string $column): void
    {
        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ids = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static fn($v) => trim($v) !== ''));
            if (!empty($ids)) {
                $where[] = $column . " IN (" . implode(',', array_fill(0, count($ids), '?')) . ")";
                $params = array_merge($params, $ids);
                return;
            }
        }
        if (!empty($filters['owner_didar_ids_csv'])) {
            $ids = array_values(array_filter(array_map('trim', explode(',', (string)$filters['owner_didar_ids_csv']))));
            if (!empty($ids)) {
                $where[] = $column . " IN (" . implode(',', array_fill(0, count($ids), '?')) . ")";
                $params = array_merge($params, $ids);
                return;
            }
        }
        if (!empty($filters['owner_didar_id'])) {
            $where[] = $column . " = ?";
            $params[] = $filters['owner_didar_id'];
        }
    }

    public function getCatalog(): array
    {
        $owners = $this->getOwnerOptions();
        $dealStages = [
            'بلاتکلیف', 'معامله جدید', 'تماس عدم پاسخ', 'تماس پیگیری',
            'ارسال لینک پرداخت', 'پرداخت', 'پیگیری مانده حساب', 'برگزاری دوره',
            'پشتیبانی', 'ارجاع به CRM', 'اتمام'
        ];

        // Fetch custom field configurations to add to export columns
        $pdo = $this->db->getPdo();
        $customConfigs = $pdo->query("SELECT field_name, field_label, entity_type FROM field_configurations WHERE is_active = 1")->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $leadsColumns = [
            ['key' => 'lead_id', 'label' => 'شناسه لید'],
            ['key' => 'lead_code', 'label' => 'کد مشتری'],
            ['key' => 'first_name', 'label' => 'نام'],
            ['key' => 'last_name', 'label' => 'نام خانوادگی'],
            ['key' => 'mobile_phone', 'label' => 'موبایل'],
            ['key' => 'secondary_mobile_phone', 'label' => 'موبایل ۲'],
            ['key' => 'mobile_phone_3', 'label' => 'موبایل ۳'],
            ['key' => 'mobile_phone_4', 'label' => 'موبایل ۴'],
            ['key' => 'work_phone', 'label' => 'تلفن ثابت/کار'],
            ['key' => 'email', 'label' => 'ایمیل'],
            ['key' => 'city', 'label' => 'شهر'],
            ['key' => 'job_title', 'label' => 'شغل'],
            ['key' => 'job_description', 'label' => 'شرح شغل'],
            ['key' => 'acquaintance_source', 'label' => 'شیوه آشنایی'],
            ['key' => 'acquaintance_detail', 'label' => 'جزئیات آشنایی'],
            ['key' => 'acquaintance_duration', 'label' => 'مدت آشنایی'],
            ['key' => 'content_topic', 'label' => 'موضوع محتوا'],
            ['key' => 'national_id', 'label' => 'کد ملی'],
            ['key' => 'has_previous_purchase', 'label' => 'خرید قبلی'],
            ['key' => 'sale_status', 'label' => 'وضعیت فروش لید'],
            ['key' => 'register_time_jalali', 'label' => 'تاریخ ثبت (شمسی)'],
            ['key' => 'register_time', 'label' => 'تاریخ ثبت (نمایش)'],
            ['key' => 'owner_name', 'label' => 'مسئول'],
            ['key' => 'previous_owner_name', 'label' => 'مسئول قبلی'],
            ['key' => 'kariz_active_stage', 'label' => 'مرحله کاریز (فعال)'],
            ['key' => 'kariz_special_stage', 'label' => 'مرحله کاریز (ویژه)'],
            ['key' => 'kariz_most_recent_stage', 'label' => 'آخرین مرحله کاریز'],
            ['key' => 'customer_products', 'label' => 'محصولات/خدمات خریداری‌شده'],
            ['key' => 'requested_services', 'label' => 'خدمات درخواستی'],
            ['key' => 'created_by_name', 'label' => 'ثبت شده توسط'],
            ['key' => 'created_via', 'label' => 'روش ثبت'],
            ['key' => 'deal_count', 'label' => 'تعداد معاملات'],
            ['key' => 'won_deal_count', 'label' => 'تعداد معاملات موفق'],
            ['key' => 'lost_deal_count', 'label' => 'تعداد معاملات ناموفق'],
            ['key' => 'current_deal_count', 'label' => 'تعداد معاملات جاری'],
        ];

        // Add custom person/lead fields
        foreach ($customConfigs as $conf) {
            if ($conf['entity_type'] === 'person') {
                $leadsColumns[] = ['key' => $conf['field_name'], 'label' => $conf['field_label'] . ' (سفارشی)'];
            }
        }
        $leadsColumns[] = ['key' => 'raw_json', 'label' => 'دیتای خام (raw_json)'];

        $dealsColumns = [
            ['key' => 'deal_id', 'label' => 'شناسه معامله'],
            ['key' => 'deal_internal_id', 'label' => 'شناسه داخلی'],
            ['key' => 'title', 'label' => 'عنوان'],
            ['key' => 'code', 'label' => 'کد'],
            ['key' => 'status', 'label' => 'وضعیت'],
            ['key' => 'pipeline_stage', 'label' => 'مرحله کاریز'],
            ['key' => 'owner_name', 'label' => 'مسئول'],
            ['key' => 'customer_code', 'label' => 'کد مشتری'],
            ['key' => 'contact_id', 'label' => 'شناسه لید'],
            ['key' => 'contact_name', 'label' => 'نام لید'],
            ['key' => 'contact_mobile', 'label' => 'موبایل لید'],
            ['key' => 'acquaintance_source', 'label' => 'شیوه آشنایی معامله'],
            ['key' => 'acquaintance_detail', 'label' => 'جزئیات شیوه آشنایی معامله'],
            ['key' => 'content_topic', 'label' => 'موضوع محتوا معامله'],
            ['key' => 'register_time_jalali', 'label' => 'تاریخ ثبت (شمسی)'],
            ['key' => 'products', 'label' => 'محصولات معامله'],
            ['key' => 'service_cost', 'label' => 'هزینه خدمات'],
            ['key' => 'invoice_code', 'label' => 'کد فاکتور'],
            ['key' => 'payable_amount', 'label' => 'مبلغ قابل پرداخت'],
            ['key' => 'outstanding_balance', 'label' => 'مانده حساب'],
            ['key' => 'payment_amount', 'label' => 'مبلغ پرداخت‌شده'],
            ['key' => 'payment_method', 'label' => 'روش پرداخت'],
            ['key' => 'payment_description', 'label' => 'توضیحات پرداخت'],
            ['key' => 'payments', 'label' => 'پرداخت‌ها (JSON)'],
            ['key' => 'is_paid', 'label' => 'پرداخت شده'],
            ['key' => 'payment_short_link', 'label' => 'لینک پرداخت'],
            ['key' => 'has_discount', 'label' => 'تخفیف دارد'],
            ['key' => 'discount_type', 'label' => 'نوع تخفیف'],
            ['key' => 'discount_amount', 'label' => 'مبلغ تخفیف'],
            ['key' => 'refund_amount', 'label' => 'مبلغ مرجوعی'],
            ['key' => 'refund_description', 'label' => 'توضیحات مرجوعی'],
            ['key' => 'failure_reason_code', 'label' => 'کد دلیل ناموفق'],
            ['key' => 'failure_reason_text', 'label' => 'عنوان دلیل ناموفق'],
            ['key' => 'failure_reason_description', 'label' => 'توضیحات دلیل ناموفق'],
            ['key' => 'register_time', 'label' => 'زمان ثبت (شمسی)'],
            ['key' => 'won_time', 'label' => 'زمان موفق (شمسی)'],
            ['key' => 'lost_time', 'label' => 'زمان ناموفق (شمسی)'],
            ['key' => 'created_by_name', 'label' => 'ثبت شده توسط'],
            ['key' => 'created_via', 'label' => 'روش ثبت'],
        ];

        // Add custom deal fields
        foreach ($customConfigs as $conf) {
            if ($conf['entity_type'] === 'deal' && !in_array($conf['field_name'], ['deal_acquaintance_source', 'deal_acquaintance_detail', 'deal_content_topic'], true)) {
                $dealsColumns[] = ['key' => $conf['field_name'], 'label' => $conf['field_label'] . ' (سفارشی)'];
            }
        }
        $dealsColumns[] = ['key' => 'raw_json', 'label' => 'دیتای خام (raw_json)'];

        return [
            'datasets' => [
                [
                    'id' => 'leads',
                    'title' => 'خروجی لیدها (اشخاص)',
                    'formats' => ['csv'],
                    'filters' => [
                        ['key' => 'sale_status', 'label' => 'وضعیت لید', 'type' => 'select', 'options' => [
                            ['value' => 'all', 'label' => 'همه'],
                            ['value' => 'success', 'label' => 'موفق'],
                            ['value' => 'failed', 'label' => 'ناموفق'],
                        ]],
                        ['key' => 'owner_didar_id', 'label' => 'مسئول', 'type' => 'select', 'options' => array_merge([['value' => '', 'label' => 'همه']], $owners)],
                        ['key' => 'register_date_from_jalali', 'label' => 'از تاریخ ثبت (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/01'],
                        ['key' => 'register_date_to_jalali', 'label' => 'تا تاریخ ثبت (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/30'],
                        ['key' => 'include_raw_json', 'label' => 'raw_json', 'type' => 'checkbox'],
                    ],
                    'columns' => $leadsColumns,
                ],
                [
                    'id' => 'deals',
                    'title' => 'گزارش معاملات',
                    'formats' => ['csv'],
                    'filters' => [
                        ['key' => 'status', 'label' => 'وضعیت معامله', 'type' => 'select', 'options' => [
                            ['value' => 'all', 'label' => 'همه'],
                            ['value' => 'Pending', 'label' => 'جاری'],
                            ['value' => 'Won', 'label' => 'موفق'],
                            ['value' => 'Lost', 'label' => 'ناموفق'],
                        ]],
                        ['key' => 'pipeline_stage', 'label' => 'مرحله کاریز', 'type' => 'select', 'options' => array_merge([['value' => 'all', 'label' => 'همه']], array_map(fn($s) => ['value' => $s, 'label' => $s], $dealStages))],
                        ['key' => 'owner_didar_id', 'label' => 'مسئول', 'type' => 'select', 'options' => array_merge([['value' => '', 'label' => 'همه']], $owners)],
                        ['key' => 'register_date_from_jalali', 'label' => 'از تاریخ ثبت معامله (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/01'],
                        ['key' => 'register_date_to_jalali', 'label' => 'تا تاریخ ثبت معامله (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/30'],
                        ['key' => 'include_legacy', 'label' => 'شامل معاملات قدیمی (دیدار)', 'type' => 'checkbox'],
                        ['key' => 'include_raw_json', 'label' => 'raw_json', 'type' => 'checkbox'],
                    ],
                    'columns' => $dealsColumns,
                ],
                [
                    'id' => 'activities',
                    'title' => 'گزارش فعالیت‌ها',
                    'formats' => ['csv'],
                    'filters' => [
                        ['key' => 'is_done', 'label' => 'وضعیت انجام', 'type' => 'select', 'options' => [
                            ['value' => 'all', 'label' => 'همه'],
                            ['value' => '1', 'label' => 'انجام شده'],
                            ['value' => '0', 'label' => 'انجام نشده'],
                        ]],
                        ['key' => 'owner_didar_id', 'label' => 'مسئول', 'type' => 'select', 'options' => array_merge([['value' => '', 'label' => 'همه']], $owners)],
                        ['key' => 'register_date_from_jalali', 'label' => 'از تاریخ ثبت فعالیت (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/01'],
                        ['key' => 'register_date_to_jalali', 'label' => 'تا تاریخ ثبت فعالیت (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/30'],
                        ['key' => 'include_raw_json', 'label' => 'raw_json', 'type' => 'checkbox'],
                    ],
                    'columns' => [
                        ['key' => 'activity_id', 'label' => 'شناسه فعالیت'],
                        ['key' => 'activity_type_title', 'label' => 'نوع فعالیت'],
                        ['key' => 'title', 'label' => 'عنوان'],
                        ['key' => 'note', 'label' => 'یادداشت'],
                        ['key' => 'result_note', 'label' => 'نتیجه'],
                        ['key' => 'failure_reason_code', 'label' => 'کد دلیل ناموفق'],
                        ['key' => 'failure_reason_description', 'label' => 'توضیحات دلیل ناموفق'],
                        ['key' => 'is_done', 'label' => 'انجام شده'],
                        ['key' => 'due_date', 'label' => 'زمان سررسید'],
                        ['key' => 'done_date', 'label' => 'زمان انجام'],
                        ['key' => 'duration', 'label' => 'مدت'],
                        ['key' => 'direction', 'label' => 'جهت'],
                        ['key' => 'stage', 'label' => 'مرحله (در فعالیت)'],
                        ['key' => 'owner_name', 'label' => 'مسئول'],
                        ['key' => 'customer_code', 'label' => 'کد مشتری'],
                        ['key' => 'contact_id', 'label' => 'شناسه لید'],
                        ['key' => 'contact_name', 'label' => 'نام لید'],
                        ['key' => 'deal_id', 'label' => 'شناسه معامله'],
                        ['key' => 'register_date', 'label' => 'زمان ثبت'],
                        ['key' => 'raw_json', 'label' => 'دیتای خام (raw_json)'],
                    ],
                ],
                [
                    'id' => 'payments',
                    'title' => 'گزارش پرداخت‌ها (از داخل معاملات)',
                    'formats' => ['csv'],
                    'filters' => [
                        ['key' => 'payment_date_from_persian', 'label' => 'از تاریخ پرداخت (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/01'],
                        ['key' => 'payment_date_to_persian', 'label' => 'تا تاریخ پرداخت (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/30'],
                        ['key' => 'deal_status', 'label' => 'وضعیت معامله', 'type' => 'select', 'options' => [
                            ['value' => 'all', 'label' => 'همه'],
                            ['value' => 'Pending', 'label' => 'جاری'],
                            ['value' => 'Won', 'label' => 'موفق'],
                            ['value' => 'Lost', 'label' => 'ناموفق'],
                        ]],
                        ['key' => 'owner_didar_id', 'label' => 'مسئول', 'type' => 'select', 'options' => array_merge([['value' => '', 'label' => 'همه']], $owners)],
                        ['key' => 'include_legacy', 'label' => 'شامل معاملات قدیمی (دیدار)', 'type' => 'checkbox'],
                    ],
                    'columns' => [
                        ['key' => 'payment_date_persian', 'label' => 'تاریخ پرداخت (شمسی)'],
                        ['key' => 'payment_date', 'label' => 'تاریخ پرداخت (شمسی)'],
                        ['key' => 'amount', 'label' => 'مبلغ'],
                        ['key' => 'method', 'label' => 'روش'],
                        ['key' => 'card_last4', 'label' => '۴ رقم آخر کارت'],
                        ['key' => 'bank_destination', 'label' => 'بانک مقصد'],
                        ['key' => 'destination_card_number', 'label' => '۴ رقم آخر کارت مقصد'],
                        ['key' => 'card_reader_device', 'label' => 'نام دستگاه کارت‌خوان'],
                        ['key' => 'currency', 'label' => 'نوع ارز'],
                        ['key' => 'currency_amount', 'label' => 'مبلغ ارز'],
                        ['key' => 'check_bank_name', 'label' => 'نام بانک مبدا چک'],
                        ['key' => 'check_date', 'label' => 'تاریخ چک (شمسی)'],
                        ['key' => 'check_number', 'label' => 'شماره چک'],
                        ['key' => 'check_owner_name', 'label' => 'نام صاحب چک'],
                        ['key' => 'description', 'label' => 'توضیحات'],
                        ['key' => 'invoice_code', 'label' => 'کد فاکتور'],
                        ['key' => 'deal_id', 'label' => 'شناسه معامله'],
                        ['key' => 'deal_title', 'label' => 'عنوان معامله'],
                        ['key' => 'outstanding_balance', 'label' => 'مانده حساب'],
                        ['key' => 'deal_status', 'label' => 'وضعیت معامله'],
                        ['key' => 'pipeline_stage', 'label' => 'مرحله کاریز'],
                        ['key' => 'customer_code', 'label' => 'کد مشتری'],
                        ['key' => 'contact_id', 'label' => 'شناسه لید'],
                        ['key' => 'contact_name', 'label' => 'نام لید'],
                        ['key' => 'owner_name', 'label' => 'مسئول'],
                    ],
                ],
                [
                    'id' => 'check_payments',
                    'title' => 'گزارش چک‌ها',
                    'formats' => ['csv'],
                    'filters' => [
                        ['key' => 'payment_date_from_persian', 'label' => 'از تاریخ پرداخت (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/01'],
                        ['key' => 'payment_date_to_persian', 'label' => 'تا تاریخ پرداخت (شمسی)', 'type' => 'date_jalali', 'placeholder' => 'مثال: 1403/10/30'],
                        ['key' => 'deal_status', 'label' => 'وضعیت معامله', 'type' => 'select', 'options' => [
                            ['value' => 'all', 'label' => 'همه'],
                            ['value' => 'Pending', 'label' => 'جاری'],
                            ['value' => 'Won', 'label' => 'موفق'],
                            ['value' => 'Lost', 'label' => 'ناموفق'],
                        ]],
                        ['key' => 'owner_didar_id', 'label' => 'مسئول', 'type' => 'select', 'options' => array_merge([['value' => '', 'label' => 'همه']], $owners)],
                        ['key' => 'include_legacy', 'label' => 'شامل معاملات قدیمی (دیدار)', 'type' => 'checkbox'],
                    ],
                    'columns' => [
                        ['key' => 'payment_date_persian', 'label' => 'تاریخ چک (شمسی)'],
                        ['key' => 'payment_date', 'label' => 'تاریخ چک (شمسی)'],
                        ['key' => 'amount', 'label' => 'مبلغ چک'],
                        ['key' => 'check_bank_name', 'label' => 'بانک مبدا چک'],
                        ['key' => 'check_date', 'label' => 'تاریخ چک (شمسی)'],
                        ['key' => 'check_number', 'label' => 'شماره چک'],
                        ['key' => 'check_owner_name', 'label' => 'نام صاحب چک'],
                        ['key' => 'description', 'label' => 'توضیحات'],
                        ['key' => 'invoice_code', 'label' => 'کد فاکتور'],
                        ['key' => 'deal_id', 'label' => 'شناسه معامله'],
                        ['key' => 'deal_title', 'label' => 'عنوان معامله'],
                        ['key' => 'products', 'label' => 'محصولات معامله'],
                        ['key' => 'service_cost', 'label' => 'هزینه خدمات'],
                        ['key' => 'price_list_code', 'label' => 'کد لیست قیمت'],
                        ['key' => 'payable_amount', 'label' => 'مبلغ قابل پرداخت'],
                        ['key' => 'outstanding_balance', 'label' => 'مانده حساب'],
                        ['key' => 'deal_status', 'label' => 'وضعیت معامله'],
                        ['key' => 'pipeline_stage', 'label' => 'مرحله کاریز'],
                        ['key' => 'contact_id', 'label' => 'شناسه لید'],
                        ['key' => 'contact_name', 'label' => 'نام لید'],
                        ['key' => 'contact_mobile', 'label' => 'شماره تماس لید'],
                        ['key' => 'owner_name', 'label' => 'مسئول'],
                    ],
                ],
                [
                    'id' => 'commission_lines',
                    'title' => 'گزارش ledger پورسانت',
                    'formats' => ['csv'],
                    'filters' => [
                        ['key' => 'date_from', 'label' => 'از تاریخ مؤثر', 'type' => 'date', 'placeholder' => '2026-06-01'],
                        ['key' => 'date_to', 'label' => 'تا تاریخ مؤثر', 'type' => 'date', 'placeholder' => '2026-06-30'],
                        ['key' => 'owner_didar_id', 'label' => 'دریافت‌کننده پورسانت', 'type' => 'select', 'options' => array_merge([['value' => '', 'label' => 'همه']], $owners)],
                        ['key' => 'beneficiary_type', 'label' => 'نوع پورسانت', 'type' => 'select', 'options' => [
                            ['value' => '', 'label' => 'همه'],
                            ['value' => 'DIRECT_PAYMENT_OWNER', 'label' => 'مستقیم'],
                            ['value' => 'SUPERVISOR_OF_PAYMENT_OWNER', 'label' => 'سرپرست'],
                            ['value' => 'MANAGER_OF_PAYMENT_OWNER', 'label' => 'مدیر'],
                            ['value' => 'MANAGER_OF_SUPERVISOR_COMMISSION', 'label' => 'مدیر از پورسانت سرپرست'],
                            ['value' => 'CLAWBACK', 'label' => 'Clawback'],
                        ]],
                    ],
                    'columns' => [
                        ['key' => 'effective_date', 'label' => 'تاریخ مؤثر'],
                        ['key' => 'billing_month', 'label' => 'ماه'],
                        ['key' => 'beneficiary_name', 'label' => 'دریافت‌کننده'],
                        ['key' => 'beneficiary_type', 'label' => 'نوع دریافت‌کننده'],
                        ['key' => 'payment_owner_name', 'label' => 'مسئول پرداخت'],
                        ['key' => 'deal_didar_id', 'label' => 'شناسه معامله'],
                        ['key' => 'payment_record_id', 'label' => 'شناسه پرداخت ledger'],
                        ['key' => 'product_name', 'label' => 'محصول'],
                        ['key' => 'rule_title', 'label' => 'قانون'],
                        ['key' => 'payment_amount_snapshot', 'label' => 'مبلغ پرداخت'],
                        ['key' => 'applied_rate_percent', 'label' => 'نرخ'],
                        ['key' => 'calculated_commission', 'label' => 'مبلغ پورسانت'],
                        ['key' => 'status', 'label' => 'وضعیت'],
                        ['key' => 'reversal_of_line_id', 'label' => 'برگشت از line'],
                    ],
                ],
            ],
        ];
    }

    public function export(string $dataset, array $columns, array $filters, string $format): array
    {
        $format = strtolower($format);
        if (!in_array($format, ['csv', 'xlsx'], true)) {
            throw new \Exception('فرمت خروجی نامعتبر است');
        }
        $columns = $this->uniqueColumnKeys($columns);

        $includeRawJson = !empty($filters['include_raw_json']);

        if ($dataset === 'leads') {
            return $this->exportLeads($columns, $filters, $format, $includeRawJson);
        }
        if ($dataset === 'deals') {
            return $this->exportDeals($columns, $filters, $format, $includeRawJson);
        }
        if ($dataset === 'activities') {
            return $this->exportActivities($columns, $filters, $format, $includeRawJson);
        }
        if ($dataset === 'payments') {
            return $this->exportPayments($columns, $filters, $format, 'payments');
        }
        if ($dataset === 'check_payments') {
            return $this->exportPayments($columns, $filters, $format, 'check_payments', ['check']);
        }
        if ($dataset === 'commission_lines') {
            return $this->exportCommissionLines($columns, $filters, $format);
        }

        throw new \Exception('نوع خروجی نامعتبر است');
    }

    private function uniqueColumnKeys(array $columns): array
    {
        $out = [];
        $seen = [];
        foreach ($columns as $column) {
            if (!is_string($column)) {
                continue;
            }
            $key = trim($column);
            if ($key === '' || isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $out[] = $key;
        }
        return $out;
    }

    private function sortColumnsForDataset(string $datasetId, array $columns): array
    {
        $columns = $this->uniqueColumnKeys($columns);
        $preferred = [
            'deals' => [
                'register_time_jalali', 'register_time', 'contact_name', 'customer_code', 'contact_mobile', 'deal_id', 'deal_internal_id', 'title', 'invoice_code',
                'status', 'pipeline_stage', 'owner_name', 'products', 'service_cost', 'discount_amount', 'payable_amount', 'payment_amount', 'outstanding_balance',
                'payment_method', 'payment_description', 'won_time', 'lost_time',
            ],
            'payments' => [
                'payment_date_persian', 'payment_date', 'contact_name', 'customer_code', 'contact_mobile', 'deal_id', 'deal_title', 'invoice_code', 'owner_name',
                'amount', 'outstanding_balance', 'payment_status', 'method', 'payment_type', 'card_last4', 'destination_bank', 'destination_card_number',
                'description', 'created_at',
            ],
            'check_payments' => [
                'payment_date_persian', 'payment_date', 'check_date', 'contact_name', 'customer_code', 'contact_mobile', 'deal_id', 'deal_title', 'invoice_code', 'owner_name',
                'amount', 'outstanding_balance', 'payment_status', 'method', 'check_bank_name', 'check_number', 'check_owner_name', 'description', 'created_at',
            ],
            'commission_lines' => [
                'effective_date', 'billing_month', 'beneficiary_name', 'beneficiary_type', 'payment_owner_name', 'deal_didar_id', 'payment_record_id',
                'product_name', 'rule_title', 'payment_amount_snapshot', 'applied_rate_percent', 'calculated_commission', 'status',
            ],
        ];
        $order = $preferred[$datasetId] ?? [];
        if (empty($order)) {
            return $columns;
        }
        $rank = array_flip($order);
        usort($columns, static function ($left, $right) use ($rank) {
            $l = $rank[$left] ?? 10000;
            $r = $rank[$right] ?? 10000;
            if ($l !== $r) {
                return $l <=> $r;
            }
            return strcmp($left, $right);
        });
        return $columns;
    }

    private function ensureMandatoryCustomerCodeColumn(string $datasetId, array $columns): array
    {
        $enforcedDatasets = ['leads', 'deals', 'payments', 'check_payments'];
        if (!in_array($datasetId, $enforcedDatasets, true)) {
            return $columns;
        }
        $columns = $this->uniqueColumnKeys($columns);
        if (!in_array('customer_code', $columns, true)) {
            $columns[] = 'customer_code';
        }
        return $columns;
    }

    private function getDatasetLabelMap(string $datasetId): array
    {
        try {
            $catalog = $this->getCatalog();
            $datasets = $catalog['datasets'] ?? [];
            foreach ($datasets as $d) {
                if (($d['id'] ?? '') !== $datasetId) continue;
                $map = [];
                foreach (($d['columns'] ?? []) as $c) {
                    $key = $c['key'] ?? null;
                    if (!$key) continue;
                    $map[$key] = $c['label'] ?? $key;
                }
                return $map;
            }
        } catch (\Throwable $e) {
            Logger::logWarning('Failed to build label map for export', ['dataset' => $datasetId, 'error' => $e->getMessage()]);
        }
        return [];
    }

    private function getOwnerOptions(): array
    {
        $pdo = $this->db->getPdo();
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $nameExpr = $hasDisplayName ? "COALESCE(display_name, '')" : "COALESCE(CONCAT(first_name,' ',last_name),'')";
        $stmt = $pdo->query("SELECT didar_user_id, $nameExpr AS name FROM users WHERE is_active = 1 ORDER BY name ASC");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $r) {
            $out[] = [
                'value' => $r['didar_user_id'] ?? '',
                'label' => ($r['name'] ?? '') !== '' ? $r['name'] : ($r['didar_user_id'] ?? ''),
            ];
        }
        return $out;
    }

    private function normalizePersianDateForCompare(string $date): string
    {
        $d = trim($date);
        if ($d === '') return '';
        $d = strtr($d, [
            '۰' => '0', '۱' => '1', '۲' => '2', '۳' => '3', '۴' => '4',
            '۵' => '5', '۶' => '6', '۷' => '7', '۸' => '8', '۹' => '9',
            '٠' => '0', '١' => '1', '٢' => '2', '٣' => '3', '٤' => '4',
            '٥' => '5', '٦' => '6', '٧' => '7', '٨' => '8', '٩' => '9',
        ]);
        $d = str_replace('-', '/', $d);
        $parts = explode('/', $d);
        if (count($parts) < 3) return '';
        $y = preg_replace('/\\D+/', '', $parts[0] ?? '');
        $m = preg_replace('/\\D+/', '', $parts[1] ?? '');
        $day = preg_replace('/\\D+/', '', $parts[2] ?? '');
        if ($y === '' || $m === '' || $day === '') return '';
        $y = str_pad(substr($y, 0, 4), 4, '0', STR_PAD_LEFT);
        $m = str_pad(substr($m, 0, 2), 2, '0', STR_PAD_LEFT);
        $day = str_pad(substr($day, 0, 2), 2, '0', STR_PAD_LEFT);
        return $y . '/' . $m . '/' . $day;
    }

    private function normalizeExportValue($value): string
    {
        $str = trim((string)$value);
        if ($str === '__other__') {
            return '';
        }
        return $str;
    }

    private function isFormattedExportNumericField(string $field): bool
    {
        return in_array($field, [
            'service_cost',
            'payable_amount',
            'outstanding_balance',
            'payment_amount',
            'discount_amount',
            'refund_amount',
            'amount',
            'currency_amount',
            'payment_amount_snapshot',
            'commission_base_amount',
            'applied_rate_percent',
            'calculated_commission',
        ], true);
    }

    private function formatExportNumber($value): string
    {
        if ($value === null || $value === '') {
            return '';
        }
        $normalized = preg_replace('/[^\d\.\-]/u', '', (string)$value);
        if ($normalized === '' || $normalized === '-' || $normalized === '.' || $normalized === '-.') {
            return (string)$value;
        }
        $number = (float)$normalized;
        if (abs($number - round($number)) < 0.0001) {
            return number_format((float)round($number), 0, '.', ',');
        }
        return number_format($number, 2, '.', ',');
    }

    private function writeCsv(string $filename, array $headers, \Generator $rows): void
    {
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
        header('Pragma: no-cache');

        $out = fopen('php://output', 'w');
        // UTF-8 BOM for Excel
        fwrite($out, "\xEF\xBB\xBF");
        fputcsv($out, $headers);
        foreach ($rows as $row) {
            fputcsv($out, $row);
        }
        fclose($out);
    }

    private function appendSummaryRowForDataset(string $datasetId, array $columns, array &$rows): void
    {
        if (empty($rows) || empty($columns)) {
            return;
        }

        $numericFields = [
            'service_cost',
            'payable_amount',
            'outstanding_balance',
            'payment_amount',
            'discount_amount',
            'refund_amount',
            'amount',
            'currency_amount',
            'duration',
        ];
        $countLikeFields = [
            'deal_id',
            'payment_date',
            'payment_date_persian',
            'transaction_id',
            'invoice_code',
        ];

        $summary = array_fill(0, count($columns), '');
        $summary[0] = 'جمع / تحلیل';
        $totals = [];
        foreach ($columns as $index => $column) {
            if (in_array($column, $numericFields, true)) {
                $sum = 0.0;
                foreach ($rows as $row) {
                    $value = $row[$index] ?? '';
                    $normalized = preg_replace('/[^\d\.\-]/u', '', (string)$value);
                    if ($normalized === '' || $normalized === '-' || $normalized === '.' || $normalized === '-.') {
                        continue;
                    }
                    $sum += (float)$normalized;
                }
                $summary[$index] = $sum != 0.0 ? $this->formatExportNumber($sum) : '';
                $totals[$column] = $sum;
                continue;
            }

            if (in_array($column, $countLikeFields, true)) {
                $count = 0;
                foreach ($rows as $row) {
                    $value = trim((string)($row[$index] ?? ''));
                    if ($value !== '') {
                        $count++;
                    }
                }
                $summary[$index] = 'تعداد: ' . $count;
            }
        }

        $notes = [];
        $rowCount = count($rows);
        if ($datasetId === 'deals') {
            $dealCount = $rowCount;
            $payable = (float)($totals['payable_amount'] ?? 0);
            $paid = (float)($totals['payment_amount'] ?? 0);
            $outstanding = (float)($totals['outstanding_balance'] ?? 0);
            $avgContract = $dealCount > 0 ? ($payable / $dealCount) : 0;
            $collectionRatio = $payable > 0 ? round(($paid / $payable) * 100, 2) : 0;
            $notes = [
                'تعداد معاملات: ' . $dealCount,
                'میانگین مبلغ قرارداد: ' . $this->formatExportNumber(round($avgContract, 2)),
                'نسبت وصول: ' . $collectionRatio . '%',
                'جمع مانده حساب: ' . $this->formatExportNumber(round($outstanding, 2)),
            ];
        } elseif ($datasetId === 'payments' || $datasetId === 'check_payments') {
            $paymentCount = $rowCount;
            $amount = (float)($totals['amount'] ?? 0);
            $outstanding = (float)($totals['outstanding_balance'] ?? 0);
            $avgPayment = $paymentCount > 0 ? ($amount / $paymentCount) : 0;
            $notes = [
                'تعداد پرداخت: ' . $paymentCount,
                'میانگین مبلغ پرداخت: ' . $this->formatExportNumber(round($avgPayment, 2)),
                'جمع مبالغ پرداخت: ' . $this->formatExportNumber(round($amount, 2)),
                'جمع مانده حساب: ' . $this->formatExportNumber(round($outstanding, 2)),
            ];
        } elseif ($datasetId === 'activities') {
            $activityCount = $rowCount;
            $duration = (float)($totals['duration'] ?? 0);
            $avgDuration = $activityCount > 0 ? ($duration / $activityCount) : 0;
            $callCount = 0;
            $supportCount = 0;
            $courseCount = 0;
            foreach ($rows as $row) {
                $typeValue = '';
                foreach ($columns as $index => $column) {
                    if ($column === 'activity_type_title') {
                        $typeValue = trim((string)($row[$index] ?? ''));
                        break;
                    }
                }
                if ($typeValue === '') {
                    continue;
                }
                if (mb_stripos($typeValue, 'تماس') !== false) {
                    $callCount++;
                }
                if (mb_stripos($typeValue, 'پشتیبانی') !== false) {
                    $supportCount++;
                }
                if (mb_stripos($typeValue, 'دوره') !== false || mb_stripos($typeValue, 'برگزاری') !== false) {
                    $courseCount++;
                }
            }
            $notes = [
                'تعداد فعالیت: ' . $activityCount,
                'مجموع مدت: ' . round($duration, 2) . ' دقیقه',
                'میانگین مدت: ' . round($avgDuration, 2) . ' دقیقه',
                'تعداد تماس: ' . $callCount,
                'تعداد پشتیبانی: ' . $supportCount,
                'تعداد برگزاری دوره: ' . $courseCount,
            ];
        }

        if (!empty($notes)) {
            $noteIndex = 1;
            foreach ($notes as $note) {
                while ($noteIndex < count($summary) && trim((string)$summary[$noteIndex]) !== '') {
                    $noteIndex++;
                }
                if ($noteIndex >= count($summary)) {
                    break;
                }
                $summary[$noteIndex] = $note;
                $noteIndex++;
            }
        }

        $rows[] = $summary;
    }

    private function writeXlsx(string $filename, array $headers, array $rows): void
    {
        if (!class_exists('\\PhpOffice\\PhpSpreadsheet\\Spreadsheet')) {
            throw new \Exception('کتابخانه Excel نصب نیست');
        }
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray($headers, null, 'A1');
        if (!empty($rows)) {
            $sheet->fromArray($rows, null, 'A2');
        }

        $columnCount = count($headers);
        $lastColumnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(max($columnCount, 1));
        $lastRow = count($rows) + 1;

        $sheet->getStyle("A1:{$lastColumnLetter}1")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['rgb' => '1D4ED8'],
            ],
        ]);
        $sheet->freezePane('A2');
        $sheet->setAutoFilter("A1:{$lastColumnLetter}1");

        for ($row = 2; $row <= $lastRow; $row++) {
            $fill = ($row % 2 === 0) ? 'EFF6FF' : 'FFFFFF';
            $sheet->getStyle("A{$row}:{$lastColumnLetter}{$row}")->applyFromArray([
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => $fill],
                ],
            ]);
        }

        if ($lastRow >= 2) {
            $sheet->getStyle("A{$lastRow}:{$lastColumnLetter}{$lastRow}")->applyFromArray([
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => 'DBEAFE'],
                ],
            ]);
        }

        for ($col = 1; $col <= $columnCount; $col++) {
            $letter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
            $sheet->getColumnDimension($letter)->setAutoSize(true);
        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
        header('Pragma: no-cache');

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('php://output');
    }

    private function boolToFa($value): string
    {
        if ($value === null || $value === '') {
            return '';
        }
        return ((string)$value === '1' || $value === 1 || $value === true) ? 'بله' : 'خیر';
    }

    private function leadSaleStatusToFa($value): string
    {
        if ((string)$value === '1') return 'موفق';
        if ((string)$value === '0') return 'ناموفق';
        return 'نامشخص';
    }

    private function dealStatusToFa($value): string
    {
        $status = strtolower(trim((string)$value));
        if ($status === 'won') return 'موفق';
        if ($status === 'lost') return 'ناموفق';
        if ($status === 'pending') return 'جاری';
        return (string)$value;
    }

    private function createdViaToFa($value): string
    {
        $via = strtolower(trim((string)$value));
        if ($via === 'manual') return 'دستی';
        if ($via === 'excel') return 'اکسل';
        if ($via === 'legacy') return 'قدیمی';
        if ($via === '') return '';
        return (string)$value;
    }

    private function activityDoneToFa($value): string
    {
        return ((string)$value === '1' || $value === 1 || $value === true) ? 'انجام شده' : 'انجام نشده';
    }

    private function directionToFa($value): string
    {
        $v = strtolower(trim((string)$value));
        if ($v === 'incoming') return 'ورودی';
        if ($v === 'outgoing') return 'خروجی';
        return (string)$value;
    }

    private function currencyToFa($value): string
    {
        $raw = strtoupper(trim((string)$value));
        if ($raw === 'IRR') return 'ریال';
        if ($raw === 'USD') return 'دلار';
        if ($raw === 'EUR') return 'یورو';
        if ($raw === 'GBP') return 'پوند';
        return (string)$value;
    }

    private function virtualStageToFa($value): string
    {
        $raw = trim((string)$value);
        if ($raw === '') {
            return '';
        }
        $key = strtolower($raw);
        return $this->virtualStageMap[$key] ?? $raw;
    }

    private function pipelineStageToFa($value): string
    {
        $raw = trim((string)$value);
        if ($raw === '') {
            return '';
        }
        $mapped = $this->mapByLookup('pipeline_stages', $raw);
        if ($mapped !== $raw) {
            return $mapped;
        }
        return $this->virtualStageToFa($raw);
    }

    private function normalizeLookupKey(string $value): string
    {
        $v = trim($value);
        if ($v === '') {
            return '';
        }
        $v = str_replace([' ', '-', '/', '\\'], '_', $v);
        return strtolower($v);
    }

    private function paymentMethodToFa($value): string
    {
        $raw = trim((string)$value);
        if ($raw === '') {
            return '';
        }

        $mapped = $this->mapByLookup('payment_methods', $raw);
        if ($mapped !== $raw) {
            return $mapped;
        }

        $fallback = [
            'card_to_card' => 'کارت به کارت',
            'card_reader' => 'کارت‌خوان',
            'sheba' => 'شِبا/پُل',
            'shaba' => 'شِبا/پُل',
            'sheba_pol' => 'شِبا/پُل',
            'shaba_pol' => 'شِبا/پُل',
            'online' => 'آنلاین',
            'cash' => 'نقدی',
            'installment' => 'اقساطی',
            'check' => 'چک',
            'cheque' => 'چک',
            'other' => 'سایر',
        ];
        $key = $this->normalizeLookupKey($raw);
        return $fallback[$key] ?? $raw;
    }

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

    private function firstNonEmptyValue(array $values): string
    {
        foreach ($values as $value) {
            if ($value === null) {
                continue;
            }
            $trimmed = trim((string)$value);
            if ($trimmed !== '') {
                return $trimmed;
            }
        }
        return '';
    }

    private function mapByLookup(string $groupCode, $value): string
    {
        $raw = trim((string)$value);
        if ($raw === '') {
            return '';
        }
        if (!isset($this->lookupCache[$groupCode])) {
            $this->lookupCache[$groupCode] = [];
            try {
                $pdo = $this->db->getPdo();
                $stmt = $pdo->prepare("
                    SELECT value, code, title
                    FROM lookup_items li
                    JOIN lookup_groups lg ON lg.id = li.group_id
                    WHERE lg.code = ? AND li.is_active = 1
                ");
                $stmt->execute([$groupCode]);
                $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                foreach ($rows as $row) {
                    $title = (string)($row['title'] ?? '');
                    $val = trim((string)($row['value'] ?? ''));
                    $code = trim((string)($row['code'] ?? ''));
                    if ($val !== '') {
                        $this->lookupCache[$groupCode][$val] = $title;
                        $this->lookupCache[$groupCode][$this->normalizeLookupKey($val)] = $title;
                    }
                    if ($code !== '') {
                        $this->lookupCache[$groupCode][$code] = $title;
                        $this->lookupCache[$groupCode][$this->normalizeLookupKey($code)] = $title;
                    }
                }
            } catch (\Throwable $e) {
                $this->lookupCache[$groupCode] = [];
            }
        }
        return $this->lookupCache[$groupCode][$raw]
            ?? $this->lookupCache[$groupCode][$this->normalizeLookupKey($raw)]
            ?? $raw;
    }

    private function gregorianToJalali(int $gy, int $gm, int $gd): array
    {
        $g_d_m = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334];
        $jy = ($gy <= 1600) ? 0 : 979;
        $gy -= ($gy <= 1600) ? 621 : 1600;
        $gy2 = ($gm > 2) ? ($gy + 1) : $gy;
        $days = (365 * $gy) + intdiv($gy2 + 3, 4) - intdiv($gy2 + 99, 100) + intdiv($gy2 + 399, 400) - 80 + $gd + $g_d_m[$gm - 1];
        $jy += 33 * intdiv($days, 12053);
        $days %= 12053;
        $jy += 4 * intdiv($days, 1461);
        $days %= 1461;
        if ($days > 365) {
            $jy += intdiv($days - 1, 365);
            $days = ($days - 1) % 365;
        }
        $jm = ($days < 186) ? 1 + intdiv($days, 31) : 7 + intdiv($days - 186, 30);
        $jd = 1 + (($days < 186) ? ($days % 31) : (($days - 186) % 30));
        return [$jy, $jm, $jd];
    }

    private function toJalaliDateTime(string $value, bool $withTime = false): string
    {
        $value = trim($value);
        if ($value === '' || $value === '0000-00-00 00:00:00' || $value === '0000-00-00') {
            return '';
        }
        try {
            $dt = new \DateTime($value);
            [$jy, $jm, $jd] = $this->gregorianToJalali(
                (int)$dt->format('Y'),
                (int)$dt->format('m'),
                (int)$dt->format('d')
            );
            $datePart = sprintf('%04d/%02d/%02d', $jy, $jm, $jd);
            if (!$withTime) {
                return $datePart;
            }
            return $datePart . ' ' . $dt->format('H:i');
        } catch (\Throwable $e) {
            return $value;
        }
    }

    private function exportLeads(array $columns, array $filters, string $format, bool $includeRawJson): array
    {
        $pdo = $this->db->getPdo();
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $ownerNameExpr = $hasDisplayName ? "COALESCE(u1.display_name,'')" : "COALESCE(CONCAT(u1.first_name,' ',u1.last_name),'')";
        $prevOwnerNameExpr = $hasDisplayName ? "COALESCE(u2.display_name,'')" : "COALESCE(CONCAT(u2.first_name,' ',u2.last_name),'')";

        $selectMap = [
            'lead_id' => 'p.didar_contact_id',
            'lead_code' => 'p.code',
            'customer_code' => 'p.customer_code',
            'first_name' => 'p.first_name',
            'last_name' => 'p.last_name',
            'mobile_phone' => 'p.mobile_phone',
            'secondary_mobile_phone' => 'p.secondary_mobile_phone',
            'mobile_phone_3' => 'p.mobile_phone_3',
            'mobile_phone_4' => 'p.mobile_phone_4',
            'work_phone' => 'p.work_phone',
            'email' => 'p.email',
            'city' => 'p.city',
            'job_title' => 'p.job_title',
            'job_description' => 'p.job_description',
            'acquaintance_source' => 'p.acquaintance_source',
            'acquaintance_detail' => 'p.acquaintance_detail',
            'acquaintance_duration' => 'p.acquaintance_duration',
            'content_topic' => 'p.content_topic',
            'national_id' => 'p.national_id',
            'has_previous_purchase' => 'p.has_previous_purchase',
            'sale_status' => 'p.sale_status',
            'register_time_jalali' => 'p.register_time_jalali',
            'register_time' => 'p.register_time',
            'owner_name' => $ownerNameExpr,
            'previous_owner_name' => $prevOwnerNameExpr,
            'kariz_active_stage' => "(SELECT stage_name FROM virtual_stages vs WHERE vs.contact_didar_id = p.didar_contact_id AND vs.stage_name NOT IN ('with_purchase','without_purchase','refer_crm') ORDER BY vs.entered_at DESC LIMIT 1)",
            'kariz_special_stage' => "(SELECT stage_name FROM virtual_stages vs WHERE vs.contact_didar_id = p.didar_contact_id AND vs.stage_name IN ('with_purchase','without_purchase','refer_crm') ORDER BY vs.entered_at DESC LIMIT 1)",
            'kariz_most_recent_stage' => "(SELECT stage_name FROM virtual_stages vs WHERE vs.contact_didar_id = p.didar_contact_id ORDER BY vs.entered_at DESC LIMIT 1)",
            'customer_products' => 'p.customer_products',
            'requested_services' => 'p.requested_services',
            'created_by_name' => 'p.created_by_name',
            'created_via' => 'p.created_via',
            'deal_count' => "(SELECT COUNT(*) FROM deals d WHERE d.contact_didar_id = p.didar_contact_id)",
            'won_deal_count' => "(SELECT COUNT(*) FROM deals d WHERE d.contact_didar_id = p.didar_contact_id AND LOWER(d.status) = 'won')",
            'lost_deal_count' => "(SELECT COUNT(*) FROM deals d WHERE d.contact_didar_id = p.didar_contact_id AND LOWER(d.status) = 'lost')",
            'current_deal_count' => "(SELECT COUNT(*) FROM deals d WHERE d.contact_didar_id = p.didar_contact_id AND (d.status IS NULL OR d.status = '' OR LOWER(d.status) = 'pending'))",
            'raw_json' => 'p.raw_json',
        ];

        if (!$includeRawJson) {
            unset($selectMap['raw_json']);
        }

        $columns = array_values(array_filter($columns, fn($c) => isset($selectMap[$c])));
        $columns = $this->ensureMandatoryCustomerCodeColumn('leads', $columns);
        if (empty($columns)) {
            $columns = ['lead_id', 'first_name', 'last_name', 'mobile_phone', 'owner_name', 'sale_status', 'kariz_most_recent_stage', 'register_time_jalali'];
        }

        $selectParts = array_map(fn($c) => $selectMap[$c] . " AS `$c`", $columns);
        $selectParts[] = "p.register_time AS `__register_time`";
        $selectParts[] = "(SELECT d.pipeline_stage FROM deals d WHERE d.contact_didar_id = p.didar_contact_id ORDER BY d.register_time DESC, d.created_at DESC LIMIT 1) AS `__latest_deal_stage`";
        $sql = "SELECT " . implode(', ', $selectParts) . "
                FROM persons p
                LEFT JOIN users u1 ON p.owner_didar_id = u1.didar_user_id
                LEFT JOIN users u2 ON p.previous_owner_id = u2.didar_user_id
                WHERE p.didar_contact_id IS NOT NULL";

        $where = [];
        $params = [];
        $this->applyOwnerWhere($where, $params, $filters, 'p.owner_didar_id');
        if (!empty($filters['sale_status']) && $filters['sale_status'] !== 'all') {
            if ($filters['sale_status'] === 'success') $where[] = "p.sale_status = 1";
            if ($filters['sale_status'] === 'failed') $where[] = "p.sale_status = 0";
        }
        $registerFromGregorian = trim((string)($filters['register_date_from'] ?? ''));
        $registerToGregorian = trim((string)($filters['register_date_to'] ?? ''));
        if ($registerFromGregorian !== '') {
            $where[] = "p.register_time >= ?";
            $params[] = $registerFromGregorian;
        } elseif (!empty($filters['register_date_from_jalali'])) {
            $where[] = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') >= ?";
            $params[] = $this->normalizePersianDateForCompare($filters['register_date_from_jalali']);
        }
        if ($registerToGregorian !== '') {
            $where[] = "p.register_time <= ?";
            $params[] = $registerToGregorian;
        } elseif (!empty($filters['register_date_to_jalali'])) {
            $where[] = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') <= ?";
            $params[] = $this->normalizePersianDateForCompare($filters['register_date_to_jalali']);
        }
        if (!empty($where)) {
            $sql .= " AND " . implode(' AND ', $where);
        }
        $sql .= " ORDER BY p.register_time DESC, p.created_at DESC";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        $labelMap = $this->getDatasetLabelMap('leads');
        $headers = array_map(fn($c) => $labelMap[$c] ?? $c, $columns);
        $rowsGenerator = (function () use ($stmt, $columns) {
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $out = [];
                foreach ($columns as $c) {
                    $value = $row[$c] ?? '';
                    if ($c === 'sale_status') {
                        $value = $this->leadSaleStatusToFa($value);
                    } elseif ($c === 'acquaintance_source') {
                        $value = $this->mapByLookup('lead_sources', $value);
                    } elseif ($c === 'acquaintance_detail') {
                        $value = $this->mapByLookup('lead_source_details', $value);
                    } elseif ($c === 'content_topic') {
                        $value = $this->mapByLookup('content_topics', $value);
                    } elseif ($c === 'created_via') {
                        $value = $this->createdViaToFa($value);
                    } elseif ($c === 'has_previous_purchase') {
                        $value = $this->boolToFa($value);
                    } elseif ($c === 'register_time_jalali') {
                        if ((string)$value === '') {
                            $value = $this->toJalaliDateTime((string)($row['__register_time'] ?? ''), false);
                        }
                    } elseif ($c === 'register_time') {
                        $value = $this->toJalaliDateTime((string)$value, true);
                    } elseif ($c === 'kariz_active_stage' || $c === 'kariz_most_recent_stage') {
                        $value = (string)$value;
                        $latestDealStage = (string)($row['__latest_deal_stage'] ?? '');
                        if ($value === 'deal_registered' && $latestDealStage !== '') {
                            $value = $latestDealStage;
                        }
                        $value = $this->pipelineStageToFa($value);
                    } elseif ($c === 'kariz_special_stage') {
                        $value = $this->virtualStageToFa($value);
                    }
                    $out[] = $value;
                }
                yield $out;
            }
        })();

        $filenameBase = 'leads_' . date('Ymd_His');
        if ($format === 'csv') {
            $this->writeCsv($filenameBase . '.csv', $headers, $rowsGenerator);
            return ['ok' => true];
        }

        // XLSX: cap to 5000 rows
        $rows = [];
        $i = 0;
        foreach ($rowsGenerator as $r) {
            $rows[] = $r;
            $i++;
            if ($i > 5000) {
                throw new \Exception('برای خروجی Excel، حداکثر 5000 ردیف مجاز است. لطفاً CSV بگیرید یا فیلتر دقیق‌تر بزنید.');
            }
        }
        $this->appendSummaryRowForDataset('leads', $columns, $rows);
        $this->writeXlsx($filenameBase . '.xlsx', $headers, $rows);
        return ['ok' => true];
    }

    private function exportDeals(array $columns, array $filters, string $format, bool $includeRawJson): array
    {
        $pdo = $this->db->getPdo();
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $hasDealInvoiceCode = $this->db->hasColumn('deals', 'invoice_code');
        $ownerNameExpr = $hasDisplayName ? "COALESCE(u.display_name,'')" : "COALESCE(CONCAT(u.first_name,' ',u.last_name),'')";

        $selectMap = [
            'deal_id' => 'd.didar_deal_id',
            'deal_internal_id' => 'd.id',
            'title' => 'd.title',
            'code' => 'd.code',
            'customer_code' => 'p.customer_code',
            'status' => 'd.status',
            'pipeline_stage' => 'd.pipeline_stage',
            'owner_name' => $ownerNameExpr,
            'contact_id' => 'd.contact_didar_id',
            'contact_name' => "TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, '')))",
            'contact_mobile' => 'p.mobile_phone',
            'acquaintance_source' => 'd.acquaintance_source',
            'acquaintance_detail' => 'd.acquaintance_detail',
            'content_topic' => 'd.content_topic',
            'products' => "(SELECT GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR '، ')
                           FROM deal_products dp
                           JOIN products pr ON pr.id = dp.product_id
                           WHERE dp.deal_id = d.id)",
            'service_cost' => 'd.service_cost',
            'invoice_code' => ($hasDealInvoiceCode ? 'd.invoice_code' : "''"),
            'payable_amount' => 'd.payable_amount',
            'outstanding_balance' => 'GREATEST(COALESCE(d.payable_amount, 0) - COALESCE(d.payment_amount, 0), 0)',
            'payment_amount' => 'd.payment_amount',
            'payment_method' => 'd.payment_method',
            'payment_description' => 'd.payment_description',
            'payments' => 'd.payments',
            'is_paid' => 'd.is_paid',
            'payment_short_link' => 'd.payment_short_link',
            'has_discount' => 'd.has_discount',
            'discount_type' => 'd.discount_type',
            'discount_amount' => 'd.discount_amount',
            'refund_amount' => 'd.refund_amount',
            'refund_description' => 'd.refund_description',
            'failure_reason_code' => 'd.failure_reason_code',
            'failure_reason_text' => 'd.failure_reason_text',
            'failure_reason_description' => 'd.failure_reason_description',
            'register_time_jalali' => 'd.register_time',
            'register_time' => 'd.register_time',
            'won_time' => 'd.won_time',
            'lost_time' => 'd.lost_time',
            'created_by_name' => 'd.created_by_name',
            'created_via' => 'd.created_via',
            'raw_json' => 'd.raw_json',
        ];
        if (!$includeRawJson) unset($selectMap['raw_json']);

        $columns = $this->sortColumnsForDataset('deals', $this->ensureMandatoryCustomerCodeColumn('deals', array_values(array_filter($columns, fn($c) => isset($selectMap[$c])))));
        if (empty($columns)) {
            $columns = ['deal_id', 'title', 'status', 'pipeline_stage', 'owner_name', 'contact_name', 'contact_mobile', 'invoice_code', 'payment_amount', 'register_time_jalali'];
        }

        $selectParts = array_map(fn($c) => $selectMap[$c] . " AS `$c`", $columns);
        $sql = "SELECT " . implode(', ', $selectParts) . "
                FROM deals d
                LEFT JOIN persons p ON (
                    (d.person_id IS NOT NULL AND d.person_id = p.id)
                    OR
                    (d.person_id IS NULL AND d.contact_didar_id = p.didar_contact_id COLLATE utf8mb4_general_ci)
                )
                LEFT JOIN users u ON d.owner_didar_id = u.didar_user_id
                WHERE 1=1";
        $where = [];
        $params = [];
        $this->applyOwnerWhere($where, $params, $filters, 'd.owner_didar_id');
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $where[] = "d.status = ?";
            $params[] = $filters['status'];
        }
        if (!empty($filters['pipeline_stage']) && $filters['pipeline_stage'] !== 'all') {
            $where[] = "d.pipeline_stage = ?";
            $params[] = $filters['pipeline_stage'];
        }
        if (!empty($filters['register_date_from'])) {
            $where[] = "d.register_time >= ?";
            $params[] = $filters['register_date_from'];
        }
        if (!empty($filters['register_date_to'])) {
            $where[] = "d.register_time <= ?";
            $params[] = $filters['register_date_to'];
        }
        
        // Handle Legacy Deals filter
        $includeLegacy = !empty($filters['include_legacy']);
        if (!$includeLegacy) {
            $where[] = "d.is_legacy_didar = 0";
        }

        if (!empty($where)) {
            $sql .= " AND " . implode(' AND ', $where);
        }
        $sql .= " ORDER BY d.register_time DESC, d.created_at DESC";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        $labelMap = $this->getDatasetLabelMap('deals');
        $headers = array_map(fn($c) => $labelMap[$c] ?? $c, $columns);
        $rowsGenerator = (function () use ($stmt, $columns) {
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $out = [];
                foreach ($columns as $c) {
                    $value = $row[$c] ?? '';
                    if ($c === 'status') {
                        $value = $this->dealStatusToFa($value);
                    } elseif ($c === 'pipeline_stage') {
                        $value = $this->pipelineStageToFa($value);
                    } elseif ($c === 'is_paid' || $c === 'has_discount') {
                        $value = $this->boolToFa($value);
                    } elseif ($c === 'payment_method') {
                        $value = $this->paymentMethodToFa($value);
                    } elseif ($c === 'acquaintance_source') {
                        $value = $this->mapByLookup('lead_sources', $value);
                    } elseif ($c === 'acquaintance_detail') {
                        $value = $this->mapByLookup('lead_source_details', $value);
                    } elseif ($c === 'content_topic') {
                        $value = $this->mapByLookup('content_topics', $value);
                    } elseif ($c === 'created_via') {
                        $value = $this->createdViaToFa($value);
                    } elseif ($c === 'discount_type') {
                        $value = $this->mapByLookup('discount_types', $value);
                    } elseif ($c === 'register_time_jalali') {
                        $value = $this->toJalaliDateTime((string)$value, false);
                    } elseif ($c === 'register_time' || $c === 'won_time' || $c === 'lost_time') {
                        $value = $this->toJalaliDateTime((string)$value, true);
                    } elseif ($this->isFormattedExportNumericField($c)) {
                        $value = $this->formatExportNumber($value);
                    }
                    $out[] = $value;
                }
                yield $out;
            }
        })();

        $filenameBase = 'deals_' . date('Ymd_His');
        if ($format === 'csv') {
            $rows = [];
            foreach ($rowsGenerator as $r) {
                $rows[] = $r;
            }
            $this->appendSummaryRowForDataset('deals', $columns, $rows);
            $this->writeCsv($filenameBase . '.csv', $headers, (function () use ($rows) { foreach ($rows as $r) yield $r; })());
            return ['ok' => true];
        }

        $rows = [];
        $i = 0;
        foreach ($rowsGenerator as $r) {
            $rows[] = $r;
            $i++;
            if ($i > 5000) {
                throw new \Exception('برای خروجی Excel، حداکثر 5000 ردیف مجاز است. لطفاً CSV بگیرید یا فیلتر دقیق‌تر بزنید.');
            }
        }
        $this->appendSummaryRowForDataset('deals', $columns, $rows);
        $this->writeXlsx($filenameBase . '.xlsx', $headers, $rows);
        return ['ok' => true];
    }

    private function exportActivities(array $columns, array $filters, string $format, bool $includeRawJson): array
    {
        $pdo = $this->db->getPdo();
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $ownerNameExpr = $hasDisplayName ? "COALESCE(u.display_name,'')" : "COALESCE(CONCAT(u.first_name,' ',u.last_name),'')";
        $dealOwnerNameExpr = $hasDisplayName ? "COALESCE(ud.display_name,'')" : "COALESCE(CONCAT(ud.first_name,' ',ud.last_name),'')";

        $selectMap = [
            'activity_id' => 'a.didar_activity_id',
            'activity_type_title' => 'a.activity_type_title',
            'title' => 'a.title',
            'note' => 'a.note',
            'result_note' => 'a.result_note',
            'failure_reason_code' => 'a.failure_reason_code',
            'failure_reason_description' => 'a.failure_reason_description',
            'is_done' => 'a.is_done',
            'due_date' => 'a.due_date',
            'done_date' => 'a.done_date',
            'duration' => 'a.duration',
            'direction' => 'a.direction',
            'stage' => 'a.stage',
            'owner_name' => $ownerNameExpr,
            'contact_id' => 'a.contact_didar_id',
            'contact_name' => "CONCAT(p.first_name,' ',p.last_name)",
            'deal_id' => 'a.deal_didar_id',
            'register_date' => 'a.register_date',
            'raw_json' => 'a.raw_json',
        ];
        if (!$includeRawJson) unset($selectMap['raw_json']);

        $columns = array_values(array_filter($columns, fn($c) => isset($selectMap[$c])));
        if (empty($columns)) {
            $columns = ['activity_id', 'activity_type_title', 'contact_name', 'deal_id', 'is_done', 'register_date'];
        }

        $selectParts = array_map(fn($c) => $selectMap[$c] . " AS `$c`", $columns);
        $selectParts[] = "a.raw_json AS `__raw_json`";
        $selectParts[] = "a.owner_didar_id AS `__owner_id`";
        $selectParts[] = "a.contact_didar_id AS `__contact_id`";
        $selectParts[] = "a.deal_didar_id AS `__deal_id`";
        $selectParts[] = "d.contact_didar_id AS `__deal_contact_id`";
        $selectParts[] = "d.pipeline_stage AS `__deal_pipeline_stage`";
        $selectParts[] = "COALESCE(CONCAT(pd.first_name,' ',pd.last_name),'') AS `__deal_contact_name`";
        $selectParts[] = "$dealOwnerNameExpr AS `__deal_owner_name`";
        $sql = "SELECT " . implode(', ', $selectParts) . "
                FROM activities a
                LEFT JOIN persons p ON a.contact_didar_id = p.didar_contact_id
                LEFT JOIN users u ON a.owner_didar_id = u.didar_user_id
                LEFT JOIN deals d ON a.deal_didar_id = d.didar_deal_id
                LEFT JOIN persons pd ON d.contact_didar_id = pd.didar_contact_id
                LEFT JOIN users ud ON d.owner_didar_id = ud.didar_user_id
                WHERE 1=1";
        $where = [];
        $params = [];
        $this->applyOwnerWhere($where, $params, $filters, 'a.owner_didar_id');
        if (isset($filters['is_done']) && $filters['is_done'] !== 'all' && $filters['is_done'] !== '') {
            $where[] = "a.is_done = ?";
            $params[] = (int)$filters['is_done'];
        }
        if (!empty($filters['register_date_from'])) {
            $where[] = "a.register_date >= ?";
            $params[] = $filters['register_date_from'];
        }
        if (!empty($filters['register_date_to'])) {
            $where[] = "a.register_date <= ?";
            $params[] = $filters['register_date_to'];
        }
        if (!empty($where)) $sql .= " AND " . implode(' AND ', $where);
        $sql .= " ORDER BY a.register_date DESC, a.created_at DESC";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        $labelMap = $this->getDatasetLabelMap('activities');
        $headers = array_map(fn($c) => $labelMap[$c] ?? $c, $columns);
        $rowsGenerator = (function () use ($stmt, $columns) {
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $raw = $this->decodeJsonAssoc($row['__raw_json'] ?? '');
                $rawResponse = (isset($raw['Response']) && is_array($raw['Response'])) ? $raw['Response'] : [];
                $out = [];
                foreach ($columns as $c) {
                    $value = $row[$c] ?? '';
                    if ($c === 'duration') {
                        $value = $this->firstNonEmptyValue([
                            $value,
                            $raw['duration'] ?? '',
                            $raw['Duration'] ?? '',
                            $rawResponse['duration'] ?? '',
                            $rawResponse['Duration'] ?? '',
                        ]);
                    } elseif ($c === 'direction') {
                        $value = $this->firstNonEmptyValue([
                            $value,
                            $raw['direction'] ?? '',
                            $raw['Direction'] ?? '',
                            $rawResponse['direction'] ?? '',
                            $rawResponse['Direction'] ?? '',
                        ]);
                    } elseif ($c === 'stage') {
                        $value = $this->firstNonEmptyValue([
                            $value,
                            $raw['stage'] ?? '',
                            $raw['pipeline_stage'] ?? '',
                            $raw['PipelineStageTitle'] ?? '',
                            $rawResponse['stage'] ?? '',
                            $rawResponse['pipeline_stage'] ?? '',
                            $row['__deal_pipeline_stage'] ?? '',
                        ]);
                    } elseif ($c === 'owner_name') {
                        $value = $this->firstNonEmptyValue([
                            $value,
                            $row['__deal_owner_name'] ?? '',
                            $raw['owner_name'] ?? '',
                            $rawResponse['owner_name'] ?? '',
                        ]);
                    } elseif ($c === 'contact_id') {
                        $value = $this->firstNonEmptyValue([
                            $value,
                            $row['__contact_id'] ?? '',
                            $row['__deal_contact_id'] ?? '',
                            $raw['contact_didar_id'] ?? '',
                            $raw['ContactId'] ?? '',
                            $rawResponse['contact_didar_id'] ?? '',
                            $rawResponse['ContactId'] ?? '',
                        ]);
                    } elseif ($c === 'contact_name') {
                        $value = $this->firstNonEmptyValue([
                            $value,
                            $row['__deal_contact_name'] ?? '',
                            $raw['contact_name'] ?? '',
                            $rawResponse['contact_name'] ?? '',
                        ]);
                    }

                    if ($c === 'is_done') {
                        $value = $this->activityDoneToFa($value);
                    } elseif ($c === 'direction') {
                        $value = $this->directionToFa($value);
                    } elseif ($c === 'stage') {
                        $value = $this->pipelineStageToFa($value);
                    } elseif ($c === 'register_date' || $c === 'due_date' || $c === 'done_date') {
                        $value = $this->toJalaliDateTime((string)$value, true);
                    }
                    $out[] = $value;
                }
                yield $out;
            }
        })();

        $filenameBase = 'activities_' . date('Ymd_His');
        if ($format === 'csv') {
            $this->writeCsv($filenameBase . '.csv', $headers, $rowsGenerator);
            return ['ok' => true];
        }

        $rows = [];
        $i = 0;
        foreach ($rowsGenerator as $r) {
            $rows[] = $r;
            $i++;
            if ($i > 5000) {
                throw new \Exception('برای خروجی Excel، حداکثر 5000 ردیف مجاز است. لطفاً CSV بگیرید یا فیلتر دقیق‌تر بزنید.');
            }
        }
        $this->appendSummaryRowForDataset('activities', $columns, $rows);
        $this->writeXlsx($filenameBase . '.xlsx', $headers, $rows);
        return ['ok' => true];
    }

    private function exportPayments(array $columns, array $filters, string $format, string $datasetId = 'payments', array $forcedMethods = []): array
    {
        $pdo = $this->db->getPdo();
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $hasDealInvoiceCode = $this->db->hasColumn('deals', 'invoice_code');
        $ownerNameExpr = $hasDisplayName ? "COALESCE(u.display_name,'')" : "COALESCE(CONCAT(u.first_name,' ',u.last_name),'')";

        $selectMap = [
            'payment_date_persian' => null,
            'payment_date' => null,
            'amount' => null,
            'method' => null,
            'card_last4' => null,
            'bank_destination' => null,
            'destination_card_number' => null,
            'card_reader_device' => null,
            'currency' => null,
            'currency_amount' => null,
            'check_bank_name' => null,
            'check_date' => null,
            'check_number' => null,
            'check_owner_name' => null,
            'description' => null,
            'invoice_code' => null,
            'deal_id' => null,
            'deal_title' => null,
            'products' => null,
            'service_cost' => null,
            'price_list_code' => null,
            'payable_amount' => null,
            'outstanding_balance' => null,
            'deal_status' => null,
            'pipeline_stage' => null,
            'contact_id' => null,
            'contact_name' => null,
            'contact_mobile' => null,
            'owner_name' => null,
            'customer_code' => null,
        ];
        $columns = array_values(array_filter($columns, fn($c) => array_key_exists($c, $selectMap)));
        $columns = $this->ensureMandatoryCustomerCodeColumn($datasetId, $columns);
        if (empty($columns)) {
            $columns = ['payment_date_persian', 'amount', 'method', 'card_last4', 'bank_destination', 'destination_card_number', 'description', 'deal_id', 'deal_title', 'deal_status', 'pipeline_stage', 'contact_name', 'owner_name'];
        }

        $sql = "SELECT
                    d.didar_deal_id AS deal_id,
                    d.title AS deal_title,
                    " . ($hasDealInvoiceCode ? "d.invoice_code" : "''") . " AS invoice_code,
                    d.service_cost AS service_cost,
                    d.price_list_code AS price_list_code,
                    d.payable_amount AS payable_amount,
                    d.payment_amount AS payment_amount,
                    d.status AS deal_status,
                    d.pipeline_stage AS pipeline_stage,
                    d.contact_didar_id AS contact_id,
                    TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, ''))) AS contact_name,
                    COALESCE(p.mobile_phone, '') AS contact_mobile,
                    COALESCE(p.customer_code, '') AS customer_code,
                    (SELECT GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR '، ')
                       FROM deal_products dp
                       JOIN products pr ON pr.id = dp.product_id
                      WHERE dp.deal_id = d.id) AS products,
                    $ownerNameExpr AS owner_name,
                    d.payments AS payments_json
                FROM deals d
                LEFT JOIN persons p ON (
                    (d.person_id IS NOT NULL AND d.person_id = p.id)
                    OR
                    (d.person_id IS NULL AND d.contact_didar_id = p.didar_contact_id COLLATE utf8mb4_general_ci)
                )
                LEFT JOIN users u ON d.owner_didar_id = u.didar_user_id
                WHERE d.payments IS NOT NULL AND d.payments <> ''";

        $where = [];
        $params = [];
        $this->applyOwnerWhere($where, $params, $filters, 'd.owner_didar_id');
        if (!empty($filters['deal_status']) && $filters['deal_status'] !== 'all') {
            $where[] = "d.status = ?";
            $params[] = $filters['deal_status'];
        }
        
        // Handle Legacy Deals filter
        $includeLegacy = !empty($filters['include_legacy']);
        if (!$includeLegacy) {
            $where[] = "d.is_legacy_didar = 0";
        }

        if (!empty($where)) $sql .= " AND " . implode(' AND ', $where);
        $sql .= " ORDER BY d.register_time DESC";

        $from = $this->normalizePersianDateForCompare((string)($filters['payment_date_from_persian'] ?? ''));
        $to = $this->normalizePersianDateForCompare((string)($filters['payment_date_to_persian'] ?? ''));

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        $forcedMethods = array_values(array_filter(array_map('strtolower', $forcedMethods)));
        $labelMap = $this->getDatasetLabelMap($datasetId);
        $headers = array_map(fn($c) => $labelMap[$c] ?? $c, $columns);
        $rowsGenerator = (function () use ($stmt, $columns, $from, $to, $forcedMethods) {
            while ($dealRow = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $payments = json_decode($dealRow['payments_json'] ?? '', true);
                if (!is_array($payments)) {
                    continue;
                }
                foreach ($payments as $p) {
                    $datePersian = trim((string)($p['date_persian'] ?? ''));
                    $datePersianKey = $datePersian !== '' ? $this->normalizePersianDateForCompare($datePersian) : '';
                    if ($from !== '' && ($datePersianKey === '' || $datePersianKey < $from)) {
                        continue;
                    }
                    if ($to !== '' && ($datePersianKey === '' || $datePersianKey > $to)) {
                        continue;
                    }
                    $methodCode = strtolower(trim((string)(($p['method'] ?? '') ?: ($p['payment_method'] ?? ''))));
                    if (!empty($forcedMethods) && !in_array($methodCode, $forcedMethods, true)) {
                        continue;
                    }
                    $rawBank = (string)(($p['destination_bank'] ?? '') ?: ($p['bank_destination'] ?? ''));
                    if ($rawBank === '__other__') {
                        $rawBank = (string)($p['destination_bank_other'] ?? '');
                    }
                    if ($rawBank === '') {
                        $rawBank = (string)($p['account_owner_name'] ?? '');
                    }
                    $rawBank = $this->normalizeExportValue($rawBank);
                    $destinationCardNumber = (string)(
                        (($p['destination_card_number'] ?? '') === '__other__')
                            ? (($p['destination_card_number_other'] ?? ''))
                            : (($p['destination_card_number'] ?? '') ?: ($p['destination_card_number_other'] ?? ''))
                    );
                    $destinationCardNumber = preg_replace('/\D+/', '', (string)$destinationCardNumber);
                    if (strlen($destinationCardNumber) > 4) {
                        $destinationCardNumber = substr($destinationCardNumber, -4);
                    }
                    $destinationCardNumber = $this->normalizeExportValue($destinationCardNumber);
                    $flat = [
                        'payment_date_persian' => $datePersian,
                        'payment_date' => $this->toJalaliDateTime((string)($p['date'] ?? ''), true),
                        'amount' => (string)($p['amount'] ?? ''),
                        'method' => $this->paymentMethodToFa($methodCode),
                        'card_last4' => (string)($p['card_last4'] ?? ''),
                        'bank_destination' => $this->mapByLookup('payment_destination_banks', $rawBank),
                        'destination_card_number' => $destinationCardNumber,
                        'card_reader_device' => ((string)($p['card_reader_device'] ?? '')) === 'academy'
                            ? 'آکادمی'
                            : ((((string)($p['card_reader_device'] ?? '')) === 'showroom') ? 'نمایشگاه' : (string)($p['card_reader_device'] ?? '')),
                        'currency' => $this->currencyToFa((string)(($p['currency'] ?? '') === '__other__' ? ($p['currency_other'] ?? '') : ($p['currency'] ?? ''))),
                        'currency_amount' => (string)($p['currency_amount'] ?? ''),
                        'check_bank_name' => (string)($p['check_bank_name'] ?? ''),
                        'check_date' => (string)($p['check_date'] ?? ''),
                        'check_number' => (string)($p['check_number'] ?? ''),
                        'check_owner_name' => (string)($p['check_owner_name'] ?? ''),
                        'description' => (string)($p['description'] ?? ''),
                        'invoice_code' => (string)($dealRow['invoice_code'] ?? ''),
                        'deal_id' => (string)($dealRow['deal_id'] ?? ''),
                        'deal_title' => (string)($dealRow['deal_title'] ?? ''),
                        'products' => (string)($dealRow['products'] ?? ''),
                        'service_cost' => (string)($dealRow['service_cost'] ?? ''),
                        'price_list_code' => (string)($dealRow['price_list_code'] ?? ''),
                        'payable_amount' => (string)($dealRow['payable_amount'] ?? ''),
                        'outstanding_balance' => (string)max(0, (float)($dealRow['payable_amount'] ?? 0) - (float)($dealRow['payment_amount'] ?? 0)),
                        'deal_status' => $this->dealStatusToFa((string)($dealRow['deal_status'] ?? '')),
                        'pipeline_stage' => $this->pipelineStageToFa((string)($dealRow['pipeline_stage'] ?? '')),
                        'contact_id' => (string)($dealRow['contact_id'] ?? ''),
                        'contact_name' => (string)($dealRow['contact_name'] ?? ''),
                        'contact_mobile' => (string)($dealRow['contact_mobile'] ?? ''),
                        'owner_name' => (string)($dealRow['owner_name'] ?? ''),
                        'customer_code' => (string)($dealRow['customer_code'] ?? ''),
                    ];
                    $out = [];
                    foreach ($columns as $c) {
                        $value = $flat[$c] ?? '';
                        if ($this->isFormattedExportNumericField($c)) {
                            $value = $this->formatExportNumber($value);
                        }
                        $out[] = $value;
                    }
                    yield $out;
                }
            }
        })();

        $filenameBase = ($datasetId === 'check_payments' ? 'check_payments_' : 'payments_') . date('Ymd_His');
        if ($format === 'csv') {
            $rows = [];
            foreach ($rowsGenerator as $r) {
                $rows[] = $r;
            }
            $this->appendSummaryRowForDataset($datasetId, $columns, $rows);
            $this->writeCsv($filenameBase . '.csv', $headers, (function () use ($rows) { foreach ($rows as $r) yield $r; })());
            return ['ok' => true];
        }

        $rows = [];
        $i = 0;
        foreach ($rowsGenerator as $r) {
            $rows[] = $r;
            $i++;
            if ($i > 5000) {
                throw new \Exception('برای خروجی Excel، حداکثر 5000 ردیف مجاز است. لطفاً CSV بگیرید یا فیلتر دقیق‌تر بزنید.');
            }
        }
        $this->appendSummaryRowForDataset($datasetId, $columns, $rows);
        $this->writeXlsx($filenameBase . '.xlsx', $headers, $rows);
        return ['ok' => true];
    }

    private function exportCommissionLines(array $columns, array $filters, string $format): array
    {
        $pdo = $this->db->getPdo();
        $selectMap = [
            'effective_date' => null,
            'billing_month' => null,
            'beneficiary_name' => null,
            'beneficiary_type' => null,
            'payment_owner_name' => null,
            'deal_didar_id' => null,
            'payment_record_id' => null,
            'payment_uuid' => null,
            'product_name' => null,
            'rule_title' => null,
            'payment_amount_snapshot' => null,
            'commission_base_amount' => null,
            'applied_rate_percent' => null,
            'calculated_commission' => null,
            'status' => null,
            'reversal_of_line_id' => null,
            'created_at' => null,
        ];
        $columns = array_values(array_filter($columns, fn($c) => array_key_exists($c, $selectMap)));
        if (empty($columns)) {
            $columns = ['effective_date', 'beneficiary_name', 'beneficiary_type', 'payment_owner_name', 'deal_didar_id', 'payment_amount_snapshot', 'applied_rate_percent', 'calculated_commission', 'status'];
        }

        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $nameExpr = $hasDisplayName
            ? "COALESCE(NULLIF(u.display_name,''), CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,'')), CONCAT('User #', u.id))"
            : "COALESCE(CONCAT(u.first_name,' ',u.last_name), CONCAT('User #', u.id))";
        $ownerNameExpr = $hasDisplayName
            ? "COALESCE(NULLIF(owner.display_name,''), CONCAT(COALESCE(owner.first_name,''), ' ', COALESCE(owner.last_name,'')), CONCAT('User #', owner.id))"
            : "COALESCE(CONCAT(owner.first_name,' ',owner.last_name), CONCAT('User #', owner.id))";

        $where = ["COALESCE(cl.status, 'CALCULATED') <> 'VOID'"];
        $params = [];
        if (!empty($filters['date_from'])) {
            $where[] = 'cl.effective_date >= ?';
            $params[] = trim((string)$filters['date_from']);
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'cl.effective_date <= ?';
            $params[] = trim((string)$filters['date_to']);
        }
        if (!empty($filters['owner_didar_id'])) {
            $where[] = 'u.didar_user_id = ?';
            $params[] = trim((string)$filters['owner_didar_id']);
        }
        if (!empty($filters['beneficiary_type'])) {
            $where[] = 'cl.beneficiary_type = ?';
            $params[] = strtoupper(trim((string)$filters['beneficiary_type']));
        }

        $sql = "
            SELECT cl.*,
                   {$nameExpr} AS beneficiary_name,
                   {$ownerNameExpr} AS payment_owner_name,
                   p.name AS product_name,
                   cr.title AS rule_title,
                   pr.payment_uuid
            FROM commission_lines cl
            LEFT JOIN users u ON u.id = cl.beneficiary_user_id
            LEFT JOIN users owner ON owner.id = cl.payment_owner_user_id
            LEFT JOIN products p ON p.id = cl.product_id
            LEFT JOIN commission_rules cr ON cr.id = cl.commission_rule_id
            LEFT JOIN payment_records pr ON pr.id = cl.payment_record_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY cl.effective_date ASC, cl.payment_record_id ASC, cl.id ASC
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        $labelMap = $this->getDatasetLabelMap('commission_lines');
        $headers = array_map(fn($c) => $labelMap[$c] ?? $c, $columns);
        $rows = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
            $flat = [
                'effective_date' => (string)($row['effective_date'] ?? ''),
                'billing_month' => (string)($row['billing_month'] ?? ''),
                'beneficiary_name' => (string)($row['beneficiary_name'] ?? ''),
                'beneficiary_type' => (string)($row['beneficiary_type'] ?? ''),
                'payment_owner_name' => (string)($row['payment_owner_name'] ?? ''),
                'deal_didar_id' => (string)($row['deal_didar_id'] ?? ''),
                'payment_record_id' => (string)($row['payment_record_id'] ?? ''),
                'payment_uuid' => (string)($row['payment_uuid'] ?? ''),
                'product_name' => (string)($row['product_name'] ?? ''),
                'rule_title' => (string)($row['rule_title'] ?? ''),
                'payment_amount_snapshot' => (string)($row['payment_amount_snapshot'] ?? ''),
                'commission_base_amount' => (string)($row['commission_base_amount'] ?? ''),
                'applied_rate_percent' => (string)($row['applied_rate_percent'] ?? ''),
                'calculated_commission' => (string)($row['calculated_commission'] ?? ''),
                'status' => (string)($row['status'] ?? ''),
                'reversal_of_line_id' => (string)($row['reversal_of_line_id'] ?? ''),
                'created_at' => (string)($row['created_at'] ?? ''),
            ];
            $out = [];
            foreach ($columns as $c) {
                $value = $flat[$c] ?? '';
                if ($this->isFormattedExportNumericField($c)) {
                    $value = $this->formatExportNumber($value);
                }
                $out[] = $value;
            }
            $rows[] = $out;
        }

        $filenameBase = 'commission_lines_' . date('Ymd_His');
        if ($format === 'csv') {
            $this->writeCsv($filenameBase . '.csv', $headers, (function () use ($rows) { foreach ($rows as $r) yield $r; })());
            return ['ok' => true];
        }

        if (count($rows) > 5000) {
            throw new \Exception('برای خروجی Excel، حداکثر 5000 ردیف مجاز است. لطفاً CSV بگیرید یا فیلتر دقیق‌تر بزنید.');
        }
        $this->writeXlsx($filenameBase . '.xlsx', $headers, $rows);
        return ['ok' => true];
    }
}
