/*********************************************************************************
*                       Script de Atualização LATROMI WecDB                      *
*                                                                                *
*                    Data/Hora de geração: 21/01/2020 12:59:49                   *
*********************************************************************************/

--  Este arquivo reune o(s) script(s):
   -- 00_common-fnc_xml_escape.sql
   -- 01_MenuSemFF.sql
   -- 02_trg_menu_bef_ins_upd_targetupgradecodefix.sql
   -- 01_fnc_fix_browserreferences.sql
   -- 02_consulta_parametro_remove_dependencia.sql
   -- 03_trg_consultaparametro_bef_ins_upd_browsersettingsfix.sql
   -- 02_fix_translations.sql
   -- 08_converte_links.sql
   -- 03_fnc_search.sql
   -- 01_form_lookup_remove_dependencia.sql
   -- 02_trg_campodinamico_bef_ins_upd_lookupfix.sql
   -- 04_AddColumnSequencia_formdinamico_variavel.sql
   -- 01_fnc_fix_reportreferences.sql
   -- 02_form_consulta_remove_dependencia.sql
   -- 05_AddColumnSequencia_ExtratorArquivos.sql
   -- 01_fnc_fix_extractorreferences.sql
   -- 02_form_imageview_remove_dependencia.sql
   -- 02_trg_campodinamico_bef_ins_upd_imageviewfix.sql
   -- 06_AddColumnCacheEnabled_browseDinamico.sql
   -- 01_window_remove_dependencia.sql
   -- 07_package.sql
   -- 01_fnc_fix_documents.sql
   -- 02_trg_markdowndocument_bef_ins_upd_bodyfix.sql
   -- 99_UpdateVersao.sql


CREATE OR REPLACE FUNCTION common.fnc_xml_escape(
    text,
    boolean)
  RETURNS text AS
$BODY$
DECLARE
   cText    TEXT = $1;
   bAdd     BOOLEAN = $2;
BEGIN
-- Autor:       Daniel Giacomelli 
-- Data:        31/10/2019
-- Objetivo:    Função para fazer o escape XML em textos.
   IF bAdd THEN
      cText = REPLACE(cText, '&', '&amp;');
      cText = REPLACE(cText, '"', '&#x22;');
      cText = REPLACE(cText, '''', '&#x27;');
      cText = REPLACE(cText, '>', '&gt;');
      cText = REPLACE(cText, '<', '&lt;');
      --cText = REPLACE(cText, ' ', '&nbsp;');
   ELSE
      cText = REPLACE(cText, '&amp;', '&');
      cText = REPLACE(cText, '&#x22;', '"');
      cText = REPLACE(cText, '&#x27;', '''');
      cText = REPLACE(cText, '&gt;', '>');
      cText = REPLACE(cText, '&lt;', '<');
      --cText = REPLACE(cText, '&nbsp;', ' ');   
   END IF;

   RETURN cText;
END;
$BODY$
  LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.menu', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.menu', 'ri_fk_mnu_idconsulta') THEN
		EXECUTE 'ALTER TABLE menu DROP CONSTRAINT ri_fk_mnu_idconsulta';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.menu', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.menu', 'ri_fk_mnu_idform') THEN
		EXECUTE 'ALTER TABLE menu DROP CONSTRAINT ri_fk_mnu_idform';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.menu', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.menu', 'ri_fk_mnu_idmenupai') THEN
		EXECUTE 'ALTER TABLE menu DROP CONSTRAINT ri_fk_mnu_idmenupai';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.menu', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.menu', 'targetupgradecode') THEN
		ALTER TABLE menu ADD targetupgradecode TEXT;
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.menu', 'targetupgradecode') THEN
	COMMENT ON COLUMN menu.targetupgradecode IS 'Código de atualização do alvo.';
END IF;

BEGIN
	UPDATE menu SET 
	   targetupgradecode = 
	      CASE WHEN idconsulta IS NOT NULL THEN
	         (SELECT g.upgradecode FROM consulta c JOIN globalidmapping g ON g.id = c.globalid AND c.id = menu.idconsulta)
	      WHEN idformdinamico IS NOT NULL THEN
	         (SELECT g.upgradecode FROM formdinamico f JOIN globalidmapping g ON g.id = f.globalid AND f.id = menu.idformdinamico)
	      END
	WHERE idconsulta IS NOT NULL
	OR    idformdinamico IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

IF public.fnc_objectexists('COLUMN', 'wecdb.menu', 'idconsulta') THEN
	COMMENT ON COLUMN menu.idconsulta IS '[Obsolete("Use TargetUpgradeCode instead.")] Id da Consulta';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.menu', 'idformdinamico') THEN
	COMMENT ON COLUMN menu.idformdinamico IS '[Obsolete("Use TargetUpgradeCode instead.")] Id do Formulário Dinâmico';
END IF;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();

SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_trg_menu_bef_ins_upd_targetupgradecodefix ()
RETURNS SETOF TRIGGER AS 
$BODY$

   /*============================================================================
     Autor: Daniel Giacomelli                                  
     Data: 28/10/2019

     Trigger para preecher o campo "targetupgradecode" quando os 
     campos "idconsulta" ou "idformdinamico" forem atualizados.
     A criação dessa trigger foi necessária para que o valor do campo
     sempre seja preenchido, mesmo que o LATROMI Client esteja desatualizado.
     No nomento em que todos os clientes estiverem atualizados, 
     essa trigger pode ser desabilitada.
   ==============================================================================*/
DECLARE
   cUpgradeCode TEXT;

BEGIN

   IF TG_OP = 'UPDATE' AND (
         (OLD.tipo = 3 AND OLD.idconsulta     IS NULL AND COALESCE(OLD.targetupgradecode, '') <> '' AND COALESCE(NEW.targetupgradecode, '') = '')
      OR (OLD.tipo = 5 AND OLD.idformdinamico IS NULL AND COALESCE(OLD.targetupgradecode, '') <> ''  AND COALESCE(NEW.targetupgradecode, '') = ''))
   THEN
      RAISE EXCEPTION 'Não é possível alterar o menu "%" porque ele foi gravado em uma versão mais recente do LATROMI Client. Por favor, atualize o sistema e tente novamente.', NEW.nome;
   END IF;

   --RAISE INFO '%, %, %-%', TG_OP, NEW.tipo, OLD.idformdinamico, NEW.idformdinamico;
   IF (TG_OP = 'UPDATE' AND NEW.tipo = 3 AND COALESCE(NEW.idconsulta, 0) > 0 AND (COALESCE(NEW.targetupgradecode, '') = '' OR COALESCE(NEW.idconsulta, 0) <> COALESCE(OLD.idconsulta, 0)))
      OR (TG_OP = 'INSERT' AND NEW.tipo = 3 AND NEW.idconsulta IS NOT NULL AND COALESCE(NEW.targetupgradecode, '') = '')
   THEN
      -- CONSULTA
      SELECT INTO cUpgradeCode g.upgradecode
      FROM consulta c
      JOIN globalidmapping g ON g.id = c.globalid
      WHERE c.id = NEW.idconsulta;

      IF FOUND THEN 
         NEW.targetupgradecode = cUpgradeCode;
         RAISE DEBUG 'Corrigindo "TargetUpgradeCode: %', cUpgradeCode; 
      END IF;

   ELSIF (TG_OP = 'UPDATE' AND NEW.tipo = 5 AND COALESCE(NEW.idformdinamico, 0) > 0 AND (COALESCE(NEW.targetupgradecode, '') = '' OR COALESCE(NEW.idformdinamico, 0) <> COALESCE(OLD.idformdinamico, 0)))
      OR (TG_OP = 'INSERT' AND NEW.tipo = 5 AND NEW.idformdinamico IS NOT NULL AND COALESCE(NEW.targetupgradecode, '') = '')
   THEN
      -- FORMULÁRIO
      SELECT INTO cUpgradeCode g.upgradecode
      FROM formdinamico f
      JOIN globalidmapping g ON g.id = f.globalid
      WHERE f.id = NEW.idformdinamico;

      IF FOUND THEN       
         NEW.targetupgradecode = cUpgradeCode;      
         RAISE DEBUG 'Corrigindo "TargetUpgradeCode: %', cUpgradeCode; 
      END IF;

   END IF;

   RETURN NEW;

