----------------------------------------------------------------------
-- --
--
-- Table: basconfig
-- Purpose: configuracoes do sistema
--
-- --
----------------------------------------------------------------------

CREATE TABLE "basconfig" 
(
    "moduleconfig"    text, --Modulo do parametro
    "parameter"       text, --Parametro
    "value"           text, --Valor
    "description"     text, --Descricao do parametro
    "type"            varchar(50), --Tipo do parametro
    "groupby"         varchar(50), --Agrupa os parametros para uma tela de pesquisa mais amigavel
    "orderby"         int4, --Ordem em que a preferancia ira aparecer dentro do grupo. O numero mais baixo vem antes
    "label"           varchar(50) --Rotulo do campo que sera exibido na interface de preferencias
);

COMMENT ON TABLE "basconfig" IS 'configuracoes do sistema';
COMMENT ON COLUMN "basconfig"."moduleconfig" IS 'Modulo do parametro';
COMMENT ON COLUMN "basconfig"."parameter" IS 'Parametro';
COMMENT ON COLUMN "basconfig"."value" IS 'Valor';
COMMENT ON COLUMN "basconfig"."description" IS 'Descricao do parametro';
COMMENT ON COLUMN "basconfig"."type" IS 'Tipo do parametro';
COMMENT ON COLUMN "basconfig"."groupby" IS 'Agrupa os parametros para uma tela de pesquisa mais amigavel';
COMMENT ON COLUMN "basconfig"."orderby" IS 'Ordem em que a preferancia ira aparecer dentro do grupo. O numero mais baixo vem antes';
COMMENT ON COLUMN "basconfig"."label" IS 'Rotulo do campo que sera exibido na interface de preferencias';

ALTER TABLE "basconfig" ALTER COLUMN "moduleconfig" SET NOT NULL;
ALTER TABLE "basconfig" ALTER COLUMN "parameter" SET NOT NULL;
ALTER TABLE "basconfig" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "basconfig" ALTER COLUMN "type" SET NOT NULL;

ALTER TABLE "basconfig" ALTER COLUMN "moduleconfig" SET NOT NULL;
ALTER TABLE "basconfig" ALTER COLUMN "parameter" SET NOT NULL;
ALTER TABLE "basconfig" ADD PRIMARY KEY ("moduleconfig","parameter");

----------------------------------------------------------------------
-- --
--
-- Table: gtclabellayout
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclabellayout" 
(
    "labellayoutid"        int4,
    "description"          varchar,
    "topmargin"            float8,
    "leftmargin"           float8,
    "verticalspacing"      float8,
    "horizontalspacing"    float8,
    "height"               float8,
    "width"                float8,
    "lines"                float8,
    "columns"              float8,
    "pageformat"           varchar
);

CREATE SEQUENCE "seq_labellayoutid";
ALTER TABLE "gtclabellayout" ALTER COLUMN "labellayoutid" SET DEFAULT NEXTVAL('"seq_labellayoutid"');

