create table pentest_report_ports
(
    id              bigint generated by default as identity primary key,
    task_id         bigint    not null references tasks (id) on update cascade on delete cascade,
    asset_id        bigint references hosts (id) on update cascade on delete set null,
    observed_ip     inet      not null,
    port            integer   not null,
    protocol        text      not null,
    state           text      not null,
    service_name    text,
    product_vendor  text,
    product_name    text,
    product_version text,
    cpe             text[]    not null default '{}',
    created_at      timestamp not null default now()
);

create index pentest_report_ports_task_id_idx on pentest_report_ports (task_id);
create index pentest_report_ports_asset_id_idx on pentest_report_ports (asset_id);
create index pentest_report_ports_observed_ip_idx on pentest_report_ports (observed_ip);
create index pentest_report_ports_lookup_idx on pentest_report_ports (task_id, observed_ip, port, protocol);
