INSERT INTO public.bas_smtp_email(filial, sequencia, servidor_smtp, porta_smtp, usuario_smtp, senha_smtp, tipo_email, conexao_segura, 
								  nome_remetente, confirmacao_recebimento, copia_email_remetente, corpo_html, id_msg_automatica, email_smtp, 
								  tipo_conexao, assinatura_email, provedor, tenant_id, access_token, refresh_token, expires, state)
		    SELECT filial,
				   (COALESCE((SELECT MAX(sequencia) FROM bas_smtp_email), 0) + 1) as sequencia,
				   mail_host as servidor_smtp,
			       mail_port::INTEGER as porta_smtp,
			       mail_user as usuario_smtp,
			       mail_pass as senha_smtp,
				   'T' as tipo_email,
				   mail_protocol as conexao_segura,
			       mail_from_name as nome_remetente,
				   mail_confirmacao_leitura as confirmacao_recebimento,
				   mail_copia as copia_email_remetente,
				   'S' as corpo_html,
				   cod_msg as id_msg_automatica,
				   mail_from_mail as email_smtp, 
				   mail_protocol as tipo_conexao,
				   mail_assinatura as assinatura_email,
				   1 as provedor,
				   '' as tenant_id,
				   '' as access_token,
				   '' as refresh_token,
				   0 as expires,
				   '' as state
		      FROM par_nota_fiscal_contingencia
		      WHERE NOT EXISTS (SELECT * 
								  FROM bas_smtp_email
							     WHERE mail_host = servidor_smtp
			       				   AND mail_port::INTEGER = porta_smtp
			       				   AND mail_user = usuario_smtp
			       				   AND mail_pass = senha_smtp
			       				   AND 'T' = tipo_email
			       				   AND mail_protocol = conexao_segura
			       				   AND mail_from_name = nome_remetente
			       				   AND mail_confirmacao_leitura = confirmacao_recebimento
			       				   AND mail_copia = copia_email_remetente
			       				   AND 'S' = corpo_html
			       				   AND cod_msg = id_msg_automatica
			       				   AND mail_from_mail = email_smtp
			       				   AND mail_protocol = tipo_conexao
			       				   AND mail_assinatura = assinatura_email);
								   
ALTER TABLE par_nota_fiscal_contingencia
  ADD COLUMN sequencia_servidor_smtp integer;

UPDATE par_nota_fiscal_contingencia SET sequencia_servidor_smtp = COALESCE((SELECT MAX(bas_smtp_email.sequencia)
								  FROM bas_smtp_email
								  JOIN par_nota_fiscal_contingencia
							        ON par_nota_fiscal_contingencia.mail_host = bas_smtp_email.servidor_smtp
			       				   AND par_nota_fiscal_contingencia.mail_port::INTEGER = bas_smtp_email.porta_smtp
			       				   AND par_nota_fiscal_contingencia.mail_user = bas_smtp_email.usuario_smtp
			       				   AND par_nota_fiscal_contingencia.mail_pass = bas_smtp_email.senha_smtp
			       				   AND 'T' = bas_smtp_email.tipo_email
			       				   AND par_nota_fiscal_contingencia.mail_protocol = bas_smtp_email.conexao_segura
			       				   AND par_nota_fiscal_contingencia.mail_from_name = bas_smtp_email.nome_remetente
			       				   AND par_nota_fiscal_contingencia.mail_confirmacao_leitura = bas_smtp_email.confirmacao_recebimento
			       				   AND par_nota_fiscal_contingencia.mail_copia = bas_smtp_email.copia_email_remetente
			       				   AND 'S' = bas_smtp_email.corpo_html
			       				   AND par_nota_fiscal_contingencia.cod_msg = bas_smtp_email.id_msg_automatica
			       				   AND par_nota_fiscal_contingencia.mail_from_mail = bas_smtp_email.email_smtp
			       				   AND par_nota_fiscal_contingencia.mail_protocol = bas_smtp_email.tipo_conexao
			       				   AND par_nota_fiscal_contingencia.mail_assinatura = bas_smtp_email.assinatura_email), 0) WHERE sequencia_servidor_smtp IS NULL;

ALTER TABLE par_nota_fiscal_contingencia
  ALTER COLUMN sequencia_servidor_smtp SET NOT NULL;