#!/usr/bin/env python3
"""Build the content-team Excel (CMS import) sheet from a mapping JSON.

One row per localization key, in the columns the content team's CMS import expects:

  Channel | Property Group | Property Module | Key | EN Value | TR Value | AR Value | Anotation EN | Anotation TR

- Channel  -  the delivery channel these strings belong to ("Mobile" by default; --channel).
- Property Group  -  deduced from the key namespace; describe your CMS buckets with --taxonomy, or let a
                   row override it outright with a "propertyGroup" field.
- Property Module  -  deduced too (Fields / Validation / Common / <domain>); overridable with "propertyModule".
- Key  -  our NEW (redesign) key, verbatim (row.newKey). "(owned by ...)" component tags are stripped.
- EN/TR/AR Value  -  our SUGGESTED values (row.new.en / .tr / .ar  -  resolve-new-values.py output).
- Anotation EN/TR  -  the content team's ACTUAL copy, the Figma CMS annotations (row.cms.en / .tr).

Writes a real `.xlsx` (an OOXML zip built with the Python stdlib only  -  no pip, no network, same trick
build-artifact.py uses for .docx). `--csv` also emits a UTF-8 CSV alongside it (a terminal-friendly fallback
when Excel is not needed). `--csv-only` skips the xlsx.

Usage:
  python3 build-spreadsheet.py <mapping>.json --out <dir> [--slug <slug>] [--channel Mobile]
      [--taxonomy cms-taxonomy.json] [--csv|--csv-only]
"""
import argparse
import csv
import html
import io
import json
import os
import sys
import zipfile

COLUMNS = ["Channel", "Property Group", "Property Module", "Key",
           "EN Value", "TR Value", "AR Value", "Anotation EN", "Anotation TR"]

# Approx display widths (chars) per column, for the xlsx.
COL_WIDTHS = [10, 22, 20, 40, 26, 26, 26, 26, 26]


def _text(v):
    return "" if v in (None, "") else str(v)


def slugify(name):
    slug = "".join(ch if ch.isalnum() else "-" for ch in _text(name).lower()).strip("-")
    while "--" in slug:
        slug = slug.replace("--", "-")
    return slug or "screen"


def clean_key(row):
    """The Key column = our new key, without a trailing '(owned by ...)' component tag."""
    k = _text(row.get("newKey"))
    i = k.find("(owned by")
    return (k[:i].strip() if i >= 0 else k).strip()


# --- Property Group / Module deduction ------------------------------------------------
# A CMS typically files keys into a group (shared building blocks vs feature areas) and a
# module inside it. The deduction below is a neutral default; every CMS names its buckets
# differently, so point --taxonomy at a JSON file to describe yours:
#
#   {
#     "errors":     {"group": "Common-Errors",  "module": ""},
#     "lookups":    {"group": "Common-Lookup",  "module": ""},
#     "validation": {"group": "Core",           "module": "Validation"},
#     "fields":     {"group": "Core",           "module": "Fields"},
#     "fallback":   {"group": "Core",           "module": "Common"},
#     "domainGroup": "Domains",
#     "domains": {"auth": ["login", "signup", "otp"], "billing": ["invoice", "card"]}
#   }
#
# Any subset may be given; unspecified parts keep the defaults. A mapping row always wins
# over deduction via explicit "propertyGroup" / "propertyModule".

DEFAULT_TAXONOMY = {
    "errors": {"group": "Errors", "module": ""},
    "lookups": {"group": "Lookups", "module": ""},
    "validation": {"group": "Core", "module": "Validation"},
    "fields": {"group": "Core", "module": "Fields"},
    "fallback": {"group": "Core", "module": "Common"},
    "domainGroup": "Domains",
    "domains": {
        "auth": ["auth", "login", "signin", "sign-in", "register", "signup", "otp", "password"],
        "account": ["account", "profile", "settings", "preferences"],
        "checkout": ["checkout", "cart", "payment", "billing", "order"],
        "search": ["search", "filter", "results"],
    },
}

_TAXONOMY = DEFAULT_TAXONOMY


