-- EJECUTAR EN UNA BASE DE DATOS NUEVA Y AISLADA; NO EN thalisan_logistica.
-- MySQL 8.x / InnoDB / utf8mb4
CREATE TABLE IF NOT EXISTS empresas (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 ruc CHAR(11) NOT NULL UNIQUE,
 razon_social VARCHAR(200) NOT NULL,
 activo TINYINT(1) NOT NULL DEFAULT 1,
 creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS api_clientes (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 empresa_id BIGINT UNSIGNED NOT NULL,
 nombre VARCHAR(120) NOT NULL,
 secret_hash CHAR(64) NOT NULL,
 activo TINYINT(1) NOT NULL DEFAULT 1,
 creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_api_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS comprobantes (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 empresa_id BIGINT UNSIGNED NOT NULL,
 api_cliente_id BIGINT UNSIGNED NOT NULL,
 tipo CHAR(2) NOT NULL,
 referencia_externa VARCHAR(80) NOT NULL,
 idempotency_key VARCHAR(100) NOT NULL,
 payload_hash CHAR(64) NOT NULL,
 payload_json JSON NOT NULL,
 estado VARCHAR(30) NOT NULL DEFAULT 'BORRADOR',
 creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_empresa_referencia (empresa_id, referencia_externa),
 UNIQUE KEY uq_empresa_idempotencia (empresa_id, idempotency_key),
 CONSTRAINT fk_comprobante_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id),
 CONSTRAINT fk_comprobante_api FOREIGN KEY (api_cliente_id) REFERENCES api_clientes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS auditoria (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 empresa_id BIGINT UNSIGNED NULL,
 evento VARCHAR(80) NOT NULL,
 referencia VARCHAR(100) NULL,
 creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_audit_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;