"""CSV exports - the two reports Mike named in his flow chart.

1. Ticket status master - "a rich data report that provides the ticket status
   data and changes."
2. Item movement - "a POS like report, tied to the ticket number, showing the
   product item number and description used in a system repair."

Column names deliberately echo his existing MaintainX export
(`notes/.../BP Parts Report.xlsx`) so the output drops into the spreadsheets he
already works in. `scripts/gate-p2.sh` checks that correspondence mechanically.
"""

from __future__ import annotations

import csv
import io
from collections.abc import Iterable

from sqlalchemy import func, select
from sqlalchemy.orm import Session

from app.models.tables import PartUsage, Ticket
from app.services.tickets import display_number

TICKET_COLUMNS = [
    "Ticket Number",
    "Status",
    "Priority",
    "Asset",
    "Asset Description",
    "Line",
    "Description",
    "Reported By",
    "Reported At",
    "Assigned To",
    "Closed At",
    "Parent Ticket",
    "Attachments",
    "Parts Used",
]

MOVEMENT_COLUMNS = [
    "Transaction ID",
    "Direction",
    "Part ID",
    "Part #",
    "Part Name",
    "Part Types",
    "QR/Bar code",
    "Part Location",
    "Quantity",
    "U/M",
    "Unit Cost",
    "Total Cost",
    "Transaction Date",
    "Work Order ID",
    "Work Order Asset",
    "Transaction Initiator",
]


def _write(columns: list[str], rows: Iterable[list[object]]) -> str:
    buffer = io.StringIO()
    writer = csv.writer(buffer, lineterminator="\n")
    writer.writerow(columns)
    for row in rows:
        writer.writerow(row)
    return buffer.getvalue()


def ticket_status_csv(db: Session) -> str:
    tickets = list(db.scalars(select(Ticket).order_by(Ticket.number)).unique())

    # Aggregate once rather than per row - SQL-side counting, not N+1.
    usage_counts = {
        ticket_id: count
        for ticket_id, count in db.execute(
            select(PartUsage.ticket_id, func.count(PartUsage.id)).group_by(
                PartUsage.ticket_id
            )
        ).all()
    }
    numbers = {t.id: t.number for t in tickets}

    def rows() -> Iterable[list[object]]:
        for ticket in tickets:
            parent_number = numbers.get(ticket.parent_ticket_id or -1)
            parts_used = usage_counts.get(ticket.id, 0)
            yield [
                display_number(ticket.number),
                ticket.status.value,
                ticket.priority.value,
                ticket.asset.equipment_no if ticket.asset else "",
                ticket.asset.description if ticket.asset else "",
                ticket.asset.line if ticket.asset and ticket.asset.line else "",
                ticket.description,
                ticket.reported_by.name if ticket.reported_by else "",
                ticket.reported_at.isoformat(),
                ticket.assigned_to.name if ticket.assigned_to else "",
                ticket.closed_at.isoformat() if ticket.closed_at else "",
                display_number(parent_number) if parent_number else "",
                len(ticket.media),
                parts_used,
            ]

    return _write(TICKET_COLUMNS, rows())


def item_movement_csv(db: Session) -> str:
    usages = list(
        db.scalars(select(PartUsage).order_by(PartUsage.at, PartUsage.id)).unique()
    )

    def rows() -> Iterable[list[object]]:
        for usage in usages:
            part = usage.part
            quantity = float(usage.quantity)
            unit_cost = float(part.unit_cost) if part.unit_cost is not None else None
            yield [
                usage.id,
                "OUT" if usage.direction.value == "used" else "IN",
                part.id,
                part.part_number,
                part.part_name,
                part.part_type or "",
                part.barcode or "",
                part.location or "",
                quantity,
                part.uom or "",
                f"{unit_cost:.2f}" if unit_cost is not None else "",
                f"{unit_cost * quantity:.2f}" if unit_cost is not None else "",
                usage.at.isoformat(),
                display_number(usage.ticket.number),
                usage.ticket.asset.equipment_no if usage.ticket.asset else "",
                usage.actor.name if usage.actor else "",
            ]

    return _write(MOVEMENT_COLUMNS, rows())