import os
from pathlib import Path

import pyodbc
from dotenv import load_dotenv

load_dotenv(Path(__file__).resolve().parent.parent / ".env")

_SP_PERMITIDOS = {
    'buscador_paises_padron',
    'gestor_batch_procesos',
    'gestor_prompts',
    'insert_edictos_queue_staging',
    'openai_edictos_dia',
    'openai_insert_json',
    'openai_insert_personas',
    'openai_insert_sociedades_constituciones',
    'openai_insert_vinculadas',
    'PRS_iniciar',
    'PRS_finalizar',
    'PRS_log_evento',
    'recorrerEdictos',
    'EDT_homonimia',
    'EDT_homonimia_obtener',
    'EDT_homonimia_obtener_sociedad',
    'EDT_homonimia_buscar_en_socios',
    'EDT_homonimia_buscar_en_accionistas',
    'EDT_homonimia_asignar_cuit',
    'EDT_homonimia_marcar_negativo',
    'EDT_homonimia_marcar_multiple',
    'EDT_homonimia_relaciones',
    'EDT_homonimia_relleno_cuit',
    'EDT_homonimia_id_padron'
}


def ejecutar_sp(procedure_name, params, conn=None):
    if procedure_name not in _SP_PERMITIDOS:
        raise ValueError(f"Stored procedure no permitido: {procedure_name!r}")
    _owns_conn = conn is None
    _conn = conn if conn is not None else _conectar()
    try:
        cursor = _conn.cursor()
        placeholders = ','.join(['?'] * len(params))
        cursor.execute(f"EXEC {procedure_name} {placeholders}", tuple(params))
        result = _fetch_result(cursor)
        if _owns_conn:
            _conn.commit()
        cursor.close()
        return result
    finally:
        if _owns_conn:
            _conn.close()


def ejecutar_sp_raw(procedure_name, params, conn):
    """Como ejecutar_sp pero usa conn externo, no hace commit y devuelve filas crudas (tuplas)."""
    if procedure_name not in _SP_PERMITIDOS:
        raise ValueError(f"Stored procedure no permitido: {procedure_name!r}")
    cursor = conn.cursor()
    try:
        placeholders = ','.join(['?'] * len(params))
        cursor.execute(f"EXEC {procedure_name} {placeholders}", tuple(params))
        try:
            return cursor.fetchall()
        except pyodbc.ProgrammingError:
            return None
    finally:
        cursor.close()


def ejecutar_sp_dict(procedure_name, params, conn):
    """Como ejecutar_sp_raw pero devuelve lista de dicts con nombre de columna como clave."""
    if procedure_name not in _SP_PERMITIDOS:
        raise ValueError(f"Stored procedure no permitido: {procedure_name!r}")
    cursor = conn.cursor()
    try:
        placeholders = ','.join(['?'] * len(params))
        cursor.execute(f"EXEC {procedure_name} {placeholders}", tuple(params))
        try:
            cols = [col[0] for col in cursor.description]
            return [dict(zip(cols, row)) for row in cursor.fetchall()]
        except pyodbc.ProgrammingError:
            return None
    finally:
        cursor.close()


def conectar():
    return _conectar()

def conectar_readonly():
    conn = _conectar()
    conn.autocommit = True
    return conn

def _conectar():
    server = os.getenv("DB_SERVER")
    database = os.getenv("DB_DATABASE")
    username = os.getenv("DB_USER")
    password = os.getenv("DB_PASSWORD")
    driver = os.getenv("DB_DRIVER", "ODBC Driver 18 for SQL Server")
    return pyodbc.connect(
        f'DRIVER={{{driver}}};SERVER={{{server}}};PORT=1433;'
        f'DATABASE={{{database}}};UID={{{username}}};PWD={{{password}}};TrustServerCertificate=yes;'
    )


def _fetch_result(cursor):
    while True:
        try:
            rows = cursor.fetchall()
            if rows:
                columns = [col[0] for col in cursor.description]
                return [dict(zip(columns, row)) for row in rows]
        except pyodbc.ProgrammingError:
            pass
        if not cursor.nextset():
            return None