"""Finance-authored editorial layer: per-line commentary + recon link, adopting
an AI narrative, KPI overrides, insight cards, and follow-up rows.

All of this survives a NetSuite re-sync (it lives in tables separate from the
machine-pulled `finance_flux_lines`), so analysts never lose work when a
corrected report is re-imported.
"""
from __future__ import annotations

import uuid
from decimal import Decimal

from sqlalchemy import delete, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.core.exceptions import AppException, NotFoundError
from app.domains.finance_flux import constants as c
from app.domains.finance_flux.models import (
    FinanceFluxCommentary,
    FinanceFluxFollowup,
    FinanceFluxInsightCard,
    FinanceFluxKpiOverride,
    FinanceFluxPeriod,
)

_UNSET = object()


async def _period_or_404(db: AsyncSession, period_key: str) -> FinanceFluxPeriod:
    p = (await db.execute(
        select(FinanceFluxPeriod).where(FinanceFluxPeriod.period_key == period_key)
    )).scalar_one_or_none()
    if p is None:
        raise NotFoundError(f"finance-flux period {period_key} not imported")
    return p


async def _commentary(
    db: AsyncSession, period_id, statement: str, line_key: str, *, create: bool,
) -> FinanceFluxCommentary | None:
    row = (await db.execute(
        select(FinanceFluxCommentary).where(
            FinanceFluxCommentary.period_id == period_id,
            FinanceFluxCommentary.statement == statement,
            FinanceFluxCommentary.line_key == line_key,
        )
    )).scalar_one_or_none()
    if row is None and create:
        row = FinanceFluxCommentary(
            id=uuid.uuid4(), period_id=period_id, statement=statement,
            line_key=line_key,
        )
        db.add(row)
    return row


def _serialize_commentary(row: FinanceFluxCommentary) -> dict:
    return {
        "statement": row.statement, "line_key": row.line_key,
        "commentary": row.commentary, "recon_link": row.recon_link,
        "ai_narrative": row.ai_narrative,
        "ai_generated_at": row.ai_generated_at.isoformat() if row.ai_generated_at else None,
    }


async def set_commentary(
    db: AsyncSession, *, period_key: str, statement: str, line_key: str,
    commentary=_UNSET, recon_link=_UNSET, editor_id=None,
) -> dict:
    if statement not in c.STATEMENTS:
        raise AppException(code="FINANCE_FLUX_BAD_STATEMENT",
                           message="bad statement", status_code=422)
    p = await _period_or_404(db, period_key)
    row = await _commentary(db, p.id, statement, line_key, create=True)
    if commentary is not _UNSET:
        row.commentary = commentary
    if recon_link is not _UNSET:
        row.recon_link = recon_link
    row.edited_by_person_id = editor_id
    await db.flush()
    return _serialize_commentary(row)


async def adopt_narrative(
    db: AsyncSession, *, period_key: str, statement: str, line_key: str,
    editor_id=None,
) -> dict:
    """Copy the cached AI narrative into the editable commentary field."""
    p = await _period_or_404(db, period_key)
    row = await _commentary(db, p.id, statement, line_key, create=False)
    if row is None or not row.ai_narrative:
        raise NotFoundError("no AI narrative to adopt — generate one first")
    row.commentary = row.ai_narrative
    row.edited_by_person_id = editor_id
    await db.flush()
    return _serialize_commentary(row)


async def set_kpi_override(
    db: AsyncSession, *, period_key: str, kpi_key: str, value: Decimal,
    note: str | None, editor_id=None,
) -> dict:
    p = await _period_or_404(db, period_key)
    row = (await db.execute(
        select(FinanceFluxKpiOverride).where(
            FinanceFluxKpiOverride.period_id == p.id,
            FinanceFluxKpiOverride.kpi_key == kpi_key,
        )
    )).scalar_one_or_none()
    if row is None:
        row = FinanceFluxKpiOverride(
            id=uuid.uuid4(), period_id=p.id, kpi_key=kpi_key)
        db.add(row)
    row.value = value
    row.note = note
    row.edited_by_person_id = editor_id
    await db.flush()
    return {"period_key": period_key, "kpi_key": kpi_key, "value": str(value)}


async def clear_kpi_override(
    db: AsyncSession, *, period_key: str, kpi_key: str,
) -> None:
    p = await _period_or_404(db, period_key)
    await db.execute(delete(FinanceFluxKpiOverride).where(
        FinanceFluxKpiOverride.period_id == p.id,
        FinanceFluxKpiOverride.kpi_key == kpi_key,
    ))


# ── Insight cards (Summary highlights + Insights narrative cards) ───────

