LLD: Compute Audit Summary Script

1. Objective

Replace the 13 dummy documents in the audit-summaries collection with real, computed summaries derived from the transformer collection (~405K docs). The script will aggregate transformer data at every level of the location hierarchy (SECTION → SUB_DIVISION → DIVISION → CIRCLE → ZONE → MESCOM) for each month.

---

2. Field Derivation from Transformer Schema

Every field in an auditSummary document is derived from one or more fields in the transformer collection. This section maps each field to its source.

2.1 Identity & Location

AuditSummary Field	Source
level	Not from TC. Set by the script based on aggregation level (SECTION, SUB_DIVISION, DIVISION, CIRCLE, ZONE, MESCOM)
month	Direct carry-forward from transformer.month (the group key)
location.*	$first of corresponding transformer.location.* fields within the group. Safe because all TCs in a section share the same parent hierarchy

2.2 Summary Counts

AuditSummary Field	Derivation
totalTCCount	COUNT(*) — total transformer docs in the group
auditedCount	COUNT WHERE transformer.auditStatus === "AUDITED"
failedCount	COUNT WHERE transformer.auditStatus === "AUDIT_FAILED"

How transformer.auditStatus was determined (by backfillAuditStatus.js):

• lossPercentage != null → AUDITED
• lossPercentage == null → AUDIT_FAILED

2.3 Loss Buckets

Only counts TCs where transformer.auditStatus === "AUDITED". Each TC falls into exactly one bucket.

AuditSummary Field	Condition on transformer.lossPercentage
lossBuckets.belowMinus5	lossPercentage < -5
lossBuckets.minus5to0	-5 <= lossPercentage < 0
lossBuckets.zeroTo5	0 <= lossPercentage < 5
lossBuckets.fiveTo10	5 <= lossPercentage < 10
lossBuckets.tenTo15	10 <= lossPercentage < 15
lossBuckets.fifteenTo20	15 <= lossPercentage < 20
lossBuckets.above20	lossPercentage >= 20

lossPercentage formula:

lossPercentage = ((tcConsumption * meterConstant) - installationConsumption)
                 / (tcConsumption * meterConstant) * 100

2.4 Failed Reasons

Only counts TCs where transformer.auditStatus === "AUDIT_FAILED". Categories are non-exclusive (a TC can match multiple).

AuditSummary Field	Condition on Transformer Fields	Why this causes audit failure
failedReasons.noMeterConstant	meterConstant is null or 0	Can't compute tcConsumption * meterConstant → lossPercentage not derivable
failedReasons.withoutInstallationConsumption	installationConsumption is null or 0	No installation consumption → loss formula incomplete
failedReasons.noTcConsumptionWithRemarks	tcConsumption === null AND remark !== null	TC meter not providing readings, explained by a remark (METER_NOT_RECORDING, CT_BURNT_OUT, NO_DISPLAY, METER_NOT_PROVIDED)
failedReasons.noTcConsumptionNoRemarks	tcConsumption === null AND remark === null	TC meter not providing readings with no explanation — most concerning

tcConsumption formula:

tcConsumption = reading.finalValue - reading.initialValue

If either reading.initialValue or reading.finalValue is null, tcConsumption stays null.

2.5 Metadata

AuditSummary Field	Source
lastAuditedOn	Set to new Date() at script execution time — not derived from any TC field

2.6 Chain of Dependencies

reading.initialValue ─┐
                      ├─→ tcConsumption ─────────┐
reading.finalValue  ──┘                          │
                                                 ├─→ lossPercentage ─→ auditStatus
meterConstant ───────────────────────────────────┤                      │
                                                 │                      ├─→ AUDITED → lossBuckets
