create table task_reports
(
    task_id     bigint      primary key references tasks (id) on update cascade on delete cascade,
    task_type smallint    not null,
    up_hosts    integer     not null default 0,
    down_hosts  integer     not null default 0,
    total_hosts integer     not null default 0,
    started_at  timestamptz,
    finished_at timestamptz,
    elapsed_ms  bigint      not null default 0,
    app_version text        not null default '',
    created_at  timestamptz not null default now(),
    updated_at  timestamptz not null default now()
);

create table pentest_report_findings
(
    id          bigint generated by default as identity primary key,
    task_id     bigint      not null references tasks (id) on update cascade on delete cascade,
    task_type smallint    not null,
    asset_id    bigint references hosts (id) on update cascade on delete set null,
    observed_ip inet        not null,
    cve_id      text        not null,
    port        integer,
    protocol    text,
    service_name text,
    product_vendor text,
    product_name text,
    product_version text,
    cpe         text,
    cpe_part    text,
    cpe_vendor  text,
    cpe_product text,
    cpe_version text,
    cvss        real        not null default 0,
    severity    text        not null,
    payload     jsonb       not null default '{}'::jsonb,
    epss        double precision,
    percentile  double precision,
    created_at  timestamptz not null default now()
);

create table agent_inventory_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,
    host_info   jsonb       not null default '{}'::jsonb,
    inventory   jsonb       not null default '{}'::jsonb,
    created_at  timestamptz not null default now()
);

create index task_reports_task_type_idx on task_reports (task_type);
create index pentest_report_findings_task_id_idx on pentest_report_findings (task_id);
create index pentest_report_findings_task_type_idx on pentest_report_findings (task_type);
create index pentest_report_findings_cve_id_idx on pentest_report_findings (cve_id);
create index pentest_report_findings_asset_id_idx on pentest_report_findings (asset_id);
create index pentest_report_findings_observed_ip_idx on pentest_report_findings (observed_ip);
create index pentest_report_findings_severity_idx on pentest_report_findings (severity);
create index pentest_report_findings_port_product_idx
    on pentest_report_findings (task_id, asset_id, observed_ip, port, protocol, product_vendor, product_name, product_version);
create index pentest_report_findings_cpe_dimensions_idx
    on pentest_report_findings (task_id, cpe_part, cpe_vendor, cpe_product, cpe_version);
create index agent_inventory_report_assets_task_id_idx on agent_inventory_report_assets (task_id);
create index agent_inventory_report_assets_asset_id_idx on agent_inventory_report_assets (asset_id);
create index agent_inventory_report_assets_observed_ip_idx on agent_inventory_report_assets (observed_ip);
