CREATE TABLE networkscan_report_host
(
    task_id         bigint not null references tasks (id) on update cascade on delete cascade,
    id              bigint generated by default as identity primary key,
    host_address    inet not null,
    mac             macaddr,
    domain_name     varchar(128)[] not null default '{}',
    hops            int,
    error           jsonb
);

CREATE TABLE networkscan_report
(
    id          bigint generated by default as identity primary key,
    host_id     bigint not null references networkscan_report_host (id) on update cascade on delete cascade,
    system_name text   not null,
    percent     float  not null
);

CREATE TABLE networkscan_report_class
(
    id           bigint generated by default as identity primary key,
    system_id    bigint        not null references networkscan_report (id) on update cascade on delete cascade,
    task_id      bigint        not null references tasks (id) on update cascade on delete cascade,
    vendor       varchar(256)  not null,
    os_family    varchar(64)   not null,
    os_version   varchar(64)   not null,
    device_type  varchar(64)   not null,
    cpe          varchar(64)[] not null default '{}'
);

CREATE TABLE asset_system_info
(
    asset_id    bigint        references hosts (id) on update cascade on delete set null unique,
    system_name text          not null,
    vendor      varchar(256)  not null,
    os_family   varchar(64)   not null,
    os_version  varchar(64)   not null,
    device_type varchar(64)   not null,
    cpe         varchar(64)[] not null default '{}'
);

CREATE INDEX networkscan_report_host_id_idx ON networkscan_report (host_id);
CREATE INDEX networkscan_report_host_task_id_idx ON networkscan_report_host (task_id);
CREATE INDEX networkscan_report_class_task_id_idx ON networkscan_report_class (task_id);
CREATE INDEX asset_system_info_asset_id_idx ON asset_system_info (asset_id);