installationConsumption (sum of tagged           │                      └─→ AUDIT_FAILED → failedReasons
  installations' consumption) ───────────────────┘

remark ──────────────────────────────────→ used to classify failedReasons
                                           (withRemarks vs noRemarks)

---

3. Algorithm

Phase 1: Clear existing collection

Delete ALL documents from audit-summaries collection (auditSummaryModel.deleteMany({}))

This removes all 13 dummy documents regardless of month, ensuring a clean slate before inserting real data.

Phase 2: Resolve target months

Input: config.months (optional string array, e.g., ["2025-09", "2025-10"])
If not provided → query transformer.distinct("month") to get all available months

Phase 3: Compute SECTION-level summaries (from transformer collection)

For each target month, run a single MongoDB aggregation pipeline on the transformer collection:

Pipeline:
  $match  → { month: <targetMonth> }
  $group  → by { sectionCode: "$location.sectionCode" }
    Carry forward (via $first):
      - sectionName, subDivisionCode, subDivisionName,
        divisionCode, divisionName, circleCode, circleName,
        zoneCode, zoneName
    Compute:
      - totalTCCount: { $sum: 1 }
      - auditedCount: count where auditStatus === "AUDITED"
      - failedCount: count where auditStatus === "AUDIT_FAILED"
      - 7 loss bucket counts (conditional on AUDITED + lossPercentage range)
      - 4 failed reason counts (conditional on AUDIT_FAILED + field checks)

This produces ~202 section summaries per month.

Phase 4: Roll up higher levels (from computed SECTION docs)

For each higher level, aggregate the SECTION-level results (in application memory, not a DB aggregation — only ~808 docs):

For each level in [SUB_DIVISION, DIVISION, CIRCLE, ZONE, MESCOM]:
  Group SECTION docs by { month, <levelCode> }
  Sum: totalTCCount, auditedCount, failedCount, all lossBuckets, all failedReasons
  Carry forward: location names from the first section in the group
  Set: level, lastAuditedOn = now

Grouping keys per level:

Level	Group by
SUB_DIVISION	month + subDivisionCode
DIVISION	month + divisionCode
CIRCLE	month + circleCode
ZONE	month + zoneCode
MESCOM	month only

Location fields populated per level:

Level	Location fields set
SECTION	sectionCode/Name, subDivisionCode/Name, divisionCode/Name, circleCode/Name, zoneCode/Name
SUB_DIVISION	subDivisionCode/Name, divisionCode/Name, circleCode/Name, zoneCode/Name
DIVISION	divisionCode/Name, circleCode/Name, zoneCode/Name
CIRCLE	circleCode/Name, zoneCode/Name
ZONE	zoneCode/Name
MESCOM	(empty object)

Phase 5: Persist to database

Insert all computed docs (SECTION + all rolled-up levels) via insertMany

Collection is already cleared in Phase 1, so this is a straightforward bulk insert.

• ordered: false for better performance (continues on individual failures)
• Script is idempotent — Phase 1 clears everything, so re-running produces the same result

---

4. Script Design

File location

script/src/entities/auditSummary/computeAuditSummary.js

Invocation

Config name: auditSummary_computeAuditSummary

In config.json:

{
  "scriptRunArray": ["auditSummary_computeAuditSummary"],
  "input": {
    "auditSummary": {
      "computeAuditSummary": {
        "months": ["2025-09", "2025-10", "2025-11", "2025-12"]
      }
    }
  }
}

If months is omitted, the script auto-discovers all distinct months from the transformer collection.

Exports

export const tags = [];

export async function mainFunction(input) { ... }

Internal structure

mainFunction(input)
├── clearCollection()                     // Phase 1: delete all existing docs
├── resolveMonths(input.months)           // Phase 2: get target months
├── for each month:
│   ├── computeSectionSummaries(month)    // Phase 3: MongoDB aggregation
│   ├── rollUpToHigherLevels(sections)    // Phase 4: in-memory grouping
│   └── collect all docs for this month
└── insertAllDocs(allDocs)               // Phase 5: bulk insert

Key functions

computeSectionSummaries(month) → SectionDoc[]

Runs the MongoDB aggregation pipeline (Phase 2) and returns an array of section-level summary objects ready for insertion.

rollUpToHigherLevels(sectionDocs) → RolledUpDoc[]

Takes section docs for a single month, groups them by each higher level's code, sums all numeric fields, and returns SUB_DIVISION + DIVISION + CIRCLE + ZONE + MESCOM docs.

groupAndSum(docs, level, groupKeyFn, locationFn) → Doc[]

Generic helper that groups an array of docs by a key, sums the numeric summary fields, and constructs the output doc with the correct level and location.

---

5. MongoDB Aggregation Pipeline (Phase 2 — detail)

[
  { $match: { month } },
  {
    $group: {
      _id: "$location.sectionCode",

      // Location (carry forward)
      sectionName:      { $first: "$location.sectionName" },
      subDivisionCode:  { $first: "$location.subDivisionCode" },
      subDivisionName:  { $first: "$location.subDivisionName" },
      divisionCode:     { $first: "$location.divisionCode" },
      divisionName:     { $first: "$location.divisionName" },
      circleCode:       { $first: "$location.circleCode" },
      circleName:       { $first: "$location.circleName" },
      zoneCode:         { $first: "$location.zoneCode" },
      zoneName:         { $first: "$location.zoneName" },

      // Counts
      totalTCCount: { $sum: 1 },
      auditedCount: {
        $sum: { $cond: [{ $eq: ["$auditStatus", "AUDITED"] }, 1, 0] }
      },
      failedCount: {
        $sum: { $cond: [{ $eq: ["$auditStatus", "AUDIT_FAILED"] }, 1, 0] }
      },

      // Loss buckets (AUDITED only)
      belowMinus5: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDITED"] },
            { $lt: ["$lossPercentage", -5] }
          ]}, 1, 0
        ]}
      },
      minus5to0: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDITED"] },
            { $gte: ["$lossPercentage", -5] },
            { $lt: ["$lossPercentage", 0] }
          ]}, 1, 0
        ]}
      },
      zeroTo5: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDITED"] },
            { $gte: ["$lossPercentage", 0] },
            { $lt: ["$lossPercentage", 5] }
          ]}, 1, 0
        ]}
      },
      fiveTo10: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDITED"] },
            { $gte: ["$lossPercentage", 5] },
            { $lt: ["$lossPercentage", 10] }
          ]}, 1, 0
        ]}
      },
      tenTo15: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDITED"] },
            { $gte: ["$lossPercentage", 10] },
            { $lt: ["$lossPercentage", 15] }
          ]}, 1, 0
        ]}
      },
      fifteenTo20: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDITED"] },
            { $gte: ["$lossPercentage", 15] },
            { $lt: ["$lossPercentage", 20] }
          ]}, 1, 0
        ]}
      },
      above20: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDITED"] },
            { $gte: ["$lossPercentage", 20] }
          ]}, 1, 0
        ]}
      },

      // Failed reasons (AUDIT_FAILED only, non-exclusive)
      noMeterConstant: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDIT_FAILED"] },
            { $in: ["$meterConstant", [null, 0]] }
          ]}, 1, 0
        ]}
      },
      withoutInstallationConsumption: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDIT_FAILED"] },
            { $in: ["$installationConsumption", [null, 0]] }
          ]}, 1, 0
        ]}
      },
      noTcConsumptionWithRemarks: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDIT_FAILED"] },
            { $eq: ["$tcConsumption", null] },
            { $ne: ["$remark", null] }
          ]}, 1, 0
        ]}
      },
      noTcConsumptionNoRemarks: {
        $sum: { $cond: [
          { $and: [
            { $eq: ["$auditStatus", "AUDIT_FAILED"] },
            { $eq: ["$tcConsumption", null] },
            { $eq: ["$remark", null] }
          ]}, 1, 0
        ]}
      },
    }
  }
]

