CREATE TABLE IF NOT EXISTS api_keys (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    api_keys_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    public_id text NOT NULL UNIQUE,
    name text NOT NULL,
    description text,
    client_name text,
    key_salt text NOT NULL,
    key_hash text NOT NULL,
    expires_at timestamptz,
    is_active boolean NOT NULL DEFAULT true,
    last_used_at timestamptz,
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_api_keys_client_name ON api_keys(client_name);
CREATE INDEX IF NOT EXISTS idx_api_keys_expires_at ON api_keys(expires_at);
CREATE TABLE IF NOT EXISTS api_key_rights (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_rights_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    name text NOT NULL UNIQUE,
    description text,
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS api_key_right_grants (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_right_grants_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    api_key_id bigint NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    right_id bigint NOT NULL REFERENCES api_key_rights(id) ON DELETE CASCADE,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (api_key_id, right_id)
);
CREATE TABLE IF NOT EXISTS api_key_config (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_config_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    enforce_api_keys boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO api_key_config (api_key_config_id, enforce_api_keys)
VALUES ('00000000-0000-0000-0000-000000000000', false) ON CONFLICT (api_key_config_id) DO NOTHING;
CREATE TABLE IF NOT EXISTS api_key_client_config (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_client_config_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    client_name text PRIMARY KEY,
    enforce_api_keys boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS api_key_auth_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_auth_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    api_key_id bigint REFERENCES api_keys(id) ON DELETE
    SET NULL,
        api_key_public_id text,
        client_name text,
        method text NOT NULL,
        path text NOT NULL,
        presented_key_hash text,
        presented_key_salt text,
        required_rights jsonb,
        granted_rights jsonb,
        authenticated boolean NOT NULL DEFAULT false,
        authorized boolean NOT NULL DEFAULT false,
        enforced boolean NOT NULL DEFAULT false,
        failure_reason text,
        remote_addr text,
        user_agent text,
        time timestamptz NOT NULL DEFAULT now(),
        UNIQUE(request_id, api_key_public_id)
);
CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_api_key_id ON api_key_auth_log(api_key_id);
CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_client_name ON api_key_auth_log(client_name);
CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_time ON api_key_auth_log(time);