CREATE TABLE assessor_policy_evaluation_runs (
  id text PRIMARY KEY CHECK (btrim(id) <> ''),
  project_id text NOT NULL REFERENCES assessor_projects(id) ON DELETE CASCADE,
  aggregation_run_id text NOT NULL REFERENCES assessor_policy_snapshot_aggregation_runs(id) ON DELETE CASCADE,
  environment_profile_id text NOT NULL,
  evaluator_version text NOT NULL CHECK (btrim(evaluator_version) <> ''),
  comparator_catalog_version text NOT NULL CHECK (btrim(comparator_catalog_version) <> ''),
  dependency_rules_version text NOT NULL CHECK (btrim(dependency_rules_version) <> ''),
  risk_rules_version text NOT NULL CHECK (btrim(risk_rules_version) <> ''),
  run_status text NOT NULL CHECK (run_status IN ('complete', 'partial', 'blocked')),
  projected_finding_count integer NOT NULL DEFAULT 0 CHECK (projected_finding_count = 0),
  started_at timestamptz NOT NULL,
  completed_at timestamptz NOT NULL CHECK (completed_at >= started_at),
  data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object'),
  FOREIGN KEY (project_id, environment_profile_id)
    REFERENCES assessor_environment_profiles(project_id, id)
);

CREATE INDEX assessor_policy_evaluation_runs_project_idx
  ON assessor_policy_evaluation_runs(project_id, completed_at, id);
CREATE INDEX assessor_policy_evaluation_runs_aggregation_idx
  ON assessor_policy_evaluation_runs(aggregation_run_id, completed_at, id);

CREATE TABLE assessor_policy_evaluation_baseline_sources (
  evaluation_run_id text NOT NULL REFERENCES assessor_policy_evaluation_runs(id) ON DELETE CASCADE,
  baseline_analysis_id text NOT NULL REFERENCES assessor_microsoft_security_baseline_analyses(id) ON DELETE RESTRICT,
  baseline_id text NOT NULL REFERENCES assessor_microsoft_security_baselines(id) ON DELETE RESTRICT,
  PRIMARY KEY (evaluation_run_id, baseline_analysis_id)
);

CREATE INDEX assessor_policy_evaluation_baseline_source_idx
  ON assessor_policy_evaluation_baseline_sources(baseline_analysis_id, evaluation_run_id);

CREATE TABLE assessor_policy_evaluation_results (
  id text PRIMARY KEY CHECK (btrim(id) <> ''),
  evaluation_run_id text NOT NULL REFERENCES assessor_policy_evaluation_runs(id) ON DELETE CASCADE,
  aggregation_state_id text NOT NULL REFERENCES assessor_policy_snapshot_states(id) ON DELETE CASCADE,
  identity_id text NOT NULL REFERENCES assessor_policy_identities(id) ON DELETE RESTRICT,
  classification text NOT NULL CHECK (classification IN ('configured_match', 'confirmed_configured_deviation', 'review_required')),
  comparator_kind text NOT NULL CHECK (comparator_kind IN ('equality', 'minimum', 'maximum', 'enum', 'bitmask', 'set', 'audit-flags')),
  finding_eligible boolean NOT NULL,
  effective_state text NOT NULL DEFAULT 'unknown' CHECK (effective_state = 'unknown'),
  data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object'),
  UNIQUE (evaluation_run_id, aggregation_state_id),
  CHECK (NOT finding_eligible OR classification = 'confirmed_configured_deviation')
);

CREATE INDEX assessor_policy_evaluation_results_run_idx
  ON assessor_policy_evaluation_results(evaluation_run_id, classification, id);
CREATE INDEX assessor_policy_evaluation_results_identity_idx
  ON assessor_policy_evaluation_results(identity_id, evaluation_run_id);

CREATE TABLE assessor_policy_evaluation_evidence (
  id text PRIMARY KEY CHECK (btrim(id) <> ''),
  evaluation_result_id text NOT NULL REFERENCES assessor_policy_evaluation_results(id) ON DELETE CASCADE,
  evidence_kind text NOT NULL CHECK (evidence_kind IN ('customer-observation', 'gpo-backup', 'baseline-setting', 'baseline-analysis', 'environment-context')),
  source_id text NOT NULL CHECK (btrim(source_id) <> ''),
  data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object'),
  UNIQUE (evaluation_result_id, evidence_kind, source_id)
);

CREATE INDEX assessor_policy_evaluation_evidence_result_idx
  ON assessor_policy_evaluation_evidence(evaluation_result_id, evidence_kind, id);