---

6. In-Memory Roll-Up (Phase 3 — detail)

After SECTION docs are computed for a month, roll-up is done in-memory (not another DB query). With ~202 section docs per month, this is trivial.

Summable fields (summed during roll-up):
  totalTCCount, auditedCount, failedCount,
  lossBuckets.{ belowMinus5, minus5to0, zeroTo5, fiveTo10, tenTo15, fifteenTo20, above20 },
  failedReasons.{ noMeterConstant, withoutInstallationConsumption,
                  noTcConsumptionWithRemarks, noTcConsumptionNoRemarks }

Roll-up example: SECTION → SUB_DIVISION

Input:  202 section docs for month "2025-09"
Group by: subDivisionCode
Output: 64 sub-division docs

For each group:
  - level: "SUB_DIVISION"
  - month: "2025-09"
  - location: {
      subDivisionCode, subDivisionName,    ← from first section in group
      divisionCode, divisionName,
      circleCode, circleName,
      zoneCode, zoneName
    }
  - totalTCCount: sum of sections' totalTCCount
  - auditedCount: sum of sections' auditedCount
  - ... (all summable fields)
  - lastAuditedOn: new Date()

Same pattern repeats for DIVISION (group sections by divisionCode), CIRCLE (by circleCode), ZONE (by zoneCode), MESCOM (all sections in one group).

