<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\Deal;
use App\Utils\Logger;
use PDO;
use PDOException;

class DealRepository
{
    private $db;
    private ?bool $hasPipelineBucketCache = null;

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

    private function hasPipelineBucketColumns(): bool
    {
        if ($this->hasPipelineBucketCache === null) {
            $this->hasPipelineBucketCache = $this->db->hasColumn('deals', 'pipeline_bucket');
        }
        return $this->hasPipelineBucketCache;
    }

    private function applyPipelineBucketFilter(array &$where, array &$params, array $filters, string $alias = ''): void
    {
        if (!$this->hasPipelineBucketColumns()) {
            return;
        }

        $prefix = $alias !== '' ? rtrim($alias, '.') . '.' : '';
        $bucket = isset($filters['pipeline_bucket']) ? trim((string)$filters['pipeline_bucket']) : '';
        // PERF (phase 2): pipeline_bucket is NOT NULL DEFAULT 'active' and migration-backfilled,
        // so the COALESCE wrapper is redundant. Dropping it lets the bucket filter use indexes.
        if ($bucket !== '' && $bucket !== 'all') {
            $where[] = "{$prefix}pipeline_bucket = ?";
            $params[] = $bucket;
            return;
        }

        if (!empty($filters['exclude_parked'])) {
            $where[] = "{$prefix}pipeline_bucket <> 'parked'";
        }
    }

    private function applyRequestedServiceFilter(array &$where, array &$params, array $filters, string $alias = ''): void
    {
        $service = isset($filters['requested_service']) ? trim((string)$filters['requested_service']) : '';
        if ($service === '') {
            return;
        }

        $prefix = $alias !== '' ? rtrim($alias, '.') . '.' : '';
        $where[] = "COALESCE({$prefix}requested_services, '') LIKE ?";
        $params[] = '%' . $service . '%';
    }

