ALTER TABLE gateway_request_log
ADD COLUMN IF NOT EXISTS api_key_id text;

ALTER TABLE gateway_request_log
ADD COLUMN IF NOT EXISTS presented_api_key_public_id text;

ALTER TABLE gateway_request_log
ADD COLUMN IF NOT EXISTS presented_api_key_hash text;

ALTER TABLE gateway_request_log
ADD COLUMN IF NOT EXISTS presented_api_key_salt text;

ALTER TABLE gateway_request_log
ADD COLUMN IF NOT EXISTS api_key_authenticated boolean DEFAULT false;

ALTER TABLE gateway_request_log
ADD COLUMN IF NOT EXISTS api_key_authorized boolean DEFAULT false;

ALTER TABLE gateway_request_log
ADD COLUMN IF NOT EXISTS api_key_enforced boolean DEFAULT false;

ALTER TABLE gateway_request_log
ADD COLUMN IF NOT EXISTS api_key_auth_reason text;

CREATE TABLE IF NOT EXISTS api_key_auth_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    api_key_id text,
    api_key_public_id text,
    client_name text,
    method text,
    path text,
    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 numeric NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS api_keys (
    id uuid PRIMARY KEY,
    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,
    created_at timestamptz NOT NULL DEFAULT now(),
    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 uuid PRIMARY KEY,
    name text NOT NULL UNIQUE,
    description text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS api_key_right_grants (
    api_key_id uuid NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    right_id uuid 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 integer PRIMARY KEY CHECK (id = 1),
    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 (id, enforce_api_keys)
VALUES (1, false)
ON CONFLICT (id) DO NOTHING;

CREATE TABLE IF NOT EXISTS api_key_client_config (
    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()
);
