shinkan-jinkendo/backend/migrations/047_legal_documents.sql

-- Migration 047: Admin-konfigurierbare Rechtstexte
-- Tabellen: legal_documents (versioniert), legal_document_audit (Änderungslog)
-- document_type: impressum | privacy_policy | terms_of_use | media_policy
-- status:        draft | published | archived
-- Partial unique index: nur genau ein published-Dokument pro document_type erlaubt.

CREATE TABLE IF NOT EXISTS legal_documents (
    id                      SERIAL PRIMARY KEY,
    document_type           VARCHAR(50) NOT NULL
                                CHECK (document_type IN ('impressum', 'privacy_policy', 'terms_of_use', 'media_policy')),
    version                 INT NOT NULL DEFAULT 1,
    title                   VARCHAR(255) NOT NULL,
    content_sections        JSONB NOT NULL DEFAULT '[]',
    status                  VARCHAR(20) NOT NULL DEFAULT 'draft'
                                CHECK (status IN ('draft', 'published', 'archived')),
    change_note             TEXT,
    created_at              TIMESTAMP DEFAULT NOW(),
    updated_at              TIMESTAMP DEFAULT NOW(),
    created_by_profile_id   INT REFERENCES profiles(id) ON DELETE SET NULL,
    published_by_profile_id INT REFERENCES profiles(id) ON DELETE SET NULL,
    published_at            TIMESTAMP
);

-- Sicherstellt: pro document_type maximal ein published-Datensatz
CREATE UNIQUE INDEX IF NOT EXISTS legal_documents_unique_published
    ON legal_documents (document_type)
    WHERE status = 'published';

CREATE TABLE IF NOT EXISTS legal_document_audit (
    id                      SERIAL PRIMARY KEY,
    legal_document_id       INT NOT NULL REFERENCES legal_documents(id) ON DELETE CASCADE,
    action                  VARCHAR(50) NOT NULL,
    changed_by_profile_id   INT REFERENCES profiles(id) ON DELETE SET NULL,
    change_note             TEXT,
    previous_status         VARCHAR(20),
    created_at              TIMESTAMP DEFAULT NOW()
);