#!/usr/bin/env python3
"""
Read a Google Sheets spreadsheet and write its data to JSON and CSV files.

Uses only the Python standard library. The sheet must be shared as
"Anyone with the link can view" (or be published), since no login is performed.

Usage:
    python3 sheet_to_json.py            # writes NEDECN-REPEATER-TABLE.json
                                        # writes NEDECN-REPEATER-TABLE.csv  
    python3 sheet_to_json.py "<sheet url>" -o data.json


2026-09-28 KB1B nedecn@kb1b.org

"""

import argparse
import csv
import datetime
import io
import json
import os
import re
import sys
import urllib.error
import urllib.parse
import urllib.request

DEFAULT_URL = (
    "https://docs.google.com/spreadsheets/d/"
    "1HXAw-SUQTo6Y_cHMMVl4eVGGkj5IcAk4qpprWBJJV6c/edit?pli=1&gid=2034472484#gid=2034472484"
)


def build_csv_export_url(sheet_url: str) -> str:
    """Turn a normal Google Sheets URL into a CSV export URL for the same tab."""
    match = re.search(r"/spreadsheets/d/([a-zA-Z0-9_-]+)", sheet_url)
    if not match:
        raise ValueError("Could not find a spreadsheet ID in the URL.")
    sheet_id = match.group(1)

    # gid identifies the specific tab; it may be in the query string or the fragment
    parsed = urllib.parse.urlparse(sheet_url)
    gid = None
    for part in (parsed.query, parsed.fragment):
        values = urllib.parse.parse_qs(part).get("gid")
        if values:
            gid = values[0]
            break

    export_url = f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=csv"
    if gid:
        export_url += f"&gid={gid}"
    return export_url


def download_csv(url: str) -> str:
    request = urllib.request.Request(url, headers={"User-Agent": "Mozilla/5.0"})
    try:
        with urllib.request.urlopen(request, timeout=30) as response:
            content_type = response.headers.get("Content-Type", "")
            raw = response.read()
    except urllib.error.HTTPError as e:
        raise RuntimeError(
            f"HTTP {e.code} while downloading the sheet. Make sure it is shared "
            "as 'Anyone with the link can view'."
        ) from e

    text = raw.decode("utf-8-sig")
    # If the sheet is private, Google returns an HTML login page instead of CSV
    if "text/html" in content_type.lower():
        raise RuntimeError(
            "Google returned an HTML page instead of CSV. The sheet is probably "
            "not shared publicly ('Anyone with the link can view')."
        )
    return text


def csv_to_records(csv_text: str) -> list:
    """Convert CSV text to a list of dicts, using the first row as keys."""
    reader = csv.DictReader(io.StringIO(csv_text))
    return [dict(row) for row in reader]


def main() -> int:
    parser = argparse.ArgumentParser(description="Convert a Google Sheet to JSON.")
    parser.add_argument("url", nargs="?", default=DEFAULT_URL, help="Google Sheets URL")
    default_output = f"NEDECN-REPEATER-TABLE.json"
    parser.add_argument(
        "-o", "--output", default=default_output,
        help=f"Output JSON file (default: {default_output})",
    )
    args = parser.parse_args()

    try:
        export_url = build_csv_export_url(args.url)
        csv_text = download_csv(export_url)
        records = csv_to_records(csv_text)
    except (ValueError, RuntimeError, urllib.error.URLError) as e:
        print(f"Error: {e}", file=sys.stderr)
        return 1

    with open(args.output, "w", encoding="utf-8") as f:
        json.dump(records, f, indent=2, ensure_ascii=False)

    # Write the CSV next to the JSON, using the same base name
    csv_path = os.path.splitext(args.output)[0] + ".csv"
    with open(csv_path, "w", encoding="utf-8", newline="") as f:
        f.write(csv_text)

    print(f"Wrote {len(records)} rows to {args.output} and {csv_path}")
    return 0


if __name__ == "__main__":
    sys.exit(main())