gupi-osint-board/server/caseReports.ts

import { randomUUID } from 'node:crypto'
import type { Pool, PoolClient } from 'pg'
import type { CaseReport, CaseReportEvidence, CaseReportSubmissionInput, CaseReportSubmissionStatus, EvidenceVerification, SourceFileType } from '../src/types.js'

type LevelRef = { id:string; board_id:string }

function trimmed(value: unknown, max: number) { return String(value || '').trim().slice(0,max) }
function unfinishedRelation(value: string) {
  return !value.trim() || /^proof\s+that(?:\s*(?:…|\.{3}))?\s*$/iu.test(value.trim())
}

type RecognitionRow = {
  evidence_accepted:boolean;extraction_status:'succeeded'|'unsupported'|'failed'|null
  deterministic_evaluated:boolean;deterministic_matched:boolean|null;deterministic_score:string|null
  matched_anchor_count:number|null;minimum_anchor_matches:number|null
  semantic_status:'pending'|'succeeded'|'failed'|null;semantic_subject:'target'|'related'|'ambiguous'|'neither'|null
  semantic_supports_claim:boolean|null;semantic_confidence:string|null;semantic_minimum_confidence:string|null
}

function verification(row:RecognitionRow):EvidenceVerification {
  const score=row.deterministic_score === null ? undefined : Number(row.deterministic_score)
  const metrics={ score,matchedMarkers:row.matched_anchor_count ?? undefined,requiredMarkers:row.minimum_anchor_matches ?? undefined }
  if (row.evidence_accepted) return { status:'accepted',detail:row.deterministic_matched
    ? 'The extracted text matched the source fingerprint for this objective.'
    : 'Semantic review found that this exhibit directly supports the target claim.',...metrics }
  if (row.semantic_status === 'pending') return { status:'semantic_pending',detail:'Text was extracted, but semantic review is still pending.',...metrics }
  if (row.semantic_status === 'failed') return { status:'semantic_failed',detail:'Text was extracted, but semantic review could not be completed. Retry document analysis.',...metrics }
  if (row.semantic_status === 'succeeded') {
    if (row.semantic_subject === 'target' && row.semantic_supports_claim) {
      const confidence=Math.round(Number(row.semantic_confidence || 0) * 100)
      const required=Math.round(Number(row.semantic_minimum_confidence || 0) * 100)
      return { status:'semantic_rejected',detail:`Semantic review supported the claim, but confidence was ${confidence}% and this objective requires ${required}%.`,...metrics }
    }
    const subject = row.semantic_subject === 'related' ? 'a related person rather than the claim subject'
      : row.semantic_subject === 'ambiguous' ? 'an ambiguous subject' : 'content that does not establish the target claim'
    return { status:'semantic_rejected',detail:`Semantic review found ${subject}.`,...metrics }
  }
  if (row.deterministic_evaluated) {
    const matched=row.matched_anchor_count || 0,required=row.minimum_anchor_matches || 0
    const percentage=score === undefined ? null : Math.round(score * 100)
    return { status:'text_not_matched',detail:`OCR succeeded, but this exhibit matched ${matched} of ${required} required source markers${percentage === null ? '' : ` (best similarity ${percentage}%)`}.`,...metrics }
  }
  if (row.extraction_status === 'failed') return { status:'ocr_unavailable',detail:'The image was saved, but OCR could not read usable text from it.' }
  if (row.extraction_status === 'unsupported') return { status:'ocr_unavailable',detail:'This file format could not be checked automatically.' }
  if (row.extraction_status === 'succeeded') return { status:'not_evaluated',detail:'Text was extracted, but no evidence-recognition rule evaluated this exhibit.' }
  return { status:'not_evaluated',detail:'This exhibit has not been analyzed for the objective.' }
}

