
-- TABLES --
CREATE TABLE "others_tags" (
  "Id" text NOT NULL,
  "Title" text,
  "tag_key" text,
  "tag_value" text,
  "ModifiedDate" text,
  "DeletedDate" text,
  "CreatedDate" text,
  "validSince" text,
  "validUntil" text
); 
CREATE TABLE "requests_tags" (
  "Id" text NOT NULL,
  "Title" text,
  "tag_key" text,
  "tag_value" text,
  "ModifiedDate" text,
  "DeletedDate" text,
  "CreatedDate" text,
  "validSince" text,
  "validUntil" text
); 
CREATE TABLE "basisregistraties_tags" (
  "Title" text,
  "tag_key" text,
  "tag_value" text,
  "ModifiedDate" text,
  "DeletedDate" text,
  "CreatedDate" text,
  "validSince" text,
  "validUntil" text,
  "id" text NOT NULL
); 
CREATE TABLE "documents_tags" (
  "Id" text NOT NULL,
  "Title" text,
  "tag_key" text,
  "tag_value" text,
  "ModifiedDate" text,
  "DeletedDate" text,
  "CreatedDate" text NOT NULL,
  "validSince" text,
  "validUntil" text
); 
CREATE TABLE "requests_oo_tags" (
  "Id" text NOT NULL,
  "Title" text,
  "tag_key" text,
  "tag_value" text,
  "ModifiedDate" text,
  "DeletedDate" text,
  "CreatedDate" text,
  "validSince" text,
  "validUntil" text
); 
CREATE TABLE "documents_oo_tags" (
  "Id" text NOT NULL,
  "Title" text,
  "tag_key" text,
  "tag_value" text,
  "ModifiedDate" text,
  "DeletedDate" text,
  "CreatedDate" text,
  "validSince" text,
  "validUntil" text
); 
CREATE TABLE "wookey_users" (
  "user_id" text NOT NULL,
  "username" text,
  "email" text,
  "email_verified" boolean DEFAULT false,
  "created_at" timestamp without time zone,
  "id" uuid DEFAULT gen_random_uuid() NOT NULL,
  "service" character varying DEFAULT 'telegram'::character varying
); 
CREATE TABLE "documents" (
  "id" text NOT NULL,
  "Document ID" text,
  "Document naam" text,
  "Bestandsnaam" text,
  "Beoordeling" text,
  "Beoordelingsgrond" text,
  "Toelichting" text,
  "Publieke link" text,
  "Locatie open.minvws.nl" text,
  "Opgeschort" text,
  "Definitief ID" text,
  "Status" text,
  "FromFile" text,
  "DocumentPublicationDate" text,
  "DocumentStatusCheckedDate" text,
  "DocumentStatusCheckedResult" text,
  "SourceFileType" text,
  "Tags" text,
  "Besluit" text,
  "ModifiedDate" text,
  "DeletedDate" text,
  "BesluitURL" text,
  "CreatedDate" text NOT NULL,
  "DocumentDownloadLink" text,
  "DigestLaatsteControleDatum" text
); 
CREATE TABLE "dutch_words" (
  "word" text NOT NULL
); 
CREATE TABLE "documents_inventaris" (
  "Gerelateerd ID" text,
  "Beoordeling" text,
  "CreatedDate" text,
  "Toelichting" text,
  "Id" text NOT NULL,
  "Opgeschort" text,
  "Publieke link" text,
  "SourceFileType" text,
  "Locatie open.minvws.nl" text,
  "Definitief ID" text,
  "Bestandsnaam" text,
  "Status" text,
  "Document ID" text,
  "DocumentPublicationDate" text,
  "DocumentStatusCheckedDate" text,
  "DocumentStatusCheckedResult" text,
  "FromFile" text,
  "Tags" text,
  "Besluit" text,
  "ModifiedDate" text,
  "DeletedDate" text,
  "BesluitURL" text,
  "DocumentDownloadLink" text,
  "Locatie document ID" text,
  "Locatie gerelateerd ID" text,
  "Document naam" text,
  "Beoordelingsgrond" text,
  "OriginalURL" text,
  "RowChecksum" text
); 
CREATE TABLE "wookey_users_uploads" (
  "id" uuid NOT NULL,
  "user_id" uuid,
  "file_name" character varying,
  "file_hash" character varying,
  "file_snapshot" character varying,
  "file_source_url" character varying,
  "file_meta_data_tags" jsonb,
  "file_type" character varying,
  "file_master_source" uuid,
  "created_date" timestamp without time zone
); 
CREATE TABLE "wookey_verification_codes" (
  "user_id" uuid NOT NULL,
  "email" text,
  "code" text NOT NULL,
  "created_at" timestamp without time zone,
  "expires_at" timestamp without time zone
); 
CREATE TABLE "maandmap" (
  "naam" text,
  "nummer" text
); 
CREATE TABLE "documents_archives" (
  "fromFile" text,
  "fromFilePath" text,
  "documentName" text,
  "documentType" text,
  "documentCompressedSize" bigint,
  "documentSize" bigint,
  "documentCompressRatio" double precision,
  "documentDateModified" text,
  "dossierReference" text,
  "dossierReferenceAlias" text,
  "createdDate" text,
  "lastSeen" text,
  "text" text,
  "noTextDate" text,
  "ocrText" text,
  "documentIndicatedDate" text,
  "documentIndicatedDateCorrected" text,
  "documentMailAddresses" text,
  "documentMailAdresses" text,
  "digestSHA256" text,
  "documentDate" text,
  "ModifiedDate" text,
  "search_vector" tsvector,
  "Id" text NOT NULL,
  "textDigestSHA256" text,
  "ocrTextDigestSHA256" text,
  "extractedId" text
); 
CREATE TABLE "wookey_user_tags" (
  "id" uuid DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL,
  "title" text,
  "tag_key" text,
  "tag_value" text,
  "modified_date" timestamp without time zone,
  "created_date" timestamp without time zone,
  "valid_since" timestamp without time zone,
  "valid_until" timestamp without time zone
); 
CREATE TABLE "wookey_keyword_queries" (
  "id" uuid DEFAULT gen_random_uuid() NOT NULL,
  "query" text NOT NULL,
  "user_id" uuid NOT NULL,
  "creation_date" timestamp without time zone
); 
CREATE TABLE "wookey_subscriptions" (
  "id" uuid DEFAULT gen_random_uuid() NOT NULL,
  "query_id" text NOT NULL,
  "user_id" uuid NOT NULL,
  "created_at" timestamp without time zone
); 

-- VIEWS --
CREATE VIEW "vw_checkrequests" AS
 WITH doc_counts AS (
         SELECT documents."FromFile",
            count(*) AS reg_docs
           FROM documents
          WHERE NOT documents."Document ID" = 'besluitbrief'::text
          GROUP BY documents."FromFile"
        ), disclosure_types AS (
         SELECT DISTINCT ON (rt."Title") rt."Title",
            rt.tag_value AS disclosuretype
           FROM requests_tags rt
          WHERE rt.tag_key = 'DisclosureType'::text
        ), request_docs AS (
         SELECT rt."Title",
            count(rt.tag_key) AS total_tags,
            sum(
                CASE
                    WHEN rt.tag_key ~~ 'ContainsDocument%'::text AND rt.tag_key ~~ '%_linkhref_%'::text AND rt."validUntil" = '9999-12-31'::text THEN 1
                    ELSE 0
                END) AS total_contains,
            max(
                CASE
                    WHEN rt2.tag_value ~ '^\d+(\.\d+)?$'::text THEN replace(rt2.tag_value, '.'::text, ''::text)
                    ELSE NULL::text
                END) AS raw_docs
           FROM requests_tags rt
             LEFT JOIN requests_tags rt2 ON rt."Title" = rt2."Title" AND rt2.tag_key = 'Omvang openbaarmaking_documenten'::text
          WHERE rt."validUntil"::date > CURRENT_DATE
          GROUP BY rt."Title"
        )
 SELECT r."Title",
    dt.disclosuretype AS "disclosureType",
    r.total_tags AS "totalTags",
    r.total_contains AS "totalContains",
    r.total_tags - COALESCE(r.total_contains, 0::bigint) AS "tagsMeta",
    COALESCE(r.raw_docs, ''::text) AS "docsAssigned",
    COALESCE(d.reg_docs, 0::bigint) AS "docsRegistered",
        CASE
            WHEN r.raw_docs ~ '^\d+$'::text THEN r.raw_docs::integer - COALESCE(d.reg_docs, 0::bigint)
            ELSE NULL::bigint
        END AS "diffDocs"
   FROM request_docs r
     LEFT JOIN doc_counts d ON d."FromFile" = r."Title"
     LEFT JOIN disclosure_types dt ON dt."Title" = r."Title"
  ORDER BY (
        CASE
            WHEN r.raw_docs ~ '^\d+$'::text THEN r.raw_docs::integer - COALESCE(d.reg_docs, 0::bigint)
            ELSE NULL::bigint
        END) DESC NULLS LAST;; 
