Repository Public
1import Foundation2import Testing3import SheetCore4import SheetUI5import Statistics6import WhatIf7import AnalysisKit8@testable import WhatIfTools910@MainActor @Suite struct ScenarioTests {11 func cells(_ s: WorkbookSession, _ text: String) -> [CellAddress] { RangeReader.cells(text, workbook: s.workbook, sheet: 0) ?? [] }1213 @Test func capturePersistsInTheWorkbookAttachment() throws {14 let s = makeModel()15 let captured = try ScenarioStore.capture(name: " Base ", comment: "as planned", cells: cells(s, "B1:B2"), session: s)16 #expect(captured.name == "Base" && captured.cells.map(\.value) == [.number(50), .number(1000)])17 let data = try #require(s.workbook.attachments[ScenarioManager.attachmentKey])18 let decoded = try JSONDecoder().decode(ScenarioSet.self, from: data)19 #expect(decoded.scenarios == [captured])20 // a clone of the workbook (what a save / reopen carries) reads the same set21 #expect(try ScenarioManager.load(from: s.workbook.clone()).scenarios.first?.comment == "as planned")22 // same name replaces23 s.perform("Edit") { $0.set(sheet: 0, row: 0, col: 1, .value(.number(55))) }24 try ScenarioStore.capture(name: "base", comment: "", cells: cells(s, "B1:B2"), session: s)25 #expect(ScenarioStore.load(s).scenarios.count == 1 && ScenarioStore.load(s).scenarios[0].cells[0].value == .number(55))26 #expect(throws: ToolError.self) { try ScenarioStore.capture(name: " ", comment: "", cells: cells(s, "B1"), session: s) }27 #expect(throws: ToolError.self) { try ScenarioStore.capture(name: "x", comment: "", cells: [], session: s) }28 }2930 @Test func applyIsOneUndoStepAndRestoresFormulas() throws {31 let s = makeModel()32 try ScenarioStore.capture(name: "Base", comment: "", cells: cells(s, "B1:B2, B7"), session: s) // B7 holds a formula33 var high = try #require(ScenarioStore.load(s).scenario(named: "Base"))34 high.id = UUID(); high.name = "High"35 high.cells[0].value = .number(60); high.cells[1].value = .number(1500); high.cells[2].value = .number(9)36 var set = ScenarioStore.load(s); set.upsert(high); try ScenarioStore.save(set, session: s)37 let before = snapshot(s.workbook)38 #expect(!s.engine.canUndo)39 try ScenarioStore.apply(high, session: s)40 let sh = s.workbook.sheets[0]41 #expect(sh.value(0, 1) == .number(60) && sh.value(1, 1) == .number(1500) && sh.value(6, 1) == .number(9))42 #expect(sh.cell(6, 1)?.formula == nil)43 #expect(sh.value(4, 1) == .number(33_000)) // (60 − 30)·1500 − 12000, recalculated44 #expect(s.engine.undoLabel == "Apply Scenario")45 s.undo()46 #expect(snapshot(s.workbook) == before && !s.engine.canUndo)47 #expect(s.workbook.sheets[0].cell(6, 1)?.formula != nil)48 // applying the captured base puts the formula back as a formula49 try ScenarioStore.apply(high, session: s)50 try ScenarioStore.apply(try #require(ScenarioStore.load(s).scenario(named: "Base")), session: s)51 #expect(s.workbook.sheets[0].cell(6, 1)?.formula?.expr.formulaText() == "2+3" && s.workbook.sheets[0].value(4, 1) == .number(8000))52 }5354 @Test func applyFailsCleanlyWhenASheetIsGone() throws {55 let s = makeModel()56 let other = Scenario(name: "Ghost", cells: [ScenarioCell(sheetID: 999, cell: CellRef(0, 0), value: .number(1))])57 let before = LiveState(s)58 #expect(throws: ToolError.self) { try ScenarioStore.apply(other, session: s) }59 #expect(LiveState(s) == before)60 }6162 @Test func summaryComparesResultCellsAndIsWrittenAsOneStep() throws {63 let s = makeModel()64 let id = s.workbook.sheets[0].id65 var set = ScenarioSet()66 set.scenarios = [67 Scenario(name: "Low", cells: [ScenarioCell(sheetID: id, cell: CellRef(1, 1), value: .number(500))]),68 Scenario(name: "High", comment: "boom", cells: [ScenarioCell(sheetID: id, cell: CellRef(1, 1), value: .number(2000)),69 ScenarioCell(sheetID: id, cell: CellRef(0, 1), value: .number(60))]),70 ]71 try ScenarioStore.save(set, session: s)72 try ScenarioStore.setResultCells(cells(s, "B5"), session: s)73 set = ScenarioStore.load(s)74 #expect(set.resultCells == [ScenarioResultCell(sheetID: id, cell: CellRef(4, 1))])75 let before = LiveState(s)76 let result = cells(s, "B5")77 let summary = try ScenarioStore.summary(set, resultCells: result, model: WhatIfSupport.snapshot(s))78 #expect(summary.resultValues == [[.number(8000), .number(-2000), .number(48_000)]]) // current, Low, High79 let report = ScenarioStore.report(summary, set: set, resultCells: result, workbook: s.workbook)80 let table = try #require(report.table(titled: "Result cells"))81 #expect(table.rows[0] == [.text("Cell"), .text("Address"), .text("Current values"), .text("Low"), .text("High")])82 #expect(table.rows[1] == [.text("Profit"), .text("M!B5"), .number(8000), .number(-2000), .number(48_000)])83 let changing = try #require(report.table(titled: "Changing cells"))84 #expect(changing.rows[1] == [.text("Units"), .text("M!B2"), .number(1000), .number(500), .number(2000)])85 #expect(changing.rows[2] == [.text("Price"), .text("M!B1"), .number(50), .number(50), .number(60)]) // Low keeps the current price86 #expect(LiveState(s) == before)87 let o = try ScenarioStore.writeSummary(session: s)88 #expect(s.workbook.sheets.count == 3 && s.workbook.sheets[o.sheet].value(0, 0) == .text("Scenario Summary"))89 s.undo()90 #expect(LiveState(s).cells == before.cells && s.workbook.sheets.count == 2)91 }9293 @Test func editingValuesParsesLikeACellEntry() throws {94 let s = makeModel()95 #expect(try ScenarioStore.parse("1,250.5", session: s) == .number(1250.5))96 #expect(try ScenarioStore.parse("=B1*2", session: s) == .formula("B1*2"))97 #expect(try ScenarioStore.parse(" ", session: s) == .blank)98 #expect(try ScenarioStore.parse("hello", session: s) == .text("hello"))99 #expect(ScenarioStore.editText(.number(0.5), locale: Locale(identifier: "fr_CA")) == "0,5")100 #expect(ScenarioStore.editText(.formula("B1*2"), locale: s.locale) == "=B1*2")101 try ScenarioStore.capture(name: "A", comment: "", cells: cells(s, "B1"), session: s)102 var a = try #require(ScenarioStore.load(s).scenarios.first)103 a.cells[0].value = .number(77)104 try ScenarioStore.update(a, session: s)105 #expect(ScenarioStore.load(s).scenarios[0].cells[0].value == .number(77))106 try ScenarioStore.delete(a.id, session: s)107 #expect(ScenarioStore.load(s).scenarios.isEmpty)108 }109}110111@MainActor @Suite struct MonteCarloTests {112 func bindings() -> [MCBinding] {113 var price = MCBinding.preset(cell: "M!B1", name: "", value: 50); price.kind = .triangular; price.p1 = 45; price.p2 = 50; price.p3 = 58114 let units = MCBinding.preset(cell: "B2", name: "Volume", value: 1000)115 var cost = MCBinding.preset(cell: "B3", name: "", value: 30); cost.kind = .uniform; cost.p1 = 28; cost.p2 = 33116 return [price, units, cost]117 }118119 func input(_ s: WorkbookSession, seed: Int, trials: Int = 2000, _ more: (inout FormValues) -> Void = { _ in },120 isCancelled: @escaping @Sendable () -> Bool = { false }) throws -> ToolInput {121 let tool = MonteCarloTool.descriptor122 var v = FormValues(defaults: tool.fields)123 v.set("bindings", text: MCBinding.encode(bindings())); v.set("outputs", text: "B5"); v.set("trials", int: trials); v.set("seed", int: seed)124 more(&v)125 return try ToolInputResolver.resolve(tool, values: v, session: s, isCancelled: isCancelled)126 }127128 @Test func sameSeedReproducesExactlyAndTheWorkbookIsUntouched() throws {129 let s = makeModel()130 let before = LiveState(s)131 let (setup, a) = try MonteCarloTool.simulate(try input(s, seed: 7))132 let (_, b) = try MonteCarloTool.simulate(try input(s, seed: 7))133 let (_, c) = try MonteCarloTool.simulate(try input(s, seed: 8))134 #expect(a.outputSamples == b.outputSamples && a.inputSamples == b.inputSamples && a.outputs[0].mean == b.outputs[0].mean)135 #expect(a.outputSamples != c.outputSamples)136 #expect(a.trialsCompleted == 2000 && !a.cancelled)137 #expect(setup.inputs.map(\.name) == ["Price", "Volume", "Unit cost"])138 // every recorded output equals the model algebra on the sampled inputs139 for t in stride(from: 0, to: 2000, by: 97) {140 let (p, u, k) = (a.inputSamples[0][t], a.inputSamples[1][t], a.inputSamples[2][t])141 #expect(abs(a.outputSamples[0][t] - ((p - k) * u - 12_000)) < 1e-6)142 }143 #expect(LiveState(s) == before)144 }145146 @Test func resultCarriesStatisticsChartsAndThreshold() throws {147 let s = makeModel()148 let r = try MonteCarloTool.run(try input(s, seed: 3) { $0.set("threshold", text: "8000") })149 let (_, raw) = try MonteCarloTool.simulate(try input(s, seed: 3))150 #expect(r.metric("Mean") == raw.outputs[0].mean)151 let above = Double(raw.outputSamples[0].filter { $0 > 8000 }.count) / 2000152 #expect(abs(r.metric("P(Profit > 8,000)") - above) < 1e-12)153 #expect(r.charts.count == 3)154 #expect(r.table(titled: "Percentiles of Profit")?.rows.count == 10)155 #expect(r.table(titled: "Input assumptions")?.rows.count == 4)156 #expect(r.table(titled: "Sensitivity of Profit to the inputs")?.rows.count == 4)157 let o = ReportWriter.write(r, to: .newSheet, includeCharts: true, session: s)158 #expect(o.chartCount == 3 && s.engine.undoLabel == "Insert Report")159 }160161 @Test func cancellationReturnsPartialResultsAndLeavesTheWorkbookUntouched() throws {162 let s = makeModel()163 let before = LiveState(s)164 let polls = Counter()165 let r = try MonteCarloTool.run(try input(s, seed: 1, trials: 4000, isCancelled: { polls.next() > 6 }))166 #expect(r.warnings.first?.contains("cancelled") == true)167 let (_, full) = try MonteCarloTool.simulate(try input(s, seed: 1, trials: 4000))168 let polls2 = Counter()169 let (_, partial) = try MonteCarloTool.simulate(try input(s, seed: 1, trials: 4000, isCancelled: { polls2.next() > 6 }))170 #expect(partial.cancelled && partial.trialsCompleted > 0 && partial.trialsCompleted < 4000)171 #expect(Array(full.outputSamples[0].prefix(partial.trialsCompleted)) == Array(partial.outputSamples[0].prefix(partial.trialsCompleted)))172 #expect(throws: CancellationError.self) { try MonteCarloTool.simulate(try self.input(s, seed: 1, isCancelled: { true })) }173 #expect(LiveState(s) == before)174 }175176 @Test func bindingsRoundTripThroughJSON() throws {177 var list = bindings()178 var d = MCBinding(); d.cell = "B4"; d.kind = .discrete; d.valuesText = "10000; 12000; 15000"; d.probabilitiesText = "0.2; 0.5; 0.3"179 list.append(d)180 let decoded = MCBinding.decode(MCBinding.encode(list))181 #expect(decoded == list)182 #expect(MCBinding.decode("not json").isEmpty)183 let en = Locale(identifier: "en_US")184 #expect(try decoded[3].distribution(locale: en) == .discrete(values: [10000, 12000, 15000], probabilities: [0.2, 0.5, 0.3]))185 #expect(try decoded[0].distribution(locale: en) == .triangular(min: 45, mode: 50, max: 58))186 var b = decoded[1]187 b.convert(to: .uniform)188 #expect(b.kind == .uniform && b.p1 == 800 && b.p2 == 1200) // mean ± 2 sd189 b.convert(to: .pert)190 #expect(try b.distribution(locale: en) == .pert(min: 800, mode: 1000, max: 1200, lambda: 4))191 }192193 @Test func invalidSetupsAreRejectedWithAMessage() throws {194 let s = makeModel()195 var bad = MCBinding.preset(cell: "B1", name: "", value: 50); bad.kind = .uniform; bad.p1 = 9; bad.p2 = 1196 #expect(throws: ToolError.self) { try bad.distribution(locale: s.locale) }197 var log = MCBinding.preset(cell: "B1", name: "", value: 50); log.kind = .lognormal; log.p1 = -1198 #expect(throws: ToolError.self) { try log.distribution(locale: s.locale) }199 var disc = MCBinding(); disc.kind = .discrete; disc.valuesText = "1; 2"; disc.probabilitiesText = "1"200 #expect(throws: ToolError.self) { try disc.distribution(locale: s.locale) }201 func attempt(_ list: [MCBinding], _ more: (inout FormValues) -> Void = { _ in }) throws {202 _ = try MonteCarloTool.simulate(try input(s, seed: 1) { v in v.set("bindings", text: MCBinding.encode(list)); more(&v) })203 }204 #expect(throws: ToolError.self) { try attempt([]) } // no input205 #expect(throws: ToolError.self) { try attempt([bad]) } // invalid distribution206 #expect(throws: ToolError.self) { try attempt([.preset(cell: "B5", name: "", value: 1)]) } // formula as input207 #expect(throws: ToolError.self) { try attempt([.preset(cell: "B1", name: "", value: 1), .preset(cell: "M!B1", name: "", value: 1)]) } // bound twice208 #expect(throws: ToolError.self) { try attempt(self.bindings()) { $0.set("threshold", text: "abc") } }209 #expect(throws: ToolError.self) { try attempt(self.bindings()) { $0.set("correlation", choice: "matrix"); $0.set("matrix", text: "1 0.5; 0.5 1") } } // 2×2 for 3 inputs210 #expect(!s.engine.canUndo)211 }212213 @Test func rankCorrelationIsApplied() throws {214 let s = makeModel()215 let (setup, r) = try MonteCarloTool.simulate(try input(s, seed: 5, trials: 5000) { $0.set("correlation", choice: "common"); $0.set("rho", number: 0.6) })216 #expect(setup.options.correlation?.matrix == [[1, 0.6, 0.6], [0.6, 1, 0.6], [0.6, 0.6, 1]])217 let achieved = try #require(r.achievedInputRankCorrelation)218 #expect(abs(achieved[0][1] - 0.6) < 0.05 && abs(achieved[1][2] - 0.6) < 0.05)219 let m = try MCCorrelation.parse("1 0.3 0; 0.3 1 -0.2; 0 -0.2 1", count: 3, locale: Locale(identifier: "en_US"))220 #expect(m[1] == [0.3, 1, -0.2])221 #expect(try MCCorrelation.parse("1 0,3; 0,3 1", count: 2, locale: Locale(identifier: "fr_CA"))[0][1] == 0.3)222 #expect(throws: ToolError.self) { try MCCorrelation.parse("1 0.3; 0.4 1", count: 2, locale: Locale(identifier: "en_US")) }223 #expect(throws: ToolError.self) { try MCCorrelation.parse("1 2; 2 1", count: 2, locale: Locale(identifier: "en_US")) }224 }225226 @Test func selectionPresetsNormalDistributions() {227 let s = makeModel()228 s.select(RangeRef(a1: "B1:B5")!)229 let list = MCBinding.fromSelection(s, existing: [MCBinding.preset(cell: "B2", name: "", value: 1)])230 #expect(list.map(\.cell) == ["M!B1", "M!B3", "M!B4"]) // B2 already bound, B5 is a formula231 #expect(list[0].kind == .normal && list[0].p1 == 50 && list[0].p2 == 5 && list[0].name == "Price")232 }233}234235@MainActor @Suite struct WhatIfCatalogTests {236 @Test func toolsAreRegisteredWithDemosAndModelIsolation() {237 let tools = WhatIfToolList.all238 #expect(tools.map(\.id) == ["goalseek", "breakeven", "datatable1", "datatable2", "scenarios", "tornado", "montecarlo"])239 for t in tools {240 #expect(t.demo != nil && !t.info.isEmpty && !t.title.isEmpty)241 #expect(t.needsModel || t.customPanel != nil)242 }243 }244245 @Test func catalogHasCanadianFrench() throws {246 let fr = try #require(AnalysisLocalization.localized(whatIfBundle, "fr-CA"))247 #expect(fr.localizedString(forKey: "Goal Seek", value: nil, table: nil) == "Valeur cible")248 #expect(fr.localizedString(forKey: "Monte Carlo Simulation", value: nil, table: nil) == "Simulation de Monte-Carlo")249 }250251 @Test func helpersNameAndFormatCells() {252 let s = makeModel()253 let wb = s.workbook254 let b5 = CellAddress(sheet: 0, cell: CellRef(a1: "B5")!)255 #expect(WhatIfSupport.name(b5, in: wb) == "Profit" && WhatIfSupport.label(b5, in: wb) == "M!B5")256 #expect(WhatIfSupport.nameWithAddress(CellAddress(sheet: 0, cell: CellRef(a1: "D9")!), in: wb) == "M!D9")257 #expect(WhatIfSupport.formulaText(b5, in: wb) == "=B1*B2-B3*B2-B4")258 s.perform("Format") { e in259 e.setStyle(sheet: 0, range: RangeRef(a1: "B1")!) { $0.numberFormat = "#,##0.00" }260 e.setStyle(sheet: 0, range: RangeRef(a1: "B2")!) { $0.numberFormat = "0.0%" }261 }262 #expect(WhatIfSupport.format(CellAddress(sheet: 0, cell: CellRef(0, 1)), in: wb) == .fixed(2))263 #expect(WhatIfSupport.format(CellAddress(sheet: 0, cell: CellRef(1, 1)), in: wb) == .percent(1))264 #expect(WhatIfSupport.format(b5, in: wb) == .auto)265 #expect(WhatIfSupport.binLabels(edges: [0, 0.2, 0.4], locale: Locale(identifier: "en_US")) == ["0.10", "0.30"])266 }267}268