-- Fila persistente de sincronizacao por municipio, ambiente e perfil.
CREATE TABLE IF NOT EXISTS nfse_sync_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  municipio_id BIGINT UNSIGNED NOT NULL,
  ambiente ENUM('producao_restrita', 'producao') NOT NULL,
  perfil ENUM('municipio', 'contribuinte') NOT NULL,
  status ENUM('pending', 'running', 'done', 'error') NOT NULL DEFAULT 'pending',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  available_at DATETIME NOT NULL,
  locked_at DATETIME NULL,
  locked_by VARCHAR(120) NULL,
  last_error_message TEXT NULL,
  finished_at DATETIME 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_jobs_scope (municipio_id, ambiente, perfil),
  KEY idx_nfse_sync_jobs_pick (status, available_at, id),
  CONSTRAINT fk_nfse_sync_jobs_municipio
    FOREIGN KEY (municipio_id) REFERENCES nfse_municipios (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;