<?php
namespace EasyChat\Library;

use EasyChat\Library\Messenger\SmsPhoneNumber;
use EasyChat\Model\Customer;

class CustomerContactService {
    public const STATUS_AUTO_DETECTED = 'auto_detected';
    public const STATUS_OPERATOR_CONFIRMED = 'operator_confirmed';
    public const STATUS_PROVIDER_CONFIRMED = 'provider_confirmed';
    public const STATUS_ARCHIVED = 'archived';

    private $registry;
    private $db;
    private $prefix;

    public function __construct($registry) {
        $this->registry = $registry;
        $this->db = $registry->get('db');
        $this->prefix = easychat_db_prefix();
    }

    public function saveCustomerFromPanel(int $chat_id, string $name, string $email, string $phone, int $manager_id = 0): array {
        $customer_id = (new Customer($this->registry))->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }
        return $this->withCustomerMutationLock($customer_id, function () use ($customer_id, $chat_id, $name, $email, $phone, $manager_id) {
            $name = $this->cleanName($name);
            if ($name !== '') {
                $now = $this->now();
                $this->db->query(
                    "UPDATE `{$this->prefix}sessions`
                     SET `customer_name` = '" . $this->db->escape($name) . "'
                     WHERE `chat_id` = " . (int)$chat_id
                );
                $this->db->query(
                    "UPDATE `{$this->prefix}customers`
                     SET `display_name` = '" . $this->db->escape($name) . "',
                         `date_modified` = '" . $this->db->escape($now) . "'
                     WHERE `customer_id` = " . (int)$customer_id
                );
            }

            $this->upsertContactUnlocked($customer_id, 'email', $email, [
                'source' => 'operator',
                'source_chat_id' => $chat_id,
                'added_by' => $manager_id,
                'status' => self::STATUS_OPERATOR_CONFIRMED,
                'primary' => true,
            ]);
            $this->upsertContactUnlocked($customer_id, 'phone', $phone, [
                'source' => 'operator',
                'source_chat_id' => $chat_id,
                'added_by' => $manager_id,
                'status' => self::STATUS_OPERATOR_CONFIRMED,
                'primary' => true,
            ]);

            return ['success' => true, 'customer_id' => $customer_id];
        }, ['success' => false, 'error' => 'customer_contact_busy']);
    }

    public function recordDetectedContactsForChat(int $chat_id, string $email = '', string $phone = '', string $source = 'ai', int $message_id = 0): array {
        $customer_id = (new Customer($this->registry))->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }

        return $this->withCustomerMutationLock($customer_id, function () use ($customer_id, $chat_id, $email, $phone, $source, $message_id) {
            $saved = [];
            foreach (['email' => $email, 'phone' => $phone] as $type => $value) {
                $saved_contact = $this->upsertContactUnlocked($customer_id, $type, $value, [
                    'source' => $source,
                    'source_chat_id' => $chat_id,
                    'source_message_id' => $message_id,
                    'status' => self::STATUS_AUTO_DETECTED,
                    'primary' => !$this->hasPrimaryContact($customer_id, $type),
                ]);
                if ($saved_contact) {
                    $saved[$type] = $saved_contact;
                }
            }
            return ['success' => true, 'customer_id' => $customer_id, 'contacts' => $saved];
        }, ['success' => false, 'error' => 'customer_contact_busy']);
    }

    public function recordCmsConfirmedContactsForChat(int $chat_id, string $email, string $phone, string $provider): array {
        $customer_id = (new Customer($this->registry))->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) return ['success' => false, 'error' => 'chat_not_found'];
        return $this->withCustomerMutationLock($customer_id, function () use ($customer_id, $chat_id, $email, $phone, $provider) {
            $saved = [];
            foreach (['email' => $email, 'phone' => $phone] as $type => $value) {
                $value = $this->normalizeValue($type, $value);
                if ($value === '') continue;
                $existing = $this->findContactByHash($customer_id, $type, $this->valueHash($type, $value));
                if ((string)($existing['status'] ?? '') === self::STATUS_ARCHIVED) continue;
                $saved[$type] = $this->upsertContactUnlocked($customer_id, $type, $value, [
                    'source' => 'cms_' . $this->cleanSource($provider),
                    'source_chat_id' => $chat_id,
                    'status' => self::STATUS_PROVIDER_CONFIRMED,
                    'verification_source' => 'authenticated_cms_session',
                    'verified_at' => $this->now(),
                    'primary' => !$this->hasOperatorConfirmedPrimary($customer_id, $type),
                ]);
            }
            return ['success' => true, 'customer_id' => $customer_id, 'contacts' => $saved];
        }, ['success' => false, 'error' => 'customer_contact_busy']);
    }

    public function upsertContact(int $customer_id, string $type, string $value, array $options = []): array {
        if ($customer_id <= 0) return [];
        return $this->withCustomerMutationLock($customer_id, function () use ($customer_id, $type, $value, $options) {
            return $this->upsertContactUnlocked($customer_id, $type, $value, $options);
        }, []);
    }

    



    public function recordProviderConfirmedPhoneForChat(
        int $chat_id,
        string $phone,
        string $verification_source = 'provider_profile',
        string $provider = 'max'
    ): array {
        $phone = SmsPhoneNumber::normalize($phone);
        if ($chat_id <= 0 || $phone === '') {
            return ['success' => false, 'error' => 'phone_invalid'];
        }
        $customer_id = (new Customer($this->registry))->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }
        $provider = strtolower(trim($provider));
        if (!in_array($provider, ['max', 'whatsapp-personal'], true)) {
            $provider = 'provider';
        }
        return $this->withCustomerMutationLock($customer_id, function () use ($customer_id, $chat_id, $phone, $verification_source, $provider) {
            $existing = $this->findContactByHash($customer_id, 'phone', $this->valueHash('phone', $phone));
            if ((string)($existing['status'] ?? '') === self::STATUS_ARCHIVED) {
                 
                 
                 
                return [
                    'success' => true,
                    'customer_id' => $customer_id,
                    'ignored' => 'contact_archived',
                ];
            }
            if ((string)($existing['status'] ?? '') === self::STATUS_OPERATOR_CONFIRMED) {
                 
                 
                 
                return [
                    'success' => true,
                    'customer_id' => $customer_id,
                    'contact' => $this->payload($existing),
                    'ignored' => 'operator_confirmation_authoritative',
                ];
            }
            $primary = !$this->hasOperatorConfirmedPrimary($customer_id, 'phone');
            $contact = $this->upsertContactUnlocked($customer_id, 'phone', $phone, [
                'source' => $provider,
                'source_chat_id' => $chat_id,
                'status' => self::STATUS_PROVIDER_CONFIRMED,
                'verification_source' => $verification_source,
                'verified_at' => $this->now(),
                'primary' => $primary,
            ]);
            return ['success' => true, 'customer_id' => $customer_id, 'contact' => $contact];
        }, ['success' => false, 'error' => 'customer_contact_busy']);
    }

    private function upsertContactUnlocked(int $customer_id, string $type, string $value, array $options = []): array {
        $type = $this->normalizeType($type);
        $value = $this->normalizeValue($type, $value);
        if ($customer_id <= 0 || $type === '' || $value === '') {
            return [];
        }

        $status = $this->normalizeStatus((string)($options['status'] ?? self::STATUS_AUTO_DETECTED));
        $source = $this->cleanSource((string)($options['source'] ?? ''));
        $source_chat_id = (int)($options['source_chat_id'] ?? 0);
        $source_message_id = (int)($options['source_message_id'] ?? 0);
        $added_by = (int)($options['added_by'] ?? 0);
        $primary = !empty($options['primary']);
        $verification_source = $this->cleanSource((string)($options['verification_source'] ?? ''));
        $verified_at = $this->normalizeDate((string)($options['verified_at'] ?? ''));
        $hash = $this->valueHash($type, $value);
        $now = $this->now();

        if ($primary) {
            $this->db->query(
                "UPDATE `{$this->prefix}customer_contact_points`
                 SET `is_primary` = 0,
                     `date_modified` = '" . $this->db->escape($now) . "'
                 WHERE `customer_id` = " . (int)$customer_id . "
                   AND `type` = '" . $this->db->escape($type) . "'
                   AND `status` <> '" . self::STATUS_ARCHIVED . "'"
            );
        }

        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_contact_points`
                (`customer_id`, `type`, `value`, `value_hash`, `source`, `source_customer_id`,
                 `is_primary`, `status`, `verification_source`, `verified_at`,
                 `source_chat_id`, `source_message_id`, `added_by`,
                 `date_added`, `date_modified`, `last_seen_at`)
             VALUES
                (" . (int)$customer_id . ",
                 '" . $this->db->escape($type) . "',
                 '" . $this->db->escape($value) . "',
                 '" . $this->db->escape($hash) . "',
                 '" . $this->db->escape($source) . "',
                 0,
                 " . ($primary ? 1 : 0) . ",
                 '" . $this->db->escape($status) . "',
                 '" . $this->db->escape($verification_source) . "',
                 " . ($verified_at !== '' ? "'" . $this->db->escape($verified_at) . "'" : 'NULL') . ",
                 " . max(0, $source_chat_id) . ",
                 " . max(0, $source_message_id) . ",
                 " . max(0, $added_by) . ",
                 '" . $this->db->escape($now) . "',
                 '" . $this->db->escape($now) . "',
                 '" . $this->db->escape($now) . "')
             ON DUPLICATE KEY UPDATE
                `value` = VALUES(`value`),
                `source` = IF(
                    VALUES(`status`) IN ('" . self::STATUS_OPERATOR_CONFIRMED . "', '" . self::STATUS_PROVIDER_CONFIRMED . "'),
                    VALUES(`source`),
                    IF(`{$this->prefix}customer_contact_points`.`source` = '', VALUES(`source`), `{$this->prefix}customer_contact_points`.`source`)
                ),
                `is_primary` = GREATEST(`{$this->prefix}customer_contact_points`.`is_primary`, VALUES(`is_primary`)),
                `status` = " . $this->statusMergeSql("`{$this->prefix}customer_contact_points`.`status`", "VALUES(`status`)") . ",
                `verification_source` = IF(VALUES(`verification_source`) <> '', VALUES(`verification_source`), `{$this->prefix}customer_contact_points`.`verification_source`),
                `verified_at` = COALESCE(VALUES(`verified_at`), `{$this->prefix}customer_contact_points`.`verified_at`),
                `source_chat_id` = IF(VALUES(`source_chat_id`) > 0, VALUES(`source_chat_id`), `{$this->prefix}customer_contact_points`.`source_chat_id`),
                `source_message_id` = IF(VALUES(`source_message_id`) > 0, VALUES(`source_message_id`), `{$this->prefix}customer_contact_points`.`source_message_id`),
                `added_by` = IF(VALUES(`added_by`) > 0, VALUES(`added_by`), `{$this->prefix}customer_contact_points`.`added_by`),
                `date_modified` = VALUES(`date_modified`),
                `last_seen_at` = VALUES(`last_seen_at`)"
        );

        $this->touchCustomer($customer_id);
        return $this->findContactByHash($customer_id, $type, $hash);
    }

    public function primaryContactsForCustomer(int $customer_id): array {
        $contacts = ['email' => '', 'phone' => ''];
        if ($customer_id <= 0) {
            return $contacts;
        }

        $query = $this->db->query(
            "SELECT `type`, `value`
             FROM `{$this->prefix}customer_contact_points`
             WHERE `customer_id` = " . (int)$customer_id . "
               AND `is_primary` = 1
               AND `status` <> '" . self::STATUS_ARCHIVED . "'
             ORDER BY FIELD(`type`, 'email', 'phone'), `contact_point_id` ASC
             LIMIT 4"
        );
        foreach ($query->rows as $row) {
            $type = (string)$row['type'];
            if (isset($contacts[$type]) && $contacts[$type] === '') {
                $contacts[$type] = (string)$row['value'];
            }
        }
        return $contacts;
    }

    public function primaryContactsForCustomers(array $customer_ids): array {
        $ids = [];
        foreach ($customer_ids as $customer_id) {
            $customer_id = (int)$customer_id;
            if ($customer_id > 0) {
                $ids[$customer_id] = $customer_id;
            }
        }
        if (!$ids) {
            return [];
        }

        $result = [];
        foreach ($ids as $customer_id) {
            $result[$customer_id] = ['email' => '', 'phone' => ''];
        }

        $query = $this->db->query(
            "SELECT `customer_id`, `type`, `value`
             FROM `{$this->prefix}customer_contact_points`
             WHERE `customer_id` IN (" . implode(',', array_values($ids)) . ")
               AND `is_primary` = 1
               AND `status` <> '" . self::STATUS_ARCHIVED . "'
             ORDER BY `customer_id` ASC, FIELD(`type`, 'email', 'phone'), `contact_point_id` ASC"
        );
        foreach ($query->rows as $row) {
            $customer_id = (int)$row['customer_id'];
            $type = (string)$row['type'];
            if (isset($result[$customer_id][$type]) && $result[$customer_id][$type] === '') {
                $result[$customer_id][$type] = (string)$row['value'];
            }
        }

        return $result;
    }

    public function contactPointsForCustomer(int $customer_id): array {
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}customer_contact_points`
             WHERE `customer_id` = " . (int)$customer_id . "
               AND `status` <> '" . self::STATUS_ARCHIVED . "'
             ORDER BY FIELD(`type`, 'email', 'phone'), `is_primary` DESC,
                      FIELD(`status`, '" . self::STATUS_OPERATOR_CONFIRMED . "', '" . self::STATUS_PROVIDER_CONFIRMED . "', '" . self::STATUS_AUTO_DETECTED . "', '" . self::STATUS_ARCHIVED . "'),
                      `contact_point_id` ASC
             LIMIT 50"
        );

        $items = [];
        foreach ($query->rows as $row) {
            $items[] = $this->payload($row);
        }
        return $items;
    }

    public function confirmedContextContacts(int $customer_id): array {
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}customer_contact_points`
             WHERE `customer_id` = " . (int)$customer_id . "
               AND `status` IN ('" . self::STATUS_OPERATOR_CONFIRMED . "', '" . self::STATUS_PROVIDER_CONFIRMED . "')
             ORDER BY `is_primary` DESC, FIELD(`type`, 'email', 'phone'), `contact_point_id` ASC
             LIMIT 8"
        );
        $items = [];
        foreach ($query->rows as $row) {
            $items[] = $this->payload($row);
        }
        return $items;
    }

    public function makeContactPrimaryForChat(int $chat_id, int $contact_point_id, int $manager_id = 0): array {
        $customer_id = (new Customer($this->registry))->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) return ['success' => false, 'error' => 'contact_not_found'];
        return $this->withCustomerMutationLock($customer_id, function () use ($customer_id, $contact_point_id, $manager_id) {
            $contact = $this->contactPointForCustomer($customer_id, $contact_point_id);
            if (!$contact) return ['success' => false, 'error' => 'contact_not_found'];

            $now = $this->now();
            try {
                $this->db->query('START TRANSACTION');
                $this->db->query(
                    "UPDATE `{$this->prefix}customer_contact_points`
                     SET `is_primary` = 0,
                         `date_modified` = '" . $this->db->escape($now) . "'
                     WHERE `customer_id` = " . (int)$customer_id . "
                       AND `type` = '" . $this->db->escape((string)$contact['type']) . "'
                       AND `status` <> '" . self::STATUS_ARCHIVED . "'"
                );
                $this->db->query(
                    "UPDATE `{$this->prefix}customer_contact_points`
                     SET `is_primary` = 1,
                         `status` = '" . self::STATUS_OPERATOR_CONFIRMED . "',
                         `added_by` = IF(" . (int)$manager_id . " > 0, " . (int)$manager_id . ", `added_by`),
                         `date_modified` = '" . $this->db->escape($now) . "',
                         `last_seen_at` = '" . $this->db->escape($now) . "'
                     WHERE `contact_point_id` = " . (int)$contact_point_id . "
                       AND `customer_id` = " . (int)$customer_id
                );
                $this->touchCustomer($customer_id);
                $this->db->query('COMMIT');
            } catch (\Throwable $e) {
                try {
                    $this->db->query('ROLLBACK');
                } catch (\Throwable $rollback_error) {
                     
                }
                return ['success' => false, 'error' => 'contact_primary_failed'];
            }

            return ['success' => true, 'customer_id' => $customer_id];
        }, ['success' => false, 'error' => 'customer_contact_busy']);
    }

    public function archiveContactForChat(int $chat_id, int $contact_point_id): array {
        $customer_id = (new Customer($this->registry))->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) return ['success' => false, 'error' => 'contact_not_found'];
        return $this->withCustomerMutationLock($customer_id, function () use ($customer_id, $contact_point_id) {
            $contact = $this->contactPointForCustomer($customer_id, $contact_point_id);
            if (!$contact) return ['success' => false, 'error' => 'contact_not_found'];
            if ((int)($contact['is_primary'] ?? 0) === 1) {
                return ['success' => false, 'error' => 'primary_contact_delete_denied'];
            }

            $now = $this->now();
            $this->db->query(
                "UPDATE `{$this->prefix}customer_contact_points`
                 SET `is_primary` = 0,
                     `status` = '" . self::STATUS_ARCHIVED . "',
                     `date_modified` = '" . $this->db->escape($now) . "'
                 WHERE `contact_point_id` = " . (int)$contact_point_id . "
                   AND `customer_id` = " . (int)$customer_id . "
                   AND `is_primary` = 0"
            );
            $this->touchCustomer($customer_id);

            return ['success' => true, 'customer_id' => $customer_id];
        }, ['success' => false, 'error' => 'customer_contact_busy']);
    }

    public function payload(array $row): array {
        return [
            'contact_point_id' => (int)($row['contact_point_id'] ?? 0),
            'type' => (string)($row['type'] ?? ''),
            'value' => (string)($row['value'] ?? ''),
            'source' => (string)($row['source'] ?? ''),
            'source_customer_id' => (int)($row['source_customer_id'] ?? 0),
            'is_primary' => (int)($row['is_primary'] ?? 0),
            'status' => (string)($row['status'] ?? self::STATUS_AUTO_DETECTED),
            'confirmed' => in_array((string)($row['status'] ?? ''), [self::STATUS_OPERATOR_CONFIRMED, self::STATUS_PROVIDER_CONFIRMED], true) ? 1 : 0,
            'verification_source' => (string)($row['verification_source'] ?? ''),
            'verified_at' => (string)($row['verified_at'] ?? ''),
            'source_chat_id' => (int)($row['source_chat_id'] ?? 0),
            'source_message_id' => (int)($row['source_message_id'] ?? 0),
            'added_by' => (int)($row['added_by'] ?? 0),
            'date_added' => (string)($row['date_added'] ?? ''),
            'date_modified' => (string)($row['date_modified'] ?? ''),
            'last_seen_at' => (string)($row['last_seen_at'] ?? ''),
        ];
    }

    



    public function acquireCustomerMutationLock(int $customer_id, int $timeout_seconds = 3): string {
        if ($customer_id <= 0) return '';
        $timeout_seconds = max(0, min(10, $timeout_seconds));
        $scope = (defined('DB_DATABASE') ? (string)DB_DATABASE : '') . '|' . $this->prefix . '|' . $customer_id;
        $lock_name = 'ec_customer_' . substr(hash('sha256', $scope), 0, 32);
        try {
            $query = $this->db->query(
                "SELECT GET_LOCK('" . $this->db->escape($lock_name) . "', " . $timeout_seconds . ") AS `locked`"
            );
            return $query && (int)($query->row['locked'] ?? 0) === 1 ? $lock_name : '';
        } catch (\Throwable $e) {
            return '';
        }
    }

    public function releaseCustomerMutationLock(string $lock_name): void {
        if (!preg_match('/^ec_customer_[a-f0-9]{32}$/D', $lock_name)) return;
        try {
            $this->db->query("SELECT RELEASE_LOCK('" . $this->db->escape($lock_name) . "')");
        } catch (\Throwable $ignored) {
        }
    }

    private function withCustomerMutationLock(int $customer_id, callable $callback, $busy_result) {
        $lock_name = $this->acquireCustomerMutationLock($customer_id);
        if ($lock_name === '') return $busy_result;
        try {
            return $callback();
        } finally {
            $this->releaseCustomerMutationLock($lock_name);
        }
    }

    private function contactPointForCustomer(int $customer_id, int $contact_point_id): array {
        if ($customer_id <= 0 || $contact_point_id <= 0) return [];
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}customer_contact_points`
             WHERE `contact_point_id` = " . (int)$contact_point_id . "
               AND `customer_id` = " . (int)$customer_id . "
               AND `status` <> '" . self::STATUS_ARCHIVED . "'
             LIMIT 1"
        );
        return ($query && $query->num_rows) ? $query->row : [];
    }

    private function hasPrimaryContact(int $customer_id, string $type): bool {
        $type = $this->normalizeType($type);
        if ($customer_id <= 0 || $type === '') {
            return false;
        }
        $query = $this->db->query(
            "SELECT `contact_point_id`
             FROM `{$this->prefix}customer_contact_points`
             WHERE `customer_id` = " . (int)$customer_id . "
               AND `type` = '" . $this->db->escape($type) . "'
               AND `is_primary` = 1
               AND `status` <> '" . self::STATUS_ARCHIVED . "'
             LIMIT 1"
        );
        return $query && $query->num_rows > 0;
    }

    private function hasOperatorConfirmedPrimary(int $customer_id, string $type): bool {
        $type = $this->normalizeType($type);
        if ($customer_id <= 0 || $type === '') return false;
        $query = $this->db->query(
            "SELECT `contact_point_id`
             FROM `{$this->prefix}customer_contact_points`
             WHERE `customer_id` = " . (int)$customer_id . "
               AND `type` = '" . $this->db->escape($type) . "'
               AND `is_primary` = 1
               AND `status` = '" . self::STATUS_OPERATOR_CONFIRMED . "'
             LIMIT 1"
        );
        return $query && $query->num_rows > 0;
    }

    private function findContactByHash(int $customer_id, string $type, string $hash): array {
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}customer_contact_points`
             WHERE `customer_id` = " . (int)$customer_id . "
               AND `type` = '" . $this->db->escape($type) . "'
               AND `value_hash` = '" . $this->db->escape($hash) . "'
             LIMIT 1"
        );
        return ($query && $query->num_rows) ? $this->payload($query->row) : [];
    }

    private function touchCustomer(int $customer_id): void {
        $this->db->query(
            "UPDATE `{$this->prefix}customers`
             SET `date_modified` = '" . $this->db->escape($this->now()) . "'
             WHERE `customer_id` = " . (int)$customer_id
        );
    }

    private function normalizeType(string $type): string {
        $type = strtolower(trim($type));
        return in_array($type, ['email', 'phone'], true) ? $type : '';
    }

    private function normalizeStatus(string $status): string {
        return in_array($status, [self::STATUS_AUTO_DETECTED, self::STATUS_OPERATOR_CONFIRMED, self::STATUS_PROVIDER_CONFIRMED, self::STATUS_ARCHIVED], true)
            ? $status
            : self::STATUS_AUTO_DETECTED;
    }

    private function normalizeValue(string $type, string $value): string {
        if ($type === 'email') {
            $value = mb_strtolower(trim($value), 'UTF-8');
            if ($value === '' || !filter_var($value, FILTER_VALIDATE_EMAIL)) {
                return '';
            }
            return mb_substr($value, 0, 255, 'UTF-8');
        }
        if ($type === 'phone') {
            return SmsPhoneNumber::normalize($value);
        }
        return '';
    }

    private function valueHash(string $type, string $value): string {
        return hash('sha256', $type . '|' . mb_strtolower($value, 'UTF-8'));
    }

    private function cleanName(string $value): string {
        $value = trim(strip_tags($value));
        $value = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/u', '', $value);
        return mb_substr((string)$value, 0, 255, 'UTF-8');
    }

    private function cleanSource(string $source): string {
        $source = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $source));
        return mb_substr($source, 0, 32, 'UTF-8');
    }

    private function statusMergeSql(string $current, string $incoming): string {
        return "CASE
            WHEN {$current} = '" . self::STATUS_OPERATOR_CONFIRMED . "' THEN {$current}
            WHEN {$incoming} = '" . self::STATUS_OPERATOR_CONFIRMED . "' THEN {$incoming}
            WHEN {$current} = '" . self::STATUS_ARCHIVED . "' THEN {$current}
            WHEN {$incoming} = '" . self::STATUS_PROVIDER_CONFIRMED . "' THEN {$incoming}
            WHEN {$current} = '" . self::STATUS_PROVIDER_CONFIRMED . "' THEN {$current}
            ELSE {$current}
        END";
    }

    private function normalizeDate(string $value): string {
        $value = trim($value);
        return preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/D', $value) === 1 ? $value : '';
    }

    private function now(): string {
        return (new AppClock($this->registry))->nowSql();
    }
}
