# Archivo: DMAccessEmployeeSQL.py
# Ruta: src\Consultas_SQL\SupYCtrol\DataMaster\DMAccessEmployeeSQL.py
# Lenguaje: Python con Flask

import datetime
import pytz
from Consultas_SQL.conexion import get_connection

def get_AccessEmployees():
    """Obtiene los empleados pendientes de aprobar desde la base de datos."""
    query = """
    SELECT
        UserRequests.RequestID,
        UserRequests.FirstName,
        UserRequests.LastName,
        Division.Division,
        Departament.Departament,
        UserRequests.Position,
        UserRequests.DirectSupervisor,
        UserRequests.PermissionType,
        FORMAT(UserRequests.CreatedAt, 'dd-MM-yyyy HH:mm') AS CreatedAt
    FROM UserRequests
    LEFT JOIN
        Division ON UserRequests.DivisionID = Division.DivisionID
    LEFT JOIN
        Departament ON UserRequests.DepartamentID = Departament.DepartamentID
    WHERE (RequestTypeID = 'EMPLEADO')
        AND Status = 'PENDIENTE'
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
        
        cursor = conn.cursor()
        cursor.execute(query)
        results = cursor.fetchall()

        return [{
                "RequestID":        row[0], 
                "FirstName":        row[1], 
                "LastName":         row[2], 
                "Division":         row[3], 
                "Departament":      row[4],
                "Position":         row[5], 
                "DirectSupervisor": row[6], 
                "PermissionType":   row[7], 
                "CreatedAt":        row[8]
                } for row in results]

    except Exception as e:
        print(f"[Error inesperado] Error obteniendo listado de empleados: {e}")
        return []
    finally:
        if conn:
            conn.close()
            
def get_info(request_id):
    """Obtiene la información completa de un empleado por su RequestID."""
    query = """
    SELECT
        FORMAT(UserRequests.CreatedAt, 'dd-MM-yyyy HH:mm') AS CreatedAt,
        FirstName,
        MiddleName,
        LastName,
        SecondLastName,
        RequestType,
        Position,
        DirectSupervisor,
        PermissionType,
        CorporateEmail,
        ContactPhone,
        EmployeeNumber,
        Company,
        Division,
        Departament,
        Comments
    FROM
        UserRequests
    LEFT JOIN 
        RequestType ON UserRequests.RequestTypeID = RequestType.RequestTypeID
    LEFT JOIN 
        Company ON UserRequests.CompanyID = Company.CompanyID
    LEFT JOIN 
        Division ON UserRequests.DivisionID = Division.DivisionID
    LEFT JOIN 
        Departament ON UserRequests.DepartamentID = Departament.DepartamentID
    WHERE RequestID = ?    
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")

        cursor = conn.cursor()
        cursor.execute(query, (request_id,))
        row = cursor.fetchone()

        if not row:
            return {"error": "Empleado no encontrado"}

        return {
            "CreatedAt": row[0],
            "FirstName": row[1],
            "MiddleName": row[2],
            "LastName": row[3],
            "SecondLastName": row[4],
            "RequestType": row[5],
            "Position": row[6],
            "DirectSupervisor": row[7],
            "PermissionType": row[8],
            "CorporateEmail": row[9],
            "ContactPhone": row[10],
            "EmployeeNumber": row[11],
            "Company": row[12],
            "Division": row[13],
            "Departament": row[14],
            "Comments": row[15]
        }

    except Exception as e:
        print(f"[Error] Error obteniendo información del empleado: {e}")
        return {"error": str(e)}
    finally:
        if conn:
            conn.close()

def get_all_request_types():
    """Obtiene todos los tipos de formulario disponibles."""
    query = """
    SELECT
        RequestTypeID,
        RequestType
    FROM
        RequestType
    ORDER BY
        RequestType
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
            
        cursor = conn.cursor()
        cursor.execute(query)
        results = cursor.fetchall()
        
        return [{"id": row[0], "name": row[1]} for row in results]
    except Exception as e:
        print(f"[Error] Error obteniendo tipos de formulario: {e}")
        return []
    finally:
        if conn:
            conn.close()
            
def get_roles_by_filters(company_id, division_id, departament_id):
    """Obtiene los roles filtrados por compañía, división y departamento."""
    query = """
    SELECT
        RoleID,
        RoleName
    FROM
        Roles
    WHERE
        CompanyID = ? AND
        DivisionID = ? AND
        DepartamentID = ?
    ORDER BY
        RoleName
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
            
        cursor = conn.cursor()
        cursor.execute(query, (company_id, division_id, departament_id))
        results = cursor.fetchall()
        
        return [{"id": row[0], "name": row[1]} for row in results]
    except Exception as e:
        print(f"[Error] Error obteniendo roles filtrados: {e}")
        return []
    finally:
        if conn:
            conn.close()
            
def get_all_companies():
    """Obtiene todas las compañías disponibles."""
    query = """
    SELECT
        CompanyID,
        Company
    FROM
        Company
    ORDER BY
        Company
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
            
        cursor = conn.cursor()
        cursor.execute(query)
        results = cursor.fetchall()
        
        return [{"id": row[0], "name": row[1]} for row in results]
    except Exception as e:
        print(f"[Error] Error obteniendo compañías: {e}")
        return []
    finally:
        if conn:
            conn.close()

def get_divisions_by_company(company_id):
    """Obtiene las divisiones para una compañía específica."""
    query = """
    SELECT
        DivisionID,
        Division
    FROM
        Division
    WHERE
        CompanyID = ?
    ORDER BY
        Division
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
            
        cursor = conn.cursor()
        cursor.execute(query, (company_id,))
        results = cursor.fetchall()
        
        return [{"id": row[0], "name": row[1]} for row in results]
    except Exception as e:
        print(f"[Error] Error obteniendo divisiones: {e}")
        return []
    finally:
        if conn:
            conn.close()

