File size: 12,814 Bytes
51b3964
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""
sync_spreadsheet_cases.py β€” Create Drive folders and local case study dirs for
entries in the Ajibade et al. 2022 spreadsheet not already covered by CS1-CS20.

Reads the "Full data_Clean" sheet from:
  https://docs.google.com/spreadsheets/d/1PGvxXlBUP-DFTuaYKhO5xSesHnPGhMlM

Compares Village/City + Country against existing RFR - CS* folders in Drive.
Creates new CS## folders (Drive + local data/raw/ + case_meta.json) for
entries not yet covered.

Usage:
    python3 -m case_study_pipeline.sync_spreadsheet_cases           # dry-run
    python3 -m case_study_pipeline.sync_spreadsheet_cases --execute # create folders

Flags:
    --execute        Actually create folders (default is dry-run only)
    --start-cs NUM   Override starting CS number (default: next after highest existing)
    --skip-drive     Skip Drive folder creation, only create local dirs
"""

from __future__ import annotations

import argparse
import csv
import io
import json
import re
import sys
from pathlib import Path

REPO_ROOT = Path(__file__).resolve().parent.parent
PIPELINE_DIR = Path(__file__).resolve().parent
DRIVE_FOLDERS_FILE = PIPELINE_DIR / "drive_folders.json"
DATA_RAW = REPO_ROOT / "data" / "raw"

SPREADSHEET_ID = "1PGvxXlBUP-DFTuaYKhO5xSesHnPGhMlM"
SHEET_GID = "1698438069"          # Full data_Clean tab
PARENT_FOLDER_ID = "1SEYkC05ICecWn6EleREn6GZaw2CKEWZZ"

# Rows where the city cell contains citation text rather than a place name
_JUNK_PATTERNS = [
    re.compile(r"^\s*pp\.\s*\d+"),
    re.compile(r"Environmental resettlement", re.I),
    re.compile(r"Village\s*/\s*City", re.I),   # header repeat
]

# ---------------------------------------------------------------------------
# Drive helpers (reuse existing auth)
# ---------------------------------------------------------------------------

def _get_service():
    from .onboard_case_study import get_drive_service
    matches = list(REPO_ROOT.glob("client_secret*.json"))
    if not matches:
        print("  [Drive] No client_secret*.json found β€” use --skip-drive to skip Drive creation.")
        return None
    token_file = Path.home() / ".rfr-drive-token.json"
    return get_drive_service(matches[0], token_file)


def _fetch_sheet_csv(service) -> str:
    """Download the Full data_Clean sheet as CSV using the Drive-authorized HTTP session."""
    url = (
        f"https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}"
        f"/export?format=csv&gid={SHEET_GID}"
    )
    http = service._http
    response, content = http.request(url)
    if response.status != 200:
        raise RuntimeError(f"Failed to fetch spreadsheet CSV: HTTP {response.status}")
    return content.decode("utf-8-sig")   # strip BOM if present


def _list_existing_cs_folders(service) -> dict[str, dict]:
    """Return {folder_title: {id, title}} for all RFR - CS* folders in the parent."""
    result = {}
    page_token = None
    while True:
        params = dict(
            q=f"'{PARENT_FOLDER_ID}' in parents and mimeType = 'application/vnd.google-apps.folder'",
            fields="nextPageToken, files(id, name)",
            pageSize=100,
        )
        if page_token:
            params["pageToken"] = page_token
        resp = service.files().list(**params).execute()
        for f in resp.get("files", []):
            if re.match(r"RFR\s*-\s*CS\d+", f["name"], re.I):
                result[f["name"]] = {"id": f["id"], "title": f["name"]}
        page_token = resp.get("nextPageToken")
        if not page_token:
            break
    return result


def _create_drive_folder(service, folder_name: str) -> str:
    meta = {
        "name": folder_name,
        "mimeType": "application/vnd.google-apps.folder",
        "parents": [PARENT_FOLDER_ID],
    }
    f = service.files().create(body=meta, fields="id,name").execute()
    return f["id"]


# ---------------------------------------------------------------------------
# Spreadsheet parsing
# ---------------------------------------------------------------------------

def _is_junk(city: str) -> bool:
    if not city or city.strip() in ("", "N/A", "Village/ City"):
        return True
    return any(p.search(city) for p in _JUNK_PATTERNS)


def _parse_sheet(csv_text: str) -> list[dict]:
    """Return list of {city, province, country} dicts from the sheet."""
    reader = csv.reader(io.StringIO(csv_text))
    headers = None
    rows = []
    for raw in reader:
        if headers is None:
            headers = [h.strip().lower() for h in raw]
            continue
        if len(raw) < 3:
            continue
        city_idx     = next((i for i, h in enumerate(headers) if "village" in h or "city" in h), None)
        province_idx = next((i for i, h in enumerate(headers) if "province" in h or "state" in h), None)
        country_idx  = next((i for i, h in enumerate(headers) if "country" in h), None)
        if city_idx is None or country_idx is None:
            continue
        city     = re.sub(r'\s+', ' ', raw[city_idx]).strip()    if city_idx < len(raw) else ""
        province = re.sub(r'\s+', ' ', raw[province_idx]).strip() if province_idx is not None and province_idx < len(raw) else ""
        country  = re.sub(r'\s+', ' ', raw[country_idx]).strip() if country_idx < len(raw) else ""
        if not country:
            continue
        rows.append({"city": city, "province": province, "country": country})
    return rows


def _deduplicate(rows: list[dict]) -> list[dict]:
    seen = set()
    out = []
    for r in rows:
        key = (r["city"].lower(), r["country"].lower())
        if key in seen:
            continue
        seen.add(key)
        out.append(r)
    return out


# ---------------------------------------------------------------------------
# Matching against existing CS folders
# ---------------------------------------------------------------------------

def _normalise(s: str) -> str:
    s = s.lower()
    s = re.sub(r"[^a-z0-9 ]", " ", s)
    s = re.sub(r"\s+", " ", s).strip()
    # Normalise country name variants
    s = s.replace("united states", "us").replace("united kingdom", "uk").replace("england", "uk")
    return s


def _already_covered(row: dict, existing_titles: list[str]) -> bool:
    """Return True if this city+country is already represented in a CS folder title."""
    city    = _normalise(row["city"])
    country = _normalise(row["country"])
    for title in existing_titles:
        t = _normalise(title)
        city_words = [w for w in city.split() if len(w) > 2]
        if city_words and country and all(w in t for w in city_words) and country[:4] in t:
            return True
    return False


# ---------------------------------------------------------------------------
# Folder/dir creation helpers
# ---------------------------------------------------------------------------

def _next_cs_number(existing_titles: list[str], override: int | None) -> int:
    if override:
        return override
    nums = []
    for t in existing_titles:
        m = re.search(r"CS(\d+)", t, re.I)
        if m:
            nums.append(int(m.group(1)))
    return max(nums, default=20) + 1


def _make_folder_name(cs_num: int, city: str, province: str, country: str) -> str:
    """RFR - CS## City, Province, Country"""
    parts = [p for p in [city if city != "N/A" else "", province, country] if p]
    location = ", ".join(parts)
    return f"RFR - CS{cs_num} {location}"


