# -*- coding: utf-8 -*-
"""Translator handoff as a formatted workbook, replacing the bare CSV.

Same layout as the marketing site's beyond-ja-translations spreadsheets
(bigscreenvr-site/beyond-ja-translations-update-v4.xlsx), so the translator
sees one familiar format across both projects:

  - dark header, frozen; grey banner row per surface with a needs-count
  - ID / Where / Status / English / Japanese / Notes
  - yellow Japanese cell = needs writing, blue = draft to review
  - a "How to use" tab with the status legend

  python scripts/strings-xlsx.py export            -> content/translations-ja.xlsx
  python scripts/strings-xlsx.py import <file.xlsx> [--english] [--all]

Import mirrors strings-csv.mjs semantics: only the Japanese column is read
(--english also applies owner edits to the English column, flagging stale
Japanese for review); a flagged row that comes back unchanged stays flagged -
a round trip is not a review. The translator's Notes column is captured into
strings.json (translator_note) so questions are never lost with the file.
"""
import io, json, sys, collections
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

SRC = 'content/strings.json'
OUT = 'content/translations-ja.xlsx'

HEADER_FILL = PatternFill('solid', start_color='1A1A1E')
BANNER_FILL = PatternFill('solid', start_color='3D3F45')
YELLOW = PatternFill('solid', start_color='FFF2A8')   # needs writing
BLUE = PatternFill('solid', start_color='E8F0FE')     # draft - review freely
WHITE_B = Font(name='Arial', bold=True, size=10, color='FFFFFF')
BANNER_F = Font(name='Arial', bold=True, size=11, color='FFFFFF')
BODY = Font(name='Arial', size=10)
BODY_I = Font(name='Arial', size=10, italic=True, color='70777A')
WRAP = Alignment(wrap_text=True, vertical='top')

# surface -> (banner label, sort order). Everything else falls to the end.
SURFACES = {
    'header':             ('HEADER — chrome labels + marquee config', 1),
    'header_nav':         ('HEADER — navigation dropdown labels', 2),
    'marquee_string':     ('MARQUEE — the scrolling ticker lines', 3),
    'product_misc':       ('PRODUCT PAGE — configurator UI strings', 4),
    'product_story':      ('PRODUCT PAGE — below-the-fold story band', 5),
    'addon_type':         ('CONFIGURATOR — option group headings (applied by the other workstream)', 6),
    'addon_group':        ('CONFIGURATOR — option group extras (applied by the other workstream)', 7),
    'variant metafields': ('CONFIGURATOR — option card texts (variant metafields, applied per variant)', 7.5),
    'rx_insert_form':     ('PRESCRIPTION FORM — LensAdvizor overlay', 8),
    'cart':               ('CART — drawer and cart page', 9),
    'footer':             ('FOOTER — link labels + legal', 10),
    'shipping_milestone': ('SHIPPING — milestone labels', 11),
    'confirmation_email': ('EMAIL — order confirmation', 12),
    'email_sections':     ('EMAIL — section blocks', 13),
    'locale file':        ('INTERFACE — small UI + screen-reader labels (locale files)', 14),
}


def load_strings():
    with io.open(SRC, encoding='utf-8') as fh:
        return json.load(fh)


def surface_of(row):
    if row.get('target') == 'metafield':
        return 'variant metafields'
    return row.get('metaobject') or 'locale file'


def status_of(row):
    if row.get('ja_intentionally_blank'):
        return 'KEEP ENGLISH'
    if not row.get('ja'):
        return 'TRANSLATE'
    if row.get('needs_review'):
        if row.get('ja_source') == 'machine':
            return 'REVIEW DRAFT'
        return 'FROM OLD SITE' if row.get('ja_source') == 'builder-archive' else 'COPY CHANGED'
    if row.get('ja_source') == 'shopify':
        return 'FROM STORE'
    return 'DONE'


def note_of(row):
    n = []
    if row.get('owned_elsewhere'):
        n.append('Translate normally; applied by the other workstream.')
    if row.get('target') == 'locale' and '{{' in (row.get('en') or ''):
        n.append('Keep {{ placeholders }} exactly as written.')
    if (row.get('field') or '').endswith('_value'):
        n.append('A measurement - normally leave the numerals unchanged.')
    return ' '.join(n)


# Fields that exist to drive the system, not to be read by a customer.
# Verified against the theme (2026-08-27): addon_type renders ONLY header and
# description; prop_key is the cart line-item property key and ui_style /
# cart_presentation are render-mode enums - a translated value breaks the
# section or the cart. The .title fields on addon_type, addon_group and
# marquee_string are admin display names the theme never reads (group headings
# come from addon_type.header, the ticker renders marquee_string.content).
SYSTEM_FIELDS = {
    'addon_type': {'prop_key', 'ui_style', 'cart_presentation', 'title'},
    'addon_group': {'title'},
    'marquee_string': {'title'},
}


