Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Sources/WhatIf/Scenarios.swift10// Module : WhatIf11// 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 SheetCore89import Statistics9091/// Content of an input cell stored in a scenario.92public enum ScenarioValue: Hashable, Sendable, Codable {93 case blank94 case number(Double)95 case text(String)96 case bool(Bool)97 /// Formula text without the leading "=" (canonical syntax). Used by restore points so that98 /// applying a scenario over a formula can be reverted faithfully.99 case formula(String)100101 public init(_ value: Value) {102 switch value {103 case .number(let n): self = .number(n)104 case .text(let s): self = .text(s)105 case .bool(let b): self = .bool(b)106 default: self = .blank107 }108 }109110 init(_ input: CellInput) {111 switch input {112 case .blank: self = .blank113 case .value(let v): self = ScenarioValue(v)114 case .formula(let e): self = .formula(e.formulaText())115 }116 }117118 func cellInput() throws -> CellInput {119 switch self {120 case .blank: return .blank121 case .number(let n): return .value(.number(n))122 case .text(let s): return .value(.text(s))123 case .bool(let b): return .value(.bool(b))124 case .formula(let t):125 guard let e = try? FormulaParser.parse(t) else { throw WhatIfError.invalidFormula(t) }126 return .formula(e)127 }128 }129130 /// Value shown in summary tables (formulas appear as "=text").131 public var displayValue: Value {132 switch self {133 case .blank: .blank134 case .number(let n): .number(n)135 case .text(let s): .text(s)136 case .bool(let b): .bool(b)137 case .formula(let t): .text("=" + t)138 }139 }140}141142/// One input cell of a scenario. Sheets are identified by their stable `Sheet.id`, so scenarios143/// survive renaming and reordering of sheets.144public struct ScenarioCell: Hashable, Sendable, Codable {145 public var sheetID: Int146 public var cell: CellRef147 public var value: ScenarioValue148 public init(sheetID: Int, cell: CellRef, value: ScenarioValue) { self.sheetID = sheetID; self.cell = cell; self.value = value }149}150151/// A named set of input-cell values.152public struct Scenario: Hashable, Sendable, Codable, Identifiable {153 public var id: UUID154 public var name: String155 public var comment: String156 public var cells: [ScenarioCell]157 public init(id: UUID = UUID(), name: String, comment: String = "", cells: [ScenarioCell]) {158 self.id = id; self.name = name; self.comment = comment; self.cells = cells159 }160161 /// Builds a scenario from (cell, value) pairs addressed by sheet index.162 public init(name: String, comment: String = "", workbook: Workbook, values: [(ModelCell, Value)]) throws {163 var cells = [ScenarioCell]()164 for (c, v) in values {165 guard c.isValid(in: workbook) else { throw WhatIfError.invalidCell(c.label(in: workbook)) }166 cells.append(ScenarioCell(sheetID: workbook.sheets[c.sheet].id, cell: c.cell, value: ScenarioValue(v)))167 }168 self.init(name: name, comment: comment, cells: cells)169 }170}171172/// A result cell watched by the scenario summary.173public struct ScenarioResultCell: Hashable, Sendable, Codable {174 public var sheetID: Int175 public var cell: CellRef176 public init(sheetID: Int, cell: CellRef) { self.sheetID = sheetID; self.cell = cell }177}178179/// Everything the Scenario Manager persists in the workbook.180public struct ScenarioSet: Hashable, Sendable, Codable {181 public var version = 1182 public var scenarios: [Scenario] = []183 public var resultCells: [ScenarioResultCell] = []184 public init() {}185186 public func scenario(named name: String) -> Scenario? {187 scenarios.first { $0.name.compare(name, options: .caseInsensitive) == .orderedSame }188 }189 /// Adds the scenario, replacing an existing one with the same name (case-insensitive).190 public mutating func upsert(_ s: Scenario) {191 if let i = scenarios.firstIndex(where: { $0.name.compare(s.name, options: .caseInsensitive) == .orderedSame }) { scenarios[i] = s }192 else { scenarios.append(s) }193 }194 public mutating func remove(named name: String) {195 scenarios.removeAll { $0.name.compare(name, options: .caseInsensitive) == .orderedSame }196 }197}198199/// Comparison of scenarios: the values of the changing cells and of the result cells under each.200public struct ScenarioSummary: Sendable, Hashable, TableReport {201 /// "Current Values" followed by the scenario names.202 public var columns: [String]203 public var changingCellLabels: [String]204 /// changingValues[i][j]: content of changing cell i under column j (the current content when the scenario does not set it).205 public var changingValues: [[Value]]206 public var resultCellLabels: [String]207 /// resultValues[i][j]: computed value of result cell i under column j.208 public var resultValues: [[Value]]209210 public func table() -> [[Value]] {211 var rows: [[Value]] = [[.text("Scenario Summary")] + columns.map { .text($0) }]212 rows.append([.text("Changing Cells")])213 for (i, l) in changingCellLabels.enumerated() { rows.append([.text(l)] + changingValues[i]) }214 rows.append([.text("Result Cells")])215 for (i, l) in resultCellLabels.enumerated() { rows.append([.text(l)] + resultValues[i]) }216 return Report.rectangular(rows)217 }218}219220/// Scenario Manager: capture, store, apply and compare named sets of input values.221public enum ScenarioManager {222 /// Key of the JSON document in `Workbook.attachments`.223 public static let attachmentKey = "scenarios.json"224225 /// Reads the scenarios stored in the workbook (an empty set when there are none).226 public static func load(from workbook: Workbook) throws -> ScenarioSet {227 guard let data = workbook.attachments[attachmentKey] else { return ScenarioSet() }228 return try JSONDecoder().decode(ScenarioSet.self, from: data)229 }230231 /// Stores the scenarios in the workbook (deterministic JSON: sorted keys).232 public static func save(_ set: ScenarioSet, to workbook: Workbook) throws {233 let enc = JSONEncoder()234 enc.outputFormatting = [.sortedKeys]235 workbook.attachments[attachmentKey] = try enc.encode(set)236 }237238 private static func location(_ sheetID: Int, _ cell: CellRef, in wb: Workbook) throws -> ModelCell {239 guard let si = wb.sheetIndex(id: sheetID), cell.isValid else { throw WhatIfError.invalidCell("sheet id \(sheetID)!\(cell.a1)") }240 return ModelCell(sheet: si, cell: cell)241 }242243 private static func content(_ c: ModelCell, in wb: Workbook) -> CellInput {244 guard let cell = wb.sheets[c.sheet].cell(c.cell.row, c.cell.col) else { return .blank }245 if let f = cell.formula { return .formula(f.expr) }246 if cell.isSpill || cell.value.isBlank { return .blank }247 return .value(cell.value)248 }249250 /// Captures the current content of the given cells as a scenario.251 public static func capture(name: String, comment: String = "", cells: [ModelCell], engine: CalcEngine) throws -> Scenario {252 let wb = engine.workbook253 var out = [ScenarioCell]()254 for c in cells {255 guard c.isValid(in: wb) else { throw WhatIfError.invalidCell(c.label(in: wb)) }256 out.append(ScenarioCell(sheetID: wb.sheets[c.sheet].id, cell: c.cell, value: ScenarioValue(content(c, in: wb))))257 }258 return Scenario(name: name, comment: comment, cells: out)259 }260261 /// Writes the scenario's values into the live workbook as ONE undoable edit ("Apply Scenario") and262 /// recalculates. Returns a restore point: a scenario holding the previous content of the same cells263 /// (formulas included); applying it — or a single undo — reverts the change.264 /// Nothing is modified when a cell of the scenario no longer exists or a stored formula is invalid.265 @discardableResult266 public static func apply(_ scenario: Scenario, engine: CalcEngine) throws -> Scenario {267 let wb = engine.workbook268 var edits = [(ModelCell, CellInput)]()269 var previous = [ScenarioCell]()270 for sc in scenario.cells {271 let loc = try location(sc.sheetID, sc.cell, in: wb)272 edits.append((loc, try sc.value.cellInput()))273 previous.append(ScenarioCell(sheetID: sc.sheetID, cell: sc.cell, value: ScenarioValue(content(loc, in: wb))))274 }275 engine.transaction("Apply Scenario") {276 for (c, input) in edits { engine.set(sheet: c.sheet, row: c.cell.row, col: c.cell.col, input) }277 }278 engine.recalculate()279 return Scenario(name: "Before " + scenario.name, comment: "Restore point", cells: previous)280 }281282 /// Evaluates every scenario without changing the live workbook and tabulates the changing cells283 /// (union over the scenarios, in order of first appearance) and the result cells.284 public static func summary(scenarios: [Scenario], resultCells: [ModelCell], engine: CalcEngine,285 isolation: WhatIfIsolation = .clone) throws -> ScenarioSummary {286 let wb = engine.workbook287 for c in resultCells { guard c.isValid(in: wb) else { throw WhatIfError.invalidCell(c.label(in: wb)) } }288 var changing = [ModelCell]()289 var perScenario = [[(ModelCell, ScenarioValue)]]()290 for s in scenarios {291 var list = [(ModelCell, ScenarioValue)]()292 for sc in s.cells {293 let loc = try location(sc.sheetID, sc.cell, in: wb)294 _ = try sc.value.cellInput()295 if !changing.contains(loc) { changing.append(loc) }296 list.append((loc, sc.value))297 }298 perScenario.append(list)299 }300 let current = changing.map { ScenarioValue(content($0, in: wb)).displayValue }301 var changingValues = current.map { [$0] }302 var resultValues = [[Value]]()303 try ModelSandbox.run(on: engine, isolation: isolation) { box in304 resultValues = try resultCells.map { [try box.value($0)] }305 for list in perScenario {306 box.resetAll()307 for (c, v) in list { try box.set(c, try v.cellInput()) }308 for (i, c) in changing.enumerated() {309 changingValues[i].append(list.last { $0.0 == c }?.1.displayValue ?? current[i])310 }311 for (i, c) in resultCells.enumerated() { resultValues[i].append(try box.value(c)) }312 }313 }314 return ScenarioSummary(columns: ["Current Values"] + scenarios.map(\.name), changingCellLabels: changing.map { $0.label(in: wb) },315 changingValues: changingValues, resultCellLabels: resultCells.map { $0.label(in: wb) }, resultValues: resultValues)316 }317}318