<?php
namespace App\Demo;

use PDO;

class DemoApi
{
    public static function handle($app, string $action): bool
    {
        if (!defined('SALEOS_DEMO_MODE') || SALEOS_DEMO_MODE !== true) {
            return false;
        }

        $pdo = $app->db ? $app->db->getPdo() : null;
        if (!$pdo instanceof PDO) {
            return false;
        }

        if ($action === 'login') {
            self::login($pdo);
            return true;
        }
        if ($action === 'logout') {
            session_destroy();
            self::ok([], 'خروج موفقیت‌آمیز بود');
            return true;
        }
        if ($action === 'get_login_users') {
            self::loginUsers($pdo);
            return true;
        }

        $public = ['get_lead_assignment', 'get_lead_vip_access', 'reconcile_app_lead'];
        if (in_array($action, $public, true)) {
            self::ok(['demo_mode' => true]);
            return true;
        }

        if (empty($_SESSION['user_id'])) {
            self::error('لطفاً ابتدا وارد شوید');
            return true;
        }

        $routes = [
            'rbac_current_permissions' => 'permissions',
            'get_dashboard_stats' => 'dashboard',
            'get_users_list' => 'users',
            'get_didar_users' => 'ownerUsers',
            'sync_didar_users' => 'ownerUsers',
            'get_lookups' => 'lookups',
            'get_lookup_groups' => 'lookupGroups',
            'get_lookup_items' => 'lookupItems',
            'get_activity_types' => 'activityTypes',
            'get_products' => 'products',
            'get_kpi_products' => 'products',
            'get_notifications' => 'notifications',
            'get_notification_count' => 'notificationCount',
            'notification_count' => 'notificationCount',
            'check_reminders' => 'notifications',
            'get_reminders_list' => 'reminders',
            'get_leads' => 'leads',
            'get_referrals' => 'referrals',
            'get_deals' => 'deals',
            'get_legacy_deals' => 'deals',
            'get_person_profile' => 'personProfile',
            'get_deal' => 'deal',
            'get_field_configurations_public' => 'fieldConfigurations',
            'get_field_configurations' => 'fieldConfigurations',
            'get_kpi' => 'kpi',
            'get_kpi_section' => 'kpi',
            'get_commission_kpis' => 'commissionKpis',
            'get_performance_dashboard' => 'performanceDashboard',
            'get_performance_dashboard_section' => 'performanceDashboardSection',
            'get_kpi_drilldown' => 'kpiDrilldown',
            'get_financial_payments' => 'financialPayments',
            'mark_payment_sepidar' => 'markPaymentSepidar',
            'get_cheques' => 'cheques',
            'set_cheque_status' => 'setChequeStatus',
            'set_cheque_collection' => 'setChequeCollection',
            'get_finance_cheque_inbox' => 'cheques',
            'finance_approve_cheque' => 'setChequeStatus',
            'finance_reject_cheque' => 'setChequeStatus',
            'log_client_event' => 'emptyOk',
            'bulk_phone_lookup' => 'bulkPhoneLookup',
        ];

        if (!isset($routes[$action])) {
            return false;
        }

        self::{$routes[$action]}($pdo);
        return true;
    }

    private static function ok(array $data = [], string $message = ''): void
    {
        $payload = array_merge(['status' => 'success', 'demo_mode' => true], $data);
        if ($message !== '') {
            $payload['message'] = $message;
        }
        echo json_encode($payload, JSON_UNESCAPED_UNICODE);
    }

    private static function error(string $message): void
    {
        echo json_encode(['status' => 'error', 'demo_mode' => true, 'message' => $message], JSON_UNESCAPED_UNICODE);
    }

    private static function login(PDO $pdo): void
    {
        $identifier = trim((string)($_POST['email'] ?? $_POST['username'] ?? ''));
        $password = (string)($_POST['password'] ?? '');
        $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ? OR username = ? OR display_name = ? LIMIT 1");
        $stmt->execute([$identifier, $identifier, $identifier]);
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$user || (int)$user['is_active'] !== 1 || !password_verify($password, (string)$user['password'])) {
            self::error('نام کاربری یا رمز عبور اشتباه است');
            return;
        }

        $_SESSION['user_id'] = (int)$user['id'];
        $_SESSION['didar_id'] = (string)$user['didar_user_id'];
        $_SESSION['email'] = (string)$user['email'];
        $_SESSION['role'] = (string)$user['role'];
        $_SESSION['first_name'] = (string)$user['first_name'];
        $_SESSION['last_name'] = (string)$user['last_name'];
        $_SESSION['display_name'] = (string)$user['display_name'];
        $_SESSION['name'] = (string)$user['display_name'];

