-- EK-DOS-WEB: Benutzer, Rollen und Protokoll.
-- Sitzungen und n8n-Antworten liegen in Redis, nicht hier.

create extension if not exists pgcrypto;

create table if not exists users (
    id            uuid primary key     default gen_random_uuid(),
    username      text        not null,
    display_name  text        not null,
    role          text        not null default 'buero',
    password_hash text        not null,
    is_active     boolean     not null default true,
    created_at    timestamptz not null default now(),
    updated_at    timestamptz not null default now(),
    last_login_at timestamptz,
    constraint users_role_check check (role in ('admin', 'inhaber', 'buero'))
);

-- Anmeldename ohne Ruecksicht auf Gross-/Kleinschreibung eindeutig.
create unique index if not exists users_username_key on users (lower(username));
create index if not exists users_active_idx on users (is_active) where is_active;

create table if not exists user_audit (
    id         bigserial primary key,
    actor_id   uuid references users (id) on delete set null,
    actor_name text        not null,
    action     text        not null,
    subject_id uuid,
    subject    text        not null default '',
    detail     jsonb       not null default '{}'::jsonb,
    created_at timestamptz not null default now()
);

create index if not exists user_audit_created_at_idx on user_audit (created_at desc);