====== 5. Auth-Migration — Legacy-Systeme ======

//Stand: 2026-03-05//

Übergeordnet: [[de:int:wvdsshell:notes:01-siam:start|Auth-Architektur — Gesamtübersicht]]
Verwandt: [[de:int:wvdsshell:notes:01-siam:auth-database|4. Datenschicht — DB-Schemas]]

===== Ausgangssituation =====

Vor der Migration existierten Benutzer und Rollen in zwei vollständig getrennten Systemen. ENIVERSCAFM verwaltete Auth-Daten in eigenen Tabellen (''auth.*''), die aus der historischen ENIVERS-Entwicklung stammten. Das LD-System führte parallel dazu ''tblPerson'' und ''tblRolle'' — eine eigene Benutzer- und Rollenverwaltung für die Elektrikerseite, mit eigenem Passwort-Mechanismus und eigener Admin-Oberfläche.

Ein Benutzer, der in beiden Systemen aktiv war, hatte in der Praxis zwei Identitäten: eine für AMED-Funktionen, eine für LD-Funktionen. Änderungen an Rollen oder Passwörtern mussten doppelt gepflegt werden. Es gab keine gemeinsame Audit-Spur — ob ein Benutzer in System A oder System B tätig war, ließ sich nicht zusammenhängend nachvollziehen.

Die Migration ist deshalb kein rein technischer Vorgang, sondern die Umsetzung einer konzeptuellen Entscheidung: Es gibt eine Benutzeridentität, nicht zwei. Phasen 1 und 2 haben diesen Zustand hergestellt — 558 Benutzer aus zwei Quellsystemen, konsolidiert in ENIVERSSIAM, ohne Anpassungen am bestehenden Anwendungscode, da ENIVERSCAFM die Tabellen weiterhin über Synonyme erreicht. Ab Phase 3 folgt die inhaltliche Migration: Assets, Prüfprozesse, Legacy-Rohrdaten.

===== Migrations-Übersicht =====

^ Phase ^ Inhalt                                                         ^ Status           ^ Ergebnis                                                        ^
| 1     | ''auth.*'' + ''core.person'': ENIVERSCAFM → ENIVERSSIAM       | ✓ Abgeschlossen  | 15 Permissions, 26 Rollen, 241 User, 9.445 Rollenzuordnungen   |
| 2     | LD-System: ENIVERS ''tblPerson/tblRolle'' → ENIVERSSIAM        | ✓ Abgeschlossen  | 317 Personen, 314 User, 38 Rollen, 9.476 Rollenzuordnungen     |
| 3     | AMED Bestandsdaten (Branches, Devices, Facilities) → ENIVERSCAFM | Ausstehend     | —                                                               |
| 4     | ENIVERSPIMS aufbauen; WIS/PROOF/TecDB migrieren                | Ausstehend       | —                                                               |
| 5     | ENIVERSASYS aufbauen; Feature-Flags aus ENIVERSCAFM auslagern  | Ausstehend       | —                                                               |

===== Phase 1 — ENIVERSCAFM auth.* → ENIVERSSIAM =====

==== Ausgangslage ====

Die Auth-Tabellen (''auth.*'') und die Personen-Tabelle (''hr.person'') lagen ursprünglich in
**ENIVERSCAFM**. Ziel war die Konsolidierung: Auth-Daten gehören in die dedizierte
Identity-Datenbank **ENIVERSSIAM**. ENIVERSCAFM erhält danach Synonyme — bestehender
Code funktioniert transparent weiter ohne Änderungen.

==== Mapping: auth.* (13 Tabellen + core.person) ====

^ ENIVERSCAFM (Quelle)             ^ ENIVERSSIAM (Ziel)               ^ Verfahren                      ^
| ''auth.permission''               | ''auth.permission''              | Daten kopiert, Tabelle dropped, Synonym erstellt |
| ''auth.role''                     | ''auth.role''                    | Daten kopiert, Tabelle dropped, Synonym erstellt |
| ''auth.[user]''                   | ''auth.[user]''                  | Daten kopiert, Tabelle dropped, Synonym erstellt |
| ''auth.user_role''                | ''auth.user_role''               | Daten kopiert, Tabelle dropped, Synonym erstellt |
| ''auth.role_permission''          | ''auth.role_permission''         | Daten kopiert, Tabelle dropped, Synonym erstellt |
| ''auth.device''                   | ''auth.device''                  | Synonym (Tabelle war leer)     |
| ''auth.backup_code''              | ''auth.backup_code''             | Synonym (Tabelle war leer)     |
| ''auth.known_location''           | ''auth.known_location''          | Synonym (Tabelle war leer)     |
| ''auth.mfa_session''              | ''auth.mfa_session''             | Synonym (Tabelle war leer)     |
| ''auth.nonce_cache''              | ''auth.nonce_cache''             | Synonym (Tabelle war leer)     |
| ''auth.rate_limit_event''         | ''auth.rate_limit_event''        | Synonym (Tabelle war leer)     |
| ''auth.refresh_token_family''     | ''auth.refresh_token_family''    | Synonym (Tabelle war leer)     |
| ''auth.server_key''               | ''auth.server_key''              | Synonym (Tabelle war leer)     |
| ''hr.person''                     | ''core.person''                  | Daten kopiert, Tabelle dropped, Synonym erstellt |

> **Hinweis:** Cross-DB Foreign Keys können in SQL Server nicht erzwungen werden.
> Referenzielle Integrität zwischen ENIVERSCAFM und ENIVERSSIAM wird app-seitig
> (Gateway.Service) sichergestellt.

==== Skripte Phase 1 ====

**Ausführungsreihenfolge:**

  - ''siam_create_tables.sql'' — ENIVERSSIAM: Schema ''auth'' + 13 Tabellen + ''core'' Schema anlegen
  - ''siam_copy_data.sql'' — Daten von ENIVERSCAFM.auth.* nach ENIVERSSIAM.auth.* kopieren
  - ''siam_person.sql'' — ''core.person'' anlegen, Daten kopieren, FK wiederherstellen, Synonym in ENIVERSCAFM
  - ''siam_drop_and_synonyms.sql'' — ENIVERSCAFM: auth-Tabellen droppen, 13 Synonyme erstellen

=== siam_create_tables.sql — ENIVERSSIAM Schema + Tabellen ===

<code sql>
/* ================================================================
   siam_create_tables.sql
   Ziel:    ENIVERSSIAM
   Zweck:   Schema 'auth' anlegen, alle 13 Tabellen erstellen
   Hinweis: Idempotent — IF NOT EXISTS-Guards für alle Objekte
   ================================================================ */
USE ENIVERSSIAM;
GO

-- Schema 'auth' anlegen (falls nicht vorhanden)
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'auth')
    EXEC('CREATE SCHEMA auth');
PRINT 'Schema auth: OK';
GO

-- ------------------------------------------------------------------
-- 1. auth.permission — Berechtigungs-Objekte (Resource / Action / Scope)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'permission'
)
BEGIN
    CREATE TABLE auth.permission (
        id          INT           NOT NULL IDENTITY(1,1),
        resource    VARCHAR(50)   NOT NULL,
        action      VARCHAR(20)   NOT NULL,
        scope       VARCHAR(20)   NOT NULL CONSTRAINT df_permission_scope DEFAULT ('all'),
        description NVARCHAR(256)     NULL,
        CONSTRAINT pk_permission PRIMARY KEY (id),
        CONSTRAINT uq_permission UNIQUE (resource, action, scope)
    );
    PRINT 'auth.permission: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 2. auth.role — Rollen-Definitionen
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'role'
)
BEGIN
    CREATE TABLE auth.role (
        id             INT           NOT NULL IDENTITY(1,1),
        code           VARCHAR(50)   NOT NULL,
        name           NVARCHAR(100) NOT NULL,
        description    NVARCHAR(MAX)     NULL,
        is_system_role BIT           NOT NULL CONSTRAINT df_role_system  DEFAULT (0),
        is_active      BIT           NOT NULL CONSTRAINT df_role_active  DEFAULT (1),
        created_at     DATETIME2     NOT NULL CONSTRAINT df_role_created DEFAULT (SYSUTCDATETIME()),
        updated_at     DATETIME2     NOT NULL CONSTRAINT df_role_updated DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_role      PRIMARY KEY (id),
        CONSTRAINT uq_role_code UNIQUE (code)
    );
    PRINT 'auth.role: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 3. auth.[user] — Anmeldedaten, TOTP, Passwort-Hash
