create extension if not exists pgcrypto;

create or replace function set_updated_at()
returns trigger language plpgsql as $$
begin
    new.updated_at = now();
    return new;
end;
$$;

create table if not exists api_keys (
    id uuid primary key default gen_random_uuid(),
    key_prefix text not null unique,
    key_hash text not null,
    hash_algorithm text not null default 'hmac_sha256_pepper',
    operator_id text,
    domain_scope text[] not null default '{}',
    scopes text[] not null default '{}',
    status text not null default 'active' check (status in ('active', 'rotating', 'revoked', 'expired')),
    expires_at timestamptz,
    last_used_at timestamptz,
    revoked_at timestamptz,
    meta jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table if not exists api_access_audit_log (
    id uuid primary key default gen_random_uuid(),
    request_id text not null,
    key_prefix text,
    operator_subject_hash text,
    domain_scope_hash text,
    scope text not null,
    endpoint text not null,
    method text not null,
    decision text not null check (decision in ('allow', 'deny', 'rate_limited', 'revoked', 'expired', 'unavailable')),
    status_code integer not null,
    ip_hash text,
    user_agent_hash text,
    meta jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now()
);

create index if not exists api_keys_status_expires_idx on api_keys (status, expires_at);
create index if not exists api_keys_scopes_gin_idx on api_keys using gin (scopes);
create index if not exists api_keys_domain_scope_gin_idx on api_keys using gin (domain_scope);
create index if not exists api_access_audit_created_idx on api_access_audit_log (created_at desc);
create index if not exists api_access_audit_key_scope_idx on api_access_audit_log (key_prefix, scope, created_at desc);
create index if not exists api_access_audit_decision_idx on api_access_audit_log (decision, created_at desc);

drop trigger if exists api_keys_set_updated_at on api_keys;
create trigger api_keys_set_updated_at
before update on api_keys
for each row execute function set_updated_at();
