File size: 15,346 Bytes
eaa56f0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2b18225
eaa56f0
 
 
 
 
 
 
 
 
 
 
2b18225
 
eaa56f0
 
 
2b18225
 
 
 
 
 
 
 
 
 
 
 
 
 
eaa56f0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2b18225
 
 
 
 
 
 
 
 
 
 
 
 
 
eaa56f0
2b18225
 
 
eaa56f0
2b18225
eaa56f0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2b18225
 
 
 
 
eaa56f0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
392a0c4
 
 
 
 
eaa56f0
 
 
 
 
 
 
392a0c4
 
 
 
 
 
 
 
 
 
 
 
eaa56f0
 
 
392a0c4
eaa56f0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2b18225
 
eaa56f0
 
 
 
 
 
 
 
2b18225
eaa56f0
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
"""
Google Sheet → vector store sync.

Fetches a JSON payload produced by the Apps Script publisher and upserts each
entry into the existing ChromaDB collection used by `retrieval_engine`. Entries
sourced from the sheet are tagged with `origin=sheet` so we can prune ones the
staff have deleted without touching uploaded documents.

The payload schema (produced by sheet_integration/apps_script/Code.gs):

    {
      "version": "<sha256-prefix>",
      "generated_at": "...",
      "entries": [
        { "id": "...", "department": "...", "tab": "...", "category": "...",
          "title": "...", "content": "...", "source_url": "...",
          "last_updated": "...", "row": 12 }, ...
      ]
    }

Public API:
    SheetSync(retrieval_engine).refresh(force=False) -> dict
    SheetSync(retrieval_engine).status() -> dict
"""

from __future__ import annotations

import json
import logging
import os
import re
import time
from dataclasses import dataclass, field
from pathlib import Path
from typing import Any, Dict, List, Optional

import urllib.request
import urllib.error

logger = logging.getLogger(__name__)

SHEET_JSON_URL_ENV = "KNOWLEDGE_JSON_URL"
DATA_DIR = Path(os.environ.get("DATA_DIR", "./data"))
VERSION_FILE = DATA_DIR / "sheet_version.json"
FETCH_TIMEOUT_SECONDS = 20
SHEET_ID_PREFIX = "sheet:"  # vector store id namespace


def _version_file_for(org_id: Optional[str]) -> Path:
    """Where this sync's last-seen version marker lives.

    Each organization needs its own marker: sharing one file would make the
    first university's version look current for everyone, so the others would
    be skipped as "unchanged" and silently never update. `None` keeps the
    original single-Sheet path writing to the original filename.
    """
    if not org_id:
        return VERSION_FILE
    safe = re.sub(r"[^a-zA-Z0-9_.-]", "_", str(org_id))
    return DATA_DIR / f"sheet_version_{safe}.json"

# Max characters per chunk when an entry's content is long. Matches the value
# used by the document_processor pipeline so retrieval behaves consistently.
MAX_CHARS_PER_CHUNK = 1000
CHUNK_OVERLAP = 150


@dataclass
class SyncResult:
    ok: bool
    version: Optional[str] = None
    entries_seen: int = 0
    entries_upserted: int = 0
    entries_deleted: int = 0
    skipped_unchanged: bool = False
    error: Optional[str] = None
    fetched_at: float = field(default_factory=time.time)

    def to_dict(self) -> Dict[str, Any]:
        return {
            "ok": self.ok,
            "version": self.version,
            "entries_seen": self.entries_seen,
            "entries_upserted": self.entries_upserted,
            "entries_deleted": self.entries_deleted,
            "skipped_unchanged": self.skipped_unchanged,
            "error": self.error,
            "fetched_at": self.fetched_at,
        }


