nexarch/dms/migrations/tenant/0001_documents.up.sql

-- Kern-Entitaeten des DMS (FDN-02): Dokument, Datei-Revision, Ordner, Tag,
-- Metadatenfeld. Laeuft in der DB EINES Mandanten (Modell C, siehe Core
-- TEN-01) — keine tenant_id-Spalte, die Tenant-Zugehoerigkeit ist implizit
-- durch die Datenbankverbindung gegeben. FK auf users(id) spiegelt das
-- Benutzer-Datenmodell aus Core IAM-01 (migrations/tenant/0001_users.up.sql
-- im NEXARCH-Core-Modul) — Auth/Benutzerverwaltung liegt vollstaendig in
-- Core (siehe "Nicht Bestandteil" in FDN-02), diese Migration dupliziert sie
-- NICHT, sondern setzt sie als bereits vorhanden voraus (users-Tabelle wird
-- durch Cores eigene Migration in derselben physischen Tenant-Datenbank
-- angelegt, bevor DMS-Migrationen laufen).
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE folders (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_folder_id UUID REFERENCES folders(id) ON DELETE CASCADE,
    name             TEXT NOT NULL,
    created_by       UUID NOT NULL REFERENCES users(id),
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_folders_parent_folder_id ON folders(parent_folder_id);

CREATE TABLE documents (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    folder_id           UUID REFERENCES folders(id) ON DELETE SET NULL,
    title               TEXT NOT NULL,
    -- current_revision_id verweist erst NACH der Anlage von file_revisions
    -- auf eine Zeile (siehe ALTER TABLE unten) — beim INSERT eines Dokuments
    -- existiert noch keine Revision, daher NULLable und zirkulaer per
    -- nachtraeglichem FOREIGN KEY statt Inline-Referenz geloest.
    current_revision_id UUID,
    created_by          UUID NOT NULL REFERENCES users(id),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at          TIMESTAMPTZ
);
CREATE INDEX idx_documents_folder_id ON documents(folder_id);
CREATE INDEX idx_documents_created_by ON documents(created_by);

CREATE TABLE file_revisions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id     UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    revision_number INT NOT NULL,
    -- storage_key ist ein Platzhalter fuer die Objekt-Storage-Abstraktion
    -- (FDN-03, "Nicht Bestandteil" dieser Kachel) — hier nur die Spalte, die
    -- spaetere Kachel legt fest, was tatsaechlich dahinter liegt.
    storage_key     TEXT NOT NULL,
    checksum_sha256 TEXT NOT NULL,
    size_bytes      BIGINT NOT NULL,
    mime_type       TEXT NOT NULL,
    created_by      UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (document_id, revision_number)
);
CREATE INDEX idx_file_revisions_document_id ON file_revisions(document_id);

ALTER TABLE documents
    ADD CONSTRAINT fk_documents_current_revision
    FOREIGN KEY (current_revision_id) REFERENCES file_revisions(id) ON DELETE SET NULL;

CREATE TABLE tags (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name       TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE document_tags (
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    tag_id      UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (document_id, tag_id)
);
CREATE INDEX idx_document_tags_tag_id ON document_tags(tag_id);

CREATE TABLE metadata_fields (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    field_key  TEXT NOT NULL UNIQUE,
    label      TEXT NOT NULL,
    field_type TEXT NOT NULL CHECK (field_type IN ('text', 'number', 'date', 'bool', 'select')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE document_metadata_values (
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    field_id    UUID NOT NULL REFERENCES metadata_fields(id) ON DELETE CASCADE,
    value       TEXT NOT NULL,
    PRIMARY KEY (document_id, field_id)
);
CREATE INDEX idx_document_metadata_values_field_id ON document_metadata_values(field_id);