--    Hinweis: person_id referenziert core.person (Intra-DB-FK,
--             wird nach Daten-Migration in siam_person.sql gesetzt)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'user'
)
BEGIN
    CREATE TABLE auth.[user] (
        id                      INT            NOT NULL IDENTITY(1,1),
        person_id               INT            NOT NULL,
        username                VARCHAR(50)    NOT NULL,
        email                   NVARCHAR(256)      NULL,
        -- Passwort-Felder: Hash + Salt, nie Klartext (CWE-256)
        password_hash           VARBINARY(512)     NULL,
        password_salt           VARBINARY(32)      NULL,
        password_changed_at     DATETIME2          NULL,
        password_failed_attempts INT           NOT NULL CONSTRAINT df_user_pw_fails   DEFAULT (0),
        password_locked_until   DATETIME2          NULL,
        -- TOTP-Felder: Secret AES-verschlüsselt gespeichert (CWE-256)
        totp_secret             VARBINARY(256)     NULL,
        totp_enabled            BIT            NOT NULL CONSTRAINT df_user_totp_en    DEFAULT (0),
        totp_verified_at        DATETIME2          NULL,
        totp_failed_attempts    INT            NOT NULL CONSTRAINT df_user_totp_fails DEFAULT (0),
        totp_locked_until       DATETIME2          NULL,
        totp_pending_secret     VARBINARY(256)     NULL,
        totp_pending_expires_at DATETIME2          NULL,
        last_mfa_at             DATETIME2          NULL,
        last_mfa_method         VARCHAR(20)        NULL,
        is_active               BIT            NOT NULL CONSTRAINT df_user_active     DEFAULT (1),
        last_login_at           DATETIME2          NULL,
        created_at              DATETIME2      NOT NULL CONSTRAINT df_user_created    DEFAULT (SYSUTCDATETIME()),
        updated_at              DATETIME2      NOT NULL CONSTRAINT df_user_updated    DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_user          PRIMARY KEY (id),
        CONSTRAINT uq_user_person   UNIQUE (person_id),
        CONSTRAINT uq_user_username UNIQUE (username)
    );
    CREATE INDEX ix_user_person       ON auth.[user] (person_id);
    CREATE INDEX ix_user_username     ON auth.[user] (username);
    CREATE INDEX ix_user_active       ON auth.[user] (is_active);
    CREATE INDEX ix_user_totp_enabled ON auth.[user] (totp_enabled);
    PRINT 'auth.[user]: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 4. auth.device — Geräte-Sessions, PQ-Keys, Refresh-Token-Hash
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'device'
)
BEGIN
    CREATE TABLE auth.device (
        id                       INT            NOT NULL IDENTITY(1,1),
        device_id                VARCHAR(64)    NOT NULL,
        user_id                  INT            NOT NULL,
        name                     NVARCHAR(100)      NULL,
        passkey_credential_id    VARCHAR(256)       NULL,
        passkey_public_key       VARBINARY(MAX)     NULL,
        pq_dsa_public_key        VARBINARY(MAX)     NULL,
        pq_dsa_key_id            VARCHAR(64)        NULL,
        pq_kem_public_key        VARBINARY(MAX)     NULL,
        pq_kem_key_id            VARCHAR(64)        NULL,
        pq_key_created_at        DATETIME2          NULL,
        pq_dsa_public_key_prev   VARBINARY(MAX)     NULL,
        pq_dsa_key_id_prev       VARCHAR(64)        NULL,
        pq_kem_public_key_prev   VARBINARY(MAX)     NULL,
        pq_kem_key_id_prev       VARCHAR(64)        NULL,
        pq_key_prev_expires_at   DATETIME2          NULL,
        refresh_token_hash       VARBINARY(64)      NULL,
        refresh_token_expires_at DATETIME2          NULL,
        refresh_family_id        VARCHAR(64)        NULL,
        refresh_generation       INT            NOT NULL CONSTRAINT df_device_regen  DEFAULT (0),
        last_ip                  VARCHAR(45)        NULL,
        last_geo_country         CHAR(2)            NULL,
        last_geo_region          VARCHAR(50)        NULL,
        last_asn                 INT                NULL,
        last_asn_type            VARCHAR(20)        NULL,
        last_user_agent          NVARCHAR(512)      NULL,
        session_state            VARCHAR(20)    NOT NULL CONSTRAINT df_device_state  DEFAULT ('ACTIVE'),
        session_hold_until       DATETIME2          NULL,
        session_hold_reason      VARCHAR(50)        NULL,
        session_revoked_at       DATETIME2          NULL,
        session_revoked_reason   VARCHAR(50)        NULL,
        is_active                BIT            NOT NULL CONSTRAINT df_device_active DEFAULT (1),
        last_used_at             DATETIME2          NULL,
        created_at               DATETIME2      NOT NULL CONSTRAINT df_device_created DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_device      PRIMARY KEY (id),
        CONSTRAINT uq_device_id   UNIQUE (device_id),
        CONSTRAINT fk_device_user FOREIGN KEY (user_id) REFERENCES auth.[user](id)
    );
    CREATE INDEX ix_device_user          ON auth.device (user_id);
    CREATE INDEX ix_device_user_active   ON auth.device (user_id, is_active);
    CREATE INDEX ix_device_session_state ON auth.device (session_state, user_id);
    CREATE INDEX ix_device_refresh_family ON auth.device (refresh_family_id);
    PRINT 'auth.device: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 5. auth.backup_code — MFA Backup-Codes (gehashed, CWE-256)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'backup_code'
)
BEGIN
    CREATE TABLE auth.backup_code (
        id           INT           NOT NULL IDENTITY(1,1),
        user_id      INT           NOT NULL,
        code_hash    VARBINARY(64) NOT NULL,
        code_prefix  CHAR(4)       NOT NULL,
        is_used      BIT           NOT NULL CONSTRAINT df_bc_used    DEFAULT (0),
        used_at      DATETIME2         NULL,
        used_from_ip VARCHAR(45)       NULL,
        created_at   DATETIME2     NOT NULL CONSTRAINT df_bc_created DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_backup_code PRIMARY KEY (id),
        CONSTRAINT fk_bc_user     FOREIGN KEY (user_id) REFERENCES auth.[user](id)
    );
    CREATE INDEX ix_backup_code_user ON auth.backup_code (user_id, is_used);
    PRINT 'auth.backup_code: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 6. auth.known_location — Bekannte Standorte (Geo-IP, MFA-verifiziert)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'known_location'
)
BEGIN
    CREATE TABLE auth.known_location (
        id               INT        NOT NULL IDENTITY(1,1),
        user_id          INT        NOT NULL,
        geo_country      CHAR(2)    NOT NULL,
        geo_region       VARCHAR(50)    NULL,
        asn              INT            NULL,
        first_seen_at    DATETIME2  NOT NULL CONSTRAINT df_kl_first DEFAULT (SYSUTCDATETIME()),
        last_seen_at     DATETIME2  NOT NULL CONSTRAINT df_kl_last  DEFAULT (SYSUTCDATETIME()),
        expires_at       DATETIME2  NOT NULL,
        verified_via_mfa BIT        NOT NULL CONSTRAINT df_kl_mfa   DEFAULT (1),
        CONSTRAINT pk_known_location PRIMARY KEY (id),
        CONSTRAINT uq_known_location UNIQUE (user_id, geo_country, geo_region, asn),
        CONSTRAINT fk_kl_user        FOREIGN KEY (user_id) REFERENCES auth.[user](id)
    );
    CREATE INDEX ix_known_location_user ON auth.known_location (user_id, expires_at);
    PRINT 'auth.known_location: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 7. auth.mfa_session — TOTP Challenge-Sessions (In-Memory-Äquivalent in DB)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'mfa_session'
)
BEGIN
    CREATE TABLE auth.mfa_session (
        id             INT         NOT NULL IDENTITY(1,1),
        mfa_session_id VARCHAR(64) NOT NULL,
        user_id        INT         NOT NULL,
        device_id      INT             NULL,
        reason         VARCHAR(50) NOT NULL,
        original_action VARCHAR(50)    NULL,
        is_verified    BIT         NOT NULL CONSTRAINT df_mfas_verified DEFAULT (0),
        verified_method VARCHAR(20)    NULL,
        attempts       INT         NOT NULL CONSTRAINT df_mfas_attempts DEFAULT (0),
        created_at     DATETIME2   NOT NULL CONSTRAINT df_mfas_created  DEFAULT (SYSUTCDATETIME()),
        expires_at     DATETIME2   NOT NULL,
        verified_at    DATETIME2       NULL,
        CONSTRAINT pk_mfa_session    PRIMARY KEY (id),
        CONSTRAINT uq_mfa_session_id UNIQUE (mfa_session_id),
        CONSTRAINT fk_mfas_user      FOREIGN KEY (user_id)   REFERENCES auth.[user](id),
        CONSTRAINT fk_mfas_device    FOREIGN KEY (device_id) REFERENCES auth.device(id)
    );
    CREATE INDEX ix_mfa_session_user ON auth.mfa_session (user_id, created_at);
    PRINT 'auth.mfa_session: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 8. auth.nonce_cache — Replay-Protection (jti-Blacklist)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'nonce_cache'
)
BEGIN
    CREATE TABLE auth.nonce_cache (
        nonce       VARCHAR(64) NOT NULL,
        device_id   INT         NOT NULL,
        received_at DATETIME2   NOT NULL CONSTRAINT df_nc_received DEFAULT (SYSUTCDATETIME()),
        expires_at  DATETIME2   NOT NULL,
        CONSTRAINT pk_nonce_cache PRIMARY KEY (nonce)
    );
    CREATE INDEX ix_nonce_expires ON auth.nonce_cache (expires_at);
    PRINT 'auth.nonce_cache: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 9. auth.rate_limit_event — Rate-Limit Protokoll
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'rate_limit_event'
)
BEGIN
    CREATE TABLE auth.rate_limit_event (
        id         INT         NOT NULL IDENTITY(1,1),
        user_id    INT             NULL,
        device_id  INT             NULL,
        ip_address VARCHAR(45) NOT NULL,
        event_type VARCHAR(30) NOT NULL,
        created_at DATETIME2   NOT NULL CONSTRAINT df_rle_created DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_rate_limit_event PRIMARY KEY (id),
        CONSTRAINT fk_rle_user         FOREIGN KEY (user_id)   REFERENCES auth.[user](id),
        CONSTRAINT fk_rle_device       FOREIGN KEY (device_id) REFERENCES auth.device(id)
    );
    CREATE INDEX ix_rate_limit_user_time ON auth.rate_limit_event (user_id, created_at);
    CREATE INDEX ix_rate_limit_ip_time   ON auth.rate_limit_event (ip_address, created_at);
    CREATE INDEX ix_rate_limit_cleanup   ON auth.rate_limit_event (created_at);
    PRINT 'auth.rate_limit_event: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 10. auth.refresh_token_family — Refresh Token Rotation Tracking
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'refresh_token_family'
)
BEGIN
    CREATE TABLE auth.refresh_token_family (
        id                 INT         NOT NULL IDENTITY(1,1),
        family_id          VARCHAR(64) NOT NULL,
        user_id            INT         NOT NULL,
        device_id          INT         NOT NULL,
        current_generation INT         NOT NULL CONSTRAINT df_rtf_gen     DEFAULT (0),
        is_revoked         BIT         NOT NULL CONSTRAINT df_rtf_revoked DEFAULT (0),
        revoked_reason     VARCHAR(50)     NULL,
        created_at         DATETIME2   NOT NULL CONSTRAINT df_rtf_created DEFAULT (SYSUTCDATETIME()),
        last_used_at       DATETIME2       NULL,
        revoked_at         DATETIME2       NULL,
        CONSTRAINT pk_refresh_token_family PRIMARY KEY (id),
        CONSTRAINT uq_rtf_family_id        UNIQUE (family_id),
        CONSTRAINT fk_rtf_user             FOREIGN KEY (user_id)   REFERENCES auth.[user](id),
        CONSTRAINT fk_rtf_device           FOREIGN KEY (device_id) REFERENCES auth.device(id)
    );
    CREATE INDEX ix_refresh_family_user ON auth.refresh_token_family (user_id, is_revoked);
    PRINT 'auth.refresh_token_family: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 11. auth.role_permission — Rollen-Berechtigungs-Zuordnung (Junction)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'role_permission'
)
BEGIN
    CREATE TABLE auth.role_permission (
        role_id       INT NOT NULL,
        permission_id INT NOT NULL,
        CONSTRAINT pk_role_permission PRIMARY KEY (role_id, permission_id),
        CONSTRAINT fk_rp_role         FOREIGN KEY (role_id)       REFERENCES auth.role(id),
        CONSTRAINT fk_rp_permission   FOREIGN KEY (permission_id) REFERENCES auth.permission(id)
    );
    PRINT 'auth.role_permission: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 12. auth.server_key — Server-seitige ML-DSA/ML-KEM Schlüssel
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'server_key'
)
BEGIN
    CREATE TABLE auth.server_key (
        id                    INT            NOT NULL IDENTITY(1,1),
        key_id                VARCHAR(64)    NOT NULL,
        key_type              VARCHAR(20)    NOT NULL,
        algorithm             VARCHAR(30)    NOT NULL,
        public_key            VARBINARY(MAX) NOT NULL,
        -- Private Key verschlüsselt gespeichert (CWE-256)
        private_key_encrypted VARBINARY(MAX) NOT NULL,
        is_active             BIT            NOT NULL CONSTRAINT df_sk_active  DEFAULT (1),
        not_before            DATETIME2      NOT NULL CONSTRAINT df_sk_before  DEFAULT (SYSUTCDATETIME()),
        not_after             DATETIME2      NOT NULL,
        created_at            DATETIME2      NOT NULL CONSTRAINT df_sk_created DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT pk_server_key    PRIMARY KEY (id),
        CONSTRAINT uq_server_key_id UNIQUE (key_id)
    );
    CREATE INDEX ix_server_key_type_active ON auth.server_key (key_type, is_active, not_after);
    PRINT 'auth.server_key: erstellt';
