"""
Fiscal Period Management Service

Provides business logic for:
- Creating and managing fiscal years
- Opening and closing periods
- Period locking and unlocking
- Financial statement generation
- Period-end checklists
"""

from datetime import datetime, date, timedelta
from decimal import Decimal
from types import SimpleNamespace
from typing import List, Optional, Dict, Any, Tuple
from dateutil.relativedelta import relativedelta
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, func, desc
from sqlalchemy.exc import IntegrityError

from app.models.fiscal_period import (
    FiscalYear, FiscalPeriod, PeriodEndStatement, PeriodCloseChecklist,
    FiscalYearStatus, PeriodStatus, PeriodType
)
from app.models.accounting import AccountingEntry, JournalEntry, AccountingCode, OpeningBalance
from app.models.app_setting import AppSetting
from app.services.vat_service import VatService
from app.models.user import User


def _make_json_safe(obj):
    """Recursively convert date/datetime/Decimal objects to JSON-serializable types."""
    if isinstance(obj, dict):
        return {k: _make_json_safe(v) for k, v in obj.items()}
    if isinstance(obj, list):
        return [_make_json_safe(i) for i in obj]
    if isinstance(obj, (datetime, date)):
        return obj.isoformat()
    if isinstance(obj, Decimal):
        return float(obj)
    return obj


