import os
from flask import Flask, send_file, send_from_directory,render_template,jsonify,redirect,url_for, request
import requests
import json
import os.path
from os.path import basename
import salespipeline
import Project_Arcane
import datetime
import subprocess
from zipfile import ZipFile
import Auto_Carrier
import pandas as pd
from Arcane_tools import sort_path
import numpy as np
from pptx import Presentation
from pptx.util import Inches, Pt
from pptx.enum.shapes import MSO_SHAPE_TYPE
from pptx.enum.shapes import MSO_AUTO_SHAPE_TYPE
from pptx.enum.shapes import MSO_CONNECTOR_TYPE
from pptx.enum.text import PP_PARAGRAPH_ALIGNMENT
from pptx.dml.color import RGBColor
from pptx.enum.dml import MSO_FILL_TYPE
from pptx.enum.dml import MSO_LINE_DASH_STYLE
import math
from pptx.enum.text import MSO_TEXT_UNDERLINE_TYPE
from pptx.enum.text import MSO_VERTICAL_ANCHOR
from create_docx import *
from Auto_Pricing_IEPL_IPLC import *
from Auto_Solution_IEPL_IPLC import *
import logging
from bot_arcana_tools import *
from arcane_p1_solution_tools import *
from Auto_Pricing_DIA import *
from Auto_Pricing_IPT import *
from Auto_Pricing_MPLS_EVPN import *
from Auto_Pricing_IEPL_IPLC import *
from Auto_Pricing_SDWAN import *
import glob
import traceback
from urllib.parse import quote
from testppt2pdf import *
# from flask_les
# import lessc
#from loguru import logger
#import sys

app = Flask(__name__)

project_name=""
link_solution=""
link_pricing_gm="" 
link_pricing_price_ref = ""
link_zip=""
projectname=""
recordID=""
current_dir = os.path.dirname(os.path.abspath(__file__))



#logger.remove(0)
#logger.add(sys.stderr, format="{time} | {level} | Elapsed:{elapsed.seconds:0>4} seconds | {module} | {line} | {message} ")

# def ppt_to_pdf(input_file, output_pdf):
#     command = ['libreoffice', '--headless', '--convert-to', 'pdf', '--outdir', '.', input_file]
#     subprocess.run(command)

#     # Rename the output file
#     os.rename(os.path.splitext(input_file)[0] + '.pdf', output_pdf)

pid = ""
@app.route("/")
def hello_world():
    return "<p>Hello, CMIers!!!</p>"

# @app.route('/static/css/custom.less')
# def custom_style():
#     return lessc('css/custom.less', force=True, minified=True, static_url='/static/css')

@app.route("/test")
def test():
    projectname="TEST PROJECT"
    return render_template('loading.html', value=projectname)

@app.route("/done/<string:projectname>/<string:euregion>")
def done(projectname,euregion):
    project_name = (str(projectname)).replace("_","")
    date = datetime.date.today().strftime("%Y%m%d")
    save_path = "/download/files/"
    output_pdf = date+"_"+projectname+"_solution.pdf"

    filename_pricing_gm = date+"_"+projectname+"_GM_Level_Price.xlsx" 
    filename_pricing_price_ref = date+"_"+projectname+"_Price_Reference.xlsx"
    filename_solution = date+"_"+projectname+"_solution.pptx"
    filename_zip = date+"_"+projectname+".zip"
    link_solution = save_path+output_pdf
    #link_pricing = save_path+filename_pricing
    link_pricing_gm = save_path+filename_pricing_gm
    link_pricing_price_ref = save_path+filename_pricing_price_ref
    link_zip = save_path+filename_zip
    return render_template('index.html',project_name=project_name, link_solution=link_solution,link_pricing_gm=link_pricing_gm, link_pricing_price_ref = link_pricing_price_ref,link_zip=link_zip,output_pdf=output_pdf, euregion=euregion)

@app.route("/<string:projectname>/<string:recordID>")
def loading(projectname,recordID):
    projectname=projectname
    recordID=recordID
    euregion = "nonEU"
    return render_template('loading.html',projectname=projectname, recordID=recordID,euregion=euregion)

@app.route("/IEPL/nonEU/<string:projectname>/<string:recordID>")
def smart_path_loading(projectname,recordID):
    projectname=projectname
    recordID=recordID
    euregion = "nonEU"
    return render_template('new_loading.html',projectname=projectname, recordID=recordID,euregion=euregion)

@app.route("/IEPL/EU/<string:projectname>/<string:recordID>")
def smart_path_loading_eu(projectname,recordID):
    projectname=projectname
    recordID=recordID
    euregion = "EU"
    return render_template('new_loading.html',projectname=projectname, recordID=recordID,euregion=euregion)

@app.route("/loading_arcana.html")
def loading_screen():
    return render_template("loading_arcana.html")

@app.route("/preview/<string:euregion>/<string:projectname>")
def smart_path_preview(projectname, euregion):
    date = datetime.date.today().strftime("%Y%m%d")
    save_path = "/download/files/"
    #filename_smart_path = save_path + date+"_"+projectname+"_Auto_Solution_Path.xlsx" 
    filename_smart_path = save_path +projectname+"_Auto_Solution_and_GM_Price.xlsx" 
    file_name = date+"_"+projectname+"_Auto_Solution_Path.xlsx" 
    return render_template('Graph.html',filename_smart_path=filename_smart_path, file_name = file_name, projectname = projectname, euregion = euregion)

@app.route("/IEPL_preview/<string:euregion>/<string:projectname>")
def IEPL_path_preview(projectname, euregion):
    save_path = "/download/files/"
    file_name = projectname+"_Auto_Solution_and_GM_Price.xlsx"
    selected_path = save_path + projectname+"_selected_path.xlsx"
    return render_template('Graph_arcana_test.html',selected_path=selected_path, file_name = file_name, projectname = projectname, euregion = euregion)

@app.route("/roundtable/<string:projectname>/<string:recordID>")
def roundtable(projectname,recordID):
    projectname=projectname
    recordID=recordID
    euregion= "roundtable"
    return render_template('loading.html',projectname=projectname, recordID=recordID,euregion=euregion)

@app.route("/EU/<string:projectname>/<string:recordID>")
def loadingEU(projectname,recordID):
    projectname=projectname
    recordID=recordID
    euregion= "EU"
    return render_template('loading.html',projectname=projectname, recordID=recordID,euregion=euregion)
#@app.route("/GTT/<string:recordID>")
#def gtt(recordID):
#    tat_url = "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal/"
#    tat_data = {"app_id" : "cli_a25457b15878d00d", 
#             "app_secret" : "3ioLqEY91u0NUNmkl4DvddCgM6QQz21v"}
#    tat_repsonse = requests.post(url = tat_url, data = tat_data)
#
#    bitable_token = 'Etq6biqqcaMEM8sYv5ncsM1Dneg'
#    bitable_table_token = 'Etq6biqqcaMEM8sYv5ncsM1Dneg'
#    payload = ''
#    tat = tat_repsonse.json()["tenant_access_token"] # response.json() covert json to Python object
#    # get all spreadsheet content(获取表格元数据): https://open.feishu.cn/document/ukTMukTMukTM/ugTMzUjL4EzM14COxMTN
#    header = {"Authorization" : "Bearer " + tat,
#            "Content-Type" : "application/json; charset=utf-8"}
#    bitable_response = requests.get(
#        url = "https://open.feishu.cn/open-apis/bitable/v1/apps/"+bitable_token+"/tables/"+bitable_table_token+"/records",
#        headers = header,
#        data = payload
#    )
#    Auto_Carrier.Auto_Carrier(recordID)
#    #solution
#    #pricing
#
#    result = "<h3>Finished, please return to bitable for results.<h3>"
#    return result

@app.route("/create_solution/<string:projectname>/<string:recordID>/<string:euregion>")
def diagram(projectname,recordID,euregion):
    print("Create Solution")
    print("EU:"+euregion)
    ###logger.info("Start program")
    result = "Project Name:"+str(projectname) + "      " + "Record ID:" + str(recordID)
    print(result)
    project_name = (str(projectname)).replace("_"," ")
    #field
    field_id = "fldoIHs4Uw"
    #record
    record_id = "recHaFYvEq"

    tat_url = "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal/"
    tat_data = {"app_id" : "cli_a25457b15878d00d", 
             "app_secret" : "3ioLqEY91u0NUNmkl4DvddCgM6QQz21v"}
    tat_repsonse = requests.post(url = tat_url, data = tat_data)
    tat = tat_repsonse.json()["tenant_access_token"]
    #print(result)
    #logger.info("tenan_access_token")

    # add records to a bitable called "Sales Weekly Report" : https://cmitry.feishu.cn/base/bascngNrhfUe7VOlq8J2dFONc9d?table=tblTL4gf9jci5fG4&view=vewWptyMSW
    #post_record_url="https://open.feishu.cn/open-apis/bitable/v1/apps/" + bitable_app_token + "/tables/" + table_id + "/records/" + recordID
    #post_record_header = {"Authorization" : "Bearer " + tat,
    #      "Content-Type" : "application/json; charset=utf-8"}
    date = datetime.date.today().strftime("%Y%m%d")
    #logger.info("Call Project_Arcane.py")
    Project_Arcane.main(project_name,euregion) #run project arcane main module
    #logger.info("Finish Project_Arcane.py")
    save_path = "/download/files/"
    #filename_pricing = date+"_"+projectname+"_pricing.zip" #temp file name
    filename_pricing_gm = date+"_"+projectname+"_GM_Level_Price.xlsx" 
    filename_pricing_price_ref = date+"_"+projectname+"_Price_Reference.xlsx"
    filename_solution = date+"_"+projectname+"_solution.pptx"
    filename_template = date+"_"+projectname+"_resale_quotation_email_template.docx"
    filename_zip = date+"_"+projectname+".zip"
    # with open(current_dir+'/download/files/'+filename_pricing, 'w') as f:
    #     f.write("Pretending we have pricing info in this file :)")
    #     f.write("Project Name is:"+projectname)
    # f.close()
    dir_path = current_dir+'/download/files/'
    #logger.info("Before ppt2pdf")
    #ppt to pdf
    
    
    input_file = filename_solution
    output_pdf = date+"_"+projectname+"_solution.pdf"

    # ppt_to_pdf(dir_path+input_file, dir_path+output_pdf)


    # specify the prefix of the xlsx files to be zipped
    prefix = date+"_"+projectname
    #logger.info("Before ZIP")
    # create a zip file object to write the zipped files to
    zip_file = ZipFile(os.path.join(dir_path, f'{prefix}_pricing.zip'), 'w')

    # loop through all the xlsx files in the directory that start with the specified prefix
    for file_name in os.listdir(dir_path):
        if file_name.startswith(prefix) and file_name.endswith('.xlsx'):
            # add the file to the zip file object
            zip_file.write(os.path.join(dir_path, file_name), basename(os.path.join(dir_path, file_name)))

    # close the zip file object
    zip_file.close()
    #create zip file
    with ZipFile(current_dir+'/download/files/'+filename_zip, "w") as newzip:
        path = current_dir+'/download/files/'
        newzip.write(path + filename_solution,basename(path + filename_solution))
        # newzip.write(path + filename_pricing,basename(path + filename_pricing))
        newzip.write(path + filename_pricing_gm,basename(path + filename_pricing_gm))
        if euregion!="EU":
            newzip.write(path + filename_pricing_price_ref,basename(path + filename_pricing_price_ref))
        newzip.write(path + filename_template,basename(path + filename_template))
        newzip.close()

    #link_solution = save_path+filename_solution
    link_solution = save_path+output_pdf
    #link_pricing = save_path+filename_pricing
    link_pricing_gm = save_path+filename_pricing_gm
    link_pricing_price_ref = save_path+filename_pricing_price_ref
    link_zip = save_path+filename_zip
    #logger.info("render html")
    #return redirect(url_for('done',projectname=projectname))
    return jsonify("OK")
    """
    down_url = "https://chinamobile.ddns.net:8443/download/files/"+projectname+".txt"
    post_data = {"fields": { "Download Link": down_url }}
    post_downloadlink = requests.put(url = post_record_url, headers = post_record_header, data = json.dumps(post_data))
    print(post_downloadlink)
    message = "Please click the url on column 'Download Link'!"
    #return message
    filepath = "/opt/flask/download/files/"+projectname+".txt"
    return send_file(filepath, as_attachment = True)
    """