CREATE VIEW "vw_documents_tags_tag_key_count" AS
 SELECT tag_key,
    count(*) AS count
   FROM documents_tags
  GROUP BY tag_key
  ORDER BY tag_key;; 
CREATE VIEW "vw_additions_requests_tags_250" AS
 SELECT "CreatedDate" AS created_date,
    tag_key,
    tag_value,
    "Title" AS title
   FROM requests_tags
  ORDER BY "CreatedDate" DESC
 LIMIT 250;; 
CREATE VIEW "vw_documents_inventaris_url" AS
 SELECT DISTINCT COALESCE("Locatie open.minvws.nl", "Locatie document ID") AS url
   FROM documents_inventaris di;; 
CREATE VIEW "vw_documents_tags_createddate" AS
 SELECT "CreatedDate",
    count(*) AS count
   FROM documents_tags
  GROUP BY "CreatedDate"
  ORDER BY "CreatedDate" DESC;; 
CREATE VIEW "vw_documents_url" AS
 SELECT DISTINCT COALESCE("Locatie open.minvws.nl", 'n/a'::text) AS url,
    "CreatedDate" AS createddate,
    COALESCE("DocumentStatusCheckedResult", 'n/a'::text) AS checked
   FROM documents d
  ORDER BY "CreatedDate" DESC;; 
CREATE VIEW "vw_requests_tags_tag_key_count" AS
 SELECT tag_key,
    count(*) AS count
   FROM requests_tags
  GROUP BY tag_key
  ORDER BY tag_key;; 
CREATE VIEW "vw_documents_inventaris_url_createddate" AS
 SELECT DISTINCT COALESCE("Locatie open.minvws.nl", "Locatie document ID") AS url,
    "Bestandsnaam",
    "FromFile",
    "Document ID",
    "CreatedDate"
   FROM documents_inventaris di
  ORDER BY "CreatedDate" DESC;; 
CREATE VIEW "vw_documents_inventaris_url_fromfile_docid_invfile_createddate" AS
 SELECT url,
    split_part(url, '/document'::text, 1) AS fromfile,
    reverse(split_part(reverse(url), '-'::text, 1)) AS docid,
    "Bestandsnaam" AS bestandsnaam,
    "Document ID" AS docid2,
    "FromFile" AS invfile,
    "CreatedDate"
   FROM vw_documents_inventaris_url_createddate
  WHERE NOT (url IN ( SELECT vw_documents_url.url
           FROM vw_documents_url)) AND url !~ '/dossier/[0-9]{3}/'::text
  ORDER BY "CreatedDate";; 
CREATE VIEW "vw_additions_requests_tag_keys" AS
 SELECT created_date,
    title,
    tag_group,
        CASE
            WHEN tag_group = 'ContainsDocument_'::text THEN count(*) / 5
            ELSE count(*)
        END AS totalrefs
   FROM ( SELECT requests_tags."CreatedDate"::date AS created_date,
                CASE
                    WHEN requests_tags.tag_key ~~ 'ContainsDocument_%'::text THEN 'ContainsDocument_'::text
                    ELSE requests_tags.tag_key
                END AS tag_group,
            requests_tags."Title" AS title
           FROM requests_tags) unnamed_subquery
  GROUP BY created_date, tag_group, title
  ORDER BY created_date DESC, tag_group, title;; 
CREATE VIEW "vw_besluitbrieven" AS
 SELECT d."Locatie open.minvws.nl" AS "Besluitbrief",
    rt.tag_value AS periode,
    rt2.tag_value AS datum_besluit,
    dt.tag_key,
    dt.tag_value,
    dt."ModifiedDate",
    dt."CreatedDate"
   FROM documents d
     JOIN documents_tags dt ON d."Locatie open.minvws.nl" = dt."Title"
     JOIN requests_tags rt ON d."FromFile" = rt."Title" AND rt.tag_key = 'Periode'::text
     JOIN requests_tags rt2 ON d."FromFile" = rt2."Title" AND rt2.tag_key = 'Datum besluit'::text
  WHERE d."Document ID" = 'besluitbrief'::text
  GROUP BY d."Locatie open.minvws.nl", rt.tag_value, rt2.tag_value, dt.tag_key, dt.tag_value, dt."ModifiedDate", dt."CreatedDate";; 
CREATE VIEW "vw_documenten_meerdere_beoordelingen" AS
 SELECT dt."Title" AS url,
    rt.tag_value AS onderwerp,
    dt."CreatedDate" AS registratiedatum,
    dt.tag_value AS beoordeling,
    COALESCE(dt2.tag_value, ''::text) AS bestandsnaam,
    COALESCE(dt3.tag_value, ''::text) AS downloadlink
   FROM documents_tags dt
     JOIN documents d ON dt."Title" = d."Locatie open.minvws.nl"
     JOIN requests_tags rt ON d."FromFile" = rt."Title" AND rt.tag_key = 'Onderwerp'::text
     LEFT JOIN documents_tags dt2 ON dt."Title" = dt2."Title" AND dt2.tag_key = 'DocumentDownload_proposed_filename'::text
     LEFT JOIN documents_tags dt3 ON dt."Title" = dt3."Title" AND dt3.tag_key = 'DocumentDownload_linkhref'::text
  WHERE (dt."Title" IN ( SELECT documents_tags."Title"
           FROM documents_tags
          WHERE documents_tags.tag_key = 'About_Beoordeling'::text
          GROUP BY documents_tags."Title"
         HAVING count(*) > 1)) AND dt.tag_key = 'About_Beoordeling'::text
  ORDER BY dt."Title", dt."CreatedDate";; 
CREATE VIEW "vw_mv_requests_dates" AS
 WITH maandmap AS (
         SELECT m.naam,
            m.nummer
           FROM ( VALUES ('januari'::text,'01'::text), ('februari'::text,'02'::text), ('maart'::text,'03'::text), ('april'::text,'04'::text), ('mei'::text,'05'::text), ('juni'::text,'06'::text), ('juli'::text,'07'::text), ('augustus'::text,'08'::text), ('september'::text,'09'::text), ('oktober'::text,'10'::text), ('november'::text,'11'::text), ('december'::text,'12'::text)) m(naam, nummer)
        ), parsed AS (
         SELECT mv_requests_matbase.onderwerp,
            mv_requests_matbase.periode,
            lower(mv_requests_matbase.periode) AS p,
            regexp_matches(lower(mv_requests_matbase.periode), '(\w+)\s*(\d{4})?\s*t/m\s*(\w+)\s*(\d{4})?'::text, 'g'::text) AS match,
            regexp_matches(lower(mv_requests_matbase.periode), '(\w+)\s*(\d{4})'::text, 'g'::text) AS enkel,
            to_date(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(mv_requests_matbase.datumbesluit, 'januari'::text, '01'::text), 'februari'::text, '02'::text), 'maart'::text, '03'::text), 'april'::text, '04'::text), 'mei'::text, '05'::text), 'juni'::text, '06'::text), 'juli'::text, '07'::text), 'augustus'::text, '08'::text), 'september'::text, '09'::text), 'oktober'::text, '10'::text), 'november'::text, '11'::text), 'december'::text, '12'::text), 'DD MM YYYY'::text) AS datum_besluit,
            mv_requests_matbase.omvangpaginas,
            mv_requests_matbase."CreatedDate",
            mv_requests_matbase."Title"
           FROM mv_requests_matbase
        )
 SELECT DISTINCT ON (p."Title") p.periode,
    p.onderwerp,
    p."Title",
    p.datum_besluit,
    p.omvangpaginas,
    p."CreatedDate",
        CASE
            WHEN p.match IS NOT NULL THEN to_char(to_date(concat('01-', m1.nummer, '-', COALESCE(p.match[2], p.match[4])), 'DD-MM-YYYY'::text)::timestamp with time zone, 'YYYYMMDD'::text)
            WHEN p.enkel IS NOT NULL THEN to_char(to_date(concat('01-', m2.nummer, '-', p.enkel[2]), 'DD-MM-YYYY'::text)::timestamp with time zone, 'YYYYMMDD'::text)
            ELSE NULL::text
        END AS periodedatumvan,
        CASE
            WHEN p.match IS NOT NULL THEN to_char(to_date(concat('01-', m3.nummer, '-', p.match[4]), 'DD-MM-YYYY'::text) + '1 mon'::interval - '1 day'::interval, 'YYYYMMDD'::text)
            WHEN p.enkel IS NOT NULL THEN to_char(to_date(concat('01-', m2.nummer, '-', p.enkel[2]), 'DD-MM-YYYY'::text) + '1 mon'::interval - '1 day'::interval, 'YYYYMMDD'::text)
            ELSE NULL::text
        END AS periodedatumtotmet
   FROM parsed p
     LEFT JOIN maandmap m1 ON m1.naam = p.match[1]
     LEFT JOIN maandmap m3 ON m3.naam = p.match[3]
     LEFT JOIN maandmap m2 ON m2.naam = p.enkel[1];; 
