-- Dokumentation, siehe README.md. Wird zur Laufzeit idempotent von
-- internal/tenantstore, internal/userstore, internal/storage und
-- internal/audit initSchema()-Funktionen erzeugt.

CREATE TABLE IF NOT EXISTS tenants (
    id         BIGSERIAL PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    slug       VARCHAR(100) UNIQUE NOT NULL,
    domain     VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_tenants_domain ON tenants (domain) WHERE domain IS NOT NULL;

CREATE TABLE IF NOT EXISTS users (
    id            BIGSERIAL PRIMARY KEY,
    username      VARCHAR(100) UNIQUE NOT NULL,
    email         VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL DEFAULT '',
    role          VARCHAR(20)  NOT NULL DEFAULT 'user',
    active        BOOLEAN      NOT NULL DEFAULT true,
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    last_login_at TIMESTAMPTZ,
    tenant_id     BIGINT
);
CREATE INDEX IF NOT EXISTS idx_users_tenant ON users (tenant_id);

CREATE TABLE IF NOT EXISTS token_blacklist (
    jti        VARCHAR(255) PRIMARY KEY,
    expires_at TIMESTAMPTZ  NOT NULL
);

CREATE TABLE IF NOT EXISTS documents (
    id            BIGSERIAL PRIMARY KEY,
    tenant_id     BIGINT NOT NULL,
    title         TEXT NOT NULL,
    doc_type      TEXT,
    correspondent TEXT,
    storage_path  TEXT NOT NULL,
    content_hash  TEXT NOT NULL,
    ocr_text      TEXT,
    retain_until  DATE,
    source        TEXT,   -- z.B. 'upload' | 'archivmail_import' (kein Importer implementiert)
    source_ref    TEXT,   -- externe Referenz (z.B. archivmail Mail-ID via API-Pull)
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_documents_tenant ON documents(tenant_id);

CREATE TABLE IF NOT EXISTS audit_log (
    id          BIGSERIAL    PRIMARY KEY,
    timestamp   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    event_type  VARCHAR(50)  NOT NULL,
    username    VARCHAR(255) NOT NULL DEFAULT '',
    ip_address  VARCHAR(45)  NOT NULL DEFAULT '',
    document_id VARCHAR(64)  NOT NULL DEFAULT '',
    success     BOOLEAN      NOT NULL DEFAULT true,
    detail      TEXT         NOT NULL DEFAULT '',
    tenant_id   BIGINT
);
-- append-only: BEFORE UPDATE OR DELETE trigger raises, see internal/audit/audit.go