SPB Git

Repository Public

Scenarios.swift 14.0 KB 318 lines swift

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
Files main
Menu

Theme