"""Output XLSX writer.

Produces a workbook with up to three sheets:

  1. "Adjustments"  -- flat adjustment table (FROM and TO rows).

  2. "Summary"      -- one row per TransferRecord showing calculation details.

  3. "Errors"       -- present only when one or more adjustment rows carry an
     error.

``write_output_to_buffer`` is the primary export for Lambda use (writes to an
in-memory buffer uploaded to S3).  ``write_output`` writes to the filesystem
and is retained for CLI / local use.
"""

from __future__ import annotations

import os
from calendar import month_abbr as MONTH_ABBR
from datetime import datetime
from io import BytesIO

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter as _col_letter
from openpyxl.worksheet.worksheet import Worksheet

from src.enums import RateType
from src.types import AdjustmentRow, CalculationResult, TransferRecord

# ─── Styling constants ───────────────────────────────────────────────────────

_HEADER_FILL = PatternFill(start_color='FFD9E1F2', fill_type='solid')
_HEADER_FONT = Font(bold=True)
_ERROR_FILL = PatternFill(start_color='FFFFC7CE', fill_type='solid')


# ─── Helpers ─────────────────────────────────────────────────────────────────


def _pct(n: float | None) -> str:
    """Format a ratio as a percentage string."""
    if n is None:
        return 'N/A'
    raw = f'{(n * 100):.4f}'
    # Strip trailing zeros and optional trailing dot
    raw = raw.rstrip('0').rstrip('.')
    return f'{raw}%'


def _fmt(n: float | None, dp: int = 2) -> float | str:
    """Format a number to *dp* decimal places, or 'N/A' if *None*."""
    if n is None:
        return 'N/A'
    return float(f'{n:.{dp}f}')


def _fmt_input(field: str, balance: float | None) -> str:
    """Format input field with its balance value, e.g. 'closing_balance = 1234.56'."""
    if balance is None:
        return f'{field} = N/A'
    return f'{field} = {balance:.2f}'


def _bold(ws: Worksheet, row: int) -> None:
    """Apply bold font to every cell in *row*."""
    for cell in ws[row]:
        cell.font = _HEADER_FONT


def _header_row(ws: Worksheet, headers: list[str]) -> None:
    """Append a styled header row."""
    ws.append(headers)
    row_num = ws.max_row
    for cell in ws[row_num]:
        cell.font = _HEADER_FONT
        cell.fill = _HEADER_FILL


# ─── Client-facing comment generation ────────────────────────────────────────


def _month_abbr(m: int) -> str:
    """Return the 3-letter month abbreviation (1-indexed)."""
    return MONTH_ABBR[m] if 1 <= m <= 12 else ''


def _fmt_amount(amount: float | None, currency: str) -> str:
    if amount is None:
        return ''
    return f' {currency} {amount:.2f}'


def _generate_from_comment(
    record: TransferRecord,
    amount: float | None = None,
) -> str:
    """Generate the client-facing comment for a FROM adjustment row."""
    to_name = (
        record.to_contract.account_name or f'contract {record.to_contract.contract_id}'
    )
    amount_str = _fmt_amount(amount, record.currency) if amount is not None else ''
    return f'{amount_str} to {to_name}'.lstrip()


def _generate_to_comment(
    record: TransferRecord,
    amount: float | None = None,
) -> str:
    """Generate the client-facing comment for a TO adjustment row."""
    from_name = (
        record.from_contract.account_name
        or f'contract {record.from_contract.contract_id}'
    )
    amount_str = _fmt_amount(amount, record.currency) if amount is not None else ''
    return f'{amount_str} from {from_name}'.lstrip()


# ─── Adjustment rows derivation ─────────────────────────────────────────────