CREATE VIEW "vw_documents_inventaris_full_sorted" AS
 SELECT "Gerelateerd ID",
    "Beoordeling",
    "CreatedDate",
    "Toelichting",
    "Id",
    "Opgeschort",
    "Publieke link",
    "SourceFileType",
    COALESCE("Locatie open.minvws.nl", "Locatie document ID") AS "Locatie open.minvws.nl",
    "Definitief ID",
    "Bestandsnaam",
    "Status",
    "Document ID",
    "DocumentPublicationDate",
    "DocumentStatusCheckedDate",
    "DocumentStatusCheckedResult",
    "FromFile",
    "Tags",
    "Besluit",
    "ModifiedDate",
    "DeletedDate",
    "BesluitURL",
    "DocumentDownloadLink",
    "Locatie gerelateerd ID",
    "Document naam",
    "Beoordelingsgrond",
    "OriginalURL",
    "RowChecksum"
   FROM documents_inventaris;; 
CREATE VIEW "vw_wookey_doc_decisions" AS
 SELECT COALESCE(tag_value, 'n/a'::text) AS beoordeling,
    count(*) AS count
   FROM documents_tags
  WHERE tag_key = 'About_Beoordeling'::text
  GROUP BY tag_value
  ORDER BY tag_value;; 
CREATE VIEW "vw_wookey_doc_responsible" AS
 SELECT COALESCE(rt.tag_value, 'n/a'::text) AS verantwoordelijke,
    COALESCE(dt.tag_value, 'n/a'::text) AS beoordeling,
    count(*) AS count
   FROM requests_tags rt
     JOIN documents d ON d."FromFile" = rt."Title"
     JOIN documents_tags dt ON d."Locatie open.minvws.nl" = dt."Title"
  WHERE dt.tag_key = 'About_Beoordeling'::text AND rt.tag_key = 'Verantwoordelijk(en)'::text
  GROUP BY rt.tag_value, dt.tag_value
  ORDER BY rt.tag_value, dt.tag_value;; 
CREATE VIEW "vw_documents_archives_documentname_content" AS
 WITH tag_values AS (
         SELECT documents_tags."Title",
            max(
                CASE
                    WHEN documents_tags.tag_key = 'document_documentName'::text THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS docname,
            max(
                CASE
                    WHEN documents_tags.tag_key = 'DocumentDownload_proposed_filename'::text THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS filename2,
            max(
                CASE
                    WHEN documents_tags.tag_key = 'document_documentPublicationDate'::text THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS docpubdate,
            max(
                CASE
                    WHEN documents_tags.tag_key = 'About_Beoordeling'::text THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS beoordeling
           FROM documents_tags
          WHERE documents_tags.tag_key = ANY (ARRAY['document_documentName'::text, 'DocumentDownload_proposed_filename'::text, 'document_documentPublicationDate'::text, 'About_Beoordeling'::text])
          GROUP BY documents_tags."Title"
        ), ranked_documents AS (
         SELECT d.id AS documentid,
            da."Id" AS archiveid,
            da."createdDate" AS created_date,
            d."Locatie open.minvws.nl" AS docurl,
            da."documentIndicatedDateCorrected" AS docdate,
            tv.docpubdate,
            tv.docname,
            da."documentName" AS filename,
            tv.filename2,
            da."digestSHA256" AS filedigest,
            tv.beoordeling AS docverdict,
            COALESCE(TRIM(BOTH FROM da.text), TRIM(BOTH FROM da."ocrText"), 'n/a'::text) AS filetext,
            COALESCE(da."textDigestSHA256", da."ocrTextDigestSHA256", 'n/a'::text) AS textdigest,
            row_number() OVER (PARTITION BY d.id ORDER BY da."documentIndicatedDateCorrected" DESC NULLS LAST) AS rn
           FROM documents_archives da
             JOIN documents d ON d."Definitief ID" = da."extractedId"
             JOIN tag_values tv ON tv."Title" = d."Locatie open.minvws.nl"
        )
 SELECT documentid,
    archiveid,
    created_date,
    docurl,
    docdate,
    docpubdate,
    docname,
    filename,
    filename2,
    filedigest,
    docverdict,
    filetext,
    textdigest,
    rn
   FROM ranked_documents
  WHERE rn = 1;; 
CREATE VIEW "vw_documents_tags_downloaddocumenthref" AS
 SELECT DISTINCT COALESCE(d."Locatie open.minvws.nl", 'n/a'::text) AS document,
    dt."CreatedDate" AS createddate,
    COALESCE(d."DocumentStatusCheckedResult", 'n/a'::text) AS checked,
    COALESCE(dt.tag_value, 'n/a'::text) AS downloadurl
   FROM documents d
     JOIN documents_tags dt ON d."Locatie open.minvws.nl" = dt."Title" AND dt.tag_key = 'DocumentDownload_linkhref'::text AND dt."validUntil" = '9999-12-31'::text
  ORDER BY dt."CreatedDate" DESC;; 
CREATE VIEW "vw_additions_documents_tags_250" AS
 SELECT "CreatedDate" AS created_date,
    tag_key,
    tag_value,
    "Title" AS title
   FROM documents_tags
  WHERE "validUntil" = '9999-12-31'::text
  ORDER BY "CreatedDate" DESC
 LIMIT 250;; 
CREATE VIEW "vw_additions_requests_tags_desc" AS
 SELECT "CreatedDate" AS created_date,
    tag_key,
    tag_value,
    "Title" AS title
   FROM requests_tags
  WHERE "validUntil" = '9999-12-31'::text
  ORDER BY "CreatedDate" DESC;; 
CREATE VIEW "vw_additions_requests_tags" AS
 SELECT "CreatedDate" AS created_date,
    tag_key,
    tag_value,
    "Title" AS title,
    "validUntil" AS valid_until
   FROM requests_tags
  WHERE "validUntil" = '9999-12-31'::text
  ORDER BY "CreatedDate" DESC;; 
CREATE VIEW "vw_wookey_db_size_info" AS
 SELECT table_name,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name::text)::regclass)) AS total_size,
    pg_size_pretty(pg_relation_size(quote_ident(table_name::text)::regclass)) AS data_size,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name::text)::regclass) - pg_relation_size(quote_ident(table_name::text)::regclass)) AS index_size
   FROM information_schema.tables
  WHERE table_schema::name = 'public'::name AND table_name::name !~~ 'vw_%'::text
  ORDER BY (pg_total_relation_size(quote_ident(table_name::text)::regclass)) DESC;; 
CREATE VIEW "vw_additions_documents_tags_desc" AS
 SELECT "CreatedDate" AS created_date,
    tag_key,
    tag_value,
    "Title" AS title
   FROM documents_tags
  WHERE "validUntil" = '9999-12-31'::text
  ORDER BY "CreatedDate" DESC;; 
CREATE VIEW "vw_additions_documents_tags" AS
 SELECT "CreatedDate" AS created_date,
    tag_key,
    tag_value,
    "Title" AS title
   FROM documents_tags
  WHERE "validUntil" = '9999-12-31'::text;; 
CREATE VIEW "vw_documents_tags_tag_key_createddate" AS
 SELECT tag_key,
    "CreatedDate"::date AS date,
    count(*) AS count
   FROM documents_tags
  WHERE "validUntil" = '9999-12-31'::text
  GROUP BY ("CreatedDate"::date), tag_key
  ORDER BY ("CreatedDate"::date) DESC;; 