def _load_drive_folders() -> dict:
    if DRIVE_FOLDERS_FILE.exists():
        return json.loads(DRIVE_FOLDERS_FILE.read_text())
    return {"_comment": "Maps case study IDs to Google Drive folder IDs.",
            "_parent_folder_id": PARENT_FOLDER_ID}


def _save_drive_folders(data: dict) -> None:
    DRIVE_FOLDERS_FILE.write_text(json.dumps(data, indent=2) + "\n")


def _create_local_dirs(cs_id: str, city: str, province: str, country: str) -> None:
    case_dir = DATA_RAW / cs_id
    case_dir.mkdir(parents=True, exist_ok=True)
    (case_dir / "pipeline_output").mkdir(exist_ok=True)
    meta_path = case_dir / "case_meta.json"
    if not meta_path.exists():
        location = f"{city}, {province}" if province and province != "N/A" else city
        meta_path.write_text(json.dumps({
            "case_id": cs_id,
            "name": city if city != "N/A" else location,
            "location": location,
            "country": country,
        }, indent=2))


# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------

def main(argv: list[str] | None = None) -> None:
    parser = argparse.ArgumentParser(
        description="Create Drive + local dirs for spreadsheet case studies not yet in CS1-CS20."
    )
    parser.add_argument("--execute", action="store_true",
                        help="Actually create folders (default: dry-run only)")
    parser.add_argument("--start-cs", type=int, default=None, metavar="NUM",
                        help="Override starting CS number")
    parser.add_argument("--skip-drive", action="store_true",
                        help="Skip Drive folder creation, local dirs only")
    parser.add_argument("--csv", metavar="PATH",
                        help="Path to a locally-downloaded CSV of the Full data_Clean sheet "
                             "(fallback if Drive auth is broken). "
                             "In Google Sheets: File β†’ Download β†’ CSV.")
    args = parser.parse_args(argv)

    dry_run = not args.execute
    if dry_run:
        print("DRY RUN β€” pass --execute to actually create folders.\n")

    # --- Auth & data --------------------------------------------------------
    service = None
    if not args.skip_drive:
        service = _get_service()
        if service is None:
            print("Could not get Drive service. Use --skip-drive to create local dirs only.")
            sys.exit(1)

    print("Fetching spreadsheet data...")
    if args.csv:
        csv_text = Path(args.csv).read_text(encoding="utf-8-sig")
        print(f"  Using local CSV: {args.csv}")
    elif service:
        csv_text = _fetch_sheet_csv(service)
    else:
        print("  No CSV source available. Pass --csv <path> or fix Drive auth.")
        sys.exit(1)

    rows = _parse_sheet(csv_text)
    rows = [r for r in rows if not _is_junk(r["city"])]
    rows = _deduplicate(rows)
    print(f"  {len(rows)} unique location rows after filtering.\n")

    print("Fetching existing CS folders from Drive...")
    existing = _list_existing_cs_folders(service) if service else {}
    existing_titles = list(existing.keys())
    print(f"  {len(existing_titles)} existing CS folders found.\n")

    # --- Compare & plan -----------------------------------------------------
    to_create = []
    covered = []
    for row in rows:
        if _already_covered(row, existing_titles):
            covered.append(row)
        else:
            to_create.append(row)

    print(f"Already covered by existing folders: {len(covered)}")
    print(f"New folders to create:               {len(to_create)}\n")

    if not to_create:
        print("Nothing to do β€” all spreadsheet entries are already covered.")
        return

    next_cs = _next_cs_number(existing_titles, args.start_cs)
    drive_data = _load_drive_folders()

    print(f"{'CS ID':<8} {'Drive folder name':<65} {'Status'}")
    print("-" * 90)

    for i, row in enumerate(to_create):
        cs_num  = next_cs + i
        cs_id   = f"CS{cs_num}"
        folder_name = _make_folder_name(cs_num, row["city"], row["province"], row["country"])

        if dry_run:
            print(f"{cs_id:<8} {folder_name:<65} [dry-run]")
            continue

        # Create Drive folder
        folder_id = ""
        if service and not args.skip_drive:
            try:
                folder_id = _create_drive_folder(service, folder_name)
                drive_data[cs_id] = folder_id
                status = f"Drive βœ“ ({folder_id[:20]}...)"
            except Exception as e:
                status = f"Drive FAILED: {e}"
        else:
            status = "Drive skipped"

        # Create local dirs
        _create_local_dirs(cs_id, row["city"], row["province"], row["country"])
        status += " | Local βœ“"

        print(f"{cs_id:<8} {folder_name:<65} {status}")

    if not dry_run:
        _save_drive_folders(drive_data)
        print(f"\ndrive_folders.json updated ({len(drive_data) - 2} entries).")
        print(f"Local dirs created under data/raw/.")
        print(f"\nNext steps:")
        print(f"  Download papers:  bash download_all_papers.sh")
        print(f"  Run pipeline:     python3 -m case_study_pipeline.run_case_study --case-folder data/raw/CS<NN>")
    else:
        print(f"\nRun with --execute to create {len(to_create)} folders.")


if __name__ == "__main__":
    main()