export async function loadCaseReport(client: Pool | PoolClient, level: LevelRef): Promise<CaseReport | undefined> {
  const config = (await client.query<{ title:string; investigator_name:string; required_for_completion:boolean }>(
    'SELECT title,investigator_name,required_for_completion FROM osint.case_reports WHERE board_id=$1', [level.board_id])).rows[0]
  if (!config) return undefined
  const claims = await client.query<{ exhibit_id:string; statement:string }>(`SELECT claim.exhibit_id,claim.statement
    FROM osint.claim_exhibits claim JOIN osint.exhibits exhibit ON exhibit.id=claim.exhibit_id
    WHERE exhibit.board_id=$1 ORDER BY exhibit.created_at,exhibit.id`, [level.board_id])
  const evidence = await client.query<{
    claim_exhibit_id:string; connection_id:string; document_exhibit_id:string; display_number:number; document_title:string
    document_type_id:SourceFileType; relation_text:string; published_at:Date|null; citation_text:string; source_uri:string|null
    evidence_accepted:boolean;extraction_status:'succeeded'|'unsupported'|'failed'|null
    deterministic_evaluated:boolean;deterministic_matched:boolean|null;deterministic_score:string|null
    matched_anchor_count:number|null;minimum_anchor_matches:number|null
    semantic_status:'pending'|'succeeded'|'failed'|null;semantic_subject:'target'|'related'|'ambiguous'|'neither'|null
    semantic_supports_claim:boolean|null;semantic_confidence:string|null;semantic_minimum_confidence:string|null
  }>(`SELECT claim.exhibit_id AS claim_exhibit_id,connection.id AS connection_id,document.exhibit_id AS document_exhibit_id,
      citation.display_number,document.title AS document_title,document.document_type_id,COALESCE(connection.label,'') AS relation_text,
      document.published_at,document.citation_text,document.source_uri,
      (COALESCE(deterministic.matched,FALSE) OR COALESCE(semantic.status='succeeded' AND semantic.subject='target'
        AND semantic.supports_claim AND semantic.confidence >= semantic.minimum_confidence,FALSE)) AS evidence_accepted,
      extraction.status AS extraction_status,(deterministic.rule_id IS NOT NULL) AS deterministic_evaluated,
      deterministic.matched AS deterministic_matched,deterministic.score::text AS deterministic_score,
      deterministic.matched_anchor_count,deterministic.minimum_anchor_matches,
      semantic.status AS semantic_status,semantic.subject AS semantic_subject,semantic.supports_claim AS semantic_supports_claim,
      semantic.confidence::text AS semantic_confidence,semantic.minimum_confidence::text AS semantic_minimum_confidence
    FROM osint.claim_exhibits claim
    JOIN osint.exhibits claim_exhibit ON claim_exhibit.id=claim.exhibit_id AND claim_exhibit.board_id=$1
    JOIN osint.exhibit_connections connection ON connection.board_id=$1
      AND (connection.from_exhibit_id=claim.exhibit_id OR connection.to_exhibit_id=claim.exhibit_id)
    JOIN osint.document_exhibits document ON document.exhibit_id=CASE
      WHEN connection.from_exhibit_id=claim.exhibit_id THEN connection.to_exhibit_id ELSE connection.from_exhibit_id END
    JOIN osint.exhibit_citations citation ON citation.board_id=$1 AND citation.exhibit_id=document.exhibit_id
    LEFT JOIN LATERAL (SELECT evaluation.rule_id,evaluation.matched,evaluation.score,evaluation.matched_anchor_count,rule.minimum_anchor_matches
      FROM osint.evidence_match_evaluations evaluation
      JOIN osint.evidence_match_rules rule ON rule.id=evaluation.rule_id AND rule.board_id=$1 AND rule.enabled
      WHERE evaluation.level_id=$2 AND evaluation.document_exhibit_id=document.exhibit_id
      ORDER BY evaluation.matched DESC,evaluation.score DESC,evaluation.evaluated_at DESC LIMIT 1) deterministic ON TRUE
    LEFT JOIN LATERAL (SELECT evaluation.status,evaluation.subject,evaluation.supports_claim,evaluation.confidence,rule.minimum_confidence
      FROM osint.evidence_semantic_evaluations evaluation
      JOIN osint.evidence_semantic_rules rule ON rule.id=evaluation.rule_id AND rule.board_id=$1 AND rule.enabled
      WHERE evaluation.level_id=$2 AND evaluation.document_exhibit_id=document.exhibit_id
      ORDER BY (evaluation.status='succeeded' AND evaluation.subject='target' AND evaluation.supports_claim
        AND evaluation.confidence >= rule.minimum_confidence) DESC,evaluation.updated_at DESC LIMIT 1) semantic ON TRUE
    LEFT JOIN LATERAL (SELECT candidate.status FROM osint.asset_text_extractions candidate
      WHERE candidate.asset_id=document.asset_id ORDER BY candidate.updated_at DESC LIMIT 1) extraction ON TRUE
    ORDER BY claim_exhibit.created_at,claim.exhibit_id,citation.display_number,connection.created_at`, [level.board_id,level.id])
  const latest = (await client.query<{ id:string;status:CaseReportSubmissionStatus; feedback:string }>(
    'SELECT id,status,feedback FROM osint.case_report_submissions WHERE level_id=$1 ORDER BY submitted_at DESC,id DESC LIMIT 1', [level.id])).rows[0]
  const issues = latest ? (await client.query<{ issue_key:string }>(
    'SELECT issue_key FROM osint.case_report_submission_issues WHERE submission_id=$1 ORDER BY issue_key', [latest.id])).rows.map(row => row.issue_key) : []
  const byClaim = new Map<string,CaseReportEvidence[]>()
  for (const row of evidence.rows) byClaim.set(row.claim_exhibit_id,[...(byClaim.get(row.claim_exhibit_id) || []),{
    connectionId:row.connection_id,documentExhibitId:row.document_exhibit_id,displayNumber:row.display_number,
    documentTitle:row.document_title,fileType:row.document_type_id,relationText:row.relation_text,
    publishedAt:row.published_at?.toISOString(),sourceCitation:row.citation_text || undefined,sourceUri:row.source_uri || undefined,
    evidenceAccepted:row.evidence_accepted,verification:verification(row),
  }])
  return { title:config.title,investigatorName:config.investigator_name,requiredForCompletion:config.required_for_completion,
    status:latest?.status || 'draft',feedback:latest?.feedback,issues,
    claims:claims.rows.map(row => ({ claimExhibitId:row.exhibit_id,statement:row.statement,evidence:byClaim.get(row.exhibit_id) || [] })) }
}