def load_taxonomy(path):
    """Merge a user taxonomy file over the neutral defaults (one level deep)."""
    if not path:
        return dict(DEFAULT_TAXONOMY)
    with open(path, encoding="utf-8") as fh:
        user = json.load(fh)
    if not isinstance(user, dict):
        raise SystemExit(f"--taxonomy must be a JSON object, got {type(user).__name__}")
    merged = dict(DEFAULT_TAXONOMY)
    for k, v in user.items():
        if isinstance(v, dict) and isinstance(merged.get(k), dict):
            merged[k] = {**merged[k], **v}
        else:
            merged[k] = v
    return merged


def _bucket(name, group, module):
    b = _TAXONOMY.get(name) or {}
    return (group or _text(b.get("group")), module or _text(b.get("module")))


def _first_segment(key):
    for sep in (".", "-", "_", "/"):
        if sep in key:
            return key.split(sep, 1)[0]
    return key


def deduce_group_module(row):
    """Return (group, module) for a row, honoring explicit overrides first."""
    group = _text(row.get("propertyGroup"))
    module = _text(row.get("propertyModule"))
    if group and module:
        return group, module

    key = clean_key(row)
    low = key.lower()

    # Cross-cutting pools first.
    if "error" in low or "errormessage" in low:
        return _bucket("errors", group, module)
    if "lookup" in low:
        return _bucket("lookups", group, module)

    # Core building blocks.
    if "validation" in low or "invalid" in low or low.endswith(".valid"):
        return _bucket("validation", group, module)
    if any(w in low for w in ("field", "placeholder", "hint", "label")):
        return _bucket("fields", group, module)

    # Feature domains, matched on the first namespace segment then anywhere in the key.
    first = _first_segment(low)
    for dom, hints in (_TAXONOMY.get("domains") or {}).items():
        if first in hints or any(h in low for h in hints):
            return (group or _text(_TAXONOMY.get("domainGroup")), module or dom)

    return _bucket("fallback", group, module)


def row_to_record(row, channel):
    group, module = deduce_group_module(row)
    new = row.get("new") or {}
    cms = row.get("cms") or {}
    return [
        channel,
        group,
        module,
        clean_key(row),
        _text(new.get("en")),
        _text(new.get("tr")),
        _text(new.get("ar")),
        _text(cms.get("en")),
        _text(cms.get("tr")),
    ]


def build_records(mapping, channel):
    return [row_to_record(r, channel) for r in mapping.get("rows", [])]


# --- xlsx writer (stdlib only: an .xlsx is a zip of OOXML parts) -----------------------

def _xesc(s):
    return html.escape(_text(s), quote=True)


def _col_letter(idx):  # 0-based -> A, B, ... Z, AA, ...
    idx += 1
    out = ""
    while idx:
        idx, rem = divmod(idx - 1, 26)
        out = chr(65 + rem) + out
    return out


def _sheet_xml(records):
    cols = "".join(
        f'<col min="{i+1}" max="{i+1}" width="{w}" customWidth="1"/>'
        for i, w in enumerate(COL_WIDTHS)
    )

    def cell(col_idx, row_idx, value, style):
        ref = f"{_col_letter(col_idx)}{row_idx}"
        return (f'<c r="{ref}" t="inlineStr" s="{style}">'
                f'<is><t xml:space="preserve">{_xesc(value)}</t></is></c>')

    rows_xml = []
    header = "".join(cell(i, 1, COLUMNS[i], 1) for i in range(len(COLUMNS)))
    rows_xml.append(f'<row r="1">{header}</row>')
    for ri, rec in enumerate(records, start=2):
        cells = "".join(cell(i, ri, rec[i], 0) for i in range(len(COLUMNS)))
        rows_xml.append(f'<row r="{ri}">{cells}</row>')

    last = f"{_col_letter(len(COLUMNS)-1)}{max(1, len(records)+1)}"
    return (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
        f'<dimension ref="A1:{last}"/>'
        '<sheetViews><sheetView workbookViewId="0">'
        '<pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>'
        '</sheetView></sheetViews>'
        '<sheetFormatPr defaultRowHeight="15"/>'
        f'<cols>{cols}</cols>'
        f'<sheetData>{"".join(rows_xml)}</sheetData>'
        f'<autoFilter ref="A1:{_col_letter(len(COLUMNS)-1)}1"/>'
        '</worksheet>'
    )


