ALTER TABLE TBMODELO_COMPOSICAO
    ADD PECA INTEGER DEFAULT 1;


UPDATE TBMODELO_COMPOSICAO
	SET PECA = 1
	WHERE PECA IS NULL;

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

CREATE INDEX ITBMODELO_COMPOSICAO_05
ON TBMODELO_COMPOSICAO (SEPARADOR);

CREATE INDEX ITBMODELO_COMPOSICAO_04
ON TBMODELO_COMPOSICAO (PECA);

CREATE INDEX ITBMODELO_COMPOSICAO_03
ON TBMODELO_COMPOSICAO (COMPOSICAO_ID);

CREATE INDEX ITBMODELO_COMPOSICAO_02
ON TBMODELO_COMPOSICAO (ORDEM);

CREATE INDEX ITBMODELO_COMPOSICAO_01
ON TBMODELO_COMPOSICAO (MODELO_ID);

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

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 ||'), PECA: '||NEW.PECA||' 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 (OLD.PECA <> NEW.PECA) THEN
    BEGIN
        HISTORICO = HISTORICO||' PECA:'||OLD.PECA||'->'||NEW.PECA;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       HISTORICO = ' COMPOSICAO ID: '||FN_LPAD(NEW.COMPOSICAO_ID, 4, '0')||' ('|| NEW.PERCENTUAL ||'), PECA: '||NEW.PECA||' 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 ||'), PECA: '||OLD.PECA||' EXCLUIDA.';
    END

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

SET TERM ; ^

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

CREATE GENERATOR GTBMODELO_SIMBOLOGIA_COMP;

CREATE TABLE TBMODELO_SIMBOLOGIA_COMP (
    ID                      INTEGER NOT NULL,
    MODELO_ID               INTEGER,
    ETIQUETA_COMPOSICAO_ID  INTEGER,
    PECA                    INTEGER DEFAULT 1
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBMODELO_SIMBOLOGIA_COMP ADD CONSTRAINT PK_TBMODELO_SIMBOLOGIA_COMP PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBMODELO_SIMBOLOGIA_COMP_01 ON TBMODELO_SIMBOLOGIA_COMP (MODELO_ID);
CREATE INDEX ITBMODELO_SIMBOLOGIA_COMP_02 ON TBMODELO_SIMBOLOGIA_COMP (ETIQUETA_COMPOSICAO_ID);
CREATE INDEX ITBMODELO_SIMBOLOGIA_COMP_03 ON TBMODELO_SIMBOLOGIA_COMP (PECA);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBMODELO_SIMBOLOGIA_COMP_B01D */
CREATE OR ALTER TRIGGER TTBMODELO_SIMBOLOGIA_COMP_B01D FOR TBMODELO_SIMBOLOGIA_COMP
ACTIVE BEFORE DELETE POSITION 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    :HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        :HISTORICO = :HISTORICO||' SIMBOLOGIA ID:'||FN_LPAD(OLD.ETIQUETA_COMPOSICAO_ID, 4, '0')||', PECA: '||OLD.PECA||' EXCLUIDA.';
    END

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


/* Trigger: TTBMODELO_SIMBOLOGIA_COMP_B01I */
CREATE OR ALTER TRIGGER TTBMODELO_SIMBOLOGIA_COMP_B01I FOR TBMODELO_SIMBOLOGIA_COMP
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBMODELO_SIMBOLOGIA_COMP, 1);
END
^


/* Trigger: TTBMODELO_SIMBOLOGIA_COMP_B01U */
CREATE OR ALTER TRIGGER TTBMODELO_SIMBOLOGIA_COMP_B01U FOR TBMODELO_SIMBOLOGIA_COMP
ACTIVE BEFORE UPDATE POSITION 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

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

    IF (OLD.ETIQUETA_COMPOSICAO_ID IS DISTINCT FROM NEW.ETIQUETA_COMPOSICAO_ID) THEN
    BEGIN
        :HISTORICO = :HISTORICO||' ETQ.COMP:'||OLD.ETIQUETA_COMPOSICAO_ID||'->'||NEW.ETIQUETA_COMPOSICAO_ID;
    END

    IF (OLD.PECA IS DISTINCT FROM NEW.PECA) THEN
    BEGIN
        :HISTORICO = :HISTORICO||' PECA:'||OLD.PECA||'->'||NEW.PECA;
    END

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


/* Trigger: TTBMODELO_SIMBOLOGIA_COMP_B02I */
CREATE OR ALTER TRIGGER TTBMODELO_SIMBOLOGIA_COMP_B02I FOR TBMODELO_SIMBOLOGIA_COMP
ACTIVE BEFORE INSERT POSITION 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    :HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        :HISTORICO = :HISTORICO||' SIMBOLOGIA ID:'||FN_LPAD(NEW.ETIQUETA_COMPOSICAO_ID, 4, '0')||', PECA: '||NEW.PECA||' INCLUIDA.';
    END

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

SET TERM ; ^