class FiscalPeriodService:
    """Service for fiscal period management"""

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

    # ==================== FISCAL YEAR MANAGEMENT ====================

    def create_fiscal_year(
        self,
        year_name: str,
        year_code: str,
        start_date: date,
        end_date: date,
        period_type: PeriodType = PeriodType.MONTHLY,
        auto_create_periods: bool = True,
        branch_id: Optional[str] = None,
        notes: Optional[str] = None
    ) -> FiscalYear:
        """
        Create a new fiscal year

        Args:
            year_name: Display name (e.g., "FY 2025-2026")
            year_code: Short code (e.g., "FY2025")
            start_date: First day of fiscal year
            end_date: Last day of fiscal year
            period_type: Default period breakdown type
            auto_create_periods: Whether to auto-create periods
            branch_id: Optional branch ID
            notes: Optional notes

        Returns:
            Created FiscalYear object
        """
        if not year_name or not year_name.strip():
            raise ValueError("Fiscal year name is required")

        if not year_code or not year_code.strip():
            raise ValueError("Fiscal year code is required")

        existing_name = self.db.query(FiscalYear).filter(
            FiscalYear.year_name == year_name.strip()
        ).first()
        if existing_name:
            raise ValueError(f"Fiscal year name already exists: {year_name}")

        existing_code = self.db.query(FiscalYear).filter(
            FiscalYear.year_code == year_code.strip()
        ).first()
        if existing_code:
            raise ValueError(f"Fiscal year code already exists: {year_code}")

        # Check for overlapping fiscal years
        overlapping = self.db.query(FiscalYear).filter(
            and_(
                FiscalYear.branch_id == branch_id,
                or_(
                    and_(FiscalYear.start_date <= start_date, FiscalYear.end_date >= start_date),
                    and_(FiscalYear.start_date <= end_date, FiscalYear.end_date >= end_date)
                )
            )
        ).first()

        if overlapping:
            raise ValueError(
                f"Fiscal year overlaps with existing year '{overlapping.year_name}' "
                f"({overlapping.start_date} to {overlapping.end_date})"
            )

        # Create fiscal year
        fiscal_year = FiscalYear(
            year_name=year_name,
            year_code=year_code,
            start_date=start_date,
            end_date=end_date,
            period_type_default=period_type,
            auto_create_periods=auto_create_periods,
            branch_id=branch_id,
            notes=notes,
            status=FiscalYearStatus.DRAFT
        )

        self.db.add(fiscal_year)
        self.db.flush()

        # Auto-create periods if requested
        if auto_create_periods:
            self._auto_create_periods(fiscal_year, period_type)

        try:
            self.db.commit()
            self.db.refresh(fiscal_year)
        except IntegrityError as exc:
            self.db.rollback()
            raise ValueError("Fiscal year violates a uniqueness constraint") from exc

        return fiscal_year

    def _auto_create_periods(self, fiscal_year: FiscalYear, period_type: PeriodType):
        """Auto-create periods for a fiscal year"""
        if period_type == PeriodType.MONTHLY:
            self._create_monthly_periods(fiscal_year)
        elif period_type == PeriodType.QUARTERLY:
            self._create_quarterly_periods(fiscal_year)
        elif period_type == PeriodType.YEARLY:
            self._create_yearly_period(fiscal_year)

    def _create_monthly_periods(self, fiscal_year: FiscalYear):
        """Create monthly periods for a fiscal year"""
        current_date = fiscal_year.start_date
        period_number = 1
        periods = []

        while current_date <= fiscal_year.end_date:
            next_period_start = current_date + relativedelta(months=1)
            period_end = next_period_start - timedelta(days=1)

            if period_end > fiscal_year.end_date:
                period_end = fiscal_year.end_date

            if period_end <= current_date:
                if periods:
                    periods[-1].end_date = fiscal_year.end_date
                    break
                raise ValueError("Fiscal year is too short to auto-create monthly periods")

            # Create period
            period_name = current_date.strftime("%B %Y")
            period_code = current_date.strftime("%Y-%m")

            period = FiscalPeriod(
                fiscal_year_id=fiscal_year.id,
                period_name=period_name,
                period_code=period_code,
                period_number=period_number,
                period_type=PeriodType.MONTHLY,
                start_date=current_date,
                end_date=period_end,
                status=PeriodStatus.DRAFT,
                branch_id=fiscal_year.branch_id
            )

            self.db.add(period)
            periods.append(period)

            # Move to next month
            current_date = period_end + timedelta(days=1)
            period_number += 1

    def _create_quarterly_periods(self, fiscal_year: FiscalYear):
        """Create quarterly periods for a fiscal year"""
        current_date = fiscal_year.start_date
        period_number = 1
        periods = []

        while current_date <= fiscal_year.end_date:
            # Calculate end of quarter (3 months)
            period_end = current_date + relativedelta(months=3) - timedelta(days=1)

            # Don't exceed fiscal year end
            if period_end > fiscal_year.end_date:
                period_end = fiscal_year.end_date

            if period_end <= current_date:
                if periods:
                    periods[-1].end_date = fiscal_year.end_date
                    break
                raise ValueError("Fiscal year is too short to auto-create quarterly periods")

            # Create period
            quarter = (period_number - 1) % 4 + 1
            year = current_date.year
            period_name = f"Q{quarter} {year}"
            period_code = f"{year}-Q{quarter}"

            period = FiscalPeriod(
                fiscal_year_id=fiscal_year.id,
                period_name=period_name,
                period_code=period_code,
                period_number=period_number,
                period_type=PeriodType.QUARTERLY,
                start_date=current_date,
                end_date=period_end,
                status=PeriodStatus.DRAFT,
                branch_id=fiscal_year.branch_id
            )

            self.db.add(period)
            periods.append(period)

            # Move to next quarter
            current_date = period_end + timedelta(days=1)
            period_number += 1

    def _create_yearly_period(self, fiscal_year: FiscalYear):
        """Create a single yearly period"""
        period = FiscalPeriod(
            fiscal_year_id=fiscal_year.id,
            period_name=fiscal_year.year_name,
            period_code=fiscal_year.year_code,
            period_number=1,
            period_type=PeriodType.YEARLY,
            start_date=fiscal_year.start_date,
            end_date=fiscal_year.end_date,
            status=PeriodStatus.DRAFT,
            branch_id=fiscal_year.branch_id
        )

        self.db.add(period)

    def activate_fiscal_year(self, fiscal_year_id: str, user_id: Optional[str] = None) -> FiscalYear:
        """
        Activate a fiscal year (set as current active year)
        Deactivates any other active year
        """
        fiscal_year = self.db.query(FiscalYear).filter(
            FiscalYear.id == fiscal_year_id
        ).first()

        if not fiscal_year:
            raise ValueError("Fiscal year not found")

        # Guard: cannot activate a fiscal year that hasn't started yet
        today = date.today()
        if fiscal_year.start_date > today:
            raise ValueError(
                f"Cannot activate fiscal year '{fiscal_year.year_name}': it starts on "
                f"{fiscal_year.start_date}, which is in the future. "
                f"Only years that have already begun can be set as active."
            )

        # Deactivate other fiscal years for same branch
        self.db.query(FiscalYear).filter(
            and_(
                FiscalYear.branch_id == fiscal_year.branch_id,
                FiscalYear.id != fiscal_year_id
            )
        ).update({"is_active": False})

        # Activate this year
        fiscal_year.is_active = True
        fiscal_year.status = FiscalYearStatus.OPEN

        # Auto-open the period that contains today (if any)
        today = date.today()
        current_period = self.db.query(FiscalPeriod).filter(
            and_(
                FiscalPeriod.fiscal_year_id == fiscal_year_id,
                FiscalPeriod.start_date <= today,
                FiscalPeriod.end_date >= today
            )
        ).order_by(FiscalPeriod.period_number).first()

        if current_period and current_period.status == PeriodStatus.DRAFT:
            current_period.status = PeriodStatus.OPEN
            current_period.opened_date = datetime.utcnow()
            if user_id:
                current_period.opened_by_user_id = user_id

        self.db.commit()
        self.db.refresh(fiscal_year)

        return fiscal_year

    def close_fiscal_year(
        self,
        fiscal_year_id: str,
        user_id: str,
        generate_statements: bool = True
    ) -> FiscalYear:
        """
        Close a fiscal year

        Args:
            fiscal_year_id: ID of fiscal year to close
            user_id: User performing the close
            generate_statements: Whether to generate year-end statements

        Returns:
            Updated FiscalYear object
        """
        fiscal_year = self.db.query(FiscalYear).filter(
            FiscalYear.id == fiscal_year_id
        ).first()

        if not fiscal_year:
            raise ValueError("Fiscal year not found")

        if fiscal_year.status == FiscalYearStatus.CLOSED:
            raise ValueError("Fiscal year is already closed")

        # Check all periods are closed (LOCKED counts as closed — IAS 10)
        open_periods = self.db.query(FiscalPeriod).filter(
            and_(
                FiscalPeriod.fiscal_year_id == fiscal_year_id,
                FiscalPeriod.status.notin_(
                    [PeriodStatus.HARD_CLOSED, PeriodStatus.LOCKED]
                ),
            )
        ).count()

        if open_periods > 0:
            raise ValueError(
                f"{open_periods} period(s) are still open. "
                "All periods must be hard-closed or locked before closing the year."
            )

        # Generate year-end statements if requested
        if generate_statements:
            # Get the last period of the year
            last_period = self.db.query(FiscalPeriod).filter(
                FiscalPeriod.fiscal_year_id == fiscal_year_id
            ).order_by(desc(FiscalPeriod.period_number)).first()

            if last_period:
                self.generate_period_statements(
                    period_id=last_period.id,
                    user_id=user_id,
                    statement_types=['trial_balance', 'income_statement',
                                   'balance_sheet', 'cash_flow']
                )

        # Close the year
        fiscal_year.status = FiscalYearStatus.CLOSED
        fiscal_year.closed_date = datetime.utcnow()
        fiscal_year.closed_by_user_id = user_id
        fiscal_year.is_active = False

        self.db.commit()
        self.db.refresh(fiscal_year)

        return fiscal_year

    # ==================== BALANCE CARRYOVER (IFRS COMPLIANCE) ====================

    def calculate_opening_balances(
        self,
        fiscal_year_id: str
    ) -> Dict[str, Dict[str, Any]]:
        """
        Calculate opening balances for a fiscal year using prior year closing balances.

        IFRS principle: Balance sheet accounts carry forward automatically.
        Opening balance = prior year closing balance.
        """
        fiscal_year = self.db.query(FiscalYear).filter(
            FiscalYear.id == fiscal_year_id
        ).first()

        if not fiscal_year:
            raise ValueError("Fiscal year not found")

        prior_year = self.db.query(FiscalYear).filter(
            and_(
                FiscalYear.branch_id == fiscal_year.branch_id,
                FiscalYear.end_date < fiscal_year.start_date
            )
        ).order_by(desc(FiscalYear.end_date)).first()

        opening_balances: Dict[str, Dict[str, Any]] = {}

        if not prior_year:
            # First fiscal year: opening balances start at zero for balance sheet accounts.
            accounts = self.db.query(AccountingCode).filter(
                AccountingCode.account_type.in_(["Asset", "Liability", "Equity"])
            ).all()

            for account in accounts:
                opening_balances[account.id] = {
                    "account_id": account.id,
                    "account_code": account.code,
                    "account_name": account.name,
                    "opening_balance": Decimal("0"),
                    "prior_year_id": None,
                    "source": "first_year"
                }

            return opening_balances

        query = self.db.query(
            JournalEntry.accounting_code_id.label("account_id"),
            func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount).label("balance")
        ).join(
            AccountingEntry,
            JournalEntry.accounting_entry_id == AccountingEntry.id
        ).join(
            AccountingCode,
            JournalEntry.accounting_code_id == AccountingCode.id
        ).filter(
            and_(
                AccountingEntry.status == "posted",
                JournalEntry.date <= prior_year.end_date,
                AccountingCode.account_type.in_(["Asset", "Liability", "Equity"])
            )
        )

        if fiscal_year.branch_id:
            query = query.filter(JournalEntry.branch_id == fiscal_year.branch_id)

        balances = query.group_by(JournalEntry.accounting_code_id).all()

        for account_id, balance in balances:
            account = self.db.query(AccountingCode).filter(
                AccountingCode.id == account_id
            ).first()

            if not account:
                continue

            opening_balances[account.id] = {
                "account_id": account.id,
                "account_code": account.code,
                "account_name": account.name,
                "opening_balance": Decimal(str(balance or 0)),
                "prior_year_id": prior_year.id,
                "source": "prior_year_closing"
            }

        # ── IAS 1 / IAS 8: Roll prior-year net income into Retained Earnings ──
        # Because no automated closing journal entries are posted, we must
        # simulate the year-end P&L transfer to Retained Earnings so that
        # the opening Balance Sheet for the new year satisfies A = L + E.
        prior_revenue = self.db.query(
            func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount)
        ).join(
            AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id
        ).join(
            AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id
        ).filter(
            and_(
                AccountingEntry.status == "posted",
                AccountingCode.account_type == "Revenue",
                JournalEntry.date >= prior_year.start_date,
                JournalEntry.date <= prior_year.end_date,
            )
        ).scalar() or 0

        prior_expenses = self.db.query(
            func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount)
        ).join(
            AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id
        ).join(
            AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id
        ).filter(
            and_(
                AccountingEntry.status == "posted",
                AccountingCode.account_type == "Expense",
                JournalEntry.date >= prior_year.start_date,
                JournalEntry.date <= prior_year.end_date,
            )
        ).scalar() or 0

        prior_net_income = Decimal(str(prior_revenue)) - Decimal(str(prior_expenses))

        if prior_net_income != Decimal("0"):
            # Find Retained Earnings account (code 3200 or name match)
            ret_earnings = self.db.query(AccountingCode).filter(
                or_(
                    AccountingCode.code == "3200",
                    AccountingCode.name.ilike("%retained earnings%"),
                )
            ).first()

            if ret_earnings:
                if ret_earnings.id in opening_balances:
                    opening_balances[ret_earnings.id]["opening_balance"] += prior_net_income
                    opening_balances[ret_earnings.id]["source"] = "prior_year_closing+net_income"
                else:
                    opening_balances[ret_earnings.id] = {
                        "account_id": ret_earnings.id,
                        "account_code": ret_earnings.code,
                        "account_name": ret_earnings.name,
                        "opening_balance": prior_net_income,
                        "prior_year_id": prior_year.id,
                        "source": "net_income_carryover",
                    }

        return opening_balances

    def apply_opening_balances(
        self,
        fiscal_year_id: str,
        user_id: str
    ) -> Dict[str, Any]:
        """
        Persist opening balances for the fiscal year using the OpeningBalance table.

        Uses fiscal_year.start_date.year as the stored year key for compatibility
        with existing opening balance lookups.
        """
        fiscal_year = self.db.query(FiscalYear).filter(
            FiscalYear.id == fiscal_year_id
        ).first()

        if not fiscal_year:
            raise ValueError("Fiscal year not found")

        opening_balances = self.calculate_opening_balances(fiscal_year_id)
        year_key = fiscal_year.start_date.year

        for data in opening_balances.values():
            account_id = data["account_id"]
            amount = data["opening_balance"]

            existing = self.db.query(OpeningBalance).filter(
                and_(
                    OpeningBalance.accounting_code_id == account_id,
                    OpeningBalance.year == year_key
                )
            ).first()

            if existing:
                existing.amount = amount
            else:
                self.db.add(OpeningBalance(
                    accounting_code_id=account_id,
                    year=year_key,
                    amount=amount
                ))

        self.db.commit()

        count = len(opening_balances)
        return {
            "fiscal_year_id": fiscal_year.id,
            "fiscal_year_name": fiscal_year.year_name,
            "year_key": year_key,
            "applied_count": count,
            "opening_balances_count": count,
            "updated_by_user_id": user_id,
        }

    def generate_year_end_reports(self, fiscal_year_id: str) -> Dict[str, Any]:
        """Generate a full year-end report package including tax reports."""
        fiscal_year = self.db.query(FiscalYear).filter(
            FiscalYear.id == fiscal_year_id
        ).first()

        if not fiscal_year:
            raise ValueError("Fiscal year not found")

        periods = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.fiscal_year_id == fiscal_year_id
        ).order_by(FiscalPeriod.period_number).all()

        if not periods:
            if fiscal_year.auto_create_periods:
                period_type = fiscal_year.period_type_default or PeriodType.MONTHLY
                self._auto_create_periods(fiscal_year, period_type)
                self.db.commit()
                periods = self.db.query(FiscalPeriod).filter(
                    FiscalPeriod.fiscal_year_id == fiscal_year_id
                ).order_by(FiscalPeriod.period_number).all()

            if not periods:
                raise ValueError("No periods found for fiscal year")

        first_period = periods[0]
        last_period = periods[-1]

        year_period = SimpleNamespace(
            period_name=fiscal_year.year_name,
            start_date=fiscal_year.start_date,
            end_date=fiscal_year.end_date
        )

        # Opening balance sheet: as of day before fiscal year starts.
        # period_start=None because the prior year's P&L is already rolled into
        # Retained Earnings via closing entries (or the opening balance carryover).
        opening_as_of = fiscal_year.start_date - timedelta(days=1)
        opening_balance_sheet = self._generate_balance_sheet_as_of_date(
            opening_as_of,
            fiscal_year.branch_id,
            period_start=None,
        )
        opening_balance_sheet["statement_date"] = opening_as_of.isoformat()
        opening_balance_sheet["period_name"] = f"Opening - {fiscal_year.year_name}"

        # Year-end balance sheet: include YTD net income in equity (IAS 1 para 79)
        year_end_balance_sheet = self._generate_balance_sheet_as_of_date(
            fiscal_year.end_date,
            fiscal_year.branch_id,
            period_start=fiscal_year.start_date,
        )
        year_end_balance_sheet["statement_date"] = fiscal_year.end_date.isoformat()
        year_end_balance_sheet["period_name"] = f"Year End - {fiscal_year.year_name}"

        trial_balance = self._generate_trial_balance_for_range(
            fiscal_year.start_date,
            fiscal_year.end_date,
            fiscal_year.branch_id,
            fiscal_year.year_name
        )

        income_statement = self._generate_income_statement_for_range(
            fiscal_year.start_date,
            fiscal_year.end_date,
            fiscal_year.branch_id,
            fiscal_year.year_name
        )

        cash_flow = self._generate_cash_flow(year_period)

        tax_settings = self._get_tax_settings()
        vat_report = self._generate_vat_report(
            fiscal_year.start_date,
            fiscal_year.end_date,
            fiscal_year.branch_id
        )

        if tax_settings.get("apply_corporate_tax_reports", True):
            corporate_tax_report = self._generate_corporate_tax_report(
                income_statement,
                tax_settings
            )
        else:
            corporate_tax_report = {
                "report_type": "corporate_tax",
                "enabled": False,
                "message": "Corporate tax reporting disabled in settings",
                "period_start": income_statement.get("period_start"),
                "period_end": income_statement.get("period_end")
            }

        if tax_settings.get("apply_capital_gains_tax_reports", True):
            capital_gains_tax_report = self._generate_capital_gains_tax_report(
                fiscal_year.start_date,
                fiscal_year.end_date,
                fiscal_year.branch_id,
                tax_settings
            )
        else:
            capital_gains_tax_report = {
                "report_type": "capital_gains_tax",
                "enabled": False,
                "message": "Capital gains tax reporting disabled in settings",
                "period_start": fiscal_year.start_date.isoformat(),
                "period_end": fiscal_year.end_date.isoformat()
            }

        return {
            "fiscal_year_id": fiscal_year.id,
            "fiscal_year_name": fiscal_year.year_name,
            "opening_balance_sheet": opening_balance_sheet,
            "year_end_balance_sheet": year_end_balance_sheet,
            "trial_balance": trial_balance,
            "income_statement": income_statement,
            "cash_flow": cash_flow,
            "vat_report": vat_report,
            "corporate_tax_report": corporate_tax_report,
            "capital_gains_tax_report": capital_gains_tax_report
        }

    def save_year_end_reports(self, fiscal_year_id: str, user_id: str) -> Dict[str, Any]:
        """Persist year-end report package into PeriodEndStatement records."""
        fiscal_year = self.db.query(FiscalYear).filter(
            FiscalYear.id == fiscal_year_id
        ).first()

        if not fiscal_year:
            raise ValueError("Fiscal year not found")

        periods = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.fiscal_year_id == fiscal_year_id
        ).order_by(FiscalPeriod.period_number).all()

        if not periods:
            raise ValueError("No periods found for fiscal year")

        first_period = periods[0]
        last_period = periods[-1]
        reports = self.generate_year_end_reports(fiscal_year_id)

        statement_map = [
            ("opening_balance_sheet", "Opening Balance Sheet", first_period, fiscal_year.start_date),
            ("year_end_balance_sheet", "Year-End Balance Sheet", last_period, fiscal_year.end_date),
            ("trial_balance", "Trial Balance", last_period, fiscal_year.end_date),
            ("income_statement", "Income Statement", last_period, fiscal_year.end_date),
            ("cash_flow", "Cash Flow Statement", last_period, fiscal_year.end_date),
            ("vat_report", "VAT Report", last_period, fiscal_year.end_date),
            ("corporate_tax_report", "Corporate Tax Report", last_period, fiscal_year.end_date),
            ("capital_gains_tax_report", "Capital Gains Tax Report", last_period, fiscal_year.end_date),
        ]

        created = []
        for report_key, label, period, statement_date in statement_map:
            statement = PeriodEndStatement(
                fiscal_period_id=period.id,
                fiscal_year_id=fiscal_year.id,
                statement_type=report_key,
                statement_name=f"{label} - {fiscal_year.year_name}",
                statement_date=statement_date,
                generated_by_user_id=user_id,
                statement_data=_make_json_safe(reports.get(report_key, {})),
                branch_id=fiscal_year.branch_id
            )
            self.db.add(statement)
            created.append(statement)

        self.db.commit()

        return {
            "fiscal_year_id": fiscal_year.id,
            "fiscal_year_name": fiscal_year.year_name,
            "saved_reports": len(created)
        }

    def export_year_end_report(self, fiscal_year_id: str, report_type: str, format_type: str) -> Tuple[bytes, str, str]:
        """Export a single year-end report as PDF or CSV."""
        reports = self.generate_year_end_reports(fiscal_year_id)
        report_data = reports.get(report_type)

        if not report_data:
            raise ValueError("Report type not found")

        fiscal_year_name = reports.get("fiscal_year_name", "Fiscal Year")
        filename_base = f"{report_type}_{fiscal_year_name.replace(' ', '_')}"

        if format_type == "pdf":
            from app.services.report_export_utils import export_key_value_pdf, export_simple_table_pdf
            period = {
                "start": report_data.get("period_start") or report_data.get("statement_date") or "",
                "end": report_data.get("period_end") or report_data.get("statement_date") or ""
            }

            if report_type == "trial_balance":
                rows = report_data.get("items", [])
                columns = ["Code", "Name", "Debit", "Credit"]
                table_rows = [
                    [r.get("code"), r.get("name"), r.get("debit", 0), r.get("credit", 0)]
                    for r in rows
                ]
                pdf = export_simple_table_pdf(
                    "Trial Balance",
                    period,
                    columns,
                    table_rows
                )
            else:
                pdf = export_key_value_pdf(report_type.replace('_', ' ').title(), period, report_data)

            return pdf.read(), f"{filename_base}.pdf", "application/pdf"

        if format_type == "csv":
            import csv
            from io import StringIO

            buffer = StringIO()
            writer = csv.writer(buffer)

            if report_type == "trial_balance":
                writer.writerow(["Code", "Name", "Debit", "Credit"])
                for row in report_data.get("items", []):
                    writer.writerow([
                        row.get("code"),
                        row.get("name"),
                        row.get("debit", 0),
                        row.get("credit", 0)
                    ])
            else:
                writer.writerow(["Key", "Value"])
                for key, value in report_data.items():
                    writer.writerow([key, value])

            return buffer.getvalue().encode("utf-8"), f"{filename_base}.csv", "text/csv"

        raise ValueError("Unsupported export format")

    def _generate_balance_sheet_as_of_date(
        self,
        as_of_date: date,
        branch_id: Optional[str],
        period_start: Optional[date] = None,
    ) -> Dict[str, Any]:
        """
        Generate balance sheet (Statement of Financial Position) as of a specific date.

        IFRS IAS 1 compliance:
        - Only POSTED journal entries are included.
        - Because this system does not auto-post closing entries, the current-year
          net income (Revenue credits – Expense debits from period_start → as_of_date)
          is computed separately and included in Total Equity so that the
          fundamental equation Assets = Liabilities + Equity holds.
        - When period_start is None the P&L adjustment is omitted (used for
          'opening balance sheet as of prior-year-end' where closing is complete).
        """

        def _bs_query(account_type: str, debit_minus_credit: bool):
            col = (
                JournalEntry.debit_amount - JournalEntry.credit_amount
                if debit_minus_credit
                else JournalEntry.credit_amount - JournalEntry.debit_amount
            )
            q = (
                self.db.query(func.sum(col))
                .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
                .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
                .filter(
                    and_(
                        AccountingEntry.status == "posted",
                        AccountingCode.account_type == account_type,
                        JournalEntry.date <= as_of_date,
                    )
                )
            )
            if branch_id:
                q = q.filter(JournalEntry.branch_id == branch_id)
            return float(q.scalar() or 0)

        assets = _bs_query("Asset", True)
        liabilities = _bs_query("Liability", False)
        stated_equity = _bs_query("Equity", False)

        # IAS 1 para 79 – include undistributed net income in equity
        # (required when the system has no automated closing-entry posting)
        net_income_ytd = 0.0
        if period_start is not None:
            revenue_q = (
                self.db.query(
                    func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount)
                )
                .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
                .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
                .filter(
                    and_(
                        AccountingEntry.status == "posted",
                        AccountingCode.account_type == "Revenue",
                        JournalEntry.date >= period_start,
                        JournalEntry.date <= as_of_date,
                    )
                )
            )
            expense_q = (
                self.db.query(
                    func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount)
                )
                .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
                .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
                .filter(
                    and_(
                        AccountingEntry.status == "posted",
                        AccountingCode.account_type == "Expense",
                        JournalEntry.date >= period_start,
                        JournalEntry.date <= as_of_date,
                    )
                )
            )
            if branch_id:
                revenue_q = revenue_q.filter(JournalEntry.branch_id == branch_id)
                expense_q = expense_q.filter(JournalEntry.branch_id == branch_id)
            net_income_ytd = float(revenue_q.scalar() or 0) - float(expense_q.scalar() or 0)

        total_equity = stated_equity + net_income_ytd

        return {
            "statement_date": as_of_date.isoformat(),
            "total_assets": round(assets, 2),
            "total_liabilities": round(liabilities, 2),
            "stated_equity": round(stated_equity, 2),
            "net_income_ytd": round(net_income_ytd, 2),
            "total_equity": round(total_equity, 2),
            "balance_check": round(assets - (liabilities + total_equity), 2),
            "ifrs_note": "Assets = Liabilities + Equity (includes undistributed P&L per IAS 1)",
        }

    def _generate_trial_balance_for_range(
        self,
        start_date: date,
        end_date: date,
        branch_id: Optional[str],
        label: str
    ) -> Dict[str, Any]:
        """
        Generate trial balance (movement) for a date range.

        IFRS IAS 1 compliance:
        - Only POSTED journal entries are included.
        - Balance sheet accounts (Asset/Liability/Equity) include the cumulative
          balance brought forward (c/fwd) as the opening column plus period
          movement, so the closing balance can be derived.
        """
        accounts = self.db.query(AccountingCode).order_by(AccountingCode.code).all()
        items = []
        total_debits = Decimal("0")
        total_credits = Decimal("0")

        for account in accounts:
            # Period movements (all account types: start_date → end_date)
            q = (
                self.db.query(
                    func.sum(JournalEntry.debit_amount).label("debits"),
                    func.sum(JournalEntry.credit_amount).label("credits"),
                )
                .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
                .filter(
                    and_(
                        AccountingEntry.status == "posted",
                        JournalEntry.accounting_code_id == account.id,
                        JournalEntry.date >= start_date,
                        JournalEntry.date <= end_date,
                    )
                )
            )
            if branch_id:
                q = q.filter(JournalEntry.branch_id == branch_id)
            row = q.first()
            debit_total = float(row.debits or 0)
            credit_total = float(row.credits or 0)

            # Opening brought-forward balance for balance sheet accounts
            opening_bf = 0.0
            if account.account_type in ("Asset", "Liability", "Equity"):
                bf_q = (
                    self.db.query(
                        func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount)
                    )
                    .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
                    .filter(
                        and_(
                            AccountingEntry.status == "posted",
                            JournalEntry.accounting_code_id == account.id,
                            JournalEntry.date < start_date,
                        )
                    )
                )
                if branch_id:
                    bf_q = bf_q.filter(JournalEntry.branch_id == branch_id)
                opening_bf = float(bf_q.scalar() or 0)

            if debit_total > 0 or credit_total > 0 or opening_bf != 0:
                items.append({
                    "code": account.code,
                    "name": account.name,
                    "account_type": account.account_type,
                    "opening_balance": round(opening_bf, 2),
                    "debit": round(debit_total, 2),
                    "credit": round(credit_total, 2),
                    "closing_balance": round(opening_bf + debit_total - credit_total, 2),
                })
                total_debits += Decimal(str(debit_total))
                total_credits += Decimal(str(credit_total))

        return {
            "period_name": label,
            "period_start": start_date.isoformat(),
            "period_end": end_date.isoformat(),
            "items": items,
            "total_debits": float(total_debits),
            "total_credits": float(total_credits),
            "difference": float(total_debits - total_credits),
            "is_balanced": abs(float(total_debits - total_credits)) < 0.01,
            "ifrs_note": "Period movements only (IAS 1). BS accounts show opening b/fwd and closing balance.",
        }

    def _generate_income_statement_for_range(
        self,
        start_date: date,
        end_date: date,
        branch_id: Optional[str],
        label: str
    ) -> Dict[str, Any]:
        """
        Generate income statement (Statement of Profit or Loss) for a date range.

        IFRS IAS 1 compliance:
        - Only POSTED journal entries are included.
        - Revenue and Expense accounts are period-scoped (start_date → end_date)
          so prior-year balances are not mixed in.
        - Gross profit is shown separately (Revenue – COGS) where COGS accounts
          are identified by category containing 'cost of' or 'cogs'.
        """

        def _is_query(account_type: str, debit_minus_credit: bool):
            col = (
                JournalEntry.debit_amount - JournalEntry.credit_amount
                if debit_minus_credit
                else JournalEntry.credit_amount - JournalEntry.debit_amount
            )
            q = (
                self.db.query(func.sum(col))
                .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
                .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
                .filter(
                    and_(
                        AccountingEntry.status == "posted",
                        AccountingCode.account_type == account_type,
                        JournalEntry.date >= start_date,
                        JournalEntry.date <= end_date,
                    )
                )
            )
            if branch_id:
                q = q.filter(JournalEntry.branch_id == branch_id)
            return float(q.scalar() or 0)

        # Revenue (credit-normal)
        revenue = _is_query("Revenue", False)

        # COGS – expenses in 'cost of' or 'cogs' categories (IAS 2 / IAS 1)
        cogs_q = (
            self.db.query(
                func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount)
            )
            .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
            .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
            .filter(
                and_(
                    AccountingEntry.status == "posted",
                    AccountingCode.account_type == "Expense",
                    or_(
                        AccountingCode.category.ilike("%cost of%"),
                        AccountingCode.category.ilike("%cogs%"),
                        AccountingCode.name.ilike("%cost of goods%"),
                    ),
                    JournalEntry.date >= start_date,
                    JournalEntry.date <= end_date,
                )
            )
        )
        if branch_id:
            cogs_q = cogs_q.filter(JournalEntry.branch_id == branch_id)
        cogs = float(cogs_q.scalar() or 0)

        # All expenses
        total_expenses = _is_query("Expense", True)
        opex = total_expenses - cogs
        gross_profit = revenue - cogs
        net_income = revenue - total_expenses

        return {
            "period_name": label,
            "period_start": start_date.isoformat(),
            "period_end": end_date.isoformat(),
            "total_revenue": round(revenue, 2),
            "cost_of_goods_sold": round(cogs, 2),
            "gross_profit": round(gross_profit, 2),
            "total_operating_expenses": round(opex, 2),
            "total_expenses": round(total_expenses, 2),
            "net_income": round(net_income, 2),
            "gross_margin_pct": round((gross_profit / revenue * 100) if revenue != 0 else 0.0, 2),
            "net_margin_pct": round((net_income / revenue * 100) if revenue != 0 else 0.0, 2),
            "ifrs_note": "Revenue and Expense accounts scoped to period only (IAS 1).",
        }

    def _get_tax_settings(self) -> Dict[str, Any]:
        settings = AppSetting.get_instance(self.db)
        try:
            return settings.tax_settings
        except AttributeError:
            return {
                "corporate_tax_rate": 0.0,
                "capital_gains_tax_rate": 0.0,
                "capital_gains_income_account_codes": [],
                "capital_gains_cost_account_codes": [],
                "tax_authority": "",
                "apply_corporate_tax_reports": True,
                "apply_capital_gains_tax_reports": True
            }

    def _generate_vat_report(self, start_date: date, end_date: date, branch_id: Optional[str]) -> Dict[str, Any]:
        vat_service = VatService(self.db)
        summary = vat_service.get_vat_summary_range(start_date, end_date, branch_id)
        return {
            **summary,
            'report_type': 'vat',
            'period_start': start_date.isoformat(),
            'period_end': end_date.isoformat()
        }

    def _generate_corporate_tax_report(self, income_statement: Dict[str, Any], tax_settings: Dict[str, Any]) -> Dict[str, Any]:
        rate = float(tax_settings.get('corporate_tax_rate', 0.0) or 0.0)
        taxable_income = float(income_statement.get('net_income', 0.0) or 0.0)
        tax_due = taxable_income * (rate / 100.0) if taxable_income > 0 else 0.0

        return {
            'report_type': 'corporate_tax',
            'taxable_income': taxable_income,
            'corporate_tax_rate': rate,
            'corporate_tax_due': tax_due,
            'period_start': income_statement.get('period_start'),
            'period_end': income_statement.get('period_end')
        }

    def _generate_capital_gains_tax_report(
        self,
        start_date: date,
        end_date: date,
        branch_id: Optional[str],
        tax_settings: Dict[str, Any]
    ) -> Dict[str, Any]:
        rate = float(tax_settings.get('capital_gains_tax_rate', 0.0) or 0.0)
        income_codes = tax_settings.get('capital_gains_income_account_codes', [])
        cost_codes = tax_settings.get('capital_gains_cost_account_codes', [])

        def _sum_for_codes(codes: List[str], debit_minus_credit: bool) -> float:
            if not codes:
                return 0.0

            query = self.db.query(
                func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount)
                if debit_minus_credit else
                func.sum(JournalEntry.credit_amount - JournalEntry.debit_amount)
            ).join(AccountingCode).filter(
                and_(
                    AccountingCode.code.in_(codes),
                    JournalEntry.date >= start_date,
                    JournalEntry.date <= end_date
                )
            )

            if branch_id:
                query = query.filter(JournalEntry.branch_id == branch_id)

            return float(query.scalar() or 0)

        gains = _sum_for_codes(income_codes, debit_minus_credit=False)
        costs = _sum_for_codes(cost_codes, debit_minus_credit=True)
        net_gain = gains - costs
        tax_due = net_gain * (rate / 100.0) if net_gain > 0 else 0.0

        return {
            'report_type': 'capital_gains_tax',
            'capital_gains_income': gains,
            'capital_gains_costs': costs,
            'net_capital_gain': net_gain,
            'capital_gains_tax_rate': rate,
            'capital_gains_tax_due': tax_due,
            'period_start': start_date.isoformat(),
            'period_end': end_date.isoformat()
        }

    # ==================== PERIOD MANAGEMENT ====================

    def open_period(
        self,
        period_id: str,
        user_id: str,
        create_checklist: bool = True
    ) -> FiscalPeriod:
        """
        Open a period for transactions

        Args:
            period_id: ID of period to open
            user_id: User opening the period
            create_checklist: Whether to create a period-end checklist

        Returns:
            Updated FiscalPeriod object
        """
        period = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.id == period_id
        ).first()

        if not period:
            raise ValueError("Period not found")

        if period.status not in [PeriodStatus.DRAFT, PeriodStatus.SOFT_CLOSED]:
            raise ValueError(
                f"Cannot open period with status '{period.status.value}'"
            )

        # Open the period
        period.status = PeriodStatus.OPEN
        period.opened_date = datetime.utcnow()
        period.opened_by_user_id = user_id
        period.is_locked = False

        # Create checklist if requested
        if create_checklist:
            self._create_period_checklist(period)

        self.db.commit()
        self.db.refresh(period)

        return period

    def soft_close_period(
        self,
        period_id: str,
        user_id: str,
        notes: Optional[str] = None
    ) -> FiscalPeriod:
        """
        Soft close a period (can be reopened by Finance Manager)

        Args:
            period_id: ID of period to close
            user_id: User closing the period
            notes: Optional closing notes

        Returns:
            Updated FiscalPeriod object
        """
        period = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.id == period_id
        ).first()

        if not period:
            raise ValueError("Period not found")

        if period.status != PeriodStatus.OPEN:
            raise ValueError("Can only soft-close an open period")

        # Calculate period statistics
        stats = self._calculate_period_statistics(period)

        # Soft close the period
        period.status = PeriodStatus.SOFT_CLOSED
        period.soft_closed_date = datetime.utcnow()
        period.soft_closed_by_user_id = user_id
        period.total_debits = stats['total_debits']
        period.total_credits = stats['total_credits']
        period.transaction_count = stats['transaction_count']

        if notes:
            period.notes = notes

        self.db.commit()
        self.db.refresh(period)

        return period

    def hard_close_period(
        self,
        period_id: str,
        user_id: str,
        generate_statements: bool = True,
        notes: Optional[str] = None
    ) -> FiscalPeriod:
        """
        Hard close a period (requires CFO to reopen)

        Args:
            period_id: ID of period to close
            user_id: User closing the period
            generate_statements: Whether to generate period-end statements
            notes: Optional closing notes

        Returns:
            Updated FiscalPeriod object
        """
        period = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.id == period_id
        ).first()

        if not period:
            raise ValueError("Period not found")

        if period.status not in [PeriodStatus.OPEN, PeriodStatus.SOFT_CLOSED]:
            raise ValueError(
                f"Cannot hard-close period with status '{period.status.value}'"
            )

        # Check if checklist is complete (if exists)
        checklist = self.db.query(PeriodCloseChecklist).filter(
            and_(
                PeriodCloseChecklist.fiscal_period_id == period_id,
                PeriodCloseChecklist.is_template == False
            )
        ).first()

        if checklist and not checklist.is_complete:
            # Allow hard close when period has no posted activity (empty period)
            stats = self._calculate_period_statistics(period)
            if stats["transaction_count"] > 0:
                raise ValueError(
                    "Period-end checklist is not complete. "
                    f"{checklist.completed_items}/{checklist.total_items} items completed."
                )

        # Calculate period statistics if not already done
        if not period.total_debits:
            stats = self._calculate_period_statistics(period)
            period.total_debits = stats['total_debits']
            period.total_credits = stats['total_credits']
            period.transaction_count = stats['transaction_count']

        # Generate statements if requested
        if generate_statements:
            self.generate_period_statements(
                period_id=period_id,
                user_id=user_id,
                statement_types=['trial_balance', 'income_statement',
                               'balance_sheet']
            )

        # Hard close the period
        period.status = PeriodStatus.HARD_CLOSED
        period.hard_closed_date = datetime.utcnow()
        period.hard_closed_by_user_id = user_id

        if notes:
            period.notes = notes

        self.db.commit()
        self.db.refresh(period)

        return period

    def reopen_period(
        self,
        period_id: str,
        user_id: str,
        reason: str
    ) -> FiscalPeriod:
        """
        Reopen a closed period

        Args:
            period_id: ID of period to reopen
            user_id: User reopening the period
            reason: Reason for reopening

        Returns:
            Updated FiscalPeriod object
        """
        period = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.id == period_id
        ).first()

        if not period:
            raise ValueError("Period not found")

        if period.status not in [PeriodStatus.SOFT_CLOSED, PeriodStatus.HARD_CLOSED]:
            raise ValueError("Can only reopen closed periods")

        if period.is_locked:
            raise ValueError("Cannot reopen locked period (unlock first)")

        # Reopen the period
        period.status = PeriodStatus.OPEN
        period.last_reopened_date = datetime.utcnow()
        period.last_reopened_by_user_id = user_id
        period.reopen_count += 1

        # Add note about reopening
        reopen_note = f"\n[{datetime.utcnow().isoformat()}] Reopened by user {user_id}: {reason}"
        period.notes = (period.notes or "") + reopen_note

        self.db.commit()
        self.db.refresh(period)

        return period

    def lock_period(
        self,
        period_id: str,
        user_id: str,
        reason: str = "Audit lock"
    ) -> FiscalPeriod:
        """
        Lock a period (prevents any changes)

        Args:
            period_id: ID of period to lock
            user_id: User locking the period
            reason: Reason for locking

        Returns:
            Updated FiscalPeriod object
        """
        period = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.id == period_id
        ).first()

        if not period:
            raise ValueError("Period not found")

        period.is_locked = True
        period.locked_date = datetime.utcnow()
        period.locked_by_user_id = user_id
        period.locked_reason = reason
        period.status = PeriodStatus.LOCKED

        self.db.commit()
        self.db.refresh(period)

        return period

    def unlock_period(
        self,
        period_id: str,
        user_id: str
    ) -> FiscalPeriod:
        """
        Unlock a period

        Args:
            period_id: ID of period to unlock
            user_id: User unlocking the period

        Returns:
            Updated FiscalPeriod object
        """
        period = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.id == period_id
        ).first()

        if not period:
            raise ValueError("Period not found")

        if not period.is_locked:
            raise ValueError("Period is not locked")

        # Unlock and return to previous status
        period.is_locked = False

        # Restore previous status based on closure dates
        if period.hard_closed_date:
            period.status = PeriodStatus.HARD_CLOSED
        elif period.soft_closed_date:
            period.status = PeriodStatus.SOFT_CLOSED
        else:
            period.status = PeriodStatus.OPEN

        # Add note about unlocking
        unlock_note = f"\n[{datetime.utcnow().isoformat()}] Unlocked by user {user_id}"
        period.notes = (period.notes or "") + unlock_note

        self.db.commit()
        self.db.refresh(period)

        return period

    # ==================== HELPERS ====================

    def _calculate_period_statistics(self, period: FiscalPeriod) -> Dict[str, Any]:
        """Calculate transaction statistics for a period"""
        stats = self.db.query(
            func.sum(JournalEntry.debit_amount).label('total_debits'),
            func.sum(JournalEntry.credit_amount).label('total_credits'),
            func.count(JournalEntry.id).label('transaction_count')
        ).filter(
            and_(
                JournalEntry.date >= period.start_date,
                JournalEntry.date <= period.end_date
            )
        ).first()

        return {
            'total_debits': stats.total_debits or 0,
            'total_credits': stats.total_credits or 0,
            'transaction_count': stats.transaction_count or 0
        }

    def _create_period_checklist(self, period: FiscalPeriod):
        """Create a period-end checklist for a period"""
        # Default checklist items
        default_items = [
            {"task": "Reconcile all bank accounts", "status": "pending", "required": True, "order": 1},
            {"task": "Review accounts receivable aging", "status": "pending", "required": True, "order": 2},
            {"task": "Review accounts payable aging", "status": "pending", "required": True, "order": 3},
            {"task": "Reconcile inventory", "status": "pending", "required": True, "order": 4},
            {"task": "Review fixed assets register", "status": "pending", "required": False, "order": 5},
            {"task": "Accrue expenses and revenues", "status": "pending", "required": True, "order": 6},
            {"task": "Review prepayments and deferrals", "status": "pending", "required": True, "order": 7},
            {"task": "Calculate depreciation", "status": "pending", "required": False, "order": 8},
            {"task": "Reconcile VAT accounts", "status": "pending", "required": True, "order": 9},
            {"task": "Review journal entries", "status": "pending", "required": True, "order": 10},
            {"task": "Run trial balance", "status": "pending", "required": True, "order": 11},
            {"task": "Generate financial statements", "status": "pending", "required": True, "order": 12},
        ]

        checklist = PeriodCloseChecklist(
            is_template=False,
            fiscal_period_id=period.id,
            checklist_items=default_items,
            total_items=len(default_items),
            completed_items=0,
            completion_percentage=0.0,
            is_complete=False
        )

        self.db.add(checklist)

    def generate_period_statements(
        self,
        period_id: str,
        user_id: str,
        statement_types: List[str] = None
    ) -> List[PeriodEndStatement]:
        """
        Generate and save period-end financial statements

        Args:
            period_id: ID of period
            user_id: User generating statements
            statement_types: List of statement types to generate
                           (trial_balance, income_statement, balance_sheet, cash_flow)

        Returns:
            List of generated PeriodEndStatement objects
        """
        if statement_types is None:
            statement_types = ['trial_balance', 'income_statement', 'balance_sheet']

        period = self.db.query(FiscalPeriod).filter(
            FiscalPeriod.id == period_id
        ).first()

        if not period:
            raise ValueError("Period not found")

        statements = []

        for statement_type in statement_types:
            # Generate statement data based on type
            if statement_type == 'trial_balance':
                statement_data = self._generate_trial_balance(period)
                statement_name = f"Trial Balance - {period.period_name}"
            elif statement_type == 'income_statement':
                statement_data = self._generate_income_statement(period)
                statement_name = f"Income Statement - {period.period_name}"
            elif statement_type == 'balance_sheet':
                statement_data = self._generate_balance_sheet(period)
                statement_name = f"Balance Sheet - {period.period_name}"
            elif statement_type == 'cash_flow':
                statement_data = self._generate_cash_flow(period)
                statement_name = f"Cash Flow Statement - {period.period_name}"
            else:
                continue

            # Create statement record
            statement = PeriodEndStatement(
                fiscal_period_id=period.id,
                fiscal_year_id=period.fiscal_year_id,
                statement_type=statement_type,
                statement_name=statement_name,
                statement_date=period.end_date,
                generated_by_user_id=user_id,
                statement_data=_make_json_safe(statement_data),
                branch_id=period.branch_id,
                # Populate summary metrics
                total_assets=statement_data.get('total_assets'),
                total_liabilities=statement_data.get('total_liabilities'),
                total_equity=statement_data.get('total_equity'),
                net_income=statement_data.get('net_income'),
                total_revenue=statement_data.get('total_revenue'),
                total_expenses=statement_data.get('total_expenses')
            )

            self.db.add(statement)
            statements.append(statement)

        self.db.commit()

        return statements

    def _generate_trial_balance(self, period: FiscalPeriod) -> Dict[str, Any]:
        """Generate trial balance for period (delegates to shared implementation)."""
        return self._generate_trial_balance_for_range(
            period.start_date, period.end_date, period.branch_id, period.period_name
        )

    def _generate_income_statement(self, period: FiscalPeriod) -> Dict[str, Any]:
        """Generate income statement for period (delegates to shared implementation)."""
        return self._generate_income_statement_for_range(
            period.start_date, period.end_date, period.branch_id, period.period_name
        )

    def _generate_balance_sheet(self, period: FiscalPeriod) -> Dict[str, Any]:
        """Generate balance sheet as of period end (delegates to shared implementation)."""
        # Determine fiscal year start for YTD net income inclusion (IAS 1)
        fy = self.db.query(FiscalYear).filter(FiscalYear.id == period.fiscal_year_id).first()
        period_start = fy.start_date if fy else None
        data = self._generate_balance_sheet_as_of_date(
            period.end_date, period.branch_id, period_start=period_start
        )
        data["period_name"] = period.period_name
        data["statement_date"] = period.end_date.isoformat()
        return data

    def _generate_cash_flow(self, period) -> Dict[str, Any]:
        """
        Generate Statement of Cash Flows using the indirect method (IAS 7).

        Structure:
          A. Operating Activities  – Net income adjusted for working-capital changes
          B. Investing Activities  – Net movement in non-current assets
          C. Financing Activities  – Net movement in equity and long-term debt
          D. Net change in cash    – A + B + C
          E. Closing cash balance  – cumulative Cash & Cash Equivalents

        Only POSTED journal entries are included (IAS 7 para 6).
        """
        start = period.start_date
        end = period.end_date
        period_name = period.period_name
        branch_id = getattr(period, "branch_id", None)

        def _sum(account_type: str, debit_minus_credit: bool,
                 date_from=None, date_to=None,
                 category_ilike: str = None) -> float:
            col = (
                JournalEntry.debit_amount - JournalEntry.credit_amount
                if debit_minus_credit
                else JournalEntry.credit_amount - JournalEntry.debit_amount
            )
            filters = [
                AccountingEntry.status == "posted",
                AccountingCode.account_type == account_type,
                JournalEntry.date >= (date_from or start),
                JournalEntry.date <= (date_to or end),
            ]
            if category_ilike:
                filters.append(AccountingCode.category.ilike(f"%{category_ilike}%"))
            q = (
                self.db.query(func.sum(col))
                .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
                .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
                .filter(and_(*filters))
            )
            if branch_id:
                q = q.filter(JournalEntry.branch_id == branch_id)
            return float(q.scalar() or 0)

        # ── A. Operating Activities (Indirect method) ────────────────────────
        net_income = _sum("Revenue", False) - _sum("Expense", True)

        # Working capital adjustments – increase in CA (excl. cash) uses cash
        receivables_movement = _sum("Asset", True, category_ilike="receivable")
        inventory_movement = _sum("Asset", True, category_ilike="inventor")
        # Increase in CL provides cash
        payables_movement = _sum("Liability", False, category_ilike="payable")
        tax_payable_movement = _sum("Liability", False, category_ilike="tax")

        operating_cf = (
            net_income
            - receivables_movement
            - inventory_movement
            + payables_movement
            + tax_payable_movement
        )

        # ── B. Investing Activities ──────────────────────────────────────────
        fixed_assets_movement = _sum("Asset", True, category_ilike="fixed")
        intangible_assets_movement = _sum("Asset", True, category_ilike="intangible")
        investing_cf = -(fixed_assets_movement + intangible_assets_movement)

        # ── C. Financing Activities ──────────────────────────────────────────
        equity_movement = _sum("Equity", False)
        long_term_debt_movement = _sum("Liability", False, category_ilike="long-term")
        financing_cf = equity_movement + long_term_debt_movement

        net_change = operating_cf + investing_cf + financing_cf

        # ── E. Closing cash & equivalents (cumulative to period end) ─────────
        cash_q = (
            self.db.query(
                func.sum(JournalEntry.debit_amount - JournalEntry.credit_amount)
            )
            .join(AccountingEntry, JournalEntry.accounting_entry_id == AccountingEntry.id)
            .join(AccountingCode, JournalEntry.accounting_code_id == AccountingCode.id)
            .filter(
                and_(
                    AccountingEntry.status == "posted",
                    AccountingCode.account_type == "Asset",
                    or_(
                        AccountingCode.category.ilike("%cash%"),
                        AccountingCode.name.ilike("%cash%"),
                    ),
                    JournalEntry.date <= end,
                )
            )
        )
        if branch_id:
            cash_q = cash_q.filter(JournalEntry.branch_id == branch_id)
        ending_cash = float(cash_q.scalar() or 0)

        return {
            "report_type": "cash_flow",
            "method": "indirect",
            "ifrs_standard": "IAS 7",
            "period_name": period_name,
            "period_start": start.isoformat() if hasattr(start, "isoformat") else str(start),
            "period_end": end.isoformat() if hasattr(end, "isoformat") else str(end),
            # A – Operating
            "net_income": round(net_income, 2),
            "adjustments_receivables": round(-receivables_movement, 2),
            "adjustments_inventory": round(-inventory_movement, 2),
            "adjustments_payables": round(payables_movement, 2),
            "adjustments_tax_payable": round(tax_payable_movement, 2),
            "net_operating_cash_flow": round(operating_cf, 2),
            # B – Investing
            "investing_fixed_assets": round(-fixed_assets_movement, 2),
            "investing_intangibles": round(-intangible_assets_movement, 2),
            "net_investing_cash_flow": round(investing_cf, 2),
            # C – Financing
            "financing_equity_movement": round(equity_movement, 2),
            "financing_long_term_debt": round(long_term_debt_movement, 2),
            "net_financing_cash_flow": round(financing_cf, 2),
            # Summary
            "net_change_in_cash": round(net_change, 2),
            "ending_cash_and_equivalents": round(ending_cash, 2),
        }

    def get_current_period(self, branch_id: Optional[str] = None) -> Optional[FiscalPeriod]:
        """Get the current period for today's date.

        Returns the period whose date range covers today, regardless of status.
        Prefer OPEN periods first; fall back to any date-matching period so the
        API never 404s just because the period was closed/hard-closed.
        """
        today = date.today()

        base_filter = [
            FiscalPeriod.start_date <= today,
            FiscalPeriod.end_date >= today,
        ]
        if branch_id:
            base_filter.append(FiscalPeriod.branch_id == branch_id)

        # Prefer open period
        period = self.db.query(FiscalPeriod).filter(
            and_(*base_filter, FiscalPeriod.status == PeriodStatus.OPEN)
        ).first()

        if not period:
            # Fall back to any period covering today (closed/hard-closed)
            period = self.db.query(FiscalPeriod).filter(
                and_(*base_filter)
            ).first()

        return period

    def get_periods_for_year(self, fiscal_year_id: str) -> List[FiscalPeriod]:
        """Get all periods for a fiscal year"""
        return self.db.query(FiscalPeriod).filter(
            FiscalPeriod.fiscal_year_id == fiscal_year_id
        ).order_by(FiscalPeriod.period_number).all()

    def get_period_summaries(self, fiscal_year_id: str) -> List[Dict[str, Any]]:
        """Get period summaries with live debit/credit totals for a fiscal year."""
        periods = self.get_periods_for_year(fiscal_year_id)
        today = date.today()
        summaries: List[Dict[str, Any]] = []

        for period in periods:
            stats = self._calculate_period_statistics(period)
            status = period.status.value if hasattr(period.status, "value") else period.status
            summaries.append({
                "period_id": period.id,
                "period_name": period.period_name,
                "period_code": period.period_code,
                "status": status,
                "is_locked": period.is_locked,
                "is_current": period.start_date <= today <= period.end_date,
                "transaction_count": stats["transaction_count"],
                "total_debits": float(stats["total_debits"]),
                "total_credits": float(stats["total_credits"]),
            })

        return summaries

    @staticmethod
    def fiscal_year_dates_for_date(
        ref_date: date,
        start_month: int = 1,
    ) -> tuple[date, date, str, str]:
        """Compute fiscal year start/end and labels that contain ref_date."""
        start_month = max(1, min(12, int(start_month or 1)))
        if ref_date.month >= start_month:
            start_year = ref_date.year
        else:
            start_year = ref_date.year - 1

        fy_start = date(start_year, start_month, 1)
        if start_month == 1:
            fy_end = date(start_year, 12, 31)
        else:
            fy_end = date(start_year + 1, start_month, 1) - timedelta(days=1)

        if fy_end.year == fy_start.year:
            year_name = f"FY {fy_start.year}"
            year_code = f"FY{fy_start.year}"
        else:
            year_name = f"FY {fy_start.year}-{fy_end.year}"
            year_code = f"FY{fy_start.year}{str(fy_end.year)[-2:]}"

        return fy_start, fy_end, year_name, year_code

    def bootstrap_default_fiscal_year(
        self,
        branch_id: Optional[str] = None,
        start_month: int = 1,
        user_id: Optional[str] = None,
        activate: bool = True,
    ) -> Optional[FiscalYear]:
        """
        Create and optionally activate a fiscal year covering today when none exists
        for the branch (or globally when branch_id is None).
        """
        today = date.today()
        query = self.db.query(FiscalYear)
        if branch_id:
            query = query.filter(FiscalYear.branch_id == branch_id)
        existing = query.filter(
            FiscalYear.start_date <= today,
            FiscalYear.end_date >= today,
        ).first()
        if existing:
            if activate and not existing.is_active and existing.start_date <= today:
                return self.activate_fiscal_year(existing.id, user_id)
            return existing

        fy_start, fy_end, year_name, year_code = self.fiscal_year_dates_for_date(
            today, start_month
        )

        if self.db.query(FiscalYear).filter(FiscalYear.year_code == year_code).first():
            year_code = f"{year_code}-{branch_id[:4] if branch_id else 'HQ'}"

        fiscal_year = self.create_fiscal_year(
            year_name=year_name,
            year_code=year_code,
            start_date=fy_start,
            end_date=fy_end,
            period_type=PeriodType.MONTHLY,
            auto_create_periods=True,
            branch_id=branch_id,
            notes="Auto-created by CNPERP setup",
        )

        if activate and fy_start <= today:
            try:
                fiscal_year = self.activate_fiscal_year(fiscal_year.id, user_id)
            except ValueError:
                pass

        return fiscal_year

    # ==================== HISTORICAL REPORTS & COMPARISON ====================

    def list_saved_reports(
        self,
        fiscal_year_id: Optional[str] = None,
        statement_type: Optional[str] = None,
    ) -> List[Dict[str, Any]]:
        """List all saved PeriodEndStatements with fiscal year metadata."""
        query = self.db.query(PeriodEndStatement, FiscalYear).join(
            FiscalYear, PeriodEndStatement.fiscal_year_id == FiscalYear.id
        )
        if fiscal_year_id:
            query = query.filter(PeriodEndStatement.fiscal_year_id == fiscal_year_id)
        if statement_type:
            query = query.filter(PeriodEndStatement.statement_type == statement_type)

        rows = query.order_by(
            desc(FiscalYear.start_date),
            PeriodEndStatement.statement_type,
        ).all()

        result = []
        for stmt, year in rows:
            generated_by = None
            if stmt.generated_by_user_id:
                from app.models.user import User as UserModel
                u = self.db.query(UserModel).filter(UserModel.id == stmt.generated_by_user_id).first()
                if u:
                    generated_by = u.username
            result.append({
                "id": stmt.id,
                "fiscal_year_id": stmt.fiscal_year_id,
                "fiscal_year_name": year.year_name,
                "fiscal_year_code": year.year_code,
                "fiscal_period_id": stmt.fiscal_period_id,
                "statement_type": stmt.statement_type,
                "statement_name": stmt.statement_name,
                "statement_date": stmt.statement_date.isoformat() if stmt.statement_date else None,
                "generated_date": stmt.generated_date.isoformat() if stmt.generated_date else None,
                "generated_by": generated_by,
                "branch_id": stmt.branch_id,
            })
        return result

    def get_saved_report(self, statement_id: str) -> Dict[str, Any]:
        """Get a single saved report with its full data."""
        stmt = self.db.query(PeriodEndStatement).filter(
            PeriodEndStatement.id == statement_id
        ).first()
        if not stmt:
            raise ValueError("Statement not found")

        year = self.db.query(FiscalYear).filter(FiscalYear.id == stmt.fiscal_year_id).first()
        return {
            "id": stmt.id,
            "fiscal_year_id": stmt.fiscal_year_id,
            "fiscal_year_name": year.year_name if year else None,
            "statement_type": stmt.statement_type,
            "statement_name": stmt.statement_name,
            "statement_date": stmt.statement_date.isoformat() if stmt.statement_date else None,
            "generated_date": stmt.generated_date.isoformat() if stmt.generated_date else None,
            "data": stmt.statement_data or {},
        }

    def export_saved_report(self, statement_id: str, format_type: str) -> Tuple[bytes, str, str]:
        """Export a saved PeriodEndStatement by ID as PDF or CSV."""
        stmt = self.db.query(PeriodEndStatement).filter(
            PeriodEndStatement.id == statement_id
        ).first()
        if not stmt:
            raise ValueError("Statement not found")

        year = self.db.query(FiscalYear).filter(FiscalYear.id == stmt.fiscal_year_id).first()
        year_name = year.year_name if year else "Unknown"
        filename_base = f"{stmt.statement_type}_{year_name.replace(' ', '_')}"
        report_data = stmt.statement_data or {}

        if format_type == "pdf":
            from app.services.report_export_utils import export_key_value_pdf, export_simple_table_pdf
            period = {
                "start": report_data.get("period_start") or str(stmt.statement_date or ""),
                "end": report_data.get("period_end") or str(stmt.statement_date or ""),
            }
            if stmt.statement_type == "trial_balance":
                rows = report_data.get("items", [])
                cols = ["Code", "Name", "Debit", "Credit"]
                trows = [[r.get("code"), r.get("name"), r.get("debit", 0), r.get("credit", 0)] for r in rows]
                pdf = export_simple_table_pdf(stmt.statement_name, period, cols, trows)
            else:
                pdf = export_key_value_pdf(stmt.statement_name, period, report_data)
            return pdf.read(), f"{filename_base}.pdf", "application/pdf"

        if format_type in ("csv", "xlsx"):
            import csv
            from io import StringIO
            buf = StringIO()
            w = csv.writer(buf)
            if stmt.statement_type == "trial_balance":
                w.writerow(["Code", "Name", "Debit", "Credit"])
                for r in report_data.get("items", []):
                    w.writerow([r.get("code"), r.get("name"), r.get("debit", 0), r.get("credit", 0)])
            elif stmt.statement_type == "income_statement":
                w.writerow(["Metric", "Amount"])
                for k in ["total_revenue", "total_expenses", "net_income"]:
                    w.writerow([k.replace("_", " ").title(), report_data.get(k, 0)])
            elif stmt.statement_type in ("year_end_balance_sheet", "opening_balance_sheet"):
                w.writerow(["Metric", "Amount"])
                for k in ["total_assets", "total_liabilities", "total_equity"]:
                    w.writerow([k.replace("_", " ").title(), report_data.get(k, 0)])
            else:
                w.writerow(["Key", "Value"])
                for k, v in report_data.items():
                    if not isinstance(v, (dict, list)):
                        w.writerow([k, v])
            csv_bytes = buf.getvalue().encode("utf-8")
            ext = "xlsx" if format_type == "xlsx" else "csv"
            return csv_bytes, f"{filename_base}.{ext}", "text/csv"

        raise ValueError(f"Unsupported export format: {format_type}")

    def compare_fiscal_years(
        self,
        year_ids: List[str],
        report_type: str,
    ) -> Dict[str, Any]:
        """
        Generate side-by-side comparison of a single report type across multiple fiscal years.
        Computes variance (absolute and %) between the chronologically earliest and latest year.
        """
        SUPPORTED = {"income_statement", "balance_sheet", "trial_balance"}
        if report_type not in SUPPORTED:
            raise ValueError(f"report_type must be one of: {', '.join(SUPPORTED)}")

        years_data = []
        for yid in year_ids:
            fy = self.db.query(FiscalYear).filter(FiscalYear.id == yid).first()
            if not fy:
                continue
            if report_type == "income_statement":
                data = self._generate_income_statement_for_range(
                    fy.start_date, fy.end_date, fy.branch_id, fy.year_name
                )
            elif report_type == "balance_sheet":
                data = self._generate_balance_sheet_as_of_date(
                    fy.end_date, fy.branch_id, period_start=fy.start_date
                )
                data["period_name"] = fy.year_name
            else:  # trial_balance
                data = self._generate_trial_balance_for_range(
                    fy.start_date, fy.end_date, fy.branch_id, fy.year_name
                )
            years_data.append({
                "fiscal_year_id": fy.id,
                "fiscal_year_name": fy.year_name,
                "fiscal_year_code": fy.year_code,
                "start_date": fy.start_date.isoformat(),
                "end_date": fy.end_date.isoformat(),
                "status": fy.status,
                "data": data,
            })

        # Sort chronologically
        years_data.sort(key=lambda x: x["start_date"])

        # Compute variance between earliest (baseline) and latest year
        variance: Dict[str, Any] = {}
        if len(years_data) >= 2:
            base = years_data[0]["data"]
            latest = years_data[-1]["data"]
            if report_type == "income_statement":
                for key in ["total_revenue", "total_expenses", "net_income"]:
                    a = float(base.get(key, 0) or 0)
                    b = float(latest.get(key, 0) or 0)
                    diff = b - a
                    pct = round((diff / a * 100) if a != 0 else 0.0, 2)
                    variance[key] = {"absolute": round(diff, 2), "percent": pct}
            elif report_type == "balance_sheet":
                for key in ["total_assets", "total_liabilities", "total_equity"]:
                    a = float(base.get(key, 0) or 0)
                    b = float(latest.get(key, 0) or 0)
                    diff = b - a
                    pct = round((diff / a * 100) if a != 0 else 0.0, 2)
                    variance[key] = {"absolute": round(diff, 2), "percent": pct}

        return {
            "report_type": report_type,
            "years": years_data,
            "variance": variance,
        }

    def get_benchmarks(self, branch_id: Optional[str] = None) -> Dict[str, Any]:
        """
        Generate year-over-year KPI benchmarks covering all closed/active fiscal years.
        Returns revenue, net income, expenses, expense ratio, and net margin for each year.
        """
        years = self.db.query(FiscalYear).order_by(FiscalYear.start_date).all()
        rows = []
        for fy in years:
            if fy.status not in (FiscalYearStatus.CLOSED, FiscalYearStatus.ARCHIVED) and not fy.is_active:
                continue
            effective_branch = branch_id or fy.branch_id
            is_data = self._generate_income_statement_for_range(
                fy.start_date, fy.end_date, effective_branch, fy.year_name
            )
            bs_data = self._generate_balance_sheet_as_of_date(fy.end_date, effective_branch, period_start=fy.start_date)
            rev = float(is_data.get("total_revenue", 0) or 0)
            exp = float(is_data.get("total_expenses", 0) or 0)
            net = float(is_data.get("net_income", 0) or 0)
            assets = float(bs_data.get("total_assets", 0) or 0)
            equity = float(bs_data.get("total_equity", 0) or 0)
            rows.append({
                "fiscal_year_id": fy.id,
                "fiscal_year_name": fy.year_name,
                "fiscal_year_code": fy.year_code,
                "start_date": fy.start_date.isoformat(),
                "end_date": fy.end_date.isoformat(),
                "status": fy.status,
                "total_revenue": round(rev, 2),
                "total_expenses": round(exp, 2),
                "net_income": round(net, 2),
                "total_assets": round(assets, 2),
                "total_equity": round(equity, 2),
                "net_margin_pct": round((net / rev * 100) if rev != 0 else 0.0, 2),
                "expense_ratio_pct": round((exp / rev * 100) if rev != 0 else 0.0, 2),
            })

        # YoY growth rates
        for i in range(1, len(rows)):
            prev_rev = rows[i - 1]["total_revenue"]
            curr_rev = rows[i]["total_revenue"]
            rows[i]["revenue_growth_pct"] = round(
                ((curr_rev - prev_rev) / prev_rev * 100) if prev_rev != 0 else 0.0, 2
            )
            prev_net = rows[i - 1]["net_income"]
            curr_net = rows[i]["net_income"]
            rows[i]["net_income_growth_pct"] = round(
                ((curr_net - prev_net) / prev_net * 100) if prev_net != 0 else 0.0, 2
            )
        if rows:
            rows[0]["revenue_growth_pct"] = None
            rows[0]["net_income_growth_pct"] = None

        return {"benchmarks": rows}

    def year_end_workflow(
        self,
        fiscal_year_id: str,
        user_id: str,
        next_year_id: Optional[str] = None,
        hard_close_remaining: bool = False,
    ) -> Dict[str, Any]:
        """
        Execute the full year-end close workflow:
        1. Optionally hard-close any still-open/soft-closed periods
        2. Close the fiscal year
        3. Save year-end financial statement snapshots
        4. Optionally apply opening balances to the specified next fiscal year
        """
        steps: List[str] = []
        errors: List[str] = []

        # Step 1 – close remaining periods
        if hard_close_remaining:
            pending = self.db.query(FiscalPeriod).filter(
                and_(
                    FiscalPeriod.fiscal_year_id == fiscal_year_id,
                    FiscalPeriod.status.notin_([PeriodStatus.HARD_CLOSED, PeriodStatus.LOCKED]),
                )
            ).all()
            for p in pending:
                try:
                    if p.status == PeriodStatus.DRAFT:
                        # DRAFT periods have no transactions and no checklist –
                        # hard-close directly (IAS 10: no adjusting events possible)
                        p.status = PeriodStatus.HARD_CLOSED
                        p.hard_closed_date = datetime.utcnow()
                        p.hard_closed_by_user_id = user_id
                        self.db.flush()
                    elif p.status == PeriodStatus.OPEN:
                        # Soft-close first (calculates stats), then hard-close
                        self.soft_close_period(p.id, user_id)
                        self.hard_close_period(p.id, user_id, generate_statements=False)
                    else:  # SOFT_CLOSED
                        self.hard_close_period(p.id, user_id, generate_statements=False)
                    steps.append(f"Hard-closed: {p.period_name}")
                except Exception as e:
                    errors.append(f"Could not close {p.period_name}: {e}")

        # Step 2 – close fiscal year
        try:
            year = self.close_fiscal_year(fiscal_year_id, user_id, generate_statements=False)
            steps.append(f"Fiscal year '{year.year_name}' closed")
        except Exception as e:
            errors.append(f"Could not close fiscal year: {e}")
            return {"success": False, "fiscal_year_id": fiscal_year_id, "steps": steps, "errors": errors}

        # Step 3 – save year-end report snapshots
        try:
            saved = self.save_year_end_reports(fiscal_year_id, user_id)
            steps.append(f"Saved {saved['saved_reports']} year-end report snapshots")
        except Exception as e:
            errors.append(f"Could not save reports: {e}")

        # Step 4 – apply opening balances to next year
        if next_year_id:
            try:
                ob = self.apply_opening_balances(next_year_id, user_id)
                count = ob.get("applied_count", ob.get("accounts_applied", "?"))
                steps.append(f"Opening balances applied for next fiscal year ({count} accounts)")
            except Exception as e:
                errors.append(f"Could not apply opening balances: {e}")

        return {
            "success": len(errors) == 0,
            "fiscal_year_id": fiscal_year_id,
            "steps": steps,
            "errors": errors,
        }