_STYLES_XML = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
    '<fonts count="2">'
    '<font><sz val="11"/><name val="Calibri"/></font>'
    '<font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font>'
    '</fonts>'
    '<fills count="3">'
    '<fill><patternFill patternType="none"/></fill>'
    '<fill><patternFill patternType="gray125"/></fill>'
    '<fill><patternFill patternType="solid"><fgColor rgb="FF4472C4"/><bgColor indexed="64"/></patternFill></fill>'
    '</fills>'
    '<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>'
    '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
    '<cellXfs count="2">'
    '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">'
    '<alignment vertical="top" wrapText="1"/></xf>'
    '<xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyAlignment="1">'
    '<alignment vertical="center" wrapText="1"/></xf>'
    '</cellXfs>'
    '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
    '</styleSheet>'
)


def write_xlsx(records, out_path, sheet_name="Localization"):
    safe = "".join(c for c in sheet_name if c not in r'[]:*?/\\')[:31] or "Sheet1"
    parts = {
        "[Content_Types].xml":
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
            '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
            '<Default Extension="xml" ContentType="application/xml"/>'
            '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
            '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
            '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
            '</Types>',
        "_rels/.rels":
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
            '</Relationships>',
        "xl/workbook.xml":
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
            'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
            f'<sheets><sheet name="{_xesc(safe)}" sheetId="1" r:id="rId1"/></sheets></workbook>',
        "xl/_rels/workbook.xml.rels":
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'
            '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
            '</Relationships>',
        "xl/styles.xml": _STYLES_XML,
        "xl/worksheets/sheet1.xml": _sheet_xml(records),
    }
    with zipfile.ZipFile(out_path, "w", zipfile.ZIP_DEFLATED) as z:
        for name, data in parts.items():
            z.writestr(name, data)


def write_csv(records, out_path):
    with open(out_path, "w", newline="", encoding="utf-8-sig") as f:
        w = csv.writer(f)
        w.writerow(COLUMNS)
        w.writerows(records)


def main():
    ap = argparse.ArgumentParser(description=__doc__,
                                 formatter_class=argparse.RawDescriptionHelpFormatter)
    ap.add_argument("mapping", help="path to the mapping JSON (- for stdin)")
    ap.add_argument("--out", default=".", help="output directory")
    ap.add_argument("--slug", help="output basename (default: slug of mapping.screen)")
    ap.add_argument("--channel", default="Mobile", help="Channel column value (default: Mobile)")
    ap.add_argument("--taxonomy", help="JSON file describing your CMS group/module buckets "
                                      "(merged over the neutral defaults; see the module docstring)")
    ap.add_argument("--csv", action="store_true", help="also write a <slug>.localization.csv")
    ap.add_argument("--csv-only", action="store_true", help="write only the CSV, skip the xlsx")
    args = ap.parse_args()

    global _TAXONOMY
    _TAXONOMY = load_taxonomy(args.taxonomy)

    raw = sys.stdin.read() if args.mapping == "-" else open(args.mapping, encoding="utf-8").read()
    mapping = json.loads(raw)

    slug = args.slug or slugify(mapping.get("screen"))
    os.makedirs(args.out, exist_ok=True)
    records = build_records(mapping, args.channel)

    written = []
    if not args.csv_only:
        xlsx_path = os.path.join(args.out, f"{slug}.localization.xlsx")
        write_xlsx(records, xlsx_path)
        written.append(xlsx_path)
    if args.csv or args.csv_only:
        csv_path = os.path.join(args.out, f"{slug}.localization.csv")
        write_csv(records, csv_path)
        written.append(csv_path)

    print(json.dumps({
        "slug": slug,
        "rows": len(records),
        "columns": COLUMNS,
        "channel": args.channel,
        "written": [os.path.basename(p) for p in written],
    }, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()