---

7. Persistence Strategy

// Phase 1: Clear entire collection
await auditSummaryModel.deleteMany({});

// Phase 5: Bulk insert all computed docs
await auditSummaryModel.insertMany(allDocs, { ordered: false });

• Phase 1 clears the entire collection upfront (not filtered by month) — removes all dummy data
• ordered: false for better insert performance (continues on individual failures)
• Script is idempotent — safe to re-run

---

8. Validation & Logging

After computation, log these sanity checks per month:

Check	Validation
auditedCount + failedCount === totalTCCount	Per section and per MESCOM
Sum of loss buckets === auditedCount	Per section
MESCOM totalTCCount matches transformer.countDocuments({ month })	Cross-collection
Total docs inserted per level	Expected counts match hierarchy

Log format:

[2025-09] SECTION: 202 docs | SUB_DIVISION: 64 | DIVISION: 14 | CIRCLE: 4 | ZONE: 2 | MESCOM: 1
[2025-09] MESCOM summary → total: 100392 | audited: 5512 | failed: 94880
[2025-09] ✓ auditedCount + failedCount === totalTCCount
[2025-09] ✓ lossBucket sum === auditedCount

---

9. Performance Estimate

Operation	Data size	Expected time
Section aggregation (per month)	~100K transformer docs	~2–5 seconds (indexed on month)
In-memory roll-up (per month)	~202 section docs	<10 ms
Delete old docs	~13 docs (current dummy)	<100 ms
Insert new docs	~1,148 docs total	<1 second
Total (4 months)		~10–25 seconds

---

10. Dependencies

Dependency	Import path
transformerModel	../../../../common/src/schemas/transformer.model.js
auditSummaryModel	../../../../common/src/schemas/auditSummary.model.js
log	../../utils/logger.js
assignDefaults	../../utils/functions.js

No new dependencies required.

---

11. Error Handling

Scenario	Handling
No transformer docs for a month	Log warning, skip that month
Aggregation returns 0 sections	Log error, skip insertion for that month
insertMany partial failure	ordered: false ensures other docs still insert; log failures
Script re-run	Idempotent — deletes old data first

---

12. Testing Strategy

After running the script, execute the following tests in order. Each test is a standalone mongosh query with its expected outcome. All tests must pass.

Connection:

mongosh "<MONGO_CONNECTION_STRING>" --db dtcea-mumbai-demo --quiet

---

Test 1: Document count per level

Verify the correct number of docs were created at each level.

db.getCollection("audit-summaries").aggregate([
  { $group: { _id: "$level", count: { $sum: 1 } } },
  { $sort: { _id: 1 } }
]).forEach(r => print(r._id, ":", r.count));

Expected output:

CIRCLE : 16        (4 circles × 4 months)
DIVISION : 56      (14 divisions × 4 months)
MESCOM : 4         (1 × 4 months)
SECTION : 808      (202 sections × 4 months)
SUB_DIVISION : 256 (64 sub-divisions × 4 months)
ZONE : 8           (2 zones × 4 months)

Pass criteria: All 6 levels present. Total docs = ~1,148. No other levels exist.

---

Test 2: All months covered

Verify all 4 months from the transformer collection are represented.

printjson(db.getCollection("audit-summaries").distinct("month").sort());

Expected output:

["2025-09", "2025-10", "2025-11", "2025-12"]

Pass criteria: Exactly these 4 months, no extras (no dummy 2026-01, 2026-02).

---

Test 3: MESCOM totalTCCount matches transformer count

Cross-validate the top-level counts against the source collection.

print("=== Transformer counts (source of truth) ===");
db.transformer.aggregate([
  { $group: { _id: "$month", count: { $sum: 1 } } },
  { $sort: { _id: 1 } }
]).forEach(r => print(r._id, ":", r.count));

print("");
print("=== MESCOM audit-summary totalTCCount ===");
db.getCollection("audit-summaries")
  .find({ level: "MESCOM" }, { month: 1, totalTCCount: 1, _id: 0 })
  .sort({ month: 1 })
  .forEach(r => print(r.month, ":", r.totalTCCount));

Pass criteria: For each month, MESCOM.totalTCCount === transformer.countDocuments({ month }).