@app.route("/salespipeline/generate_report/strategic")
def generatepipeline():
    salespipeline.salespipeline_report()
    #return ('', 204)
    return "<p>Program completed, please close this page after you see this message.</p>"

@app.route('/<path:filename>', methods=['GET'])
def download(filename):
    path = filename
    return send_file(path, as_attachment=True)


download_time = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
@app.route('/iepl_price/<string:euregion>/<string:filename>', methods=['POST']) 
def get_price(filename, euregion):  
    filterID = json.loads(request.data)
    print("----------------------------Auto Solution Diagram n Pricing starts in hello.py -----------")
    # Extract the part before '_Auto_.xlsx'
    extracted_project_name = filename.split('_Auto_Solution_and_GM_Price.xlsx')[0]
    #print(f'filename with filter id: {filterID} is {extracted_project_name}_selected_path.xlsx"')
    df_to_excel_final = pd.read_excel(current_dir+'/download/files/' + extracted_project_name + "_selected_path.xlsx")
    df_to_excel_final = df_to_excel_final.ffill(axis = 0)
    #print("--------- df from excel")
    #print(df_to_excel_final)
    # add segment number for price calculation
    #df_to_excel_final['Segment Number'] = np.repeat(0, len(df_to_excel_final))
    #segment_number_integer = 1
    #segment_number_decimal = 0.01
    #for i, row in df_to_excel_final.iterrows():
    #    if i == 0:
    #        segment_number_integer += 0
    #        segment_number_decimal += 0
    #    elif i >= 1 and (row['POP Code Path'] is None or row['POP Code Path'].strip() == ''):
    #        segment_number_integer += 0
    #        segment_number_decimal += 0.01
    #    else:  # where a new segment/path starts
    #        segment_number_integer += 1
    #        segment_number_decimal = 0.01
    #    segment_number = segment_number_integer + segment_number_decimal
    #    df_to_excel_final.loc[i, 'Segment Number'] = segment_number
    #df_to_excel_w_seg = df_to_excel_final.sort_values(by = ["Segment Number"], ascending = True).reset_index(drop=True)
    #print("---------------- df_to_excel_w_seg: Segment Number")
    #print(df_to_excel_w_seg['Segment Number'].values)
    # filter df on Filter ID
    #df_auto_solution_pricing = df_to_excel_w_seg.loc[df_to_excel_w_seg["Filter ID"].isin(filterID), :]
    df_auto_solution_pricing = df_to_excel_final.loc[df_to_excel_final["Filter ID"].isin(filterID), :]
    print("---------filtered df")
    print(df_auto_solution_pricing)

    save_path = df_auto_solution_pricing["save_path"].iloc[0]
    date = extracted_project_name.split('_')[0]
    Project_Name = df_auto_solution_pricing["Project_Name"].iloc[0]
    folder_path = df_auto_solution_pricing["folder_path"].iloc[0]
    Record_Count = df_auto_solution_pricing["Record_Count"].iloc[0]
    A_end_Address = df_auto_solution_pricing["A_end_Address"].iloc[0]
    Z_end_Address = df_auto_solution_pricing["Z_end_Address"].iloc[0]
    Product_Type = df_auto_solution_pricing["Product Type"].iloc[0]
    BW_need = df_auto_solution_pricing["BW_need"].iloc[0]
    Term = df_auto_solution_pricing["Term"].iloc[0] 
    Site_A_POP_recommend = df_auto_solution_pricing["Site_A_POP_recommend"].iloc[0] 
    Site_A_Location = df_auto_solution_pricing["Site_A_Location"].iloc[0] 
    Site_A_City = df_auto_solution_pricing["Site_A_City"].iloc[0] 
    Site_Z_POP_recommend = df_auto_solution_pricing["Site_Z_POP_recommend"].iloc[0] 
    Site_Z_Location = df_auto_solution_pricing["Site_Z_Location"].iloc[0] 
    Site_Z_City = df_auto_solution_pricing["Site_Z_City"].iloc[0] 
    Site_A = df_auto_solution_pricing["Site_A"].iloc[0] 
    Site_Z = df_auto_solution_pricing["Site_Z"].iloc[0] 
    On_off_net_recommend = df_auto_solution_pricing["On_off_net_recommend"].iloc[0] 
    Bandwidth = df_auto_solution_pricing["Bandwidth"].iloc[0] 
    Port_Type_Recommemded = df_auto_solution_pricing["Port_Type_Recommemded"].iloc[0]

    # ---------------------------------------------------------------------------
    #date_time_string = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
    # Initialize pricing quotation template:
    template_project_name_template = 'resale_quotation_email_template_template.docx'
    template_file_path = folder_path + template_project_name_template

    doc = Document(template_file_path)
    project_name = Project_Name.replace(" ", "_")
    template_save_path = save_path + download_time + "_" + project_name + "_resale_quotation_email_template.docx"
    doc.save(template_save_path)

    # Initialize pricing GM-level price excel template:
    excel_template_project_name_template = 'GM_level_price_excel_template_template.xlsx'
    excel_template_file_path = folder_path + excel_template_project_name_template
    workbook = load_workbook(excel_template_file_path)
    excel_template_save_path = save_path + download_time + "_" + project_name + "_GM_Level_Price.xlsx"
    workbook.save(excel_template_save_path)
    workbook.close()

    # Initialize pricing price reference excel template:
    #reference_template_project_name_template = 'Price_Reference_Template.xlsx'
    #reference_template_file_path = folder_path + reference_template_project_name_template
    reference_template_project_name_template =  date + "_" + project_name + "_Historical_Won_Order_Price_Reference.xlsx"
    reference_template_file_path = save_path + reference_template_project_name_template
    workbook = load_workbook(reference_template_file_path)
    reference_template_save_path = save_path + download_time + "_" + project_name + "_Historical_Won_Order_Price_Reference.xlsx"
    workbook.save(reference_template_save_path)
    workbook.close()

    # Initialize selected_path template:
    #reference_template_project_name_template = 'Price_Reference_Template.xlsx'
    #reference_template_file_path = folder_path + reference_template_project_name_template
    initial_path_template_project_name_template =  date + "_" + project_name + "_selected_path.xlsx"
    initial_path_template_file_path = save_path + initial_path_template_project_name_template
    workbook = load_workbook(initial_path_template_file_path)
    initial_path_template_save_path = save_path + download_time + "_" + project_name + "_selected_path.xlsx"
    workbook.save(initial_path_template_save_path)
    workbook.close()

    # Initialize Auto_Solution_and_GM_level_Price template:
    #reference_template_project_name_template = 'Price_Reference_Template.xlsx'
    #reference_template_file_path = folder_path + reference_template_project_name_template
    auto_path_template_project_name_template =  date + "_" + project_name + "_Auto_Solution_and_GM_Price.xlsx"
    auto_path_template_file_path = save_path + auto_path_template_project_name_template
    workbook = load_workbook(auto_path_template_file_path)
    auto_path_template_save_path = save_path + download_time + "_" + project_name + "_Auto_Solution_and_GM_Price.xlsx"
    workbook.save(auto_path_template_save_path)
    workbook.close()

    #-------------------------------------------------------------------------------------

    prs = Presentation(folder_path+'Auto_Solution_Template_IEPL.pptx')
    filename = download_time + "_" + project_name+"_solution.pptx"
    ##print("before save0")
    #print(f'pptx template filename: {filename}')
    prs.save(save_path+filename)

    #reopen template and use blank layout:
    #filename = date + "_" + Project_Name.replace(" ", "_") + "_solution.pptx"
    #prs = Presentation(save_path + filename)
    #prs = Presentation(r'C:/Users/liu4w/Dropbox/CMI/Auto_Workflow/Solution_Diagram/Auto_Solution_Test.pptx')
    #current_dir = os.path.dirname(os.path.abspath(__file__))
    #prs = Presentation(current_dir+'/Auto_Workflow/Solution_Diagram/Auto_Solution_Test.pptx')
    #prs = Presentation(folder_path + 'Auto_Solution_Test.pptx')
    #blank_slide_layout = prs.slide_layouts[6]
    #filter_id = df_auto_solution_pricing['Filter ID']
    
    #Project_Arcane.main(project_name,euregion) #run project arcane main module
    folder_path_to_legend = folder_path + 'IEPL_IPLC/'
    print("runnning add_IEPL_diagram_to_pptx in get_price")
    #add_IEPL_sol_diagram(df_solution_diagram_full = df_auto_solution_pricing, folder_path_to_legend = folder_path_to_legend, 
    #                     path_to_pop_address_info = folder_path_to_legend, save_path = save_path, saved_filename = filename)
    
    add_IEPL_diagram_to_pptx(df_solution_diagram_full = df_auto_solution_pricing, folder_path_to_legend = folder_path_to_legend, 
                             path_to_pop_address_info = folder_path_to_legend, save_path = save_path, saved_filename = filename)

    # initialize columns for df for auto pricing IEPL IPLC:
    auto_pricing_columns = ['Project Name', 'Product Type', 'POP Code Path', 'Segment', 'Segment Number', 'Filter ID',
                            'A end POP', 'A end Country', 'A end City', 'Proj Arcane A end POP Code', 
                            'Z end POP', 'Z end Country', 'Z end City', 'Proj Arcane Z end POP Code' ,
                            'Bandwidth(M)', 'Cable Name', 'Term(Months)', 'Protection', 
                            'end2end A End POP Code', 'end2end A end country', 'end2end A end city',
                            'end2end Z End POP Code', 'end2end Z end country', 'end2end Z end city',
                            'Site A Code', 'Site Z Code', 'Onnet/Offnet']
    #auto_pricing_columns = ['Project Name', 'Product Type', 'Segment Number', 'A end POP', 'Proj Arcane A end POP Code', 
    #                    'Z end POP', 'Proj Arcane Z end POP Code' , 'Bandwidth(M)', 'Cable Name', 'Term(Months)', 'Protection']
    df_IEPL_IPLC_Pricing_result = pd.DataFrame(columns=auto_pricing_columns)

    if Product_Type == 'IEPL' or Product_Type == 'IPLC':
        len_of_result_df = len(df_auto_solution_pricing)
        df_IEPL_IPLC_Pricing_add = pd.DataFrame(
            {
                'Project Name' : np.repeat(Project_Name, len_of_result_df), 
                'Product Type' : np.repeat(Product_Type, len_of_result_df), 
                'POP Code Path' : df_auto_solution_pricing['POP Code Path'],
                'Segment' : df_auto_solution_pricing['Segment'],
                'Segment Number' : df_auto_solution_pricing['Segment Number'],  
                'Filter ID' : df_auto_solution_pricing['Filter ID'],
                'A end POP' : df_auto_solution_pricing["A end POP"] , 
                'A end Country' : df_auto_solution_pricing["A end Country"], 
                'A end City': df_auto_solution_pricing["A end City"], 
                'Proj Arcane A end POP Code': df_auto_solution_pricing["A end POP Code"], 
                'Z end POP' : df_auto_solution_pricing["Z end POP"], 
                'Z end Country': df_auto_solution_pricing["Z end Country"], 
                'Z end City': df_auto_solution_pricing["Z end City"], 
                'Proj Arcane Z end POP Code': df_auto_solution_pricing["Z end POP Code"],
                'Bandwidth(M)': np.repeat(BW_need, len_of_result_df), 
                'Cable Name' : df_auto_solution_pricing["Cable Name"], 
                'Term(Months)': np.repeat(int(Term), len_of_result_df), 
                'Protection' : df_auto_solution_pricing["Protection Status"],
                'end2end A End POP Code' : np.repeat(Site_A_POP_recommend, len_of_result_df),
                'end2end A end country': np.repeat(Site_A_Location, len_of_result_df),
                'end2end A end city': np.repeat(Site_A_City, len_of_result_df),
                'end2end Z End POP Code' : np.repeat(Site_Z_POP_recommend, len_of_result_df),
                'end2end Z end country': np.repeat(Site_Z_Location, len_of_result_df),
                'end2end Z end city': np.repeat(Site_Z_City, len_of_result_df),
                'Site A Code': np.repeat(Site_A, len_of_result_df),
                'Site Z Code': np.repeat(Site_Z, len_of_result_df),
                'Onnet/Offnet': np.repeat(On_off_net_recommend, len_of_result_df)
            }
        )
        #print("df_IEPL_IPLC_Pricing_add : \n", df_IEPL_IPLC_Pricing_add)
        df_IEPL_IPLC_Pricing_result = pd.concat([df_IEPL_IPLC_Pricing_result, df_IEPL_IPLC_Pricing_add], ignore_index = True)
        # Pricing Quotation Template:
        if On_off_net_recommend == 'Onnet' and Site_A_Location != 'China' and Site_Z_Location != 'China':
            print("Onnet, resale layer 1 local loop out of China")
            export_resale_template(project_name=Project_Name, service_type = 'Layer 1 Local Loop', product_type = Product_Type,
                        A_end_country_or_loc = Site_A_Location, A_end_full_address = A_end_Address,
                        A_end_pop_code = Site_A_POP_recommend, B_end_full_address = '', 
                        B_end_pop_code = '', bandwidth = Bandwidth, port_type = Port_Type_Recommemded, 
                        term = Term, burstable_bw = '', QoS = '', IP_Address = '',
                        folder_path = save_path, date = download_time)
            export_resale_template(project_name=Project_Name, service_type = 'Layer 1 Local Loop', product_type = Product_Type,
                        A_end_country_or_loc = Site_Z_Location, A_end_full_address = Z_end_Address,
                        A_end_pop_code = Site_Z_POP_recommend, B_end_full_address = '', 
                        B_end_pop_code = '', bandwidth = Bandwidth, port_type = Port_Type_Recommemded, 
                        term = Term, burstable_bw = '', QoS = '', IP_Address = '',
                        folder_path = save_path, date = download_time)
        elif On_off_net_recommend == 'Offnet':
            print("Offnet, total resale")
            export_resale_template(project_name=Project_Name, service_type = 'IEPL/IPLC', product_type = Product_Type, 
                A_end_country_or_loc = Site_A_Location, A_end_full_address = A_end_Address,
                A_end_pop_code = '', B_end_full_address = Z_end_Address, 
                B_end_pop_code = '', bandwidth = Bandwidth, port_type = Port_Type_Recommemded, 
                term = Term, burstable_bw = '', QoS = '', IP_Address = '',
                folder_path = save_path, date = download_time) 

    print("df_IEPL_IPLC_Pricing_result : \n", df_IEPL_IPLC_Pricing_result)
    #IEPL_IPLC_main_func(IEPL_IPLC_df_from_solution = df_IEPL_IPLC_Pricing_result, folder_path = folder_path, date = date)
    #                    #end2end_A_End_POP_Code, end2end_A_end_country_or_region, end2end_A_end_city, 
    #                    #end2end_Z_End_POP_Code, end2end_Z_end_country_or_region, end2end_Z_end_city)
    export_IEPL_IPLC_gm_price(IEPL_IPLC_df_from_solution = df_IEPL_IPLC_Pricing_result, date = download_time)
    #prs.save(save_path + 'Auto_Solution_Test.pptx')
    #prs.save(save_path + date + "_" + Project_Name.replace(" ", "_") + "_solution.pptx")

    # ----------- Start zipping files -----------------------

    save_path = "/download/files/"
    #filename_pricing = date+"_"+projectname+"_pricing.zip" #temp file name
    filename_pricing_gm = download_time + "_" + project_name+"_GM_Level_Price.xlsx" 
    filename_pricing_price_ref = download_time + "_" + project_name+"_Historical_Won_Order_Price_Reference.xlsx"
    filename_solution = download_time + "_" + project_name+"_solution.pptx"
    #print(f'pptx filename_solution: {filename_solution}')
    filename_template = download_time + "_" + project_name+"_resale_quotation_email_template.docx"
    filename_zip = download_time + "_" + project_name+".zip"
    # with open(current_dir+'/download/files/'+filename_pricing, 'w') as f:
    #     f.write("Pretending we have pricing info in this file :)")
    #     f.write("Project Name is:"+projectname)
    # f.close()
    dir_path = current_dir+'/download/files/'
    #logger.info("Before ppt2pdf")
    #ppt to pdf
    
    
    input_file = filename_solution
    output_pdf = download_time + "_" + project_name+"_solution.pdf"
    ppt_to_pdf(input_file, output_pdf)


    # specify the prefix of the xlsx files to be zipped
    prefix = download_time + "_" + project_name
    #logger.info("Before ZIP")
    # create a zip file object to write the zipped files to
    zip_file = ZipFile(os.path.join(dir_path, f'{prefix}_pricing.zip'), 'w')

    # loop through all the xlsx files in the directory that start with the specified prefix
    for file_name in os.listdir(dir_path):
        if file_name.startswith(prefix) and file_name.endswith('.xlsx'):
            # add the file to the zip file object
            zip_file.write(os.path.join(dir_path, file_name), basename(os.path.join(dir_path, file_name)))

    # close the zip file object
    zip_file.close()
    #create zip file
    with ZipFile(current_dir+'/download/files/'+filename_zip, "w") as newzip:
        path = current_dir+'/download/files/'
        newzip.write(path + filename_solution,basename(path + filename_solution))
        # newzip.write(path + filename_pricing,basename(path + filename_pricing))
        newzip.write(path + filename_pricing_gm,basename(path + filename_pricing_gm))
        if euregion!="EU":
            newzip.write(path + filename_pricing_price_ref,basename(path + filename_pricing_price_ref))
        newzip.write(path + filename_template,basename(path + filename_template))
        newzip.close()


    print("Program completed for IEPL IPLC auto solution and pricing.")

    #link_solution = save_path+filename_solution
    #link_solution = save_path+output_pdf
    ##link_pricing = save_path+filename_pricing
    #link_pricing_gm = save_path+filename_pricing_gm
    #link_pricing_price_ref = save_path+filename_pricing_price_ref
    #link_zip = save_path+filename_zip
    #logger.info("render html")
    #return redirect(url_for('done',projectname=projectname))
    return jsonify("OK")
    #return redirect(url_for('IEPL_IPLC_done',euregion = euregion, projectname = date_time_string + "_" + project_name))