END
GO

-- ------------------------------------------------------------------
-- 13. auth.user_role — User-Rollen-Zuordnung (mit optionalem Site-Scope)
--     Hinweis: site_id referenziert org.site (Cross-DB → kein echtes FK,
--              Integrität app-seitig sichergestellt)
-- ------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = 'auth' AND t.name = 'user_role'
)
BEGIN
    CREATE TABLE auth.user_role (
        id         INT       NOT NULL IDENTITY(1,1),
        user_id    INT       NOT NULL,
        role_id    INT       NOT NULL,
        site_id    INT           NULL,   -- NULL = global, sonst Standort-Scope
        granted_at DATETIME2 NOT NULL CONSTRAINT df_ur_granted DEFAULT (SYSUTCDATETIME()),
        granted_by INT           NULL,
        CONSTRAINT pk_user_role      PRIMARY KEY (id),
        CONSTRAINT uq_user_role_site UNIQUE (user_id, role_id, site_id),
        CONSTRAINT fk_ur_user        FOREIGN KEY (user_id)    REFERENCES auth.[user](id),
        CONSTRAINT fk_ur_role        FOREIGN KEY (role_id)    REFERENCES auth.role(id),
        CONSTRAINT fk_ur_granted_by  FOREIGN KEY (granted_by) REFERENCES auth.[user](id)
    );
    PRINT 'auth.user_role: erstellt';
END
GO

PRINT '=== siam_create_tables: 13 Tabellen in ENIVERSSIAM.auth bereit ===';
</code>

=== siam_copy_data.sql — Daten ENIVERSCAFM → ENIVERSSIAM ===

<code sql>
/* ================================================================
   siam_copy_data.sql
   Quelle: ENIVERSCAFM.auth.*
   Ziel:   ENIVERSSIAM.auth.*
   Zweck:  Bestehende Auth-Daten in neue Identity-Datenbank übertragen
   Reihenfolge: Parents vor Children (FK-Abhängigkeiten beachten)
   ================================================================ */

-- ------------------------------------------------------------------
-- 1. auth.permission — 15 Zeilen, keine Abhängigkeiten
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.permission ON;

INSERT INTO ENIVERSSIAM.auth.permission (id, resource, action, scope, description)
SELECT id, resource, action, scope, description
FROM   ENIVERSCAFM.auth.permission;

SET IDENTITY_INSERT ENIVERSSIAM.auth.permission OFF;
PRINT 'auth.permission: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

-- ------------------------------------------------------------------
-- 2. auth.role — 26 Zeilen, keine Abhängigkeiten
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.role ON;

INSERT INTO ENIVERSSIAM.auth.role
    (id, code, name, description, is_system_role, is_active, created_at, updated_at)