END;
$BODY$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_menu_bef_ins_upd_targetupgradecodefix ON menu;

CREATE TRIGGER trg_menu_bef_ins_upd_targetupgradecodefix
  BEFORE INSERT OR UPDATE
  ON menu
  FOR EACH ROW
  EXECUTE PROCEDURE fnc_trg_menu_bef_ins_upd_targetupgradecodefix();


SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_fix_browserreferences(pnIdBrowser BIGINT, pcBrowserLink TEXT, pnBrowserMode SMALLINT, pcBrowserValueMember TEXT, pcBrowserDisplayMember TEXT)
   RETURNS TEXT AS 
$BODY$
DECLARE
   cParameters TEXT;
   nMemberType INT;
   cMemberType TEXT;
   cUpgradeCode TEXT;
   cDescription TEXT;
BEGIN

   SELECT INTO 
      nMemberType, cUpgradeCode, cDescription
      e.tipocoluna, g.upgradecode, b.titulo
   FROM browsedinamico_estrutura e
   JOIN browsedinamico b ON b.id = e.idbrowse
   JOIN globalidmapping g ON g.id = b.globalid
   WHERE idbrowse = pnIdBrowser 
   AND LOWER(nomecoluna) = LOWER(pcBrowserValueMember);

   /*IF NOT FOUND THEN
      RAISE INFO 'Coluna "%" do Browser de ID % não encontrada.', pcBrowserValueMember, pnIdBrowser;
   END IF;*/

   nMemberType = COALESCE(nMemberType, 2);

   cMemberType = CASE nMemberType
         WHEN 1 THEN 'Number'
         WHEN 3 THEN 'DateTime'
         WHEN 4 THEN 'Boolean'
         ELSE 'String' END;

   WITH a AS 
   (
      SELECT UNNEST(STRING_TO_ARRAY(pcBrowserLink, ';')) AS item
   )
   , b AS 
   (
      SELECT 
         SPLIT_PART(item, '=', 1)  AS parametro
         , NULLIF(common.fnc_xml_escape(SPLIT_PART(item, '=', 2), true), '')  AS valor
      FROM a
   )
   SELECT INTO cParameters
      ARRAY_TO_STRING(ARRAY_AGG('<item name="'||p.nome||'" enable="' || (CASE WHEN b.valor IS NULL THEN 'false"' ELSE 'true"' END) 
         || ' dataType="'|| CASE p.tipo WHEN 3 THEN 'Date' WHEN 2 THEN 'String' ELSE 'String' END ||'"'
         || CASE WHEN COALESCE(b.valor, '') = '' THEN  ' />' ELSE ('>'||b.valor||'</item>')END ORDER BY p.sequenciaparametro), E'\n      ' )
   FROM browsedinamico_parametro p
   LEFT JOIN b ON p.idbrowse = pnIdBrowser 
   WHERE p.idbrowse = pnIdBrowser 
   AND UPPER(p.nome) = UPPER(b.parametro)
   /*ORDER BY p.id*/;

   --'<?xml version="1.0" encoding="utf-8"?>
   -- xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

   RETURN format(
'<references>
   <target type="DynamicBrowser" upgradeCode="%s" description="%s" />
   <settings>
      <setting name="mode">%s</setting>
      <setting name="valueMember">%s</setting>
      <setting name="valueMemberDataType">%s</setting>
      <setting name="displayMember">%s</setting>
   </settings>
   <items>
      %s
   </items>
</references>', cUpgradeCode, common.fnc_xml_escape(cDescription, true), 
      CASE WHEN pnBrowserMode = '1' THEN 'Popup' ELSE 'ComboBox' END, pcBrowserValueMember, cMemberType, pcBrowserDisplayMember, cParameters);
END;
$BODY$
LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.consulta_parametro', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.consulta_parametro', 'browsersettings') THEN
		ALTER TABLE consulta_parametro ADD browsersettings TEXT;
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.consulta_parametro', 'browsersettings') THEN
	COMMENT ON COLUMN consulta_parametro.browsersettings IS 'Xml com as configurações do Browser Dinâmico Vinculado';
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.consulta_parametro', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.consulta_parametro', 'ri_fk_consp_idbrowse') THEN
		EXECUTE 'ALTER TABLE consulta_parametro DROP CONSTRAINT ri_fk_consp_idbrowse';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.consulta_parametro', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.consulta_parametro', 'chk_consp_lookup') THEN
		EXECUTE 'ALTER TABLE consulta_parametro DROP CONSTRAINT chk_consp_lookup';
	END IF;
END IF;

BEGIN
	UPDATE consulta_parametro 
	SET browsersettings = E'<!-- convertido -->\n'||fnc_fix_browserreferences(idbrowse, vinculobrowse, browsermode, browservaluemember, browserdisplaymember)
	WHERE tipo = 5
	AND idbrowse IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

IF public.fnc_objectexists('TABLE', 'wecdb.consulta_parametro', '') THEN
	IF NOT public.fnc_objectexists('CHECK CONSTRAINT', 'wecdb.consulta_parametro', 'chk_consp_lookup') THEN
		ALTER TABLE consulta_parametro  ADD CONSTRAINT chk_consp_lookup CHECK (browsersettings IS NOT NULL OR tipo <> 5);
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.consulta_parametro', 'idbrowse') THEN
	COMMENT ON COLUMN consulta_parametro.idbrowse IS '[Obsolete("Use BrowserSettings instead.")] FK com a tabela BrowseDinamico';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.consulta_parametro', 'browservaluemember') THEN
	COMMENT ON COLUMN consulta_parametro.browservaluemember IS '[Obsolete("Use BrowserSettings instead.")] Nome da Coluna do Browse Dinamico';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.consulta_parametro', 'vinculobrowse') THEN
	COMMENT ON COLUMN consulta_parametro.vinculobrowse IS '[Obsolete("Use BrowserSettings instead.")] Campos + Valor do vinculo entre o browse e parâmetros, separados por ";"';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.consulta_parametro', 'browsermode') THEN
	COMMENT ON COLUMN consulta_parametro.browsermode IS '[Obsolete("Use BrowserSettings instead.")] Modo de utilização do Browser Dinâmico:
1. Popup
2. ComboBox';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.consulta_parametro', 'browserdisplaymember') THEN
	COMMENT ON COLUMN consulta_parametro.browserdisplaymember IS '[Obsolete("Use BrowserSettings instead.")] Nome da Coluna do Browse Dinamico';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.consulta_parametro', 'tipocolunabrowse') THEN
	COMMENT ON COLUMN consulta_parametro.tipocolunabrowse IS '[Obsolete("Use BrowserSettings instead.")] Tipo da coluna de retorno do Browser';
