production-evyos-systems-and-services-3/ServicesBank/Finder/Payment/draft/old_runner_second.py

import arrow
import calendar
import time

from decimal import Decimal
from datetime import datetime, timedelta
    
from Schemas import BuildDecisionBookPayments, AccountRecords, ApiEnumDropdown, Build, BuildDecisionBook
from time import perf_counter
from sqlalchemy import select, func, distinct, cast, Date, String, literal, desc, and_, or_, case
from Controllers.Postgres.engine import get_session_factory
#from ServicesApi.Schemas.account.account import AccountRecords
#from ServicesApi.Schemas.building.decision_book import BuildDecisionBookPayments


def find_last_day_of_month(date_value):
    today = date_value.date()
    _, last_day = calendar.monthrange(today.year, today.month)
    return datetime(today.year, today.month, last_day, 23, 59, 59)

def find_first_day_of_month(date_value):
    today = date_value.date()
    return datetime(today.year, today.month, 1)


def add_month_to_date(date_value):
    month_to_process = date_value.month
    year_to_process = date_value.year
    if date_value.month == 12:
        month_to_process = 1
        year_to_process += 1
    else:
        month_to_process += 1
    _, last_day = calendar.monthrange(year_to_process, month_to_process)
    return datetime(year_to_process, month_to_process, 1), datetime(year_to_process, month_to_process, last_day)


class BuildDuesTypes:
    
    def __init__(self):
        self.debit: ApiEnumDropdownShallowCopy = None
        self.add_debit: ApiEnumDropdownShallowCopy = None
        self.renovation: ApiEnumDropdownShallowCopy = None
        self.lawyer_expence: ApiEnumDropdownShallowCopy = None
        self.service_fee: ApiEnumDropdownShallowCopy = None
        self.information: ApiEnumDropdownShallowCopy = None


class ApiEnumDropdownShallowCopy:
    id: int
    uuid: str
    enum_class: str
    key: str
    value: str

    def __init__(self, id: int, uuid: str, enum_class: str, key: str, value: str):
        self.id = id
        self.uuid = uuid
        self.enum_class = enum_class
        self.key = key
        self.value = value


def retrieve_remainder_balance_set_if_needed(account_record_id: int, session):
    """Update the remainder_balance of an account after spending money.
    
    Args:
        account_record: The account record to update
        amount_spent: The amount spent in this transaction
        session: Database session
        
    Returns:
        bool: True if all money is spent, False otherwise
    """
    AccountRecords.set_session(session)

    account_record_remainder_balance = session.query(func.sum(func.abs(BuildDecisionBookPayments.payment_amount))).filter(
        BuildDecisionBookPayments.account_records_id == account_record_id,
        BuildDecisionBookPayments.account_is_debit == False 
    ).scalar()

    if not account_record_remainder_balance:
        account_record_remainder_balance = 0

    account_record = AccountRecords.query.filter_by(id=account_record_id).first()
    account_record.remainder_balance = -1 * abs(account_record_remainder_balance) if account_record_remainder_balance != 0 else 0 
    account_record.save()
    return account_record_remainder_balance


def retrieve_current_debt_to_pay_from_database(ref_id: int, session):
    debt_to_pay = session.query(func.sum(BuildDecisionBookPayments.payment_amount)).filter(BuildDecisionBookPayments.ref_id == ref_id).scalar()
    return abs(debt_to_pay)


def check_current_debt_to_pay_from_database_is_closed(ref_id: int, session):
    session.commit()
    BuildDecisionBookPayments.set_session(session)
    payment_row_book = BuildDecisionBookPayments.query.filter(BuildDecisionBookPayments.ref_id == str(ref_id), BuildDecisionBookPayments.account_is_debit == True).first()
    debt_paid = session.query(func.sum(func.abs(BuildDecisionBookPayments.payment_amount))).filter(
        BuildDecisionBookPayments.ref_id == str(ref_id), BuildDecisionBookPayments.account_is_debit == False
    ).scalar()
    payment_row_book.debt_paid = abs(debt_paid) if debt_paid else 0                                          # Debt Reminder is how much money is needed to close record
    payment_row_book.debt_to_pay = abs(payment_row_book.payment_amount) - abs(payment_row_book.debt_paid)    # Debt To Pay is how much money is needed to close record
    payment_row_book.is_closed = payment_row_book.debt_to_pay == 0
    payment_row_book.save()