@app.route("/IEPL_IPLC_done/<string:euregion>/<string:projectname>") 
def IEPL_IPLC_done(projectname, euregion): 
    #project_name = (str(projectname)).replace("_","")
    #print(f"download_time in IEPL_IPLC_done is {download_time}")
    #print(f"projectname in IEPL_IPLC_done is {projectname}")
    project_name = download_time + "_" + "_".join(projectname.split("_")[1:])
    print(f"project_name in IEPL_IPLC_done is {project_name}")
    #date = datetime.date.today().strftime("%Y%m%d")
    save_path = "/download/files/"
    output_pdf = project_name+"_solution.pdf"
    #output_pdf = "20230825_Test_Project_IEPL_connecting_front-end_to_back-end_solution.pdf"

    filename_pricing_gm = project_name+"_GM_Level_Price.xlsx" 
    filename_pricing_price_ref = project_name+"_Historical_Won_Order_Price_Reference.xlsx"
    filename_solution = project_name+"_solution.pptx"
    filename_zip = project_name+".zip"
    link_solution = save_path+output_pdf
    #link_solution = save_path + "20230825_Test_Project_IEPL_connecting_front-end_to_back-end_solution.pdf"
    #link_pricing = save_path+filename_pricing
    link_pricing_gm = save_path+filename_pricing_gm
    link_pricing_price_ref = save_path+filename_pricing_price_ref
    link_zip = save_path+filename_zip
    return render_template('index.html',project_name=project_name, link_solution=link_solution,link_pricing_gm=link_pricing_gm, link_pricing_price_ref = link_pricing_price_ref,link_zip=link_zip,output_pdf=output_pdf, euregion=euregion) 


