import os
import json
import argparse
import pymysql
import logging
from dotenv import load_dotenv

load_dotenv()
DB_USER = os.getenv('DB_USER')
DB_PASSWORD = os.getenv('DB_PASSWORD')
DB_HOST = os.getenv('DB_HOST')
DB_NAME = os.getenv('DB_NAME')

TABLE = "UserInteraction"
BATCH_SIZE = 200

def is_emptyish(v):
    if v is None:
        return True
    s = str(v).strip()
    return s == "" or s.lower() == "null"

def to_int_or_none(v):
    try:
        if is_emptyish(v): 
            return None
        return int(str(v))
    except:
        return None

def get_conn():
    return pymysql.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DB_NAME,
        cursorclass=pymysql.cursors.DictCursor, charset="utf8mb4", use_unicode=True,
        autocommit=False
    )

def list_json_files_oldest_first(root_dir):
    files = []
    for dp, _, fns in os.walk(root_dir):
        for fn in fns:
            if fn.lower().endswith(".json"):
                full = os.path.join(dp, fn)
                try:
                    mtime = os.path.getmtime(full)
                except Exception:
                    mtime = 0
                files.append((mtime, full))
    files.sort(key=lambda x: x[0])
    return [p for _, p in files]

def read_json(path):
    with open(path, "r", encoding="utf-8") as f:
        return json.load(f)

# JSON -> columna BD
MAP = {
    "boton_interes":           "boton_interes",
    "primera_respuesta":       "primera_respuesta",
    "boton_seguimiento":       "boton_seguimiento",
    "boton_abandono":          "boton_abandono",
    "topico":                  "topico",
    "acuerdo":                 "acuerdo",
    "dentro de presupuesto":   "dentro_de_presupuesto",
    "rentadeclarada":          "rentadeclarada",
    "situacion_de_vivienda":   "situacion_de_vivienda",
    "inicial":                 "inicial",
    "fechaentrega":            "fechaentrega",
    "nivel_de_interes":        "nivel_de_interes",
    "subsidio":                "subsidio",
    "subsidio_puede_postular": "subsidio_puede_postular",
    "contactar_por":           "contactar_por",
    "credito":                 "credito",
    "crm id trato":            "crm_id_trato",
    "crm Nombre_vendedor":     "crm_Nombre_vendedor",
    "crm_medio_id":            "crm_medio_id",
    "crm_proyecto":            "crm_proyecto",
    "pie":                     "pie",
    # dos variantes de origen para el mismo destino:
    "motivo de problema":      "motivo_de_problema",
    "motivo_de_problema":      "motivo_de_problema",
    "crm_Rut vendedor":        "crm_Rut_vendedor",
}

def fetch_existing_columns(conn):
    sql = """
      SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s
    """
    with conn.cursor() as cur:
        cur.execute(sql, (DB_NAME, TABLE))
        return {row["COLUMN_NAME"] for row in cur.fetchall()}

def build_updates(d, existing_cols):
    updates = {}
    # mapea y filtra vacíos
    for src, dst in MAP.items():
        if dst not in existing_cols:
            continue
        val = d.get(src)
        if is_emptyish(val):
            continue
        if dst == "crm_Rut_vendedor":
            v = to_int_or_none(val)
            if v is None:
                continue
            val = v
        updates[dst] = val
    return updates

def update_one(conn, d, existing_cols):
    wa_id = d.get("whatsapp user id")
    proy  = d.get("proyecto id")
    if is_emptyish(wa_id) or is_emptyish(proy):
        return False, "sin clave"

    updates = build_updates(d, existing_cols)
    if not updates:
        return False, "sin campos"

    set_clause = ", ".join(f"{c}=%s" for c in updates.keys())
    params = list(updates.values()) + [wa_id, str(proy)]

    sql = f"""
        UPDATE {TABLE}
           SET {set_clause}
         WHERE whatsapp_user_id = %s
           AND CAST(proyecto_id AS CHAR) = %s
        LIMIT 1
    """
    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.rowcount > 0, f"updated {cur.rowcount}"

def main():
    ap = argparse.ArgumentParser(description="Actualizar UserInteraction desde JSON (campos seleccionados).")
    ap.add_argument("-i", "--input-dir", required=True, help="Carpeta con JSON (se procesan del más antiguo al más nuevo)")
    ap.add_argument("--dry-run", action="store_true")
    args = ap.parse_args()

    logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")

    files = list_json_files_oldest_first(args.input_dir)
    logging.info(f"Archivos JSON: {len(files)}")
    if not files:
        return

    conn = get_conn()
    processed = updated = skipped = 0
    batch = 0
    try:
        existing = fetch_existing_columns(conn)
        logging.info(f"Columnas en {TABLE}: {len(existing)}")

        for path in files:
            try:
                d = read_json(path)
            except Exception as e:
                logging.warning(f"No se pudo leer {path}: {e}")
                skipped += 1
                continue

            ok, msg = update_one(conn, d, existing)
            processed += 1
            batch += 1
            if ok:
                updated += 1
            else:
                skipped += 1

            if batch >= BATCH_SIZE and not args.dry_run:
                conn.commit()
                logging.info(f"Commit lote. proc={processed} upd={updated} skip={skipped}")
                batch = 0

        if not args.dry_run:
            conn.commit()
        logging.info(f"FIN. proc={processed} upd={updated} skip={skipped}")

    except Exception as e:
        conn.rollback()
        logging.exception("Error; rollback.")
    finally:
        conn.close()

if __name__ == "__main__":
    main()