SELECT id, code, name, description, is_system_role, is_active, created_at, updated_at
FROM   ENIVERSCAFM.auth.role;

SET IDENTITY_INSERT ENIVERSSIAM.auth.role OFF;
PRINT 'auth.role: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

-- ------------------------------------------------------------------
-- 3. auth.[user] — 241 Zeilen, hängt von core.person ab (person_id)
--    FK auth.[user] → core.person wird nach siam_person.sql gesetzt
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.[user] ON;

INSERT INTO ENIVERSSIAM.auth.[user] (
    id, person_id, username, email,
    password_hash, password_salt, password_changed_at,
    password_failed_attempts, password_locked_until,
    totp_secret, totp_enabled, totp_verified_at,
    totp_failed_attempts, totp_locked_until,
    totp_pending_secret, totp_pending_expires_at,
    last_mfa_at, last_mfa_method,
    is_active, last_login_at, created_at, updated_at
)
SELECT
    id, person_id, username, email,
    password_hash, password_salt, password_changed_at,
    password_failed_attempts, password_locked_until,
    totp_secret, totp_enabled, totp_verified_at,
    totp_failed_attempts, totp_locked_until,
    totp_pending_secret, totp_pending_expires_at,
    last_mfa_at, last_mfa_method,
    is_active, last_login_at, created_at, updated_at
FROM ENIVERSCAFM.auth.[user];

SET IDENTITY_INSERT ENIVERSSIAM.auth.[user] OFF;
PRINT 'auth.[user]: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

-- ------------------------------------------------------------------
-- 4–8. Tabellen ohne Daten (0 Zeilen) — Synonyme genügen
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.device ON;
INSERT INTO ENIVERSSIAM.auth.device (
    id, device_id, user_id, name,
    passkey_credential_id, passkey_public_key,
    pq_dsa_public_key, pq_dsa_key_id, pq_kem_public_key, pq_kem_key_id, pq_key_created_at,
    pq_dsa_public_key_prev, pq_dsa_key_id_prev, pq_kem_public_key_prev, pq_kem_key_id_prev, pq_key_prev_expires_at,
    refresh_token_hash, refresh_token_expires_at, refresh_family_id, refresh_generation,
    last_ip, last_geo_country, last_geo_region, last_asn, last_asn_type, last_user_agent,
    session_state, session_hold_until, session_hold_reason,
    session_revoked_at, session_revoked_reason,
    is_active, last_used_at, created_at
)
SELECT
    id, device_id, user_id, name,
    passkey_credential_id, passkey_public_key,
    pq_dsa_public_key, pq_dsa_key_id, pq_kem_public_key, pq_kem_key_id, pq_key_created_at,
    pq_dsa_public_key_prev, pq_dsa_key_id_prev, pq_kem_public_key_prev, pq_kem_key_id_prev, pq_key_prev_expires_at,
    refresh_token_hash, refresh_token_expires_at, refresh_family_id, refresh_generation,
    last_ip, last_geo_country, last_geo_region, last_asn, last_asn_type, last_user_agent,
    session_state, session_hold_until, session_hold_reason,
    session_revoked_at, session_revoked_reason,
    is_active, last_used_at, created_at
FROM ENIVERSCAFM.auth.device;
SET IDENTITY_INSERT ENIVERSSIAM.auth.device OFF;
PRINT 'auth.device: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

SET IDENTITY_INSERT ENIVERSSIAM.auth.backup_code ON;
INSERT INTO ENIVERSSIAM.auth.backup_code
    (id, user_id, code_hash, code_prefix, is_used, used_at, used_from_ip, created_at)
SELECT id, user_id, code_hash, code_prefix, is_used, used_at, used_from_ip, created_at
FROM   ENIVERSCAFM.auth.backup_code;
SET IDENTITY_INSERT ENIVERSSIAM.auth.backup_code OFF;
PRINT 'auth.backup_code: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

SET IDENTITY_INSERT ENIVERSSIAM.auth.known_location ON;
INSERT INTO ENIVERSSIAM.auth.known_location
    (id, user_id, geo_country, geo_region, asn, first_seen_at, last_seen_at, expires_at, verified_via_mfa)
SELECT id, user_id, geo_country, geo_region, asn, first_seen_at, last_seen_at, expires_at, verified_via_mfa
FROM   ENIVERSCAFM.auth.known_location;
SET IDENTITY_INSERT ENIVERSSIAM.auth.known_location OFF;
PRINT 'auth.known_location: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

SET IDENTITY_INSERT ENIVERSSIAM.auth.mfa_session ON;
INSERT INTO ENIVERSSIAM.auth.mfa_session
    (id, mfa_session_id, user_id, device_id, reason, original_action, is_verified, verified_method, attempts, created_at, expires_at, verified_at)
SELECT id, mfa_session_id, user_id, device_id, reason, original_action, is_verified, verified_method, attempts, created_at, expires_at, verified_at
FROM   ENIVERSCAFM.auth.mfa_session;
SET IDENTITY_INSERT ENIVERSSIAM.auth.mfa_session OFF;
PRINT 'auth.mfa_session: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

INSERT INTO ENIVERSSIAM.auth.nonce_cache (nonce, device_id, received_at, expires_at)
SELECT nonce, device_id, received_at, expires_at
FROM   ENIVERSCAFM.auth.nonce_cache;
PRINT 'auth.nonce_cache: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

SET IDENTITY_INSERT ENIVERSSIAM.auth.rate_limit_event ON;
INSERT INTO ENIVERSSIAM.auth.rate_limit_event (id, user_id, device_id, ip_address, event_type, created_at)
SELECT id, user_id, device_id, ip_address, event_type, created_at
FROM   ENIVERSCAFM.auth.rate_limit_event;
SET IDENTITY_INSERT ENIVERSSIAM.auth.rate_limit_event OFF;
PRINT 'auth.rate_limit_event: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

SET IDENTITY_INSERT ENIVERSSIAM.auth.refresh_token_family ON;
INSERT INTO ENIVERSSIAM.auth.refresh_token_family
    (id, family_id, user_id, device_id, current_generation, is_revoked, revoked_reason, created_at, last_used_at, revoked_at)
SELECT id, family_id, user_id, device_id, current_generation, is_revoked, revoked_reason, created_at, last_used_at, revoked_at
FROM   ENIVERSCAFM.auth.refresh_token_family;
SET IDENTITY_INSERT ENIVERSSIAM.auth.refresh_token_family OFF;
PRINT 'auth.refresh_token_family: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

-- ------------------------------------------------------------------
-- 9. auth.role_permission — 33 Zeilen, hängt von role + permission ab
-- ------------------------------------------------------------------
INSERT INTO ENIVERSSIAM.auth.role_permission (role_id, permission_id)
SELECT role_id, permission_id
FROM   ENIVERSCAFM.auth.role_permission;
PRINT 'auth.role_permission: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

SET IDENTITY_INSERT ENIVERSSIAM.auth.server_key ON;
INSERT INTO ENIVERSSIAM.auth.server_key
    (id, key_id, key_type, algorithm, public_key, private_key_encrypted, is_active, not_before, not_after, created_at)
SELECT id, key_id, key_type, algorithm, public_key, private_key_encrypted, is_active, not_before, not_after, created_at
FROM   ENIVERSCAFM.auth.server_key;
SET IDENTITY_INSERT ENIVERSSIAM.auth.server_key OFF;
PRINT 'auth.server_key: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

-- ------------------------------------------------------------------
-- 10. auth.user_role — 9.445 Zeilen, zuletzt (FK auf user + role)
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.auth.user_role ON;

INSERT INTO ENIVERSSIAM.auth.user_role (id, user_id, role_id, site_id, granted_at, granted_by)
SELECT id, user_id, role_id, site_id, granted_at, granted_by
FROM   ENIVERSCAFM.auth.user_role;

SET IDENTITY_INSERT ENIVERSSIAM.auth.user_role OFF;
PRINT 'auth.user_role: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';

PRINT '=== siam_copy_data: Datenmigration ENIVERSCAFM → ENIVERSSIAM abgeschlossen ===';
</code>

=== siam_person.sql — core.person anlegen, befüllen, FK + Synonym ===

<code sql>
/* ================================================================
   siam_person.sql
   Zweck:  core.person in ENIVERSSIAM erstellen, Daten aus
           ENIVERSCAFM.hr.person übertragen, FK von auth.[user]
           wiederherstellen, Synonym in ENIVERSCAFM anlegen
   ================================================================ */

