#!/usr/bin/env python3
"""
fix_imported_k2_metas.py

Asigna metas, categorías y Polylang a los posts importados por import_new_k2_items.py.
Los posts WP ya existen (IDs 43914-44082); este script solo añade los metadatos.

Mapping: wp_id = k2_id + 26040
"""

import json
import subprocess
import sys
import re

# ── Config ─────────────────────────────────────────────────────────────────────
JOOMLA_SSH_HOST = "134.0.10.170"
JOOMLA_SSH_USER = "feadulta"
JOOMLA_SSH_PASS = "6Rm2qOF@eundwpda"
JOOMLA_DB_HOST  = "127.0.0.1"
JOOMLA_DB_USER  = "fejoomla3"
JOOMLA_DB_PASS  = "5FF-}5^[>7^pK4W9"
JOOMLA_DB_NAME  = "fejoomla3"

WP_DOCKER   = "wordpress-mysql"
WP_DB_USER  = "wordpress_user"
WP_DB_PASS  = "wordpress_pass"
WP_DB_NAME  = "wordpress_db"

LAST_K2_ID  = 17873
WP_ID_OFFSET = 26040  # wp_id = k2_id + WP_ID_OFFSET

CAT_FEADULTA   = 71
CAT_ARTICULOS  = 1650
CAT_EVANGELIO  = 1647
CAT_EUCARISTIA = 1648
LANG_MAP = {1: 'es', 2: 'en', 3: 'fr', 4: 'it', 5: 'pt'}
DOMINGO_RE = r'DOMINGO|SEMANA SANTA|SEMANA DE PASCUA|PENTECOST|NAVIDAD|EPIFAN'

DRY_RUN = '--dry-run' in sys.argv

# ── Helpers ────────────────────────────────────────────────────────────────────

def wp_execute(sql: str):
    if DRY_RUN:
        print(f"  [DRY] {sql[:100]}")
        return
    cmd = ['docker', 'exec', WP_DOCKER,
           'mysql', '-u', WP_DB_USER, f'-p{WP_DB_PASS}', WP_DB_NAME,
           '--default-character-set=utf8mb4', '-e', sql]
    result = subprocess.run(cmd, capture_output=True, text=True)
    if result.returncode != 0:
        err = result.stderr.replace('mysql: [Warning] Using a password on the command line interface can be insecure.\n', '')
        if err.strip():
            print(f"  [ERR] {err.strip()[:200]}", file=sys.stderr)


def wp_mysql(query: str) -> list[dict]:
    cmd = ['docker', 'exec', WP_DOCKER,
           'mysql', '-u', WP_DB_USER, f'-p{WP_DB_PASS}', WP_DB_NAME,
           '--default-character-set=utf8mb4', '-B', '-e', query]
    result = subprocess.run(cmd, capture_output=True, text=True, encoding='utf-8')
    if result.returncode != 0:
        return []
    lines = result.stdout.strip().split('\n')
    if len(lines) < 2:
        return []
    headers = lines[0].split('\t')
    return [dict(zip(headers, line.split('\t'))) for line in lines[1:] if line]


def esc(s: str) -> str:
    return s.replace('\\', '\\\\').replace("'", "\\'")


def unhex(val: str) -> str:
    if not val or val == 'NULL':
        return ''
    try:
        return bytes.fromhex(val).decode('utf-8', errors='replace')
    except Exception:
        return val


def parse_extra_fields(ef_json: str) -> dict:
    result = {'lang_val': None, 'has_libro': False}
    if not ef_json:
        return result
    try:
        fields = json.loads(ef_json)
    except json.JSONDecodeError:
        return result
    for f in fields:
        fid = str(f.get('id', ''))
        val = f.get('value')
        if fid == '16' and val is not None:
            try:
                result['lang_val'] = int(val)
            except (ValueError, TypeError):
                pass
        elif fid == '9':
            result['has_libro'] = True
    return result


def determine_categories(ef: dict, title: str) -> list[int]:
    lang = ef.get('lang_val')
    es = (lang == 1 or lang is None)
    cats = [CAT_FEADULTA]
    if es and ef.get('has_libro'):
        cats.append(CAT_EVANGELIO)
    elif es and re.search(DOMINGO_RE, title, re.IGNORECASE):
        cats.append(CAT_EUCARISTIA)
    else:
        cats.append(CAT_ARTICULOS)
    return cats


# ── Main ───────────────────────────────────────────────────────────────────────

