create table hosts
(
    id                  bigint generated by default as identity primary key,
    type                smallint     not null default 2 check (type in (1, 2)),
    agent_id            uuid unique,
    hostname            varchar(255) not null,
    description         text,
    current_ip          inet,
    status              smallint     not null default 1 check (status > 0),
    os                  text,
    agent_version       text,
    identity_confidence smallint     not null default 3 check (identity_confidence in (1, 2, 3)),
    fingerprint_hash    text,
    fingerprint_data    jsonb        not null default '{}'::jsonb,
    last_seen           timestamptz,
    created_at          timestamptz  not null default now(),
    updated_at          timestamptz  not null default now(),
    deleted_at          timestamptz,
    check (
        (type = 1 and agent_id is not null and identity_confidence = 1)
            or
        (type = 2 and agent_id is null and identity_confidence in (2, 3))
    )
);

create index hosts_hostname_trgm_idx on hosts using gin (hostname gin_trgm_ops);
create index hosts_current_ip_idx on hosts (current_ip);
create index hosts_agent_id_idx on hosts (agent_id);
create index hosts_fingerprint_hash_idx on hosts (fingerprint_hash);
