-- SESSION_SECURITY TABLE

CREATE TABLE session_security (
    user_id character varying(32) NOT NULL,
    session_begin_date date NOT NULL,
    session_end_date date,
    full_where_clause character varying NOT NULL,
    last_available_bitmask character varying,
    last_object_id character varying
);

COMMENT ON TABLE session_security IS 'Session security management (originally for MoReq2 developpments).';

ALTER TABLE ONLY session_security
    ADD CONSTRAINT session_security_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY session_security
    ADD CONSTRAINT session_security_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);

-- MR_CLASSIFICATION_SCHEME TABLE

CREATE TABLE mr_classification_scheme
(
    mr_classification_scheme_id bigint NOT NULL,
    mr_title character varying(255) NOT NULL,
    mr_description text,
    mr_full_classification_code character varying(8) NOT NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ONLY mr_classification_scheme
    ADD CONSTRAINT mr_classification_scheme_full_code_ukey UNIQUE (mr_full_classification_code);
ALTER TABLE ONLY mr_classification_scheme
    ADD CONSTRAINT mr_classification_scheme_pkey PRIMARY KEY (mr_classification_scheme_id);

-- MR_AGGREGATION TABLE

CREATE TABLE mr_aggregation
(
    mr_aggregation_id bigint NOT NULL,
    mr_title character varying(255) NOT NULL,
    mr_creation_date timestamp without time zone NOT NULL,
    mr_full_classification_code character varying(255),
    mr_classification_code character varying(255),
    mr_description text,
    parent_mr_aggregation_id bigint,
    level_number integer NOT NULL,
    is_filled_with_records boolean DEFAULT false NOT NULL,
    can_have_records boolean DEFAULT true NOT NULL,
    parent_mr_classification_scheme_id bigint NOT NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ONLY mr_aggregation
    ADD CONSTRAINT mr_aggregation_pkey PRIMARY KEY (mr_aggregation_id),
    ADD CONSTRAINT parent_mr_classification_scheme_id_fkey FOREIGN KEY (parent_mr_classification_scheme_id) REFERENCES mr_classification_scheme(mr_classification_scheme_id);

-- MR_RES_RECORD_MULTI TABLE

CREATE TABLE mr_res_record_multi (
    mr_res_record_multi_id bigint NOT NULL,
    title character varying(255) DEFAULT NULL::character varying,
    subject text,
    description text,
    publisher character varying(255) DEFAULT NULL::character varying,
    contributor character varying(255) DEFAULT NULL::character varying,
    type_id bigint NOT NULL,
    format character varying(50) NOT NULL,
    typist character varying(50) NOT NULL,
    creation_date timestamp without time zone NOT NULL,
    fulltext_result character varying(10) DEFAULT NULL::character varying,
    ocr_result character varying(10) DEFAULT NULL::character varying,
    converter_result character varying(10) DEFAULT NULL::character varying,
    author character varying(255) DEFAULT NULL::character varying,
    author_name text,
    identifier character varying(255) DEFAULT NULL::character varying,
    source character varying(255) DEFAULT NULL::character varying,
    doc_language character varying(50) DEFAULT NULL::character varying,
    relation bigint,
    coverage character varying(255) DEFAULT NULL::character varying,
    doc_date timestamp without time zone,
    docserver_id character varying(32) NOT NULL,
    folders_system_id bigint,
    arbox_id character varying(32) DEFAULT NULL::character varying,
    path character varying(255) DEFAULT NULL::character varying,
    filename character varying(255) DEFAULT NULL::character varying,
    offset_doc character varying(255) DEFAULT NULL::character varying,
    logical_adr character varying(255) DEFAULT NULL::character varying,
    fingerprint character varying(255) DEFAULT NULL::character varying,
    filesize bigint,
    is_paper character(1) DEFAULT NULL::bpchar,
    page_count integer,
    scan_date timestamp without time zone,
    scan_user character varying(50) DEFAULT NULL::character varying,
    scan_location character varying(255) DEFAULT NULL::character varying,
    scan_wkstation character varying(255) DEFAULT NULL::character varying,
    scan_batch character varying(50) DEFAULT NULL::character varying,
    burn_batch character varying(50) DEFAULT NULL::character varying,
    scan_postmark character varying(50) DEFAULT NULL::character varying,
    envelop_id bigint,
    status character varying(10) ,
    destination character varying(50) DEFAULT NULL::character varying,
    approver character varying(50) DEFAULT NULL::character varying,
    validation_date timestamp without time zone,
    work_batch bigint,
    origin character varying(50) DEFAULT NULL::character varying,
    is_ingoing character(1) DEFAULT NULL::bpchar,
    priority smallint,
    arbatch_id bigint,
    custom_t1 text,
    custom_n1 bigint,
    custom_f1 numeric,
    custom_d1 timestamp without time zone,
    custom_t2 character varying(255) DEFAULT NULL::character varying,
    custom_n2 bigint,
    custom_f2 numeric,
    custom_d2 timestamp without time zone,
    custom_t3 character varying(255) DEFAULT NULL::character varying,
    custom_n3 bigint,
    custom_f3 numeric,
    custom_d3 timestamp without time zone,
    custom_t4 character varying(255) DEFAULT NULL::character varying,
    custom_n4 bigint,
    custom_f4 numeric,
    custom_d4 timestamp without time zone,
    custom_t5 character varying(255) DEFAULT NULL::character varying,
    custom_n5 bigint,
    custom_f5 numeric,
    custom_d5 timestamp without time zone,
    custom_t6 character varying(255) DEFAULT NULL::character varying,
    custom_d6 timestamp without time zone,
    custom_t7 character varying(255) DEFAULT NULL::character varying,
    custom_d7 timestamp without time zone,
    custom_t8 character varying(255) DEFAULT NULL::character varying,
    custom_d8 timestamp without time zone,
    custom_t9 character varying(255) DEFAULT NULL::character varying,
    custom_d9 timestamp without time zone,
    custom_t10 character varying(255) DEFAULT NULL::character varying,
    custom_d10 timestamp without time zone,
    custom_t11 character varying(255) DEFAULT NULL::character varying,
    custom_t12 character varying(255) DEFAULT NULL::character varying,
    custom_t13 character varying(255) DEFAULT NULL::character varying,
    custom_t14 character varying(255) DEFAULT NULL::character varying,
    custom_t15 character varying(255) DEFAULT NULL::character varying,
    tablename character varying(32) DEFAULT 'mr_res_record_multi'::character varying,
    initiator character varying(50) DEFAULT NULL::character varying,
    dest_user character varying(50) DEFAULT NULL::character varying,
    video_batch integer,
    video_time integer,
    video_user character varying(50) DEFAULT NULL::character varying,
    video_date timestamp without time zone,
    mr_deletion_date timestamp without time zone,
    mr_deletion_user_id character varying(50),
    is_mr_permanent boolean DEFAULT false,
    is_mr_vital boolean DEFAULT false,
    rendition_of_mr_res_record_multi_id bigint,
    mr_rendition_reason character varying(255),
    mr_rendition_date timestamp without time zone,
    is_mr_rendered boolean DEFAULT false,
    redaction_of_mr_res_record_multi_id bigint,
    mr_keywords character varying(255),
    mr_author_listinstance_id character varying(50),
    mr_sender_listinstance_id character varying(50),
    mr_r_d_schedule_id bigint,
    mr_sent_date timestamp without time zone,
    mr_received_date timestamp without time zone,
    mr_encrypted_date timestamp without time zone,
    mr_decrypted_date timestamp without time zone,
    mr_electronic_signature_verified_date timestamp without time zone,
    mr_electronic_signature text,
    mr_signature_certificate_issuer character varying(255),
    mr_signature_validation_user_id character varying(50),
    mr_signature_certification_service_provider character varying(255),
    mr_signature_validation_token text,
    mr_counter_signature text,
    mr_encryption_algorythm character varying(50),
    mr_encryption_level character varying(50),
    mr_encryption_serial_number_certificate text,
    mr_drm_features character varying(50),
    is_mr_offline_download boolean DEFAULT false
) WITH (
  OIDS=FALSE
);


ALTER TABLE ONLY mr_res_record_multi
    ADD CONSTRAINT mr_res_record_multi_pkey PRIMARY KEY (mr_res_record_multi_id);

-- CLASSIFICATION_MR_RES_RECORD_MULTI_MR_AGGREGATION

CREATE TABLE classification_mr_res_record_multi_mr_aggregation (
    classification_mr_res_record_multi_mr_aggregation_id bigint NOT NULL,
    classified_mr_res_record_multi_id bigint NOT NULL,
    parent_mr_aggregation_id bigint NOT NULL,
    coll_id character varying(32) NOT NULL,
    mr_owner_entity_id character varying(32) NOT NULL,
    mr_full_classification_code character varying(255) NOT NULL,
    mr_classification_code character varying(255),
    previous_mr_full_classification_code character varying(255),
    is_public boolean DEFAULT false NOT NULL,
    mr_reclassification_date timestamp without time zone,
    mr_reclassification_reason character varying(255),
    mr_reclassification_user_id character varying(50)
);


COMMENT ON TABLE classification_mr_res_record_multi_mr_aggregation IS 'Classification of documents in aggregations';

ALTER TABLE ONLY classification_mr_res_record_multi_mr_aggregation
    ADD CONSTRAINT classification_mr_res_record_multi_mr_aggregation_pkey PRIMARY KEY (classification_mr_res_record_multi_mr_aggregation_id);

ALTER TABLE ONLY classification_mr_res_record_multi_mr_aggregation
    ADD CONSTRAINT fkey_parent_mr_aggregation_id FOREIGN KEY (parent_mr_aggregation_id) REFERENCES mr_aggregation(mr_aggregation_id);

ALTER TABLE ONLY classification_mr_res_record_multi_mr_aggregation
    ADD CONSTRAINT fkey_classified_mr_res_record_multi FOREIGN KEY (classified_mr_res_record_multi_id) REFERENCES mr_res_record_multi(mr_res_record_multi_id);

ALTER TABLE ONLY classification_mr_res_record_multi_mr_aggregation
    ADD CONSTRAINT ukey_classification_mr_res_record_multi_mr_aggregation UNIQUE (classified_mr_res_record_multi_id, parent_mr_aggregation_id);

--- MR_LIST_RES_VIEW

CREATE VIEW mr_list_res_view AS
    SELECT c.parent_mr_aggregation_id AS parent_mr_aggregation_id, c.classified_mr_res_record_multi_id AS classified_mr_res_record_multi_id, c.mr_full_classification_code, r.title FROM classification_mr_res_record_multi_mr_aggregation c, mr_res_record_multi r WHERE (c.classified_mr_res_record_multi_id = r.mr_res_record_multi_id);

COMMENT ON VIEW mr_list_res_view IS 'Need to have few metadata elements for document list of a given aggregation.';

--- SECURITY
--ALTER TABLE "security" ADD COLUMN rights_bitmask integer;
--ALTER TABLE "security" ADD COLUMN mr_start_date timestamp without time zone;
--ALTER TABLE "security" ADD COLUMN mr_stop_date timestamp without time zone;
--ALTER TABLE "security" ADD COLUMN where_target character varying(15) NOT NULL DEFAULT 'DOC';
--ALTER TABLE "security" ADD COLUMN security_id integer NOT NULL DEFAULT 0;
--- ALTER TABLE "security" ADD CONSTRAINT pk_security PRIMARY KEY (security_id);

CREATE OR REPLACE VIEW mr_classification_scheme_view AS
 SELECT a.*, cs.mr_classification_scheme_id, cs.mr_title as cs_title, cs.mr_description as cs_description, cs.mr_full_classification_code
 as cs_full_classification_code
   FROM mr_aggregation a
  LEFT JOIN  mr_classification_scheme cs ON a.parent_mr_classification_scheme_id = cs.mr_classification_scheme_id;
