import Foundation
import TeaQLCore
import TeaQLSQLite
import GeneratedTeaQL

enum VerificationError: Error { case invalidPhase, missingValue(String) }
func required<T>(_ value: T?) throws -> T {
    guard let value else { throw VerificationError.missingValue("Required loaded value") }; return value
}
func number(_ row: TeaQLRecord, _ field: String) throws -> Double {
    NSDecimalNumber(decimal: try required(row[field]?.decimalValue)).doubleValue
}
func text(_ row: TeaQLRecord, _ field: String) throws -> String { try required(row[field]?.stringValue) }
func report(_ name: String, _ value: Any) throws {
    let data = try JSONSerialization.data(withJSONObject: ["case": name, "value": value], options: [.sortedKeys])
    print("SCHOOL_CASE " + String(decoding: data, as: UTF8.self))
}
func names(_ rows: SmartList<School>) throws -> [String] { try rows.map { try required(E.school($0).name().eval()) } }
func buckets(_ rows: SmartList<School>, _ name: String, _ field: String) throws -> [String: Double] {
    try Dictionary(uniqueKeysWithValues: required(rows.facet(name)).map { (try text($0, field), try number($0, "count")) })
}
func load(_ context: UserContext, _ name: String) async throws -> School {
    let rows = try await Q.schools().withNameIs(name).limit(2)
        .comment("Load complete School state for mutation").purpose("Preserve optimistic version").executeForList(context)
    guard rows.count == 1 else { throw VerificationError.missingValue("Expected exactly one School") }
    return rows[0]
}
struct Fixture: Decodable {
    let address: String, establishedDate: String
    let schools: [Item]
    struct Item: Decodable { let name: String, capacity: Int64, active: Bool, type: String }
}
let phase = CommandLine.arguments.dropFirst().first ?? ""
guard ["bootstrap", "transaction", "worker", "seed", "query", "update", "read-update", "delete", "read-delete"].contains(phase) else { throw VerificationError.invalidPhase }
let database = try required(ProcessInfo.processInfo.environment["SCHOOL_DATABASE"])
let provider = try SQLiteDataService(path: database)
var runtime = TeaQLRuntime()
try runtime.install(GeneratedRuntimeModule.module)
let context = UserContext(runtime: runtime, actor: "school-example", trustedTenant: "school-example",
    queryExecutor: provider, mutationExecutor: provider, requestPolicy: RequestPolicy { $0 })
if phase != "worker" { try await context.ensureSchema(GeneratedRuntimeModule.module) }
if phase == "seed" {
    let path = try required(ProcessInfo.processInfo.environment["SCHOOL_FIXTURE"])
    let fixture = try JSONDecoder().decode(Fixture.self, from: Data(contentsOf: URL(fileURLWithPath: path)))
    let established = try required(ISO8601DateFormatter().date(from: fixture.establishedDate + "T00:00:00Z"))
    for item in fixture.schools {
        var school = try Q.schools().comment("Create an advanced School fixture row")
            .purpose("Seed advanced School scenarios").newEntity(context)
        school.updatePlatform(1)
        if item.type == "PRIMARY" { school.updateSchoolTypeToPrimary() } else { school.updateSchoolTypeToSecondary() }
        school.updateName(item.name); school.updateAddress(fixture.address); school.updateEstablishedDate(established)
        school.updateStudentCapacity(item.capacity); school.updateActive(item.active)
        _ = try await school.auditAs("Seed the advanced School fixture").save(context)
    }
}

