CREATE TABLE assessor_scanner_report_sections (
  id text PRIMARY KEY CHECK (btrim(id) <> ''),
  scan_run_id text NOT NULL REFERENCES assessor_scan_runs(id) ON DELETE CASCADE,
  import_file_id text NOT NULL REFERENCES assessor_import_files(id) ON DELETE CASCADE,
  tool_id text NOT NULL CHECK (btrim(tool_id) <> ''),
  section_key text NOT NULL CHECK (btrim(section_key) <> ''),
  section_state text NOT NULL CHECK (section_state IN (
    'observed', 'explicitly-absent', 'empty', 'not-assessed', 'access-denied',
    'license-restricted', 'unsupported', 'parse-failed', 'unknown'
  )),
  ordinal integer NOT NULL CHECK (ordinal >= 0),
  data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object'),
  UNIQUE (import_file_id, section_key)
);

CREATE INDEX assessor_scanner_report_sections_run_idx
  ON assessor_scanner_report_sections(scan_run_id, ordinal, id);
CREATE INDEX assessor_scanner_report_sections_lookup_idx
  ON assessor_scanner_report_sections(tool_id, section_key, section_state);
CREATE INDEX assessor_scanner_report_sections_data_idx
  ON assessor_scanner_report_sections USING gin (data jsonb_path_ops);

CREATE TABLE assessor_environment_observations (
  id text PRIMARY KEY CHECK (btrim(id) <> ''),
  scan_run_id text NOT NULL REFERENCES assessor_scan_runs(id) ON DELETE CASCADE,
  import_file_id text NOT NULL REFERENCES assessor_import_files(id) ON DELETE CASCADE,
  tool_id text NOT NULL CHECK (btrim(tool_id) <> ''),
  subject_type text NOT NULL CHECK (btrim(subject_type) <> ''),
  property_key text NOT NULL CHECK (btrim(property_key) <> ''),
  observation_state text NOT NULL CHECK (observation_state IN (
    'observed', 'explicitly-absent', 'empty', 'not-assessed', 'access-denied',
    'license-restricted', 'unsupported', 'parse-failed', 'unknown'
  )),
  observed_at timestamptz,
  data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object')
);

CREATE INDEX assessor_environment_observations_run_idx
  ON assessor_environment_observations(scan_run_id, property_key, id);
CREATE INDEX assessor_environment_observations_lookup_idx
  ON assessor_environment_observations(tool_id, subject_type, property_key, observation_state);
CREATE INDEX assessor_environment_observations_observed_at_idx
  ON assessor_environment_observations(observed_at) WHERE observed_at IS NOT NULL;
CREATE INDEX assessor_environment_observations_data_idx
  ON assessor_environment_observations USING gin (data jsonb_path_ops);