from openai import OpenAI
import openai
import json
import time
import re
from pinecone import Pinecone
from datetime import datetime, timedelta
from azure.storage.blob import BlobClient
import base64
import ast
import conexiones
import json
import a_env_vars
import mysql.connector
from mysql.connector import Error
import sys
import requests
import traceback
import random
import tiktoken
from dotenv import load_dotenv
import os


# función para conectar a la base de datos en local
def conectar_db():
    # traerse la variable de conifg del archivo de conexiones.py para conectarse al localhost
    config = conexiones.config
    try:
        connection = mysql.connector.connect(**config)
        if connection.is_connected():
            return connection
        else:
            return None
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        return None

def conectar_db3():
    # traerse la variable de conifg del archivo de conexiones.py para conectarse al localhost
    config3 = conexiones.config3
    try:
        connection = mysql.connector.connect(**config3)
        if connection.is_connected():
            return connection
        else:
            return None
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        return None

def predios_de_propietario(idcliente):
    if idcliente == 'no':
        return "No se puede entregar la información porque el usuario no está logiado. Debe iniciar sesión para consultar sus predios."

    try:
        # Conexión 1: Base de datos principal (ciudadanos)
        connection = conectar_db()
        if connection is None or not connection.is_connected():
            return {"status": "error", "message": "No se pudo conectar a la base de datos principal."}

        cursor = connection.cursor(dictionary=True)
        try:
            # Limpieza de resultados pendientes por seguridad
            while cursor.nextset():
                cursor.fetchall()

            # Consulta del documento del ciudadano
            cursor.execute("""
                SELECT `num_doc_ident`
                FROM `ciudadanos`
                WHERE `id` = %s;
            """, (idcliente,))
            cliente = cursor.fetchone()

            if not cliente:
                return {
                    "status": "error",
                    "message": "La persona no se encuentra registrada. No se puede proporcionar información de predios.",
                    "idcliente": idcliente
                }

            documento = cliente['num_doc_ident']
        finally:
            cursor.close()
            connection.close()

        # Conexión 2: Base de datos de propiedades
        connection2 = conectar_db3()
        if connection2 is None or not connection2.is_connected():
            return {"status": "error", "message": "No se pudo conectar a la base de datos de propiedades."}

        cursor2 = connection2.cursor(dictionary=True)
        try:
            while cursor2.nextset():
                cursor2.fetchall()

            # Consulta en registro_1 para obtener FICHA
            cursor2.execute("""
                SELECT `FICHA`
                FROM `registro_1`
                WHERE `DOCUMENTO` = %s;
            """, (documento,))
            registro1 = cursor2.fetchone()

            if not registro1:
                return {
                    "status": "error",
                    "message": "No se encontraron registros en 'registro_1' para el documento proporcionado."
                }

            ficha = registro1['FICHA']

            # Limpieza por si quedan resultados antes de siguiente consulta
            while cursor2.nextset():
                cursor2.fetchall()

            # Consulta de predios en registro_2 usando la ficha
            cursor2.execute("""
                SELECT 
                    my_row_id, TOTAL_REGISTROS, NRO_ORDEN, COD_MUNICIPIO, MNCPIO_NOMBRE, NPN, SECTOR, CORREGIMIENTO, 
                    BARRIO, MNZVER, TERRENO, NRO_CONS, FICHA, CIRULO, MATRICULA, DIRECCION, NUMERO, PUNTOS, PISOS, 
                    COCINAS, HABITACIONES, LOCALES, BAÑOS, TIP_CONST, IDUSO, VR_IDNUSO_NOMBRE, TIP_NOMBRE, EDAD, 
                    PORC_CONS, UNDPRD_COEFICIENTE, TERRENO_HA, AREA_CONSTRM2, AREA_CONSTR_TOTAL_M2, VALOR_CONSTR, 
                    AVALUO_CONSTR_TOTAL, AVALUO_TERRENO, AVALUO_TOTAL, VIGENCIA, CEDCATAS, CARACTERISTICA, TIPO_PREDIO, 
                    SECTOR2
                FROM `registro_2`
                WHERE `FICHA` = %s;
            """, (ficha,))
            predios = cursor2.fetchall()

            if predios:
                print(f"✅ Se encontraron {len(predios)} predios asociados a la ficha {ficha}.")
                return {"status": "success", "data": predios}
            else:
                print(f"⚠️ No se encontraron predios asociados a la ficha {ficha}.")
                return {"status": "error", "message": "No se encontraron predios con la ficha proporcionada."}

        finally:
            cursor2.close()
            connection2.close()

    except Exception as e:
        print(f"❌ Error general al consultar los predios: {e}")
        return {"status": "error", "message": f"Ocurrió un error al consultar los predios: {e}"}
    

