# -*- coding: utf-8 -*-
import os
import mysql.connector
from dotenv import load_dotenv

load_dotenv()

def conectar():
    return mysql.connector.connect(
        host=os.getenv("MYSQL_HOST", "localhost"),
        user=os.getenv("MYSQL_USER"),
        password=os.getenv("MYSQL_PASSWORD"),
        database=os.getenv("MYSQL_DATABASE"),
        port=int(os.getenv("MYSQL_PORT", "3306")),
    )

def guardar_en_mysql(resultados):
    if not resultados:
        print("Sin resultados para guardar en MySQL")
        return

    conn = conectar()
    cursor = conn.cursor()

    cursor.execute("TRUNCATE TABLE sims_Claro")
    print("Tabla sims_Claro truncada")

    sql = """
        INSERT INTO sims_Claro (fecha, linea, titulo, porcentaje, detalle, restante, total, unidad, renovacion)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
    """

    registros = [
        (
            r["fecha"],
            r["linea"],
            r["titulo"],
            r["porcentaje_usado"],
            r["detalle"],
            r["usado"],
            r["total"],
            r["unidad"],
            r["renovacion"],
        )
        for r in resultados
    ]

    cursor.executemany(sql, registros)
    conn.commit()
    print(f"MySQL: {cursor.rowcount} registros insertados")
    cursor.close()
    conn.close()

def verificar_y_actualizar_sims(resultados):
    if not resultados:
        print("Sin resultados para verificar")
        return

    conn = conectar()
    cursor        = conn.cursor(dictionary=True)
    cursor_otras  = conn.cursor(dictionary=True)
    cursor_update = conn.cursor()

    lineas = list(set(r["linea"] for r in resultados))

    for linea in lineas:
        print(f"\n  Verificando linea {linea}...")

        # Verificar llamadas a destinos Claro
        cursor.execute("""
            SELECT restante FROM sims_Claro
            WHERE titulo = 'Llamadas a destinos Claro'
            AND linea = %s
        """, (linea,))
        sim = cursor.fetchone()
        cursor.fetchall()

        if not sim:
            print(f"  Linea {linea} no encontrada en sims_Claro")
            continue

        sim_cid  = linea
        restante = sim["restante"]
        print(f"  sim_cid={sim_cid} | restante Claro={restante}")

        if restante <= 10:
            print(f"  ALERTA Claro: restante={restante} <= 10, desactivando campaign 1...")
            cursor_update.execute("""
                UPDATE campaign_cid_numbers
                SET active_cid = 0
                WHERE sim_cid = %s
                AND campaign = 1
                AND active_cid = 1
            """, (sim_cid,))
            print(f"    campaign_cid_numbers: {cursor_update.rowcount} filas (active_cid=0, campaign=1)")
            conn.commit()

            # Verificar llamadas a otras companias
            cursor_otras.execute("""
                SELECT restante FROM sims_Claro
                WHERE titulo = 'Llamadas a otras compa\u00f1\u00edas'
                AND linea = %s
            """, (linea,))
            sim_directo = cursor_otras.fetchone()
            cursor_otras.fetchall()

            if not sim_directo:
                print(f"  Linea {linea} sin datos de otras companias")
                continue

            restante_otras = sim_directo["restante"]
            print(f"  restante otras={restante_otras}")

            if restante_otras <= 200:
                cursor_update.execute("""
                    UPDATE campaign_cid_numbers
                    SET active_cid = 0
                    WHERE sim_cid = %s
                    AND campaign = 8
                    AND active_cid = 1
                """, (sim_cid,))
                print(f"    campaign_cid_numbers: {cursor_update.rowcount} filas (active_cid=0, campaign=8)")
            else:
                cursor_update.execute("""
                    UPDATE campaign_cid_numbers
                    SET active_cid = 1
                    WHERE sim_cid = %s
                    AND campaign = 8
                    AND active_cid = 0
                """, (sim_cid,))
                print(f"    campaign_cid_numbers: {cursor_update.rowcount} filas (active_cid=1, campaign=8)")

            conn.commit()

        else:
            # restante Claro > 10: desactivar campaign 8
            cursor_update.execute("""
                UPDATE campaign_cid_numbers
                SET active_cid = 0
                WHERE sim_cid = %s
                AND campaign = 8
                AND active_cid = 1
            """, (sim_cid,))
            print(f"    campaign_cid_numbers: {cursor_update.rowcount} filas (active_cid=0, campaign=8)")
            conn.commit()

    cursor.close()
    cursor_otras.close()
    cursor_update.close()
    conn.close()