<?php
/**
 * ICYLAB OS - Customer Service
 *
 * Capa unica para alta, edicion, busqueda y validacion operativa de clientes.
 * Mantiene compatibilidad con clientes sincronizados desde FACTO y con soft delete.
 */

require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/../../core/format.php';
require_once __DIR__ . '/../geo/GoogleMapsService.php';

class CustomerService
{
    private $pdo;

    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $pdo ?: db();
    }

    public function normalizeRut($rut)
    {
        $rut = trim((string)$rut);
        if ($rut === '') {
            return '';
        }

        $rut = clean_rut($rut);
        $rut = preg_replace('/\s+/', '', $rut);
        return strtoupper($rut);
    }

    public function normalizePhone($phone)
    {
        return trim((string)$phone);
    }

    private function cleanText($value, $nullable = true)
    {
        $value = trim((string)$value);
        if ($value === '' && $nullable) {
            return null;
        }
        return $value;
    }

    private function intOrNull($value)
    {
        if ($value === null || $value === '') {
            return null;
        }
        return (int)$value;
    }

    private function decimalOrNull($value)
    {
        if ($value === null || $value === '') {
            return null;
        }
        $value = str_replace(',', '.', (string)$value);
        return is_numeric($value) ? (float)$value : null;
    }

    private function supplierPaymentTermKey($termId)
    {
        $termId = $this->intOrNull($termId);
        if (!$termId) {
            return null;
        }

        try {
            $stmt = $this->pdo->prepare("SELECT term_key FROM billing_payment_terms WHERE id = ? AND is_active = 1 LIMIT 1");
            $stmt->execute(array($termId));
            $value = $stmt->fetchColumn();
            return $value !== false ? trim((string)$value) : null;
        } catch (Throwable $e) {
            return null;
        }
    }

    public function mapPayload(array $data)
    {
        $legacyName = $this->cleanText($data['customer_name'] ?? ($data['name'] ?? ''), false);
        $commercialName = $this->cleanText($data['commercial_name'] ?? $legacyName, true);
        $legalName = $this->cleanText($data['legal_name'] ?? $legacyName, true);

        // Invariante de identidad:
        // - legal_name: razón social usada en documentos tributarios.
        // - commercial_name: nombre visible para la gestión comercial.
        // - customer_name: alias heredado que siempre replica el nombre visible.
        $commercialName = $commercialName ?: ($legalName ?: $legacyName);
        $legalName = $legalName ?: ($legacyName !== '' ? $legacyName : $commercialName);
        $customerName = $commercialName ?: ($legalName ?: $legacyName);
        $rut = $this->normalizeRut($data['customer_tax_id'] ?? ($data['rut'] ?? ''));

        $restockDays = isset($data['restock_days']) && $data['restock_days'] !== '' ? max(0, (int)$data['restock_days']) : null;
        $nextRestockDate = array_key_exists('next_restock_date', $data) ? $this->cleanText($data['next_restock_date'], true) : null;

        if ($restockDays !== null && $restockDays <= 0) {
            $nextRestockDate = null;
        }

        $counterpartyType = strtolower(trim((string)($data['counterparty_type'] ?? 'customer')));
        if (!in_array($counterpartyType, array('customer', 'supplier', 'both'), true)) {
            $counterpartyType = 'customer';
        }
        $isSupplier = !empty($data['is_supplier']) || in_array($counterpartyType, array('supplier', 'both'), true);
        if ($isSupplier && $counterpartyType === 'customer') {
            $counterpartyType = 'both';
        }
        $supplierStatus = strtolower(trim((string)($data['supplier_status'] ?? ($isSupplier ? 'active' : 'inactive'))));
        if (!in_array($supplierStatus, array('active', 'inactive', 'blocked'), true)) {
            $supplierStatus = $isSupplier ? 'active' : 'inactive';
        }

        $restockCycleSource = $this->cleanText($data['restock_cycle_source'] ?? ($restockDays > 0 ? 'manual' : 'none'), true) ?: 'none';
        $restockConfidence = isset($data['restock_confidence']) && $data['restock_confidence'] !== ''
            ? (float)$data['restock_confidence']
            : ($restockDays > 0 ? 1.0 : null);
        $restockObservations = isset($data['restock_observations']) && $data['restock_observations'] !== ''
            ? (int)$data['restock_observations']
            : null;
        if ($counterpartyType === 'supplier') {
            $restockDays = null;
            $nextRestockDate = null;
            $restockCycleSource = 'none';
            $restockConfidence = null;
            $restockObservations = null;
        }

        $supplierPaymentTermId = $this->intOrNull($data['supplier_payment_terms_id'] ?? null);
        $paymentTermsRaw = $data['payment_terms'] ?? ($data['facto_payment_term'] ?? 'contado');
        if ($isSupplier && $supplierPaymentTermId && (!array_key_exists('payment_terms', $data) || trim((string)$data['payment_terms']) === '')) {
            $paymentTermsRaw = $this->supplierPaymentTermKey($supplierPaymentTermId) ?: $paymentTermsRaw;
        }

        return array(
            'customer_name' => $customerName,
            'commercial_name' => $commercialName,
            'legal_name' => $legalName,
            'customer_tax_id' => $rut !== '' ? $rut : null,
            'contact_name' => $this->cleanText($data['contact_name'] ?? '', true),
            'contact_first_name' => $this->cleanText($data['contact_first_name'] ?? '', true),
            'contact_last_name' => $this->cleanText($data['contact_last_name'] ?? '', true),
            'email' => $this->cleanText($data['email'] ?? '', true),
            'phone' => $this->cleanText($this->normalizePhone($data['phone'] ?? ''), true),
            'address' => $this->cleanText($data['address'] ?? '', true),
            'district' => $this->cleanText($data['district'] ?? '', true),
            'city' => $this->cleanText($data['city'] ?? '', true),
            'region' => $this->cleanText($data['region'] ?? '', true),
            'country' => $this->cleanText($data['country'] ?? 'Chile', true) ?: 'Chile',
            'country_id' => $this->intOrNull($data['country_id'] ?? null),
            'latitude' => $this->decimalOrNull($data['latitude'] ?? null),
            'longitude' => $this->decimalOrNull($data['longitude'] ?? null),
            'geocode_status' => $this->cleanText($data['geocode_status'] ?? 'pending', true) ?: 'pending',
            'geocode_source' => $this->cleanText($data['geocode_source'] ?? '', true),
            'geocode_query' => $this->cleanText($data['geocode_query'] ?? '', true),
            'geocoded_at' => $this->cleanText($data['geocoded_at'] ?? '', true),
            'website' => $this->cleanText($data['website'] ?? '', true),
            'payment_terms' => icylab_normalize_payment_terms($paymentTermsRaw),
            'default_income_center_id' => $this->intOrNull($data['default_income_center_id'] ?? null),
            'default_cost_center_id' => $this->intOrNull($data['default_cost_center_id'] ?? null),
            'counterparty_type' => $counterpartyType,
            'is_supplier' => $isSupplier ? 1 : 0,
            'supplier_status' => $supplierStatus,
            'supplier_default_cost_center_id' => $this->intOrNull($data['supplier_default_cost_center_id'] ?? ($data['default_cost_center_id'] ?? null)),
            'supplier_payment_terms_id' => $supplierPaymentTermId,
            'business_activity' => $this->cleanText($data['business_activity'] ?? '', true),
            'owner_name' => $this->cleanText($data['owner_name'] ?? '', true),
            'notes' => $this->cleanText($data['notes'] ?? '', true),
            'source' => $this->cleanText($data['source'] ?? 'manual', true) ?: 'manual',
            'restock_days' => $restockDays,
            'next_restock_date' => $nextRestockDate,
            'restock_cycle_source' => $restockCycleSource,
            'restock_confidence' => $restockConfidence,
            'restock_observations' => $restockObservations,
            'last_purchase_date' => $this->cleanText($data['last_purchase_date'] ?? '', true),
            'customer_lifecycle_status' => $this->cleanText($data['customer_lifecycle_status'] ?? 'unknown', true) ?: 'unknown',
            'customer_lifecycle_updated_at' => $this->cleanText($data['customer_lifecycle_updated_at'] ?? '', true)
        );
    }

    public function findByRut($rut, $includeDeleted = true, $excludeId = 0)
    {
        $rut = $this->normalizeRut($rut);
        if ($rut === '') {
            return null;
        }

        $sql = "SELECT * FROM crm_customers WHERE customer_tax_id = ?";
        $params = array($rut);

        if (!$includeDeleted) {
            $sql .= " AND deleted_at IS NULL";
        }

        if ((int)$excludeId > 0) {
            $sql .= " AND id <> ?";
            $params[] = (int)$excludeId;
        }

        $sql .= " ORDER BY deleted_at IS NOT NULL ASC, id ASC LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;
    }

    public function findActiveById($customerId)
    {
        $stmt = $this->pdo->prepare("SELECT * FROM crm_customers WHERE id = ? AND deleted_at IS NULL LIMIT 1");
        $stmt->execute(array((int)$customerId));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function requireActiveById($customerId, $message = 'El cliente seleccionado no existe o está desactivado.')
    {
        $customer = $this->findActiveById($customerId);
        if (!$customer) {
            throw new RuntimeException($message);
        }
        return $customer;
    }

    public function assertRutAvailable($rut, $excludeId = 0)
    {
        $rut = $this->normalizeRut($rut);
        if ($rut === '') {
            return null;
        }

        $existing = $this->findByRut($rut, true, $excludeId);
        if (!$existing) {
            return null;
        }

        if (!empty($existing['deleted_at'])) {
            throw new RuntimeException('Ya existe un cliente desactivado con ese RUT. Reactívalo desde Clientes antes de crear un duplicado.');
        }

        throw new RuntimeException('Ya existe un cliente activo con ese RUT.');
    }

    private function hasCoordinates(array $payload)
    {
        return $payload['latitude'] !== null && $payload['longitude'] !== null;
    }

    private function hasMinimumLocationForGeocode(array $payload)
    {
        return trim((string)($payload['address'] ?? '')) !== ''
            && trim((string)($payload['country'] ?? '')) !== ''
            && (
                trim((string)($payload['district'] ?? '')) !== ''
                || trim((string)($payload['city'] ?? '')) !== ''
                || trim((string)($payload['region'] ?? '')) !== ''
            );
    }

    private function buildGeocodeQuery(array $payload)
    {
        return trim(implode(', ', array_filter(array(
            $payload['address'] ?? '',
            $payload['district'] ?? '',
            $payload['city'] ?? '',
            $payload['region'] ?? '',
            $payload['country'] ?? ''
        ), function ($value) {
            return trim((string)$value) !== '';
        })));
    }

    private function applyAutoGeocode(array $payload, $force = false)
    {
        $hasLatitude = $payload['latitude'] !== null;
        $hasLongitude = $payload['longitude'] !== null;
        if ($hasLatitude !== $hasLongitude) {
            $payload['latitude'] = null;
            $payload['longitude'] = null;
            $payload['geocode_status'] = 'pending';
        }

        if ($this->hasCoordinates($payload)) {
            if (empty($payload['geocoded_at'])) {
                $payload['geocoded_at'] = date('Y-m-d H:i:s');
            }
            if (empty($payload['geocode_status']) || $payload['geocode_status'] === 'pending' || $payload['geocode_status'] === 'manual') {
                $payload['geocode_status'] = 'ok';
            }
            return $payload;
        }

        if (!$force && !$this->hasMinimumLocationForGeocode($payload)) {
            $payload['geocode_status'] = $payload['geocode_status'] ?: 'pending';
            return $payload;
        }

        $query = $this->buildGeocodeQuery($payload);
        if ($query === '') {
            $payload['geocode_status'] = 'pending';
            return $payload;
        }

        try {
            $maps = new GoogleMapsService(null, $payload['country'] ?? 'Chile');
            if (!$maps->isConfigured()) {
                $payload['geocode_status'] = $payload['geocode_status'] ?: 'pending';
                $payload['geocode_source'] = $payload['geocode_source'] ?: null;
                $payload['geocode_query'] = $payload['geocode_query'] ?: $query;
                return $payload;
            }

            $place = $maps->geocode($query, $payload['country'] ?? 'Chile');
            if (!$place || empty($place['latitude']) || empty($place['longitude'])) {
                $payload['latitude'] = null;
                $payload['longitude'] = null;
                $payload['geocode_status'] = 'failed';
                $payload['geocode_source'] = 'google_maps_autosave';
                $payload['geocode_query'] = $query;
                $payload['geocoded_at'] = date('Y-m-d H:i:s');
                return $payload;
            }

            $payload['address'] = !empty($place['address']) ? $place['address'] : $payload['address'];
            $payload['district'] = !empty($place['district']) ? $place['district'] : $payload['district'];
            $payload['city'] = !empty($place['city']) ? $place['city'] : $payload['city'];
            $payload['region'] = !empty($place['region']) ? $place['region'] : $payload['region'];
            $payload['country'] = !empty($place['country']) ? $place['country'] : $payload['country'];
            $payload['latitude'] = (float)$place['latitude'];
            $payload['longitude'] = (float)$place['longitude'];
            $payload['geocode_status'] = 'ok';
            $payload['geocode_source'] = 'google_maps_autosave';
            $payload['geocode_query'] = $query;
            $payload['geocoded_at'] = date('Y-m-d H:i:s');
        } catch (Throwable $e) {
            $payload['latitude'] = null;
            $payload['longitude'] = null;
            $payload['geocode_status'] = 'failed';
            $payload['geocode_source'] = 'google_maps_autosave';
            $payload['geocode_query'] = $query;
            $payload['geocoded_at'] = date('Y-m-d H:i:s');
        }

        return $payload;
    }

    private function locationChanged(array $current, array $payload)
    {
        foreach (array('address', 'district', 'city', 'region', 'country') as $field) {
            $old = trim((string)($current[$field] ?? ''));
            $new = trim((string)($payload[$field] ?? ''));
            if ($old !== $new) {
                return true;
            }
        }
        return false;
    }

    private function requireLocationPayload(array $payload)
    {
        $missing = array();
        foreach (array('country' => 'país', 'address' => 'dirección', 'district' => 'comuna', 'city' => 'ciudad', 'region' => 'región') as $key => $label) {
            if (!isset($payload[$key]) || trim((string)$payload[$key]) === '') {
                $missing[] = $label;
            }
        }

        if (!empty($missing)) {
            throw new RuntimeException('Para crear el cliente debes completar: ' . implode(', ', $missing) . '.');
        }
    }

    public function create(array $data, array $options = array())
    {
        $payload = $this->mapPayload($data);
        $reuseExisting = array_key_exists('reuse_existing', $options) ? (bool)$options['reuse_existing'] : true;
        $requireLocation = array_key_exists('require_location', $options) ? (bool)$options['require_location'] : false;

        if ($payload['customer_name'] === '') {
            throw new RuntimeException('El nombre del cliente es obligatorio.');
        }

        if ($requireLocation) {
            $this->requireLocationPayload($payload);
        }

        if (!empty($payload['customer_tax_id'])) {
            $existing = $this->findByRut($payload['customer_tax_id'], true);
            if ($existing) {
                if (!empty($existing['deleted_at'])) {
                    throw new RuntimeException('Ya existe un cliente desactivado con ese RUT. Reactívalo desde Clientes antes de crear un duplicado.');
                }

                if ($reuseExisting) {
                    return array(
                        'status' => 'reused',
                        'customer' => $existing,
                        'customer_id' => (int)$existing['id']
                    );
                }

                throw new RuntimeException('Ya existe un cliente activo con ese RUT.');
            }
        }

        $payload = $this->applyAutoGeocode($payload, $requireLocation);

        $columns = array(
            'customer_name', 'commercial_name', 'legal_name', 'customer_tax_id',
            'contact_name', 'contact_first_name', 'contact_last_name', 'email', 'phone',
            'address', 'district', 'city', 'region', 'country', 'country_id',
            'latitude', 'longitude', 'geocode_status', 'geocode_source', 'geocode_query', 'geocoded_at',
            'website', 'payment_terms', 'default_income_center_id', 'default_cost_center_id',
            'counterparty_type', 'is_supplier', 'supplier_status', 'supplier_default_cost_center_id', 'supplier_payment_terms_id',
            'business_activity', 'owner_name', 'notes', 'source', 'restock_days', 'next_restock_date',
            'restock_cycle_source', 'restock_confidence', 'restock_observations', 'last_purchase_date',
            'customer_lifecycle_status', 'customer_lifecycle_updated_at'
        );

        $placeholders = array_fill(0, count($columns), '?');
        $values = array();
        foreach ($columns as $column) {
            $values[] = $payload[$column];
        }

        $stmt = $this->pdo->prepare("INSERT INTO crm_customers (" . implode(', ', $columns) . ", created_at, updated_at) VALUES (" . implode(', ', $placeholders) . ", NOW(), NOW())");
        $stmt->execute($values);

        $id = (int)$this->pdo->lastInsertId();
        $customer = $this->findActiveById($id);

        return array(
            'status' => 'created',
            'customer' => $customer,
            'customer_id' => $id
        );
    }

    public function update($customerId, array $data, array $options = array())
    {
        $customerId = (int)$customerId;
        if ($customerId <= 0) {
            throw new RuntimeException('ID de cliente inválido.');
        }

        $current = $this->requireActiveById($customerId, 'Cliente no encontrado o desactivado.');
        if (array_key_exists('supplier_payment_terms_id', $data) && !array_key_exists('payment_terms', $data)) {
            $termKey = $this->supplierPaymentTermKey($data['supplier_payment_terms_id']);
            if ($termKey !== null) $data['payment_terms'] = $termKey;
        }
        $payload = $this->mapPayload(array_merge($current, $data));
        $locationChanged = $this->locationChanged($current, $payload);

        if ($locationChanged) {
            $postedLat = trim((string)($data['latitude'] ?? ''));
            $postedLng = trim((string)($data['longitude'] ?? ''));
            $postedQuery = trim((string)($data['geocode_query'] ?? ''));
            $currentQuery = trim((string)($current['geocode_query'] ?? ''));
            $hasFreshCoordinates = $postedLat !== '' && $postedLng !== '' && $postedQuery !== '' && $postedQuery !== $currentQuery;

            if (!$hasFreshCoordinates) {
                $payload['latitude'] = null;
                $payload['longitude'] = null;
                $payload['geocode_status'] = 'pending';
                $payload['geocode_source'] = null;
                $payload['geocode_query'] = null;
                $payload['geocoded_at'] = null;
            }
        }

        $payload = $this->applyAutoGeocode($payload, $locationChanged);

        if ($payload['customer_name'] === '') {
            throw new RuntimeException('El nombre del cliente es obligatorio.');
        }

        if (!empty($payload['customer_tax_id'])) {
            $existing = $this->findByRut($payload['customer_tax_id'], true, $customerId);
            if ($existing) {
                if (!empty($existing['deleted_at'])) {
                    throw new RuntimeException('Ya existe un cliente desactivado con ese RUT. Reactívalo o corrige el RUT antes de guardar.');
                }
                throw new RuntimeException('Ya existe otro cliente activo con ese RUT.');
            }
        }

        if (array_key_exists('restock_days', $data)) {
            $newRestockDays = $payload['restock_days'];
            if ($newRestockDays !== null && $newRestockDays > 0) {
                $baseDate = trim((string)($current['last_purchase_date'] ?? ''));
                $payload['next_restock_date'] = $baseDate !== '' ? date('Y-m-d', strtotime($baseDate . ' +' . $newRestockDays . ' days')) : null;
                $payload['restock_cycle_source'] = 'manual';
                $payload['restock_confidence'] = 1.0;
                $payload['restock_observations'] = null;
            } else {
                $payload['restock_days'] = null;
                $payload['next_restock_date'] = null;
                $payload['restock_cycle_source'] = 'none';
                $payload['restock_confidence'] = null;
                $payload['restock_observations'] = null;
            }
        } elseif (($payload['counterparty_type'] ?? 'customer') !== 'supplier') {
            foreach (array('restock_days','next_restock_date','restock_cycle_source','restock_confidence','restock_observations','last_purchase_date','customer_lifecycle_status','customer_lifecycle_updated_at') as $restockField) {
                $payload[$restockField] = $current[$restockField] ?? null;
            }
        } else {
            $payload['last_purchase_date'] = null;
            $payload['customer_lifecycle_status'] = 'unknown';
            $payload['customer_lifecycle_updated_at'] = date('Y-m-d H:i:s');
        }

        $columns = array(
            'customer_name', 'commercial_name', 'legal_name', 'customer_tax_id',
            'contact_name', 'contact_first_name', 'contact_last_name', 'email', 'phone',
            'address', 'district', 'city', 'region', 'country', 'country_id',
            'latitude', 'longitude', 'geocode_status', 'geocode_source', 'geocode_query', 'geocoded_at',
            'website', 'payment_terms', 'default_income_center_id', 'default_cost_center_id',
            'counterparty_type', 'is_supplier', 'supplier_status', 'supplier_default_cost_center_id', 'supplier_payment_terms_id',
            'business_activity', 'owner_name', 'notes', 'source', 'restock_days', 'next_restock_date',
            'restock_cycle_source', 'restock_confidence', 'restock_observations', 'last_purchase_date',
            'customer_lifecycle_status', 'customer_lifecycle_updated_at'
        );

        $sets = array();
        $values = array();
        foreach ($columns as $column) {
            $sets[] = $column . ' = ?';
            $values[] = $payload[$column];
        }
        $values[] = $customerId;

        $stmt = $this->pdo->prepare("UPDATE crm_customers SET " . implode(', ', $sets) . ", updated_at = NOW() WHERE id = ? AND deleted_at IS NULL");
        $stmt->execute($values);

        $this->syncCustomerSnapshots($customerId);

        return $this->findActiveById($customerId);
    }

    public function syncCustomerSnapshots($customerId)
    {
        $customer = $this->findActiveById($customerId);
        if (!$customer) {
            return array('opportunities' => 0);
        }

        $stmt = $this->pdo->prepare("\n            UPDATE crm_opportunities\n            SET customer_name = ?, customer_tax_id = ?, updated_at = updated_at\n            WHERE customer_id = ?\n              AND deleted_at IS NULL\n        ");
        $stmt->execute(array($customer['customer_name'], $customer['customer_tax_id'], (int)$customerId));

        $opportunityCount = $stmt->rowCount();

        $activityCount = 0;
        try {
            $activityStmt = $this->pdo->prepare("
                UPDATE crm_activities
                SET customer_name = ?
                WHERE customer_id = ?
            ");
            $activityStmt->execute(array($customer['customer_name'], (int)$customerId));
            $activityCount = $activityStmt->rowCount();
        } catch (Throwable $e) {
            // Compatibilidad con instalaciones antiguas sin customer_id en actividades.
        }

        return array('opportunities' => $opportunityCount, 'activities' => $activityCount);
    }

    public function search($query, $limit = 12)
    {
        $query = trim((string)$query);
        if (mb_strlen($query) < 2) {
            return array();
        }

        $limit = max(1, min(50, (int)$limit));
        $like = '%' . $query . '%';
        $compactRut = preg_replace('/[^0-9kK]/', '', $query);
        $compactLike = '%' . $compactRut . '%';

        $sql = "
            SELECT id,
                   customer_name,
                   commercial_name,
                   legal_name,
                   customer_tax_id,
                   tax_id_type,
                   export_tax_id_code,
                   export_tax_id_type,
                   email,
                   email_dte,
                   phone,
                   address,
                   district,
                   city,
                   region,
                   country,
                   country_id,
                   export_country_id,
                   payment_terms,
                   facto_payment_term,
                   business_activity,
                   default_income_center_id,
                   default_cost_center_id,
                   counterparty_type,
                   is_supplier,
                   supplier_status,
                   supplier_default_cost_center_id,
                   supplier_payment_terms_id,
                   latitude,
                   longitude
            FROM crm_customers
            WHERE deleted_at IS NULL
              AND (
                  customer_name LIKE ?
                  OR commercial_name LIKE ?
                  OR legal_name LIKE ?
                  OR customer_tax_id LIKE ?
                  OR REPLACE(REPLACE(customer_tax_id, '.', ''), '-', '') LIKE ?
                  OR email LIKE ?
                  OR city LIKE ?
                  OR district LIKE ?
                  OR address LIKE ?
              )
            ORDER BY
                CASE
                    WHEN commercial_name LIKE ? THEN 0
                    WHEN legal_name LIKE ? THEN 1
                    WHEN customer_name LIKE ? THEN 2
                    ELSE 3
                END,
                commercial_name ASC,
                legal_name ASC,
                customer_name ASC
            LIMIT " . $limit . "
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(
            $like,
            $like,
            $like,
            $like,
            $compactRut !== '' ? $compactLike : $like,
            $like,
            $like,
            $like,
            $like,
            $like,
            $like,
            $like
        ));

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function publicPayload(array $customer)
    {
        return array(
            'id' => (int)($customer['id'] ?? 0),
            'customer_name' => $customer['customer_name'] ?? '',
            'commercial_name' => $customer['commercial_name'] ?? '',
            'legal_name' => !empty($customer['legal_name']) ? $customer['legal_name'] : ($customer['customer_name'] ?? ''),
            'display_name' => !empty($customer['commercial_name']) ? $customer['commercial_name'] : (!empty($customer['legal_name']) ? $customer['legal_name'] : ($customer['customer_name'] ?? '')),
            'customer_tax_id' => $customer['customer_tax_id'] ?? '',
            'tax_id_type' => $customer['tax_id_type'] ?? 'CL-RUT',
            'export_tax_id_code' => $customer['export_tax_id_code'] ?? '',
            'export_tax_id_type' => $customer['export_tax_id_type'] ?? '',
            'email' => $customer['email'] ?? '',
            'email_dte' => $customer['email_dte'] ?? '',
            'phone' => $customer['phone'] ?? '',
            'address' => $customer['address'] ?? '',
            'district' => $customer['district'] ?? '',
            'city' => $customer['city'] ?? '',
            'region' => $customer['region'] ?? '',
            'country' => $customer['country'] ?? 'Chile',
            'country_id' => $customer['country_id'] ?? null,
            'export_country_id' => $customer['export_country_id'] ?? null,
            'business_activity' => $customer['business_activity'] ?? '',
            'default_income_center_id' => $customer['default_income_center_id'] ?? null,
            'default_cost_center_id' => $customer['default_cost_center_id'] ?? null,
            'counterparty_type' => $customer['counterparty_type'] ?? 'customer',
            'is_supplier' => !empty($customer['is_supplier']) ? 1 : 0,
            'supplier_status' => $customer['supplier_status'] ?? 'inactive',
            'supplier_default_cost_center_id' => $customer['supplier_default_cost_center_id'] ?? null,
            'supplier_payment_terms_id' => $customer['supplier_payment_terms_id'] ?? null,
            'payment_terms' => icylab_normalize_payment_terms($customer['payment_terms'] ?? ($customer['facto_payment_term'] ?? 'contado')),
            'payment_terms_label' => icylab_payment_terms_label($customer['payment_terms'] ?? ($customer['facto_payment_term'] ?? 'contado')),
            'latitude' => $customer['latitude'] ?? null,
            'longitude' => $customer['longitude'] ?? null
        );
    }
}