def detalle_predio(idcliente):
    try:
        id_num = int(idcliente)
        if id_num == 0:
            return {"status": "error", "message":"No se puede entregar la información porque el usuario no está logiado. Debe iniciar sesión para consultar sus predios."}
    except (ValueError, TypeError):
        return {"status": "error", "message":"No se puede entregar la información porque el usuario no está logiado. Debe iniciar sesión para consultar sus predios."}
   
    try:
        # Conexión 1: Ciudadanos
        connection = conectar_db()
        if connection is None or not connection.is_connected():
            return {"status": "error", "message": "No se pudo conectar a la base de datos principal."}

        cursor = connection.cursor(dictionary=True)
        try:
            while cursor.nextset():
                cursor.fetchall()

            cursor.execute("""
                SELECT `num_doc_ident`
                FROM `ciudadanos`
                WHERE `id` = %s;
            """, (idcliente,))
            cliente = cursor.fetchone()

            if not cliente:
                return {
                    "status": "error",
                    "message": "La persona no se encuentra registrada.",
                    "idcliente": idcliente
                }

            documento = cliente['num_doc_ident']
        finally:
            cursor.close()
            connection.close()

        print("el documento es"+documento)
        
        try:
            docu = int(documento)
            if docu == 0:
                return {"status": "error", "message":"⚠️ El usuario no tiene un documento válido registrado."}
        except (ValueError, TypeError):
            return {"status": "error", "message":"Error en el documento."}
   
        # Conexión 2: Propiedades
        connection2 = conectar_db3()
        if connection2 is None or not connection2.is_connected():
            return {"status": "error", "message": "No se pudo conectar a la base de datos de propiedades."}

        cursor2 = connection2.cursor(dictionary=True)
        try:
            while cursor2.nextset():
                cursor2.fetchall()

            # Consulta agregada por documento
            cursor2.execute("""
            SELECT 
              r1.DOCUMENTO,
            
              (SELECT COUNT(DISTINCT r1_sub.FICHA)
               FROM registro_1 r1_sub
               WHERE r1_sub.DOCUMENTO = r1.DOCUMENTO
              ) AS total_predios,
            
              (SELECT SUM(r2_sub.AVALUO_TOTAL)
               FROM registro_1 r1_sub
               JOIN registro_2 r2_sub ON r1_sub.FICHA = r2_sub.FICHA
               WHERE r1_sub.DOCUMENTO = r1.DOCUMENTO
              ) AS avaluo_total,
            
              (SELECT SUM(r2_sub.TERRENO_HA * 10000)
               FROM registro_1 r1_sub
               JOIN registro_2 r2_sub ON r1_sub.FICHA = r2_sub.FICHA
               WHERE r1_sub.DOCUMENTO = r1.DOCUMENTO
              ) AS area_total_terreno_m2,
            
              (SELECT SUM(r2_sub.AREA_CONSTR_TOTAL_M2)
               FROM registro_1 r1_sub
               JOIN registro_2 r2_sub ON r1_sub.FICHA = r2_sub.FICHA
               WHERE r1_sub.DOCUMENTO = r1.DOCUMENTO
              ) AS area_total_construida_m2,
            
              (SELECT 
                 SUM(r2_sub.AVALUO_TOTAL) / NULLIF(SUM(r2_sub.AREA_CONSTR_TOTAL_M2), 0)
               FROM registro_1 r1_sub
               JOIN registro_2 r2_sub ON r1_sub.FICHA = r2_sub.FICHA
               WHERE r1_sub.DOCUMENTO = r1.DOCUMENTO
              ) AS valor_promedio_m2,
            
              (
                SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                    'ficha', r1_det.FICHA,
                    'direccion', r1_det.DIRECCION,
                    'municipio', r1_det.MNCPIO_NOMBRE,
                    'barrio', r2_det.BARRIO,
                    'sector', r2_det.SECTOR,
                    'avaluo_total', r2_det.AVALUO_TOTAL,
                    'area_terreno_m2', r2_det.TERRENO_M2,
                    'area_construida_m2', r2_det.AREA_CONSTR_TOTAL_M2,
                    'valor_m2', IFNULL(r2_det.AVALUO_TOTAL / NULLIF(r2_det.AREA_CONSTR_TOTAL_M2, 0), 0)
                  )
                )
                FROM (
                  SELECT DISTINCT FICHA, DIRECCION, MNCPIO_NOMBRE, DOCUMENTO
                  FROM registro_1
                  WHERE DOCUMENTO = r1.DOCUMENTO
                ) AS r1_det
                JOIN (
                  SELECT 
                    FICHA,
                    MAX(BARRIO) AS BARRIO,
                    MAX(SECTOR) AS SECTOR,
                    SUM(AVALUO_TOTAL) AS AVALUO_TOTAL,
                    SUM(TERRENO_HA) * 10000 AS TERRENO_M2,
                    SUM(AREA_CONSTR_TOTAL_M2) AS AREA_CONSTR_TOTAL_M2
                  FROM registro_2
                  GROUP BY FICHA
                ) r2_det ON r1_det.FICHA = r2_det.FICHA
              ) AS detalle_predios
            
            FROM registro_1 r1
            WHERE r1.DOCUMENTO = %s
            LIMIT 1;
                        """, (documento,))
            resultado = cursor2.fetchone()

            if resultado:
                print(f"✅ Se encontró información detallada del documento {documento}.")
                return {"status": "success", "data": resultado}
            else:
                print(f"⚠️ No se encontraron predios asociados al documento {documento}.")
                return {"status": "error", "message": "No se encontraron predios asociados al documento proporcionado."}

        finally:
            cursor2.close()
            connection2.close()

    except Exception as e:
        print(f"❌ Error general al consultar detalle_predio: {e}")
        return {"status": "error", "message": f"Ocurrió un error al consultar el detalle del predio: {e}"}



