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

# Campos que JAMÁS queremos actualizar
FIELDS_FORBIDDEN = {"enviado_crm", "fecha_registro", "estado_flujo"}


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",
    "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 = {}
    for src, dst in MAP.items():
        # Blindaje extra: nunca tocar los prohibidos
        if dst in FIELDS_FORBIDDEN:
            continue

        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_raw = d.get("proyecto id")

    if is_emptyish(wa_id):
        return False, "sin whatsapp_user_id"

    # Normalizar proyecto_id del JSON
    proy_str = str(proy_raw).strip() if proy_raw is not None else ""
    use_proyecto = not is_emptyish(proy_str)

    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_base = list(updates.values())

    with conn.cursor() as cur:
        # CASO A: JSON trae proyecto_id
        if use_proyecto:
            # 1) Intento principal: wa + proyecto_id
            sql1 = f"""
                UPDATE {TABLE}
                   SET {set_clause}
                 WHERE whatsapp_user_id = %s
                   AND CAST(proyecto_id AS CHAR) = %s
                 LIMIT 1
            """
            params1 = params_base + [wa_id, proy_str]
            cur.execute(sql1, params1)
            if cur.rowcount > 0:
                return True, f"updated {cur.rowcount} (wa+proy)"

            # 2) Fallback: misma persona, proyecto_id NULL/blanco
            sql2 = f"""
                UPDATE {TABLE}
                   SET {set_clause}
                 WHERE whatsapp_user_id = %s
                   AND (
                         proyecto_id IS NULL
                      OR TRIM(CAST(proyecto_id AS CHAR)) = ''
                   )
                 LIMIT 1
            """
            params2 = params_base + [wa_id]
            cur.execute(sql2, params2)
            if cur.rowcount > 0:
                return True, f"updated {cur.rowcount} (wa, proy NULL/blank)"

            return False, "sin match (wa+proy)"

        # CASO B: JSON NO trae proyecto_id → solo wa_id, sobre filas sin proyecto
        sql3 = f"""
            UPDATE {TABLE}
               SET {set_clause}
             WHERE whatsapp_user_id = %s
               AND (
                     proyecto_id IS NULL
                  OR TRIM(CAST(proyecto_id AS CHAR)) = ''
               )
             LIMIT 1
        """
        params3 = params_base + [wa_id]
        cur.execute(sql3, params3)
        if cur.rowcount > 0:
            return True, f"updated {cur.rowcount} (wa, proy NULL/blank)"

        return False, "sin match (solo wa)"


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")

    logging.info(f"Buscando JSON en: {args.input_dir}")
    if not os.path.isdir(args.input_dir):
        logging.warning(f"Directorio no existe o no es carpeta: {args.input_dir}")

    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()