def close_payment_book(payment_row_book, account_record, value, session):
    """Create a credit entry in BuildDecisionBookPayments to close a debt.
    
    Args:
        payment_row_book: The debit entry to be paid
        account_record: The account record containing the funds
        value: The amount to pay
        session: Database session
        
    Returns:
        The newly created payment record
    """
    BuildDecisionBookPayments.set_session(session)
    account_record_remainder_balance = retrieve_remainder_balance_set_if_needed(account_record_id=account_record.id, session=session)
    print(f'NOT Updated remainder balance: {account_record_remainder_balance} | Account record id: {account_record.id}')

    # Create a new credit entry (payment)
    new_row = BuildDecisionBookPayments.create(
        ref_id=str(payment_row_book.uu_id),
        payment_plan_time_periods=payment_row_book.payment_plan_time_periods,
        period_time=payment_row_book.period_time,
        currency=payment_row_book.currency,
        account_records_id=account_record.id,
        account_records_uu_id=str(account_record.uu_id),
        build_parts_id=payment_row_book.build_parts_id,
        build_parts_uu_id=str(payment_row_book.build_parts_uu_id),
        payment_amount=abs(value),  # Negative for credit entries
        payment_types_id=payment_row_book.payment_types_id,
        payment_types_uu_id=str(payment_row_book.payment_types_uu_id),
        process_date_m=payment_row_book.process_date.month,
        process_date_y=payment_row_book.process_date.year,
        process_date=payment_row_book.process_date,
        build_decision_book_item_id=payment_row_book.build_decision_book_item_id if payment_row_book.build_decision_book_item_id else None,
        build_decision_book_item_uu_id=str(payment_row_book.build_decision_book_item_uu_id) if payment_row_book.build_decision_book_item_uu_id else None,
        decision_book_project_id=payment_row_book.decision_book_project_id if payment_row_book.decision_book_project_id else None,
        decision_book_project_uu_id=str(payment_row_book.decision_book_project_uu_id) if payment_row_book.decision_book_project_uu_id else None,
        build_decision_book_id=payment_row_book.build_decision_book_id if payment_row_book.build_decision_book_id else None,
        build_decision_book_uu_id=str(payment_row_book.build_decision_book_uu_id) if payment_row_book.build_decision_book_uu_id else None,
        is_confirmed=True,
        account_is_debit=False,
    )
    
    # Save the new payment record
    saved_row = new_row.save()
    session.commit()
    session.refresh(saved_row)
    account_record_remainder_balance = retrieve_remainder_balance_set_if_needed(account_record_id=account_record.id, session=session)
    print(f'Updated remainder balance: {account_record_remainder_balance} | Account record id: {account_record.id}')
    check_current_debt_to_pay_from_database_is_closed(ref_id=new_row.ref_id, session=session)
    return account_record_remainder_balance


def get_enums_from_database():
    build_dues_types = BuildDuesTypes()
    with ApiEnumDropdown.new_session() as session:

        ApiEnumDropdown.set_session(session)

        debit_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-D").first()                  # Debit
        add_debit_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-A").first()              # Add Debit
        renovation_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-R").first()             # Renovation
        late_payment_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-L").first()           # Lawyer expence
        service_fee_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-S").first()            # Service fee
        information_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-I").first()            # Information

        build_dues_types.debit = ApiEnumDropdownShallowCopy(
            debit_enum_shallow.id, str(debit_enum_shallow.uu_id), debit_enum_shallow.enum_class, debit_enum_shallow.key, debit_enum_shallow.value
        )
        build_dues_types.add_debit = ApiEnumDropdownShallowCopy(
            add_debit_enum_shallow.id, str(add_debit_enum_shallow.uu_id), add_debit_enum_shallow.enum_class, add_debit_enum_shallow.key, add_debit_enum_shallow.value
        )
        build_dues_types.renovation = ApiEnumDropdownShallowCopy(
            renovation_enum_shallow.id, str(renovation_enum_shallow.uu_id), renovation_enum_shallow.enum_class, renovation_enum_shallow.key, renovation_enum_shallow.value
        )
        build_dues_types.lawyer_expence = ApiEnumDropdownShallowCopy(
            late_payment_enum_shallow.id, str(late_payment_enum_shallow.uu_id), late_payment_enum_shallow.enum_class, late_payment_enum_shallow.key, late_payment_enum_shallow.value
        )
        build_dues_types.service_fee = ApiEnumDropdownShallowCopy(
            service_fee_enum_shallow.id, str(service_fee_enum_shallow.uu_id), service_fee_enum_shallow.enum_class, service_fee_enum_shallow.key, service_fee_enum_shallow.value
        )
        build_dues_types.information = ApiEnumDropdownShallowCopy(
            information_enum_shallow.id, str(information_enum_shallow.uu_id), information_enum_shallow.enum_class, information_enum_shallow.key, information_enum_shallow.value
        )
    return [build_dues_types.debit, build_dues_types.lawyer_expence, build_dues_types.add_debit, build_dues_types.renovation, build_dues_types.service_fee, build_dues_types.information]


