site/apps/api/app/modules/zootech/wesp_feed_accounting_service.py

"""Feed accounting (СП-20, exports, org requisites) for WESP consumption UI."""

from __future__ import annotations

import base64
import hashlib
import io
import json
import re
import zipfile
from datetime import UTC, datetime
from typing import Any
from xml.sax.saxutils import escape

from sqlalchemy import select

from app.core.database import session_scope
from app.modules.sync import repository as sync_repo
from app.modules.zootech.settings_models import ZootechOrgSettings
from app.modules.zootech.wesp_compat_reports import _parse_payload, _parse_report_time
from app.modules.zootech.pdf_export import build_table_pdf
from app.modules.zootech.wesp_sklad_service import list_sklad_items
from app.modules.zootech.report_models import ZootechLoadingReport

ORG_FIELD_KEYS = (
    "organization_name",
    "okpo",
    "department",
    "farm_name",
    "brigade",
    "animal_type",
    "responsible_person",
    "zootechnician",
    "warehouse_keeper",
    "document_number_prefix",
)

SIGNATURE_ROLES = ("zootechnician", "warehouse_keeper", "recipient")


def _empty_org_fields() -> dict[str, str]:
    return {key: "" for key in ORG_FIELD_KEYS}


def _parse_payload_json(raw: str | None) -> dict[str, Any]:
    if not raw:
        return {}
    try:
        parsed = json.loads(raw)
    except json.JSONDecodeError:
        return {}
    return parsed if isinstance(parsed, dict) else {}


def _load_org_row(enterprise_id: str) -> tuple[ZootechOrgSettings | None, dict[str, Any]]:
    with session_scope() as db:
        row = db.scalar(select(ZootechOrgSettings).where(ZootechOrgSettings.enterprise_id == enterprise_id))
        if not row:
            return None, {}
        return row, _parse_payload_json(row.payload_json)


def _signature_status(payload: dict[str, Any]) -> dict[str, bool]:
    signatures = payload.get("signatures")
    if not isinstance(signatures, dict):
        signatures = {}
    return {role: bool(signatures.get(role)) for role in SIGNATURE_ROLES}


def _has_requisites(fields: dict[str, str]) -> bool:
    return bool(
        (fields.get("organization_name") or "").strip()
        and (fields.get("zootechnician") or "").strip()
        and (fields.get("warehouse_keeper") or "").strip()
    )


def get_org_settings(enterprise_id: str) -> dict[str, Any]:
    _row, payload = _load_org_row(enterprise_id)
    fields = _empty_org_fields()
    stored = payload.get("fields")
    if isinstance(stored, dict):
        for key in ORG_FIELD_KEYS:
            fields[key] = str(stored.get(key) or "")
    farms = [hub.name for hub in sync_repo.list_farm_hubs(enterprise_id) if hub.name]
    return {
        **fields,
        "available_farms": farms,
        "signatures": _signature_status(payload),
    }


def save_org_settings(enterprise_id: str, body: dict[str, Any]) -> dict[str, Any]:
    fields = _empty_org_fields()
    for key in ORG_FIELD_KEYS:
        if key in body:
            fields[key] = str(body.get(key) or "").strip()
    with session_scope() as db:
        row = db.scalar(select(ZootechOrgSettings).where(ZootechOrgSettings.enterprise_id == enterprise_id))
        payload = _parse_payload_json(row.payload_json) if row else {}
        payload["fields"] = fields
        payload_json = json.dumps(payload, ensure_ascii=False, separators=(",", ":"))
        content_hash = hashlib.sha256(payload_json.encode("utf-8")).hexdigest()
        if row:
            row.payload_json = payload_json
            row.version = int(row.version or 0) + 1
            row.content_hash = content_hash
            row.updated_at = datetime.now(UTC)
        else:
            db.add(
                ZootechOrgSettings(
                    enterprise_id=enterprise_id,
                    payload_json=payload_json,
                    version=1,
                    content_hash=content_hash,
                )
            )
    return {"success": True, "signatures": _signature_status(payload)}


def _decode_png_base64(data_url: str) -> bytes:
    text = (data_url or "").strip()
    if "," in text:
        text = text.split(",", 1)[1]
    return base64.b64decode(text)