Expected values:

2025-09 : 100392
2025-10 : 100501
2025-11 : 102327
2025-12 : 102583

---

Test 4: Invariant — auditedCount + failedCount === totalTCCount

Check this invariant holds for every single document in the collection.

var broken = db.getCollection("audit-summaries").find({
  $expr: { $ne: [{ $add: ["$auditedCount", "$failedCount"] }, "$totalTCCount"] }
}).toArray();

print("Docs violating invariant:", broken.length);
if (broken.length > 0) {
  broken.slice(0, 5).forEach(d =>
    print(d.level, d.month, d.location,
      "audited:", d.auditedCount, "failed:", d.failedCount, "total:", d.totalTCCount)
  );
}

Pass criteria: broken.length === 0. No document should violate this.

---

Test 5: Invariant — sum of loss buckets === auditedCount

Every audited TC must land in exactly one loss bucket.

var broken = db.getCollection("audit-summaries").find({
  $expr: {
    $ne: [
      { $add: [
        "$lossBuckets.belowMinus5", "$lossBuckets.minus5to0",
        "$lossBuckets.zeroTo5", "$lossBuckets.fiveTo10",
        "$lossBuckets.tenTo15", "$lossBuckets.fifteenTo20",
        "$lossBuckets.above20"
      ]},
      "$auditedCount"
    ]
  }
}).toArray();

print("Docs where lossBucket sum != auditedCount:", broken.length);
if (broken.length > 0) {
  broken.slice(0, 5).forEach(d =>
    print(d.level, d.month, JSON.stringify(d.location),
      "auditedCount:", d.auditedCount,
      "bucketSum:", Object.values(d.lossBuckets).reduce((a, b) => a + b, 0))
  );
}

Pass criteria: broken.length === 0.

---

Test 6: Roll-up consistency — SECTION sums match higher levels

Verify that summing SECTION docs produces the same totals as each higher level.

var fields = ["totalTCCount", "auditedCount", "failedCount"];
var levels = [
  { level: "SUB_DIVISION", groupKey: "location.subDivisionCode" },
  { level: "DIVISION", groupKey: "location.divisionCode" },
  { level: "CIRCLE", groupKey: "location.circleCode" },
  { level: "ZONE", groupKey: "location.zoneCode" },
  { level: "MESCOM", groupKey: null },
];

var months = db.getCollection("audit-summaries").distinct("month");
var pass = true;

months.forEach(month => {
  levels.forEach(({ level, groupKey }) => {
    // Sum sections grouped by the level's key
    var groupId = groupKey ? "$" + groupKey : null;
    var sectionSums = {};
    db.getCollection("audit-summaries").aggregate([
      { $match: { level: "SECTION", month } },
      { $group: {
        _id: groupId,
        totalTCCount: { $sum: "$totalTCCount" },
        auditedCount: { $sum: "$auditedCount" },
        failedCount: { $sum: "$failedCount" },
      }},
    ]).forEach(r => { sectionSums[r._id || "ALL"] = r; });

    // Get actual level docs
    db.getCollection("audit-summaries")
      .find({ level, month })
      .forEach(doc => {
        var key = groupKey ? doc.location[groupKey.split(".")[1]] : "ALL";
        var expected = sectionSums[key];
        if (!expected) {
          print("FAIL:", level, month, key, "- no matching section sum");
          pass = false;
          return;
        }
        fields.forEach(f => {
          if (doc[f] !== expected[f]) {
            print("FAIL:", level, month, key, f, "expected:", expected[f], "got:", doc[f]);
            pass = false;
          }
        });
      });
  });
});

print(pass ? "PASS: All roll-up sums match" : "FAIL: Some roll-up sums do not match");

Pass criteria: Prints PASS: All roll-up sums match.

---

Test 7: Cross-validate MESCOM auditedCount and failedCount against transformer

Directly count AUDITED and AUDIT_FAILED transformers and compare with MESCOM docs.

var pass = true;