def propietarios_del_predio(idcliente):
    if idcliente == 'no':
        return "No se puede entregar la información porque el usuario no está logiado. Debe iniciar sesión para consultar sus predios."

    connection = None
    connection2 = None
    cursor = None
    cursor2 = None

    try:
        # Paso 1: Obtener el documento del ciudadano
        connection = conectar_db()
        if connection is None or not connection.is_connected():
            return {"status": "error", "message": "No se pudo conectar a la base de datos principal."}

        cursor = connection.cursor(dictionary=True)
        while cursor.nextset():
            cursor.fetchall()

        cursor.execute("""
            SELECT num_doc_ident
            FROM ciudadanos
            WHERE id = %s;
        """, (idcliente,))
        cliente = cursor.fetchone()

        if not cliente:
            return {"status": "error", "message": "El ciudadano no está registrado."}

        documento = cliente['num_doc_ident']
        cursor.close()
        connection.close()

        # Paso 2: Obtener todas las fichas del ciudadano desde registro_1
        connection2 = conectar_db3()
        if connection2 is None or not connection2.is_connected():
            return {"status": "error", "message": "No se pudo conectar a la base de datos de propiedades."}

        cursor2 = connection2.cursor(dictionary=True)
        while cursor2.nextset():
            cursor2.fetchall()

        cursor2.execute("""
            SELECT FICHA
            FROM registro_1
            WHERE DOCUMENTO = %s;
        """, (documento,))
        fichas = cursor2.fetchall()

        if not fichas:
            return {"status": "error", "message": "No se encontraron fichas prediales asociadas al ciudadano."}

        resultado = ""
        for ficha_row in fichas:
            ficha = ficha_row["FICHA"]

            while cursor2.nextset():
                cursor2.fetchall()

            # Consulta unida para obtener propietarios por ficha
            cursor2.execute("""
                SELECT 
                    r2.FICHA,
                    r2.MATRICULA,
                    r2.DIRECCION,
                    r2.AREA_CONSTR_TOTAL_M2,
                    r2.AVALUO_TERRENO,
                    r1.NOMBRE1,
                    r1.NOMBRE2,
                    r1.APELLIDO1,
                    r1.APELLIDO2,
                    r1.RAZON_SOCIAL,
                    r1.PORC_DERECHO,
                    r1.MODO_ADQUISICION,
                    r1.FECHA_REGISTRO
                FROM 
                    registro_2 r2
                INNER JOIN 
                    registro_1 r1 ON r2.NPN = r1.NPN
                WHERE 
                    r2.FICHA = %s;
            """, (ficha,))
            propietarios = cursor2.fetchall()

            if propietarios:
                resultado += f"\n📌 Propietarios para ficha predial {ficha}:\n\n"
                for p in propietarios:
                    nombre_completo = (
                        f"{p.get('NOMBRE1', '')} {p.get('NOMBRE2', '')} "
                        f"{p.get('APELLIDO1', '')} {p.get('APELLIDO2', '')}".strip()
                    )
                    nombre_final = nombre_completo if nombre_completo else p.get('RAZON_SOCIAL', 'N/A')
                    resultado += (
                        f"🧾 Ficha: {p['FICHA']}\n"
                        f"📜 Matrícula: {p['MATRICULA']}\n"
                        f"📍 Dirección: {p['DIRECCION']}\n"
                        f"📐 Área construida total (m²): {p['AREA_CONSTR_TOTAL_M2']}\n"
                        f"🌍 Avalúo terreno: {p['AVALUO_TERRENO']}\n"
                        f"👤 Propietario: {nombre_final}\n"
                        f"🔢 Porcentaje derecho: {p.get('PORC_DERECHO', 'N/A')}\n"
                        f"📝 Modo de adquisición: {p.get('MODO_ADQUISICION', 'N/A')}\n"
                        f"📅 Fecha de registro: {p.get('FECHA_REGISTRO', 'N/A')}\n"
                        "--------------------------------------------\n"
                    )
            else:
                resultado += f"\n⚠️ No se encontraron propietarios para la ficha {ficha}.\n"

        if resultado.strip() == "":
            return {"status": "error", "message": "No se encontraron datos de propietarios para ninguna ficha del ciudadano."}

        return resultado.strip()

    except Exception as e:
        return {"status": "error", "message": f"Ocurrió un error al consultar los propietarios: {e}"}

    finally:
        if cursor2:
            cursor2.close()
        if connection2 and connection2.is_connected():
            connection2.close()


