from services.dbservice import DbService
from sqlalchemy.sql import text
from app import db
import time

from Models.com_ac_history_and_dpd import Com_ac_history_and_dpd
from Models.com_credit_facility_current_detail import Com_credit_facility_current_detail
# from Models.con_enquiry_detail import Con_enquiry_detail
from Models.month_bucket_master import Month_bucket_master
from Models.com_borrower_profile import Com_borrower_profile
from Models.com_enquiry_detail import Com_enquiry_detail


class CibilCommercialDataService:

    def getCreditFacilityCurrentDetails(application_reference_number,los_id):
        # https://flask-sqlalchemy.palletsprojects.com/en/2.x/queries/
        # print(application_reference_number)
        # records = Com_credit_facility_current_detail.query.all()
        start_time = time.time()
        records = Com_credit_facility_current_detail.query.filter_by(ApplicationRefno = application_reference_number).all()
        temp = []
        for idx,record in enumerate(records): 
            # print(record.temp_id,'---',record.CreditFacilityCDcfSrNo[16:])
            row_as_dict = {column: str(getattr(record, column)) for column in record.__table__.c.keys()}
            # print(row_as_dict)
            temp.append(row_as_dict)
        # print("--- %s seconds ---" % (time.time() - start_time))    
        return temp


    def getCommercialAcHistoryAndDPDDetails(application_reference_number,los_id):
        
        # records = Com_ac_history_and_dpd.query.filter_by(ApplicationRefno = application_reference_number,BorrowerCurrentDetailsID = credit_facility_id).order_by(Com_ac_history_and_dpd.BorrowerCurrentDetailsID.asc()).all()
        records = Com_ac_history_and_dpd.query.filter_by(ApplicationRefno = application_reference_number).order_by(Com_ac_history_and_dpd.BorrowerCurrentDetailsID.asc()).all()
        temp = []
        # print(records)
        for record in records: 
            # print(record.id,'---',record.AccountTranDetailsID)
            row_as_dict = {column: str(getattr(record, column)) for column in record.__table__.c.keys()}
            # print(row_as_dict)
            temp.append(row_as_dict)
            
        return temp

    def getMonthBucketMaster(mtype=None):
        
        # records = Con_dpd_transaction_detail.query.filter_by(MemberRefNo = member_reference_number,AccountTranDetailsID = account_tran_details_id).order_by(Con_dpd_transaction_detail.StartDate.desc()).all()
        records = Month_bucket_master.query.filter_by(type = (mtype),isactive = 1).order_by(Month_bucket_master.id.asc()).all()
        temp = []
        # print(records)
        for record in records: 
            # print(record.id,'---',record.AccountTranDetailsID)
            row_as_dict = {column: str(getattr(record, column)) for column in record.__table__.c.keys()}
            # print(row_as_dict)
            temp.append(row_as_dict)
            
        return temp


    def getCommercialBorrowerDetails(application_reference_number,los_id):
        
        # records = Con_dpd_transaction_detail.query.filter_by(MemberRefNo = member_reference_number,AccountTranDetailsID = account_tran_details_id).order_by(Con_dpd_transaction_detail.StartDate.desc()).all()
        records = Com_borrower_profile.query.filter_by(ApplicationRefno = application_reference_number).all()
        temp = []
        # print(records)
        for record in records: 
            # print(record.id,'---',record.AccountTranDetailsID)
            row_as_dict = {column: str(getattr(record, column)) for column in record.__table__.c.keys()}
            # print(row_as_dict)
            temp.append(row_as_dict)
            
        return temp


    def getCommercialsEnquriyDetails(application_reference_number,los_id):
        
        # records = Con_dpd_transaction_detail.query.filter_by(MemberRefNo = member_reference_number,AccountTranDetailsID = account_tran_details_id).order_by(Con_dpd_transaction_detail.StartDate.desc()).all()
        records = Com_enquiry_detail.query.filter_by(ApplicationRefno = application_reference_number).all()
        temp = []
        # print(records)
        for record in records: 
            # print(record.id,'---',record.AccountTranDetailsID)
            row_as_dict = {column: str(getattr(record, column)) for column in record.__table__.c.keys()}
            # print(row_as_dict)
            temp.append(row_as_dict)
            
        return temp