<?php
/**
 * Persistent, append-only journal and deterministic queue for VeriFactu.
 *
 * Canonical records are never updated except for transmission state and AEAT
 * response metadata. The original XML, fingerprint input and request digest
 * are immutable after insertion.
 */

if (!defined('ABSPATH')) {
    exit;
}

class VerifactuRepository
{
    const STATUS_GENERATED = 'GENERATED';
    const STATUS_SENDING = 'SENDING';
    const STATUS_ACCEPTED = 'ACCEPTED';
    const STATUS_ACCEPTED_WITH_ERRORS = 'ACCEPTED_WITH_ERRORS';
    const STATUS_REJECTED = 'REJECTED';
    const STATUS_TRANSPORT_UNKNOWN = 'TRANSPORT_UNKNOWN';
    const STATUS_CANCELLED_LOCALLY = 'CANCELLED_LOCALLY';

    const QUEUE_WAITING_INVOICE = 'WAITING_INVOICE';
    const QUEUE_PENDING = 'PENDING';
    const QUEUE_PROCESSING = 'PROCESSING';
    const QUEUE_RETRY = 'RETRY';
    const QUEUE_DONE = 'DONE';
    const QUEUE_ERROR = 'ERROR';

    public static function installSchema()
    {
        $db = DdvWpDb::getInstance();
        $queries = array(
            'CREATE TABLE IF NOT EXISTS `' . DdvWpDb::prefix() . 'ddverifactu_records` (
                `id_record` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `id_shop` INT UNSIGNED NOT NULL DEFAULT 0,
                `chain_key` CHAR(64) NOT NULL,
                `installation_id` VARCHAR(100) NOT NULL,
                `id_order` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `id_order_invoice` BIGINT UNSIGNED DEFAULT NULL,
                `id_order_slip` BIGINT UNSIGNED DEFAULT NULL,
                `operation` VARCHAR(16) NOT NULL,
                `record_mode` VARCHAR(20) NOT NULL DEFAULT \'ORIGINAL\',
                `document_kind` VARCHAR(20) NOT NULL,
                `source_record_id` BIGINT UNSIGNED DEFAULT NULL,
                `issuer_nif` VARCHAR(20) NOT NULL,
                `document_number` VARCHAR(60) NOT NULL,
                `issue_date` CHAR(10) NOT NULL,
                `invoice_type` VARCHAR(2) DEFAULT NULL,
                `rectification_type` CHAR(1) DEFAULT NULL,
                `subsanacion` CHAR(1) DEFAULT NULL,
                `rechazo_previo` CHAR(1) DEFAULT NULL,
                `sin_registro_previo` CHAR(1) DEFAULT NULL,
                `intent_key` VARCHAR(191) NOT NULL,
                `record_timestamp` VARCHAR(35) NOT NULL,
                `fingerprint` CHAR(64) NOT NULL,
                `fingerprint_input` TEXT NOT NULL,
                `previous_record_id` BIGINT UNSIGNED DEFAULT NULL,
                `previous_issuer_nif` VARCHAR(20) DEFAULT NULL,
                `previous_document_number` VARCHAR(60) DEFAULT NULL,
                `previous_issue_date` CHAR(10) DEFAULT NULL,
                `previous_fingerprint` CHAR(64) DEFAULT NULL,
                `request_xml` MEDIUMTEXT NOT NULL,
                `request_sha256` CHAR(64) NOT NULL,
                `soap_request` MEDIUMTEXT NULL,
                `soap_response` MEDIUMTEXT NULL,
                `environment` VARCHAR(16) NOT NULL,
                `local_status` VARCHAR(32) NOT NULL,
                `aeat_submission_status` VARCHAR(32) DEFAULT NULL,
                `aeat_record_status` VARCHAR(32) DEFAULT NULL,
                `duplicate_status` VARCHAR(32) DEFAULT NULL,
                `error_code` VARCHAR(32) DEFAULT NULL,
                `error_description` TEXT NULL,
                `csv` VARCHAR(100) DEFAULT NULL,
                `aeat_request_id` VARCHAR(100) DEFAULT NULL,
                `aeat_presentation_timestamp` VARCHAR(40) DEFAULT NULL,
                `duplicate_request_id` VARCHAR(100) DEFAULT NULL,
                `wait_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
                `retry_count` INT UNSIGNED NOT NULL DEFAULT 0,
                `next_retry_at` DATETIME DEFAULT NULL,
                `last_attempt_at` DATETIME DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                `updated_at` DATETIME NOT NULL,
                PRIMARY KEY (`id_record`),
                UNIQUE KEY `uniq_env_fingerprint` (`environment`, `fingerprint`),
                KEY `idx_chain` (`environment`, `chain_key`, `id_record`),
                KEY `idx_invoice` (`environment`, `id_order_invoice`, `operation`, `local_status`),
                KEY `idx_slip` (`environment`, `id_order_slip`, `operation`, `local_status`),
                KEY `idx_identity` (`environment`, `issuer_nif`, `document_number`, `issue_date`, `operation`),
                KEY `idx_intent` (`environment`, `intent_key`(140), `local_status`)
            ) ENGINE=' . 'InnoDB' . ' DEFAULT CHARSET=utf8mb4',

            'CREATE TABLE IF NOT EXISTS `' . DdvWpDb::prefix() . 'ddverifactu_queue` (
                `id_queue` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `queue_key` VARCHAR(191) NOT NULL,
                `environment` VARCHAR(16) NOT NULL,
                `chain_key` CHAR(64) NOT NULL,
                `id_shop` INT UNSIGNED NOT NULL DEFAULT 0,
                `id_order` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `id_order_invoice` BIGINT UNSIGNED DEFAULT NULL,
                `id_order_slip` BIGINT UNSIGNED DEFAULT NULL,
                `id_record` BIGINT UNSIGNED DEFAULT NULL,
                `action` VARCHAR(32) NOT NULL,
                `payload_json` TEXT NULL,
                `status` VARCHAR(24) NOT NULL,
                `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
                `next_attempt_at` DATETIME DEFAULT NULL,
                `last_error` TEXT NULL,
                `locked_at` DATETIME DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                `updated_at` DATETIME NOT NULL,
                PRIMARY KEY (`id_queue`),
                UNIQUE KEY `uniq_queue_key` (`queue_key`),
                KEY `idx_queue_due` (`environment`, `status`, `next_attempt_at`, `id_queue`),
                KEY `idx_queue_record` (`id_record`),
                KEY `idx_queue_order` (`id_order`, `status`)
            ) ENGINE=' . 'InnoDB' . ' DEFAULT CHARSET=utf8mb4',

            'CREATE TABLE IF NOT EXISTS `' . DdvWpDb::prefix() . 'ddverifactu_transmissions` (
                `id_transmission` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `id_record` BIGINT UNSIGNED NOT NULL,
                `attempt_no` INT UNSIGNED NOT NULL,
                `incident` TINYINT(1) NOT NULL DEFAULT 0,
                `endpoint` VARCHAR(255) NOT NULL,
                `request_xml` MEDIUMTEXT NOT NULL,
                `request_sha256` CHAR(64) NOT NULL,
                `soap_request` MEDIUMTEXT NULL,
                `soap_response` MEDIUMTEXT NULL,
                `transport_status` VARCHAR(32) NOT NULL,
                `aeat_record_status` VARCHAR(32) DEFAULT NULL,
                `error_message` TEXT NULL,
                `certificate_subject` VARCHAR(255) DEFAULT NULL,
                `certificate_serial` VARCHAR(100) DEFAULT NULL,
                `started_at` DATETIME NOT NULL,
                `finished_at` DATETIME DEFAULT NULL,
                PRIMARY KEY (`id_transmission`),
                UNIQUE KEY `uniq_record_attempt` (`id_record`, `attempt_no`),
                KEY `idx_transmission_record` (`id_record`, `id_transmission`)
            ) ENGINE=' . 'InnoDB' . ' DEFAULT CHARSET=utf8mb4',