def do_payments(build_id: int, work_date: datetime, run_now_bool: bool = False):
    """Process payments for the current month's unpaid debts.
    This function retrieves account records with available funds and processes
    payments for current month's unpaid debts in order of payment type priority.
    """
    session_factory = get_session_factory()
    session = session_factory()
    
    # Set session for all models
    AccountRecords.set_session(session)
    BuildDecisionBookPayments.set_session(session)
    Build.set_session(session)
    BuildDecisionBook.set_session(session)
    
    # Get payment types in priority order
    payment_type_list = get_enums_from_database()
    fund_finished = lambda money_spend, money_in_account: money_spend == money_in_account
    payments_made, total_amount_paid, paid_count = 0, 0, 0
    target_build = Build.query.filter(Build.id == build_id).first()
    if not target_build:
        raise ValueError(f"Build with id {build_id} not found")

    decision_book = BuildDecisionBook.query.filter(
        BuildDecisionBook.build_id == build_id,  cast(BuildDecisionBook.expiry_starts, Date) <= work_date.date(), 
        cast(BuildDecisionBook.expiry_ends, Date) >= work_date.date(), BuildDecisionBook.decision_type == "RBM"
    ).first()
    if not decision_book:
        raise ValueError(f"Decision book not found for build with id {build_id}")

    period_date_start = decision_book.expiry_starts
    period_date_end = decision_book.expiry_ends
    period_id = decision_book.id
    
    first_date_of_process_date = find_first_day_of_month(datetime(work_date.year, work_date.month, 1))
    last_date_of_process_date = find_last_day_of_month(datetime(work_date.year, work_date.month, 1))
    if run_now_bool:
        last_date_of_process_date = work_date
    print('first_date_of_process_date', first_date_of_process_date)
    print('last_date_of_process_date', last_date_of_process_date)
    
    # Current month date filter
    date_query_tuple = (
        cast(BuildDecisionBookPayments.process_date, Date) >= first_date_of_process_date.date(), cast(BuildDecisionBookPayments.process_date, Date) <= last_date_of_process_date.date()
    )
    date_query_account_tuple = (
        cast(AccountRecords.bank_date, Date) >= first_date_of_process_date.date(), cast(AccountRecords.bank_date, Date) <= last_date_of_process_date.date()
    )
    for payment_type in payment_type_list:
        query_of_payments = BuildDecisionBookPayments.query.filter(*date_query_tuple)
        query_of_payments = query_of_payments.filter(
            BuildDecisionBookPayments.payment_types_id == payment_type.id, BuildDecisionBookPayments.account_is_debit == True,
            BuildDecisionBookPayments.is_closed == False
        ).order_by(BuildDecisionBookPayments.process_date.desc()).all()
        # priority_uuids = [payment_type.uuid, *[pt.uuid for pt in payment_type_list if pt.uuid != payment_type.uuid]]
        # case_order = case(*[(AccountRecords.payment_result_type_uu_id == uuid, index) for index, uuid in enumerate(priority_uuids)], else_=len(priority_uuids))

        for payment_row in query_of_payments:
            money_to_pay_rows = AccountRecords.query.filter(
                AccountRecords.build_parts_id == payment_row.build_parts_id, AccountRecords.payment_result_type == payment_type.id,
                AccountRecords.currency_value > 0, AccountRecords.currency_value > func.abs(AccountRecords.remainder_balance), *date_query_account_tuple
            ).order_by(case_order, AccountRecords.bank_date.asc()).all()
            for money_to_pay_row in money_to_pay_rows:
                # Get remainder balance from database regardless trust over AccountRecords row from first query
                remainder_balance_from_database = retrieve_remainder_balance_set_if_needed(money_to_pay_row.id, session)
                available_funds = abs(money_to_pay_row.currency_value) - abs(remainder_balance_from_database)
                # BuildDecisionBookPayments must be refreshed to check is there still money to be paid for this row
                debt_to_pay = retrieve_current_debt_to_pay_from_database(ref_id=payment_row.ref_id, session=session)
                if debt_to_pay == 0:
                    break   # For session caused errors double check the database FOR multi process actions
                if abs(available_funds) > abs(debt_to_pay):
                    payments_made += 1
                    total_amount_paid += debt_to_pay
                    paid_count += 1
                    close_payment_book(payment_row, money_to_pay_row, abs(debt_to_pay), session)
                    break      # More fund to spend so go to next BuildDecisionBookPayments
                else:
                    payments_made += 1
                    total_amount_paid += available_funds
                    paid_count += 1
                    close_payment_book(payment_row, money_to_pay_row, abs(available_funds), session)
                    continue   # Fund has finished so go to next AccountRecords

    print('payments_made', payments_made)
    print('total_amount_paid', total_amount_paid)
    print('paid_count', paid_count)
    session.close()
    session_factory.remove()  # Clean up the session from the registry