CREATE VIEW "vw_docs_outside_of_period" AS
 SELECT DISTINCT ON (mda.indicated_date_corrected, mda.docurl) replace(mda.indicated_date_corrected, '-'::text, ''::text) AS indicated_date,
    rd.periode AS period,
    rd.periodedatumvan AS since,
    rd.periodedatumtotmet AS until,
    mda.docname,
    mda.filename,
    mda.created_date,
    documents."FromFile" AS request,
    COALESCE(replace(replace(replace(rt.tag_value, 'De minister van Volksgezondheid, Welzijn en Sport heeft'::text, '...'::text), 'Besluit Ik heb besloten (deels) aan uw verzoek tegemoet te komen en de informatie waarom u verzocht, opgenomen in de documenten (deels) openbaar te maken. Daarnaast heb ik besloten de door u gevraagde informatie opgenomen in een aantal documenten niet openbaar te maken. Voor de motivering verwijs ik naar het onderdeel ''Overwegingen'' van dit besluit.'::text, NULL::text), ' op verzoeken in het kader van de Wet open overheid. Het besluit betreft openbaarmaking van documenten die betrekking hebben op informatie'::text, '...'::text), rd.onderwerp) AS subjecta,
    replace(replace(replace(replace(replace(replace(replace(rt.tag_value, 'De minister van Volksgezondheid, Welzijn en Sport heeft'::text, '...'::text), 'in het kader van de Wet open overheid. Het besluit betreft openbaarmaking van documenten die betrekking hebben op informatie'::text, ''::text), 'in het kader van de Wet openbaarheid van bestuur. Het besluit betreft openbaarmaking van documenten die betrekking hebben op informatie aangaande'::text, ''::text), 'in het kader van de Wet open overheid. Het besluit betreft openbaarmaking van documenten die betrekking hebben op '::text, ''::text), 'Besluit Ik heb besloten (deels) aan uw verzoek tegemoet te komen en de informatie waarom u verzocht, opgenomen in de documenten (deels) openbaar te maken. Daarnaast heb ik besloten de door u gevraagde informatie opgenomen in een aantal documenten niet openbaar te maken. Voor de motivering verwijs ik naar het onderdeel ''Overwegingen'' van dit besluit.'::text, ''::text), 'Besluit Ik heb besloten (deels) aan uw verzoek tegemoet te komen en de informatie waarom u verzocht, opgenomen in de documenten (deels) openbaar te maken. Daarnaast heb ik besloten de door u gevraagde informatie opgenomen in een aantal documenten niet openbaar te maken. Voor de motivering verwijs ik naar het onderdeel ‘Overwegingen'' van dit besluit.'::text, ''::text), 'Wob-deelbesluit'::text, '... '::text) AS subjectb,
    mda.docurl
   FROM ( SELECT DISTINCT ON (mv_documents_archives_documentname_content.docurl) mv_documents_archives_documentname_content.documentid,
            mv_documents_archives_documentname_content.archiveid,
            mv_documents_archives_documentname_content.created_date,
            mv_documents_archives_documentname_content.docurl,
            mv_documents_archives_documentname_content.docdate,
            mv_documents_archives_documentname_content.indicated_date_corrected,
            mv_documents_archives_documentname_content.docpubdatenl,
            mv_documents_archives_documentname_content.docname,
            mv_documents_archives_documentname_content.filename,
            mv_documents_archives_documentname_content.filename2,
            mv_documents_archives_documentname_content.filedigest,
            mv_documents_archives_documentname_content.docverdict,
            mv_documents_archives_documentname_content.filetext,
            mv_documents_archives_documentname_content.textdigest,
            mv_documents_archives_documentname_content.search_vector
           FROM mv_documents_archives_documentname_content
          ORDER BY mv_documents_archives_documentname_content.docurl, mv_documents_archives_documentname_content.indicated_date_corrected) mda
     JOIN documents ON documents."Locatie open.minvws.nl" = mda.docurl
     LEFT JOIN vw_mv_requests_dates rd ON rd."Title" = documents."FromFile"
     LEFT JOIN requests_tags rt ON rt."Title" = documents."FromFile" AND rt.tag_key = 'request_Summary_1'::text AND rt."validUntil" = '9999-12-31'::text
  WHERE replace(mda.indicated_date_corrected, '-'::text, ''::text) < rd.periodedatumvan OR replace(mda.indicated_date_corrected, '-'::text, ''::text) > rd.periodedatumtotmet;; 

-- MATERIALIZED VIEWS --
CREATE MATERIALIZED VIEW "mv_documents_archives_documents" AS
 SELECT DISTINCT ON (d."Locatie open.minvws.nl") d."Definitief ID" AS docid,
    d."Locatie open.minvws.nl" AS docurl,
    da."digestSHA256" AS filedigest,
        CASE
            WHEN (length(TRIM(BOTH FROM da."textDigestSHA256")) > 0) THEN da."textDigestSHA256"
            ELSE NULL::text
        END AS textdigest,
        CASE
            WHEN (length(TRIM(BOTH FROM da."ocrTextDigestSHA256")) > 0) THEN da."ocrTextDigestSHA256"
            ELSE NULL::text
        END AS ocrdigest,
    COALESCE(da."documentName", d."Bestandsnaam") AS filename,
        CASE
            WHEN (length(TRIM(BOTH FROM da.text)) > 0) THEN da.text
            ELSE NULL::text
        END AS text,
        CASE
            WHEN (length(TRIM(BOTH FROM da."ocrText")) > 0) THEN da."ocrText"
            ELSE NULL::text
        END AS cocrtext,
    da."fromFilePath" AS fromfilepath
   FROM (documents_archives da
     JOIN documents d ON ((d."Definitief ID" = da."extractedId")))
  WHERE ((length(TRIM(BOTH FROM da."textDigestSHA256")) > 0) OR (length(TRIM(BOTH FROM da."ocrTextDigestSHA256")) > 0));; 
CREATE MATERIALIZED VIEW "mv_wookey_recordsnumbers" AS
 SELECT n.nspname AS schema,
    c.relname AS name,
        CASE c.relkind
            WHEN 'r'::"char" THEN 'table'::text
            WHEN 'm'::"char" THEN 'materialized view'::text
            WHEN 'v'::"char" THEN 'view'::text
            ELSE NULL::text
        END AS type,
    s.n_live_tup AS estimated_rows,
    count(a.attname) AS column_count,
    (count(a.attname) * COALESCE(s.n_live_tup, (0)::bigint)) AS estimated_cells
   FROM (((pg_class c
     JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
     LEFT JOIN pg_stat_user_tables s ON ((s.relid = c.oid)))
     JOIN pg_attribute a ON ((a.attrelid = c.oid)))
  WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'v'::"char"])) AND (a.attnum > 0) AND (NOT a.attisdropped) AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (c.relname <> 'wookey_recordsnumbers'::name) AND (c.relname <> 'wookey_recordsnumbers_vw'::name) AND (c.relname !~~ 'pg_temp%'::text))
  GROUP BY n.nspname, c.relname, c.relkind, s.n_live_tup
  ORDER BY (count(a.attname) * COALESCE(s.n_live_tup, (0)::bigint)) DESC NULLS LAST;; 
CREATE MATERIALIZED VIEW "mv_requests_matbase" AS
 SELECT DISTINCT "Title",
    max(
        CASE
            WHEN (tag_key = 'Onderwerp'::text) THEN tag_value
            ELSE NULL::text
        END) AS onderwerp,
    max(
        CASE
            WHEN (tag_key = 'Periode'::text) THEN tag_value
            ELSE NULL::text
        END) AS periode,
    max(
        CASE
            WHEN (tag_key = 'Verantwoordelijk(en)'::text) THEN tag_value
            ELSE NULL::text
        END) AS verantwoordelijken,
    max(
        CASE
            WHEN (tag_key = 'Omvang openbaarmaking_documenten'::text) THEN tag_value
            ELSE NULL::text
        END) AS omvangdocumenten,
    max(
        CASE
            WHEN (tag_key = 'Omvang publicatie'::text) THEN tag_value
            ELSE NULL::text
        END) AS omvangpublicatie,
    max(
        CASE
            WHEN (tag_key = 'Omvang openbaarmaking_paginas'::text) THEN tag_value
            ELSE NULL::text
        END) AS omvangpaginas,
    max(
        CASE
            WHEN (tag_key = 'Datum besluit'::text) THEN tag_value
            ELSE NULL::text
        END) AS datumbesluit,
    max(
        CASE
            WHEN (tag_key = 'Soort besluit'::text) THEN tag_value
            ELSE NULL::text
        END) AS soortbesluit,
    max(
        CASE
            WHEN (tag_key = 'Download document archief_linkname_1'::text) THEN tag_value
            ELSE NULL::text
        END) AS archiefnaam,
    max(
        CASE
            WHEN (tag_key = 'Download document archief_linkhref_1'::text) THEN tag_value
            ELSE NULL::text
        END) AS archiefhref,
    max(
        CASE
            WHEN (tag_key = 'Download document archief_size_1'::text) THEN tag_value
            ELSE NULL::text
        END) AS archiefomvang,
    max(
        CASE
            WHEN (tag_key = 'request_Summary'::text) THEN tag_value
            ELSE NULL::text
        END) AS samenvatting,
    min("CreatedDate") AS "CreatedDate"
   FROM requests_tags
  WHERE ((tag_key = ANY (ARRAY['Omvang publicatie'::text, 'Onderwerp'::text, 'Periode'::text, 'Verantwoordelijk(en)'::text, 'Omvang openbaarmaking_documenten'::text, 'Omvang openbaarmaking_paginas'::text, 'Datum besluit'::text, 'DisclosureType'::text, 'DecisionType'::text, 'Soort besluit'::text, 'Download document archief_linkname_1'::text, 'Download document archief_linkhref_1'::text, 'Download document archief_size_1'::text, 'request_Summary'::text])) AND ("validUntil" = '9999-12-31'::text))
  GROUP BY "Title";; 
