<?php
namespace App\Repositories;

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

class DealRepository
{
    private $db;

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

    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');

        $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 ($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'], 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();
        $stmt = $pdo->prepare("UPDATE deals SET pipeline_stage = ? WHERE didar_deal_id = ?");
        return $stmt->execute([$stage, $dealId]);
    }

    /**
     * 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 = [];
        
        if (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) {
                $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 (!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 (!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 (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'])) {
            if ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true) {
                $where[] = "COALESCE(is_legacy_didar, 0) = 1";
            } elseif ($filters['legacy'] === '0' || $filters['legacy'] === 0 || $filters['legacy'] === false) {
                $where[] = "COALESCE(is_legacy_didar, 0) = 0";
            }
        }

        $whereClause = empty($where) ? "1=1" : implode(" AND ", $where);
        $stmt = $pdo->prepare("SELECT COALESCE(pipeline_stage,'') AS stage, COUNT(*) AS cnt FROM deals WHERE $whereClause GROUP BY COALESCE(pipeline_stage,'')");
        $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 (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'])) {
            if ($filters['legacy'] === '1' || $filters['legacy'] === 1 || $filters['legacy'] === true) {
                $where[] = "COALESCE(is_legacy_didar, 0) = 1";
            } elseif ($filters['legacy'] === '0' || $filters['legacy'] === 0 || $filters['legacy'] === false) {
                $where[] = "COALESCE(is_legacy_didar, 0) = 0";
            }
        }
        
        $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();
    }
}