db.getCollection("audit-summaries").find({ level: "MESCOM" }).sort({ month: 1 }).forEach(doc => {
  var audited = db.transformer.countDocuments({ month: doc.month, auditStatus: "AUDITED" });
  var failed = db.transformer.countDocuments({ month: doc.month, auditStatus: "AUDIT_FAILED" });

  if (doc.auditedCount !== audited) {
    print("FAIL:", doc.month, "auditedCount expected:", audited, "got:", doc.auditedCount);
    pass = false;
  }
  if (doc.failedCount !== failed) {
    print("FAIL:", doc.month, "failedCount expected:", failed, "got:", doc.failedCount);
    pass = false;
  }
  print(doc.month, "- audited:", doc.auditedCount, "(expected", audited + ")",
                    "failed:", doc.failedCount, "(expected", failed + ")");
});

print(pass ? "PASS" : "FAIL");

Pass criteria: All months show matching counts. Prints PASS.

---

Test 8: Cross-validate loss buckets against transformer (spot-check one month)

Pick 2025-09 and count transformers in each loss range directly, then compare with the MESCOM summary.

var month = "2025-09";
var match = { month, auditStatus: "AUDITED" };

var expected = {
  belowMinus5:  db.transformer.countDocuments({ ...match, lossPercentage: { $lt: -5 } }),
  minus5to0:    db.transformer.countDocuments({ ...match, lossPercentage: { $gte: -5, $lt: 0 } }),
  zeroTo5:      db.transformer.countDocuments({ ...match, lossPercentage: { $gte: 0, $lt: 5 } }),
  fiveTo10:     db.transformer.countDocuments({ ...match, lossPercentage: { $gte: 5, $lt: 10 } }),
  tenTo15:      db.transformer.countDocuments({ ...match, lossPercentage: { $gte: 10, $lt: 15 } }),
  fifteenTo20:  db.transformer.countDocuments({ ...match, lossPercentage: { $gte: 15, $lt: 20 } }),
  above20:      db.transformer.countDocuments({ ...match, lossPercentage: { $gte: 20 } }),
};

var mescom = db.getCollection("audit-summaries").findOne({ level: "MESCOM", month });
var pass = true;

Object.keys(expected).forEach(k => {
  var match = mescom.lossBuckets[k] === expected[k];
  if (!match) pass = false;
  print(k, "- expected:", expected[k], "got:", mescom.lossBuckets[k], match ? "OK" : "FAIL");
});

print(pass ? "PASS" : "FAIL");

Pass criteria: All 7 buckets match. Prints PASS.

---

Test 9: Cross-validate failed reasons against transformer (spot-check one month)

Pick 2025-09 and count failed transformers by each reason directly.

var month = "2025-09";
var match = { month, auditStatus: "AUDIT_FAILED" };

var expected = {
  noMeterConstant: db.transformer.countDocuments({
    ...match, $or: [{ meterConstant: null }, { meterConstant: 0 }]
  }),
  withoutInstallationConsumption: db.transformer.countDocuments({
    ...match, $or: [{ installationConsumption: null }, { installationConsumption: 0 }]
  }),
  noTcConsumptionWithRemarks: db.transformer.countDocuments({
    ...match, tcConsumption: null, remark: { $ne: null }
  }),
  noTcConsumptionNoRemarks: db.transformer.countDocuments({
    ...match, tcConsumption: null, remark: null
  }),
};

var mescom = db.getCollection("audit-summaries").findOne({ level: "MESCOM", month });
var pass = true;

Object.keys(expected).forEach(k => {
  var match = mescom.failedReasons[k] === expected[k];
  if (!match) pass = false;
  print(k, "- expected:", expected[k], "got:", mescom.failedReasons[k], match ? "OK" : "FAIL");
});

print(pass ? "PASS" : "FAIL");

Pass criteria: All 4 reasons match. Prints PASS.

---

Test 10: Spot-check a single SECTION against direct transformer aggregation

Pick one specific section and month, compute its summary directly from transformers, and compare with the stored audit-summary doc.

var month = "2025-09";
var sectionCode = "1"; // NR- KAIKAMBA

