create table public.api_keys (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references public.users(id) on delete cascade,
    name text not null,
    key_prefix text not null,
    key_hash text not null unique,
    last_used_at timestamptz,
    expires_at timestamptz,
    revoked_at timestamptz,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create index api_keys_user_id_idx on public.api_keys(user_id);
create index api_keys_key_hash_idx on public.api_keys(key_hash);

create trigger api_keys_updated_at
    before update on public.api_keys
    for each row execute function public.handle_updated_at();

alter table public.api_keys enable row level security;

create policy "Users can view own API keys"
    on public.api_keys for select
    using (auth.uid() = user_id);

create policy "Users can create own API keys"
    on public.api_keys for insert
    with check (auth.uid() = user_id);

create policy "Users can update own API keys"
    on public.api_keys for update
    using (auth.uid() = user_id);

create policy "Users can delete own API keys"
    on public.api_keys for delete
    using (auth.uid() = user_id);

create or replace function public.validate_api_key(api_key_hash text)
returns table (user_id uuid, api_key_id uuid)
language plpgsql
security definer
as $$
begin
    return query
    select ak.user_id, ak.id as api_key_id
    from public.api_keys ak
    where ak.key_hash = api_key_hash
        and ak.revoked_at is null
        and (ak.expires_at is null or ak.expires_at > now());
end;
$$;

create or replace function public.update_api_key_last_used(p_api_key_id uuid)
returns void
language plpgsql
security definer
as $$
begin
    update public.api_keys
    set last_used_at = now()
    where id = p_api_key_id;
end;
$$;