def build_adjustment_rows(
    results: list[CalculationResult],
    default_date: datetime | None = None,
) -> list[AdjustmentRow]:
    """Derive FROM/TO ``AdjustmentRow`` objects from calculation results."""
    now = default_date or datetime.now()
    default_month = now.month
    default_year = now.year

    rows: list[AdjustmentRow] = []

    for r in results:
        rec = r.record
        calculated_amount = r.calculated_amount

        if calculated_amount == 0 and not rec.error:
            continue

        activity_month = rec.activity_month or default_month
        activity_year = rec.activity_year or default_year
        statement_month = rec.statement_month or default_month
        statement_year = rec.statement_year or default_year

        from_balance = _selected_balance(rec)

        if rec.error:
            rows.append(
                AdjustmentRow(
                    earnings_transfer_id=rec.earnings_transfer_id,
                    transfer_type=rec.transfer_type,
                    currency=rec.currency,
                    activity_month=activity_month,
                    activity_year=activity_year,
                    statement_month=statement_month,
                    statement_year=statement_year,
                    side='FROM',
                    account_name=rec.from_contract.account_name,
                    account_id=rec.from_contract.account_id,
                    contract_name=rec.from_contract.contract_name,
                    contract_id=rec.from_contract.contract_id,
                    amount=None,
                    client_facing_comments=f'ERROR: {rec.error}',
                    input_field=_fmt_input(rec.input, from_balance),
                    projected_balance=None,
                    error=rec.error,
                    error_contract_id=rec.error_contract_id,
                ),
            )
            continue

        from_projected = (
            round(from_balance - calculated_amount, 2)
            if from_balance is not None
            else None
        )

        to_balance = _to_selected_balance(rec)

        rows.append(
            AdjustmentRow(
                earnings_transfer_id=rec.earnings_transfer_id,
                transfer_type=rec.transfer_type,
                currency=rec.currency,
                activity_month=activity_month,
                activity_year=activity_year,
                statement_month=statement_month,
                statement_year=statement_year,
                side='FROM',
                account_name=rec.from_contract.account_name,
                account_id=rec.from_contract.account_id,
                contract_name=rec.from_contract.contract_name,
                contract_id=rec.from_contract.contract_id,
                amount=-calculated_amount,
                client_facing_comments=(
                    rec.from_comment or _generate_from_comment(rec, calculated_amount)
                ),
                input_field=_fmt_input(rec.input, from_balance),
                projected_balance=from_projected,
            ),
        )
        to_projected = (
            round(to_balance + calculated_amount, 2) if to_balance is not None else None
        )

        rows.append(
            AdjustmentRow(
                earnings_transfer_id=rec.earnings_transfer_id,
                transfer_type=rec.transfer_type,
                currency=rec.currency,
                activity_month=activity_month,
                activity_year=activity_year,
                statement_month=statement_month,
                statement_year=statement_year,
                side='TO',
                account_name=rec.to_contract.account_name
                or rec.from_contract.account_name,
                account_id=rec.to_contract.account_id,
                contract_name=rec.to_contract.contract_name or '',
                contract_id=rec.to_contract.contract_id,
                amount=calculated_amount,
                client_facing_comments=(
                    rec.to_comment or _generate_to_comment(rec, calculated_amount)
                ),
                input_field=_fmt_input(rec.input, to_balance),
                projected_balance=to_projected,
            ),
        )

    return rows


# ─── Sheet builders ──────────────────────────────────────────────────────────


def _none_to_empty(val: int | float | None) -> int | float | str:
    """Return *val* if not None, else empty string. 0 stays as 0."""
    if val is None:
        return ''
    return val


def _zero_or_empty(val: int) -> int | str:
    """Return *val* if non-zero, else empty string. For date fields where 0 means unset."""
    return val if val else ''


def _add_adjustments_sheet(wb: Workbook, rows: list[AdjustmentRow]) -> None:
    ws = wb.create_sheet('Adjustments')

    _header_row(
        ws,
        [
            'Account Name',  # A
            'Account ID',  # B
            'Contract Name',  # C
            'Contract ID',  # D
            'UPC',  # E
            'Amount',  # F
            'Currency',  # G
            'Activity Month',  # H
            'Activity Year',  # I
            'Statement Month',  # J
            'Statement Year',  # K
            'Adjustment Type',  # L
            'Client Facing Comments',  # M
            'Distribution Type',  # N
            'Internal Note',  # O
            'Apply to Flowthrough Payment',  # P
            'Side',  # Q
            'Earnings Transfer ID',  # R
            'Input',  # S
            'Projected Balance',  # T
        ],
    )

    for r in rows:
        if r.error:
            continue

        ws.append(
            [
                r.account_name,
                _none_to_empty(r.account_id),
                r.contract_name,
                _none_to_empty(r.contract_id),
                r.upc or '',
                _fmt(r.amount),
                r.currency,
                _zero_or_empty(r.activity_month),
                _zero_or_empty(r.activity_year),
                _zero_or_empty(r.statement_month),
                _zero_or_empty(r.statement_year),
                r.transfer_type,
                r.client_facing_comments,
                '',  # Distribution Type
                '',  # Internal Note
                '',  # Apply to Flowthrough Payment
                r.side,
                _none_to_empty(r.earnings_transfer_id),
                r.input_field,
                _fmt(r.projected_balance),
            ]
        )

    # Column widths
    for col_idx in range(1, 21):
        ws.column_dimensions[_col_letter(col_idx)].width = 20
    ws.column_dimensions['A'].width = 35  # Account Name
    ws.column_dimensions['C'].width = 35  # Contract Name
    ws.column_dimensions['F'].width = 14  # Amount
    ws.column_dimensions['M'].width = 45  # Client Facing Comments
    ws.column_dimensions['T'].width = 18  # Projected Balance