END IF;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();

SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_trg_consultaparametro_bef_ins_upd_browsersettingsfix ()
RETURNS SETOF TRIGGER AS 
$BODY$

   /*============================================================================
     Autor: Daniel Giacomelli                                  
     Data: 28/10/2019

     Trigger para preecher o campo "browsersettings" quando o 
     tipo de parâmetro for "Lookup".
     A criação dessa trigger foi necessária para que o valor do campo
     sempre seja preenchido, mesmo que o LATROMI Client esteja desatualizado.
     No nomento em que todos os clientes estiverem atualizados, 
     essa trigger pode ser desabilitada.
   ==============================================================================*/
BEGIN

   IF TG_OP = 'UPDATE' 
      AND OLD.tipo = 5 
      AND NEW.tipo = 5 
      AND NOT COALESCE(OLD.browsersettings, '') LIKE '<!-- convertido -->%'
      AND OLD.idbrowse IS NULL 
      AND COALESCE(NEW.idbrowse, 0) > 0 
   THEN
      RAISE EXCEPTION 'Não é possível alterar o parâmetro "%" porque ele foi gravado em uma versão mais recente do LATROMI Client. Por favor, atualize o sistema e tente novamente.', NEW.nome;
   END IF;

   --RAISE INFO '%, %, %-%', TG_OP, NEW.tipo, OLD.idformdinamico, NEW.idformdinamico;
   IF (TG_OP = 'UPDATE' AND NEW.tipo = 5 
      AND (COALESCE(NEW.browsersettings, '') = '' OR
          (COALESCE(NEW.idbrowse, 0) <> COALESCE(OLD.idbrowse, 0) 
         OR COALESCE(NEW.vinculobrowse, '') <> COALESCE(OLD.vinculobrowse, '') 
         OR COALESCE(NEW.browsermode, 0) <> COALESCE(OLD.browsermode, 0) 
         OR COALESCE(NEW.browservaluemember, '') <> COALESCE(OLD.browservaluemember, '') 
         OR COALESCE(NEW.browserdisplaymember, '') <> COALESCE(OLD.browserdisplaymember, ''))
      ))
      OR (TG_OP = 'INSERT' AND NEW.tipo = 5 AND COALESCE(NEW.browsersettings, '') = '')
   THEN
      NEW.browsersettings = E'<!-- convertido -->\n'||fnc_fix_browserreferences(NEW.idbrowse, NEW.vinculobrowse, NEW.browsermode, NEW.browservaluemember, NEW.browserdisplaymember);
   END IF;

   RETURN NEW;

END;
$BODY$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_consultaparametro_bef_ins_upd_browsersettingsfix ON consulta_parametro;

CREATE TRIGGER trg_consultaparametro_bef_ins_upd_browsersettingsfix
  BEFORE INSERT OR UPDATE
  ON consulta_parametro
  FOR EACH ROW
  EXECUTE PROCEDURE fnc_trg_consultaparametro_bef_ins_upd_browsersettingsfix();

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.consulta', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.consulta', 'translatesetupgradecode') THEN
		ALTER TABLE consulta ADD translatesetupgradecode TEXT;
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.browsedinamico', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.browsedinamico', 'translatesetupgradecode') THEN
		ALTER TABLE browsedinamico ADD translatesetupgradecode TEXT;
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.formdinamico', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.formdinamico', 'translatesetupgradecode') THEN
		ALTER TABLE formdinamico ADD translatesetupgradecode TEXT;
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.owner', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.owner', 'menutranslatesetupgradecode') THEN
		ALTER TABLE owner ADD menutranslatesetupgradecode TEXT;
	END IF;
END IF;

BEGIN
	UPDATE consulta 
	SET translatesetupgradecode = (SELECT g.upgradecode FROM globalidmapping g JOIN translatesets f ON f.globalid = g.id WHERE f.id = translatesetid)
	WHERE translatesetid IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

BEGIN
	UPDATE browsedinamico 
	SET translatesetupgradecode = (SELECT g.upgradecode FROM globalidmapping g JOIN translatesets f ON f.globalid = g.id WHERE f.id = translatesetid)
	WHERE translatesetid IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

BEGIN
	UPDATE formdinamico 
	SET translatesetupgradecode = (SELECT g.upgradecode FROM globalidmapping g JOIN translatesets f ON f.globalid = g.id WHERE f.id = translatesetid)
	WHERE translatesetid IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

BEGIN
	UPDATE owner 
	SET menutranslatesetupgradecode = (SELECT g.upgradecode FROM globalidmapping g JOIN translatesets f ON f.globalid = g.id WHERE f.id = menutranslatesetid)
	WHERE menutranslatesetid IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

IF public.fnc_objectexists('COLUMN', 'wecdb.consulta', 'TranslateSetID') THEN
	COMMENT ON COLUMN consulta.TranslateSetID IS '[Obsolete("Use TranslateSetUpgradeCode instead")] ID do Conjunto de Traduções';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.browsedinamico', 'TranslateSetID') THEN
	COMMENT ON COLUMN browsedinamico.TranslateSetID IS '[Obsolete("Use TranslateSetUpgradeCode instead")] ID do Conjunto de Traduções';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.formdinamico', 'TranslateSetID') THEN
	COMMENT ON COLUMN formdinamico.TranslateSetID IS '[Obsolete("Use TranslateSetUpgradeCode instead")] ID do Conjunto de Traduções';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.owner', 'menutranslatesetid') THEN
	COMMENT ON COLUMN owner.menutranslatesetid IS '[Obsolete("Use "MenuTranslateSetUpgradeCode" instead")] ID do Conjunto de Traduções do Menu';
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.browsedinamico', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.browsedinamico', 'fk_brwsd_translsetid') THEN
		EXECUTE 'ALTER TABLE browsedinamico DROP CONSTRAINT fk_brwsd_translsetid';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.consulta', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.consulta', 'fk_cons_translsetid') THEN
		EXECUTE 'ALTER TABLE consulta DROP CONSTRAINT fk_cons_translsetid';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.formdinamico', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.formdinamico', 'fk_formdin_translsetid') THEN
		EXECUTE 'ALTER TABLE formdinamico DROP CONSTRAINT fk_formdin_translsetid';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.owner', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.owner', 'fk_owner_translsetid') THEN
		EXECUTE 'ALTER TABLE owner DROP CONSTRAINT fk_owner_translsetid';
	END IF;
END IF;

IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

ALTER TABLE link_referencia ALTER COLUMN idglobal DROP NOT NULL;
IF public.fnc_objectexists('TABLE', 'wecdb.link_referencia', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.link_referencia', 'targetupgradecode') THEN
		ALTER TABLE link_referencia ADD targetupgradecode TEXT;
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.link_referencia', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.link_referencia', 'targetname') THEN
		ALTER TABLE link_referencia ADD targetname TEXT;
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.link_referencia', 'targetupgradecode') THEN
	COMMENT ON COLUMN link_referencia.targetupgradecode IS 'Código de atualização do objeto alvo';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.link_referencia', 'targetname') THEN
	COMMENT ON COLUMN link_referencia.targetname IS 'Nome do objeto alvo';
END IF;