// Direct aggregation on transformer
var direct = db.transformer.aggregate([
  { $match: { month, "location.sectionCode": sectionCode } },
  { $group: {
    _id: null,
    totalTCCount: { $sum: 1 },
    auditedCount: { $sum: { $cond: [{ $eq: ["$auditStatus", "AUDITED"] }, 1, 0] } },
    failedCount: { $sum: { $cond: [{ $eq: ["$auditStatus", "AUDIT_FAILED"] }, 1, 0] } },
    belowMinus5: { $sum: { $cond: [{ $and: [{ $eq: ["$auditStatus", "AUDITED"] }, { $lt: ["$lossPercentage", -5] }] }, 1, 0] } },
    above20: { $sum: { $cond: [{ $and: [{ $eq: ["$auditStatus", "AUDITED"] }, { $gte: ["$lossPercentage", 20] }] }, 1, 0] } },
    noTcConsumptionNoRemarks: { $sum: { $cond: [{ $and: [{ $eq: ["$auditStatus", "AUDIT_FAILED"] }, { $eq: ["$tcConsumption", null] }, { $eq: ["$remark", null] }] }, 1, 0] } },
  }}
]).toArray()[0];

// Stored audit-summary
var stored = db.getCollection("audit-summaries").findOne({
  level: "SECTION", month, "location.sectionCode": sectionCode
});

print("=== Section", sectionCode, "Month", month, "===");
print("totalTCCount    - direct:", direct.totalTCCount, "stored:", stored.totalTCCount);
print("auditedCount    - direct:", direct.auditedCount, "stored:", stored.auditedCount);
print("failedCount     - direct:", direct.failedCount, "stored:", stored.failedCount);
print("belowMinus5     - direct:", direct.belowMinus5, "stored:", stored.lossBuckets.belowMinus5);
print("above20         - direct:", direct.above20, "stored:", stored.lossBuckets.above20);
print("noTcNoRemarks   - direct:", direct.noTcConsumptionNoRemarks, "stored:", stored.failedReasons.noTcConsumptionNoRemarks);

var pass = direct.totalTCCount === stored.totalTCCount
  && direct.auditedCount === stored.auditedCount
  && direct.failedCount === stored.failedCount
  && direct.belowMinus5 === stored.lossBuckets.belowMinus5
  && direct.above20 === stored.lossBuckets.above20
  && direct.noTcConsumptionNoRemarks === stored.failedReasons.noTcConsumptionNoRemarks;

print(pass ? "PASS" : "FAIL");

Pass criteria: All fields match between direct aggregation and stored doc. Prints PASS.

---

Test 11: No dummy data remains

Verify no documents with fake location codes or months outside the transformer data exist.

var dummyCodes = db.getCollection("audit-summaries").countDocuments({
  $or: [
    { "location.zoneCode": "ZN-001" },
    { "location.circleCode": "CIR-001" },
    { "location.divisionCode": "DIV-001" },
    { "location.subDivisionCode": "SD-001" },
    { month: { $in: ["2026-01", "2026-02"] } },
  ]
});

print("Dummy docs remaining:", dummyCodes);
print(dummyCodes === 0 ? "PASS" : "FAIL");

Pass criteria: dummyCodes === 0.

---

Test 12: Location hierarchy completeness

Verify that every SECTION doc has all parent location codes populated (not null).

var incomplete = db.getCollection("audit-summaries").countDocuments({
  level: "SECTION",
  $or: [
    { "location.sectionCode": null },
    { "location.subDivisionCode": null },
    { "location.divisionCode": null },
    { "location.circleCode": null },
    { "location.zoneCode": null },
  ]
});

print("SECTION docs with missing parent codes:", incomplete);
print(incomplete === 0 ? "PASS" : "FAIL");

Pass criteria: incomplete === 0. Every section doc must have the full hierarchy.

---

Test Summary

#	Test	What it validates
1	Doc count per level	Correct number of docs at each hierarchy level
2	All months covered	No missing months, no dummy months
3	MESCOM totalTCCount vs transformer	Top-level count matches source collection
4	audited + failed === total	Core invariant holds for every doc
5	Loss bucket sum === auditedCount	Every audited TC is in exactly one bucket
6	Roll-up consistency	SECTION sums match SUB_DIVISION, DIVISION, CIRCLE, ZONE, MESCOM
7	MESCOM audited/failed vs transformer	Direct count validation against source
8	Loss buckets vs transformer	Bucket boundaries produce correct counts
9	Failed reasons vs transformer	Failed classification logic is correct
10	Single section spot-check	End-to-end validation for one section
11	No dummy data remains	Old fake data is fully removed
12	Location hierarchy completeness	No null parent codes in SECTION docs

All 12 tests must print PASS for the script to be considered successful.