class FinancialReport:


    def get_column_type(field_name: str) -> str:
        if field_name in GeneralLedger.__table__.columns:
            return "column"
        else:
            return "jsonb"

    def get_field(entry: any, field_name: str) -> any:
        column_type = get_column_type(field_name)
        if column_type == "column":
            return getattr(entry, field_name, None)
        elif column_type == "jsonb":
            if entry.extra_data:
                return entry.extra_data.get(field_name) or entry.extra_data.get(field_name.lower())
        return None
        
    @staticmethod
    def get_yearly_date_range(year: int):
        start = datetime(year, 1, 1)
        end = datetime(year, 12, 31, 23, 59, 59, 999999)
        return start, end

    @staticmethod
    def get_quarterly_date_range(year: int, today: datetime):
        # Determine current quarter based on today's month
        quarter = (today.month - 1) // 3 + 1
        start_month = 3 * (quarter - 1) + 1
        start = datetime(year, start_month, 1)
        if quarter == 4:
            end = datetime(year, 12, 31, 23, 59, 59, 999999)
        else:
            # Start of next quarter minus 1 microsecond
            next_quarter_start = datetime(year, start_month + 3, 1)
            end = next_quarter_start - timedelta(microseconds=1)
        return start, end

    @staticmethod
    async def get_account_mapping(db: AsyncSession) -> Dict[str, Tuple[str, str]]:
        logger.debug("Loading account mapping from database")
        stmt = select(
            GeneralLedgerMaster.account_name,
            GeneralLedgerMaster.category,
            GeneralLedgerMaster.tags
        )
        res = await db.execute(stmt)
        rows = res.all()

        mapping = {}
        for account_name, category, tags in rows:
            if not account_name:
                logger.warning(f"Skipping mapping row with missing account_name: {(account_name, category, tags)}")
                continue
            key = account_name.strip().lower()
            cat = (category or "").strip().lower()
            tg = ",".join([t.strip().lower() for t in tags if t]) if tags else ""
            mapping[key] = (cat, tg)
        logger.debug(f"Loaded {len(mapping)} account mappings")
        return mapping

    @staticmethod
    def build_filters(
        start: Optional[datetime], 
        end: Optional[datetime], 
        entity: Optional[str] = None, 
        hierarchy: Optional[str] = None
    ):
        filters = []
        if start:
            filters.append(GeneralLedger.date >= start)
        if end:
            filters.append(GeneralLedger.date <= end)

        for field_name, value in [("entity", entity), ("hierarchy", hierarchy)]:
            if value:
                column_type = get_column_type(field_name)
                val_lower = value.lower()
                if column_type == "column":
                    filters.append(func.lower(getattr(GeneralLedger, field_name)) == val_lower)
                elif column_type == "jsonb":
                    filters.append(func.lower(func.jsonb_extract_path_text(GeneralLedger.extra_data, field_name)) == val_lower)

        return and_(*filters) if filters else text("TRUE")

    @staticmethod
    async def get_balance_sheet_mapping(db: AsyncSession) -> Dict[str, Tuple[str, str]]:
        """Get mapping specifically for balance sheet accounts (assets, liabilities, equity)"""
        logger.debug("Loading BALANCE SHEET account mapping from database")
        
        stmt = select(
            GeneralLedgerMaster.account_name,
            GeneralLedgerMaster.category,
            GeneralLedgerMaster.tags
        ).where(
            GeneralLedgerMaster.category.in_(["asset", "liability", "equity"])
        )
        
        res = await db.execute(stmt)
        rows = res.all()

        mapping = {}
        for account_name, category, tags in rows:
            if not account_name:
                logger.warning(f"Skipping mapping row with missing account_name: {(account_name, category, tags)}")
                continue
            key = account_name.strip().lower()
            cat = (category or "").strip().lower()
            tg = ",".join([t.strip().lower() for t in tags if t]) if tags else ""
            mapping[key] = (cat, tg)
        
        logger.debug(f"Loaded {len(mapping)} balance sheet account mappings")
        return mapping

    @staticmethod
    async def balance_sheet(
        db: AsyncSession,
        period: str = "yearly",
        year: Optional[int] = None,
        custom_start: Optional[datetime] = None,
        custom_end: Optional[datetime] = None,
        entity: Optional[str] = None,
        hierarchy: Optional[str] = None
    ) -> Dict[str, any]:
        def to_naive(dt: datetime) -> datetime:
            if dt is None:
                return None
            return dt.replace(tzinfo=None) if dt.tzinfo else dt

        today = datetime.utcnow()
        year = year or today.year

        # Calculate date ranges based on period
        if period == "yearly":
            cur_s = datetime(year, 1, 1)
            cur_e = datetime(year, 12, 31, 23, 59, 59, 999999)
            prev_s = datetime(year - 1, 1, 1)
            prev_e = datetime(year - 1, 12, 31, 23, 59, 59, 999999)
        elif period == "quarterly":
            current_quarter = (today.month - 1) // 3 + 1
            start_month = 3 * (current_quarter - 1) + 1
            end_month = start_month + 2
            cur_s = datetime(year, start_month, 1)
            if end_month < 12:
                cur_e = datetime(year, end_month + 1, 1) - timedelta(days=1)
                cur_e = cur_e.replace(hour=23, minute=59, second=59, microsecond=999999)
            else:
                cur_e = datetime(year, 12, 31, 23, 59, 59, 999999)
            prev_s = datetime(year - 1, start_month, 1)
            if end_month < 12:
                prev_e = datetime(year - 1, end_month + 1, 1) - timedelta(days=1)
                prev_e = prev_e.replace(hour=23, minute=59, second=59, microsecond=999999)
            else:
                prev_e = datetime(year - 1, 12, 31, 23, 59, 59, 999999)
        elif period == "custom":
            if not custom_start or not custom_end:
                raise ValueError("For custom period, both custom_start and custom_end must be provided.")
            cur_s, cur_e = custom_start, custom_end
            prev_s = prev_e = None
        else:
            raise ValueError(f"Invalid period type: {period}. Must be 'yearly', 'quarterly', or 'custom'.")

        # Make all dates naive to match DB timestamp without time zone
        cur_s, cur_e = to_naive(cur_s), to_naive(cur_e)
        prev_s, prev_e = to_naive(prev_s), to_naive(prev_e)

        # Load BALANCE SHEET account mapping only
        mapping = await FinancialReport.get_balance_sheet_mapping(db)

        # Build filters and query current period
        current_filters = FinancialReport.build_filters(cur_s, cur_e, entity, hierarchy)
        current_entries = (await db.execute(select(GeneralLedger).where(current_filters))).scalars().all()

        # Query previous period if available
        previous_entries = []
        if prev_s and prev_e:
            previous_filters = FinancialReport.build_filters(prev_s, prev_e, entity, hierarchy)
            previous_entries = (await db.execute(select(GeneralLedger).where(previous_filters))).scalars().all()

        balances = defaultdict(lambda: {"current": Decimal("0.0"), "previous": Decimal("0.0")})
        totals = defaultdict(Decimal)

        def process_entries(entries, period_label):
            for entry in entries:
                acct = (entry.account_name or "").strip().lower()
                if not acct or acct not in mapping:
                    continue

                category, tags = mapping[acct]
                tag_set = set(t.strip().lower() for t in tags.split(",") if t)

                # CORRECTED AMOUNT CALCULATION:
                if category == "asset":
                    # Assets: Debit minus Credit (normal debit balance = positive)
                    amount = Decimal(entry.debit or 0) - Decimal(entry.credit or 0)
                else:
                    # Liabilities and Equity: Credit minus Debit (normal credit balance = positive)
                    amount = Decimal(entry.credit or 0) - Decimal(entry.debit or 0)

                balances[(acct, category)][period_label] += amount

                if category == "asset":
                    totals[f"{period_label}_assets"] += amount
                    if "current asset" in tag_set:
                        totals[f"{period_label}_current_assets"] += amount
                elif category == "liability":
                    totals[f"{period_label}_liabilities"] += amount  # This will now be positive
                    if "current liability" in tag_set:
                        totals[f"{period_label}_current_liabilities"] += amount
                elif category == "equity":
                    totals[f"{period_label}_equity"] += amount  # This will now be positive

        process_entries(current_entries, "current")
        if previous_entries:
            process_entries(previous_entries, "previous")

        report = []
        for (acct, cat), vals in balances.items():
            current = vals["current"]
            previous = vals["previous"]
            change = current - previous
            pct_change = (change / previous * 100) if previous != 0 else Decimal("0.0")

            report.append({
                "account": acct,
                "category": cat,
                "current": float(round(current, 2)),
                "previous": float(round(previous, 2)),
                "change": float(round(change, 2)),
                "change_percentage": float(round(pct_change, 2))
            })

        def format_pct_change(current_val, prev_val):
            if prev_val == 0:
                return "N/A"
            return f"{round(((current_val - prev_val) / prev_val) * 100, 1)}%"

        current_assets = totals.get("current_current_assets", Decimal("0.0"))
        current_liabilities = totals.get("current_current_liabilities", Decimal("0.0"))

        total_assets = totals.get("current_assets", Decimal("0.0"))
        total_liabilities = totals.get("current_liabilities", Decimal("0.0"))
        total_equity = totals.get("current_equity", Decimal("0.0"))

        prev_assets = totals.get("previous_assets", Decimal("0.0"))
        prev_liabilities = totals.get("previous_liabilities", Decimal("0.0"))
        prev_equity = totals.get("previous_equity", Decimal("0.0"))

        current_ratio = (current_assets / current_liabilities) if current_liabilities else Decimal("0.0")
        debt_to_equity = (total_liabilities / total_equity) if total_equity else Decimal("0.0")
        working_capital = current_assets - current_liabilities

        quick_assets = Decimal("0.0")
        for (acct, cat), vals in balances.items():
            tags = mapping.get(acct, ("", ""))[1]
            tag_set = set(t.strip().lower() for t in tags.split(",") if t)
            if cat == "asset" and "current asset" in tag_set and ("cash" in tag_set or "receivable" in tag_set):
                quick_assets += vals["current"]  # This should be positive for assets
        quick_ratio = (quick_assets / current_liabilities) if current_liabilities else Decimal("0.0")

        return {
            "report": report,
            "period_info": {
                "type": period,
                "year": year,
                "start_date": cur_s,
                "end_date": cur_e,
                "previous_start": prev_s,
                "previous_end": prev_e
            },
            "key_metrics": {
                "Total_Assets": {
                    "current": float(round(total_assets, 2)),
                    "previous": float(round(prev_assets, 2)) if prev_s else None,
                    "change_pct": format_pct_change(total_assets, prev_assets) if prev_s else "N/A"
                },
                "Total_Liabilities": {
                    "current": float(round(total_liabilities, 2)),
                    "previous": float(round(prev_liabilities, 2)) if prev_s else None,
                    "change_pct": format_pct_change(total_liabilities, prev_liabilities) if prev_s else "N/A"
                },
                "Total_Equity": {
                    "current": float(round(total_equity, 2)),
                    "previous": float(round(prev_equity, 2)) if prev_s else None,
                    "change_pct": format_pct_change(total_equity, prev_equity) if prev_s else "N/A"
                },
                "Current_Ratio": float(round(current_ratio, 2)),
                "Quick_Ratio": float(round(quick_ratio, 2)),
                "Debt_to_Equity": float(round(debt_to_equity, 2)),
                "Working_Capital": float(round(working_capital, 2))
            }
    } # @staticmethod
    # def generate_monthly_report(rows, mapping):
    #     from collections import defaultdict
    #     import calendar

    #     monthly_data = defaultdict(lambda: defaultdict(Decimal))
    #     monthly_totals = defaultdict(lambda: defaultdict(Decimal))

    #     for row in rows:
    #         acct = (row.account_name or "").strip().lower()
    #         if not acct or acct not in mapping:
    #             continue

    #         category, tags = mapping[acct]
    #         tag_set = set(t.strip().lower() for t in tags.split(",") if t)

    #         amount = Decimal(row.debit or 0) - Decimal(row.credit or 0)
    #         month = int(row.month)

    #         monthly_data[month][acct] += amount

    #         if category == "asset":
    #             monthly_totals[month]["assets"] += amount
    #             if "current asset" in tag_set:
    #                 monthly_totals[month]["current_assets"] += amount
    #         elif category == "liability":
    #             monthly_totals[month]["liabilities"] += amount
    #             if "current liability" in tag_set:
    #                 monthly_totals[month]["current_liabilities"] += amount
    #         elif category == "equity":
    #             monthly_totals[month]["equity"] += amount

    #     reports = []
    #     for month in range(1, 13):
    #         accounts = monthly_data.get(month, {})
    #         report = []
    #         for acct, val in accounts.items():
    #             report.append({
    #                 "account": acct,
    #                 "category": mapping[acct][0],
    #                 "amount": float(round(val, 2))
    #             })

    #         totals = monthly_totals.get(month, {})
    #         month_name = calendar.month_name[month]
    #         reports.append({
    #             "month": month_name,
    #             "report": report,
    #             "totals": {k: float(round(v, 2)) for k, v in totals.items()}
    #         })

    #     return reports, monthly_totals, monthly_data

    # @staticmethod
    # def generate_quarterly_report(rows, mapping):
    #     from collections import defaultdict

    #     quarterly_data = defaultdict(lambda: defaultdict(Decimal))
    #     quarterly_totals = defaultdict(lambda: defaultdict(Decimal))

    #     for row in rows:
    #         acct = (row.account_name or "").strip().lower()
    #         if not acct or acct not in mapping:
    #             continue

    #         category, tags = mapping[acct]
    #         tag_set = set(t.strip().lower() for t in tags.split(",") if t)

    #         amount = Decimal(row.debit or 0) - Decimal(row.credit or 0)
    #         quarter = f"Q{int(row.quarter)}"

    #         quarterly_data[quarter][acct] += amount

    #         if category == "asset":
    #             quarterly_totals[quarter]["assets"] += amount
    #             if "current asset" in tag_set:
    #                 quarterly_totals[quarter]["current_assets"] += amount
    #         elif category == "liability":
    #             quarterly_totals[quarter]["liabilities"] += amount
    #             if "current liability" in tag_set:
    #                 quarterly_totals[quarter]["current_liabilities"] += amount
    #         elif category == "equity":
    #             quarterly_totals[quarter]["equity"] += amount

    #     reports = []
    #     for quarter in sorted(quarterly_data):
    #         accounts = quarterly_data[quarter]
    #         report = [{
    #             "account": acct,
    #             "category": mapping[acct][0],
    #             "amount": float(round(val, 2))
    #         } for acct, val in accounts.items()]
    #         totals = quarterly_totals[quarter]
    #         reports.append({
    #             "quarter": quarter,
    #             "report": report,
    #             "totals": {k: float(round(v, 2)) for k, v in totals.items()}
    #         })

    #     return reports, quarterly_totals, quarterly_data

    # @staticmethod
    # async def balance_sheet(
    #         db: AsyncSession,
    #         period: str = "yearly",
    #         year: Optional[int] = None,
    #         custom_start: Optional[datetime] = None,
    #         custom_end: Optional[datetime] = None,
    #         entity: Optional[str] = None,
    #         hierarchy: Optional[str] = None
    #     ) -> Dict[str, any]:
    #         def to_naive(dt: datetime) -> datetime:
    #             if dt is None:
    #                 return None
    #             return dt.replace(tzinfo=None) if dt.tzinfo else dt

    #         today = datetime.utcnow()
    #         year = year or today.year

    #         # Calculate date ranges based on period
    #         if period == "yearly":
    #             cur_s = datetime(year, 1, 1)
    #             cur_e = datetime(year, 12, 31, 23, 59, 59, 999999)
    #             prev_s = datetime(year - 1, 1, 1)
    #             prev_e = datetime(year - 1, 12, 31, 23, 59, 59, 999999)
    #         elif period == "quarterly":
    #             current_quarter = (today.month - 1) // 3 + 1
    #             start_month = 3 * (current_quarter - 1) + 1
    #             end_month = start_month + 2
    #             cur_s = datetime(year, start_month, 1)
    #             if end_month < 12:
    #                 cur_e = datetime(year, end_month + 1, 1) - timedelta(days=1)
    #                 cur_e = cur_e.replace(hour=23, minute=59, second=59, microsecond=999999)
    #             else:
    #                 cur_e = datetime(year, 12, 31, 23, 59, 59, 999999)
    #             prev_s = datetime(year - 1, start_month, 1)
    #             if end_month < 12:
    #                 prev_e = datetime(year - 1, end_month + 1, 1) - timedelta(days=1)
    #                 prev_e = prev_e.replace(hour=23, minute=59, second=59, microsecond=999999)
    #             else:
    #                 prev_e = datetime(year - 1, 12, 31, 23, 59, 59, 999999)
    #         elif period == "custom":
    #             if not custom_start or not custom_end:
    #                 raise ValueError("For custom period, both custom_start and custom_end must be provided.")
    #             cur_s, cur_e = custom_start, custom_end
    #             prev_s = prev_e = None
    #         else:
    #             raise ValueError(f"Invalid period type: {period}. Must be 'yearly', 'quarterly', or 'custom'.")

    #         # Make all dates naive to match DB timestamp without time zone
    #         cur_s, cur_e = to_naive(cur_s), to_naive(cur_e)
    #         prev_s, prev_e = to_naive(prev_s), to_naive(prev_e)

    #         # Load account mapping
    #         mapping = await FinancialReport.get_account_mapping(db)

    #         # Build filters and query current period
    #         current_filters = FinancialReport.build_filters(cur_s, cur_e, entity, hierarchy)
    #         current_entries = (await db.execute(select(GeneralLedger).where(current_filters))).scalars().all()

    #         # Query previous period if available
    #         previous_entries = []
    #         if prev_s and prev_e:
    #             previous_filters = FinancialReport.build_filters(prev_s, prev_e, entity, hierarchy)
    #             previous_entries = (await db.execute(select(GeneralLedger).where(previous_filters))).scalars().all()

    #         balances = defaultdict(lambda: {"current": Decimal("0.0"), "previous": Decimal("0.0")})
    #         totals = defaultdict(Decimal)

    #         def process_entries(entries, period_label):
    #             for entry in entries:
    #                 acct = (entry.account_name or "").strip().lower()
    #                 if not acct or acct not in mapping:
    #                     continue

    #                 category, tags = mapping[acct]
    #                 tag_set = set(t.strip().lower() for t in tags.split(",") if t)

    #                 amount = Decimal(entry.debit or 0) - Decimal(entry.credit or 0)
    #                 balances[(acct, category)][period_label] += amount

    #                 if category == "asset":
    #                     totals[f"{period_label}_assets"] += amount
    #                     if "current asset" in tag_set:
    #                         totals[f"{period_label}_current_assets"] += amount
    #                 elif category == "liability":
    #                     totals[f"{period_label}_liabilities"] += amount
    #                     if "current liability" in tag_set:
    #                         totals[f"{period_label}_current_liabilities"] += amount
    #                 elif category == "equity":
    #                     totals[f"{period_label}_equity"] += amount

    #         process_entries(current_entries, "current")
    #         if previous_entries:
    #             process_entries(previous_entries, "previous")

    #         report = []
    #         for (acct, cat), vals in balances.items():
    #             current = vals["current"]
    #             previous = vals["previous"]
    #             change = current - previous
    #             pct_change = (change / previous * 100) if previous != 0 else Decimal("0.0")

    #             report.append({
    #                 "account": acct,
    #                 "category": cat,
    #                 "current": float(round(current, 2)),
    #                 "previous": float(round(previous, 2)),
    #                 "change": float(round(change, 2)),
    #                 "change_percentage": float(round(pct_change, 2))
    #             })

    #         def format_pct_change(current_val, prev_val):
    #             if prev_val == 0:
    #                 return "N/A"
    #             return f"{round(((current_val - prev_val) / prev_val) * 100, 1)}%"

    #         current_assets = totals.get("current_current_assets", Decimal("0.0"))
    #         current_liabilities = totals.get("current_current_liabilities", Decimal("0.0"))

    #         total_assets = totals.get("current_assets", Decimal("0.0"))
    #         total_liabilities = totals.get("current_liabilities", Decimal("0.0"))
    #         total_equity = totals.get("current_equity", Decimal("0.0"))

    #         prev_assets = totals.get("previous_assets", Decimal("0.0"))
    #         prev_liabilities = totals.get("previous_liabilities", Decimal("0.0"))
    #         prev_equity = totals.get("previous_equity", Decimal("0.0"))

    #         current_ratio = (current_assets / current_liabilities) if current_liabilities else Decimal("0.0")
    #         debt_to_equity = (total_liabilities / total_equity) if total_equity else Decimal("0.0")
    #         working_capital = current_assets - current_liabilities

    #         quick_assets = Decimal("0.0")
    #         for (acct, cat), vals in balances.items():
    #             tags = mapping.get(acct, ("", ""))[1]
    #             tag_set = set(t.strip().lower() for t in tags.split(",") if t)
    #             if cat == "asset" and "current asset" in tag_set and ("cash" in tag_set or "receivable" in tag_set):
    #                 quick_assets += vals["current"]
    #         quick_ratio = (quick_assets / current_liabilities) if current_liabilities else Decimal("0.0")

    #         return {
    #             "report": report,
    #             "period_info": {
    #                 "type": period,
    #                 "year": year,
    #                 "start_date": cur_s,
    #                 "end_date": cur_e,
    #                 "previous_start": prev_s,
    #                 "previous_end": prev_e
    #             },
    #             "key_metrics": {
    #                 "Total_Assets": {
    #                     "current": float(round(total_assets, 2)),
    #                     "previous": float(round(prev_assets, 2)) if prev_s else None,
    #                     "change_pct": format_pct_change(total_assets, prev_assets) if prev_s else "N/A"
    #                 },
    #                 "Total_Liabilities": {
    #                     "current": float(round(total_liabilities, 2)),
    #                     "previous": float(round(prev_liabilities, 2)) if prev_s else None,
    #                     "change_pct": format_pct_change(total_liabilities, prev_liabilities) if prev_s else "N/A"
    #                 },
    #                 "Total_Equity": {
    #                     "current": float(round(total_equity, 2)),
    #                     "previous": float(round(prev_equity, 2)) if prev_s else None,
    #                     "change_pct": format_pct_change(total_equity, prev_equity) if prev_s else "N/A"
    #                 },
    #                 "Current_Ratio": float(round(current_ratio, 2)),
    #                 "Quick_Ratio": float(round(quick_ratio, 2)),
    #                 "Debt_to_Equity": float(round(debt_to_equity, 2)),
    #                 "Working_Capital": float(round(working_capital, 2))
    #             }
    #         }

    @staticmethod
    async def get_fixed_asset_register(
        db: AsyncSession,
        period: str = "yearly",
        year: Optional[int] = None,
        custom_start: Optional[datetime] = None,
        custom_end: Optional[datetime] = None,
        entity: Optional[str] = None,
        hierarchy: Optional[str] = None
    ) -> Dict[str, Any]:
        def to_naive(dt: Optional[datetime]) -> Optional[datetime]:
            """Convert datetime to naive if it has timezone"""
            if dt is None:
                return None
            return dt.replace(tzinfo=None) if dt.tzinfo else dt

        def calculate_depreciation(cost: Decimal, salvage: Decimal, life: Decimal, purchase_date: datetime) -> Tuple[Decimal, Decimal, Decimal]:
            """Calculate depreciation metrics"""
            if life == 0:
                return Decimal("0.0"), Decimal("0.0"), cost
            
            age_days = (today - purchase_date).days if purchase_date else 0
            age_years = Decimal(age_days) / Decimal("365.25")
            age_used = min(age_years, life)
            
            depr = age_used * ((cost - salvage) / life)
            nbv = max(cost - depr, salvage)
            return depr, nbv, age_years

        logger.debug("Generating fixed asset register")

        today = datetime.utcnow()
        year = year or today.year

        # Calculate date ranges based on period
        if period == "yearly":
            start_date, end_date = FinancialReport.get_yearly_date_range(year)
            prev_start, prev_end = FinancialReport.get_yearly_date_range(year - 1)
        elif period == "quarterly":
            start_date, end_date = FinancialReport.get_quarterly_date_range(year, today)
            prev_start, prev_end = FinancialReport.get_quarterly_date_range(year - 1, today)
        elif period == "custom":
            if not custom_start or not custom_end:
                raise ValueError("For custom period, both custom_start and custom_end must be provided")
            start_date, end_date = custom_start, custom_end
            prev_start = prev_end = None
        elif period == "all":
            start_date = end_date = prev_start = prev_end = None
        else:
            raise ValueError(f"Invalid period: {period}. Must be 'yearly', 'quarterly', 'custom', or 'all'")

        # Convert to naive datetimes
        start_date, end_date = to_naive(start_date), to_naive(end_date)
        prev_start, prev_end = to_naive(prev_start), to_naive(prev_end)

        # Build filters
        current_filters = FinancialReport.build_filters(start_date, end_date, entity, hierarchy)
        current_entries = (await db.execute(select(GeneralLedger).where(current_filters))).scalars().all()

        # Load previous period if available
        previous_entries = []
        if prev_start and prev_end:
            previous_filters = FinancialReport.build_filters(prev_start, prev_end, entity, hierarchy)
            previous_entries = (await db.execute(select(GeneralLedger).where(previous_filters))).scalars().all()

        # Load account mapping
        mapping = await FinancialReport.get_account_mapping(db)
        logger.debug(f"Loaded {len(mapping)} account mappings")

        # Process current and previous period assets
        assets = {
            "current": defaultdict(lambda: {
                "cost": Decimal("0.0"),
                "purchase": None,
                "life": Decimal("5"),  # Default 5 years
                "salvage": Decimal("0.0")
            }),
            "previous": defaultdict(lambda: {
                "cost": Decimal("0.0"),
                "purchase": None,
                "life": Decimal("5"),
                "salvage": Decimal("0.0")
            })
        }

        for period_type, entries in [("current", current_entries), ("previous", previous_entries)]:
            if not entries:
                continue
                
            for entry in entries:
                acct_name = (entry.account_name or "").strip().lower()
                if not acct_name:
                    continue

                cat_tag = mapping.get(acct_name)
                if not cat_tag:
                    continue

                category, tags = cat_tag
                if category != "asset":
                    continue

                tags_lower = tags.lower() if tags else ""
                if "fixed asset" not in tags_lower and "tangible" not in tags_lower:
                    continue

                amount = Decimal(entry.debit or 0)
                asset = assets[period_type][acct_name]
                
                asset["cost"] += amount
                asset["life"] = Decimal(getattr(entry, "useful_life", 5))
                asset["salvage"] = Decimal(getattr(entry, "salvage_value", "0.0"))
                
                # Track earliest purchase date
                if entry.date and (not asset["purchase"] or entry.date < asset["purchase"]):
                    asset["purchase"] = entry.date

        # Generate asset list with depreciation calculations
        asset_list = []
        totals = {
            "current": {
                "cost": Decimal("0.0"),
                "depr": Decimal("0.0"),
                "nbv": Decimal("0.0"),
                "age": Decimal("0.0")
            },
            "previous": {
                "cost": Decimal("0.0"),
                "depr": Decimal("0.0"),
                "nbv": Decimal("0.0"),
                "age": Decimal("0.0")
            }
        }

        all_assets = set(assets["current"].keys()).union(set(assets["previous"].keys()))
        
        for asset_name in all_assets:
            current = assets["current"].get(asset_name)
            previous = assets["previous"].get(asset_name)
            
            if not current and not previous:
                continue
                
            # Calculate current period values
            current_cost = current["cost"] if current else Decimal("0.0")
            current_depr, current_nbv, current_age = calculate_depreciation(
                current_cost,
                current["salvage"] if current else Decimal("0.0"),
                current["life"] if current else Decimal("5"),
                current["purchase"] if current else today
            ) if current else (Decimal("0.0"), Decimal("0.0"), Decimal("0.0"))
            
            # Calculate previous period values if available
            prev_cost = previous["cost"] if previous else Decimal("0.0")
            prev_depr, prev_nbv, prev_age = calculate_depreciation(
                prev_cost,
                previous["salvage"] if previous else Decimal("0.0"),
                previous["life"] if previous else Decimal("5"),
                previous["purchase"] if previous else today
            ) if previous else (Decimal("0.0"), Decimal("0.0"), Decimal("0.0"))

            asset_list.append({
                "asset": asset_name,
                "current": {
                    "cost": float(round(current_cost, 2)),
                    "accum_depr": float(round(current_depr, 2)),
                    "net_book": float(round(current_nbv, 2)),
                    "age_years": float(round(current_age, 1))
                },
                "previous": {
                    "cost": float(round(prev_cost, 2)) if previous else None,
                    "accum_depr": float(round(prev_depr, 2)) if previous else None,
                    "net_book": float(round(prev_nbv, 2)) if previous else None,
                    "age_years": float(round(prev_age, 1)) if previous else None
                },
                "change": {
                    "cost": float(round(current_cost - prev_cost, 2)) if previous else None,
                    "depr": float(round(current_depr - prev_depr, 2)) if previous else None,
                    "nbv": float(round(current_nbv - prev_nbv, 2)) if previous else None
                }
            })

            # Update totals
            totals["current"]["cost"] += current_cost
            totals["current"]["depr"] += current_depr
            totals["current"]["nbv"] += current_nbv
            totals["current"]["age"] += current_age
            
            if previous:
                totals["previous"]["cost"] += prev_cost
                totals["previous"]["depr"] += prev_depr
                totals["previous"]["nbv"] += prev_nbv
                totals["previous"]["age"] += prev_age

        # Calculate averages
        asset_count = len(asset_list)
        avg_age_current = float(totals["current"]["age"] / asset_count) if asset_count else 0.0
        avg_age_previous = float(totals["previous"]["age"] / asset_count) if asset_count and prev_start else None

        logger.debug(f"Fixed asset register generated with {asset_count} assets")

        return {
            "assets": asset_list,
            "period_info": {
                "type": period,
                "year": year,
                "start_date": start_date,
                "end_date": end_date,
                "previous_start": prev_start,
                "previous_end": prev_end
            },
            "key_metrics": {
                "Total_Cost": {
                    "current": float(round(totals["current"]["cost"], 2)),
                    "previous": float(round(totals["previous"]["cost"], 2)) if prev_start else None,
                    "change": float(round(totals["current"]["cost"] - totals["previous"]["cost"], 2)) if prev_start else None,
                    "change_pct": f"{((totals['current']['cost'] - totals['previous']['cost']) / totals['previous']['cost'] * 100):.1f}%" if prev_start and totals['previous']['cost'] else "N/A"
                },
                "Total_Depreciation": {
                    "current": float(round(totals["current"]["depr"], 2)),
                    "previous": float(round(totals["previous"]["depr"], 2)) if prev_start else None,
                    "change": float(round(totals["current"]["depr"] - totals["previous"]["depr"], 2)) if prev_start else None
                },
                "Net_Book_Value": {
                    "current": float(round(totals["current"]["nbv"], 2)),
                    "previous": float(round(totals["previous"]["nbv"], 2)) if prev_start else None,
                    "change": float(round(totals["current"]["nbv"] - totals["previous"]["nbv"], 2)) if prev_start else None
                },
                "Average_Age": {
                    "current": round(avg_age_current, 1),
                    "previous": round(avg_age_previous, 1) if prev_start else None,
                    "change": round(avg_age_current - avg_age_previous, 1) if prev_start else None
                }
            }
        }
    
        
    
    @staticmethod
    def get_date_range(period: str, reference_date: datetime, custom_start: Optional[datetime], custom_end: Optional[datetime]):
        from datetime import datetime

        if period == "yearly":
            year = reference_date.year
            return datetime(year, 1, 1), datetime(year, 12, 31)

        elif period == "quarterly":
            month = reference_date.month
            year = reference_date.year
            if 1 <= month <= 3:
                return datetime(year, 1, 1), datetime(year, 3, 31)
            elif 4 <= month <= 6:
                return datetime(year, 4, 1), datetime(year, 6, 30)
            elif 7 <= month <= 9:
                return datetime(year, 7, 1), datetime(year, 9, 30)
            else:
                return datetime(year, 10, 1), datetime(year, 12, 31)

        elif period == "custom":
            if not custom_start or not custom_end:
                raise ValueError("Custom period requires both start and end dates.")
            return custom_start, custom_end

        else:
            raise ValueError(f"Invalid period: {period}. Must be 'yearly', 'quarterly', or 'custom'")


    @staticmethod
    async def get_product_revenue_report(
        db: AsyncSession,
        year: Optional[int] = None,
        period: str = "yearly",
        custom_start: Optional[datetime] = None,
        custom_end: Optional[datetime] = None,
        entity: Optional[str] = None,
        hierarchy: Optional[str] = None,
    ) -> Dict[str, Any]:
        def to_naive(dt: Optional[datetime]) -> Optional[datetime]:
            """Convert datetime to naive if it has timezone"""
            return dt.replace(tzinfo=None) if dt and dt.tzinfo else dt

        def calculate_growth(current: Decimal, previous: Decimal) -> str:
            """Calculate percentage growth between periods"""
            if previous == 0:
                return "N/A"
            # convert to float for division and formatting
            return f"{(float(current - previous) / float(previous) * 100):.1f}%"

        logger.debug("Generating product revenue report")

        today = datetime.utcnow()
        year = year or today.year

        # Calculate date ranges based on period
        if period == "yearly":
            start_date, end_date = FinancialReport.get_yearly_date_range(year)
            prev_start, prev_end = FinancialReport.get_yearly_date_range(year - 1)
        elif period == "quarterly":
            start_date, end_date = FinancialReport.get_quarterly_date_range(year, today)
            prev_start, prev_end = FinancialReport.get_quarterly_date_range(year - 1, today)
        elif period == "custom":
            if not custom_start or not custom_end:
                raise ValueError("For custom period, both custom_start and custom_end must be provided")
            start_date, end_date = custom_start, custom_end
            prev_start = prev_end = None
        elif period == "all":
            start_date = end_date = prev_start = prev_end = None
        else:
            raise ValueError(f"Invalid period: {period}. Must be 'yearly', 'quarterly', 'custom', or 'all'")

        # Convert to naive datetimes
        start_date, end_date = to_naive(start_date), to_naive(end_date)
        prev_start, prev_end = to_naive(prev_start), to_naive(prev_end)

        # Build filters for current and previous periods
        current_filters = FinancialReport.build_filters(start_date, end_date, entity, hierarchy)
        prev_filters = FinancialReport.build_filters(prev_start, prev_end, entity, hierarchy) if prev_start else None

        # Load account mapping from DB
        mapping = await FinancialReport.get_account_mapping(db)
        logger.debug(f"Loaded {len(mapping)} account mappings")

        async def get_revenue_data(filters: Any) -> Dict[str, List[Decimal]]:
            """Helper to get revenue data by quarter for given filters"""
            if filters is None:
                return defaultdict(lambda: [Decimal("0.0")] * 4)

            stmt = (
                select(
                    GeneralLedger.account_name,
                    extract("quarter", GeneralLedger.date).label("quarter"),
                    func.coalesce(func.sum(GeneralLedger.credit), 0).label("amount"),
                )
                .where(filters)
                .group_by(GeneralLedger.account_name, "quarter")
            )
            rows = (await db.execute(stmt)).all()

            revenue_data = defaultdict(lambda: [Decimal("0.0")] * 4)
            for acct_name, quarter, amount in rows:
                if not acct_name:
                    continue

                acct_key = acct_name.strip().lower()
                cat_tag = mapping.get(acct_key)
                if not cat_tag:
                    continue

                category, tags = cat_tag
                tags_list = [t.strip().lower() for t in tags.split(",")] if isinstance(tags, str) else []

                # Filter only revenue-related accounts
                if category != "revenue" and "income" not in tags_list and "sales" not in tags_list:
                    continue

                # quarter may be None; default to Q1 (index 0)
                quarter_idx = int(quarter) - 1 if quarter else 0
                revenue_data[acct_key][quarter_idx] += Decimal(amount) if amount is not None else Decimal("0.0")

            return revenue_data

        # Get revenue data for current and previous periods
        current_revenue = await get_revenue_data(current_filters)
        previous_revenue = await get_revenue_data(prev_filters) if prev_start else None

        # Build report and calculate totals and growth
        report = []
        totals = {
            "current": [Decimal("0.0")] * 4,
            "previous": [Decimal("0.0")] * 4 if prev_start else None,
            "growth": [None] * 4
        }

        all_products = set(current_revenue.keys())
        if previous_revenue:
            all_products.update(previous_revenue.keys())

        for product in sorted(all_products):
            current = current_revenue.get(product, [Decimal("0.0")] * 4)
            previous = previous_revenue.get(product, [Decimal("0.0")] * 4) if previous_revenue else None

            total_current = sum(current)
            total_previous = sum(previous) if previous else Decimal("0.0")

            # Skip if no revenue in either period
            if total_current == 0 and (not previous or total_previous == 0):
                continue

            cat_tag = mapping.get(product, ("", ""))
            report.append({
                "product": product,
                "category": cat_tag[0],
                "tags": cat_tag[1],
                "current": {
                    "Q1": float(current[0]),
                    "Q2": float(current[1]),
                    "Q3": float(current[2]),
                    "Q4": float(current[3]),
                    "Total": float(total_current)
                },
                "previous": {
                    "Q1": float(previous[0]) if previous else None,
                    "Q2": float(previous[1]) if previous else None,
                    "Q3": float(previous[2]) if previous else None,
                    "Q4": float(previous[3]) if previous else None,
                    "Total": float(total_previous) if previous else None
                } if prev_start else None,
                "growth": calculate_growth(total_current, total_previous) if prev_start else "N/A"
            })

            # Accumulate totals
            for q in range(4):
                totals["current"][q] += current[q]
                if previous:
                    totals["previous"][q] += previous[q]

        # Calculate quarter-wise growth totals
        if prev_start:
            totals["growth"] = [
                calculate_growth(totals["current"][q], totals["previous"][q])
                for q in range(4)
            ]

        # Overall growth for total revenue
        total_current = sum(totals["current"])
        total_previous = sum(totals["previous"]) if prev_start else Decimal("0.0")
        overall_growth = calculate_growth(total_current, total_previous) if prev_start else "N/A"

        # Identify top and bottom products by total current revenue
        if report:
            top_product = max(report, key=lambda x: x["current"]["Total"])
            bottom_product = min(report, key=lambda x: x["current"]["Total"])
        else:
            top_product = bottom_product = None

        return {
            "report": report,
            "period_info": {
                "type": period,
                "year": year,
                "start_date": start_date,
                "end_date": end_date,
                "previous_start": prev_start,
                "previous_end": prev_end
            },
            "totals": {
                "current": {
                    "Q1": float(totals["current"][0]),
                    "Q2": float(totals["current"][1]),
                    "Q3": float(totals["current"][2]),
                    "Q4": float(totals["current"][3]),
                    "Total": float(total_current)
                },
                "previous": {
                    "Q1": float(totals["previous"][0]) if prev_start else None,
                    "Q2": float(totals["previous"][1]) if prev_start else None,
                    "Q3": float(totals["previous"][2]) if prev_start else None,
                    "Q4": float(totals["previous"][3]) if prev_start else None,
                    "Total": float(total_previous) if prev_start else None
                } if prev_start else None,
                "growth": {
                    "Q1": totals["growth"][0] if prev_start else "N/A",
                    "Q2": totals["growth"][1] if prev_start else "N/A",
                    "Q3": totals["growth"][2] if prev_start else "N/A",
                    "Q4": totals["growth"][3] if prev_start else "N/A",
                    "Total": overall_growth
                }
            },
            "key_metrics": {
    "Top_Product": {
        "name": top_product["product"] if top_product else None,
        "amount": top_product["current"]["Total"] if top_product else None,
        "percentage": f"{(top_product['current']['Total'] / float(total_current) * 100):.1f}%"
                    if top_product and total_current > 0 else "N/A"
    },
    "Bottom_Product": {
        "name": bottom_product["product"] if bottom_product else None,
        "amount": bottom_product["current"]["Total"] if bottom_product else None,
        "percentage": f"{(bottom_product['current']['Total'] / float(total_current) * 100):.1f}%"
                    if bottom_product and total_current > 0 else "N/A"
    },
    "Quarterly_Variation": {
        "min_quarter": f"Q{totals['current'].index(min(totals['current'])) + 1}"
                    if totals['current'] else None,
        "max_quarter": f"Q{totals['current'].index(max(totals['current'])) + 1}"
                    if totals['current'] else None,
        "variation": f"{(float(max(totals['current'])) - float(min(totals['current']))) / float(max(totals['current'])) * 100:.1f}%"
                    if totals['current'] and max(totals['current']) > 0 else "N/A"
    }
}

        }
    
    @staticmethod
    async def get_cashflow_statement(
        db: AsyncSession,
        period: str = "yearly",
        year: Optional[int] = None,
        custom_start: Optional[datetime] = None,
        custom_end: Optional[datetime] = None,
        entity: Optional[str] = None,
        hierarchy: Optional[str] = None
    ) -> Dict[str, Any]:
        def to_naive(dt: Optional[datetime]) -> Optional[datetime]:
            """Convert datetime to naive if it has timezone"""
            if dt is None:
                return None
            return dt.replace(tzinfo=None) if dt.tzinfo else dt

        def calculate_ccc(
            inventory_balances: List[Decimal],
            ar_balances: List[Decimal],
            ap_balances: List[Decimal],
            cogs_total: Decimal,
            revenue_total: Decimal,
            days_in_period: int
        ) -> Dict[str, Any]:
            """Calculate Cash Conversion Cycle metrics"""
            def average(lst):
                return sum(lst) / len(lst) if lst else Decimal("0.0")

            avg_inventory = average(inventory_balances)
            avg_ar = average(ar_balances)
            avg_ap = average(ap_balances)

            try:
                dio = (avg_inventory / cogs_total) * days_in_period if cogs_total > 0 else Decimal("0.0")
            except DivisionByZero:
                dio = Decimal("0.0")

            try:
                dso = (avg_ar / revenue_total) * days_in_period if revenue_total > 0 else Decimal("0.0")
            except DivisionByZero:
                dso = Decimal("0.0")

            try:
                dpo = (avg_ap / cogs_total) * days_in_period if cogs_total > 0 else Decimal("0.0")
            except DivisionByZero:
                dpo = Decimal("0.0")

            ccc = dio + dso - dpo

            return {
                "DIO": round(dio, 2),
                "DSO": round(dso, 2),
                "DPO": round(dpo, 2),
                "CCC": round(ccc, 2)
            }

        logger.debug("Generating cashflow statement")

        today = datetime.utcnow()
        year = year or today.year

        # Calculate date ranges based on period
        if period == "yearly":
            start_date, end_date = FinancialReport.get_yearly_date_range(year)
            prev_start, prev_end = FinancialReport.get_yearly_date_range(year - 1)
        elif period == "quarterly":
            start_date, end_date = FinancialReport.get_quarterly_date_range(year, today)
            prev_start, prev_end = FinancialReport.get_quarterly_date_range(year - 1, today)
        elif period == "custom":
            if not custom_start or not custom_end:
                raise ValueError("For custom period, both custom_start and custom_end must be provided")
            start_date, end_date = custom_start, custom_end
            prev_start = prev_end = None
        elif period == "all":
            start_date = end_date = prev_start = prev_end = None
        else:
            raise ValueError(f"Invalid period: {period}. Must be 'yearly', 'quarterly', 'custom', or 'all'")

        # Convert to naive datetimes
        start_date, end_date = to_naive(start_date), to_naive(end_date)
        prev_start, prev_end = to_naive(prev_start), to_naive(prev_end)

        # Build filters
        current_filters = FinancialReport.build_filters(start_date, end_date, entity, hierarchy)
        current_entries = (await db.execute(select(GeneralLedger).where(current_filters))).scalars().all()

        # Load previous period if available
        previous_entries = []
        if prev_start and prev_end:
            previous_filters = FinancialReport.build_filters(prev_start, prev_end, entity, hierarchy)
            previous_entries = (await db.execute(select(GeneralLedger).where(previous_filters))).scalars().all()

        # Load account mapping
        mapping = await FinancialReport.get_account_mapping(db)
        logger.debug(f"Loaded {len(mapping)} account mappings")

        # Process cash flow data for current and previous periods
        cash_flows = {
            "current": {
                "operating": Decimal("0.0"),
                "investing": Decimal("0.0"),
                "financing": Decimal("0.0"),
                "ccc_data": {
                    "inventory": [],
                    "receivable": [],
                    "payable": [],
                    "cogs": Decimal("0.0"),
                    "revenue": Decimal("0.0")
                }
            },
            "previous": {
                "operating": Decimal("0.0"),
                "investing": Decimal("0.0"),
                "financing": Decimal("0.0"),
                "ccc_data": {
                    "inventory": [],
                    "receivable": [],
                    "payable": [],
                    "cogs": Decimal("0.0"),
                    "revenue": Decimal("0.0")
                }
            } if prev_start else None
        }

        def process_entries(entries: List[Any], period_type: str):
            """Process ledger entries for cash flow categorization"""
            for entry in entries:
                if not entry.date or not entry.account_name:
                    continue

                acct = entry.account_name.strip().lower()
                meta = mapping.get(acct)
                if not meta:
                    continue

                category, tags = meta
                tags_list = [t.strip().lower() for t in tags.split(",")] if isinstance(tags, str) else []
                credit = entry.credit or Decimal("0.0")
                debit = entry.debit or Decimal("0.0")
                net_amount = credit - debit

                # Cash flow categorization
                if "operating" in tags_list or category in ("expense", "revenue"):
                    cash_flows[period_type]["operating"] += net_amount
                elif "investing" in tags_list or ("fixed asset" in tags_list and category == "asset"):
                    cash_flows[period_type]["investing"] -= debit
                elif "financing" in tags_list or category in ("liability", "equity"):
                    cash_flows[period_type]["financing"] += net_amount

                # CCC components collection
                if ("inventory" in tags_list or "inventory" in acct) and category == "asset":
                    balance = debit - credit
                    cash_flows[period_type]["ccc_data"]["inventory"].append(balance)
                elif ("receivable" in tags_list or "accounts receivable" in acct) and category == "asset":
                    balance = debit - credit
                    cash_flows[period_type]["ccc_data"]["receivable"].append(balance)
                elif ("payable" in tags_list or "accounts payable" in acct) and category in ("liability", "liabilities"):
                    balance = credit - debit
                    cash_flows[period_type]["ccc_data"]["payable"].append(balance)
                elif "cogs" in tags_list or "cost of goods sold" in acct:
                    cash_flows[period_type]["ccc_data"]["cogs"] += abs(net_amount)
                elif category == "revenue" or "revenue" in tags_list:
                    cash_flows[period_type]["ccc_data"]["revenue"] += abs(net_amount)

        # Process both periods
        process_entries(current_entries, "current")
        if previous_entries:
            process_entries(previous_entries, "previous")

        # Calculate CCC metrics
        days_in_period = (end_date - start_date).days if (start_date and end_date) else 365
        ccc_metrics = {
            "current": calculate_ccc(
                cash_flows["current"]["ccc_data"]["inventory"],
                cash_flows["current"]["ccc_data"]["receivable"],
                cash_flows["current"]["ccc_data"]["payable"],
                cash_flows["current"]["ccc_data"]["cogs"],
                cash_flows["current"]["ccc_data"]["revenue"],
                days_in_period
            ),
            "previous": calculate_ccc(
                cash_flows["previous"]["ccc_data"]["inventory"],
                cash_flows["previous"]["ccc_data"]["receivable"],
                cash_flows["previous"]["ccc_data"]["payable"],
                cash_flows["previous"]["ccc_data"]["cogs"],
                cash_flows["previous"]["ccc_data"]["revenue"],
                days_in_period
            ) if cash_flows["previous"] else None
        }

        # Calculate totals
        total_current = cash_flows["current"]["operating"] + cash_flows["current"]["investing"] + cash_flows["current"]["financing"]
        total_previous = (
            (cash_flows["previous"]["operating"] + cash_flows["previous"]["investing"] + cash_flows["previous"]["financing"])
            if cash_flows["previous"] else None
        )

        def calculate_change(current: Decimal, previous: Decimal) -> Dict[str, Any]:
            """Calculate change metrics between periods"""
            if previous is None:
                return {
                    "amount": None,
                    "percentage": "N/A"
                }
            change = current - previous
            pct = (change / previous * 100) if previous != 0 else Decimal("0.0")
            return {
                "amount": float(round(change, 2)),
                "percentage": f"{round(pct, 1)}%"
            }

        return {
            "period_info": {
                "type": period,
                "year": year,
                "start_date": start_date,
                "end_date": end_date,
                "previous_start": prev_start,
                "previous_end": prev_end
            },
            "cash_flows": {
                "operating": {
                    "current": float(round(cash_flows["current"]["operating"], 2)),
                    "previous": float(round(cash_flows["previous"]["operating"], 2)) if cash_flows["previous"] else None,
                    "change": calculate_change(cash_flows["current"]["operating"], cash_flows["previous"]["operating"] if cash_flows["previous"] else None)
                },
                "investing": {
                    "current": float(round(cash_flows["current"]["investing"], 2)),
                    "previous": float(round(cash_flows["previous"]["investing"], 2)) if cash_flows["previous"] else None,
                    "change": calculate_change(cash_flows["current"]["investing"], cash_flows["previous"]["investing"] if cash_flows["previous"] else None)
                },
                "financing": {
                    "current": float(round(cash_flows["current"]["financing"], 2)),
                    "previous": float(round(cash_flows["previous"]["financing"], 2)) if cash_flows["previous"] else None,
                    "change": calculate_change(cash_flows["current"]["financing"], cash_flows["previous"]["financing"] if cash_flows["previous"] else None)
                },
                "total": {
                    "current": float(round(total_current, 2)),
                    "previous": float(round(total_previous, 2)) if total_previous is not None else None,
                    "change": calculate_change(total_current, total_previous if total_previous is not None else None)
                }
            },
            "cash_conversion_cycle": {
                "current": ccc_metrics["current"],
                "previous": ccc_metrics["previous"],
                "change": {
                    "DIO": calculate_change(ccc_metrics["current"]["DIO"], ccc_metrics["previous"]["DIO"] if ccc_metrics["previous"] else None),
                    "DSO": calculate_change(ccc_metrics["current"]["DSO"], ccc_metrics["previous"]["DSO"] if ccc_metrics["previous"] else None),
                    "DPO": calculate_change(ccc_metrics["current"]["DPO"], ccc_metrics["previous"]["DPO"] if ccc_metrics["previous"] else None),
                    "CCC": calculate_change(ccc_metrics["current"]["CCC"], ccc_metrics["previous"]["CCC"] if ccc_metrics["previous"] else None)
                } if ccc_metrics["previous"] else None
            },
            "key_metrics": {
                "operating_cash_flow_ratio": {
                    "current": float(round(cash_flows["current"]["operating"] / abs(total_current), 2)) if total_current != 0 else 0.0,
                    "previous": float(round(cash_flows["previous"]["operating"] / abs(total_previous), 2)) if total_previous and total_previous != 0 else None,
                    "change": calculate_change(
                        cash_flows["current"]["operating"] / abs(total_current) if total_current != 0 else Decimal("0.0"),
                        cash_flows["previous"]["operating"] / abs(total_previous) if total_previous and total_previous != 0 else None
                    )
                } if cash_flows["previous"] else None,
                "free_cash_flow": {
                    "current": float(round(cash_flows["current"]["operating"] + cash_flows["current"]["investing"], 2)),
                    "previous": float(round(cash_flows["previous"]["operating"] + cash_flows["previous"]["investing"], 2)) if cash_flows["previous"] else None,
                    "change": calculate_change(
                        cash_flows["current"]["operating"] + cash_flows["current"]["investing"],
                        (cash_flows["previous"]["operating"] + cash_flows["previous"]["investing"]) if cash_flows["previous"] else None
                    )
                } if cash_flows["previous"] else None
            }
        }
   
    
    
   
   

    @staticmethod
    async def profit_loss_statement(period: str,year:int, custom_start: datetime, custom_end: datetime, db: AsyncSession,entity: Optional[str] = None ,hierarchy:Optional[str]=None) -> Dict[str, any]:
        today = datetime.today()
    
        # Compute date range based on custom or preset period
        start, end = FinancialReport.get_date_range(period, year, custom_start, custom_end)
        logger.debug(f"Generating cashflow statement for period '{period}' from {start} to {end}")

        # Apply dynamic filters using helper
        filters = FinancialReport.build_filters(start=start, end=end, entity=entity, hierarchy=hierarchy)
        
        # Fetch account mapping and ledger data
        mapping = await FinancialReport.get_account_mapping(db)
        stmt = select(GeneralLedger).where(filters)
        gl_data = (await db.execute(stmt)).scalars().all()
        logger.debug(f"Fetched {len(gl_data)} general ledger entries")
        summary_by_month = defaultdict(lambda: {
            "revenue": Decimal("0.0"),
            "costs": Decimal("0.0"),
            "opexpense": Decimal("0.0"),
            "actual_profit": Decimal("0.0"),
            "budget_revenue": Decimal("0.0"),
            "budget_costs": Decimal("0.0"),
            "budget_profit": Decimal("0.0"),
            "variance": Decimal("0.0"),
            "variance_percentage": Decimal("0.0")
        })

        monthly = defaultdict(lambda: {
            "revenue": defaultdict(Decimal),
            "cogs": defaultdict(Decimal),
            "opexpense": defaultdict(Decimal),
            "total": {"revenue": Decimal(0), "cogs": Decimal(0), "opexpense": Decimal(0)}
        })

        skipped_count = 0
        for e in gl_data:
            if not e.date or not e.account_name:
                skipped_count += 1
                continue
            acct_raw = e.account_name.strip()
            acct = acct_raw.lower()
            cat_tags = mapping.get(acct)
            if not cat_tags:
                skipped_count += 1
                continue

            category, tags_str = cat_tags
            tags = [x.strip() for x in tags_str.split(',')]
            amount = (e.credit or Decimal(0)) if category == "revenue" else (e.debit or Decimal(0))
            mm = e.date.strftime('%b')
            acct_title = acct_raw.title()

            if category == "revenue":
                summary_by_month[mm]["revenue"] += amount
                monthly[mm]["revenue"][acct_title] += amount
                monthly[mm]["total"]["revenue"] += amount
            elif category == "expense" and "cost of sales" in tags:
                print("expense",category,tags)
                summary_by_month[mm]["costs"] += amount
                monthly[mm]["cogs"][acct_title] += amount
                monthly[mm]["total"]["cogs"] += amount
            elif category == "expense" and "operating expense" in tags:
                print("expenses",category,tags)
                summary_by_month[mm]["opexpense"] += amount
                monthly[mm]["opexpense"][acct_title] += amount
                monthly[mm]["total"]["opexpense"] += amount

        months = sorted(monthly.keys(), key=lambda m: datetime.strptime(m, '%b'))

        # Key metric calculations
        total_actual = total_budget = Decimal("0.0")
        best_month = worst_month = None
        best_profit = worst_profit = None

        for m, data in summary_by_month.items():
            data["actual_profit"] = data["revenue"] - data["costs"]
            data["budget_revenue"] = data["revenue"] * Decimal("0.9")
            data["budget_costs"] = data["costs"] * Decimal("0.95")
            data["budget_profit"] = data["budget_revenue"] - data["budget_costs"]
            data["variance"] = data["actual_profit"] - data["budget_profit"]
            if data["budget_profit"] != 0:
                data["variance_percentage"] = (data["variance"] / data["budget_profit"]) * Decimal("100")

            total_actual += data["actual_profit"]
            total_budget += data["budget_profit"]

            if best_profit is None or data["actual_profit"] > best_profit:
                best_profit = data["actual_profit"]
                best_month = m
            if worst_profit is None or data["actual_profit"] < worst_profit:
                worst_profit = data["actual_profit"]
                worst_month = m

        overall_variance = total_actual - total_budget
        overall_pct = (overall_variance / total_budget * Decimal("100")) if total_budget else Decimal("0.0")

        # Calculate total revenue, gross profit, net income
        total_revenue = sum(data["revenue"] for data in summary_by_month.values())
        total_costs = sum(data["costs"] for data in summary_by_month.values())
        total_opexpense = sum(data["opexpense"] for data in summary_by_month.values())
        total_gross_profit = total_revenue - total_costs
        total_net_income = total_gross_profit - total_opexpense

        # Monthly overview
        monthly_overview = {
            "months": months,
            "revenue_accounts": {},
            "cogs_accounts": {},
            "opexpense_accounts": {},
            "gross_profit": [],
            "net_income": []
        }

        for m in months:
            tot = monthly[m]["total"]
            gp = tot["revenue"] - tot["cogs"]
            ni = gp - tot["opexpense"]
            monthly_overview["gross_profit"].append(str(gp))
            monthly_overview["net_income"].append(str(ni))
            monthly_overview["revenue_accounts"][m] = {acct: str(val) for acct, val in monthly[m]["revenue"].items()}
            monthly_overview["cogs_accounts"][m] = {acct: str(val) for acct, val in monthly[m]["cogs"].items()}
            monthly_overview["opexpense_accounts"][m] = {acct: str(val) for acct, val in monthly[m]["opexpense"].items()}

        def get_quarter(month_name: str) -> str:
            num = datetime.strptime(month_name, '%b').month
            return f"Q{((num - 1) // 3 + 1)}"

        quarterly = defaultdict(lambda: {
            "revenue": defaultdict(Decimal),
            "cogs": defaultdict(Decimal),
            "opexpense": defaultdict(Decimal),
            "total": {"revenue": Decimal(0), "cogs": Decimal(0), "opexpense": Decimal(0)}
        })

        for m in months:
            q = get_quarter(m)
            for acct, amt in monthly[m]["revenue"].items():
                quarterly[q]["revenue"][acct] += amt
                quarterly[q]["total"]["revenue"] += amt
            for acct, amt in monthly[m]["cogs"].items():
                quarterly[q]["cogs"][acct] += amt
                quarterly[q]["total"]["cogs"] += amt
            for acct, amt in monthly[m]["opexpense"].items():
                quarterly[q]["opexpense"][acct] += amt
                quarterly[q]["total"]["opexpense"] += amt

        quarterly_overview = {
            "quarters": sorted(quarterly.keys()),
            "revenue_accounts": {},
            "cogs_accounts": {},
            "opexpense_accounts": {},
            "gross_profit": [],
            "net_income": []
        }

        for q in quarterly_overview["quarters"]:
            tot = quarterly[q]["total"]
            gp = tot["revenue"] - tot["cogs"]
            ni = gp - tot["opexpense"]
            quarterly_overview["gross_profit"].append(str(gp))
            quarterly_overview["net_income"].append(str(ni))
            quarterly_overview["revenue_accounts"][q] = {acct: str(val) for acct, val in quarterly[q]["revenue"].items()}
            quarterly_overview["cogs_accounts"][q] = {acct: str(val) for acct, val in quarterly[q]["cogs"].items()}
            quarterly_overview["opexpense_accounts"][q] = {acct: str(val) for acct, val in quarterly[q]["opexpense"].items()}

        def generate_commentary(actual: Decimal, budget: Decimal, variance: Decimal) -> str:
            if variance > Decimal("1000"):
                return "Performance exceeded expectations significantly."
            elif variance < Decimal("-1000"):
                return "Performance fell short of expectations. Investigate further."
            else:
                return "Performance was close to budget."

        budget_vs_actual = {
            "revenue_accounts": {},
            "cogs_accounts": {},
            "opexpense_accounts": {}
        }

        for category in ["revenue", "cogs", "opexpense"]:
            seen_accounts = set()
            for m in months:
                seen_accounts.update(monthly[m][category].keys())

            for acct in seen_accounts:
                actual = sum(monthly[m][category].get(acct, Decimal(0)) for m in months)
                budget_multiplier = Decimal("0.9") if category == "revenue" else Decimal("0.95")
                budget = actual * budget_multiplier
                variance = actual - budget
                monthly_avg = actual / max(len(months), 1)
                forecast1 = monthly_avg
                forecast2 = monthly_avg * 3
                comment = generate_commentary(actual, budget, variance)

                budget_vs_actual[f"{category}_accounts"][acct] = {
                    "actual": str(actual),
                    "budget": str(budget),
                    "variance": str(variance),
                    "forecast1": str(forecast1),
                    "forecast2": str(forecast2),
                    "commentary": comment
                }

        return {
            "financial_overview": {
                "monthly": monthly_overview,
                "quarterly": quarterly_overview
            },
            "budget_vs_actual": budget_vs_actual,
            "monthly_summary": summary_by_month,
            "key_metrics": {
                "Total_Revenue": f"{round(total_revenue, 2):,}",
                "Total_Gross_Profit": f"{round(total_gross_profit, 2):,}",
                "Total_Net_Income": f"{round(total_net_income, 2):,}",
                "Total_Actual_Profit": f"{round(total_actual, 2):,}",
                "Total_Budget_Profit": f"{round(total_budget, 2):,}",
                "Overall_Variance": f"{round(overall_variance, 2):,}",
                "Overall_Variance_Percentage": f"{round(overall_pct, 1)}%",
                "Best_Month": best_month or "-",
                "Worst_Month": worst_month or "-"
            }
        }


    @staticmethod
    async def get_Accounts_Receivable(
        db: AsyncSession,
        period: str,
        year: Optional[int] = None,
        custom_start: Optional[datetime] = None,
        custom_end: Optional[datetime] = None,
        entity: Optional[str] = None,
        hierarchy: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        Retrieve and analyze accounts receivable data for a given period.
        
        Args:
            db: Async database session
            period: Time period to analyze ('yearly', 'quarterly', or 'custom')
            year: Year to analyze (required for yearly and quarterly periods)
            custom_start: Start date for custom period
            custom_end: End date for custom period
            entity: Optional entity filter
            hierarchy: Optional hierarchy filter
        
        Returns:
            Dictionary containing AR aging analysis, totals, and customer breakdown
        
        Raises:
            ValueError: If invalid parameters are provided
        """
        from collections import defaultdict
        from decimal import Decimal
        from datetime import datetime, timedelta
        from sqlalchemy import select
        import json

        today = datetime.utcnow()
        year = year or today.year

        # Calculate date ranges based on period
        if period == "yearly":
            cur_s = datetime(year, 1, 1)
            cur_e = datetime(year, 12, 31, 23, 59, 59, 999999)
            prev_s = datetime(year - 1, 1, 1)
            prev_e = datetime(year - 1, 12, 31, 23, 59, 59, 999999)
        elif period == "quarterly":
            current_quarter = (today.month - 1) // 3 + 1
            start_month = 3 * (current_quarter - 1) + 1
            end_month = start_month + 2
            cur_s = datetime(year, start_month, 1)
            if end_month < 12:
                cur_e = datetime(year, end_month + 1, 1) - timedelta(days=1)
                cur_e = cur_e.replace(hour=23, minute=59, second=59, microsecond=999999)
            else:
                cur_e = datetime(year, 12, 31, 23, 59, 59, 999999)
            prev_s = datetime(year - 1, start_month, 1)
            if end_month < 12:
                prev_e = datetime(year - 1, end_month + 1, 1) - timedelta(days=1)
                prev_e = prev_e.replace(hour=23, minute=59, second=59, microsecond=999999)
            else:
                prev_e = datetime(year - 1, 12, 31, 23, 59, 59, 999999)
        elif period == "custom":
            if not custom_start or not custom_end:
                raise ValueError("For custom period, both custom_start and custom_end must be provided.")
            cur_s, cur_e = custom_start, custom_end
            prev_s = prev_e = None
        else:
            raise ValueError(f"Invalid period type: {period}. Must be 'yearly', 'quarterly', or 'custom'.")

        reference_date = custom_end if period == "custom" else cur_e
        mapping = await FinancialReport.get_account_mapping(db)

        def get_quarter_range(year: int, quarter: int):
            if quarter == 1:
                return datetime(year, 1, 1), datetime(year, 3, 31)
            elif quarter == 2:
                return datetime(year, 4, 1), datetime(year, 6, 30)
            elif quarter == 3:
                return datetime(year, 7, 1), datetime(year, 9, 30)
            elif quarter == 4:
                return datetime(year, 10, 1), datetime(year, 12, 31)

        def build_aging(entries: list, reference_date: datetime):
            aging_buckets = {
                "Current": Decimal("0.0"),
                "1-30": Decimal("0.0"),
                "31-60": Decimal("0.0"),
                "61-90": Decimal("0.0"),
                "90+": Decimal("0.0")
            }
            ar_customer = defaultdict(lambda: {
                "Current": Decimal("0.0"),
                "1-30 Days": Decimal("0.0"),
                "31-60 Days": Decimal("0.0"),
                "61-90 Days": Decimal("0.0"),
                "90+ Days": Decimal("0.0"),
                "Total": Decimal("0.0")
            })
            total = Decimal("0.0")

            for entry in entries:
                if not (entry.date and entry.debit and entry.account_name):
                    continue

                acct = entry.account_name.strip().lower()
                cat_tag = mapping.get(acct, ("", ""))
                category, tags = cat_tag

                if category != "asset" or "receivables" not in tags.lower():
                    continue

                days = (reference_date - entry.date).days
                amount = Decimal(entry.debit)
                total += amount

                if days <= 0:
                    bucket = "Current"
                elif days <= 30:
                    bucket = "1-30 Days"
                elif days <= 60:
                    bucket = "31-60 Days"
                elif days <= 90:
                    bucket = "61-90 Days"
                else:
                    bucket = "90+ Days"

                aging_buckets[bucket if bucket == "Current" else bucket.split()[0]] += amount

                try:
                    extra = json.loads(entry.extra_data or "{}")
                    customer = extra.get("customer") or extra.get("entity") or "Unknown Customer"
                except:
                    customer = "Unknown Customer"

                ar_customer[customer][bucket] += amount
                ar_customer[customer]["Total"] += amount

            overdue = total - aging_buckets["Current"]
            avg_days = (
                sum((reference_date - e.date).days * Decimal(e.debit)
                    for e in entries
                    if e.date and e.debit and mapping.get(e.account_name.strip().lower(), ("", ""))[0] == "asset")
                / total
                if total else Decimal("0.0")
            )

            aging_report = []
            for bucket_label, amt in aging_buckets.items():
                pct = float((amt / total * Decimal("100")) if total else Decimal("0.0"))
                aging_report.append({
                    "bucket": bucket_label,
                    "amount": float(round(amt, 2)),
                    "pct": round(pct, 1)
                })

            ar_customer_report = [
                {
                    "customer": cust,
                    **{k.replace(" ", "_"): float(round(v, 2)) for k, v in [
                        ("Current", buckets["Current"]),
                        ("1-30 Days", buckets["1-30 Days"]),
                        ("31-60 Days", buckets["31-60 Days"]),
                        ("61-90 Days", buckets["61-90 Days"]),
                        ("90+ Days", buckets["90+ Days"]),
                        ("Total", buckets["Total"])
                    ]},
                }
                for cust, buckets in ar_customer.items()
            ]

            key_metrics = {
                "TotalReceivables": float(round(total, 2)),
                "Overdue": float(round(overdue, 2)),
                "90_Days_Overdue": {
                    "amount": float(round(aging_buckets["90+"], 2)),
                    "pct": round((aging_buckets["90+"] / total * Decimal("100")) if total else Decimal("0.0"), 1)
                },
                "Days_Sales_Outstanding(DSO)": float(round(avg_days, 1))
            }

            return {
                "aging": aging_report,
                "total": float(round(total, 2)),
                "overdue": float(round(overdue, 2)),
                "overdue_pct": round((overdue / total * Decimal("100")) if total else Decimal("0.0"), 1),
                "avg_days": float(round(avg_days, 1)),
                "ar_customer": ar_customer_report,
                "key_metrics": key_metrics
            }

        # Get current period data
        filters = FinancialReport.build_filters(start=cur_s, end=cur_e, entity=entity, hierarchy=hierarchy)
        stmt = select(GeneralLedger).where(filters)
        entries = (await db.execute(stmt)).scalars().all()
        result = build_aging(entries, cur_e)

        # Add comparison data if not custom period
        if period != "custom":
            prev_filters = FinancialReport.build_filters(start=prev_s, end=prev_e, entity=entity, hierarchy=hierarchy)
            prev_stmt = select(GeneralLedger).where(prev_filters)
            prev_entries = (await db.execute(prev_stmt)).scalars().all()
            result["comparison"] = build_aging(prev_entries, prev_e)

        if period == "quarterly":
            quarterly_data = {}
            for q in range(1, 5):
                q_start, q_end = get_quarter_range(year, q)
                q_filters = FinancialReport.build_filters(start=q_start, end=q_end, entity=entity, hierarchy=hierarchy)
                q_stmt = select(GeneralLedger).where(q_filters)
                q_entries = (await db.execute(q_stmt)).scalars().all()
                quarterly_data[f"Q{q}"] = build_aging(q_entries, q_end)
            result["quarterly"] = quarterly_data

        return result

    @staticmethod
    def get_date_range(
        period: str,
        year: Optional[int] = None,
        custom_start: Optional[datetime] = None,
        custom_end: Optional[datetime] = None
    ) -> Tuple[Optional[datetime], Optional[datetime]]:
        """Calculate date ranges for yearly, quarterly, or custom periods within a specific year."""
        today = datetime.today()

        if period == "yearly":
            if year is None:
                year = today.year
            start = datetime(year, 1, 1)
            end = datetime(year, 12, 31)

        elif period == "quarterly":
            if year is None:
                year = today.year
            current_month = today.month
            quarter = (current_month - 1) // 3 + 1
            start_month = 3 * (quarter - 1) + 1
            start = datetime(year, start_month, 1)

            # Calculate end of quarter
            if start_month + 2 == 12:
                end = datetime(year, 12, 31)
            else:
                next_month = datetime(year, start_month + 3, 1)
                end = next_month - timedelta(days=1)

        elif period == "custom":
            if not custom_start or not custom_end:
                raise ValueError("Custom period requires both start and end dates")
            if custom_start > custom_end:
                raise ValueError("Start date cannot be after end date")

            # Year enforcement
            if year is None:
                raise ValueError("Year must be provided for custom period")
            if custom_start.year != year or custom_end.year != year:
                raise ValueError("Custom start and end dates must be within the specified year")

            start, end = custom_start, custom_end

        else:
            raise ValueError(f"Invalid period '{period}'. Must be 'yearly', 'quarterly', or 'custom'.")

        return start, end

    @staticmethod
    async def get_Accounts_Payable(
        db: AsyncSession,
        period: str,
        year: Optional[int] = None,
        custom_start: Optional[datetime] = None,
        custom_end: Optional[datetime] = None,
        entity: Optional[str] = None,
        hierarchy: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        Retrieve and analyze accounts payable data for a given period.
        
        Args:
            db: Async database session
            period: Time period to analyze ('yearly', 'quarterly', or 'custom')
            year: Year to analyze (required for yearly and quarterly periods)
            custom_start: Start date for custom period
            custom_end: End date for custom period
            entity: Optional entity filter
            hierarchy: Optional hierarchy filter
        
        Returns:
            Dictionary containing AP aging analysis, totals, and vendor breakdown
        
        Raises:
            ValueError: If invalid parameters are provided
        """
        from collections import defaultdict
        from decimal import Decimal
        from datetime import datetime, timedelta
        from sqlalchemy import select
        import json

        today = datetime.utcnow()
        year = year or today.year

        # Calculate date ranges based on period
        if period == "yearly":
            cur_s = datetime(year, 1, 1)
            cur_e = datetime(year, 12, 31, 23, 59, 59, 999999)
            prev_s = datetime(year - 1, 1, 1)
            prev_e = datetime(year - 1, 12, 31, 23, 59, 59, 999999)
        elif period == "quarterly":
            current_quarter = (today.month - 1) // 3 + 1
            start_month = 3 * (current_quarter - 1) + 1
            end_month = start_month + 2
            cur_s = datetime(year, start_month, 1)
            if end_month < 12:
                cur_e = datetime(year, end_month + 1, 1) - timedelta(days=1)
                cur_e = cur_e.replace(hour=23, minute=59, second=59, microsecond=999999)
            else:
                cur_e = datetime(year, 12, 31, 23, 59, 59, 999999)
            prev_s = datetime(year - 1, start_month, 1)
            if end_month < 12:
                prev_e = datetime(year - 1, end_month + 1, 1) - timedelta(days=1)
                prev_e = prev_e.replace(hour=23, minute=59, second=59, microsecond=999999)
            else:
                prev_e = datetime(year - 1, 12, 31, 23, 59, 59, 999999)
        elif period == "custom":
            if not custom_start or not custom_end:
                raise ValueError("For custom period, both custom_start and custom_end must be provided.")
            cur_s, cur_e = custom_start, custom_end
            prev_s = prev_e = None
        else:
            raise ValueError(f"Invalid period type: {period}. Must be 'yearly', 'quarterly', or 'custom'.")

        reference_date = custom_end if period == "custom" else cur_e
        mapping = await FinancialReport.get_account_mapping(db)

        def get_quarter_range(year: int, quarter: int):
            if quarter == 1:
                return datetime(year, 1, 1), datetime(year, 3, 31)
            elif quarter == 2:
                return datetime(year, 4, 1), datetime(year, 6, 30)
            elif quarter == 3:
                return datetime(year, 7, 1), datetime(year, 9, 30)
            elif quarter == 4:
                return datetime(year, 10, 1), datetime(year, 12, 31)

        def build_aging(entries: list, reference_date: datetime):
            aging_buckets = {
                "Current": Decimal("0.0"),
                "1-30": Decimal("0.0"),
                "31-60": Decimal("0.0"),
                "61-90": Decimal("0.0"),
                "90+": Decimal("0.0")
            }
            ap_vendor = defaultdict(lambda: {
                "Current": Decimal("0.0"),
                "1-30 Days": Decimal("0.0"),
                "31-60 Days": Decimal("0.0"),
                "61-90 Days": Decimal("0.0"),
                "90+ Days": Decimal("0.0"),
                "Total": Decimal("0.0")
            })
            total = Decimal("0.0")

            for entry in entries:
                if not (entry.date and entry.credit and entry.account_name):
                    continue

                acct = entry.account_name.strip().lower()
                cat_tag = mapping.get(acct, ("", ""))
                category, tags = cat_tag

                if category != "liability" or "payables" not in tags.lower():
                    continue

                days = (reference_date - entry.date).days
                amount = Decimal(entry.credit)
                total += amount

                if days <= 0:
                    bucket = "Current"
                elif days <= 30:
                    bucket = "1-30 Days"
                elif days <= 60:
                    bucket = "31-60 Days"
                elif days <= 90:
                    bucket = "61-90 Days"
                else:
                    bucket = "90+ Days"

                aging_buckets[bucket if bucket == "Current" else bucket.split()[0]] += amount

                try:
                    extra = json.loads(entry.extra_data or "{}")
                    vendor = extra.get("vendor") or extra.get("supplier") or "Unknown Vendor"
                except:
                    vendor = "Unknown Vendor"

                ap_vendor[vendor][bucket] += amount
                ap_vendor[vendor]["Total"] += amount

            overdue = total - aging_buckets["Current"]
            avg_days = (
                sum((reference_date - e.date).days * Decimal(e.credit)
                    for e in entries
                    if e.date and e.credit and mapping.get(e.account_name.strip().lower(), ("", ""))[0] == "liability")
                / total
                if total else Decimal("0.0")
            )

            aging_report = []
            for bucket_label, amt in aging_buckets.items():
                pct = float((amt / total * Decimal("100")) if total else Decimal("0.0"))
                aging_report.append({
                    "bucket": bucket_label,
                    "amount": float(round(amt, 2)),
                    "pct": round(pct, 1)
                })

            # Sort vendors by total amount (descending)
            sorted_vendors = sorted(ap_vendor.items(), key=lambda x: x[1]["Total"], reverse=True)
            
            ap_vendor_report = [
                {
                    "vendor": vendor,
                    "current": float(round(buckets["Current"], 2)),
                    "1_30_days": float(round(buckets["1-30 Days"], 2)),
                    "31_60_days": float(round(buckets["31-60 Days"], 2)),
                    "61_90_days": float(round(buckets["61-90 Days"], 2)),
                    "90_plus_days": float(round(buckets["90+ Days"], 2)),
                    "total": float(round(buckets["Total"], 2))
                }
                for vendor, buckets in sorted_vendors
            ]

            # Add totals row
            ap_vendor_report.append({
                "vendor": "Total",
                "current": float(round(aging_buckets["Current"], 2)),
                "1_30_days": float(round(aging_buckets["1-30"], 2)),
                "31_60_days": float(round(aging_buckets["31-60"], 2)),
                "61_90_days": float(round(aging_buckets["61-90"], 2)),
                "90_plus_days": float(round(aging_buckets["90+"], 2)),
                "total": float(round(total, 2))
            })

            key_metrics = {
                "TotalPayables": float(round(total, 2)),
                "Overdue": float(round(overdue, 2)),
                "90_Days_Overdue": {
                    "amount": float(round(aging_buckets["90+"], 2)),
                    "pct": round((aging_buckets["90+"] / total * Decimal("100")) if total else Decimal("0.0"), 1)
                },
                "Days_Payable_Outstanding(DPO)": float(round(avg_days, 1))
            }

            return {
                "aging": aging_report,
                "total": float(round(total, 2)),
                "overdue": float(round(overdue, 2)),
                "overdue_pct": round((overdue / total * Decimal("100")) if total else Decimal("0.0"), 1),
                "avg_days": float(round(avg_days, 1)),
                "ap_vendor": ap_vendor_report,
                "key_metrics": key_metrics
            }

        # Get current period data
        filters = FinancialReport.build_filters(start=cur_s, end=cur_e, entity=entity, hierarchy=hierarchy)
        stmt = select(GeneralLedger).where(filters)
        entries = (await db.execute(stmt)).scalars().all()
        result = build_aging(entries, cur_e)

        # Add comparison data if not custom period
        if period != "custom":
            prev_filters = FinancialReport.build_filters(start=prev_s, end=prev_e, entity=entity, hierarchy=hierarchy)
            prev_stmt = select(GeneralLedger).where(prev_filters)
            prev_entries = (await db.execute(prev_stmt)).scalars().all()
            result["comparison"] = build_aging(prev_entries, prev_e)

        if period == "quarterly":
            quarterly_data = {}
            for q in range(1, 5):
                q_start, q_end = get_quarter_range(year, q)
                q_filters = FinancialReport.build_filters(start=q_start, end=q_end, entity=entity, hierarchy=hierarchy)
                q_stmt = select(GeneralLedger).where(q_filters)
                q_entries = (await db.execute(q_stmt)).scalars().all()
                quarterly_data[f"Q{q}"] = build_aging(q_entries, q_end)
            result["quarterly"] = quarterly_data

        return result
    @staticmethod
    async def get_operation_expense_report(
        db: AsyncSession,
        period: str,
        year: Optional[int],
        start_date: Optional[datetime],
        end_date: Optional[datetime],
        hierarchy: Optional[Any] = None,  # Expected to be a list/set of allowed departments
        entity: Optional[str] = None
    ) -> Dict[str, Any]:
        today = datetime.today()

        # Get start and end dates according to your FinancialReport method
        start, end = FinancialReport.get_date_range(period, year, start_date, end_date)
        logger.info(f"Fetching operational expense report | Period: {period}, Year: {year}, Start: {start}, End: {end}")

        # Base filters for date range and entity
        filters = FinancialReport.build_filters(start=start, end=end, entity=entity)

        # Add hierarchy filter if provided
        if hierarchy:
            # Make sure hierarchy is a list/set of lowercase department names for consistency
            hierarchy_lower = {d.lower() for d in hierarchy}
            # Add filter to check department in hierarchy list from JSONB field
            filters.append(
                func.lower(func.jsonb_extract_path_text(GeneralLedger.extra_data, 'department')).in_(hierarchy_lower)
            )

        # Debug: fetch some raw GL entries in this date range and hierarchy
        stmt_debug = (
            select(
                GeneralLedger.date,
                GeneralLedger.debit,
                GeneralLedger.credit,
                GeneralLedger.extra_data
            )
            .where(and_(*filters))
            .limit(5)
        )
        debug_result = await db.execute(stmt_debug)
        debug_rows = debug_result.all()
        logger.info("Sample GeneralLedger entries in date range with hierarchy filter:")
        for row in debug_rows:
            logger.info(f"Date: {row.date}, Debit: {row.debit}, Credit: {row.credit}, Extra Data: {row.extra_data}")

        # Add debit > 0 filter for actual expenses query
        actual_filters = list(filters)
        actual_filters.append(GeneralLedger.debit > 0)

        # Query actual expenses grouped by account_name and department (json field)
        stmt_actuals = (
            select(
                GeneralLedger.account_name,
                func.coalesce(
                    func.lower(
                        func.jsonb_extract_path_text(GeneralLedger.extra_data, 'department')
                    ),
                    text("'unknown'")
                ).label("department"),
                func.sum(GeneralLedger.debit).label("total_debit")
            )
            .where(and_(*actual_filters))
            .group_by(GeneralLedger.account_name, "department")
        )
        actuals_result = await db.execute(stmt_actuals)
        actuals_rows = actuals_result.all()
        logger.info(f"Fetched {len(actuals_rows)} actual expense rows from ledger")

        actuals = {}
        for row in actuals_rows:
            acct = row.account_name.lower() if row.account_name else "unknown_account"
            dept = row.department.lower() if row.department else "unknown"
            key = f"{acct}_{dept}"
            actuals[key] = Decimal(row.total_debit or 0)

        logger.debug(f"Sample actuals data: {dict(list(actuals.items())[:3])}")

        # Fetch master expense accounts
        stmt_master = (
            select(
                GeneralLedgerMaster.account_name,
                GeneralLedgerMaster.category
            )
            .where(GeneralLedgerMaster.category == "expense")
        )
        master_result = await db.execute(stmt_master)
        master_data = master_result.all()
        logger.info(f"Loaded {len(master_data)} expense accounts from GeneralLedgerMaster")

        # Placeholder for budgets: implement your budget logic here, e.g., fetch from DB
        budget_department_map = {}  # Example: {"account_department": {"budget": Decimal("1000.00")}, ...}

        response = {}
        total_actual = Decimal("0.0")
        total_budget = Decimal("0.0")
        categories_over_budget = 0
        favorable_categories = 0
        unfavorable_categories = 0

        for row in master_data:
            acct = row.account_name.lower()
            category = row.category
            matching_keys = [k for k in actuals if k.startswith(f"{acct}_")]

            if not matching_keys:
                key = f"{acct}_unknown"
                actual = Decimal("0.0")
                budget = Decimal("0.0")
                department = "Unknown"
                variance = budget - actual
                response[key] = {
                    "category": category,
                    "actual": float(round(actual, 2)),
                    "budget": float(round(budget, 2)),
                    "variance": float(round(variance, 2)),
                    "department": department
                }
                favorable_categories += 1
                total_actual += actual
                total_budget += budget
                continue

            for key in matching_keys:
                actual = actuals.get(key, Decimal("0.0"))
                budget = budget_department_map.get(key, {}).get("budget", Decimal("0.0"))
                department = key.split("_", 1)[1]

                variance = budget - actual

                if actual > budget:
                    categories_over_budget += 1
                    unfavorable_categories += 1
                else:
                    favorable_categories += 1

                response[key] = {
                    "category": category,
                    "actual": float(round(actual, 2)),
                    "budget": float(round(budget, 2)),
                    "variance": float(round(variance, 2)),
                    "department": department.title() if department != "unknown" else "Unknown"
                }
                total_actual += actual
                total_budget += budget

        overall_variance = total_budget - total_actual

        logger.info(f"Total Actual Spend: ${total_actual:,.2f}")
        logger.info(f"Total Budget: ${total_budget:,.2f}")
        logger.info(f"Overall Variance: ${overall_variance:,.2f}")
        logger.info(f"Over Budget Categories: {categories_over_budget}")
        logger.info(f"Favorable Categories: {favorable_categories}, Unfavorable Categories: {unfavorable_categories}")

        key_metrics = {
            "Total_Budget": f"${total_budget:,.2f}",
            "Total_Actual_Spend": f"${total_actual:,.2f}",
            "Overall_Variance": f"${overall_variance:,.2f}",
            "Categories_Over_Budget": categories_over_budget,
            "Favorable_Categories": favorable_categories,
            "Unfavorable_Categories": unfavorable_categories
        }

        return {
            "category_summary": response,
            "key_metrics": key_metrics
        }
        
