\set ON_ERROR_STOP on

\c nubarchiva
SET SCHEMA 'nuba00001';

/*
 *********************************************
 * source: nuba-commons.01.create_table.sql
 *                                           *
 *      This is an auto generated file.      *
 *     !!!!! DO NOT EDIT DIRECTLY. !!!!!     *
 *                                           *
 *********************************************
 */

CREATE TABLE SETTING
(
  IDSETTING BIGSERIAL,
  NAME      VARCHAR(100),
  VALUE     VARCHAR(2000),
  
  CONSTRAINT SETTING_PK PRIMARY KEY (IDSETTING)
);

CREATE TABLE USERPREFERENCES (
    IDENTITY        BIGSERIAL,
    IDUSER          VARCHAR(50),
    IDPREFERENCE    VARCHAR(255),
    PREFERENCEVALUE VARCHAR(2000),
    
	CONSTRAINT USERPREFERENCES_PK PRIMARY KEY (IDENTITY)
);

CREATE TABLE AUDITLOG (
    IDAUDITLOG        	BIGSERIAL,
    CREATIONDATE	  	TIMESTAMP,
	USERNAME		  	VARCHAR(255),
	ENTITYID 		  	VARCHAR(255),
	ENTITYNAME 		  	VARCHAR(255),
	PROPERTYNAME	  	VARCHAR(255),
	PROPERTYOLDVALUE	TEXT,
	PROPERTYNEWVALUE	TEXT,
	OPERATIONTYPE		VARCHAR(2),
	CONSTRAINT AUDITLOG_PK PRIMARY KEY (IDAUDITLOG)
);

CREATE UNIQUE INDEX AUDITLOG_IX_DATE
    ON AUDITLOG (CREATIONDATE DESC, IDAUDITLOG);
CREATE UNIQUE INDEX AUDITLOG_IX_ENTITY
    ON AUDITLOG (ENTITYNAME, ENTITYID, CREATIONDATE DESC, IDAUDITLOG);
CREATE UNIQUE INDEX AUDITLOG_IX_USER
    ON AUDITLOG (USERNAME, CREATIONDATE DESC, IDAUDITLOG);

CREATE TABLE USERS
(
IDUSER BIGSERIAL,
FULLNAME VARCHAR(60 ) NOT NULL, 
USERNAME VARCHAR(50 ) NOT NULL, 
PASSWORD VARCHAR(50 ) NOT NULL, 
ENABLED BOOLEAN DEFAULT true NOT NULL,

CONSTRAINT USERS_PK PRIMARY KEY (IDUSER),
CONSTRAINT USERS_UK UNIQUE (USERNAME)
);

CREATE TABLE AUTHORITIES 
(
IDAUTHORITY BIGSERIAL,
USERNAME VARCHAR(50) NOT NULL, 
AUTHORITY VARCHAR(50 ) NOT NULL, 
ORGANIZATION VARCHAR(50) DEFAULT NULL,

CONSTRAINT AUTHORITIES_PK PRIMARY KEY (IDAUTHORITY)
);

CREATE TABLE REVINFO (
  REV BIGSERIAL,
  REVTSTMP NUMERIC,
        CONSTRAINT REVINFO_PK PRIMARY KEY (REV)
);

CREATE TABLE WORKSTATUS
(
  IDWORK BIGSERIAL,
  WORKTYPE   VARCHAR(30) NOT NULL,
  STATUS   VARCHAR(30) NOT NULL,
  INFO   VARCHAR(512),
  EXCEPTION   VARCHAR(512),
  CONSTRAINT WORKSTATUS_PK PRIMARY KEY (IDWORK)
);

CREATE INDEX WORKSTATUS_IX_WORKTYPE
    ON WORKSTATUS (WORKTYPE);

/*
 *********************************************
 * source: nuba-opac.01.create_table.sql
 *                                           *
 *      This is an auto generated file.      *
 *     !!!!! DO NOT EDIT DIRECTLY. !!!!!     *
 *                                           *
 *********************************************
 */

CREATE TABLE ARCHIVE
(
   IDARCHIVE   BIGSERIAL,
   ARCHIVE     VARCHAR(50) NOT NULL,
   ARCHIVECODE VARCHAR(20),
   WEBSITE     VARCHAR(256),
   ROOTNODE    INTEGER,
   IMAGEURL    VARCHAR(512),
   ICONURL     VARCHAR(512),
   NOTES       VARCHAR(2000),
   CONSTRAINT ARCHIVE_PK PRIMARY KEY (IDARCHIVE)
);