class SheetSync:
    """Syncs one Google Sheet into one retrieval engine's collection.

    Pass `json_url` and `organization_id` to sync a specific university's
    Sheet into that university's own collection. Omit both and it behaves
    exactly as before, reading KNOWLEDGE_JSON_URL and writing to whichever
    engine it was handed — that keeps ALU's existing sync untouched.
    """

    def __init__(
        self,
        retrieval_engine: Any,
        json_url: Optional[str] = None,
        organization_id: Optional[str] = None,
    ) -> None:
        self.retrieval_engine = retrieval_engine
        self.organization_id = organization_id
        self.json_url = (json_url if json_url is not None else os.getenv(SHEET_JSON_URL_ENV, "")).strip()
        self._version_file = _version_file_for(organization_id)
        self._last_result: Optional[SyncResult] = None
        self._version_file.parent.mkdir(parents=True, exist_ok=True)

    # ------------------------------------------------------------------
    # Public
    # ------------------------------------------------------------------

    @property
    def enabled(self) -> bool:
        return bool(self.json_url) and self._collection() is not None

    def status(self) -> Dict[str, Any]:
        info: Dict[str, Any] = {
            "enabled": self.enabled,
            "json_url_set": bool(self.json_url),
            "last_version": self._read_version(),
        }
        if self._last_result:
            info["last_result"] = self._last_result.to_dict()
        info["entry_count"] = self._count_sheet_entries()
        return info

    def refresh(self, force: bool = False) -> SyncResult:
        if not self.json_url:
            return self._fail(
                f"no sheet URL configured for organization {self.organization_id}"
                if self.organization_id
                else "KNOWLEDGE_JSON_URL not set"
            )
        collection = self._collection()
        if collection is None:
            return self._fail("Retrieval engine has no Chroma collection")

        try:
            payload = self._fetch_payload()
        except Exception as e:
            logger.error("Sheet fetch failed: %s", e)
            return self._fail(f"fetch failed: {e}")

        version = str(payload.get("version") or "")
        entries = payload.get("entries") or []
        if not isinstance(entries, list):
            return self._fail("payload.entries is not a list")

        last_version = self._read_version()
        if not force and version and version == last_version:
            result = SyncResult(
                ok=True, version=version, entries_seen=len(entries),
                skipped_unchanged=True,
            )
            self._last_result = result
            logger.info("[sheet_sync] unchanged (version=%s, %d entries)", version, len(entries))
            return result

        # Upsert every entry. Chroma's `upsert` replaces existing ids.
        upserted = self._upsert_entries(collection, entries)

        # Remove sheet-origin entries that are no longer in the payload.
        deleted = self._prune_missing(collection, entries)

        if version:
            self._write_version(version)

        result = SyncResult(
            ok=True, version=version,
            entries_seen=len(entries),
            entries_upserted=upserted,
            entries_deleted=deleted,
        )
        self._last_result = result
        logger.info(
            "[sheet_sync] refreshed: version=%s seen=%d upserted=%d deleted=%d",
            version, len(entries), upserted, deleted,
        )
        return result

    # ------------------------------------------------------------------
    # Internals
    # ------------------------------------------------------------------

    def _collection(self) -> Any:
        return getattr(self.retrieval_engine, "collection", None)

    def _fetch_payload(self) -> Dict[str, Any]:
        req = urllib.request.Request(
            self.json_url,
            headers={"Accept": "application/json", "User-Agent": "alu-chatbot-sheet-sync"},
        )
        with urllib.request.urlopen(req, timeout=FETCH_TIMEOUT_SECONDS) as resp:
            raw = resp.read().decode("utf-8")
        return json.loads(raw)

    def _upsert_entries(self, collection: Any, entries: List[Dict[str, Any]]) -> int:
        ids: List[str] = []
        documents: List[str] = []
        metadatas: List[Dict[str, Any]] = []

        # Track how many times each base entry_id has been seen so we can
        # disambiguate collisions (e.g. two rows with identical titles whose
        # slugs collide). Chroma rejects duplicate ids in a single batch.
        id_counts: Dict[str, int] = {}

        for entry in entries:
            entry_id = str(entry.get("id") or "").strip()
            content = (entry.get("content") or "").strip()
            title = (entry.get("title") or "").strip()
            if not entry_id or (not content and not title):
                continue

            seen = id_counts.get(entry_id, 0)
            id_counts[entry_id] = seen + 1
            # Mutate the id we use downstream — keeps the metadata.doc_id stable
            # for citations but makes the Chroma id unique. We append the
            # 1-indexed sheet row number when available, falling back to a
            # running counter so we never collide within a batch.
            if seen > 0:
                row_suffix = entry.get("row") or seen + 1
                entry_id_for_index = f"{entry_id}--r{row_suffix}"
            else:
                entry_id_for_index = entry_id

            text = self._format_text(title, content, entry)
            chunks = self._chunk(text)
            for i, chunk in enumerate(chunks):
                chunk_id = f"{SHEET_ID_PREFIX}{entry_id_for_index}#{i}"
                ids.append(chunk_id)
                documents.append(chunk)
                metadatas.append(self._build_metadata(entry, chunk_index=i, chunk_total=len(chunks)))

        if not ids:
            return 0

        # Chroma's `upsert` is idempotent; falls back to delete+add if absent.
        try:
            collection.upsert(documents=documents, ids=ids, metadatas=metadatas)
        except AttributeError:
            # Older Chroma versions: emulate upsert with delete-then-add.
            try:
                collection.delete(ids=ids)
            except Exception:
                pass
            collection.add(documents=documents, ids=ids, metadatas=metadatas)
        return len(ids)

    def _prune_missing(self, collection: Any, entries: List[Dict[str, Any]]) -> int:
        try:
            existing = collection.get(where={"origin": "sheet"})
        except Exception as e:
            logger.warning("[sheet_sync] could not list existing sheet docs: %s", e)
            return 0

        existing_ids = set((existing or {}).get("ids") or [])
        keep_ids: set[str] = set()
        for entry in entries:
            entry_id = str(entry.get("id") or "").strip()
            if not entry_id:
                continue
            # Chunks share the same prefix; we don't know chunk_total at this
            # point without re-chunking, so just keep anything that starts with
            # the entry's prefix.
            prefix = f"{SHEET_ID_PREFIX}{entry_id}#"
            for existing_id in existing_ids:
                if existing_id.startswith(prefix):
                    keep_ids.add(existing_id)

        to_delete = [i for i in existing_ids if i not in keep_ids]
        # Also delete ids whose prefix matches an entry but whose chunk index
        # is beyond the current chunk count (handles content shrinking).
        # The upsert above will have rewritten the kept ones already.
        current_chunk_ids: set[str] = set()
        for entry in entries:
            entry_id = str(entry.get("id") or "").strip()
            if not entry_id:
                continue
            content = (entry.get("content") or "").strip()
            title = (entry.get("title") or "").strip()
            text = self._format_text(title, content, entry)
            chunk_count = max(1, len(self._chunk(text)))
            for i in range(chunk_count):
                current_chunk_ids.add(f"{SHEET_ID_PREFIX}{entry_id}#{i}")
        stale = [i for i in existing_ids if i not in current_chunk_ids]
        to_delete = sorted(set(to_delete) | set(stale))

        if not to_delete:
            return 0
        try:
            collection.delete(ids=to_delete)
        except Exception as e:
            logger.warning("[sheet_sync] delete failed: %s", e)
            return 0
        return len(to_delete)

    def _count_sheet_entries(self) -> int:
        collection = self._collection()
        if collection is None:
            return 0
        try:
            result = collection.get(where={"origin": "sheet"})
            return len((result or {}).get("ids") or [])
        except Exception:
            return 0

    # ------------------------------------------------------------------
    # Formatting helpers
    # ------------------------------------------------------------------

    @staticmethod
    def _format_text(title: str, content: str, entry: Dict[str, Any]) -> str:
        dept = (entry.get("department") or "").strip()
        cat = (entry.get("category") or "").strip()
        header_bits = [b for b in [dept, cat] if b]
        header = " — ".join(header_bits)
        parts: List[str] = []
        if header:
            parts.append(f"[{header}]")
        if title:
            parts.append(title)
        if content:
            parts.append(content)
        return "\n".join(parts).strip()

    @staticmethod
    def _build_metadata(entry: Dict[str, Any], chunk_index: int, chunk_total: int) -> Dict[str, Any]:
        # Chroma metadata must be flat scalars.
        return {
            "origin": "sheet",
            "doc_id": str(entry.get("id") or ""),
            "title": (entry.get("title") or "ALU Knowledge Base").strip()[:200],
            "department": (entry.get("department") or "").strip(),
            "tab": (entry.get("tab") or "").strip(),
            "category": (entry.get("category") or "").strip(),
            "source": (entry.get("source_url") or "").strip(),       # legacy key
            "source_url": (entry.get("source_url") or "").strip(),   # canonical
            "last_updated": (entry.get("last_updated") or "").strip(),
            "row": int(entry.get("row") or 0),
            "chunk_index": chunk_index,
            "chunk_total": chunk_total,
        }

    @staticmethod
    def _chunk(text: str) -> List[str]:
        if not text:
            return []
        if len(text) <= MAX_CHARS_PER_CHUNK:
            return [text]
        chunks: List[str] = []
        start = 0
        n = len(text)
        while start < n:
            end = min(start + MAX_CHARS_PER_CHUNK, n)
            if end < n:
                window = text.rfind(". ", start, end)
                if window == -1:
                    window = text.rfind("\n", start, end)
                if window == -1:
                    window = text.rfind(" ", start, end)
                if window != -1 and window > start + 200:
                    end = window + 1
            chunks.append(text[start:end].strip())
            if end >= n:
                break
            start = max(end - CHUNK_OVERLAP, start + 1)
        return [c for c in chunks if c]

    # ------------------------------------------------------------------
    # Version persistence
    # ------------------------------------------------------------------

    def _read_version(self) -> Optional[str]:
        try:
            if self._version_file.exists():
                data = json.loads(self._version_file.read_text())
                v = data.get("version")
                return str(v) if v else None
        except Exception as e:
            logger.warning("[sheet_sync] could not read version file: %s", e)
        return None

    def _write_version(self, version: str) -> None:
        try:
            self._version_file.write_text(json.dumps({
                "version": version,
                "written_at": time.time(),
            }))
        except Exception as e:
            logger.warning("[sheet_sync] could not write version file: %s", e)

    def _fail(self, message: str) -> SyncResult:
        result = SyncResult(ok=False, error=message)
        self._last_result = result
        return result