def is_config(i, r):
    """Timing values and placeholders are settings, not copy - a translator
    staring at a row whose English is '18' can only be confused by it."""
    f = r.get('field') or ''
    if f.startswith(('dev_', 'marquee_seconds_')):
        return True
    if f in SYSTEM_FIELDS.get(r.get('metaobject') or '', ()):
        return True
    if (r.get('en') or '').strip() in ('---', '--', '-'):
        return True
    # Reference lists, not text: the marquee_strings_* fields hold JSON arrays
    # of Metaobject gids naming WHICH ticker entries each region plays and in
    # what order. The sentences themselves are separate marquee_string rows.
    # Japanese pasted over one of these would break the ticker for a region.
    if 'gid://shopify/' in (r.get('en') or ''):
        return True
    en = (r.get('en') or '').strip()
    # More machinery the store discovery surfaced: admin entry names ("LIVE"),
    # URLs, shipping_milestone titles that are routing rules ("qty 0 -> Q2
    # 2025", the customer text is set_msg), a "..." test ticker entry, and
    # email bodies stored as Shopify rich-text JSON - handing a translator a
    # JSON blob invites broken structure, so those go through Translate &
    # Adapt instead.
    if f == 'metaobject_name' or f.endswith('_url'):
        return True
    if r.get('metaobject') == 'shipping_milestone' and f == 'title':
        return True
    if en in ('...', '…'):
        return True
    if en.startswith('{"type":"root"'):
        return True
    return False


def export():
    doc = load_strings()
    rows = [(i, r) for i, r in doc['strings'].items()
            if r.get('en') and not is_config(i, r)]
    groups = collections.OrderedDict()
    for i, r in sorted(rows, key=lambda x: (SURFACES.get(surface_of(x[1]), ('', 99))[1], x[0])):
        groups.setdefault(surface_of(r), []).append((i, r))

    wb = Workbook()
    ws = wb.active
    ws.title = 'Translations'
    widths = {'A': 40, 'B': 26, 'C': 15, 'D': 62, 'E': 62, 'F': 30}
    for c, w in widths.items():
        ws.column_dimensions[c].width = w
    heads = ['ID (do not edit)', 'Where', 'Status', 'English (do not edit)',
             'Japanese  ←  EDIT THIS COLUMN', 'Notes (optional)']
    for c, h in enumerate(heads, 1):
        cell = ws.cell(row=1, column=c, value=h)
        cell.font = WHITE_B; cell.fill = HEADER_FILL; cell.alignment = WRAP
    ws.freeze_panes = 'A2'

    r = 2
    need_total = 0
    for surf, items in groups.items():
        label = SURFACES.get(surf, (surf.upper(), 99))[0]
        need = sum(1 for _, x in items if status_of(x) in ('TRANSLATE', 'COPY CHANGED'))
        draft = sum(1 for _, x in items if status_of(x) == 'REVIEW DRAFT')
        spot = sum(1 for _, x in items if status_of(x) == 'FROM OLD SITE')
        need_total += need + draft
        parts = ['%d strings' % len(items)]
        if need: parts.append('%d need translating' % need)
        if draft: parts.append('%d machine drafts to review' % draft)
        if spot: parts.append('%d to spot-check' % spot)
        if not (need or draft or spot): parts.append('nothing missing')
        banner = ws.cell(row=r, column=1, value='%s   (%s)' % (label, ', '.join(parts)))
        banner.font = BANNER_F; ws.row_dimensions[r].height = 22
        for c in range(1, 7):
            ws.cell(row=r, column=c).fill = BANNER_FILL
        r += 1
        for i, x in items:
            st = status_of(x)
            vals = [i, x.get('where') or x.get('key') or '', st, x.get('en'),
                    x.get('ja') or '', note_of(x)]
            for c, v in enumerate(vals, 1):
                cell = ws.cell(row=r, column=c, value=v)
                cell.font = BODY_I if c == 6 else BODY
                cell.alignment = WRAP
            if st in ('TRANSLATE', 'COPY CHANGED'):
                ws.cell(row=r, column=5).fill = YELLOW
            elif st in ('FROM OLD SITE', 'REVIEW DRAFT'):
                ws.cell(row=r, column=5).fill = BLUE
            r += 1

    ht = wb.create_sheet('How to use')
    ht.column_dimensions['A'].width = 26
    ht.column_dimensions['B'].width = 92
    lines = [
        ('Bigscreen store — Japanese translation handoff', None),
        ('', None),
        ('Edit ONLY the Japanese column (yellow = missing, blue = recovered draft to review).', None),
        ('Do not edit the ID or English columns — the import matches on the ID.', None),
        ('Leave a Japanese cell EMPTY to keep that string English on the store.', None),
        ('Anything you want to ask or flag: write it in the Notes column, it comes back to Max.', None),
        ('Rows are grouped by surface: each grey banner names the part of the store', None),
        ('the strings below it belong to, plus how many still need translating.', None),
        ('Filtering by Status hides the grey banners — clear the filter to see them again.', None),
        ('', None),
        ('Statuses:', None),
        ('TRANSLATE', 'No Japanese yet — needs writing (yellow cells).'),
        ('COPY CHANGED', 'The English moved since the last file — please translate fresh (yellow cells).'),
        ('REVIEW DRAFT', 'Machine-drafted so the page reads Japanese today - please correct freely, every word needs your eyes (blue cells).'),
        ('FROM OLD SITE', "The previous store page's own Japanese — the English was reworded during the rebuild, please check it still matches (blue cells)."),
        ('FROM STORE', 'Already live on the Japanese store — spot-check only.'),
        ('KEEP ENGLISH', 'Deliberately English or deliberately empty (product names, one half of a before/after pair).'),
        ('DONE', 'Confirmed in an earlier pass.'),
        ('', None),
        ('Not in this file:', 'Spec numerals stay as digits; product names, SteamVR, VRChat and Bigscreen stay in Latin script.'),
        ('', None),
        ('Example:', None),
        ('English:  Order now', None),
        ('Japanese:  今すぐ注文', None),
    ]
    for idx, (a, b) in enumerate(lines, 1):
        ca = ht.cell(row=idx, column=1, value=a)
        ca.font = Font(name='Arial', bold=(idx == 1 or a.endswith(':')), size=13 if idx == 1 else 10)
        if b:
            cb = ht.cell(row=idx, column=2, value=b)
            cb.font = BODY; cb.alignment = WRAP

    wb.save(OUT)
    print('wrote %s' % OUT)
    print('  %d rows across %d surfaces, %d need the translator' %
          (len(rows), len(groups), need_total))


