# Archivo: GSYCGetBomGenset.py
# Ruta: src/App/SupyCtrol_Module/GerenteSyC/UtilityMaterials/GSYCGetBomGenset.py
# Lenguaje: Python con Flask

from flask import jsonify, request
from Consultas_SQL.conexion import get_connectionERP
import datetime

def funciones_GetBomGenset(app):
    @app.route('/api/get_bom', methods=['GET'])
    def obtener_arbol_bom():
        jobNum_input = request.args.get('job_num')
        if not jobNum_input:
            return jsonify({"error": "Debe proporcionar un JobNum"}), 400

        print(f"Obteniendo árbol BOM para JobNum {jobNum_input}")
        connection = get_connectionERP()
        if connection is None:
            print("Error: no se pudo establecer la conexión con la base de datos ERP")
            return jsonify({"error": "Conexión a base de datos fallida"}), 500

        try:
            cursor = connection.cursor()
            cursor.execute(
                "SELECT PartNum, PartDescription, ProdQty, QtyCompleted, JobComplete, ClosedDate "
                "FROM Erp.JobHead WHERE JobNum = ? AND Company = 'IGSA'",
                (jobNum_input,)
            )
            row = cursor.fetchone()
            if not row:
                print(f"No se encontró el JobHead para JobNum {jobNum_input}")
                return jsonify({"error": "Trabajo no creado, favor de revisarlo en Seguimiento de trabajo"}), 404

            part_num, part_desc, prod_qty, qty_completed, job_complete, close_date = row
            ref_date = close_date if job_complete else datetime.datetime.now()

            root_node = {
                "Nodo": "1",
                "JobNum": jobNum_input,
                "Seq": "",
                "PartNum": part_num,
                "Descripcion": part_desc,
                "QtyRequerida": float(prod_qty) if prod_qty else 0.0,
                "QtyEmitida": float(qty_completed) if qty_completed else 0.0,
                "Completado": bool(job_complete),
                "TLAMaterialCost": 0.0,
                "TLALaborCost": 0.0,
                "TLABurdenCost": 0.0,
                "TLASubcontractCost": 0.0,
                "TLAMtlBurCost": 0.0,
                "Tipo": "Ensamblaje raíz",
                "children": []
            }

            def obtener_componentes(job_num, prefix, referencia, cost_factor=1.0, qty_factor=1.0):
                componentes = []
                cursor = connection.cursor()
                cursor.execute(
                    "SELECT jm.MtlSeq, jm.PartNum, jm.Description, jm.RequiredQty, jm.IssuedQty, jm.IssuedComplete, "
                    "jm.RevisionNum, jm.AssemblySeq, p.TypeCode "
                    "FROM Erp.JobMtl jm "
                    "INNER JOIN Erp.Part p ON p.PartNum = jm.PartNum AND p.Company = jm.Company "
                    "WHERE jm.JobNum = ? AND jm.Company = 'IGSA'",
                    (job_num,)
                )
                mtl_rows = cursor.fetchall()

                for idx, mtl in enumerate(mtl_rows, start=1):
                    mtl_seq, mtl_part, mtl_desc, req_qty, issued_qty, issued_complete, rev_num, asm_seq, type_code = mtl
                    node_id = f"{prefix}.{idx}"

                    nodo_comp = {
                        "Nodo": node_id,
                        "JobNum": "",
                        "Seq": int(mtl_seq) if mtl_seq is not None else "",
                        "PartNum": mtl_part,
                        "Descripcion": mtl_desc,
                        "QtyRequerida": (float(req_qty) if req_qty else 0.0) * qty_factor,
                        "QtyEmitida": (float(issued_qty) if issued_qty else 0.0) * qty_factor,
                        "Completado": bool(issued_complete),
                        "TLAMaterialCost": 0.0,
                        "TLALaborCost": 0.0,
                        "TLABurdenCost": 0.0,
                        "TLASubcontractCost": 0.0,
                        "TLAMtlBurCost": 0.0,
                        "Tipo": "",
                        "children": []
                    }

                    if type_code == 'M':
                        cursor_prod = connection.cursor()
                        cursor_prod.execute(
                            "SELECT JobNum FROM Erp.JobProd "
                            "WHERE Company = 'IGSA' AND TargetJobNum = ? AND TargetAssemblySeq = ? AND TargetMtlSeq = ?",
                            (job_num, asm_seq, mtl_seq)
                        )
                        prod = cursor_prod.fetchone()

                        if prod:
                            child_job = prod[0]
                            nodo_comp["Tipo"] = "Manufacturado cruzado"
                            cursor_jh = connection.cursor()
                            cursor_jh.execute(
                                "SELECT JobComplete, ClosedDate FROM Erp.JobHead WHERE JobNum = ? AND Company = 'IGSA'",
                                (child_job,)
                            )
                            child_info = cursor_jh.fetchone()
                            child_ref = child_info[1] if child_info and child_info[0] else datetime.datetime.now()
                            nodo_comp["JobNum"] = child_job

                            cursor_cost = connection.cursor()
                            cursor_cost.execute(
                                "SELECT TLAMaterialCost, TLALaborCost, TLABurdenCost, TLASubcontractCost, TLAMtlBurCost "
                                "FROM Erp.JobAsmbl WHERE JobNum = ? AND AssemblySeq = 0 AND Company = 'IGSA'",
                                (child_job,)
                            )
                            c_row = cursor_cost.fetchone()
                            if c_row:
                                c_mat, c_lab, c_bur, c_sub, c_mtlbur = c_row
                                nodo_comp.update({
                                    "TLAMaterialCost": float(c_mat) * cost_factor,
                                    "TLALaborCost": float(c_lab) * cost_factor,
                                    "TLABurdenCost": float(c_bur) * cost_factor,
                                    "TLASubcontractCost": float(c_sub) * cost_factor,
                                    "TLAMtlBurCost": float(c_mtlbur) * cost_factor
                                })
                            nodo_comp["children"] = obtener_componentes(child_job, node_id, child_ref, cost_factor, qty_factor)
                        else:
                            cursor_pt = connection.cursor()
                            cursor_pt.execute(
                                "SELECT TOP 1 JobNum, TranDate FROM Erp.PartTran "
                                "WHERE Company = 'IGSA' AND PartNum = ? AND RevisionNum = ? AND TranType = 'MFG-STK' "
                                "AND TranDate <= ? ORDER BY TranDate DESC",
                                (mtl_part, rev_num, referencia)
                            )
                            pt = cursor_pt.fetchone()
                            if pt:
                                child_job = pt[0]
                                nodo_comp["Tipo"] = "Manufacturado (sin cruce)"
                                cursor_jh = connection.cursor()
                                cursor_jh.execute(
                                    "SELECT ProdQty, JobComplete, ClosedDate FROM Erp.JobHead WHERE JobNum = ? AND Company = 'IGSA'",
                                    (child_job,)
                                )
                                child_head = cursor_jh.fetchone()
                                if child_head:
                                    child_prodqty, child_complete, child_close = child_head
                                else:
                                    child_prodqty = 0
                                if child_prodqty:
                                    proration_factor = float(req_qty) / float(child_prodqty)
                                else:
                                    proration_factor = 0.0

                                cursor_cost = connection.cursor()
                                cursor_cost.execute(
                                    "SELECT TLAMaterialCost, TLALaborCost, TLABurdenCost, TLASubcontractCost, TLAMtlBurCost "
                                    "FROM Erp.JobAsmbl WHERE JobNum = ? AND AssemblySeq = 0 AND Company = 'IGSA'",
                                    (child_job,)
                                )
                                c_row = cursor_cost.fetchone()
                                if c_row:
                                    c_mat, c_lab, c_bur, c_sub, c_mtlbur = c_row
                                    nodo_comp.update({
                                        "TLAMaterialCost": float(c_mat) * cost_factor * proration_factor,
                                        "TLALaborCost": float(c_lab) * cost_factor * proration_factor,
                                        "TLABurdenCost": float(c_bur) * cost_factor * proration_factor,
                                        "TLASubcontractCost": float(c_sub) * cost_factor * proration_factor,
                                        "TLAMtlBurCost": float(c_mtlbur) * cost_factor * proration_factor
                                    })
                                child_ref = child_close if child_complete and child_close else datetime.datetime.now()
                                nodo_comp["JobNum"] = child_job
                                nodo_comp["children"] = obtener_componentes(child_job, node_id, child_ref, cost_factor * proration_factor, proration_factor)
                            else:
                                nodo_comp["Tipo"] = "Manufacturado (sin cruce)"
                                nodo_comp["JobNum"] = "No trazable"
                    else:
                        nodo_comp["Tipo"] = "Material"
                        nodo_comp["JobNum"] = ""
                        nodo_comp["children"] = []

                    componentes.append(nodo_comp)
                return componentes

            root_node["children"] = obtener_componentes(jobNum_input, "1", ref_date)
            return jsonify([root_node])

        finally:
            connection.close()
            print("Conexión a la base de datos cerrada")