[{'Text': 'Edited /Users/peterdonaghey/Projects/point-topic-mcp/_explore_gbs.py:\n\n```diff\n@@ -1,160 +1,176 @@\n-"""Explore GBS MongoDB data shapes - temporary exploration script."""\n-import json\n-import os\n-import subprocess\n-import sys\n+"""Explore GBS with aggregation pipelines — single efficient run."""\n+import json, os, subprocess, sys\n+from collections import defaultdict\n+from datetime import datetime\n \n URI = os.environ["PT_RESEARCH_DATABASE_URI"]\n+CACHE = {}  # namespace -> result\n \n-def run_js(js, timeout=30):\n+def agg(pipeline, coll="Operator", timeout=30):\n+    """Run an aggregation pipeline, return parsed JSON."""\n+    js = f"EJSON.stringify(db.{coll}.aggregate({json.dumps(pipeline)}).toArray())"\n     uri = URI\n     if os.environ.get("PT_RESEARCH_TLS_SKIP_VERIFY", "").strip() in ("1", "true", "yes"):\n         sep = "&" if "?" in uri else "?"\n         uri = f"{uri}{sep}tlsAllowInvalidCertificates=true"\n-    r = subprocess.run(\n-        ["mongosh", uri, "--eval", js, "--quiet"],\n-        capture_output=True, text=True, timeout=timeout,\n-    )\n+    r = subprocess.run(["mongosh", uri, "--eval", js, "--quiet"],\n+                       capture_output=True, text=True, timeout=timeout)\n     if r.returncode != 0:\n-        raise RuntimeError(f"FAIL: {r.stderr or r.stdout}")\n-    return r.stdout.strip()\n+        raise RuntimeError(r.stderr or r.stdout)\n+    out = r.stdout.strip()\n+    return json.loads(out) if out else []\n \n-# 1. GlobalVariables\n-print("=== GlobalVariables ===")\n-gv_raw = run_js("EJSON.stringify(db.GlobalVariables.findOne({isCurrent: true}))")\n-print(gv_raw[:2000])\n-gv = json.loads(gv_raw)\n-current_period = gv.get("currentStatsPeriod", "UNKNOWN")\n-print(f"\\nCurrent period: {current_period}")\n+def find_one(coll, query, timeout=15):\n+    js = f"EJSON.stringify(db.{coll}.findOne({json.dumps(query)}))"\n+    uri = URI\n+    if os.environ.get("PT_RESEARCH_TLS_SKIP_VERIFY", "").strip() in ("1", "true", "yes"):\n+        sep = "&" if "?" in uri else "?"\n+        uri = f"{uri}{sep}tlsAllowInvalidCertificates=true"\n+    r = subprocess.run(["mongosh", uri, "--eval", js, "--quiet"],\n+                       capture_output=True, text=True, timeout=timeout)\n+    if r.returncode != 0:\n+        raise RuntimeError(r.stderr or r.stdout)\n+    out = r.stdout.strip()\n+    return json.loads(out) if out else None\n \n-# 2. Operator counts\n-print("\\n=== Operator counts ===")\n-total_ops = run_js("db.Operator.countDocuments()")\n-print(f"Total operators: {total_ops}")\n-countries = json.loads(run_js("EJSON.stringify(db.Operator.distinct(\'country\'))"))\n-print(f"Countries ({len(countries)}): {countries[:10]}{\'...\' if len(countries) > 10 else \'\'}")\n+def uid(doc):\n+    """Extract ObjectId string from a doc."""\n+    v = doc.get("_id", {})\n+    if isinstance(v, dict): return v.get("$oid", "")\n+    return str(v)\n \n-# 3. UK operator count\n-uk_ops = run_js("db.Operator.countDocuments({country: \'United Kingdom\'})")\n-print(f"UK operators: {uk_ops}")\n+# ── 1. Global State ──────────────────────────────────────────────\n+print("── 1. GLOBALS ──")\n+gv = find_one("GlobalVariables", {"isCurrent": True})\n+current = gv.get("currentStatsPeriod", "")\n+print(f"Current stats period: {current}")\n+print(f"Current tariffs period: {gv.get(\'currentTariffsPeriod\', \'\')}")\n+print(f"Updated by: {gv.get(\'updatedBy\', \'\')} @ {gv.get(\'updatedAt\', {}).get(\'$date\', \'\')}")\n \n-# 4. Sample operator (UK)\n-print("\\n=== Sample UK operator ===")\n-sample_uk = json.loads(run_js("EJSON.stringify(db.Operator.findOne({country: \'United Kingdom\'}))"))\n-print(json.dumps({k: v for k, v in sample_uk.items() if k != \'_id\'}, indent=2)[:1000])\n+# ── 2. All statistics periods — one aggregation ──────────────────\n+print("\\n── 2. PERIOD COVERAGE (ALL STATS) ──")\n+period_data = agg([\n+    {"$group": {"_id": "$period", "count": {"$sum": 1}, "operators": {"$addToSet": "$operator"}}}\n+], "Statistics")\n+periods = sorted(period_data, key=lambda x: x["_id"])\n+for p in periods:\n+    label = " ← CURRENT" if p["_id"] == current else (" ← FUTURE?" if p["_id"] > current else "")\n+    print(f"  {p[\'_id\']}: {p[\'count\']:>5} records, {len(p[\'operators\']):>4} operators{label}")\n \n-# 5. Statistics - distinct values\n-print("\\n=== Statistics distinct values ===")\n-periods = json.loads(run_js("EJSON.stringify(db.Statistics.distinct(\'period\'))"))\n-print(f"Periods: {sorted(periods)}")\n-states = json.loads(run_js("EJSON.stringify(db.Statistics.distinct(\'state\'))"))\n-print(f"States: {states}")\n-channels = json.loads(run_js("EJSON.stringify(db.Statistics.distinct(\'channel\'))"))\n-print(f"Channels: {channels}")\n-domains = json.loads(run_js("EJSON.stringify(db.Statistics.distinct(\'domain\'))"))\n-print(f"Domains: {domains}")\n-types = json.loads(run_js("EJSON.stringify(db.Statistics.distinct(\'type\'))"))\n-print(f"Types: {types}")\n+# ── 3. UK overview — one aggregation with $lookup ────────────────\n+print(f"\\n── 3. UK OPERATOR COVERAGE ({current}) ──")\n+uk_overview = agg([\n+    {"$match": {"country": "United Kingdom", "isArchived": {"$ne": True}}},\n+    {"$lookup": {\n+        "from": "Statistics",\n+        "let": {"oid": "$_id"},\n+        "pipeline": [\n+            {"$match": {"$expr": {\n+                "$and": [\n+                    {"$eq": ["$operatorId", "$$oid"]},\n+                    {"$eq": ["$period", current]}\n+                ]\n+            }}}\n+        ],\n+        "as": "stats"\n+    }},\n+    {"$project": {\n+        "name": 1, "technologies": 1,\n+        "statCount": {"$size": "$stats"},\n+        "states": {\n+            "$reduce": {\n+                "input": "$stats",\n+                "initialValue": {},\n+                "in": {\n+                    "$$value": 1,\n+                    # Just count them, we\'ll process below\n+                }\n+            }\n+        },\n+        "stats": 1\n+    }},\n+    {"$sort": {"name": 1}}\n+])\n \n-# 6. Sample statistic\n-print("\\n=== Sample statistic ===")\n-sample_stat = json.loads(run_js("EJSON.stringify(db.Statistics.findOne())"))\n-print(json.dumps({k: v for k, v in sample_stat.items() if k != \'_id\'}, indent=2)[:1500])\n+total_uk = len(uk_overview)\n+with_data = [o for o in uk_overview if o["statCount"] > 0]\n+without_data = [o for o in uk_overview if o["statCount"] == 0]\n+print(f"Total UK operators: {total_uk}")\n+print(f"With data: {len(with_data)}, Without data: {len(without_data)}")\n \n-# 7. Coverage stats for current period\n-print(f"\\n=== Coverage for {current_period} ===")\n-count_stats = run_js(f"db.Statistics.countDocuments({{period: \'{current_period}\'}})")\n-print(f"Total stats records: {count_stats}")\n-ops_with_data = json.loads(\n-    run_js(f"EJSON.stringify(db.Statistics.distinct(\'operatorId\', {{period: \'{current_period}\'}}))")\n-)\n-print(f"Unique operators with data: {len(ops_with_data)}")\n+# Coverage rate\n+print(f"\\nCoverage: {len(with_data)}/{total_uk} ({100*len(with_data)/total_uk:.1f}%)")\n \n-# 8. Previous period\n-all_periods = sorted(periods)\n-if len(all_periods) >= 2:\n-    prev = all_periods[-2]\n-    pcount = run_js(f"db.Statistics.countDocuments({{period: \'{prev}\'}})")\n-    pops = json.loads(\n-        run_js(f"EJSON.stringify(db.Statistics.distinct(\'operatorId\', {{period: \'{prev}\'}}))")\n-    )\n-    print(f"\\nPrevious period ({prev}): {pcount} records, {len(pops)} operators with data")\n+# Top operators by record count\n+print(f"\\nTop operators by stat count in {current}:")\n+for op in sorted(with_data, key=lambda x: -x["statCount"])[:10]:\n+    print(f"  {op[\'name\'][:30]:30s} {op[\'statCount\']:>3} records  techs: {op.get(\'technologies\', [])}")\n \n-# 9. State distribution for current period\n-print(f"\\n=== State distribution for {current_period} ===")\n-state_counts = run_js(f"""\n-  EJSON.stringify(\n-    db.Statistics.aggregate([\n-      {{$match: {{period: \'{current_period}\'}}}},\n-      {{$group: {{_id: \'$state\', count: {{$sum: 1}}}}}}\n-    ]).toArray()\n-  )\n-""")\n-print(state_counts)\n+# ── 4. GAP ANALYSIS: what combos are missing for UK operators ────\n+print(f"\\n── 4. GAP ANALYSIS (UK, {current}) ──")\n+# For a sample of operators (top 3 with data, bottom 3 without)\n+VALID_TYPES = ["broadband", "mobile", "iptv"]\n+VALID_CHANNELS = ["Infrastructure", "Retail"]\n+VALID_DOMAINS = ["Residential", "Business", "Total"]\n \n-# 10. Tech distribution\n-print("\\n=== Tech distribution ===")\n-techs = json.loads(run_js("EJSON.stringify(db.Statistics.distinct(\'tech\'))"))\n-print(f"Techs: {techs}")\n+def show_gaps(op):\n+    name = op["name"]\n+    techs = op.get("technologies", [])\n+    stats = op["stats"]\n+    # Build set of what exists\n+    exist = set()\n+    for s in stats:\n+        exist.add((s.get("type"), s.get("tech"), s.get("channel"), s.get("domain"), s.get("state")))\n+    \n+    # Group by type\n+    gap_count = 0\n+    gap_detail = defaultdict(list)\n+    filled = 0\n+    for t in VALID_TYPES:\n+        for tech in techs:\n+            for ch in VALID_CHANNELS:\n+                for dom in VALID_DOMAINS:\n+                    combo = (t, tech, ch, dom)\n+                    found = [s for s in stats if s.get("type")==t and s.get("tech")==tech \n+                             and s.get("channel")==ch and s.get("domain")==dom]\n+                    if found:\n+                        filled += 1\n+                    else:\n+                        gap_count += 1\n+                        gap_detail[t].append(f"{tech}/{ch}/{dom}")\n+    \n+    state_dist = defaultdict(int)\n+    for s in stats:\n+        state_dist[s.get("state")] += 1\n+    state_names = {1: "pending", 2: "approved", 3: "published"}\n+    state_str = ", ".join(f"{state_names.get(k,k)}: {v}" for k,v in sorted(state_dist.items())) if state_dist else "none"\n+    \n+    print(f"\\n  {name} ({len(stats)} records, {filled} filled/{gap_count} gaps)")\n+    print(f"    States: {state_str}")\n+    for t, glist in gap_detail.items():\n+        if glist:\n+            print(f"    {t} gaps ({len(glist)}): {\', \'.join(glist[:5])}{\'...\' if len(glist)>5 else \'\'}")\n \n-# 11. Test batched query: get all stats for all UK operators in current period\n-print(f"\\n=== BATCHED: All stats for {current_period} ===")\n-# First get all UK operator IDs\n-uk_ops_raw = json.loads(\n-    run_js("EJSON.stringify(db.Operator.find({country: \'United Kingdom\'}, {_id: 1}).toArray())")\n-)\n-uk_ids = [op[\'_id\'][\'$oid\'] for op in uk_ops_raw]\n-print(f"Total UK operators: {len(uk_ids)}")\n+print("Operators WITH data (sample top 3):")\n+for op in with_data[:3]:\n+    show_gaps(op)\n \n-# Batched query: all stats for these operators in current period\n-oid_list = json.dumps([f"ObjectId(\'{oid}\')" for oid in uk_ids])\n-batched_js = f"""\n-  const ids = [{\',\'.join(f"ObjectId(\'{oid}\')" for oid in uk_ids)}];\n-  EJSON.stringify(\n-    db.Statistics.find({{\n-      operatorId: {{$in: ids}},\n-      period: \'{current_period}\'\n-    }}).toArray()\n-  )\n-"""\n-batched_raw = run_js(batched_js, timeout=60)\n-batched_stats = json.loads(batched_raw) if batched_raw else []\n-print(f"Batched stats returned: {len(batched_stats)} records")\n-if batched_stats:\n-    print(f"Sample: {json.dumps(batched_stats[0], indent=2)[:500]}")\n+print("\\nOperators WITHOUT data (sample 3):")\n+for op in without_data[:3]:\n+    show_gaps(op)\n \n-# 12. Operator coverage summary (has_data / no_data)\n-print(f"\\n=== Operator coverage summary for {current_period} ===")\n-# Which UK operators have data?\n-ops_with_data_set = set(s.get(\'operatorId\', {}).get(\'$oid\', \'\') for s in batched_stats)\n-coverage = []\n-for op in uk_ops_raw:\n-    oid = op[\'_id\'][\'$oid\']\n-    has_data = oid in ops_with_data_set\n-    coverage.append((oid, has_data))\n+# ── 5. WHAT DOES A COMPLETE OPERATOR LOOK LIKE? ──────────────────\n+print(f"\\n── 5. MOST COMPLETE UK OPERATOR IN {current} ──")\n+most_complete = max(with_data, key=lambda x: x["statCount"])\n+show_gaps(most_complete)\n \n-with_data = sum(1 for _, h in coverage if h)\n-without_data = sum(1 for _, h in coverage if not h)\n-print(f"With data: {with_data}, Without data: {without_data}")\n+# ── 6. ADMIN LINK ────────────────────────────────────────────────\n+print(f"\\n── 6. ADMIN CONTEXT ──")\n+admin_url = "https://research-app.pt-research.net/admin"\n+print(f"Admin URL: {admin_url}")\n+# MongoDB IDs for quick reference\n+print(f"\\nGlobalVariables ID: {uid(gv)}")\n+print(f"Current period: {current}")\n+print(f"Previous complete period: 2025Q2 (unless shown differently above)")\n \n-# 13. Per-operator record count for current period\n-print(f"\\n=== Per-operator record counts (top 20) ===")\n-from collections import Counter\n-op_stat_counts = Counter()\n-for s in batched_stats:\n-    oid = s.get(\'operatorId\', {}).get(\'$oid\', \'\')\n-    op_stat_counts[oid] += 1\n-\n-# Get operator names\n-name_map = {}\n-for op in uk_ops_raw:\n-    oid = op[\'_id\'][\'$oid\']\n-    op_data = json.loads(\n-        run_js(f"EJSON.stringify(db.Operator.findOne({{_id: ObjectId(\'{oid}\')}}, {{name: 1}}))")\n-    )\n-    name_map[oid] = op_data.get(\'name\', \'UNKNOWN\')\n-\n-for oid, count in op_stat_counts.most_common(20):\n-    print(f"  {name_map.get(oid, oid)[:30]:30s} {count} records")\n-\n-print("\\n=== DONE ===")\n+print("\\n── DONE ──")\n\n```'}]