def save_signature(enterprise_id: str, role: str, png_base64: str) -> dict[str, Any]:
    if role not in SIGNATURE_ROLES:
        return {"success": False, "message": "Неизвестная роль подписи"}
    try:
        png_bytes = _decode_png_base64(png_base64)
    except (ValueError, TypeError):
        return {"success": False, "message": "Некорректное изображение подписи"}
    encoded = base64.b64encode(png_bytes).decode("ascii")
    with session_scope() as db:
        row = db.scalar(select(ZootechOrgSettings).where(ZootechOrgSettings.enterprise_id == enterprise_id))
        payload = _parse_payload_json(row.payload_json) if row else {}
        signatures = payload.get("signatures")
        if not isinstance(signatures, dict):
            signatures = {}
        signatures[role] = encoded
        payload["signatures"] = signatures
        payload_json = json.dumps(payload, ensure_ascii=False, separators=(",", ":"))
        content_hash = hashlib.sha256(payload_json.encode("utf-8")).hexdigest()
        if row:
            row.payload_json = payload_json
            row.version = int(row.version or 0) + 1
            row.content_hash = content_hash
            row.updated_at = datetime.now(UTC)
        else:
            db.add(
                ZootechOrgSettings(
                    enterprise_id=enterprise_id,
                    payload_json=payload_json,
                    version=1,
                    content_hash=content_hash,
                )
            )
    return {"success": True, "roles": _signature_status(payload)}


def get_signature_png(enterprise_id: str, role: str) -> bytes | None:
    if role not in SIGNATURE_ROLES:
        return None
    _row, payload = _load_org_row(enterprise_id)
    signatures = payload.get("signatures")
    if not isinstance(signatures, dict):
        return None
    encoded = signatures.get(role)
    if not encoded:
        return None
    try:
        return base64.b64decode(str(encoded))
    except (ValueError, TypeError):
        return None


def _month_window(month: str) -> tuple[datetime, datetime]:
    start = datetime.strptime(month.strip() + "-01", "%Y-%m-%d").replace(tzinfo=UTC)
    if start.month == 12:
        end = datetime(start.year + 1, 1, 1, tzinfo=UTC)
    else:
        end = datetime(start.year, start.month + 1, 1, tzinfo=UTC)
    return start, end


def _reports_in_window(enterprise_id: str, window: tuple[datetime, datetime]) -> list[dict[str, Any]]:
    rows: list[dict[str, Any]] = []
    with session_scope() as db:
        for row in db.scalars(
            select(ZootechLoadingReport).where(
                ZootechLoadingReport.enterprise_id == enterprise_id,
                ZootechLoadingReport.is_deleted.is_(False),
            )
        ):
            payload = _parse_payload(row)
            start_time = _parse_report_time(payload.get("start_time"))
            if start_time is None or not (window[0] <= start_time < window[1]):
                continue
            rows.append(payload)
    return rows


def sp20_preview(enterprise_id: str, month: str) -> dict[str, Any]:
    window = _month_window(month)
    reports = _reports_in_window(enterprise_id, window)
    lines_count = 0
    total_kg = 0.0
    for payload in reports:
        components = payload.get("components")
        if not isinstance(components, list):
            continue
        for comp in components:
            if not isinstance(comp, dict):
                continue
            actual = float(comp.get("actual_weight") or 0)
            if actual <= 0:
                continue
            lines_count += 1
            total_kg += actual
    settings = get_org_settings(enterprise_id)
    fields = {key: settings.get(key, "") for key in ORG_FIELD_KEYS}
    return {
        "lines_count": lines_count,
        "total_kg": round(total_kg, 2),
        "has_requisites": _has_requisites(fields),
        "month": month,
    }


def _col_letter(index: int) -> str:
    result = ""
    n = index
    while True:
        result = chr(n % 26 + ord("A")) + result
        n = n // 26 - 1
        if n < 0:
            break
    return result


