/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBCASO_FINALIZACAO;

CREATE TABLE TBCASO_FINALIZACAO (
    ID         INTEGER NOT NULL,
    PAINEL_ID  INTEGER,
    CASO_ID    INTEGER,
    PROBLEMA   BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    SOLUCAO    BLOB SUB_TYPE 1 SEGMENT SIZE 80
);




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

ALTER TABLE TBCASO_FINALIZACAO ADD CONSTRAINT PK_TBCASO_FINALIZACAO PRIMARY KEY (ID);


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

CREATE INDEX ITBCASO_FINALIZACAO_01 ON TBCASO_FINALIZACAO (PAINEL_ID);
CREATE INDEX ITBCASO_FINALIZACAO_02 ON TBCASO_FINALIZACAO (CASO_ID);


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



SET TERM ^ ;



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



/* Trigger: TTBCASO_FINALIZACAO_B01D */
CREATE OR ALTER TRIGGER TTBCASO_FINALIZACAO_B01D FOR TBCASO_FINALIZACAO
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN

    IF (COALESCE(OLD.PROBLEMA, '') <> '') THEN
    BEGIN

        EXECUTE PROCEDURE SPI_CASO_HISTORICO(OLD.CASO_ID, OLD.PAINEL_ID, 'DESCRICAO DO PROBLEMA DO CASO EXCLUIDA');

    END

    IF (COALESCE(OLD.SOLUCAO, '') <> '') THEN
    BEGIN

        EXECUTE PROCEDURE SPI_CASO_HISTORICO(OLD.CASO_ID, OLD.PAINEL_ID, 'SOLUCAO DO CASO EXCLUIDA');

    END

END
^


/* Trigger: TTBCASO_FINALIZACAO_B01I */
CREATE OR ALTER TRIGGER TTBCASO_FINALIZACAO_B01I FOR TBCASO_FINALIZACAO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (COALESCE(NEW.ID, 0) = 0) THEN
        NEW.ID = GEN_ID(GTBCASO_FINALIZACAO, 1);
END
^


/* Trigger: TTBCASO_FINALIZACAO_B01U */
CREATE OR ALTER TRIGGER TTBCASO_FINALIZACAO_B01U FOR TBCASO_FINALIZACAO
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO_PROBLEMA VARCHAR(5000);
DECLARE VARIABLE HISTORICO_SOLUCAO VARCHAR(5000);
BEGIN

    :HISTORICO_PROBLEMA = '';
    :HISTORICO_SOLUCAO = '';

    IF (COALESCE(OLD.PROBLEMA, '') <> COALESCE(NEW.PROBLEMA, '')) THEN
    BEGIN

        :HISTORICO_PROBLEMA = :HISTORICO_PROBLEMA||' '||FN_ELIPSE(OLD.PROBLEMA, 400)||'->'||FN_ELIPSE(NEW.PROBLEMA, 400);

        EXECUTE PROCEDURE SPI_CASO_HISTORICO(NEW.CASO_ID, NEW.PAINEL_ID, 'DESCRICAO DO PROBELMA DO CASO ALTERADA:'||FN_ELIPSE(:HISTORICO_PROBLEMA, 800));

    END

    IF (COALESCE(OLD.SOLUCAO, '') <> COALESCE(NEW.SOLUCAO, '')) THEN
    BEGIN

        :HISTORICO_SOLUCAO = :HISTORICO_SOLUCAO||' '||FN_ELIPSE(OLD.SOLUCAO, 400)||'->'||FN_ELIPSE(NEW.SOLUCAO, 400);

        EXECUTE PROCEDURE SPI_CASO_HISTORICO(NEW.CASO_ID, NEW.PAINEL_ID, 'SOLUCAO DO CASO ALTERADA:'||FN_ELIPSE(:HISTORICO_SOLUCAO, 800));

    END

END
^


/* Trigger: TTBCASO_FINALIZACAO_B02I */
CREATE OR ALTER TRIGGER TTBCASO_FINALIZACAO_B02I FOR TBCASO_FINALIZACAO
ACTIVE BEFORE INSERT POSITION 1
AS
BEGIN

    IF (COALESCE(NEW.PROBLEMA, '') <> '') THEN
    BEGIN

        EXECUTE PROCEDURE SPI_CASO_HISTORICO(NEW.CASO_ID, NEW.PAINEL_ID, 'DESCRICAO DO PROBLEMA DO CASO ADICIONADA');

    END

    IF (COALESCE(NEW.SOLUCAO, '') <> '') THEN
    BEGIN

        EXECUTE PROCEDURE SPI_CASO_HISTORICO(NEW.CASO_ID, NEW.PAINEL_ID, 'SOLUCAO DO CASO ADICIONADA');

    END

END
^

SET TERM ; ^



/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBCASO_FINALIZACAO.PAINEL_ID IS 
'id do painel de caso';

COMMENT ON COLUMN TBCASO_FINALIZACAO.CASO_ID IS 
'id do caso';

COMMENT ON COLUMN TBCASO_FINALIZACAO.PROBLEMA IS 
'Descricao tecnica do caso';

COMMENT ON COLUMN TBCASO_FINALIZACAO.SOLUCAO IS 
'Solucao do caso';