    public function findByDidarId($didarId)
    {
        if (empty($didarId)) return null;
        $pdo = $this->db->getPdo();
        
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $ownerNameField = $hasDisplayName 
            ? "u.display_name" 
            : "COALESCE(CONCAT(u.first_name, ' ', u.last_name), '')";
        $hasCreatedByDidarId = $this->db->hasColumn('deals', 'created_by_didar_id');
        $hasCreatedByName = $this->db->hasColumn('deals', 'created_by_name');
        $creatorDidarExpr = $hasCreatedByDidarId
            ? "COALESCE(d.created_by_didar_id, (SELECT al.actor_didar_id FROM audit_logs al WHERE al.entity_type = 'deal' AND al.entity_id = d.didar_deal_id ORDER BY al.id ASC LIMIT 1), (SELECT fal.actor_didar_id FROM field_audit_logs fal WHERE fal.entity_type = 'deal' AND fal.entity_id = d.didar_deal_id ORDER BY fal.id ASC LIMIT 1), '')"
            : "COALESCE((SELECT al.actor_didar_id FROM audit_logs al WHERE al.entity_type = 'deal' AND al.entity_id = d.didar_deal_id ORDER BY al.id ASC LIMIT 1), (SELECT fal.actor_didar_id FROM field_audit_logs fal WHERE fal.entity_type = 'deal' AND fal.entity_id = d.didar_deal_id ORDER BY fal.id ASC LIMIT 1), '')";
        $creatorNameExpr = $hasCreatedByName
            ? "COALESCE(d.created_by_name, (SELECT al.actor_name FROM audit_logs al WHERE al.entity_type = 'deal' AND al.entity_id = d.didar_deal_id ORDER BY al.id ASC LIMIT 1), (SELECT fal.actor_name FROM field_audit_logs fal WHERE fal.entity_type = 'deal' AND fal.entity_id = d.didar_deal_id ORDER BY fal.id ASC LIMIT 1), '')"
            : "COALESCE((SELECT al.actor_name FROM audit_logs al WHERE al.entity_type = 'deal' AND al.entity_id = d.didar_deal_id ORDER BY al.id ASC LIMIT 1), (SELECT fal.actor_name FROM field_audit_logs fal WHERE fal.entity_type = 'deal' AND fal.entity_id = d.didar_deal_id ORDER BY fal.id ASC LIMIT 1), '')";

        // Join with persons to get the CORRECT local person information
        // Use person_id if available, otherwise fallback to didar_contact_id
        // Explicit COLLATE used to prevent "Illegal mix of collations" error
        $stmt = $pdo->prepare("SELECT d.*, 
                               TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, ''))) as contact_name,
                               p.mobile_phone as contact_mobile,
                               p.id as local_person_id,
                               $ownerNameField as owner_name,
                               $creatorDidarExpr as created_by_didar_id,
                               $creatorNameExpr as created_by_name
                               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.didar_deal_id = ? LIMIT 1");
        $stmt->execute([$didarId]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return $data ? new Deal($data) : null;
    }

    public function findInternalIdByDidarId($didarId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT id FROM deals WHERE didar_deal_id = ? LIMIT 1");
        $stmt->execute([$didarId]);
        return $stmt->fetchColumn();
    }

    public function findById($id)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM deals WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return $data ? new Deal($data) : null;
    }

    public function findByContactId($contactId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM deals WHERE contact_didar_id = ? ORDER BY register_time DESC");
        $stmt->execute([$contactId]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(function($data) {
            return new Deal($data);
        }, $results);
    }

    public function findByOwnerDidarId(string $ownerDidarId): array
    {
        $ownerDidarId = trim($ownerDidarId);
        if ($ownerDidarId === '') {
            return [];
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM deals WHERE owner_didar_id = ? ORDER BY register_time DESC, id DESC");
        $stmt->execute([$ownerDidarId]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return array_map(function ($data) {
            return new Deal($data);
        }, $results);
    }

    public function findByContactIdLegacy($contactId, $legacy = false, $limit = 50)
    {
        $pdo = $this->db->getPdo();
        $hasLegacy = $this->db->hasColumn('deals', 'is_legacy_didar');
        if (!$hasLegacy && $legacy) {
            return [];
        }

        $sql = "SELECT * FROM deals WHERE contact_didar_id = ?";
        $params = [$contactId];

        if ($hasLegacy) {
            $sql .= " AND COALESCE(is_legacy_didar, 0) = ?";
            $params[] = $legacy ? 1 : 0;
        }

        $limit = (int)$limit;
        if ($limit <= 0) {
            $limit = 50;
        }
        $sql .= " ORDER BY register_time DESC LIMIT ?";
        $params[] = $limit;
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(function($data) {
            return new Deal($data);
        }, $results);
    }

    public function save(Deal $deal)
    {
        $pdo = $this->db->getPdo();

        // Optional columns (backward compatibility)
	        $hasFinancial = $this->db->hasColumn('deals', 'financial_level');
	        $hasAsset = $this->db->hasColumn('deals', 'asset_estimation');
	        $hasIncome = $this->db->hasColumn('deals', 'income_estimation');
	        $hasPrevPurchase = $this->db->hasColumn('deals', 'has_previous_purchase');
	        $hasFailureCode = $this->db->hasColumn('deals', 'failure_reason_code');
	        $hasFailureText = $this->db->hasColumn('deals', 'failure_reason_text');
	        $hasFailureDesc = $this->db->hasColumn('deals', 'failure_reason_description');
	        $hasRefundAmount = $this->db->hasColumn('deals', 'refund_amount');
        $hasRefundDescription = $this->db->hasColumn('deals', 'refund_description');
        $hasInvoiceCode = $this->db->hasColumn('deals', 'invoice_code');
        $hasPersonId = $this->db->hasColumn('deals', 'person_id');
        $hasCreatedByDidarId = $this->db->hasColumn('deals', 'created_by_didar_id');
        $hasCreatedByName = $this->db->hasColumn('deals', 'created_by_name');
        $hasCreatedVia = $this->db->hasColumn('deals', 'created_via');
        $hasAcquaintanceSource = $this->db->hasColumn('deals', 'acquaintance_source');
        $hasAcquaintanceDetail = $this->db->hasColumn('deals', 'acquaintance_detail');
        $hasContentTopic = $this->db->hasColumn('deals', 'content_topic');
        $hasPipelineBucket = $this->db->hasColumn('deals', 'pipeline_bucket');
        $hasParkedFromStage = $this->db->hasColumn('deals', 'parked_from_stage');
        $hasParkedAt = $this->db->hasColumn('deals', 'parked_at');
        $hasParkedByDidarId = $this->db->hasColumn('deals', 'parked_by_didar_id');
        $hasParkedReason = $this->db->hasColumn('deals', 'parked_reason');
        $hasParkCount = $this->db->hasColumn('deals', 'park_count');

        $columns = [
            'didar_deal_id', 'owner_didar_id', 'contact_didar_id', 'title', 'code', 'status',
            'pipeline_stage', 'pipeline_stage_id', 'pipeline_stage_title', 'estimated_price',
            'final_price', 'probability', 'is_paid', 'payment_short_link', 'service_name',
            'service_cost', 'price_list_code', 'has_discount', 'discount_type', 'discount_occasion',
            'discount_amount', 'payable_amount', 'payment_conditions', 'payment_amount', 'payment_method',
            'payment_description', 'payments', 'requested_services'
        ];
        $values = [
            $deal->didar_deal_id,
            $deal->owner_didar_id,
            $deal->contact_didar_id,
            $deal->title,
            $deal->code,
            $deal->status,
            $deal->pipeline_stage,
            $deal->pipeline_stage_id,
            $deal->pipeline_stage_title,
            $deal->estimated_price,
            $deal->final_price,
            $deal->probability,
            $deal->is_paid ?? 0,
            $deal->payment_short_link,
            $deal->service_name,
            $deal->service_cost,
            $deal->price_list_code,
            $deal->has_discount ?? 0,
            $deal->discount_type,
            $deal->discount_occasion,
            $deal->discount_amount ?? 0,
            $deal->payable_amount,
            $deal->payment_conditions,
            $deal->payment_amount ?? 0,
            $deal->payment_method,
            $deal->payment_description,
            $deal->payments,
            $deal->requested_services
        ];

        if ($hasPersonId) {
            $columns[] = 'person_id';
            $values[] = $deal->local_person_id;
        }
        if ($hasInvoiceCode && $deal->invoice_code !== null) {
            $columns[] = 'invoice_code';
            $values[] = $deal->invoice_code;
        }
        if ($hasCreatedByDidarId) {
            $columns[] = 'created_by_didar_id';
            $values[] = $deal->created_by_didar_id;
        }
        if ($hasCreatedByName) {
            $columns[] = 'created_by_name';
            $values[] = $deal->created_by_name;
        }
        if ($hasCreatedVia) {
            $columns[] = 'created_via';
            $values[] = $deal->created_via;
        }
        if ($hasAcquaintanceSource) {
            $columns[] = 'acquaintance_source';
            $values[] = $deal->acquaintance_source;
        }
        if ($hasAcquaintanceDetail) {
            $columns[] = 'acquaintance_detail';
            $values[] = $deal->acquaintance_detail;
        }
        if ($hasContentTopic) {
            $columns[] = 'content_topic';
            $values[] = $deal->content_topic;
        }
        if ($hasPipelineBucket) {
            $columns[] = 'pipeline_bucket';
            $values[] = $deal->pipeline_bucket ?: 'active';
        }
        if ($hasParkedFromStage) {
            $columns[] = 'parked_from_stage';
            $values[] = $deal->parked_from_stage;
        }
        if ($hasParkedAt) {
            $columns[] = 'parked_at';
            $values[] = $deal->parked_at;
        }
        if ($hasParkedByDidarId) {
            $columns[] = 'parked_by_didar_id';
            $values[] = $deal->parked_by_didar_id;
        }
        if ($hasParkedReason) {
            $columns[] = 'parked_reason';
            $values[] = $deal->parked_reason;
        }
        if ($hasParkCount) {
            $columns[] = 'park_count';
            $values[] = max(0, (int)($deal->park_count ?? 0));
        }

        if ($hasFinancial) {
            $columns[] = 'financial_level';
            $values[] = $deal->financial_level;
        }
        if ($hasAsset) {
            $columns[] = 'asset_estimation';
            $values[] = $deal->asset_estimation;
        }
        if ($hasIncome) {
            $columns[] = 'income_estimation';
            $values[] = $deal->income_estimation;
        }
	        if ($hasPrevPurchase) {
	            $columns[] = 'has_previous_purchase';
	            $values[] = $deal->has_previous_purchase;
	        }
	        if ($hasFailureCode) {
	            $columns[] = 'failure_reason_code';
	            $values[] = $deal->failure_reason_code;
	        }
	        if ($hasFailureText) {
	            $columns[] = 'failure_reason_text';
	            $values[] = $deal->failure_reason_text;
	        }
	        if ($hasFailureDesc) {
	            $columns[] = 'failure_reason_description';
	            $values[] = $deal->failure_reason_description;
	        }
	        if ($hasRefundAmount) {
	            $columns[] = 'refund_amount';
	            $values[] = $deal->refund_amount;
	        }
	        if ($hasRefundDescription) {
	            $columns[] = 'refund_description';
	            $values[] = $deal->refund_description;
	        }

        $columns = array_merge($columns, [
            'city', 'job_title', 'job_description', 'acquaintance_duration', 'extra_info',
            'register_time', 'last_update_time', 'won_time', 'lost_time',
            'visibility_type', 'last_sync', 'raw_json'
        ]);
        $values = array_merge($values, [
            $deal->city,
            $deal->job_title,
            $deal->job_description,
            $deal->acquaintance_duration,
            $deal->extra_info,
            $deal->register_time,
            $deal->last_update_time,
            $deal->won_time,
            $deal->lost_time,
            $deal->visibility_type,
            $deal->last_sync,
            $deal->raw_json
        ]);

        $placeholders = implode(', ', array_fill(0, count($columns), '?'));
        $colList = implode(', ', $columns);

        $updateParts = [];
        foreach ($columns as $col) {
            if (in_array($col, ['didar_deal_id', 'register_time', 'created_by_didar_id', 'created_by_name', 'created_via', 'park_count'], true)) continue;
            $updateParts[] = "$col = VALUES($col)";
        }
        $updateSql = implode(', ', $updateParts);

        $sql = "INSERT INTO deals ($colList) VALUES ($placeholders)
                ON DUPLICATE KEY UPDATE $updateSql";

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

        return $pdo->lastInsertId();
    }

	    public function updateStatus($dealId, $status, $timeField = null)
	    {
	        $pdo = $this->db->getPdo();
        if ($timeField) {
            $sql = "UPDATE deals SET status = ?, $timeField = NOW(), last_sync = NOW() WHERE didar_deal_id = ?";
        } else {
            $sql = "UPDATE deals SET status = ?, last_sync = NOW() WHERE didar_deal_id = ?";
        }
	        $stmt = $pdo->prepare($sql);
	        return $stmt->execute([$status, $dealId]);
	    }

	    public function updateFailureReason(string $dealId, ?string $code, ?string $text, ?string $description): bool
	    {
	        $pdo = $this->db->getPdo();

	        $setParts = [];
	        $params = [];

	        if ($this->db->hasColumn('deals', 'failure_reason_code')) {
	            $setParts[] = "failure_reason_code = ?";
	            $params[] = $code;
	        }
	        if ($this->db->hasColumn('deals', 'failure_reason_text')) {
	            $setParts[] = "failure_reason_text = ?";
	            $params[] = $text;
	        }
	        if ($this->db->hasColumn('deals', 'failure_reason_description')) {
	            $setParts[] = "failure_reason_description = ?";
	            $params[] = $description;
	        }

	        if (empty($setParts)) {
	            return false;
	        }

	        $params[] = $dealId;
	        $sql = "UPDATE deals SET " . implode(', ', $setParts) . ", last_sync = NOW() WHERE didar_deal_id = ?";
	        $stmt = $pdo->prepare($sql);
	        return $stmt->execute($params);
	    }

    public function delete($dealId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM deals WHERE didar_deal_id = ?");
        return $stmt->execute([$dealId]);
    }

    public function deleteByContactId(string $contactId): int
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM deals WHERE contact_didar_id = ?");
        $stmt->execute([$contactId]);
        return (int)$stmt->rowCount();
    }

    public function updatePipelineStage($dealId, $stage)
    {
        $pdo = $this->db->getPdo();
        // A deliberate stage change (deal edit, activity submission, completion/failure)
        // unparks the deal so it returns to the active pipeline and is counted in the active
        // dashboard stages. park_count is preserved as historical record. Mirrors the
        // auto-unpark in DealService::saveDeal so the activity path stays consistent.
        if ($this->hasPipelineBucketColumns()) {
            $stmt = $pdo->prepare(
                "UPDATE deals
                 SET pipeline_stage = ?,
                     pipeline_bucket = 'active',
                     parked_from_stage = NULL,
                     parked_at = NULL,
                     parked_by_didar_id = NULL,
                     parked_reason = NULL,
                     last_sync = NOW()
                 WHERE didar_deal_id = ?"
            );
            return $stmt->execute([$stage, $dealId]);
        }
        $stmt = $pdo->prepare("UPDATE deals SET pipeline_stage = ?, last_sync = NOW() WHERE didar_deal_id = ?");
        return $stmt->execute([$stage, $dealId]);
    }

    public function moveToParked(string $dealId, string $fromStage, string $actorDidarId = '', string $reason = ''): bool
    {
        if (!$this->hasPipelineBucketColumns()) {
            return false;
        }

        $pdo = $this->db->getPdo();
        $setParts = [
            "pipeline_bucket = 'parked'",
            "parked_from_stage = ?",
            "parked_at = NOW()",
            "last_sync = NOW()",
        ];
        $params = [$fromStage];
        if ($this->db->hasColumn('deals', 'parked_by_didar_id')) {
            $setParts[] = "parked_by_didar_id = ?";
            $params[] = $actorDidarId !== '' ? $actorDidarId : null;
        }
        if ($this->db->hasColumn('deals', 'parked_reason')) {
            $setParts[] = "parked_reason = ?";
            $params[] = $reason !== '' ? $reason : null;
        }
        if ($this->db->hasColumn('deals', 'park_count')) {
            $setParts[] = "park_count = COALESCE(park_count, 0) + 1";
        }
        $params[] = $dealId;
        $stmt = $pdo->prepare("UPDATE deals SET " . implode(', ', $setParts) . " WHERE didar_deal_id = ?");
        return $stmt->execute($params);
    }

    public function moveStageDealsToParked(string $stage, string $actorDidarId = '', string $reason = '', array $filters = []): int
    {
        if (!$this->hasPipelineBucketColumns()) {
            return 0;
        }

        $stage = trim($stage);
        if ($stage === '') {
            return 0;
        }

        $pdo = $this->db->getPdo();
        $where = [
            "pipeline_stage = ?",
            "status = 'Pending'",
            "COALESCE(pipeline_bucket, 'active') <> 'parked'",
        ];
        $whereParams = [$stage];

        if (array_key_exists('legacy', $filters)) {
            if ($filters['legacy'] === '0' || $filters['legacy'] === 0 || $filters['legacy'] === false) {
                $where[] = "COALESCE(is_legacy_didar, 0) = 0";
            } elseif ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true) {
                $where[] = "COALESCE(is_legacy_didar, 0) = 1";
            }
        }

        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) {
                return trim($v) !== '';
            }));
            if (!empty($ownerIds)) {
                $where[] = "owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $whereParams = array_merge($whereParams, $ownerIds);
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = "owner_didar_id = ?";
            $whereParams[] = (string)$filters['owner_didar_id'];
        }

        $setParts = [
            "pipeline_bucket = 'parked'",
            "parked_from_stage = pipeline_stage",
            "parked_at = NOW()",
            "last_sync = NOW()",
        ];
        $setParams = [];
        if ($this->db->hasColumn('deals', 'parked_by_didar_id')) {
            $setParts[] = "parked_by_didar_id = ?";
            $setParams[] = $actorDidarId !== '' ? $actorDidarId : null;
        }
        if ($this->db->hasColumn('deals', 'parked_reason')) {
            $setParts[] = "parked_reason = ?";
            $setParams[] = $reason !== '' ? $reason : null;
        }
        if ($this->db->hasColumn('deals', 'park_count')) {
            $setParts[] = "park_count = COALESCE(park_count, 0) + 1";
        }

        $sql = "UPDATE deals SET " . implode(', ', $setParts) . " WHERE " . implode(' AND ', $where);
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_merge($setParams, $whereParams));
        return (int)$stmt->rowCount();
    }

    public function getStageParkingSummary(string $stage, array $filters = []): array
    {
        $stage = trim($stage);
        if ($stage === '') {
            return [];
        }

        $pdo = $this->db->getPdo();
        $hasLegacy = $this->db->hasColumn('deals', 'is_legacy_didar');
        $legacyExpr = $hasLegacy ? "COALESCE(is_legacy_didar, 0)" : "0";
        $where = ["pipeline_stage = ?"];
        $params = [$stage];

        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) {
                return trim($v) !== '';
            }));
            if (!empty($ownerIds)) {
                $where[] = "owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = "owner_didar_id = ?";
            $params[] = (string)$filters['owner_didar_id'];
        }

        $whereSql = implode(' AND ', $where);
        $sql = "
            SELECT
                COUNT(*) AS total_in_stage,
                SUM(CASE WHEN status = 'Pending' AND COALESCE(pipeline_bucket, 'active') <> 'parked' AND {$legacyExpr} = 0 THEN 1 ELSE 0 END) AS eligible_pending,
                SUM(CASE WHEN status = 'Pending' AND COALESCE(pipeline_bucket, 'active') = 'parked' AND {$legacyExpr} = 0 THEN 1 ELSE 0 END) AS already_parked_pending,
                SUM(CASE WHEN status <> 'Pending' AND COALESCE(pipeline_bucket, 'active') <> 'parked' AND {$legacyExpr} = 0 THEN 1 ELSE 0 END) AS skipped_closed,
                SUM(CASE WHEN status = 'Pending' AND COALESCE(pipeline_bucket, 'active') <> 'parked' AND {$legacyExpr} = 1 THEN 1 ELSE 0 END) AS skipped_legacy_pending,
                SUM(CASE WHEN status <> 'Pending' AND COALESCE(pipeline_bucket, 'active') <> 'parked' AND {$legacyExpr} = 1 THEN 1 ELSE 0 END) AS skipped_legacy_closed
            FROM deals
            WHERE {$whereSql}
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        return [
            'total_in_stage' => (int)($row['total_in_stage'] ?? 0),
            'eligible_pending' => (int)($row['eligible_pending'] ?? 0),
            'already_parked_pending' => (int)($row['already_parked_pending'] ?? 0),
            'skipped_closed' => (int)($row['skipped_closed'] ?? 0),
            'skipped_legacy_pending' => (int)($row['skipped_legacy_pending'] ?? 0),
            'skipped_legacy_closed' => (int)($row['skipped_legacy_closed'] ?? 0),
        ];
    }

    public function getStageUnparkingSummary(string $stage, array $filters = []): array
    {
        $stage = trim($stage);
        if ($stage === '') {
            return [];
        }

        $pdo = $this->db->getPdo();
        $hasLegacy = $this->db->hasColumn('deals', 'is_legacy_didar');
        $legacyExpr = $hasLegacy ? "COALESCE(is_legacy_didar, 0)" : "0";
        $where = ["parked_from_stage = ?"];
        $params = [$stage];

        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) {
                return trim($v) !== '';
            }));
            if (!empty($ownerIds)) {
                $where[] = "owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = "owner_didar_id = ?";
            $params[] = (string)$filters['owner_didar_id'];
        }

        $whereSql = implode(' AND ', $where);
        $sql = "
            SELECT
                COUNT(*) AS total_from_stage,
                SUM(CASE WHEN status = 'Pending' AND COALESCE(pipeline_bucket, 'active') = 'parked' AND {$legacyExpr} = 0 THEN 1 ELSE 0 END) AS eligible_parked_pending,
                SUM(CASE WHEN status <> 'Pending' AND COALESCE(pipeline_bucket, 'active') = 'parked' AND {$legacyExpr} = 0 THEN 1 ELSE 0 END) AS skipped_closed_parked,
                SUM(CASE WHEN status = 'Pending' AND COALESCE(pipeline_bucket, 'active') = 'parked' AND {$legacyExpr} = 1 THEN 1 ELSE 0 END) AS skipped_legacy_parked,
                SUM(CASE WHEN COALESCE(pipeline_bucket, 'active') <> 'parked' AND {$legacyExpr} = 0 THEN 1 ELSE 0 END) AS already_unparked
            FROM deals
            WHERE {$whereSql}
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        return [
            'total_from_stage' => (int)($row['total_from_stage'] ?? 0),
            'eligible_parked_pending' => (int)($row['eligible_parked_pending'] ?? 0),
            'skipped_closed_parked' => (int)($row['skipped_closed_parked'] ?? 0),
            'skipped_legacy_parked' => (int)($row['skipped_legacy_parked'] ?? 0),
            'already_unparked' => (int)($row['already_unparked'] ?? 0),
        ];
    }

    public function moveStageDealsOutOfParked(string $stage, array $filters = []): int
    {
        if (!$this->hasPipelineBucketColumns()) {
            return 0;
        }

        $stage = trim($stage);
        if ($stage === '') {
            return 0;
        }

        $pdo = $this->db->getPdo();
        $where = [
            "parked_from_stage = ?",
            "status = 'Pending'",
            "COALESCE(pipeline_bucket, 'active') = 'parked'",
        ];
        $params = [$stage];

        if (array_key_exists('legacy', $filters)) {
            if ($filters['legacy'] === '0' || $filters['legacy'] === 0 || $filters['legacy'] === false) {
                $where[] = "COALESCE(is_legacy_didar, 0) = 0";
            } elseif ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true) {
                $where[] = "COALESCE(is_legacy_didar, 0) = 1";
            }
        }

        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) {
                return trim($v) !== '';
            }));
            if (!empty($ownerIds)) {
                $where[] = "owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = "owner_didar_id = ?";
            $params[] = (string)$filters['owner_didar_id'];
        }

        $sql = "UPDATE deals
                SET pipeline_stage = parked_from_stage,
                    pipeline_bucket = 'active',
                    parked_from_stage = NULL,
                    parked_at = NULL,
                    parked_by_didar_id = NULL,
                    parked_reason = NULL,
                    last_sync = NOW()
                WHERE " . implode(' AND ', $where);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->rowCount();
    }

    public function getAllParkedSummary(array $filters = []): array
    {
        if (!$this->hasPipelineBucketColumns()) {
            return ['eligible_parked_pending' => 0];
        }
        $pdo = $this->db->getPdo();
        $hasLegacy = $this->db->hasColumn('deals', 'is_legacy_didar');
        $legacyExpr = $hasLegacy ? "COALESCE(is_legacy_didar, 0)" : "0";
        $where = [
            "COALESCE(pipeline_bucket, 'active') = 'parked'",
            "status = 'Pending'",
            "{$legacyExpr} = 0",
        ];
        $params = [];
        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) { return trim($v) !== ''; }));
            if (!empty($ownerIds)) {
                $where[] = "owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = "owner_didar_id = ?";
            $params[] = (string)$filters['owner_didar_id'];
        }
        $stmt = $pdo->prepare("SELECT COUNT(*) AS cnt FROM deals WHERE " . implode(' AND ', $where));
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        return ['eligible_parked_pending' => (int)($row['cnt'] ?? 0)];
    }

    public function unparkAllDeals(array $filters = []): int
    {
        if (!$this->hasPipelineBucketColumns()) {
            return 0;
        }
        $pdo = $this->db->getPdo();
        $where = [
            "COALESCE(pipeline_bucket, 'active') = 'parked'",
            "status = 'Pending'",
            "COALESCE(is_legacy_didar, 0) = 0",
        ];
        $params = [];
        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) { return trim($v) !== ''; }));
            if (!empty($ownerIds)) {
                $where[] = "owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = "owner_didar_id = ?";
            $params[] = (string)$filters['owner_didar_id'];
        }
        $sql = "UPDATE deals
                SET pipeline_stage = parked_from_stage,
                    pipeline_bucket = 'active',
                    parked_from_stage = NULL,
                    parked_at = NULL,
                    parked_by_didar_id = NULL,
                    parked_reason = NULL,
                    last_sync = NOW()
                WHERE " . implode(' AND ', $where);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->rowCount();
    }

    public function getParkedByUserSummary(array $filters = []): array
    {
        if (!$this->hasPipelineBucketColumns()) {
            return [];
        }
        $pdo = $this->db->getPdo();
        $hasLegacy = $this->db->hasColumn('deals', 'is_legacy_didar');
        $legacyExpr = $hasLegacy ? "COALESCE(is_legacy_didar, 0)" : "0";
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $ownerNameField = $hasDisplayName ? "u.display_name" : "u.username";
        $where = [
            "COALESCE(d.pipeline_bucket, 'active') = 'parked'",
            "d.status = 'Pending'",
            "{$legacyExpr} = 0",
        ];
        $params = [];
        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) { return trim($v) !== ''; }));
            if (!empty($ownerIds)) {
                $where[] = "d.owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (!empty($filters['owner_didar_id'])) {
            $where[] = "d.owner_didar_id = ?";
            $params[] = (string)$filters['owner_didar_id'];
        }
        $sql = "SELECT d.owner_didar_id, {$ownerNameField} AS owner_name, COUNT(*) AS cnt
                FROM deals d
                LEFT JOIN users u ON d.owner_didar_id = u.didar_user_id
                WHERE " . implode(' AND ', $where) . "
                GROUP BY d.owner_didar_id, {$ownerNameField}
                ORDER BY cnt DESC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $result = [];
        foreach ($rows as $row) {
            $result[] = [
                'didar_id'   => (string)($row['owner_didar_id'] ?? ''),
                'owner_name' => (string)($row['owner_name'] ?? $row['owner_didar_id'] ?? ''),
                'count'      => (int)($row['cnt'] ?? 0),
            ];
        }
        return $result;
    }

    public function restoreFromParked(string $dealId, ?string $targetStage = null): bool
    {
        if (!$this->hasPipelineBucketColumns()) {
            return false;
        }

        $pdo = $this->db->getPdo();
        $setParts = [
            "pipeline_bucket = 'active'",
            "parked_at = NULL",
            "last_sync = NOW()",
        ];
        $params = [];
        if ($targetStage !== null && trim($targetStage) !== '') {
            $setParts[] = "pipeline_stage = ?";
            $params[] = trim($targetStage);
        }
        if ($this->db->hasColumn('deals', 'parked_by_didar_id')) {
            $setParts[] = "parked_by_didar_id = NULL";
        }
        if ($this->db->hasColumn('deals', 'parked_reason')) {
            $setParts[] = "parked_reason = NULL";
        }
        $setParts[] = "parked_from_stage = NULL";
        $params[] = $dealId;
        $stmt = $pdo->prepare("UPDATE deals SET " . implode(', ', $setParts) . " WHERE didar_deal_id = ?");
        return $stmt->execute($params);
    }

    /**
     * Remove all deals of a contact from pipeline stages (set to empty or null)
     */
    public function removeDealsFromPipeline(string $contactId): bool
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE deals SET pipeline_stage = '' WHERE contact_didar_id = ?");
        return $stmt->execute([$contactId]);
    }

    /**
     * Bulk update owner for all deals of a contact.
     */
    public function updateOwnerByContact(string $contactId, string $ownerId): bool
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE deals SET owner_didar_id = ? WHERE contact_didar_id = ?");
        return $stmt->execute([$ownerId, $contactId]);
    }

    public function updateRequestedServices($dealId, $services)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE deals SET requested_services = ?, last_sync = NOW() WHERE didar_deal_id = ?");
        return $stmt->execute([$services, $dealId]);
    }

    public function updateCourseStatus($dealId, $status, $pipelineStage, $courseInfoText)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE deals SET status = ?, pipeline_stage = ?, payment_description = ? WHERE didar_deal_id = ?");
        return $stmt->execute([$status, $pipelineStage, $courseInfoText, $dealId]);
    }

    public function getAll($filters = [])
    {
        $pdo = $this->db->getPdo();
        $where = [];
        $params = [];

        // RBAC data-scope: applyOwnerScope() emits the plural owner_didar_ids array
        // whenever a selected_users scope resolves to 0 or >=2 users (and unsets the
        // singular key). This branch must mirror countAll()/getStageCounts() or the
        // deals list silently fails open and leaks every deal.
        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) {
                return trim($v) !== '';
            }));
            if (!empty($ownerIds)) {
                $where[] = "d.owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (isset($filters['owner_didar_id']) && !empty($filters['owner_didar_id'])) {
            $where[] = "d.owner_didar_id = ?";
            $params[] = $filters['owner_didar_id'];
        }

        if (isset($filters['status']) && $filters['status'] !== 'all') {
            $where[] = "d.status = ?";
            $params[] = $filters['status'];
        }

        if (isset($filters['pipeline_stage']) && $filters['pipeline_stage'] !== 'all') {
            $where[] = "d.pipeline_stage = ?";
            $params[] = $filters['pipeline_stage'];
        }

        $this->applyPipelineBucketFilter($where, $params, $filters, 'd');
        $this->applyRequestedServiceFilter($where, $params, $filters, 'd');

        if (!empty($filters['register_date_from'])) {
            $where[] = "d.register_time >= ?";
            $params[] = $filters['register_date_from'] . " 00:00:00";
        }
        if (!empty($filters['register_date_to'])) {
            $where[] = "d.register_time <= ?";
            $params[] = $filters['register_date_to'] . " 23:59:59";
        }

        if (!empty($filters['activity_date_from'])) {
            $where[] = "(SELECT MAX(a.register_date) FROM activities a WHERE a.deal_didar_id = d.didar_deal_id) >= ?";
            $params[] = $filters['activity_date_from'] . " 00:00:00";
        }
        if (!empty($filters['activity_date_to'])) {
            $where[] = "(SELECT MAX(a.register_date) FROM activities a WHERE a.deal_didar_id = d.didar_deal_id) <= ?";
            $params[] = $filters['activity_date_to'] . " 23:59:59";
        }

        if (isset($filters['legacy'])) {
            if ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true) {
                $where[] = "COALESCE(d.is_legacy_didar, 0) = 1";
            } elseif ($filters['legacy'] === '0' || $filters['legacy'] === 0 || $filters['legacy'] === false) {
                $where[] = "COALESCE(d.is_legacy_didar, 0) = 0";
            }
        }
        
        if (isset($filters['search']) && !empty($filters['search'])) {
            $where[] = "(d.title LIKE ? OR d.code LIKE ?)";
            $searchTerm = "%{$filters['search']}%";
            $params = array_merge($params, [$searchTerm, $searchTerm]);
        }
        if (isset($filters['customer_code_search']) && !empty($filters['customer_code_search'])) {
            $where[] = "EXISTS (
                SELECT 1 FROM persons pc
                WHERE pc.customer_code LIKE ?
                  AND (
                    (d.person_id IS NOT NULL AND d.person_id = pc.id)
                    OR d.contact_didar_id = pc.didar_contact_id COLLATE utf8mb4_general_ci
                  )
            )";
            $params[] = "%{$filters['customer_code_search']}%";
        }

        if (!empty($filters['cursor_time'])) {
            $where[] = "(d.register_time < ? OR (d.register_time = ? AND d.didar_deal_id < ?))";
            $params[] = $filters['cursor_time'];
            $params[] = $filters['cursor_time'];
            $params[] = $filters['cursor_id'] ?? '';
        }
        
        $whereClause = empty($where) ? "1=1" : implode(' AND ', $where);
        
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $ownerNameField = $hasDisplayName 
            ? "u.display_name" 
            : "COALESCE(CONCAT(u.first_name, ' ', u.last_name), '')";
        
        // MySQL: Use CONCAT with COALESCE to handle NULL names
        // Join with persons table using person_id (local match) or didar_contact_id (remote match)
        // This prevents collisions between Didar IDs and Local IDs
        $limit = isset($filters['limit']) ? (int)$filters['limit'] : 100;
        if ($limit <= 0) {
            $limit = 100;
        }
        if ($limit > 200) {
            $limit = 200;
        }
        $offset = isset($filters['offset']) ? (int)$filters['offset'] : 0;
        if ($offset < 0) {
            $offset = 0;
        }

        // Phase 1 performance optimization: Deferred Join / Late Row Lookups
        // Step 1) fetch only page IDs from deals with all filters/sorting applied
        $idSql = "SELECT d.id, d.register_time, d.didar_deal_id
                  FROM deals d
                  WHERE $whereClause
                  ORDER BY d.register_time DESC, d.didar_deal_id DESC";
        $idParams = $params;
        if (!empty($filters['cursor_time'])) {
            $idSql .= " LIMIT ?";
            $idParams[] = $limit;
        } else {
            $idSql .= " LIMIT ? OFFSET ?";
            $idParams[] = $limit;
            $idParams[] = $offset;
        }

        $idStmt = $pdo->prepare($idSql);
        $idStmt->execute($idParams);
        $idRows = $idStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        if (empty($idRows)) {
            return [];
        }

        $idList = [];
        $orderMap = [];
        foreach ($idRows as $index => $row) {
            $rid = (int)($row['id'] ?? 0);
            if ($rid <= 0) {
                continue;
            }
            $idList[] = $rid;
            $orderMap[$rid] = $index;
        }
        if (empty($idList)) {
            return [];
        }

        // Step 2) join heavy tables only for selected row IDs
        $placeholders = implode(',', array_fill(0, count($idList), '?'));
        $sql = "SELECT d.*, 
                TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, ''))) as contact_name, 
                p.mobile_phone as contact_mobile,
                p.id as local_person_id,
                $ownerNameField as owner_name
                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.id IN ($placeholders)";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($idList);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        usort($rows, function ($a, $b) use ($orderMap) {
            $ia = $orderMap[(int)($a['id'] ?? 0)] ?? PHP_INT_MAX;
            $ib = $orderMap[(int)($b['id'] ?? 0)] ?? PHP_INT_MAX;
            return $ia <=> $ib;
        });

        return $rows;
    }

    /**
     * Debug/diagnostic helper for performance tuning of getAll().
     * Returns EXPLAIN rows for the page-ID selection query and hydration query.
     * Safe to call from admin-only tooling (e.g. perf profiler).
     */
    public function explainGetAll(array $filters = []): array
    {
        $pdo = $this->db->getPdo();
        $where = [];
        $params = [];

        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) {
                return trim($v) !== '';
            }));
            if (!empty($ownerIds)) {
                $where[] = "d.owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (isset($filters['owner_didar_id']) && !empty($filters['owner_didar_id'])) {
            $where[] = "d.owner_didar_id = ?";
            $params[] = $filters['owner_didar_id'];
        }
        if (isset($filters['status']) && $filters['status'] !== 'all') {
            $where[] = "d.status = ?";
            $params[] = $filters['status'];
        }
        if (isset($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'] . " 00:00:00";
        }
        if (!empty($filters['register_date_to'])) {
            $where[] = "d.register_time <= ?";
            $params[] = $filters['register_date_to'] . " 23:59:59";
        }
        if (!empty($filters['activity_date_from'])) {
            $where[] = "(SELECT MAX(a.register_date) FROM activities a WHERE a.deal_didar_id = d.didar_deal_id) >= ?";
            $params[] = $filters['activity_date_from'] . " 00:00:00";
        }
        if (!empty($filters['activity_date_to'])) {
            $where[] = "(SELECT MAX(a.register_date) FROM activities a WHERE a.deal_didar_id = d.didar_deal_id) <= ?";
            $params[] = $filters['activity_date_to'] . " 23:59:59";
        }
        if (isset($filters['legacy'])) {
            if ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true || $filters['legacy'] === 'exclude') {
                // preserve existing semantics for explicit boolean forms
                if ($filters['legacy'] === 'exclude') {
                    $where[] = "COALESCE(d.is_legacy_didar, 0) = 0";
                } elseif ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true) {
                    $where[] = "COALESCE(d.is_legacy_didar, 0) = 1";
                }
            } elseif ($filters['legacy'] === '0' || $filters['legacy'] === 0 || $filters['legacy'] === false) {
                $where[] = "COALESCE(d.is_legacy_didar, 0) = 0";
            } elseif ($filters['legacy'] === 'only') {
                $where[] = "COALESCE(d.is_legacy_didar, 0) = 1";
            }
        }
        if (isset($filters['search']) && !empty($filters['search'])) {
            $where[] = "(d.title LIKE ? OR d.code LIKE ?)";
            $searchTerm = "%{$filters['search']}%";
            $params[] = $searchTerm;
            $params[] = $searchTerm;
        }
        if (isset($filters['customer_code_search']) && !empty($filters['customer_code_search'])) {
            $where[] = "EXISTS (
                SELECT 1 FROM persons pc
                WHERE pc.customer_code LIKE ?
                  AND (
                    (d.person_id IS NOT NULL AND d.person_id = pc.id)
                    OR d.contact_didar_id = pc.didar_contact_id COLLATE utf8mb4_general_ci
                  )
            )";
            $params[] = "%{$filters['customer_code_search']}%";
        }
        if (!empty($filters['cursor_time'])) {
            $where[] = "(d.register_time < ? OR (d.register_time = ? AND d.didar_deal_id < ?))";
            $params[] = $filters['cursor_time'];
            $params[] = $filters['cursor_time'];
            $params[] = $filters['cursor_id'] ?? '';
        }

        $whereClause = empty($where) ? "1=1" : implode(' AND ', $where);
        $limit = isset($filters['limit']) ? (int)$filters['limit'] : 100;
        if ($limit <= 0) {
            $limit = 100;
        }
        if ($limit > 200) {
            $limit = 200;
        }
        $offset = isset($filters['offset']) ? (int)$filters['offset'] : 0;
        if ($offset < 0) {
            $offset = 0;
        }

        $idSql = "SELECT d.id, d.register_time, d.didar_deal_id
                  FROM deals d
                  WHERE $whereClause
                  ORDER BY d.register_time DESC, d.didar_deal_id DESC";
        $idParams = $params;
        $cursorMode = !empty($filters['cursor_time']) || !empty($filters['use_cursor']);
        if ($cursorMode) {
            $idSql .= " LIMIT ?";
            $idParams[] = $limit;
        } else {
            $idSql .= " LIMIT ? OFFSET ?";
            $idParams[] = $limit;
            $idParams[] = $offset;
        }

        $idStmt = $pdo->prepare($idSql);
        $idStmt->execute($idParams);
        $idRows = $idStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $idList = array_values(array_filter(array_map(static function ($r) {
            $id = (int)($r['id'] ?? 0);
            return $id > 0 ? $id : null;
        }, $idRows)));

        $idExplain = $this->runExplain($idSql, $idParams);
        $hydrateExplain = [];
        if (!empty($idList)) {
            $hasDisplayName = $this->db->hasColumn('users', 'display_name');
            $ownerNameField = $hasDisplayName
                ? "u.display_name"
                : "COALESCE(CONCAT(u.first_name, ' ', u.last_name), '')";
            $placeholders = implode(',', array_fill(0, count($idList), '?'));
            $hydrateSql = "SELECT d.*,
                TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, ''))) as contact_name,
                p.mobile_phone as contact_mobile,
                p.id as local_person_id,
                $ownerNameField as owner_name
                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.id IN ($placeholders)";
            $hydrateExplain = $this->runExplain($hydrateSql, $idList);
        }

        return [
            'page_selection' => [
                'sql' => $idSql,
                'params' => $idParams,
                'rows_selected' => count($idList),
                'explain' => $idExplain,
            ],
            'hydration' => [
                'rows_selected' => count($idList),
                'explain' => $hydrateExplain,
            ],
        ];
    }

    private function runExplain(string $sql, array $params): array
    {
        $pdo = $this->db->getPdo();
        $rows = [];

        // Try JSON format first (MySQL 5.7+/8)
        try {
            $stmt = $pdo->prepare("EXPLAIN FORMAT=JSON " . $sql);
            $stmt->execute($params);
            $json = $stmt->fetchColumn();
            if ($json !== false) {
                return ['format' => 'json', 'data' => json_decode((string)$json, true), 'raw' => (string)$json];
            }
        } catch (\Throwable $e) {
            $rows[] = ['error' => 'EXPLAIN FORMAT=JSON failed: ' . $e->getMessage()];
        }

        // Fallback to classic EXPLAIN
        try {
            $stmt = $pdo->prepare("EXPLAIN " . $sql);
            $stmt->execute($params);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            return ['format' => 'classic', 'data' => $data, 'notes' => $rows];
        } catch (\Throwable $e) {
            return ['format' => 'error', 'error' => $e->getMessage(), 'notes' => $rows];
        }
    }

    /**
     * Stage counts for dashboard (no LIMIT).
     */
    public function getStageCounts($filters = []): array
    {
        $pdo = $this->db->getPdo();
        $where = [];
        $params = [];

        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) {
                return trim($v) !== '';
            }));
            if (!empty($ownerIds)) {
                $where[] = "owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (isset($filters['owner_didar_id']) && !empty($filters['owner_didar_id'])) {
            $where[] = "owner_didar_id = ?";
            $params[] = $filters['owner_didar_id'];
        }

        if (isset($filters['status']) && $filters['status'] !== 'all') {
            $where[] = "status = ?";
            $params[] = $filters['status'];
        }

        if (isset($filters['stage_count_field']) && $filters['stage_count_field'] === 'parked_from_stage') {
            $countField = "COALESCE(parked_from_stage,'')";
        } else {
            $countField = "COALESCE(pipeline_stage,'')";
        }

        if (isset($filters['legacy'])) {
            // PERF (phase 2): is_legacy_didar has no NULLs (migration-backfilled), so the bare
            // predicate is equivalent to COALESCE(...) and remains index-usable.
            if ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true) {
                $where[] = "is_legacy_didar = 1";
            } elseif ($filters['legacy'] === '0' || $filters['legacy'] === 0 || $filters['legacy'] === false) {
                $where[] = "is_legacy_didar = 0";
            }
        }

        $this->applyPipelineBucketFilter($where, $params, $filters);
        $this->applyRequestedServiceFilter($where, $params, $filters);

        $whereClause = empty($where) ? "1=1" : implode(" AND ", $where);
        $stmt = $pdo->prepare("SELECT {$countField} AS stage, COUNT(*) AS cnt FROM deals WHERE $whereClause GROUP BY {$countField}");
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $out = [];
        foreach ($rows as $r) {
            $key = $r['stage'] !== '' ? $r['stage'] : 'نامشخص';
            $out[$key] = (int)($r['cnt'] ?? 0);
        }
        return $out;
    }

    public function countAll($filters = []): int
    {
        $pdo = $this->db->getPdo();
        $where = [];
        $params = [];
        
        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ownerIds = array_values(array_filter(array_map('strval', $filters['owner_didar_ids']), static function ($v) {
                return trim($v) !== '';
            }));
            if (!empty($ownerIds)) {
                $where[] = "owner_didar_id IN (" . implode(',', array_fill(0, count($ownerIds), '?')) . ")";
                $params = array_merge($params, $ownerIds);
            }
        } elseif (isset($filters['owner_didar_id']) && !empty($filters['owner_didar_id'])) {
            $where[] = "owner_didar_id = ?";
            $params[] = $filters['owner_didar_id'];
        }
        
        if (isset($filters['status']) && $filters['status'] !== 'all') {
            $where[] = "status = ?";
            $params[] = $filters['status'];
        }

        if (isset($filters['legacy'])) {
            // PERF (phase 2): bare predicate (is_legacy_didar has no NULLs) stays index-usable.
            if ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true) {
                $where[] = "is_legacy_didar = 1";
            } elseif ($filters['legacy'] === '0' || $filters['legacy'] === 0 || $filters['legacy'] === false) {
                $where[] = "is_legacy_didar = 0";
            }
        }

        $this->applyPipelineBucketFilter($where, $params, $filters);

        $whereClause = empty($where) ? "1=1" : implode(" AND ", $where);
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM deals WHERE $whereClause");
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    public function countByStatus(string $status, $filters = []): int
    {
        $filters['status'] = $status;
        return $this->countAll($filters);
    }

    public function getProductsByDealDidarId($didarDealId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT dp.product_id, pr.name 
                               FROM deals d
                               JOIN deal_products dp ON dp.deal_id = d.id
                               JOIN products pr ON pr.id = dp.product_id
                               WHERE d.didar_deal_id = ?");
        $stmt->execute([$didarDealId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getProductsByDealDidarIds(array $didarDealIds): array
    {
        $didarDealIds = array_values(array_filter(array_map('strval', $didarDealIds), static function ($v) {
            return $v !== '';
        }));
        if (empty($didarDealIds)) {
            return [];
        }

        $pdo = $this->db->getPdo();
        $placeholders = implode(',', array_fill(0, count($didarDealIds), '?'));
        $stmt = $pdo->prepare("SELECT d.didar_deal_id, dp.product_id, pr.name
                               FROM deals d
                               JOIN deal_products dp ON dp.deal_id = d.id
                               JOIN products pr ON pr.id = dp.product_id
                               WHERE d.didar_deal_id IN ($placeholders)");
        $stmt->execute($didarDealIds);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $result = [];
        foreach ($rows as $row) {
            $key = (string)($row['didar_deal_id'] ?? '');
            if ($key === '') {
                continue;
            }
            if (!isset($result[$key])) {
                $result[$key] = [];
            }
            $result[$key][] = [
                'product_id' => $row['product_id'] ?? null,
                'name' => $row['name'] ?? ''
            ];
        }
        return $result;
    }

    public function countDealsByContactId($contactId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM deals WHERE contact_didar_id = ?");
        $stmt->execute([$contactId]);
        return (int) $stmt->fetchColumn();
    }

    public function countDealsByPersonId($personId): int
    {
        if (empty($personId)) {
            return 0;
        }
        if (!$this->db->hasColumn('deals', 'person_id')) {
            return 0;
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM deals WHERE person_id = ?");
        $stmt->execute([$personId]);
        return (int) $stmt->fetchColumn();
    }

    public function updateExtraFields($dealId, array $fields)
    {
        if (empty($dealId) || empty($fields)) {
            return 0;
        }
        $allowed = ['city', 'job_title', 'job_description', 'acquaintance_duration', 'extra_info', 'financial_level', 'asset_estimation', 'income_estimation', 'requested_services', 'has_previous_purchase'];
        $setParts = [];
        $values = [];
        $hasColumn = function($col) {
            return $this->db->hasColumn('deals', $col);
        };
        foreach ($allowed as $key) {
            if (array_key_exists($key, $fields) && $hasColumn($key)) {
                $setParts[] = "$key = ?";
                $values[] = $fields[$key];
            }
        }
        if (empty($setParts)) {
            return 0;
        }
        $sql = "UPDATE deals SET " . implode(', ', $setParts) . " WHERE didar_deal_id = ?";
        $values[] = $dealId;
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare($sql);
        $stmt->execute($values);
        return $stmt->rowCount();
    }
}