ALTER TABLE "gtclabellayout" ALTER COLUMN "labellayoutid" SET NOT NULL;
ALTER TABLE "gtclabellayout" ADD PRIMARY KEY ("labellayoutid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcformatbackofbook
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcformatbackofbook" 
(
    "formatbackofbookid"    int4,
    "description"           varchar(40),
    "format"                text,
    "internalformat"        text
);

CREATE SEQUENCE "seq_formatbackofbookid";
ALTER TABLE "gtcformatbackofbook" ALTER COLUMN "formatbackofbookid" SET DEFAULT NEXTVAL('"seq_formatbackofbookid"');
ALTER TABLE "gtcformatbackofbook" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "gtcformatbackofbook" ALTER COLUMN "format" SET NOT NULL;

ALTER TABLE "gtcformatbackofbook" ALTER COLUMN "formatbackofbookid" SET NOT NULL;
ALTER TABLE "gtcformatbackofbook" ADD PRIMARY KEY ("formatbackofbookid");

----------------------------------------------------------------------
-- --
--
-- Table: gtccutter
-- Purpose: tabela cutter
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtccutter" 
(
    "abbreviation"    varchar,
    "code"            int4
);

COMMENT ON TABLE "gtccutter" IS 'tabela cutter';

ALTER TABLE "gtccutter" ALTER COLUMN "abbreviation" SET NOT NULL;
ALTER TABLE "gtccutter" ALTER COLUMN "code" SET NOT NULL;

----------------------------------------------------------------------
-- --
--
-- Table: gtcz3950servers
-- Purpose: armazena o servidores z3950
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcz3950servers" 
(
    "serverid"       integer,
    "description"    varchar(255),
    "host"           varchar(255),
    "recordtype"     varchar(100),
    "sintax"         varchar(50),
    "username"       varchar(50),
    "password"       varchar(32),
    "country"        varchar(100)
);

COMMENT ON TABLE "gtcz3950servers" IS 'armazena o servidores z3950';

CREATE SEQUENCE "seq_serverid";
ALTER TABLE "gtcz3950servers" ALTER COLUMN "serverid" SET DEFAULT NEXTVAL('"seq_serverid"');
ALTER TABLE "gtcz3950servers" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "gtcz3950servers" ALTER COLUMN "host" SET NOT NULL;
ALTER TABLE "gtcz3950servers" ALTER COLUMN "recordtype" SET NOT NULL;

ALTER TABLE "gtcz3950servers" ALTER COLUMN "serverid" SET NOT NULL;
ALTER TABLE "gtcz3950servers" ADD PRIMARY KEY ("serverid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcdomain
-- Purpose: armazena os dominios do sistema
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcdomain" 
(
    "domainid"       varchar(100),
    "sequence"       integer,
    "key"            varchar(100),
    "abbreviated"    varchar(100),
    "label"          varchar(255)
);

COMMENT ON TABLE "gtcdomain" IS 'armazena os dominios do sistema';

ALTER TABLE "gtcdomain" ALTER COLUMN "domainid" SET NOT NULL;
ALTER TABLE "gtcdomain" ALTER COLUMN "sequence" SET NOT NULL;
ALTER TABLE "gtcdomain" ALTER COLUMN "key" SET NOT NULL;
ALTER TABLE "gtcdomain" ALTER COLUMN "abbreviated" SET NOT NULL;
ALTER TABLE "gtcdomain" ALTER COLUMN "label" SET NOT NULL;

ALTER TABLE "gtcdomain" ALTER COLUMN "domainid" SET NOT NULL;
ALTER TABLE "gtcdomain" ALTER COLUMN "sequence" SET NOT NULL;
ALTER TABLE "gtcdomain" ADD PRIMARY KEY ("domainid","sequence");

----------------------------------------------------------------------
-- --
--
-- Table: gtcprivilegegroup
-- Purpose: grupo de privilegio. relaciona as unidade com pol
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcprivilegegroup" 
(
    "privilegegroupid"    integer,
    "description"         varchar(50)
);

COMMENT ON TABLE "gtcprivilegegroup" IS 'grupo de privilegio. relaciona as unidade com pol';

CREATE SEQUENCE "seq_privilegegroupid";
ALTER TABLE "gtcprivilegegroup" ALTER COLUMN "privilegegroupid" SET DEFAULT NEXTVAL('"seq_privilegegroupid"');
ALTER TABLE "gtcprivilegegroup" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcprivilegegroup" ALTER COLUMN "privilegegroupid" SET NOT NULL;
ALTER TABLE "gtcprivilegegroup" ADD PRIMARY KEY ("privilegegroupid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcmarctaglisting
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcmarctaglisting" 
(
    "marctaglistingid"    varchar,
    "description"         varchar
);

CREATE SEQUENCE "seq_marctaglistingid";
ALTER TABLE "gtcmarctaglisting" ALTER COLUMN "marctaglistingid" SET DEFAULT NEXTVAL('"seq_marctaglistingid"');

ALTER TABLE "gtcmarctaglisting" ALTER COLUMN "marctaglistingid" SET NOT NULL;
ALTER TABLE "gtcmarctaglisting" ADD PRIMARY KEY ("marctaglistingid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcmaterialgender
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcmaterialgender" 
(
    "materialgenderid"    int4,
    "description"         varchar
);

CREATE SEQUENCE "seq_materialgenderid";
ALTER TABLE "gtcmaterialgender" ALTER COLUMN "materialgenderid" SET DEFAULT NEXTVAL('"seq_materialgenderid"');

ALTER TABLE "gtcmaterialgender" ALTER COLUMN "materialgenderid" SET NOT NULL;
ALTER TABLE "gtcmaterialgender" ADD PRIMARY KEY ("materialgenderid");

----------------------------------------------------------------------
-- --
--
-- Table: baslink
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "baslink" 
(
    "linkid"               int4,
    "description"          text,
    "level"                int4,
    "isvisibletoperson"    boolean
);

CREATE SEQUENCE "seq_linkid";
ALTER TABLE "baslink" ALTER COLUMN "linkid" SET DEFAULT NEXTVAL('"seq_linkid"');

ALTER TABLE "baslink" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "baslink" ADD PRIMARY KEY ("linkid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcoperation
-- Purpose: operacoes
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcoperation" 
(
    "operationid"    int4, --Identificador da operacao
    "description"    varchar(100), --Descricao
    "definerule"     boolean
);

COMMENT ON TABLE "gtcoperation" IS 'operacoes';
COMMENT ON COLUMN "gtcoperation"."operationid" IS 'Identificador da operacao';
COMMENT ON COLUMN "gtcoperation"."description" IS 'Descricao';

CREATE SEQUENCE "seq_operationid";
ALTER TABLE "gtcoperation" ALTER COLUMN "operationid" SET DEFAULT NEXTVAL('"seq_operationid"');
ALTER TABLE "gtcoperation" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcoperation" ALTER COLUMN "operationid" SET NOT NULL;
ALTER TABLE "gtcoperation" ADD PRIMARY KEY ("operationid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcfinestatus
-- Purpose: estado da multa
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcfinestatus" 
(
    "finestatusid"    int4, --Identificador do estado da multa
    "description"     varchar(40) --Descri
);

COMMENT ON TABLE "gtcfinestatus" IS 'estado da multa';
COMMENT ON COLUMN "gtcfinestatus"."finestatusid" IS 'Identificador do estado da multa';
COMMENT ON COLUMN "gtcfinestatus"."description" IS 'Descri';

CREATE SEQUENCE "seq_finestatusid";
ALTER TABLE "gtcfinestatus" ALTER COLUMN "finestatusid" SET DEFAULT NEXTVAL('"seq_finestatusid"');
ALTER TABLE "gtcfinestatus" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcfinestatus" ALTER COLUMN "finestatusid" SET NOT NULL;
ALTER TABLE "gtcfinestatus" ADD PRIMARY KEY ("finestatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtclibrarygroup
-- Purpose: cadastra grupos para separar as unidades
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclibrarygroup" 
(
    "librarygroupid"    int4, --C
    "description"       varchar(100), --Nome do grupo
    "observation"       text --Observacoes
);

COMMENT ON TABLE "gtclibrarygroup" IS 'cadastra grupos para separar as unidades';
COMMENT ON COLUMN "gtclibrarygroup"."librarygroupid" IS 'C';
COMMENT ON COLUMN "gtclibrarygroup"."description" IS 'Nome do grupo';
COMMENT ON COLUMN "gtclibrarygroup"."observation" IS 'Observacoes';

CREATE SEQUENCE "seq_librarygroupid";
ALTER TABLE "gtclibrarygroup" ALTER COLUMN "librarygroupid" SET DEFAULT NEXTVAL('"seq_librarygroupid"');
ALTER TABLE "gtclibrarygroup" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtclibrarygroup" ALTER COLUMN "librarygroupid" SET NOT NULL;
ALTER TABLE "gtclibrarygroup" ADD PRIMARY KEY ("librarygroupid");

----------------------------------------------------------------------
-- --
--
-- Table: basperson
-- Purpose: pessoas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "basperson" 
(
    "personid"            int4, --Codigo da pessoa
    "name"                varchar(100), --Nome
    "city"                varchar(100), --Codigo da cidade
    "zipcode"             varchar(9), --CEP
    "location"            varchar(100), --Logradouro
    "number"              varchar(10), --Numero
    "complement"          varchar(60), --Complemento
    "neighborhood"        text, --Bairro
    "email"               varchar(60), --Email
    "password"            varchar(10), --Senha para acesso aos processo on-line
    "operationprocess"    timestamp --Data e hora que o usuario entrou em um processo de operacao e deve ser bolqueado. Se o campo for nulo o usuario nao est
);

COMMENT ON TABLE "basperson" IS 'pessoas';
COMMENT ON COLUMN "basperson"."personid" IS 'Codigo da pessoa';
COMMENT ON COLUMN "basperson"."name" IS 'Nome';
COMMENT ON COLUMN "basperson"."city" IS 'Codigo da cidade';
COMMENT ON COLUMN "basperson"."zipcode" IS 'CEP';
COMMENT ON COLUMN "basperson"."location" IS 'Logradouro';
COMMENT ON COLUMN "basperson"."number" IS 'Numero';
COMMENT ON COLUMN "basperson"."complement" IS 'Complemento';
COMMENT ON COLUMN "basperson"."neighborhood" IS 'Bairro';
COMMENT ON COLUMN "basperson"."email" IS 'Email';
COMMENT ON COLUMN "basperson"."password" IS 'Senha para acesso aos processo on-line';
COMMENT ON COLUMN "basperson"."operationprocess" IS 'Data e hora que o usuario entrou em um processo de operacao e deve ser bolqueado. Se o campo for nulo o usuario nao est';

CREATE SEQUENCE "seq_personid";
ALTER TABLE "basperson" ALTER COLUMN "personid" SET DEFAULT NEXTVAL('"seq_personid"');
ALTER TABLE "basperson" ALTER COLUMN "name" SET NOT NULL;

ALTER TABLE "basperson" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "basperson" ADD PRIMARY KEY ("personid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcassociation
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcassociation" 
(
    "associationid"    int4,
    "description"      varchar
);

CREATE SEQUENCE "seq_associationid";
ALTER TABLE "gtcassociation" ALTER COLUMN "associationid" SET DEFAULT NEXTVAL('"seq_associationid"');

ALTER TABLE "gtcassociation" ALTER COLUMN "associationid" SET NOT NULL;
ALTER TABLE "gtcassociation" ADD PRIMARY KEY ("associationid");

----------------------------------------------------------------------
-- --
--
-- Table: gtclocationformaterialmovement
-- Purpose: local para circula
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclocationformaterialmovement" 
(
    "locationformaterialmovementid"    int4, --Identificador do local para circula
    "description"                      varchar(40), --Descri
    "observation"                      text,
    "sendloanreceiptbyemail"           boolean,
    "sendrenewreceiptbyemail"          boolean,
    "sendreturnreceiptbyemail"         boolean
);

COMMENT ON TABLE "gtclocationformaterialmovement" IS 'local para circula';
COMMENT ON COLUMN "gtclocationformaterialmovement"."locationformaterialmovementid" IS 'Identificador do local para circula';
COMMENT ON COLUMN "gtclocationformaterialmovement"."description" IS 'Descri';

CREATE SEQUENCE "seq_locationformaterialmovementid";
ALTER TABLE "gtclocationformaterialmovement" ALTER COLUMN "locationformaterialmovementid" SET DEFAULT NEXTVAL('"seq_locationformaterialmovementid"');
ALTER TABLE "gtclocationformaterialmovement" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtclocationformaterialmovement" ALTER COLUMN "locationformaterialmovementid" SET NOT NULL;
ALTER TABLE "gtclocationformaterialmovement" ADD PRIMARY KEY ("locationformaterialmovementid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcexemplarystatus
-- Purpose: estado dos exemplares
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcexemplarystatus" 
(
    "exemplarystatusid"                 int4, --Identificador do status
    "description"                       varchar(40), --descri
    "mask"                              varchar(40),
    "level"                             int4, --Nivel definido como: 1 - Inicial e 2 - Transicao
    "executeloan"                       boolean, --Define se neste estado o material podera ser emprestado.
    "momentaryloan"                     boolean, --Se permite que o estado seja emprestado para o Xerox, etc.
    "daysofmomentaryloan"               int4,
    "executereserve"                    boolean,
    "executereserveininitiallevel"      boolean,
    "meetreserve"                       boolean, --Se for true e um livro for devolvido para este estado ele executara o processo de atender reserva
    "isreservestatus"                   boolean,
    "islowstatus"                       boolean,
    "observation"                       text,
    "schedulechangestatusforrequest"    boolean
);

COMMENT ON TABLE "gtcexemplarystatus" IS 'estado dos exemplares';
COMMENT ON COLUMN "gtcexemplarystatus"."exemplarystatusid" IS 'Identificador do status';
COMMENT ON COLUMN "gtcexemplarystatus"."description" IS 'descri';
COMMENT ON COLUMN "gtcexemplarystatus"."level" IS 'Nivel definido como: 1 - Inicial e 2 - Transicao';
COMMENT ON COLUMN "gtcexemplarystatus"."executeloan" IS 'Define se neste estado o material podera ser emprestado.';
COMMENT ON COLUMN "gtcexemplarystatus"."momentaryloan" IS 'Se permite que o estado seja emprestado para o Xerox, etc.';
COMMENT ON COLUMN "gtcexemplarystatus"."meetreserve" IS 'Se for true e um livro for devolvido para este estado ele executara o processo de atender reserva';

CREATE SEQUENCE "seq_exemplarystatusid";
ALTER TABLE "gtcexemplarystatus" ALTER COLUMN "exemplarystatusid" SET DEFAULT NEXTVAL('"seq_exemplarystatusid"');
ALTER TABLE "gtcexemplarystatus" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcexemplarystatus" ALTER COLUMN "exemplarystatusid" SET NOT NULL;
ALTER TABLE "gtcexemplarystatus" ADD PRIMARY KEY ("exemplarystatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcloantype
-- Purpose: identifica tipos de empr
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcloantype" 
(
    "loantypeid"     int4, --Identificador do tipo de emprestimo
    "description"    varchar(40) --Descri
);

COMMENT ON TABLE "gtcloantype" IS 'identifica tipos de empr';
COMMENT ON COLUMN "gtcloantype"."loantypeid" IS 'Identificador do tipo de emprestimo';
COMMENT ON COLUMN "gtcloantype"."description" IS 'Descri';

CREATE SEQUENCE "seq_loantypeid";
ALTER TABLE "gtcloantype" ALTER COLUMN "loantypeid" SET DEFAULT NEXTVAL('"seq_loantypeid"');
ALTER TABLE "gtcloantype" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcloantype" ALTER COLUMN "loantypeid" SET NOT NULL;
ALTER TABLE "gtcloantype" ADD PRIMARY KEY ("loantypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrenewtype
-- Purpose: identifica tipos de renovacao.
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrenewtype" 
(
    "renewtypeid"    int4, --Identificador do tipo de renovaca
    "description"    varchar(40) --Descri
);

COMMENT ON TABLE "gtcrenewtype" IS 'identifica tipos de renovacao.';
COMMENT ON COLUMN "gtcrenewtype"."renewtypeid" IS 'Identificador do tipo de renovaca';
COMMENT ON COLUMN "gtcrenewtype"."description" IS 'Descri';

CREATE SEQUENCE "seq_renewtypeid";
ALTER TABLE "gtcrenewtype" ALTER COLUMN "renewtypeid" SET DEFAULT NEXTVAL('"seq_renewtypeid"');
ALTER TABLE "gtcrenewtype" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcrenewtype" ALTER COLUMN "renewtypeid" SET NOT NULL;
ALTER TABLE "gtcrenewtype" ADD PRIMARY KEY ("renewtypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreservestatus
-- Purpose: estado da reserva
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreservestatus" 
(
    "reservestatusid"    int4, --Identificador da situa
    "description"        varchar(40) --Descri
);

COMMENT ON TABLE "gtcreservestatus" IS 'estado da reserva';
COMMENT ON COLUMN "gtcreservestatus"."reservestatusid" IS 'Identificador da situa';
COMMENT ON COLUMN "gtcreservestatus"."description" IS 'Descri';

CREATE SEQUENCE "seq_reservestatusid";
ALTER TABLE "gtcreservestatus" ALTER COLUMN "reservestatusid" SET DEFAULT NEXTVAL('"seq_reservestatusid"');
ALTER TABLE "gtcreservestatus" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcreservestatus" ALTER COLUMN "reservestatusid" SET NOT NULL;
ALTER TABLE "gtcreservestatus" ADD PRIMARY KEY ("reservestatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreservetype
-- Purpose: identifica tipos de reserva.
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreservetype" 
(
    "reservetypeid"    int4, --Identificador do tipo de reserva
    "description"      varchar(40) --Descri
);

COMMENT ON TABLE "gtcreservetype" IS 'identifica tipos de reserva.';
COMMENT ON COLUMN "gtcreservetype"."reservetypeid" IS 'Identificador do tipo de reserva';
COMMENT ON COLUMN "gtcreservetype"."description" IS 'Descri';

CREATE SEQUENCE "seq_reservetypeid";
ALTER TABLE "gtcreservetype" ALTER COLUMN "reservetypeid" SET DEFAULT NEXTVAL('"seq_reservetypeid"');
ALTER TABLE "gtcreservetype" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcreservetype" ALTER COLUMN "reservetypeid" SET NOT NULL;
ALTER TABLE "gtcreservetype" ADD PRIMARY KEY ("reservetypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcweekday
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcweekday" 
(
    "weekdayid"      int4,
    "description"    varchar(40)
);

CREATE SEQUENCE "seq_weekdayid";
ALTER TABLE "gtcweekday" ALTER COLUMN "weekdayid" SET DEFAULT NEXTVAL('"seq_weekdayid"');
ALTER TABLE "gtcweekday" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcweekday" ALTER COLUMN "weekdayid" SET NOT NULL;
ALTER TABLE "gtcweekday" ADD PRIMARY KEY ("weekdayid");

----------------------------------------------------------------------
-- --
--
-- Table: gtctag
-- Purpose: etiquetas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtctag" 
(
    "fieldid"            varchar(3), --Campo marc
    "subfieldid"         varchar(1), --Subcampo marc
    "description"        varchar(100),
    "observation"        text,
    "isrepetitive"       boolean,
    "hassubfield"        boolean,
    "isactive"           boolean,
    "indemonstration"    boolean,
    "isobsolete"         boolean,
    "help"               text
);

COMMENT ON TABLE "gtctag" IS 'etiquetas';
COMMENT ON COLUMN "gtctag"."fieldid" IS 'Campo marc';
COMMENT ON COLUMN "gtctag"."subfieldid" IS 'Subcampo marc';

ALTER TABLE "gtctag" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtctag" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtctag" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtctag" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtctag" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtctag" ADD PRIMARY KEY ("fieldid","subfieldid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcspreadsheet
-- Purpose: armazenam o modelo das planilhas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcspreadsheet" 
(
    "category"               varchar(2),
    "level"                  varchar(1), --Nivel de catalogacao
    "field"                  text,
    "required"               text, --Campo obrigatorios
    "repeatfieldrequired"    text,
    "defaultvalue"           text, --Valor padrao de um campo marc
    "menuname"               varchar,
    "menuoption"             varchar,
    "menulevel"              int4
);

COMMENT ON TABLE "gtcspreadsheet" IS 'armazenam o modelo das planilhas';
COMMENT ON COLUMN "gtcspreadsheet"."level" IS 'Nivel de catalogacao';
COMMENT ON COLUMN "gtcspreadsheet"."required" IS 'Campo obrigatorios';
COMMENT ON COLUMN "gtcspreadsheet"."defaultvalue" IS 'Valor padrao de um campo marc';

ALTER TABLE "gtcspreadsheet" ALTER COLUMN "category" SET NOT NULL;
ALTER TABLE "gtcspreadsheet" ALTER COLUMN "level" SET NOT NULL;
ALTER TABLE "gtcspreadsheet" ALTER COLUMN "field" SET NOT NULL;

ALTER TABLE "gtcspreadsheet" ALTER COLUMN "category" SET NOT NULL;
ALTER TABLE "gtcspreadsheet" ALTER COLUMN "level" SET NOT NULL;
ALTER TABLE "gtcspreadsheet" ADD PRIMARY KEY ("category","level");

----------------------------------------------------------------------
-- --
--
-- Table: gtcclassificationarea
-- Purpose: area de classificacao
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcclassificationarea" 
(
    "classificationareaid"    integer, --Identificacao da classifica
    "areaname"                varchar(50), --Nome da area de classificacao
    "classification"          text, --Classificacoess separadas por virgula e com percente para caracter de truncamento
    "ignoreclassification"    text --Ignorar classificacoes separadas por virgula e com percente para caracter de truncamento
);

COMMENT ON TABLE "gtcclassificationarea" IS 'area de classificacao';
COMMENT ON COLUMN "gtcclassificationarea"."classificationareaid" IS 'Identificacao da classifica';
COMMENT ON COLUMN "gtcclassificationarea"."areaname" IS 'Nome da area de classificacao';
COMMENT ON COLUMN "gtcclassificationarea"."classification" IS 'Classificacoess separadas por virgula e com percente para caracter de truncamento';
COMMENT ON COLUMN "gtcclassificationarea"."ignoreclassification" IS 'Ignorar classificacoes separadas por virgula e com percente para caracter de truncamento';

CREATE SEQUENCE "seq_classificationareaid";
ALTER TABLE "gtcclassificationarea" ALTER COLUMN "classificationareaid" SET DEFAULT NEXTVAL('"seq_classificationareaid"');
ALTER TABLE "gtcclassificationarea" ALTER COLUMN "areaname" SET NOT NULL;

ALTER TABLE "gtcclassificationarea" ALTER COLUMN "classificationareaid" SET NOT NULL;
ALTER TABLE "gtcclassificationarea" ADD PRIMARY KEY ("classificationareaid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsearchformat
-- Purpose: formato da pesquisa
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsearchformat" 
(
    "searchformatid"    int4,
    "description"       varchar,
    "isrestricted"      boolean
);

COMMENT ON TABLE "gtcsearchformat" IS 'formato da pesquisa';

CREATE SEQUENCE "seq_searchformatid";
ALTER TABLE "gtcsearchformat" ALTER COLUMN "searchformatid" SET DEFAULT NEXTVAL('"seq_searchformatid"');
ALTER TABLE "gtcsearchformat" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcsearchformat" ALTER COLUMN "searchformatid" SET NOT NULL;
ALTER TABLE "gtcsearchformat" ADD PRIMARY KEY ("searchformatid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsearchablefield
-- Purpose: campos pesquisaveis
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsearchablefield" 
(
    "searchablefieldid"    int4,
    "description"          varchar,
    "field"                varchar,
    "identifier"           varchar,
    "fieldtype"            int4, --1 - Numerico, 2 - String
    "isrestricted"         boolean,
    "level"                int4,
    "observation"          text,
    "helps"                varchar
);

COMMENT ON TABLE "gtcsearchablefield" IS 'campos pesquisaveis';
COMMENT ON COLUMN "gtcsearchablefield"."fieldtype" IS '1 - Numerico, 2 - String';

CREATE SEQUENCE "seq_searchablefieldid";
ALTER TABLE "gtcsearchablefield" ALTER COLUMN "searchablefieldid" SET DEFAULT NEXTVAL('"seq_searchablefieldid"');
ALTER TABLE "gtcsearchablefield" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "gtcsearchablefield" ALTER COLUMN "field" SET NOT NULL;
ALTER TABLE "gtcsearchablefield" ALTER COLUMN "identifier" SET NOT NULL;

ALTER TABLE "gtcsearchablefield" ALTER COLUMN "searchablefieldid" SET NOT NULL;
ALTER TABLE "gtcsearchablefield" ADD PRIMARY KEY ("searchablefieldid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcdictionary
-- Purpose: dicion
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcdictionary" 
(
    "dictionaryid"    int4,
    "description"     varchar,
    "tags"            text,
    "readonly"        boolean
);

COMMENT ON TABLE "gtcdictionary" IS 'dicion';

CREATE SEQUENCE "seq_dictionaryid";
ALTER TABLE "gtcdictionary" ALTER COLUMN "dictionaryid" SET DEFAULT NEXTVAL('"seq_dictionaryid"');
ALTER TABLE "gtcdictionary" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcdictionary" ALTER COLUMN "dictionaryid" SET NOT NULL;
ALTER TABLE "gtcdictionary" ADD PRIMARY KEY ("dictionaryid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcloanbetweenlibrarystatus
-- Purpose: estado dos empr
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcloanbetweenlibrarystatus" 
(
    "loanbetweenlibrarystatusid"    int4,
    "description"                   varchar(40) --Descri
);

COMMENT ON TABLE "gtcloanbetweenlibrarystatus" IS 'estado dos empr';
COMMENT ON COLUMN "gtcloanbetweenlibrarystatus"."description" IS 'Descri';

CREATE SEQUENCE "seq_loanbetweenlibrarystatusid";
ALTER TABLE "gtcloanbetweenlibrarystatus" ALTER COLUMN "loanbetweenlibrarystatusid" SET DEFAULT NEXTVAL('"seq_loanbetweenlibrarystatusid"');
ALTER TABLE "gtcloanbetweenlibrarystatus" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcloanbetweenlibrarystatus" ALTER COLUMN "loanbetweenlibrarystatusid" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrarystatus" ADD PRIMARY KEY ("loanbetweenlibrarystatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcfile
-- Purpose: armazena os arquivos
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcfile" 
(
    "fileid"     integer,
    "content"    oid,
    "name"       varchar,
    "type"       varchar,
    "size"       int4,
    "path"       varchar
);

COMMENT ON TABLE "gtcfile" IS 'armazena os arquivos';

CREATE SEQUENCE "seq_fileid";
ALTER TABLE "gtcfile" ALTER COLUMN "fileid" SET DEFAULT NEXTVAL('"seq_fileid"');

ALTER TABLE "gtcfile" ALTER COLUMN "fileid" SET NOT NULL;
ALTER TABLE "gtcfile" ADD PRIMARY KEY ("fileid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreport
-- Purpose: relatorios
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreport" 
(
    "reportid"        int4,
    "title"           varchar,
    "description"     text,
    "permission"      varchar,
    "reportsql"       text,
    "reportsubsql"    text,
    "script"          text,
    "model"           varchar,
    "isactive"        boolean,
    "reportgroup"     varchar
);

COMMENT ON TABLE "gtcreport" IS 'relatorios';

CREATE SEQUENCE "seq_reportid";
ALTER TABLE "gtcreport" ALTER COLUMN "reportid" SET DEFAULT NEXTVAL('"seq_reportid"');
ALTER TABLE "gtcreport" ALTER COLUMN "title" SET NOT NULL;
ALTER TABLE "gtcreport" ALTER COLUMN "permission" SET NOT NULL;
ALTER TABLE "gtcreport" ALTER COLUMN "isactive" SET NOT NULL;

ALTER TABLE "gtcreport" ALTER COLUMN "reportid" SET NOT NULL;
ALTER TABLE "gtcreport" ADD PRIMARY KEY ("reportid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcmaterialtype
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcmaterialtype" 
(
    "materialtypeid"    int4,
    "description"       varchar,
    "isrestricted"      boolean,
    "level"             int4,
    "observation"       text
);

CREATE SEQUENCE "seq_materialtypeid";
ALTER TABLE "gtcmaterialtype" ALTER COLUMN "materialtypeid" SET DEFAULT NEXTVAL('"seq_materialtypeid"');
ALTER TABLE "gtcmaterialtype" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "gtcmaterialtype" ALTER COLUMN "isrestricted" SET NOT NULL;

ALTER TABLE "gtcmaterialtype" ALTER COLUMN "materialtypeid" SET NOT NULL;
ALTER TABLE "gtcmaterialtype" ADD PRIMARY KEY ("materialtypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcmaterialphysicaltype
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcmaterialphysicaltype" 
(
    "materialphysicaltypeid"    int4,
    "description"               varchar,
    "image"                     text,
    "observation"               text
);

CREATE SEQUENCE "seq_materialphysicaltypeid";
ALTER TABLE "gtcmaterialphysicaltype" ALTER COLUMN "materialphysicaltypeid" SET DEFAULT NEXTVAL('"seq_materialphysicaltypeid"');
ALTER TABLE "gtcmaterialphysicaltype" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcmaterialphysicaltype" ALTER COLUMN "materialphysicaltypeid" SET NOT NULL;
ALTER TABLE "gtcmaterialphysicaltype" ADD PRIMARY KEY ("materialphysicaltypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcinterchangetype
-- Purpose: estado da reserva
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcinterchangetype" 
(
    "interchangetypeid"    int4, --Identificador da situa
    "description"          varchar(40) --Descri
);

COMMENT ON TABLE "gtcinterchangetype" IS 'estado da reserva';
COMMENT ON COLUMN "gtcinterchangetype"."interchangetypeid" IS 'Identificador da situa';
COMMENT ON COLUMN "gtcinterchangetype"."description" IS 'Descri';

CREATE SEQUENCE "seq_interchangetypeid";
ALTER TABLE "gtcinterchangetype" ALTER COLUMN "interchangetypeid" SET DEFAULT NEXTVAL('"seq_interchangetypeid"');
ALTER TABLE "gtcinterchangetype" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcinterchangetype" ALTER COLUMN "interchangetypeid" SET NOT NULL;
ALTER TABLE "gtcinterchangetype" ADD PRIMARY KEY ("interchangetypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtccataloguingformat
-- Purpose: tabela para separadores
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtccataloguingformat" 
(
    "cataloguingformatid"    int4,
    "description"            varchar,
    "observation"            varchar
);

COMMENT ON TABLE "gtccataloguingformat" IS 'tabela para separadores';

CREATE SEQUENCE "seq_cataloguingformatid";
ALTER TABLE "gtccataloguingformat" ALTER COLUMN "cataloguingformatid" SET DEFAULT NEXTVAL('"seq_cataloguingformatid"');
ALTER TABLE "gtccataloguingformat" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtccataloguingformat" ALTER COLUMN "cataloguingformatid" SET NOT NULL;
ALTER TABLE "gtccataloguingformat" ADD PRIMARY KEY ("cataloguingformatid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcnews
-- Purpose: noticia
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcnews" 
(
    "newsid"          int4, --Identifica
    "place"           int4, --1 - Minha biblioteca, 2 - Tela inicial, 3 - Pesquisa
    "title1"          varchar,
    "news"            text,
    "date"            timestamp,
    "begindate"       timestamp,
    "enddate"         timestamp,
    "isrestricted"    boolean,
    "isactive"        boolean,
    "operator"        varchar(30)
);

COMMENT ON TABLE "gtcnews" IS 'noticia';
COMMENT ON COLUMN "gtcnews"."newsid" IS 'Identifica';
COMMENT ON COLUMN "gtcnews"."place" IS '1 - Minha biblioteca, 2 - Tela inicial, 3 - Pesquisa';

CREATE SEQUENCE "seq_newsid";
ALTER TABLE "gtcnews" ALTER COLUMN "newsid" SET DEFAULT NEXTVAL('"seq_newsid"');
ALTER TABLE "gtcnews" ALTER COLUMN "place" SET NOT NULL;
ALTER TABLE "gtcnews" ALTER COLUMN "date" SET NOT NULL;

ALTER TABLE "gtcnews" ALTER COLUMN "newsid" SET NOT NULL;
ALTER TABLE "gtcnews" ADD PRIMARY KEY ("newsid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrequestchangeexemplarystatusstatus
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrequestchangeexemplarystatusstatus" 
(
    "requestchangeexemplarystatusstatusid"    int4,
    "description"                             varchar(40)
);

CREATE SEQUENCE "seq_requestchangeexemplarystatusstatusid";
ALTER TABLE "gtcrequestchangeexemplarystatusstatus" ALTER COLUMN "requestchangeexemplarystatusstatusid" SET DEFAULT NEXTVAL('"seq_requestchangeexemplarystatusstatusid"');
ALTER TABLE "gtcrequestchangeexemplarystatusstatus" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcrequestchangeexemplarystatusstatus" ALTER COLUMN "requestchangeexemplarystatusstatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatusstatus" ADD PRIMARY KEY ("requestchangeexemplarystatusstatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcformcontenttype
-- Purpose: identifica tipos de empr
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcformcontenttype" 
(
    "formcontenttypeid"    int4, --Identificador do tipo de emprestimo
    "description"          varchar(40) --Descri
);

COMMENT ON TABLE "gtcformcontenttype" IS 'identifica tipos de empr';
COMMENT ON COLUMN "gtcformcontenttype"."formcontenttypeid" IS 'Identificador do tipo de emprestimo';
COMMENT ON COLUMN "gtcformcontenttype"."description" IS 'Descri';

CREATE SEQUENCE "seq_formcontenttypeid";
ALTER TABLE "gtcformcontenttype" ALTER COLUMN "formcontenttypeid" SET DEFAULT NEXTVAL('"seq_formcontenttypeid"');
ALTER TABLE "gtcformcontenttype" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcformcontenttype" ALTER COLUMN "formcontenttypeid" SET NOT NULL;
ALTER TABLE "gtcformcontenttype" ADD PRIMARY KEY ("formcontenttypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsupplier
-- Purpose: fornecedor
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsupplier" 
(
    "supplierid"     int4, --Identifica
    "name"           varchar,
    "companyname"    varchar,
    "date"           timestamp
);

COMMENT ON TABLE "gtcsupplier" IS 'fornecedor';
COMMENT ON COLUMN "gtcsupplier"."supplierid" IS 'Identifica';

CREATE SEQUENCE "seq_supplierid";
ALTER TABLE "gtcsupplier" ALTER COLUMN "supplierid" SET DEFAULT NEXTVAL('"seq_supplierid"');

ALTER TABLE "gtcsupplier" ALTER COLUMN "supplierid" SET NOT NULL;
ALTER TABLE "gtcsupplier" ADD PRIMARY KEY ("supplierid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreturntype
-- Purpose: define os tipos de devolu
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreturntype" 
(
    "returntypeid"    int4,
    "description"     varchar(250)
);

COMMENT ON TABLE "gtcreturntype" IS 'define os tipos de devolu';

CREATE SEQUENCE "seq_returntypeid";
ALTER TABLE "gtcreturntype" ALTER COLUMN "returntypeid" SET DEFAULT NEXTVAL('"seq_returntypeid"');
ALTER TABLE "gtcreturntype" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcreturntype" ALTER COLUMN "returntypeid" SET NOT NULL;
ALTER TABLE "gtcreturntype" ADD PRIMARY KEY ("returntypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcschedulecycle
-- Purpose: ciclos de agendamento
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcschedulecycle" 
(
    "schedulecycleid"    integer,
    "description"        varchar,
    "valuetype"          varchar
);

COMMENT ON TABLE "gtcschedulecycle" IS 'ciclos de agendamento';

ALTER TABLE "gtcschedulecycle" ALTER COLUMN "schedulecycleid" SET NOT NULL;
ALTER TABLE "gtcschedulecycle" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "gtcschedulecycle" ALTER COLUMN "valuetype" SET NOT NULL;

ALTER TABLE "gtcschedulecycle" ALTER COLUMN "schedulecycleid" SET NOT NULL;
ALTER TABLE "gtcschedulecycle" ADD PRIMARY KEY ("schedulecycleid");

----------------------------------------------------------------------
-- --
--
-- Table: gtctask
-- Purpose: tarefas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtctask" 
(
    "taskid"         integer,
    "description"    varchar,
    "parameters"     varchar,
    "enable"         boolean,
    "scriptname"     varchar
);

COMMENT ON TABLE "gtctask" IS 'tarefas';

ALTER TABLE "gtctask" ALTER COLUMN "taskid" SET NOT NULL;
ALTER TABLE "gtctask" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "gtctask" ALTER COLUMN "enable" SET DEFAULT true;
ALTER TABLE "gtctask" ALTER COLUMN "scriptname" SET NOT NULL;

ALTER TABLE "gtctask" ALTER COLUMN "taskid" SET NOT NULL;
ALTER TABLE "gtctask" ADD PRIMARY KEY ("taskid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsoapclient
-- Purpose: clientes soap
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsoapclient" 
(
    "soapclientid"         integer,
    "clientdescription"    varchar,
    "ip"                   varchar,
    "password"             varchar,
    "enable"               boolean
);

COMMENT ON TABLE "gtcsoapclient" IS 'clientes soap';

ALTER TABLE "gtcsoapclient" ALTER COLUMN "soapclientid" SET NOT NULL;
ALTER TABLE "gtcsoapclient" ALTER COLUMN "ip" SET NOT NULL;
ALTER TABLE "gtcsoapclient" ALTER COLUMN "password" SET NOT NULL;
ALTER TABLE "gtcsoapclient" ALTER COLUMN "enable" SET DEFAULT true;

ALTER TABLE "gtcsoapclient" ALTER COLUMN "soapclientid" SET NOT NULL;
ALTER TABLE "gtcsoapclient" ADD PRIMARY KEY ("soapclientid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcwebservice
-- Purpose: lista de servi
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcwebservice" 
(
    "webserviceid"          integer,
    "servicedescription"    text,
    "class"                 varchar,
    "method"                varchar,
    "enable"                boolean,
    "needauthentication"    boolean,
    "checkclientip"         boolean
);

COMMENT ON TABLE "gtcwebservice" IS 'lista de servi';

ALTER TABLE "gtcwebservice" ALTER COLUMN "webserviceid" SET NOT NULL;
ALTER TABLE "gtcwebservice" ALTER COLUMN "class" SET NOT NULL;
ALTER TABLE "gtcwebservice" ALTER COLUMN "method" SET NOT NULL;
ALTER TABLE "gtcwebservice" ALTER COLUMN "enable" SET DEFAULT true;
ALTER TABLE "gtcwebservice" ALTER COLUMN "needauthentication" SET DEFAULT true;
ALTER TABLE "gtcwebservice" ALTER COLUMN "checkclientip" SET DEFAULT true;

ALTER TABLE "gtcwebservice" ALTER COLUMN "webserviceid" SET NOT NULL;
ALTER TABLE "gtcwebservice" ADD PRIMARY KEY ("webserviceid");

----------------------------------------------------------------------
-- --
--
-- Table: gtclibraryunit
-- Purpose: unidades de bibliotecas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclibraryunit" 
(
    "libraryunitid"       int4, --C
    "libraryname"         varchar(100), --Nome da unidade
    "isrestricted"        boolean,
    "city"                varchar(50), --Nome da cidade, por enquanto fica com nome direto
    "zipcode"             varchar(9), --CEP
    "location"            varchar(100), --Logradouro
    "number"              varchar(10), --N
    "complement"          varchar(60), --Complemento
    "email"               varchar(60), --Email da unidade
    "url"                 varchar(60), --Site da unidade
    "librarygroupid"      int,
    "privilegegroupid"    integer,
    "observation"         text,
    "level"               int
);

COMMENT ON TABLE "gtclibraryunit" IS 'unidades de bibliotecas';
COMMENT ON COLUMN "gtclibraryunit"."libraryunitid" IS 'C';
COMMENT ON COLUMN "gtclibraryunit"."libraryname" IS 'Nome da unidade';
COMMENT ON COLUMN "gtclibraryunit"."city" IS 'Nome da cidade, por enquanto fica com nome direto';
COMMENT ON COLUMN "gtclibraryunit"."zipcode" IS 'CEP';
COMMENT ON COLUMN "gtclibraryunit"."location" IS 'Logradouro';
COMMENT ON COLUMN "gtclibraryunit"."number" IS 'N';
COMMENT ON COLUMN "gtclibraryunit"."complement" IS 'Complemento';
COMMENT ON COLUMN "gtclibraryunit"."email" IS 'Email da unidade';
COMMENT ON COLUMN "gtclibraryunit"."url" IS 'Site da unidade';

CREATE SEQUENCE "seq_libraryunitid";
ALTER TABLE "gtclibraryunit" ALTER COLUMN "libraryunitid" SET DEFAULT NEXTVAL('"seq_libraryunitid"');
ALTER TABLE "gtclibraryunit" ALTER COLUMN "libraryname" SET NOT NULL;
ALTER TABLE "gtclibraryunit" ALTER COLUMN "isrestricted" SET DEFAULT FALSE;
ALTER TABLE "gtclibraryunit" ALTER COLUMN "privilegegroupid" SET NOT NULL;
CREATE SEQUENCE "seq_level";
ALTER TABLE "gtclibraryunit" ALTER COLUMN "level" SET DEFAULT NEXTVAL('"seq_level"');

ALTER TABLE "gtclibraryunit" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtclibraryunit" ADD PRIMARY KEY ("libraryunitid");

ALTER TABLE "gtclibraryunit" ADD FOREIGN KEY ("privilegegroupid") REFERENCES "gtcprivilegegroup"("privilegegroupid");

ALTER TABLE "gtclibraryunit" ADD FOREIGN KEY ("librarygroupid") REFERENCES "gtclibrarygroup"("librarygroupid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcgeneralpolicy
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcgeneralpolicy" 
(
    "privilegegroupid"                     int4,
    "linkid"                               int4,
    "loangenerallimit"                     int4,
    "reservegenerallimit"                  int4,
    "reservegenerallimitininitiallevel"    int4
);

ALTER TABLE "gtcgeneralpolicy" ALTER COLUMN "privilegegroupid" SET NOT NULL;
ALTER TABLE "gtcgeneralpolicy" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "gtcgeneralpolicy" ADD PRIMARY KEY ("privilegegroupid","linkid");

ALTER TABLE "gtcgeneralpolicy" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

ALTER TABLE "gtcgeneralpolicy" ADD FOREIGN KEY ("privilegegroupid") REFERENCES "gtcprivilegegroup"("privilegegroupid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcmarctaglistingoption
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcmarctaglistingoption" 
(
    "marctaglistingid"    varchar,
    "option"              text,
    "description"         varchar
);

ALTER TABLE "gtcmarctaglistingoption" ADD FOREIGN KEY ("marctaglistingid") REFERENCES "gtcmarctaglisting"("marctaglistingid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcholiday
-- Purpose: especifica o feriado para uma unidade. se for null vale 
--          para todas as unidades 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcholiday" 
(
    "holidayid"        int4,
    "date"             date,
    "description"      varchar,
    "occursallyear"    bool,
    "libraryunitid"    int
);

COMMENT ON TABLE "gtcholiday" IS 'especifica o feriado para uma unidade. se for null vale para todas as unidades';

CREATE SEQUENCE "seq_holidayid";
ALTER TABLE "gtcholiday" ALTER COLUMN "holidayid" SET DEFAULT NEXTVAL('"seq_holidayid"');

ALTER TABLE "gtcholiday" ALTER COLUMN "holidayid" SET NOT NULL;
ALTER TABLE "gtcholiday" ADD PRIMARY KEY ("holidayid");

ALTER TABLE "gtcholiday" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcright
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcright" 
(
    "privilegegroupid"       int4,
    "linkid"                 int4,
    "materialgenderid"       int4,
    "operationid"            int4,
    "bug_dia2sql_ignorar"    int4
);

ALTER TABLE "gtcright" ALTER COLUMN "privilegegroupid" SET NOT NULL;
ALTER TABLE "gtcright" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "gtcright" ALTER COLUMN "materialgenderid" SET NOT NULL;
ALTER TABLE "gtcright" ALTER COLUMN "operationid" SET NOT NULL;
ALTER TABLE "gtcright" ADD PRIMARY KEY ("privilegegroupid","linkid","materialgenderid","operationid");

ALTER TABLE "gtcright" ADD FOREIGN KEY ("privilegegroupid") REFERENCES "gtcprivilegegroup"("privilegegroupid");

ALTER TABLE "gtcright" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

ALTER TABLE "gtcright" ADD FOREIGN KEY ("operationid") REFERENCES "gtcoperation"("operationid");

ALTER TABLE "gtcright" ADD FOREIGN KEY ("materialgenderid") REFERENCES "gtcmaterialgender"("materialgenderid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcpolicy
-- Purpose: numero maximo de reservas de nivel inicial - disponivel, 
--          congelado, etc 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcpolicy" 
(
    "privilegegroupid"                      int4,
    "linkid"                                int4,
    "materialgenderid"                      int4,
    "loandays"                              int4,
    "loandate"                              date,
    "loanlimit"                             int4,
    "renewallimit"                          int4,
    "reservelimit"                          int4,
    "daysofwaitforreserve"                  int4,
    "reservelimitininitiallevel"            int4,
    "daysofwaitforreserveininitiallevel"    int4,
    "finevalue"                             float8,
    "renewalweblimit"                       int4,
    "renewalwebbonus"                       bool,
    "additionaldaysforholidays"             int4
);

COMMENT ON TABLE "gtcpolicy" IS 'numero maximo de reservas de nivel inicial - disponivel, congelado, etc';

ALTER TABLE "gtcpolicy" ALTER COLUMN "privilegegroupid" SET NOT NULL;
ALTER TABLE "gtcpolicy" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "gtcpolicy" ALTER COLUMN "materialgenderid" SET NOT NULL;
ALTER TABLE "gtcpolicy" ADD PRIMARY KEY ("privilegegroupid","linkid","materialgenderid");

ALTER TABLE "gtcpolicy" ADD FOREIGN KEY ("privilegegroupid") REFERENCES "gtcprivilegegroup"("privilegegroupid");

ALTER TABLE "gtcpolicy" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

ALTER TABLE "gtcpolicy" ADD FOREIGN KEY ("materialgenderid") REFERENCES "gtcmaterialgender"("materialgenderid");

----------------------------------------------------------------------
-- --
--
-- Table: baspersonlink
-- Purpose: vinculos das pessoas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "baspersonlink" 
(
    "personid"        int4, --Codigo da pessoa
    "linkid"          int4, --Codigo do vinculo
    "datevalidate"    date --Data de validade
);

COMMENT ON TABLE "baspersonlink" IS 'vinculos das pessoas';
COMMENT ON COLUMN "baspersonlink"."personid" IS 'Codigo da pessoa';
COMMENT ON COLUMN "baspersonlink"."linkid" IS 'Codigo do vinculo';
COMMENT ON COLUMN "baspersonlink"."datevalidate" IS 'Data de validade';

ALTER TABLE "baspersonlink" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "baspersonlink" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "baspersonlink" ALTER COLUMN "datevalidate" SET NOT NULL;

ALTER TABLE "baspersonlink" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "baspersonlink" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "baspersonlink" ADD PRIMARY KEY ("personid","linkid");

ALTER TABLE "baspersonlink" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "baspersonlink" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

----------------------------------------------------------------------
-- --
--
-- Table: gtclibraryassociation
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclibraryassociation" 
(
    "associationid"          int4,
    "libraryunitid"          int4,
    "bug_dia2sql_ignorar"    int4
);

ALTER TABLE "gtclibraryassociation" ALTER COLUMN "associationid" SET NOT NULL;
ALTER TABLE "gtclibraryassociation" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtclibraryassociation" ADD PRIMARY KEY ("associationid","libraryunitid");

ALTER TABLE "gtclibraryassociation" ADD FOREIGN KEY ("associationid") REFERENCES "gtcassociation"("associationid");

ALTER TABLE "gtclibraryassociation" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrulesformaterialmovement
-- Purpose: regras para circulacao de material
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrulesformaterialmovement" 
(
    "currentstate"                     int4, --Estado atual
    "operationid"                      int4, --Identificador da operacao
    "locationformaterialmovementid"    int4, --Identificacao do local para circulacao do material
    "futurestate"                      int4 --Estado futuro
);

COMMENT ON TABLE "gtcrulesformaterialmovement" IS 'regras para circulacao de material';
COMMENT ON COLUMN "gtcrulesformaterialmovement"."currentstate" IS 'Estado atual';
COMMENT ON COLUMN "gtcrulesformaterialmovement"."operationid" IS 'Identificador da operacao';
COMMENT ON COLUMN "gtcrulesformaterialmovement"."locationformaterialmovementid" IS 'Identificacao do local para circulacao do material';
COMMENT ON COLUMN "gtcrulesformaterialmovement"."futurestate" IS 'Estado futuro';

ALTER TABLE "gtcrulesformaterialmovement" ALTER COLUMN "currentstate" SET NOT NULL;
ALTER TABLE "gtcrulesformaterialmovement" ALTER COLUMN "operationid" SET NOT NULL;
ALTER TABLE "gtcrulesformaterialmovement" ALTER COLUMN "locationformaterialmovementid" SET NOT NULL;
ALTER TABLE "gtcrulesformaterialmovement" ALTER COLUMN "futurestate" SET NOT NULL;

ALTER TABLE "gtcrulesformaterialmovement" ALTER COLUMN "currentstate" SET NOT NULL;
ALTER TABLE "gtcrulesformaterialmovement" ALTER COLUMN "operationid" SET NOT NULL;
ALTER TABLE "gtcrulesformaterialmovement" ALTER COLUMN "locationformaterialmovementid" SET NOT NULL;
ALTER TABLE "gtcrulesformaterialmovement" ADD PRIMARY KEY ("currentstate","operationid","locationformaterialmovementid");

ALTER TABLE "gtcrulesformaterialmovement" ADD FOREIGN KEY ("locationformaterialmovementid") REFERENCES "gtclocationformaterialmovement"("locationformaterialmovementid");

ALTER TABLE "gtcrulesformaterialmovement" ADD FOREIGN KEY ("currentstate") REFERENCES "gtcexemplarystatus"("exemplarystatusid");

ALTER TABLE "gtcrulesformaterialmovement" ADD FOREIGN KEY ("futurestate") REFERENCES "gtcexemplarystatus"("exemplarystatusid");

ALTER TABLE "gtcrulesformaterialmovement" ADD FOREIGN KEY ("operationid") REFERENCES "gtcoperation"("operationid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreserve
-- Purpose: reservas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreserve" 
(
    "reserveid"          int4, --Identificador da reserva
    "libraryunitid"      int4, --C
    "personid"           int4, --C
    "requesteddate"      timestamp,
    "limitdate"          timestamp, --Data limite de esprera. 
    "reservestatusid"    int4, --C
    "reservetypeid"      int4
);

COMMENT ON TABLE "gtcreserve" IS 'reservas';
COMMENT ON COLUMN "gtcreserve"."reserveid" IS 'Identificador da reserva';
COMMENT ON COLUMN "gtcreserve"."libraryunitid" IS 'C';
COMMENT ON COLUMN "gtcreserve"."personid" IS 'C';
COMMENT ON COLUMN "gtcreserve"."limitdate" IS 'Data limite de esprera. ';
COMMENT ON COLUMN "gtcreserve"."reservestatusid" IS 'C';

CREATE SEQUENCE "seq_reserveid";
ALTER TABLE "gtcreserve" ALTER COLUMN "reserveid" SET DEFAULT NEXTVAL('"seq_reserveid"');
ALTER TABLE "gtcreserve" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcreserve" ALTER COLUMN "requesteddate" SET NOT NULL;
ALTER TABLE "gtcreserve" ALTER COLUMN "reservestatusid" SET NOT NULL;
ALTER TABLE "gtcreserve" ALTER COLUMN "reservetypeid" SET NOT NULL;

ALTER TABLE "gtcreserve" ALTER COLUMN "reserveid" SET NOT NULL;
ALTER TABLE "gtcreserve" ADD PRIMARY KEY ("reserveid");

ALTER TABLE "gtcreserve" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "gtcreserve" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

ALTER TABLE "gtcreserve" ADD FOREIGN KEY ("reservestatusid") REFERENCES "gtcreservestatus"("reservestatusid");

ALTER TABLE "gtcreserve" ADD FOREIGN KEY ("reservetypeid") REFERENCES "gtcreservetype"("reservetypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreservestatushistory
-- Purpose: mantem o historico das trocas de estado
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreservestatushistory" 
(
    "reserveid"          int4, --Identifica
    "reservestatusid"    int4, --Estado da reserva
    "date"               timestamp, --Cado o exemplar volta ela se torna confirmada
    "operator"           varchar(30)
);

COMMENT ON TABLE "gtcreservestatushistory" IS 'mantem o historico das trocas de estado';
COMMENT ON COLUMN "gtcreservestatushistory"."reserveid" IS 'Identifica';
COMMENT ON COLUMN "gtcreservestatushistory"."reservestatusid" IS 'Estado da reserva';
COMMENT ON COLUMN "gtcreservestatushistory"."date" IS 'Cado o exemplar volta ela se torna confirmada';

ALTER TABLE "gtcreservestatushistory" ALTER COLUMN "reserveid" SET NOT NULL;
ALTER TABLE "gtcreservestatushistory" ALTER COLUMN "reservestatusid" SET NOT NULL;
ALTER TABLE "gtcreservestatushistory" ALTER COLUMN "date" SET NOT NULL;
ALTER TABLE "gtcreservestatushistory" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcreservestatushistory" ADD FOREIGN KEY ("reserveid") REFERENCES "gtcreserve"("reserveid");

ALTER TABLE "gtcreservestatushistory" ADD FOREIGN KEY ("reservestatusid") REFERENCES "gtcreservestatus"("reservestatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtclibraryunitisclosed
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclibraryunitisclosed" 
(
    "libraryunitid"          int4,
    "weekdayid"              int4,
    "bug_dia2sql_ignorar"    int4
);

ALTER TABLE "gtclibraryunitisclosed" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtclibraryunitisclosed" ALTER COLUMN "weekdayid" SET NOT NULL;

ALTER TABLE "gtclibraryunitisclosed" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtclibraryunitisclosed" ALTER COLUMN "weekdayid" SET NOT NULL;
ALTER TABLE "gtclibraryunitisclosed" ADD PRIMARY KEY ("libraryunitid","weekdayid");

ALTER TABLE "gtclibraryunitisclosed" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

ALTER TABLE "gtclibraryunitisclosed" ADD FOREIGN KEY ("weekdayid") REFERENCES "gtcweekday"("weekdayid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcpenalty
-- Purpose: penalidade
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcpenalty" 
(
    "penaltyid"              int4, --Identifica
    "personid"               int4, --C
    "libraryunitid"          int4,
    "observation"            text, --Descri
    "internalobservation"    text,
    "penaltydate"            timestamp, --Data da inclus
    "penaltyenddate"         timestamp, --Data de final da penalidade.
    "operator"               varchar(30) --Usu
);

COMMENT ON TABLE "gtcpenalty" IS 'penalidade';
COMMENT ON COLUMN "gtcpenalty"."penaltyid" IS 'Identifica';
COMMENT ON COLUMN "gtcpenalty"."personid" IS 'C';
COMMENT ON COLUMN "gtcpenalty"."observation" IS 'Descri';
COMMENT ON COLUMN "gtcpenalty"."penaltydate" IS 'Data da inclus';
COMMENT ON COLUMN "gtcpenalty"."penaltyenddate" IS 'Data de final da penalidade.';
COMMENT ON COLUMN "gtcpenalty"."operator" IS 'Usu';

CREATE SEQUENCE "seq_penaltyid";
ALTER TABLE "gtcpenalty" ALTER COLUMN "penaltyid" SET DEFAULT NEXTVAL('"seq_penaltyid"');
ALTER TABLE "gtcpenalty" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcpenalty" ALTER COLUMN "penaltydate" SET NOT NULL;
ALTER TABLE "gtcpenalty" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcpenalty" ALTER COLUMN "penaltyid" SET NOT NULL;
ALTER TABLE "gtcpenalty" ADD PRIMARY KEY ("penaltyid");

ALTER TABLE "gtcpenalty" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "gtcpenalty" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcpersonconfig
-- Purpose: configuracoes do sistema
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcpersonconfig" 
(
    "personid"     int4, --Modulo do parametro
    "parameter"    text, --Parametro
    "value"        text --Valor
);

COMMENT ON TABLE "gtcpersonconfig" IS 'configuracoes do sistema';
COMMENT ON COLUMN "gtcpersonconfig"."personid" IS 'Modulo do parametro';
COMMENT ON COLUMN "gtcpersonconfig"."parameter" IS 'Parametro';
COMMENT ON COLUMN "gtcpersonconfig"."value" IS 'Valor';

ALTER TABLE "gtcpersonconfig" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcpersonconfig" ALTER COLUMN "parameter" SET NOT NULL;

ALTER TABLE "gtcpersonconfig" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcpersonconfig" ALTER COLUMN "parameter" SET NOT NULL;
ALTER TABLE "gtcpersonconfig" ADD PRIMARY KEY ("personid","parameter");

ALTER TABLE "gtcpersonconfig" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

----------------------------------------------------------------------
-- --
--
-- Table: gtclibraryunitconfig
-- Purpose: configuracoes do sistema
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclibraryunitconfig" 
(
    "libraryunitid"    int4, --Modulo do parametro
    "parameter"        text, --Parametro
    "value"            text --Valor
);

COMMENT ON TABLE "gtclibraryunitconfig" IS 'configuracoes do sistema';
COMMENT ON COLUMN "gtclibraryunitconfig"."libraryunitid" IS 'Modulo do parametro';
COMMENT ON COLUMN "gtclibraryunitconfig"."parameter" IS 'Parametro';
COMMENT ON COLUMN "gtclibraryunitconfig"."value" IS 'Valor';

ALTER TABLE "gtclibraryunitconfig" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtclibraryunitconfig" ALTER COLUMN "parameter" SET NOT NULL;

ALTER TABLE "gtclibraryunitconfig" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtclibraryunitconfig" ALTER COLUMN "parameter" SET NOT NULL;
ALTER TABLE "gtclibraryunitconfig" ADD PRIMARY KEY ("libraryunitid","parameter");

ALTER TABLE "gtclibraryunitconfig" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtclibraryunitaccess
-- Purpose: identifica os grupos que ter
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclibraryunitaccess" 
(
    "libraryunitid"          int4,
    "linkid"                 int4,
    "bug_dia2sql_ignorar"    int4
);

COMMENT ON TABLE "gtclibraryunitaccess" IS 'identifica os grupos que ter';

ALTER TABLE "gtclibraryunitaccess" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtclibraryunitaccess" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "gtclibraryunitaccess" ADD PRIMARY KEY ("libraryunitid","linkid");

ALTER TABLE "gtclibraryunitaccess" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

ALTER TABLE "gtclibraryunitaccess" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtccontrolfielddetail
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtccontrolfielddetail" 
(
    "controlfielddetailid"    int4,
    "fieldid"                 varchar(3),
    "subfieldid"              varchar(1),
    "beginposition"           int,
    "lenght"                  int,
    "description"             varchar(100),
    "categoryid"              varchar(2),
    "marctaglistid"           varchar,
    "isactive"                boolean,
    "defaultvalue"            varchar,
    "emptyvalue"              varchar(50)
);

CREATE SEQUENCE "seq_controlfielddetailid";
ALTER TABLE "gtccontrolfielddetail" ALTER COLUMN "controlfielddetailid" SET DEFAULT NEXTVAL('"seq_controlfielddetailid"');
ALTER TABLE "gtccontrolfielddetail" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtccontrolfielddetail" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtccontrolfielddetail" ALTER COLUMN "beginposition" SET NOT NULL;
ALTER TABLE "gtccontrolfielddetail" ALTER COLUMN "lenght" SET NOT NULL;
ALTER TABLE "gtccontrolfielddetail" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "gtccontrolfielddetail" ALTER COLUMN "categoryid" SET NOT NULL;

ALTER TABLE "gtccontrolfielddetail" ALTER COLUMN "controlfielddetailid" SET NOT NULL;
ALTER TABLE "gtccontrolfielddetail" ADD PRIMARY KEY ("controlfielddetailid");

ALTER TABLE "gtccontrolfielddetail" ADD FOREIGN KEY ("fieldid","subfieldid") REFERENCES "gtctag"("fieldid","subfieldid");

ALTER TABLE "gtccontrolfielddetail" ADD FOREIGN KEY ("marctaglistid") REFERENCES "gtcmarctaglisting"("marctaglistingid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcinterestsarea
-- Purpose: define as 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcinterestsarea" 
(
    "personid"                int4, --C
    "classificationareaid"    integer, --C
    "bud_dia2sql_ignorar"     int4
);

COMMENT ON TABLE "gtcinterestsarea" IS 'define as ';
COMMENT ON COLUMN "gtcinterestsarea"."personid" IS 'C';
COMMENT ON COLUMN "gtcinterestsarea"."classificationareaid" IS 'C';

ALTER TABLE "gtcinterestsarea" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcinterestsarea" ALTER COLUMN "classificationareaid" SET NOT NULL;

ALTER TABLE "gtcinterestsarea" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcinterestsarea" ALTER COLUMN "classificationareaid" SET NOT NULL;
ALTER TABLE "gtcinterestsarea" ADD PRIMARY KEY ("personid","classificationareaid");

ALTER TABLE "gtcinterestsarea" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "gtcinterestsarea" ADD FOREIGN KEY ("classificationareaid") REFERENCES "gtcclassificationarea"("classificationareaid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcpersonlibraryunit
-- Purpose: reservas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcpersonlibraryunit" 
(
    "libraryunitid"          int4, --C
    "personid"               int4, --C
    "bug_dia2sql_ignorar"    int4
);

COMMENT ON TABLE "gtcpersonlibraryunit" IS 'reservas';
COMMENT ON COLUMN "gtcpersonlibraryunit"."libraryunitid" IS 'C';
COMMENT ON COLUMN "gtcpersonlibraryunit"."personid" IS 'C';

ALTER TABLE "gtcpersonlibraryunit" ALTER COLUMN "personid" SET NOT NULL;

ALTER TABLE "gtcpersonlibraryunit" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtcpersonlibraryunit" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcpersonlibraryunit" ADD PRIMARY KEY ("libraryunitid","personid");

ALTER TABLE "gtcpersonlibraryunit" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "gtcpersonlibraryunit" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsearchpresentationformat
-- Purpose: formato da apresentacao da pesquisa
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsearchpresentationformat" 
(
    "searchformatid"    int4,
    "category"          varchar(2),
    "searchformat"      text,
    "detailformat"      text
);

COMMENT ON TABLE "gtcsearchpresentationformat" IS 'formato da apresentacao da pesquisa';

ALTER TABLE "gtcsearchpresentationformat" ALTER COLUMN "searchformatid" SET NOT NULL;
ALTER TABLE "gtcsearchpresentationformat" ALTER COLUMN "category" SET NOT NULL;
ALTER TABLE "gtcsearchpresentationformat" ALTER COLUMN "searchformat" SET NOT NULL;
ALTER TABLE "gtcsearchpresentationformat" ALTER COLUMN "detailformat" SET NOT NULL;

ALTER TABLE "gtcsearchpresentationformat" ALTER COLUMN "searchformatid" SET NOT NULL;
ALTER TABLE "gtcsearchpresentationformat" ALTER COLUMN "category" SET NOT NULL;
ALTER TABLE "gtcsearchpresentationformat" ADD PRIMARY KEY ("searchformatid","category");

ALTER TABLE "gtcsearchpresentationformat" ADD FOREIGN KEY ("searchformatid") REFERENCES "gtcsearchformat"("searchformatid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsearchformataccess
-- Purpose: especifica o acesso para o formato da pesquisa
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsearchformataccess" 
(
    "searchformatid"         int4,
    "linkid"                 int4,
    "bug_dia2sql_ignorar"    int4
);

COMMENT ON TABLE "gtcsearchformataccess" IS 'especifica o acesso para o formato da pesquisa';

ALTER TABLE "gtcsearchformataccess" ALTER COLUMN "searchformatid" SET NOT NULL;
ALTER TABLE "gtcsearchformataccess" ALTER COLUMN "linkid" SET NOT NULL;

ALTER TABLE "gtcsearchformataccess" ALTER COLUMN "searchformatid" SET NOT NULL;
ALTER TABLE "gtcsearchformataccess" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "gtcsearchformataccess" ADD PRIMARY KEY ("searchformatid","linkid");

ALTER TABLE "gtcsearchformataccess" ADD FOREIGN KEY ("searchformatid") REFERENCES "gtcsearchformat"("searchformatid");

ALTER TABLE "gtcsearchformataccess" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsuppliertypeandlocation
-- Purpose: tipo de fornecedor e detalhes de localiza
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsuppliertypeandlocation" 
(
    "supplierid"             int4, --Identifica
    "type"                   char check (type in ('c', 'p', 'd')),
    "name"                   varchar,
    "companyname"            varchar,
    "cnpj"                   varchar, --CNPJ
    "location"               varchar, --Endere
    "neighborhood"           varchar, --Bairro
    "city"                   varchar,
    "zipcode"                varchar,
    "phone"                  varchar,
    "fax"                    varchar,
    "alternativephone"       varchar,
    "email"                  varchar,
    "alternativeemail"       varchar,
    "contact"                varchar,
    "site"                   varchar,
    "observation"            text,
    "bankdeposit"            text,
    "date"                   timestamp,
    "bug_dia2sql_ignorar"    int4
);

COMMENT ON TABLE "gtcsuppliertypeandlocation" IS 'tipo de fornecedor e detalhes de localiza';
COMMENT ON COLUMN "gtcsuppliertypeandlocation"."supplierid" IS 'Identifica';
COMMENT ON COLUMN "gtcsuppliertypeandlocation"."cnpj" IS 'CNPJ';
COMMENT ON COLUMN "gtcsuppliertypeandlocation"."location" IS 'Endere';
COMMENT ON COLUMN "gtcsuppliertypeandlocation"."neighborhood" IS 'Bairro';

CREATE SEQUENCE "seq_supplierid";
ALTER TABLE "gtcsuppliertypeandlocation" ALTER COLUMN "supplierid" SET DEFAULT NEXTVAL('"seq_supplierid"');

ALTER TABLE "gtcsuppliertypeandlocation" ALTER COLUMN "supplierid" SET NOT NULL;
ALTER TABLE "gtcsuppliertypeandlocation" ALTER COLUMN "type" SET NOT NULL;
ALTER TABLE "gtcsuppliertypeandlocation" ADD PRIMARY KEY ("supplierid","type");

ALTER TABLE "gtcsuppliertypeandlocation" ADD FOREIGN KEY ("supplierid") REFERENCES "gtcsupplier"("supplierid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrulestocompletefieldsmarc
-- Purpose: regras para preenchimento de campos marc
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrulestocompletefieldsmarc" 
(
    "rulestocompletefieldsmarcid"    int4,
    "category"                       varchar(2),
    "originfield"                    text,
    "fatefield"                      text,
    "affectrecordscompleted"         boolean --Se for true altera os registros com valor
);

COMMENT ON TABLE "gtcrulestocompletefieldsmarc" IS 'regras para preenchimento de campos marc';
COMMENT ON COLUMN "gtcrulestocompletefieldsmarc"."affectrecordscompleted" IS 'Se for true altera os registros com valor';

CREATE SEQUENCE "seq_rulestocompletefieldsmarcid";
ALTER TABLE "gtcrulestocompletefieldsmarc" ALTER COLUMN "rulestocompletefieldsmarcid" SET DEFAULT NEXTVAL('"seq_rulestocompletefieldsmarcid"');
ALTER TABLE "gtcrulestocompletefieldsmarc" ALTER COLUMN "category" SET NOT NULL;
ALTER TABLE "gtcrulestocompletefieldsmarc" ALTER COLUMN "originfield" SET NOT NULL;
ALTER TABLE "gtcrulestocompletefieldsmarc" ALTER COLUMN "fatefield" SET NOT NULL;

ALTER TABLE "gtcrulestocompletefieldsmarc" ALTER COLUMN "rulestocompletefieldsmarcid" SET NOT NULL;
ALTER TABLE "gtcrulestocompletefieldsmarc" ADD PRIMARY KEY ("rulestocompletefieldsmarcid");

----------------------------------------------------------------------
-- --
--
-- Table: gtclinkoffieldsbetweenspreadsheets
-- Purpose: liga
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtclinkoffieldsbetweenspreadsheets" 
(
    "linkoffieldsbetweenspreadsheetsid"    int4,
    "category"                             varchar(2),
    "level"                                varchar(1),
    "tag"                                  varchar,
    "categoryson"                          varchar(2),
    "levelson"                             varchar(1),
    "tagson"                               varchar,
    "type"                                 int4
);

COMMENT ON TABLE "gtclinkoffieldsbetweenspreadsheets" IS 'liga';

CREATE SEQUENCE "seq_linkoffieldsbetweenspreadsheetsid";
ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ALTER COLUMN "linkoffieldsbetweenspreadsheetsid" SET DEFAULT NEXTVAL('"seq_linkoffieldsbetweenspreadsheetsid"');
ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ALTER COLUMN "category" SET NOT NULL;
ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ALTER COLUMN "level" SET NOT NULL;
ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ALTER COLUMN "tag" SET NOT NULL;
ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ALTER COLUMN "categoryson" SET NOT NULL;
ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ALTER COLUMN "levelson" SET NOT NULL;
ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ALTER COLUMN "tagson" SET NOT NULL;

ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ALTER COLUMN "linkoffieldsbetweenspreadsheetsid" SET NOT NULL;
ALTER TABLE "gtclinkoffieldsbetweenspreadsheets" ADD PRIMARY KEY ("linkoffieldsbetweenspreadsheetsid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcloanbetweenlibrary
-- Purpose: emprestimos
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcloanbetweenlibrary" 
(
    "loanbetweenlibraryid"          int4, --Identificador do emprestimo
    "loandate"                      timestamp, --Data e hora do empr
    "returnforecastdate"            timestamp, --Data prevista para devolu
    "returndate"                    timestamp, --Data da devolu
    "limitdate"                     timestamp,
    "libraryunitid"                 int4,
    "personid"                      int4,
    "loanbetweenlibrarystatusid"    int4,
    "observation"                   text
);

COMMENT ON TABLE "gtcloanbetweenlibrary" IS 'emprestimos';
COMMENT ON COLUMN "gtcloanbetweenlibrary"."loanbetweenlibraryid" IS 'Identificador do emprestimo';
COMMENT ON COLUMN "gtcloanbetweenlibrary"."loandate" IS 'Data e hora do empr';
COMMENT ON COLUMN "gtcloanbetweenlibrary"."returnforecastdate" IS 'Data prevista para devolu';
COMMENT ON COLUMN "gtcloanbetweenlibrary"."returndate" IS 'Data da devolu';

CREATE SEQUENCE "seq_loanbetweenlibraryid";
ALTER TABLE "gtcloanbetweenlibrary" ALTER COLUMN "loanbetweenlibraryid" SET DEFAULT NEXTVAL('"seq_loanbetweenlibraryid"');
ALTER TABLE "gtcloanbetweenlibrary" ALTER COLUMN "loandate" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrary" ALTER COLUMN "returnforecastdate" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrary" ALTER COLUMN "loanbetweenlibrarystatusid" SET NOT NULL;

ALTER TABLE "gtcloanbetweenlibrary" ALTER COLUMN "loanbetweenlibraryid" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrary" ADD PRIMARY KEY ("loanbetweenlibraryid");

ALTER TABLE "gtcloanbetweenlibrary" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "gtcloanbetweenlibrary" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

ALTER TABLE "gtcloanbetweenlibrary" ADD FOREIGN KEY ("loanbetweenlibrarystatusid") REFERENCES "gtcloanbetweenlibrarystatus"("loanbetweenlibrarystatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcloanbetweenlibrarystatushistory
-- Purpose: mantem o historico dos emprestimos entre bibliotecas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcloanbetweenlibrarystatushistory" 
(
    "loanbetweenlibraryid"          int4, --Identifica
    "loanbetweenlibrarystatusid"    int4, --Estado da reserva
    "date"                          timestamp, --Cado o exemplar volta ela se torna confirmada
    "operator"                      varchar(30)
);

COMMENT ON TABLE "gtcloanbetweenlibrarystatushistory" IS 'mantem o historico dos emprestimos entre bibliotecas';
COMMENT ON COLUMN "gtcloanbetweenlibrarystatushistory"."loanbetweenlibraryid" IS 'Identifica';
COMMENT ON COLUMN "gtcloanbetweenlibrarystatushistory"."loanbetweenlibrarystatusid" IS 'Estado da reserva';
COMMENT ON COLUMN "gtcloanbetweenlibrarystatushistory"."date" IS 'Cado o exemplar volta ela se torna confirmada';

ALTER TABLE "gtcloanbetweenlibrarystatushistory" ALTER COLUMN "loanbetweenlibraryid" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrarystatushistory" ALTER COLUMN "loanbetweenlibrarystatusid" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrarystatushistory" ALTER COLUMN "date" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrarystatushistory" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcloanbetweenlibrarystatushistory" ADD FOREIGN KEY ("loanbetweenlibrarystatusid") REFERENCES "gtcloanbetweenlibrarystatus"("loanbetweenlibrarystatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreportparameter
-- Purpose: parametros do relatorios
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreportparameter" 
(
    "reportparameterid"    int4,
    "reportid"             int4,
    "label"                varchar,
    "identifier"           varchar,
    "type"                 varchar,
    "defaultvalue"         varchar,
    "options"              text,
    "lastvalue"            varchar,
    "level"                int4
);

COMMENT ON TABLE "gtcreportparameter" IS 'parametros do relatorios';

CREATE SEQUENCE "seq_reportparameterid";
ALTER TABLE "gtcreportparameter" ALTER COLUMN "reportparameterid" SET DEFAULT NEXTVAL('"seq_reportparameterid"');
ALTER TABLE "gtcreportparameter" ALTER COLUMN "reportid" SET NOT NULL;
ALTER TABLE "gtcreportparameter" ALTER COLUMN "label" SET NOT NULL;
ALTER TABLE "gtcreportparameter" ALTER COLUMN "identifier" SET NOT NULL;
ALTER TABLE "gtcreportparameter" ALTER COLUMN "type" SET NOT NULL;

ALTER TABLE "gtcreportparameter" ALTER COLUMN "reportparameterid" SET NOT NULL;
ALTER TABLE "gtcreportparameter" ADD PRIMARY KEY ("reportparameterid");

ALTER TABLE "gtcreportparameter" ADD FOREIGN KEY ("reportid") REFERENCES "gtcreport"("reportid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcoperatorlibraryunit
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcoperatorlibraryunit" 
(
    "operator"         varchar,
    "libraryunitid"    int4
);

ALTER TABLE "gtcoperatorlibraryunit" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcoperatorlibraryunit" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcdictionarycontent
-- Purpose: dicion
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcdictionarycontent" 
(
    "dictionarycontentid"    int4,
    "dictionaryid"           int4,
    "content"                text
);

COMMENT ON TABLE "gtcdictionarycontent" IS 'dicion';

CREATE SEQUENCE "seq_dictionarycontentid";
ALTER TABLE "gtcdictionarycontent" ALTER COLUMN "dictionarycontentid" SET DEFAULT NEXTVAL('"seq_dictionarycontentid"');
ALTER TABLE "gtcdictionarycontent" ALTER COLUMN "dictionaryid" SET NOT NULL;
ALTER TABLE "gtcdictionarycontent" ALTER COLUMN "content" SET NOT NULL;

ALTER TABLE "gtcdictionarycontent" ALTER COLUMN "dictionarycontentid" SET NOT NULL;
ALTER TABLE "gtcdictionarycontent" ADD PRIMARY KEY ("dictionarycontentid");

ALTER TABLE "gtcdictionarycontent" ADD FOREIGN KEY ("dictionaryid") REFERENCES "gtcdictionary"("dictionaryid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcdictionaryrelatedcontent
-- Purpose: dicion
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcdictionaryrelatedcontent" 
(
    "dictionaryrelatedcontentid"    int4,
    "dictionarycontentid"           int4,
    "relatedcontent"                varchar
);

COMMENT ON TABLE "gtcdictionaryrelatedcontent" IS 'dicion';

CREATE SEQUENCE "seq_dictionaryrelatedcontentid";
ALTER TABLE "gtcdictionaryrelatedcontent" ALTER COLUMN "dictionaryrelatedcontentid" SET DEFAULT NEXTVAL('"seq_dictionaryrelatedcontentid"');
ALTER TABLE "gtcdictionaryrelatedcontent" ALTER COLUMN "dictionarycontentid" SET NOT NULL;

ALTER TABLE "gtcdictionaryrelatedcontent" ALTER COLUMN "dictionaryrelatedcontentid" SET NOT NULL;
ALTER TABLE "gtcdictionaryrelatedcontent" ADD PRIMARY KEY ("dictionaryrelatedcontentid");

ALTER TABLE "gtcdictionaryrelatedcontent" ADD FOREIGN KEY ("dictionarycontentid") REFERENCES "gtcdictionarycontent"("dictionarycontentid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcinterchangestatus
-- Purpose: estado da reserva
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcinterchangestatus" 
(
    "interchangestatusid"    int4, --Identificador da situa
    "description"            varchar(40), --Descri
    "interchangetypeid"      int4
);

COMMENT ON TABLE "gtcinterchangestatus" IS 'estado da reserva';
COMMENT ON COLUMN "gtcinterchangestatus"."interchangestatusid" IS 'Identificador da situa';
COMMENT ON COLUMN "gtcinterchangestatus"."description" IS 'Descri';

CREATE SEQUENCE "seq_interchangestatusid";
ALTER TABLE "gtcinterchangestatus" ALTER COLUMN "interchangestatusid" SET DEFAULT NEXTVAL('"seq_interchangestatusid"');
ALTER TABLE "gtcinterchangestatus" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtcinterchangestatus" ALTER COLUMN "interchangestatusid" SET NOT NULL;
ALTER TABLE "gtcinterchangestatus" ADD PRIMARY KEY ("interchangestatusid");

ALTER TABLE "gtcinterchangestatus" ADD FOREIGN KEY ("interchangetypeid") REFERENCES "gtcinterchangetype"("interchangetypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcprefixsuffix
-- Purpose: tabela para prefixos e sufixos
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcprefixsuffix" 
(
    "prefixsuffixid"    int4,
    "fieldid"           varchar(3),
    "subfieldid"        varchar(1),
    "content"           varchar,
    "type"              int4 --1 - prefix 2 - suffix
);

COMMENT ON TABLE "gtcprefixsuffix" IS 'tabela para prefixos e sufixos';
COMMENT ON COLUMN "gtcprefixsuffix"."type" IS '1 - prefix 2 - suffix';

CREATE SEQUENCE "seq_prefixsuffixid";
ALTER TABLE "gtcprefixsuffix" ALTER COLUMN "prefixsuffixid" SET DEFAULT NEXTVAL('"seq_prefixsuffixid"');
ALTER TABLE "gtcprefixsuffix" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtcprefixsuffix" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtcprefixsuffix" ALTER COLUMN "content" SET NOT NULL;

ALTER TABLE "gtcprefixsuffix" ALTER COLUMN "prefixsuffixid" SET NOT NULL;
ALTER TABLE "gtcprefixsuffix" ADD PRIMARY KEY ("prefixsuffixid");

ALTER TABLE "gtcprefixsuffix" ADD FOREIGN KEY ("fieldid","subfieldid") REFERENCES "gtctag"("fieldid","subfieldid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcseparator
-- Purpose: tabela para separadores
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcseparator" 
(
    "separatorid"            int4,
    "cataloguingformatid"    int4,
    "fieldid"                varchar(3),
    "subfieldid"             varchar(1),
    "content"                varchar,
    "fieldid2"               varchar(3),
    "subfieldid2"            varchar(1)
);

COMMENT ON TABLE "gtcseparator" IS 'tabela para separadores';

CREATE SEQUENCE "seq_separatorid";
ALTER TABLE "gtcseparator" ALTER COLUMN "separatorid" SET DEFAULT NEXTVAL('"seq_separatorid"');
ALTER TABLE "gtcseparator" ALTER COLUMN "cataloguingformatid" SET NOT NULL;
ALTER TABLE "gtcseparator" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtcseparator" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtcseparator" ALTER COLUMN "content" SET NOT NULL;
ALTER TABLE "gtcseparator" ALTER COLUMN "fieldid2" SET NOT NULL;
ALTER TABLE "gtcseparator" ALTER COLUMN "subfieldid2" SET NOT NULL;

ALTER TABLE "gtcseparator" ALTER COLUMN "separatorid" SET NOT NULL;
ALTER TABLE "gtcseparator" ADD PRIMARY KEY ("separatorid");

ALTER TABLE "gtcseparator" ADD FOREIGN KEY ("cataloguingformatid") REFERENCES "gtccataloguingformat"("cataloguingformatid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcnewsaccess
-- Purpose: identifica os grupos que ter
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcnewsaccess" 
(
    "newsid"                 int4,
    "linkid"                 int4,
    "bug_dia2sql_ignorar"    int4
);

COMMENT ON TABLE "gtcnewsaccess" IS 'identifica os grupos que ter';

ALTER TABLE "gtcnewsaccess" ALTER COLUMN "newsid" SET NOT NULL;
ALTER TABLE "gtcnewsaccess" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "gtcnewsaccess" ADD PRIMARY KEY ("newsid","linkid");

ALTER TABLE "gtcnewsaccess" ADD FOREIGN KEY ("newsid") REFERENCES "gtcnews"("newsid");

ALTER TABLE "gtcnewsaccess" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsearchablefieldaccess
-- Purpose: especifica o acesso para o formato da pesquisa
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsearchablefieldaccess" 
(
    "searchablefieldid"      int4,
    "linkid"                 int4,
    "bug_dia2sql_ignorar"    int4
);

COMMENT ON TABLE "gtcsearchablefieldaccess" IS 'especifica o acesso para o formato da pesquisa';

ALTER TABLE "gtcsearchablefieldaccess" ALTER COLUMN "searchablefieldid" SET NOT NULL;
ALTER TABLE "gtcsearchablefieldaccess" ALTER COLUMN "linkid" SET NOT NULL;

ALTER TABLE "gtcsearchablefieldaccess" ALTER COLUMN "searchablefieldid" SET NOT NULL;
ALTER TABLE "gtcsearchablefieldaccess" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "gtcsearchablefieldaccess" ADD PRIMARY KEY ("searchablefieldid","linkid");

ALTER TABLE "gtcsearchablefieldaccess" ADD FOREIGN KEY ("searchablefieldid") REFERENCES "gtcsearchablefield"("searchablefieldid");

ALTER TABLE "gtcsearchablefieldaccess" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrequestchangeexemplarystatus
-- Purpose: estado do exemplar
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrequestchangeexemplarystatus" 
(
    "requestchangeexemplarystatusid"          int4,
    "futurestatusid"                          int4,
    "personid"                                int4,
    "observation"                             text,
    "date"                                    timestamp,
    "finaldate"                               timestamp,
    "requestchangeexemplarystatusstatusid"    int4, --referencia tabela de possiveis estados
    "libraryunitid"                           int4, --referencia biblioteca
    "aprovejustone"                           boolean,
    "discipline"                              varchar --Disciplina (mat
);

COMMENT ON TABLE "gtcrequestchangeexemplarystatus" IS 'estado do exemplar';
COMMENT ON COLUMN "gtcrequestchangeexemplarystatus"."requestchangeexemplarystatusstatusid" IS 'referencia tabela de possiveis estados';
COMMENT ON COLUMN "gtcrequestchangeexemplarystatus"."libraryunitid" IS 'referencia biblioteca';
COMMENT ON COLUMN "gtcrequestchangeexemplarystatus"."discipline" IS 'Disciplina (mat';

CREATE SEQUENCE "seq_requestchangeexemplarystatusid";
ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "requestchangeexemplarystatusid" SET DEFAULT NEXTVAL('"seq_requestchangeexemplarystatusid"');
ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "futurestatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "date" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "finaldate" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "requestchangeexemplarystatusstatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "aprovejustone" SET DEFAULT true;

ALTER TABLE "gtcrequestchangeexemplarystatus" ALTER COLUMN "requestchangeexemplarystatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatus" ADD PRIMARY KEY ("requestchangeexemplarystatusid");

ALTER TABLE "gtcrequestchangeexemplarystatus" ADD FOREIGN KEY ("futurestatusid") REFERENCES "gtcexemplarystatus"("exemplarystatusid");

ALTER TABLE "gtcrequestchangeexemplarystatus" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "gtcrequestchangeexemplarystatus" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrequestchangeexemplarystatusstatushistory
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrequestchangeexemplarystatusstatushistory" 
(
    "requestchangeexemplarystatusid"          int4,
    "requestchangeexemplarystatusstatusid"    int4,
    "date"                                    timestamp,
    "operator"                                varchar(40)
);

ALTER TABLE "gtcrequestchangeexemplarystatusstatushistory" ALTER COLUMN "requestchangeexemplarystatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatusstatushistory" ALTER COLUMN "requestchangeexemplarystatusstatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatusstatushistory" ALTER COLUMN "date" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatusstatushistory" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcrequestchangeexemplarystatusstatushistory" ADD FOREIGN KEY ("requestchangeexemplarystatusid") REFERENCES "gtcrequestchangeexemplarystatus"("requestchangeexemplarystatusid");

ALTER TABLE "gtcrequestchangeexemplarystatusstatushistory" ADD FOREIGN KEY ("requestchangeexemplarystatusstatusid") REFERENCES "gtcrequestchangeexemplarystatusstatus"("requestchangeexemplarystatusstatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrequestchangeexemplarystatusaccess
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrequestchangeexemplarystatusaccess" 
(
    "baslinkid"              int4,
    "exemplarystatusid"      int4,
    "bug_dia2sql_ignorar"    int
);

ALTER TABLE "gtcrequestchangeexemplarystatusaccess" ALTER COLUMN "baslinkid" SET not null;
ALTER TABLE "gtcrequestchangeexemplarystatusaccess" ALTER COLUMN "exemplarystatusid" SET not null;

ALTER TABLE "gtcrequestchangeexemplarystatusaccess" ALTER COLUMN "baslinkid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatusaccess" ALTER COLUMN "exemplarystatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatusaccess" ADD PRIMARY KEY ("baslinkid","exemplarystatusid");

ALTER TABLE "gtcrequestchangeexemplarystatusaccess" ADD FOREIGN KEY ("exemplarystatusid") REFERENCES "gtcexemplarystatus"("exemplarystatusid");

ALTER TABLE "gtcrequestchangeexemplarystatusaccess" ADD FOREIGN KEY ("baslinkid") REFERENCES "baslink"("linkid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcformcontent
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcformcontent" 
(
    "formcontentid"      int4,
    "operator"           varchar,
    "form"               varchar,
    "name"               varchar,
    "description"        varchar,
    "formcontenttype"    int
);

CREATE SEQUENCE "seq_formcontentid";
ALTER TABLE "gtcformcontent" ALTER COLUMN "formcontentid" SET DEFAULT NEXTVAL('"seq_formcontentid"');
ALTER TABLE "gtcformcontent" ALTER COLUMN "form" SET NOT NULL;
ALTER TABLE "gtcformcontent" ALTER COLUMN "formcontenttype" SET NOT NULL;

ALTER TABLE "gtcformcontent" ALTER COLUMN "formcontentid" SET NOT NULL;
ALTER TABLE "gtcformcontent" ADD PRIMARY KEY ("formcontentid");

ALTER TABLE "gtcformcontent" ADD FOREIGN KEY ("formcontenttype") REFERENCES "gtcformcontenttype"("formcontenttypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsearchformatcolumn
-- Purpose: especifica as colunas no search format
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsearchformatcolumn" 
(
    "searchformatid"         int4,
    "column"                 varchar,
    "bug_dia2sql_ignorar"    int4
);

COMMENT ON TABLE "gtcsearchformatcolumn" IS 'especifica as colunas no search format';

ALTER TABLE "gtcsearchformatcolumn" ALTER COLUMN "searchformatid" SET NOT NULL;
ALTER TABLE "gtcsearchformatcolumn" ALTER COLUMN "column" SET NOT NULL;

ALTER TABLE "gtcsearchformatcolumn" ALTER COLUMN "searchformatid" SET NOT NULL;
ALTER TABLE "gtcsearchformatcolumn" ALTER COLUMN "column" SET NOT NULL;
ALTER TABLE "gtcsearchformatcolumn" ADD PRIMARY KEY ("searchformatid","column");

ALTER TABLE "gtcsearchformatcolumn" ADD FOREIGN KEY ("searchformatid") REFERENCES "gtcsearchformat"("searchformatid");

----------------------------------------------------------------------
-- --
--
-- Table: gtccostcenter
-- Purpose: centro de custo
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtccostcenter" 
(
    "costcenterid"     integer,
    "libraryunitid"    integer,
    "description"      varchar(255)
);

COMMENT ON TABLE "gtccostcenter" IS 'centro de custo';

CREATE SEQUENCE "seq_costcenterid";
ALTER TABLE "gtccostcenter" ALTER COLUMN "costcenterid" SET DEFAULT NEXTVAL('"seq_costcenterid"');
ALTER TABLE "gtccostcenter" ALTER COLUMN "description" SET NOT NULL;

ALTER TABLE "gtccostcenter" ALTER COLUMN "costcenterid" SET NOT NULL;
ALTER TABLE "gtccostcenter" ADD PRIMARY KEY ("costcenterid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcscheduletask
-- Purpose: agendamento de tarefas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcscheduletask" 
(
    "scheduletaskid"     integer,
    "taskid"             integer,
    "schedulecycleid"    integer,
    "description"        varchar,
    "cyclevalue"         varchar,
    "enable"             boolean,
    "parameters"         varchar
);

COMMENT ON TABLE "gtcscheduletask" IS 'agendamento de tarefas';

ALTER TABLE "gtcscheduletask" ALTER COLUMN "scheduletaskid" SET NOT NULL;
ALTER TABLE "gtcscheduletask" ALTER COLUMN "taskid" SET NOT NULL;
ALTER TABLE "gtcscheduletask" ALTER COLUMN "schedulecycleid" SET NOT NULL;
ALTER TABLE "gtcscheduletask" ALTER COLUMN "description" SET NOT NULL;
ALTER TABLE "gtcscheduletask" ALTER COLUMN "cyclevalue" SET NOT NULL;
ALTER TABLE "gtcscheduletask" ALTER COLUMN "enable" SET DEFAULT true;

ALTER TABLE "gtcscheduletask" ALTER COLUMN "scheduletaskid" SET NOT NULL;
ALTER TABLE "gtcscheduletask" ADD PRIMARY KEY ("scheduletaskid");

ALTER TABLE "gtcscheduletask" ADD FOREIGN KEY ("schedulecycleid") REFERENCES "gtcschedulecycle"("schedulecycleid");

ALTER TABLE "gtcscheduletask" ADD FOREIGN KEY ("taskid") REFERENCES "gtctask"("taskid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcscheduletasklog
-- Purpose: log de tarefas realizadas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcscheduletasklog" 
(
    "scheduletaskid"    integer,
    "log"               text,
    "date"              timestamp
);

COMMENT ON TABLE "gtcscheduletasklog" IS 'log de tarefas realizadas';

ALTER TABLE "gtcscheduletasklog" ALTER COLUMN "scheduletaskid" SET NOT NULL;
ALTER TABLE "gtcscheduletasklog" ALTER COLUMN "log" SET NOT NULL;
ALTER TABLE "gtcscheduletasklog" ALTER COLUMN "date" SET NOT NULL;

ALTER TABLE "gtcscheduletasklog" ADD FOREIGN KEY ("scheduletaskid") REFERENCES "gtcscheduletask"("scheduletaskid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcsoapaccess
-- Purpose: access soap
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcsoapaccess" 
(
    "soapclientid"           integer,
    "webserviceid"           integer,
    "bug_dia2sql_ignorar"    int4
);

COMMENT ON TABLE "gtcsoapaccess" IS 'access soap';

ALTER TABLE "gtcsoapaccess" ALTER COLUMN "soapclientid" SET NOT NULL;
ALTER TABLE "gtcsoapaccess" ALTER COLUMN "webserviceid" SET NOT NULL;

ALTER TABLE "gtcsoapaccess" ALTER COLUMN "soapclientid" SET NOT NULL;
ALTER TABLE "gtcsoapaccess" ALTER COLUMN "webserviceid" SET NOT NULL;
ALTER TABLE "gtcsoapaccess" ADD PRIMARY KEY ("soapclientid","webserviceid");

ALTER TABLE "gtcsoapaccess" ADD FOREIGN KEY ("soapclientid") REFERENCES "gtcsoapclient"("soapclientid");

ALTER TABLE "gtcsoapaccess" ADD FOREIGN KEY ("webserviceid") REFERENCES "gtcwebservice"("webserviceid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcemailcontrolnotifyaquisition
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcemailcontrolnotifyaquisition" 
(
    "personid"    int4,
    "lastsent"    timestamp
);

ALTER TABLE "gtcemailcontrolnotifyaquisition" ALTER COLUMN "lastsent" SET NOT NULL;

ALTER TABLE "gtcemailcontrolnotifyaquisition" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

----------------------------------------------------------------------
-- --
--
-- Table: basphone
-- Purpose: armazena os telefones da pessoa
--
-- --
----------------------------------------------------------------------

CREATE TABLE "basphone" 
(
    "personid"    int4, --C
    "type"        varchar(100), --Tipo de telefone
    "phone"       varchar(20) --telefone da pessoa
);

COMMENT ON TABLE "basphone" IS 'armazena os telefones da pessoa';
COMMENT ON COLUMN "basphone"."personid" IS 'C';
COMMENT ON COLUMN "basphone"."type" IS 'Tipo de telefone';
COMMENT ON COLUMN "basphone"."phone" IS 'telefone da pessoa';

ALTER TABLE "basphone" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "basphone" ALTER COLUMN "type" SET NOT NULL;
ALTER TABLE "basphone" ALTER COLUMN "phone" SET NOT NULL;

ALTER TABLE "basphone" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "basphone" ALTER COLUMN "type" SET NOT NULL;
ALTER TABLE "basphone" ADD PRIMARY KEY ("personid","type");

ALTER TABLE "basphone" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcmaterial
-- Purpose: armazena os materias no padr
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcmaterial" 
(
    "controlnumber"    integer,
    "fieldid"          varchar(3),
    "subfieldid"       varchar(1),
    "line"             integer,
    "indicator1"       varchar(1),
    "indicator2"       varchar(1),
    "content"          text,
    "searchcontent"    text,
    "prefixid"         int4,
    "suffixid"         int4,
    "separatorid"      int4
);

COMMENT ON TABLE "gtcmaterial" IS 'armazena os materias no padr';

CREATE SEQUENCE "seq_controlnumber";
ALTER TABLE "gtcmaterial" ALTER COLUMN "controlnumber" SET DEFAULT NEXTVAL('"seq_controlnumber"');
ALTER TABLE "gtcmaterial" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtcmaterial" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtcmaterial" ALTER COLUMN "line" SET NOT NULL;

ALTER TABLE "gtcmaterial" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcmaterial" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtcmaterial" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtcmaterial" ALTER COLUMN "line" SET NOT NULL;
ALTER TABLE "gtcmaterial" ADD PRIMARY KEY ("controlnumber","fieldid","subfieldid","line");

ALTER TABLE "gtcmaterial" ADD FOREIGN KEY ("fieldid","subfieldid") REFERENCES "gtctag"("fieldid","subfieldid");

ALTER TABLE "gtcmaterial" ADD FOREIGN KEY ("prefixid") REFERENCES "gtcprefixsuffix"("prefixsuffixid");

ALTER TABLE "gtcmaterial" ADD FOREIGN KEY ("suffixid") REFERENCES "gtcprefixsuffix"("prefixsuffixid");

ALTER TABLE "gtcmaterial" ADD FOREIGN KEY ("separatorid") REFERENCES "gtcseparator"("separatorid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcmaterialhistory
-- Purpose: armazena os materias no padr
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcmaterialhistory" 
(
    "materialhistoryid"     int4,
    "controlnumber"         integer,
    "revisionnumber"        int4,
    "operator"              varchar(30),
    "data"                  timestamp,
    "chancestype"           char, --I - insert U - update D - delete
    "fieldid"               varchar(3),
    "subfieldid"            varchar(1),
    "previousline"          int4,
    "previousindicator1"    varchar(1),
    "previousindicator2"    varchar(1),
    "previouscontent"       text,
    "currentline"           int4,
    "currentindicator1"     varchar(1),
    "currentindicator2"     varchar(1),
    "currentcontent"        text
);

COMMENT ON TABLE "gtcmaterialhistory" IS 'armazena os materias no padr';
COMMENT ON COLUMN "gtcmaterialhistory"."chancestype" IS 'I - insert U - update D - delete';

CREATE SEQUENCE "seq_materialhistoryid";
ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "materialhistoryid" SET DEFAULT NEXTVAL('"seq_materialhistoryid"');
ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "revisionnumber" SET NOT NULL;
ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "operator" SET NOT NULL;
ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "data" SET NOT NULL;
ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "chancestype" SET NOT NULL;
ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "subfieldid" SET NOT NULL;

ALTER TABLE "gtcmaterialhistory" ALTER COLUMN "materialhistoryid" SET NOT NULL;
ALTER TABLE "gtcmaterialhistory" ADD PRIMARY KEY ("materialhistoryid");

ALTER TABLE "gtcmaterialhistory" ADD FOREIGN KEY ("fieldid","subfieldid") REFERENCES "gtctag"("fieldid","subfieldid");

ALTER TABLE "gtcmaterialhistory" ADD FOREIGN KEY ("controlnumber") REFERENCES "gtcmaterial"("controlnumber");

----------------------------------------------------------------------
-- --
--
-- Table: gtcmaterialcontrol
-- Purpose: armazena os exemplares
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcmaterialcontrol" 
(
    "controlnumber"             int4, --Identificador da obra
    "controlnumberfather"       int4,
    "entrancedate"              date,
    "lastchangedate"            date,
    "category"                  varchar(2),
    "level"                     varchar(1),
    "materialgenderid"          int4,
    "materialtypeid"            int4,
    "materialphysicaltypeid"    int4,
    "originalimage"             int4,
    "smallimage"                int4,
    "middleimage"               int4
);

COMMENT ON TABLE "gtcmaterialcontrol" IS 'armazena os exemplares';
COMMENT ON COLUMN "gtcmaterialcontrol"."controlnumber" IS 'Identificador da obra';

ALTER TABLE "gtcmaterialcontrol" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcmaterialcontrol" ALTER COLUMN "entrancedate" SET NOT NULL;
ALTER TABLE "gtcmaterialcontrol" ALTER COLUMN "lastchangedate" SET NOT NULL;
ALTER TABLE "gtcmaterialcontrol" ALTER COLUMN "category" SET NOT NULL;
ALTER TABLE "gtcmaterialcontrol" ALTER COLUMN "level" SET NOT NULL;

ALTER TABLE "gtcmaterialcontrol" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcmaterialcontrol" ADD PRIMARY KEY ("controlnumber");

ALTER TABLE "gtcmaterialcontrol" ADD FOREIGN KEY ("category","level") REFERENCES "gtcspreadsheet"("category","level");

ALTER TABLE "gtcmaterialcontrol" ADD FOREIGN KEY ("controlnumber") REFERENCES "gtcmaterial"("controlnumber");

ALTER TABLE "gtcmaterialcontrol" ADD FOREIGN KEY ("materialgenderid") REFERENCES "gtcmaterialgender"("materialgenderid");

ALTER TABLE "gtcmaterialcontrol" ADD FOREIGN KEY ("materialtypeid") REFERENCES "gtcmaterialtype"("materialtypeid");

ALTER TABLE "gtcmaterialcontrol" ADD FOREIGN KEY ("materialphysicaltypeid") REFERENCES "gtcmaterialphysicaltype"("materialphysicaltypeid");

ALTER TABLE "gtcmaterialcontrol" ADD FOREIGN KEY ("originalimage") REFERENCES "gtcfile"("fileid");

ALTER TABLE "gtcmaterialcontrol" ADD FOREIGN KEY ("smallimage") REFERENCES "gtcfile"("fileid");

ALTER TABLE "gtcmaterialcontrol" ADD FOREIGN KEY ("middleimage") REFERENCES "gtcfile"("fileid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcfavorite
-- Purpose: materiais favoritos do usu
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcfavorite" 
(
    "personid"         int4, --Id da pessoa
    "controlnumber"    int4, --numero de controle
    "entracedate"      timestamp --Data de entrada
);

COMMENT ON TABLE "gtcfavorite" IS 'materiais favoritos do usu';
COMMENT ON COLUMN "gtcfavorite"."personid" IS 'Id da pessoa';
COMMENT ON COLUMN "gtcfavorite"."controlnumber" IS 'numero de controle';
COMMENT ON COLUMN "gtcfavorite"."entracedate" IS 'Data de entrada';

ALTER TABLE "gtcfavorite" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcfavorite" ALTER COLUMN "controlnumber" SET NOT NULL;

ALTER TABLE "gtcfavorite" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcfavorite" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcfavorite" ADD PRIMARY KEY ("personid","controlnumber");

ALTER TABLE "gtcfavorite" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "gtcfavorite" ADD FOREIGN KEY ("controlnumber") REFERENCES "gtcmaterial"("controlnumber");

----------------------------------------------------------------------
-- --
--
-- Table: gtckardexcontrol
-- Purpose: armazena dados da cole
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtckardexcontrol" 
(
    "controlnumber"             int4, --Identificador da obra
    "codigodeassinante"         varchar(40),
    "libraryunitid"             integer, --Codigo da unidade
    "acquisitiontype"           varchar(1), --Tipo de aquisicao
    "vencimentodaassinatura"    date,
    "datadaassinatura"          date,
    "entrancedate"              date,
    "line"                      int4
);

COMMENT ON TABLE "gtckardexcontrol" IS 'armazena dados da cole';
COMMENT ON COLUMN "gtckardexcontrol"."controlnumber" IS 'Identificador da obra';
COMMENT ON COLUMN "gtckardexcontrol"."libraryunitid" IS 'Codigo da unidade';
COMMENT ON COLUMN "gtckardexcontrol"."acquisitiontype" IS 'Tipo de aquisicao';

ALTER TABLE "gtckardexcontrol" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtckardexcontrol" ALTER COLUMN "codigodeassinante" SET NOT NULL;
ALTER TABLE "gtckardexcontrol" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtckardexcontrol" ALTER COLUMN "acquisitiontype" SET NOT NULL;

ALTER TABLE "gtckardexcontrol" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtckardexcontrol" ALTER COLUMN "codigodeassinante" SET NOT NULL;
ALTER TABLE "gtckardexcontrol" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtckardexcontrol" ADD PRIMARY KEY ("controlnumber","codigodeassinante","libraryunitid");

ALTER TABLE "gtckardexcontrol" ADD FOREIGN KEY ("controlnumber") REFERENCES "gtcmaterial"("controlnumber");

ALTER TABLE "gtckardexcontrol" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcprecatalogue
-- Purpose: armazena temporariamente os materias no padr
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcprecatalogue" 
(
    "controlnumber"    integer,
    "fieldid"          varchar(3),
    "subfieldid"       varchar(1),
    "line"             integer,
    "indicator1"       varchar(1),
    "indicator2"       varchar(1),
    "content"          text,
    "searchcontent"    text,
    "prefixid"         int4,
    "suffixid"         int4,
    "separatorid"      int4
);

COMMENT ON TABLE "gtcprecatalogue" IS 'armazena temporariamente os materias no padr';

ALTER TABLE "gtcprecatalogue" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcprecatalogue" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtcprecatalogue" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtcprecatalogue" ALTER COLUMN "line" SET NOT NULL;

ALTER TABLE "gtcprecatalogue" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcprecatalogue" ALTER COLUMN "fieldid" SET NOT NULL;
ALTER TABLE "gtcprecatalogue" ALTER COLUMN "subfieldid" SET NOT NULL;
ALTER TABLE "gtcprecatalogue" ALTER COLUMN "line" SET NOT NULL;
ALTER TABLE "gtcprecatalogue" ADD PRIMARY KEY ("controlnumber","fieldid","subfieldid","line");

ALTER TABLE "gtcprecatalogue" ADD FOREIGN KEY ("prefixid") REFERENCES "gtcprefixsuffix"("prefixsuffixid");

ALTER TABLE "gtcprecatalogue" ADD FOREIGN KEY ("suffixid") REFERENCES "gtcprefixsuffix"("prefixsuffixid");

ALTER TABLE "gtcprecatalogue" ADD FOREIGN KEY ("separatorid") REFERENCES "gtcseparator"("separatorid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcformcontentdetail
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcformcontentdetail" 
(
    "formcontentid"    varchar,
    "field"            varchar,
    "value"            varchar
);

ALTER TABLE "gtcformcontentdetail" ALTER COLUMN "formcontentid" SET NOT NULL;
ALTER TABLE "gtcformcontentdetail" ALTER COLUMN "field" SET NOT NULL;

ALTER TABLE "gtcformcontentdetail" ALTER COLUMN "formcontentid" SET NOT NULL;
ALTER TABLE "gtcformcontentdetail" ALTER COLUMN "field" SET NOT NULL;
ALTER TABLE "gtcformcontentdetail" ADD PRIMARY KEY ("formcontentid","field");

ALTER TABLE "gtcformcontentdetail" ADD FOREIGN KEY ("formcontentid") REFERENCES "gtcformcontent"("formcontentid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcinterchange
-- Purpose: permuta
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcinterchange" 
(
    "interchangeid"          int4, --Identifica
    "type"                   char check (type in ('p', 'd')),
    "supplierid"             int4,
    "description"            varchar,
    "date"                   timestamp,
    "interchangestatusid"    int4,
    "interchangetypeid"      int4, --1 - Envio; 2 - Recebimento
    "operator"               varchar(30)
);

COMMENT ON TABLE "gtcinterchange" IS 'permuta';
COMMENT ON COLUMN "gtcinterchange"."interchangeid" IS 'Identifica';
COMMENT ON COLUMN "gtcinterchange"."interchangetypeid" IS '1 - Envio; 2 - Recebimento';

CREATE SEQUENCE "seq_interchangeid";
ALTER TABLE "gtcinterchange" ALTER COLUMN "interchangeid" SET DEFAULT NEXTVAL('"seq_interchangeid"');
ALTER TABLE "gtcinterchange" ALTER COLUMN "supplierid" SET NOT NULL;
ALTER TABLE "gtcinterchange" ALTER COLUMN "date" SET NOT NULL;

ALTER TABLE "gtcinterchange" ALTER COLUMN "interchangeid" SET NOT NULL;
ALTER TABLE "gtcinterchange" ADD PRIMARY KEY ("interchangeid");

ALTER TABLE "gtcinterchange" ADD FOREIGN KEY ("interchangestatusid") REFERENCES "gtcinterchangestatus"("interchangestatusid");

ALTER TABLE "gtcinterchange" ADD FOREIGN KEY ("interchangetypeid") REFERENCES "gtcinterchangetype"("interchangetypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcinterchangeitem
-- Purpose: itens de permuta
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcinterchangeitem" 
(
    "interchangeitemid"    int4, --Identifica
    "interchangeid"        int4,
    "controlnumber"        int4,
    "content"              varchar
);

COMMENT ON TABLE "gtcinterchangeitem" IS 'itens de permuta';
COMMENT ON COLUMN "gtcinterchangeitem"."interchangeitemid" IS 'Identifica';

CREATE SEQUENCE "seq_interchangeitemid";
ALTER TABLE "gtcinterchangeitem" ALTER COLUMN "interchangeitemid" SET DEFAULT NEXTVAL('"seq_interchangeitemid"');
ALTER TABLE "gtcinterchangeitem" ALTER COLUMN "interchangeid" SET NOT NULL;

ALTER TABLE "gtcinterchangeitem" ALTER COLUMN "interchangeitemid" SET NOT NULL;
ALTER TABLE "gtcinterchangeitem" ADD PRIMARY KEY ("interchangeitemid");

ALTER TABLE "gtcinterchangeitem" ADD FOREIGN KEY ("interchangeid") REFERENCES "gtcinterchange"("interchangeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcinterchangeobservation
-- Purpose: observa
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcinterchangeobservation" 
(
    "interchangeobservationid"    int4, --Identifica
    "interchangeid"               int4,
    "observation"                 text,
    "date"                        timestamp,
    "operator"                    varchar(30)
);

COMMENT ON TABLE "gtcinterchangeobservation" IS 'observa';
COMMENT ON COLUMN "gtcinterchangeobservation"."interchangeobservationid" IS 'Identifica';

CREATE SEQUENCE "seq_interchangeobservationid";
ALTER TABLE "gtcinterchangeobservation" ALTER COLUMN "interchangeobservationid" SET DEFAULT NEXTVAL('"seq_interchangeobservationid"');
ALTER TABLE "gtcinterchangeobservation" ALTER COLUMN "interchangeid" SET NOT NULL;

ALTER TABLE "gtcinterchangeobservation" ALTER COLUMN "interchangeobservationid" SET NOT NULL;
ALTER TABLE "gtcinterchangeobservation" ADD PRIMARY KEY ("interchangeobservationid");

ALTER TABLE "gtcinterchangeobservation" ADD FOREIGN KEY ("interchangeid") REFERENCES "gtcinterchange"("interchangeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcexemplarycontrol
-- Purpose: armazena os exemplares
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcexemplarycontrol" 
(
    "controlnumber"             int4, --Identificador da obra
    "itemnumber"                varchar(20), --Descri
    "originallibraryunitid"     int4,
    "libraryunitid"             integer, --Codigo da unidade
    "acquisitiontype"           varchar(1), --Tipo de aquisicao
    "exemplarystatusid"         integer, --C
    "materialgenderid"          int4,
    "materialtypeid"            int4,
    "materialphysicaltypeid"    int4,
    "entrancedate"              date,
    "lowdate"                   date,
    "line"                      int4,
    "observation"               text
);

COMMENT ON TABLE "gtcexemplarycontrol" IS 'armazena os exemplares';
COMMENT ON COLUMN "gtcexemplarycontrol"."controlnumber" IS 'Identificador da obra';
COMMENT ON COLUMN "gtcexemplarycontrol"."itemnumber" IS 'Descri';
COMMENT ON COLUMN "gtcexemplarycontrol"."libraryunitid" IS 'Codigo da unidade';
COMMENT ON COLUMN "gtcexemplarycontrol"."acquisitiontype" IS 'Tipo de aquisicao';
COMMENT ON COLUMN "gtcexemplarycontrol"."exemplarystatusid" IS 'C';

ALTER TABLE "gtcexemplarycontrol" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcexemplarycontrol" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcexemplarycontrol" ALTER COLUMN "originallibraryunitid" SET NOT NULL;
ALTER TABLE "gtcexemplarycontrol" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtcexemplarycontrol" ALTER COLUMN "acquisitiontype" SET NOT NULL;
ALTER TABLE "gtcexemplarycontrol" ALTER COLUMN "exemplarystatusid" SET NOT NULL;

ALTER TABLE "gtcexemplarycontrol" ALTER COLUMN "controlnumber" SET NOT NULL;
ALTER TABLE "gtcexemplarycontrol" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcexemplarycontrol" ADD PRIMARY KEY ("controlnumber","itemnumber");

ALTER TABLE "gtcexemplarycontrol" ADD FOREIGN KEY ("exemplarystatusid") REFERENCES "gtcexemplarystatus"("exemplarystatusid");

ALTER TABLE "gtcexemplarycontrol" ADD FOREIGN KEY ("controlnumber") REFERENCES "gtcmaterial"("controlnumber");

ALTER TABLE "gtcexemplarycontrol" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

ALTER TABLE "gtcexemplarycontrol" ADD FOREIGN KEY ("materialgenderid") REFERENCES "gtcmaterialgender"("materialgenderid");

ALTER TABLE "gtcexemplarycontrol" ADD FOREIGN KEY ("originallibraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

ALTER TABLE "gtcexemplarycontrol" ADD FOREIGN KEY ("materialtypeid") REFERENCES "gtcmaterialtype"("materialtypeid");

ALTER TABLE "gtcexemplarycontrol" ADD FOREIGN KEY ("materialphysicaltypeid") REFERENCES "gtcmaterialphysicaltype"("materialphysicaltypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreservecomposition
-- Purpose: composi
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreservecomposition" 
(
    "reserveid"      int4, --Identifica
    "itemnumber"     varchar(20), --N
    "isconfirmed"    boolean --Cado o exemplar volta ela se torna confirmada
);

COMMENT ON TABLE "gtcreservecomposition" IS 'composi';
COMMENT ON COLUMN "gtcreservecomposition"."reserveid" IS 'Identifica';
COMMENT ON COLUMN "gtcreservecomposition"."itemnumber" IS 'N';
COMMENT ON COLUMN "gtcreservecomposition"."isconfirmed" IS 'Cado o exemplar volta ela se torna confirmada';

ALTER TABLE "gtcreservecomposition" ALTER COLUMN "reserveid" SET NOT NULL;
ALTER TABLE "gtcreservecomposition" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcreservecomposition" ALTER COLUMN "isconfirmed" SET DEFAULT FALSE;

ALTER TABLE "gtcreservecomposition" ALTER COLUMN "reserveid" SET NOT NULL;
ALTER TABLE "gtcreservecomposition" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcreservecomposition" ADD PRIMARY KEY ("reserveid","itemnumber");

ALTER TABLE "gtcreservecomposition" ADD FOREIGN KEY ("reserveid") REFERENCES "gtcreserve"("reserveid");

ALTER TABLE "gtcreservecomposition" ADD FOREIGN KEY ("itemnumber") REFERENCES "gtcexemplarycontrol"("itemnumber");

----------------------------------------------------------------------
-- --
--
-- Table: gtcexemplarystatushistory
-- Purpose: mantem o historico das trocas de estado
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcexemplarystatushistory" 
(
    "itemnumber"           varchar(20), --Identifica
    "exemplarystatusid"    int4, --Estado do exemplar
    "libraryunitid"        integer,
    "date"                 timestamp, --Cado o exemplar volta ela se torna confirmada
    "operator"             varchar(30)
);

COMMENT ON TABLE "gtcexemplarystatushistory" IS 'mantem o historico das trocas de estado';
COMMENT ON COLUMN "gtcexemplarystatushistory"."itemnumber" IS 'Identifica';
COMMENT ON COLUMN "gtcexemplarystatushistory"."exemplarystatusid" IS 'Estado do exemplar';
COMMENT ON COLUMN "gtcexemplarystatushistory"."date" IS 'Cado o exemplar volta ela se torna confirmada';

ALTER TABLE "gtcexemplarystatushistory" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcexemplarystatushistory" ALTER COLUMN "exemplarystatusid" SET NOT NULL;
ALTER TABLE "gtcexemplarystatushistory" ALTER COLUMN "libraryunitid" SET NOT NULL;
ALTER TABLE "gtcexemplarystatushistory" ALTER COLUMN "date" SET NOT NULL;
ALTER TABLE "gtcexemplarystatushistory" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcexemplarystatushistory" ADD FOREIGN KEY ("exemplarystatusid") REFERENCES "gtcexemplarystatus"("exemplarystatusid");

ALTER TABLE "gtcexemplarystatushistory" ADD FOREIGN KEY ("itemnumber") REFERENCES "gtcexemplarycontrol"("itemnumber");

ALTER TABLE "gtcexemplarystatushistory" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcexemplaryfuturestatusdefined
-- Purpose: 

--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcexemplaryfuturestatusdefined" 
(
    "exemplaryfuturestatusdefinedid"    int4,
    "exemplarystatusid"                 int4,
    "itemnumber"                        varchar(20),
    "applied"                           bool,
    "date"                              timestamp,
    "operator"                          varchar(30),
    "observation"                       text
);

COMMENT ON TABLE "gtcexemplaryfuturestatusdefined" IS '
';

CREATE SEQUENCE "seq_exemplaryfuturestatusdefinedid";
ALTER TABLE "gtcexemplaryfuturestatusdefined" ALTER COLUMN "exemplaryfuturestatusdefinedid" SET DEFAULT NEXTVAL('"seq_exemplaryfuturestatusdefinedid"');

ALTER TABLE "gtcexemplaryfuturestatusdefined" ALTER COLUMN "exemplaryfuturestatusdefinedid" SET NOT NULL;
ALTER TABLE "gtcexemplaryfuturestatusdefined" ADD PRIMARY KEY ("exemplaryfuturestatusdefinedid");

ALTER TABLE "gtcexemplaryfuturestatusdefined" ADD FOREIGN KEY ("exemplarystatusid") REFERENCES "gtcexemplarystatus"("exemplarystatusid");

ALTER TABLE "gtcexemplaryfuturestatusdefined" ADD FOREIGN KEY ("itemnumber") REFERENCES "gtcexemplarycontrol"("itemnumber");

----------------------------------------------------------------------
-- --
--
-- Table: gtcloanbetweenlibrarycomposition
-- Purpose: composi
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcloanbetweenlibrarycomposition" 
(
    "loanbetweenlibraryid"    int4, --Identificador do emprestimo
    "itemnumber"              varchar(20), --Data e hora do empr
    "isconfirmed"             boolean --Data prevista para devolu
);

COMMENT ON TABLE "gtcloanbetweenlibrarycomposition" IS 'composi';
COMMENT ON COLUMN "gtcloanbetweenlibrarycomposition"."loanbetweenlibraryid" IS 'Identificador do emprestimo';
COMMENT ON COLUMN "gtcloanbetweenlibrarycomposition"."itemnumber" IS 'Data e hora do empr';
COMMENT ON COLUMN "gtcloanbetweenlibrarycomposition"."isconfirmed" IS 'Data prevista para devolu';

ALTER TABLE "gtcloanbetweenlibrarycomposition" ALTER COLUMN "loanbetweenlibraryid" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrarycomposition" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrarycomposition" ALTER COLUMN "isconfirmed" SET NOT NULL;

ALTER TABLE "gtcloanbetweenlibrarycomposition" ALTER COLUMN "loanbetweenlibraryid" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrarycomposition" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcloanbetweenlibrarycomposition" ADD PRIMARY KEY ("loanbetweenlibraryid","itemnumber");

ALTER TABLE "gtcloanbetweenlibrarycomposition" ADD FOREIGN KEY ("itemnumber") REFERENCES "gtcexemplarycontrol"("itemnumber");

ALTER TABLE "gtcloanbetweenlibrarycomposition" ADD FOREIGN KEY ("loanbetweenlibraryid") REFERENCES "gtcloanbetweenlibrary"("loanbetweenlibraryid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrequestchangeexemplarystatuscomposition
-- Purpose: quando o administrador permite a requisi
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrequestchangeexemplarystatuscomposition" 
(
    "requestchangeexemplarystatusid"    int,
    "itemnumber"                        varchar(20),
    "exemplaryfuturestatusdefinedid"    int,
    "confirm"                           boolean,
    "date"                              timestamp,
    "applied"                           boolean --quando a requisi
);

COMMENT ON TABLE "gtcrequestchangeexemplarystatuscomposition" IS 'quando o administrador permite a requisi';
COMMENT ON COLUMN "gtcrequestchangeexemplarystatuscomposition"."applied" IS 'quando a requisi';

ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ALTER COLUMN "requestchangeexemplarystatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ALTER COLUMN "confirm" SET DEFAULT FALSE;
ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ALTER COLUMN "date" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ALTER COLUMN "applied" SET DEFAULT false;

ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ALTER COLUMN "requestchangeexemplarystatusid" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ADD PRIMARY KEY ("requestchangeexemplarystatusid","itemnumber");

ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ADD FOREIGN KEY ("requestchangeexemplarystatusid") REFERENCES "gtcrequestchangeexemplarystatus"("requestchangeexemplarystatusid");

ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ADD FOREIGN KEY ("itemnumber") REFERENCES "gtcexemplarycontrol"("itemnumber");

ALTER TABLE "gtcrequestchangeexemplarystatuscomposition" ADD FOREIGN KEY ("exemplaryfuturestatusdefinedid") REFERENCES "gtcexemplaryfuturestatusdefined"("exemplaryfuturestatusdefinedid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcreturnregister
-- Purpose: registra as devolu
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcreturnregister" 
(
    "returnregisterid"    int,
    "returntypeid"        int4,
    "itemnumber"          varchar(20),
    "date"                timestamp,
    "operator"            varchar(30)
);

COMMENT ON TABLE "gtcreturnregister" IS 'registra as devolu';

CREATE SEQUENCE "seq_returnregisterid";
ALTER TABLE "gtcreturnregister" ALTER COLUMN "returnregisterid" SET DEFAULT NEXTVAL('"seq_returnregisterid"');
ALTER TABLE "gtcreturnregister" ALTER COLUMN "returntypeid" SET NOT NULL;
ALTER TABLE "gtcreturnregister" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcreturnregister" ALTER COLUMN "date" SET NOT NULL;
ALTER TABLE "gtcreturnregister" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcreturnregister" ALTER COLUMN "returnregisterid" SET NOT NULL;
ALTER TABLE "gtcreturnregister" ADD PRIMARY KEY ("returnregisterid");

ALTER TABLE "gtcreturnregister" ADD FOREIGN KEY ("itemnumber") REFERENCES "gtcexemplarycontrol"("itemnumber");

ALTER TABLE "gtcreturnregister" ADD FOREIGN KEY ("returntypeid") REFERENCES "gtcreturntype"("returntypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcloan
-- Purpose: emprestimos
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcloan" 
(
    "loanid"                int4, --Identificador do emprestimo
    "loantypeid"            int4,
    "personid"              int4,
    "linkid"                int4, --Grupo que pertence o usu
    "privilegegroupid"      int4,
    "itemnumber"            varchar(20), --N
    "libraryunitid"         int4, --Unidade da biblioteca que emprestou
    "loandate"              timestamp, --Data e hora do empr
    "loanoperator"          varchar(30), --Login do miolo que emprestou o material
    "returnforecastdate"    timestamp, --Data prevista para devolu
    "returndate"            timestamp, --Data da devolu
    "returnoperator"        varchar(30), --Login do miolo que devolveu o material
    "renewalamount"         int4, --Quantidade de renova
    "renewalwebamount"      int4, --Valor das renova
    "renewalwebbonus"       boolean --Se este campo for verdadeiro, reinicia o campo renewalWebAmount a cada renova
);

COMMENT ON TABLE "gtcloan" IS 'emprestimos';
COMMENT ON COLUMN "gtcloan"."loanid" IS 'Identificador do emprestimo';
COMMENT ON COLUMN "gtcloan"."linkid" IS 'Grupo que pertence o usu';
COMMENT ON COLUMN "gtcloan"."itemnumber" IS 'N';
COMMENT ON COLUMN "gtcloan"."libraryunitid" IS 'Unidade da biblioteca que emprestou';
COMMENT ON COLUMN "gtcloan"."loandate" IS 'Data e hora do empr';
COMMENT ON COLUMN "gtcloan"."loanoperator" IS 'Login do miolo que emprestou o material';
COMMENT ON COLUMN "gtcloan"."returnforecastdate" IS 'Data prevista para devolu';
COMMENT ON COLUMN "gtcloan"."returndate" IS 'Data da devolu';
COMMENT ON COLUMN "gtcloan"."returnoperator" IS 'Login do miolo que devolveu o material';
COMMENT ON COLUMN "gtcloan"."renewalamount" IS 'Quantidade de renova';
COMMENT ON COLUMN "gtcloan"."renewalwebamount" IS 'Valor das renova';
COMMENT ON COLUMN "gtcloan"."renewalwebbonus" IS 'Se este campo for verdadeiro, reinicia o campo renewalWebAmount a cada renova';

CREATE SEQUENCE "seq_loanid";
ALTER TABLE "gtcloan" ALTER COLUMN "loanid" SET DEFAULT NEXTVAL('"seq_loanid"');
ALTER TABLE "gtcloan" ALTER COLUMN "loantypeid" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "personid" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "linkid" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "privilegegroupid" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "itemnumber" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "loandate" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "loanoperator" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "returnforecastdate" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "renewalamount" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "renewalwebamount" SET NOT NULL;
ALTER TABLE "gtcloan" ALTER COLUMN "renewalwebbonus" SET NOT NULL;

ALTER TABLE "gtcloan" ALTER COLUMN "loanid" SET NOT NULL;
ALTER TABLE "gtcloan" ADD PRIMARY KEY ("loanid");

ALTER TABLE "gtcloan" ADD FOREIGN KEY ("linkid") REFERENCES "baslink"("linkid");

ALTER TABLE "gtcloan" ADD FOREIGN KEY ("personid") REFERENCES "basperson"("personid");

ALTER TABLE "gtcloan" ADD FOREIGN KEY ("loantypeid") REFERENCES "gtcloantype"("loantypeid");

ALTER TABLE "gtcloan" ADD FOREIGN KEY ("itemnumber") REFERENCES "gtcexemplarycontrol"("itemnumber");

ALTER TABLE "gtcloan" ADD FOREIGN KEY ("libraryunitid") REFERENCES "gtclibraryunit"("libraryunitid");

ALTER TABLE "gtcloan" ADD FOREIGN KEY ("privilegegroupid") REFERENCES "gtcprivilegegroup"("privilegegroupid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcrenew
-- Purpose: renovacao
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcrenew" 
(
    "renewid"               int4, --Identifica
    "loanid"                int4, --C
    "renewtypeid"           integer,
    "renewdate"             timestamp, --Data e hora da renovacao
    "returnforecastdate"    timestamp, --Data da inclus
    "operator"              varchar(30) --Usu
);

COMMENT ON TABLE "gtcrenew" IS 'renovacao';
COMMENT ON COLUMN "gtcrenew"."renewid" IS 'Identifica';
COMMENT ON COLUMN "gtcrenew"."loanid" IS 'C';
COMMENT ON COLUMN "gtcrenew"."renewdate" IS 'Data e hora da renovacao';
COMMENT ON COLUMN "gtcrenew"."returnforecastdate" IS 'Data da inclus';
COMMENT ON COLUMN "gtcrenew"."operator" IS 'Usu';

CREATE SEQUENCE "seq_renewid";
ALTER TABLE "gtcrenew" ALTER COLUMN "renewid" SET DEFAULT NEXTVAL('"seq_renewid"');
ALTER TABLE "gtcrenew" ALTER COLUMN "loanid" SET NOT NULL;
ALTER TABLE "gtcrenew" ALTER COLUMN "returnforecastdate" SET NOT NULL;
ALTER TABLE "gtcrenew" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcrenew" ALTER COLUMN "renewid" SET NOT NULL;
ALTER TABLE "gtcrenew" ADD PRIMARY KEY ("renewid");

ALTER TABLE "gtcrenew" ADD FOREIGN KEY ("loanid") REFERENCES "gtcloan"("loanid");

ALTER TABLE "gtcrenew" ADD FOREIGN KEY ("renewtypeid") REFERENCES "gtcrenewtype"("renewtypeid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcfine
-- Purpose: multas
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcfine" 
(
    "fineid"          int4, --Identificador da multa
    "loanid"          int4, --Identifica
    "begindate"       timestamp, --Data do registor da multa
    "value"           numeric(10,2), --Valor total da multa. Deve vir calculada.
    "finestatusid"    int4, --Estado da multa. 1 - Em aberto. 2 - Abonada. 3 - Pago 4 - Pagamento via boleto
    "enddate"         timestamp, --Data do pagamento
    "observation"     text
);

COMMENT ON TABLE "gtcfine" IS 'multas';
COMMENT ON COLUMN "gtcfine"."fineid" IS 'Identificador da multa';
COMMENT ON COLUMN "gtcfine"."loanid" IS 'Identifica';
COMMENT ON COLUMN "gtcfine"."begindate" IS 'Data do registor da multa';
COMMENT ON COLUMN "gtcfine"."value" IS 'Valor total da multa. Deve vir calculada.';
COMMENT ON COLUMN "gtcfine"."finestatusid" IS 'Estado da multa. 1 - Em aberto. 2 - Abonada. 3 - Pago 4 - Pagamento via boleto';
COMMENT ON COLUMN "gtcfine"."enddate" IS 'Data do pagamento';

CREATE SEQUENCE "seq_fineid";
ALTER TABLE "gtcfine" ALTER COLUMN "fineid" SET DEFAULT NEXTVAL('"seq_fineid"');
ALTER TABLE "gtcfine" ALTER COLUMN "loanid" SET NOT NULL;
ALTER TABLE "gtcfine" ALTER COLUMN "begindate" SET NOT NULL;
ALTER TABLE "gtcfine" ALTER COLUMN "value" SET NOT NULL;
ALTER TABLE "gtcfine" ALTER COLUMN "finestatusid" SET NOT NULL;

ALTER TABLE "gtcfine" ALTER COLUMN "fineid" SET NOT NULL;
ALTER TABLE "gtcfine" ADD PRIMARY KEY ("fineid");

ALTER TABLE "gtcfine" ADD FOREIGN KEY ("loanid") REFERENCES "gtcloan"("loanid");

ALTER TABLE "gtcfine" ADD FOREIGN KEY ("finestatusid") REFERENCES "gtcfinestatus"("finestatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcfinestatushistory
-- Purpose: mantem o historico das trocas de estado
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcfinestatushistory" 
(
    "fineid"          int4, --Identifica
    "finestatusid"    integer, --Estado da reserva
    "date"            timestamp, --Cado o exemplar volta ela se torna confirmada
    "operator"        varchar(30),
    "observation"     text
);

COMMENT ON TABLE "gtcfinestatushistory" IS 'mantem o historico das trocas de estado';
COMMENT ON COLUMN "gtcfinestatushistory"."fineid" IS 'Identifica';
COMMENT ON COLUMN "gtcfinestatushistory"."finestatusid" IS 'Estado da reserva';
COMMENT ON COLUMN "gtcfinestatushistory"."date" IS 'Cado o exemplar volta ela se torna confirmada';

ALTER TABLE "gtcfinestatushistory" ALTER COLUMN "fineid" SET NOT NULL;
ALTER TABLE "gtcfinestatushistory" ALTER COLUMN "finestatusid" SET NOT NULL;
ALTER TABLE "gtcfinestatushistory" ALTER COLUMN "date" SET NOT NULL;
ALTER TABLE "gtcfinestatushistory" ALTER COLUMN "operator" SET NOT NULL;

ALTER TABLE "gtcfinestatushistory" ADD FOREIGN KEY ("fineid") REFERENCES "gtcfine"("fineid");

ALTER TABLE "gtcfinestatushistory" ADD FOREIGN KEY ("finestatusid") REFERENCES "gtcfinestatus"("finestatusid");

----------------------------------------------------------------------
-- --
--
-- Table: gtcemailcontroldelayedloan
-- Purpose: 
--
-- --
----------------------------------------------------------------------

CREATE TABLE "gtcemailcontroldelayedloan" 
(
    "loanid"        int4,
    "lastsent"      timestamp,
    "amountsent"    int4
);

ALTER TABLE "gtcemailcontroldelayedloan" ALTER COLUMN "lastsent" SET NOT NULL;
ALTER TABLE "gtcemailcontroldelayedloan" ALTER COLUMN "amountsent" SET NOT NULL;

ALTER TABLE "gtcemailcontroldelayedloan" ADD FOREIGN KEY ("loanid") REFERENCES "gtcloan"("loanid");