-- ------------------------------------------------------------------
-- Schritt 1: Schema 'core' und Tabelle 'core.person' in ENIVERSSIAM
-- ------------------------------------------------------------------
USE ENIVERSSIAM;
GO

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'core')
    EXEC('CREATE SCHEMA core');
GO

CREATE TABLE core.person (
    id             INT           NOT NULL IDENTITY(1,1),
    first_name     NVARCHAR(50)  NOT NULL,
    last_name      NVARCHAR(50)  NOT NULL,
    position       NVARCHAR(100)     NULL,
    notes          NVARCHAR(MAX)     NULL,
    is_active      BIT           NOT NULL CONSTRAINT df_person_active  DEFAULT (1),
    is_system_user BIT           NOT NULL CONSTRAINT df_person_sysuser DEFAULT (0),
    created_at     DATETIME2     NOT NULL CONSTRAINT df_person_created DEFAULT (SYSUTCDATETIME()),
    updated_at     DATETIME2     NOT NULL CONSTRAINT df_person_updated DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT pk_person PRIMARY KEY (id)
);
PRINT 'core.person: erstellt';
GO

-- ------------------------------------------------------------------
-- Schritt 2: Daten aus ENIVERSCAFM.hr.person übertragen (241 Zeilen)
--            IDENTITY_INSERT ON, damit Original-IDs erhalten bleiben
-- ------------------------------------------------------------------
SET IDENTITY_INSERT ENIVERSSIAM.core.person ON;

INSERT INTO ENIVERSSIAM.core.person
    (id, first_name, last_name, position, notes, is_active, is_system_user, created_at, updated_at)
SELECT id, first_name, last_name, position, notes, is_active, is_system_user, created_at, updated_at
FROM   ENIVERSCAFM.hr.person;

SET IDENTITY_INSERT ENIVERSSIAM.core.person OFF;
PRINT 'core.person: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen kopiert';
GO

-- ------------------------------------------------------------------
-- Schritt 3: FK auth.[user].person_id → core.person(id) wiederherstellen
--            (jetzt echter Intra-DB-FK innerhalb ENIVERSSIAM)
-- ------------------------------------------------------------------
ALTER TABLE auth.[user]
    ADD CONSTRAINT fk_user_person
    FOREIGN KEY (person_id) REFERENCES core.person(id);
PRINT 'FK auth.[user].person_id → core.person(id): wiederhergestellt';
GO

-- ------------------------------------------------------------------
-- Schritt 4: hr.person in ENIVERSCAFM droppen und Synonym anlegen
-- ------------------------------------------------------------------
USE ENIVERSCAFM;
GO

DROP TABLE hr.person;
PRINT 'hr.person: aus ENIVERSCAFM entfernt';
GO

CREATE SYNONYM hr.person FOR ENIVERSSIAM.core.person;
PRINT 'Synonym hr.person → ENIVERSSIAM.core.person: erstellt';
GO

PRINT '=== siam_person: core.person Migration abgeschlossen ===';
</code>

=== siam_drop_and_synonyms.sql — FKs droppen, Tabellen droppen, Synonyme erstellen ===

<code sql>
/* ================================================================
   siam_drop_and_synonyms.sql
   Ziel:   ENIVERSCAFM
   Zweck:  Cross-Schema FKs und auth-Tabellen entfernen,
           13 Synonyme auf ENIVERSSIAM erstellen
   Hinweis: Reihenfolge zwingend — Children vor Parents droppen
   ================================================================ */
USE ENIVERSCAFM;
GO

-- ------------------------------------------------------------------
-- Schritt 1: Cross-Schema FKs droppen
--            (audit→auth, settings→auth, auth→hr, auth→org)
--            Diese können nach DB-Move nicht als echte FKs wieder-
--            hergestellt werden — Integrität wird app-seitig gewährleistet
-- ------------------------------------------------------------------
IF OBJECT_ID('audit.event_log') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_el_user'
                  AND parent_object_id = OBJECT_ID('audit.event_log'))
    ALTER TABLE audit.event_log DROP CONSTRAINT fk_el_user;

IF OBJECT_ID('audit.event_log') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_el_device'
                  AND parent_object_id = OBJECT_ID('audit.event_log'))
    ALTER TABLE audit.event_log DROP CONSTRAINT fk_el_device;

IF OBJECT_ID('audit.pq_signature') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_pqs_user'
                  AND parent_object_id = OBJECT_ID('audit.pq_signature'))
    ALTER TABLE audit.pq_signature DROP CONSTRAINT fk_pqs_user;

IF OBJECT_ID('audit.pq_signature') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_pqs_device'
                  AND parent_object_id = OBJECT_ID('audit.pq_signature'))
    ALTER TABLE audit.pq_signature DROP CONSTRAINT fk_pqs_device;

IF OBJECT_ID('settings.user_preference') IS NOT NULL
    AND EXISTS (SELECT 1 FROM sys.foreign_keys
                WHERE name = 'fk_user_preference_user'
                  AND parent_object_id = OBJECT_ID('settings.user_preference'))
    ALTER TABLE settings.user_preference DROP CONSTRAINT fk_user_preference_user;

IF EXISTS (SELECT 1 FROM sys.foreign_keys
           WHERE name = 'fk_user_person'
             AND parent_object_id = OBJECT_ID('auth.user'))
    ALTER TABLE auth.[user] DROP CONSTRAINT fk_user_person;

IF EXISTS (SELECT 1 FROM sys.foreign_keys
           WHERE name = 'fk_ur_site'
             AND parent_object_id = OBJECT_ID('auth.user_role'))
    ALTER TABLE auth.user_role DROP CONSTRAINT fk_ur_site;

PRINT 'Cross-Schema FKs: entfernt';
GO

-- ------------------------------------------------------------------
-- Schritt 2: Interne auth-FKs droppen (Children zuerst)
-- ------------------------------------------------------------------

-- auth.user_role hängt von auth.[user] + auth.role ab
ALTER TABLE auth.user_role DROP CONSTRAINT fk_ur_granted_by;
ALTER TABLE auth.user_role DROP CONSTRAINT fk_ur_user;
ALTER TABLE auth.user_role DROP CONSTRAINT fk_ur_role;

-- auth.role_permission
ALTER TABLE auth.role_permission DROP CONSTRAINT fk_rp_permission;
ALTER TABLE auth.role_permission DROP CONSTRAINT fk_rp_role;

-- auth.refresh_token_family
ALTER TABLE auth.refresh_token_family DROP CONSTRAINT fk_rtf_device;
ALTER TABLE auth.refresh_token_family DROP CONSTRAINT fk_rtf_user;

-- auth.rate_limit_event
ALTER TABLE auth.rate_limit_event DROP CONSTRAINT fk_rle_device;
ALTER TABLE auth.rate_limit_event DROP CONSTRAINT fk_rle_user;

-- auth.mfa_session
ALTER TABLE auth.mfa_session DROP CONSTRAINT fk_mfas_device;
ALTER TABLE auth.mfa_session DROP CONSTRAINT fk_mfas_user;

-- auth.known_location
ALTER TABLE auth.known_location DROP CONSTRAINT fk_kl_user;

-- auth.backup_code
ALTER TABLE auth.backup_code DROP CONSTRAINT fk_bc_user;

-- auth.device (letzte Child-Tabelle von auth.[user])
ALTER TABLE auth.device DROP CONSTRAINT fk_device_user;

PRINT 'Interne auth-FKs: entfernt';
GO

-- ------------------------------------------------------------------
-- Schritt 3: auth-Tabellen droppen (Children vor Parents)
-- ------------------------------------------------------------------
DROP TABLE auth.nonce_cache;
DROP TABLE auth.mfa_session;
DROP TABLE auth.rate_limit_event;
DROP TABLE auth.refresh_token_family;
DROP TABLE auth.backup_code;
DROP TABLE auth.known_location;
DROP TABLE auth.role_permission;
DROP TABLE auth.user_role;
DROP TABLE auth.device;
DROP TABLE auth.[user];
DROP TABLE auth.role;
DROP TABLE auth.permission;
DROP TABLE auth.server_key;
PRINT 'auth-Tabellen: aus ENIVERSCAFM entfernt';
GO

