Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetIntelligence/Tests/SheetIntelligenceTests/IntelligenceTests.swift10// Module : SheetIntelligenceTests11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import SheetIntelligence9192@Suite struct IntelligenceTests {93 func collect(_ stream: AsyncThrowingStream<String, Error>) async throws -> [String] {94 var out: [String] = []95 for try await s in stream { out.append(s) }96 return out97 }9899 func failure<T>(_ body: () async throws -> T) async -> IntelligenceError? {100 do { _ = try await body(); return nil } catch { return error as? IntelligenceError }101 }102103 // MARK: availability104105 @Test func availabilityMapping() {106 #expect(IntelligenceAvailability.mapping(systemAvailable: true, reason: nil, modelDescription: "M") == .available(modelDescription: "M"))107 #expect(IntelligenceAvailability.mapping(systemAvailable: false, reason: .deviceNotEligible, modelDescription: "") == .unavailable(reason: .deviceNotEligible))108 #expect(IntelligenceAvailability.mapping(systemAvailable: false, reason: .appleIntelligenceNotEnabled, modelDescription: "") == .unavailable(reason: .appleIntelligenceNotEnabled))109 #expect(IntelligenceAvailability.mapping(systemAvailable: false, reason: .modelNotReady, modelDescription: "") == .unavailable(reason: .modelNotReady))110 #expect(IntelligenceAvailability.mapping(systemAvailable: false, reason: .unknown("future"), modelDescription: "") == .unavailable(reason: .other("future")))111 #expect(IntelligenceAvailability.mapping(systemAvailable: false, reason: nil, modelDescription: "").reason == .other("no reason reported"))112 }113114 @Test func everyReasonHasAnExplanation() {115 let reasons: [UnavailableReason] = [.osTooOld, .deviceNotEligible, .appleIntelligenceNotEnabled, .modelNotReady, .frameworkMissing, .other("x")]116 #expect(Set(reasons.map(\.code)).count == reasons.count)117 for r in reasons { #expect(r.explanation.count > 20) }118 #expect(UnavailableReason.deviceNotEligible.guidance == nil)119 #expect(UnavailableReason.appleIntelligenceNotEnabled.guidance?.contains("System Settings") == true)120 #expect(UnavailableReason.other("x").explanation.contains("x"))121 }122123 @Test func unavailableBackendFailsEveryTaskWithTheReason() async {124 let backend = ScriptedBackend(responses: ["never used"], availability: .unavailable(reason: .appleIntelligenceNotEnabled))125 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))126 #expect(await ai.availability() == .unavailable(reason: .appleIntelligenceNotEnabled))127 let ctx = Fixtures.context(), profile = Fixtures.profile()128 let expected = IntelligenceError.unavailable(.appleIntelligenceNotEnabled)129 #expect(await failure { try await ai.suggestFormulas(request: "sum", context: ctx) } == expected)130 #expect(await failure { try await ai.describeData(profile) } == expected)131 #expect(await failure { try await ai.suggestCharts(profile) } == expected)132 #expect(await failure { try await ai.transformationPlan(goal: "g", profile: profile) } == expected)133 #expect(await failure { try await ai.explainStatistics([["a"]], kind: "k") } == expected)134 #expect(await failure { try await ai.documentSheet(ctx) } == expected)135 #expect(await failure { try await ai.pythonHelp(request: "r", context: ctx, currentCode: nil) } == expected)136 #expect(await failure { try await ai.reviewForErrors(findings: PostCheckTests.findings, context: ctx) } == expected)137 #expect(backend.requests.isEmpty)138 }139140 @Test func scriptedBackendIsMarked() {141 let ai = Intelligence(backend: ScriptedBackend(responses: ["x"]))142 #expect(ai.debugMarker == "SCRIPTED PREVIEW")143 #expect(Intelligence().debugMarker == nil)144 #expect(FoundationModelsBackend().debugMarker == nil)145 }146147 // MARK: language148149 @Test func languageFollowsTheLocaleWhenSupported() async throws {150 let backend = ScriptedBackend(responses: ["ok"], languages: ["en", "fr"])151 let fr = Intelligence(backend: backend, locale: Locale(identifier: "fr_CA"))152 #expect(await fr.answerLanguage() == .appLanguage(code: "fr"))153 _ = try await collect(try await fr.explainStatistics([["a", "1"]], kind: "t-test"))154 #expect(backend.requests[0].instructions.contains("Write your answer in French (Canada)"))155 let de = Intelligence(backend: ScriptedBackend(responses: ["ok"], languages: ["en"]), locale: Locale(identifier: "de_DE"))156 #expect(await de.answerLanguage() == .englishFallback(requested: "de"))157 #expect(await Intelligence(backend: backend, locale: Locale(identifier: "en_CA")).answerLanguage() == .appLanguage(code: "en"))158 }159160 // MARK: decoding161162 /// Every streamed prose task asks for the same deterministic decoding as the plain and structured requests.163 @Test func streamedTasksAreDeterministicToo() async throws {164 let backend = ScriptedBackend(responses: ["a", "b", "c", "d"])165 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))166 let (wb, engine) = Fixtures.sales()167 engine.set(sheet: 0, "H2", .formula(try FormulaParser.parse("=SUM(E2:E21)")))168 _ = engine.recalculate()169 let formula = try #require(ContextBuilder.formulaContext(engine: engine, sheet: 0, cell: CellRef(a1: "H2")!))170 let profile = ContextBuilder.tableProfile(workbook: wb, sheet: 0, range: RangeRef(a1: "A1:F21")!)171 let sheet = ContextBuilder.sheetContext(workbook: wb, sheet: 0, selection: RangeRef(a1: "H2")!, active: CellRef(a1: "H2")!)172 _ = try await collect(try await ai.explainFormula(formula))173 _ = try await collect(try await ai.explainStatistics([["a", "1"]], kind: "t-test"))174 _ = try await ai.describeData(profile)175 _ = try await collect(try await ai.documentSheet(sheet, audit: []))176 #expect(backend.requests.count == 4)177 #expect(backend.requests.allSatisfy { $0.kind == .stream && $0.options.temperature == 0 })178 #expect(backend.requests.allSatisfy { $0.options.maximumResponseTokens == Intelligence.maxProseTokens })179 }180181 /// The real-model tests are opt-in: only the exact value `1` enables them.182 @Test func realModelTestsRunOnlyOnExplicitOptIn() {183 #expect(RealModelTests.evaluationRequested == (ProcessInfo.processInfo.environment["ORIX_AI_EVAL"] == "1"))184 }185186 // MARK: formulas187188 @Test func suggestionsAreValidatedAndValidOnesComeFirst() async throws {189 let answer = """190 Here you go:191 FORMULA: =SUMX(E2:E21)192 WHY: bogus function193 2. FORMULA: `=SUM(E2:E21)`194 WHY: Adds all revenue.195 - =SUM(E2:E21)196 duplicate197 FORMULA: =SUM(H1:H5)198 WHY: self reference199 """200 let backend = ScriptedBackend(responses: [answer])201 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))202 let out = try await ai.suggestFormulas(request: "total revenue", context: Fixtures.context())203 #expect(out.count == 2) // at most 3 are read, the duplicate is dropped204 #expect(out.map(\.id) == [0, 1])205 #expect(out[0].usableFormula == "=SUM(E2:E21)")206 #expect(out[0].validation.value == .number((0..<20).reduce(0.0) { $0 + Fixtures.revenue($1) }))207 #expect(out[0].explanation == "Adds all revenue.")208 #expect(out[1].usableFormula == nil && out[1].validation.failedStage == .functions)209 #expect(backend.requests.count == 1) // no repair round when something is valid210 let prompt = backend.requests[0].prompt211 #expect(prompt.contains("Request: total revenue"))212 #expect(prompt.contains("Available functions (only these exist): SUM"))213 #expect(prompt.contains("data in E2:E21"))214 #expect(backend.requests[0].kind == .respond && backend.requests[0].options.temperature == 0)215 #expect(backend.requests[0].options.maximumResponseTokens == 400)216 }217218 @Test func formulaLineProtocolIsParsedStrictly() {219 let parsed = Intelligence.parseSuggestions("Sure!\n\nFORMULA: =COUNTIF(C2:C21,\">20\")\nWHY: counts rows\n\n* `=SUMIF(A2:A21,\"North\",E2:E21)`\nIt sums North.\nSome other remark.\nformula: =A1+1\nExplanation: adds one\n```\nnot a formula\n")220 #expect(parsed.map(\.formula) == ["=COUNTIF(C2:C21,\">20\")", "=SUMIF(A2:A21,\"North\",E2:E21)", "=A1+1"])221 #expect(parsed.map(\.explanation) == ["counts rows", "It sums North.", "adds one"])222 #expect(Intelligence.parseSuggestions("I cannot do that.").isEmpty)223 #expect(Intelligence.parseSuggestions("").isEmpty)224 }225226 @Test func oneRepairRoundWhenNothingIsValid() async throws {227 let backend = ScriptedBackend(responses: ["FORMULA: =AVERAGEIF(C2:C21, <>0)\nWHY: a", "FORMULA: =AVERAGEIF(C2:C21,\"<>0\")\nWHY: fixed"])228 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))229 let out = try await ai.suggestFormulas(request: "average units ignoring zeros", context: Fixtures.context())230 #expect(out.count == 2)231 #expect(out[0].isRepair && out[0].validation.isValid)232 #expect(!out[1].isRepair && !out[1].validation.isValid)233 #expect(backend.requests.count == 2)234 #expect(backend.requests[1].prompt.contains("Rejected formulas:") && backend.requests[1].prompt.contains("It is not a valid formula"))235 }236237 @Test func answersWithoutAFormulaAreInvalidResponses() async {238 let ai = Intelligence(backend: ScriptedBackend(responses: ["I think you should use SUM."]), locale: Locale(identifier: "en_US"))239 #expect(await failure { try await ai.suggestFormulas(request: "x", context: Fixtures.context()) } == .invalidResponse("no formula in the answer"))240 let ai2 = Intelligence(backend: ScriptedBackend(responses: ["{\"charts\": \"none\"}"]), locale: Locale(identifier: "en_US"))241 #expect(await failure { try await ai2.suggestCharts(Fixtures.profile()) } == .invalidResponse("wrong type at charts"))242 let ai3 = Intelligence(backend: ScriptedBackend(responses: ["no json"]), locale: Locale(identifier: "en_US"))243 #expect(await failure { try await ai3.transformationPlan(goal: "g", profile: Fixtures.profile()) } == .invalidResponse("no JSON found"))244 }245246 // MARK: errors247248 @Test func contextOverflowShrinksAndRetriesOnce() async throws {249 let backend = ScriptedBackend(results: [.failure(.contextWindowExceeded), .success("It adds the units.")])250 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))251 let big = (0..<60).map { ["row \($0)", String(repeating: "9", count: 20), String(repeating: "x", count: 28), String(repeating: "y", count: 28)] }252 let out = try await collect(try await ai.explainStatistics(big, kind: "Regression"))253 #expect(out.last == "It adds the units.")254 #expect(backend.requests.count == 2)255 #expect(backend.requests[0].prompt.count <= ContextLimits().maxCharacters)256 #expect(backend.requests[1].prompt.count <= ContextLimits().reducedCharacters)257 #expect(backend.requests[1].prompt.count < backend.requests[0].prompt.count)258259 let twice = ScriptedBackend(results: [.failure(.contextWindowExceeded), .failure(.contextWindowExceeded), .success("unused")])260 let ai2 = Intelligence(backend: twice, locale: Locale(identifier: "en_US"))261 #expect(await failure { try await self.collect(try await ai2.explainStatistics(big, kind: "k")) } == .contextWindowExceeded)262 #expect(twice.requests.count == 2)263264 let structured = ScriptedBackend(results: [.failure(.contextWindowExceeded), .success("FORMULA: =SUM(C2:C21)")])265 let ai3 = Intelligence(backend: structured, locale: Locale(identifier: "en_US"))266 #expect(try await ai3.suggestFormulas(request: "sum", context: Fixtures.context()).first?.validation.isValid == true)267 #expect(structured.requests.count == 2 && structured.requests[1].prompt.count <= structured.requests[0].prompt.count)268 }269270 @Test func guardrailsRefusalsAndLanguageErrorsSurfaceAsSuch() async {271 for error in [IntelligenceError.guardrailViolation, .refusal("not allowed"), .unsupportedLanguage, .rateLimited] {272 let ai = Intelligence(backend: ScriptedBackend(results: [.failure(error)]), locale: Locale(identifier: "en_US"))273 #expect(await failure { try await self.collect(try await ai.describeDataStream(Fixtures.profile())) } == error)274 #expect(await failure { try await ai.suggestCharts(Fixtures.profile()) } == error)275 #expect(error.errorDescription?.isEmpty == false)276 }277 #expect(Intelligence.normalise(CancellationError()) == .cancelled)278 #expect(Intelligence.normalise(NSError(domain: "x", code: 1)) != .cancelled)279 }280281 // MARK: structured tasks282283 @Test func reviewOnlyKeepsRealFindings() async throws {284 let json = "{\"overview\": \"One error.\", \"items\": [{\"findingId\": \"ghost:Sales!A1\", \"priority\": 1, \"explanation\": \"x\", \"suggestedFix\": \"y\"}, {\"findingId\": \"errorValue:Sales!H4\", \"priority\": 2, \"explanation\": \"Division by zero.\", \"suggestedFix\": \"Guard with IFERROR.\"}]}"285 let backend = ScriptedBackend(responses: [json])286 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))287 let review = try await ai.reviewForErrors(findings: PostCheckTests.findings, context: Fixtures.context())288 #expect(review.items.map(\.findingID) == ["errorValue:Sales!H4"])289 #expect(review.rejectedIDs == ["ghost:Sales!A1"])290 #expect(backend.requests[0].prompt.contains("id: errorValue:Sales!H4"))291 #expect(backend.requests[0].options.schema != nil)292 // nothing to review: the model is not asked293 let none = try await ai.reviewForErrors(findings: [], context: Fixtures.context())294 #expect(none.items.isEmpty && backend.requests.count == 1)295 }296297 @Test func describeFlagsNumbersThatAreNotInTheProfile() async throws {298 let backend = ScriptedBackend(responses: ["The table has 20 rows. Revenue is up 12% over 777 days."])299 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))300 let d = try await ai.describeData(Fixtures.profile())301 #expect(d.unverifiedNumbers == ["12%", "777"])302 #expect(d.verifiedNumberCount == 1)303 // the prompt is the profile, not the rows: the 20 revenue values are not in it304 let prompt = backend.requests[0].prompt305 #expect(prompt.contains("\"Revenue\" (column E, number)"))306 #expect(!prompt.contains(Value.number(Fixtures.revenue(7)).description + " |"))307 #expect(prompt.count <= ContextLimits().maxCharacters)308 }309310 @Test func chartsAndPlansAreFiltered() async throws {311 let charts = "{\"charts\": [{\"kind\": \"column\", \"title\": \"Revenue by region\", \"x\": \"Region\", \"y\": [\"Revenue\"], \"rationale\": \"r\"}, {\"kind\": \"line\", \"title\": \"t\", \"x\": \"Month\", \"y\": [\"Revenue\"], \"rationale\": \"r\"}]}"312 let plan = "{\"steps\": [{\"operation\": \"groupBy\", \"columns\": [\"Region\"], \"newColumns\": [\"Total\"], \"detail\": \"sum of Revenue\"}, {\"operation\": \"sort\", \"columns\": [\"Totl\"], \"detail\": \"descending\"}, {\"operation\": \"explode\", \"columns\": [], \"detail\": \"\"}]}"313 let ai = Intelligence(backend: ScriptedBackend(responses: [charts, plan]), locale: Locale(identifier: "en_US"))314 let c = try await ai.suggestCharts(Fixtures.profile())315 #expect(c.count == 1 && c[0].kind == .column && c[0].x == "Region")316 let steps = try await ai.transformationPlan(goal: "revenue per region", profile: Fixtures.profile())317 #expect(steps.map(\.operation) == [.groupBy, .sort])318 #expect(steps[0].isValid && !steps[1].isValid)319 }320321 @Test func pythonIsReturnedAsTextWithChecks() async throws {322 let answer = "Here is the script.\n```python\nimport orix\ns = orix.active_sheet()\ndf = s.range('A1:F21').to_dataframe()\norix.plot(df)\n```\nLoads the table.\nThen plots it."323 let backend = ScriptedBackend(responses: [answer])324 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))325 let p = try await ai.pythonHelp(request: "load the table", context: Fixtures.context(), currentCode: "print('hi')")326 #expect(p.code.hasPrefix("import orix") && !p.code.contains("```") && p.code.hasSuffix("orix.plot(df)"))327 #expect(p.unknownAPICalls == ["plot"])328 #expect(p.explanation == "Here is the script. Loads the table. Then plots it.")329 if PythonChecker.interpreter() != nil { #expect(p.syntax == .passed(interpreter: PythonChecker.interpreter()!)) } else {330 #expect(p.syntax == .skipped(reason: "No python3 was found on this Mac."))331 }332 #expect(backend.requests[0].prompt.contains("orix API (complete)") && backend.requests[0].prompt.contains("print('hi')"))333 #expect(Intelligence.parsePython("x = 1\n").code == "x = 1")334 let empty = Intelligence(backend: ScriptedBackend(responses: ["```\n```"]), locale: Locale(identifier: "en_US"))335 #expect(await failure { try await empty.pythonHelp(request: "r", context: Fixtures.context(), currentCode: nil) } == .invalidResponse("no code in the answer"))336 }337338 @Test func documentationKeepsDeterministicSectionsAroundTheModelParagraph() async throws {339 let backend = ScriptedBackend(responses: ["This sheet likely tracks sales."])340 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))341 let snapshots = try await collect(try await ai.documentSheet(Fixtures.context(), audit: PostCheckTests.findings))342 let doc = try #require(snapshots.last)343 #expect(snapshots.count == 5)344 #expect(doc.hasPrefix("# Sales\n"))345 #expect(doc.contains(SheetDocumenter.purposeHeading + "\n\nThis sheet likely tracks sales.\n"))346 #expect(doc.contains("`=C2*D2`") && doc.contains("The auditor reported 2 findings (1 errors, 1 warnings, 0 info)."))347 #expect(backend.requests[0].prompt.contains("Formulas:\n- E2:E21"))348 let empty = Intelligence(backend: ScriptedBackend(responses: [" "]), locale: Locale(identifier: "en_US"))349 #expect(await failure { try await self.collect(try await empty.documentSheet(Fixtures.context())) } == .invalidResponse("empty answer"))350 }351352 @Test func explainFormulaPromptIsGroundedInEngineFacts() async throws {353 let (_, engine) = Fixtures.sales()354 engine.set(sheet: 0, "H2", .formula(try FormulaParser.parse("=SUMIF(A2:A21,\"North\",E2:E21)")))355 _ = engine.recalculate()356 let ctx = try #require(ContextBuilder.formulaContext(engine: engine, sheet: 0, cell: CellRef(a1: "H2")!))357 let backend = ScriptedBackend(responses: ["It sums revenue for North."])358 let ai = Intelligence(backend: backend, locale: Locale(identifier: "en_US"))359 let out = try await collect(try await ai.explainFormula(ctx))360 #expect(out == ["It", "It sums", "It sums revenue", "It sums revenue for", "It sums revenue for North."])361 let prompt = backend.requests[0].prompt362 #expect(prompt.contains("=SUMIF(A2:A21,\"North\",E2:E21)"))363 #expect(prompt.contains("Its computed result is: \(ctx.result)"))364 #expect(prompt.contains("SUMIF("))365 #expect(backend.requests[0].instructions.contains("You never calculate anything yourself"))366 }367}368