late_interest_rec_db_query.py

import csv
import dataclasses
import io
import sys
import time
import typing
from pprint import pprint
from typing import Iterable
from uuid import UUID
from decimal import Decimal
import datetime

from django.db.models import QuerySet

from fund_admin.common.constants import SYSTEM_USER
from fund_admin.documents.services import DocumentService
from fund_admin.fund_admin.models import Fund, Firm, FundFamily
from fund_admin.identity.cache import get_user
from fund_admin.reporting.constants import PackageStatusEnum
from fund_admin.reporting.models import FinancialPackageReport
from fund_admin.reporting.models.templates import FinancialPackageTemplateSettings, NonCashDisclosures
from fund_admin.reporting.services.financial_package.health_checks.helpers.balance_sheet import get_balance_sheet
from fund_admin.reporting.reports.balance_sheet.assets.late_interest_receivable import LateInterestReceivable
from fund_admin.reporting.reports.balance_sheet.templates import AbstractBalanceSheet
from scripts.utils import ScriptRunner


@dataclasses.dataclass
class TemplateSettingsObj:
    template_setting_ids: Iterable[UUID]
    template_settings: list[FinancialPackageTemplateSettings]
    num_templates: int
    num_packages: int




def print_section(lines: list[str]):
    print()
    print("==========================")
    for line in lines:
        if not line:
            print()
        else:
            print(line)
    print("==========================")
    print()


def get_template_settings() -> TemplateSettingsObj:
    packages = FinancialPackageReport.objects.filter(status=PackageStatusEnum.DRAFT)

    # Using a set to de-dupe list of template settings
    template_setting_ids = {package.template_settings_id for package in packages}
    template_settings = FinancialPackageTemplateSettings.objects.filter(id__in=template_setting_ids)

    return TemplateSettingsObj(
        template_setting_ids=list(template_setting_ids),
        template_settings=template_settings,
        num_templates=len(template_setting_ids),
        num_packages=len(packages),
    )


def get_template_settings_hiding_line_item(template_settings: list[FinancialPackageTemplateSettings], ncd_id: str):
    with_line_item = []
    without_line_item = []

    for ts in template_settings:
        ids = [ts.id for ts in ts.non_cash_disclosures_to_hide.all()]

        if ncd_id in ids:
            with_line_item.append(ts)
        else:
            without_line_item.append(ts)

    return with_line_item, without_line_item


def get_line_item_value(fund_id: int, end_date: datetime.date):
    bs = get_balance_sheet(fund_id=fund_id, end_date=end_date)
    qtr = bs.get_quarter(end_date)
    line_item_value = (
        qtr.get_line_item(
            section=AbstractBalanceSheet().assets_label,
            line_item=LateInterestReceivable.name
        )
        if qtr
        else Decimal(0)
    )
    return line_item_value


def packages_with_data(limit_packages: typing.Optional[int] = None) -> list[tuple[FinancialPackageReport, Decimal]]:
    start_time = time.perf_counter()

    # Store variables to return
    has_value = []
    no_value = []

    # NCD that we're planning on removing
    ncd = NonCashDisclosures.objects.get(type="LATE_INTEREST_RECEIVABLE")

    # Grab the unique list of template settings used among draft packages
    template_settings_obj = get_template_settings()
    (with_line_item, without_line_item) = get_template_settings_hiding_line_item(
        template_settings_obj.template_settings, ncd.id)

    # Find all draft packages using one of these template settings
    ts_ids = [ts.id for ts in without_line_item]
    packages: QuerySet[FinancialPackageReport] = FinancialPackageReport.objects.filter(template_settings_id__in=ts_ids, status=PackageStatusEnum.DRAFT)[:limit_packages]

    print_section([
        f"There are currently {template_settings_obj.num_packages} packages in DRAFT status using {template_settings_obj.num_templates} unique Template Settings.",
        f"{len(with_line_item)} template settings DO have the LATE_INTEREST_RECEIVABLE line item hidden",
        f"{len(without_line_item)} DO NOT have it hidden",
        None,
        f"There are {len(packages)}" if not limit_packages else f"These are the first {limit_packages} draft packages currently using one of the "
        + f"{len(with_line_item)} Template Settings that have hidden this line item"
    ])

    try:
        # TODO: Don't forget to remove this slice
        for idx, pkg in enumerate(packages):
            pkg_idx = idx + 1
            num_packages = len(packages)
            pct_complete = (pkg_idx / num_packages) * 100

            one = "\\"
            two = "/"
            print(f"{round(pct_complete, 2)}% Complete {one if pkg_idx % 2 == 0 else two} Checking package {pkg.id}")

            line_item_value = get_line_item_value(fund_id=pkg.fund_id if pkg.fund_id else pkg.fund_family_id,
                                                  end_date=pkg.end_date)
            if line_item_value > 0:
                has_value.append((pkg, line_item_value))
            else:
                no_value.append((pkg, Decimal(0)))
    except:
        print("¡¡Exception!!")
        print(repr(sys.exception()))

    end_time = time.perf_counter()

    print_section([
        f"Completed iterating over {len(packages)} packages in {end_time - start_time:0.4f}s",
        f"{len(has_value)} packages have a value for the line item LATE_INTEREST_RECEIVABLE",
        f"{len(no_value)} packages do not"
    ])

    return has_value