BEGIN
	UPDATE link_referencia
	SET targetupgradecode = (SELECT g.upgradecode 
	                        FROM consulta c
	                        JOIN globalidmapping g ON g.id = c.globalid
	                        WHERE c.id = SUBSTR(idglobal, 3)::BIGINT)
	WHERE tipoobjeto = 1
	AND idglobal IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

BEGIN
	UPDATE link_referencia
	SET targetupgradecode = (SELECT g.upgradecode 
	                        FROM formdinamico f
	                        JOIN globalidmapping g ON g.id = f.globalid
	                        WHERE f.id = SUBSTR(idglobal, 3)::BIGINT)
	WHERE tipoobjeto = 2
	AND targetupgradecode IS NULL
	AND idglobal IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

BEGIN
	UPDATE link_referencia
	SET targetupgradecode = (SELECT g.upgradecode 
	                        FROM fileextractor f
	                        JOIN globalidmapping g ON g.id = f.globalid
	                        WHERE f.id = SUBSTR(idglobal, 4)::BIGINT)
	WHERE tipoobjeto = 13
	AND targetupgradecode IS NULL
	AND idglobal IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

BEGIN
	UPDATE link_referencia SET targetname = (SELECT g.objectdescription FROM globalidmapping g WHERE g.upgradecode = targetupgradecode LIMIT 1)
	WHERE targetupgradecode IS NOT NULL
	AND targetname IS NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

DROP TYPE IF EXISTS search_result CASCADE;
CREATE TYPE search_result
AS 
(  
   idglobal TEXT,
   tipo TEXT,
   titulo TEXT,  
   texto TEXT,
   url TEXT,
   urltootip TEXT
);
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();

CREATE OR REPLACE FUNCTION fnc_search(pcSearchText TEXT)
RETURNS SETOF search_result
AS
$BODY$
DECLARE
   searchResult   search_result;
BEGIN
   FOR searchResult IN SELECT * FROM fnc_search(pcSearchText, '', '')
   LOOP
      RETURN NEXT searchResult;
   END LOOP;
END;
$BODY$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION fnc_search(pcSearchText TEXT, pcTypes TEXT, pcOption TEXT)
RETURNS SETOF search_result
AS
$BODY$
DECLARE
   cSearchText    TEXT = pcSearchText;
   cRegexEscape   TEXT = '][.\\|()-?*{}';
   rData          RECORD;
   searchResult   search_result;

   aTypes         TEXT[] = STRING_TO_ARRAY(LOWER(pcTypes), ',');
   aOptions       TEXT[] = STRING_TO_ARRAY(LOWER(pcOption), ',');

   bTypesSpecified   BOOLEAN = COALESCE(pcTypes, '') <> '';
   bOptionsSpecified BOOLEAN = COALESCE(pcOption, '') <> '';

   bGetHiddenMenus   BOOLEAN = bOptionsSpecified AND pcOption ~* 'getHiddenMenus';

   cUpgradeCode      TEXT = NULL;
   cDuplicatedFriendlyNames TEXT[] = NULL;

   aPuncts            TEXT[] = ARRAY['cç', 'aäáãàâ', 'eëéèê', 'iïíìî', 'oöóõòô', 'uüúùû'];

