import os import requests import time import boto3 import json from db.query import QueryExecution from helper_functions import get_data from logger_module.package_logger import print_log class VendorStaticFunctions: @classmethod def fetch_bre_payload(cls, application_id): # Default payload representing the hardcoded fallback payload = { "age": 32, "gender": "male", "marital_status": "single", "applicant_ntc": "no", "household_monthly_income": 500000, "household_annual_income": 6000000, "household_expenses": 100000, "existing_emi_obligations": 9000, "business_income": 100000, "agriculture_income": 0, "dairy_income": 0, "foir": 47, "eligible_emi": 5000, "eligible_loan_amount": 250000, "loan_utilization": "expansion of existing business", "name_of_bureau": "crif", "bureau_score": 750, "bureau_pull_date": "1983-01-01", "overdue_amount": 0, "dpd_0_6_months": 0, "bureau_sub_count": 0, "bureau_dbt_count": 0, "bureau_loss_count": 0, "bureau_write_off_count": 0, "bureau_settlement_count": 0, "bureau_suit_filled_count": 0, "bureau_willful_default_count": 0, "restructured_count": 0, "dpd_6_12_months": 0, "enquiries_last_3_months": 0, "active_credit_lines_count": 0, "bank_statement_6m": 1, "cheque_bounce_count_6m": 0, "loan_amount": 200000, "tenure": 12, "interest_rate": 31, "apr": 34.14, "repayment_frequency": "monthly", "loan_purpose": "business expansion", "residence_area": "rural", "residence_vintage_years": 20, "residence_ownership": "rented", "residence_to_branch_distance_km": 9, "business_vintage_years": 20, "asset_fridge_value": 10000, "asset_air_conditioner_value": 0, "asset_washing_machine_value": 12000, "asset_tv_value": 8000, "asset_smart_phone_value": 20000, "co_applicant_age_1": 30, "co_applicant_years_of_stay_in_current_address_1": "", "co_applicant_ntc_1": "no", "co_applicant_financial_status_1": "financial", "co_applicant_name_of_bureau_1": "crif", "co_applicant_bureau_score_1": 745, "co_applicant_bureau_pull_date_1": "2025-03-26", "co_applicant_overdue_amount_1": 0, "co_applicant_dpd_0_6_months_1": 0, "co_applicant_dpd_6_12_months_1": 0, "co_applicant_bureau_sub_count_1": 0, "co_applicant_bureau_dbt_count_1": 0, "co_applicant_bureau_loss_count_1": 0, "co_applicant_bureau_write_off_count_1": 0, "co_applicant_bureau_settlement_count_1": 0, "co_applicant_bureau_suit_filed_count_1": 0, "co_applicant_bureau_willful_default_count_1": 0, "co_applicant_restructured_count_1": 0, "co_applicant_enquiries_last_3_months_1": 0, "co_applicant_active_credit_lines_count_1": 3, "co_applicant_age_2": 43, "co_applicant_years_of_stay_in_current_address_2": 30, "co_applicant_ntc_2": "no", "co_applicant_financial_status_2": "financial", "co_applicant_name_of_bureau_2": "crif", "co_applicant_bureau_score_2": 0, "co_applicant_bureau_pull_date_2": "2026-04-15", "co_applicant_overdue_amount_2": 0, "co_applicant_dpd_0_6_months_2": 0, "co_applicant_dpd_6_12_months_2": 0, "co_applicant_bureau_sub_count_2": 0, "co_applicant_bureau_dbt_count_2": 0, "co_applicant_bureau_loss_count_2": 0, "co_applicant_bureau_write_off_count_2": 0, "co_applicant_bureau_settlement_count_2": 0, "co_applicant_bureau_suit_filed_count_2": 0, "co_applicant_bureau_willful_default_count_2": 0, "co_applicant_restructured_count_2": 0, "co_applicant_enquiries_last_3_months_2": 0, "co_applicant_active_credit_lines_count_2": 0, "borrower_maturity_age": 46, "co_applicant_relationship_1": "son", "co_applicant_relationship_2": "father" } # vendor_loans_data status, v_data = QueryExecution.fetch_result('SELECT gender, marital_status, loan_amount, purpose_of_loan, tenure, roi, apr, date_of_birth FROM vendor_loans_data WHERE application_id = %s', (application_id,)) if v_data: if v_data.get('gender'): payload['gender'] = v_data.get('gender') if v_data.get('marital_status'): payload['marital_status'] = v_data.get('marital_status') if v_data.get('loan_amount') is not None: payload['loan_amount'] = v_data.get('loan_amount') if v_data.get('purpose_of_loan'): payload['loan_purpose'] = v_data.get('purpose_of_loan') if v_data.get('tenure') is not None: payload['tenure'] = v_data.get('tenure') if v_data.get('roi') is not None: payload['interest_rate'] = v_data.get('roi') if v_data.get('apr') is not None: payload['apr'] = v_data.get('apr') if v_data.get('date_of_birth'): from datetime import date dob = v_data.get('date_of_birth') payload['age'] = date.today().year - dob.year - ((date.today().month, date.today().day) < (dob.month, dob.day)) # vendor_loans_bank_details (fetch latest) status, bank_data = QueryExecution.fetch_result('SELECT no_of_cheque_bounces FROM vendor_loans_bank_details WHERE application_id = %s ORDER BY id DESC LIMIT 1', (application_id,)) if bank_data and bank_data.get('no_of_cheque_bounces') is not None: payload['cheque_bounce_count_6m'] = bank_data.get('no_of_cheque_bounces') # vendor_loans_bureau_obligations (fetch latest) status, bureau_data = QueryExecution.fetch_result('SELECT total_existing_obligations, overdue_amount_except_cc_and_kcc, name_of_bureau FROM vendor_loans_bureau_obligations WHERE application_id = %s ORDER BY id DESC LIMIT 1', (application_id,)) if bureau_data: if bureau_data.get('total_existing_obligations') is not None: payload['existing_emi_obligations'] = bureau_data.get('total_existing_obligations') if bureau_data.get('overdue_amount_except_cc_and_kcc') is not None: payload['overdue_amount'] = bureau_data.get('overdue_amount_except_cc_and_kcc') if bureau_data.get('name_of_bureau'): payload['name_of_bureau'] = bureau_data.get('name_of_bureau') # vendor_loans_business_details (fetch latest) status, business_data = QueryExecution.fetch_result('SELECT self_employed_business_vintage, property_ownership FROM vendor_loans_business_details WHERE application_id = %s ORDER BY id DESC LIMIT 1', (application_id,)) if business_data: pass # vendor_loans_co_applicants_data (fetch all) status, co_apps = QueryExecution.fetch_result('SELECT co_applicant_index, age, relationship, gender, marital_status FROM vendor_loans_co_applicants_data WHERE application_id = %s ORDER BY co_applicant_index ASC', (application_id,)) if co_apps: if isinstance(co_apps, dict): co_apps = [co_apps] for c in co_apps: idx = c.get('co_applicant_index', 1) if c.get('age') is not None: payload[f'co_applicant_age_{idx}'] = c.get('age') if c.get('relationship'): payload[f'co_applicant_relationship_{idx}'] = c.get('relationship') return payload @classmethod def _execute_bre(cls, application_number, policy_id, payload): from logger_module.package_logger import print_log # Robustly check if payload is missing, empty, or string "None"/"null" if not payload or payload in ["None", "null", "{}", ""] or payload == {}: payload = cls.fetch_bre_payload(application_number) print_log(f"run_bre_policy started: {application_number}, policy_id: {policy_id}, payload: {payload}") policy_url = f"https://decision.creditfair.in/api/live/policy/{policy_id}" headers = { "X-API-Key": os.getenv("WERIZE_BRE_POLICY_KEY"), "X-API-Secret": os.getenv("WERIZE_BRE_POLICY_SECRET") } policy_res = requests.get(policy_url, headers=headers) if policy_res.status_code != 200: print_log(f"run_bre_policy failed to fetch: {policy_res.status_code} {policy_res.text}") return {"error": "Failed to fetch policy", "status_code": policy_res.status_code, "raw": policy_res.text} rules = policy_res.json() if isinstance(payload, str): try: payload = json.loads(payload) except Exception: pass # Dynamically inject the payload into whatever rule groups were fetched if isinstance(payload, dict): payload['application_id'] = application_number try: for rule_name, rule_data in rules.get("grade", {}).get("rules", {}).items(): rule_data["payload"] = payload except Exception: pass final_payload = rules run_url = "https://decision.creditfair.in/api/sandbox/run" headers_run = {'Content-Type': 'application/json'} run_res = requests.post(run_url, json=final_payload, headers=headers_run) if run_res.status_code == 200: res_json = run_res.json() print_log(f"run_bre_policy success: {res_json}") return res_json print_log(f"run_bre_policy failed execution: {run_res.status_code} {run_res.text}") return {"error": "BRE execution failed", "status": run_res.status_code, "raw": run_res.text} @classmethod def run_bre_policy(cls, application_number, policy_id, payload): res_json = cls._execute_bre(application_number, policy_id, payload) if "error" in res_json: return res_json status_bool = res_json.get("status", False) return { "status": "Approved" if status_bool else "Rejected" } @classmethod def get_bre_output(cls, application_number, policy_id, payload): return cls._execute_bre(application_number, policy_id, payload) @classmethod def DownloadAndSaveDocument(cls, application_id, applicant_type, filename, docpath): try: s3_client = boto3.client( "s3", aws_access_key_id=os.getenv("AWS_ACCESS_KEY_ID"), aws_secret_access_key=os.getenv("AWS_SECRET_ACCESS_KEY"), region_name=os.getenv("AWS_REGION_NAME") ) bucket = os.getenv("AWS_BUCKET") max_file_size = 20 * 1024 * 1024 file_content = b"" try: if isinstance(docpath, str) and docpath.startswith(("http://", "https://")): response = requests.get(docpath, timeout=60) try: if response.status_code != 200: return { "status": "doc_download_fail", "message": "Unable to download document from presigned URL" } if int(response.headers.get("Content-Length", 0) or 0) > max_file_size: return { "status": "doc_download_fail", "message": "Document exceeds the 20 MiB size limit" } file_content = response.content finally: response.close() else: source_object = s3_client.get_object(Bucket=bucket, Key=docpath) source_body = source_object["Body"] try: if source_object.get("ContentLength", 0) > max_file_size: return { "status": "doc_download_fail", "message": "Document exceeds the 20 MiB size limit" } file_content = source_body.read(max_file_size + 1) finally: source_body.close() if len(file_content) > max_file_size: return { "status": "doc_size_exceeded", "message": "Document exceeds the 20 MiB size limit" } except Exception as ex: print_log( f"Document download failed for application " f"{application_id}: {str(ex)}" ) return { "status": "doc_download_fail", "message": "Unable to download document" } if not file_content: return { "status": "doc_download_empty", "message": "Downloaded document is empty" } if applicant_type == 1: s3_filename = f"{filename}_{int(time.time())}" elif int(applicant_type) == 2: s3_filename = f"{filename}_{str(applicant_type).split('.')[1]}_{int(time.time())}" # S3 Upload s3_path = f"{application_id}/{s3_filename}" s3_client.put_object(Bucket=bucket, Key=s3_path, Body=file_content) return {"status": "success", "s3_path": s3_path} except Exception as ex: print_log( f"DownloadAndSaveDocument failed for application " f"{application_id}: {str(ex)}" ) return {"status": "failed",} @classmethod def application_data_logs(cls, request_body): data = {} data.update(add_fields(request_body, { "dateOfBirth": "dob", "gender": "gender", "maritalStatus": "marital_status", "mobileNumber": "mobile_number", "personalEmailId": "email", "panNumber": "pan_number", "aadhaarNumber": "aadhaar_number", "nationality": "nationality", "entityType": "entity_type", "loanAmount": "loan_amount", "purposeOfLoan": "purpose_of_loan", "tenure": "tenure", "tenureFrequency": "tenure_frequency", "roi": "roi", "apr": "apr", "emi": "emi", "processingFees": "processing_fees", "insuranceCharges": "insurance_charges", "documentCharges": "document_charges", "eligibleLoanAmountMethod": "eligible_loan_amount_method", "propertyOwnership": "property_ownership", })) address_fields = { "addressType": "type", "addressLine": "line", "city": "city", "district": "district", "state": "state", "pincode": "pincode", } for index, address in enumerate(request_body.get("addresses") or [], start=1): if not isinstance(address, dict): continue address_type = address.get("addressType") prefix = f"{address_type}_address_" if address_type else f"address_{index}_" add_fields(address, address_fields, prefix) data.update(add_fields(request_body.get("companyDetails") or {}, { "companyName": "name", "companyAddress": "address", "companyCity": "city", "companyDistrict": "district", "companyState": "state", "companyPincode": "pincode", "industryType": "industry_type", "businessVintage": "business_vintage", "udyamNumber": "udyam_number", "annualTurnover": "annual_turnover", "gst": "gst", "cin": "cin", "propertyOwnership": "property_ownership", }, "company_")) add_fields(request_body.get("bankDetails") or {}, { "accountHolderName": "account_holder_name", "accountNumber": "account_number", "accountType": "account_type", "bankName": "bank_name", "ifscCode": "ifsc_code", "minBankTransactionInMonth": "min_bank_transaction_in_month", "noOfCreditTransactionInMonth": "no_of_credit_transaction_in_month", "noOfDebitTransactionInMonth": "no_of_debit_transaction_in_month", "noOfInwardTransactions": "no_of_inward_transactions", "noOfChequeBounces": "no_of_cheque_bounces", "noOfInwardChequeReturns": "no_of_inward_cheque_returns", "noOfOutwardChequeReturns": "no_of_outward_cheque_returns", "maxCcOdUtilization": "max_cc_od_utilization", "nachReturnInLast3Months": "nach_return_in_last_3_months", }, "bank_") add_fields(request_body.get("bureauDetails") or {}, { "totalExistingObligations": "total_existing_obligations", "overdueAmountInCCKCCGuarantorLoans": "overdue_amount_in_cc_kcc_guarantor_loans", "overdueAmountExceptCCAndKCC": "overdue_amount_except_cc_and_kcc", "nameOfBureau": "name_of_bureau", "maxDpdTradelineIn6Months": "max_dpd_tradeline_in_6_months", "maxDpdTradelineIn12Months": "max_dpd_tradeline_in_12_months", "restructuringInCcKccGoldLoanLast36Months": "restructuring_in_cc_kcc_gold_loan_last_36_months", "restructuringInCcLast36Months": "restructuring_in_cc_last_36_months", "restructuringInLast36Months": "restructuring_in_last_36_months", }, "bureau_") co_applicant_fields = { "firstName": "name", "dateOfBirth": "dob", "gender": "gender", "maritalStatus": "marital_status", "relationship": "relationship", "mobileNumber": "mobile_number", "panNumber": "pan_number", "aadhaarNumber": "aadhaar_number", "permanentAddress": "permanent_address", "permanentCity": "permanent_city", "permanentState": "permanent_state", "permanentPincode": "permanent_pincode", } for index, applicant in enumerate(request_body.get("coApplicants") or [], start=1): if isinstance(applicant, dict): add_fields(applicant, co_applicant_fields, f"co_applicant_{index}_") return json.dumps(data) @classmethod def setFileName(cls, applicant_type): if applicant_type == 1: result_type = "applicant" if isinstance(applicant_type, float): applicant_type_split = str(applicant_type).split('.') if len(applicant_type_split) > 1: if applicant_type_split[0] == '2': result_type = f"co_applicant_{applicant_type_split[1]}" # elif applicant_type_split[0] == '3': # result_type = f"guarantor_{applicant_type_split[1]}" return result_type @classmethod def setApplicantType(cls, applicant_type): result_type = applicant_type if isinstance(applicant_type, float): applicant_type_split = str(applicant_type).split('.') if len(applicant_type_split) > 1: if applicant_type_split[0] == '2': result_type = applicant_type_split[0] return result_type @classmethod def setDocumentStage(cls, file_name): pre_sanctioned_documents = { "poi":["pan","form60"], "poa":["aadhar"], "income_proof": ["bank_statement"], "ownership_proof": ["house_utility_bill","business_utility_bill","ownership_declaration","house_tax_received","water_bill"], "business_proof": ["udhyam_certificate","udyog_aadhar","MSME_certificate","gumasta","GST_certificate","POR","ITR","labor_certificate"], "bureau": ["bureau_report"] } post_sanctioned_documents = [ "signed_loan_agreement","NACH", "UDC","pennydrop_verification","vkyc_report","video_PD","customer_selfie","mandate" ] if file_name and any(stage in file_name for stage in post_sanctioned_documents): return "post_sanctioned" elif file_name and any(file_name in sublist for sublist in pre_sanctioned_documents.values()): return "pre_sanctioned" else: print_log(f"Document stage not found for file_name: {file_name}") return None @classmethod def checkPreSanctionedDocumentsAvailable(cls, loan_id): # Applicant: at least ONE document from each group must be present applicant_pre_sanctioned_documents = { "poi": ["pan", "form60"], "poa": ["aadhar"], "income_proof": ["bank_statement"], "ownership_proof": ["house_utility_bill", "business_utility_bill", "ownership_declaration", "house_tax_received", "water_bill"], "business_proof": ["udhyam_certificate", "udyog_aadhar", "MSME_certificate", "gumasta", "GST_certificate", "POR", "ITR", "labor_certificate"], "bureau": ["bureau_report"] } # Co-applicant: ALL of these must be present (only if co-applicants exist) co_applicant_pre_sanctioned_documents = ["pan", "aadhar"] def normalize(value): return str(value or "").strip().lower() # 1. Fetch active co-applicants (max 5) co_applicant_query = """ SELECT co_applicant_index FROM vendor_loans_co_applicants_data WHERE vendor_loan_id = %s AND deleted_at IS NULL ORDER BY co_applicant_index LIMIT 5 """ co_status, co_applicants = QueryExecution.fetch_result(co_applicant_query, (loan_id,)) if not co_status or not co_applicants: co_applicants = [] elif isinstance(co_applicants, dict): co_applicants = [co_applicants] co_applicant_indexes = { row.get("co_applicant_index") for row in co_applicants if isinstance(row, dict) } # 2. Fetch documents # NOTE: assumes blacksoil_documents.co_applicant_index is NULL for applicant docs doc_query = """ SELECT document_code, document_status, applicant_type FROM blacksoil_documents WHERE vendor_loan_id = %s """ status, existing_documents = QueryExecution.fetch_result(doc_query, (loan_id,)) if not status: return False if isinstance(existing_documents, dict): existing_documents = [existing_documents] applicant_valid_codes = set() co_applicant_valid_codes = {} # co_applicant_index -> set of valid codes for doc in existing_documents or []: if not isinstance(doc, dict): continue document_code = normalize(doc.get("document_code")) if not document_code: continue # status 0 = rejected -> doesn't count as available if doc.get("document_status") == 0: continue co_index = doc.get("co_applicant_index") if co_index is None: applicant_valid_codes.add(document_code) else: co_applicant_valid_codes.setdefault(co_index, set()).add(document_code) # 3. Applicant: any one document per group for group_codes in applicant_pre_sanctioned_documents.values(): if not any(normalize(code) in applicant_valid_codes for code in group_codes): return False # 4. Co-applicants: every required document, for each co-applicant on the loan required_co_codes = {normalize(c) for c in co_applicant_pre_sanctioned_documents} for co_index in co_applicant_indexes: if not required_co_codes.issubset(co_applicant_valid_codes.get(co_index, set())): return False return True @classmethod def checkPostSanctionedDocumentsAvailable(cls, loan_id): post_sanctioned_documents = [ "signed_loan_agreement", "NACH", "UDC", "pennydrop_verification", "vkyc_report", "video_PD", "customer_selfie", "mandate" ] def normalize(value): return str(value or "").strip().lower() query = """ SELECT document_code, document_status FROM blacksoil_documents WHERE vendor_loan_id = %s """ status, existing_documents = QueryExecution.fetch_result(query, (loan_id,)) if not status: return False if isinstance(existing_documents, dict): existing_documents = [existing_documents] valid_codes = set() for doc in existing_documents or []: if not isinstance(doc, dict): continue document_code = normalize(doc.get("document_code")) if not document_code: continue # status 0 = rejected -> doesn't count as available if doc.get("document_status") == 0: continue valid_codes.add(document_code) # All post-sanctioned documents must be present required_codes = {normalize(code) for code in post_sanctioned_documents} return required_codes.issubset(valid_codes) @classmethod def checkBRERehitNeeded(cls, request_body, loan_details): if request_body.get("loan_amount") != loan_details.get("loan_amount") or request_body.get("tenure") != loan_details.get("tenure") or request_body.get("purpose_of_loan") != loan_details.get("purpose_of_loan"): return True return False def add_fields(source, fields, prefix=""): result_data = {} for source_key, log_key in fields.items(): if source_key in source: result_data[f"{prefix}{log_key}"] = { "old": "", "new": source[source_key], } return result_data