package com.example.schoolmanagementservice;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.example.schoolmanagementservice.school.School;
import com.example.schoolmanagementservice.schooltype.SchoolType;
import com.example.schoolmanagementservice.platform.Platform;
import io.teaql.core.SmartList;
import io.teaql.core.UserContext;
import java.nio.file.Path;
import java.time.LocalDate;
import java.util.*;

/** Application-owned executable checks; generated domain classes remain untouched. */
public final class SchoolCheck {
    private static final ObjectMapper JSON = new ObjectMapper();
    private static void report(String name, Object value) throws Exception {
        System.out.println("SCHOOL_CASE " + JSON.writeValueAsString(Map.of("case", name, "value", value)));
    }
    private static School load(UserContext context, String name) {
        var school = Q.schools().withNameIs(name).limit(2)
            .comment("Load complete School state for mutation").purpose("Preserve optimistic version")
            .executeForOne(context);
        return Objects.requireNonNull(school, "School not found");
    }
    public static void run(String phase, UserContext context) {
        try { execute(phase, context); }
        catch (Exception failure) { throw new IllegalStateException("Advanced School verification failed", failure); }
    }
    private static void execute(String phase, UserContext context) throws Exception {
        if (!List.of("bootstrap", "transaction", "worker", "seed", "query", "update", "read-update", "delete", "read-delete").contains(phase))
            throw new IllegalArgumentException("Unknown advanced phase");
        if (phase.equals("seed")) {
            var fixture = JSON.readTree(Path.of(System.getenv("SCHOOL_FIXTURE")).toFile());
            var roots = Q.platformsWithMinimalFields().withIdIs(1L).limit(2)
                .comment("Load the seeded School root").purpose("Seed advanced School scenarios")
                .executeForList(context);
            if (roots.size() != 1) throw new IllegalStateException("Missing root");
            for (var item : fixture.get("schools")) {
                var school = Q.schools().comment("Create an advanced School fixture row")
                    .purpose("Seed advanced School scenarios").newEntity(context);
                school.updatePlatform(roots.get(0));
                if (item.get("type").asText().equals("PRIMARY")) school.updateSchoolTypeToPrimary();
                else school.updateSchoolTypeToSecondary();
                school.updateName(item.get("name").asText());
                school.updateAddress(fixture.get("address").asText());
                school.updateEstablishedDate(LocalDate.parse(fixture.get("establishedDate").asText()));
                school.updateStudentCapacity(item.get("capacity").asInt());
                school.updateActive(item.get("active").asBoolean());
                school.auditAs("Seed the advanced School fixture").save(context);
            }
        }

        // scenario: transaction-commit, transaction-rollback
        if (phase.equals("transaction")) {
            boolean rollback = "transaction-rollback".equals(System.getenv("SCHOOL_SCENARIO"));
            var root = 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").executeForOne(context);
            var child = root.getSchoolList().get(0);
            if (rollback) { var winner = load(context, "Riverside Primary School"); winner.updateStudentCapacity(801);
                winner.auditAs("Advance School before the stale graph").save(context); }
            root.updateName("Transactional Campus"); child.updateStudentCapacity(810);
            System.out.println("SCHOOL_GRAPH_ATTEMPT");
            boolean rejected = false;
            try { root.auditAs("Revise Platform and School atomically").save(context); }
            catch (RuntimeException error) {
                if (!rollback || (!(error instanceof io.teaql.core.ConcurrentModifyException)
                    && !error.toString().toLowerCase().matches("(?s).*(version|optimistic|conflict|stale).*$"))) throw error;
                rejected = true;
            }
            report(System.getenv("SCHOOL_SCENARIO"), Map.of("rejected", rejected));
        }
        // scenario: concurrent-same, concurrent-distinct
        if (phase.equals("worker")) {
            String target = System.getenv("SCHOOL_TARGET"), worker = System.getenv("SCHOOL_WORKER");
            var row = load(context, target); var observedVersion = E.school(row).getVersion().eval();
            var barrier = Path.of(System.getenv("SCHOOL_BARRIER"));
            java.nio.file.Files.writeString(barrier.resolve(worker + ".ready"), JSON.writeValueAsString(Map.of("observedVersion", observedVersion)));
            long deadline = System.nanoTime() + 30_000_000_000L;
            while (!java.nio.file.Files.exists(barrier.resolve("release"))) {
                if (System.nanoTime() > deadline) throw new IllegalStateException("Barrier timeout"); Thread.sleep(10);
            }
            row.updateStudentCapacity(worker.equals("a") ? 811 : 822);
            String outcome = "committed", conflictReason = "";
            try { row.auditAs("Concurrent School capacity revision").save(context); }
            catch (RuntimeException error) {
                if (!(error instanceof io.teaql.core.ConcurrentModifyException)
                    && !error.toString().toLowerCase().matches("(?s).*(version|optimistic|conflict|stale).*$")) throw error;
                outcome = "conflict"; conflictReason = error.toString();
            }
            System.out.println("SCHOOL_WORKER " + JSON.writeValueAsString(Map.of("worker", worker, "target", target, "observedVersion", observedVersion, "outcome", outcome, "conflictReason", conflictReason)));
        }

        if (phase.equals("query")) {
            // scenario: continuous-ascending
            if ("continuous-ascending".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var pages = new ArrayList<Object>(); var plans = new ArrayList<Object>();
                var totals = new ArrayList<Object>(); var observedCounts = new ArrayList<Object>();
                for (int[] step : new int[][]{{0,0},{2,0},{4,0}}) {
                    var page = Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(step[1])
                        .orderByIdAscending().offset(step[0], 2).optimizeForContinuousPageFetch("continuous-ascending", 60)
                        .comment("Browse School pagination").purpose("Verify continuous-ascending").executeForList(context);
                    pages.add(page.stream().map(s -> E.school(s).getName().eval()).toList());
                    plans.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.CONTINUOUS_PAGE_PLAN));

                }
                report("continuous-ascending", Map.of("pages", pages, "plans", plans));
            }
            // scenario: continuous-descending
            if ("continuous-descending".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var pages = new ArrayList<Object>(); var plans = new ArrayList<Object>();
                var totals = new ArrayList<Object>(); var observedCounts = new ArrayList<Object>();
                for (int[] step : new int[][]{{0,0},{2,0},{4,0}}) {
                    var page = Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(step[1])
                        .orderByIdDescending().offset(step[0], 2).optimizeForContinuousPageFetch("continuous-descending", 60)
                        .comment("Browse School pagination").purpose("Verify continuous-descending").executeForList(context);
                    pages.add(page.stream().map(s -> E.school(s).getName().eval()).toList());
                    plans.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.CONTINUOUS_PAGE_PLAN));

                }
                report("continuous-descending", Map.of("pages", pages, "plans", plans));
            }
            // scenario: continuous-cache-miss
            if ("continuous-cache-miss".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var pages = new ArrayList<Object>(); var plans = new ArrayList<Object>();
                var totals = new ArrayList<Object>(); var observedCounts = new ArrayList<Object>();
                for (int[] step : new int[][]{{2,0},{4,0}}) {
                    var page = Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(step[1])
                        .orderByIdAscending().offset(step[0], 2).optimizeForContinuousPageFetch("continuous-cache-miss", 60)
                        .comment("Browse School pagination").purpose("Verify continuous-cache-miss").executeForList(context);
                    pages.add(page.stream().map(s -> E.school(s).getName().eval()).toList());
                    plans.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.CONTINUOUS_PAGE_PLAN));

                }
                report("continuous-cache-miss", Map.of("pages", pages, "plans", plans));
            }
            // scenario: id-set-jump
            if ("id-set-jump".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var pages = new ArrayList<Object>(); var plans = new ArrayList<Object>();
                var totals = new ArrayList<Object>(); var observedCounts = new ArrayList<Object>();
                for (int[] step : new int[][]{{4,0},{0,0},{2,0},{6,0}}) {
                    var page = Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(step[1])
                        .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(step[0], 2).optimizePaginationWithIdSet("id-set-jump", 60, 100).count()
                        .comment("Browse School pagination").purpose("Verify id-set-jump").executeForList(context);
                    pages.add(page.stream().map(s -> E.school(s).getName().eval()).toList());
                    plans.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.ID_SET_PLAN));
                    totals.add(page.getTotalCount()); observedCounts.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.ID_SET_COUNT));
                }
                report("id-set-jump", Map.of("pages", pages, "plans", plans, "totals", totals, "observedCounts", observedCounts));
            }
            // scenario: id-set-empty
            if ("id-set-empty".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var pages = new ArrayList<Object>(); var plans = new ArrayList<Object>();
                var totals = new ArrayList<Object>(); var observedCounts = new ArrayList<Object>();
                for (int[] step : new int[][]{{0,100000},{2,100000}}) {
                    var page = Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(step[1])
                        .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(step[0], 2).optimizePaginationWithIdSet("id-set-empty", 60, 100).count()
                        .comment("Browse School pagination").purpose("Verify id-set-empty").executeForList(context);
                    pages.add(page.stream().map(s -> E.school(s).getName().eval()).toList());
                    plans.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.ID_SET_PLAN));
                    totals.add(page.getTotalCount()); observedCounts.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.ID_SET_COUNT));
                }
                report("id-set-empty", Map.of("pages", pages, "plans", plans, "totals", totals, "observedCounts", observedCounts));
            }
            // scenario: id-set-overflow
            if ("id-set-overflow".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var pages = new ArrayList<Object>(); var plans = new ArrayList<Object>();
                var totals = new ArrayList<Object>(); var observedCounts = new ArrayList<Object>();
                for (int[] step : new int[][]{{2,0}}) {
                    var page = Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(step[1])
                        .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(step[0], 2).optimizePaginationWithIdSet("id-set-overflow", 60, 3).count()
                        .comment("Browse School pagination").purpose("Verify id-set-overflow").executeForList(context);
                    pages.add(page.stream().map(s -> E.school(s).getName().eval()).toList());
                    plans.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.ID_SET_PLAN));
                    totals.add(page.getTotalCount()); observedCounts.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.ID_SET_COUNT));
                }
                report("id-set-overflow", Map.of("pages", pages, "plans", plans, "totals", totals, "observedCounts", observedCounts));
            }
            // scenario: id-set-isolation
            if ("id-set-isolation".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var pages = new ArrayList<Object>(); var plans = new ArrayList<Object>();
                var totals = new ArrayList<Object>(); var observedCounts = new ArrayList<Object>();
                for (int[] step : new int[][]{{0,500},{0,1000},{0,500}}) {
                    var page = Q.schoolsWithMinimalFields().selectName().withStudentCapacityGreaterThanOrEqualTo(step[1])
                        .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(step[0], 2).optimizePaginationWithIdSet("id-set-isolation", 60, 100).count()
                        .comment("Browse School pagination").purpose("Verify id-set-isolation").executeForList(context);
                    pages.add(page.stream().map(s -> E.school(s).getName().eval()).toList());
                    plans.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.ID_SET_PLAN));
                    totals.add(page.getTotalCount()); observedCounts.add(context.getAttribute(io.teaql.core.sql.portable.PortableSQLRepository.ID_SET_COUNT));
                }
                report("id-set-isolation", Map.of("pages", pages, "plans", plans, "totals", totals, "observedCounts", observedCounts));
            }
            // scenario: pagination
            if ("pagination".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var page = Q.schoolsWithMinimalFields().selectName().whichAreActive()
                .withStudentCapacityGreaterThanOrEqualTo(500)
                .orderByStudentCapacityDescending().orderByIdAscending().offset(1, 2).count()
                .comment("Page active schools by capacity").purpose("Review the School directory")
                .executeForList(context);
            report("pagination", Map.of("names", page.stream().map(s -> E.school(s).getName().eval()).toList(), "total", page.getTotalCount()));
            }
            // scenario: relations
            if ("relations".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var rows = 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);
            if (rows.size() != 1) throw new IllegalStateException("Expected one School");
            var school = rows.get(0);
            report("relations", Map.of("school", E.school(school).getName().eval(),
                "type", E.schoolType(E.school(school).getSchoolType().eval()).getCode().eval(),
                "platform", E.platform(E.school(school).getPlatform().eval()).getName().eval()));
            }
            // scenario: groups
            if ("groups".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var groups = Q.schools().groupBySchoolTypeAs("type").countAs("count")
                .sumStudentCapacityAs("total").avgStudentCapacityAs("average").limit(10)
                .comment("Group School capacity by type").purpose("Verify provider aggregation")
                .aggregation(context);
            report("groups", groups.toList());
            }
            // scenario: relation-metrics
            if ("relation-metrics".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var cards = Q.schoolTypesWithMinimalFields().selectCode().countSchoolsAs("count")
                .sumStudentCapacityOfSchoolsAs("total", Q.schools()).orderByCodeAscending().limit(10)
                .comment("Calculate SchoolType relation metrics").purpose("Verify provider relation aggregation")
                .executeForList(context);
            var metrics = new TreeMap<String, Object>();
            for (var card : cards) metrics.put(E.schoolType(card).getCode().eval(),
                Map.of("count", card.getDynamicProperty("count"), "total", card.getDynamicProperty("total")));
            report("relation-metrics", metrics);
            }
            // scenario: facets
            if ("facets".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var allRows = facets(context, true, "Riverside Primary School");
            var matched = facets(context, false, "Riverside Primary School");
            SmartList<SchoolType> allBuckets = allRows.getFacet("types"), matchedBuckets = matched.getFacet("types");
            SmartList<Platform> platformBuckets = allRows.getFacet("platforms");
            var allValues = new TreeMap<String, Object>();
            var matchedValues = new TreeMap<String, Object>();
            var platforms = new TreeMap<String, Object>();
            for (var bucket : allBuckets) allValues.put(E.schoolType(bucket).getCode().eval(), bucket.getDynamicProperty("count"));
            for (var bucket : matchedBuckets) matchedValues.put(E.schoolType(bucket).getCode().eval(), bucket.getDynamicProperty("count"));
            for (var bucket : platformBuckets) platforms.put(E.platform(bucket).getName().eval(), bucket.getDynamicProperty("count"));
            report("facets", Map.of("includeAll", allValues, "matchedOnly", matchedValues, "platforms", platforms,
                "rows", allRows.stream().map(s -> E.school(s).getName().eval()).toList()));
            }
            // scenario: page-boundaries
            if ("page-boundaries".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var pages = new ArrayList<Object>(); var totals = new ArrayList<Object>();
                for (int offset : new int[]{0, 2, 4, 6}) {
                    var page = Q.schoolsWithMinimalFields().selectName()
                        .orderByStudentCapacityDescending().orderByNameAscending().orderByIdAscending().offset(offset, 2).count()
                        .comment("Traverse School pages including an empty tail").purpose("Verify stable page boundaries")
                        .executeForList(context);
                    pages.add(page.stream().map(s -> E.school(s).getName().eval()).toList()); totals.add(page.getTotalCount());
                }
                report("page-boundaries", Map.of("pages", pages, "totals", totals));
            }
            // scenario: relation-predicates
            if ("relation-predicates".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var matching = 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);
                var excluding = 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);
                var nested = 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);
                report("relation-predicates", Map.of("matching", matching.stream().map(s -> E.school(s).getName().eval()).toList(),
                    "excluding", excluding.stream().map(s -> E.school(s).getName().eval()).toList(),
                    "nested", nested.stream().map(s -> E.school(s).getName().eval()).toList()));
            }
            // scenario: blank-intent
            if ("blank-intent".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
                var rejected = new ArrayList<Boolean>();
                for (var intent : List.of(new String[]{" ", "Verify blank comment rejection"}, new String[]{"Check blank purpose rejection", " "})) {
                    boolean denied = false;
                    try { Q.schoolsWithMinimalFields().limit(1).comment(intent[0]).purpose(intent[1]).executeForList(context); }
                    catch (RuntimeException error) {
                        if (!error.toString().toLowerCase().matches("(?s).*(comment|purpose|intent).*") ) throw error;
                        denied = true;
                    }
                    rejected.add(denied);
                }
                report("blank-intent", Map.of("blankComment", rejected.get(0), "blankPurpose", rejected.get(1)));
            }
            // scenario: empty-page
            if ("empty-page".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var page = Q.schoolsWithMinimalFields().selectName().withNameIs("No such School")

                .orderByStudentCapacityDescending().orderByIdAscending().offset(1, 2).count()
                .comment("Read a School filter with no matches").purpose("Verify empty page count")
                .executeForList(context);
            report("empty-page", Map.of("names", page.stream().map(s -> E.school(s).getName().eval()).toList(), "total", page.getTotalCount()));
            }
            // scenario: filtered-groups
            if ("filtered-groups".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var groups = Q.schools().whichAreActive().withStudentCapacityGreaterThanOrEqualTo(500).groupBySchoolTypeAs("type").countAs("count")
                .sumStudentCapacityAs("total").avgStudentCapacityAs("average").limit(10)
                .comment("Aggregate active Schools with capacity at least 500").purpose("Verify provider aggregation")
                .aggregation(context);
            report("filtered-groups", groups.toList());
            }
            // scenario: filtered-children
            if ("filtered-children".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var types = 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 = new TreeMap<String, Object>();
            for (var type : types) {
                var names = new ArrayList<String>();
                for (int i = 0; i < E.schoolType(type).getSchoolList().size().eval(); i++)
                    names.add(E.schoolType(type).getSchoolList().get(i).getName().eval());
                top.put(E.schoolType(type).getCode().eval(), names);
            }
            report("filtered-children", top);
            }
            // scenario: empty-facets
            if ("empty-facets".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var allRows = facets(context, true, "No such School");
            var matched = facets(context, false, "No such School");
            SmartList<SchoolType> allBuckets = allRows.getFacet("types"), matchedBuckets = matched.getFacet("types");
            SmartList<Platform> platformBuckets = allRows.getFacet("platforms");
            var allValues = new TreeMap<String, Object>();
            var matchedValues = new TreeMap<String, Object>();
            var platforms = new TreeMap<String, Object>();
            for (var bucket : allBuckets) allValues.put(E.schoolType(bucket).getCode().eval(), bucket.getDynamicProperty("count"));
            for (var bucket : matchedBuckets) matchedValues.put(E.schoolType(bucket).getCode().eval(), bucket.getDynamicProperty("count"));
            for (var bucket : platformBuckets) platforms.put(E.platform(bucket).getName().eval(), bucket.getDynamicProperty("count"));
            report("empty-facets", Map.of("includeAll", allValues, "matchedOnly", matchedValues, "platforms", platforms,
                "rows", allRows.stream().map(s -> E.school(s).getName().eval()).toList()));
            }
            // scenario: top-n
            if ("top-n".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var types = 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 = new TreeMap<String, Object>();
            for (var type : types) {
                var names = new ArrayList<String>();
                for (int i = 0; i < E.schoolType(type).getSchoolList().size().eval(); i++)
                    names.add(E.schoolType(type).getSchoolList().get(i).getName().eval());
                top.put(E.schoolType(type).getCode().eval(), names);
            }
            report("top-n", top);
            }
        }
        // scenario: update
        if (phase.equals("update")) {
            var school = load(context, "Riverside Primary School");
            var stale = load(context, "Riverside Primary School");
            var before = E.school(school).getVersion().eval();
            school.updateStudentCapacity(950);
            school = school.auditAs("Approve School capacity revision").save(context);
            report("update", Map.of("before", before, "after", E.school(school).getVersion().eval(), "capacity", E.school(school).getStudentCapacity().eval()));
            stale.updateStudentCapacity(999);
            boolean rejected = false;
            try { stale.auditAs("Reject stale School capacity revision").save(context); }
            catch (RuntimeException error) {
                if (!(error instanceof io.teaql.core.ConcurrentModifyException)
                    && !error.toString().toLowerCase().matches("(?s).*(version|optimistic|conflict|stale).*") ) throw error;
                rejected = true;
            }
            report("stale-write", Map.of("rejected", rejected));
        }
        if (phase.equals("read-update")) {
            var school = load(context, "Riverside Primary School");
            report("read-update", Map.of("capacity", E.school(school).getStudentCapacity().eval(), "version", E.school(school).getVersion().eval()));
        }
        // scenario: delete
        if (phase.equals("delete")) load(context, "Hill Secondary School").markForDeletion()
            .auditAs("Retire inactive School fixture").save(context);
        if (phase.equals("delete") || (phase.equals("read-delete") && (System.getenv("SCHOOL_SCENARIO") == null || "read-delete".equals(System.getenv("SCHOOL_SCENARIO"))))) {
            var visible = Q.schoolsWithMinimalFields().withNameIs("Hill Secondary School").limit(2)
                .comment("Check the retired School is hidden").purpose("Verify soft deletion visibility")
                .executeForList(context);
            if (phase.equals("delete")) report(phase, Map.of("visible", visible.size()));
            else {
                var deleted = Q.schoolsWithMinimalFields().deletedRowsOnly().withNameIs("Hill Secondary School").limit(2)
                    .comment("Read the School tombstone").purpose("Verify persisted deletion intent")
                    .executeForList(context);
                report(phase, Map.of("visible", visible.size(), "deleted", deleted.size()));
            }
        }
        if (phase.equals("read-delete")) {
            // scenario: deleted-groups
            if ("deleted-groups".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var groups = Q.schools().groupBySchoolTypeAs("type").countAs("count")
                .sumStudentCapacityAs("total").avgStudentCapacityAs("average").limit(10)
                .comment("Aggregate surviving Schools after deletion").purpose("Verify provider aggregation")
                .aggregation(context);
            report("deleted-groups", groups.toList());
            }
            // scenario: deleted-metrics
            if ("deleted-metrics".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var cards = Q.schoolTypesWithMinimalFields().selectCode().countSchoolsAs("count")
                .sumStudentCapacityOfSchoolsAs("total", Q.schools()).orderByCodeAscending().limit(10)
                .comment("Calculate relation metrics after School deletion").purpose("Verify provider relation aggregation")
                .executeForList(context);
            var metrics = new TreeMap<String, Object>();
            for (var card : cards) metrics.put(E.schoolType(card).getCode().eval(),
                Map.of("count", card.getDynamicProperty("count"), "total", card.getDynamicProperty("total")));
            report("deleted-metrics", metrics);
            }
            // scenario: deleted-facets
            if ("deleted-facets".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var allRows = facets(context, true, "School");
            var matched = facets(context, false, "School");
            SmartList<SchoolType> allBuckets = allRows.getFacet("types"), matchedBuckets = matched.getFacet("types");
            SmartList<Platform> platformBuckets = allRows.getFacet("platforms");
            var allValues = new TreeMap<String, Object>();
            var matchedValues = new TreeMap<String, Object>();
            var platforms = new TreeMap<String, Object>();
            for (var bucket : allBuckets) allValues.put(E.schoolType(bucket).getCode().eval(), bucket.getDynamicProperty("count"));
            for (var bucket : matchedBuckets) matchedValues.put(E.schoolType(bucket).getCode().eval(), bucket.getDynamicProperty("count"));
            for (var bucket : platformBuckets) platforms.put(E.platform(bucket).getName().eval(), bucket.getDynamicProperty("count"));
            report("deleted-facets", Map.of("includeAll", allValues, "matchedOnly", matchedValues, "platforms", platforms,
                "rows", allRows.stream().map(s -> E.school(s).getName().eval()).toList()));
            }
            // scenario: deleted-children
            if ("deleted-children".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var types = 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 = new TreeMap<String, Object>();
            for (var type : types) {
                var names = new ArrayList<String>();
                for (int i = 0; i < E.schoolType(type).getSchoolList().size().eval(); i++)
                    names.add(E.schoolType(type).getSchoolList().get(i).getName().eval());
                top.put(E.schoolType(type).getCode().eval(), names);
            }
            report("deleted-children", top);
            }
            // scenario: deleted-child-tombstones
            if ("deleted-child-tombstones".equals(System.getenv("SCHOOL_SCENARIO")) || System.getenv("SCHOOL_SCENARIO") == null) {
            var types = 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 = new TreeMap<String, Object>();
            for (var type : types) {
                var names = new ArrayList<String>();
                for (int i = 0; i < E.schoolType(type).getSchoolList().size().eval(); i++)
                    names.add(E.schoolType(type).getSchoolList().get(i).getName().eval());
                top.put(E.schoolType(type).getCode().eval(), names);
            }
            report("deleted-child-tombstones", top);
            }
        }
        System.out.println("SCHOOL_OK phase=" + phase);
    }
    private static SmartList<School> facets(UserContext context, boolean includeAll, String name) {
        return Q.schoolsWithMinimalFields().selectName().withNameContaining(name)
            .facetBySchoolTypeAs("types", Q.schoolTypesWithMinimalFields().selectCode().countAs("count"), includeAll)
            .facetByPlatformAs("platforms", Q.platformsWithMinimalFields().selectName().countAs("count"), false)
            .orderByStudentCapacityDescending().orderByIdAscending().limit(2)
            .comment("Facet the matching School set").purpose("Compare zero and matched buckets")
            .executeForList(context);
    }
}