            'CREATE TABLE IF NOT EXISTS `' . DdvWpDb::prefix() . 'ddverifactu_events` (
                `id_event` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `severity` VARCHAR(12) NOT NULL,
                `event_type` VARCHAR(64) NOT NULL,
                `id_shop` INT UNSIGNED NOT NULL DEFAULT 0,
                `id_order` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `id_order_invoice` BIGINT UNSIGNED DEFAULT NULL,
                `id_order_slip` BIGINT UNSIGNED DEFAULT NULL,
                `id_record` BIGINT UNSIGNED DEFAULT NULL,
                `message` TEXT NOT NULL,
                `context_json` MEDIUMTEXT NULL,
                `created_at` DATETIME NOT NULL,
                PRIMARY KEY (`id_event`),
                KEY `idx_event_created` (`created_at`, `severity`),
                KEY `idx_event_record` (`id_record`),
                KEY `idx_event_order` (`id_order`)
            ) ENGINE=' . 'InnoDB' . ' DEFAULT CHARSET=utf8mb4'
        );

        foreach ($queries as $sql) {
            if (!$db->execute($sql)) {
                return false;
            }
        }

        $recordColumns = array(
            // The 1.2.0 beta could leave this table created without the
            // environment column. It must be repaired before any composite
            // index that references it is added.
            'environment' => 'VARCHAR(16) NOT NULL DEFAULT \'\'',
            'chain_key' => 'CHAR(64) NOT NULL DEFAULT \'\'',
            'installation_id' => 'VARCHAR(100) NOT NULL DEFAULT \'\'',
            'record_mode' => 'VARCHAR(20) NOT NULL DEFAULT \'ORIGINAL\'',
            'source_record_id' => 'BIGINT UNSIGNED DEFAULT NULL',
            'fingerprint_input' => 'TEXT NULL',
            'request_sha256' => 'CHAR(64) NULL',
            'next_retry_at' => 'DATETIME DEFAULT NULL',
            'last_attempt_at' => 'DATETIME DEFAULT NULL',
            'aeat_request_id' => 'VARCHAR(100) DEFAULT NULL',
            'aeat_presentation_timestamp' => 'VARCHAR(40) DEFAULT NULL',
            'duplicate_request_id' => 'VARCHAR(100) DEFAULT NULL',
        );
        foreach ($recordColumns as $name => $definition) {
            if (!self::columnExists(DdvWpDb::prefix() . 'ddverifactu_records', $name)
                && !$db->execute(
                    'ALTER TABLE `' . DdvWpDb::prefix() . 'ddverifactu_records` ADD `'
                    . DdvWpDb::quoteIdentifier($name) . '` ' . $definition
                )
            ) {
                return false;
            }
        }

        // Assign legacy/beta rows to the environment that is active during
        // the migration. New rows always provide the environment explicitly.
        $environment = VerifactuConfig::getEnvironment();
        if (!$db->execute(
            'UPDATE `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . " SET `environment` = '" . DdvWpDb::escape($environment) . "'"
            . " WHERE `environment` IS NULL OR `environment` = ''"
        )) {
            return false;
        }

        // Older builds used a global fingerprint unique index, which prevents
        // the same valid chain from being tested in pre-production and later
        // generated independently in production.
        if (self::indexExists(DdvWpDb::prefix() . 'ddverifactu_records', 'uniq_fingerprint')) {
            if (!$db->execute('ALTER TABLE `' . DdvWpDb::prefix() . 'ddverifactu_records` DROP INDEX `uniq_fingerprint`')) {
                return false;
            }
        }
        if (!self::indexExists(DdvWpDb::prefix() . 'ddverifactu_records', 'uniq_env_fingerprint')) {
            if (!$db->execute(
                'ALTER TABLE `' . DdvWpDb::prefix() . 'ddverifactu_records`'
                . ' ADD UNIQUE KEY `uniq_env_fingerprint` (`environment`, `fingerprint`)'
            )) {
                return false;
            }
        }

        $recordIndexes = array(
            'idx_chain' => 'KEY `idx_chain` (`environment`, `chain_key`, `id_record`)',
            'idx_invoice' => 'KEY `idx_invoice` (`environment`, `id_order_invoice`, `operation`, `local_status`)',
            'idx_slip' => 'KEY `idx_slip` (`environment`, `id_order_slip`, `operation`, `local_status`)',
            'idx_identity' => 'KEY `idx_identity` (`environment`, `issuer_nif`, `document_number`, `issue_date`, `operation`)',
            // Prefixing intent_key keeps the index below the legacy InnoDB
            // 767-byte limit on utf8mb4 installations supported by PS 1.7.8.
            'idx_intent' => 'KEY `idx_intent` (`environment`, `intent_key`(140), `local_status`)',
        );
        foreach ($recordIndexes as $name => $definition) {
            if (!self::indexExists(DdvWpDb::prefix() . 'ddverifactu_records', $name)
                && !$db->execute(
                    'ALTER TABLE `' . DdvWpDb::prefix() . 'ddverifactu_records` ADD ' . $definition
                )
            ) {
                return false;
            }
        }

        // Backfill integrity metadata for records created by the 1.1.16 beta.
        $db->execute(
            'UPDATE `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . ' SET `request_sha256` = SHA2(`request_xml`, 256)'
            . ' WHERE (`request_sha256` IS NULL OR `request_sha256` = \'\')'
            . ' AND `request_xml` IS NOT NULL'
        );
        $db->execute(
            'UPDATE `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . ' SET `chain_key` = SHA2(CONCAT(`environment`, \'|\', `issuer_nif`, \'|legacy\'), 256),'
            . ' `installation_id` = \'legacy\''
            . ' WHERE `chain_key` IS NULL OR `chain_key` = \'\''
        );

        return true;
    }

    public static function buildChainKey($environment, $issuerNif, $installationId)
    {
        return hash(
            'sha256',
            strtolower(trim((string) $environment)) . '|'
            . strtoupper(trim((string) $issuerNif)) . '|'
            . trim((string) $installationId)
        );
    }

    public function acquireChainLock($chainKey, $timeoutSeconds)
    {
        return $this->acquireLock('ddvf_chain_' . substr((string) $chainKey, 0, 48), $timeoutSeconds);
    }

    public function releaseChainLock($chainKey)
    {
        $this->releaseLock('ddvf_chain_' . substr((string) $chainKey, 0, 48));
    }

    public function acquireQueueLock($timeoutSeconds)
    {
        return $this->acquireLock('ddvf_queue_' . VerifactuConfig::getEnvironment(), $timeoutSeconds);
    }

    public function releaseQueueLock()
    {
        $this->releaseLock('ddvf_queue_' . VerifactuConfig::getEnvironment());
    }

    public function getLastGeneratedRecord($chainKey, $environment = null)
    {
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . " WHERE `environment` = '" . DdvWpDb::escape($this->environment($environment)) . "'"
            . " AND `chain_key` = '" . DdvWpDb::escape($chainKey) . "'"
            . ' ORDER BY `id_record` DESC'
        );

        if ($row) {
            return $row;
        }
        if (VerifactuConfig::getLegacyChainDecision() === 'use_latest') {
            return $this->getLastLegacyRecord();
        }

        return null;
    }

    public function findLegacyForIdentity($issuerNif, $number, $issueDate)
    {
        if (!self::tableExists(DdvWpDb::prefix() . 'ddverifactu_huella')) {
            return null;
        }
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_huella`'
            . " WHERE `id_emisor` = '" . DdvWpDb::escape($issuerNif) . "'"
            . " AND `num_serie` = '" . DdvWpDb::escape($number) . "'"
            . " AND `fecha_expedicion` = '" . DdvWpDb::escape($issueDate) . "'"
            . ' ORDER BY `id_huella` DESC'
        );
        return $row ?: null;
    }

    public function hasLegacyRows()
    {
        return self::tableExists(DdvWpDb::prefix() . 'ddverifactu_huella')
            && (int) DdvWpDb::getInstance()->getValue(
                "SELECT COUNT(*) FROM `" . DdvWpDb::prefix() . "ddverifactu_huella` WHERE `huella` IS NOT NULL AND `huella` <> ''"
            ) > 0;
    }

    public function insertGeneratedRecord(array $record)
    {
        $now = date('Y-m-d H:i:s');
        $required = array(
            'id_shop', 'chain_key', 'installation_id', 'id_order', 'operation', 'record_mode',
            'document_kind', 'issuer_nif', 'document_number', 'issue_date', 'intent_key',
            'record_timestamp', 'fingerprint', 'fingerprint_input', 'request_xml', 'request_sha256',
            'environment'
        );
        foreach ($required as $field) {
            if (!array_key_exists($field, $record)) {
                throw new Exception('Falta ' . $field . ' al conservar el registro VeriFactu.');
            }
        }

        $existing = $this->findLatestByIntent($record['intent_key'], $record['environment']);
        if ($existing) {
            return (int) $existing['id_record'];
        }

        $data = array(
            'id_shop' => (int) $record['id_shop'],
            'chain_key' => (string) $record['chain_key'],
            'installation_id' => (string) $record['installation_id'],
            'id_order' => (int) $record['id_order'],
            'id_order_invoice' => !empty($record['id_order_invoice']) ? (int) $record['id_order_invoice'] : null,
            'id_order_slip' => !empty($record['id_order_slip']) ? (int) $record['id_order_slip'] : null,
            'operation' => (string) $record['operation'],
            'record_mode' => (string) $record['record_mode'],
            'document_kind' => (string) $record['document_kind'],
            'source_record_id' => !empty($record['source_record_id']) ? (int) $record['source_record_id'] : null,
            'issuer_nif' => (string) $record['issuer_nif'],
            'document_number' => (string) $record['document_number'],
            'issue_date' => (string) $record['issue_date'],
            'invoice_type' => !empty($record['invoice_type']) ? (string) $record['invoice_type'] : null,
            'rectification_type' => !empty($record['rectification_type']) ? (string) $record['rectification_type'] : null,
            'subsanacion' => !empty($record['subsanacion']) ? (string) $record['subsanacion'] : null,
            'rechazo_previo' => !empty($record['rechazo_previo']) ? (string) $record['rechazo_previo'] : null,
            'sin_registro_previo' => !empty($record['sin_registro_previo']) ? (string) $record['sin_registro_previo'] : null,
            'intent_key' => (string) $record['intent_key'],
            'record_timestamp' => (string) $record['record_timestamp'],
            'fingerprint' => strtoupper((string) $record['fingerprint']),
            'fingerprint_input' => (string) $record['fingerprint_input'],
            'previous_record_id' => !empty($record['previous_record_id']) ? (int) $record['previous_record_id'] : null,
            'previous_issuer_nif' => !empty($record['previous_issuer_nif']) ? (string) $record['previous_issuer_nif'] : null,
            'previous_document_number' => !empty($record['previous_document_number']) ? (string) $record['previous_document_number'] : null,
            'previous_issue_date' => !empty($record['previous_issue_date']) ? (string) $record['previous_issue_date'] : null,
            'previous_fingerprint' => !empty($record['previous_fingerprint']) ? (string) $record['previous_fingerprint'] : null,
            'request_xml' => (string) $record['request_xml'],
            'request_sha256' => strtolower((string) $record['request_sha256']),
            'environment' => $this->environment($record['environment']),
            'local_status' => self::STATUS_GENERATED,
            'created_at' => $now,
            'updated_at' => $now,
        );

        if (!DdvWpDb::getInstance()->insert('ddverifactu_records', $data, true)) {
            $existing = $this->findLatestByIntent($record['intent_key'], $record['environment']);
            if ($existing) {
                return (int) $existing['id_record'];
            }
            throw new Exception('No se ha podido conservar el registro de facturacion generado.');
        }

        $id = (int) DdvWpDb::getInstance()->Insert_ID();
        $this->logEvent('INFO', 'RECORD_GENERATED', array(
            'id_shop' => (int) $record['id_shop'],
            'id_order' => (int) $record['id_order'],
            'id_order_invoice' => !empty($record['id_order_invoice']) ? (int) $record['id_order_invoice'] : null,
            'id_order_slip' => !empty($record['id_order_slip']) ? (int) $record['id_order_slip'] : null,
            'id_record' => $id,
            'message' => 'Registro ' . $record['operation'] . ' generado para ' . $record['document_number'] . '.',
            'context' => array('fingerprint' => strtoupper($record['fingerprint'])),
        ));

        return $id;
    }

    public function getById($idRecord)
    {
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_records` WHERE `id_record` = ' . (int) $idRecord
        );

        return $row ?: null;
    }

    public function findLatestByIntent($intentKey, $environment = null)
    {
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . " WHERE `environment` = '" . DdvWpDb::escape($this->environment($environment)) . "'"
            . " AND `intent_key` = '" . DdvWpDb::escape($intentKey) . "'"
            . ' ORDER BY `id_record` DESC'
        );

        return $row ?: null;
    }

    public function findLatestForIdentity($issuerNif, $number, $issueDate, $operation, $environment = null)
    {
        return $this->findIdentity($issuerNif, $number, $issueDate, $operation, '', $environment);
    }

    public function findAcceptedForIdentity($issuerNif, $number, $issueDate, $operation, $environment = null)
    {
        return $this->findIdentity(
            $issuerNif,
            $number,
            $issueDate,
            $operation,
            " AND `local_status` IN ('" . self::STATUS_ACCEPTED . "','" . self::STATUS_ACCEPTED_WITH_ERRORS . "')",
            $environment
        );
    }

    public function findAcceptedCancellationForIdentity($issuerNif, $number, $issueDate, $environment = null)
    {
        return $this->findAcceptedForIdentity(
            $issuerNif,
            $number,
            $issueDate,
            'ANULACION',
            $environment
        );
    }

    public function getLatestByInvoice($idOrderInvoice, $environment = null)
    {
        return $this->getLatestByDocument('id_order_invoice', (int) $idOrderInvoice, '', $environment);
    }

    public function getLatestAltaByInvoice($idOrderInvoice, $environment = null)
    {
        return $this->getLatestByDocument(
            'id_order_invoice',
            (int) $idOrderInvoice,
            " AND `operation` = 'ALTA'",
            $environment
        );
    }

    public function getLatestAcceptedByInvoice($idOrderInvoice, $environment = null)
    {
        return $this->getLatestByDocument(
            'id_order_invoice',
            (int) $idOrderInvoice,
            " AND `operation` = 'ALTA' AND `local_status` IN ('" . self::STATUS_ACCEPTED . "','" . self::STATUS_ACCEPTED_WITH_ERRORS . "')",
            $environment
        );
    }

    public function getLatestBySlip($idOrderSlip, $environment = null)
    {
        return $this->getLatestByDocument('id_order_slip', (int) $idOrderSlip, '', $environment);
    }

    public function getLatestAltaBySlip($idOrderSlip, $environment = null)
    {
        return $this->getLatestByDocument(
            'id_order_slip',
            (int) $idOrderSlip,
            " AND `operation` = 'ALTA'",
            $environment
        );
    }

    public function getLatestAcceptedBySlip($idOrderSlip, $environment = null)
    {
        return $this->getLatestByDocument(
            'id_order_slip',
            (int) $idOrderSlip,
            " AND `operation` = 'ALTA' AND `local_status` IN ('" . self::STATUS_ACCEPTED . "','" . self::STATUS_ACCEPTED_WITH_ERRORS . "')",
            $environment
        );
    }

    public function hasEarlierPendingTransmission(array $record)
    {
        $count = (int) DdvWpDb::getInstance()->getValue(
            'SELECT COUNT(*) FROM `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . " WHERE `environment` = '" . DdvWpDb::escape($record['environment']) . "'"
            . " AND `chain_key` = '" . DdvWpDb::escape($record['chain_key']) . "'"
            . ' AND `id_record` < ' . (int) $record['id_record']
            . " AND `local_status` IN ('" . self::STATUS_GENERATED . "','" . self::STATUS_SENDING
            . "','" . self::STATUS_TRANSPORT_UNKNOWN . "')"
        );

        return $count > 0;
    }

    public function markSending($idRecord)
    {
        return $this->updateRecordState((int) $idRecord, array(
            'local_status' => self::STATUS_SENDING,
            'retry_count' => array('sql' => '`retry_count` + 1'),
            'last_attempt_at' => date('Y-m-d H:i:s'),
            'next_retry_at' => null,
        ));
    }

    public function updateFromResponse($idRecord, array $result, $soapRequest, $soapResponse)
    {
        $localStatus = self::STATUS_REJECTED;
        if (!empty($result['accepted'])) {
            $localStatus = !empty($result['accepted_with_errors'])
                ? self::STATUS_ACCEPTED_WITH_ERRORS
                : self::STATUS_ACCEPTED;
        }
        return $this->updateRecordState((int) $idRecord, array(
            'local_status' => $localStatus,
            'aeat_submission_status' => isset($result['submission_status']) ? (string) $result['submission_status'] : null,
            'aeat_record_status' => isset($result['record_status']) ? (string) $result['record_status'] : null,
            'duplicate_status' => isset($result['duplicate_status']) ? (string) $result['duplicate_status'] : null,
            'error_code' => !empty($result['error_code']) ? (string) $result['error_code'] : null,
            'error_description' => !empty($result['error_description']) ? (string) $result['error_description'] : null,
            'csv' => !empty($result['csv']) ? (string) $result['csv'] : null,
            'aeat_request_id' => !empty($result['request_id']) ? (string) $result['request_id'] : null,
            'aeat_presentation_timestamp' => !empty($result['presentation_timestamp']) ? (string) $result['presentation_timestamp'] : null,
            'duplicate_request_id' => !empty($result['duplicate_request_id']) ? (string) $result['duplicate_request_id'] : null,
            'wait_seconds' => isset($result['wait_seconds']) ? max(0, (int) $result['wait_seconds']) : 0,
            'soap_request' => (string) $soapRequest,
            'soap_response' => (string) $soapResponse,
            'next_retry_at' => null,
        ));
    }

    public function markTransportUnknown($idRecord, $message, $soapRequest, $soapResponse, $retryDelay)
    {
        $next = date('Y-m-d H:i:s', time() + max(1, (int) $retryDelay));
        return $this->updateRecordState((int) $idRecord, array(
            'local_status' => self::STATUS_TRANSPORT_UNKNOWN,
            'error_description' => (string) $message,
            'soap_request' => (string) $soapRequest,
            'soap_response' => (string) $soapResponse,
            'next_retry_at' => $next,
        ));
    }

    /**
     * SOAP/client/protocol errors mean the remittance was not accepted as a
     * valid request. Keep the immutable fiscal record generated, retain the
     * raw SOAP evidence, and require an explicit retry after correction.
     */
    public function markProtocolError($idRecord, $message, $soapRequest, $soapResponse)
    {
        return $this->updateRecordState((int) $idRecord, array(
            'local_status' => self::STATUS_GENERATED,
            'error_description' => (string) $message,
            'soap_request' => (string) $soapRequest,
            'soap_response' => (string) $soapResponse,
            'next_retry_at' => null,
        ));
    }

    public function getWaitRemainingSeconds($chainKey, $environment = null)
    {
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT `wait_seconds`, `updated_at` FROM `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . " WHERE `environment` = '" . DdvWpDb::escape($this->environment($environment)) . "'"
            . " AND `chain_key` = '" . DdvWpDb::escape($chainKey) . "'"
            . ' AND `wait_seconds` > 0 ORDER BY `id_record` DESC'
        );
        if (!$row) {
            return 0;
        }
        $elapsed = max(0, time() - (int) strtotime($row['updated_at']));
        return max(0, (int) $row['wait_seconds'] - $elapsed);
    }

    public function startTransmission(array $record, $requestXml, $incident, array $certificateInfo = array())
    {
        $attemptNo = (int) DdvWpDb::getInstance()->getValue(
            'SELECT COALESCE(MAX(`attempt_no`), 0) + 1 FROM `' . DdvWpDb::prefix() . 'ddverifactu_transmissions`'
            . ' WHERE `id_record` = ' . (int) $record['id_record']
        );
        $subject = '';
        $serial = '';
        if (!empty($certificateInfo['subject']) && is_array($certificateInfo['subject'])) {
            $parts = array();
            foreach ($certificateInfo['subject'] as $key => $value) {
                $parts[] = $key . '=' . (is_array($value) ? implode(',', $value) : $value);
            }
            $subject = implode(', ', $parts);
        }
        if (!empty($certificateInfo['serialNumberHex'])) {
            $serial = (string) $certificateInfo['serialNumberHex'];
        } elseif (!empty($certificateInfo['serialNumber'])) {
            $serial = (string) $certificateInfo['serialNumber'];
        }
        $data = array(
            'id_record' => (int) $record['id_record'],
            'attempt_no' => $attemptNo,
            'incident' => $incident ? 1 : 0,
            'endpoint' => VerifactuConfig::getSoapEndpoint(),
            'request_xml' => (string) $requestXml,
            'request_sha256' => hash('sha256', (string) $requestXml),
            'transport_status' => 'STARTED',
            'certificate_subject' => $subject !== '' ? substr($subject, 0, 255) : null,
            'certificate_serial' => $serial !== '' ? substr($serial, 0, 100) : null,
            'started_at' => date('Y-m-d H:i:s'),
        );
        if (!DdvWpDb::getInstance()->insert('ddverifactu_transmissions', $data, true)) {
            throw new Exception('No se ha podido abrir la evidencia del intento de transmision.');
        }
        return (int) DdvWpDb::getInstance()->Insert_ID();
    }

    public function finishTransmission($idTransmission, $status, $soapRequest, $soapResponse, $aeatStatus, $errorMessage)
    {
        return DdvWpDb::getInstance()->update(
            'ddverifactu_transmissions',
            array(
                'transport_status' => (string) $status,
                'soap_request' => (string) $soapRequest,
                'soap_response' => (string) $soapResponse,
                'aeat_record_status' => $aeatStatus !== '' ? (string) $aeatStatus : null,
                'error_message' => $errorMessage !== '' ? (string) $errorMessage : null,
                'finished_at' => date('Y-m-d H:i:s'),
            ),
            '`id_transmission` = ' . (int) $idTransmission,
            0,
            true
        );
    }

    public function enqueue(array $task)
    {
        $required = array('queue_key', 'environment', 'chain_key', 'id_shop', 'id_order', 'action', 'status');
        foreach ($required as $field) {
            if (!array_key_exists($field, $task)) {
                throw new Exception('Falta ' . $field . ' al crear la cola VeriFactu.');
            }
        }
        $existing = $this->getQueueByKey($task['queue_key']);
        if ($existing) {
            if (in_array($existing['status'], array(self::QUEUE_ERROR, self::QUEUE_RETRY), true)) {
                DdvWpDb::getInstance()->update(
                    'ddverifactu_queue',
                    array(
                        'status' => (string) $task['status'],
                        'next_attempt_at' => array_key_exists('next_attempt_at', $task)
                            ? ($task['next_attempt_at'] === null ? null : (string) $task['next_attempt_at'])
                            : date('Y-m-d H:i:s'),
                        'last_error' => null,
                        'updated_at' => date('Y-m-d H:i:s'),
                    ),
                    '`id_queue` = ' . (int) $existing['id_queue'],
                    0,
                    true
                );
                return (int) $existing['id_queue'];
            }
            if ($existing['status'] === self::QUEUE_PENDING
                && (int) $existing['attempts'] === 0
                && array_key_exists('next_attempt_at', $task)
                && $task['next_attempt_at'] === null) {
                DdvWpDb::getInstance()->update(
                    'ddverifactu_queue',
                    array(
                        'next_attempt_at' => null,
                        'locked_at' => null,
                        'updated_at' => date('Y-m-d H:i:s'),
                    ),
                    '`id_queue` = ' . (int) $existing['id_queue'],
                    0,
                    true
                );
            }
            return (int) $existing['id_queue'];
        }

        $now = date('Y-m-d H:i:s');
        $data = array(
            'queue_key' => substr((string) $task['queue_key'], 0, 191),
            'environment' => $this->environment($task['environment']),
            'chain_key' => (string) $task['chain_key'],
            'id_shop' => (int) $task['id_shop'],
            'id_order' => (int) $task['id_order'],
            'id_order_invoice' => !empty($task['id_order_invoice']) ? (int) $task['id_order_invoice'] : null,
            'id_order_slip' => !empty($task['id_order_slip']) ? (int) $task['id_order_slip'] : null,
            'id_record' => !empty($task['id_record']) ? (int) $task['id_record'] : null,
            'action' => (string) $task['action'],
            'payload_json' => isset($task['payload']) ? json_encode($task['payload'], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
            'status' => (string) $task['status'],
            'attempts' => 0,
            'next_attempt_at' => array_key_exists('next_attempt_at', $task)
                ? ($task['next_attempt_at'] === null ? null : (string) $task['next_attempt_at'])
                : $now,
            'created_at' => $now,
            'updated_at' => $now,
        );
        if (!DdvWpDb::getInstance()->insert('ddverifactu_queue', $data, true)) {
            $existing = $this->getQueueByKey($task['queue_key']);
            if ($existing) {
                return (int) $existing['id_queue'];
            }
            throw new Exception('No se ha podido crear la tarea VeriFactu.');
        }
        return (int) DdvWpDb::getInstance()->Insert_ID();
    }

    public function getQueueByKey($queueKey)
    {
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_queue`'
            . " WHERE `queue_key` = '" . DdvWpDb::escape(substr((string) $queueKey, 0, 191)) . "'"
        );
        return $row ?: null;
    }

    public function completeWaitingInvoiceTasks($idOrder)
    {
        return DdvWpDb::getInstance()->update(
            'ddverifactu_queue',
            array('status' => self::QUEUE_DONE, 'updated_at' => date('Y-m-d H:i:s')),
            '`id_order` = ' . (int) $idOrder . " AND `status` = '" . self::QUEUE_WAITING_INVOICE . "'"
        );
    }

    /**
     * Repairs only the legacy <=1.2.11 Codigo[102] state created before the
     * explicit SOAP transport was installed. It is intentionally narrow and
     * idempotent so a genuine 1.2.13 Server/102 keeps its normal retry backoff.
     */
    public function repairLegacyCodigo102Queue($environment = null)
    {
        $environment = $this->environment($environment);
        $db = DdvWpDb::getInstance();
        $records = DdvWpDb::prefix() . 'ddverifactu_records';
        $queue = DdvWpDb::prefix() . 'ddverifactu_queue';
        $legacyPrefix = 'No se ha podido completar la remision VeriFactu: Codigo[102]';
        $changed = 0;

        $recordIds = (array) $db->executeS(
            'SELECT `id_record` FROM `' . DdvWpDb::quoteIdentifier($records) . '`'
            . " WHERE `environment` = '" . DdvWpDb::escape($environment) . "'"
            . " AND `error_description` LIKE '" . DdvWpDb::escape($legacyPrefix) . "%'"
            . " AND `local_status` IN ('" . self::STATUS_TRANSPORT_UNKNOWN . "','" . self::STATUS_GENERATED . "')"
        );
        if (!empty($recordIds)) {
            $ids = array();
            foreach ($recordIds as $row) {
                $ids[] = (int) $row['id_record'];
            }
            $ids = array_values(array_filter(array_unique($ids)));
            if (!empty($ids)) {
                $idList = implode(',', $ids);
                if ($db->execute(
                    'UPDATE `' . DdvWpDb::quoteIdentifier($records) . '`'
                    . " SET `local_status` = '" . self::STATUS_GENERATED . "',"
                    . ' `next_retry_at` = NULL, `error_code` = NULL, `error_description` = NULL'
                    . " WHERE `environment` = '" . DdvWpDb::escape($environment) . "' AND `id_record` IN (" . $idList . ')'
                )) {
                    $changed += count($ids);
                }
                $db->execute(
                    'UPDATE `' . DdvWpDb::quoteIdentifier($queue) . '`'
                    . " SET `status` = '" . self::QUEUE_PENDING . "', `next_attempt_at` = NULL,"
                    . " `locked_at` = NULL, `last_error` = NULL, `updated_at` = '" . DdvWpDb::escape(date('Y-m-d H:i:s')) . "'"
                    . " WHERE `environment` = '" . DdvWpDb::escape($environment) . "'"
                    . " AND `action` = 'SEND_RECORD' AND `id_record` IN (" . $idList . ')'
                    . " AND `status` IN ('" . self::QUEUE_RETRY . "','" . self::QUEUE_ERROR . "')"
                );
            }
        }

        // Release followers whose only error was waiting behind that legacy
        // predecessor. Chain order is still enforced by id_record.
        $db->execute(
            'UPDATE `' . DdvWpDb::quoteIdentifier($queue) . '`'
            . " SET `status` = '" . self::QUEUE_PENDING . "', `next_attempt_at` = NULL,"
            . " `locked_at` = NULL, `last_error` = NULL, `updated_at` = '" . DdvWpDb::escape(date('Y-m-d H:i:s')) . "'"
            . " WHERE `environment` = '" . DdvWpDb::escape($environment) . "' AND `action` = 'SEND_RECORD'"
            . " AND `status` = '" . self::QUEUE_RETRY . "'"
            . " AND `last_error` LIKE 'Existe un registro anterior de la misma cadena pendiente de resolver.%'"
        );

        return $changed;
    }

    public function claimNextQueue()
    {
        // Queue timestamps are created by PHP/PrestaShop. Compare them with
        // that same clock instead of MySQL NOW(), whose session timezone may
        // differ from the shop timezone and leave immediate sends PENDING.
        $nowValue = date('Y-m-d H:i:s');
        $staleValue = date('Y-m-d H:i:s', time() - 900);
        $now = DdvWpDb::escape($nowValue);
        $stale = DdvWpDb::escape($staleValue);

        $this->acquireQueueLock(5);
        try {
            DdvWpDb::getInstance()->execute(
                'UPDATE `' . DdvWpDb::prefix() . 'ddverifactu_queue` SET `status` = \'RETRY\','
                . " `locked_at` = NULL, `next_attempt_at` = '" . $now . "',"
                . ' `last_error` = CONCAT(COALESCE(`last_error`, \'\'), \' [recuperada tras bloqueo abandonado]\')'
                . " WHERE `status` = '" . self::QUEUE_PROCESSING . "'"
                . " AND `locked_at` < '" . $stale . "'"
            );
            $row = DdvWpDb::getInstance()->getRow(
                'SELECT q.* FROM `' . DdvWpDb::prefix() . 'ddverifactu_queue` q'
                . ' LEFT JOIN `' . DdvWpDb::prefix() . 'ddverifactu_records` r ON r.`id_record` = q.`id_record`'
                . " WHERE q.`environment` = '" . DdvWpDb::escape(VerifactuConfig::getEnvironment()) . "'"
                . " AND q.`status` IN ('" . self::QUEUE_PENDING . "','" . self::QUEUE_RETRY . "')"
                . " AND (q.`next_attempt_at` IS NULL OR q.`next_attempt_at` <= '" . $now . "')"
                . ' ORDER BY CASE WHEN q.`id_record` IS NULL THEN 1 ELSE 0 END ASC,'
                . ' COALESCE(q.`id_record`, 9223372036854775807) ASC, q.`id_queue` ASC'
            );
            if (!$row) {
                return null;
            }
            $updated = DdvWpDb::getInstance()->execute(
                'UPDATE `' . DdvWpDb::prefix() . 'ddverifactu_queue`'
                . " SET `status` = '" . self::QUEUE_PROCESSING . "',"
                . ' `attempts` = `attempts` + 1,'
                . " `locked_at` = '" . $now . "', `updated_at` = '" . $now . "'"
                . ' WHERE `id_queue` = ' . (int) $row['id_queue']
                . " AND `status` IN ('" . self::QUEUE_PENDING . "','" . self::QUEUE_RETRY . "')"
            );
            if (!$updated) {
                return null;
            }
            $claimed = DdvWpDb::getInstance()->getRow(
                'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_queue` WHERE `id_queue` = ' . (int) $row['id_queue']
            );
            return $claimed ?: null;
        } finally {
            $this->releaseQueueLock();
        }
    }

    public function completeQueue($idQueue, $idRecord = 0)
    {
        return DdvWpDb::getInstance()->update(
            'ddverifactu_queue',
            array(
                'status' => self::QUEUE_DONE,
                'id_record' => $idRecord > 0 ? (int) $idRecord : null,
                'last_error' => null,
                'locked_at' => null,
                'next_attempt_at' => null,
                'updated_at' => date('Y-m-d H:i:s'),
            ),
            '`id_queue` = ' . (int) $idQueue,
            0,
            true
        );
    }

    public function retryQueue($idQueue, $message, $delaySeconds, $idRecord = 0)
    {
        return DdvWpDb::getInstance()->update(
            'ddverifactu_queue',
            array(
                'status' => self::QUEUE_RETRY,
                'id_record' => $idRecord > 0 ? (int) $idRecord : null,
                'last_error' => (string) $message,
                'locked_at' => null,
                'next_attempt_at' => date('Y-m-d H:i:s', time() + max(1, (int) $delaySeconds)),
                'updated_at' => date('Y-m-d H:i:s'),
            ),
            '`id_queue` = ' . (int) $idQueue,
            0,
            true
        );
    }

    public function errorQueue($idQueue, $message, $idRecord = 0)
    {
        return DdvWpDb::getInstance()->update(
            'ddverifactu_queue',
            array(
                'status' => self::QUEUE_ERROR,
                'id_record' => $idRecord > 0 ? (int) $idRecord : null,
                'last_error' => (string) $message,
                'locked_at' => null,
                'next_attempt_at' => null,
                'updated_at' => date('Y-m-d H:i:s'),
            ),
            '`id_queue` = ' . (int) $idQueue,
            0,
            true
        );
    }

    public function enqueueRecordForTransmission(array $record, $delaySeconds = 0)
    {
        return $this->enqueue(array(
            'queue_key' => $record['environment'] . ':SEND:' . (int) $record['id_record'],
            'environment' => $record['environment'],
            'chain_key' => $record['chain_key'],
            'id_shop' => (int) $record['id_shop'],
            'id_order' => (int) $record['id_order'],
            'id_order_invoice' => !empty($record['id_order_invoice']) ? (int) $record['id_order_invoice'] : null,
            'id_order_slip' => !empty($record['id_order_slip']) ? (int) $record['id_order_slip'] : null,
            'id_record' => (int) $record['id_record'],
            'action' => 'SEND_RECORD',
            'payload' => array(),
            'status' => $delaySeconds > 0 ? self::QUEUE_RETRY : self::QUEUE_PENDING,
            // NULL means due immediately and is timezone-independent.
            'next_attempt_at' => $delaySeconds > 0
                ? date('Y-m-d H:i:s', time() + (int) $delaySeconds)
                : null,
        ));
    }

    public function listRecords($limit = 100, $idOrder = 0)
    {
        $where = " WHERE `environment` = '" . DdvWpDb::escape(VerifactuConfig::getEnvironment()) . "'";
        if ((int) $idOrder > 0) {
            $where .= ' AND `id_order` = ' . (int) $idOrder;
        }
        return (array) DdvWpDb::getInstance()->executeS(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . $where . ' ORDER BY `id_record` DESC LIMIT ' . max(1, min(500, (int) $limit))
        );
    }

    public function listQueue($limit = 100)
    {
        return (array) DdvWpDb::getInstance()->executeS(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_queue`'
            . " WHERE `environment` = '" . DdvWpDb::escape(VerifactuConfig::getEnvironment()) . "'"
            . ' ORDER BY `id_queue` DESC LIMIT ' . max(1, min(500, (int) $limit))
        );
    }

    public function listEvents($limit = 100)
    {
        return (array) DdvWpDb::getInstance()->executeS(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_events`'
            . ' ORDER BY `id_event` DESC LIMIT ' . max(1, min(500, (int) $limit))
        );
    }

    public function logEvent($severity, $eventType, array $data)
    {
        $context = isset($data['context']) ? $data['context'] : array();
        return DdvWpDb::getInstance()->insert(
            'ddverifactu_events',
            array(
                'severity' => strtoupper(substr((string) $severity, 0, 12)),
                'event_type' => substr((string) $eventType, 0, 64),
                'id_shop' => isset($data['id_shop']) ? (int) $data['id_shop'] : 0,
                'id_order' => isset($data['id_order']) ? (int) $data['id_order'] : 0,
                'id_order_invoice' => !empty($data['id_order_invoice']) ? (int) $data['id_order_invoice'] : null,
                'id_order_slip' => !empty($data['id_order_slip']) ? (int) $data['id_order_slip'] : null,
                'id_record' => !empty($data['id_record']) ? (int) $data['id_record'] : null,
                'message' => isset($data['message']) ? (string) $data['message'] : '',
                'context_json' => !empty($context) ? json_encode($context, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
                'created_at' => date('Y-m-d H:i:s'),
            ),
            true
        );
    }

    private function getLastLegacyRecord()
    {
        if (!self::tableExists(DdvWpDb::prefix() . 'ddverifactu_huella')) {
            return null;
        }
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_huella`'
            . " WHERE `huella` IS NOT NULL AND `huella` <> ''"
            . " AND `id_emisor` = '" . DdvWpDb::escape(VerifactuConfig::getIssuerNif()) . "'"
            . " AND `num_serie` IS NOT NULL AND `num_serie` <> ''"
            . " AND `fecha_expedicion` IS NOT NULL AND `fecha_expedicion` <> ''"
            . ' ORDER BY `id_huella` DESC'
        );
        if (!$row) {
            return null;
        }
        return array(
            'id_record' => 0,
            'issuer_nif' => (string) $row['id_emisor'],
            'document_number' => (string) $row['num_serie'],
            'issue_date' => (string) $row['fecha_expedicion'],
            'fingerprint' => strtoupper((string) $row['huella']),
            'legacy_id' => (int) $row['id_huella'],
        );
    }

    private function findIdentity($issuerNif, $number, $issueDate, $operation, $extra, $environment)
    {
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . " WHERE `environment` = '" . DdvWpDb::escape($this->environment($environment)) . "'"
            . " AND `issuer_nif` = '" . DdvWpDb::escape($issuerNif) . "'"
            . " AND `document_number` = '" . DdvWpDb::escape($number) . "'"
            . " AND `issue_date` = '" . DdvWpDb::escape($issueDate) . "'"
            . " AND `operation` = '" . DdvWpDb::escape($operation) . "'"
            . $extra
            . ' ORDER BY `id_record` DESC'
        );
        return $row ?: null;
    }

    private function getLatestByDocument($column, $id, $extra, $environment)
    {
        if (!in_array($column, array('id_order_invoice', 'id_order_slip'), true)) {
            throw new Exception('Columna documental no permitida.');
        }
        $row = DdvWpDb::getInstance()->getRow(
            'SELECT * FROM `' . DdvWpDb::prefix() . 'ddverifactu_records`'
            . " WHERE `environment` = '" . DdvWpDb::escape($this->environment($environment)) . "'"
            . ' AND `' . DdvWpDb::quoteIdentifier($column) . '` = ' . (int) $id
            . $extra
            . ' ORDER BY `id_record` DESC'
        );
        return $row ?: null;
    }

    private function updateRecordState($idRecord, array $data)
    {
        $sets = array();
        foreach ($data as $field => $value) {
            if (is_array($value) && isset($value['sql'])) {
                $sets[] = '`' . DdvWpDb::quoteIdentifier($field) . '` = ' . $value['sql'];
            } elseif ($value === null) {
                $sets[] = '`' . DdvWpDb::quoteIdentifier($field) . '` = NULL';
            } elseif (is_int($value)) {
                $sets[] = '`' . DdvWpDb::quoteIdentifier($field) . '` = ' . (int) $value;
            } else {
                $sets[] = '`' . DdvWpDb::quoteIdentifier($field) . "` = '" . DdvWpDb::escape((string) $value, true) . "'";
            }
        }
        $sets[] = "`updated_at` = '" . DdvWpDb::escape(date('Y-m-d H:i:s')) . "'";
        return DdvWpDb::getInstance()->execute(
            'UPDATE `' . DdvWpDb::prefix() . 'ddverifactu_records` SET ' . implode(', ', $sets)
            . ' WHERE `id_record` = ' . (int) $idRecord
        );
    }

    private function acquireLock($name, $timeoutSeconds)
    {
        $value = DdvWpDb::getInstance()->getValue(
            "SELECT GET_LOCK('" . DdvWpDb::escape(substr((string) $name, 0, 64)) . "', " . max(0, (int) $timeoutSeconds) . ')'
        );
        if ((int) $value !== 1) {
            throw new Exception('No se ha podido obtener el bloqueo de integridad VeriFactu. Reintente la operacion.');
        }
        return true;
    }

    private function releaseLock($name)
    {
        DdvWpDb::getInstance()->getValue(
            "SELECT RELEASE_LOCK('" . DdvWpDb::escape(substr((string) $name, 0, 64)) . "')"
        );
    }

    private function environment($environment)
    {
        return $environment ? (string) $environment : VerifactuConfig::getEnvironment();
    }

    private static function tableExists($table)
    {
        return (bool) DdvWpDb::getInstance()->getValue(
            "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()"
            . " AND TABLE_NAME = '" . DdvWpDb::escape($table) . "'"
        );
    }

    private static function columnExists($table, $column)
    {
        return (bool) DdvWpDb::getInstance()->getValue(
            "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()"
            . " AND TABLE_NAME = '" . DdvWpDb::escape($table) . "'"
            . " AND COLUMN_NAME = '" . DdvWpDb::escape($column) . "'"
        );
    }

    private static function indexExists($table, $index)
    {
        return (bool) DdvWpDb::getInstance()->getValue(
            "SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE()"
            . " AND TABLE_NAME = '" . DdvWpDb::escape($table) . "'"
            . " AND INDEX_NAME = '" . DdvWpDb::escape($index) . "'"
        );
    }
}