CREATE MATERIALIZED VIEW "mv_document_conflicts" AS
 SELECT da."Id" AS daid,
    da."createdDate" AS createddate,
    da."documentName",
    da."digestSHA256" AS digest,
    da2."Id" AS daidnext,
    da2."createdDate" AS createddatenext,
    da2."digestSHA256" AS digestnext,
    COALESCE(da.text, da."ocrText") AS text,
    COALESCE(da2.text, da2."ocrText") AS textnext,
    regexp_replace("substring"(da."documentName", '^((?:[^-]+-){3}[^-]+)'::text), '\.[^.]+$'::text, ''::text) AS "Document ID"
   FROM (documents_archives da
     JOIN documents_archives da2 ON (((da."documentName" = da2."documentName") AND (da."digestSHA256" <> da2."digestSHA256") AND (da."Id" < da2."Id"))));; 
CREATE MATERIALIZED VIEW "mv_documents_matbase" AS
 WITH tags_pivot AS (
         SELECT documents_tags."Title",
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'document_documentNumber'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentnumber,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'document_documentName'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentname,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'document_documentPublicationDate'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentpublicationdateint,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Datum document'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentdatumdocumentnl,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Beoordeling'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentbeoordeling,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Uitzonderingsgrond(en)_linkname_1'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentuzg1,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Uitzonderingsgrond(en)_linkname_2'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentuzg2,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Uitzonderingsgrond(en)_linkname_3'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentuzg3,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Uitzonderingsgrond(en)_linkname_4'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentuzg4,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Uitzonderingsgrond(en)_linkname_5'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentuzg5,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Uitzonderingsgrond(en)_linkname_6'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentuzg6,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'DocumentDownload_linkhref'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS documentdownloadlink,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'Request_Onderdeel van_linkname_1'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS requestonderdeelvannaam,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'Request_Onderdeel van_linkhref_1'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS requestonderdeelvanlink,
            max(
                CASE
                    WHEN ((documents_tags.tag_key = 'Notificaties_1'::text) AND (documents_tags.tag_value ~~ 'Er loopt nog een procedure over dit document met een betrokkene.%'::text)) THEN 'Opgeschort'::text
                    ELSE NULL::text
                END) AS opgeschortprocedure,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_1'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink1,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_2'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink2,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_3'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink3,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_4'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink4,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_5'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink5,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_6'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink6,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_7'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink7,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_8'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink8,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_9'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink9,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_10'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink10,
            max(
                CASE
                    WHEN (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_11'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS reedsopenbaarlink11,
            documents_tags."validUntil"
           FROM documents_tags
          WHERE ((documents_tags.tag_key = ANY (ARRAY['document_documentNumber'::text, 'document_documentName'::text, 'document_documentPublicationDate'::text, 'About_Datum document'::text, 'Notificaties_1'::text, 'About_Uitzonderingsgrond(en)_linkname_1'::text, 'About_Uitzonderingsgrond(en)_linkname_2'::text, 'About_Uitzonderingsgrond(en)_linkname_3'::text, 'About_Uitzonderingsgrond(en)_linkname_4'::text, 'About_Uitzonderingsgrond(en)_linkname_5'::text, 'About_Uitzonderingsgrond(en)_linkname_6'::text, 'About_Beoordeling'::text, 'DocumentDownload_linkhref'::text, 'Request_Onderdeel van_linkname_1'::text, 'Request_Onderdeel van_linkhref_1'::text])) OR (documents_tags.tag_key ~~ 'Notificaties_1_linkhref_%'::text))
          GROUP BY documents_tags."Title", documents_tags."validUntil"
        )
 SELECT DISTINCT d."Locatie open.minvws.nl" AS documenturl,
    COALESCE(tp.documentnumber, d."Document ID") AS documentnumber,
    COALESCE(tp.documentname, d."Document naam") AS documentname,
    ' '::text AS correcteddate,
    tp.documentpublicationdateint,
    tp.documentdatumdocumentnl,
    tp.documentbeoordeling,
    COALESCE(
        CASE
            WHEN (POSITION((' '::text) IN (tp.documentuzg1)) > 0) THEN SUBSTRING(tp.documentuzg1 FROM 1 FOR (POSITION((' '::text) IN (tp.documentuzg1)) - 1))
            ELSE tp.documentuzg1
        END, tp.documentuzg1) AS documentuzg1,
    COALESCE(
        CASE
            WHEN (POSITION((' '::text) IN (tp.documentuzg2)) > 0) THEN SUBSTRING(tp.documentuzg2 FROM 1 FOR (POSITION((' '::text) IN (tp.documentuzg2)) - 1))
            ELSE tp.documentuzg2
        END, tp.documentuzg2) AS documentuzg2,
    COALESCE(
        CASE
            WHEN (POSITION((' '::text) IN (tp.documentuzg3)) > 0) THEN SUBSTRING(tp.documentuzg3 FROM 1 FOR (POSITION((' '::text) IN (tp.documentuzg3)) - 1))
            ELSE tp.documentuzg3
        END, tp.documentuzg3) AS documentuzg3,
    COALESCE(
        CASE
            WHEN (POSITION((' '::text) IN (tp.documentuzg4)) > 0) THEN SUBSTRING(tp.documentuzg4 FROM 1 FOR (POSITION((' '::text) IN (tp.documentuzg4)) - 1))
            ELSE tp.documentuzg4
        END, tp.documentuzg4) AS documentuzg4,
    COALESCE(
        CASE
            WHEN (POSITION((' '::text) IN (tp.documentuzg5)) > 0) THEN SUBSTRING(tp.documentuzg5 FROM 1 FOR (POSITION((' '::text) IN (tp.documentuzg5)) - 1))
            ELSE tp.documentuzg5
        END, tp.documentuzg5) AS documentuzg5,
    COALESCE(
        CASE
            WHEN (POSITION((' '::text) IN (tp.documentuzg6)) > 0) THEN SUBSTRING(tp.documentuzg6 FROM 1 FOR (POSITION((' '::text) IN (tp.documentuzg6)) - 1))
            ELSE tp.documentuzg6
        END, tp.documentuzg6) AS documentuzg6,
    tp.documentdownloadlink,
    tp.requestonderdeelvannaam,
    tp.requestonderdeelvanlink,
    tp.opgeschortprocedure,
    ((((((((((COALESCE((tp.reedsopenbaarlink1 || ','::text), ''::text) || COALESCE((tp.reedsopenbaarlink2 || ','::text), ''::text)) || COALESCE((tp.reedsopenbaarlink3 || ','::text), ''::text)) || COALESCE((tp.reedsopenbaarlink4 || ','::text), ''::text)) || COALESCE((tp.reedsopenbaarlink5 || ','::text), ''::text)) || COALESCE((tp.reedsopenbaarlink6 || ','::text), ''::text)) || COALESCE((tp.reedsopenbaarlink7 || ','::text), ''::text)) || COALESCE((tp.reedsopenbaarlink8 || ','::text), ''::text)) || COALESCE((tp.reedsopenbaarlink9 || ','::text), ''::text)) || COALESCE((tp.reedsopenbaarlink10 || ','::text), ''::text)) || COALESCE(tp.reedsopenbaarlink11, ''::text)) AS "reedsOpenbaarLinks"
   FROM (documents d
     LEFT JOIN tags_pivot tp ON ((d."Locatie open.minvws.nl" = tp."Title")));; 
CREATE MATERIALIZED VIEW "mv_documents_archives_documentname_content" AS
 WITH tag_values AS (
         SELECT documents_tags."Title",
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'document_documentName'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS docname,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'DocumentDownload_proposed_filename'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS filename2,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'document_documentPublicationDate'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS docpubdate,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Datum document'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS docpubdatenl,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Beoordeling'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS beoordeling
           FROM documents_tags
          WHERE (documents_tags.tag_key = ANY (ARRAY['document_documentName'::text, 'About_Datum document'::text, 'DocumentDownload_proposed_filename'::text, 'document_documentPublicationDate'::text, 'About_Beoordeling'::text]))
          GROUP BY documents_tags."Title"
        ), ranked_documents AS (
         SELECT d.id AS documentid,
            da."Id" AS archiveid,
            da."createdDate" AS created_date,
            d."Locatie open.minvws.nl" AS docurl,
            da."documentIndicatedDateCorrected" AS docdate,
            COALESCE(wut.tag_value, tv.docpubdate) AS indicated_date_corrected,
            tv.docpubdatenl,
            tv.docname,
            da."documentName" AS filename,
            tv.filename2,
            da."digestSHA256" AS filedigest,
            tv.beoordeling AS docverdict,
            COALESCE(TRIM(BOTH FROM da.text), TRIM(BOTH FROM da."ocrText"), 'n/a'::text) AS filetext,
            COALESCE(da."textDigestSHA256", da."ocrTextDigestSHA256", 'n/a'::text) AS textdigest,
            da."extractedId" AS extractedid,
            da.search_vector
           FROM (((documents_archives da
             JOIN documents d ON ((d."Definitief ID" = da."extractedId")))
             JOIN tag_values tv ON ((tv."Title" = d."Locatie open.minvws.nl")))
             LEFT JOIN wookey_user_tags wut ON (((d."Locatie open.minvws.nl" = wut.title) AND (wut.tag_key = 'indicatedDateCorrected'::text))))
        )
 SELECT documentid,
    archiveid,
    created_date,
    docurl,
    docdate,
    indicated_date_corrected,
    docpubdatenl,
    docname,
    filename,
    filename2,
    filedigest,
    docverdict,
    filetext,
    textdigest,
    extractedid,
    search_vector
   FROM ranked_documents;; 
CREATE MATERIALIZED VIEW "mv_documents_archives_content" AS
 WITH tag_values AS (
         SELECT documents_tags."Title",
            max(documents_tags."CreatedDate") AS tag_created_date,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'document_documentName'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS docname,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'DocumentDownload_proposed_filename'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS filename2,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'document_documentPublicationDate'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS docpubdate,
            max(
                CASE
                    WHEN (documents_tags.tag_key = 'About_Beoordeling'::text) THEN documents_tags.tag_value
                    ELSE NULL::text
                END) AS beoordeling
           FROM documents_tags
          WHERE (documents_tags.tag_key = ANY (ARRAY['document_documentName'::text, 'DocumentDownload_proposed_filename'::text, 'document_documentPublicationDate'::text, 'About_Beoordeling'::text]))
          GROUP BY documents_tags."Title"
        ), ranked_documents AS (
         SELECT d.id AS documentid,
            da."Id" AS archiveid,
            d."Locatie open.minvws.nl" AS docurl,
            d."FromFile" AS fromfile,
            tv.tag_created_date AS created_date,
            da."documentIndicatedDateCorrected" AS docdate,
            tv.docpubdate,
            tv.docname,
            da."documentName" AS filename,
            tv.filename2,
            da."digestSHA256" AS filedigest,
            tv.beoordeling AS docverdict,
            COALESCE(TRIM(BOTH FROM da.text), TRIM(BOTH FROM da."ocrText"), 'n/a'::text) AS filetext,
            COALESCE(da."textDigestSHA256", da."ocrTextDigestSHA256", 'n/a'::text) AS textdigest,
            row_number() OVER (PARTITION BY d.id ORDER BY da."documentIndicatedDateCorrected" DESC NULLS LAST) AS rn
           FROM ((documents_archives da
             JOIN documents d ON ((d."Definitief ID" = da."extractedId")))
             JOIN tag_values tv ON ((tv."Title" = d."Locatie open.minvws.nl")))
        )
 SELECT documentid,
    archiveid,
    docurl,
    fromfile,
    created_date,
    docdate,
    docpubdate,
    docname,
    filename,
    filename2,
    filedigest,
    docverdict,
    filetext,
    textdigest,
    rn
   FROM ranked_documents
  WHERE (rn = 1);; 

-- FUNCTIONS --
CREATE OR REPLACE FUNCTION public.digest(text, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_digest$function$
 
CREATE OR REPLACE FUNCTION public.digest(bytea, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_digest$function$
 
CREATE OR REPLACE FUNCTION public.hmac(text, text, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_hmac$function$
 
CREATE OR REPLACE FUNCTION public.hmac(bytea, bytea, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_hmac$function$
 
CREATE OR REPLACE FUNCTION public.crypt(text, text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_crypt$function$
 
CREATE OR REPLACE FUNCTION public.gen_salt(text)
 RETURNS text
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_gen_salt$function$
 
CREATE OR REPLACE FUNCTION public.gen_salt(text, integer)
 RETURNS text
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_gen_salt_rounds$function$
 
CREATE OR REPLACE FUNCTION public.encrypt(bytea, bytea, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_encrypt$function$
 
CREATE OR REPLACE FUNCTION public.decrypt(bytea, bytea, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_decrypt$function$
 
CREATE OR REPLACE FUNCTION public.encrypt_iv(bytea, bytea, bytea, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_encrypt_iv$function$
 
CREATE OR REPLACE FUNCTION public.decrypt_iv(bytea, bytea, bytea, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_decrypt_iv$function$
 
CREATE OR REPLACE FUNCTION public.gen_random_bytes(integer)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_random_bytes$function$
 
CREATE OR REPLACE FUNCTION public.gen_random_uuid()
 RETURNS uuid
 LANGUAGE c
 PARALLEL SAFE
AS '$libdir/pgcrypto', $function$pg_random_uuid$function$
 
CREATE OR REPLACE FUNCTION public.pgp_sym_encrypt(text, text)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_sym_encrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_sym_encrypt_bytea(bytea, text)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_sym_encrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_sym_encrypt(text, text, text)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_sym_encrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_sym_encrypt_bytea(bytea, text, text)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_sym_encrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_sym_decrypt(bytea, text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_sym_decrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_sym_decrypt_bytea(bytea, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_sym_decrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_sym_decrypt(bytea, text, text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_sym_decrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_sym_decrypt_bytea(bytea, text, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_sym_decrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_encrypt(text, bytea)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_encrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_encrypt_bytea(bytea, bytea)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_encrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_encrypt(text, bytea, text)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_encrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_encrypt_bytea(bytea, bytea, text)
 RETURNS bytea
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_encrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_decrypt(bytea, bytea)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_decrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_decrypt_bytea(bytea, bytea)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_decrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_decrypt(bytea, bytea, text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_decrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_decrypt_bytea(bytea, bytea, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_decrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_decrypt(bytea, bytea, text, text)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_decrypt_text$function$
 
CREATE OR REPLACE FUNCTION public.pgp_pub_decrypt_bytea(bytea, bytea, text, text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_pub_decrypt_bytea$function$
 
CREATE OR REPLACE FUNCTION public.pgp_key_id(bytea)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_key_id_w$function$
 
CREATE OR REPLACE FUNCTION public.armor(bytea)
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_armor$function$
 
CREATE OR REPLACE FUNCTION public.armor(bytea, text[], text[])
 RETURNS text
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_armor$function$
 
CREATE OR REPLACE FUNCTION public.dearmor(text)
 RETURNS bytea
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pg_dearmor$function$
 
CREATE OR REPLACE FUNCTION public.pgp_armor_headers(text, OUT key text, OUT value text)
 RETURNS SETOF record
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pgcrypto', $function$pgp_armor_headers$function$
 
CREATE OR REPLACE FUNCTION public.normal_rand(integer, double precision, double precision)
 RETURNS SETOF double precision
 LANGUAGE c
 STRICT
AS '$libdir/tablefunc', $function$normal_rand$function$
 
CREATE OR REPLACE FUNCTION public.crosstab(text)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$crosstab$function$
 
CREATE OR REPLACE FUNCTION public.crosstab2(text)
 RETURNS SETOF tablefunc_crosstab_2
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$crosstab$function$
 
CREATE OR REPLACE FUNCTION public.crosstab3(text)
 RETURNS SETOF tablefunc_crosstab_3
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$crosstab$function$
 
CREATE OR REPLACE FUNCTION public.crosstab4(text)
 RETURNS SETOF tablefunc_crosstab_4
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$crosstab$function$
 
CREATE OR REPLACE FUNCTION public.crosstab(text, integer)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$crosstab$function$
 
CREATE OR REPLACE FUNCTION public.crosstab(text, text)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$crosstab_hash$function$
 
CREATE OR REPLACE FUNCTION public.connectby(text, text, text, text, integer, text)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$connectby_text$function$
 
CREATE OR REPLACE FUNCTION public.connectby(text, text, text, text, integer)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$connectby_text$function$
 
CREATE OR REPLACE FUNCTION public.connectby(text, text, text, text, text, integer, text)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$connectby_text_serial$function$
 
CREATE OR REPLACE FUNCTION public.connectby(text, text, text, text, text, integer)
 RETURNS SETOF record
 LANGUAGE c
 STABLE STRICT
AS '$libdir/tablefunc', $function$connectby_text_serial$function$
 
CREATE OR REPLACE FUNCTION public.set_limit(real)
 RETURNS real
 LANGUAGE c
 STRICT
AS '$libdir/pg_trgm', $function$set_limit$function$
 
CREATE OR REPLACE FUNCTION public.show_limit()
 RETURNS real
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$show_limit$function$
 
CREATE OR REPLACE FUNCTION public.show_trgm(text)
 RETURNS text[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$show_trgm$function$
 
CREATE OR REPLACE FUNCTION public.similarity(text, text)
 RETURNS real
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$similarity$function$
 
CREATE OR REPLACE FUNCTION public.similarity_op(text, text)
 RETURNS boolean
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$similarity_op$function$
 
CREATE OR REPLACE FUNCTION public.word_similarity(text, text)
 RETURNS real
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$word_similarity$function$
 
CREATE OR REPLACE FUNCTION public.word_similarity_op(text, text)
 RETURNS boolean
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$word_similarity_op$function$
 
CREATE OR REPLACE FUNCTION public.word_similarity_commutator_op(text, text)
 RETURNS boolean
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$word_similarity_commutator_op$function$
 
CREATE OR REPLACE FUNCTION public.similarity_dist(text, text)
 RETURNS real
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$similarity_dist$function$
 
CREATE OR REPLACE FUNCTION public.word_similarity_dist_op(text, text)
 RETURNS real
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$word_similarity_dist_op$function$
 
CREATE OR REPLACE FUNCTION public.word_similarity_dist_commutator_op(text, text)
 RETURNS real
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$word_similarity_dist_commutator_op$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_in(cstring)
 RETURNS gtrgm
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_in$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_out(gtrgm)
 RETURNS cstring
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_out$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_consistent(internal, text, smallint, oid, internal)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_consistent$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_distance(internal, text, smallint, oid, internal)
 RETURNS double precision
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_distance$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_compress(internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_compress$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_decompress(internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_decompress$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_penalty(internal, internal, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_penalty$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_picksplit(internal, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_picksplit$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_union(internal, internal)
 RETURNS gtrgm
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_union$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_same(gtrgm, gtrgm, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gtrgm_same$function$
 
CREATE OR REPLACE FUNCTION public.gin_extract_value_trgm(text, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gin_extract_value_trgm$function$
 
CREATE OR REPLACE FUNCTION public.gin_extract_query_trgm(text, internal, smallint, internal, internal, internal, internal)
 RETURNS internal
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gin_extract_query_trgm$function$
 
CREATE OR REPLACE FUNCTION public.gin_trgm_consistent(internal, smallint, text, integer, internal, internal, internal, internal)
 RETURNS boolean
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gin_trgm_consistent$function$
 
CREATE OR REPLACE FUNCTION public.gin_trgm_triconsistent(internal, smallint, text, integer, internal, internal, internal)
 RETURNS "char"
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$gin_trgm_triconsistent$function$
 
CREATE OR REPLACE FUNCTION public.strict_word_similarity(text, text)
 RETURNS real
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$strict_word_similarity$function$
 
CREATE OR REPLACE FUNCTION public.strict_word_similarity_op(text, text)
 RETURNS boolean
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$strict_word_similarity_op$function$
 
CREATE OR REPLACE FUNCTION public.strict_word_similarity_commutator_op(text, text)
 RETURNS boolean
 LANGUAGE c
 STABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$strict_word_similarity_commutator_op$function$
 
CREATE OR REPLACE FUNCTION public.strict_word_similarity_dist_op(text, text)
 RETURNS real
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$strict_word_similarity_dist_op$function$
 
CREATE OR REPLACE FUNCTION public.strict_word_similarity_dist_commutator_op(text, text)
 RETURNS real
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/pg_trgm', $function$strict_word_similarity_dist_commutator_op$function$
 
CREATE OR REPLACE FUNCTION public.gtrgm_options(internal)
 RETURNS void
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE
AS '$libdir/pg_trgm', $function$gtrgm_options$function$
 
CREATE OR REPLACE FUNCTION public.vul_definitief_id()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF NEW."Definitief ID" IS NULL AND NEW."Locatie open.minvws.nl" IS NOT NULL THEN
    NEW."Definitief ID" := regexp_replace(
      NEW."Locatie open.minvws.nl",
      '^.*/',  -- vervangt alles tot en met de laatste slash
      ''
    );
  END IF;

  RETURN NEW;
END;
$function$
 
CREATE OR REPLACE FUNCTION public.update_extracted_id()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF NEW."extractedId" IS NULL OR NEW."extractedId" = '' THEN
	  NEW."extractedId" := substring(
	    NEW."documentName"
	    FROM '(VWS-WOO-[^-]+-\d{5,7}|VWS-WC-[^-]+-\d{1,7}|CCMO-WOO-[^-]+-\d{1,6}|CCMO-\d{4}-\d{5})'
	  );
	END IF;
  RETURN NEW;
END;
$function$
 
CREATE OR REPLACE FUNCTION public.fill_createddate_if_missing()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW."CreatedDate" IS NULL THEN
        NEW."CreatedDate" := CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;
END;
$function$
 

-- TRIGGERS --
CREATE TRIGGER trigger_vul_definitief_id BEFORE INSERT OR UPDATE ON public.documents FOR EACH ROW EXECUTE FUNCTION vul_definitief_id(); 
CREATE TRIGGER trg_update_extracted_id BEFORE INSERT OR UPDATE ON public.documents_archives FOR EACH ROW EXECUTE FUNCTION update_extracted_id(); 
CREATE TRIGGER trg_fill_createddate BEFORE INSERT ON public.documents FOR EACH ROW EXECUTE FUNCTION fill_createddate_if_missing(); 

-- INDEXES --
CREATE UNIQUE INDEX others_tags_pkey ON public.others_tags USING btree ("Id"); 
CREATE INDEX idx_others_tags_control_key ON public.others_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE INDEX idx_others_tags_tag_value ON public.others_tags USING btree (tag_value); 
CREATE INDEX idx_others_tags_title_key_value ON public.others_tags USING btree ("Title", tag_key, tag_value); 
CREATE INDEX idx_others_tags_validity ON public.others_tags USING btree ("validSince", "validUntil"); 
CREATE INDEX idx_others_tags_validsince_validuntil_key ON public.others_tags USING btree ("validSince", "validUntil"); 
CREATE UNIQUE INDEX unique_ot_title_tag_key_tag_value_validuntil ON public.others_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE INDEX idx_others_tags_createddate ON public.others_tags USING btree ("CreatedDate"); 
CREATE INDEX idx_requests_tags_validsince_validuntil_key ON public.requests_tags USING btree ("validSince", "validUntil"); 
CREATE UNIQUE INDEX requests_tags_pkey ON public.requests_tags USING btree ("Id"); 
CREATE INDEX idx_requests_tags_title_key_value ON public.requests_tags USING btree ("Title", tag_key, tag_value); 
CREATE UNIQUE INDEX unique_rt_title_tag_key_tag_value_validuntil ON public.requests_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE INDEX idx_requests_tags_control_key ON public.requests_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE INDEX idx_requests_tags_validity ON public.requests_tags USING btree ("validSince", "validUntil"); 
CREATE INDEX idx_requests_tags_tag_value ON public.requests_tags USING btree (tag_value); 
CREATE INDEX idx_tag_value_trgm ON public.basisregistraties_tags USING gin (tag_value gin_trgm_ops); 
CREATE INDEX idx_tag_value_md5 ON public.basisregistraties_tags USING btree (md5(tag_value)); 
CREATE UNIQUE INDEX basisregistraties_tags_pkey ON public.basisregistraties_tags USING btree (id); 
CREATE INDEX idx_basisregistraties_title_key_value ON public.basisregistraties_tags USING btree ("Title", tag_key, tag_value); 
CREATE INDEX idx_documents_tags_title_key_value_until ON public.documents_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE INDEX idx_documents_tags_title ON public.documents_tags USING btree ("Title"); 
CREATE UNIQUE INDEX unique_dt_title_tag_key_tag_value_validuntil ON public.documents_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE INDEX idx_dt_tag_value_trgm ON public.documents_tags USING gin (tag_value gin_trgm_ops); 
CREATE INDEX idx_dt_filter_key_until ON public.documents_tags USING btree (tag_key, "validUntil"); 
CREATE INDEX idx_documents_tags_tag_key ON public.documents_tags USING btree (tag_key); 
CREATE INDEX idx_documents_tags_title_key ON public.documents_tags USING btree ("Title", tag_key); 
CREATE UNIQUE INDEX documents_tags_pkey ON public.documents_tags USING btree ("Id"); 
CREATE INDEX idx_documents_tags_title_key_value ON public.documents_tags USING btree ("Title", tag_key, tag_value); 
CREATE INDEX "idx_documents_tags_validUntil" ON public.documents_tags USING btree ("validUntil"); 
CREATE INDEX "idx_documents_tags_Title_tag_key_validSince" ON public.documents_tags USING btree ("Title", tag_key, "validSince"); 
CREATE INDEX idx_dt_key_validuntil_title ON public.documents_tags USING btree (tag_key, "validUntil", "Title"); 
CREATE INDEX idx_documents_tags_createddate ON public.documents_tags USING btree ("CreatedDate"); 
CREATE UNIQUE INDEX unique_rtoo_title_tag_key_tag_value_validuntil ON public.requests_oo_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE UNIQUE INDEX requests_oo_tags_pkey ON public.requests_oo_tags USING btree ("Id"); 
CREATE INDEX idx_requests_oo_tags_control_key ON public.requests_oo_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE INDEX idx_requests_oo_tags_tag_value ON public.requests_oo_tags USING btree (tag_value); 
CREATE INDEX idx_requests_oo_tags_title_key_value ON public.requests_oo_tags USING btree ("Title", tag_key, tag_value); 
CREATE INDEX idx_requests_oo_tags_validity ON public.requests_oo_tags USING btree ("validSince", "validUntil"); 
CREATE INDEX idx_requests_oo_tags_validsince_validuntil_key ON public.requests_oo_tags USING btree ("validSince", "validUntil"); 
CREATE UNIQUE INDEX unique_dtoo_title_tag_key_tag_value_validuntil ON public.documents_oo_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE UNIQUE INDEX documents_oo_tags_pkey ON public.documents_oo_tags USING btree ("Id"); 
CREATE INDEX idx_documents_oo_tags_control_key ON public.documents_oo_tags USING btree ("Title", tag_key, tag_value, "validUntil"); 
CREATE INDEX idx_documents_oo_tags_tag_value ON public.documents_oo_tags USING btree (tag_value); 
CREATE INDEX idx_documents_oo_tags_title_key_value ON public.documents_oo_tags USING btree ("Title", tag_key, tag_value); 
CREATE INDEX idx_documents_oo_tags_validity ON public.documents_oo_tags USING btree ("validSince", "validUntil"); 
CREATE INDEX idx_documents_oo_tags_validsince_validuntil_key ON public.documents_oo_tags USING btree ("validSince", "validUntil"); 
CREATE INDEX idx_documents_oo_tags_createddate ON public.documents_oo_tags USING btree ("CreatedDate"); 
CREATE UNIQUE INDEX wookey_users_pkey ON public.wookey_users USING btree (id); 
CREATE INDEX idx_document_documentstatuscheckeddate ON public.documents USING btree ("DocumentStatusCheckedDate"); 
CREATE INDEX idx_document_fromfile ON public.documents USING btree ("FromFile"); 
CREATE INDEX idx_document_url ON public.documents USING btree ("Locatie open.minvws.nl"); 
CREATE INDEX idx_documents_definitief_id ON public.documents USING btree ("Definitief ID"); 
CREATE UNIQUE INDEX documents_pkey ON public.documents USING btree (id); 
CREATE INDEX idx_documents_locatie_docid ON public.documents USING btree ("Locatie open.minvws.nl", "Document ID"); 
CREATE UNIQUE INDEX documents_document_id_fromfile_key ON public.documents USING btree ("Document ID", "FromFile"); 
CREATE INDEX idx_documents_locatie ON public.documents USING btree ("Locatie open.minvws.nl"); 
CREATE INDEX idx_documents_document_id ON public.documents USING btree ("Document ID"); 
CREATE INDEX idx_documents_document_id_fromfile ON public.documents USING btree ("Document ID", "FromFile"); 
CREATE UNIQUE INDEX documents_definitief_id_fromfile_key ON public.documents USING btree ("Definitief ID", "FromFile"); 
CREATE UNIQUE INDEX dutch_words_pkey ON public.dutch_words USING btree (word); 
CREATE INDEX idx_document_inventaris_url ON public.documents_inventaris USING btree ("Locatie open.minvws.nl", "Locatie document ID"); 
CREATE UNIQUE INDEX documents_inventaris_pkey ON public.documents_inventaris USING btree ("Id"); 
CREATE INDEX idx_documents_inventaris_createddate ON public.documents_inventaris USING btree ("CreatedDate"); 
CREATE UNIQUE INDEX wookey_users_uploads_user_id_file_hash_key ON public.wookey_users_uploads USING btree (user_id, file_hash); 
CREATE UNIQUE INDEX wookey_users_uploads_pkey ON public.wookey_users_uploads USING btree (id); 
CREATE UNIQUE INDEX wookey_verification_codes_pkey ON public.wookey_verification_codes USING btree (user_id, code); 
CREATE INDEX idx_mv_docid ON public.mv_document_conflicts USING btree ("Document ID"); 
CREATE INDEX idx_documents_archives_documentname ON public.documents_archives USING btree ("documentName"); 
CREATE INDEX idx_notextdate ON public.documents_archives USING btree ("noTextDate"); 
CREATE INDEX idx_documents_archives_digest ON public.documents_archives USING btree ("digestSHA256"); 
CREATE UNIQUE INDEX documents_archives_pkey ON public.documents_archives USING btree ("Id"); 
CREATE INDEX idx_id ON public.documents_archives USING btree ("Id"); 
CREATE INDEX idx_documenttype ON public.documents_archives USING btree ("documentType"); 
CREATE INDEX idx_fromfilepath ON public.documents_archives USING btree ("fromFilePath"); 
CREATE INDEX idx_fromfile ON public.documents_archives USING btree ("fromFile"); 
CREATE INDEX idx_documentname ON public.documents_archives USING btree ("documentName"); 
CREATE INDEX idx_dossierreference ON public.documents_archives USING btree ("dossierReference"); 
CREATE INDEX idx_lastseen ON public.documents_archives USING btree ("lastSeen"); 
CREATE INDEX idx_createddate ON public.documents_archives USING btree ("createdDate"); 
CREATE INDEX idx_search_vector ON public.documents_archives USING gin (search_vector); 
CREATE UNIQUE INDEX unique_documentname_fromfilepath ON public.documents_archives USING btree ("documentName", "fromFilePath"); 
CREATE INDEX idx_digestsha256 ON public.documents_archives USING btree ("digestSHA256"); 
CREATE INDEX idx_extractedid ON public.documents_archives USING btree ("extractedId"); 
CREATE INDEX idx_documents_archives_notextdate ON public.documents_archives USING btree ("noTextDate"); 
CREATE UNIQUE INDEX wookey_user_tags_pkey ON public.wookey_user_tags USING btree (id); 
CREATE UNIQUE INDEX wookey_user_tags_user_id_title_tag_key_valid_until ON public.wookey_user_tags USING btree (user_id, title, tag_key, valid_until); 
CREATE INDEX idx_mv_doc_date_corrected ON public.mv_documents_archives_documentname_content USING btree (indicated_date_corrected); 
CREATE INDEX idx_mv_docurl_date ON public.mv_documents_archives_documentname_content USING btree (docurl, indicated_date_corrected DESC); 
CREATE UNIQUE INDEX wookey_keyword_queries_pkey ON public.wookey_keyword_queries USING btree (id); 
CREATE INDEX idx_query ON public.wookey_keyword_queries USING btree (query); 
CREATE UNIQUE INDEX wookey_subscriptions_pkey ON public.wookey_subscriptions USING btree (id); 
