structure.sql

user_id INTEGER NOT NULL,
updated_date timestamp without time zone NOT NULL,
CONSTRAINT listinstance_history_pkey PRIMARY KEY (listinstance_history_id)
)
WITH ( OIDS=FALSE );

DROP SEQUENCE IF EXISTS listinstance_history_details_id_seq;
CREATE SEQUENCE listinstance_history_details_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

DROP TABLE IF EXISTS listinstance_history_details;
CREATE TABLE listinstance_history_details
(
listinstance_history_details_id bigint NOT NULL DEFAULT nextval('listinstance_history_details_id_seq'::regclass),
listinstance_history_id bigint NOT NULL,
coll_id character varying(50) NOT NULL,
res_id bigint NOT NULL,
listinstance_type character varying(50) DEFAULT 'DOC'::character varying,
sequence bigint NOT NULL,
item_id INTEGER,
item_type character varying(255) NOT NULL,
item_mode character varying(50) NOT NULL,
added_by_user INTEGER,
visible character varying(1) NOT NULL DEFAULT 'Y'::bpchar,
viewed bigint,
difflist_type character varying(50),
process_date timestamp without time zone,
process_comment character varying(255),
requested_signature boolean default false,
signatory boolean default false,
CONSTRAINT listinstance_history_details_pkey PRIMARY KEY (listinstance_history_details_id)
) WITH ( OIDS=FALSE );