def do_payments_of_previos_months(build_id: int):
    """Process payments for the previous month's unpaid debts.
    This function retrieves account records with available funds and processes
    payments for previous month's unpaid debts in order of payment type priority.
    """
    session_factory = get_session_factory()
    session = session_factory()
    
    # Set session for all models
    AccountRecords.set_session(session)
    BuildDecisionBookPayments.set_session(session)
    Build.set_session(session)
    BuildDecisionBook.set_session(session)
    
    # Get payment types in priority order
    payment_type_list = get_enums_from_database()
    print('payment_type_list', payment_type_list)
    fund_finished = lambda money_spend, money_in_account: money_spend == money_in_account
    payments_made, total_amount_paid, paid_count = 0, 0, 0
    target_build = Build.query.filter(Build.id == build_id).first()
    if not target_build:
        raise ValueError(f"Build with id {build_id} not found")

    now = datetime.now()
    decision_book = BuildDecisionBook.query.filter(
        BuildDecisionBook.build_id == build_id,  cast(BuildDecisionBook.expiry_starts, Date) <= now.date(), 
        cast(BuildDecisionBook.expiry_ends, Date) >= now.date(), BuildDecisionBook.decision_type == "RBM"
    ).first()
    if not decision_book:
        raise ValueError(f"Decision book not found for build with id {build_id}")

    period_date_start = decision_book.expiry_starts
    period_date_end = decision_book.expiry_ends
    period_id = decision_book.id
    
    print(dict(
        period_date_start=str(period_date_start), period_date_end=str(period_date_end), period_id=period_id
    ))

    first_date_of_process_date = find_first_day_of_month(datetime(period_date_start.year, period_date_start.month, 1))
    last_date_of_process_date = find_first_day_of_month(datetime(now.year, now.month - 1, 1))
    print('first_date_of_process_date', first_date_of_process_date)
    print('last_date_of_process_date', last_date_of_process_date)
    
    # Current month date filter
    date_query_tuple = (
        cast(BuildDecisionBookPayments.process_date, Date) >= first_date_of_process_date.date(), cast(BuildDecisionBookPayments.process_date, Date) <= last_date_of_process_date.date()
    )

    for payment_type in payment_type_list:
        query_of_payments = BuildDecisionBookPayments.query.filter(
            *date_query_tuple, BuildDecisionBookPayments.payment_types_id == payment_type.id, BuildDecisionBookPayments.account_is_debit == True
            ).order_by(BuildDecisionBookPayments.process_date.desc()).all()
        print('length of debit', len(query_of_payments))
        priority_uuids = [payment_type.uuid, *[pt.uuid for pt in payment_type_list if pt.uuid != payment_type.uuid]]
        case_order = case(*[(AccountRecords.payment_result_type_uu_id == uuid, index) for index, uuid in enumerate(priority_uuids)], else_=len(priority_uuids))
        for payment_row in query_of_payments:
            print('-'* 100)
            print('BuildDecisionBookPayments result: ',dict(
                process_date=str(payment_row.process_date), ref_id=payment_row.ref_id, payment_amount=payment_row.payment_amount, build_parts_id=payment_row.build_parts_id, 
                payment_types_id=payment_row.payment_types_id, account_is_debit=payment_row.account_is_debit
            ))
            print('-'* 100)


def do_regular_monthly_payers_payment_function(work_date: datetime, build_id: int, month_count: int = 23, year_count: int = 2):
    start_date = work_date - timedelta(days=365 * year_count)
    start_date = find_first_day_of_month(start_date)
    for i in range(month_count):
        start_date, _ = add_month_to_date(start_date)
        do_payments(build_id, work_date=start_date, run_now_bool=False)
    return True


if __name__ == "__main__":
    start_time = perf_counter()
    
    print("\n===== PROCESSING PAYMENTS =====\n")
    print("Starting payment processing at:", datetime.now())

    build_id = 1
    # Do regular monthly payers payments
    print("\n1. Processing regular monthly payers payments...")
    do_regular_monthly_payers_payment_function(work_date=datetime.now(), build_id=build_id, month_count=23, year_count=2)

    # Process payments for current month first
    print("\n2. Processing current month payments...")
    do_payments(build_id=build_id, work_date=datetime.now(), run_now_bool=True)

    # Process payments for previous months
    print("\n2. Processing previous months payments...")
    # do_regular_monthly_payers_payment_function(work_date=datetime.now(), build_id=build_id, month_count=23, year_count=2)
    
    print("\n===== PAYMENT PROCESSING COMPLETE =====\n")
    print("Payment processing completed at:", datetime.now())
    
    # Analyze the payment situation after processing payments
    print("\n===== ANALYZING PAYMENT SITUATION AFTER PROCESSING =====\n")
    # analyze_payment_function()
    
    end_time = perf_counter()
    print(f"\n{end_time - start_time:.3f} : seconds")