-- ------------------------------------------------------------------
-- Schritt 4: 13 Synonyme auf ENIVERSSIAM.auth.* erstellen
--            Bestehender Code kann weiterhin ENIVERSCAFM.auth.*
--            ansprechen — transparent umgeleitet
-- ------------------------------------------------------------------
CREATE SYNONYM auth.backup_code          FOR ENIVERSSIAM.auth.backup_code;
CREATE SYNONYM auth.device               FOR ENIVERSSIAM.auth.device;
CREATE SYNONYM auth.known_location       FOR ENIVERSSIAM.auth.known_location;
CREATE SYNONYM auth.mfa_session          FOR ENIVERSSIAM.auth.mfa_session;
CREATE SYNONYM auth.nonce_cache          FOR ENIVERSSIAM.auth.nonce_cache;
CREATE SYNONYM auth.permission           FOR ENIVERSSIAM.auth.permission;
CREATE SYNONYM auth.rate_limit_event     FOR ENIVERSSIAM.auth.rate_limit_event;
CREATE SYNONYM auth.refresh_token_family FOR ENIVERSSIAM.auth.refresh_token_family;
CREATE SYNONYM auth.role                 FOR ENIVERSSIAM.auth.role;
CREATE SYNONYM auth.role_permission      FOR ENIVERSSIAM.auth.role_permission;
CREATE SYNONYM auth.server_key           FOR ENIVERSSIAM.auth.server_key;
CREATE SYNONYM auth.[user]               FOR ENIVERSSIAM.auth.[user];
CREATE SYNONYM auth.user_role            FOR ENIVERSSIAM.auth.user_role;
PRINT '13 Synonyme in ENIVERSCAFM.auth: erstellt';
GO

PRINT '=== siam_drop_and_synonyms: Phase 1 vollständig abgeschlossen ===';
</code>

===== Phase 2 — LD-System: ENIVERS → ENIVERSSIAM =====

==== Quellsysteme ====

=== Quelle 1: ENIVERS-Datenbank (SQL Server) ===

**Verbindung:** ''localhost'', Datenbank ''ENIVERS'', Schema ''dbo''

^ ENIVERS-Tabelle           ^ Zeilen ^ Ziel ENIVERSSIAM              ^ Mapping-Hinweise                              ^
| ''tblPerson''             | 80     | ''core.person'' + ''auth.user'' | Feldmapping siehe unten                     |
| ''tblRolle''              | 14     | ''auth.role''                 | ''strRolAbk''→''code'', ''strRolName''→''name'' |
| ''tblPersonRolle''        | 70     | ''auth.user_role''            | Ohne Site-Scope (''site_id = NULL'')         |
| ''tblPersonRolleBetrieb'' | 35     | ''auth.user_role''            | Mit Site-Scope (''fiPrbBetrieb''→''site_id'') |
| ''tblRecht''              | 1      | ''auth.permission''           | Nur 1 Eintrag — manuell prüfen               |
| ''tblRolleRechtObjekt''   | 1.130  | ''auth.role_permission''      | Strukturell verschieden — separate Phase      |

=== Quelle 2: LdUsr.accdb (MS Access, Datei-Datenbank) ===

**Pfad:** ''D:\Workspace\AMED\LdUsr_ACCDB\LdUsr.accdb''
**Export-Stand:** 2026-01-31 (288 Formulare, 95 Berichte, 76 Module, 19 Makros)

Die Access-Datei enthält primär die **Benutzeroberfläche** (Formulare, Berichte, VBA)
und nutzt **verknüpfte Tabellen** (Linked Tables) aus der ENIVERS SQL-Server-Datenbank.
Es gibt keine eigenständigen Access-nativen Tabellen — alle Daten kommen aus Quelle 1.

Relevant als Referenz für Geschäftsregeln:
  * Formular-Logik: ''fpopLogin'', ''fpopMandant''
  * Rollen-/Rechte-Zuordnung: ''sfrRolleRechtObjekt'', ''sfrPersonRolle''
  * Scheduling-Logik: ''ufrmAgent_Schedule'', ''ufrmAgent_Log''

==== Feldmapping: tblPerson → core.person + auth.user ====

^ tblPerson-Feld       ^ Ziel-Tabelle    ^ Ziel-Feld      ^ Bemerkung                                        ^
| ''idPerson''         | ''core.person'' | ''id''         | IDENTITY_INSERT ON — IDs 1–179, kein Konflikt mit 591–2896 |
| ''strPeVorname''     | ''core.person'' | ''first_name'' | ISNULL → leer (''∅'')                            |
| ''strPeNachname''    | ''core.person'' | ''last_name''  | ISNULL → leer                                    |
| ''strPePosition''    | ''core.person'' | ''position''   |                                                  |
| ''memPeNotiz''       | ''core.person'' | ''notes''      |                                                  |
| ''bitPeAktiv''       | ''core.person'' | ''is_active''  | ISNULL → 1                                       |
| ''bitPeSysUser''     | ''core.person'' | ''is_system_user'' | ISNULL → 0                                   |
| ''dtCreated''        | ''core.person'' | ''created_at'' |                                                  |
| ''dtUpdated''        | ''core.person'' | ''updated_at'' | ISNULL → dtCreated                               |
| ''idPerson''         | ''auth.[user]'' | ''person_id''  | FK auf core.person                               |
| ''strPeWinUser''     | ''auth.[user]'' | ''username''   | Windows-Username → für Kerberos SSO              |
| ''strPeAccUser''     | —               | —              | **Nicht übernehmen** — Legacy Access-Credential  |
| ''strPeAccPasswort'' | —               | —              | **Nicht übernehmen** — Klartext-Passwort (CWE-256) |
| ''dtmPeGueltigBis''  | —               | —              | Prüfen ob für ''license.expires'' relevant       |
| ''fiPeFirma''        | —               | —              | Firma-Referenz — kein Ziel definiert             |
| ''fiPeBetrieb''      | —               | —              | Betrieb-Referenz → org.site prüfen               |

> **Sicherheitshinweis:** ''strPeAccPasswort'' enthält Klartext-Passwörter aus dem alten
> Access-System. Diese Spalte darf **niemals** übernommen werden (CWE-256).
> Benutzer erhalten beim ersten Login einen Passwort-Reset-Link.

==== Feldmapping: tblRolle → auth.role ====

^ tblRolle-Feld    ^ Ziel-Feld       ^ Bemerkung                                        ^
| ''idRolle''      | —               | Kein IDENTITY_INSERT — ID-Konflikt mit id=4 (viewer) |
| ''strRolAbk''    | ''code''        | Code-basiertes Matching statt ID-basiert         |
| ''strRolName''   | ''name''        |                                                  |
| ''memRolNotiz''  | ''description'' |                                                  |
| ''bitRolSysRole'' | ''is_system_role'' | ISNULL → 0                                  |
| ''bitRolAktiv''  | ''is_active''   | ISNULL → 1                                       |
| ''dtCreated''    | ''created_at''  |                                                  |
| ''dtEdited''     | ''updated_at''  | ISNULL → dtCreated                               |

==== Feldmapping: tblPersonRolle + tblPersonRolleBetrieb → auth.user_role ====

^ Quell-Tabelle              ^ Quell-Feld    ^ Ziel-Feld     ^ Bemerkung                             ^
| ''tblPersonRolle''         | ''fiPrPerson'' | ''user_id''  | Via #person_map (enivers_id → siam_user_id) |
| ''tblPersonRolle''         | ''fiPrRolle''  | ''role_id''  | Via #role_map (enivers_id → siam_id)        |
| ''tblPersonRolle''         | ''dtCreated''  | ''granted_at'' |                                       |
| ''tblPersonRolle''         | —             | ''site_id''    | NULL (kein Site-Scope)                |
| ''tblPersonRolleBetrieb''  | ''fiPrbPerson'' | ''user_id'' | Via #person_map                       |
| ''tblPersonRolleBetrieb''  | ''fiPrbRolle''  | ''role_id'' | Via #role_map                         |
| ''tblPersonRolleBetrieb''  | ''fiPrbBetrieb'' | ''site_id'' | Direkte Übernahme des Betrieb-IDs    |
| ''tblPersonRolleBetrieb''  | ''dtCreated''  | ''granted_at'' |                                       |

==== Besonderheiten ====

=== Rollen-ID-Konflikt ===

''ENIVERS.dbo.tblRolle'' enthält ''idRolle = 4'' — in ENIVERSSIAM ist ''auth.role.id = 4''
bereits durch eine System-Rolle belegt. Kein IDENTITY_INSERT für ''auth.role'' möglich.

**Lösung:** Code-basiertes Mapping über temporäre ''#role_map''-Tabelle:
  * Matching via ''LOWER(ar.code) = LOWER(r.strRolAbk)''
  * Für bestehende Rollen (z. B. ''entwickler'', ''admins'') → existing id übernehmen
  * Für neue LD-Rollen → auto-generierte IDs aus IDENTITY

