import json
import os
import re

# from fuzzywuzzy import fuzz
from fuzzy_match_service import fuzzy_name_match

from db import direct_query
from db.query import QueryExecution
import jwt
from logger_module.package_logger import print_log
import requests
import base64
import io
import boto3
from integrations.karza import KarzaIntegration
import img2pdf
from hashids import Hashids
from core_utilities import send_mail

class StaticFunctions:

    @classmethod
    def get_loan_application_number(cls, application_id, merchant_id):
        buffer_chars = ""
        if len(str(application_id)) < 7:
            diff = 7 - len(str(application_id))
            i = 0
            while i < diff:
                buffer_chars = buffer_chars + "0"
                i = i + 1
                
        from helper_functions import get_data
        vendor_details = get_data('vendor_details')
        if vendor_details and vendor_details.get('type') == 'partner':
            merchant_details = get_data('merchant_details')
            slug = merchant_details.get('merchant_name').replace(" ", "").lower() if merchant_details.get('merchant_name') else ""
        else:
            status, result = QueryExecution.fetch_result('select slug from merchant where id = %s', (merchant_id,))
            slug = result.get('slug') if result else ""
        return str(slug)+str(buffer_chars)+str(application_id)

    @classmethod
    def get_state(cls, state_code):
        status, result = QueryExecution.fetch_result('SELECT name FROM state where code = %s', (state_code,))
        return result.get('name')

    @classmethod
    def get_company_id(cls, company_name):
        try:
            status, result = QueryExecution.fetch_result('SELECT id FROM company where company = %s', (company_name,))
            if not result:
                result_data = QueryExecution.fetch_result('INSERT INTO company(category, company) VALUES (%s, %s)',
                                                      ('CAT D', company_name))
                auto_id = result_data[-1]
            else:
                if len(result) > 1:
                    auto_id = result[0].get('id')
                else:
                    auto_id = result.get('id')
            return auto_id
        except Exception as ex:
            print_log(ex)
            return ''

    @classmethod
    def generate_token(cls, payload, algorithm, signing_key):
        token = jwt.encode(payload, signing_key, algorithm=algorithm)
        return token

    @classmethod
    def format_offer(cls, offers):
        merchant_offers = []
        frequency = {1: "Monthly", 2: "Bi-Weekly (5th and 18th)", 3: "Weekly - every wednesday", 4: "Daily"}

        for offer in offers:
            tenure = offer.get('total_emi') if offer.get('total_emi') else offer.get('tenure')
            payable = offer.get('payable') if offer.get('payable') else 0
            emi_amount = offer.get('emi') if offer.get('emi') else 200
            interest_rate = offer.get('flat_annual_interest') if offer.get('flat_annual_interest') else offer.get('interest_rate')
            merchant_offers.append({
                'id': offer['id'],
                'tenure': tenure,
                'interest_rate': interest_rate,
                'payment_frequency': frequency.get(offer['payment_frequency']),
                'fee': {'type': offer['processing_fee_type'], 'value': offer['processing_fee']},
                'advance': {'type': offer['advance_type'], 'value': offer['advance_emi']},
                'total_payable': payable,
                'emi_amount': emi_amount,
            })
        return merchant_offers

    @classmethod
    def json_dumps(cls, body):
        if body:
            dict_obj = body if isinstance(body, dict) else eval(body.replace("'", '"'))
            json_str = json.dumps(dict_obj)
            return json_str
        return None

    @classmethod
    def format_application_data(cls, request_body):
        from helper_functions import get_data
        current_addr = get_data('current_address')
        permanent_addr = get_data('permanent_address')
        current_addr_state = cls.get_state(current_addr.get('state')) if current_addr else None
        permanent_addr_state = cls.get_state(permanent_addr.get('state')) if permanent_addr else None
        co_app_name = get_data('co_app_name')
        if request_body.get('name'):
            return_dict = {
            "name": {"old": "", "new": request_body.get('name')},
            "lender_id": {"old": "", "new": 20},
            "monthly_income": {"old": "", "new": request_body.get('profession').get('professionDetails').get('monthlyIncome')},
            "dob": {"old": "", "new": request_body.get('dateofBirth')},
            "pan_card": {"old": "", "new": request_body.get('panNumber')},
            "current_address_line1": {"old": "", "new": current_addr.get('addrLine1') + current_addr.get('addrLine2')},
            "current_address_pincode": {"old": "", "new": current_addr.get('pincode')},
            "current_address_city": {"old": "", "new": current_addr.get('city')},
            "current_address_state": {"old": "", "new": current_addr_state},
            "current_address_landmark": {"old": "", "new": current_addr.get('landmark')},
            "permanent_address_line1": {"old": "", "new": permanent_addr.get('addrLine1') + permanent_addr.get('addrLine2')},
            "permanent_address_pincode": {"old": "", "new": permanent_addr.get('pincode')},
            "permanent_address_city": {"old": "", "new": permanent_addr.get('city')},
            "permanent_address_state": {"old": "", "new": permanent_addr_state},
            "permanent_address_landmark": {"old": "", "new": permanent_addr.get('landmark')},
            "email": {"old": "", "new": request_body.get('email')},
            "mobile_number": {"old": "", "new": request_body.get('phone')},
            "employee_status": {"old": "", "new": get_data('profession_type')},
            "merchant_id": {"old": "", "new": get_data('merchant_id')},
            "power_savings": {"old": "", "new": get_data('inverter_power_savings')},
            "co_applicant_name": {"old": "", "new": co_app_name},
            "co_applicant_email": {"old": "", "new": get_data('co_app_email')},
            "co_applicant_mobile_number": {"old": "", "new": get_data('co_app_phone')},
            "co_applicant_pan_card": {"old": "", "new": get_data('co_app_pan_card')},
            "is_co_applicant": {"old": "", "new": 1 if co_app_name else 0}
        }
        elif request_body.get('name') and request_body.get('offer_id'):
            application_id = get_data('consumer_application_id')
            result, application_data = QueryExecution.fetch_result(direct_query.fetch_application_data,
                                                                   (application_id,))
            old_offer = application_data.get('offer_id') if application_data.get('offer_id') else ""
            old_loan_amount = application_data.get('loan_amount') if application_data.get('loan_amount') else ""
            return_dict = {
                "name": {"old": "", "new": request_body.get('name')},
                "lender_id": {"old": "", "new": application_data.get('lender_id') if application_data.get('lender_id') else 20},
                "monthly_income": {"old": "",
                                   "new": request_body.get('profession').get('professionDetails').get('monthlyIncome')},
                "dob": {"old": "", "new": request_body.get('dateofBirth')},
                "pan_card": {"old": "", "new": request_body.get('panNumber')},
                "current_address_line1": {"old": "",
                                          "new": current_addr.get('addrLine1') + current_addr.get('addrLine2')},
                "current_address_pincode": {"old": "", "new": current_addr.get('pincode')},
                "current_address_city": {"old": "", "new": current_addr.get('city')},
                "current_address_state": {"old": "", "new": current_addr_state},
                "current_address_landmark": {"old": "", "new": current_addr.get('landmark')},
                "permanent_address_line1": {"old": "",
                                            "new": permanent_addr.get('addrLine1') + permanent_addr.get('addrLine2')},
                "permanent_address_pincode": {"old": "", "new": permanent_addr.get('pincode')},
                "permanent_address_city": {"old": "", "new": permanent_addr.get('city')},
                "permanent_address_state": {"old": "", "new": permanent_addr_state},
                "permanent_address_landmark": {"old": "", "new": permanent_addr.get('landmark')},
                "email": {"old": "", "new": request_body.get('email')},
                "mobile_number": {"old": "", "new": request_body.get('phone')},
                "employee_status": {"old": "", "new": get_data('profession_type')},
                "merchant_id": {"old": "", "new": get_data('merchant_id')},
                "power_savings": {"old": "", "new": get_data('inverter_power_savings')},
                "co_applicant_name": {"old": "", "new": co_app_name},
                "co_applicant_email": {"old": "", "new": get_data('co_app_email')},
                "co_applicant_mobile_number": {"old": "", "new": get_data('co_app_phone')},
                "co_applicant_pan_card": {"old": "", "new": get_data('co_app_pan_card')},
                "is_co_applicant": {"old": "", "new": 1 if co_app_name else 0},
                "offer_id": {"old": str(old_offer), "new": request_body.get('offerId')},
                "loan_amount": {"old": str(old_loan_amount), "new": request_body.get('loanAmount')}
            }
        else:
            return_dict = {
                "loan_amount": {"old": "", "new": request_body.get('loanAmount')},
                "offer_id": {"old": "", "new": request_body.get('offerId')}
            }
        return json.dumps(return_dict)

    @classmethod
    def fuzzy_logic(cls, name1, application_id):
        # Fetch the most recent utility validation data
        query = '''
            SELECT data 
            FROM utility_validation 
            WHERE consumer_application_id = %s AND deleted_at IS NULL 
            ORDER BY created_at DESC LIMIT 1
        '''
        status, result = QueryExecution.fetch_result(query, (application_id,))

        # Return empty dictionary if no result is found
        if not result:
            return {'name_match': False, 'percentage': 0}

        # Extract the consumer name
        consumer_data = json.loads(result.get('data'))
        # consumer_data = {"result":{"bill_no":"000002743983962","bill_due_date":"15-02-2025","consumer_number":"020013159263","bill_amount":"1,050.00","bill_issue_date":"","mobile_number":"","amount_payable":"","total_amount":"","address":"ARJUN TOWER, BLOCK NO. 403, AYRE ROAD, TUKARAM NAGAR 421201","consumer_name":"Sanjay Mahadeo Kudtarkar.","email_address":"","bill_date":"26-01-2025"},"request_id":"44b9855b-6994-418a-9a33-b7c9cdcee53d","status-code":"101"}
        name2 = consumer_data.get('result').get('consumer_name')
        if not name2:
            name2 = ""

        # Compare name sets for exact matching
        name1_set, name2_set = map(lambda x: set(x.lower().split()), [name1, name2])
        if name1_set.issubset(name2_set):
            return {"name_match": True, "percentage": 100}

        # Fuzzy match for non-exact cases
        #percentage_match = fuzz.ratio(name1.lower(), name2.lower())
        percentage_match = fuzzy_name_match(name1.lower(), name2.lower())
        return {"name_match": False, "percentage": percentage_match}

    @classmethod
    def fuzzy_set(cls, fuzzy_result,application_id):
        # Fetch the most recent utility validation data
        query = '''
                SELECT  ebill_name_verify
                FROM consumer_application_files
                WHERE consumer_application_id = %s AND deleted_at IS NULL AND file_type = 'electricity_bill_validation_report'
                ORDER BY created_at DESC LIMIT 1
            '''
        status, result = QueryExecution.fetch_result(query, (application_id,))

        # Return empty dictionary if no result is found
        if not result:
            return {}

        result_data = json.loads(result.get("ebill_name_verify"))
        result_data.update({"co_applicant_match":fuzzy_result.get("percentage")})

        return result_data

    @classmethod
    def format_vendor_list(cls, vendors):
        vendor_list = []
        for items in vendors:
            vendor_dict = {'vendor_id': items.get('id'), 'vendor_name': items.get('name')}
            vendor_list.append(vendor_dict)
        return vendor_list

    @classmethod
    def set_address_value(cls, address_block, type):
        for addr in address_block:
            if addr.get("addrType") == type or addr.get("addressType") == type:
                return addr

    @classmethod
    def disbursement_checklist_check(cls, application_id):
        _, application_details = QueryExecution.fetch_result(direct_query.fetch_application_data, (application_id,))
        if not application_details:
            return False
        return bool(StaticFunctions().check_agreement_signed(application_id, application_details))

    @classmethod
    def check_post_approval_journey(cls, application_id, lender_id):
        try:
            # KYC block
            query_status, check_co_app = QueryExecution.fetch_result(direct_query.check_co_app, (application_id,))
            digio_status, digio_vkyc_details = QueryExecution.fetch_result(direct_query.digio_vkyc, (application_id,))
            karza_status, karza_vkyc = QueryExecution.fetch_result(direct_query.karza_vkyc, (application_id,))
            if (bool(check_co_app.get("co_applicant_name")) and (len(digio_vkyc_details) != 2 or len(karza_vkyc) != 2)) and (not check_co_app.get("co_applicant_name") and (len(digio_vkyc_details) != 1 or len(karza_vkyc) != 0)):
                return False
            # Mandate block
            if lender_id in [2, 20]:  # KM Lender and BlackSoil Lender
                mandate_status, mandate_details = QueryExecution.fetch_result(direct_query.mandate, (application_id,))
                if not mandate_details:
                    return False
            else:
                manual_loan_agreement, status = QueryExecution.fetch_result(direct_query.fetch_files, ('agreement', application_id))
                if not manual_loan_agreement:
                    return False
            # payment block
            payment_status, check_payment_link = QueryExecution.fetch_result(direct_query.online_payments, (application_id,))
            if check_payment_link:
                advance_amount, pf_amount, insurance_amount, legal_charges, legal = 0, 0, 0, 0, 0
                status, emi_details = QueryExecution.fetch_result(direct_query.emi_details, (application_id,))
                status, insurance_details = QueryExecution.fetch_result(direct_query.pro_forma,(application_id,))
                status, merchant_details = QueryExecution.fetch_result(direct_query.merchant_details, (check_co_app.get("merchant_id"),))
                if insurance_details:
                    if insurance_details.get("merchant_collected_advance_amount"):
                        advance_amount = emi_details.get("upfront_from_customer", 0)
                    if insurance_details.get("processing_fee_by_merchant"):
                        if insurance_details.get("processing_fee_by_merchant") == 0:
                            legal = 1
                            pf_amount = emi_details.get("processing_fee", 0) if lender_id in [2, 20] else 0
                    else:
                        if merchant_details.get("is_collected") != 1 and merchant_details.get("is_processing_fees") != 1:
                            legal = 1
                            pf_amount = emi_details.get("processing_fee", 0) if lender_id in [2, 20] else 0
                if merchant_details.get("insurance_premium") == 1:
                    if insurance_details.get("insurance_premium_applicable") == 1 and insurance_details.get("insurance_premium_by_merchant") == 0:
                        insurance_amount = insurance_details.get("insurance_premium_amount", 0)
                if check_co_app.get("merchant_id") != 6601:
                    legal_charges = emi_details.get("legal_charges", 0) if legal else 0
                total_charges = int(advance_amount) + int(pf_amount) + int(insurance_amount) + int(legal_charges)
                status, total_paid_charges = QueryExecution.fetch_result(direct_query.total_paid_charges, (application_id,))
                if total_charges > total_paid_charges.get('amount', 0):
                    return False
            return True
        except AttributeError as ex:
            print_log(f"Error in check_post_approval_journey: {str(ex)}")
            return False

    @classmethod
    def get_comment_tag(cls,):
        try:
            users = os.environ.get('SSE_COMMENT_TAG_USER_ID').split(',')
            tag = ''
            placeholders = f"{', '.join(users)}"
            status, query_result = QueryExecution.fetch_result(direct_query.users, (placeholders,))
            if not isinstance(query_result, list):
                query_result = [query_result]
            for items in query_result:
                tag += f"@{items.get('id')}-{items.get('name')} "
            return tag
        except AttributeError as ex:
            print_log(f"Error in get_comment_tag: {str(ex)}")
            return False

    @classmethod
    def check_agreement_signed(cls, id, data):
        loan_agreement_status, loan_agreement = QueryExecution.fetch_result("select lhm.applicant_signing, lhm.co_applicant_signing  from loan_hypothecation_model lhm where lhm.application_id = %s",
            (id,))
        if loan_agreement and data.get("lender_id") in [2, 20] and loan_agreement.get("applicant_signing") and (
                not data.get("co_applicant_name") or (
                data.get("co_applicant_name") and loan_agreement.get("co_applicant_signing"))):
            return True
        if loan_agreement and data.get("lender_id") == 10:
            homeville_loan_agreement_status, homeville_loan_agreement = QueryExecution.fetch_result("SELECT application_id from homeville_document_info where application_id = %s and doc_type in ('signed_document')", (id,))
            if homeville_loan_agreement_status and homeville_loan_agreement.get("application_id"):
                return True
        return False

    @classmethod
    def format_address(cls, address_block):
        cleaned = re.sub(r'[^a-zA-Z0-9 ,]', '', address_block)
        return cleaned
    
    @classmethod
    def uploadPirimidDocument(cls, application_id, applicantType, docContent, docFormat,custDocPassword):
        pirimid_api = os.getenv("Pirimid_Api")
        api_key = os.getenv("PIRIMID_API_KEY")
        
        if docFormat != "pdf":
            raise ValueError("Password handling is supported only for PDF files")

        try:
            file_bytes = base64.b64decode(docContent)
            mime_type = "application/pdf"
            url = pirimid_api
            files = {
                "file": (
                    f"document_{application_id}.pdf",
                    io.BytesIO(file_bytes),
                    mime_type
                )
            }   
            payload = {
                "id": str(application_id),
                "applicant_type": str(applicantType)
            }
            headers = {
                "Accept": "*/*",
                "API_KEY": api_key
            }
            
            if custDocPassword:
                payload["pdfPasswords"] = custDocPassword
                
            response = requests.post(
                url,
                files=files,
                data=payload,
                headers=headers,
                timeout=100
            )
           
            response.raise_for_status()
            response_json = response.json()
            tracking_id = response_json.get("trackingId")
            user_id =  107

            request_payload = {
                "file_name": f"document_{application_id}.pdf",
                "mime_type": mime_type,
                "applicant_type": applicantType
            }

            insert_query = """
                INSERT INTO pirimid_bank_analysis
                (tracking_id, application_id, request, response, user_id, applicant_type, created_at, updated_at)
                VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW())
            """ 

            QueryExecution.fetch_result(
                insert_query,
                (
                    tracking_id,
                    application_id,
                    json.dumps(request_payload),  
                    json.dumps(response_json),
                    user_id,
                    applicantType
                )
            )
            
            return response_json
        
        except Exception as ex:
            print_log(f"Error in Pirimid upload failed: {str(ex)}")
            return {
                "error": "Pirimid upload failed",
                "message": str(ex)
            }
        
    @classmethod
    def MaskAadhar(cls, application_id, applicantType, docContent, doc_side, docFormat):
        karza_api_url = os.getenv("KARZA_URL") + "/v3/ocr/kyc"
        karza_api_key = os.getenv("karza_api_key")
        bucket_name = os.getenv("AWS_BUCKET")

        try:
            s3 = boto3.client("s3", region_name=os.getenv("AWS_REGION_NAME"))
            payload = {"fileB64": docContent, "maskAadhaar": True}
            headers = {"x-karza-key": karza_api_key}
            response = requests.post(karza_api_url, json=payload, headers=headers, timeout=100)
            response.raise_for_status()
            results = response.json().get("result", [])
                
            if not results:
                raise Exception("No results returned from Karza")
            matched_result = None
            
            if doc_side in ["aadhar_card", "co_applicant_aadhar_card"]:
                # For front, prefer "Aadhaar Front", then "Aadhaar Front Bottom", then "Aadhaar Front Top"
                priorities = ["Aadhaar Front", "Aadhaar Front Bottom", "Aadhaar Front Top", "Aadhaar"]
            elif doc_side in ["aadhar_card_back", "co_applicant_aadhar_card_back"]:
                # For back, prefer "Aadhaar Back", "Aadhaar Back Bottom", then "Aadhaar Front Top"
                priorities = ["Aadhaar Back", "Aadhaar Back Bottom", "Aadhaar Front Top", "Aadhaar"]
            else:
                priorities = ["Aadhaar Front", "Aadhaar Front Bottom", "Aadhaar Front Top"]

            for pref in priorities:
                for item in results:
                    if item.get("type", "").lower() == pref.lower():
                        matched_result = item
                        break
                if matched_result:
                    break

            if not matched_result:
                target_prefix = "Aadhaar Back" if "back" in doc_side else "Aadhaar Front"
                for item in results:
                    if item.get("type", "").lower().startswith(target_prefix.lower()):
                        matched_result = item
                        break
            
            if not matched_result:
                matched_result = results[0]

            extracted_url = matched_result.get("details", {}).get("imageUrl", {}).get("value")
            if not extracted_url:
                raise Exception(f"ImageUrl missing in Karza response for {doc_side}")
            
            print_log(f"Masking Completed - Application ID: {application_id} | Side: {doc_side} | Type: {applicantType} | URL: {extracted_url}")

            image_resp = requests.get(extracted_url, timeout=60)
            image_resp.raise_for_status()
            masked_image_bytes = image_resp.content
            
            clean_ext = docFormat.replace('.', '').lower()
            if clean_ext == 'pdf':
                final_body = img2pdf.convert(masked_image_bytes)
                final_ext = "pdf"
                content_type = "application/pdf"
            else:
                final_body = masked_image_bytes
                final_ext = clean_ext if clean_ext in ['jpg', 'jpeg', 'png'] else 'jpg'
                content_type = f"image/{final_ext}"

            s3_key = f"{application_id}/masked_{doc_side}.{final_ext}"
            s3.put_object(
                Bucket=bucket_name,
                Key=s3_key,
                Body=final_body,
                ContentType=content_type
            )    
            return {"s3_key": s3_key, 'status': 'success'}
        except Exception as ex:
            print_log(json.dumps({"error": "Masking process failed", "message": str(ex), "app_id": application_id}))
            try:
                s3 = boto3.client("s3", region_name=os.getenv("AWS_REGION_NAME"))
                original_file_bytes = base64.b64decode(docContent)
                
                clean_ext = docFormat.replace('.', '').lower()
                final_ext = clean_ext if clean_ext in ['pdf', 'jpg', 'jpeg', 'png'] else 'jpg'
                content_type = "application/pdf" if final_ext == 'pdf' else f"image/{final_ext}"
                
                s3_key = f"{application_id}/unmasked_{doc_side}.{final_ext}"
                s3.put_object(
                    Bucket=bucket_name,
                    Key=s3_key,
                    Body=original_file_bytes,
                    ContentType=content_type
                )
                
                return {"s3_key": s3_key, 'status': 'failed'}

            except Exception as fallback_ex:
                print_log(json.dumps({"error": "Fallback upload failed", "message": str(fallback_ex), "app_id": application_id}))
                return {"error": "Masking process failed", "message": str(ex), "app_id": application_id}
    
    @classmethod
    def run_karza_validation(cls, application_id, consumer_id, service_provider, district, mobile_number, request_bill_amount):
        karza = KarzaIntegration()
        response = karza.electricity_bill_verification(consumer_id, service_provider, district, mobile_number)
        
        if not response.get("error"):
            data = {'id': application_id, 'monthly_bill': request_bill_amount, 'consumer_id': consumer_id, 'service_provider': service_provider}
            result, bill_amount = karza.save_utility_validation(data, response)
            if result:
                return {'status': 'success', 'message': 'Successfully validated utility bill', 'bill_amount': bill_amount}
        return {'status': 'error', 'message': 'Failed to validate utility bill', 'bill_amount': request_bill_amount}
        
    @classmethod
    def generate_url(cls, application_id, route):
        try:
            application_id = int(application_id)
            
            print("called this fucntion")
            hashids = Hashids(salt='Creditfair by CS', min_length=20)
            hashed_id = hashids.encode(application_id)
            domain = os.getenv("DOMAIN")
            return f"{domain}/{route}/id/{hashed_id}"
        except Exception as e:
            print_log(f"Error in generate_url: {e}")
            return ""
        
    @classmethod
    def change_request_comment(cls, details):
        try:
            final_comment = "Update Request"
            if details.get("applicantName"):
                final_comment += f"<br> applicant name - {details.get('applicantName')}"
            if details.get("coApplicantName"):
                final_comment += f"<br> co-applicant name - {details.get('coApplicantName')}"
            if details.get("projectSize"):
                final_comment += f"<br> project size - {details.get('projectSize')}"
            if details.get("projectCost"):
                final_comment += f"<br> project cost - {details.get('projectCost')}"
            if details.get("coApplicantMobileNumber"):
                final_comment += f"<br> co-applicant mobile number - {details.get('coApplicantMobileNumber')}"
            if details.get("applicantAddress"):
                final_comment += f"<br> applicant address - {details.get('applicantAddress')}"
            if details.get("applicantMobileNumber"):
                final_comment += f"<br> applicant mobile number - {details.get('applicantMobileNumber')}"
            if details.get("applicantEmail"):
                final_comment += f"<br> applicant email - {details.get('applicantEmail')}"
            if details.get("coApplicantEmail"):
                final_comment += f"<br> co-applicant email - {details.get('coApplicantEmail')}"
            if details.get("tenure"):
                final_comment += f"<br> tenure - {details.get('tenure')}"
            if details.get("interestRate"):
                final_comment += f"<br> interest rate - {details.get('interestRate')}"
            if details.get("loanAmount"):
                final_comment += f"<br> loan amount - {details.get('loanAmount')}"
            if details.get("typeOfCustomer"):
                final_comment += f"<br> type of customer - {details.get('typeOfCustomer')}"
            return final_comment
        except Exception as e:
            print_log(f"Error in change request comment: {e}")
            return ""

    @classmethod
    def trigger_user_emails(cls, user_ids, application_id, message):
        try:
            if not user_ids:
                return {'status': 'error', 'message': 'No user IDs provided'}

            subject = f"Tagged: Creditfair tagged you on {application_id}"
            placeholders = ', '.join(['%s'] * len(user_ids))
            _, users = QueryExecution.fetch_result(
                f"SELECT id, email FROM users WHERE id IN ({placeholders}) AND email IS NOT NULL",
                tuple(user_ids)
            )

            if not users:
                return {'status': 'error', 'message': 'No users found for the given IDs'}

            if not isinstance(users, list):
                users = [users]

            sent, failed = [], []
            for user in users:
                email = user.get('email')
                if send_mail([email], message, subject):
                    sent.append(user.get('id'))
                else:
                    failed.append(user.get('id'))

            return {'status': 'success', 'sent_user_ids': sent, 'failed_user_ids': failed}

        except Exception as ex:
            print_log(f"Error in trigger_user_emails: {str(ex)}")
            return {'status': 'error', 'message': str(ex)}

    @classmethod
    def _run_update(cls, table, values, where_column, where_value):
        # Drop columns whose resolved value is null so existing data is preserved.
        values = {col: val for col, val in values.items() if val is not None}
        if not values:
            return
        set_clause = ', '.join(f"{col} = %s" for col in values.keys())
        query = f"UPDATE {table} SET {set_clause} WHERE {where_column} = %s"
        params = tuple(values.values()) + (where_value,)
        QueryExecution.fetch_result(query, params)

    @classmethod
    def save_applicant(cls, request_details, application_id, profession_type_enum, discom_name_enum):
        try:
            applicant = request_details.get('applicant')
            if not applicant or not application_id:
                return True

            # consumer_application: resolve transform-backed values only when their source is present.
            permanent_state = applicant.get('permanent_address_state')
            current_state = applicant.get('current_address_state')
            company_name = applicant.get('company_name')
            profession_type = applicant.get('profession_type')

            consumer_application_values = {
                "name": applicant.get('name'),
                "pan_card": applicant.get('panNumber'),
                "permanent_address_line_1": applicant.get('permanent_address'),
                "permanent_address_city": applicant.get('permanent_address_city'),
                "permanent_address_pincode": applicant.get('permanent_address_pincode'),
                "permanent_address_state": cls.get_state(permanent_state) if permanent_state else None,
                "current_address_line_1": applicant.get('current_address'),
                "current_address_city": applicant.get('current_address_city'),
                "current_address_pincode": applicant.get('current_address_pincode'),
                "current_address_state": cls.get_state(current_state) if current_state else None,
                "current_address_status": applicant.get('current_address_status'),
                "permanent_address_status": applicant.get('permanent_address_status'),
                "company_name": cls.get_company_id(company_name) if company_name else None,
                "monthly_income": applicant.get('monthly_income'),
                "work_address_line_1": applicant.get('employment_address'),
                "aadhaar_card": applicant.get('aadharNumber'),
                "employee_status": profession_type_enum.get(profession_type) if profession_type else None,
            }
            cls._run_update('consumer_application', consumer_application_values, 'id', application_id)

            # consumer_application_element.
            installation_state = applicant.get('installation_address_state')
            service_provider = applicant.get('service_provider')

            consumer_application_element_values = {
                "installation_address_line_1": applicant.get('installation_address'),
                "installation_address_city": applicant.get('installation_address_city'),
                "installation_address_pincode": applicant.get('installation_address_pincode'),
                "installation_address_state": cls.get_state(installation_state) if installation_state else None,
                "installation_address_status": applicant.get('installation_address_status'),
                "type_of_customer": applicant.get('typeOfCustomer'),
                "solar_power_type": applicant.get('solarPowerType'),
                "inverter": applicant.get('inverterToBeInstalled'),
                "panel": applicant.get('panelToBeInstalled'),
                "inverter_gross_project_amount": applicant.get('grossProjectValue'),
                "inverter_monthly_power_bill": applicant.get('monthlyPowerBill'),
                "discom_service_provider": discom_name_enum.get(service_provider) if service_provider else None,
                "inverter_KW_installation": applicant.get('kwInstallation'),
                "inverter_subsidy_amount": applicant.get('subsidyAmount'),
                "father_name": applicant.get('fatherName'),
                "mother_name": applicant.get('motherName'),
            }
            cls._run_update('consumer_application_element', consumer_application_element_values,
                            'application_id', application_id)

            return True
        except Exception as ex:
            print_log(f"Save applicant error - {str(ex)}")
            return False

    @classmethod
    def checkQueueConditions(cls, app_id, type):
        try:
            status, result = QueryExecution.fetch_result(direct_query.fetch_application_data, (app_id,))
            if result.get("co_applicant_name"):
                status, data = QueryExecution.fetch_result(direct_query.consent_logs, (app_id,2 if type == 1 else 1))
                if data:
                    return True
                return False
            return True
        except Exception as ex:
            print_log(f"Error in checkQueueConditions: {str(ex)}")
            return False

    @classmethod
    def get_vendor_application_status(cls, application_id):
        try:
            query1 = "SELECT id FROM vendor_loans_data WHERE application_id = %s ORDER BY created_at DESC LIMIT 1"
            status1, result1 = QueryExecution.fetch_result(query1, (application_id,))
            if not result1:
                return None

            vendor_id = result1.get('id') if isinstance(result1, dict) else result1[0].get('id')

            query2 = "SELECT status, remark, visible_to_partner, updated_at FROM vendor_status_logs WHERE vendor_loan_id = %s ORDER BY created_at DESC LIMIT 1"
            status2, result2 = QueryExecution.fetch_result(query2, (vendor_id,))
            if not result2:
                return None
            log_data = result2 if isinstance(result2, dict) else result2[0]
            message = log_data.get('remark', '')
            if log_data.get('visible_to_partner', 0) == 0:
                message=''
            return {
                "status": log_data.get('status', ''),
                "remark": message,
                "updated_at": log_data.get('updated_at', '')
            }
        except Exception as ex:
            print_log(f"Error in get_vendor_application_status: {str(ex)}")
            return None