def display_package_data(limit_packages: typing.Optional[int] = None):
    packages = packages_with_data(limit_packages)

    for package, line_item_value in packages:
        template_settings = FinancialPackageTemplateSettings.objects.get(id=package.template_settings_id)
        # reviewer_names = ", ".join([reviewer.name for reviewer in package.reviewers.all()])

        has_fund_family = package.fund_family_id is not None and package.fund_id is None
        fund = None
        fund_family = None

        if has_fund_family:
            fund_family = FundFamily.objects.get(id=package.fund_family_id)
        else:
            fund = Fund.objects.get(id=package.fund_id)

        firm_id = fund.firm_id if fund else fund_family.firm_id
        firm = Firm.objects.get(id=firm_id)

        # This doesn't work in Jupyter. Only enable when running in Django shell or via Gist
        preparer_user = None
        if not is_jupyter:
            preparer_user = get_user(package.preparer.user_id)

        package_url = f"https://app.sandbox.carta.team/investors/firm/{firm.carta_id}/portfolio/{'fund' if fund else 'family'}/{fund.carta_id if fund else fund_family.carta_id}/fund-accounting/financial-reporting/{package.id}/"

        package_data_to_print = [
            f"Package ID: {package.id}",
            f"Template Settings ID: {template_settings.id}",
            f"Template Settings Label: {template_settings.label}",
            f"Start Date: {package.start_date}",
            f"End Date: {package.end_date}",
            "--------------------------",
            f"Firm Id: {firm.id}",
            f"Firm Carta Id: {firm.carta_id}",
            f"Firm Name: {firm.name}",
            "--------------------------",
        ]

        if has_fund_family:
            package_data_to_print.append(f"Fund Family ID: {fund_family.id}")
            package_data_to_print.append(f"Fund Family Name: {fund_family.name}")
        else:
            package_data_to_print.append(f"Fund ID: {fund.id}")
            package_data_to_print.append(f"Fund Carta Id: {fund.carta_id}")
            package_data_to_print.append(f"Fund Name: {fund.name}")

        package_data_to_print.append("--------------------------")

        if preparer_user:
            package_data_to_print.append(f"Preparer: {preparer_user.first_name} {preparer_user.last_name}")
            package_data_to_print.append(f"Preparer Username: {preparer_user.username}")
            package_data_to_print.append(f"Preparer Email: {preparer_user.email}")
            package_data_to_print.append(f"Preparer Is Staff: {preparer_user.is_staff}")
        else:
            package_data_to_print.append(f"Preparer: {package.preparer.name} (User ID: {package.preparer.user_id})")
        package_data_to_print.append("--------------------------")
        package_data_to_print.append(f"LATE_INTEREST_RECEIVABLE Value: {line_item_value}")
        package_data_to_print.append(f"Package URL: {package_url}")

        print_section(package_data_to_print)


is_jupyter = False
display_package_data()