=== Username-Duplikate ===

3 Duplikat-Paare in ''tblPerson'' (gleicher ''strPeWinUser'', verschiedene ''idPerson''):

^ strPeWinUser   ^ idPerson (Duplikate) ^ Lösung              ^
| ''uid1041442'' | 150, 151             | MIN(idPerson) → 150 |
| ''EID0109386'' | 171, 172             | MIN(idPerson) → 171 |
| ''pede0153''   | 129, 168             | MIN(idPerson) → 129 |

**Lösung:** ''WITH dedup AS (SELECT MIN(idPerson) ... GROUP BY strPeWinUser)''
Ergebnis: 73 neue ''auth.user''-Einträge statt 76 (3 Duplikate zusammengeführt).

==== Skripte Phase 2 ====

=== ld_migration.sql — Hauptmigration ===

<code sql>
/* ================================================================
   ld_migration.sql
   Quelle:  ENIVERS.dbo (tblPerson, tblRolle, tblPersonRolle,
                          tblPersonRolleBetrieb)
   Ziel:    ENIVERSSIAM (core.person, auth.[user], auth.role,
                          auth.user_role)
   Strategie:
     core.person  — IDENTITY_INSERT ON (IDs 1–179 kollidieren nicht
                    mit bestehenden 591–2896)
     auth.[user]  — auto-ID (kein IDENTITY_INSERT)
     auth.role    — auto-ID; Code-basiertes Mapping alter → neuer IDs
     auth.user_role — nur fehlende Einträge einfügen (NOT EXISTS)
     Passwortfelder — nicht übernommen (CWE-256, Klartext)
   ================================================================ */

USE ENIVERSSIAM;
GO

-- ------------------------------------------------------------------
-- Schritt 1: Neue Personen einfügen (76 Zeilen, IDENTITY_INSERT)
--            Nur Personen, die noch keinen übereinstimmenden
--            auth.[user]-Eintrag haben (Collation-sicherer Vergleich)
-- ------------------------------------------------------------------
SET IDENTITY_INSERT core.person ON;

INSERT INTO core.person
    (id, first_name, last_name, position, notes,
     is_active, is_system_user, created_at, updated_at)
SELECT
    p.idPerson,
    ISNULL(p.strPeVorname,  ''),
    ISNULL(p.strPeNachname, ''),
    p.strPePosition,
    p.memPeNotiz,
    ISNULL(p.bitPeAktiv,   1),
    ISNULL(p.bitPeSysUser, 0),
    p.dtCreated,
    ISNULL(p.dtUpdated, p.dtCreated)
FROM ENIVERS.dbo.tblPerson p
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.[user] u
    WHERE  LOWER(u.username   COLLATE Latin1_General_CI_AS)
         = LOWER(p.strPeWinUser COLLATE Latin1_General_CI_AS)
);

SET IDENTITY_INSERT core.person OFF;
PRINT 'core.person: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' neue Zeilen eingefügt';
GO

-- ------------------------------------------------------------------
-- Schritt 2: Neue auth.[user] einfügen (auto-ID)
--            person_id = idPerson aus tblPerson (soeben eingefügt)
--            username  = strPeWinUser (Windows-Login für Kerberos)
--            Kein Passwort — Legacy-Passwörter nicht übernehmen (CWE-256)
-- ------------------------------------------------------------------
INSERT INTO auth.[user]
    (person_id, username, is_active, created_at, updated_at)
SELECT
    p.idPerson,
    p.strPeWinUser,
    ISNULL(p.bitPeAktiv, 1),
    p.dtCreated,
    ISNULL(p.dtUpdated, p.dtCreated)
FROM ENIVERS.dbo.tblPerson p
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.[user] u
    WHERE  LOWER(u.username   COLLATE Latin1_General_CI_AS)
         = LOWER(p.strPeWinUser COLLATE Latin1_General_CI_AS)
);
PRINT 'auth.[user]: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' neue Zeilen eingefügt';
GO

-- ------------------------------------------------------------------
-- Schritt 3: Neue Rollen einfügen (auto-ID)
--            Kein IDENTITY_INSERT — idRolle=4 kollidiert mit
--            bestehendem System-Role (viewer). Code-basiertes Matching.
--            12 neue LD-spezifische Rollen werden eingefügt.
-- ------------------------------------------------------------------
INSERT INTO auth.role
    (code, name, description, is_system_role, is_active, created_at, updated_at)
SELECT
    r.strRolAbk,
    r.strRolName,
    r.memRolNotiz,
    ISNULL(r.bitRolSysRole, 0),
    ISNULL(r.bitRolAktiv,   1),
    r.dtCreated,
    ISNULL(r.dtEdited, r.dtCreated)
FROM ENIVERS.dbo.tblRolle r
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.role ar
    WHERE  LOWER(ar.code COLLATE Latin1_General_CI_AS)
         = LOWER(r.strRolAbk COLLATE Latin1_General_CI_AS)
);
PRINT 'auth.role: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' neue Zeilen eingefügt';
GO

-- ------------------------------------------------------------------
-- Schritt 4: Rollen-ID-Mapping aufbauen (#role_map, temporär)
--            Verbindet ENIVERS idRolle mit dem tatsächlichen
--            ENIVERSSIAM auth.role.id (via Code-Matching)
-- ------------------------------------------------------------------
IF OBJECT_ID('tempdb..#role_map') IS NOT NULL
    DROP TABLE #role_map;

CREATE TABLE #role_map (
    enivers_id INT NOT NULL,
    siam_id    INT NOT NULL
);

INSERT INTO #role_map (enivers_id, siam_id)
SELECT
    r.idRolle,
    ar.id
FROM ENIVERS.dbo.tblRolle r
JOIN auth.role ar
    ON LOWER(ar.code COLLATE Latin1_General_CI_AS)
     = LOWER(r.strRolAbk COLLATE Latin1_General_CI_AS);

PRINT '#role_map: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Mappings erstellt';

-- ------------------------------------------------------------------
-- Schritt 5: Personen-ID-Mapping aufbauen (#person_map, temporär)
--            Verbindet ENIVERS idPerson mit ENIVERSSIAM auth.[user].id
-- ------------------------------------------------------------------
IF OBJECT_ID('tempdb..#person_map') IS NOT NULL
    DROP TABLE #person_map;

CREATE TABLE #person_map (
    enivers_id   INT NOT NULL,
    siam_user_id INT NOT NULL
);

INSERT INTO #person_map (enivers_id, siam_user_id)
SELECT
    p.idPerson,
    u.id
FROM ENIVERS.dbo.tblPerson p
JOIN auth.[user] u
    ON LOWER(u.username   COLLATE Latin1_General_CI_AS)
     = LOWER(p.strPeWinUser COLLATE Latin1_General_CI_AS);

PRINT '#person_map: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Mappings erstellt';

-- ------------------------------------------------------------------
-- Schritt 6a: auth.user_role aus tblPersonRolle (kein Site-Scope)
--             Nur einfügen wenn Kombination noch nicht vorhanden
-- ------------------------------------------------------------------
INSERT INTO auth.user_role (user_id, role_id, granted_at, granted_by)
SELECT
    pm.siam_user_id,
    rm.siam_id,
    pr.dtCreated,
    NULL
FROM ENIVERS.dbo.tblPersonRolle pr
JOIN #person_map pm ON pm.enivers_id = pr.fiPrPerson
JOIN #role_map   rm ON rm.enivers_id = pr.fiPrRolle
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.user_role ur
    WHERE  ur.user_id = pm.siam_user_id
      AND  ur.role_id = rm.siam_id
      AND  ur.site_id IS NULL
);
PRINT 'auth.user_role (ohne Site): ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen eingefügt';

-- ------------------------------------------------------------------
-- Schritt 6b: auth.user_role aus tblPersonRolleBetrieb (mit Site-Scope)
--             fiPrbBetrieb → site_id (FK auf org.site, Cross-DB,
--             app-seitig sichergestellt)
-- ------------------------------------------------------------------
INSERT INTO auth.user_role (user_id, role_id, site_id, granted_at, granted_by)
SELECT
    pm.siam_user_id,
    rm.siam_id,
    prb.fiPrbBetrieb,   -- site_id: direkte Übernahme des Betrieb-IDs
    prb.dtCreated,
    NULL