def get_departments_by_division(company_id, division_id):
    """Obtiene los departamentos para una compañía y división específicas."""
    query = """
    SELECT
        DepartamentID,
        Departament
    FROM
        Departament
    WHERE
        CompanyID = ? AND
        DivisionID = ?
    ORDER BY
        Departament
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
            
        cursor = conn.cursor()
        cursor.execute(query, (company_id, division_id))
        results = cursor.fetchall()
        
        return [{"id": row[0], "name": row[1]} for row in results]
    except Exception as e:
        print(f"[Error] Error obteniendo departamentos: {e}")
        return []
    finally:
        if conn:
            conn.close()
            
def check_if_email_exists(email):
    """Verifica si un correo electrónico ya existe en la tabla Users."""
    query = """
    SELECT COUNT(*) AS count
    FROM Users
    WHERE Email = ?
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
            
        cursor = conn.cursor()
        cursor.execute(query, (email,))
        row = cursor.fetchone()
        
        # Si el contador es mayor que 0, el correo ya existe
        return row[0] > 0
    except Exception as e:
        print(f"[Error] Error verificando si el correo existe: {e}")
        # En caso de error, asumimos que no existe para no bloquear el flujo
        return False
    finally:
        if conn:
            conn.close()

def update_info(data):
    """
    Actualiza la información de un empleado en las tablas UserRequests, Users, Profiles y UserRoles.
    
    Proceso:
    1. Actualiza el estado y fecha de revisión en UserRequests
    2. Crea un nuevo usuario en la tabla Users
    3. Obtiene el UserID generado automáticamente
    4. Crea un nuevo perfil en la tabla Profiles con los datos del modal
    5. Asocia el usuario con el rol seleccionado en la tabla UserRoles
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")

        cursor = conn.cursor()
        timezone = pytz.timezone("America/Mexico_City")
        review_date = datetime.datetime.now(timezone).strftime('%Y-%m-%d %H:%M:%S')
        
        # 1. Actualizar el estado y fecha de revisión en UserRequests
        query_requests = """
        UPDATE UserRequests
        SET 
            Status = 'APROBADO',
            ReviewDate = ?
        WHERE 
            RequestID = ?
        """
        cursor.execute(query_requests, (review_date, data["RequestID"]))
        
        # 2. Crear un nuevo usuario en la tabla Users
        query_users = """
        INSERT INTO Users (RequestTypeID, Email)
        VALUES (?, ?)
        """
        cursor.execute(query_users, (
            data["RequestTypeID"],
            data["CorporateEmail"]
        ))
        
        # 3. Obtener el UserID generado automáticamente
        query_get_user_id = """
        SELECT @@IDENTITY AS UserID
        """
        cursor.execute(query_get_user_id)
        user_id_row = cursor.fetchone()
        user_id = user_id_row[0]
        
        # 4. Crear un nuevo perfil en la tabla Profiles
        query_profiles = """
        INSERT INTO Profiles (
            UserID,
            RequestID,
            RequestTypeID,
            FirstName,
            MiddleName,
            LastName,
            SecondLastName,
            EmployeeNumber,
            Position,
            DirectSupervisor,
            PermissionType,
            Email,
            ContactPhone,
            CompanyID,
            DivisionID,
            DepartamentID
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        cursor.execute(query_profiles, (
            user_id,
            data["RequestID"],
            data["RequestTypeID"],
            data["FirstName"],
            data["MiddleName"],
            data["LastName"],
            data["SecondLastName"],
            data["EmployeeNumber"],
            data["Position"],
            data["DirectSupervisor"],
            data["PermissionType"],
            data["CorporateEmail"],
            data["ContactPhone"],
            data["CompanyID"],
            data["DivisionID"],
            data["DepartamentID"]
        ))
        
        # 5. Asociar el usuario con el rol seleccionado en la tabla UserRoles
        query_user_roles = """
        INSERT INTO UserRoles (UserID, RoleID)
        VALUES (?, ?)
        """
        cursor.execute(query_user_roles, (user_id, data["RoleID"]))

        conn.commit()

        # Construir la respuesta con los nuevos datos del usuario
        return {
            "user_id": user_id,
            "full_name": f"{data['FirstName']} {data['LastName']}",
            "email": data["CorporateEmail"]
        }   
        
        # return True

    except Exception as e:
        print(f"[Error] Error actualizando información del empleado: {e}")
        if conn:
            conn.rollback()
        return False
    finally:
        if conn:
            conn.close()
            
def update_user_request_rejected(request_id, review_date, comentario=None):
    """
    Actualiza el estado a RECHAZADO y fecha de revisión en UserRequests.
    El comentario (motivo del rechazo) no se guarda en la base de datos,
    solo se usa para el correo electrónico.
    """
    query = """
    UPDATE UserRequests
    SET 
        Status = 'RECHAZADO',
        ReviewDate = ?
    WHERE 
        RequestID = ?
    """
    params = (review_date, request_id)
    
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
        
        cursor = conn.cursor()
        cursor.execute(query, params)
        conn.commit()
        
        return {"message": "Solicitud rechazada correctamente"}
    except Exception as e:
        print(f"[Error SQL] Error actualizando estado de solicitud a RECHAZADO: {e}")
        return {"error": str(e)}
    finally:
        if conn:
            conn.close()