create table if not exists xylok
(
  name  text not null primary key,
  value text not null
);
insert into xylok (name, value) values ('version', '11');

-- region Client
create table if not exists clients
(
  client_id                text    not null primary key,
  name                     text    not null,
  short_name               text    not null,
  rmf_c_level              int     not null,
  rmf_i_level              int     not null,
  rmf_a_level              int     not null,
  rmf_overlay_names        json    not null,
  rmf_add_control_names    json    not null,
  rmf_remove_control_names json    not null,
  is_classified            boolean not null,
  mark_results_as          text    not null,
  tombstone                boolean not null,
  question_answers         json    not null
);

create table if not exists client_settings
(
  client_id text not null,
  name      text not null,
  value     json not null
);

create unique index if not exists unique_client_settings ON client_settings (client_id, name);

create table if not exists locations
(
  location_id        text not null primary key,
  client_id          text not null,
  name               text not null,
  short_name         text not null,
  inside_location_id text null,
  question_answers   json not null,
  benchmark_ids      json not null
);

create table if not exists machines
(
  machine_id         text    not null primary key,
  client_id          text    not null,
  location_id        text    not null,
  family_id          text    null,
  host_machine_id    text    null,
  tombstone          boolean not null,
  name               text    not null,
  short_name         text    not null,
  question_answers   json    not null,
  benchmark_ids      json    not null,
  os_key             text    not null,
  device_type        text    not null,
  hostname           text    not null,
  purpose            text    not null,
  make               text    not null,
  model              text    not null,
  serial             text    not null,
  ip_address         text    not null,
  mac_address        text    not null,
  fqdn               text    not null,
  new_information    json    not null,
  field_overwritable json    not null,
  pps_information    json    not null
);

create table if not exists machine_settings
(
  machine_id text not null,
  name      text not null,
  value     json not null
);

create table if not exists families
(
  family_id text    not null primary key,
  client_id text    not null,
  tombstone boolean not null,
  name      text    not null
);

create table if not exists documents
(
  document_id             text     not null primary key,
  client_id               text     not null,
  upload_date             datetime not null,
  document_date           datetime null,
  document_classification text     not null,
  title                   text     not null,
  notes                   text     not null,
  include_in_search       boolean  not null,
  contents                blob     not null
);

create table if not exists reports
(
  report_id   text     not null primary key,
  client_id   text     not null,
  update_date datetime not null,
  is_current  boolean  not null,
  report_type text     not null,
  description text     not null,
  data        json     not null
);
-- endregion

-- region AA
create table if not exists aa
(
  aa_id          text not null primary key,
  update_date    datetime not null,
  client_id      text null, -- Which AA pool the item fills
  family_id      text null, -- Which AA pool the item fills
  machine_id     text null, -- Which AA pool the item fills
  command_values json not null, -- Dict of {command pk: hash val, ...}
  status         text not null, -- Desired finding status
  comment        text not null  -- Desired comment
);

create unique index if not exists aa_command_value_outputs_unique ON aa (family_id, client_id, machine_id, command_values);
-- endregion

-- region Ratings
create table if not exists cci_ratings
(
  cci_rating_id                  text     not null primary key,
  client_id                      text     not null,
  update_date                    datetime not null,
  reviewed_date                  datetime null,
  cci_number                     int      not null,
  is_current                     boolean  not null,
  is_technical                   boolean  not null,
  nontechnical_compliance_status text     not null,
  nontechnical_impact            int      not null,
  nontechnical_likelihood        int      not null,
  nontechnical_risk              int      not null,
  nontechnical_comment           text     not null,
  technical_compliance_status    text     not null,
  technical_impact               int      not null,
  technical_likelihood           int      not null,
  technical_risk                 int      not null,
  technical_comment              text     not null,
  impact                         int      not null,
  raw_likelihood                 int      not null,
  raw_risk                       int      not null,
  raw_comment                    text     not null,
  mitigated_likelihood           int      not null,
  mitigated_risk                 int      not null,
  mitigated_comment              text     not null,
  recommendation_comment         text     not null,
  compliance_status              text     not null,
  scan_items                     json     not null
);

create table if not exists control_ratings
(
  control_rating_id    text     not null primary key,
  client_id            text     not null,
  update_date          datetime not null,
  reviewed_date        datetime null,
  control_name         text     not null,
  is_current         boolean  not null,
  impact               int      not null,
  raw_likelihood       int      not null,
  raw_risk             int      not null,
  raw_comment          int      not null,
  mitigated_likelihood int      not null,
  mitigated_risk       int      not null,
  mitigated_comment    text     not null,
  default_comment      text     not null,
  compliance_status    text     not null,
  cci_ratings          json     not null
);