def build_xlsx_bytes(sheet_name: str, headers: list[str], rows: list[list[Any]]) -> bytes:
    sheet_rows: list[str] = []
    all_rows = [headers, *rows]
    for row_idx, row in enumerate(all_rows, start=1):
        cells: list[str] = []
        for col_idx, value in enumerate(row):
            ref = f"{_col_letter(col_idx)}{row_idx}"
            if isinstance(value, (int, float)) and not isinstance(value, bool):
                cells.append(f'<c r="{ref}"><v>{value}</v></c>')
            else:
                text = escape(str(value if value is not None else ""))
                cells.append(f'<c r="{ref}" t="inlineStr"><is><t>{text}</t></is></c>')
        sheet_rows.append(f'<row r="{row_idx}">{"".join(cells)}</row>')
    sheet_xml = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
        f"<sheetData>{''.join(sheet_rows)}</sheetData></worksheet>"
    )
    workbook_xml = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
        'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
        f'<sheets><sheet name="{escape(sheet_name)}" sheetId="1" r:id="rId1"/></sheets></workbook>'
    )
    content_types = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
        '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
        '<Default Extension="xml" ContentType="application/xml"/>'
        '<Override PartName="/xl/workbook.xml" '
        'ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
        '<Override PartName="/xl/worksheets/sheet1.xml" '
        'ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
        "</Types>"
    )
    rels_root = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
        '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" '
        'Target="xl/workbook.xml"/>'
        "</Relationships>"
    )
    rels_workbook = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
        '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" '
        'Target="worksheets/sheet1.xml"/>'
        "</Relationships>"
    )
    buffer = io.BytesIO()
    with zipfile.ZipFile(buffer, "w", compression=zipfile.ZIP_DEFLATED) as archive:
        archive.writestr("[Content_Types].xml", content_types)
        archive.writestr("_rels/.rels", rels_root)
        archive.writestr("xl/workbook.xml", workbook_xml)
        archive.writestr("xl/_rels/workbook.xml.rels", rels_workbook)
        archive.writestr("xl/worksheets/sheet1.xml", sheet_xml)
    return buffer.getvalue()


def build_consumption_xlsx(enterprise_id: str, date_from: str, date_to: str) -> bytes:
    data = list_sklad_items(enterprise_id, date_from=date_from, date_to=date_to)
    rows = [
        [
            item.get("name", ""),
            item.get("consumed_total_kg", 0),
            item.get("remaining_kg", 0),
            item.get("planned_consumption_per_day_kg", 0),
        ]
        for item in data.get("items") or []
    ]
    return build_xlsx_bytes(
        "Потребление",
        ["Компонент", "Расход (кг)", "Остаток (кг)", "План кг/сут"],
        rows,
    )


def build_stock_balances_xlsx(enterprise_id: str, date_from: str, date_to: str) -> bytes:
    data = list_sklad_items(enterprise_id, date_from=date_from, date_to=date_to)
    rows = [
        [
            item.get("name", ""),
            item.get("total_kg", 0),
            item.get("inflow_kg", 0),
            item.get("remaining_kg", 0),
        ]
        for item in data.get("items") or []
        if item.get("has_stock")
    ]
    return build_xlsx_bytes(
        "Остатки",
        ["Компонент", "Всего (кг)", "Приход (кг)", "Остаток (кг)"],
        rows,
    )


def _org_subtitle_lines(enterprise_id: str, *, month: str | None = None) -> list[str]:
    settings = get_org_settings(enterprise_id)
    lines = [
        f"Организация: {settings.get('organization_name') or '—'}",
        f"ОКПО: {settings.get('okpo') or '—'}",
        f"Отделение: {settings.get('department') or '—'}",
        f"Ферма: {settings.get('farm_name') or '—'}",
    ]
    if month:
        lines.append(f"Период: {month}")
    return lines


def _collect_sp20_rows(enterprise_id: str, month: str) -> list[list[Any]]:
    window = _month_window(month)
    reports = _reports_in_window(enterprise_id, window)
    rows: list[list[Any]] = []
    for payload in reports:
        start_time = payload.get("start_time") or ""
        recipe_name = payload.get("recipe_name") or ""
        components = payload.get("components")
        if not isinstance(components, list):
            continue
        for comp in components:
            if not isinstance(comp, dict):
                continue
            actual = float(comp.get("actual_weight") or 0)
            if actual <= 0:
                continue
            rows.append(
                [
                    str(start_time)[:10],
                    recipe_name,
                    comp.get("component_name") or comp.get("component_id") or "",
                    actual,
                ]
            )
    return rows


