<?php
namespace App\Repositories;

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

class PersonRepository
{
    private $db;

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

    private function getProductNameById(int $productId): ?string
    {
        if ($productId <= 0) return null;
        try {
            $pdo = $this->db->getPdo();
            $stmt = $pdo->prepare("SELECT name FROM products WHERE id = ? LIMIT 1");
            $stmt->execute([$productId]);
            $name = $stmt->fetchColumn();
            $name = is_string($name) ? trim($name) : '';
            return $name !== '' ? $name : null;
        } catch (\Throwable $e) {
            Logger::logWarning('Failed to load product name for filter', ['product_id' => $productId, 'error' => $e->getMessage()]);
            return null;
        }
    }

    public function getDb()
    {
        return $this->db;
    }

    public function deleteByDidarId(string $didarContactId): bool
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM persons WHERE didar_contact_id = ?");
        return (bool)$stmt->execute([$didarContactId]);
    }

    public function findByDidarId($didarId)
    {
        if (empty($didarId)) return null;
        $pdo = $this->db->getPdo();
        
        // IMPORTANT: Strictly only search by didar_contact_id
        // When numeric collision happens (e.g. didar_id 647 vs local id 647),
        // this ensures we get the real Didar person.
        $stmt = $pdo->prepare("SELECT * FROM persons WHERE didar_contact_id = ? LIMIT 1");
        $stmt->execute([$didarId]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($data) {
            \App\Utils\Logger::logDebug("findByDidarId: Found strictly by Didar ID", ['didar_id' => $didarId, 'person_id' => $data['id']]);
            return new Person($data);
        }
        
        return null;
    }

    public function findByPhoneCandidatesWithOwners(array $phoneCandidates): array
    {
        $phones = array_values(array_unique(array_filter(array_map(function ($p) {
            $p = is_string($p) ? trim($p) : '';
            return $p !== '' ? $p : null;
        }, $phoneCandidates))));

        if (empty($phones)) {
            return [];
        }

        $pdo = $this->db->getPdo();
        $placeholders = implode(',', array_fill(0, count($phones), '?'));

        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $ownerNameField = $hasDisplayName
            ? "display_name"
            : "COALESCE(CONCAT(first_name, ' ', last_name), '')";

        $sql = "SELECT
                    p.id,
                    p.didar_contact_id,
                    p.first_name,
                    p.last_name,
                    p.mobile_phone,
                    p.secondary_mobile_phone,
                    p.mobile_phone_3,
                    p.mobile_phone_4,
                    p.owner_didar_id,
                    p.previous_owner_id,
                    u1.$ownerNameField AS owner_name,
                    u2.$ownerNameField AS previous_owner_name
                FROM persons p
                LEFT JOIN users u1 ON p.owner_didar_id = u1.didar_user_id
                LEFT JOIN users u2 ON p.previous_owner_id = u2.didar_user_id
                WHERE
                    p.mobile_phone IN ($placeholders)
                    OR p.secondary_mobile_phone IN ($placeholders)
                    OR p.mobile_phone_3 IN ($placeholders)
                    OR p.mobile_phone_4 IN ($placeholders)
                ORDER BY p.register_time DESC, p.created_at DESC
                LIMIT 20";

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

        return array_map(function ($row) {
            $name = trim(($row['first_name'] ?? '') . ' ' . ($row['last_name'] ?? ''));
            $row['display_name'] = $name !== '' ? $name : null;
            return $row;
        }, $rows);
    }

    /**
     * Find person either by internal numeric id or didar_contact_id.
     * Robust logic: handles local numeric id, GUID, and local_ prefixed IDs.
     */
    public function findByIdOrDidarId($idOrDidar)
    {
        if (empty($idOrDidar)) return null;

        // 1. If it's numeric, it's most likely a Local ID from the UI/URL hash.
        // We MUST prioritize Local ID to ensure the profile opened matches the ID in the URL.
        if (is_numeric($idOrDidar)) {
            $person = $this->findById($idOrDidar);
            if ($person) {
                return $person;
            }
            
            // If not found as Local ID, then it might be a numeric Didar ID
            return $this->findByDidarId($idOrDidar);
        }

        // 2. If it's a GUID or other string, it's definitely a Didar ID.
        return $this->findByDidarId($idOrDidar);
    }

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

    /**
     * Find person by any phone (supports array of candidate numbers).
     */
    public function findByPhone($phoneOrPhones)
    {
        $phones = is_array($phoneOrPhones) ? $phoneOrPhones : [$phoneOrPhones];
        $phones = array_values(array_unique(array_filter(array_map('trim', $phones), fn($p) => $p !== '')));

        if (empty($phones)) {
            return null;
        }

        $placeholders = implode(',', array_fill(0, count($phones), '?'));
        $sql = "SELECT * FROM persons WHERE 
                mobile_phone IN ($placeholders) 
                OR secondary_mobile_phone IN ($placeholders) 
                OR mobile_phone_3 IN ($placeholders) 
                OR mobile_phone_4 IN ($placeholders)
                LIMIT 1";

        $params = array_merge($phones, $phones, $phones, $phones);
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($data) {
            \App\Utils\Logger::logDebug("findByPhone: Found person", [
                'phones' => $phones,
                'found_id' => $data['id'],
                'found_didar_id' => $data['didar_contact_id'],
                'found_name' => trim(($data['first_name'] ?? '') . ' ' . ($data['last_name'] ?? ''))
            ]);
        }
        
        return $data ? new Person($data) : null;
    }

    /**
     * Append products to customer_products and mark sale_status = 1.
     */
    public function appendProducts(string $contactId, array $products): void
    {
        $clean = array_values(array_filter(array_map(function($p) {
            return trim((string)$p);
        }, $products)));
        if (empty($clean)) {
            return;
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT customer_products FROM persons WHERE didar_contact_id = ? LIMIT 1");
        $stmt->execute([$contactId]);
        $current = $stmt->fetchColumn() ?: '';
        $existing = array_values(array_filter(array_map('trim', explode(',', $current))));
        $merged = array_unique(array_merge($existing, $clean));
        $newValue = implode(', ', $merged);

        $update = $pdo->prepare("UPDATE persons SET customer_products = ?, sale_status = 1 WHERE didar_contact_id = ?");
        $update->execute([$newValue, $contactId]);
    }

    private function parseRequestedServicesToList($requestedServices): array
    {
        if ($requestedServices === null) {
            return [];
        }

        if (is_array($requestedServices)) {
            $values = $requestedServices;
        } else {
            $raw = trim((string)$requestedServices);
            if ($raw === '') {
                return [];
            }

            $decoded = null;
            if ($raw !== '') {
                $decoded = json_decode($raw, true);
            }

            if (is_array($decoded)) {
                $values = $decoded;
            } else {
                // Fallback for legacy comma-separated values
                $values = preg_split('/[،,]+/u', $raw) ?: [];
            }
        }

        $clean = [];
        foreach ($values as $value) {
            $item = trim((string)$value);
            if ($item === '') {
                continue;
            }
            $clean[] = $item;
        }

        return array_values(array_unique($clean));
    }

    private function mergeCustomerProductsWithRequestedServicesValue(?string $customerProducts, $requestedServices): string
    {
        $requestedList = $this->parseRequestedServicesToList($requestedServices);
        if (empty($requestedList)) {
            return (string)($customerProducts ?? '');
        }

        $existing = array_values(array_filter(array_map('trim', preg_split('/[،,]+/u', (string)($customerProducts ?? '')) ?: [])));
        $merged = array_values(array_unique(array_merge($existing, $requestedList)));
        return implode(', ', $merged);
    }

    public function backfillCustomerProductsFromRequestedServices(): int
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("SELECT didar_contact_id, customer_products, requested_services FROM persons");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $updated = 0;
        $upd = $pdo->prepare("UPDATE persons SET customer_products = ? WHERE didar_contact_id = ?");
        foreach ($rows as $row) {
            $contactId = (string)($row['didar_contact_id'] ?? '');
            if ($contactId === '') {
                continue;
            }
            $current = (string)($row['customer_products'] ?? '');
            $merged = $this->mergeCustomerProductsWithRequestedServicesValue($current, $row['requested_services'] ?? null);
            if ($merged !== $current) {
                $upd->execute([$merged, $contactId]);
                $updated++;
            }
        }

        return $updated;
    }

    public function syncCustomerProductsFromRequestedServicesForContact(string $contactId): bool
    {
        $contactId = trim($contactId);
        if ($contactId === '') {
            return false;
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT customer_products, requested_services FROM persons WHERE didar_contact_id = ? LIMIT 1");
        $stmt->execute([$contactId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) {
            return false;
        }

        $current = (string)($row['customer_products'] ?? '');
        $merged = $this->mergeCustomerProductsWithRequestedServicesValue($current, $row['requested_services'] ?? null);
        if ($merged === $current) {
            return false;
        }

        $upd = $pdo->prepare("UPDATE persons SET customer_products = ? WHERE didar_contact_id = ?");
        $upd->execute([$merged, $contactId]);
        return true;
    }

    public function save(Person $person)
    {
        $pdo = $this->db->getPdo();

        $existingCustomerCode = '';
        if (!empty((string)($person->didar_contact_id ?? ''))) {
            $existingStmt = $pdo->prepare("SELECT customer_code FROM persons WHERE didar_contact_id = ? LIMIT 1");
            $existingStmt->execute([(string)$person->didar_contact_id]);
            $existingCustomerCode = trim((string)$existingStmt->fetchColumn());
        }

        if ($existingCustomerCode !== '' && empty(trim((string)($person->customer_code ?? '')))) {
            // Preserve existing code when update payload does not include one.
            $person->customer_code = $existingCustomerCode;
        } elseif ($existingCustomerCode === '' && empty(trim((string)($person->customer_code ?? '')))) {
            // Assign only on first create path.
            $person->customer_code = $this->allocateNextCustomerCode();
        }

        // Keep lead products in sync with requested services (services should also appear in customer_products).
        $person->customer_products = $this->mergeCustomerProductsWithRequestedServicesValue(
            $person->customer_products ?? '',
            $person->requested_services ?? null
        );
        
        // Check which columns exist in the persons table
        static $columnsExist = null;
        if ($columnsExist === null) {
            $columnsExist = [];
            $checkColumns = ['customer_level', 'customer_level_notes', 'previous_owner_history', 'created_by_didar_id', 'created_by_name', 'created_via'];
            foreach ($checkColumns as $col) {
                try {
                    $stmt = $pdo->query("SHOW COLUMNS FROM persons LIKE '{$col}'");
                    $columnsExist[$col] = $stmt->rowCount() > 0;
                } catch (\Exception $e) {
                    $columnsExist[$col] = false;
                }
            }
        }
        
        // Build column list (always include base columns, add optional columns if they exist)
        $baseColumns = [
            'didar_contact_id', 'owner_didar_id', 'previous_owner_id', 'code', 'customer_code', 'first_name', 'last_name', 'mobile_phone', 'secondary_mobile_phone',
            'mobile_phone_3', 'mobile_phone_4', 'work_phone', 'email', 'contact_type', 'city', 'job_title', 'job_description', 'acquaintance_source', 'acquaintance_detail',
            'acquaintance_duration', 'content_topic', 'national_id', 'has_previous_purchase', 'sale_status', 'birth_date', 'register_time_jalali', 'register_time', 'visibility_type',
            'background_info', 'extra_info', 'customer_products', 'financial_level', 'asset_estimation', 'income_estimation', 'requested_services'
        ];
        
        $columns = $baseColumns;
        
        // Add optional columns if they exist in the database
        if ($columnsExist['customer_level']) {
            $columns[] = 'customer_level';
        }
        if ($columnsExist['customer_level_notes']) {
            $columns[] = 'customer_level_notes';
        }
        if ($columnsExist['previous_owner_history']) {
            $columns[] = 'previous_owner_history';
        }
        if ($columnsExist['created_by_didar_id']) {
            $columns[] = 'created_by_didar_id';
        }
        if ($columnsExist['created_by_name']) {
            $columns[] = 'created_by_name';
        }
        if ($columnsExist['created_via']) {
            $columns[] = 'created_via';
        }
        
        // Always add these
        $columns[] = 'last_sync';
        $columns[] = 'raw_json';
        
        $columnsList = implode(', ', $columns);
        $placeholders = implode(', ', array_fill(0, count($columns), '?'));
        
        $updateClauses = [];
        foreach ($columns as $col) {
            if (!in_array($col, ['didar_contact_id', 'created_by_didar_id', 'created_by_name', 'created_via'], true)) { // Don't update identity/creation metadata
                $updateClauses[] = "{$col} = VALUES({$col})";
            }
        }
        $updateClause = implode(", ", $updateClauses);
        
        $sql = "INSERT INTO persons ({$columnsList})
                VALUES ({$placeholders})
                ON DUPLICATE KEY UPDATE {$updateClause}";
        
        // Build values array in the same order as columns
        $values = [];
        foreach ($columns as $col) {
            $value = null;
            
            if ($col === 'customer_level') {
                $value = $person->customer_level ?? null;
            } elseif ($col === 'customer_level_notes') {
                $value = $person->customer_level_notes ?? null;
            } elseif ($col === 'has_previous_purchase') {
                $value = isset($person->has_previous_purchase) && $person->has_previous_purchase !== ''
                    ? (int)$person->has_previous_purchase
                    : null;
            } elseif ($col === 'sale_status') {
                $value = $person->sale_status ?? 0;
            } elseif ($col === 'last_sync') {
                $value = $person->last_sync ?? date('Y-m-d H:i:s');
            } else {
                $value = $person->{$col} ?? null;
            }
            
            $values[] = $value;
        }
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($values);
        
        return $pdo->lastInsertId();
    }

    public function allocateNextCustomerCode(): string
    {
        $pdo = $this->db->getPdo();

        $startedTransaction = false;
        if (!$pdo->inTransaction()) {
            $pdo->beginTransaction();
            $startedTransaction = true;
        }
        try {
            $sequenceKey = 'customer_code';
            $initStmt = $pdo->prepare("
                INSERT INTO invoice_sequences (sequence_key, last_serial, updated_at)
                VALUES (?, 19999, NOW())
                ON DUPLICATE KEY UPDATE updated_at = NOW()
            ");
            $initStmt->execute([$sequenceKey]);

            $lockStmt = $pdo->prepare("SELECT last_serial FROM invoice_sequences WHERE sequence_key = ? FOR UPDATE");
            $lockStmt->execute([$sequenceKey]);
            $lastSerial = (int)$lockStmt->fetchColumn();
            if ($lastSerial < 19999) {
                $lastSerial = 19999;
            }
            $nextCode = $lastSerial + 1;

            $updateStmt = $pdo->prepare("UPDATE invoice_sequences SET last_serial = ?, updated_at = NOW() WHERE sequence_key = ?");
            $updateStmt->execute([$nextCode, $sequenceKey]);

            if ($startedTransaction && $pdo->inTransaction()) {
                $pdo->commit();
            }
            return (string)$nextCode;
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            Logger::logWarning('Failed to allocate customer code', ['error' => $e->getMessage()]);
            return (string)time();
        }
    }

    public function backfillMissingCustomerCodes(): int
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("SELECT didar_contact_id FROM persons WHERE customer_code IS NULL OR customer_code = '' ORDER BY COALESCE(register_time, created_at) ASC, id ASC");
        $rows = $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];

        $updated = 0;
        foreach ($rows as $contactId) {
            $code = $this->allocateNextCustomerCode();
            $upd = $pdo->prepare("UPDATE persons SET customer_code = ? WHERE didar_contact_id = ? AND (customer_code IS NULL OR customer_code = '')");
            $upd->execute([$code, $contactId]);
            $updated += (int)$upd->rowCount();
        }

        return $updated;
    }

    public function assignCustomerCodeIfMissing(string $contactId): ?string
    {
        $contactId = trim($contactId);
        if ($contactId === '') {
            return null;
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT customer_code FROM persons WHERE didar_contact_id = ? LIMIT 1");
        $stmt->execute([$contactId]);
        $current = trim((string)$stmt->fetchColumn());
        if ($current !== '') {
            return $current;
        }

        $code = $this->allocateNextCustomerCode();
        $upd = $pdo->prepare("UPDATE persons SET customer_code = ? WHERE didar_contact_id = ? AND (customer_code IS NULL OR customer_code = '')");
        $upd->execute([$code, $contactId]);
        return ((int)$upd->rowCount() > 0) ? $code : null;
    }

    public function isCustomerCodeTakenByAnother(string $customerCode, string $excludeDidarContactId = ''): bool
    {
        $customerCode = trim($customerCode);
        if ($customerCode === '') {
            return false;
        }
        $pdo = $this->db->getPdo();
        if ($excludeDidarContactId !== '') {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM persons WHERE customer_code = ? AND didar_contact_id <> ?");
            $stmt->execute([$customerCode, $excludeDidarContactId]);
        } else {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM persons WHERE customer_code = ?");
            $stmt->execute([$customerCode]);
        }
        return ((int)$stmt->fetchColumn()) > 0;
    }

    public function updateOwner($contactId, $ownerId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE persons SET owner_didar_id = ? WHERE didar_contact_id = ?");
        return $stmt->execute([$ownerId, $contactId]);
    }

    /**
     * Update owner while preserving previous owner.
     */
    public function updateOwnerWithPrevious($contactId, $newOwnerId, $previousOwnerId = null)
    {
        $pdo = $this->db->getPdo();
        if (!$this->db->hasColumn('persons', 'previous_owner_history')) {
            $stmt = $pdo->prepare("UPDATE persons SET owner_didar_id = ?, previous_owner_id = ? WHERE didar_contact_id = ?");
            return $stmt->execute([$newOwnerId, $previousOwnerId, $contactId]);
        }

        $select = $pdo->prepare("SELECT previous_owner_id, previous_owner_history FROM persons WHERE didar_contact_id = ? LIMIT 1");
        $select->execute([$contactId]);
        $row = $select->fetch(PDO::FETCH_ASSOC) ?: [];

        $ids = [];
        foreach ([$row['previous_owner_history'] ?? '', $row['previous_owner_id'] ?? '', $previousOwnerId] as $source) {
            foreach (array_map('trim', explode(',', (string)$source)) as $value) {
                if ($value === '' || isset($ids[$value])) {
                    continue;
                }
                $ids[$value] = true;
            }
        }

        $history = empty($ids) ? null : implode(',', array_keys($ids));
        $stmt = $pdo->prepare("UPDATE persons SET owner_didar_id = ?, previous_owner_id = ?, previous_owner_history = ? WHERE didar_contact_id = ?");
        return $stmt->execute([$newOwnerId, $previousOwnerId, $history, $contactId]);
    }

    public function updateSaleStatus($contactId, int $saleStatus)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE persons SET sale_status = ? WHERE didar_contact_id = ?");
        return $stmt->execute([$saleStatus, $contactId]);
    }

    public function getLeadsWithVirtualStage($filters = [])
    {
        $pdo = $this->db->getPdo();
        $where = ["p.didar_contact_id IS NOT NULL"];
        $params = [];

        $hasBlataklif = $this->db->hasColumn('persons', 'is_blataklif');
        if ($hasBlataklif && !empty($filters['blataklif_bucket'])) {
            if ($filters['blataklif_bucket'] === 'blataklif') {
                $where[] = "p.is_blataklif = 1";
            } elseif ($filters['blataklif_bucket'] === 'normal') {
                $where[] = "(p.is_blataklif IS NULL OR p.is_blataklif = 0)";
            }
        }
        
        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[] = "p.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[] = "p.owner_didar_id = ?";
            $params[] = $filters['owner_didar_id'];
        }
        
        if (isset($filters['has_previous_purchase'])) {
            if ($filters['has_previous_purchase'] === 'yes') {
                $where[] = "p.has_previous_purchase = 1";
            } elseif ($filters['has_previous_purchase'] === 'no') {
                $where[] = "p.has_previous_purchase = 0";
            }
        }

        if (isset($filters['sale_status'])) {
            if ($filters['sale_status'] === 'success') {
                $where[] = "p.sale_status = 1";
            } elseif ($filters['sale_status'] === 'failed') {
                $where[] = "p.sale_status = 0";
            }
        }

        if (!empty($filters['customer_level'])) {
            $where[] = "p.customer_level = ?";
            $params[] = $filters['customer_level'];
        }

        $registerFromValid = isset($filters['register_date_from']) && preg_match('/^\d{4}[-\/]\d{2}[-\/]\d{2}$/', (string)$filters['register_date_from']) === 1;
        $registerToValid = isset($filters['register_date_to']) && preg_match('/^\d{4}[-\/]\d{2}[-\/]\d{2}$/', (string)$filters['register_date_to']) === 1;
        $registerFromJalaliValid = isset($filters['register_date_from_jalali']) && preg_match('/^\d{4}\/\d{2}\/\d{2}$/', (string)$filters['register_date_from_jalali']) === 1;
        $registerToJalaliValid = isset($filters['register_date_to_jalali']) && preg_match('/^\d{4}\/\d{2}\/\d{2}$/', (string)$filters['register_date_to_jalali']) === 1;

        if ($registerFromValid) {
            $where[] = "(
                (p.register_time IS NOT NULL AND CAST(p.register_time AS CHAR) >= ?)
                OR STR_TO_DATE(
                    NULLIF(
                        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9'),
                        ''
                    ),
                    '%Y/%m/%d'
                ) >= STR_TO_DATE(NULLIF(REPLACE(?, '/', '-'), ''), '%Y-%m-%d')
                OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') >= ?
            )";
            $params[] = str_replace('/', '-', $filters['register_date_from']) . " 00:00:00";
            $params[] = str_replace('/', '-', $filters['register_date_from']);
            $params[] = $registerFromJalaliValid ? $filters['register_date_from_jalali'] : str_replace('-', '/', $filters['register_date_from']);
        } elseif ($registerFromJalaliValid) {
            $where[] = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') >= ?";
            $params[] = $filters['register_date_from_jalali'];
        }
        if ($registerToValid) {
            $where[] = "(
                (p.register_time IS NOT NULL AND CAST(p.register_time AS CHAR) <= ?)
                OR STR_TO_DATE(
                    NULLIF(
                        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9'),
                        ''
                    ),
                    '%Y/%m/%d'
                ) <= STR_TO_DATE(NULLIF(REPLACE(?, '/', '-'), ''), '%Y-%m-%d')
                OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') <= ?
            )";
            $params[] = str_replace('/', '-', $filters['register_date_to']) . " 23:59:59";
            $params[] = str_replace('/', '-', $filters['register_date_to']);
            $params[] = $registerToJalaliValid ? $filters['register_date_to_jalali'] : str_replace('-', '/', $filters['register_date_to']);
        } elseif ($registerToJalaliValid) {
            $where[] = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') <= ?";
            $params[] = $filters['register_date_to_jalali'];
        }

        if (!empty($filters['activity_date_from']) && !empty($filters['activity_date_to'])) {
            $where[] = "EXISTS (SELECT 1 FROM activities a WHERE a.contact_didar_id = p.didar_contact_id AND a.register_date BETWEEN ? AND ?)";
            $params[] = $filters['activity_date_from'] . " 00:00:00";
            $params[] = $filters['activity_date_to'] . " 23:59:59";
        } elseif (!empty($filters['activity_date_from'])) {
            $where[] = "EXISTS (SELECT 1 FROM activities a WHERE a.contact_didar_id = p.didar_contact_id AND a.register_date >= ?)";
            $params[] = $filters['activity_date_from'] . " 00:00:00";
        } elseif (!empty($filters['activity_date_to'])) {
            $where[] = "EXISTS (SELECT 1 FROM activities a WHERE a.contact_didar_id = p.didar_contact_id AND a.register_date <= ?)";
            $params[] = $filters['activity_date_to'] . " 23:59:59";
        }

        if (!empty($filters['product_id'])) {
            $productId = (int)$filters['product_id'];
            $productName = $this->getProductNameById($productId);

            $or = [];
            $or[] = "EXISTS (
                SELECT 1 FROM deals d
                JOIN deal_products dp ON dp.deal_id = d.id
                WHERE d.contact_didar_id = p.didar_contact_id AND dp.product_id = ?
            )";
            $params[] = $productId;

            // Backward compatibility: some datasets store product/service only in persons.customer_products/requested_services
            if (!empty($productName)) {
                $or[] = "p.customer_products LIKE ?";
                $params[] = '%' . $productName . '%';
                $or[] = "p.requested_services LIKE ?";
                $params[] = '%' . $productName . '%';
            }

            $where[] = '(' . implode(' OR ', $or) . ')';
        } elseif (!empty($filters['product_name'])) {
            $where[] = "(p.customer_products LIKE ? OR p.requested_services LIKE ?)";
            $needle = '%' . $filters['product_name'] . '%';
            $params[] = $needle;
            $params[] = $needle;
        }
        
        if (isset($filters['search']) && !empty($filters['search'])) {
            $where[] = "(p.customer_code LIKE ? OR p.first_name LIKE ? OR p.last_name LIKE ? OR p.mobile_phone LIKE ? OR p.secondary_mobile_phone LIKE ? OR p.mobile_phone_3 LIKE ? OR p.mobile_phone_4 LIKE ? OR p.email LIKE ?)";
            $searchTerm = "%{$filters['search']}%";
            $params = array_merge($params, [$searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm]);
        }
        if (isset($filters['customer_code_search']) && !empty($filters['customer_code_search'])) {
            $where[] = "p.customer_code LIKE ?";
            $params[] = "%{$filters['customer_code_search']}%";
        }
        
        $whereClause = 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 instead of ||
        $sql = "SELECT p.*,
                (SELECT MAX(a.register_date) FROM activities a 
                 WHERE a.contact_didar_id = p.didar_contact_id) as last_activity_date,
                $ownerNameField as owner_name
                FROM persons p
                LEFT JOIN users u ON p.owner_didar_id = u.didar_user_id
                WHERE $whereClause
                ORDER BY p.register_time DESC, p.created_at DESC";
        
        // Add pagination
        $page = isset($filters['page']) ? (int)$filters['page'] : 1;
        $perPage = isset($filters['per_page']) ? (int)$filters['per_page'] : 50;
        $offset = ($page - 1) * $perPage;
        
        $sql .= " LIMIT " . $perPage . " OFFSET " . $offset;
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getLeadsCount($filters = [])
    {
        $pdo = $this->db->getPdo();
        $where = ["p.didar_contact_id IS NOT NULL"];
        $params = [];

        $hasBlataklif = $this->db->hasColumn('persons', 'is_blataklif');
        if ($hasBlataklif && !empty($filters['blataklif_bucket'])) {
            if ($filters['blataklif_bucket'] === 'blataklif') {
                $where[] = "p.is_blataklif = 1";
            } elseif ($filters['blataklif_bucket'] === 'normal') {
                $where[] = "(p.is_blataklif IS NULL OR p.is_blataklif = 0)";
            }
        }
        
        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[] = "p.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[] = "p.owner_didar_id = ?";
            $params[] = $filters['owner_didar_id'];
        }
        
        if (isset($filters['has_previous_purchase'])) {
            if ($filters['has_previous_purchase'] === 'yes') {
                $where[] = "p.has_previous_purchase = 1";
            } elseif ($filters['has_previous_purchase'] === 'no') {
                $where[] = "p.has_previous_purchase = 0";
            }
        }

        if (isset($filters['sale_status'])) {
            if ($filters['sale_status'] === 'success') {
                $where[] = "p.sale_status = 1";
            } elseif ($filters['sale_status'] === 'failed') {
                $where[] = "p.sale_status = 0";
            }
        }

        if (!empty($filters['customer_level'])) {
            $where[] = "p.customer_level = ?";
            $params[] = $filters['customer_level'];
        }

        $registerFromValid2 = isset($filters['register_date_from']) && preg_match('/^\d{4}[-\/]\d{2}[-\/]\d{2}$/', (string)$filters['register_date_from']) === 1;
        $registerToValid2 = isset($filters['register_date_to']) && preg_match('/^\d{4}[-\/]\d{2}[-\/]\d{2}$/', (string)$filters['register_date_to']) === 1;
        $registerFromJalaliValid2 = isset($filters['register_date_from_jalali']) && preg_match('/^\d{4}\/\d{2}\/\d{2}$/', (string)$filters['register_date_from_jalali']) === 1;
        $registerToJalaliValid2 = isset($filters['register_date_to_jalali']) && preg_match('/^\d{4}\/\d{2}\/\d{2}$/', (string)$filters['register_date_to_jalali']) === 1;

        if ($registerFromValid2) {
            $where[] = "(
                (p.register_time IS NOT NULL AND CAST(p.register_time AS CHAR) >= ?)
                OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') >= ?
            )";
            $params[] = str_replace('/', '-', $filters['register_date_from']) . " 00:00:00";
            $params[] = $registerFromJalaliValid2 ? $filters['register_date_from_jalali'] : str_replace('-', '/', $filters['register_date_from']);
        } elseif ($registerFromJalaliValid2) {
            $where[] = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') >= ?";
            $params[] = $filters['register_date_from_jalali'];
        }
        if ($registerToValid2) {
            $where[] = "(
                (p.register_time IS NOT NULL AND CAST(p.register_time AS CHAR) <= ?)
                OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') <= ?
            )";
            $params[] = str_replace('/', '-', $filters['register_date_to']) . " 23:59:59";
            $params[] = $registerToJalaliValid2 ? $filters['register_date_to_jalali'] : str_replace('-', '/', $filters['register_date_to']);
        } elseif ($registerToJalaliValid2) {
            $where[] = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.register_time_jalali,'۰','0'),'۱','1'),'۲','2'),'۳','3'),'۴','4'),'۵','5'),'۶','6'),'۷','7'),'۸','8'),'۹','9') <= ?";
            $params[] = $filters['register_date_to_jalali'];
        }

        if (!empty($filters['activity_date_from']) && !empty($filters['activity_date_to'])) {
            $where[] = "EXISTS (SELECT 1 FROM activities a WHERE a.contact_didar_id = p.didar_contact_id AND a.register_date BETWEEN ? AND ?)";
            $params[] = $filters['activity_date_from'] . " 00:00:00";
            $params[] = $filters['activity_date_to'] . " 23:59:59";
        } elseif (!empty($filters['activity_date_from'])) {
            $where[] = "EXISTS (SELECT 1 FROM activities a WHERE a.contact_didar_id = p.didar_contact_id AND a.register_date >= ?)";
            $params[] = $filters['activity_date_from'] . " 00:00:00";
        } elseif (!empty($filters['activity_date_to'])) {
            $where[] = "EXISTS (SELECT 1 FROM activities a WHERE a.contact_didar_id = p.didar_contact_id AND a.register_date <= ?)";
            $params[] = $filters['activity_date_to'] . " 23:59:59";
        }

        if (!empty($filters['product_id'])) {
            $productId = (int)$filters['product_id'];
            $productName = $this->getProductNameById($productId);

            $or = [];
            $or[] = "EXISTS (
                SELECT 1 FROM deals d
                JOIN deal_products dp ON dp.deal_id = d.id
                WHERE d.contact_didar_id = p.didar_contact_id AND dp.product_id = ?
            )";
            $params[] = $productId;

            if (!empty($productName)) {
                $or[] = "p.customer_products LIKE ?";
                $params[] = '%' . $productName . '%';
                $or[] = "p.requested_services LIKE ?";
                $params[] = '%' . $productName . '%';
            }

            $where[] = '(' . implode(' OR ', $or) . ')';
        } elseif (!empty($filters['product_name'])) {
            $where[] = "(p.customer_products LIKE ? OR p.requested_services LIKE ?)";
            $needle = '%' . $filters['product_name'] . '%';
            $params[] = $needle;
            $params[] = $needle;
        }
        
        if (isset($filters['search']) && !empty($filters['search'])) {
            $where[] = "(p.customer_code LIKE ? OR p.first_name LIKE ? OR p.last_name LIKE ? OR p.mobile_phone LIKE ? OR p.secondary_mobile_phone LIKE ? OR p.mobile_phone_3 LIKE ? OR p.mobile_phone_4 LIKE ? OR p.email LIKE ?)";
            $searchTerm = "%{$filters['search']}%";
            $params = array_merge($params, [$searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm, $searchTerm]);
        }
        if (isset($filters['customer_code_search']) && !empty($filters['customer_code_search'])) {
            $where[] = "p.customer_code LIKE ?";
            $params[] = "%{$filters['customer_code_search']}%";
        }
        
        $whereClause = implode(' AND ', $where);
        
        $sql = "SELECT COUNT(*) FROM persons p WHERE $whereClause";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }
}