create table if not exists tech_ratings
(
  tech_rating_id text    not null primary key,
  client_id      text    not null,
  check_id       text    not null,
  has_findings   boolean not null,
  impact         integer not null,
  likelihood     integer not null,
  risk           integer not null,
  comment        text    not null,
  scan_items     json    not null
);

create table if not exists poam_ratings
(
  poam_rating_id     text     not null primary key,
  client_id          text     not null,
  update_date        datetime not null,
  reviewed_date      datetime null,
  cci_number         integer  not null,
  control_name       text     not null,
  is_current         boolean  not null,
  user_edited        boolean  not null,
  comments           text     not null,
  scheduled_date     datetime null,
  description        text     not null,
  devices            text     not null,
  impact_description text     not null,
  milestone          text     not null,
  milestone_changes  text     not null,
  mitigations        text     not null,
  office             text     not null,
  raw_comment        text     not null,
  raw_severity       text     not null,
  recommendation     text     not null,
  related            text     not null,
  relevance          text     not null,
  resources          text     not null,
  security_checks    text     not null,
  source             text     not null,
  status             text     not null,
  severity           int      not null,
  likelihood         int      not null,
  impact             int      not null,
  risk               int      not null
);

create table if not exists rtm_ratings
(
  rtm_rating_id       text     not null primary key,
  client_id           text     not null,
  update_date         datetime not null,
  reviewed_date       datetime null,
  is_current          boolean  not null,
  req_number_constant text     not null,
  req_number_readable text     not null,
  sort_order          int      not null,
  section             text     not null,
  req_desc            text     not null,
  tech_ref            text     not null,
  objectives          text     not null,
  ver_method          text     not null,
  ver_date            text     null,
  ver_artifact        text     not null,
  req_val_date        text     not null,
  traced_from         json     not null,
  comment             text     not null,
  ver_phase           text     not null,
  req_owner           json     not null,
  ver_authority       text     not null
);

-- endregion

-- region Scans
create table if not exists scans
(
  scan_id       text     not null primary key,
  client_id     text     not null,
  location_id   text     not null,
  machine_id    text     null,
  date          datetime not null,
  description   text     not null,
  user_editable boolean  not null
);

create table if not exists scan_items
(
  scan_item_id text not null primary key,
  scan_id      text not null,
  check_id     text not null,
  status       text not null,
  comment      text not null
);

create table if not exists scan_item_values
(
  scan_item_value_id text not null primary key,
  scan_item_id       text not null,
  command_id         text not null,
  raw_output         text not null,
  pp_output          text null,
  additional_output  json not null
);

-- endregion

-- region Benchmarks
create table if not exists commands
(
  command_id   text     not null primary key,
  check_id     text     not null,
  created_date datetime not null,
  cmd_order    int      not null,
  oses         json     not null,
  os_tags      json     not null,
  runner       text     not null,
  command      text     not null,
  pp_script    text     not null,
  tombstone    boolean  not null
);

create table if not exists checks
(
  check_id       text    not null primary key,
  benchmark_pk   text    not null,
  tombstone      boolean not null,
  human_id       text    not null,
  vulnerability_id       not null,
  rule_id        text    not null,
  group_title    text    not null,
  title          text    not null,
  nist_impact    int     not null,
  check_content  text    not null,
  discussion     text    not null,
  fix            text    not null,
  expert_comment text    not null,
  ccis           json    not null
);

create table if not exists benchmarks
(
  benchmark_pk               text    not null primary key,
  benchmark_id               text    not null unique on conflict ignore,
  alternate_benchmark_ids    json    not null,
  commit_id                  text    not null,
  tombstone                  boolean not null,
  sunset                     boolean not null,
  title                      text    not null,
  short_title                text    not null,
  source                     text    not null,
  is_protected               boolean not null,
  questions                  json    not null,
  matching_software_versions json    not null
);

create table if not exists versions
(
  version_id   text     not null primary key,
  benchmark_pk text     not null,
  is_ready     boolean  not null,
  tombstone    boolean  not null,
  tag          text     not null,
  date         datetime not null
);

create unique index if not exists version_tags ON versions (version_id, tag);

create table if not exists version_checks
(
  version_id text not null,
  check_id   text not null
);

create unique index if not exists unique_version_checks ON version_checks (version_id, check_id);
-- endregion