ALTER TABLE TBMODELO_COMPOSICAO
    ADD ORDEM INTEGER;
	
ALTER TABLE TBMODELO_COMPOSICAO
    ADD SEPARADOR INTEGER DEFAULT 0;


COMMENT ON COLUMN TBMODELO_COMPOSICAO.SEPARADOR IS
'0 - Nao e utilizado como separador de composicao
1 - E utilizado como separador de composicao (utiliza a observacao como texto)';

/************************************************************************/
/************************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure SPU_AJUSTE_ORDEM_COMPOSICAO
as
declare variable MODELO_ID integer;
declare variable COMPOSICAO_ID integer;
declare variable NEW_ORDEM integer;
BEGIN

    FOR
        SELECT
            T.MODELO_ID,
            T.COMPOSICAO_ID,
        
            ROW_NUMBER() OVER (PARTITION BY T.MODELO_ID) AS NEW_ORDEM
        
        
        FROM TBMODELO_COMPOSICAO T
        WHERE T.ORDEM IS NULL

    INTO
        :MODELO_ID,
        :COMPOSICAO_ID,
        :NEW_ORDEM

    DO
    BEGIN

        UPDATE TBMODELO_COMPOSICAO
            SET ORDEM = :NEW_ORDEM
            WHERE MODELO_ID = :MODELO_ID
            AND COMPOSICAO_ID = :COMPOSICAO_ID;

    END
END
^

SET TERM ; ^

/************************************************************************/

EXECUTE PROCEDURE SPU_AJUSTE_ORDEM_COMPOSICAO;

/************************************************************************/

UPDATE TBMODELO_COMPOSICAO
    SET SEPARADOR = 0
    WHERE SEPARADOR IS NULL;

/************************************************************************/
/************************************************************************/
/************************************************************************/

ALTER TABLE TBMODELO_COMPOSICAO
    ADD ID INTEGER;


CREATE SEQUENCE GTBMODELO_COMPOSICAO;

/************************************************************************/

UPDATE TBMODELO_COMPOSICAO
    SET ID = GEN_ID(GTBMODELO_COMPOSICAO, 1)
    WHERE ID IS NULL;
	

ALTER TABLE TBMODELO_COMPOSICAO ALTER ID SET NOT NULL;
ALTER TABLE TBMODELO_COMPOSICAO ALTER ORDEM SET NOT NULL;

/************************************************************************/

alter table TBMODELO_COMPOSICAO
alter ID position 1;

alter table TBMODELO_COMPOSICAO
alter ORDEM position 2;

alter table TBMODELO_COMPOSICAO
alter MODELO_ID position 3;

alter table TBMODELO_COMPOSICAO
alter COMPOSICAO_ID position 4;

alter table TBMODELO_COMPOSICAO
alter PERCENTUAL position 5;

alter table TBMODELO_COMPOSICAO
alter OBSERVACAO position 6;

alter table TBMODELO_COMPOSICAO
alter SEPARADOR position 7;

/************************************************************************/
/************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbmodelo_composicao_b02i for tbmodelo_composicao
active before insert position 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBMODELO_COMPOSICAO, 1);
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbmodelo_composicao_b01i for tbmodelo_composicao
active before insert position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' COMPOSICAO ID:'||FN_LPAD(NEW.COMPOSICAO_ID, 4, '0')||' ('|| NEW.PERCENTUAL ||') INCLUIDA.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBMODELO', NEW.MODELO_ID, HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbmodelo_composicao_b01u for tbmodelo_composicao
active before update position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN
  
    HISTORICO = '';
    
    IF (OLD.ORDEM <> NEW.ORDEM) THEN
    BEGIN
        HISTORICO = HISTORICO||' ORDEM:'||OLD.ORDEM||'->'||NEW.ORDEM;
    END

    IF (OLD.MODELO_ID <> NEW.MODELO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' MODELO:'||OLD.MODELO_ID||'->'||NEW.MODELO_ID;
    END

    IF (OLD.COMPOSICAO_ID <> NEW.COMPOSICAO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' COMPOSICAO:'||OLD.COMPOSICAO_ID||'->'||NEW.COMPOSICAO_ID;
    END

    IF (OLD.PERCENTUAL <> NEW.PERCENTUAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' PERC.(%):'||OLD.PERCENTUAL||'->'||NEW.PERCENTUAL;
    END

    IF (OLD.OBSERVACAO <> NEW.OBSERVACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' OBSERVACAO:'||OLD.OBSERVACAO||'->'||NEW.OBSERVACAO;
    END

    IF (OLD.SEPARADOR <> NEW.SEPARADOR) THEN
    BEGIN
        HISTORICO = HISTORICO||' SEPARADOR:'||OLD.SEPARADOR||'->'||NEW.SEPARADOR;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       HISTORICO = ' COMPOSICAO ID: '||FN_LPAD(NEW.COMPOSICAO_ID, 4, '0')||' ('|| NEW.PERCENTUAL ||') ALTERADA:'||HISTORICO;
       EXECUTE PROCEDURE SPI_HISTORICO('TBMODELO', NEW.MODELO_ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbmodelo_composicao_b01d for tbmodelo_composicao
active before delete position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.COMPOSICAO_ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' COMPOSICAO ID:'||FN_LPAD(OLD.COMPOSICAO_ID, 4, '0')||' ('|| OLD.PERCENTUAL ||') EXCLUIDA.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBMODELO', OLD.MODELO_ID, HISTORICO);
    END
END
^

SET TERM ; ^