FROM ENIVERS.dbo.tblPersonRolleBetrieb prb
JOIN #person_map pm ON pm.enivers_id = prb.fiPrbPerson
JOIN #role_map   rm ON rm.enivers_id = prb.fiPrbRolle
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.user_role ur
    WHERE  ur.user_id = pm.siam_user_id
      AND  ur.role_id = rm.siam_id
      AND  ur.site_id = prb.fiPrbBetrieb
);
PRINT 'auth.user_role (mit Site): ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen eingefügt';
GO

-- ------------------------------------------------------------------
-- Schritt 7: Ergebnis-Übersicht
-- ------------------------------------------------------------------
SELECT 'core.person'    AS tabelle, COUNT(*) AS gesamt FROM core.person
UNION ALL
SELECT 'auth.[user]',                COUNT(*) FROM auth.[user]
UNION ALL
SELECT 'auth.role',                  COUNT(*) FROM auth.role
UNION ALL
SELECT 'auth.user_role',             COUNT(*) FROM auth.user_role;

PRINT '=== ld_migration: Phase 2 abgeschlossen ===';
GO
</code>

=== ld_migration_users_fix.sql — Username-Duplikat-Fix ===

<code sql>
/* ================================================================
   ld_migration_users_fix.sql
   Zweck:   auth.[user] für neue ENIVERS-Personen einfügen
            (Schritt 2 aus ld_migration.sql schlug fehl wegen
            3 Username-Duplikaten in tblPerson)
   Duplikate:
     uid1041442 (idPerson 150 + 151)
     EID0109386 (idPerson 171 + 172)
     pede0153   (idPerson 129 + 168)
   Lösung: Pro Username nur den ältesten Eintrag (MIN idPerson)
   ================================================================ */

USE ENIVERSSIAM;
GO

-- Deduplizierung: Pro Username nur MIN(idPerson) verwenden
WITH dedup AS (
    SELECT MIN(idPerson) AS idPerson
    FROM   ENIVERS.dbo.tblPerson
    GROUP BY strPeWinUser
)
INSERT INTO auth.[user]
    (person_id, username, is_active, created_at, updated_at)
SELECT
    p.idPerson,
    p.strPeWinUser,
    ISNULL(p.bitPeAktiv, 1),
    p.dtCreated,
    ISNULL(p.dtUpdated, p.dtCreated)
FROM ENIVERS.dbo.tblPerson p
JOIN dedup d ON d.idPerson = p.idPerson
WHERE NOT EXISTS (
    SELECT 1
    FROM   auth.[user] u
    WHERE  LOWER(u.username   COLLATE Latin1_General_CI_AS)
         = LOWER(p.strPeWinUser COLLATE Latin1_General_CI_AS)
);
PRINT 'auth.[user]: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' Zeilen eingefügt';

-- Ergebnis-Übersicht
SELECT 'core.person'    AS tabelle, COUNT(*) AS gesamt FROM core.person
UNION ALL
SELECT 'auth.[user]',                COUNT(*) FROM auth.[user]
UNION ALL
SELECT 'auth.role',                  COUNT(*) FROM auth.role
UNION ALL
SELECT 'auth.user_role',             COUNT(*) FROM auth.user_role;
GO
</code>

==== Ergebnis Phase 2 ====

^ Tabelle           ^ Vor Phase 2 ^ Nach Phase 2 ^ Neu eingefügt ^
| ''core.person''   | 241         | 317           | 76            |
| ''auth.[user]''   | 241         | 314           | 73            |
| ''auth.role''     | 26          | 38            | 12            |
| ''auth.user_role'' | 9.445      | 9.476         | 31            |

> **Hinweis:** 73 statt 76 neue User — 3 Username-Duplikate zusammengeführt (MIN idPerson).
> Personen ohne gültigen ''strPeWinUser'' (NULL) erhalten keinen ''auth.[user]''-Eintrag.

===== Quellsysteme und Migrationspfade =====

==== Stack-Übersicht (AMED / WIS / TecDB) ====

^ Stack                ^ Pfad                                  ^ Technologie               ^ Status            ^
| AMED .NET Framework  | ''D:\Workspace\AMED\AmedNET\**''      | C# + ADO.NET TypedDataSet | Produktiv         |
| AMED .NET 8 (SETY)   | ''D:\Workspace\AMED\net8\src\**''     | C# .NET 8 + WebAPI        | In Entwicklung    |
| Delphi (alle Module) | ''D:\Workspace\AMED\Delphi\src\**''   | Delphi + TADOConnection   | Legacy, produktiv |

==== Delphi-Anwendungen (alle Module) ====

^ Modul         ^ Pfad (relativ zu src\)  ^ Zweck                                         ^ Migrationsziel             ^
| ''WIS''       | ''WIS\''                | Geräte, Prüfplanung, Prüfausführung (PROOF)   | ENIVERSCAFM + ENIVERSPIMS  |
| ''SIAM''      | ''SIAM\''               | Legacy-Benutzerverwaltung                     | **abgelöst** (Phase 2 ✓)   |
| ''SCCM_PWD''  | ''SCCM_PWD\''           | Passwort-Verwaltung (legacy)                  | **abgelöst** (Phase 2 ✓)   |
| ''SCCM_WEB''  | ''SCCM_WEB\''           | Web-Frontend für SIAM                         | **abgelöst** → Gateway.Service |
| ''ATEA''      | ''ATEA\''               | Anlage-/Apparate-Verwaltung                   | ENIVERSCAFM                |
| ''ETCM''      | ''ETCM\''               | Konfigurationsverwaltung                      | ENIVERSCAFM                |
| ''MOT''       | ''MOT\''                | Motor-Stammdaten, Lager, Verteilung           | ENIVERSCAFM                |
| ''_DEVICES_'' | ''_DEVICES_\''          | Geräte-Stammdaten (Package-Bibliothek)        | ENIVERSCAFM                |
| ''_MOTORS_''  | ''_MOTORS_\''           | Motor-Package-Bibliothek                      | ENIVERSCAFM                |
| ''REP''       | ''REP\''                | Schadensmeldungen, Berichte, Gantt-Ansicht    | ENIVERSPIMS                |
| ''SB''        | ''SB\''                 | Hauptmenü / Switchboard (Launcher)            | **abgelöst** → WvdS.Shell  |
| ''ISAPI''     | ''ISAPI\''              | Web-Endpunkte für AMED-Daten (legacy)         | **abgelöst** → Gateway.Service |
| ''PFED''      | ''PFED\''               | Feldgeräte-Verteilung (Personal)              | ENIVERSCAFM (prüfen)       |
| ''UTI''       | ''UTI\''                | DataSetConverter, gemeinsame Hilfsfunktionen  | kein Migrationsziel        |
| ''ZOHO''      | ''ZOHO\''               | Zoho-CRM-Inventar-Integration                 | extern / out-of-scope      |

===== Phase 3 — AMED Bestandsdaten (geplant) =====

Migrationsziel: **ENIVERSCAFM** (thematische Schemas ''asset'', ''org'', ''facility'')

Geplante Quellen:
  * AMED-Datenbank (SQL Server): Branches, BETRIEB, tblGebaeude, APPMASTER, MOTORMASTER, GreatPlants
  * AMED .NET Framework DAL (Referenz für Schema-Struktur)
  * Delphi ATEA / ETCM / MOT (ergänzende Bestandsdaten)

Schema-Reorganisation: von ''dbo'' in thematische Schemas (''asset'', ''org'', ''facility'', ''elec'')

===== Phase 4 — ENIVERSPIMS aufbauen (geplant) =====

Neues Schema, Quellen:
  * WIS-Delphi: DAL-Units ''PJ.AMED.DAL.Proof.Plan.Data'', ''PJ.AMED.DAL.Proof.Exec.Data''
  * TecDB (SQL Server): Rohrleitungs- und Messtechnik-Daten (Struktur noch zu analysieren)
  * Delphi REP: Schadensmeldungen, Berichte, Messergebnisse

===== Phase 5 — ENIVERSASYS aufbauen (geplant) =====

Feature-Flags-System aus ENIVERSCAFM auslagern:
  * Aktuell: Feature-Flags im ''auth.*''-Namespace in ENIVERSCAFM
  * Ziel: Dediziertes ENIVERSASYS mit eigenem Feature-Flags-Schema
  * Shell und Gateway sprechen bereits gegen den ''/api/v1/features''-Endpoint —
    das Backend-Target (ENIVERSCAFM vs. ENIVERSASYS) ist intern transparent