# Archivo: GSYCManufacBOMIngSQL.py
# Ruta: src\Consultas_SQL\SupYCtrol\GerenteSyC\UtilityMaterials\GSYCManufacBOMIngSQL.py
# Lenguaje: Python con Flask

import uuid
from datetime import datetime
from config import Productivo, ENVIRONMENT
from Consultas_SQL.conexion import get_connectionbdproductivo, get_connection, get_connectionERP

# Dependiendo del entorno, se selecciona la función de conexión adecuada
if Productivo:
    ConexionBD_VPS = get_connectionbdproductivo
else:
    ConexionBD_VPS = get_connection

ConexionBD_ERP = get_connectionERP

# ConexionBD_VPS: Conexión a la base de datos VPS donde crearemos tablas BOM
# ConexionBD_ERP: Conexión a la base de datos ERP (solo lectura) donde obtenemos datos BOM

class BOMExploder:
    
    def __init__(self):
        self.process_id = None
    #ok
    def explode_bom(self, part_num: str, revision: str) -> dict:
        """
        Explota una BOM multinivel y retorna información del proceso
        """
        self.process_id = str(uuid.uuid4())
        start_time = datetime.now()
        
        try:
            # Inicializar control de proceso
            self._init_process_control(part_num, revision)

            # Proceso de funciones operaciones
            PricingMO = self._get_pricingMO()
            #print('$$$$$$$',PricingMO, 'lbr=', PricingMO['LbrUnitCost']) 
                       
            # Explotar BOM recursivamente
            self._explode_recursive(PricingMO, part_num, revision, 0, 1)
            
            # Actualizar estadísticas del proceso
            processing_time = (datetime.now() - start_time).total_seconds() * 1000
            self._update_process_stats(processing_time)
            
            return {
                'success': True,
                'process_id': self.process_id,
                'message': 'BOM explodida exitosamente'
            }
            
        except Exception as e:
            self._mark_process_error(str(e))
            return {
                'success': False,
                'error': str(e),
                'process_id': self.process_id
            }
    #ok
    def _explode_recursive(self, part_num: str, revision: str, level: int, order_start: int):
        """Explota un nivel específico de la BOM recursivamente"""
        current_order = order_start
        
        # Obtener operaciones del nivel actual
        operations = self._get_operations(part_num, revision)
        for op in operations:            
            Cost = self._get_totalcost_operation(PricingMO, op)
            #print('%%%%', Cost)

            self._insert_operation(op, part_num, revision, level, current_order, Cost, PricingMO)
            current_order += 1
        
        # Obtener materiales del nivel actual
        materials = self._get_materials(part_num, revision)
        for material in materials:
            if material['Type'] == 'P':
                CostMat = self._get_pricingMaterials(material)                
            else:  # Para materiales manufacturados (Type == 'M')
                CostMat = {
                    'TranNum': None,
                    'TranDate': None,
                    'MtlUnitCost': None,
                    'LbrUnitCost': None,
                    'BurUnitCost': None,
                    'SubUnitCost': None,
                    'MtlBurUnitCost': None,
                    'ExtCost': 0,
                    'PO': None,
                    'Line': None,
                    'Status': 'CALCULADO'
                }            
            print('$$$$', CostMat)
            

            self._insert_material(material, part_num, revision, level, current_order, CostMat)
            current_order += 1
            
            # Si el material es fabricado, explotar sus componentes
            if material['Type'] == 'M':
                child_revision = self._get_latest_revision(material['PartNum'])
                if child_revision:
                    current_order = self._explode_recursive(
                        PricingMO,
                        material['PartNum'], 
                        child_revision, 
                        level + 1, 
                        current_order
                    )
        
        return current_order
    #ok
    def _get_materials(self, part_num: str, revision: str) -> list:
        """Obtiene materiales de la BOM desde Epicor ERP"""
        query = """
        SELECT 
            Erp.PartMtl.MtlSeq AS Seq,
            Erp.Part.TypeCode AS [Type],
            CASE Erp.Part.TypeCode 
                WHEN 'M' THEN 'Fabricado'
                WHEN 'P' THEN 'Material' 
                ELSE Erp.Part.TypeCode 
            END AS TipoDesc,
            Erp.PartMtl.MtlPartNum AS PartNum,
            
            (
                SELECT TOP 1 Erp.PartRev.RevisionNum 
                FROM Erp.PartRev 
                WHERE Erp.PartRev.Company = Erp.PartMtl.Company 
                    AND Erp.PartRev.PartNum = Erp.PartMtl.MtlPartNum 
                ORDER BY Erp.PartRev.EffectiveDate DESC 
            ) AS Rev,

            Erp.Part.PartDescription,
            LEFT(Erp.Part.PartDescription, 35) AS ShortDescription,
            Erp.PartMtl.QtyPer,
            Erp.PartMtl.UOMCode

        FROM 
            Erp.PartMtl 
        LEFT JOIN 
            Erp.Part ON Erp.PartMtl.Company = Erp.Part.Company 
            AND Erp.PartMtl.MtlPartNum = Erp.Part.PartNum 
        WHERE 
            Erp.PartMtl.Company = 'IGSA' 
            AND Erp.PartMtl.PartNum = ?
            AND Erp.PartMtl.RevisionNum = ?
        ORDER BY 
            Erp.PartMtl.MtlSeq
        """
        
        connection = ConexionBD_ERP()
        if not connection:
            raise Exception("Error de conexión a ERP")
            
        try:
            cursor = connection.cursor()
            cursor.execute(query, (part_num, revision))
            columns = [column[0] for column in cursor.description]
            return [dict(zip(columns, row)) for row in cursor.fetchall()]
        finally:
            connection.close()
    #ok
    def _get_operations(self, part_num: str, revision: str) -> list:
        """Obtiene operaciones de la BOM desde Epicor ERP"""
        query = """
        SELECT 
            Erp.PartOpr.OprSeq AS Seq,
            Erp.PartOpr.SubContract AS [Type],
            CASE Erp.PartOpr.SubContract 
                WHEN 0 THEN 'Mano de Obra' 
                WHEN 1 THEN 'Subcontrato' 
            END AS TipoDesc,
            Erp.PartOpr.OpCode,
            Erp.OpMaster.OpDesc,
            LEFT(Erp.OpMaster.OpDesc, 35) AS ShortDescription,
            Erp.PartOpr.EstProdHours,
            '' AS UOMCode                         

        FROM Erp.PartOpr 
        LEFT JOIN Erp.OpMaster ON Erp.PartOpr.Company = Erp.OpMaster.Company 
            AND Erp.PartOpr.OpCode = Erp.OpMaster.OpCode 
        WHERE Erp.PartOpr.Company = 'IGSA' 
            AND Erp.PartOpr.PartNum = ?
            AND Erp.PartOpr.RevisionNum = ?
        ORDER BY Erp.PartOpr.OprSeq
        """
        
        connection = ConexionBD_ERP()
        if not connection:
            raise Exception("Error de conexión a ERP")
            
        try:
            cursor = connection.cursor()
            cursor.execute(query, (part_num, revision))
            columns = [column[0] for column in cursor.description]
            return [dict(zip(columns, row)) for row in cursor.fetchall()]
        finally:
            connection.close()
    #ok
    def _get_latest_revision(self, part_num: str) -> str:
        """Obtiene la revisión más reciente de una parte"""
        query = """
        SELECT TOP 1 RevisionNum 
        FROM Erp.PartRev 
        WHERE Company = 'IGSA' AND PartNum = ?
        ORDER BY EffectiveDate DESC
        """
        
        connection = ConexionBD_ERP()
        if not connection:
            return None
            
        try:
            cursor = connection.cursor()
            cursor.execute(query, (part_num,))
            result = cursor.fetchone()
            return result[0] if result else None
        finally:
            connection.close()
    #ok
    def _init_process_control(self, part_num: str, revision: str):
        """Inicializa el registro de control del proceso en VPS"""
        query = """
        INSERT INTO BOM_Process_Control 
        (ProcessID, PartNumFather, RevisionNumFather, Status, ProcessDate)
        VALUES (?, ?, ?, 'Processing', GETDATE())
        """
        
        connection = ConexionBD_VPS()
        if not connection:
            raise Exception("Error de conexión a VPS")
            
        try:
            cursor = connection.cursor()
            cursor.execute(query, (self.process_id, part_num, revision))
            connection.commit()
        finally:
            connection.close()
    #ok
    def _insert_material(self, material: dict, part_father: str, rev_father: str, level: int, order: int):
        """Inserta un material en la tabla BOM_Materials (VPS)"""
        query = """
        INSERT INTO BOM_Materials 
        (ProcessID, PartNumFather, RevisionNumFather, [Order], [Level], Seq, [Type], 
        PartNum, Rev, PartDescription, ShortDescription, QtyPer, UOMCode)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """

        connection = ConexionBD_VPS()
        if not connection:
            raise Exception("Error de conexión a VPS")
            
        try:
            cursor = connection.cursor()
            cursor.execute(query, (
                self.process_id, part_father, rev_father, order, level,
                material['Seq'], material['Type'], material['PartNum'],
                material['Rev'], material['PartDescription'],
                material['ShortDescription'], material['QtyPer'], material['UOMCode'],
                CostMat['TranNum'], CostMat['TranDate'], CostMat['MtlUnitCost'], 
                CostMat['LbrUnitCost'], CostMat['BurUnitCost'], CostMat['SubUnitCost'], 
                CostMat['MtlBurUnitCost'], CostMat['ExtCost'],
                CostMat['PO'], CostMat['Line'], CostMat['Status']
            ))

        
            connection.commit()

        finally:
            connection.close()
    #ok
    def _insert_operation(self, operation: dict, part_father: str, rev_father: str, level: int, order: int):
        """Inserta una operación en la tabla BOM_Operations (VPS)"""
        query = """
        INSERT INTO BOM_Operations 
        (ProcessID, PartNumFather, RevisionNumFather, [Order], [Level], Seq, [Type], 
        OpCode, Rev, OpDesc, ShortDescription, EstProdHours, UOMCode, LbrUnitCost, BurUnitCost, ExtCost)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        connection = ConexionBD_VPS()
        if not connection:
            raise Exception("Error de conexión a VPS")
        try:
            cursor = connection.cursor()
            cursor.execute(query, (
                self.process_id, part_father, rev_father, order, level,
                operation['Seq'], operation['Type'], operation['OpCode'],
                None, operation['OpDesc'], operation['ShortDescription'],
                operation['EstProdHours'], operation['UOMCode'],
                PricingMO['LbrUnitCost'],
                PricingMO['BurUnitCost'],
                Cost  # <---- Aquí ahora se guarda en ExtCost
            ))
            connection.commit()
        finally:
            connection.close()
    #ok
    def _update_process_stats(self, processing_time_ms: float):
        """Actualiza estadísticas del proceso"""
        query = """
        UPDATE BOM_Process_Control 
        SET Status = 'Completed',
            TotalMaterials = (SELECT COUNT(*) FROM BOM_Materials WHERE ProcessID = ?),
            TotalOperations = (SELECT COUNT(*) FROM BOM_Operations WHERE ProcessID = ?),
            MaxLevel = (SELECT ISNULL(MAX([Level]), 0) FROM BOM_Materials WHERE ProcessID = ?),
            ProcessingTime_MS = ?
        WHERE ProcessID = ?
        """
        
        connection = ConexionBD_VPS()
        if not connection:
            return
            
        try:
            cursor = connection.cursor()
            cursor.execute(query, (
                self.process_id, self.process_id, self.process_id,
                int(processing_time_ms), self.process_id
            ))
            connection.commit()
        finally:
            connection.close()
    
    def _mark_process_error(self, error_message: str):
        """Marca el proceso como erróneo"""
        query = """
        UPDATE BOM_Process_Control 
        SET Status = 'Error', ErrorMessage = ?
        WHERE ProcessID = ?
        """
        
        connection = ConexionBD_VPS()
        if not connection:
            return
            
        try:
            cursor = connection.cursor()
            cursor.execute(query, (error_message, self.process_id))
            connection.commit()
        finally:
            connection.close()
    

    #----- Operations Cost ------
    def _get_pricingMO(self):
        """ Obtiene desde VPS """ 
        query = """
        SELECT 
            LbrUnitCost, 
            BurUnitCost
        FROM CostLbr
        """  

        connection = ConexionBD_VPS()
        if not connection:
            raise Exception("Error de conexión a VPS")
            
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            row = cursor.fetchone()
            if row:
                columns = [column[0] for column in cursor.description]
                return dict(zip(columns, row))
            else:
                raise Exception("No se encontraron datos de CostLbr")
        finally:
            connection.close()    

    
    def _get_totalcost_operation(self, pricingMO: dict, operation: dict):
        est_hours = float(operation.get('EstProdHours') or 0)
        lbr_unit_cost = float(pricingMO.get('LbrUnitCost') or 0)
        bur_unit_cost = float(pricingMO.get('BurUnitCost') or 0)
        total_cost = (est_hours * lbr_unit_cost) + (est_hours * bur_unit_cost)
        return total_cost
    

    #----------------------------------------------
    # FUNCIÓN PARA OBTENER INFORMACIÓN DE PRECIOS Y TRANSACCIONES DE MATERIALES DESDE EPICOR ERP
    # CONSULTA LA TABLA PARTTRAN PARA EXTRAER COSTOS 
    #----------------------------------------------
    def _get_pricingMaterials(self, material: dict):
        """Obtiene materiales de la BOM desde Epicor ERP"""
        query = """
        SELECT
            TranNum,
            TranDate,
            MtlUnitCost,
            LbrUnitCost,
            BurUnitCost,
            SubUnitCost,
            MtlBurUnitCost,
            PONum AS PO,
            POLine AS Line,
            ([MtlUnitCost] * ? + [LbrUnitCost] * ? + [BurUnitCost] * ? + [SubUnitCost] * ? + [MtlBurUnitCost] * ?) AS ExtCost,
            CASE            
                -- Si está en los últimos 90 días 
                WHEN TranDate >= DATEADD(day, -90, GETDATE()) THEN 'COSTEADOS'
                
                -- Si está entre 90 y 180 días 
                WHEN TranDate >= DATEADD(day, -180, GETDATE())
                    AND TranDate < DATEADD(day, -90, GETDATE()) THEN 'REVISION' 

                -- Si no cumple las condiciones anteriores (sin TranNum/TranDate o más de 180 días)
                ELSE 'INSERTAR'
            END AS Status                
                    
        FROM 
            Erp.PartTran 
        WHERE 
            Erp.PartTran.Company = 'IGSA' 
            AND Erp.PartTran.PartNum = ?
        ORDER BY Erp.PartTran.TranDate DESC 
        """
        
        connection = ConexionBD_ERP()
        if not connection:
            raise Exception("Error de conexión a ERP")
            
        try:
            cursor = connection.cursor()
            cursor.execute(query, (material['QtyPer'], material['QtyPer'], material['QtyPer'], material['QtyPer'], material['QtyPer'], material['PartNum']))
            row = cursor.fetchone()
            if row:
                columns = [column[0] for column in cursor.description]
                return dict(zip(columns, row))
            
            else:
                raise Exception("No se encontraron datos de PartTran")
        finally:
            connection.close()


# ------------------------------
# MODIFICA REGISTROS ESPECÍFICOS EN LA TABLA MATERIAL CAMBIANDO SU ESTADO A MANUAL
# ------------------------------
def get_status_manual(new_ext_cost: float, process_id: str, part_num: str):
    """Actualiza el ExtCost y cambia el Status a MANUAL en la base de datos"""
    # Validación adicional de datos
    connection = None
    try:
        connection = ConexionBD_VPS()
        if not connection:
            return {
                'success': False,
                'error': "Error de conexión a la base de datos"
            }

        cursor = connection.cursor()

        # Consulta UPDATE que no devuelve resultados
        update_query = """
        UPDATE BOM_Materials
        SET ExtCost = ?,
            Status = 'MANUAL'
        WHERE ProcessID = ?
          AND PartNum = ?
        """
        
        # Ejecutar la consulta de actualización
        cursor.execute(update_query, (new_ext_cost, process_id, part_num))
        
        # Verificar cuántas filas fueron afectadas
        row_count = cursor.rowcount
        
        connection.commit()
        
        if row_count == 0:
            return {
                'success': False,
                'error': f"No se encontró el registro. ProcessID: {process_id}, PartNum: {part_num}"
            }
            
        return {
            'success': True,
            'message': "Costo actualizado exitosamente",
            'updated_rows': row_count
        }

    except Exception as e:
        if connection:
            connection.rollback()
        return {
            'success': False,
            'error': f"Error en la base de datos: {str(e)}"
        }
    finally:
        if connection:
            connection.close()


def calculate_total_costs(process_id: str) -> dict:
    # Consulta SQL mejorada con mayor precisión y trazabilidad
    calculate_costs_query = """
    -- Materiales comprados agrupados por padre con precisión decimal
    WITH MaterialCosts AS (
        SELECT 
            PartNumFather AS PartNum,
            CAST(SUM(COALESCE(CAST(ExtCost AS DECIMAL(18,8)), 0)) AS DECIMAL(18,8)) AS MaterialCost,
            COUNT(*) AS MaterialComponents
        FROM BOM_Materials
        WHERE ProcessID = ? 
          AND Type = 'P'
        GROUP BY PartNumFather
    ),
    
    -- Costos de operaciones agrupados por padre con precisión decimal
    OperationCosts AS (
        SELECT 
            PartNumFather AS PartNum,
            CAST(SUM(COALESCE(CAST(ExtCost AS DECIMAL(18,8)), 0)) AS DECIMAL(18,8)) AS OperationCost,
            COUNT(*) AS OperationComponents
        FROM BOM_Operations
        WHERE ProcessID = ?
        GROUP BY PartNumFather
    ),
    
    -- Costos combinados con verificación de NULLs
    CombinedCosts AS (
        SELECT
            m.PartNum,
            CAST(COALESCE(m.MaterialCost, 0) AS DECIMAL(18,8)) AS MaterialCost,
            m.MaterialComponents,
            CAST(COALESCE(o.OperationCost, 0) AS DECIMAL(18,8)) AS OperationCost,
            o.OperationComponents,
            CAST(COALESCE(m.MaterialCost, 0) + COALESCE(o.OperationCost, 0) AS DECIMAL(18,8)) AS TotalCost
        FROM MaterialCosts m
        LEFT JOIN OperationCosts o ON m.PartNum = o.PartNum
        
        UNION
        
        SELECT
            o.PartNum,
            CAST(0 AS DECIMAL(18,8)) AS MaterialCost,
            0 AS MaterialComponents,
            CAST(o.OperationCost AS DECIMAL(18,8)) AS OperationCost,
            o.OperationComponents,
            CAST(o.OperationCost AS DECIMAL(18,8)) AS TotalCost
        FROM OperationCosts o
        WHERE NOT EXISTS (SELECT 1 FROM MaterialCosts m WHERE m.PartNum = o.PartNum)
    )
    
    SELECT 
        PartNum,
        MaterialCost,
        MaterialComponents,
        OperationCost,
        OperationComponents,
        TotalCost
    FROM CombinedCosts
    WHERE PartNum IS NOT NULL
    ORDER BY PartNum
    """
    
    update_fabricated_materials = """
    UPDATE BOM_Materials
    SET ExtCost = CAST(? AS DECIMAL(18,8)),
        Status = 'CALCULADO'
    WHERE ProcessID = ?
      AND PartNum = ?
      AND Type = 'M'  -- FABRICADO
    """
    
    get_fabricated_materials = """
    SELECT PartNum, 
           CAST(ExtCost AS DECIMAL(18,8)) AS ExtCost, 
           Status
    FROM BOM_Materials
    WHERE ProcessID = ? 
      AND Type = 'M'  -- FABRICADO
    """
    
    # Consulta para verificación de consistencia
    verification_query = """
    SELECT 
        PartNumFather, 
        SUM(COALESCE(CAST(ExtCost AS DECIMAL(18,8)), 0)) AS TotalMaterialCost,
        COUNT(*) AS Components
    FROM BOM_Materials 
    WHERE ProcessID = ? 
      AND Type = 'P'
    GROUP BY PartNumFather
    """
    
    database_connection = ConexionBD_VPS()
    if not database_connection:
        return {'success': False, 'error': "Error de conexión a la base de datos"}

    try:
        database_cursor = database_connection.cursor()
        
        # 1. Calcular costos por material fabricado con la nueva consulta
        database_cursor.execute(calculate_costs_query, (process_id, process_id))
        
        # Obtener datos con más detalle para trazabilidad
        cost_data = {}
        cost_breakdown = []
        for row in database_cursor.fetchall():
            part_num = row[0]
            cost_data[part_num] = {
                'material_cost': float(row[1]),
                'material_components': int(row[2]),
                'operation_cost': float(row[3]),
                'operation_components': int(row[4]),
                'total_cost': float(row[5])
            }
            cost_breakdown.append({
                'partNum': part_num,
                'materialCost': float(row[1]),
                'materialComponents': int(row[2]),
                'operationCost': float(row[3]),
                'operationComponents': int(row[4]),
                'totalCost': float(row[5])
            })
        
        if not cost_data:
            return {'success': False, 'error': "No se encontraron componentes para calcular"}
        
        # 2. Verificación de consistencia
        database_cursor.execute(verification_query, (process_id,))
        verification_data = []
        total_material_cost = 0.0
        for row in database_cursor.fetchall():
            part_num = row[0]
            verification_data.append({
                'partNum': part_num,
                'calculatedCost': cost_data.get(part_num, {}).get('total_cost', 0),
                'materialCost': float(row[1]),
                'components': int(row[2])
            })
            total_material_cost += float(row[1])
        
        # 3. Obtener materiales fabricados existentes
        database_cursor.execute(get_fabricated_materials, (process_id,))
        fabricated_materials = database_cursor.fetchall()
        
        # 4. Actualizar costos
        updated_materials = []
        for part_num, current_cost, current_status in fabricated_materials:
            new_cost = cost_data.get(part_num, {}).get('total_cost', 0)
            
            database_cursor.execute(
                update_fabricated_materials,
                (new_cost, process_id, part_num)
            )
            
            updated_materials.append({
                'partNum': part_num,
                'previousCost': float(current_cost) if current_cost else 0,
                'newCost': new_cost,
                'statusChange': 'CALCULADO',
                'materialComponents': cost_data.get(part_num, {}).get('material_components', 0),
                'operationComponents': cost_data.get(part_num, {}).get('operation_components', 0)
            })
        
        database_connection.commit()
        
        # Calcular totales
        total_calculated_cost = round(sum(v['total_cost'] for v in cost_data.values()), 5)
        total_operation_cost = round(sum(v['operation_cost'] for v in cost_data.values()), 5)
        
        return {
            'success': True,
            'updatedMaterials': updated_materials,
            'totalCalculatedCost': total_calculated_cost,
            'totalMaterialCost': round(total_material_cost, 5),
            'totalOperationCost': total_operation_cost,
            'verificationData': verification_data,
            'costBreakdown': cost_breakdown,
            'processId': process_id
        }
        
    except Exception as error:
        database_connection.rollback()
        return {
            'success': False,
            'error': str(error),
            'processId': process_id
        }
    finally:
        if database_connection:
            database_connection.close()
              

def get_total_cost_for_parent(process_id: str, part_num: str, revision: str) -> dict:
    """Función con mejor manejo de errores para SQL"""
    query = """
    SELECT
        SUM(COALESCE(BOM_Materials.ExtCost, 0)) AS TotalCostMaterial
    FROM
        BOM_Materials
    WHERE
        BOM_Materials.PartNumFather = ?
        AND BOM_Materials.RevisionNumFather = ?
        AND BOM_Materials.ProcessID = ?

    UNION ALL

    SELECT
        SUM(COALESCE(BOM_Operations.ExtCost, 0)) AS TotalCostOperation
    FROM 
        BOM_Operations
    WHERE
        BOM_Operations.PartNumFather = ?
        AND BOM_Operations.RevisionNumFather = ?
        AND BOM_Operations.ProcessID = ?
    """
    
    try:
        connection = ConexionBD_VPS()
        if not connection:
            raise Exception("No se pudo conectar a la base de datos")
        
        cursor = connection.cursor()
        cursor.execute(query, (part_num, revision, process_id, part_num, revision, process_id))
        
        results = cursor.fetchall()
        
        if not results:
            return {'success': False, 'error': "No se encontraron registros"}
            
        material_cost = float(results[0][0]) if results[0][0] is not None else 0.0
        operation_cost = float(results[1][0]) if len(results) > 1 and results[1][0] is not None else 0.0
        
        return {
            'success': True,
            'totalCost': material_cost + operation_cost,
            'materialCost': material_cost,
            'operationCost': operation_cost
        }
        
    except Exception as e:
        return {'success': False, 'error': f"Error en base de datos: {str(e)}"}
    finally:
        if connection:
            connection.close()
            
                       
    #def _get_totalcost_materials(self, pricingMat: dict, material: dict):
    #    Qty_Per = float(material.get('QtyPer') or 0)
    #    Mtl_Unit_Cost = float(pricingMat.get('MtlUnitCost') or 0)
    #    Lbr_Unit_Cost = float(pricingMat.get('LbrUnitCost') or 0)
    #    Bur_Unit_Cost = float(pricingMat.get('BurUnitCost') or 0)
    #    Sub_Unit_Cost = float(pricingMat.get('SubUnitCost') or 0)
    #    Mtl_BurUnit_Cost = float(pricingMat.get('MtlBurUnitCost') or 0)
    #    total_cost = (Qty_Per * Mtl_Unit_Cost) + (Qty_Per * Lbr_Unit_Cost) + (Qty_Per * Bur_Unit_Cost) + (Qty_Per * Sub_Unit_Cost) + (Qty_Per * Mtl_BurUnit_Cost)
    #    return total_cost


def get_bom_data(process_id: str) -> dict:
    """Obtiene datos completos de una BOM explodida"""
    query_materials = """
    SELECT 
        [Level], [Order], Seq, [Type], PartNum, Rev, 
        PartDescription, ShortDescription, QtyPer, UOMCode, TranNum,
        TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, 
        SubUnitCost, MtlBurUnitCost, ExtCost, PO, Line, Status
    FROM BOM_Materials 
    WHERE ProcessID = ?
    ORDER BY [Order]
    """
    
    query_operations = """
    SELECT 
        [Level], [Order], Seq, [Type], OpCode, 
        OpDesc, ShortDescription, EstProdHours, UOMCode, TranNum,
        TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, 
        SubUnitCost, MtlBurUnitCost, ExtCost, PO, Line
    FROM BOM_Operations 
    WHERE ProcessID = ?
    ORDER BY [Order]
    """
    
    query_control = """
    SELECT * FROM BOM_Process_Control WHERE ProcessID = ?
    """
    
    connection = ConexionBD_VPS()
    if not connection:
        return {'error': 'Error de conexión a VPS'}
    
    try:
        cursor = connection.cursor()
        
        # Obtener materiales
        cursor.execute(query_materials, (process_id,))
        materials_columns = [column[0] for column in cursor.description]
        materials = [dict(zip(materials_columns, row)) for row in cursor.fetchall()]
        
        # Obtener operaciones
        cursor.execute(query_operations, (process_id,))
        operations_columns = [column[0] for column in cursor.description]
        operations = [dict(zip(operations_columns, row)) for row in cursor.fetchall()]
        
        # Obtener control
        cursor.execute(query_control, (process_id,))
        control_columns = [column[0] for column in cursor.description]
        control_data = cursor.fetchone()
        control = dict(zip(control_columns, control_data)) if control_data else None
        
        return {
            'materials': materials,
            'operations': operations,
            'control': control
        }
        
    finally:
        connection.close()

def search_parts(query: str) -> list:
    """Busca partes en el ERP por nombre o código"""
    search_query = """
    SELECT TOP 10 
        p.PartNum, 
        p.PartDescription,
        pr.RevisionNum,
        p.TypeCode
    FROM Erp.Part p
    LEFT JOIN Erp.PartRev pr ON p.Company = pr.Company AND p.PartNum = pr.PartNum
    WHERE p.Company = 'IGSA' 
        AND p.TypeCode = 'M'  -- Solo manufacturados
        AND (p.PartNum LIKE ? OR p.PartDescription LIKE ?)
    ORDER BY p.PartNum, pr.EffectiveDate DESC
    """
    
    connection = ConexionBD_ERP()
    if not connection:
        return []
    
    try:
        cursor = connection.cursor()
        search_term = f'%{query}%'
        cursor.execute(search_query, (search_term, search_term))
        
        columns = [column[0] for column in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]
        
    finally:
        connection.close()