def do_import(path, take_english, take_all):
    doc = load_strings()
    wb = load_workbook(path)
    ws = wb['Translations']
    changed = ja_same_flagged = en_changed = notes = 0
    unknown = []
    for row in ws.iter_rows(min_row=2, values_only=True):
        rid = (row[0] or '').strip() if row[0] else ''
        if not rid or rid not in doc['strings']:
            if rid and not rid.startswith(('HEADER', 'MARQUEE', 'PRODUCT', 'CONFIGURATOR',
                                           'PRESCRIPTION', 'CART', 'FOOTER', 'SHIPPING',
                                           'EMAIL', 'INTERFACE')):
                if rid: unknown.append(rid)
            continue
        tgt = doc['strings'][rid]
        en = (row[3] or '').strip() if row[3] else ''
        ja = (row[4] or '').strip() if row[4] else ''
        note = (row[5] or '').strip() if row[5] else ''
        if note and note != note_of(tgt):
            tgt['translator_note'] = note
            notes += 1
        if take_english and en and en != tgt.get('en'):
            tgt['en'] = en
            tgt['en_source'] = 'owner'
            if tgt.get('ja'):
                tgt['needs_review'] = True
            en_changed += 1
        if not ja:
            continue
        # Excel normalizes line endings inside a cell, so a multi-paragraph
        # HTML value comes back byte-different while being the same text. A
        # byte compare then re-attributes Shopify's own Japanese to the
        # translator. Compare normalized; store the original untouched.
        norm = lambda t: '\n'.join(
            x.strip() for x in (t or '').replace('\r\n', '\n').split('\n')).strip()
        if norm(tgt.get('ja')) == norm(ja):
            # unchanged is not a review - see strings-csv.mjs for the reasoning
            if tgt.get('needs_review'):
                ja_same_flagged += 1
            continue
        tgt['ja'] = ja
        tgt['ja_source'] = 'translator'
        if not (take_english and en_changed):
            tgt.pop('needs_review', None)
        changed += 1
    with io.open(SRC, 'w', encoding='utf-8') as fh:
        json.dump(doc, fh, ensure_ascii=False, indent=2)
        fh.write('\n')
    print('updated %s' % SRC)
    print('  %d translations applied, %d notes captured' % (changed, notes))
    if ja_same_flagged:
        print('  %d flagged rows came back unchanged and stay flagged' % ja_same_flagged)
    if take_english:
        print('  %d English values changed (push with sync-strings --plan)' % en_changed)
    if unknown:
        print('  %d unknown ids ignored:' % len(unknown))
        for u in unknown[:8]:
            print('    ' + u)
    missing = sum(1 for v in doc['strings'].values()
                  if not v.get('ja') and not v.get('ja_intentionally_blank'))
    print('  still missing japanese: %d' % missing)


if __name__ == '__main__':
    args = sys.argv[1:]
    if args[:1] == ['export']:
        export()
    elif args[:1] == ['import'] and len(args) >= 2:
        do_import(args[1], '--english' in args, '--all' in args)
    else:
        print('usage: python scripts/strings-xlsx.py export | import <file.xlsx> [--english]')
        sys.exit(1)