import os
import pymysql
import argparse
import csv
import requests
from dotenv import load_dotenv

load_dotenv()

# ===============================
# CONFIG
# ===============================
ARCHIVO_LOG = "telefonos_email_enviados.csv"

DB_CONFIG = {
    'host': os.getenv("DB_HOST"),
    'user': os.getenv("DB_USER"),
    'password': os.getenv("DB_PASSWORD"),
    'database': os.getenv("DB_NAME"),
    'cursorclass': pymysql.cursors.DictCursor
}

CHATFUEL_BOT_ID = os.getenv("CHATFUEL_BOT_ID")
CHATFUEL_TOKEN = os.getenv("CHATFUEL_TOKEN")

CHATFUEL_URL = (
    f"https://api.chatfuel.com/whatsapp/bots/"
    f"{CHATFUEL_BOT_ID}/contacts?chatfuel_token={CHATFUEL_TOKEN}"
)

HEADERS = {"Content-Type": "application/json"}

# ===============================
# LEER LOG
# ===============================
def leer_enviados():
    enviados = set()

    if not os.path.exists(ARCHIVO_LOG):
        return enviados

    with open(ARCHIVO_LOG, newline='', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            enviados.add(row["telefono"])

    return enviados


def guardar_enviado(phone, email, resp):
    existe = os.path.exists(ARCHIVO_LOG)

    with open(ARCHIVO_LOG, "a", newline='', encoding='utf-8') as f:
        writer = csv.writer(f)

        if not existe:
            writer.writerow(["telefono", "email", "respuesta"])

        writer.writerow([phone, email, str(resp)])


# ===============================
# FETCH LEADS (SIN LIMITE)
# ===============================
def fetch_leads():
    conn = pymysql.connect(**DB_CONFIG)
    try:
        with conn.cursor() as cur:
            sql = """
                SELECT 
                    phone_norm_gen,
                    Email_cliente
                FROM Lead_entrantes
                WHERE phone_norm_gen IS NOT NULL 
                  AND phone_norm_gen != ''
                  AND Email_cliente IS NOT NULL
                  AND Email_cliente != ''
                  AND Fecha_ingreso >= CURDATE() - INTERVAL 30 DAY
            """
            cur.execute(sql)
            return cur.fetchall()
    finally:
        conn.close()


# ===============================
# FETCH UI
# ===============================
def fetch_ui_record(phone):
    conn = pymysql.connect(**DB_CONFIG)
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT *
                FROM UserInteraction
                WHERE whatsapp_phone = %s
                ORDER BY fecha_registro DESC
                LIMIT 1
            """, (phone,))
            return cur.fetchone()
    finally:
        conn.close()


# ===============================
# UPDATE UI
# ===============================
def update_ui_record(phone, updates):
    if not updates:
        return

    conn = pymysql.connect(**DB_CONFIG)
    try:
        with conn.cursor() as cur:
            set_clause = ", ".join([f"{field} = %s" for field in updates])
            values = list(updates.values())
            values.append(phone)

            sql = f"""
                UPDATE UserInteraction
                SET {set_clause}
                WHERE whatsapp_phone = %s
            """
            cur.execute(sql, values)
        conn.commit()
    finally:
        conn.close()


# ===============================
# CHATFUEL
# ===============================
def enviar_chatfuel(phone, email):
    payload = {
        "phone": phone,
        "attributes": {
            "whatsapp_phone": phone,
            "crm Email_cliente": email
        }
    }

    try:
        r = requests.post(CHATFUEL_URL, json=payload, headers=HEADERS, timeout=20)
        return {"status_code": r.status_code, "text": r.text[:200]}
    except Exception as e:
        return {"error": str(e)}


# ===============================
# VALIDACIONES
# ===============================
def is_empty(value):
    if value is None:
        return True
    if isinstance(value, int):
        return value == 0
    if isinstance(value, str):
        return value.strip() == ""
    return False


def email_valido(e):
    return "@" in e and "." in e


# ===============================
# PROCESO
# ===============================
def procesar(modo_test=False):

    enviados = leer_enviados()
    leads = fetch_leads()

    print(f"📧 Leads totales: {len(leads)}")
    print(f"🚫 Ya enviados: {len(enviados)}")

    contador = 0

    for lead in leads:
        phone = lead["phone_norm_gen"]
        email = lead["Email_cliente"]

        if phone in enviados:
            continue

        if not email_valido(email):
            print(f"❌ Email inválido: {email}")
            continue

        ui = fetch_ui_record(phone)

        if not ui:
            print(f"⚠️ Sin UI: {phone}")
            continue

        updates = {}

        if is_empty(ui.get("mail")):
            updates["mail"] = email

        if is_empty(ui.get("crm_email")):
            updates["crm_email"] = email

        print(f"📤 {phone} → {email}")

        # TEST → solo uno
        if modo_test:
            print("🧪 TEST → se detiene aquí")
            break

        # Update BD
        if updates:
            update_ui_record(phone, updates)

        # Enviar Chatfuel
        resp = enviar_chatfuel(phone, email)
        print(f"🤖 {resp}")

        # Guardar log
        if resp.get("status_code") == 200:
            guardar_enviado(phone, email, resp)
            contador += 1
        else:
            print("❌ Error, no se guarda en log")

    print(f"✅ Enviados en esta ejecución: {contador}")


# ===============================
# MAIN
# ===============================
if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("--test", action="store_true")
    args = parser.parse_args()

    procesar(modo_test=args.test)