#!/usr/bin/env python3
"""Convert saved Table Grid REST API responses into a Rowmill import CSV.

Runs offline on the admin's own computer. It reads JSON files you saved from the
Table Grid REST API (one file per issue, named after the issue key, for example
PROJ-12.json) and writes one CSV in the format the Rowmill Table import page reads:

    Issue key,<column 1>,<column 2>,...
    PROJ-12,Bolt,10,2026-03-02,true
    PROJ-12,Nut,4,,false

What it understands (per Table Grid's documented value types):
- {"rows": [{"rowId": ..., "order": n, "columns": {"<columnId>": value}}]}  (Cloud)
- the same with "rows" under another key, or a bare list of rows (DC variants)
- dates / datetimes as Unix milliseconds -> YYYY-MM-DD (UTC)
- multi select lists -> "a, b"; user values {"value": name, ...} -> name
- booleans -> true / false; null -> empty

Column titles: Table Grid returns column ids. Pass --titles with a JSON object
{"columnId": "Title"} (for example built from the grid configuration you can
download in Table Grid) to get readable headers. Without it the ids are used and
you map them on the import page.

Usage:
    python3 tablegrid-json-to-csv.py --in ./grids --out import.csv [--titles titles.json]
          [--date-columns col1,col2]

--date-columns lists column ids whose numbers are dates (Unix ms). Without it, any
13-digit number between 1990 and 2100 is treated as a date.

Status: written from Table Grid's public docs; not yet run against a live
Table Grid export. Check the output before importing.
"""
import argparse
import csv
import datetime as dt
import json
import os
import re
import sys

SKIP = {"rowid", "row_id", "id", "order", "#"}


def find_rows(data):
    """Return the list of row objects from a saved API response."""
    if isinstance(data, list):
        return data
    if isinstance(data, dict):
        for key in ("rows", "data", "values"):
            if isinstance(data.get(key), list):
                return data[key]
    return []


def is_ms_date(v):
    return isinstance(v, (int, float)) and not isinstance(v, bool) and 631152000000 <= v <= 4102444800000


def cell(v, as_date=False):
    """Turn one Table Grid value into plain CSV text."""
    if v is None:
        return ""
    if isinstance(v, bool):
        return "true" if v else "false"
    if (as_date or is_ms_date(v)) and isinstance(v, (int, float)):
        return dt.datetime.fromtimestamp(v / 1000, tz=dt.timezone.utc).strftime("%Y-%m-%d")
    if isinstance(v, dict):
        # users: {"accountId", "value": full name, "avatar"}; select objects: {"value"} or {"title"}
        for key in ("value", "title", "name", "displayName"):
            if key in v:
                return cell(v[key])
        return json.dumps(v, ensure_ascii=False)
    if isinstance(v, list):
        return ", ".join(cell(x) for x in v if x not in (None, ""))
    if isinstance(v, float) and v.is_integer():
        return str(int(v))
    return str(v)


def row_columns(row):
    """Column map of one row; tolerate rows that are already flat dicts."""
    if isinstance(row, dict) and isinstance(row.get("columns"), dict):
        return row["columns"]
    if isinstance(row, dict):
        return {k: v for k, v in row.items() if k.lower() not in SKIP}
    return {}


def convert(in_dir, titles, date_cols):
    # only files named like an issue key (PROJ-12.json); skips titles.json and others
    files = sorted(f for f in os.listdir(in_dir) if re.fullmatch(r"[A-Z][A-Z0-9_]*-\d+\.json", f))
    issues = []
    col_order = []
    for name in files:
        key = os.path.splitext(name)[0]
        with open(os.path.join(in_dir, name), encoding="utf-8-sig") as fh:
            data = json.load(fh)
        rows = find_rows(data)
        rows = sorted(rows, key=lambda r: r.get("order", 0) if isinstance(r, dict) else 0)
        out_rows = []
        for r in rows:
            cols = row_columns(r)
            for c in cols:
                if c.lower() not in SKIP and c not in col_order:
                    col_order.append(c)
            out_rows.append(cols)
        issues.append((key, out_rows))
    header = ["Issue key"] + [titles.get(c, c) for c in col_order]
    lines = []
    for key, rows in issues:
        for cols in rows:
            lines.append([key] + [cell(cols.get(c), c in date_cols) for c in col_order])
    return header, lines, len(issues)


def main(argv=None):
    p = argparse.ArgumentParser(description=__doc__.split("\n")[0])
    p.add_argument("--in", dest="in_dir", required=True, help="folder with <ISSUE-KEY>.json files")
    p.add_argument("--out", required=True, help="CSV file to write")
    p.add_argument("--titles", help="JSON file mapping column id -> title")
    p.add_argument("--date-columns", default="", help="comma-separated column ids holding dates")
    a = p.parse_args(argv)
    titles = {}
    if a.titles:
        with open(a.titles, encoding="utf-8-sig") as fh:
            titles = json.load(fh)
    date_cols = {c.strip() for c in a.date_columns.split(",") if c.strip()}
    header, lines, n = convert(a.in_dir, titles, date_cols)
    with open(a.out, "w", newline="", encoding="utf-8") as fh:
        w = csv.writer(fh)
        w.writerow(header)
        w.writerows(lines)
    print(f"Wrote {len(lines)} rows for {n} issues to {a.out}", file=sys.stderr)


if __name__ == "__main__":
    main()