def _selected_balance(rec: TransferRecord) -> float | None:
    if rec.input == 'net_revenue':
        return rec.net_revenue
    if rec.input == 'gross_revenue':
        return rec.gross_revenue
    return rec.closing_balance


def _to_selected_balance(rec: TransferRecord) -> float | None:
    if rec.input == 'net_revenue':
        return rec.to_net_revenue
    if rec.input == 'gross_revenue':
        return rec.to_gross_revenue
    return rec.to_closing_balance


def _add_summary_sheet(wb: Workbook, results: list[CalculationResult]) -> None:
    ws = wb.create_sheet('Summary')

    _header_row(
        ws,
        [
            'Earnings Transfer ID',
            'Transfer Type',
            'Rate Type',
            'Rate / Amount',
            'Input',
            'Negative',
            'From Account',
            'From Contract ID',
            'To Account',
            'To Contract ID',
            'Currency',
            'Closing Balance',
            'Net Revenue',
            'Gross Revenue',
            'To Closing Balance',
            'To Net Revenue',
            'To Gross Revenue',
            'Selected Balance',
            'Calculated Amount',
            'Description',
            'Status',
            'Error',
            'Block Reason',
        ],
    )

    for r in results:
        rec = r.record
        ws.append(
            [
                rec.earnings_transfer_id,
                rec.transfer_type,
                rec.rate_type,
                _fmt(rec.transfer_amount)
                if rec.rate_type == RateType.FLAT_RATE
                else _pct(rec.transfer_amount),
                rec.input,
                rec.negative,
                rec.from_contract.account_name,
                _none_to_empty(rec.from_contract.contract_id),
                rec.to_contract.account_name,
                _none_to_empty(rec.to_contract.contract_id),
                rec.currency,
                _fmt(rec.closing_balance, 4),
                _fmt(rec.net_revenue, 4),
                _fmt(rec.gross_revenue, 4),
                _fmt(rec.to_closing_balance, 4),
                _fmt(rec.to_net_revenue, 4),
                _fmt(rec.to_gross_revenue, 4),
                _fmt(_selected_balance(rec), 4),
                _fmt(r.calculated_amount),
                rec.description,
                rec.status,
                rec.error or '',
                r.block_reason or '',
            ]
        )

    # Column widths
    for col_idx in range(1, 24):
        ws.column_dimensions[_col_letter(col_idx)].width = 18
    ws.column_dimensions['A'].width = 20  # Earnings Transfer ID
    ws.column_dimensions['G'].width = 35  # From Account
    ws.column_dimensions['I'].width = 35  # To Account
    ws.column_dimensions['T'].width = 30  # Description
    ws.column_dimensions['W'].width = 45  # Block Reason


def _add_errors_sheet(wb: Workbook, rows: list[AdjustmentRow]) -> None:
    error_rows = [r for r in rows if r.error]
    if not error_rows:
        return

    ws = wb.create_sheet('Errors')

    _header_row(
        ws,
        [
            'Earnings Transfer ID',
            'Contract ID',
            'Error',
        ],
    )

    for r in error_rows:
        ws.append(
            [
                _none_to_empty(r.earnings_transfer_id),
                _none_to_empty(r.error_contract_id),
                r.error,
            ]
        )
        row_num = ws.max_row
        for cell in ws[row_num]:
            cell.fill = _ERROR_FILL

    ws.column_dimensions['A'].width = 20
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 80


def _build_workbook(
    results: list[CalculationResult], default_date: datetime | None = None
) -> Workbook:
    """Build the complete output workbook."""
    wb = Workbook()
    # Remove the default sheet created by openpyxl
    wb.remove(wb.active)  # type: ignore[arg-type]

    adj_rows = build_adjustment_rows(results, default_date=default_date)
    _add_adjustments_sheet(wb, adj_rows)
    _add_summary_sheet(wb, results)
    _add_errors_sheet(wb, adj_rows)

    return wb


# ─── Public API ──────────────────────────────────────────────────────────────


def write_output_to_buffer(
    results: list[CalculationResult], default_date: datetime | None = None
) -> bytes:
    """Write the output XLSX to an in-memory buffer.

    Used by the Lambda handler to upload directly to S3.
    """
    wb = _build_workbook(results, default_date=default_date)
    buf = BytesIO()
    wb.save(buf)
    return buf.getvalue()


def write_output(
    results: list[CalculationResult],
    source_file_name: str,
    output_path: str | None = None,
    default_date: datetime | None = None,
) -> str:
    """Write the output XLSX to *output_path* on the filesystem.

    Retained for CLI / local use.
    """
    if output_path is None:
        base = os.path.splitext(os.path.basename(source_file_name))[0]
        output_path = os.path.join(os.getcwd(), 'output', f'{base}_output.xlsx')

    wb = _build_workbook(results, default_date=default_date)
    wb.save(output_path)
    return output_path