/* SHIPPING TEMPLATES */
DROP TABLE IF EXISTS shipping_templates;
CREATE TABLE shipping_templates
(
id serial NOT NULL,
label character varying(64) NOT NULL,
description character varying(255) NOT NULL,
options json DEFAULT '{}',
fee json DEFAULT '{}',
entities jsonb DEFAULT '{}',
account json DEFAULT '{}',
CONSTRAINT shipping_templates_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE shippings
(
id serial NOT NULL,
user_id INTEGER NOT NULL,
document_id INTEGER NOT NULL,
document_type character varying(255) NOT NULL,
options json DEFAULT '{}',
fee FLOAT NOT NULL,
recipient_entity_id INTEGER NOT NULL,
recipients jsonb DEFAULT '[]',
account_id character varying(64) NOT NULL,
creation_date timestamp without time zone NOT NULL,
CONSTRAINT shippings_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

--VIEWS
-- view for letterbox
CREATE OR REPLACE VIEW res_view_letterbox AS
SELECT r.res_id,
       r.type_id,
       r.policy_id,
       r.cycle_id,
       d.description AS type_label,
       d.doctypes_first_level_id,
       dfl.doctypes_first_level_label,
       dfl.css_style AS doctype_first_level_style,
       d.doctypes_second_level_id,
       dsl.doctypes_second_level_label,
       dsl.css_style AS doctype_second_level_style,
       r.format,
       r.typist,
       r.creation_date,
       r.modification_date,
       r.docserver_id,
       r.path,
       r.filename,
       r.fingerprint,
       r.filesize,
       r.status,
       r.work_batch,
       r.doc_date,
       r.external_id,
       r.departure_date,
       r.opinion_limit_date,
       r.barcode,
       r.initiator,
       r.destination,
       r.dest_user,
       r.confidentiality,
       r.category_id,
       r.alt_identifier,
       r.admission_date,
       r.process_limit_date,
       r.closing_date,
       r.alarm1_date,
       r.alarm2_date,
       r.flag_alarm1,
       r.flag_alarm2,
       r.subject,
       r.priority,
       r.locker_user_id,
       r.locker_time,
       r.custom_fields,
       r.retention_frozen,
       r.binding,
       en.entity_label,
       en.entity_type AS entitytype
FROM doctypes d,
     doctypes_first_level dfl,
     doctypes_second_level dsl,
     res_letterbox r
    LEFT JOIN entities en ON r.destination::text = en.entity_id::text
WHERE r.type_id = d.type_id AND d.doctypes_first_level_id = dfl.doctypes_first_level_id AND d.doctypes_second_level_id = dsl.doctypes_second_level_id;

/* ORDER ON CHRONO */
CREATE OR REPLACE FUNCTION order_alphanum(text) RETURNS text AS $$
declare
    tmp text;
begin
    tmp := $1;
    tmp := tmp || 'Z';
    tmp := regexp_replace(tmp, E'(\\D)', E'\\1/', 'g');

    IF count(regexp_match(tmp, E'(\\D(\\d{8})\\D)')) > 0 THEN
        tmp := regexp_replace(tmp, E'(\\D)(\\d{8}\\D)', E'\\10\\2', 'g');
    END IF;
    IF count(regexp_match(tmp, E'(\\D)(\\d{7}\\D)')) > 0 THEN
        tmp := regexp_replace(tmp, E'(\\D)(\\d{7}\\D)', E'\\100\\2', 'g');
    END IF;
    IF count(regexp_match(tmp, E'(\\D)(\\d{6}\\D)')) > 0 THEN
        tmp := regexp_replace(tmp, E'(\\D)(\\d{6}\\D)', E'\\1000\\2', 'g');
    END IF;
    IF count(regexp_match(tmp, E'(\\D)(\\d{5}\\D)')) > 0 THEN
        tmp := regexp_replace(tmp, E'(\\D)(\\d{5}\\D)', E'\\10000\\2', 'g');
    END IF;
    IF count(regexp_match(tmp, E'(\\D)(\\d{4}\\D)')) > 0 THEN
        tmp := regexp_replace(tmp, E'(\\D)(\\d{4}\\D)', E'\\100000\\2', 'g');
    END IF;
    IF count(regexp_match(tmp, E'(\\D)(\\d{3}\\D)')) > 0 THEN
        tmp := regexp_replace(tmp, E'(\\D)(\\d{3}\\D)', E'\\1000000\\2', 'g');
    END IF;
    IF count(regexp_match(tmp, E'(\\D)(\\d{2}\\D)')) > 0 THEN
        tmp := regexp_replace(tmp, E'(\\D)(\\d{2}\\D)', E'\\10000000\\2', 'g');
    END IF;
    IF count(regexp_match(tmp, E'(\\D)(\\d{1}\\D)')) > 0 THEN
        tmp := regexp_replace(tmp, E'(\\D)(\\d{1}\\D)', E'\\100000000\\2', 'g');
    END IF;

    RETURN tmp;
end;
$$ LANGUAGE plpgsql;


CREATE TABLE message_exchange
(
  message_id text NOT NULL,
  schema text,
  type text NOT NULL,
  status text NOT NULL,
  
  date timestamp NOT NULL,
  reference text NOT NULL,
  
  account_id integer,
  sender_org_identifier text NOT NULL,
  sender_org_name text,
  recipient_org_identifier text NOT NULL,
  recipient_org_name text,

  archival_agreement_reference text,
  reply_code text,
  operation_date timestamp,
  reception_date timestamp,
  
  related_reference text,
  request_reference text,
  reply_reference text,
  derogation boolean,
  
  data_object_count integer,
  size numeric,
  
  data text,
  
  active boolean,
  archived boolean,
  
  res_id_master numeric default NULL,

  docserver_id character varying(32) DEFAULT NULL,
  path character varying(255) DEFAULT NULL,
  filename character varying(255) DEFAULT NULL,
  fingerprint character varying(255) DEFAULT NULL,
  filesize bigint,
  file_path text default NULL,

  PRIMARY KEY ("message_id")
)
WITH (
  OIDS=FALSE
);

CREATE TABLE unit_identifier
(
  message_id text NOT NULL,
  tablename text NOT NULL,
  res_id text NOT NULL,
  disposition text default NULL
);

DROP TABLE IF EXISTS users_baskets_preferences;
CREATE TABLE users_baskets_preferences
(
  id serial NOT NULL,
  user_serial_id integer NOT NULL,
  group_serial_id integer NOT NULL,
  basket_id character varying(32) NOT NULL,
  display boolean NOT NULL,
  color character varying(16),
  CONSTRAINT users_baskets_preferences_pkey PRIMARY KEY (id),
  CONSTRAINT users_baskets_preferences_key UNIQUE (user_serial_id, group_serial_id, basket_id)
)
WITH (OIDS=FALSE);


-- convert working table
DROP TABLE IF EXISTS convert_stack;
CREATE TABLE convert_stack
(
  coll_id character varying(32) NOT NULL,
  res_id bigint NOT NULL,
  convert_format character varying(32) NOT NULL DEFAULT 'pdf'::character varying,
  cnt_retry integer,
  status character(1) NOT NULL,
  work_batch bigint,
  regex character varying(32),
  CONSTRAINT convert_stack_pkey PRIMARY KEY (coll_id, res_id, convert_format)
)
WITH (OIDS=FALSE);

CREATE TABLE password_rules
(
  id serial,
  label character varying(64) NOT NULL,
  "value" integer NOT NULL,
  enabled boolean DEFAULT FALSE NOT NULL,
  CONSTRAINT password_rules_pkey PRIMARY KEY (id),
  CONSTRAINT password_rules_label_key UNIQUE (label)
)
WITH (OIDS=FALSE);

CREATE TABLE password_history
(
  id serial,
  user_serial_id INTEGER NOT NULL,
  password character varying(255) NOT NULL,
  CONSTRAINT password_history_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE contacts_filling
(
  id serial NOT NULL,
  enable boolean NOT NULL,
  first_threshold int NOT NULL,
  second_threshold int NOT NULL,
  CONSTRAINT contacts_filling_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

/* Sender/Recipient */
DROP TABLE IF EXISTS resource_contacts;
CREATE TABLE resource_contacts
(
  id serial NOT NULL,
  res_id int NOT NULL,
  item_id int NOT NULL,
  type character varying(32) NOT NULL,
  mode character varying(32) NOT NULL,
  CONSTRAINT resource_contacts_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE configurations
(
id serial NOT NULL,
privilege character varying(64) NOT NULL,
value json DEFAULT '{}' NOT NULL,
CONSTRAINT configuration_pkey PRIMARY KEY (id),
CONSTRAINT configuration_unique_key UNIQUE (privilege)
)
WITH (OIDS=FALSE);

CREATE TABLE emails
(
id serial NOT NULL,
user_id INTEGER NOT NULL,
sender json DEFAULT '{}' NOT NULL,
recipients json DEFAULT '[]' NOT NULL,
cc json DEFAULT '[]' NOT NULL,
cci json DEFAULT '[]' NOT NULL,
object character varying(256),
body text,
document json,
is_html boolean NOT NULL DEFAULT TRUE,
status character varying(16) NOT NULL,
message_exchange_id text,
creation_date timestamp without time zone NOT NULL,
send_date timestamp without time zone,
CONSTRAINT emails_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE exports_templates
(
id serial NOT NULL,
user_id INTEGER NOT NULL,
delimiter character varying(3),
format character varying(3) NOT NULL,
data json DEFAULT '[]' NOT NULL,
CONSTRAINT exports_templates_pkey PRIMARY KEY (id),
CONSTRAINT exports_templates_unique_key UNIQUE (user_id, format)
)
WITH (OIDS=FALSE);

CREATE TABLE acknowledgement_receipts
(
id serial NOT NULL,
res_id INTEGER NOT NULL,
type CHARACTER VARYING(16) NOT NULL,
format CHARACTER VARYING(8) NOT NULL,
user_id INTEGER NOT NULL,
contact_id INTEGER NOT NULL,
creation_date timestamp without time zone NOT NULL,
send_date timestamp without time zone,
docserver_id CHARACTER VARYING(128) NOT NULL,
path CHARACTER VARYING(256) NOT NULL,
filename CHARACTER VARYING(256) NOT NULL,
fingerprint CHARACTER VARYING(256) NOT NULL,
CONSTRAINT acknowledgement_receipts_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TYPE custom_fields_modes AS ENUM ('form', 'technical');

CREATE TABLE custom_fields
(
    id serial NOT NULL,
    label character varying(256) NOT NULL,
    type character varying(256) NOT NULL,
    mode custom_fields_modes NOT NULL DEFAULT 'form',
    values jsonb,
    CONSTRAINT custom_fields_pkey PRIMARY KEY (id),
    CONSTRAINT custom_fields_unique_key UNIQUE (label)
)
WITH (OIDS=FALSE);

CREATE TABLE indexing_models
(
    id SERIAL NOT NULL,
    label character varying(256) NOT NULL,
    category character varying(256) NOT NULL,
    "default" BOOLEAN NOT NULL,
    owner INTEGER NOT NULL,
    private BOOLEAN NOT NULL,
    master INTEGER DEFAULT NULL,
    enabled BOOLEAN DEFAULT TRUE NOT NULL,
    CONSTRAINT indexing_models_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE indexing_models_fields
(
    id SERIAL NOT NULL,
    model_id INTEGER NOT NULL,
    identifier text NOT NULL,
    mandatory BOOLEAN NOT NULL,
    enabled BOOLEAN DEFAULT TRUE NOT NULL,
    default_value json,
    unit text NOT NULL,
    CONSTRAINT indexing_models_fields_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE contacts_custom_fields_list
(
    id serial NOT NULL,
    label character varying(256) NOT NULL,
    type character varying(256) NOT NULL,
    values jsonb,
    CONSTRAINT contacts_custom_fields_list_pkey PRIMARY KEY (id),
    CONSTRAINT contacts_custom_fields_list_unique_key UNIQUE (label)
)
WITH (OIDS=FALSE);

CREATE TABLE list_templates
(
    id SERIAL NOT NULL,
    title text NOT NULL,
    description text,
    type CHARACTER VARYING(32) NOT NULL,
    entity_id INTEGER,
    owner INTEGER DEFAULT NULL,
    CONSTRAINT list_templates_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE list_templates_items
(
    id SERIAL NOT NULL,
    list_template_id INTEGER NOT NULL,
    item_id INTEGER NOT NULL,
    item_type CHARACTER VARYING(32) NOT NULL,
    item_mode CHARACTER VARYING(64) NOT NULL,
    sequence INTEGER NOT NULL,
    CONSTRAINT list_templates_items_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

/* users followed resources */
CREATE TABLE users_followed_resources
(
    id serial NOT NULL,
    res_id int NOT NULL,
    user_id int NOT NULL,
    CONSTRAINT users_followed_resources_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE IF NOT EXISTS registered_mail_issuing_sites
(
    id                  SERIAL                 NOT NULL,
    label               CHARACTER VARYING(256) NOT NULL,
    post_office_label   CHARACTER VARYING(256),
    account_number      INTEGER,
    address_number      CHARACTER VARYING(256) NOT NULL,
    address_street      CHARACTER VARYING(256) NOT NULL,
    address_additional1 CHARACTER VARYING(256),
    address_additional2 CHARACTER VARYING(256),
    address_postcode    CHARACTER VARYING(256) NOT NULL,
    address_town        CHARACTER VARYING(256) NOT NULL,
    address_country     CHARACTER VARYING(256),
    CONSTRAINT registered_mail_issuing_sites_pkey PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS registered_mail_issuing_sites_entities
(
    id        SERIAL  NOT NULL,
    site_id   INTEGER NOT NULL,
    entity_id INTEGER NOT NULL,
    CONSTRAINT registered_mail_issuing_sites_entities_pkey PRIMARY KEY (id),
    CONSTRAINT registered_mail_issuing_sites_entities_unique_key UNIQUE (site_id, entity_id)
);
CREATE TABLE IF NOT EXISTS registered_mail_number_range (
    id SERIAL NOT NULL,
    type CHARACTER VARYING(15) NOT NULL,
    tracking_account_number CHARACTER VARYING(256) NOT NULL,
    range_start INTEGER NOT NULL,
    range_end INTEGER NOT NULL,
    creator INTEGER NOT NULL,
    creation_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    status CHARACTER VARYING(10) NOT NULL,
    current_number INTEGER,
    CONSTRAINT registered_mail_number_range_pkey PRIMARY KEY (id),
    CONSTRAINT registered_mail_number_range_unique_key UNIQUE (tracking_account_number)
);

CREATE TABLE IF NOT EXISTS registered_mail_resources (
    id SERIAL NOT NULL,
    res_id INTEGER NOT NULL,
    type CHARACTER VARYING(2) NOT NULL,
    issuing_site INTEGER NOT NULL,
    warranty CHARACTER VARYING(2) NOT NULL,
    letter BOOL NOT NULL DEFAULT FALSE,
    recipient jsonb NOT NULL,
    number INTEGER NOT NULL,
    reference TEXT,
    generated BOOL NOT NULL DEFAULT FALSE,
    deposit_id INTEGER,
    received_date TIMESTAMP WITHOUT TIME ZONE,
    return_reason CHARACTER VARYING(256),
    CONSTRAINT registered_mail_resources_pkey PRIMARY KEY (id),
    CONSTRAINT registered_mail_resources_unique_key UNIQUE (res_id)
);

CREATE TABLE attachment_types
(
    id SERIAL NOT NULL,
    type_id text NOT NULL,
    label text NOT NULL,
    visible BOOLEAN NOT NULL,
    email_link BOOLEAN NOT NULL,
    signable BOOLEAN NOT NULL,
    icon text,
    chrono BOOLEAN NOT NULL,
    version_enabled BOOLEAN NOT NULL,
    new_version_default BOOLEAN NOT NULL,
    CONSTRAINT attachment_types_pkey PRIMARY KEY (id),
    CONSTRAINT attachment_types_unique_key UNIQUE (type_id)
)
WITH (OIDS=FALSE);