export async function submitCaseReport(pool: Pool, levelSlug: string, rawInput: CaseReportSubmissionInput): Promise<CaseReport | null> {
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const level = (await client.query<LevelRef>(
      'SELECT id,board_id FROM osint.levels WHERE slug=$1 FOR UPDATE', [levelSlug])).rows[0]
    if (!level) { await client.query('ROLLBACK'); return null }
    const report = (await client.query<{ board_id:string }>('SELECT board_id FROM osint.case_reports WHERE board_id=$1 FOR UPDATE', [level.board_id])).rows[0]
    if (!report) throw new Error('This level does not have a case report')
    const investigatorName = trimmed(rawInput?.investigatorName,300) || 'Player'
    await client.query('UPDATE osint.case_reports SET investigator_name=$2,updated_at=NOW() WHERE board_id=$1', [level.board_id,investigatorName])
    const assembled = (await loadCaseReport(client,level))!
    const pendingGoals = Number((await client.query<{ count:string }>(`SELECT COUNT(*)::text AS count FROM osint.level_goals goal
      WHERE goal.board_id=$1 AND goal.enabled AND (
        NOT EXISTS (SELECT 1 FROM osint.level_goal_flag_requirements requirement WHERE requirement.goal_id=goal.id)
        OR EXISTS (SELECT 1 FROM osint.level_goal_flag_requirements requirement WHERE requirement.goal_id=goal.id
          AND NOT EXISTS (SELECT 1 FROM osint.level_flags flag WHERE flag.level_id=$2 AND flag.flag_key=requirement.flag_key)))`,
    [level.board_id,level.id])).rows[0].count)
    const connectedAccepted = assembled.claims.length > 0 && assembled.claims.every(claim => claim.evidence.some(item => item.evidenceAccepted))
    const blockingIssues = new Set<string>()
    if (!assembled.claims.length) blockingIssues.add('missing_claim')
    for (const claim of assembled.claims) {
      const accepted = claim.evidence.filter(item => item.evidenceAccepted)
      if (!accepted.length) {
        blockingIssues.add('missing_accepted_evidence')
        blockingIssues.add(claim.evidence.length ? 'connected_evidence_unverified' : 'missing_connected_evidence')
        continue
      }
      for (const item of accepted) {
        if (unfinishedRelation(item.relationText)) blockingIssues.add('unfinished_relation')
        if (!item.publishedAt) blockingIssues.add('missing_date')
        if (!item.sourceCitation) blockingIssues.add('missing_source')
      }
    }
    let status:CaseReportSubmissionStatus
    let feedback:string
    if (pendingGoals || !connectedAccepted) {
      status='evidence_insufficient'
      const emptyClaim=assembled.claims.find(claim => !claim.evidence.length)
      const rejected=assembled.claims.flatMap(claim => claim.evidence).find(item => !item.evidenceAccepted)
      feedback=emptyClaim
        ? 'No source document is connected to the claim. Return to the board and attach one with red thread.'
        : rejected
          ? `Exhibit ${rejected.displayNumber} is connected to the claim, but it was not accepted: ${rejected.verification.detail}`
          : 'The connected evidence was recognized, but another required level objective is still incomplete.'
    } else if (blockingIssues.size) {
      status='evidence_accepted_report_incomplete'
      feedback=blockingIssues.has('missing_date') || blockingIssues.has('missing_source')
        ? "The evidence is good enough, but the report itself won't hold up in court. Add the date, cite the source, and provide the link if you can. Then we can accept it."
        : 'The evidence is good enough, but the report still says “Proof that…”. Finish the evidentiary statement before submitting it.'
    } else {
      status='accepted'
      feedback='Case report accepted. The claim is supported by identified, dated source evidence.'
    }
    const submissionId=randomUUID()
    await client.query(`INSERT INTO osint.case_report_submissions (id,level_id,board_id,status,investigator_name,feedback)
      VALUES ($1,$2,$3,$4,$5,$6)`, [submissionId,level.id,level.board_id,status,investigatorName,feedback])
    for (const issue of blockingIssues) await client.query(
      'INSERT INTO osint.case_report_submission_issues (submission_id,issue_key) VALUES ($1,$2)', [submissionId,issue])
    await client.query('UPDATE osint.levels SET updated_at=NOW() WHERE id=$1', [level.id])
    await client.query('COMMIT')
    return { ...(await loadCaseReport(pool,level))!,status,feedback,issues:[...blockingIssues].sort() }
  } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
}