BEGIN

   IF pcSearchText ~* '^[A-Z]{3}[0-9]{35}$' OR 
      pcSearchText ~* '^[a-z0-9]{8}[-][a-z0-9]{4}[-][a-z0-9]{4}[-][a-z0-9]{4}[-][a-z0-9]{12}$' 
   THEN
      cUpgradeCode = pcSearchText;
   END IF;

   --Faz o escape dos carecteres especiais de expressão regular
   FOR i IN 1..LENGTH(cRegexEscape)
   LOOP
      cSearchText = REPLACE(cSearchText, SUBSTR(cRegexEscape,i,1), '[' ||SUBSTR(cRegexEscape,i,1) || ']');
   END LOOP;

   -- Tratamento para desconsiderar os acentos
   FOR i IN 1..ARRAY_UPPER(aPuncts, 1)
   LOOP
      cSearchText = REGEXP_REPLACE(cSearchText, '['||aPuncts[i]||']', '['||aPuncts[i]||']', 'gi');
   END LOOP;

   -- verifica se tem duplicidade de menus com o mesmo friendlypath.
   SELECT INTO cDuplicatedFriendlyNames ARRAY(
      SELECT 
         UPPER(friendlypath) normalizedfriendlypath
      FROM menu 
      WHERE friendlypath IS NOT NULL
      GROUP BY UPPER(friendlypath)
      HAVING count(1) > 1);

   IF NOT bTypesSpecified OR 'menu' = ANY(aTypes) OR 'all' = ANY(aTypes) THEN    
      FOR searchResult IN 
         -- Consultas
         (SELECT 
            'MN'||menu.id::TEXT AS idglobal,
            'Menu' AS tipo,
            menu.nome AS titulo,
            --consulta.descricaomenu AS texto,
            format('<ul class="result-properties"><li>Caminho: %s</li><li>Tipo: Consulta</li></ul>', 
               fnc_get_menupath(menu.id, ' › ')) AS texto,

            CASE WHEN menu.friendlypath IS NOT NULL AND UPPER(menu.friendlypath) = ANY(cDuplicatedFriendlyNames) THEN 
               menu.friendlypath || '/?menuid='||menu.id::TEXT
            WHEN menu.friendlypath IS NOT NULL THEN 
               menu.friendlypath || '/'
            ELSE
               'Report.aspx?menuid='||menu.id::TEXT
            END AS url
         FROM menu
         JOIN globalidmapping mnugidmap ON mnugidmap.id = menu.globalid
         JOIN globalidmapping cstgidmap ON menu.tipo = 3 AND cstgidmap.upgradecode = menu.targetupgradecode
         JOIN consulta ON consulta.globalid = cstgidmap.id
         WHERE (consulta.descricao ~* cSearchText
         OR consulta.descricaomenu ~* cSearchText
         OR consulta.searchtags ~* cSearchText 
         OR menu.nome ~* cSearchText
         OR menu.searchtags ~* cSearchText 
         OR menu.friendlypath ~* cSearchText
         OR mnugidmap.upgradecode = cUpgradeCode
         OR cstgidmap.upgradecode = cUpgradeCode)
         AND (bGetHiddenMenus OR NOT menu.hidden)

         UNION ALL

         -- Forms
         SELECT 
            'MN'||menu.id::TEXT AS idglobal,
            'Menu' AS tipo,        
            menu.nome AS titulo, 
            format('<ul class="result-properties"><li>Caminho: %s</li><li>Tipo: Formulário</li></ul>', 
               fnc_get_menupath(menu.id, ' › ')) AS texto,

            CASE WHEN menu.friendlypath IS NOT NULL AND UPPER(menu.friendlypath) = ANY(cDuplicatedFriendlyNames) THEN 
               menu.friendlypath || '/?menuid='||menu.id::TEXT
            WHEN menu.friendlypath IS NOT NULL THEN
               menu.friendlypath || '/'
            ELSE
               'DynamicForm.aspx?menuid='||menu.id::TEXT 
            END AS url

         FROM menu
         JOIN globalidmapping mnugidmap ON mnugidmap.id = menu.globalid
         JOIN globalidmapping frmgidmap ON menu.tipo = 5 AND frmgidmap.upgradecode = menu.targetupgradecode
         JOIN formdinamico ON formdinamico.globalid = frmgidmap.id
         WHERE (formdinamico.titulo ~* cSearchText
         OR formdinamico.searchtags ~* cSearchText 
         OR menu.nome ~* cSearchText
         OR menu.searchtags ~* cSearchText 
         OR menu.friendlypath ~* cSearchText 
         OR mnugidmap.upgradecode = cUpgradeCode
         OR frmgidmap.upgradecode = cUpgradeCode)
         AND (bGetHiddenMenus OR NOT menu.hidden)
         )

         UNION ALL

         -- Urls
         SELECT 
            'MN'||menu.id::TEXT AS idglobal,
            'Menu' AS tipo,        
            menu.nome AS titulo, 
            '<ul class="result-properties"><li>Tipo: Url Externa</li></ul>' AS texto,
            menu.url
         FROM menu
         JOIN globalidmapping mnugidmap ON mnugidmap.id = menu.globalid
         WHERE (menu.nome ~* cSearchText
         OR menu.searchtags ~* cSearchText 
         OR menu.friendlypath ~* cSearchText 
         OR mnugidmap.upgradecode = cUpgradeCode
         OR menu.url ~* cSearchText)
         AND (bGetHiddenMenus OR NOT menu.hidden)
         AND menu.tipo = 4

         ORDER BY titulo

      LOOP
         RETURN NEXT searchResult;
      END LOOP;
   END IF;

   IF NOT bTypesSpecified OR 'carrossel' = ANY(aTypes) OR 'all' = ANY(aTypes) THEN    
      FOR searchResult IN 
         SELECT * FROM
         (
            SELECT
               'CR'||cenario.idcarrossel::TEXT AS idglobal,
               'Wallboard Slideshow'::TEXT AS tipo,
               carrossel.titulo AS titulo,	       
               'Duração: '|| ROUND(SUM(CASE WHEN COALESCE(cenario_item.tempoatualizacao, 0) > 0 THEN cenario_item.tempoatualizacao ELSE carrossel.tempoatualizacao END)::NUMERIC / 60, 2)
               || E' min\n<ul class="result-properties">'
               || ARRAY_TO_STRING(
                     ARRAY_AGG(
                        format('<li>%s</li>', COALESCE(consulta.descricaomenu, consulta.descricao, cenario_item.url)::TEXT)
                     ), E'\n')
               || E'\n</ul>' AS texto,
               'CarrosselView.aspx?id='||cenario.idcarrossel::TEXT         
            FROM cenario
            JOIN carrossel ON carrossel.id = cenario.idcarrossel
            JOIN cenario_item ON cenario_item.idcenario = cenario.id
            LEFT JOIN consulta ON consulta.id = cenario_item.idconsulta
            WHERE (carrossel.titulo ~* cSearchText
            OR consulta.descricao ~* cSearchText
            OR consulta.descricaomenu ~* cSearchText
            OR cenario_item.url ~* cSearchText)
            GROUP BY carrossel.titulo, cenario.idcarrossel, cenario_item.sequencia 
            ORDER BY titulo 
         ) AS QUERY
      LOOP
         RETURN NEXT searchResult;
      END LOOP;
   END IF;

   IF NOT bTypesSpecified OR 'usuario' = ANY(aTypes) OR 'all' = ANY(aTypes) THEN    
      FOR searchResult IN 
         SELECT 
            'USR'||usuario.id::TEXT AS idglobal,
            'Usuário' AS tipo,
            COALESCE(usuario.nomecompleto, usuario.loginusuario) AS titulo,

            format('<ul class="result-properties">
                     <li>Nome: %s</li>
                     <li>Login: %s</li>
                     <li>E-mail: %s</li>
                   </ul>'
                     , usuario.nomecompleto::TEXT
                     , usuario.loginusuario::TEXT
                     , CASE WHEN COALESCE(usuario.email, '') <> '' THEN
                        format('<a class="defaultLink" href="mailto:%s">%s</a>', usuario.email, usuario.email)
                       ELSE '' END) AS texto,
            format('UserForm.aspx?mode=edit&id=%s', usuario.id) AS url

         FROM usuario
         WHERE (usuario.nomecompleto ~* cSearchText
         OR usuario.loginusuario ~* cSearchText
         OR usuario.email ~* cSearchText)
         ORDER BY titulo
      LOOP
         RETURN NEXT searchResult;
      END LOOP;
   END IF;

   IF NOT bTypesSpecified OR 'documento' = ANY(aTypes) OR 'all' = ANY(aTypes) THEN    
      FOR searchResult IN 
         SELECT 
            'MKD'||doc.id::TEXT AS idglobal,
            'Documento' AS tipo,
            doc.title AS titulo,
            format('<ul class="result-properties">
                     <li>Proprietário: %s</li>
                     <li>Autor: %s</li>
                     <li>%s em: %s</li>
                   </ul>'
                     , gidmap.owner
                     , gidmap.author
                     , CASE WHEN doc.lastmodificationtime IS NULL THEN 'Criado' ELSE 'Modificado' END
                     , TO_CHAR(COALESCE(doc.lastmodificationtime, doc.creationtime), 'dd/mm/yyyy hh24:mi')) AS texto,
            format('DocumentWindow.aspx?id=%s', doc.id) AS url

         FROM markdowndocument doc
         JOIN globalidmapping gidmap ON gidmap.id = doc.globalid
         WHERE (doc.title ~* cSearchText
         OR doc.body ~* cSearchText)
         ORDER BY 
            -- Relevância
            CASE WHEN doc.title ~* cSearchText THEN 1 ELSE 2 END, doc.title
      LOOP
         RETURN NEXT searchResult;
      END LOOP;
   END IF;

END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.campodinamico', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.campodinamico', 'ri_fk_campodin_idbrowse') THEN
		EXECUTE 'ALTER TABLE campodinamico DROP CONSTRAINT ri_fk_campodin_idbrowse';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.campodinamico', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.campodinamico', 'chk_campodin_colunabrowse') THEN
		EXECUTE 'ALTER TABLE campodinamico DROP CONSTRAINT chk_campodin_colunabrowse';
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.campodinamico', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.campodinamico', 'chk_campodin_lookup') THEN
		EXECUTE 'ALTER TABLE campodinamico DROP CONSTRAINT chk_campodin_lookup';
	END IF;
END IF;

BEGIN
	UPDATE campodinamico 
	SET referencesxml = E'<!-- convertido -->\n'||fnc_fix_browserreferences(idbrowse, vinculobrowse, browsermode, browservaluemember, browserdisplaymember)
	WHERE tipo = 5
	AND idbrowse IS NOT NULL;
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'idbrowse') THEN
	COMMENT ON COLUMN campodinamico.idbrowse IS '[Obsolete("Use ReferencesXml instead.")] FK com a tabela BrowseDinamico';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'browservaluemember') THEN
	COMMENT ON COLUMN campodinamico.browservaluemember IS '[Obsolete("Use ReferencesXml instead.")] Nome da Coluna do Browse Dinamico';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'vinculobrowse') THEN
	COMMENT ON COLUMN campodinamico.vinculobrowse IS '[Obsolete("Use ReferencesXml instead.")] Campos + Valor do vinculo entre o browse e parâmetros, separados por ";"';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'browsermode') THEN
	COMMENT ON COLUMN campodinamico.browsermode IS '[Obsolete("Use ReferencesXml instead.")] Modo de utilização do Browser Dinâmico:
1. Popup
2. ComboBox';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'browserdisplaymember') THEN
	COMMENT ON COLUMN campodinamico.browserdisplaymember IS '[Obsolete("Use ReferencesXml instead.")] Nome da Coluna do Browse Dinamico';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'tipocolunabrowse') THEN
	COMMENT ON COLUMN campodinamico.tipocolunabrowse IS '[Obsolete("Use ReferencesXml instead.")] Tipo da coluna de retorno do Browser';
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.campodinamico', '') THEN
	IF NOT public.fnc_objectexists('CHECK CONSTRAINT', 'wecdb.campodinamico', 'chk_campodin_lookup') THEN
		ALTER TABLE campodinamico ADD CONSTRAINT chk_campodin_lookup CHECK (referencesxml IS NOT NULL OR tipo <> 5);
	END IF;
END IF;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();

SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_trg_campodinamico_bef_ins_upd_lookupfix ()
RETURNS SETOF TRIGGER AS 
$BODY$

   /*============================================================================
     Autor: Daniel Giacomelli                                  
     Data: 11/11/2019

     Trigger para preecher o campo "referencesxml" quando o 
     tipo de campo for "Lookup".
     A criação dessa trigger foi necessária para que o valor do campo
     sempre seja preenchido, mesmo que o LATROMI Client esteja desatualizado.
     No nomento em que todos os clientes estiverem atualizados, 
     essa trigger pode ser desabilitada.
   ==============================================================================*/
BEGIN

   IF TG_OP = 'UPDATE' 
      AND OLD.tipo = 5 
      AND NEW.tipo = 5 
      AND NOT COALESCE(OLD.referencesxml, '') LIKE '<!-- convertido -->%'
      AND OLD.idbrowse IS NULL 
      AND COALESCE(NEW.idbrowse, 0) > 0 
   THEN
      RAISE EXCEPTION 'Não é possível alterar o campo "%" porque ele foi gravado em uma versão mais recente do LATROMI Client. Por favor, atualize o sistema e tente novamente.', NEW.nome;
   END IF;

   --RAISE INFO '%, %, %-%', TG_OP, NEW.tipo, OLD.idformdinamico, NEW.idformdinamico;
   IF (TG_OP = 'UPDATE' AND NEW.tipo = 5 
      AND (COALESCE(NEW.referencesxml, '') = ''  OR 
         (COALESCE(NEW.idbrowse, 0) > 0 AND 
         (COALESCE(NEW.idbrowse, 0) <> COALESCE(OLD.idbrowse, 0) 
         OR COALESCE(NEW.vinculobrowse, '') <> COALESCE(OLD.vinculobrowse, '') 
         OR COALESCE(NEW.browsermode, 0) <> COALESCE(OLD.browsermode, 0) 
         OR COALESCE(NEW.browservaluemember, '') <> COALESCE(OLD.browservaluemember, '') 
         OR COALESCE(NEW.browserdisplaymember, '') <> COALESCE(OLD.browserdisplaymember, '')))
      ))
      OR (TG_OP = 'INSERT' AND NEW.tipo = 5 AND COALESCE(NEW.referencesxml, '') = '')
   THEN
      NEW.referencesxml = E'<!-- convertido -->\n'||fnc_fix_browserreferences(NEW.idbrowse, NEW.vinculobrowse, NEW.browsermode, NEW.browservaluemember, NEW.browserdisplaymember);
   END IF;

   RETURN NEW;

END;
$BODY$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_campodinamico_bef_ins_upd_lookupfix ON campodinamico;

CREATE TRIGGER trg_campodinamico_bef_ins_upd_lookupfix
  BEFORE INSERT OR UPDATE
  ON campodinamico
  FOR EACH ROW
  EXECUTE PROCEDURE fnc_trg_campodinamico_bef_ins_upd_lookupfix();

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.formdinamico_variavel', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.formdinamico_variavel', 'sequencia') THEN
		ALTER TABLE formdinamico_variavel ADD sequencia SMALLINT NOT NULL DEFAULT 0;
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.formdinamico_variavel', 'sequencia') THEN
	COMMENT ON COLUMN formdinamico_variavel.sequencia IS 'Sequencia da variavel dentro do container.';
END IF;

WITH q AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY idformdinamico ORDER BY id) AS seq, * 
    FROM formdinamico_variavel 
)
UPDATE formdinamico_variavel
SET sequencia = seq
FROM q
WHERE q.id = formdinamico_variavel.id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();

SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_fix_reportreferences(pnFieldID BIGINT)
   RETURNS TEXT AS 
$BODY$
DECLARE
   cParameters TEXT;
   cUpgradeCode TEXT;
   cDescription TEXT;
   cReferencesXml TEXT;
   cOutputType TEXT;
BEGIN

   SELECT INTO 
      cUpgradeCode, cDescription, cReferencesXml, cOutputType
      g.upgradecode, cs.descricao, c.referencesxml,

      CASE cs.outputtype
         WHEN 1 THEN 'Grid'
         WHEN 2 THEN 'Chart'
         WHEN 3 THEN 'CrossTab'
         WHEN 4 THEN 'CrystalReports'
         WHEN 5 THEN 'CrossMap'
         ELSE 'None'
      END outputtype

   FROM campodinamico c
   LEFT JOIN consulta cs ON cs.id = c.idconsulta
   LEFT JOIN globalidmapping g ON g.id = cs.globalid
   WHERE c.tipo = 12 AND c.id = pnFieldID;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Campo com Id % não encontrado.', pnFieldID;
   END IF;

   -- Já Convertido
   IF COALESCE(cReferencesXml, '') <> '' THEN

      -- Se a Consulta não foi encontrada, retorna o XML existente
      IF cUpgradeCode IS NULL THEN
         RETURN cReferencesXml;
      END IF;

      cParameters = substring(cReferencesXml FROM '<items>(.*?)</items>');

   ELSE
      SELECT INTO cParameters
         ARRAY_TO_STRING(ARRAY_AGG('<item name="'||csp.nome||'" enable="' || (CASE WHEN p.alterarvalor THEN 'true"' ELSE 'false"' END) 
            || (CASE WHEN csp.tipo = 5 THEN '' ELSE (' dataType="'|| CASE csp.tipo WHEN 3 THEN 'Date' WHEN 2 THEN 'String' ELSE 'String' END ||'"') END)
            || CASE WHEN COALESCE(p.valor, '') = '' THEN  ' />' ELSE ('>'||p.valor||'</item>')END ORDER BY csp.sequenciaparametro), E'\n      ' )
      FROM campodinamico_parametroconsulta p 
      LEFT JOIN consulta_parametro csp ON csp.id = p.idparametro
      WHERE p.idcampodinamico = pnFieldID;
   END IF;

   RETURN format(
'<references>
   <target type="Consulta" upgradeCode="%s" description="%s" />
   <settings>
      <setting name="outputType">%s</setting>
   </settings>
   <items>%s</items>
</references>', cUpgradeCode, common.fnc_xml_escape(cDescription, true), cOutputType, cParameters);

END;
$BODY$
LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.campodinamico', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.campodinamico', 'ri_fk_campodin_idcons') THEN
		EXECUTE 'ALTER TABLE campodinamico DROP CONSTRAINT ri_fk_campodin_idcons';
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'idconsulta') THEN
	COMMENT ON COLUMN campodinamico.idconsulta IS '[Obsolete("Use ReferencesXml instead.")] ID da Consulta';
END IF;