# #Consultas actualizadas 
# def all_predios(documento):
#     try:
#         print("el documento es: " + documento)
        
#         try:
#             docu = int(documento)
#             if docu == 0:
#                 return {"status": "error", "message":"⚠️ El usuario no tiene un documento válido registrado."}
#         except (ValueError, TypeError):
#             return {"status": "error", "message":"Error en el documento."}
   
#         # Conexión 2: Propiedades
#         connection2 = conectar_db3()
#         if connection2 is None or not connection2.is_connected():
#             return {"status": "error", "message": "No se pudo conectar a la base de datos de propiedades."}

#         cursor2 = connection2.cursor(dictionary=True, buffered=True)
#         try:            
#             # Consulta agregada por documento
#             cursor2.execute("""
#                 SELECT * FROM registro_1 r WHERE r.DOCUMENTO = %s;
#             """, (documento,))
            
#             # ✅ CAMBIO: fetchall() en lugar de fetchone()
#             resultados = cursor2.fetchall()

#             if resultados:
#                 print(f"✅ Se encontraron {len(resultados)} predios para el documento {documento}.")
#                 return {"status": "success", "data": resultados}
#             else:
#                 print(f"⚠️ No se encontraron predios asociados al documento {documento}.")
#                 return {"status": "error", "message": "No se encontraron predios asociados al documento proporcionado."}

#         finally:
#             cursor2.close()
#             connection2.close()

#     except Exception as e:
#         print(f"❌ Error general al consultar all_predios: {e}")
#         return {"status": "error", "message": f"Ocurrió un error al consultar el detalle del predio: {e}"}


# def detalles_predios(ficha, npn):
#     try:
#         # Conexión 2: Propiedades
#         connection2 = conectar_db3()
#         if connection2 is None or not connection2.is_connected():
#             return {"status": "error", "message": "No se pudo conectar a la base de datos de propiedades."}

#         cursor2 = connection2.cursor(dictionary=True, buffered=True)
#         try:
#             cursor2.execute("""
#                 SELECT * 
#                 FROM registro_2 r 
#                 WHERE r.FICHA = %s AND r.NPN = %s;
#             """, (ficha, npn))

#             resultado = cursor2.fetchall()

#             if resultado:
#                 print(f"✅ Se encontró detalle del predio FICHA={ficha}, NPN={npn}.")
#                 return {"status": "success", "data": resultado}
#             else:
#                 print(f"⚠️ No se encontró detalle para FICHA={ficha}, NPN={npn}.")
#                 return {"status": "error", "message": "No se encontraron detalles del predio con los parámetros proporcionados."}

#         finally:
#             cursor2.close()
#             connection2.close()

#     except Exception as e:
#         print(f"❌ Error general al consultar detalle_predio: {e}")
#         return {"status": "error", "message": f"Ocurrió un error al consultar el detalle del predio: {e}"}


