Dokumenten-Check/Scripts/Datenmodell DocumentCheck.sql

drop table dc_users cascade constraints;
drop table dc_document_types cascade constraints;
drop table dc_user_roles cascade constraints;
drop table dc_question_catalogs cascade constraints;
drop table dc_question_categories cascade constraints;
drop table dc_questions cascade constraints;
drop table dc_reference_documents cascade constraints;
drop table dc_projects cascade constraints;
drop table dc_project_documents cascade constraints;
drop table dc_results cascade constraints;
-- create tables

create table dc_users (
    id               number generated by default on null as identity
                     constraint dc_users_id_pk primary key,
    username         varchar2(50 char)
                     constraint users_username_unq unique not null,
    email            varchar2(100 char)
                     constraint users_email_unq unique not null,
    password_hash    varchar2(255 char),
    role             varchar2(20 char),
    is_active        number default on null 1,
    created_at       date default on null sysdate,
    updated_at       date default on null sysdate,
    row_version      integer not null,
    created          date not null,
    created_by       varchar2(255 char) not null,
    updated          date not null,
    updated_by       varchar2(255 char) not null
);

create table dc_document_types (
    id             number generated by default on null as identity
                   constraint dc_document_types_id_pk primary key,
    name           varchar2(100 char) not null,
    description    clob,
    row_version    integer not null,
    created        date not null,
    created_by     varchar2(255 char) not null,
    updated        date not null,
    updated_by     varchar2(255 char) not null
);

create table dc_reference_documents (
    id             number generated by default on null as identity
                   constraint dc_reference_documents_id_pk primary key,
    uploaded_by    number                   constraint dc_reference_documents_uploaded_by_fk
                   references dc_users,
    document_type_id number constraint dc_reference_documents_doc_type_fk
                   references dc_document_types,                   
    name           varchar2(100 char) not null,
    description    clob,
    file_path      varchar2(500 char),
    upload_date    date default on null sysdate,
    mime_type      varchar2(100 char),
    row_version    integer not null,
    created        date not null,
    created_by     varchar2(255 char) not null,
    updated        date not null,
    updated_by     varchar2(255 char) not null
);

-- table index
create index dc_reference_documents_i1 on dc_reference_documents (uploaded_by);









create table dc_user_roles (
    id             number generated by default on null as identity
                   constraint dc_user_roles_id_pk primary key,
    role_name      varchar2(20 char)
                   constraint user_roles_role_name_unq unique not null,
    description    clob,
    row_version    integer not null,
    created        date not null,
    created_by     varchar2(255 char) not null,
    updated        date not null,
    updated_by     varchar2(255 char) not null
);



create table dc_question_catalogs (
    id             number generated by default on null as identity
                   constraint dc_question_catalogs_id_pk primary key,
    created_by_user     number                   constraint dc_question_catalogs_created_by_fk
                   references dc_users,
    based_on_reference number                    constraint dc_question_catalogs_reference_document_fk                   
                   references dc_reference_documents,
    document_type_id number constraint dc_question_catalogs_doc_type_fk
                   references dc_document_types,                   
    name           varchar2(100 char) not null,
    description    varchar2(4000 char),
    row_version    integer not null,
    created        date not null,
    created_by     varchar2(255 char) not null,
    updated        date not null,
    updated_by     varchar2(255 char) not null
);

-- table index
create index dc_question_catalogs_i1 on dc_question_catalogs (created_by);



create table dc_question_categories (
    id             number generated by default on null as identity
                   constraint dc_question_categories_id_pk primary key,
    catalog_id     number                   constraint dc_question_categories_catalog_id_fk
                   references dc_question_catalogs,
    name           varchar2(100 char) not null,
    description    varchar2(4000 char),
    row_version    integer not null,
    created        date not null,
    created_by     varchar2(255 char) not null,
    updated        date not null,
    updated_by     varchar2(255 char) not null
);

