ALTER TABLE PESSOA
    ADD FV_VERSION_CURRENT SMALLINT,
    ADD FV_VERSION_NEXT SMALLINT,
    ADD FV_VERSION_LINK VARCHAR(100),
    ADD FV_VERSION_NOTE VARCHAR(500),
	ADD FV_VERSION_LENGTH INTEGER DEFAULT 0;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE SEL_FV_VERSION_RELEASE(PID_REP INTEGER, SELECT_ALL SMALLINT)
RETURNS (
    ID_REP INTEGER,
    RAZ_SOC VARCHAR(100),
    FV_VERSION_CURRENT SMALLINT,
    FV_VERSION_NEXT SMALLINT,
    FV_VERSION_LINK VARCHAR(100),
    FV_VERSION_NOTE VARCHAR(500),
	FV_VERSION_LENGTH INTEGER)
AS
DECLARE FALSE SMALLINT = 0;
DECLARE TRUE  SMALLINT = 1;
BEGIN
  FOR
    SELECT PESSOA.ID_PES,
           PESSOA.RAZ_SOC,
           PESSOA.FV_VERSION_CURRENT,
           PESSOA.FV_VERSION_NEXT,
           PESSOA.FV_VERSION_LINK,
           PESSOA.FV_VERSION_NOTE,
		   PESSOA.FV_VERSION_LENGTH
    FROM PESSOA JOIN REPRES ON (PESSOA.ID_PES = REPRES.ID_PES AND REPRES.FORC_VEND = :TRUE AND PESSOA.INATIVO = :FALSE)
    WHERE (PESSOA.ID_PES = :PID_REP AND :SELECT_ALL = :FALSE) OR (COALESCE(:PID_REP, 0) = 0 AND :SELECT_ALL = :TRUE)
    INTO :ID_REP,
         :RAZ_SOC,
         :FV_VERSION_CURRENT,
         :FV_VERSION_NEXT,
         :FV_VERSION_LINK,
         :FV_VERSION_NOTE,
		 :FV_VERSION_LENGTH
  DO
  BEGIN
    SUSPEND;
  END
END^

SET TERM ; ^

GRANT EXECUTE ON PROCEDURE SEL_FV_VERSION_RELEASE TO MARCELO;
GRANT EXECUTE ON PROCEDURE SEL_FV_VERSION_RELEASE TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SEL_FV_VERSION_RELEASE TO ROLE_ADMIN;


UPDATE PESSOA SET 
    FV_VERSION_CURRENT = 603,
    FV_VERSION_NEXT = 603,
    FV_VERSION_LINK = 'https://www.idealbrasil.net/ForcaV6.0.3.apk',
    FV_VERSION_NOTE = NULL,
	FV_VERSION_LENGTH = 8666379
WHERE ID_PES IN (SELECT REPRES.ID_PES FROM REPRES WHERE REPRES.FORC_VEND = 1) AND PESSOA.INATIVO = 0;
COMMIT WORK;