BEGIN
	UPDATE campodinamico 
	SET referencesxml = E'<!-- convertido -->\n'||fnc_fix_reportreferences(id)
	WHERE tipo = 12 AND NOT COALESCE(referencesxml, '') LIKE '%<settings>%';
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.fileextractor_parameter', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.fileextractor_parameter', 'sequence') THEN
		ALTER TABLE fileextractor_parameter ADD sequence SMALLINT NOT NULL DEFAULT 0;
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.fileextractor_parameter', 'sequence') THEN
	COMMENT ON COLUMN fileextractor_parameter.sequence IS 'Sequencia da variavel dentro do container.';
END IF;

WITH q AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY fileextractorid ORDER BY id) AS seq, * 
    FROM fileextractor_parameter 
)
UPDATE fileextractor_parameter
SET sequence = seq
FROM q
WHERE q.id = fileextractor_parameter.id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();

SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_fix_extractorreferences(pnFileExtractorID INT, pcReferencesXML TEXT)
   RETURNS TEXT AS 
$BODY$
DECLARE
   cParameters TEXT;
   cUpgradeCode TEXT;
   cDescription TEXT;
   cReferencesXml TEXT = pcReferencesXML;
BEGIN

   SELECT INTO 
      cUpgradeCode, cDescription
      g.upgradecode, fx.name
   FROM fileextractor fx
   LEFT JOIN globalidmapping g ON g.id = fx.globalid
   WHERE fx.id = pnFileExtractorID;

   -- Já Convertido
   IF COALESCE(cReferencesXml, '') <> '' THEN

      -- Se a Consulta não foi encontrada, retorna o XML existente
      IF cUpgradeCode IS NULL THEN
         RETURN cReferencesXml;
      END IF;

      cParameters = substring(cReferencesXml FROM '<items>(.*?)</items>');
   END IF;

   RETURN format(
'<references>
   <target type="FileExtractor" upgradeCode="%s" description="%s" />
   <items>
      %s
   </items>
</references>', cUpgradeCode, common.fnc_xml_escape(cDescription, true), cParameters);

END;
$BODY$
LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'idformdinamico') THEN
	COMMENT ON COLUMN campodinamico.idformdinamico IS 'ID do Formulário';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.campodinamico', 'fileextractorid') THEN
	COMMENT ON COLUMN campodinamico.fileextractorid  IS '[Obsolete("Use ReferencesXml instead.")] ID do extrator de arquivos';
END IF;

BEGIN
	UPDATE campodinamico SET
	referencesxml = E'<!-- convertido -->\n'||fnc_fix_extractorreferences(fileextractorid, referencesxml)
	WHERE tipo = 16
	AND fileextractorid IS NOT NULL
	AND NOT referencesxml LIKE '%<target %';
EXCEPTION
	WHEN unique_violation OR undefined_column THEN
		RAISE INFO '%', SQLERRM;
END;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();

SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_trg_campodinamico_bef_ins_upd_imageviewfix ()
RETURNS SETOF TRIGGER AS 
$BODY$

   /*============================================================================
     Autor: Daniel Giacomelli                                  
     Data: 14/11/2019

     Trigger para preecher o campo "referencesxml" quando o 
     tipo de campo for "Lookup".
     A criação dessa trigger foi necessária para que o valor do campo
     sempre seja preenchido, mesmo que o LATROMI Client esteja desatualizado.
     No nomento em que todos os clientes estiverem atualizados, 
     essa trigger pode ser desabilitada.
   ==============================================================================*/
BEGIN

   IF TG_OP = 'UPDATE' 
      AND OLD.tipo = 16 
      AND NEW.tipo = 16 
      AND NOT COALESCE(OLD.referencesxml, '') LIKE '<!-- convertido -->%'
      AND OLD.fileextractorid IS NULL 
      AND COALESCE(NEW.fileextractorid, 0) > 0 
   THEN
      RAISE EXCEPTION 'Não é possível alterar o campo "%" porque ele foi gravado em uma versão mais recente do LATROMI Client. Por favor, atualize o sistema e tente novamente.', NEW.nome;
   END IF;

   IF (TG_OP = 'UPDATE' AND NEW.tipo = 16 AND COALESCE(NEW.fileextractorid, 0) > 0
      AND (NOT COALESCE(NEW.referencesxml, '') LIKE '%<target %' OR COALESCE(NEW.fileextractorid, 0) <> COALESCE(OLD.fileextractorid, 0))
      )
      OR (TG_OP = 'INSERT' AND NEW.tipo = 16 AND COALESCE(NEW.fileextractorid, 0) > 0 AND NOT COALESCE(NEW.referencesxml, '') LIKE '%<target %' )
   THEN
      NEW.referencesxml = E'<!-- convertido -->\n'||fnc_fix_extractorreferences(NEW.fileextractorid, NEW.referencesxml);
   END IF;

   RETURN NEW;

END;
$BODY$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_campodinamico_bef_ins_upd_imageviewfix ON campodinamico;

CREATE TRIGGER trg_campodinamico_bef_ins_upd_imageviewfix
  BEFORE INSERT OR UPDATE
  ON campodinamico
  FOR EACH ROW
  EXECUTE PROCEDURE fnc_trg_campodinamico_bef_ins_upd_imageviewfix();

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.browsedinamico', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.browsedinamico', 'cacheenabled') THEN
		ALTER TABLE browsedinamico ADD cacheenabled boolean NOT NULL DEFAULT false;
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.browsedinamico', 'cacheenabled') THEN
	COMMENT ON COLUMN browsedinamico.cacheenabled IS 'Se true, o Data Source será armazenado em memória ao executar a Browse pela primeira vez, e ao navegar no paginador, ordenar, agrupar e etc, os dados serão pegos diretamente da memória, ao invés da query ser executada novamente no banco de dados..';
END IF;

IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF public.fnc_objectexists('TABLE', 'wecdb.windows', '') THEN
	IF public.fnc_objectexists('CONSTRAINT', 'wecdb.windows', 'fk_win_tgid') THEN
		EXECUTE 'ALTER TABLE windows DROP CONSTRAINT fk_win_tgid';
	END IF;
END IF;

ALTER TABLE windows ALTER COLUMN targetglobalid SET DEFAULT 0;
IF public.fnc_objectexists('COLUMN', 'wecdb.windows', 'targetglobalid') THEN
	COMMENT ON COLUMN windows.targetglobalid IS '[Obsolete("Use TargetReferences instead.")] Global ID do Alvo (o objeto que será aberto)';
END IF;

IF NOT public.fnc_objectexists('NAMESPACE', '', 'wecdb') THEN
	CREATE SCHEMA wecdb;
END IF;
SET SEARCH_PATH = wecdb;

