#!/usr/bin/env python3
"""Lleva a D1 y a content/en las grafias aplicadas al repo, sea cual sea el libro.

Mapea el fichero del repo a su ref:
  srimad-bhagavatam/canto-04/chapter-05/sb-4.5.9.md   -> sb/4/5/9
  sri-caitanya-caritamrta/adi-lila/chapter-07/cc-adi-7.83.md -> cc/adi/7/83
  bhagavad-gita-as-it-is/chapter-02/bg-2.54.md        -> bg/2/54
  <libro-en-prosa>/chapter-9.md                       -> <pref>/9

Sustituye SIEMPRE con limite de palabra, en las tres copias. Un REPLACE de
SQLite es por subcadena y ya corrompio texto una vez ('nothing' -> 'nnothing').

Uso: python3 sincronizar_masivo.py <fichero-de-grafias.json> [--aplicar]
"""
import json, re, sys, os, subprocess, pathlib, collections

HERE = pathlib.Path(__file__).resolve().parent
ASTRO = pathlib.Path('/Users/jaganat/git_projects/astro_vedabase')
q = lambda s: "'" + s.replace("'", "''") + "'"
dec = json.JSONDecoder()

PROSA = {'teachings-of-lord-caitanya': 'tlc', 'nectar-of-devotion': 'nod',
         'krsna-the-supreme-personality-of-godhead': 'kb', 'science-of-self-realization': 'ssr',
         'teachings-of-queen-kunti': 'tqk', 'path-of-perfection': 'pop', 'raja-vidya': 'rv',
         'beyond-birth-and-death': 'bbd', 'elevation-to-krsna-consciousness': 'ekc',
         'perfection-of-yoga': 'poy', 'topmost-yoga-system': 'ttp', 'on-the-way-to-krsna': 'owk',
         'perfect-questions-perfect-answers': 'pqpa', 'light-of-the-bhagavata': 'lob',
         'easy-journey-to-other-planets': 'ej', 'life-comes-from-life': 'lcfl',
         'nectar-of-instruction': 'noi', 'reservoir-of-pleasure': 'krp'}
ESPECIALES = {'tlc': {'preface.md': '0a', 'prologue.md': '0b',
                      'lord-chaitanya-s-mission.md': '0c', 'introduction.md': '0d'},
              'nod': {'preface.md': '0a', 'introduction.md': '0b',
                      'concluding-words.md': 'concluding_words'},
              'kb': {'dedication.md': '0a', 'words-from-apple.md': '0b', 'preface.md': '0c',
                     'introduction-by-a-c-bhaktivedanta-swami-prabhupada.md': '0d'}}


def ref_de(fich):
    n = fich.split('/')[-1]
    m = re.fullmatch(r'sb-(\d+)\.(\d+)\.(.+)\.md', n)
    if m: return f'sb/{m.group(1)}/{m.group(2)}/{m.group(3)}'
    m = re.fullmatch(r'cc-(adi|madhya|antya)-(\d+)\.(.+)\.md', n)
    if m: return f'cc/{m.group(1)}/{m.group(2)}/{m.group(3)}'
    m = re.fullmatch(r'bg-(\d+)\.(.+)\.md', n)
    if m: return f'bg/{m.group(1)}/{m.group(2)}'
    m = re.fullmatch(r'iso-(.+)\.md', n)
    if m: return f'iso/{m.group(1)}'
    libro = fich.split('/')[0]
    pref = PROSA.get(libro)
    if not pref: return None
    m = re.fullmatch(r'chapter-(\d+)\.md', n)
    if m: return f'{pref}/{m.group(1)}'
    esp = ESPECIALES.get(pref, {}).get(n)
    return f'{pref}/{esp}' if esp else None


def d1(sql, js=False):
    args = ['npx', 'wrangler', 'd1', 'execute', 'vedabase-search-db', '--remote']
    if js: args.append('--json')
    args += ['--command', sql]
    for _ in range(3):
        p = subprocess.run(args, capture_output=True, text=True, cwd=str(ASTRO),
                           env={**os.environ, 'CF_API_TOKEN': '', 'CLOUDFLARE_API_TOKEN': ''})
        if js:
            for m in re.finditer(r'\[\s*\{', p.stdout):
                try: o, _ = dec.raw_decode(p.stdout[m.start():])
                except ValueError: continue
                if isinstance(o, list) and o and 'results' in o[0]: return o[0]['results']
        elif 'rows_written' in p.stdout:
            return True
    return [] if js else False


def sub(t, a, b):
    def f(m): return b.capitalize() if m.group(0)[:1].isupper() else b
    return re.subn(rf'\b{re.escape(a)}\b', f, t, flags=re.I)


def main():
    datos = json.load(open(sys.argv[1]))
    aplicar = '--aplicar' in sys.argv
    ok = fallo = ce = sinref = 0
    for fich, pares in sorted(datos.items()):
        ref = ref_de(fich)
        if not ref:
            sinref += 1; continue
        if not aplicar:
            ok += 1; continue
        filas = d1(f"SELECT COALESCE(purport,'') p, COALESCE(translation,'') t, "
                   f"COALESCE(synonyms,'') s FROM verses WHERE ref={q(ref)} AND lang='en'", js=True)
        if not filas:
            fallo += 1; continue
        r = filas[0]; sets = []
        for campo, val in (('purport', r['p']), ('translation', r['t']), ('synonyms', r['s'])):
            nuevo = val; tot = 0
            for a, b in pares:
                nuevo, c = sub(nuevo, a, b); tot += c
            if tot: sets.append(f"{campo}={q(nuevo)}")
        if sets and d1(f"UPDATE verses SET {', '.join(sets)} WHERE ref={q(ref)} AND lang='en'"):
            ok += 1
        elif sets:
            fallo += 1
        p = ASTRO / 'content/en' / (ref + '.md')
        if not p.exists():
            pref = ref.split('/')[0]
            p = ASTRO / 'content/en/books' / pref / (ref.split('/', 1)[1] + '.md')
        if p.exists():
            t = orig = p.read_text(encoding='utf-8')
            for a, b in pares: t, _ = sub(t, a, b)
            if t != orig: p.write_text(t, encoding='utf-8'); ce += 1
    print(f'refs: {ok} ok | {fallo} fallos | {sinref} sin mapear | content/en: {ce}')


if __name__ == '__main__':
    main()