-- Migration: 001_create_nfse_tables
-- Objetivo: criar banco novo para motor NFS-e/ADN em modelo multitenant por municipio.
--
-- Modelo:
-- - tabelas globais guardam configuracao, certificados, estado de sincronizacao e catalogo;
-- - tabelas de volume ficam separadas por codigo SIAFI:
--   nfse_documentos_7107
--   nfse_eventos_7107
--   nfse_documento_partes_7107

CREATE TABLE IF NOT EXISTS nfse_municipios (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  codigo_ibge CHAR(7) NOT NULL,
  codigo_siafi VARCHAR(10) NOT NULL,
  nome VARCHAR(150) NOT NULL,
  uf CHAR(2) NOT NULL,
  tenant_schema VARCHAR(64) NOT NULL,
  tabela_documentos VARCHAR(80) NOT NULL,
  tabela_eventos VARCHAR(80) NOT NULL,
  tabela_partes VARCHAR(80) NOT NULL,
  ambiente ENUM('producao_restrita', 'producao') NOT NULL DEFAULT 'producao_restrita',
  perfil ENUM('municipio', 'contribuinte') NOT NULL DEFAULT 'municipio',
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_nfse_municipios_codigo_ambiente_perfil (codigo_ibge, ambiente, perfil),
  UNIQUE KEY uk_nfse_municipios_siafi_ambiente_perfil (codigo_siafi, ambiente, perfil),
  UNIQUE KEY uk_nfse_municipios_tabela_documentos (tabela_documentos),
  UNIQUE KEY uk_nfse_municipios_tabela_eventos (tabela_eventos),
  UNIQUE KEY uk_nfse_municipios_tabela_partes (tabela_partes),
  KEY idx_nfse_municipios_ativo (ativo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nfse_certificados (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  municipio_id BIGINT UNSIGNED NOT NULL,
  tipo ENUM('A1_PFX') NOT NULL DEFAULT 'A1_PFX',
  storage_tipo ENUM('db_encrypted', 'file_path') NOT NULL DEFAULT 'db_encrypted',
  arquivo_path VARCHAR(500) NULL,
  certificado_criptografado LONGBLOB NULL,
  certificado_crypto_alg VARCHAR(50) NOT NULL DEFAULT 'AES-256-GCM',
  certificado_key_id VARCHAR(120) NOT NULL DEFAULT 'NFSE_MASTER_KEY',
  certificado_sha256 CHAR(64) NULL,
  certificado_nome_original VARCHAR(255) NULL,
  senha_criptografada TEXT NOT NULL,
  senha_crypto_alg VARCHAR(50) NOT NULL DEFAULT 'AES-256-GCM',
  senha_key_id VARCHAR(120) NOT NULL DEFAULT 'NFSE_MASTER_KEY',
  subject_name VARCHAR(255) NULL,
  documento_federal VARCHAR(14) NULL,
  valido_de DATETIME NULL,
  valido_ate DATETIME NULL,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_nfse_certificados_municipio_sha (municipio_id, certificado_sha256),
  KEY idx_nfse_certificados_municipio_ativo (municipio_id, ativo),
  KEY idx_nfse_certificados_storage (storage_tipo),
  CONSTRAINT fk_nfse_certificados_municipio
    FOREIGN KEY (municipio_id) REFERENCES nfse_municipios (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nfse_sync_state (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  municipio_id BIGINT UNSIGNED NOT NULL,
  ambiente ENUM('producao_restrita', 'producao') NOT NULL DEFAULT 'producao_restrita',
  perfil ENUM('municipio', 'contribuinte') NOT NULL DEFAULT 'municipio',
  last_nsu BIGINT UNSIGNED NOT NULL DEFAULT 0,
  max_nsu BIGINT UNSIGNED NULL,
  status ENUM('idle', 'pending', 'running', 'ready', 'error', 'disabled') NOT NULL DEFAULT 'idle',
  locked_at DATETIME NULL,
  locked_by VARCHAR(120) NULL,
  last_success_at DATETIME NULL,
  last_error_at DATETIME NULL,
  last_error_message TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_nfse_sync_state_municipio_ambiente_perfil (municipio_id, ambiente, perfil),
  KEY idx_nfse_sync_state_status (status),
  CONSTRAINT fk_nfse_sync_state_municipio
    FOREIGN KEY (municipio_id) REFERENCES nfse_municipios (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nfse_sync_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  municipio_id BIGINT UNSIGNED NULL,
  ambiente ENUM('producao_restrita', 'producao') NOT NULL DEFAULT 'producao_restrita',
  nivel ENUM('debug', 'info', 'warning', 'error') NOT NULL DEFAULT 'info',
  evento VARCHAR(120) NOT NULL,
  mensagem TEXT NOT NULL,
  contexto LONGTEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_nfse_sync_logs_municipio_created (municipio_id, created_at),
  KEY idx_nfse_sync_logs_nivel_created (nivel, created_at),
  CONSTRAINT fk_nfse_sync_logs_municipio
    FOREIGN KEY (municipio_id) REFERENCES nfse_municipios (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nfse_tenant_table_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  codigo_siafi VARCHAR(10) NOT NULL,
  table_version VARCHAR(30) NOT NULL,
  applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_nfse_tenant_table_versions_siafi_version (codigo_siafi, table_version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;