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


CREATE GENERATOR GTBPCM_SETOR_STATUS;

CREATE TABLE TBPCM_SETOR_STATUS (
    ID               INTEGER NOT NULL,
    SETOR_ID         INTEGER,
    DESCRICAO        VARCHAR(100),
    COR              VARCHAR(10),
    TIPO             CHAR(1),
    DESCRICAO_ABREV  VARCHAR(15)
);




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

ALTER TABLE TBPCM_SETOR_STATUS ADD CONSTRAINT PK_TBPCM_SETOR_STATUS PRIMARY KEY (ID);


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



SET TERM ^ ;



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


/* Trigger: TTBPCM_SETOR_STATUS_B01D */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_STATUS_B01D FOR TBPCM_SETOR_STATUS
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE CHAMADOS INTEGER;
BEGIN

    CHAMADOS = COALESCE((SELECT FIRST 1 A.ID FROM TBPCM_REGISTRO_CHAMADO A WHERE A.STATUS = OLD.ID), 0);
    
    IF (CHAMADOS > 0) THEN
    BEGIN
        EXCEPTION "..." 'O STATUS:'||FN_LPAD(OLD.ID, 4, '0')||' - '||OLD.DESCRICAO||' ESTÁ EM USO NA TELA DE CHAMADOS. EXCLUSÃO NÃO AUTORIZADA!';
    END
END
^


/* Trigger: TTBPCM_SETOR_STATUS_B02D */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_STATUS_B02D FOR TBPCM_SETOR_STATUS
ACTIVE BEFORE DELETE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';
    
    IF (OLD.ID = 0) THEN
    BEGIN
        EXCEPTION "..." 'Este registro não pode ser excluído!';
    END

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'STATUS: '||FN_LPAD(OLD.ID, 4, '0')||' - '||OLD.DESCRICAO||' EXCLUIDO';
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', OLD.SETOR_ID, HISTORICO);
    END
END
^


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


/* Trigger: TTBPCM_SETOR_STATUS_B01U */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_STATUS_B01U FOR TBPCM_SETOR_STATUS
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.ID = 0) THEN
    BEGIN
        EXCEPTION "..." 'Este registro não pode ser alterado!';
    END

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

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

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

    IF (OLD.DESCRICAO_ABREV <> NEW.DESCRICAO_ABREV) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC.ABREV:'||OLD.DESCRICAO_ABREV||'->'||NEW.DESCRICAO_ABREV;
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'STATUS: '||FN_LPAD(OLD.ID, 4, '0')||' - '||NEW.DESCRICAO||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', OLD.SETOR_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_SETOR_STATUS_B02I */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_STATUS_B02I FOR TBPCM_SETOR_STATUS
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'STATUS: '||FN_LPAD(NEW.ID, 4, '0')||' - '||NEW.DESCRICAO||' INCLUIDO';
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', NEW.SETOR_ID, HISTORICO);
    END
END
^

SET TERM ; ^



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

COMMENT ON COLUMN TBPCM_SETOR_STATUS.COR IS 
'Cor do Status na Tela';

COMMENT ON COLUMN TBPCM_SETOR_STATUS.TIPO IS 
'0 - ABERTURA (SOMENTE UM STATUS DE ABERTURA - ID: 0); 
1 - ATENDIMENTO;
2 - PAUSA;
3 - ENCERRAMENTO;';

COMMENT ON COLUMN TBPCM_SETOR_STATUS.DESCRICAO_ABREV IS 
'Descrição do Status Abreviada';

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

SET TERM ^ ;

CREATE OR ALTER trigger ttbpcm_setor_b02d for tbpcm_setor
active before delete position 1
AS
BEGIN
    DELETE FROM TBPCM_SETOR_OPERADOR A WHERE A.SETOR_ID = OLD.ID;
    DELETE FROM TBPCM_SETOR_STATUS B WHERE B.SETOR_ID = OLD.ID;
END
^

SET TERM ; ^

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

UPDATE OR INSERT INTO TBPCM_SETOR_STATUS (ID, SETOR_ID, DESCRICAO, COR, TIPO, DESCRICAO_ABREV) 
	VALUES (0, NULL, 'ABERTO', '#f0ad4e', '0', 'ABERTO')
	MATCHING (ID);
	