-- table index
create index dc_question_categories_i1 on dc_question_categories (catalog_id);



create table dc_questions (
    id                     number generated by default on null as identity
                           constraint dc_questions_id_pk primary key,
    category_id            number                           constraint dc_questions_category_id_fk
                           references dc_question_categories,
    question_text          clob not null,
    evaluation_type        varchar2(20 char),
    threshold              number,
    result_handling        varchar2(20 char),
    example_0_percent      clob,
    example_100_percent    clob,
    row_version            integer not null,
    created                date not null,
    created_by             varchar2(255 char) not null,
    updated                date not null,
    updated_by             varchar2(255 char) not null
);

-- table index
create index dc_questions_i1 on dc_questions (category_id);







create table dc_projects (
    id                    number generated by default on null as identity
                          constraint dc_projects_id_pk primary key,
    catalog_id            number                          constraint dc_projects_catalog_id_fk
                          references dc_question_catalogs,
    created_by_user            number                          constraint dc_projects_created_by_fk
                          references dc_users,                        
    name                  varchar2(100 char) not null,
    description           clob,
    status                varchar2(20 char) constraint dc_projects_status_ck
                          check (status in ('PENDING','IN_PROGRESS','COMPLETED')),
    progress              number default on null 0,
    completed_at          date,
    notification_email    varchar2(100 char),
    notify_ok             char(1 char)      default 'Y' not null
                          constraint dc_projects_notify_ok_ck
                          check (notify_ok in ('Y','N')),
    notify_hinweis        char(1 char)      default 'Y' not null
                          constraint dc_projects_notify_hinweis_ck
                          check (notify_hinweis in ('Y','N')),
    notify_abweichend     char(1 char)      default 'Y' not null
                          constraint dc_projects_notify_abweichend_ck
                          check (notify_abweichend in ('Y','N')),
    row_version           integer not null,
    created               date not null,
    created_by            varchar2(255 char) not null,
    updated               date not null,
    updated_by            varchar2(255 char) not null
);

-- table index
create index dc_projects_i1 on dc_projects (catalog_id);

create index dc_projects_i2 on dc_projects (created_by);



create table dc_project_documents (
    id                 number generated by default on null as identity
                       constraint dc_project_documents_id_pk primary key,
    project_id         number                       constraint dc_project_documents_project_id_fk
                       references dc_projects,
    document_type_id number constraint dc_project_documents_doc_type_fk
                   references dc_document_types,   
    catalog_id     number                   constraint dc_project_documents_catalog_id_fk
                   references dc_question_catalogs,                                       
    status             varchar2(20 char) default on null 'PENDING'
                       constraint dc_project_documents_status_ck
                       check (status in ('PENDING','IN_PROGRESS','COMPLETED','ERROR')),
    progress           number default on null 0,
    original_file      blob,
    original_text      clob,
    translated_text    clob,
    filename           varchar2(255 char) not null,
    mime_type          varchar2(100 char),
    uploaded_at        date default on null sysdate,
    row_version        integer not null,
    created            date not null,
    created_by         varchar2(255 char) not null,
    updated            date not null,
    updated_by         varchar2(255 char) not null
);

-- table index
create index dc_project_documents_i1 on dc_project_documents (project_id);



create table dc_results (
    id              number generated by default on null as identity
                    constraint dc_results_id_pk primary key,
    project_id      number                    constraint dc_results_project_id_fk
                    references dc_projects,
    question_id     number                    constraint dc_results_question_id_fk
                    references dc_questions,
    doc_id          number                    constraint dc_results_doc_id_fk
                    references dc_project_documents,
    answer          clob,
    score           number,
    result_type     varchar2(20 char) constraint dc_results_result_type_ck
                    check (result_type in ('OK','UNKLAR','NOK')),
    deviation       number default on null 0,
    warning         number default on null 0,
    the_comment     clob,
    evaluated_at    date default on null sysdate,
    row_version     integer not null,
    created         date not null,
    created_by      varchar2(255 char) not null,
    updated         date not null,
    updated_by      varchar2(255 char) not null
);

