create table pentest_report_assets
(
    id          bigint generated by default as identity primary key,
    task_id     bigint      not null references task_reports (task_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,
    created_at  timestamptz not null default now()
);

create unique index pentest_report_assets_identity_idx
    on pentest_report_assets (task_id, coalesce(asset_id, -1), observed_ip);
create index pentest_report_assets_task_id_idx on pentest_report_assets (task_id);
create index pentest_report_assets_asset_id_idx on pentest_report_assets (asset_id);
create index pentest_report_assets_observed_ip_idx on pentest_report_assets (observed_ip);

alter table pentest_report_ports
    add column report_asset_id bigint references pentest_report_assets (id) on update cascade on delete cascade;

alter table pentest_report_findings
    add column report_asset_id bigint references pentest_report_assets (id) on update cascade on delete cascade;

create index pentest_report_ports_report_asset_id_idx on pentest_report_ports (report_asset_id);
create index pentest_report_findings_report_asset_id_idx on pentest_report_findings (report_asset_id);