CREATE TABLE DESCRIPTIONLEVEL
(
   IDDESCRIPTIONLEVEL BIGSERIAL,
   DESCRIPTIONLEVEL   VARCHAR(40) NOT NULL,
   HIERARCHY          SMALLINT,
   SHORTDESC          CHAR(2) NOT NULL,
   CONSTRAINT DESCRIPTIONLEVEL_PK PRIMARY KEY (IDDESCRIPTIONLEVEL)
);

CREATE TABLE ITEM
(
   IDITEM BIGSERIAL,
   MARCXML            TEXT,
   SIGNATURE          VARCHAR(120),
   TITLE              VARCHAR(240),
   PATH               VARCHAR(512),
   IDARCHIVE          INTEGER,
   IDPARENT           INTEGER,
   IDDESCRIPTIONLEVEL INTEGER,
   STATE              CHAR(1) NOT NULL,
   LASTMODIFIED       TIMESTAMP,
   VISIBILITY         CHAR(1) NOT NULL,
   RECORDCENTER       VARCHAR(255),
   CREATIONDATE       TIMESTAMP,
   EXPURGEDDATE       TIMESTAMP,
   DELETEDDATE        TIMESTAMP,
   CONSTRAINT ITEM_PK PRIMARY KEY (IDITEM)
);
CREATE INDEX ITEM_IX_IDPARENT   ON ITEM (IDPARENT, IDITEM);
CREATE INDEX ITEM_IX_PATH       ON ITEM (PATH);
CREATE INDEX ITEM_IX_IDARCHIVE  ON ITEM (IDARCHIVE, IDITEM);
CREATE INDEX ITEM_IX_IDARCLEVEL ON ITEM (IDDESCRIPTIONLEVEL,IDITEM);

CREATE TABLE PLACE
(
	IDPLACE      BIGSERIAL,
	OFICIALNAME  VARCHAR(150) NOT NULL,
	ORIGINALNAME VARCHAR(150),
	NOTES        VARCHAR(150),
	PLACETYPE    VARCHAR(10)  NOT NULL,

	CONSTRAINT PLACE_PK PRIMARY KEY (IDPLACE)
);

CREATE TABLE PLACERELATION
(
	IDPLACEA INTEGER NOT NULL,
	RELATION CHAR(3) NOT NULL,
	IDPLACEB INTEGER NOT NULL,

	CONSTRAINT PLACERELATION_PK PRIMARY KEY (IDPLACEA, RELATION, IDPLACEB)
);

CREATE TABLE FIXEDSEARCH
(
   IDFIXEDSEARCH BIGSERIAL,
   TITLE         VARCHAR(128) NOT NULL,
   FSEARCHTYPE	 VARCHAR(64),
   ENABLED       BOOLEAN DEFAULT true NOT NULL,
   CONSTRAINT FIXEDSEARCH_PK PRIMARY KEY (IDFIXEDSEARCH)
);

CREATE TABLE FIXEDTERM
(
    IDFIXEDTERM    BIGSERIAL,
    IDFIXEDSEARCH  INTEGER NOT NULL,
    SEARCHTERMTYPE VARCHAR(64) NOT NULL,
    VALUE          VARCHAR(2000),
    CONDITION	   VARCHAR(2000),
    CONSTRAINT FIXEDTERM_PK PRIMARY KEY (IDFIXEDTERM)
);

CREATE TABLE FIXEDSEARCHCONDITION
(
	IDFIXEDSEARCHCONDITION    BIGSERIAL,
	IDFIXEDSEARCH			  INTEGER NOT NULL,
	CONDITIONTYPE			  VARCHAR(64),
	CONDITIONVALUE			  VARCHAR(2000),
	CONSTRAINT FIXEDSEARCHCONDITION_PK PRIMARY KEY (IDFIXEDSEARCHCONDITION)
);

CREATE TABLE MONOGRAPHPORTAL
(
    IDMONOGRAPHPORTAL   BIGSERIAL,
    TITLE                  VARCHAR(64) NOT NULL,
    SHORTDESC              VARCHAR(8),
    CONFIGURATION          TEXT,
    IDFIXEDSEARCH          INTEGER NOT NULL,
    CONSTRAINT MONOGRAPHPORTAL_PK PRIMARY KEY (IDMONOGRAPHPORTAL)
);

CREATE UNIQUE INDEX MONPORTAL_IX_SHORTDESC
    ON MONOGRAPHPORTAL (SHORTDESC);

CREATE TABLE ATTACHMENT
(
	IDATTACH BIGSERIAL,
	SOURCE   VARCHAR(512) NOT NULL,
	IDITEM   INTEGER,
    SORDER   INTEGER,
	MANAGED  BOOLEAN DEFAULT false NOT NULL,
    EXISTURL  BOOLEAN,
	CONSTRAINT ATTACHMENT_PK PRIMARY KEY (IDATTACH)
);