-- table index
create index dc_results_i1 on dc_results (project_id);

create index dc_results_i2 on dc_results (question_id);

create index dc_results_i3 on dc_results (doc_id);



-- triggers
create or replace trigger dc_users_biu
    before insert or update
    on dc_users
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_users_biu;
/


create or replace trigger dc_user_roles_biu
    before insert or update
    on dc_user_roles
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_user_roles_biu;
/

create or replace trigger dc_document_types_biu
    before insert or update
    on dc_document_types
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_document_types_biu;
/

create or replace trigger dc_question_catalogs_biu
    before insert or update
    on dc_question_catalogs
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_question_catalogs_biu;
/


create or replace trigger dc_question_categories_biu
    before insert or update
    on dc_question_categories
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_question_categories_biu;
/


create or replace trigger dc_questions_biu
    before insert or update
    on dc_questions
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_questions_biu;
/


create or replace trigger dc_reference_documents_biu
    before insert or update
    on dc_reference_documents
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_reference_documents_biu;
/


create or replace trigger dc_projects_biu
    before insert or update
    on dc_projects
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_projects_biu;
/


create or replace trigger dc_project_documents_biu
    before insert or update
    on dc_project_documents
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_project_documents_biu;
/


create or replace trigger dc_results_biu
    before insert or update
    on dc_results
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := NVL(:old.row_version, 0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end dc_results_biu;
/


-- Generated by Quick SQL 1.2.9 13.4.2026, 17:07:42

/*
users
  id number generated by default on null as identity constraint users_id_pk primary key
  username vc50 /nn /unique
  email vc100 /nn /unique
  password_hash vc255
  role vc20
  is_active num /default 1
  created_at date /default sysdate
  updated_at date /default sysdate

user_roles
  id number generated by default on null as identity constraint user_roles_id_pk primary key
  role_name vc20 /nn /unique
  description clob

question_catalogs
  id number generated by default on null as identity constraint question_catalogs_id_pk primary key
  name vc100 /nn
  description clob
  created_by num /references users
  created_at date /default sysdate
  updated_at date /default sysdate

question_categories
  id number generated by default on null as identity constraint question_categories_id_pk primary key
  catalog_id num /references question_catalogs
  name vc100 /nn
  description clob

questions
  id number generated by default on null as identity constraint questions_id_pk primary key
  category_id num /references question_categories
  question_text clob /nn
  evaluation_type vc20
  threshold num
  result_handling vc20
  example_0_percent clob
  example_100_percent clob

reference_documents
  id number generated by default on null as identity constraint reference_documents_id_pk primary key
  name vc100 /nn
  description clob
  file_path vc500
  uploaded_by num /references users
  upload_date date /default sysdate
  mime_type vc100

projects
  id number generated by default on null as identity constraint projects_id_pk primary key
  name vc100 /nn
  description clob
  catalog_id num /references question_catalogs
  created_by num /references users
  created_at date /default sysdate
  status vc20 /check PENDING, IN_PROGRESS, COMPLETED
  progress num /default 0
  completed_at date
  notification_email vc100

project_documents
  id number generated by default on null as identity constraint project_documents_id_pk primary key
  project_id num /references projects
  original_file blob
  translated_file blob
  filename vc255 /nn
  mime_type vc100
  uploaded_at date /default sysdate

results
  id number generated by default on null as identity constraint results_id_pk primary key
  project_id num /references projects
  question_id num /references questions
  doc_id num /references project_documents
  answer clob
  score num
  result_type vc20 /check OK, UNKLAR, NOK
  deviation num /default 0
  warning num /default 0
  comment clob
  evaluated_at date /default sysdate



 Non-default options:
# settings = {"apex":"Y","auditcols":"Y","db":"23","drop":"Y","prefix":"Dc","pk":"IDENTITY","rowversion":"Y"}

*/