CREATE TABLE TBCHECKLIST (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    STATUS INTEGER,
    SEGUNDA INTEGER,
    TERCA INTEGER,
    QUARTA INTEGER,
    QUINTA INTEGER,
    SEXTA INTEGER,
    SABADO INTEGER,
    DOMINGO INTEGER,
    DIAS VARCHAR(100));

ALTER TABLE TBCHECKLIST
ADD CONSTRAINT PK_TBCHECKLIST
PRIMARY KEY (ID);

CREATE SEQUENCE GTBCHECKLIST;

create trigger ttbchecklist_b01i for tbchecklist
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbchecklist,1);
end;

CREATE TABLE TBCHECKLIST_USUARIO (
    ID INTEGER NOT NULL,
    CHECKLIST_ID INTEGER,
    USUARIO_ID INTEGER);

ALTER TABLE TBCHECKLIST_USUARIO
ADD CONSTRAINT PK_TBCHECKLIST_USUARIO
PRIMARY KEY (ID);

CREATE SEQUENCE GTBCHECKLIST_USUARIO;

create trigger ttbchecklist_usuario_b01i for tbchecklist_usuario
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbchecklist_usuario,1);
end;

ALTER TABLE TBCHECKLIST_USUARIO
    ADD TIPO INTEGER;

COMMENT ON COLUMN TBCHECKLIST_USUARIO.TIPO IS
'0 - usuario padrao
1 - usuario administrador';

CREATE TABLE TBCHECKLIST_TAREFAS (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(100),
    STATUS INTEGER);

ALTER TABLE TBCHECKLIST_TAREFAS
ADD CONSTRAINT PK_TBCHECKLIST_TAREFAS
PRIMARY KEY (ID);

CREATE SEQUENCE GTBCHECKLIST_TAREFAS;

create trigger ttbchecklist_tarefas_b01i for tbchecklist_tarefas
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbchecklist_tarefas,1);
end;

ALTER TABLE TBCHECKLIST_TAREFAS
    ADD CHECKLIST_ID INTEGER;

CREATE TABLE TBCHECKLIST_REGISTRO (
    ID INTEGER NOT NULL,
    DATA TIMESTAMP DEFAULT current_timestamp,
    CHECKLIST_ID INTEGER,
    USUARIO_ID INTEGER,
    OBSERVACAO BLOB SUB_TYPE 1 SEGMENT SIZE 80);

ALTER TABLE TBCHECKLIST_REGISTRO
ADD CONSTRAINT PK_TBCHECKLIST_REGISTRO
PRIMARY KEY (ID);

CREATE SEQUENCE GTBCHECKLIST_REGISTRO;

create trigger ttbchecklist_registro_b01i for tbchecklist_registro
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbchecklist_registro,1);
end;

CREATE TABLE TBCHECKLIST_REGISTRO_ITEM (
    ID INTEGER NOT NULL,
    TAREFA_ID INTEGER,
    REGISTRO_ID INTEGER,
    STATUS INTEGER,
    OBSERVACAO BLOB SUB_TYPE 1 SEGMENT SIZE 80);

ALTER TABLE TBCHECKLIST_REGISTRO_ITEM
ADD CONSTRAINT PK_TBCHECKLIST_REGISTRO_ITEM
PRIMARY KEY (ID);

COMMENT ON COLUMN TBCHECKLIST_REGISTRO_ITEM.STATUS IS
'0 - pendente
1 - em execucao
2 - finalizado
3 - parado
4 - nao executado';

CREATE SEQUENCE GTBCHECKLIST_REGISTRO_ITEM;

create trigger ttbchecklist_reg_item_b01i for tbchecklist_registro_item
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbchecklist_registro_item,1);
end;

CREATE GENERATOR GTBCHECKLIST_HORARIOS;

CREATE TABLE TBCHECKLIST_HORARIOS (
    ID            INTEGER NOT NULL,
    HORA          TIME,
    CHECKLIST_ID  INTEGER,
    STATUS        INTEGER,
    DESCRICAO     VARCHAR(50)
);

ALTER TABLE TBCHECKLIST_HORARIOS ADD CONSTRAINT PK_TBCHECKLIST_HORARIOS PRIMARY KEY (ID);

CREATE OR ALTER TRIGGER TTBCHECKLIST_HORARIOS_B01I FOR TBCHECKLIST_HORARIOS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbchecklist_horarios,1);
end;