        self::ok(['user' => self::publicUser($user)], 'ورود موفقیت‌آمیز بود');
    }

    private static function loginUsers(PDO $pdo): void
    {
        $rows = $pdo->query("SELECT id, username, display_name, first_name, last_name, email, role FROM users WHERE is_active = 1 ORDER BY role = 'admin' DESC, display_name")->fetchAll(PDO::FETCH_ASSOC);
        self::ok(['users' => $rows]);
    }

    private static function users(PDO $pdo): void
    {
        $rows = $pdo->query("SELECT id, didar_user_id, username, email, first_name, last_name, display_name, role, is_active FROM users ORDER BY role = 'admin' DESC, display_name")->fetchAll(PDO::FETCH_ASSOC);
        self::ok(['users' => $rows, 'master_password_configured' => false]);
    }

    private static function ownerUsers(PDO $pdo): void
    {
        $rows = $pdo->query("SELECT id, didar_user_id, didar_user_id AS owner_id, username, email, first_name, last_name, display_name, role, is_active FROM users WHERE is_active = 1 ORDER BY role = 'admin' DESC, display_name")->fetchAll(PDO::FETCH_ASSOC);
        self::ok(['users' => $rows, 'synced' => true]);
    }

    private static function permissions(PDO $pdo): void
    {
        self::ok([
            'role' => $_SESSION['role'] ?? 'admin',
            'capabilities' => ['*'],
            'scopes' => [],
            'data_scopes' => [],
        ]);
    }

    private static function dashboard(PDO $pdo): void
    {
        $stats = [
            'total_leads' => (int)$pdo->query("SELECT COUNT(*) FROM persons")->fetchColumn(),
            'successful_leads' => (int)$pdo->query("SELECT COUNT(*) FROM persons WHERE sale_status = 1")->fetchColumn(),
            'failed_leads' => (int)$pdo->query("SELECT COUNT(*) FROM persons WHERE sale_status = 0")->fetchColumn(),
            'total_deals' => (int)$pdo->query("SELECT COUNT(*) FROM deals")->fetchColumn(),
            'ongoing_deals' => (int)$pdo->query("SELECT COUNT(*) FROM deals WHERE status = 'Pending' AND COALESCE(pipeline_bucket, 'active') <> 'parked'")->fetchColumn(),
            'deals_by_stage' => self::keyValue($pdo->query("SELECT pipeline_stage, COUNT(*) AS c FROM deals WHERE status = 'Pending' AND COALESCE(pipeline_bucket, 'active') <> 'parked' GROUP BY pipeline_stage")->fetchAll(PDO::FETCH_ASSOC), 'pipeline_stage', 'c'),
            'parked_deals_by_stage' => self::keyValue($pdo->query("SELECT parked_from_stage, COUNT(*) AS c FROM deals WHERE status = 'Pending' AND COALESCE(pipeline_bucket, 'active') = 'parked' GROUP BY parked_from_stage")->fetchAll(PDO::FETCH_ASSOC), 'parked_from_stage', 'c'),
            'parked_stage_deals' => [],
            'parked_stage_deals_total' => 0,
            'inactive_stage_deals' => [],
            'inactive_stage_deals_total' => 0,
            'parked_by_user' => [],
        ];
        foreach ($stats['parked_deals_by_stage'] as $stage => $count) {
            $stats['parked_stage_deals'][] = ['value' => $stage, 'title' => $stage, 'count' => (int)$count];
            $stats['parked_stage_deals_total'] += (int)$count;
        }
        self::ok(['stats' => $stats]);
    }

    private static function lookups(PDO $pdo): void
    {
        $group = trim((string)($_REQUEST['group'] ?? ''));
        if ($group !== '') {
            $stmt = $pdo->prepare("SELECT li.* FROM lookup_items li INNER JOIN lookup_groups lg ON lg.id = li.group_id WHERE lg.code = ? ORDER BY li.sort_order, li.id");
            $stmt->execute([$group]);
            self::ok(['group' => $group, 'items' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
            return;
        }

        $lookups = [];
        $sql = "SELECT lg.code AS group_code, lg.title AS group_title, li.* FROM lookup_groups lg LEFT JOIN lookup_items li ON li.group_id = lg.id ORDER BY lg.sort_order, li.sort_order, li.id";
        foreach ($pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $code = $row['group_code'];
            if (!isset($lookups[$code])) {
                $lookups[$code] = ['title' => $row['group_title'], 'items' => []];
            }
            if ($row['id'] !== null) {
                $lookups[$code]['items'][] = $row;
            }
        }
        self::ok(['lookups' => $lookups]);
    }

    private static function lookupGroups(PDO $pdo): void
    {
        $rows = $pdo->query("SELECT * FROM lookup_groups ORDER BY sort_order, title")->fetchAll(PDO::FETCH_ASSOC);
        self::ok(['groups' => $rows]);
    }

    private static function lookupItems(PDO $pdo): void
    {
        $group = trim((string)($_REQUEST['group'] ?? $_REQUEST['group_code'] ?? ''));
        if ($group === '') {
            self::ok(['items' => []]);
            return;
        }
        $stmt = $pdo->prepare("SELECT li.* FROM lookup_items li INNER JOIN lookup_groups lg ON lg.id = li.group_id WHERE lg.code = ? ORDER BY li.sort_order, li.id");
        $stmt->execute([$group]);
        self::ok(['group' => $group, 'items' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
    }

    private static function activityTypes(PDO $pdo): void
    {
        self::ok(['types' => [
            ['Id' => 'call_out', 'Title' => 'تماس خروجی'],
            ['Id' => 'call_in', 'Title' => 'تماس ورودی'],
            ['Id' => 'followup', 'Title' => 'پیگیری'],
            ['Id' => 'meeting', 'Title' => 'جلسه مشاوره'],
        ]]);
    }

    private static function products(PDO $pdo): void
    {
        $rows = $pdo->query("SELECT * FROM products WHERE is_active = 1 ORDER BY sort_order, name")->fetchAll(PDO::FETCH_ASSOC);
        self::ok(['products' => $rows]);
    }

    private static function notifications(PDO $pdo): void
    {
        self::ok(['notifications' => [], 'unread_count' => 0, 'count' => 0]);
    }

    private static function notificationCount(PDO $pdo): void
    {
        self::ok(['count' => 0, 'unread_count' => 0]);
    }

    private static function reminders(PDO $pdo): void
    {
        self::ok(['reminders' => [], 'pagination' => ['current_page' => 1, 'per_page' => 20, 'total_count' => 0, 'total_pages' => 0]]);
    }

    private static function leads(PDO $pdo): void
    {
        [$where, $params] = self::commonFilters('p');
        $page = max(1, (int)($_REQUEST['page'] ?? 1));
        $perPage = min(100, max(10, (int)($_REQUEST['per_page'] ?? 50)));
        $offset = ($page - 1) * $perPage;

        $count = $pdo->prepare("SELECT COUNT(*) FROM persons p WHERE {$where}");
        $count->execute($params);
        $total = (int)$count->fetchColumn();

        $sql = "SELECT p.*,
                       COALESCE(u.display_name, p.owner_didar_id, '') AS owner_name,
                       COALESCE(v.stage_name, 'new') AS virtual_stage,
                       (SELECT MAX(register_date) FROM activities a WHERE a.contact_didar_id = p.didar_contact_id) AS last_activity_date
                FROM persons p
                LEFT JOIN users u ON u.didar_user_id = p.owner_didar_id
                LEFT JOIN virtual_stages v ON v.contact_didar_id = p.didar_contact_id
                WHERE {$where}
                ORDER BY COALESCE(p.register_time, p.created_at) DESC, p.id DESC
                LIMIT ? OFFSET ?";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_merge($params, [$perPage, $offset]));
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rows as &$row) {
            $row['display_name'] = trim((string)$row['first_name'] . ' ' . (string)$row['last_name']);
            $row['full_name'] = $row['display_name'];
        }

        self::ok([
            'leads' => $rows,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total_count' => $total,
                'total_pages' => (int)ceil($total / $perPage),
                'has_next' => ($page * $perPage) < $total,
                'has_prev' => $page > 1,
            ],
        ]);
    }

    private static function referrals(PDO $pdo): void
    {
        self::ok(['referrals' => [], 'pagination' => ['current_page' => 1, 'per_page' => 20, 'total_count' => 0, 'total_pages' => 0]]);
    }

    private static function deals(PDO $pdo): void
    {
        [$where, $params] = self::dealFilters('d');
        $limit = min(100, max(10, (int)($_REQUEST['limit'] ?? $_REQUEST['per_page'] ?? 50)));
        $offset = max(0, (int)($_REQUEST['offset'] ?? 0));
        $sql = "SELECT d.*,
                       COALESCE(u.display_name, d.owner_didar_id, '') AS owner_name,
                       TRIM(COALESCE(p.first_name, '') || ' ' || COALESCE(p.last_name, '')) AS contact_name,
                       p.mobile_phone AS contact_mobile,
                       p.customer_code,
                       p.id AS local_person_id
                FROM deals d
                LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
                LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
                WHERE {$where}
                ORDER BY COALESCE(d.register_time, d.created_at) DESC, d.id DESC
                LIMIT ? OFFSET ?";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_merge($params, [$limit, $offset]));
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        self::ok(['deals' => $rows, 'pagination' => ['limit' => $limit, 'offset' => $offset, 'rows_count' => count($rows)]]);
    }

    private static function personProfile(PDO $pdo): void
    {
        $id = trim((string)($_REQUEST['id'] ?? $_REQUEST['contact_id'] ?? $_REQUEST['didar_contact_id'] ?? ''));
        $stmt = $pdo->prepare("SELECT * FROM persons WHERE id = ? OR didar_contact_id = ? LIMIT 1");
        $stmt->execute([$id, $id]);
        $person = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$person) {
            self::error('لید پیدا نشد');
            return;
        }
        $deals = $pdo->prepare("SELECT * FROM deals WHERE contact_didar_id = ? ORDER BY register_time DESC");
        $deals->execute([$person['didar_contact_id']]);
        $activities = $pdo->prepare("SELECT * FROM activities WHERE contact_didar_id = ? ORDER BY register_date DESC LIMIT 20");
        $activities->execute([$person['didar_contact_id']]);
        self::ok(['person' => $person, 'lead' => $person, 'deals' => $deals->fetchAll(PDO::FETCH_ASSOC), 'activities' => $activities->fetchAll(PDO::FETCH_ASSOC)]);
    }

    private static function deal(PDO $pdo): void
    {
        $id = trim((string)($_REQUEST['deal_id'] ?? $_REQUEST['didar_deal_id'] ?? $_REQUEST['id'] ?? ''));
        $stmt = $pdo->prepare("SELECT d.*, TRIM(COALESCE(p.first_name, '') || ' ' || COALESCE(p.last_name, '')) AS contact_name FROM deals d LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id WHERE d.id = ? OR d.didar_deal_id = ? LIMIT 1");
        $stmt->execute([$id, $id]);
        $deal = $stmt->fetch(PDO::FETCH_ASSOC);
        $deal ? self::ok(['deal' => $deal]) : self::error('معامله پیدا نشد');
    }

    private static function fieldConfigurations(PDO $pdo): void
    {
        self::ok(['fields' => [], 'configurations' => []]);
    }

    private static function kpi(PDO $pdo): void
    {
        $payments = (float)$pdo->query("SELECT COALESCE(SUM(amount), 0) FROM transactions")->fetchColumn();
        $deals = (int)$pdo->query("SELECT COUNT(*) FROM deals")->fetchColumn();
        self::ok(['kpi' => ['total_sales' => $payments, 'deal_count' => $deals, 'conversion_rate' => 42.5]]);
    }

    private static function commissionKpis(PDO $pdo): void
    {
        $range = self::dateRange();
        $owners = self::requestList('owner_didar_id');
        if (!$owners) {
            $owners = self::requestList('owner_didar_ids');
        }
        [$join, $dealFilter, $filterParams] = self::buildDealFilter($owners, self::requestList('product_ids'), 'd', 'dp');
        $params = array_merge([$range['from'], $range['to']], $filterParams);
        $collected = self::value($pdo, "SELECT COALESCE(SUM(t.amount), 0)
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}", $params);
        $dealCount = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id)
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}", $params);
        $repCommission = round($collected * 0.025);
        self::ok([
            'rep' => [
                'tce' => $repCommission,
                'eligible_sales_amount' => $collected,
                'eligible_deal_count' => $dealCount,
                'label' => 'پورسانت نمونه',
            ],
            'manager' => [
                'tce' => round($collected * 0.01),
                'team_sales_amount' => $collected,
                'team_deal_count' => $dealCount,
                'label' => 'پورسانت تیمی نمونه',
            ],
        ]);
    }

    private static function performanceDashboard(PDO $pdo): void
    {
        $dashboard = self::buildPerformanceDashboard($pdo);
        self::ok(['dashboard' => $dashboard, 'filters' => ['criteria' => self::performanceCriteria()]]);
    }

    private static function performanceDashboardSection(PDO $pdo): void
    {
        $section = trim((string)($_REQUEST['section'] ?? 'summary'));
        $dashboard = self::buildPerformanceDashboard($pdo);
        if ($section === 'charts') {
            self::ok(['data' => ['charts' => $dashboard['charts']], 'filters' => ['criteria' => self::performanceCriteria()]]);
            return;
        }
        if ($section === 'tabs') {
            self::ok(['data' => ['tabs' => $dashboard['tabs'], 'raw' => $dashboard['raw']], 'filters' => ['criteria' => self::performanceCriteria()]]);
            return;
        }
        if ($section === 'ceo_report') {
            self::ok(['data' => ['ceo_report' => $dashboard['ceo_report']], 'filters' => ['criteria' => self::performanceCriteria()]]);
            return;
        }
        self::ok([
            'data' => [
                'summary_cards' => $dashboard['summary_cards'],
                'comparison' => $dashboard['comparison'],
            ],
            'filters' => ['criteria' => self::performanceCriteria()],
        ]);
    }

    private static function kpiDrilldown(PDO $pdo): void
    {
        $range = self::dateRange();
        [$join, $dealFilter, $filterParams] = self::buildDealFilter(self::requestList('owner_didar_ids'), self::requestList('product_ids'), 'd', 'dp');
        $rowDate = trim((string)($_REQUEST['row_date'] ?? ''));
        $dateSql = $rowDate !== '' ? 'date(COALESCE(t.payment_date, d.register_time)) = ?' : 'date(COALESCE(t.payment_date, d.register_time)) BETWEEN ? AND ?';
        $dateParams = $rowDate !== '' ? [$rowDate] : [$range['from'], $range['to']];
        $params = array_merge($dateParams, $filterParams);

        $deals = self::rows($pdo, "SELECT d.id AS deal_id, d.didar_deal_id, d.title, d.status, d.pipeline_stage,
                   d.final_price, d.payable_amount, d.payment_amount, d.contact_didar_id,
                   TRIM(COALESCE(p.first_name, '') || ' ' || COALESCE(p.last_name, '')) AS contact_name,
                   p.mobile_phone, COALESCE(u.display_name, d.owner_didar_id) AS owner_name,
                   d.register_time, d.won_time, d.lost_time
            FROM deals d
            LEFT JOIN transactions t ON t.deal_id = d.id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$join}
            WHERE {$dateSql} {$dealFilter}
            GROUP BY d.id
            ORDER BY COALESCE(t.payment_date, d.register_time) DESC
            LIMIT 80", $params);

        $payments = self::rows($pdo, "SELECT t.id, t.deal_id, d.didar_deal_id, t.amount, t.payment_date, t.method, t.destination_bank,
                   d.contact_didar_id, TRIM(COALESCE(p.first_name, '') || ' ' || COALESCE(p.last_name, '')) AS contact_name,
                   d.title AS deal_title, COALESCE(u.display_name, d.owner_didar_id) AS owner_name
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$join}
            WHERE " . ($rowDate !== '' ? 'date(t.payment_date) = ?' : 'date(t.payment_date) BETWEEN ? AND ?') . " {$dealFilter}
            ORDER BY t.payment_date DESC
            LIMIT 80", $params);

        $leadParams = $rowDate !== '' ? [$rowDate] : [$range['from'], $range['to']];
        [$personFilter, $personParams] = self::buildPersonFilter(self::requestList('owner_didar_ids'), 'p');
        $leads = self::rows($pdo, "SELECT p.didar_contact_id AS contact_id, p.didar_contact_id, p.customer_code,
                   TRIM(COALESCE(p.first_name, '') || ' ' || COALESCE(p.last_name, '')) AS full_name,
                   p.mobile_phone, p.register_time, COALESCE(u.display_name, p.owner_didar_id) AS owner_name
            FROM persons p
            LEFT JOIN users u ON u.didar_user_id = p.owner_didar_id
            WHERE " . ($rowDate !== '' ? 'date(p.register_time) = ?' : 'date(p.register_time) BETWEEN ? AND ?') . " {$personFilter}
            ORDER BY p.register_time DESC
            LIMIT 80", array_merge($leadParams, $personParams));

        self::ok([
            'meta' => [
                'title' => 'جزئیات داده‌های نمونه',
                'row_type' => count($payments) > 0 ? 'payment' : (count($deals) > 0 ? 'deal' : 'lead'),
                'total_leads' => count($leads),
                'total_deals' => count($deals),
                'total_payments' => count($payments),
            ],
            'leads' => $leads,
            'deals' => $deals,
            'payments' => $payments,
            'activities' => [],
        ]);
    }

    private static function financialPayments(PDO $pdo): void
    {
        $limit = min(100, max(10, (int)($_REQUEST['limit'] ?? 50)));
        $offset = max(0, (int)($_REQUEST['offset'] ?? 0));
        [$where, $params] = self::transactionFilters(false);
        $total = (int)self::value($pdo, "SELECT COUNT(*)
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            WHERE {$where}", $params);
        $rows = self::rows($pdo, self::transactionSelectSql() . "
            WHERE {$where}
            ORDER BY date(t.payment_date) DESC, t.id DESC
            LIMIT ? OFFSET ?", array_merge($params, [$limit, $offset]));
        self::ok(['payments' => $rows, 'total' => $total, 'limit' => $limit, 'offset' => $offset]);
    }

    private static function cheques(PDO $pdo): void
    {
        $limit = min(100, max(10, (int)($_REQUEST['limit'] ?? 50)));
        $offset = max(0, (int)($_REQUEST['offset'] ?? 0));
        [$where, $params] = self::transactionFilters(true);
        $total = (int)self::value($pdo, "SELECT COUNT(*)
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            WHERE {$where}", $params);
        $rows = self::rows($pdo, self::transactionSelectSql() . "
            WHERE {$where}
            ORDER BY date(COALESCE(t.check_due_date, t.payment_date)) DESC, t.id DESC
            LIMIT ? OFFSET ?", array_merge($params, [$limit, $offset]));
        self::ok(['cheques' => $rows, 'total' => $total, 'limit' => $limit, 'offset' => $offset]);
    }

    private static function markPaymentSepidar(PDO $pdo): void
    {
        $id = (int)($_REQUEST['transaction_id'] ?? 0);
        if ($id <= 0) {
            self::error('پرداخت پیدا نشد');
            return;
        }
        $stmt = $pdo->prepare("UPDATE transactions SET sepidar_registered_at = COALESCE(sepidar_registered_at, datetime('now')), registered_by = COALESCE(registered_by, ?) WHERE id = ?");
        $stmt->execute([$_SESSION['display_name'] ?? 'امید مالی', $id]);
        self::ok(['review' => ['transaction_id' => $id, 'sepidar_registered_at' => date('Y-m-d H:i:s'), 'registered_by' => $_SESSION['display_name'] ?? 'امید مالی']]);
    }

    private static function setChequeStatus(PDO $pdo): void
    {
        $id = (int)($_REQUEST['transaction_id'] ?? 0);
        $status = trim((string)($_REQUEST['status'] ?? $_REQUEST['check_status'] ?? 'approved'));
        if (!in_array($status, ['pending', 'approved', 'rejected'], true)) {
            $status = 'approved';
        }
        $note = trim((string)($_REQUEST['note'] ?? $_REQUEST['check_status_note'] ?? ''));
        $stmt = $pdo->prepare("UPDATE transactions SET check_status = ?, check_status_note = ?, registered_by = COALESCE(registered_by, ?) WHERE id = ? AND method = 'چک'");
        $stmt->execute([$status, $note, $_SESSION['display_name'] ?? 'امید مالی', $id]);
        self::ok(['cheque' => ['transaction_id' => $id, 'check_status' => $status, 'check_status_note' => $note]]);
    }

    private static function setChequeCollection(PDO $pdo): void
    {
        $id = (int)($_REQUEST['transaction_id'] ?? 0);
        $status = trim((string)($_REQUEST['status'] ?? $_REQUEST['collection_status'] ?? 'collected'));
        if (!in_array($status, ['pending', 'collected', 'returned', 'blataklif'], true)) {
            $status = 'collected';
        }
        $date = trim((string)($_REQUEST['collection_date'] ?? ''));
        if ($date === '') {
            $date = $status === 'collected' ? date('Y-m-d') : null;
        }
        $stmt = $pdo->prepare("UPDATE transactions SET collection_status = ?, collection_date = ? WHERE id = ? AND method = 'چک'");
        $stmt->execute([$status, $date, $id]);
        self::ok(['cheque' => ['transaction_id' => $id, 'collection_status' => $status, 'collection_date' => $date]]);
    }

    private static function transactionFilters(bool $chequesOnly): array
    {
        $where = ['1=1'];
        $params = [];
        if ($chequesOnly) {
            $where[] = "t.method = 'چک'";
        }
        $search = trim((string)($_REQUEST['search'] ?? ''));
        if ($search !== '') {
            $where[] = "(d.title LIKE ? OR p.first_name LIKE ? OR p.last_name LIKE ? OR p.mobile_phone LIKE ? OR p.customer_code LIKE ? OR t.check_number LIKE ? OR t.destination_bank LIKE ?)";
            $like = '%' . $search . '%';
            array_push($params, $like, $like, $like, $like, $like, $like, $like);
        }
        $from = trim((string)($_REQUEST['date_from'] ?? ''));
        $to = trim((string)($_REQUEST['date_to'] ?? ''));
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) {
            $where[] = "date(COALESCE(t.check_due_date, t.payment_date)) >= ?";
            $params[] = $from;
        }
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $to)) {
            $where[] = "date(COALESCE(t.check_due_date, t.payment_date)) <= ?";
            $params[] = $to;
        }
        $checkStatus = trim((string)($_REQUEST['check_status'] ?? ''));
        if ($checkStatus !== '') {
            $where[] = "COALESCE(t.check_status, 'pending') = ?";
            $params[] = $checkStatus;
        }
        $collectionStatus = trim((string)($_REQUEST['collection_status'] ?? ''));
        if ($collectionStatus !== '') {
            $where[] = "COALESCE(t.collection_status, 'pending') = ?";
            $params[] = $collectionStatus;
        }
        $sepidar = trim((string)($_REQUEST['sepidar_status'] ?? ''));
        if ($sepidar === 'registered') {
            $where[] = "t.sepidar_registered_at IS NOT NULL";
        } elseif ($sepidar === 'pending') {
            $where[] = "t.sepidar_registered_at IS NULL";
        }
        return [implode(' AND ', $where), $params];
    }

    private static function transactionSelectSql(): string
    {
        return "SELECT
                t.id AS transaction_id,
                t.id,
                t.deal_id,
                d.didar_deal_id,
                d.title AS deal_title,
                d.service_cost,
                d.payable_amount,
                t.amount AS paid_amount,
                t.amount,
                t.payment_date,
                t.payment_date AS payment_registered_at,
                t.method,
                t.card_last4,
                t.destination_bank,
                t.destination_tafsili_code,
                t.description,
                t.payer_name,
                t.receipt_image_path,
                t.check_bank_name,
                t.check_date,
                t.check_number,
                t.check_owner_name,
                t.check_due_date,
                t.check_delivery_date,
                t.check_recipient_name,
                COALESCE(t.check_status, 'pending') AS check_status,
                t.check_status_note,
                COALESCE(t.collection_status, 'pending') AS collection_status,
                t.collection_date,
                t.sepidar_registered_at,
                t.registered_by,
                t.payment_owner_didar_id,
                t.payment_owner_name_snapshot,
                t.commission_status,
                t.commission_effective_date,
                COALESCE(u.display_name, d.owner_didar_id) AS owner_name,
                TRIM(COALESCE(p.first_name, '') || ' ' || COALESCE(p.last_name, '')) AS contact_name,
                p.customer_code,
                p.mobile_phone
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id";
    }

    private static function buildPerformanceDashboard(PDO $pdo): array
    {
        $range = self::dateRange();
        $owners = self::requestList('owner_didar_ids');
        $products = self::requestList('product_ids');
        [$join, $dealFilter, $dealFilterParams] = self::buildDealFilter($owners, $products, 'd', 'dp');
        [$personFilter, $personParams] = self::buildPersonFilter($owners, 'p');

        $paymentParams = array_merge([$range['from'], $range['to']], $dealFilterParams);
        $dealDateParams = array_merge([$range['from'], $range['to']], $dealFilterParams);
        $salesAmount = self::value($pdo, "SELECT COALESCE(SUM(t.amount), 0)
            FROM transactions t INNER JOIN deals d ON d.id = t.deal_id {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}", $paymentParams);
        $salesDealCount = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id)
            FROM transactions t INNER JOIN deals d ON d.id = t.deal_id {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}", $paymentParams);
        $contractAmount = self::value($pdo, "SELECT COALESCE(SUM(d.final_price), 0)
            FROM deals d {$join}
            WHERE d.id IN (SELECT DISTINCT deal_id FROM transactions WHERE date(payment_date) BETWEEN ? AND ?) {$dealFilter}", $paymentParams);
        $receiptCount = (int)self::value($pdo, "SELECT COUNT(t.id)
            FROM transactions t INNER JOIN deals d ON d.id = t.deal_id {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}", $paymentParams);
        $lostDepositAmount = self::value($pdo, "SELECT COALESCE(SUM(t.amount), 0)
            FROM transactions t INNER JOIN deals d ON d.id = t.deal_id {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? AND d.status = 'Lost' {$dealFilter}", $paymentParams);
        $lostDepositCount = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id)
            FROM transactions t INNER JOIN deals d ON d.id = t.deal_id {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? AND d.status = 'Lost' {$dealFilter}", $paymentParams);
        $registeredCount = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $registeredAmount = self::value($pdo, "SELECT COALESCE(SUM(d.final_price), 0) FROM deals d {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $outsideFirst = self::row($pdo, "SELECT COUNT(DISTINCT d.id) AS c, COALESCE(SUM(t.amount), 0) AS amount
            FROM transactions t INNER JOIN deals d ON d.id = t.deal_id {$join}
            WHERE t.is_first_payment = 1
              AND date(t.payment_date) BETWEEN ? AND ?
              AND (date(d.register_time) < ? OR date(d.register_time) > ?)
              {$dealFilter}", array_merge([$range['from'], $range['to'], $range['from'], $range['to']], $dealFilterParams));
        $balances = self::row($pdo, "SELECT
                SUM(CASE WHEN x.balance > 0 THEN 1 ELSE 0 END) AS outstanding_count,
                COALESCE(SUM(CASE WHEN x.balance > 0 THEN x.balance ELSE 0 END), 0) AS outstanding_amount,
                COALESCE(SUM(CASE WHEN x.balance > 0 THEN x.payable ELSE 0 END), 0) AS outstanding_contract_amount,
                SUM(CASE WHEN x.payable > 0 AND x.paid >= x.payable THEN 1 ELSE 0 END) AS settled_count,
                COALESCE(SUM(CASE WHEN x.payable > 0 AND x.paid >= x.payable THEN x.payable ELSE 0 END), 0) AS settled_amount,
                COALESCE(SUM(CASE WHEN x.payable > 0 AND x.paid >= x.payable THEN x.paid ELSE 0 END), 0) AS settled_collected_amount,
                SUM(CASE WHEN x.paid <= 0 THEN 1 ELSE 0 END) AS without_payment_count,
                COALESCE(SUM(CASE WHEN x.paid <= 0 THEN x.payable ELSE 0 END), 0) AS without_payment_amount
            FROM (
                SELECT d.id, COALESCE(d.payable_amount, d.final_price, 0) AS payable, COALESCE(SUM(t.amount), 0) AS paid,
                       COALESCE(d.payable_amount, d.final_price, 0) - COALESCE(SUM(t.amount), 0) AS balance
                FROM deals d
                LEFT JOIN transactions t ON t.deal_id = d.id
                {$join}
                WHERE 1=1 {$dealFilter}
                GROUP BY d.id
            ) x", $dealFilterParams);
        $lostCount = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join}
            WHERE d.status = 'Lost' AND date(COALESCE(d.lost_time, d.register_time)) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $lostAmount = self::value($pdo, "SELECT COALESCE(SUM(d.final_price), 0) FROM deals d {$join}
            WHERE d.status = 'Lost' AND date(COALESCE(d.lost_time, d.register_time)) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $wonCount = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join}
            WHERE d.status = 'Won' AND date(COALESCE(d.won_time, d.register_time)) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $wonAmount = self::value($pdo, "SELECT COALESCE(SUM(d.final_price), 0) FROM deals d {$join}
            WHERE d.status = 'Won' AND date(COALESCE(d.won_time, d.register_time)) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $openCount = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join}
            WHERE d.status = 'Pending' {$dealFilter}", $dealFilterParams);
        $openAmount = self::value($pdo, "SELECT COALESCE(SUM(d.final_price), 0) FROM deals d {$join}
            WHERE d.status = 'Pending' {$dealFilter}", $dealFilterParams);
        $allDeals = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $dealsWithPayment = (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d INNER JOIN transactions t ON t.deal_id = d.id {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $leadTotal = (int)self::value($pdo, "SELECT COUNT(*) FROM persons p WHERE date(p.register_time) BETWEEN ? AND ? {$personFilter}", array_merge([$range['from'], $range['to']], $personParams));
        $leadConverted = (int)self::value($pdo, "SELECT COUNT(DISTINCT p.id)
            FROM persons p
            INNER JOIN deals d ON d.contact_didar_id = p.didar_contact_id
            INNER JOIN transactions t ON t.deal_id = d.id
            WHERE date(p.register_time) BETWEEN ? AND ? {$personFilter}", array_merge([$range['from'], $range['to']], $personParams));
        $avgDays = self::value($pdo, "SELECT COALESCE(AVG(julianday(x.first_payment_date) - julianday(date(d.register_time))), 0)
            FROM deals d
            INNER JOIN (SELECT deal_id, MIN(date(payment_date)) AS first_payment_date FROM transactions GROUP BY deal_id) x ON x.deal_id = d.id
            {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}", $dealDateParams);
        $targetAmount = max(1, count(self::dateSeries($range['from'], $range['to'])) * 20000000);
        $conversionRate = $allDeals > 0 ? round(($dealsWithPayment / $allDeals) * 100, 2) : 0.0;
        $leadRate = $leadTotal > 0 ? round(($leadConverted / $leadTotal) * 100, 2) : 0.0;

        $summary = [
            'total_sales_amount' => $salesAmount,
            'with_sales_deals_count' => $salesDealCount,
            'total_contract_amount' => $contractAmount,
            'payment_registered_in_range_amount' => $salesAmount,
            'payment_registered_in_range_deal_count' => $salesDealCount,
            'payment_registered_in_range_receipt_count' => $receiptCount,
            'payment_outside_registered_first_payment_amount' => (float)($outsideFirst['amount'] ?? 0),
            'payment_outside_registered_first_payment_deal_count' => (int)($outsideFirst['c'] ?? 0),
            'payment_outside_registered_first_payment_receipt_count' => (int)($outsideFirst['c'] ?? 0),
            'lost_deposit_amount' => $lostDepositAmount,
            'lost_deposit_deal_count' => $lostDepositCount,
            'lost_deposit_receipt_count' => $lostDepositCount,
            'deal_related_total_count' => $registeredCount + (int)($outsideFirst['c'] ?? 0),
            'deal_related_total_amount' => $registeredAmount + (float)($outsideFirst['amount'] ?? 0),
            'deal_registered_in_range_count' => $registeredCount,
            'deal_registered_in_range_amount' => $registeredAmount,
            'deal_outside_registered_first_payment_count' => (int)($outsideFirst['c'] ?? 0),
            'deal_outside_registered_first_payment_amount' => (float)($outsideFirst['amount'] ?? 0),
            'target_achievement_percent' => round(($salesAmount / $targetAmount) * 100, 2),
            'target_has_target' => true,
            'target_amount' => $targetAmount,
            'target_daily_amount' => 20000000,
            'outstanding_amount' => (float)($balances['outstanding_amount'] ?? 0),
            'outstanding_count' => (int)($balances['outstanding_count'] ?? 0),
            'outstanding_contract_amount' => (float)($balances['outstanding_contract_amount'] ?? 0),
            'settled_count' => (int)($balances['settled_count'] ?? 0),
            'settled_amount' => (float)($balances['settled_amount'] ?? 0),
            'settled_collected_amount' => (float)($balances['settled_collected_amount'] ?? 0),
            'lost_count' => $lostCount,
            'lost_amount' => $lostAmount,
            'lost_reason_breakdown' => self::lostReasonBreakdown($pdo, $range, $dealFilter, $join, $dealFilterParams),
            'won_count' => $wonCount,
            'won_amount' => $wonAmount,
            'open_pipeline_count' => $openCount,
            'open_pipeline_amount' => $openAmount,
            'deal_conversion_rate' => $conversionRate,
            'deal_conversion_total_deals' => $allDeals,
            'deal_non_conversion_rate' => round(100 - $conversionRate, 2),
            'lead_conversion_rate' => $leadRate,
            'lead_total_new' => $leadTotal,
            'lead_non_conversion_rate' => round(100 - $leadRate, 2),
            'lead_converted' => $leadConverted,
            'without_payment_count' => (int)($balances['without_payment_count'] ?? 0),
            'without_payment_amount' => (float)($balances['without_payment_amount'] ?? 0),
            'avg_conversion_days' => round($avgDays, 2),
            'activity_performance' => ['past_deals' => [], 'daily' => []],
        ];

        return [
            'summary_cards' => $summary,
            'comparison' => self::performanceComparison($pdo, $range, $join, $dealFilter, $dealFilterParams, $salesAmount),
            'charts' => self::performanceCharts($pdo, $range, $join, $dealFilter, $dealFilterParams, $personFilter, $personParams),
            'tabs' => self::performanceTabs($pdo, $range, $join, $dealFilter, $dealFilterParams, $personFilter, $personParams),
            'raw' => [],
            'ceo_report' => self::performanceCeoReport($pdo, $range, $join, $dealFilter, $dealFilterParams, $summary),
        ];
    }

    private static function emptyOk(PDO $pdo): void
    {
        self::ok();
    }

    private static function bulkPhoneLookup(PDO $pdo): void
    {
        $phones = preg_split('/[\s,،;]+/u', (string)($_POST['phones'] ?? $_POST['mobile_phones'] ?? '')) ?: [];
        $found = [];
        $stmt = $pdo->prepare("SELECT id, didar_contact_id, first_name, last_name, mobile_phone, owner_didar_id FROM persons WHERE mobile_phone = ? OR secondary_mobile_phone = ? LIMIT 1");
        foreach ($phones as $phone) {
            $phone = trim($phone);
            if ($phone === '') {
                continue;
            }
            $stmt->execute([$phone, $phone]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            $found[] = ['phone' => $phone, 'found' => (bool)$row, 'person' => $row ?: null];
        }
        self::ok(['results' => $found]);
    }

    private static function performanceCriteria(): array
    {
        return [
            'kpi_sales_date_basis' => 'ui_payment_date',
            'kpi_sales_count_rule' => 'first_payment_only',
            'kpi_payment_date_basis' => 'ui_payment_date',
            'kpi_payment_count_rule' => 'all_payments',
            'kpi_payment_status_scope' => 'confirmed_only',
        ];
    }

    private static function dateRange(): array
    {
        $from = trim((string)($_REQUEST['date_from'] ?? ''));
        $to = trim((string)($_REQUEST['date_to'] ?? ''));
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) {
            $from = date('Y-m-d', strtotime('-30 days'));
        }
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $to)) {
            $to = date('Y-m-d');
        }
        if ($to < $from) {
            $tmp = $from;
            $from = $to;
            $to = $tmp;
        }
        return ['from' => $from, 'to' => $to];
    }

    private static function requestList(string $key): array
    {
        $value = $_REQUEST[$key] ?? '';
        if (is_array($value)) {
            $parts = $value;
        } else {
            $parts = preg_split('/[,،\s]+/u', (string)$value) ?: [];
        }
        $out = [];
        foreach ($parts as $part) {
            $part = trim((string)$part);
            if ($part !== '' && !in_array($part, $out, true)) {
                $out[] = $part;
            }
        }
        return $out;
    }

    private static function placeholders(array $values): string
    {
        return implode(',', array_fill(0, count($values), '?'));
    }

    private static function buildDealFilter(array $owners, array $products, string $alias, string $productAlias): array
    {
        $join = '';
        $where = '';
        $params = [];
        if ($products) {
            $join = " INNER JOIN deal_products {$productAlias} ON {$productAlias}.deal_id = {$alias}.id";
            $where .= " AND {$productAlias}.product_id IN (" . self::placeholders($products) . ")";
            foreach ($products as $product) {
                $params[] = $product;
            }
        }
        if ($owners) {
            $where .= " AND {$alias}.owner_didar_id IN (" . self::placeholders($owners) . ")";
            foreach ($owners as $owner) {
                $params[] = $owner;
            }
        }
        return [$join, $where, $params];
    }

    private static function buildPersonFilter(array $owners, string $alias): array
    {
        if (!$owners) {
            return ['', []];
        }
        return [" AND {$alias}.owner_didar_id IN (" . self::placeholders($owners) . ")", $owners];
    }

    private static function value(PDO $pdo, string $sql, array $params = []): float
    {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return (float)$stmt->fetchColumn();
    }

    private static function row(PDO $pdo, string $sql, array $params = []): array
    {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: [];
    }

    private static function rows(PDO $pdo, string $sql, array $params = []): array
    {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private static function dateSeries(string $from, string $to): array
    {
        $dates = [];
        $cursor = strtotime($from);
        $end = strtotime($to);
        while ($cursor !== false && $end !== false && $cursor <= $end) {
            $dates[] = date('Y-m-d', $cursor);
            $cursor = strtotime('+1 day', $cursor);
            if (count($dates) > 370) {
                break;
            }
        }
        return $dates;
    }

    private static function performanceComparison(PDO $pdo, array $range, string $join, string $dealFilter, array $dealFilterParams, float $currentSales): array
    {
        $days = max(1, count(self::dateSeries($range['from'], $range['to'])));
        $previousTo = date('Y-m-d', strtotime($range['from'] . ' -1 day'));
        $previousFrom = date('Y-m-d', strtotime($previousTo . ' -' . ($days - 1) . ' days'));
        $previous = self::value($pdo, "SELECT COALESCE(SUM(t.amount), 0)
            FROM transactions t INNER JOIN deals d ON d.id = t.deal_id {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}", array_merge([$previousFrom, $previousTo], $dealFilterParams));
        $delta = $currentSales - $previous;
        return [
            'label' => 'مقایسه با بازه قبل',
            'summary' => [
                'total_sales_amount' => [
                    'current' => $currentSales,
                    'previous' => $previous,
                    'delta' => $delta,
                    'delta_percent' => $previous > 0 ? round(($delta / $previous) * 100, 2) : 0,
                ],
            ],
        ];
    }

    private static function lostReasonBreakdown(PDO $pdo, array $range, string $dealFilter, string $join, array $dealFilterParams): array
    {
        $rows = self::rows($pdo, "SELECT COALESCE(NULLIF(d.failure_reason_text, ''), 'نامشخص') AS reason, COUNT(*) AS count
            FROM deals d {$join}
            WHERE d.status = 'Lost' AND date(COALESCE(d.lost_time, d.register_time)) BETWEEN ? AND ? {$dealFilter}
            GROUP BY reason
            ORDER BY count DESC
            LIMIT 4", array_merge([$range['from'], $range['to']], $dealFilterParams));
        foreach ($rows as &$row) {
            $row['count'] = (int)$row['count'];
        }
        return $rows;
    }

    private static function performanceCharts(PDO $pdo, array $range, string $join, string $dealFilter, array $dealFilterParams, string $personFilter, array $personParams): array
    {
        $dailySales = self::rows($pdo, "SELECT date(t.payment_date) AS date, date(t.payment_date) AS payment_date,
                   COALESCE(SUM(t.amount), 0) AS collected_amount, COUNT(DISTINCT d.id) AS deal_count
            FROM transactions t INNER JOIN deals d ON d.id = t.deal_id {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}
            GROUP BY date(t.payment_date)
            ORDER BY date(t.payment_date)", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $dailyActivities = self::rows($pdo, "SELECT date(a.register_date) AS date, COUNT(*) AS activities_count
            FROM activities a
            LEFT JOIN persons p ON p.didar_contact_id = a.contact_didar_id
            WHERE date(a.register_date) BETWEEN ? AND ? {$personFilter}
            GROUP BY date(a.register_date)
            ORDER BY date(a.register_date)", array_merge([$range['from'], $range['to']], $personParams));
        $dailyActivitiesByType = self::rows($pdo, "SELECT date(a.register_date) AS date, COALESCE(a.activity_type_title, 'فعالیت') AS activity_type, COUNT(*) AS activities_count
            FROM activities a
            LEFT JOIN persons p ON p.didar_contact_id = a.contact_didar_id
            WHERE date(a.register_date) BETWEEN ? AND ? {$personFilter}
            GROUP BY date(a.register_date), COALESCE(a.activity_type_title, 'فعالیت')
            ORDER BY date(a.register_date)", array_merge([$range['from'], $range['to']], $personParams));
        $dealStatus = [
            ['status' => 'جاری', 'count' => (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join} WHERE d.status = 'Pending' {$dealFilter}", $dealFilterParams)],
            ['status' => 'دارای فروش', 'count' => (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d INNER JOIN transactions t ON t.deal_id = d.id {$join} WHERE 1=1 {$dealFilter}", $dealFilterParams)],
            ['status' => 'بدون فروش', 'count' => (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join} WHERE d.id NOT IN (SELECT deal_id FROM transactions WHERE deal_id IS NOT NULL) {$dealFilter}", $dealFilterParams)],
            ['status' => 'ناموفق', 'count' => (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join} WHERE d.status = 'Lost' {$dealFilter}", $dealFilterParams)],
        ];
        $productShare = self::rows($pdo, "SELECT dp.product_name_snapshot AS product_name, COUNT(DISTINCT d.id) AS deal_count, COALESCE(SUM(t.amount), 0) AS collected_amount
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            INNER JOIN deal_products dp ON dp.deal_id = d.id
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}
            GROUP BY dp.product_name_snapshot
            ORDER BY collected_amount DESC", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $funnel = [
            ['stage' => 'لیدهای جدید', 'count' => (int)self::value($pdo, "SELECT COUNT(*) FROM persons p WHERE date(p.register_time) BETWEEN ? AND ? {$personFilter}", array_merge([$range['from'], $range['to']], $personParams))],
            ['stage' => 'معامله ثبت‌شده', 'count' => (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join} WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}", array_merge([$range['from'], $range['to']], $dealFilterParams))],
            ['stage' => 'دارای واریزی', 'count' => (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d INNER JOIN transactions t ON t.deal_id = d.id {$join} WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}", array_merge([$range['from'], $range['to']], $dealFilterParams))],
            ['stage' => 'موفق', 'count' => (int)self::value($pdo, "SELECT COUNT(DISTINCT d.id) FROM deals d {$join} WHERE d.status = 'Won' AND date(COALESCE(d.won_time, d.register_time)) BETWEEN ? AND ? {$dealFilter}", array_merge([$range['from'], $range['to']], $dealFilterParams))],
        ];
        return [
            'daily_sales' => $dailySales,
            'daily_sales_previous' => [],
            'daily_activities' => $dailyActivities,
            'daily_activities_previous' => [],
            'daily_activities_by_type' => $dailyActivitiesByType,
            'deal_status_breakdown' => $dealStatus,
            'product_sales_share' => $productShare,
            'conversion_funnel' => $funnel,
        ];
    }

    private static function performanceTabs(PDO $pdo, array $range, string $join, string $dealFilter, array $dealFilterParams, string $personFilter, array $personParams): array
    {
        $activitySummary = self::rows($pdo, "SELECT date(a.register_date) AS date, COALESCE(u.display_name, a.owner_didar_id) AS owner_name, a.owner_didar_id,
                   COUNT(*) AS total_activities,
                   COALESCE(SUM(a.duration), 0) AS total_duration_minutes,
                   ROUND(AVG(COALESCE(a.duration, 0)), 2) AS avg_duration_minutes,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 0 THEN 1 ELSE 0 END) AS activities_new_lead,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 1 THEN 1 ELSE 0 END) AS activities_old_lead,
                   0 AS new_leads_without_activity,
                   0 AS old_leads_without_activity,
                   SUM(CASE WHEN a.result_note LIKE '%پاسخ%' THEN 0 ELSE 0 END) AS no_response_new,
                   0 AS no_response_old,
                   0 AS no_response_total
            FROM activities a
            LEFT JOIN persons p ON p.didar_contact_id = a.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = a.owner_didar_id
            WHERE date(a.register_date) BETWEEN ? AND ? {$personFilter}
            GROUP BY date(a.register_date), a.owner_didar_id
            ORDER BY date DESC, owner_name", array_merge([$range['from'], $range['to']], $personParams));
        $activityTypes = self::rows($pdo, "SELECT date(a.register_date) AS date, COALESCE(u.display_name, a.owner_didar_id) AS owner_name, a.owner_didar_id,
                   COALESCE(a.activity_type_title, 'فعالیت') AS activity_type,
                   COUNT(*) AS activity_count_total,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 0 THEN 1 ELSE 0 END) AS activity_type_new_count,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 1 THEN 1 ELSE 0 END) AS activity_type_old_count
            FROM activities a
            LEFT JOIN persons p ON p.didar_contact_id = a.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = a.owner_didar_id
            WHERE date(a.register_date) BETWEEN ? AND ? {$personFilter}
            GROUP BY date(a.register_date), a.owner_didar_id, COALESCE(a.activity_type_title, 'فعالیت')
            ORDER BY date DESC, owner_name", array_merge([$range['from'], $range['to']], $personParams));
        $dealStatus = self::rows($pdo, "SELECT date(d.register_time) AS date, COALESCE(u.display_name, d.owner_didar_id) AS owner_name, d.owner_didar_id,
                   COUNT(DISTINCT d.id) AS deal_total,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 0 THEN 1 ELSE 0 END) AS deal_new_total,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 1 THEN 1 ELSE 0 END) AS deal_old_total,
                   SUM(CASE WHEN d.status = 'Pending' THEN 1 ELSE 0 END) AS deal_pending_total,
                   SUM(CASE WHEN d.status = 'Pending' AND COALESCE(p.has_previous_purchase, 0) = 0 THEN 1 ELSE 0 END) AS deal_pending_new,
                   SUM(CASE WHEN d.status = 'Pending' AND COALESCE(p.has_previous_purchase, 0) = 1 THEN 1 ELSE 0 END) AS deal_pending_old,
                   SUM(CASE WHEN d.id IN (SELECT deal_id FROM transactions WHERE deal_id IS NOT NULL) THEN 1 ELSE 0 END) AS deal_with_sales_total,
                   SUM(CASE WHEN d.id IN (SELECT deal_id FROM transactions WHERE deal_id IS NOT NULL) AND COALESCE(p.has_previous_purchase, 0) = 0 THEN 1 ELSE 0 END) AS deal_with_sales_new,
                   SUM(CASE WHEN d.id IN (SELECT deal_id FROM transactions WHERE deal_id IS NOT NULL) AND COALESCE(p.has_previous_purchase, 0) = 1 THEN 1 ELSE 0 END) AS deal_with_sales_old,
                   SUM(CASE WHEN d.id NOT IN (SELECT deal_id FROM transactions WHERE deal_id IS NOT NULL) THEN 1 ELSE 0 END) AS deal_without_sales_total,
                   SUM(CASE WHEN d.id NOT IN (SELECT deal_id FROM transactions WHERE deal_id IS NOT NULL) AND COALESCE(p.has_previous_purchase, 0) = 0 THEN 1 ELSE 0 END) AS deal_without_sales_new,
                   SUM(CASE WHEN d.id NOT IN (SELECT deal_id FROM transactions WHERE deal_id IS NOT NULL) AND COALESCE(p.has_previous_purchase, 0) = 1 THEN 1 ELSE 0 END) AS deal_without_sales_old,
                   SUM(CASE WHEN d.status = 'Won' THEN 1 ELSE 0 END) AS deal_won_total,
                   SUM(CASE WHEN d.status = 'Lost' THEN 1 ELSE 0 END) AS deal_lost_total
            FROM deals d
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}
            GROUP BY date(d.register_time), d.owner_didar_id
            ORDER BY date DESC, owner_name", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $leadStatus = self::rows($pdo, "SELECT date(p.register_time) AS date, COALESCE(u.display_name, p.owner_didar_id) AS owner_name, p.owner_didar_id,
                   COUNT(*) AS total_leads,
                   SUM(CASE WHEN p.sale_status = 1 THEN 1 ELSE 0 END) AS leads_with_sales,
                   SUM(CASE WHEN p.sale_status = 1 THEN 1 ELSE 0 END) AS leads_with_new_sales,
                   0 AS leads_with_previous_sales,
                   SUM(CASE WHEN p.sale_status = 0 THEN 1 ELSE 0 END) AS leads_without_sales,
                   SUM(CASE WHEN p.sale_status = 0 THEN 1 ELSE 0 END) AS leads_without_sales_with_current_deal,
                   0 AS leads_without_sales_without_current_deal,
                   COUNT(*) AS new_leads_count
            FROM persons p
            LEFT JOIN users u ON u.didar_user_id = p.owner_didar_id
            WHERE date(p.register_time) BETWEEN ? AND ? {$personFilter}
            GROUP BY date(p.register_time), p.owner_didar_id
            ORDER BY date DESC, owner_name", array_merge([$range['from'], $range['to']], $personParams));
        $paymentStatus = self::rows($pdo, "SELECT date(d.register_time) AS date, COALESCE(u.display_name, d.owner_didar_id) AS owner_name, d.owner_didar_id,
                   SUM(CASE WHEN COALESCE(d.payable_amount, d.final_price, 0) > COALESCE(d.payment_amount, 0) THEN 1 ELSE 0 END) AS outstanding_deals_count,
                   SUM(CASE WHEN COALESCE(d.payment_amount, 0) >= COALESCE(d.payable_amount, d.final_price, 0) AND COALESCE(d.payable_amount, d.final_price, 0) > 0 THEN 1 ELSE 0 END) AS settled_deals_count,
                   COALESCE(SUM(CASE WHEN COALESCE(d.payable_amount, d.final_price, 0) > COALESCE(d.payment_amount, 0) THEN COALESCE(d.payable_amount, d.final_price, 0) - COALESCE(d.payment_amount, 0) ELSE 0 END), 0) AS outstanding_amount_sum,
                   COALESCE(SUM(CASE WHEN COALESCE(d.payment_amount, 0) >= COALESCE(d.payable_amount, d.final_price, 0) THEN COALESCE(d.payable_amount, d.final_price, 0) ELSE 0 END), 0) AS settled_contract_amount_sum,
                   COALESCE(SUM(d.payment_amount), 0) AS payments_sum
            FROM deals d
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}
            GROUP BY date(d.register_time), d.owner_didar_id
            ORDER BY date DESC, owner_name", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $paymentMix = self::rows($pdo, "SELECT date(d.register_time) AS date, COALESCE(u.display_name, d.owner_didar_id) AS owner_name, d.owner_didar_id,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 0 AND d.payment_amount > 0 THEN 1 ELSE 0 END) AS new_leads_with_payment,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 0 AND d.payment_amount <= 0 THEN 1 ELSE 0 END) AS new_leads_without_payment,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 0 AND d.status = 'Lost' THEN 1 ELSE 0 END) AS new_leads_failed,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 1 AND d.payment_amount > 0 THEN 1 ELSE 0 END) AS old_leads_with_payment,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 1 AND d.payment_amount <= 0 THEN 1 ELSE 0 END) AS old_leads_without_payment,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 1 AND d.status = 'Lost' THEN 1 ELSE 0 END) AS old_leads_failed
            FROM deals d
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}
            GROUP BY date(d.register_time), d.owner_didar_id
            ORDER BY date DESC, owner_name", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $conversionStatus = self::conversionRows($dealStatus);
        $productRows = self::productRows($pdo, $range, $dealFilter, $dealFilterParams);
        $failureRows = self::rows($pdo, "SELECT date(COALESCE(d.lost_time, d.register_time)) AS date, COALESCE(u.display_name, d.owner_didar_id) AS owner_name, d.owner_didar_id,
                   COALESCE(NULLIF(d.failure_reason_text, ''), 'نامشخص') AS failure_reason,
                   COUNT(*) AS failed_without_current_count,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 0 THEN 1 ELSE 0 END) AS failed_new_count,
                   SUM(CASE WHEN COALESCE(p.has_previous_purchase, 0) = 1 THEN 1 ELSE 0 END) AS failed_old_count
            FROM deals d
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$join}
            WHERE d.status = 'Lost' AND date(COALESCE(d.lost_time, d.register_time)) BETWEEN ? AND ? {$dealFilter}
            GROUP BY date(COALESCE(d.lost_time, d.register_time)), d.owner_didar_id, COALESCE(NULLIF(d.failure_reason_text, ''), 'نامشخص')
            ORDER BY date DESC, owner_name", array_merge([$range['from'], $range['to']], $dealFilterParams));

        return [
            'activity' => ['activity_summary' => $activitySummary, 'activity_type_details' => $activityTypes],
            'sales_deals' => ['deal_status' => $dealStatus, 'lead_status' => $leadStatus, 'conversion_status' => $conversionStatus],
            'payments' => ['payment_mix' => $paymentMix, 'payment_status' => $paymentStatus],
            'products' => [
                'product_status' => $productRows,
                'product_sold_by_owner_day' => $productRows,
                'product_requests_by_owner_day' => self::productRequestRows($productRows),
                'product_failed_by_owner_day' => self::productFailedRows($productRows),
            ],
            'failure_reasons' => ['failure_reasons' => $failureRows],
        ];
    }

    private static function conversionRows(array $dealStatusRows): array
    {
        $rows = [];
        foreach ($dealStatusRows as $row) {
            $total = (float)($row['deal_total'] ?? 0);
            $sales = (float)($row['deal_with_sales_total'] ?? 0);
            $newTotal = (float)($row['deal_new_total'] ?? 0);
            $newSales = (float)($row['deal_with_sales_new'] ?? 0);
            $oldTotal = (float)($row['deal_old_total'] ?? 0);
            $oldSales = (float)($row['deal_with_sales_old'] ?? 0);
            $rows[] = [
                'date' => $row['date'] ?? '',
                'owner_name' => $row['owner_name'] ?? '',
                'owner_didar_id' => $row['owner_didar_id'] ?? '',
                'owner_range_conversion_rate' => $total > 0 ? round(($sales / $total) * 100, 2) : 0,
                'total_deals_count' => (int)$total,
                'total_new_deals_count' => (int)$newTotal,
                'total_old_deals_count' => (int)$oldTotal,
                'conversion_rate_total' => $total > 0 ? round(($sales / $total) * 100, 2) : 0,
                'non_conversion_rate_total' => $total > 0 ? round((1 - ($sales / $total)) * 100, 2) : 0,
                'conversion_rate_new' => $newTotal > 0 ? round(($newSales / $newTotal) * 100, 2) : 0,
                'non_conversion_rate_new' => $newTotal > 0 ? round((1 - ($newSales / $newTotal)) * 100, 2) : 0,
                'conversion_rate_old' => $oldTotal > 0 ? round(($oldSales / $oldTotal) * 100, 2) : 0,
                'non_conversion_rate_old' => $oldTotal > 0 ? round((1 - ($oldSales / $oldTotal)) * 100, 2) : 0,
                'sales_total_count' => (int)$sales,
                'sales_new_count' => (int)$newSales,
                'sales_old_count' => (int)$oldSales,
            ];
        }
        return $rows;
    }

    private static function productRows(PDO $pdo, array $range, string $dealFilter, array $dealFilterParams): array
    {
        return self::rows($pdo, "SELECT date(d.register_time) AS date, COALESCE(u.display_name, d.owner_didar_id) AS owner_name, d.owner_didar_id,
                   dp.product_name_snapshot AS product_name,
                   COUNT(DISTINCT CASE WHEN d.payment_amount > 0 THEN d.id END) AS sales_total_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount > 0 AND COALESCE(p.has_previous_purchase, 0) = 0 THEN d.id END) AS sales_new_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount > 0 AND COALESCE(p.has_previous_purchase, 0) = 1 THEN d.id END) AS sales_old_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount > 0 AND COALESCE(p.has_previous_purchase, 0) = 0 THEN d.id END) AS sold_new_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount > 0 AND COALESCE(p.has_previous_purchase, 0) = 1 THEN d.id END) AS sold_old_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount > 0 THEN d.id END) AS sold_total_count,
                   COUNT(DISTINCT d.id) AS requested_total_count,
                   COUNT(DISTINCT CASE WHEN COALESCE(p.has_previous_purchase, 0) = 0 THEN d.id END) AS requested_new_count,
                   COUNT(DISTINCT CASE WHEN COALESCE(p.has_previous_purchase, 0) = 1 THEN d.id END) AS requested_old_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount <= 0 THEN d.id END) AS unsold_total_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount <= 0 AND COALESCE(p.has_previous_purchase, 0) = 0 THEN d.id END) AS failed_new_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount <= 0 AND COALESCE(p.has_previous_purchase, 0) = 1 THEN d.id END) AS failed_old_count,
                   COUNT(DISTINCT CASE WHEN d.payment_amount <= 0 THEN d.id END) AS failed_total_count
            FROM deals d
            INNER JOIN deal_products dp ON dp.deal_id = d.id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}
            GROUP BY date(d.register_time), d.owner_didar_id, dp.product_name_snapshot
            ORDER BY date DESC, owner_name, product_name", array_merge([$range['from'], $range['to']], $dealFilterParams));
    }

    private static function productRequestRows(array $productRows): array
    {
        return $productRows;
    }

    private static function productFailedRows(array $productRows): array
    {
        return $productRows;
    }

    private static function performanceCeoReport(PDO $pdo, array $range, string $join, string $dealFilter, array $dealFilterParams, array $summary): array
    {
        $cashRows = [
            ['key' => 'sales_deposit', 'label' => 'واریزی فروش', 'amount' => $summary['payment_registered_in_range_amount'], 'deal_count' => $summary['payment_registered_in_range_deal_count'], 'payment_count' => $summary['payment_registered_in_range_receipt_count']],
            ['key' => 'lost_deposit', 'label' => 'واریزی معاملات ناموفق', 'amount' => $summary['lost_deposit_amount'], 'deal_count' => $summary['lost_deposit_deal_count'], 'payment_count' => $summary['lost_deposit_receipt_count']],
            ['key' => 'balance_deposit', 'label' => 'تسویه مانده حساب', 'amount' => $summary['settled_collected_amount'], 'deal_count' => $summary['settled_count'], 'payment_count' => $summary['settled_count']],
        ];
        $productSales = self::rows($pdo, "SELECT dp.product_name_snapshot AS product_name,
                   COUNT(DISTINCT d.id) AS sold_count,
                   COUNT(DISTINCT d.id) AS deal_count,
                   COALESCE(SUM(t.amount), 0) AS amount,
                   COALESCE(SUM(t.amount), 0) AS payment_amount,
                   COUNT(DISTINCT CASE WHEN COALESCE(p.acquaintance_source, '') = 'وب‌سایت' THEN d.id END) AS crm_lead_count,
                   COUNT(DISTINCT CASE WHEN COALESCE(p.acquaintance_source, '') <> 'وب‌سایت' THEN d.id END) AS personal_lead_count,
                   0 AS sudran_lead_count
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            INNER JOIN deal_products dp ON dp.deal_id = d.id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}
            GROUP BY dp.product_name_snapshot
            ORDER BY amount DESC", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $agentSales = self::rows($pdo, "SELECT COALESCE(u.display_name, d.owner_didar_id) AS owner_name, d.owner_didar_id,
                   COUNT(DISTINCT d.id) AS sold_count, COALESCE(SUM(t.amount), 0) AS amount
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            LEFT JOIN users u ON u.didar_user_id = d.owner_didar_id
            {$join}
            WHERE date(t.payment_date) BETWEEN ? AND ? {$dealFilter}
            GROUP BY d.owner_didar_id
            ORDER BY amount DESC", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $marketing = self::rows($pdo, "SELECT COALESCE(NULLIF(d.acquaintance_source, ''), 'نامشخص') AS acquaintance_source,
                   COUNT(DISTINCT d.id) AS deal_count, COALESCE(SUM(d.final_price), 0) AS amount
            FROM deals d {$join}
            WHERE date(d.register_time) BETWEEN ? AND ? {$dealFilter}
            GROUP BY COALESCE(NULLIF(d.acquaintance_source, ''), 'نامشخص')
            ORDER BY deal_count DESC", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $chequeRows = self::rows($pdo, "SELECT
                   COALESCE(t.collection_status, 'pending') AS status,
                   COUNT(*) AS count,
                   COALESCE(SUM(t.amount), 0) AS amount
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            {$join}
            WHERE t.method = 'چک' AND date(COALESCE(t.check_due_date, t.payment_date)) BETWEEN ? AND ? {$dealFilter}
            GROUP BY COALESCE(t.collection_status, 'pending')
            ORDER BY amount DESC", array_merge([$range['from'], $range['to']], $dealFilterParams));
        $chequeCards = [];
        $chequeLabels = ['collected' => 'وصول‌شده', 'returned' => 'برگشتی', 'blataklif' => 'بلاتکلیف', 'pending' => 'در انتظار'];
        foreach ($chequeRows as $row) {
            $key = (string)($row['status'] ?? 'pending');
            $chequeCards[] = [
                'key' => $key,
                'label' => $chequeLabels[$key] ?? $key,
                'count' => (int)($row['count'] ?? 0),
                'amount' => (float)($row['amount'] ?? 0),
                'metric_key' => 'cheque_state',
            ];
        }
        $chequeTotalCount = 0;
        $chequeTotalAmount = 0.0;
        foreach ($chequeCards as $card) {
            $chequeTotalCount += (int)$card['count'];
            $chequeTotalAmount += (float)$card['amount'];
        }
        return [
            'summary' => [
                'total_deposits_amount' => $summary['payment_registered_in_range_amount'],
                'total_deposits_deal_count' => $summary['payment_registered_in_range_deal_count'],
                'total_deposits_receipt_count' => $summary['payment_registered_in_range_receipt_count'],
                'outstanding_balance_amount' => $summary['outstanding_amount'],
                'outstanding_balance_deal_count' => $summary['outstanding_count'],
                'all_cheques_count' => $chequeTotalCount,
                'all_cheques_amount' => $chequeTotalAmount,
            ],
            'cash_composition' => $cashRows,
            'deposit_cohort' => [
                ['key' => 'registered_range_deposit', 'label' => 'معاملات ثبت‌شده در بازه', 'amount' => $summary['payment_registered_in_range_amount'], 'deal_count' => $summary['payment_registered_in_range_deal_count']],
                ['key' => 'outside_range_deposit', 'label' => 'ثبت خارج بازه با واریزی در بازه', 'amount' => $summary['payment_outside_registered_first_payment_amount'], 'deal_count' => $summary['payment_outside_registered_first_payment_deal_count']],
            ],
            'product_sales' => $productSales,
            'marketing_sources' => $marketing,
            'registered_deals_sources' => $marketing,
            'agent_sales' => $agentSales,
            'access_grants' => [],
            'outstanding_balances' => [
                'total' => ['label' => 'کل مانده حساب‌ها', 'amount' => $summary['outstanding_amount'], 'deal_count' => $summary['outstanding_count']],
                'months' => [['key' => date('Y-m', strtotime($range['to'])), 'label' => 'ماه جاری', 'amount' => $summary['outstanding_amount'], 'deal_count' => $summary['outstanding_count']]],
            ],
            'cheques' => ['total' => ['label' => 'کل چک‌ها', 'count' => $chequeTotalCount, 'amount' => $chequeTotalAmount], 'cards' => $chequeCards],
            'deal_status' => [
                'cards' => [
                    ['key' => 'won', 'label' => 'موفق', 'count' => $summary['won_count'], 'amount' => $summary['won_amount']],
                    ['key' => 'open', 'label' => 'جاری', 'count' => $summary['open_pipeline_count'], 'amount' => $summary['open_pipeline_amount']],
                    ['key' => 'lost', 'label' => 'ناموفق', 'count' => $summary['lost_count'], 'amount' => $summary['lost_amount']],
                ],
                'lead_mix' => [],
                'previous_success' => [],
            ],
        ];
    }

    private static function commonFilters(string $alias): array
    {
        $where = ['1=1'];
        $params = [];
        $search = trim((string)($_REQUEST['search'] ?? ''));
        if ($search !== '') {
            $where[] = "({$alias}.first_name LIKE ? OR {$alias}.last_name LIKE ? OR {$alias}.mobile_phone LIKE ? OR {$alias}.customer_code LIKE ?)";
            $like = '%' . $search . '%';
            array_push($params, $like, $like, $like, $like);
        }
        $owner = trim((string)($_REQUEST['owner_id'] ?? $_REQUEST['owner_didar_id'] ?? ''));
        if ($owner !== '') {
            $where[] = "{$alias}.owner_didar_id = ?";
            $params[] = $owner;
        }
        return [implode(' AND ', $where), $params];
    }

    private static function dealFilters(string $alias): array
    {
        $whereParts = ['1=1'];
        $params = [];
        $search = trim((string)($_REQUEST['search'] ?? ''));
        if ($search !== '') {
            $whereParts[] = "({$alias}.title LIKE ? OR {$alias}.code LIKE ? OR {$alias}.invoice_code LIKE ? OR {$alias}.contact_didar_id LIKE ?)";
            $like = '%' . $search . '%';
            array_push($params, $like, $like, $like, $like);
        }
        $owner = trim((string)($_REQUEST['owner_id'] ?? $_REQUEST['owner_didar_id'] ?? ''));
        if ($owner !== '') {
            $whereParts[] = "{$alias}.owner_didar_id = ?";
            $params[] = $owner;
        }
        $where = implode(' AND ', $whereParts);
        $status = trim((string)($_REQUEST['status'] ?? 'all'));
        if ($status !== '' && $status !== 'all') {
            $where .= " AND {$alias}.status = ?";
            $params[] = $status;
        }
        $stage = trim((string)($_REQUEST['pipeline_stage'] ?? 'all'));
        if ($stage !== '' && $stage !== 'all') {
            $where .= " AND {$alias}.pipeline_stage = ?";
            $params[] = $stage;
        }
        return [$where, $params];
    }

    private static function keyValue(array $rows, string $key, string $value): array
    {
        $out = [];
        foreach ($rows as $row) {
            $out[(string)($row[$key] ?? '')] = (int)($row[$value] ?? 0);
        }
        return $out;
    }

    private static function publicUser(array $user): array
    {
        unset($user['password'], $user['api_key']);
        return $user;
    }
}