CREATE INDEX ATTACHMENT_IX_IDITEM
    ON ATTACHMENT (IDITEM);

CREATE INDEX ATTACHMENT_IX_EXISTURL
    ON ATTACHMENT (EXISTURL);

CREATE INDEX ATTACHMENT_IX_SOURCE
    ON ATTACHMENT (SOURCE);

/*
 *********************************************
 * source: nuba-desar.01.create_table.sql
 *                                           *
 *      This is an auto generated file.      *
 *     !!!!! DO NOT EDIT DIRECTLY. !!!!!     *
 *                                           *
 *********************************************
 */

CREATE TABLE USERLIST
(
   IDUSERLIST  BIGSERIAL,
   KEY         VARCHAR(300) NOT NULL,
   DESCRIPTION VARCHAR(2048) NOT NULL,
   CONSTRAINT LIST_PK PRIMARY KEY (IDUSERLIST)
);

CREATE UNIQUE INDEX USERLIST_IX_KEY ON USERLIST (KEY);

CREATE TABLE USERLISTVALUE
(
   IDUSERLISTVALUE BIGSERIAL,
   IDUSERLIST      INTEGER NOT NULL,
   USERKEY         VARCHAR(300) NOT NULL,
   USERVALUE       VARCHAR(2048) NOT NULL,
   CONSTRAINT USRLISTVALUE_PK PRIMARY KEY (IDUSERLISTVALUE)
);

CREATE UNIQUE INDEX USERLISTVALUE_IX_KEY ON USERLISTVALUE (IDUSERLIST, USERKEY);

CREATE TABLE FORM
(
   IDFORM      BIGSERIAL,
   TITLE       VARCHAR(50) NOT NULL,
   DESCRIPTION VARCHAR(300),
   TYPE        CHAR(1) DEFAULT 'D' NOT NULL,
   SUBTYPE     VARCHAR(2),
   SORDER      SMALLINT DEFAULT 1,
   CONSTRAINT FORM_PK PRIMARY KEY (IDFORM)
);

CREATE TABLE CLASSEDITOR
(
   IDCLASSEDITOR BIGSERIAL,
   CLASSNAME     VARCHAR(100) NOT NULL,
   CONSTRAINT CLASSEDITOR_PK PRIMARY KEY (IDCLASSEDITOR)
);

CREATE TABLE FIELD
(
   IDFIELD       BIGSERIAL,
   IDFORM        INTEGER NOT NULL,
   LABEL         VARCHAR(300),
   IDCLASSEDITOR INTEGER NOT NULL,
   REQUIERED     BOOLEAN DEFAULT false NOT NULL ,
   EDITABLE      BOOLEAN DEFAULT true NOT NULL,
   VISIBLE       BOOLEAN DEFAULT true NOT NULL,
   DEFAULTVALUE  VARCHAR(2048),
   INITEDITOR    VARCHAR(2048),
   SORDER        SMALLINT NOT NULL,
   CONSTRAINT FIELDS_PK PRIMARY KEY (IDFIELD)
);

CREATE TABLE ITEMEDIT
(
   IDITEM     INTEGER NOT NULL,
   IDFORM     INTEGER,
   ORIGINALID VARCHAR(15),
   ORIGIN 	  VARCHAR(20),
   IMPORTHASHCODE INTEGER,
   LASTIMPORTDATE TIMESTAMP,
   CONSTRAINT ITEMEDIT_PK PRIMARY KEY (IDITEM)
);

CREATE INDEX ITEMEDIT_IX_ORIGINALID ON ITEMEDIT (ORIGIN, ORIGINALID);

/*
 *********************************************
 * source: nuba-requests.01.create_table.sql
 *                                           *
 *      This is an auto generated file.      *
 *     !!!!! DO NOT EDIT DIRECTLY. !!!!!     *
 *                                           *
 *********************************************
 */

CREATE TABLE ITEMSREQUEST (
    IDENTITY        BIGSERIAL,
    DESCRIPTION     VARCHAR(2048),
    REASON          VARCHAR(2048),
    PETITIONER      VARCHAR(255),
    DEPARTMENT      VARCHAR(255),
    REQUESTDATE     TIMESTAMP,
    RESOLUTIONDATE  TIMESTAMP,
    ARCHIVECOMMENTS VARCHAR(1024),
    STATE           VARCHAR(255),
    TYPE            VARCHAR(255),
    PRIORITY        VARCHAR(255),
    NOTES           VARCHAR(1024),
    ADDRESS         VARCHAR(255),
    DNI             VARCHAR(255),
    PHONE           VARCHAR(255),
    EMAIL           VARCHAR(255),
	PRIMARY KEY (IDENTITY));