# for bot arcana
max_event_ids = 1000
event_ids = []
file_path = current_dir + '/arcana_cache/event_ids.txt'

def load_event_ids():
    try:
        with open(file_path, 'r') as file:
            for line in file:
                event_ids.append(line.strip())
    except FileNotFoundError:
        # If the file doesn't exist, start with an empty list
        open(file_path, 'w').close()

def add_event_id(event_id):
    event_ids.append(event_id)
    with open(file_path, 'a') as file:
        file.write(event_id + '\n')
    # Ensure we don't exceed the maximum number of stored event IDs
    if len(event_ids) > max_event_ids:
        oldest_event_id = event_ids.pop(0)
        # Re-write the file with one less entry
        with open(file_path, 'w') as file:
            file.writelines([eid + '\n' for eid in event_ids])
# Setup logger for the second route
bot_arcana_logger = logging.getLogger('BotArcanaLogger')
bot_arcana_logger.setLevel(logging.INFO)
bot_arcana_file_handler = logging.FileHandler('botarcana.log')
formatter = logging.Formatter('%(asctime)s - %(message)s')
bot_arcana_file_handler.setFormatter(formatter)
bot_arcana_logger.addHandler(bot_arcana_file_handler)

@app.route('/webhookarcana', methods=['POST'])
def webhookarcana():
    # for bot arcana
    VERIFICATION_TOKEN = '2iHbWZrCi6oS1jDcJwJrIeC0eq8zz3uM'
    #VERIFICATION_TOKEN = 'px9wwdcxyYmFClIVBXaiecctDtEOkLE3'
    thread_file_path = current_dir + '/Project_Arcane_P2/botarcana'
    arcane_file_path = current_dir + '/Project_Arcane_P2/arcane_data'
    folder_path = current_dir+'/Auto_Workflow/Solution_Diagram/'
    file_path_spr = current_dir + '/Project_Arcane_P2/arcane_data/spr_data'
    file_path_zfhs = current_dir + '/Project_Arcane_P2/arcane_data/zfhs_data'
    save_path = current_dir+'/download/files/'
    date = datetime.date.today().strftime("%Y%m%d")
    #date_time_string = now.strftime("%Y%m%d_%H%M%S")
    date_time_string = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
    #bot_name = 'Arcana'
    bot_name = 'CMI-nettest'
    #openai_key = '<TRUFFLEHOG_REDACTED_OPENAI>'
    assistant_id_main = 'asst_oI8CaJ4jxpJGhsbQDmp32QgE'
    assistant_id_spr = 'asst_pRXxdEDBgkiexShYoODieWaJ'
    assistant_id_zfhs = 'asst_5AlU3PkxiiBJhVuexJdKd5dn'
    assistant_id_wrap = 'asst_nOFhnimzkTi0ZMzFmCMsCvYb'
    assistant_id_wrap_IPT = 'asst_i82HgUUHAww3bWvpxKWChNOb'
    assistant_id_wrap_N2 = 'asst_sHqUhjMrVNdxpd2lXFtlLFmY'
    assistant_id_wrap_DIA = 'asst_jggYHW1DXWRsdwGwoMwseKBi'
    assistant_id_wrap_EVPN = 'asst_WPrv4kO3MtABLqqprGNzF63s'
    assistant_id_wrap_MPLS = 'asst_b5aw4WLqkmnDBs9Xow7wn7qz'
    assistant_id_wrap_IEPL = 'asst_VbYjNIa5vjIs426NVZk7P7HB'
    assistant_id_wrap_SDWAN = 'asst_hVWk055E0ykom8gM0TZDg8Se'

    data = request.get_json()
    print(data)
    # Check if received message is a v2.0 message, if not, break
    schema = data.get('schema')
    if not schema:
        # Handle URL verification if present
        if data.get('type') == 'url_verification':
            return jsonify({'challenge': data['challenge']})

        return_message = 'Received message is not in v2.0 version, pass...'
        print(return_message)
        return return_message

    elif schema == '2.0':
        bot_mention_indicator = 'no'
        bot_arcana_logger.info("Received data: %s", data)

        # Verify Lark Token
        token = data['header'].get('token')
        print("TOKEN:"+token)
        if token != VERIFICATION_TOKEN:
            return jsonify({'error': 'Invalid token'}), 403

        # Handle URL verification if present
        if data.get('type') == 'url_verification':
            return jsonify({'challenge': data['challenge']})

        event_id = data.get('header', {}).get('event_id', None)
        if event_id:
            if event_id in event_ids:
                print("Duplicate event received, ignoring.")
                return jsonify({'status': 'Duplicate, ignored'}), 200
            else:
                add_event_id(event_id)
                chat_id = data['event'].get('chat_id') or data['event'].get('message', {}).get('chat_id')
                if not chat_id:
                    bot_arcana_logger.error("chat_id is not present in the event data")
                    return jsonify({'error': 'Bad request', 'message': "chat_id not found"})#, 400

                # Check if the message sender is in Support role:
                try:
                    user_id = data['event']['sender']['sender_id']['user_id']
                except:
                    print('locating user_id error, rewrite the user id to unknown_user_id')
                    user_id = 'unknown_user_id'

                role = check_user_role(user_id)
                print(f'ROLE: {role}')

                # Check if the bot is mentioned in a group chat. If yes, proceed to openai
                event_data = data.get('event', {}).get('message', {})
                if not event_data:
                    bot_arcana_logger.error("event is not present in the event data")
                    return jsonify({'error': 'Bad request', 'message': "event not found"})#, 400
                mention_data = event_data.get('mentions')
                if not mention_data and event_data.get('chat_type') == 'group':
                    bot_arcana_logger.error("mentions is not present in the event data")
                    bot_mention_indicator = 'no'
                    return jsonify({'error': 'Bad request', 'message': "metions not found"})#, 400
                if not mention_data and event_data.get('chat_type') == 'p2p':
                    bot_arcana_logger.error("Private chat")
                    bot_mention_indicator = 'yes'
                if mention_data:
                    for mention in mention_data:
                        if bot_name in mention['name']:
                            bot_mention_indicator = 'yes'
                if bot_mention_indicator == 'yes':
                    tat = get_lark_access_token()

                    message_text = data['event'].get('message', {}).get('content')
                    message_text = extract_message_received(message_text)
                    print(f'message_text extracted: {message_text}')

                    # Check if need to reset thread upon request:
                    if 'RESET' in message_text.upper():
                        thread_directory = current_dir + '/Project_Arcane_P2/botarcana'
                        pattern = re.compile(r'oc_.*_thread_id\.txt')
                        reset_message = 'Received Incantation Spell RESET, chat history folder is empty already\n收到关键字RESET, 但chat history文件夹是空的耶~'
                        files = glob.glob(os.path.join(thread_directory, '*'))
                        for file_path in files:
                            if pattern.search(os.path.basename(file_path)):
                                try:
                                    os.remove(file_path)
                                    print(f"File {file_path} has been deleted successfully.")
                                    reset_message = 'Enchanted Incantation Spell RESET, AI chat history has been cleared\n收到关键字RESET, 已为您重置聊天记录'
                                except FileNotFoundError:
                                    print(f"File {file_path} file not found, it should alreay been a new chat")
                                    reset_message = 'Received Incantation Spell RESET, but chat history was not found, this is already a new chat\n收到关键字RESET, 但未查到聊天记录, 目前已经是一个新聊天啦'
                                except Exception as e:
                                    print(f"Error occurred while deleting file {file_path}: {e}")
                                    reset_message = 'Received Incantation Spell RESET, but an error occured, please contact admin (this is not suppose to happen though)\n收到关键字RESET, 但是哎呀报错了, 请联系管理员查看'
                        send_lark_message(chat_id=chat_id, message=reset_message, tat=tat)
                        return jsonify({'status': 'Reset'}), 200

                    # Mapping the message
                    mapping = keyword_mapping(file_path=file_path_spr)
                    mapping_reverse = keyword_mapping_reverse(file_path=file_path_spr)
                    message_text = keywords_converter(message=message_text, mapping=mapping)
                    print(f'message_text after mapping: {message_text}')

                    flag_value = 'TBD'
                    #latest_message = 'Empty'
                    latest_message_main = 'Empty'
                    loading_message = 'Arcana is channeling Arcane Magic...\n' \
                                    '收到~ Arcana正在想办法...'
                    thread_id = 'nonethread'
                    latest_message_main_json = {'Empty': 'Empty'}

                    # Send the question to main AI:
                    latest_message_main = thread_common(content=message_text, thread_file_path=thread_file_path,
                                            chat_id=chat_id, assistant_id=assistant_id_main, ai_name='main')
                    latest_message_main = keywords_converter(message=latest_message_main, mapping=mapping_reverse)
                    latest_message_main_json = json.loads(latest_message_main)

                    print(f'Main AI replied json: {latest_message_main_json}')
                    # Extract parameters from replied json:
                    try:
                        reply = latest_message_main_json['reply']
                    except:
                        reply = ' '
                    try:
                        question_to_spr = latest_message_main_json['question_to_spr']
                    except:
                        question_to_spr = ' '
                    try:
                        question_to_zfhs = latest_message_main_json['question_to_zfhs']
                    except:
                        question_to_zfhs = ' '
                    try:
                        language = latest_message_main_json['language']
                    except:
                        language = ' '
                    try:
                        product_name = latest_message_main_json['product']
                    except:
                        product_name = ' '
                    try:
                        bandwidth = latest_message_main_json['bandwidth']
                    except:
                        bandwidth = ' '
                    try:
                        burst_bandwidth = latest_message_main_json['burstable_bandwidth']
                    except:
                        burst_bandwidth = ' '
                    if (burst_bandwidth == '') or (burst_bandwidth is None) or (burst_bandwidth == 'None'):
                        burst_bandwidth = ' '
                    try:
                        location_a = latest_message_main_json['location_a']
                    except:
                        location_a = ' '
                    try:
                        location_b = latest_message_main_json['location_b']
                    except:
                        location_b = ' '
                    try:
                        on_off_net = latest_message_main_json['on-net/off-net']
                    except:
                        on_off_net = ' '
                    if (on_off_net == '') or (on_off_net is None) or (on_off_net == 'None'):
                        on_off_net = ' '
                    try:
                        term = latest_message_main_json['term']
                    except:
                        term = 12
                    if (term == '') or (term is None) or (term == 'None'):
                        term = 12
                    try:
                        wan_subnet = latest_message_main_json['wan_subnet']
                    except:
                        wan_subnet = 'WAN/30'
                    if (wan_subnet == '') or (wan_subnet is None) or (wan_subnet == 'None'):
                        wan_subnet = 'WAN/30'
                    try:
                        lan_subnet = latest_message_main_json['lan_subnet']
                    except:
                        lan_subnet = 'LAN/32'
                    if (lan_subnet == '') or (lan_subnet is None) or (lan_subnet == 'None'):
                        lan_subnet = 'LAN/32'
                    try:
                        China_traffic_percent = latest_message_main_json['china_traffic']
                    except:
                        China_traffic_percent = 0.01
                    if (China_traffic_percent == '') or (China_traffic_percent is None) or (China_traffic_percent == 'None'):
                        China_traffic_percent = 0.01
                    try:
                        if '%' in China_traffic_percent:
                            China_traffic_percent = int(China_traffic_percent.replace('%', ''))/100
                    except:
                        pass
                    try:
                        cable = latest_message_main_json['cable']
                    except:
                        cable = ' '
                    if (cable == '') or (cable is None) or (cable == 'None'):
                        cable = ' '
                    try:
                        equipment_type = latest_message_main_json['equipment_type']
                    except:
                        equipment_type = ' '
                    if (equipment_type == '') or (equipment_type is None) or (equipment_type == 'None'):
                        equipment_type = ' '
                    try:
                        flag_value = latest_message_main_json['flag']
                    except:
                        flag_value = 'No Flag Value Read'

                    print(f'Extracted Reply: {reply}')
                    print(f'Extracted Q to SPR: {question_to_spr}')
                    print(f'Extracted Q to ZFHS: {question_to_zfhs}')
                    print(f'Extracted Language: {language}')
                    print(f'Extracted Product: {product_name}')
                    print(f'Extracted Bandwidth: {bandwidth}')
                    print(f'Extracted Burstable Bandwidth: {burst_bandwidth}')
                    print(f'Extracted Location A: {location_a}')
                    print(f'Extracted Location B: {location_b}')
                    print(f'Extracted On-net/Off-net: {on_off_net}')
                    print(f'Extracted Term: {term}')
                    print(f'Extracted WAN Subnet: {wan_subnet}')
                    print(f'Extracted LAN Subnet: {lan_subnet}')
                    print(f'Extracted China Traffic: {China_traffic_percent}')
                    print(f'Extracted Cable: {cable}')
                    print(f'Extracted Equipment Type: {equipment_type}')
                    print(f'Extracted Flag Value: {flag_value}')
                    
                    # Re-write on/off-net:
                    if on_off_net.upper() == 'ON-NET' or on_off_net.upper() == 'ONNET':
                        on_off_net = 'On-net'
                    if on_off_net.upper() == 'OFF-NET' or on_off_net.upper() == 'OFFNET':
                        on_off_net = 'Off-net'


                    # extract dc code from location a & b
                    if 'EVPN' not in product_name and 'MPLS' not in product_name:
                        if location_a != ' ':
                            print('now checking location_a_pop_requested by users')
                            location_a_pop = set_recommend_pop(location_a, folder_path)
                        if location_b != ' ':
                            print('now checking location_b_pop_requested by users')
                            location_b_pop = set_recommend_pop(location_b, folder_path)
                    HK_pattern = r'\bHK\b'
                    # Find all matches of 'HK' as a standalone word
                    if location_a != ' ':
                        location_a = re.sub(HK_pattern, 'Hong Kong', location_a, flags=re.IGNORECASE)
                        if 'sg3' in location_a.lower():
                            location_a = 'Singapore Equinix SG3'
                        if 'hk1' in location_a.lower():
                            location_a = 'China Hong Kong Goodman Global Gateway Equinix HK1'
                    if location_b != ' ' or location_b != '':
                        location_b = re.sub(HK_pattern, 'Hong Kong', location_b, flags=re.IGNORECASE)
                        if 'sg3' in location_b.lower():
                            location_b = 'Singapore Equinix SG3'
                        if 'hk1' in location_b.lower():
                            location_b = 'China Hong Kong Goodman Global Gateway Equinix HK1'
                    # Send the reply from openai to lark:
                    send_lark_message(chat_id=chat_id, message=reply, tat=tat)

                    #if scenario_value == 'Arcane':
                    latest_message_spr = ' '
                    latest_message_zfhs = ' '
                    # Once the flag is Complete, execute Arcane, SPR, and ZFHS:
                    if flag_value.lower() == 'complete':
                        send_lark_message(chat_id=chat_id,message='Now calculating Solution and GM Price...', tat=tat)
                        #if scenario_value == 'Arcane':
                        if product_name == 'Global DIA':
                            product = 'Overseas DIA'
                        elif 'IPT' in product_name and 'CM-ONNET' in product_name:
                            product = 'IPT (CM-ONNET)'
                        elif 'IPT' in product_name and 'Global Transit' in product_name:
                            product = 'IPT (GT)'
                        elif 'IPT' in product_name and 'Blended' in product_name:
                            product = 'IPT (Blended Transit)'
                        else:
                            print(f'product_name is {product_name}')
                            product = product_name

                        print(f'product is {product}')
                        
                        #current_datetime = datetime.datetime.now().time().strftime('%H%M')
                        #project_name= f"{product_name}_{bandwidth}"
                        #project_name = (str(project_name)).replace(" ","_")
                        #print(f'arcana_project_name is: {project_name}')

                        #if location_b and location_b.strip() and location_b != 'None':
                        #    project_name= f"{location_a}_to_{location_b}_{product_name}_{bandwidth}"
                        #else:
                        #    project_name= f"{location_a}_{product_name}_{bandwidth}"
                        project_name= f"{product}_{bandwidth}"
                        #project_name = (str(project_name)).replace("，", " ").replace(",", " ").replace(" ","_")
                        if 'IEPL' in product or 'IPLC' in product:
                            project_name = (str(project_name)).replace("，", " ").replace(",", " ").replace(" ","_")
                        else:
                            project_name = (str(project_name)).replace(" ","_")
                        print(f'arcana_project_name is: {project_name}')

                        excel_template_project_name_template = 'GM_level_price_excel_template_template.xlsx'
                        excel_template_file_path = folder_path + excel_template_project_name_template
                        workbook = load_workbook(excel_template_file_path)
                        excel_template_save_path = save_path + date_time_string + "_" + project_name + "_GM_Level_Price.xlsx"
                        workbook.save(excel_template_save_path)
                        workbook.close()

                        # extract sender_info
                        if 'sender' in data['event']:
                            data_sender_id = data['event']['sender']['sender_id']['open_id']
                        else:
                            data_sender_id = "ou_3a4c4c012257386dd319d4e8c80c7bf9" # if error, use christine's open_id
                        
                        try:
                            # Run Project Arcane P1 here:
                            if product.__contains__('DIA'):
                                solution_message, sol_df_for_DIA_Pricing = Arcana_Auto_Solution_DIA(Project_Name = project_name, Product_Type = [product], Bandwidth = [bandwidth], 
                                                                                                    Port_Type = [' '], Site_A = [' '], Site_A_POP = [location_a_pop], A_end_Address = [location_a],
                                                                                                    WAN_Subnet = [wan_subnet], LAN_IP = [lan_subnet], On_off_net = [on_off_net], LLC_By = [' '], XC_By = [' '],
                                                                                                    Term = [term], Written_By = [' '], CE_Site_list = [' '], CE_node_list = [' '], Record_Count = 1,
                                                                                                    PPT_width = ' ', PPT_height = ' ',folder_path = folder_path, save_path = save_path, date = date_time_string)
                                pricing_gm_message, pricing_reference_message = Arcana_DIA_main_func(DIA_df_from_solution = sol_df_for_DIA_Pricing, folder_path = folder_path, date = date_time_string, open_id=data_sender_id)
                            elif (product.__contains__('IPT')) or (product.__contains__('BGP')) or (product.__contains__('CMIN2 CM-ONNET')) or (product.__contains__('CMIN2 Global Transit')):
                                solution_message, sol_df_for_IPT_Pricing = Arcana_Auto_Solution_IPT(Project_Name = project_name, Product_Type = [product], Bandwidth = [bandwidth], Burst_Bandwidth = [burst_bandwidth], 
                                                                                                    Port_Type = [' '], Site_A = [' '], Site_A_POP = [location_a_pop], A_end_Address = [location_a], WAN_Subnet = [wan_subnet], 
                                                                                                    LAN_IP = [lan_subnet], On_off_net = [on_off_net], LLC_By = [' '], XC_By = [' '], Term = [term], Written_By = [' '], 
                                                                                                    CE_Site_list = [' '], CE_node_list = [' '], Record_Count = 1, PPT_width = ' ', PPT_height = ' ', 
                                                                                                    folder_path = folder_path, save_path = save_path, China_Traffic = [China_traffic_percent], China_BGP_Route = ['Domestic Transit'], date = date_time_string)
                                if product.__contains__('IPT') or product.__contains__('CMIN2'):
                                    pricing_gm_message, pricing_reference_message = Arcana_IPT_main_FUNC(folder_path = folder_path, IPT_df_from_solution = sol_df_for_IPT_Pricing, date = date_time_string)
                                if product.__contains__('BGP'):
                                    pricing_gm_message, pricing_reference_message = Arcana_BGP_main_FUNC(folder_path = folder_path, ChinaBGP_df_from_solution = sol_df_for_IPT_Pricing, date = date_time_string)
                            elif product.__contains__('MPLS'):
                                location_a_mpls_list = re.split(r';\s*', location_a)
                                location_a_mpls_pop_list = []
                                for location_a_mpls in location_a_mpls_list:
                                    location_a_mpls_pop = ' '
                                    if location_a_mpls != ' ':
                                        print('now checking location_mpls_pop_requested by users')
                                        location_a_mpls_pop = set_recommend_pop(location_a_mpls, folder_path)
                                        location_a_mpls_pop_list.append(location_a_mpls_pop)
                                # If there is a Location B, add it to location list and pop list:
                                print(f'location_b: {location_b}')
                                pattern_location_b = r'^\s*$'
                                if location_a != ' ' and not re.match(pattern_location_b, location_b):
                                    print('now checking location_b_pop_requested by users')
                                    location_b_mpls_pop = set_recommend_pop(location_b, folder_path)
                                    location_a_mpls_list.append(location_b)
                                    location_a_mpls_pop_list.append(location_b_mpls_pop)
                                print(f'Product is {product}.\nExtracted Location A MPLS LIST: {location_a_mpls_list}')
                                print(f'Product is {product}, the POP list is:\n{location_a_mpls_pop_list}')
                                # Convert all parameters to list
                                list_length = len(location_a_mpls_list)
                                solution_message, sol_df_for_MPLS_Pricing = Arcana_Auto_Solution_MPLS(Project_Name = project_name, Product_Type = [product]*list_length, Bandwidth = [bandwidth]*list_length, Port_Type = [' ']*list_length, Site_A = [' ']*list_length, 
                                                                                                    Hub = [' ']*list_length, Spoke_List = [' ']*list_length, Site_A_POP = location_a_mpls_pop_list, A_end_Address = location_a_mpls_list, WAN_Subnet = [' ']*list_length, 
                                                                                                    LAN_IP = [' ']*list_length, On_off_net = [on_off_net]*list_length, LLC_By = [' ']*list_length, XC_By = [' ']*list_length, Term = [term]*list_length, Written_By = [' ']*list_length, 
                                                                                                    CE_Site_list = [' ']*list_length,CE_node_list = [' ']*list_length, Record_Count = list_length, PPT_width = ' ', PPT_height = ' ', 
                                                                                                    folder_path = folder_path, save_path = save_path, QoS_Voice = ['0']*list_length,
                                                                                                    QoS_Video = ['0']*list_length, QoS_P1 = ['0']*list_length, QoS_P2 = ['0']*list_length, QoS_P3 = ['0']*list_length, QoS_Default = ['0']*list_length, date = date_time_string) 
                                pricing_gm_message, pricing_reference_message = Arcana_MPLS_main(project_name = project_name, MPLS_df_from_solution = sol_df_for_MPLS_Pricing, folder_path = folder_path, date = date_time_string)
                            elif product.__contains__('EVPN'):
                                location_a_evpn_list = re.split(r';\s*', location_a)
                                location_a_evpn_pop_list = []
                                for location_a_evpn in location_a_evpn_list:
                                    location_a_evpn_pop = ' '
                                    if location_a_evpn != ' ':
                                        print('now checking location_evpn_pop_requested by users')
                                        location_a_evpn_pop = set_recommend_pop(location_a_evpn, folder_path)
                                        location_a_evpn_pop_list.append(location_a_evpn_pop)
                                # If there is a Location B, add it to location list and pop list:
                                if location_b != ' ' and location_b != '':
                                    print('now checking location_b_pop_requested by users')
                                    location_b_evpn_pop = set_recommend_pop(location_b, folder_path)
                                    location_a_evpn_list.append(location_b)
                                    location_a_evpn_pop_list.append(location_b_evpn_pop)
                                print(f'Product is {product}.\nExtracted Location A LIST: {location_a_evpn_list}')
                                print(f'Product is {product}, the POP list is:\n{location_a_evpn_pop_list}')
                                # Convert all parameters to list
                                list_length = len(location_a_evpn_list)
                                solution_message, sol_df_for_EVPN_Pricing = Arcana_Auto_Solution_EVPN(Project_Name = project_name, Product_Type = [product]*list_length, Bandwidth = [bandwidth]*list_length, Port_Type = [' ']*list_length, Site_A = [' ']*list_length,
                                                                                                    Site_A_POP = location_a_evpn_pop_list, A_end_Address = location_a_evpn_list, On_off_net = [on_off_net]*list_length, LLC_By = [' ']*list_length, XC_By = [' ']*list_length, 
                                                                                                    Term = [term]*list_length, Written_By = [' ']*list_length, Record_Count = list_length, PPT_width = ' ', PPT_height = ' ', folder_path = folder_path, save_path = save_path, date = date_time_string)
                                pricing_gm_message, pricing_reference_message = Arcana_EVPN_main(project_name = project_name, EVPN_df_from_solution = sol_df_for_EVPN_Pricing, folder_path = folder_path, date = date_time_string)
                            elif product.__contains__('IEPL') or product.__contains__('IPLC'):
                                solution_message, sol_df_for_IEPL_IPLC_Pricing = Arcana_Auto_Solution_IEPL_IPLC(Project_Name = project_name, Product_Type = [product], Bandwidth = [bandwidth], Port_Type = [' '], Cable = [cable],
                                                                                                                Port_Type_Z = [' '], Protection = [' '], Site_A = [' '], Site_A_POP = [location_a_pop], A_end_Address = [location_a], 
                                                                                                                Site_Z = [' '], Site_Z_POP = [location_b_pop], Z_end_Address = [location_b], Z_LLC_By = [' '], Z_XC_By = [' '], 
                                                                                                                On_off_net = [on_off_net], LLC_By = [' '], XC_By = [' '], Term = [term], Written_By = [' '], 
                                                                                                                Seg_1 = [' '], Seg_1_POP = [' '], Seg_2 = [' '], Seg_2_POP = [' '], Seg_3 = [' '], Seg_3_POP = [' '], 
                                                                                                                Seg_4 = [' '], Record_Count = 1, PPT_width = ' ', PPT_height = ' ', chat_id = chat_id, tat = tat,
                                                                                                                folder_path = folder_path, save_path = save_path, date = date_time_string)
                                pricing_gm_message, pricing_reference_message = Arcana_IEPL_IPLC_main_func(IEPL_IPLC_df_from_solution = sol_df_for_IEPL_IPLC_Pricing, folder_path = folder_path, date = date_time_string)
                            elif product.__contains__('SDWAN'):
                                location_a_sdwan_list = re.split(r',\s*', location_a)
                                location_a_sdwan_pop_list = []
                                for location_a_sdwan in location_a_sdwan_list:
                                    location_a_sdwan_pop = ' '
                                    if location_a_sdwan != ' ':
                                        print('now checking location_sdwan_pop_requested by users')
                                        location_a_sdwan_pop = set_recommend_pop(location_a_sdwan, folder_path)
                                        location_a_sdwan_pop_list.append(location_a_sdwan_pop)
                                # If there is a Location B, add it to location list and pop list:
                                print(f'location_b: {location_b}')
                                pattern_location_b = r'^\s*$'
                                if location_a != ' ' and not re.match(pattern_location_b, location_b):
                                    print('now checking location_b_pop_requested by users')
                                    location_b_sdwan_pop = set_recommend_pop(location_b, folder_path)
                                    location_a_sdwan_list.append(location_b)
                                    location_a_sdwan_pop_list.append(location_b_sdwan_pop)
                                print(f'Product is {product}.\nExtracted Location A SDWAN LIST: {location_a_sdwan_list}')
                                print(f'Product is {product}, the POP list is:\n{location_a_sdwan_pop_list}')
                                # Convert all parameters to list
                                list_length = len(location_a_sdwan_list)
                                solution_message, sol_df_for_SDWAN_Pricing = Auto_Solution_SDWAN(Project_Name=project_name, Equip_Type=[equipment_type]*list_length, Bandwidth = [bandwidth]*list_length,
                                                                                                    A_end_Address = location_a_sdwan_list, On_off_net = [on_off_net]*list_length, 
                                                                                                    Term = [term]*list_length, Record_Count = list_length, folder_path = folder_path, save_path = save_path, date = date_time_string)
                                pricing_gm_message, pricing_reference_message = Arcana_SDWAN_main_func(SDWAN_df_from_solution = sol_df_for_SDWAN_Pricing, folder_path = folder_path, date = date_time_string)
                                #SDWAN_solution_test_message = f'This is SDWAN solution test message:\n'\
                                #                                f'Solution Message:\n'\
                                #                                f'{solution_message}\n\n'\
                                #                                f'sol_df_for_SDWAN_Pricing:'\
                                #                                f'{sol_df_for_SDWAN_Pricing.to_string()}'
                                #send_lark_message(chat_id=chat_id, message=SDWAN_solution_test_message, tat=tat)
                            else:
                                solution_message = f'The ability on handling the kind of opportunity you provided is still under developing, please consult solution engineers on your opportunity. Thank you!'
                                pricing_gm_message = f'For GM-level price:\n'\
                                                    f'No price since no solution'
                                pricing_reference_message = f'For price reference:\n'\
                                                            f'Please consult pricing managers on your opportunity. Thank you!'
                        except Exception as e:
                            # Print the traceback
                            traceback.print_exc()
                            solution_message = f'The ability on handling the kind of opportunity you provided is still under developing, please consult solution engineers on your opportunity. Thank you!'
                            pricing_gm_message = f'For GM-level price:\n'\
                                                 f'No price since no solution'
                            pricing_reference_message = f'For price reference:\n'\
                                                        f'Please consult pricing managers on your opportunity. Thank you!'
                        # send message to lark
                        if ('IEPL' in product) or ('IPLC' in product):
                            if isinstance(solution_message, pd.DataFrame):
                                file_name = date_time_string + "_" + project_name + "_Auto_Solution_and_GM_Price.xlsx"#"_Auto_Solution_Path_Arcana.xlsx"
                                file_key = upload(tat, save_path, file_name)
                                send_file_to_lark(tat, chat_id, file_key)
                                encoded_name= quote(date_time_string+"_"+project_name)
                                auto_solution_path_message = f'Please use the link below to visualize all possible solutions for {product}:\n'\
                                                             f'http://chinamobile.fortiddns.com:55555/IEPL_preview/nonEU/{encoded_name}'
                                send_lark_message(chat_id=chat_id, message=auto_solution_path_message, tat=tat)

                                # redefine solution_message for IEPL/IPLC:
                                df_selected_path = pd.read_excel(save_path + file_name)
                                df_selected_path['POP Code Path'] = df_selected_path['POP Code Path'].ffill()
                                df_selected_path['Segment'] = df_selected_path['Segment'].ffill()
                                #solution_message = df_for_AI.loc[:, ['POP Code Path', 'Segment', 'Cable Name', 'Bandwidth', 'MRC(USD)', 'NRC(USD)']]
                                df_for_AI = df_selected_path.loc[:, ['POP Code Path', 'Segment', 'Cable Name', 'Bandwidth', 'MRC(USD)', 'NRC(USD)', 'Protection Status', 'SLA', 'Tested Latency']].fillna('Unknown')
                                paths_split = df_for_AI['POP Code Path'].str.split('===')
                                most_direct_path_idx = paths_split.apply(len).idxmin()
                                most_direct_path = df_for_AI['POP Code Path'].iloc[most_direct_path_idx]
                                #most_direct_path = df_for_AI['POP Code Path'].iloc[0]
                                most_direct_seg_cable = df_for_AI.loc[df_for_AI['POP Code Path'] == most_direct_path, :]
                                
                                all_direct_segment_message = ''
                                for i, row in most_direct_seg_cable.iterrows():
                                    all_direct_segment_message += f"Option {i+1}:\n\n"\
                                                                  f"Segment:\n" \
                                                                  f"{row['Segment']}\n"\
                                                                  f"Cable: {row['Cable Name']}\n"\
                                                                  f"Protection Status: {row['Protection Status']}\n"\
                                                                  f"SLA: {row['SLA']}\n"\
                                                                  f"Tested Latency: {row['Tested Latency']}\n"\
                                                                  f"Bandwidth: {row['Bandwidth']}\n"\
                                                                  f"MRC(USD): {row['MRC(USD)']}\n"
                                
                                direct_path_message = f'Here is the automatic solution generated based on the information provided:\n' \
                                                      f'Most Direct Path:\n' \
                                                      f'{most_direct_path}\n' \
                                                      f'Segment and Cable Components:\n'\
                                                      f'{all_direct_segment_message}\n\n'
                                
                                if cable != ' ':
                                    # adding the solution message with users' chosen cable
                                    user_picked_path = df_for_AI['POP Code Path'].iloc[0]
                                    user_picked_seg_cable = df_for_AI.loc[df_for_AI['POP Code Path'] == user_picked_path, :]

                                    user_picked_segment_message = ''
                                    for i, row in user_picked_seg_cable.iterrows():
                                        user_picked_segment_message += f"Option {i+1}:\n\n"\
                                                                    f"Segment:\n" \
                                                                    f"{row['Segment']}\n"\
                                                                    f"Cable: {row['Cable Name']}\n"\
                                                                    f"Protection Status: {row['Protection Status']}\n"\
                                                                    f"SLA: {row['SLA']}\n"\
                                                                    f"Tested Latency: {row['Tested Latency']}\n"\
                                                                    f"Bandwidth: {row['Bandwidth']}\n"\
                                                                    f"MRC(USD): {row['MRC(USD)']}\n"

                                    user_picked_path_message = f'Path of Your Choice (with Cable {cable}):\n' \
                                                            f'{user_picked_path}\n' \
                                                            f'Segment and Cable Components:\n'\
                                                            f'{user_picked_segment_message}\n\n'
                                else:
                                    user_picked_path_message = ''

                                # Group by 'path' and 'segment' and find the minimum 'MRC(USD)' for each group
                                # Add an index column to preserve the original order
                                df_for_AI['original_index'] = range(len(df_for_AI))
                                def convert_to_numeric(value):
                                    if value == 'No GM Price':
                                        value = 1000000000
                                    else:
                                        value = float(value)
                                    return value
                                # Apply the conversion function to 'MRC(USD)' column
                                df_for_AI['MRC(USD)'] = df_for_AI['MRC(USD)'].apply(convert_to_numeric)
                                min_mrc = df_for_AI.groupby(['POP Code Path', 'Segment'], as_index=False).agg(min_mrc=('MRC(USD)', 'min'))
                                paths_to_remove = min_mrc.loc[min_mrc['min_mrc'] == 1000000000, 'POP Code Path'].unique()
                                min_mrc_df = min_mrc[~min_mrc['POP Code Path'].isin(paths_to_remove)].reset_index(drop=True)
                                sum_mrc = min_mrc_df.groupby(['POP Code Path', 'Segment'], as_index=False).agg(sum_mrc=('min_mrc', 'sum'))
                                # Sum the minimum 'MRC(USD)' for each group
                                total_mrc = sum_mrc.groupby('POP Code Path', as_index=False).agg(total_mrc=('sum_mrc', 'sum'))
                                # To restore the original order, you can merge the result back to the original DataFrame
                                result = df_for_AI.merge(total_mrc, on='POP Code Path', how='inner')
                                # Sort the result by the original index to restore the initial order and remove No GM Price row from the original df
                                result = result.sort_values('original_index').loc[result['MRC(USD)']!=1000000000]
                                min_group = result[result['total_mrc'] == result['total_mrc'].min()].reset_index(drop = True)

                                if not min_group.empty:
                                    best_value_message = ''
                                    all_best_segment_message = ''
                                    min_total_mrc = min_group['total_mrc'][0]
                                    best_value_path = min_group['POP Code Path'].unique()[0]
                                    best_value_seg_cable = min_group.loc[min_group['POP Code Path'] == best_value_path, :].reset_index(drop = True)
                                    for j, seg in best_value_seg_cable.iterrows():
                                        all_best_segment_message += f"Segment {j+1}:\n" \
                                                                    f"{seg['Segment']}\n"\
                                                                    f"Cable: {seg['Cable Name']}\n"\
                                                                    f"Protection Status: {seg['Protection Status']}\n"\
                                                                    f"SLA: {seg['SLA']}\n"\
                                                                    f"Tested Latency: {seg['Tested Latency']}\n"\
                                                                    f"Bandwidth: {seg['Bandwidth']}\n"\
                                                                    f"MRC(USD): {seg['MRC(USD)']}\n"
                                    best_value_message += f"Best Value Path:\n" \
                                                        f"{best_value_path}\n"\
                                                        f"Segment and Cable Components:\n"\
                                                        f"{all_best_segment_message}\n"\
                                                        f"Total MRC(USD): {min_total_mrc}\n"
                                else:
                                    best_value_message = f"Cannot recommend the best value path since some segments don't have GM-level price.\n" \
                                                        f"Please quote your opportunity based on solution provided."
                                solution_message = direct_path_message + user_picked_path_message + best_value_message
                            
                            elif solution_message.__contains__('Private Circuit within China (CNLL)'):
                                CNLL_gm_df = pricing_gm_message.copy()
                                CNLL_gm_message = ""
                                for i, row in CNLL_gm_df.iterrows():
                                    CNLL_gm_message += f"{CNLL_gm_message} \n"\
                                                       f"A-end Location:{row['A-end Location']}\n" \
                                                       f"B-end Location:{row['B-end Location']}\n" \
                                                       f"Cable: {row['Cable']}\n" \
                                                       f"Bandwidth: {row['Bandwidth']}\n" \
                                                       f"MRC(USD): {row['MRC (USD)']}\n"\
                                                       f"NRC(USD): {row['NRC (USD)']}\n"\
                                                       f"Term(Months): {row['Term (Months)']}\n" \
                                                       f"TCV(USD): {row['TCV (USD)']}\n"\
                                                       f"Remark: {row['Remark']}\n"
                                pricing_gm_message = CNLL_gm_message                      
                        if isinstance(pricing_gm_message, pd.DataFrame):
                            if ('IEPL' not in product) and ('IPLC' not in product):
                                if len(pricing_gm_message) > 1:
                                    file_name = date_time_string + "_" + project_name + "_GM_Level_Price.xlsx"
                                    file_key = upload(tat, save_path, file_name)
                                    send_file_to_lark(tat, chat_id, file_key)

                        if isinstance(pricing_reference_message, pd.DataFrame):
                            if len(pricing_reference_message) > 1:
                                file_name = date_time_string + "_" + project_name + "_Historical_Won_Order_Price_Reference.xlsx"
                                file_key = upload(tat, save_path, file_name)
                                if role == 'support':
                                    send_file_to_lark(tat, chat_id, file_key)

                        # Send question to SPR AI:
                        if question_to_spr.upper() != 'NONE':
                            send_lark_message(chat_id=chat_id, message='Solution and GM Price completed, now fetching SPR...', tat=tat)
                            latest_message_spr = thread_code_interpreter(content=question_to_spr, thread_file_path=thread_file_path, chat_id=chat_id, assistant_id=assistant_id_spr, ai_name='spr')
                            print(f'SPR AI replied: {latest_message_spr}')

                            # Generate SPR file:
                            try:
                                spr_list = extract_spr_numbers(latest_message_spr)
                            except:
                                spr_list = ' '
                            print(f'SPR Number Extracted: {spr_list}')

                            # Load the Excel file
                            spr_data = 'spr_data_filtered.xlsx'  # Update this path accordingly

                            df = pd.read_excel(os.path.join(file_path_spr,spr_data))

                            # Filter the DataFrame based on the SPR numbers
                            filtered_df_spr = df[df['SPR单号'].isin(spr_list)]

                            filtered_df_spr = filtered_df_spr.rename(columns = {'SPR单号' : 'SPR No.',
                                                                                '审批结果': 'SPR Approval Result',
                                                                                '成单结果': 'Order Won/Lost', 
                                                                                'order price level': 'SPR Price Level',
                                                                                '是否纯转售': 'Total Resell',
                                                                                '海缆': 'Cable', 
                                                                                'Product Name': 'Product', 
                                                                                'Contract Term': 'Term', 
                                                                                'SPR create time': 'Create Date',
                                                                                'a_end_address' : 'A End Address',
                                                                                'b_end_address': 'B End Address',
                                                                                'pop_secton_start': 'POP Section Start',
                                                                                'pop_section_end': 'POP Section End',
                                                                                'pop_price_mrc': 'POP MRC Price',
                                                                                'pop_price_nrc': 'POP NRC Price',
                                                                                'pop mrc cost': 'POP MRC Cost',
                                                                                'pop nrc cost': 'POP NRC Cost'})
                            filtered_df_spr = filtered_df_spr.loc[:, ['Create Date', 'SPR No.', 'Product', 'Bandwidth', 
                                                                      'POP MRC Price', 'POP NRC Price', 'Term', 'TCV_USD', 
                                                                      'A End Address', 'B End Address', 'Cable', 
                                                                      'POP Section Start', 'POP Section End', 
                                                                      'POP MRC Cost', 'POP NRC Cost', 'SPR Price Level',
                                                                      'Total Resell', 'SPR Approval Result', 'Order Won/Lost']]
                            filtered_df_spr['SPR Approval Result'] = filtered_df_spr['SPR Approval Result'].str.replace('不通过', 'Rejected').str.replace('通过', 'Approved').str.replace('处理中', 'Still Under Review')
                            filtered_df_spr['Order Won/Lost'] = filtered_df_spr['Order Won/Lost'].str.replace('未成单', 'Lost').str.replace('成单', 'Won')
                            filtered_df_spr['Total Resell'] = filtered_df_spr['Total Resell'].str.replace('是', 'Yes').str.replace('否', 'No')
                            filtered_df_spr['Total Resell']
                            # Save the filtered DataFrame to a new Excel file
                            spr_output_file_name = date_time_string + "_Historical_SPR.xlsx"
                            #print(save_path)
                            #print(spr_output_file_name)
                            #filtered_df_spr.to_excel(save_path+spr_output_file_name, index=False)
                            with pd.ExcelWriter(save_path+spr_output_file_name, engine='openpyxl') as writer:
                                filtered_df_spr.to_excel(writer, sheet_name='Historical_SPR', index=False)
                                adjust_column_width(writer, 'Historical_SPR')

                            print("Filtered data has been saved to:", spr_output_file_name)

                            if role == 'support':
                                if len(filtered_df_spr) > 1:
                                    file_key = upload(tat, save_path, spr_output_file_name)
                                    send_file_to_lark(tat, chat_id, file_key)
                            send_lark_message(chat_id=chat_id, message='SPR fetching completed, now fetching ZFHS...', tat=tat)
                        else:
                            latest_message_spr = 'SPR Information not applicable.'
                            filtered_df_spr = 'Not applicable'
                            send_lark_message(chat_id=chat_id, message=f'SPR {latest_message_spr}, now fetching ZFHS...', tat=tat)
                            print('No need to ask SPR assistant AI since the main AI think the product does not related to SPR')

                        # Send question to ZFHS AI:
                        if question_to_zfhs.upper() != 'NONE':
                            latest_message_zfhs = thread_code_interpreter(content=question_to_zfhs,
                                                                                     thread_file_path=thread_file_path, chat_id=chat_id,
                                                                                     assistant_id=assistant_id_zfhs, ai_name='zfhs')
                            print(f'ZFHS AI replied: {latest_message_zfhs}')
                            try:
                                zfhs_no = extract_ZFHS_order_number(text = latest_message_zfhs)
                            except:
                                zfhs_no = ' '
                            print(f'ZFHS Order Number Extracted: {zfhs_no}')

                            # Load the Excel file
                            zfhs_data = 'zfhs_data_filtered.xlsx'  # Update this path accordingly

                            zfhs_df = pd.read_excel(os.path.join(file_path_zfhs,zfhs_data))

                            # Filter the DataFrame based on the SPR numbers
                            filtered_df_zfhs = zfhs_df[zfhs_df['Order Number'].isin(zfhs_no)]

                            filtered_df_zfhs = filtered_df_zfhs.rename(columns = {'业务类型' : 'Product Name',
                                            '带宽': 'Bandwidth',
                                            '地址A': 'Location A', 
                                            '地址Z': 'Location Z',
                                            'MRC 目标价': 'MRC Customer Target Price',
                                            'NRC 目标价': 'NRC Customer Target Price',
                                            '需求可用IP数': 'Number of Available IPs Required'})

                            # Save the filtered DataFrame to a new Excel file
                            zfhs_output_file_name = date_time_string + "_Historical_ZFHS.xlsx"
                            #print(save_path)
                            #print(zfhs_output_file_name)
                            #filtered_df_zfhs.to_excel(save_path+zfhs_output_file_name, index=False)
                            with pd.ExcelWriter(save_path+zfhs_output_file_name, engine='openpyxl') as writer:
                                filtered_df_zfhs.to_excel(writer, sheet_name='Historical_ZFHS', index=False)
                                adjust_column_width(writer, 'Historical_ZFHS')

                            print("Filtered ZFHS data has been saved to:", zfhs_output_file_name)

                            if role == 'support':
                                if len(filtered_df_zfhs) > 1:
                                    file_key = upload(tat, save_path, zfhs_output_file_name)
                                    send_file_to_lark(tat, chat_id, file_key)
                            send_lark_message(chat_id=chat_id, message='ZFHS fetching completed, now wrapping up...', tat=tat)
                        else:
                            latest_message_zfhs = 'ZFHS (资费会商) Information not applicable.'
                            filtered_df_zfhs = 'Not applicable'
                            send_lark_message(chat_id=chat_id, message=f'{latest_message_zfhs}, now wrapping up...', tat=tat)
                            print('No need to ask ZFHS assistant AI since the main AI think the product is unrelated to ZFHS')

                        # Summarize the messages from different AI with Arcana_wrap
                        all_messages = f'The price is based on below solution:\n' \
                                        f'{solution_message}\n\n\n' \
                                        f'GM-Level Price Information:\n' \
                                        f'{pricing_gm_message}\n' \
                                        f'Historical Won Case Reference:\n\n' \
                                        f'{pricing_reference_message}\n\n' \
                                       f'Historical SPR Reference:\n' \
                                       f'{filtered_df_spr}\n\n' \
                                       f'Historical ZFHS (资费会商) Reference:\n' \
                                       f'{filtered_df_zfhs}\n\n' \
                                       f'Please use: {language} to reply'
                        print(f'All Messages: \n'
                              f'{all_messages}')
                        # Send information to different Wrap AI according to Product Type:
                        if 'IPT' in product:
                            latest_message_wrap = thread_common(content=all_messages,
                                                                thread_file_path=thread_file_path, chat_id=chat_id,
                                                                assistant_id=assistant_id_wrap_IPT, ai_name='wrap_ipt')
                        elif 'CMIN2' in product:
                            latest_message_wrap = thread_common(content=all_messages,
                                                                thread_file_path=thread_file_path, chat_id=chat_id,
                                                                assistant_id=assistant_id_wrap_N2, ai_name='wrap_n2')
                        elif 'DIA' in product:
                            latest_message_wrap = thread_common(content=all_messages,
                                                                thread_file_path=thread_file_path, chat_id=chat_id,
                                                                assistant_id=assistant_id_wrap_DIA, ai_name='wrap_dia')
                        elif 'EVPN' in product:
                            latest_message_wrap = thread_common(content=all_messages,
                                                                thread_file_path=thread_file_path, chat_id=chat_id,
                                                                assistant_id=assistant_id_wrap_EVPN, ai_name='wrap_evpn')
                        elif 'MPLS' in product:
                            latest_message_wrap = thread_common(content=all_messages,
                                                                thread_file_path=thread_file_path, chat_id=chat_id,
                                                                assistant_id=assistant_id_wrap_MPLS, ai_name='wrap_mpls')
                        elif ('IEPL' in product) or ('IPLC' in product):
                            latest_message_wrap = thread_common(content=all_messages,
                                                                thread_file_path=thread_file_path, chat_id=chat_id,
                                                                assistant_id=assistant_id_wrap_IEPL, ai_name='wrap_iepl')
                        elif 'SDWAN' in product:
                            latest_message_wrap = thread_common(content=all_messages,
                                                                thread_file_path=thread_file_path, chat_id=chat_id,
                                                                assistant_id=assistant_id_wrap_SDWAN, ai_name='wrap_sdwan')
                        else:
                            latest_message_wrap = f'The ability on handling your opportunity: {product} by AI is still under developing, please consult solution engineers on this one. Thank you!'
                        print(f'Wrap Reply: \n'
                              f'{latest_message_wrap}')
                        send_lark_message(chat_id=chat_id,message=latest_message_wrap,tat=tat)

                        # write the above info to Auto_Workflow_Test bitable/ Project Arcane 
                        app_token = 'bascnCAnYZnRGt5PePVRliHoK5e'
                        tbl_id = 'tblYgDZ2cVHeEJZ0'

                        write_to_bitable(app_token = app_token, tbl_id = tbl_id, date = date_time_string, project_name = project_name,
                                        product = product, bandwidth = bandwidth, term = term, location_a = location_a, 
                                        location_b = location_b, on_off_net = on_off_net, open_id = data_sender_id, arcana_reply=latest_message_wrap,
                                        cable = cable, wan_subnet = wan_subnet, lan_subnet = lan_subnet, burst_bw = burst_bandwidth)

                    elif flag_value.lower() == 'incomplete':
                        print("Has not collected all information, status remains as Incomplete")
                
                else:
                    print('bot is not mentioned, therefore will not respond to the message.')
                return jsonify({'status': 'Message processed'})
        else:
            print("No event_id found in the request.")
            return jsonify({'error': 'No event_id provided'}), 400

My Lark BOT is connected to Flask via this back end code. I need to have a separate webpage route (demo.html) that has a chat bot like interface, I need a new app.route for demo. Do not modify webhookarcana app route. Port everything from webhookarcana app route. Be reminded that chat_id is not available on my demo.html. I don't need TOKEN verification or schema 2.0 check since they are for Lark only.