#Consultas actualizadas 
def all_predios(documento, tipo_consulta):
    try:
        print(f"📋 Documento: {documento} | Tipo consulta: {tipo_consulta}")
        
        # Validación del documento
        try:
            docu = int(documento)
            if docu == 0:
                return {"status": "error", "message":"⚠️ El usuario no tiene un documento válido registrado."}
        except (ValueError, TypeError):
            return {"status": "error", "message":"Error en el documento."}
        
        # Validación del tipo de consulta
        if tipo_consulta not in ["simple", "combinada"]:
            return {"status": "error", "message": "El tipo de consulta debe ser 'simple' o 'combinada'."}
   
        # Conexión a la base de datos
        connection2 = conectar_db3()
        if connection2 is None or not connection2.is_connected():
            return {"status": "error", "message": "No se pudo conectar a la base de datos de propiedades."}

        cursor2 = connection2.cursor(dictionary=True, buffered=True)
        try:            
            # Consulta de predios
            cursor2.execute("""
                SELECT * FROM registro_1 r WHERE r.DOCUMENTO = %s;
            """, (documento,))
            
            predios = cursor2.fetchall()

            if not predios:
                print(f"⚠️ No se encontraron predios asociados al documento {documento}.")
                return {"status": "error", "message": "No se encontraron predios asociados al documento proporcionado."}

            print(f"✅ Se encontraron {len(predios)} predios para el documento {documento}.")
            
            # Si es consulta simple, retornar solo los predios
            if tipo_consulta == "simple":
                return {"status": "success", "data": predios}
            
            # Si es consulta combinada, obtener detalles de cada predio
            if tipo_consulta == "combinada":
                predios_con_detalles = []
                
                for predio in predios:
                    ficha = predio.get('FICHA')
                    npn = predio.get('NPN')
                    
                    if ficha and npn:
                        # Consultar detalles del predio
                        cursor2.execute("""
                            SELECT * 
                            FROM registro_2 r 
                            WHERE r.FICHA = %s AND r.NPN = %s;
                        """, (ficha, npn))
                        
                        detalles = cursor2.fetchall()
                        
                        # Agregar los detalles al predio
                        predio_completo = {
                            "predio": predio,
                            "detalles": detalles if detalles else []
                        }
                        predios_con_detalles.append(predio_completo)
                        
                        print(f"  ✓ Detalles obtenidos para FICHA={ficha}, NPN={npn}: {len(detalles)} registros")
                    else:
                        # Si no tiene FICHA o NPN, agregar sin detalles
                        predios_con_detalles.append({
                            "predio": predio,
                            "detalles": []
                        })
                        print(f"  ⚠️ Predio sin FICHA o NPN válidos")
                
                print(f"✅ Consulta combinada completada: {len(predios_con_detalles)} predios con detalles.")
                return {
                    "status": "success", 
                    "data": predios_con_detalles,
                    "total_predios": len(predios_con_detalles)
                }

        finally:
            cursor2.close()
            connection2.close()

    except Exception as e:
        print(f"❌ Error general al consultar all_predios: {e}")
        return {"status": "error", "message": f"Ocurrió un error al consultar los predios: {e}"}


def detalles_predios(ficha, npn):
    """
    Consulta detalles de un predio específico.
    Esta función se mantiene para consultas individuales.
    """
    try:
        connection2 = conectar_db3()
        if connection2 is None or not connection2.is_connected():
            return {"status": "error", "message": "No se pudo conectar a la base de datos de propiedades."}

        cursor2 = connection2.cursor(dictionary=True, buffered=True)
        try:
            cursor2.execute("""
                SELECT * 
                FROM registro_2 r 
                WHERE r.FICHA = %s AND r.NPN = %s;
            """, (ficha, npn))

            resultado = cursor2.fetchall()

            if resultado:
                print(f"✅ Se encontró detalle del predio FICHA={ficha}, NPN={npn}.")
                return {"status": "success", "data": resultado}
            else:
                print(f"⚠️ No se encontró detalle para FICHA={ficha}, NPN={npn}.")
                return {"status": "error", "message": "No se encontraron detalles del predio con los parámetros proporcionados."}

        finally:
            cursor2.close()
            connection2.close()

    except Exception as e:
        print(f"❌ Error general al consultar detalle_predio: {e}")
        return {"status": "error", "message": f"Ocurrió un error al consultar el detalle del predio: {e}"}