// scenario: transaction-commit, transaction-rollback
if phase == "transaction" {
    let rollback = ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "transaction-rollback"
    var root = try required(try await Q.platforms().selectSchoolListWith(Q.schools().withNameIs("Riverside Primary School").limit(2))
        .limit(2).comment("Load the Platform and School transaction graph").purpose("Verify atomic graph writes").executeForList(context).first)
    if rollback { var winner = try await load(context, "Riverside Primary School"); winner.updateStudentCapacity(801)
        _ = try await winner.auditAs("Advance School before the stale graph").save(context) }
    root.updateName("Transactional Campus"); root.schoolList[0].updateStudentCapacity(810)
    print("SCHOOL_GRAPH_ATTEMPT")
    var rejected = false
    do { _ = try await root.auditAs("Revise Platform and School atomically").save(context) }
    catch { let text = String(describing: error).lowercased()
        guard rollback && ["version", "optimistic", "conflict", "stale"].contains(where: { text.contains($0) }) else { throw error }; rejected = true }
    try report(required(ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"]), ["rejected": rejected])
}
// scenario: concurrent-same, concurrent-distinct
if phase == "worker" {
    let target = try required(ProcessInfo.processInfo.environment["SCHOOL_TARGET"])
    let worker = try required(ProcessInfo.processInfo.environment["SCHOOL_WORKER"])
    let barrier = try required(ProcessInfo.processInfo.environment["SCHOOL_BARRIER"])
    var row = try await load(context, target); let observedVersion = try required(E.school(row).version().eval())
    try JSONSerialization.data(withJSONObject: ["observedVersion": observedVersion]).write(to: URL(fileURLWithPath: "\(barrier)/\(worker).ready"), options: .atomic)
    let deadline = Date().addingTimeInterval(30)
    while !FileManager.default.fileExists(atPath: "\(barrier)/release") {
        guard Date() < deadline else { throw VerificationError.missingValue("Barrier timeout") }; try await Task.sleep(nanoseconds: 10_000_000) }
    row.updateStudentCapacity(worker == "a" ? 811 : 822)
    var outcome = "committed"; var conflictReason = ""
    do { _ = try await row.auditAs("Concurrent School capacity revision").save(context) }
    catch { let text = String(describing: error).lowercased()
        guard ["version", "optimistic", "conflict", "stale"].contains(where: { text.contains($0) }) else { throw error }; outcome = "conflict"; conflictReason = String(describing: error) }
    let data = try JSONSerialization.data(withJSONObject: ["worker": worker, "target": target, "observedVersion": observedVersion, "outcome": outcome, "conflictReason": conflictReason])
    print("SCHOOL_WORKER " + String(decoding: data, as: UTF8.self))
}

if phase == "query" {
    // scenario: continuous-ascending
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "continuous-ascending" {
        var pages: [[String]] = []; var plans: [String] = []

        for (offset, threshold) in [(0, 0), (2, 0), (4, 0)] {
            let page = try await Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(Int64(threshold))
                .orderByIdAscending().offset(offset).limit(2).optimizeForContinuousPageFetch(namespace: "continuous-ascending", ttlSeconds: 60)
                .comment("Browse School pagination").purpose("Verify continuous-ascending").executeForList(context)
            pages.append(try names(page)); plans.append(await context.continuousPageObservation().plan)

        }
        try report("continuous-ascending", ["pages": pages, "plans": plans])
    }
    // scenario: continuous-descending
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "continuous-descending" {
        var pages: [[String]] = []; var plans: [String] = []

        for (offset, threshold) in [(0, 0), (2, 0), (4, 0)] {
            let page = try await Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(Int64(threshold))
                .orderByIdDescending().offset(offset).limit(2).optimizeForContinuousPageFetch(namespace: "continuous-descending", ttlSeconds: 60)
                .comment("Browse School pagination").purpose("Verify continuous-descending").executeForList(context)
            pages.append(try names(page)); plans.append(await context.continuousPageObservation().plan)

        }
        try report("continuous-descending", ["pages": pages, "plans": plans])
    }
    // scenario: continuous-cache-miss
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "continuous-cache-miss" {
        var pages: [[String]] = []; var plans: [String] = []

        for (offset, threshold) in [(2, 0), (4, 0)] {
            let page = try await Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(Int64(threshold))
                .orderByIdAscending().offset(offset).limit(2).optimizeForContinuousPageFetch(namespace: "continuous-cache-miss", ttlSeconds: 60)
                .comment("Browse School pagination").purpose("Verify continuous-cache-miss").executeForList(context)
            pages.append(try names(page)); plans.append(await context.continuousPageObservation().plan)

        }
        try report("continuous-cache-miss", ["pages": pages, "plans": plans])
    }
    // scenario: id-set-jump
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "id-set-jump" {
        var pages: [[String]] = []; var plans: [String] = []
        var totals: [Int] = []; var observedCounts: [Int] = []
        for (offset, threshold) in [(4, 0), (0, 0), (2, 0), (6, 0)] {
            let page = try await Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(Int64(threshold))
                .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(offset).limit(2).optimizePaginationWithIdSet(namespace: "id-set-jump", ttlSeconds: 60, maxIds: 100).count()
                .comment("Browse School pagination").purpose("Verify id-set-jump").executeForList(context)
            pages.append(try names(page)); plans.append(await context.idSetPaginationObservation().plan)
            totals.append(Int(try required(page.totalCount))); observedCounts.append(try required(await context.idSetPaginationObservation().count))
        }
        try report("id-set-jump", ["pages": pages, "plans": plans, "totals": totals, "observedCounts": observedCounts])
    }
    // scenario: id-set-empty
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "id-set-empty" {
        var pages: [[String]] = []; var plans: [String] = []
        var totals: [Int] = []; var observedCounts: [Int] = []
        for (offset, threshold) in [(0, 100000), (2, 100000)] {
            let page = try await Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(Int64(threshold))
                .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(offset).limit(2).optimizePaginationWithIdSet(namespace: "id-set-empty", ttlSeconds: 60, maxIds: 100).count()
                .comment("Browse School pagination").purpose("Verify id-set-empty").executeForList(context)
            pages.append(try names(page)); plans.append(await context.idSetPaginationObservation().plan)
            totals.append(Int(try required(page.totalCount))); observedCounts.append(try required(await context.idSetPaginationObservation().count))
        }
        try report("id-set-empty", ["pages": pages, "plans": plans, "totals": totals, "observedCounts": observedCounts])
    }
    // scenario: id-set-overflow
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "id-set-overflow" {
        var pages: [[String]] = []; var plans: [String] = []
        var totals: [Int] = []; var observedCounts: [Int] = []
        for (offset, threshold) in [(2, 0)] {
            let page = try await Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(Int64(threshold))
                .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(offset).limit(2).optimizePaginationWithIdSet(namespace: "id-set-overflow", ttlSeconds: 60, maxIds: 3).count()
                .comment("Browse School pagination").purpose("Verify id-set-overflow").executeForList(context)
            pages.append(try names(page)); plans.append(await context.idSetPaginationObservation().plan)
            totals.append(Int(try required(page.totalCount))); observedCounts.append(try required(await context.idSetPaginationObservation().count))
        }
        try report("id-set-overflow", ["pages": pages, "plans": plans, "totals": totals, "observedCounts": observedCounts])
    }
    // scenario: id-set-isolation
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "id-set-isolation" {
        var pages: [[String]] = []; var plans: [String] = []
        var totals: [Int] = []; var observedCounts: [Int] = []
        for (offset, threshold) in [(0, 500), (0, 1000), (0, 500)] {
            let page = try await Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(Int64(threshold))
                .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(offset).limit(2).optimizePaginationWithIdSet(namespace: "id-set-isolation", ttlSeconds: 60, maxIds: 100).count()
                .comment("Browse School pagination").purpose("Verify id-set-isolation").executeForList(context)
            pages.append(try names(page)); plans.append(await context.idSetPaginationObservation().plan)
            totals.append(Int(try required(page.totalCount))); observedCounts.append(try required(await context.idSetPaginationObservation().count))
        }
        try report("id-set-isolation", ["pages": pages, "plans": plans, "totals": totals, "observedCounts": observedCounts])
    }
    // scenario: pagination
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "pagination" {
    let page = try await Q.schoolsWithMinimalFields().selectName().whichAreActive()
        .withStudentCapacityGreaterThanOrEqualTo(500)
        .orderByStudentCapacityDescending().orderByIdAscending().offset(1).limit(2).count()
        .comment("Page active schools by capacity").purpose("Review the School directory").executeForList(context)
    try report("pagination", ["names": names(page), "total": required(page.totalCount)])
    }
    // scenario: relations
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "relations" {
    let rows = try await Q.schoolsWithMinimalFields().selectName().withNameIs("Riverside Primary School")
        .selectSchoolTypeWith(Q.schoolTypesWithMinimalFields().selectCode())
        .selectPlatformWith(Q.platformsWithMinimalFields().selectName()).limit(2)
        .comment("Load School type and platform").purpose("Review loaded School relations").executeForList(context)
    precondition(rows.count == 1)
    let one = rows[0]
    try report("relations", ["school": required(E.school(one).name().eval()),
        "type": required(E.schoolType(required(E.school(one).schoolType().eval())).code().eval()),
        "platform": required(E.platform(required(E.school(one).platform().eval())).name().eval())])
    }
    // scenario: groups
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "groups" {
    let groups = try await Q.schools().groupBySchoolType().countAs("count")
        .sumStudentCapacityAs("total").avgStudentCapacityAs("average").limit(10)
        .comment("Group School capacity by type").purpose("Verify provider aggregation").executeForRows(context)
    try report("groups", groups.map { row in ["type": try number(row, "schoolType"), "count": try number(row, "count"),
        "total": try number(row, "total"), "average": try number(row, "average")] })
    }
    // scenario: relation-metrics
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "relation-metrics" {
    let cards = try await Q.schoolTypesWithMinimalFields().selectCode().countSchoolsAs("count")
        .sumStudentCapacityOfSchoolsAs("total", Q.schools()).orderByCodeAscending().limit(10)
        .comment("Calculate SchoolType relation metrics").purpose("Verify provider relation aggregation").executeForRows(context)
    try report("relation-metrics", Dictionary(uniqueKeysWithValues: cards.map {
        (try text($0, "code"), ["count": try number($0, "count"), "total": try number($0, "total")]) }))
    }
    // scenario: facets
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "facets" {
    var results: [SmartList<School>] = []
    for includeAll in [true, false] {
        let rows = try await Q.schoolsWithMinimalFields().selectName().withNameIs("Riverside Primary School")
            .facetBySchoolTypeAs("types", Q.schoolTypesWithMinimalFields().selectCode().countSchoolsAs("count"), includeAllFacets: includeAll)
            .facetByPlatformAs("platforms", Q.platformsWithMinimalFields().selectName().countSchoolsAs("count"), includeAllFacets: false)
            .orderByIdAscending().limit(2).comment("Facet the matching School set").purpose("Compare zero and matched buckets")
            .executeForList(context)
        results.append(rows)
    }
    try report("facets", ["includeAll": buckets(results[0], "types", "code"), "matchedOnly": buckets(results[1], "types", "code"),
        "platforms": buckets(results[0], "platforms", "name"), "rows": names(results[0])])
    }
    // scenario: page-boundaries
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "page-boundaries" {
        var pages: [[String]] = []; var totals: [Int64] = []
        for offset in [0, 2, 4, 6] {
            let page = try await Q.schoolsWithMinimalFields().selectName()
                .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(offset).limit(2).count()
                .comment("Traverse School pages including an empty tail").purpose("Verify stable page boundaries").executeForList(context)
            pages.append(try names(page)); totals.append(Int64(try required(page.totalCount)))
        }
        try report("page-boundaries", ["pages": pages, "totals": totals])
    }
    // scenario: relation-predicates
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "relation-predicates" {
        let matching = try await Q.schoolsWithMinimalFields().selectName()
            .withSchoolTypeMatching(Q.schoolTypesWithMinimalFields().withCodeIs("PRIMARY"))
            .orderByNameAscending().limit(10).comment("Match School type by child request")
            .purpose("Verify relation predicate semantics").executeForList(context)
        let excluding = try await Q.schoolsWithMinimalFields().selectName()
            .withoutSchoolTypeMatching(Q.schoolTypesWithMinimalFields().withCodeIs("PRIMARY"))
            .orderByNameAscending().limit(10).comment("Exclude School type by child request")
            .purpose("Verify negative relation predicates").executeForList(context)
        let nested = try await Q.schoolsWithMinimalFields().selectName()
            .withSchoolTypeMatching(Q.schoolTypesWithMinimalFields().withCodeIs("PRIMARY")
                .withPlatformMatching(Q.platformsWithMinimalFields().withNameIs("Campus Learning Platform")))
            .orderByNameAscending().limit(10).comment("Match School through type and platform")
            .purpose("Verify two-level relation predicates").executeForList(context)
        try report("relation-predicates", ["matching": names(matching), "excluding": names(excluding), "nested": names(nested)])
    }
    // scenario: blank-intent
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "blank-intent" {
        var rejected: [Bool] = []
        for (comment, purpose) in [(" ", "Verify blank comment rejection"), ("Check blank purpose rejection", " ")] {
            var denied = false
            do { _ = try await Q.schoolsWithMinimalFields().limit(1).comment(comment).purpose(purpose).executeForList(context) }
            catch {
                let message = String(describing: error).lowercased()
                guard ["comment", "purpose", "intent"].contains(where: { message.contains($0) }) else { throw error }
                denied = true
            }
            rejected.append(denied)
        }
        try report("blank-intent", ["blankComment": rejected[0], "blankPurpose": rejected[1]])
    }
    // scenario: empty-page
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "empty-page" {
    let page = try await Q.schoolsWithMinimalFields().selectName().withNameIs("No such School")

        .orderByStudentCapacityDescending().orderByIdAscending().offset(1).limit(2).count()
        .comment("Read a School filter with no matches").purpose("Verify empty page count").executeForList(context)
    try report("empty-page", ["names": names(page), "total": required(page.totalCount)])
    }
    // scenario: filtered-groups
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "filtered-groups" {
    let groups = try await Q.schools().whichAreActive().withStudentCapacityGreaterThanOrEqualTo(500).groupBySchoolType().countAs("count")
        .sumStudentCapacityAs("total").avgStudentCapacityAs("average").limit(10)
        .comment("Aggregate active Schools with capacity at least 500").purpose("Verify provider aggregation").executeForRows(context)
    try report("filtered-groups", groups.map { row in ["type": try number(row, "schoolType"), "count": try number(row, "count"),
        "total": try number(row, "total"), "average": try number(row, "average")] })
    }
    // scenario: filtered-children
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "filtered-children" {
    let types = try await Q.schoolTypesWithMinimalFields().selectCode()
        .selectSchoolListWith(Q.schoolsWithMinimalFields().selectName().whichAreNotActive()
            .orderByStudentCapacityDescending().orderByIdAscending().limit(2))
        .orderByCodeAscending().limit(10).comment("Load inactive Schools for each type").purpose("Preserve parents with empty child lists")
        .executeForList(context)
    var top: [String: [String]] = [:]
    for type in types {
        let count = try required(E.schoolType(type).schoolList().size().eval())
        let children = try (0..<count).map { try required(E.schoolType(type).schoolList().get($0).name().eval()) }
        top[try required(E.schoolType(type).code().eval())] = children
    }
    try report("filtered-children", top)
    }
    // scenario: empty-facets
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "empty-facets" {
    var results: [SmartList<School>] = []
    for includeAll in [true, false] {
        let rows = try await Q.schoolsWithMinimalFields().selectName().withNameIs("No such School")
            .facetBySchoolTypeAs("types", Q.schoolTypesWithMinimalFields().selectCode().countSchoolsAs("count"), includeAllFacets: includeAll)
            .facetByPlatformAs("platforms", Q.platformsWithMinimalFields().selectName().countSchoolsAs("count"), includeAllFacets: false)
            .orderByIdAscending().limit(2).comment("Facet the matching School set").purpose("Compare zero and matched buckets")
            .executeForList(context)
        results.append(rows)
    }
    try report("empty-facets", ["includeAll": buckets(results[0], "types", "code"), "matchedOnly": buckets(results[1], "types", "code"),
        "platforms": buckets(results[0], "platforms", "name"), "rows": names(results[0])])
    }
    // scenario: top-n
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "top-n" {
    let types = try await Q.schoolTypesWithMinimalFields().selectCode()
        .selectSchoolListWith(Q.schoolsWithMinimalFields().selectName()
            .orderByStudentCapacityDescending().orderByIdAscending().limit(2))
        .orderByCodeAscending().limit(10).comment("Load the two largest schools for each type").purpose("Verify Top-N across two parents")
        .executeForList(context)
    var top: [String: [String]] = [:]
    for type in types {
        let count = try required(E.schoolType(type).schoolList().size().eval())
        let children = try (0..<count).map { try required(E.schoolType(type).schoolList().get($0).name().eval()) }
        top[try required(E.schoolType(type).code().eval())] = children
    }
    try report("top-n", top)
    }
}
// scenario: update
if phase == "update" {
    var school = try await load(context, "Riverside Primary School")
    var stale = try await load(context, "Riverside Primary School")
    let before = try required(E.school(school).version().eval())
    school.updateStudentCapacity(950)
    school = try await school.auditAs("Approve School capacity revision").save(context)
    try report("update", ["before": before, "after": required(E.school(school).version().eval()), "capacity": required(E.school(school).studentCapacity().eval())])
    stale.updateStudentCapacity(999)
    var rejected = false
    do { _ = try await stale.auditAs("Reject stale School capacity revision").save(context) }
    catch {
        let description = String(describing: error).lowercased()
        guard ["version", "optimistic", "conflict", "stale"].contains(where: { description.contains($0) }) else { throw error }
        rejected = true
    }
    try report("stale-write", ["rejected": rejected])
}
if phase == "read-update" {
    let school = try await load(context, "Riverside Primary School")
    try report("read-update", ["capacity": required(E.school(school).studentCapacity().eval()), "version": required(E.school(school).version().eval())])
}
// scenario: delete
if phase == "delete" {
    var school = try await load(context, "Hill Secondary School")
    school.markForDeletion()
    _ = try await school.auditAs("Retire inactive School fixture").save(context)
}
if phase == "delete" || (phase == "read-delete" && (ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "read-delete")) {
    let visible = try await Q.schoolsWithMinimalFields().withNameIs("Hill Secondary School").limit(2)
        .comment("Check the retired School is hidden").purpose("Verify soft deletion visibility").executeForList(context)
    if phase == "delete" { try report(phase, ["visible": visible.count]) }
    else {
        let deleted = try await Q.schoolsWithMinimalFields().deletedRowsOnly().withNameIs("Hill Secondary School").limit(2)
            .comment("Read the School tombstone").purpose("Verify persisted deletion intent").executeForList(context)
        try report(phase, ["visible": visible.count, "deleted": deleted.count])
    }
}
if phase == "read-delete" {
    // scenario: deleted-groups
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "deleted-groups" {
    let groups = try await Q.schools().groupBySchoolType().countAs("count")
        .sumStudentCapacityAs("total").avgStudentCapacityAs("average").limit(10)
        .comment("Aggregate surviving Schools after deletion").purpose("Verify provider aggregation").executeForRows(context)
    try report("deleted-groups", groups.map { row in ["type": try number(row, "schoolType"), "count": try number(row, "count"),
        "total": try number(row, "total"), "average": try number(row, "average")] })
    }
    // scenario: deleted-metrics
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "deleted-metrics" {
    let cards = try await Q.schoolTypesWithMinimalFields().selectCode().countSchoolsAs("count")
        .sumStudentCapacityOfSchoolsAs("total", Q.schools()).orderByCodeAscending().limit(10)
        .comment("Calculate relation metrics after School deletion").purpose("Verify provider relation aggregation").executeForRows(context)
    try report("deleted-metrics", Dictionary(uniqueKeysWithValues: cards.map {
        (try text($0, "code"), ["count": try number($0, "count"), "total": try number($0, "total")]) }))
    }
    // scenario: deleted-facets
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "deleted-facets" {
    var results: [SmartList<School>] = []
    for includeAll in [true, false] {
        let rows = try await Q.schoolsWithMinimalFields().selectName()
            .facetBySchoolTypeAs("types", Q.schoolTypesWithMinimalFields().selectCode().countSchoolsAs("count"), includeAllFacets: includeAll)
            .facetByPlatformAs("platforms", Q.platformsWithMinimalFields().selectName().countSchoolsAs("count"), includeAllFacets: false)
            .orderByStudentCapacityDescending().orderByIdAscending().limit(2).comment("Facet the matching School set").purpose("Compare zero and matched buckets")
            .executeForList(context)
        results.append(rows)
    }
    try report("deleted-facets", ["includeAll": buckets(results[0], "types", "code"), "matchedOnly": buckets(results[1], "types", "code"),
        "platforms": buckets(results[0], "platforms", "name"), "rows": names(results[0])])
    }
    // scenario: deleted-children
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "deleted-children" {
    let types = try await Q.schoolTypesWithMinimalFields().selectCode()
        .selectSchoolListWith(Q.schoolsWithMinimalFields().selectName()
            .orderByStudentCapacityDescending().orderByIdAscending().limit(2))
        .orderByCodeAscending().limit(10).comment("Load surviving Schools for each type").purpose("Exclude tombstones from ordinary child lists")
        .executeForList(context)
    var top: [String: [String]] = [:]
    for type in types {
        let count = try required(E.schoolType(type).schoolList().size().eval())
        let children = try (0..<count).map { try required(E.schoolType(type).schoolList().get($0).name().eval()) }
        top[try required(E.schoolType(type).code().eval())] = children
    }
    try report("deleted-children", top)
    }
    // scenario: deleted-child-tombstones
    if ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == nil || ProcessInfo.processInfo.environment["SCHOOL_SCENARIO"] == "deleted-child-tombstones" {
    let types = try await Q.schoolTypesWithMinimalFields().selectCode()
        .selectSchoolListWith(Q.schoolsWithMinimalFields().selectName().deletedRowsOnly()
            .orderByStudentCapacityDescending().orderByIdAscending().limit(2))
        .orderByCodeAscending().limit(10).comment("Load retired Schools for each live type").purpose("Keep child deletion scope separate from parent visibility")
        .executeForList(context)
    var top: [String: [String]] = [:]
    for type in types {
        let count = try required(E.schoolType(type).schoolList().size().eval())
        let children = try (0..<count).map { try required(E.schoolType(type).schoolList().get($0).name().eval()) }
        top[try required(E.schoolType(type).code().eval())] = children
    }
    try report("deleted-child-tombstones", top)
    }
}
print("SCHOOL_OK phase=\(phase)")
