"""
Dimensional Financial Reports Service

A comprehensive service for generating financial reports with dimensional analysis
including Cost Centers, Projects, and other business dimensions.

Supports:
- Profit & Loss with dimensional filtering
- Balance Sheet with dimensional breakdowns
- General Ledger with dimensional analysis
- Debtors/Creditors dimensional analysis
- Sales/Purchase reports with dimensions
- Comparative period reporting
"""

from datetime import date, datetime, timedelta
from decimal import Decimal
from typing import Dict, List, Optional, Any, Union
from sqlalchemy.orm import Session, joinedload
from sqlalchemy import and_, or_, func, extract, text
from collections import defaultdict

from app.models.accounting import AccountingCode, JournalEntry, AccountingEntry
from app.models.accounting_dimensions import (
    AccountingDimension, AccountingDimensionValue, DimensionType, AccountingDimensionAssignment
)
from app.models.accounting_code_dimensions import AccountingCodeDimensionRequirement
from app.core.database import get_db
from app.models.inventory import COGSEntry, Product
from app.models.sales import Sale, SaleItem


class DimensionalReportsService:
    """Service for generating dimensional financial reports"""

    def __init__(self, db: Session):
        self.db = db

    def get_dimension_filters(self, dimension_filters: Dict[str, str] = None) -> Dict[str, Any]:
        """Build dimension filter conditions for SQL queries"""
        filters = {}

        if not dimension_filters:
            return filters

        for dimension_type, dimension_value_id in dimension_filters.items():
            if dimension_value_id:
                filters[dimension_type] = dimension_value_id

        return filters

    def apply_dimensional_filters(self, query, dimension_filters: Dict[str, str] = None):
        """Apply dimensional filters to a SQLAlchemy query"""
        if not dimension_filters:
            return query

        # Join with dimensional tables if filters are provided
        for dimension_type, dimension_value_id in dimension_filters.items():
            if dimension_value_id:
                # This would need to be implemented based on your transaction model
                # For now, returning the original query
                pass

        return query

    def get_dimensional_profit_loss(
        self,
        start_date: date,
        end_date: date,
        dimension_filters: Dict[str, str] = None,
        comparison_period: bool = False,
        comparison_start_date: date = None,
        comparison_end_date: date = None,
        group_by_dimensions: bool = True
    ) -> Dict[str, Any]:
        """
        Generate Profit & Loss statement with dimensional analysis

        Args:
            start_date: Report period start
            end_date: Report period end
            dimension_filters: Dict of dimension_type -> dimension_value_id
            comparison_period: Include comparative period
            comparison_start_date: Comparison period start
            comparison_end_date: Comparison period end
            group_by_dimensions: Group results by dimensions
        """

        # Base query for revenue accounts
        revenue_query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            AccountingCode.account_type,
            AccountingCode.category,
            func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount).label('amount')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            AccountingCode.account_type == 'Revenue',
            JournalEntry.date.between(start_date, end_date)
        ).group_by(
            AccountingCode.id, AccountingCode.code, AccountingCode.name,
            AccountingCode.account_type, AccountingCode.category
        )

        # Apply dimensional filters
        revenue_query = self.apply_dimensional_filters(revenue_query, dimension_filters)
        revenue_accounts = revenue_query.all()

        # Base query for expense accounts
        expense_query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            AccountingCode.account_type,
            AccountingCode.category,
            func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount).label('amount')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            AccountingCode.account_type == 'Expense',
            JournalEntry.date.between(start_date, end_date)
        ).group_by(
            AccountingCode.id, AccountingCode.code, AccountingCode.name,
            AccountingCode.account_type, AccountingCode.category
        )

        # Apply dimensional filters
        expense_query = self.apply_dimensional_filters(expense_query, dimension_filters)
        expense_accounts = expense_query.all()

        # Calculate totals
        total_revenue = sum(float(acc.amount or 0) for acc in revenue_accounts)
        total_expenses = sum(float(acc.amount or 0) for acc in expense_accounts)
        net_income = total_revenue - total_expenses

        # Group by categories for better presentation
        revenue_by_category = defaultdict(list)
        for acc in revenue_accounts:
            revenue_by_category[acc.category or 'Other Revenue'].append({
                'code': acc.code,
                'name': acc.name,
                'amount': float(acc.amount or 0)
            })

        expense_by_category = defaultdict(list)
        for acc in expense_accounts:
            expense_by_category[acc.category or 'Other Expenses'].append({
                'code': acc.code,
                'name': acc.name,
                'amount': float(acc.amount or 0)
            })

        # Optional dimensional breakdown
        dimensional_breakdown: List[Dict[str, Any]] = []
        if group_by_dimensions:
            # Revenue by dimension
            rev_dim_query = self.db.query(
                AccountingDimension.dimension_type.label('dimension_type'),
                AccountingDimensionValue.id.label('dimension_value_id'),
                AccountingDimensionValue.code.label('dimension_code'),
                AccountingDimensionValue.name.label('dimension_name'),
                func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount).label('amount')
            ).select_from(AccountingCode).join(
                JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
            ).join(
                AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
            ).join(
                AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
            ).join(
                AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
            ).filter(
                AccountingCode.account_type == 'Revenue',
                JournalEntry.date.between(start_date, end_date)
            ).group_by(
                AccountingDimension.dimension_type,
                AccountingDimensionValue.id,
                AccountingDimensionValue.code,
                AccountingDimensionValue.name
            )

            exp_dim_query = self.db.query(
                AccountingDimension.dimension_type.label('dimension_type'),
                AccountingDimensionValue.id.label('dimension_value_id'),
                AccountingDimensionValue.code.label('dimension_code'),
                AccountingDimensionValue.name.label('dimension_name'),
                func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount).label('amount')
            ).select_from(AccountingCode).join(
                JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
            ).join(
                AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
            ).join(
                AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
            ).join(
                AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
            ).filter(
                AccountingCode.account_type == 'Expense',
                JournalEntry.date.between(start_date, end_date)
            ).group_by(
                AccountingDimension.dimension_type,
                AccountingDimensionValue.id,
                AccountingDimensionValue.code,
                AccountingDimensionValue.name
            )

            # Apply dimensional filters if provided by limiting assignment dimension_value_id against filters
            # Here, if filters specify a particular dimension type key and value code/id, we can restrict.
            # For simplicity, we rely on upstream filters in queries (not implemented); keep base grouping.

            revenue_dims = rev_dim_query.all()
            expense_dims = exp_dim_query.all()

            # Combine revenue and expense per (dimension_type, dimension_value_id)
            breakdown_map: Dict[tuple, Dict[str, Any]] = {}
            for r in revenue_dims:
                key = (r.dimension_type, r.dimension_value_id)
                breakdown_map.setdefault(key, {
                    'dimension_type': r.dimension_type,
                    'dimension_value': r.dimension_name,
                    'revenue': 0.0,
                    'expenses': 0.0
                })
                breakdown_map[key]['revenue'] += float(r.amount or 0)

            for e in expense_dims:
                key = (e.dimension_type, e.dimension_value_id)
                breakdown_map.setdefault(key, {
                    'dimension_type': e.dimension_type,
                    'dimension_value': e.dimension_name,
                    'revenue': 0.0,
                    'expenses': 0.0
                })
                breakdown_map[key]['expenses'] += float(e.amount or 0)

            for _, item in breakdown_map.items():
                item['net_profit'] = item['revenue'] - item['expenses']
                dimensional_breakdown.append(item)

        result = {
            'report_type': 'profit_loss',
            'period': {
                'start_date': start_date.isoformat(),
                'end_date': end_date.isoformat()
            },
            'dimension_filters': dimension_filters or {},
            'revenue': {
                'categories': dict(revenue_by_category),
                'total': total_revenue
            },
            'expenses': {
                'categories': dict(expense_by_category),
                'total': total_expenses
            },
            'net_income': net_income,
            'dimensional_breakdown': dimensional_breakdown,
            'generated_at': datetime.now().isoformat()
        }

        # Add comparison period if requested
        if comparison_period and comparison_start_date and comparison_end_date:
            comparison_data = self.get_dimensional_profit_loss(
                comparison_start_date, comparison_end_date, dimension_filters,
                comparison_period=False, group_by_dimensions=group_by_dimensions
            )
            result['comparison_period'] = comparison_data

            # Add variance analysis
            result['variance'] = {
                'revenue': total_revenue - comparison_data['revenue']['total'],
                'expenses': total_expenses - comparison_data['expenses']['total'],
                'net_income': net_income - comparison_data['net_income']
            }

        return result

    def get_dimensional_balance_sheet(
        self,
        as_of_date: date = None,
        dimension_filters: Dict[str, str] = None,
        comparison_date: date = None,
        group_by_dimensions: bool = True
    ) -> Dict[str, Any]:
        """
        Generate Balance Sheet with dimensional analysis
        """
        if as_of_date is None:
            as_of_date = date.today()

        # Query for asset accounts
        asset_query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            AccountingCode.account_type,
            AccountingCode.category,
            func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount).label('balance')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            AccountingCode.account_type == 'Asset',
            JournalEntry.date <= as_of_date
        ).group_by(
            AccountingCode.id, AccountingCode.code, AccountingCode.name,
            AccountingCode.account_type, AccountingCode.category
        )

        asset_query = self.apply_dimensional_filters(asset_query, dimension_filters)
        asset_accounts = asset_query.all()

        # Query for liability accounts
        liability_query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            AccountingCode.account_type,
            AccountingCode.category,
            func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount).label('balance')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            AccountingCode.account_type == 'Liability',
            JournalEntry.date <= as_of_date
        ).group_by(
            AccountingCode.id, AccountingCode.code, AccountingCode.name,
            AccountingCode.account_type, AccountingCode.category
        )

        liability_query = self.apply_dimensional_filters(liability_query, dimension_filters)
        liability_accounts = liability_query.all()

        # Query for equity accounts
        equity_query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            AccountingCode.account_type,
            AccountingCode.category,
            func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount).label('balance')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            AccountingCode.account_type == 'Equity',
            JournalEntry.date <= as_of_date
        ).group_by(
            AccountingCode.id, AccountingCode.code, AccountingCode.name,
            AccountingCode.account_type, AccountingCode.category
        )

        equity_query = self.apply_dimensional_filters(equity_query, dimension_filters)
        equity_accounts = equity_query.all()

        dimensional_breakdown: List[Dict[str, Any]] = []
        if group_by_dimensions:
            # Assets by dimension
            assets_dim_q = self.db.query(
                AccountingDimension.dimension_type.label('dimension_type'),
                AccountingDimensionValue.id.label('dimension_value_id'),
                AccountingDimensionValue.name.label('dimension_name'),
                func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount).label('balance')
            ).select_from(AccountingCode).join(
                JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
            ).join(
                AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
            ).join(
                AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
            ).join(
                AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
            ).filter(
                AccountingCode.account_type == 'Asset',
                JournalEntry.date <= as_of_date
            ).group_by(
                AccountingDimension.dimension_type,
                AccountingDimensionValue.id,
                AccountingDimensionValue.name
            )

            liabilities_dim_q = self.db.query(
                AccountingDimension.dimension_type.label('dimension_type'),
                AccountingDimensionValue.id.label('dimension_value_id'),
                AccountingDimensionValue.name.label('dimension_name'),
                func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount).label('balance')
            ).select_from(AccountingCode).join(
                JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
            ).join(
                AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
            ).join(
                AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
            ).join(
                AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
            ).filter(
                AccountingCode.account_type == 'Liability',
                JournalEntry.date <= as_of_date
            ).group_by(
                AccountingDimension.dimension_type,
                AccountingDimensionValue.id,
                AccountingDimensionValue.name
            )

            equity_dim_q = self.db.query(
                AccountingDimension.dimension_type.label('dimension_type'),
                AccountingDimensionValue.id.label('dimension_value_id'),
                AccountingDimensionValue.name.label('dimension_name'),
                func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount).label('balance')
            ).select_from(AccountingCode).join(
                JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
            ).join(
                AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
            ).join(
                AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
            ).join(
                AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
            ).filter(
                AccountingCode.account_type == 'Equity',
                JournalEntry.date <= as_of_date
            ).group_by(
                AccountingDimension.dimension_type,
                AccountingDimensionValue.id,
                AccountingDimensionValue.name
            )

            assets_dim = assets_dim_q.all()
            liabilities_dim = liabilities_dim_q.all()
            equity_dim = equity_dim_q.all()

            breakdown_map: Dict[tuple, Dict[str, Any]] = {}
            for a in assets_dim:
                key = (a.dimension_type, a.dimension_value_id)
                breakdown_map.setdefault(key, {
                    'dimension_type': a.dimension_type,
                    'dimension_value': a.dimension_name,
                    'total_assets': 0.0,
                    'total_liabilities': 0.0,
                    'total_equity': 0.0
                })
                breakdown_map[key]['total_assets'] += float(a.balance or 0)

            for l in liabilities_dim:
                key = (l.dimension_type, l.dimension_value_id)
                breakdown_map.setdefault(key, {
                    'dimension_type': l.dimension_type,
                    'dimension_value': l.dimension_name,
                    'total_assets': 0.0,
                    'total_liabilities': 0.0,
                    'total_equity': 0.0
                })
                breakdown_map[key]['total_liabilities'] += float(l.balance or 0)

            for e in equity_dim:
                key = (e.dimension_type, e.dimension_value_id)
                breakdown_map.setdefault(key, {
                    'dimension_type': e.dimension_type,
                    'dimension_value': e.dimension_name,
                    'total_assets': 0.0,
                    'total_liabilities': 0.0,
                    'total_equity': 0.0
                })
                breakdown_map[key]['total_equity'] += float(e.balance or 0)

            dimensional_breakdown = list(breakdown_map.values())

        # Group by categories
        assets_by_category = defaultdict(list)
        for acc in asset_accounts:
            assets_by_category[acc.category or 'Other Assets'].append({
                'code': acc.code,
                'name': acc.name,
                'balance': float(acc.balance or 0)
            })

        liabilities_by_category = defaultdict(list)
        for acc in liability_accounts:
            liabilities_by_category[acc.category or 'Other Liabilities'].append({
                'code': acc.code,
                'name': acc.name,
                'balance': float(acc.balance or 0)
            })

        equity_by_category = defaultdict(list)
        for acc in equity_accounts:
            equity_by_category[acc.category or 'Other Equity'].append({
                'code': acc.code,
                'name': acc.name,
                'balance': float(acc.balance or 0)
            })

        total_assets = sum(float(acc.balance or 0) for acc in asset_accounts)
        total_liabilities = sum(float(acc.balance or 0) for acc in liability_accounts)
        total_equity = sum(float(acc.balance or 0) for acc in equity_accounts)

        return {
            'report_type': 'balance_sheet',
            'as_of_date': as_of_date.isoformat(),
            'dimension_filters': dimension_filters or {},
            'assets': {
                'categories': dict(assets_by_category),
                'total': total_assets
            },
            'liabilities': {
                'categories': dict(liabilities_by_category),
                'total': total_liabilities
            },
            'equity': {
                'categories': dict(equity_by_category),
                'total': total_equity
            },
            'total_liabilities_and_equity': total_liabilities + total_equity,
            'balance_check': abs(total_assets - (total_liabilities + total_equity)) < 0.01,
            'dimensional_breakdown': dimensional_breakdown,
            'generated_at': datetime.now().isoformat()
        }

    def get_dimensional_general_ledger(
        self,
        start_date: date,
        end_date: date,
        account_codes: List[str] = None,
        dimension_filters: Dict[str, str] = None,
        group_by_dimensions: bool = True
    ) -> Dict[str, Any]:
        """
        Generate General Ledger with dimensional analysis
        """

        query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            AccountingCode.account_type,
            JournalEntry.id.label('journal_entry_id'),
            JournalEntry.date,
            JournalEntry.description,
            JournalEntry.reference,
            JournalEntry.debit_amount,
            JournalEntry.credit_amount,
            JournalEntry.description.label('entry_description')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            JournalEntry.date.between(start_date, end_date)
        )

        # Filter by specific accounts if provided
        if account_codes:
            query = query.filter(AccountingCode.code.in_(account_codes))

        # Apply dimensional filters
        query = self.apply_dimensional_filters(query, dimension_filters)

        # Order by account code and date
        query = query.order_by(AccountingCode.code, JournalEntry.date)

        entries = query.all()

        # Build dimension assignments map per journal entry
        je_ids = {e.journal_entry_id for e in entries}
        dim_map: Dict[str, List[Dict[str, Any]]] = {}
        if je_ids:
            assignments = self.db.query(
                AccountingDimensionAssignment.journal_entry_id,
                AccountingDimension.dimension_type,
                AccountingDimensionValue.name
            ).join(
                AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
            ).join(
                AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
            ).filter(
                AccountingDimensionAssignment.journal_entry_id.in_(je_ids)
            ).all()

            for a in assignments:
                dim_map.setdefault(a.journal_entry_id, []).append({
                    'type': a.dimension_type,
                    'value': a.name
                })

        # Group entries by account
        accounts = defaultdict(lambda: {
            'name': '',
            'account_type': '',
            'entries': [],
            'running_balance': 0.0,
            'total_debits': 0.0,
            'total_credits': 0.0
        })

        for entry in entries:
            account_code = entry.code

            if not accounts[account_code]['name']:
                accounts[account_code]['name'] = entry.name
                accounts[account_code]['account_type'] = entry.account_type

            debit = float(entry.debit_amount or 0)
            credit = float(entry.credit_amount or 0)

            # Update running balance based on account type
            if entry.account_type in ['Asset', 'Expense']:
                balance_change = debit - credit
            else:  # Liability, Equity, Revenue
                balance_change = credit - debit

            accounts[account_code]['running_balance'] += balance_change
            accounts[account_code]['total_debits'] += debit
            accounts[account_code]['total_credits'] += credit

            accounts[account_code]['entries'].append({
                'date': entry.date.isoformat(),
                'description': entry.description,
                'reference': entry.reference,
                'entry_description': entry.entry_description,
                'debit': debit,
                'credit': credit,
                'balance': accounts[account_code]['running_balance'],
                'dimensions': dim_map.get(entry.journal_entry_id, [])
            })

        return {
            'report_type': 'general_ledger',
            'period': {
                'start_date': start_date.isoformat(),
                'end_date': end_date.isoformat()
            },
            'dimension_filters': dimension_filters or {},
            'account_filters': account_codes or [],
            'accounts': dict(accounts),
            'generated_at': datetime.now().isoformat()
        }

    def get_debtors_dimensional_analysis(
        self,
        as_of_date: date = None,
        dimension_filters: Dict[str, str] = None,
        aging_buckets: List[int] = [30, 60, 90, 120]
    ) -> Dict[str, Any]:
        """
        Generate Debtors (Accounts Receivable) analysis with dimensional breakdown
        """
        if as_of_date is None:
            as_of_date = date.today()

        # Query for receivable accounts
        receivables_query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            JournalEntry.date,
            JournalEntry.reference,
            JournalEntry.description,
            func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount).label('balance')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            AccountingCode.category.ilike('%receivable%'),
            JournalEntry.date <= as_of_date
        ).group_by(
            AccountingCode.code, AccountingCode.name,
            JournalEntry.id, JournalEntry.date,
            JournalEntry.reference, JournalEntry.description
        ).having(
            func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount) > 0
        )

        receivables_query = self.apply_dimensional_filters(receivables_query, dimension_filters)
        receivables_query = receivables_query.join(
            AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
        ).join(
            AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
        ).join(
            AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
        )

        receivables = receivables_query.with_entities(
            AccountingCode.code,
            AccountingCode.name,
            JournalEntry.date,
            JournalEntry.reference,
            JournalEntry.description,
            AccountingDimension.dimension_type.label('dimension_type'),
            AccountingDimensionValue.name.label('dimension_name'),
            func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount).label('balance')
        ).group_by(
            AccountingCode.code,
            AccountingCode.name,
            JournalEntry.id,
            JournalEntry.date,
            JournalEntry.reference,
            JournalEntry.description,
            AccountingDimension.dimension_type,
            AccountingDimensionValue.name
        ).all()

        # Calculate aging
        aging_analysis = {
            'current': [],
            '1-30_days': [],
            '31-60_days': [],
            '61-90_days': [],
            '91-120_days': [],
            'over_120_days': []
        }

        total_outstanding = 0.0
        dimensional_map: Dict[tuple, Dict[str, Any]] = {}

        for receivable in receivables:
            days_outstanding = (as_of_date - receivable.date).days
            balance = float(receivable.balance or 0)
            total_outstanding += balance

            item = {
                'account_code': receivable.code,
                'account_name': receivable.name,
                'reference': receivable.reference,
                'description': receivable.description,
                'transaction_date': receivable.date.isoformat(),
                'days_outstanding': days_outstanding,
                'balance': balance
            }

            if days_outstanding <= 0:
                aging_analysis['current'].append(item)
            elif days_outstanding <= 30:
                aging_analysis['1-30_days'].append(item)
            elif days_outstanding <= 60:
                aging_analysis['31-60_days'].append(item)
            elif days_outstanding <= 90:
                aging_analysis['61-90_days'].append(item)
            elif days_outstanding <= 120:
                aging_analysis['91-120_days'].append(item)
            else:
                aging_analysis['over_120_days'].append(item)

        # Calculate totals for each bucket
        aging_totals = {}
        for bucket, items in aging_analysis.items():
            aging_totals[bucket] = sum(item['balance'] for item in items)

        # Build dimensional breakdown rows
        for recv in receivables:
            days_outstanding = (as_of_date - recv.date).days
            balance = float(recv.balance or 0)
            key = (recv.dimension_type, recv.dimension_name)
            dimensional_map.setdefault(key, {
                'dimension_type': recv.dimension_type,
                'dimension_value': recv.dimension_name,
                'current': 0.0,
                'days_30': 0.0,
                'days_60': 0.0,
                'days_90_plus': 0.0,
                'total': 0.0
            })
            row = dimensional_map[key]
            if days_outstanding <= 0:
                row['current'] += balance
            elif days_outstanding <= 30:
                row['days_30'] += balance
            elif days_outstanding <= 60:
                row['days_60'] += balance
            else:
                row['days_90_plus'] += balance
            row['total'] += balance

        dimensional_breakdown = list(dimensional_map.values())

        return {
            'report_type': 'debtors_analysis',
            'as_of_date': as_of_date.isoformat(),
            'dimension_filters': dimension_filters or {},
            'aging_analysis': aging_analysis,
            'aging_totals': aging_totals,
            'total_outstanding': total_outstanding,
            'dimensional_breakdown': dimensional_breakdown,
            'generated_at': datetime.now().isoformat()
        }

    def get_creditors_dimensional_analysis(
        self,
        as_of_date: date = None,
        dimension_filters: Dict[str, str] = None,
        aging_buckets: List[int] = [30, 60, 90, 120]
    ) -> Dict[str, Any]:
        """
        Generate Creditors (Accounts Payable) analysis with dimensional breakdown
        """
        if as_of_date is None:
            as_of_date = date.today()

        # Query for payable accounts
        payables_query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            JournalEntry.date,
            JournalEntry.reference,
            JournalEntry.description,
            func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount).label('balance')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            AccountingCode.category.ilike('%payable%'),
            JournalEntry.date <= as_of_date
        ).group_by(
            AccountingCode.code, AccountingCode.name,
            JournalEntry.id, JournalEntry.date,
            JournalEntry.reference, JournalEntry.description
        ).having(
            func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount) > 0
        )

        payables_query = self.apply_dimensional_filters(payables_query, dimension_filters)
        payables_query = payables_query.join(
            AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
        ).join(
            AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
        ).join(
            AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
        )

        payables = payables_query.with_entities(
            AccountingCode.code,
            AccountingCode.name,
            JournalEntry.date,
            JournalEntry.reference,
            JournalEntry.description,
            AccountingDimension.dimension_type.label('dimension_type'),
            AccountingDimensionValue.name.label('dimension_name'),
            func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount).label('balance')
        ).group_by(
            AccountingCode.code,
            AccountingCode.name,
            JournalEntry.id,
            JournalEntry.date,
            JournalEntry.reference,
            JournalEntry.description,
            AccountingDimension.dimension_type,
            AccountingDimensionValue.name
        ).all()

        # Calculate aging (similar to debtors but for payables)
        aging_analysis = {
            'current': [],
            '1-30_days': [],
            '31-60_days': [],
            '61-90_days': [],
            '91-120_days': [],
            'over_120_days': []
        }

        total_outstanding = 0.0
        dimensional_map: Dict[tuple, Dict[str, Any]] = {}

        for payable in payables:
            days_outstanding = (as_of_date - payable.date).days
            balance = float(payable.balance or 0)
            total_outstanding += balance

            item = {
                'account_code': payable.code,
                'account_name': payable.name,
                'reference': payable.reference,
                'description': payable.description,
                'transaction_date': payable.date.isoformat(),
                'days_outstanding': days_outstanding,
                'balance': balance
            }

            if days_outstanding <= 0:
                aging_analysis['current'].append(item)
            elif days_outstanding <= 30:
                aging_analysis['1-30_days'].append(item)
            elif days_outstanding <= 60:
                aging_analysis['31-60_days'].append(item)
            elif days_outstanding <= 90:
                aging_analysis['61-90_days'].append(item)
            elif days_outstanding <= 120:
                aging_analysis['91-120_days'].append(item)
            else:
                aging_analysis['over_120_days'].append(item)

        # Calculate totals for each bucket
        aging_totals = {}
        for bucket, items in aging_analysis.items():
            aging_totals[bucket] = sum(item['balance'] for item in items)

        # Build dimensional breakdown rows
        for pay in payables:
            days_outstanding = (as_of_date - pay.date).days
            balance = float(pay.balance or 0)
            key = (pay.dimension_type, pay.dimension_name)
            dimensional_map.setdefault(key, {
                'dimension_type': pay.dimension_type,
                'dimension_value': pay.dimension_name,
                'current': 0.0,
                'days_30': 0.0,
                'days_60': 0.0,
                'days_90_plus': 0.0,
                'total': 0.0
            })
            row = dimensional_map[key]
            if days_outstanding <= 0:
                row['current'] += balance
            elif days_outstanding <= 30:
                row['days_30'] += balance
            elif days_outstanding <= 60:
                row['days_60'] += balance
            else:
                row['days_90_plus'] += balance
            row['total'] += balance

        dimensional_breakdown = list(dimensional_map.values())

        return {
            'report_type': 'creditors_analysis',
            'as_of_date': as_of_date.isoformat(),
            'dimension_filters': dimension_filters or {},
            'aging_analysis': aging_analysis,
            'aging_totals': aging_totals,
            'total_outstanding': total_outstanding,
            'dimensional_breakdown': dimensional_breakdown,
            'generated_at': datetime.now().isoformat()
        }

    def get_sales_dimensional_analysis(
        self,
        start_date: date,
        end_date: date,
        dimension_filters: Dict[str, str] = None,
        include_sale_items_cogs: bool = True,
        include_direct_cogs: bool = True,
        include_journal_cogs: bool = True,
        group_by_period: str = 'month'  # 'day', 'week', 'month', 'quarter'
    ) -> Dict[str, Any]:
        """
        Generate Sales analysis with dimensional breakdown
        """

        # Query for sales/revenue transactions
        sales_query = (
            self.db.query(
                AccountingCode.code,
                AccountingCode.name,
                AccountingCode.category,
                JournalEntry.date,
                JournalEntry.date,
                JournalEntry.description,
                func.sum(JournalEntry.credit_amount).label('sales_amount')
            )
            .select_from(JournalEntry)
            .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
            .filter(
                AccountingCode.account_type == 'Revenue',
                JournalEntry.date.between(start_date, end_date)
            )
            .group_by(
                AccountingCode.code,
                AccountingCode.name,
                AccountingCode.category,
                JournalEntry.date,
                JournalEntry.reference,
                JournalEntry.description
            )
        )

        sales_query = self.apply_dimensional_filters(sales_query, dimension_filters)
        sales_data = sales_query.all()

        # Group by time periods
        period_sales = defaultdict(lambda: {
            'total_sales': 0.0,
            'transactions': []
        })

        category_sales = defaultdict(float)
        total_sales = 0.0

        for sale in sales_data:
            amount = float(sale.sales_amount or 0)
            total_sales += amount

            # Group by category
            category_sales[sale.category or 'Other Sales'] += amount

            # Group by time period
            if group_by_period == 'day':
                period_key = sale.date.isoformat()
            elif group_by_period == 'week':
                # Get start of week
                start_of_week = sale.date - timedelta(days=sale.date.weekday())
                period_key = start_of_week.isoformat()
            elif group_by_period == 'month':
                period_key = sale.date.strftime('%Y-%m')
            elif group_by_period == 'quarter':
                quarter = (sale.date.month - 1) // 3 + 1
                period_key = f"{sale.date.year}-Q{quarter}"
            else:
                period_key = sale.date.isoformat()

            period_sales[period_key]['total_sales'] += amount
            period_sales[period_key]['transactions'].append({
                'account_code': sale.code,
                'account_name': sale.name,
                'category': sale.category,
                'date': sale.date.isoformat(),
                'reference': sale.reference,
                'description': sale.description,
                'amount': amount
            })

        # Dimensional breakdown:
        # Build revenue map keyed by (dimension_type, dimension_value) using Sale + SaleItem dimensions
        revenue_map: Dict[tuple, float] = {}
        items_rev_q = (
            self.db.query(SaleItem, Sale)
            .select_from(SaleItem)
            .join(Sale, SaleItem.sale_id == Sale.id)
            .filter(Sale.date.between(start_date, end_date))
        )
        for item, sale in items_rev_q.all():
            qty = float(item.quantity or 0)
            price = float(item.selling_price or 0)
            total = float(item.total_amount or (qty * price))
            for dim_value_id in [sale.cost_center_id, sale.project_id, sale.department_id]:
                info = resolve_dim_value(dim_value_id)
                if not info:
                    continue
                key = (info['dimension_type'], info['dimension_name'])
                revenue_map[key] = revenue_map.get(key, 0.0) + total

        # Fallback: revenue by dimension from journal entries with dimension assignments
        rev_dim_q = (
            self.db.query(
                AccountingDimension.dimension_type.label('dimension_type'),
                AccountingDimensionValue.name.label('dimension_name'),
                func.sum(JournalEntry.credit_amount).label('revenue')
            )
            .select_from(JournalEntry)
            .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
            .join(AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id)
            .join(AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id)
            .join(AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id)
            .filter(
                AccountingCode.account_type == 'Revenue',
                JournalEntry.date.between(start_date, end_date)
            )
            .group_by(AccountingDimension.dimension_type, AccountingDimensionValue.name)
        )

        for row in rev_dim_q.all():
            key = (row.dimension_type, row.dimension_name)
            revenue_map[key] = revenue_map.get(key, 0.0) + float(row.revenue or 0)

        # Helper cache to resolve dimension value id -> (dimension_type, dimension_name)
        dim_value_cache: Dict[str, Dict[str, str]] = {}
        def resolve_dim_value(dim_value_id: Optional[str]) -> Optional[Dict[str, str]]:
            if not dim_value_id:
                return None
            if dim_value_id in dim_value_cache:
                return dim_value_cache[dim_value_id]
            row = (
                self.db.query(AccountingDimension.dimension_type, AccountingDimensionValue.name)
                .join(AccountingDimension, AccountingDimension.id == AccountingDimensionValue.dimension_id)
                .filter(AccountingDimensionValue.id == dim_value_id)
                .first()
            )
            if not row:
                dim_value_cache[dim_value_id] = None  # cache miss
                return None
            info = {'dimension_type': row[0], 'dimension_name': row[1]}
            dim_value_cache[dim_value_id] = info
            return info

        # Compute COGS per dimension from SaleItems (using Sale's dimension assignments)
        cogs_map: Dict[tuple, Dict[str, float]] = {}
        if include_sale_items_cogs:
            items_query = (
                self.db.query(SaleItem, Sale)
                .select_from(SaleItem)
                .join(Sale, SaleItem.sale_id == Sale.id)
                .filter(Sale.date.between(start_date, end_date))
            )
            for item, sale in items_query.all():
                qty = float(item.quantity or 0)
                unit_cost = float(item.cost_price or 0)
                item_cogs = qty * unit_cost
                # Consider up to three sale-level dimensions
                for dim_value_id in [sale.cost_center_id, sale.project_id, sale.department_id]:
                    info = resolve_dim_value(dim_value_id)
                    if not info:
                        continue
                    key = (info['dimension_type'], info['dimension_name'])
                    entry = cogs_map.setdefault(key, {'cost': 0.0, 'units_sold': 0.0})
                    entry['cost'] += item_cogs
                    entry['units_sold'] += qty

        # Include direct COGS entries grouped by Product default dimensions
        if include_direct_cogs:
            direct_cogs_q = (
                self.db.query(COGSEntry, Product)
                .select_from(COGSEntry)
                .join(Product, COGSEntry.product_id == Product.id)
                .filter(COGSEntry.date.between(start_date, end_date))
            )
            for cogs_entry, product in direct_cogs_q.all():
                qty = float(getattr(cogs_entry, 'quantity', 0) or 0)
                unit_cost = float(getattr(cogs_entry, 'cost', 0) or 0)
                total_cost = unit_cost * (qty if qty else 1.0)
                for dim_value_id in [getattr(product, 'cost_center_id', None), getattr(product, 'project_id', None)]:
                    info = resolve_dim_value(dim_value_id)
                    if not info:
                        continue
                    key = (info['dimension_type'], info['dimension_name'])
                    entry = cogs_map.setdefault(key, {'cost': 0.0, 'units_sold': 0.0})
                    entry['cost'] += total_cost
                    entry['units_sold'] += float(qty or 0)

        # Include journal-derived COGS grouped by dimension assignments on COGS expense accounts
        if include_journal_cogs:
            journal_cogs_q = (
                self.db.query(
                    AccountingDimension.dimension_type.label('dimension_type'),
                    AccountingDimensionValue.name.label('dimension_name'),
                    func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount).label('amount')
                )
                .select_from(JournalEntry)
                .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
                .join(AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id)
                .join(AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id)
                .join(AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id)
                .filter(
                    JournalEntry.date.between(start_date, end_date),
                    func.lower(AccountingCode.account_type) == 'expense',
                    or_(
                        func.lower(AccountingCode.category).like('%cost of sales%'),
                        func.lower(AccountingCode.category).like('%cost of goods%'),
                        func.lower(AccountingCode.name).like('%cost of sales%'),
                        func.lower(AccountingCode.name).like('%cost of goods%'),
                        func.lower(AccountingCode.name).like('%cogs%')
                    )
                )
                .group_by(AccountingDimension.dimension_type, AccountingDimensionValue.name)
            )

            for row in journal_cogs_q.all():
                key = (row.dimension_type, row.dimension_name)
                entry = cogs_map.setdefault(key, {'cost': 0.0, 'units_sold': 0.0})
                entry['cost'] += float(row.amount or 0)

        # Combine revenue and COGS into dimensional breakdown
        all_keys = set(revenue_map.keys()) | set(cogs_map.keys())
        dimensional_breakdown = []
        for key in sorted(all_keys):
            dim_type, dim_name = key
            revenue = revenue_map.get(key, 0.0)
            cogs = cogs_map.get(key, {'cost': 0.0, 'units_sold': 0.0})
            cost = cogs['cost']
            units_sold = cogs['units_sold']
            dimensional_breakdown.append({
                'dimension_type': dim_type,
                'dimension_value': dim_name,
                'units_sold': units_sold,
                'revenue': revenue,
                'cost': cost,
                'gross_profit': revenue - cost
            })

        return {
            'report_type': 'sales_analysis',
            'period': {
                'start_date': start_date.isoformat(),
                'end_date': end_date.isoformat()
            },
            'dimension_filters': dimension_filters or {},
            'group_by_period': group_by_period,
            'period_sales': dict(period_sales),
            'category_sales': dict(category_sales),
            'total_sales': total_sales,
            'dimensional_breakdown': dimensional_breakdown,
            'generated_at': datetime.now().isoformat()
        }

    def get_purchases_dimensional_analysis(
        self,
        start_date: date,
        end_date: date,
        dimension_filters: Dict[str, str] = None,
        group_by_period: str = 'month'
    ) -> Dict[str, Any]:
        """
        Generate Purchases analysis with dimensional breakdown
        """

        # Query for purchase/expense transactions
        purchases_query = self.db.query(
            AccountingCode.code,
            AccountingCode.name,
            AccountingCode.category,
            JournalEntry.date,
            JournalEntry.reference,
            JournalEntry.description,
            func.sum(JournalEntry.debit_amount).label('purchase_amount')
        ).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).filter(
            or_(
                AccountingCode.account_type == 'Expense',
                AccountingCode.category.ilike('%cost of%'),
                AccountingCode.category.ilike('%purchase%')
            ),
            JournalEntry.date.between(start_date, end_date)
        ).group_by(
            AccountingCode.code, AccountingCode.name, AccountingCode.category,
            JournalEntry.date, JournalEntry.reference, JournalEntry.description
        )

        purchases_query = self.apply_dimensional_filters(purchases_query, dimension_filters)
        purchases_data = purchases_query.all()

        # Group by time periods (similar to sales analysis)
        period_purchases = defaultdict(lambda: {
            'total_purchases': 0.0,
            'transactions': []
        })

        category_purchases = defaultdict(float)
        total_purchases = 0.0

        for purchase in purchases_data:
            amount = float(purchase.purchase_amount or 0)
            total_purchases += amount

            # Group by category
            category_purchases[purchase.category or 'Other Purchases'] += amount

            # Group by time period
            if group_by_period == 'day':
                period_key = purchase.date.isoformat()
            elif group_by_period == 'week':
                start_of_week = purchase.date - timedelta(days=purchase.date.weekday())
                period_key = start_of_week.isoformat()
            elif group_by_period == 'month':
                period_key = purchase.date.strftime('%Y-%m')
            elif group_by_period == 'quarter':
                quarter = (purchase.date.month - 1) // 3 + 1
                period_key = f"{purchase.date.year}-Q{quarter}"
            else:
                period_key = purchase.date.isoformat()

            period_purchases[period_key]['total_purchases'] += amount
            period_purchases[period_key]['transactions'].append({
                'account_code': purchase.code,
                'account_name': purchase.name,
                'category': purchase.category,
                'date': purchase.date.isoformat(),
                'reference': purchase.reference,
                'description': purchase.description,
                'amount': amount
            })

        # Dimensional breakdown for purchases (expenses/costs by dimension)
        dim_purch_q = self.db.query(
            AccountingDimension.dimension_type.label('dimension_type'),
            AccountingDimensionValue.name.label('dimension_name'),
            func.sum(JournalEntry.debit_amount).label('cost')
        ).select_from(AccountingCode).join(
            JournalEntry, AccountingCode.id == JournalEntry.accounting_code_id
        ).join(
            AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
        ).join(
            AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
        ).join(
            AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
        ).filter(
            or_(
                AccountingCode.account_type == 'Expense',
                AccountingCode.category.ilike('%cost of%'),
                AccountingCode.category.ilike('%purchase%')
            ),
            JournalEntry.date.between(start_date, end_date)
        ).group_by(
            AccountingDimension.dimension_type,
            AccountingDimensionValue.name
        )

        dim_rows = dim_purch_q.all()
        dimensional_breakdown = []
        for r in dim_rows:
            cost = float(r.cost or 0)
            dimensional_breakdown.append({
                'dimension_type': r.dimension_type,
                'dimension_value': r.dimension_name,
                'units_sold': 0,
                'revenue': 0.0,
                'cost': cost,
                'gross_profit': 0.0 - cost
            })

        return {
            'report_type': 'purchases_analysis',
            'period': {
                'start_date': start_date.isoformat(),
                'end_date': end_date.isoformat()
            },
            'dimension_filters': dimension_filters or {},
            'group_by_period': group_by_period,
            'period_purchases': dict(period_purchases),
            'category_purchases': dict(category_purchases),
            'total_purchases': total_purchases,
            'dimensional_breakdown': dimensional_breakdown,
            'generated_at': datetime.now().isoformat()
        }

    def get_available_dimensions(self) -> Dict[str, List[Dict[str, Any]]]:
        """
        Get all available dimensions and their values for filtering.

        Returns a structure compatible with the frontend expectations in
        app/static/reports.html:
        {
            'cost_centers': [...],
            'projects': [...],
            'departments': [...],
            'products': [...],
            'other_dimensions': [{ 'type': '...', 'id': ..., 'code': ..., 'name': ..., 'full_path': ... }, ...]
        }
        """
        dimensions = self.db.query(AccountingDimension).filter(
            AccountingDimension.is_active == True
        ).all()

        # Initialize well-known buckets for the UI
        result: Dict[str, Any] = {
            'cost_centers': [],
            'projects': [],
            'departments': [],
            'products': [],
            'other_dimensions': []
        }

        for dimension in dimensions:
            # dimension.dimension_type is stored as a lowercase string per DimensionType enum values
            dim_type: str = (dimension.dimension_type or '').lower()

            values = self.db.query(AccountingDimensionValue).filter(
                AccountingDimensionValue.dimension_id == dimension.id,
                AccountingDimensionValue.is_active == True
            ).all()

            value_items = [
                {
                    'id': value.id,
                    'code': value.code,
                    'name': value.name,
                    'full_path': value.full_path
                }
                for value in values
            ]

            # Map canonical dimension types to UI keys
            if dim_type == 'functional':
                # Functional → Cost Centers
                result['cost_centers'].extend(value_items)
            elif dim_type == 'project':
                result['projects'].extend(value_items)
            elif dim_type == 'organizational':
                # Organizational → Departments/Business Units
                result['departments'].extend(value_items)
            elif dim_type == 'product':
                result['products'].extend(value_items)
            else:
                # Preserve other dimensions with explicit type for possible advanced UIs
                for item in value_items:
                    result['other_dimensions'].append({
                        'type': dim_type,
                        **item
                    })

        return result