def build_sp20_xlsx(enterprise_id: str, month: str) -> bytes:
    rows = _collect_sp20_rows(enterprise_id, month)
    return build_xlsx_bytes(
        "СП-20",
        ["Дата", "Рецепт", "Компонент", "Расход (кг)"],
        rows,
    )


def build_sp20_pdf(enterprise_id: str, month: str) -> bytes:
    rows = _collect_sp20_rows(enterprise_id, month)
    total_kg = round(sum(float(row[3]) for row in rows), 2)
    settings = get_org_settings(enterprise_id)
    footer = [
        f"Итого расход: {total_kg} кг",
        f"Зоотехник: {settings.get('zootechnician') or '—'}",
        f"Кладовщик: {settings.get('warehouse_keeper') or '—'}",
    ]
    return build_table_pdf(
        title="СП-20 (ОКУД 0325020)",
        subtitle_lines=_org_subtitle_lines(enterprise_id, month=month),
        headers=["Дата", "Рецепт", "Компонент", "Расход (кг)"],
        rows=rows,
        footer_lines=footer,
    )


def build_consumption_pdf(enterprise_id: str, date_from: str, date_to: str) -> bytes:
    data = list_sklad_items(enterprise_id, date_from=date_from, date_to=date_to)
    rows = [
        [
            item.get("name", ""),
            item.get("consumed_total_kg", 0),
            item.get("remaining_kg", 0),
            item.get("planned_consumption_per_day_kg", 0),
        ]
        for item in data.get("items") or []
    ]
    return build_table_pdf(
        title="Потребление кормов",
        subtitle_lines=_org_subtitle_lines(enterprise_id) + [f"Период: {date_from} — {date_to}"],
        headers=["Компонент", "Расход (кг)", "Остаток (кг)", "План кг/сут"],
        rows=rows,
    )


def _collect_journal_rows(enterprise_id: str, month: str) -> list[list[Any]]:
    window = _month_window(month)
    reports = _reports_in_window(enterprise_id, window)
    return [
        [
            str(payload.get("start_time") or "")[:19],
            payload.get("recipe_name") or "",
            payload.get("total_weight") or 0,
            payload.get("dispenser_type") or "",
        ]
        for payload in reports
    ]


def build_journal_xlsx(enterprise_id: str, month: str) -> bytes:
    rows = _collect_journal_rows(enterprise_id, month)
    return build_xlsx_bytes(
        "Журнал",
        ["Дата/время", "Рецепт", "Вес (кг)", "Тип"],
        rows,
    )


def build_journal_pdf(enterprise_id: str, month: str) -> bytes:
    rows = _collect_journal_rows(enterprise_id, month)
    return build_table_pdf(
        title="Журнал учёта кормов",
        subtitle_lines=_org_subtitle_lines(enterprise_id, month=month),
        headers=["Дата/время", "Рецепт", "Вес (кг)", "Тип"],
        rows=rows,
    )


def build_documents_zip(enterprise_id: str, date_from: str, date_to: str, month: str) -> bytes:
    files = {
        f"consumption_{date_from}_{date_to}.xlsx": build_consumption_xlsx(enterprise_id, date_from, date_to),
        f"stock_balances_{date_from}_{date_to}.xlsx": build_stock_balances_xlsx(enterprise_id, date_from, date_to),
        f"sp20_{month}.xlsx": build_sp20_xlsx(enterprise_id, month),
        f"journal_{month}.xlsx": build_journal_xlsx(enterprise_id, month),
    }
    buffer = io.BytesIO()
    with zipfile.ZipFile(buffer, "w", compression=zipfile.ZIP_DEFLATED) as archive:
        for name, content in files.items():
            archive.writestr(name, content)
    return buffer.getvalue()


def safe_filename_part(value: str) -> str:
    cleaned = re.sub(r"[^\w\-.]+", "_", value.strip())
    return cleaned or "export"