IF NOT public.fnc_objectexists('TABLE', 'wecdb.packages', '') THEN
	CREATE TABLE packages
	(
	   id                SERIAL NOT NULL,
	   name              TEXT NOT NULL,
	   author            TEXT,
	   creationtime      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
	   filename          TEXT,
	   filecontent       BYTEA,
	   filesize          BIGINT,
	   filehash          VARCHAR(64),
	   xmlinfo           TEXT
	);
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.packages', '') THEN
	IF NOT public.fnc_objectexists('PRIMARY KEY CONSTRAINT', 'wecdb.packages', '') THEN
		ALTER TABLE packages ADD CONSTRAINT pk_package_is PRIMARY KEY (id);
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.packages', '') THEN
	COMMENT ON TABLE packages IS 'Tabela onde são armazenados os arquivo de extensão .lcp gerados.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.packages', 'name') THEN
	COMMENT ON COLUMN packages.name IS 'Nome do pacote.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.packages', 'author') THEN
	COMMENT ON COLUMN packages.author IS 'Autor do Pacote.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.packages', 'creationtime') THEN
	COMMENT ON COLUMN packages.creationtime IS 'Data e Hora de Criação do Pacote.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.packages', 'filename') THEN
	COMMENT ON COLUMN packages.filename IS 'Nome do Arquivo.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.packages', 'filecontent') THEN
	COMMENT ON COLUMN packages.filecontent IS 'Blob do Arquivo.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.packages', 'filesize') THEN
	COMMENT ON COLUMN packages.filesize IS 'Tamanho do Arquivo em bytes.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.packages', 'filehash') THEN
	COMMENT ON COLUMN packages.filehash IS 'Hash do Arquivo.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.packages', 'xmlinfo') THEN
	COMMENT ON COLUMN packages.xmlinfo IS 'Xml com as informações do Arquivo @info.xml, que ficam dentro do pacote.';
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.revisions', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.revisions', 'lastpackagecreationtime') THEN
		ALTER TABLE revisions ADD lastpackagecreationtime TIMESTAMP WITH TIME ZONE;
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.revisions', '') THEN
	IF NOT public.fnc_objectexists('COLUMN', 'wecdb.revisions', 'lastpackageid') THEN
		ALTER TABLE revisions ADD lastpackageid INT;
	END IF;
END IF;

IF public.fnc_objectexists('TABLE', 'wecdb.revisions', '') THEN
	IF NOT public.fnc_objectexists('FOREIGN KEY CONSTRAINT', 'wecdb.revisions', 'fk_revs_lastpackageid') THEN
		ALTER TABLE revisions ADD CONSTRAINT fk_revs_lastpackageid FOREIGN KEY (lastpackageid) REFERENCES packages(id) ON DELETE SET NULL ON UPDATE CASCADE;
	END IF;
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.revisions', 'lastpackagecreationtime') THEN
	COMMENT ON COLUMN revisions.lastpackagecreationtime IS 'Data e Hora da criação do pacote.';
END IF;

IF public.fnc_objectexists('COLUMN', 'wecdb.revisions', 'lastpackageid') THEN
	COMMENT ON COLUMN revisions.lastpackageid IS 'ID do último pacote gerado. Esta coluna fica nula se o pacote for apagado.';
END IF;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();

SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_fix_documents(pcBody TEXT)
RETURNS TEXT AS 
$BODY$
DECLARE
   rDados RECORD;
   cBody TEXT = pcBody;
BEGIN

   FOR rDados IN 
      SELECT 
         fileid
         , codigoatualizacao
      FROM (
         SELECT
             (regexp_matches(cBody, '\]\(FileID:(\d+)', 'g'))[1]::BIGINT AS fileid
      ) q
      JOIN arquivobinario a ON a.id = q.fileid
   LOOP
      cBody = REPLACE(cBody, '](FileID:'||rDados.fileid::TEXT, '](FileID:'||rDados.codigoatualizacao::TEXT);
      RAISE DEBUG 'Id de Arquivo % convertido para %', rDados.fileid, rDados.codigoatualizacao;
   END LOOP;

   FOR rDados IN 
      SELECT 
         docid
         , upgradecode
      FROM (
         SELECT
            (regexp_matches(cBody, '\]\(DocID:(\d+)\)', 'g'))[1]::INT AS docid
      ) q
      JOIN markdowndocument d ON d.id = q.docid
      JOIN globalidmapping g ON g.id = d.globalid
   LOOP
      cBody = REPLACE(cBody, '](DocID:'||rDados.docid::TEXT, '](DocID:'||rDados.upgradecode::TEXT);
      RAISE DEBUG 'Id de Documento % convertido para %', rDados.docid, rDados.upgradecode;
   END LOOP;

   RETURN cBody;
END;
$BODY$
LANGUAGE plpgsql STABLE;


SET SEARCH_PATH = wecdb;

CREATE OR REPLACE FUNCTION fnc_trg_markdowndocument_bef_ins_upd_bodyfix ()
RETURNS SETOF TRIGGER AS 
$BODY$

   /*============================================================================
     Autor: Daniel Giacomelli                                  
     Data: 29/11/2019

     Trigger para corrigir as referencias de documentos e arquivos no campo "body".
     A criação dessa trigger foi necessária para que o valor do campo
     sempre seja preenchido, mesmo que o LATROMI Client esteja desatualizado.
     No nomento em que todos os clientes estiverem atualizados, 
     essa trigger pode ser desabilitada.
   ==============================================================================*/
BEGIN

   IF (TG_OP = 'UPDATE' AND COALESCE(OLD.body, '') <> COALESCE(NEW.body, '') AND NEW.body ~ '\]\(FileID:\d+[ "\)]')
   OR (TG_OP = 'INSERT' AND NEW.body ~ '\]\(FileID:\d+[ "\)]')
   THEN
      NEW.body = fnc_fix_documents(NEW.body);
   END IF;

   RETURN NEW;

END;
$BODY$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_markdowndocument_bef_ins_upd_bodyfix ON markdowndocument;

CREATE TRIGGER trg_markdowndocument_bef_ins_upd_bodyfix
  BEFORE INSERT OR UPDATE
  ON markdowndocument
  FOR EACH ROW
  EXECUTE PROCEDURE fnc_trg_markdowndocument_bef_ins_upd_bodyfix();



CREATE OR REPLACE FUNCTION fnc_tmp_update_sistema(v1 INT, v2 INT, v3 INT, v4 INT)
RETURNS VOID AS
$BODY$
DECLARE
	rOld	RECORD;
	cDetails	TEXT;
BEGIN

	SELECT INTO rOld * FROM sistema;

	IF FOUND THEN
		cDetails = E'Aplicativo: LATROMI DataBase\r\n'
                 ||'Versão anterior: '||rOld.versaomaior||'.'||rOld.versaomenor||'.'||rOld.compilacao||'.'||rOld.revisao||E'\r\n'
                 ||'Versão atual: '||v1||'.'||v2||'.'||v3||'.'||v4;

	ELSE
		cDetails = E'Aplicativo: LATROMI DataBase\r\n'
                 ||E'Versão anterior: nenhuma\r\n'
                 ||'Versão atual: '||v1||'.'||v2||'.'||v3||'.'||v4;
	END IF;

	UPDATE sistema  SET
	versaomaior = v1
	, versaomenor = v2
	, compilacao = v3
	, revisao = v4
   , dtatualizacao = CURRENT_TIMESTAMP;

	INSERT INTO wecdb.logentry(messageid, type, description, source, category, details) 
	VALUES (2, 1, 'LATROMI DataBase foi atualizado para a versão '||v1||'.'||v2||'.'||v3||'.'||v4, 'LATROMI DataBase', 'System', cDetails);

END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public._tmp_upgrade_20200121125949()
  RETURNS VOID AS
$BODY$

DECLARE
BEGIN
PERFORM fnc_tmp_update_sistema(3,5,53,4);
DROP FUNCTION IF EXISTS fnc_tmp_update_sistema(v1 INT, v2 INT, v3 INT, v4 INT);
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT public._tmp_upgrade_20200121125949();
DROP FUNCTION public._tmp_upgrade_20200121125949();
