"""
Financial Statements Service for IFRS-compliant real-time financial reporting
"""

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

from app.models.accounting import AccountingCode, JournalEntry, AccountingEntry
from app.services.ifrs_reports_core import IFRSReportsCore
from app.schemas.financial_statements import (
    BalanceSheet, BalanceSheetAssets, BalanceSheetLiabilitiesAndEquity,
    IncomeStatement, IncomeStatementRevenue, IncomeStatementExpenses,
    CashFlowStatement, CashFlowActivity,
    StatementOfChangesInEquity, EquityMovement, TrialBalance, TrialBalanceAccount,
    FinancialReportPackage, ReportMetadata, AccountLineItem, FinancialStatementSection,
    ReportPeriodType, IFRSStandard
)


class FinancialStatementsService:
    """Service for generating comprehensive IFRS-compliant financial statements"""

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

    def get_report_metadata(self, report_type: str, as_of_date: date = None) -> ReportMetadata:
        """Generate metadata for financial reports"""
        if as_of_date is None:
            as_of_date = date.today()

        return ReportMetadata(
            report_id=f"rpt_{datetime.now().strftime('%Y%m%d_%H%M%S')}",
            report_type=report_type,
            company_name="Your Company Name",  # TODO: Get from settings
            reporting_date=as_of_date,
            period_start=as_of_date.replace(month=1, day=1),  # Start of year
            period_end=as_of_date,
            period_type=ReportPeriodType.ANNUALLY,
            ifrs_standards=[IFRSStandard.IAS_1],
            prepared_by="System Generated",
            prepared_at=datetime.now()
        )

    def get_balance_sheet(
        self,
        as_of_date: date = None,
        comparison_date: date = None,
        include_notes: bool = True
    ) -> BalanceSheet:
        """Generate IFRS-compliant Balance Sheet"""

        if as_of_date is None:
            as_of_date = date.today()

        # Get balance sheet data from existing service
        bs_data = self.ifrs_core.get_balance_sheet_data(as_of_date, comparison_date)

        # Convert to new schema format
        current_assets_section = FinancialStatementSection(
            section_name="Current Assets",
            section_code="CA",
            line_items=[
                AccountLineItem(
                    account_code=item['account_code'],
                    account_name=item['account_name'],
                    account_type="Asset",
                    ifrs_category="Current Assets",
                    current_period=Decimal(str(item['amount'])),
                    prior_period=Decimal('0.00'),  # TODO: Add comparison logic
                    notes=""
                ) for item in bs_data['assets']['current_assets']
            ],
            subtotal=Decimal(str(bs_data['assets']['total_current_assets'])),
            notes=None
        )

        non_current_assets_section = FinancialStatementSection(
            section_name="Non-Current Assets",
            section_code="NCA",
            line_items=[
                AccountLineItem(
                    account_code=item['account_code'],
                    account_name=item['account_name'],
                    account_type="Asset",
                    ifrs_category="Non-Current Assets",
                    current_period=Decimal(str(item['amount'])),
                    prior_period=Decimal('0.00'),
                    notes=""
                ) for item in bs_data['assets']['non_current_assets']
            ],
            subtotal=Decimal(str(bs_data['assets']['total_non_current_assets'])),
            notes=None
        )

        current_liabilities_section = FinancialStatementSection(
            section_name="Current Liabilities",
            section_code="CL",
            line_items=[
                AccountLineItem(
                    account_code=item['account_code'],
                    account_name=item['account_name'],
                    account_type="Liability",
                    ifrs_category="Current Liabilities",
                    current_period=Decimal(str(item['amount'])),
                    prior_period=Decimal('0.00'),
                    notes=""
                ) for item in bs_data['liabilities']['current_liabilities']
            ],
            subtotal=Decimal(str(bs_data['liabilities']['total_current_liabilities'])),
            notes=None
        )

        non_current_liabilities_section = FinancialStatementSection(
            section_name="Non-Current Liabilities",
            section_code="NCL",
            line_items=[
                AccountLineItem(
                    account_code=item['account_code'],
                    account_name=item['account_name'],
                    account_type="Liability",
                    ifrs_category="Non-Current Liabilities",
                    current_period=Decimal(str(item['amount'])),
                    prior_period=Decimal('0.00'),
                    notes=""
                ) for item in bs_data['liabilities']['non_current_liabilities']
            ],
            subtotal=Decimal(str(bs_data['liabilities']['total_non_current_liabilities'])),
            notes=None
        )

        equity_section = FinancialStatementSection(
            section_name="Equity",
            section_code="EQ",
            line_items=[
                AccountLineItem(
                    account_code=item['account_code'],
                    account_name=item['account_name'],
                    account_type="Equity",
                    ifrs_category="Equity",
                    current_period=Decimal(str(item['amount'])),
                    prior_period=Decimal('0.00'),
                    notes=""
                ) for item in bs_data['equity']
            ],
            subtotal=Decimal(str(bs_data['totals']['total_equity'])),
            notes=None
        )

        # Create the structured balance sheet according to schema
        assets = BalanceSheetAssets(
            current_assets=current_assets_section,
            non_current_assets=non_current_assets_section,
            total_assets=Decimal(str(bs_data['totals']['total_assets']))
        )

        liabilities_and_equity = BalanceSheetLiabilitiesAndEquity(
            current_liabilities=current_liabilities_section,
            non_current_liabilities=non_current_liabilities_section,
            total_liabilities=Decimal(str(bs_data['totals']['total_liabilities'])),
            equity=equity_section,
            total_equity=Decimal(str(bs_data['totals']['total_equity'])),
            total_liabilities_and_equity=Decimal(str(bs_data['totals']['total_liabilities'] + bs_data['totals']['total_equity']))
        )

        return BalanceSheet(
            metadata=self.get_report_metadata("Balance Sheet", as_of_date),
            assets=assets,
            liabilities_and_equity=liabilities_and_equity
        )

    def get_income_statement(
        self,
        start_date: date = None,
        end_date: date = None,
        comparison_start_date: date = None,
        comparison_end_date: date = None
    ) -> IncomeStatement:
        """Generate IFRS-compliant Income Statement"""

        if end_date is None:
            end_date = date.today()
        if start_date is None:
            start_date = date(end_date.year, 1, 1)  # Start of year

        # Get revenue and expense accounts
        revenue_data = self._get_accounts_by_type_and_period('REVENUE', start_date, end_date)
        expense_data = self._get_accounts_by_type_and_period('EXPENSE', start_date, end_date)

        # Split revenue between operating and other (non-operating) buckets
        operating_revenue = []
        other_revenue = []
        for item in revenue_data:
            name = (item.get('account_name') or '').lower()
            category = (item.get('category') or '').lower()
            if 'other' in name or 'other' in category or 'non-operating' in name or 'gain' in name:
                other_revenue.append(item)
            else:
                operating_revenue.append(item)

        def to_line_item(entry: dict, acc_type: str) -> AccountLineItem:
            return AccountLineItem(
                account_code=entry.get('account_code') or 'N/A',
                account_name=entry.get('account_name') or 'Account',
                account_type=acc_type.title(),
                ifrs_category=entry.get('category') or acc_type.title(),
                current_period=Decimal(str(entry.get('amount', 0))),
                prior_period=entry.get('prior_amount') and Decimal(str(entry.get('prior_amount'))),
                variance=None,
                variance_percentage=None,
                notes=None
            )

        # Categorize expenses into IFRS-friendly groupings
        cost_of_sales = []
        finance_costs = []
        other_expenses = []
        operating_expenses = []
        for item in expense_data:
            name = (item.get('account_name') or '').lower()
            category = (item.get('category') or '').lower()
            if 'cost' in name or 'cogs' in name or category == 'cost of sales':
                cost_of_sales.append(item)
            elif 'interest' in name or 'finance' in name or category in ['interest expense', 'finance costs']:
                finance_costs.append(item)
            elif 'other' in name or 'other' in category:
                other_expenses.append(item)
            else:
                operating_expenses.append(item)

        operating_revenue_section = FinancialStatementSection(
            section_name="Operating Revenue",
            section_code="REV_OP",
            line_items=[
                to_line_item(item, 'Revenue') for item in operating_revenue
            ],
            subtotal=sum(Decimal(str(item['amount'])) for item in operating_revenue)
        )

        other_revenue_section = FinancialStatementSection(
            section_name="Other Revenue",
            section_code="REV_OTH",
            line_items=[
                to_line_item(item, 'Revenue') for item in other_revenue
            ],
            subtotal=sum(Decimal(str(item['amount'])) for item in other_revenue)
        )

        cost_of_sales_section = FinancialStatementSection(
            section_name="Cost of Sales",
            section_code="COGS",
            line_items=[
                to_line_item(item, 'Expense') for item in cost_of_sales
            ],
            subtotal=sum(Decimal(str(item['amount'])) for item in cost_of_sales)
        )

        operating_expenses_section = FinancialStatementSection(
            section_name="Operating Expenses",
            section_code="EXP_OP",
            line_items=[
                to_line_item(item, 'Expense') for item in operating_expenses
            ],
            subtotal=sum(Decimal(str(item['amount'])) for item in operating_expenses)
        )

        finance_costs_section = FinancialStatementSection(
            section_name="Finance Costs",
            section_code="FIN_COST",
            line_items=[
                to_line_item(item, 'Expense') for item in finance_costs
            ],
            subtotal=sum(Decimal(str(item['amount'])) for item in finance_costs)
        )

        other_expenses_section = FinancialStatementSection(
            section_name="Other Expenses",
            section_code="EXP_OTH",
            line_items=[
                to_line_item(item, 'Expense') for item in other_expenses
            ],
            subtotal=sum(Decimal(str(item['amount'])) for item in other_expenses)
        )

        total_revenue = operating_revenue_section.subtotal + other_revenue_section.subtotal
        total_expenses = cost_of_sales_section.subtotal + operating_expenses_section.subtotal + finance_costs_section.subtotal + other_expenses_section.subtotal

        gross_profit = operating_revenue_section.subtotal - cost_of_sales_section.subtotal
        operating_profit = gross_profit - operating_expenses_section.subtotal
        profit_before_tax = operating_profit - finance_costs_section.subtotal
        tax_expense = Decimal('0.00')  # TODO: Implement
        profit_after_tax = profit_before_tax - tax_expense

        revenue = IncomeStatementRevenue(
            operating_revenue=operating_revenue_section,
            other_revenue=other_revenue_section,
            total_revenue=total_revenue
        )

        expenses = IncomeStatementExpenses(
            cost_of_sales=cost_of_sales_section,
            operating_expenses=operating_expenses_section,
            finance_costs=finance_costs_section,
            other_expenses=other_expenses_section,
            total_expenses=total_expenses
        )

        return IncomeStatement(
            metadata=self.get_report_metadata("Income Statement", end_date),
            revenue=revenue,
            expenses=expenses,
            gross_profit=gross_profit,
            operating_profit=operating_profit,
            profit_before_tax=profit_before_tax,
            tax_expense=tax_expense,
            profit_after_tax=profit_after_tax
        )

    def get_cash_flow_statement(
        self,
        start_date: date = None,
        end_date: date = None
    ) -> CashFlowStatement:
        """Generate IFRS-compliant Cash Flow Statement"""

        if end_date is None:
            end_date = date.today()
        if start_date is None:
            start_date = date(end_date.year, 1, 1)

        # Build activities from tagged accounts
        operating_items, operating_cash_flow, operating_inflows, operating_outflows = self._get_cash_flow_activity('Operating Activities', start_date, end_date)
        investing_items, investing_cash_flow, investing_inflows, investing_outflows = self._get_cash_flow_activity('Investing Activities', start_date, end_date)
        financing_items, financing_cash_flow, financing_inflows, financing_outflows = self._get_cash_flow_activity('Financing Activities', start_date, end_date)

        operating_activities = CashFlowActivity(
            line_items=operating_items,
            net_cash_flow=operating_cash_flow,
            inflows_total=operating_inflows,
            outflows_total=operating_outflows
        )

        investing_activities = CashFlowActivity(
            line_items=investing_items,
            net_cash_flow=investing_cash_flow,
            inflows_total=investing_inflows,
            outflows_total=investing_outflows
        )

        financing_activities = CashFlowActivity(
            line_items=financing_items,
            net_cash_flow=financing_cash_flow,
            inflows_total=financing_inflows,
            outflows_total=financing_outflows
        )

        net_increase_in_cash = operating_cash_flow + investing_cash_flow + financing_cash_flow

        # Opening and closing cash balances for reconciliation
        cash_at_beginning = self._get_cash_balance_at_date(start_date - timedelta(days=1)) if start_date else Decimal('0.00')
        cash_at_end = cash_at_beginning + net_increase_in_cash

        metadata = self.get_report_metadata("Cash Flow Statement", end_date)
        metadata.ifrs_standards = [IFRSStandard.IAS_7]

        return CashFlowStatement(
            metadata=metadata,
            operating_activities=operating_activities,
            investing_activities=investing_activities,
            financing_activities=financing_activities,
            net_increase_in_cash=net_increase_in_cash,
            cash_at_beginning=cash_at_beginning,
            cash_at_end=cash_at_end
        )

    def get_statement_of_changes_in_equity(
        self,
        start_date: date = None,
        end_date: date = None
    ) -> StatementOfChangesInEquity:
        """Generate Statement of Changes in Equity"""

        if end_date is None:
            end_date = date.today()
        if start_date is None:
            start_date = date(end_date.year, 1, 1)

        def balance_as_of(account_id: str, as_of: date) -> Decimal:
            """Fetch account balance as of a specific date, tolerant of missing data."""
            try:
                info = self.ifrs_core.get_account_balance_as_of_date(account_id, as_of)
                return Decimal(str(info.get('balance', 0)))
            except Exception:
                return Decimal('0.00')

        # Gather equity accounts
        equity_accounts = self.db.query(AccountingCode).filter(
            func.upper(AccountingCode.account_type) == 'EQUITY'
        ).all()

        net_profit_total = self._get_net_profit_for_period(start_date, end_date)
        oci_total = Decimal('0.00')  # Placeholder until OCI accounts are modeled

        equity_movements: List[EquityMovement] = []
        profit_allocated = False

        for account in equity_accounts:
            name_lower = (getattr(account, 'name', '') or '').lower()

            opening = balance_as_of(account.id, start_date - timedelta(days=1))
            closing = balance_as_of(account.id, end_date)

            # Allocate profit/loss to retained earnings accounts first
            profit_loss = Decimal('0.00')
            if not profit_allocated and any(key in name_lower for key in ['retained', 'earnings', 'accumulated']):
                profit_loss = net_profit_total
                profit_allocated = True

            # Placeholder OCI allocation: route to accounts containing OCI/OCI keywords
            oci = Decimal('0.00')
            if 'oci' in name_lower or 'comprehensive' in name_lower:
                oci = oci_total
                oci_total = Decimal('0.00')

            transactions_with_owners = closing - opening - profit_loss - oci
            net_movement = closing - opening

            equity_movements.append(EquityMovement(
                component_name=getattr(account, 'name', 'Equity'),
                opening_balance=opening,
                profit_loss=profit_loss,
                other_comprehensive_income=oci,
                transactions_with_owners=transactions_with_owners,
                net_movement=net_movement,
                closing_balance=closing
            ))

        # If profit was not allocated to any specific equity account, create a retained earnings line
        if not profit_allocated and net_profit_total != 0:
            opening = Decimal('0.00')
            closing = net_profit_total
            equity_movements.append(EquityMovement(
                component_name="Retained Earnings",
                opening_balance=opening,
                profit_loss=net_profit_total,
                other_comprehensive_income=Decimal('0.00'),
                transactions_with_owners=Decimal('0.00'),
                net_movement=closing - opening,
                closing_balance=closing
            ))

        total_equity_opening = sum(m.opening_balance for m in equity_movements) if equity_movements else Decimal('0.00')
        total_profit_loss = sum(m.profit_loss for m in equity_movements) if equity_movements else Decimal('0.00')
        total_oci = sum(m.other_comprehensive_income for m in equity_movements) if equity_movements else Decimal('0.00')
        total_transactions_with_owners = sum(m.transactions_with_owners for m in equity_movements) if equity_movements else Decimal('0.00')
        total_equity_closing = sum(m.closing_balance for m in equity_movements) if equity_movements else Decimal('0.00')

        metadata = self.get_report_metadata("Statement of Changes in Equity", end_date)
        metadata.ifrs_standards = [IFRSStandard.IAS_1]

        return StatementOfChangesInEquity(
            metadata=metadata,
            equity_movements=equity_movements,
            total_equity_opening=total_equity_opening,
            total_profit_loss=total_profit_loss,
            total_other_comprehensive_income=total_oci,
            total_transactions_with_owners=total_transactions_with_owners,
            total_equity_closing=total_equity_closing
        )

    def get_trial_balance(
        self,
        as_of_date: date = None,
        include_zero_balances: bool = False
    ) -> TrialBalance:
        """Generate Trial Balance"""

        if as_of_date is None:
            as_of_date = date.today()

        tb_data = self.ifrs_core.get_trial_balance_data(as_of_date, include_zero_balances)

        # Import TrialBalanceAccount here to avoid circular imports
        from app.schemas.financial_statements import TrialBalanceAccount

        accounts = []
        total_debits = Decimal('0.00')
        total_credits = Decimal('0.00')

        for item in tb_data['accounts']:
            debit_balance = Decimal(str(item.get('debit_balance', 0)))
            credit_balance = Decimal(str(item.get('credit_balance', 0)))
            net_balance = debit_balance - credit_balance

            accounts.append(TrialBalanceAccount(
                account_code=item['account_code'],
                account_name=item['account_name'],
                account_type=item.get('account_type', 'UNKNOWN'),
                debit_balance=debit_balance,
                credit_balance=credit_balance,
                net_balance=net_balance
            ))

            total_debits += debit_balance
            total_credits += credit_balance

        return TrialBalance(
            metadata=self.get_report_metadata("Trial Balance", as_of_date),
            accounts=accounts,
            total_debits=total_debits,
            total_credits=total_credits
        )

    def get_financial_report_package(
        self,
        as_of_date: date = None,
        include_comparatives: bool = True
    ) -> FinancialReportPackage:
        """Generate complete financial report package"""

        if as_of_date is None:
            as_of_date = date.today()

        start_date = date(as_of_date.year, 1, 1)

        # Build required statements
        bs = self.get_balance_sheet(as_of_date)
        is_ = self.get_income_statement(start_date, as_of_date)
        cf = self.get_cash_flow_statement(start_date, as_of_date)
        sce = self.get_statement_of_changes_in_equity(start_date, as_of_date)
        tb = self.get_trial_balance(as_of_date)

        # Build aging reports using AgingReportsService, then wrap in schema
        from app.services.aging_reports_service import AgingReportsService
        from app.schemas.financial_statements import AgingReport, AgingReportEntry, AgingBucket

        aging_service = AgingReportsService(self.db)
        debtors_raw = aging_service.generate_debtors_aging(as_of_date=as_of_date)
        creditors_raw = aging_service.generate_creditors_aging(as_of_date=as_of_date)

        # Helper to convert raw aging dict into AgingReport model safely
        def to_aging_report(raw: dict, subtype: str) -> AgingReport:
            entries = []
            summary_buckets = []
            total_outstanding = Decimal('0.00')
            try:
                # raw formats may vary; attempt common keys
                items = raw.get('debtors') if subtype == 'debtors' else raw.get('creditors')
                items = items or []
                for it in items:
                    buckets = []
                    for key in ['current','days_31_60','days_61_90','days_91_120','days_120_plus']:
                        if key in it:
                            buckets.append(AgingBucket(bucket_name=key.replace('_',' ').upper(), amount=Decimal(str(it.get(key,0))), percentage=Decimal('0')))
                    entries.append(AgingReportEntry(
                        entity_id=str(it.get('customer_id') or it.get('supplier_id') or ''),
                        entity_name=str(it.get('customer_name') or it.get('supplier_name') or 'Unknown'),
                        total_amount=Decimal(str(it.get('total_outstanding') or it.get('total') or 0)),
                        aging_buckets=buckets,
                        last_transaction_date=None
                    ))
                    total_outstanding += Decimal(str(it.get('total_outstanding') or it.get('total') or 0))
                # summary buckets
                summary = raw.get('summary') or {}
                for k,v in summary.items():
                    summary_buckets.append(AgingBucket(bucket_name=str(k).replace('_',' ').upper(), amount=Decimal(str(v)), percentage=Decimal('0')))
            except Exception:
                pass

            return AgingReport(
                metadata=self.get_report_metadata(f"{subtype.title()} Aging", as_of_date),
                report_subtype=subtype,
                entries=entries,
                summary_buckets=summary_buckets,
                total_outstanding=total_outstanding
            )

        debtors_report = to_aging_report(debtors_raw if isinstance(debtors_raw, dict) else {}, 'debtors')
        creditors_report = to_aging_report(creditors_raw if isinstance(creditors_raw, dict) else {}, 'creditors')

        return FinancialReportPackage(
            metadata=self.get_report_metadata("Financial Report Package", as_of_date),
            balance_sheet=bs,
            income_statement=is_,
            cash_flow_statement=cf,
            changes_in_equity=sce,
            trial_balance=tb,
            debtors_aging=debtors_report,
            creditors_aging=creditors_report,
            notes_to_statements=["Complete financial statements prepared in accordance with IFRS"]
        )

    # Helper methods
    def _get_accounts_by_type_and_period(self, account_type: str, start_date: date, end_date: date) -> List[Dict]:
        """Get account balances for a specific type and period"""
        try:
            # Some datasets store account_type with title-case (e.g., "Revenue" vs "REVENUE");
            # normalize to uppercase for consistent filtering.
            accounts = self.db.query(AccountingCode).filter(
                func.upper(AccountingCode.account_type) == account_type.upper()
            ).all()

            result = []
            for account in accounts:
                balance_info = self._get_account_balance_for_period(account.id, start_date, end_date)
                if balance_info['balance'] != 0:
                    result.append({
                        'account_code': getattr(account, 'code', f'ACC-{account.id}'),
                        'account_name': getattr(account, 'name', 'Unknown Account'),
                        'category': getattr(account, 'category', ''),
                        'amount': abs(float(balance_info['balance'])),
                        'account_type': account_type
                    })
            return result
        except Exception as e:
            return []

    def _derive_reporting_tag(self, account: AccountingCode) -> str:
        """Infer a reporting tag if one is missing, to keep cash flow consistent."""
        acc_type = (getattr(account, 'account_type', '') or '').upper()
        name = (getattr(account, 'name', '') or '').lower()
        category = (getattr(account, 'category', '') or '').lower()

        if 'cash' in name or 'bank' in name:
            return 'Operating Activities'
        if acc_type in ('REVENUE', 'EXPENSE'):
            return 'Operating Activities'
        if acc_type == 'ASSET':
            if 'investment' in name or 'investment' in category or 'ppe' in category or 'fixed' in category:
                return 'Investing Activities'
            return 'Operating Activities'
        if acc_type in ('LIABILITY', 'EQUITY'):
            return 'Financing Activities'
        return 'Operating Activities'

    def _get_cash_flow_activity(self, reporting_tag: str, start_date: date, end_date: date) -> tuple[List[AccountLineItem], Decimal, Decimal, Decimal]:
        """Aggregate cash flow activity by reporting tag, assigning missing tags on the fly, with inflow/outflow detail."""
        # Preload accounts to allow tag assignment
        accounts = {acc.id: acc for acc in self.db.query(AccountingCode).all()}

        try:
            rows = (
                self.db.query(
                    JournalEntry.accounting_code_id.label('acc_id'),
                    func.coalesce(func.sum(JournalEntry.debit_amount), 0).label('debits'),
                    func.coalesce(func.sum(JournalEntry.credit_amount), 0).label('credits'),
                )
                .filter(
                    JournalEntry.date >= start_date,
                    JournalEntry.date <= end_date,
                )
                .group_by(JournalEntry.accounting_code_id)
                .all()
            )
        except Exception:
            rows = []

        line_items: List[AccountLineItem] = []
        tags_updated = False
        inflows_total = Decimal('0.00')
        outflows_total = Decimal('0.00')

        def map_tag_to_activity(tag_value: str) -> str:
            """Normalize IFRS tags (A1.1, R1, X3, etc.) or plain text to cash flow sections."""
            if not tag_value:
                return ''

            normalized = (tag_value or '').strip()
            direct = normalized.lower()
            if direct in ('operating activities', 'investing activities', 'financing activities'):
                return normalized.title()

            upper = normalized.upper()
            # Operating: revenue/expense lines and working capital movements
            if upper.startswith(('R', 'X', 'A1.2', 'A1.3', 'A1', 'L1.1', 'L1.3')):
                return 'Operating Activities'
            # Investing: long-term assets
            if upper.startswith('A2'):
                return 'Investing Activities'
            # Financing: debt/equity movements
            if upper.startswith(('L1.2', 'L2', 'E')):
                return 'Financing Activities'
            # Default: treat as operating to keep cash flow complete
            return 'Operating Activities'

        def classify(account: AccountingCode) -> str:
            """Provide a human label for the line item grouping."""
            acc_type = (getattr(account, 'account_type', '') or '').upper()
            name = (getattr(account, 'name', '') or '').lower()
            category = (getattr(account, 'category', '') or '').lower()
            tag_value = (getattr(account, 'reporting_tag', '') or '').upper()

            if tag_value.startswith('R'):
                return 'Revenue Collections'
            if tag_value.startswith('X1'):
                return 'Cost of Sales'
            if tag_value.startswith('X2'):
                return 'Selling & Distribution'
            if tag_value.startswith('X3'):
                return 'Administrative Expenses'
            if tag_value.startswith('X4'):
                return 'Finance Costs'
            if tag_value.startswith('A1.2'):
                return 'Working Capital - Receivables'
            if tag_value.startswith('A1.3'):
                return 'Working Capital - Inventory'
            if tag_value.startswith('A1.1'):
                return 'Cash and Cash Equivalents'
            if tag_value.startswith('L1.1'):
                return 'Working Capital - Payables'
            if tag_value.startswith('L1.3'):
                return 'Working Capital - Taxes'
            if tag_value.startswith('A2'):
                return 'Capital Expenditure & Investments'
            if tag_value.startswith(('L1.2', 'L2', 'E')):
                return 'Financing Sources'

            if acc_type == 'REVENUE':
                return 'Revenue Collections'
            if acc_type == 'EXPENSE':
                return 'Operating Payments'
            if acc_type == 'ASSET':
                if any(key in name or key in category for key in ['investment', 'ppe', 'fixed']):
                    return 'Capital Expenditure & Investments'
                if 'inventory' in name or 'inventory' in category:
                    return 'Working Capital - Inventory'
                if 'receivable' in name or 'ar' in category:
                    return 'Working Capital - Receivables'
                if 'cash' in name or 'bank' in name:
                    return 'Cash and Cash Equivalents'
                return 'Working Capital - Assets'
            if acc_type in ('LIABILITY', 'EQUITY'):
                if 'payable' in name or 'accrued' in name:
                    return 'Working Capital - Payables'
                return 'Financing Sources'
            return 'Operating'

        for row in rows:
            account = accounts.get(row.acc_id)
            if not account:
                continue

            tag = (account.reporting_tag or '').strip() or self._derive_reporting_tag(account)
            if not account.reporting_tag:
                account.reporting_tag = tag
                tags_updated = True

            activity_bucket = map_tag_to_activity(tag)
            if activity_bucket.lower() != reporting_tag.lower():
                continue

            net_amount = Decimal(str(row.credits or 0)) - Decimal(str(row.debits or 0))
            if net_amount == 0:
                continue

            flow_direction = 'Inflow' if net_amount > 0 else 'Outflow'
            group_label = classify(account)

            if net_amount > 0:
                inflows_total += net_amount
            else:
                outflows_total += abs(net_amount)

            line_items.append(AccountLineItem(
                account_code=getattr(account, 'code', 'N/A') or 'N/A',
                account_name=getattr(account, 'name', 'Account') or 'Account',
                account_type=getattr(account, 'account_type', 'Cash') or 'Cash',
                ifrs_category=tag,
                cash_flow_group=group_label,
                flow_direction=flow_direction,
                current_period=net_amount,
                prior_period=None,
                variance=None,
                variance_percentage=None,
                notes=None
            ))

        if tags_updated:
            try:
                self.db.commit()
            except Exception:
                self.db.rollback()

        net_total = sum(item.current_period for item in line_items) if line_items else Decimal('0.00')

        # Fallback: if operating has no tagged movement, use net profit as proxy
        if net_total == 0 and reporting_tag.lower() == 'operating activities':
            net_profit = self._get_net_profit_for_period(start_date, end_date)
            if net_profit != 0:
                flow_direction = 'Inflow' if net_profit > 0 else 'Outflow'
                line_items.append(AccountLineItem(
                    account_code='NET_PROFIT',
                    account_name='Net Profit (fallback)',
                    account_type='Operating',
                    ifrs_category='Operating Activities',
                    cash_flow_group='Operating Performance',
                    flow_direction=flow_direction,
                    current_period=net_profit,
                    prior_period=None,
                    variance=None,
                    variance_percentage=None,
                    notes='Derived because no operating cash tags were present'
                ))
                net_total = net_profit
                if net_profit > 0:
                    inflows_total += net_profit
                else:
                    outflows_total += abs(net_profit)

        return line_items, net_total, inflows_total, outflows_total

    def _get_account_balance_for_period(self, account_id: int, start_date: date, end_date: date) -> Dict:
        """Get account balance for a specific period"""
        try:
            # JournalEntry stores the link as accounting_code_id and the dated column is `date`
            entries = self.db.query(JournalEntry).filter(
                and_(
                    JournalEntry.accounting_code_id == account_id,
                    JournalEntry.date >= start_date,
                    JournalEntry.date <= end_date
                )
            ).all()

            total_debits = sum(entry.debit_amount or 0 for entry in entries)
            total_credits = sum(entry.credit_amount or 0 for entry in entries)

            # Get account to determine balance calculation
            account = self.db.query(AccountingCode).filter(AccountingCode.id == account_id).first()
            account_type = getattr(account, 'account_type', 'ASSET')

            if account_type in ['ASSET', 'EXPENSE']:
                balance = total_debits - total_credits
            else:  # LIABILITY, EQUITY, REVENUE
                balance = total_credits - total_debits

            return {
                'balance': balance,
                'total_debits': total_debits,
                'total_credits': total_credits
            }
        except Exception as e:
            return {'balance': 0, 'total_debits': 0, 'total_credits': 0}

    def _get_cash_accounts(self) -> List[Dict]:
        """Get cash and cash equivalent accounts"""
        try:
            cash_accounts = self.db.query(AccountingCode).filter(
                or_(
                    AccountingCode.name.ilike('%cash%'),
                    AccountingCode.name.ilike('%bank%'),
                    AccountingCode.code.ilike('%1000%')  # Common cash account codes
                )
            ).all()
            return [{'id': acc.id, 'name': acc.name, 'code': acc.code} for acc in cash_accounts]
        except Exception as e:
            return []

    def _calculate_operating_cash_flows(self, start_date: date, end_date: date) -> Decimal:
        """Calculate operating cash flows (simplified)"""
        # This is a simplified calculation - in practice would be more complex
        net_profit = self._get_net_profit_for_period(start_date, end_date)
        return net_profit  # Simplified - would include adjustments for non-cash items

    def _calculate_investing_cash_flows(self, start_date: date, end_date: date) -> Decimal:
        """Calculate investing cash flows based on asset purchases/sales"""
        from app.models.asset_management import Asset

        # Calculate asset purchases (cash outflows)
        asset_purchases = self.db.query(Asset).filter(
            Asset.purchase_date >= start_date,
            Asset.purchase_date <= end_date
        ).all()

        total_purchases = sum(
            (asset.purchase_cost for asset in asset_purchases),
            Decimal('0.00')
        )

        # Asset sales would be credits to cash from fixed asset sales
        # Query journal entries for asset disposal accounts
        from app.models.accounting import JournalEntry, AccountingEntry, AccountingCode

        # Find accounts related to "Gain/Loss on Asset Sale" or similar
        asset_sale_accounts = self.db.query(AccountingCode).filter(
            AccountingCode.name.ilike('%gain%asset%')
            | AccountingCode.name.ilike('%loss%asset%')
            | AccountingCode.name.ilike('%disposal%')
        ).all()

        asset_sales = Decimal('0.00')
        if asset_sale_accounts:
            for account in asset_sale_accounts:
                entries = self.db.query(AccountingEntry).join(JournalEntry).filter(
                    AccountingEntry.accounting_code_id == account.id,
                    JournalEntry.entry_date >= start_date,
                    JournalEntry.entry_date <= end_date
                ).all()

                for entry in entries:
                    # Credits represent proceeds from asset sales
                    if entry.entry_type == 'credit':
                        asset_sales += entry.amount

        # Investing cash flow = Sales (inflows) - Purchases (outflows)
        return asset_sales - total_purchases

    def _calculate_financing_cash_flows(self, start_date: date, end_date: date) -> Decimal:
        """Calculate financing cash flows based on debt and equity transactions"""
        from app.models.accounting import JournalEntry, AccountingEntry, AccountingCode

        # Find liability accounts related to loans/debt
        loan_accounts = self.db.query(AccountingCode).filter(
            AccountingCode.account_type == 'Liability',
            (AccountingCode.name.ilike('%loan%')
             | AccountingCode.name.ilike('%debt%')
             | AccountingCode.name.ilike('%note%payable%')
             | AccountingCode.name.ilike('%long%term%liability%'))
        ).all()

        # Find equity accounts (capital contributions, dividends)
        equity_accounts = self.db.query(AccountingCode).filter(
            AccountingCode.account_type == 'Equity',
            (AccountingCode.name.ilike('%capital%')
             | AccountingCode.name.ilike('%dividend%')
             | AccountingCode.name.ilike('%drawing%')
             | AccountingCode.name.ilike('%stock%'))
        ).all()

        net_financing = Decimal('0.00')

        # Calculate net change in loan accounts (increases = cash inflows)
        for account in loan_accounts:
            entries = self.db.query(AccountingEntry).join(JournalEntry).filter(
                AccountingEntry.accounting_code_id == account.id,
                JournalEntry.entry_date >= start_date,
                JournalEntry.entry_date <= end_date
            ).all()

            for entry in entries:
                # Credit to liability = borrowing (cash inflow)
                # Debit to liability = repayment (cash outflow)
                if entry.entry_type == 'credit':
                    net_financing += entry.amount
                else:
                    net_financing -= entry.amount

        # Calculate net change in equity accounts
        for account in equity_accounts:
            entries = self.db.query(AccountingEntry).join(JournalEntry).filter(
                AccountingEntry.accounting_code_id == account.id,
                JournalEntry.entry_date >= start_date,
                JournalEntry.entry_date <= end_date
            ).all()

            for entry in entries:
                # Check if it's capital contribution or dividend payment
                if 'dividend' in account.name.lower() or 'drawing' in account.name.lower():
                    # Dividends/drawings are cash outflows (debits increase dividend expense)
                    if entry.entry_type == 'debit':
                        net_financing -= entry.amount
                else:
                    # Capital contributions are cash inflows (credits increase equity)
                    if entry.entry_type == 'credit':
                        net_financing += entry.amount

        return net_financing

    def _get_cash_balance_at_date(self, as_of_date: date) -> Decimal:
        """Get total cash balance at a specific date"""
        cash_accounts = self._get_cash_accounts()
        total_cash = Decimal('0.00')

        for account in cash_accounts:
            balance_info = self.ifrs_core.get_account_balance_as_of_date(account['id'], as_of_date)
            total_cash += Decimal(str(balance_info['balance']))

        return total_cash

    def _get_equity_balance_at_date(self, as_of_date: date) -> Decimal:
        """Get total equity balance at a specific date"""
        try:
            equity_accounts = self.db.query(AccountingCode).filter(
                AccountingCode.account_type == 'EQUITY'
            ).all()

            total_equity = Decimal('0.00')
            for account in equity_accounts:
                balance_info = self.ifrs_core.get_account_balance_as_of_date(account.id, as_of_date)
                total_equity += Decimal(str(balance_info['balance']))

            return total_equity
        except Exception as e:
            return Decimal('0.00')

    def _get_net_profit_for_period(self, start_date: date, end_date: date) -> Decimal:
        """Calculate net profit for a period"""
        revenue_data = self._get_accounts_by_type_and_period('REVENUE', start_date, end_date)
        expense_data = self._get_accounts_by_type_and_period('EXPENSE', start_date, end_date)

        total_revenue = sum(Decimal(str(item['amount'])) for item in revenue_data)
        total_expenses = sum(Decimal(str(item['amount'])) for item in expense_data)

        return total_revenue - total_expenses