def main():
    print(f"=== Fix metas/cats K2 items > {LAST_K2_ID} {'[DRY RUN]' if DRY_RUN else '[LIVE]'} ===\n")

    # Cargar term_taxonomy_ids
    term_ids = [CAT_FEADULTA, CAT_ARTICULOS, CAT_EVANGELIO, CAT_EUCARISTIA]
    tt_ids = {}
    rows = wp_mysql(f"SELECT term_id, term_taxonomy_id FROM wp_term_taxonomy WHERE term_id IN ({','.join(map(str,term_ids))}) AND taxonomy='category'")
    for r in rows:
        tt_ids[int(r['term_id'])] = int(r['term_taxonomy_id'])
    print(f"TT IDs categorías: {tt_ids}")

    pl_ids = {}
    rows = wp_mysql("SELECT t.slug, tt.term_taxonomy_id FROM wp_terms t JOIN wp_term_taxonomy tt ON tt.term_id=t.term_id WHERE tt.taxonomy='language' AND t.slug IN ('es','en','fr','it','pt')")
    for r in rows:
        pl_ids[r['slug']] = int(r['term_taxonomy_id'])
    print(f"Polylang TT IDs: {pl_ids}")

    # Verificar que los WP posts existen
    rows = wp_mysql(f"SELECT COUNT(*) n FROM wp_posts WHERE ID BETWEEN {LAST_K2_ID+WP_ID_OFFSET+1} AND (SELECT MAX(ID) FROM wp_posts)")
    print(f"Posts WP a procesar (aprox): {rows[0]['n'] if rows else '?'}")

    # Obtener K2 items desde Joomla
    print("\nObteniendo K2 items de Joomla prod...")
    query = (
        f"SELECT id, HEX(title) title, HEX(extra_fields) extra_fields "
        f"FROM ew4r_k2_items WHERE published=1 AND id > {LAST_K2_ID} ORDER BY id;"
    )
    mysql_cmd = (
        f"mysql -h {JOOMLA_DB_HOST} -u {JOOMLA_DB_USER} "
        f"-p'{JOOMLA_DB_PASS}' {JOOMLA_DB_NAME} "
        f"--default-character-set=utf8mb4 -B"
    )
    cmd = ['sshpass', '-p', JOOMLA_SSH_PASS, 'ssh', f'{JOOMLA_SSH_USER}@{JOOMLA_SSH_HOST}', mysql_cmd]
    result = subprocess.run(cmd, input=query, capture_output=True, text=True, encoding='utf-8')
    if result.returncode != 0:
        print(f"ERROR: {result.stderr[:300]}")
        sys.exit(1)

    lines = result.stdout.strip().split('\n')
    headers = lines[0].split('\t')
    items = [dict(zip(headers, line.split('\t'))) for line in lines[1:] if line]
    print(f"Items obtenidos: {len(items)}")

    stats = {'ok': 0, 'skip': 0}

    for item in items:
        k2_id  = int(item['id'])
        wp_id  = k2_id + WP_ID_OFFSET
        title  = unhex(item.get('title', ''))
        ef_raw = unhex(item.get('extra_fields', ''))
        ef     = parse_extra_fields(ef_raw)
        lang   = LANG_MAP.get(ef.get('lang_val'), 'es')
        cats   = determine_categories(ef, title)

        # Verificar que el WP post existe
        existing = wp_mysql(f"SELECT ID FROM wp_posts WHERE ID={wp_id} LIMIT 1")
        if not existing:
            print(f"  [SKIP] WP post ID={wp_id} no encontrado (k2={k2_id})")
            stats['skip'] += 1
            continue

        print(f"  [{k2_id}→{wp_id}] {title[:45]} | lang={lang} | cats={cats}")

        # Metas
        for meta_key, meta_val in [('_fgj2wp_old_k2_id', str(k2_id)), ('Idioma', str(ef.get('lang_val') or 1))]:
            wp_execute(
                f"INSERT IGNORE INTO wp_postmeta (post_id, meta_key, meta_value) "
                f"VALUES ({wp_id}, '{esc(meta_key)}', '{esc(meta_val)}')"
            )

        # Categorías
        for term_id in cats:
            tt_id = tt_ids.get(term_id)
            if tt_id:
                wp_execute(
                    f"INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id) "
                    f"VALUES ({wp_id}, {tt_id})"
                )

        # Polylang
        pl_tt = pl_ids.get(lang)
        if pl_tt:
            wp_execute(
                f"INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id) "
                f"VALUES ({wp_id}, {pl_tt})"
            )

        stats['ok'] += 1

    # Actualizar counts
    if not DRY_RUN and stats['ok'] > 0:
        print("\nActualizando counts de categorías y Polylang...")
        all_tt = list(tt_ids.values()) + list(pl_ids.values())
        tt_str = ','.join(str(x) for x in all_tt)
        wp_execute(
            f"UPDATE wp_term_taxonomy tt SET count = ("
            f"SELECT COUNT(*) FROM wp_term_relationships tr WHERE tr.term_taxonomy_id=tt.term_taxonomy_id"
            f") WHERE tt.term_taxonomy_id IN ({tt_str})"
        )

    print(f"\n=== Resultado: {stats['ok']} ok, {stats['skip']} skip ===")


if __name__ == '__main__':
    main()