CREATE TABLE ITEMSREQUEST_ITEM(
    ITEMSREQUEST_ID INTEGER NOT NULL,
    IDITEM          INTEGER NOT NULL,
    BORROWDATE      TIMESTAMP,
    RETURNDATELIMIT TIMESTAMP,
    STATE           VARCHAR(255),
	PRIMARY KEY (ITEMSREQUEST_ID, IDITEM));

CREATE TABLE ITEMBORROW(
    IDENTITY        BIGSERIAL,
    IDITEMSREQUEST  INTEGER,
    IDITEM          INTEGER,
    STATE           VARCHAR(255),
    RECORDCENTER    VARCHAR(255),
    BORROWDATE      TIMESTAMP,
    RETURNDATE      TIMESTAMP,
    RETURNDATELIMIT TIMESTAMP,
	PRIMARY KEY (IDENTITY));

/*
 *********************************************
 * source: nuba-submissions.01.create_table.sql
 *                                           *
 *      This is an auto generated file.      *
 *     !!!!! DO NOT EDIT DIRECTLY. !!!!!     *
 *                                           *
 *********************************************
 */

CREATE TABLE SUBMISSION (
    IDENTITY BIGSERIAL,
    SECTION      VARCHAR(255),
    DOCUMENTTYPE VARCHAR(2000),
    SERIE VARCHAR(255),
    OBSERVATIONS VARCHAR(2000),
    CREATIONTIME TIMESTAMP,
    USERNAME     VARCHAR(255),
    STATE        VARCHAR(255),
    DEPARTMENT   VARCHAR(255),
    ACCESSIONNUMBER   VARCHAR(255),
    ACCONDICIONATEDTIME TIMESTAMP,
    ATTACHMENTSOURCE    VARCHAR(512) NOT NULL,
	PRIMARY KEY (IDENTITY));
         
CREATE TABLE SUBMISSION_DETAIL (
    IDENTITY BIGSERIAL,
    IDORDER          VARCHAR(255),
    IDEXPEDIENT      VARCHAR(255),
    SERIE VARCHAR(255), 
    DESCRIPTION      VARCHAR(255),
    YEARS            VARCHAR(255),
    SOURCECALLNUMBER VARCHAR(255),
    EXPURGATION      VARCHAR(255),
    IDSUBMISSION     INTEGER,
	PRIMARY KEY (IDENTITY));


/*
 *********************************************
 * source: nuba-authorities.01.create_table.sql
 *                                           *
 *      This is an auto generated file.      *
 *     !!!!! DO NOT EDIT DIRECTLY. !!!!!     *
 *                                           *
 *********************************************
 */


CREATE TABLE AUTHORITY (
	IDAUTHORITY   BIGSERIAL,
    TITLE         VARCHAR(240) NOT NULL,
    MARCXML       TEXT NOT NULL,
    AUTHORITYTYPE VARCHAR(10) NOT NULL,
    AUTHORIZEDFORM BOOLEAN,
	CONSTRAINT AUTHORITY_PK PRIMARY KEY (IDAUTHORITY)
);

CREATE UNIQUE INDEX AUTHORITY_IX_TYPE_AUTH  ON AUTHORITY (AUTHORITYTYPE, TITLE);

CREATE TABLE AUTHORITYRELATION
(	
	IDAUTHORITYRELATION BIGSERIAL,
	IDAUTHORITYA INTEGER NOT NULL, 
	RELATION     VARCHAR(3) NOT NULL,
	IDAUTHORITYB INTEGER NOT NULL, 

	CONSTRAINT AUTHORITYRELATION_PK PRIMARY KEY(IDAUTHORITYRELATION)
);

CREATE UNIQUE INDEX AUTHORITYRELATION_UN ON AUTHORITYRELATION (IDAUTHORITYA, RELATION, IDAUTHORITYB);

/*
 *********************************************
 * source: nuba-disposals.01.create_table.sql
 *                                           *
 *      This is an auto generated file.      *
 *     !!!!! DO NOT EDIT DIRECTLY. !!!!!     *
 *                                           *
 *********************************************
 */


CREATE TABLE DISPOSAL (
	IDDISPOSAL   BIGSERIAL,
    TITLE         VARCHAR(240) NOT NULL,
    MARCXML       TEXT NOT NULL,
	CONSTRAINT DISPOSAL_PK PRIMARY KEY (IDDISPOSAL)
);

DO
$$
DECLARE
    sequence_record RECORD;
BEGIN
    FOR sequence_record IN
        SELECT sequence_name
        FROM information_schema.sequences
        WHERE sequence_schema = 'nuba00001' -- Replace 'nuba00001' with your schema name
    LOOP
        EXECUTE 'GRANT ALL ON SEQUENCE ' || sequence_record.sequence_name || ' TO nubauser';
    END LOOP;
END
$$;