async def create_card(
    db: AsyncSession, *, period_key: str, card_type: str, emoji: str | None,
    color: str | None, title: str | None, body: str | None, creator_id=None,
) -> dict:
    if card_type not in c.CARD_TYPES:
        raise AppException(code="FINANCE_FLUX_BAD_CARD_TYPE",
                           message=f"card_type must be {c.CARD_TYPES}", status_code=422)
    p = await _period_or_404(db, period_key)
    nxt = (await db.execute(
        select(FinanceFluxInsightCard).where(
            FinanceFluxInsightCard.period_id == p.id,
            FinanceFluxInsightCard.card_type == card_type,
        )
    )).scalars().all()
    row = FinanceFluxInsightCard(
        id=uuid.uuid4(), period_id=p.id, card_type=card_type, emoji=emoji,
        color=color, title=title, body=body, sort_order=len(nxt) * 10,
        created_by_person_id=creator_id,
    )
    db.add(row)
    await db.flush()
    return {"id": str(row.id), "card_type": card_type, "title": title}


async def update_card(db: AsyncSession, *, card_id: str, **fields) -> dict:
    row = await db.get(FinanceFluxInsightCard, uuid.UUID(str(card_id)))
    if row is None:
        raise NotFoundError("insight card not found")
    for k in ("emoji", "color", "title", "body", "sort_order"):
        if k in fields and fields[k] is not None:
            setattr(row, k, fields[k])
    await db.flush()
    return {"id": str(row.id)}


async def delete_card(db: AsyncSession, *, card_id: str) -> None:
    row = await db.get(FinanceFluxInsightCard, uuid.UUID(str(card_id)))
    if row is not None:
        await db.delete(row)


# ── Follow-up rows (Recommended Follow-ups table) ───────────────────────

async def create_followup(
    db: AsyncSession, *, period_key: str, item: str | None, owner_label: str | None,
    action: str | None, priority: str, creator_id=None,
) -> dict:
    if priority not in c.PRIORITIES:
        raise AppException(code="FINANCE_FLUX_BAD_PRIORITY",
                           message=f"priority must be {c.PRIORITIES}", status_code=422)
    p = await _period_or_404(db, period_key)
    existing = (await db.execute(
        select(FinanceFluxFollowup).where(FinanceFluxFollowup.period_id == p.id)
    )).scalars().all()
    row = FinanceFluxFollowup(
        id=uuid.uuid4(), period_id=p.id, item=item, owner_label=owner_label,
        action=action, priority=priority, sort_order=len(existing) * 10,
        created_by_person_id=creator_id,
    )
    db.add(row)
    await db.flush()
    return {"id": str(row.id), "priority": priority}


async def update_followup(db: AsyncSession, *, followup_id: str, **fields) -> dict:
    row = await db.get(FinanceFluxFollowup, uuid.UUID(str(followup_id)))
    if row is None:
        raise NotFoundError("follow-up not found")
    if fields.get("priority") and fields["priority"] not in c.PRIORITIES:
        raise AppException(code="FINANCE_FLUX_BAD_PRIORITY",
                           message="bad priority", status_code=422)
    for k in ("item", "owner_label", "action", "priority", "sort_order"):
        if k in fields and fields[k] is not None:
            setattr(row, k, fields[k])
    await db.flush()
    return {"id": str(row.id)}


async def delete_followup(db: AsyncSession, *, followup_id: str) -> None:
    row = await db.get(FinanceFluxFollowup, uuid.UUID(str(followup_id)))
    if row is not None:
        await db.delete(row)


async def list_editorial(db: AsyncSession, *, period_key: str) -> dict:
    """Cards + follow-ups for a period (for the Summary/Insights pages)."""
    p = await _period_or_404(db, period_key)
    cards = (await db.execute(
        select(FinanceFluxInsightCard).where(
            FinanceFluxInsightCard.period_id == p.id
        ).order_by(FinanceFluxInsightCard.card_type, FinanceFluxInsightCard.sort_order)
    )).scalars().all()
    follows = (await db.execute(
        select(FinanceFluxFollowup).where(
            FinanceFluxFollowup.period_id == p.id
        ).order_by(FinanceFluxFollowup.sort_order)
    )).scalars().all()
    return {
        "cards": [{
            "id": str(x.id), "card_type": x.card_type, "emoji": x.emoji,
            "color": x.color, "title": x.title, "body": x.body,
            "sort_order": x.sort_order,
        } for x in cards],
        "followups": [{
            "id": str(x.id), "item": x.item, "owner_label": x.owner_label,
            "action": x.action, "priority": x.priority, "sort_order": x.sort_order,
        } for x in follows],
    }