-- ============================================================
-- quoERP — Facturacion Electronica SRI Ecuador
-- Migracion: 031_sri_electronic.sql
-- ============================================================
-- Referencia: Ficha Tecnica SRI Comprobantes Electronicos
--   Factura v2.1.0, NC/ND/Retencion v2.0.0
--   XAdES-BES, IVA 15% (codigoPorcentaje=4, vigente abril 2024)
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────
-- Columnas de estado electronico en facturas de venta
-- ─────────────────────────────────────────────────────────────
ALTER TABLE sale_invoices
    ADD COLUMN IF NOT EXISTS sri_status
        ENUM('draft','pending','sending','authorized','rejected','error','not_required')
        NOT NULL DEFAULT 'draft'
        COMMENT 'Estado ante el SRI',
    ADD COLUMN IF NOT EXISTS sri_authorization_number VARCHAR(49) DEFAULT NULL
        COMMENT 'Numero de autorizacion (49 digitos) devuelto por el SRI',
    ADD COLUMN IF NOT EXISTS sri_authorization_date   DATETIME   DEFAULT NULL
        COMMENT 'Fecha y hora de autorizacion del SRI',
    ADD COLUMN IF NOT EXISTS sri_xml_signed           LONGTEXT   DEFAULT NULL
        COMMENT 'XML firmado enviado al SRI',
    ADD COLUMN IF NOT EXISTS sri_ride_path            VARCHAR(255) DEFAULT NULL
        COMMENT 'Ruta relativa del PDF RIDE generado',
    ADD COLUMN IF NOT EXISTS sri_send_attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS sri_last_error           TEXT       DEFAULT NULL
        COMMENT 'Ultimo mensaje de error del SRI';

-- Mismo bloque para notas de credito/debito
ALTER TABLE credit_debit_notes
    ADD COLUMN IF NOT EXISTS sri_status
        ENUM('draft','pending','sending','authorized','rejected','error','not_required')
        NOT NULL DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS sri_authorization_number VARCHAR(49) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS sri_authorization_date   DATETIME   DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS sri_xml_signed           LONGTEXT   DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS sri_send_attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS sri_last_error           TEXT       DEFAULT NULL;

-- Para retenciones emitidas
ALTER TABLE retention_vouchers
    ADD COLUMN IF NOT EXISTS sri_status
        ENUM('draft','pending','sending','authorized','rejected','error','not_required')
        NOT NULL DEFAULT 'draft',
    ADD COLUMN IF NOT EXISTS sri_authorization_number VARCHAR(49) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS sri_authorization_date   DATETIME   DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS sri_xml_signed           LONGTEXT   DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS sri_send_attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS sri_last_error           TEXT       DEFAULT NULL;

-- ─────────────────────────────────────────────────────────────
-- Cola de envio al SRI (para reintentos y envios asincronos)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sri_send_queue (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    document_type   ENUM('factura','nota_credito','nota_debito','retencion','liquidacion')
                    NOT NULL,
    document_id     INT UNSIGNED    NOT NULL,
    clave_acceso    VARCHAR(49)     NOT NULL,
    xml_signed      LONGTEXT        DEFAULT NULL,
    attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    last_attempt_at DATETIME        DEFAULT NULL,
    status          ENUM('pending','processing','authorized','failed') NOT NULL DEFAULT 'pending',
    error_detail    TEXT            DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_doc (document_type, document_id),
    INDEX idx_company  (company_id),
    INDEX idx_status   (status),
    INDEX idx_clave    (clave_acceso)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Certificados digitales (.p12) por empresa
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sri_certificates (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    name            VARCHAR(100)    NOT NULL DEFAULT 'Certificado digital',
    p12_path        VARCHAR(255)    DEFAULT NULL
        COMMENT 'Ruta relativa del .p12 en disco (uploads/certs/)',
    p12_password    VARCHAR(255)    DEFAULT NULL
        COMMENT 'Contrasena del .p12 (almacenada encriptada)',
    cert_subject    VARCHAR(300)    DEFAULT NULL COMMENT 'CN del certificado',
    cert_issuer     VARCHAR(300)    DEFAULT NULL,
    cert_serial     VARCHAR(100)    DEFAULT NULL,
    expires_at      DATE            DEFAULT NULL,
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,
    created_by      INT UNSIGNED    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company (company_id),
    INDEX idx_active  (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACION
-- ALTER: sale_invoices, credit_debit_notes, retention_vouchers
-- Tablas nuevas: sri_send_queue, sri_certificates
-- ─────────────────────────────────────────────────────────────