"""Экспорт отчётов: CSV по серверам, метрикам, задачам."""
from __future__ import annotations

import csv
from datetime import datetime
from pathlib import Path
from typing import Optional

from sqlmodel import select

from app.core.ssh import check_server
from app.db.database import get_session
from app.db.models import MetricSnapshot, Server, TaskRecord

REPORTS_DIR = Path.home() / ".debian-manager" / "reports"
REPORTS_DIR.mkdir(parents=True, exist_ok=True)


async def generate() -> str:
    """Сводный CSV-отчёт. Возвращает путь к файлу."""
    ts = datetime.now().strftime("%Y%m%d_%H%M%S")
    path = REPORTS_DIR / f"report_{ts}.csv"
    with open(path, "w", newline="", encoding="utf-8-sig") as f:
        w = csv.writer(f)
        w.writerow(["=== ОТЧЁТ ПО СЕРВЕРАМ", datetime.now().isoformat(), "==="])
        w.writerow([])
        w.writerow(["Сервер", "Хост", "Порт", "Пользователь", "Теги",
                    "Онлайн", "ОС", "Uptime", "CPU%", "RAM%", "Диск%"])
        with get_session() as s:
            servers = list(s.exec(select(Server)).all())
        for srv in servers:
            info = await check_server(srv)
            cpu = ram = disk = ""
            if info.get("online"):
                with get_session() as s:
                    m = s.exec(select(MetricSnapshot)
                               .where(MetricSnapshot.server_id == srv.id)
                               .order_by(MetricSnapshot.id.desc()).limit(1)).first()
                    if m:
                        cpu, ram, disk = m.cpu_percent, m.mem_percent, m.disk_percent
            w.writerow([srv.name, srv.host, srv.port, srv.username, srv.tags,
                        "да" if info.get("online") else "нет",
                        info.get("os", ""), info.get("uptime", ""), cpu, ram, disk])
        w.writerow([])
        w.writerow(["=== ПОСЛЕДНИЕ ЗАДАЧИ ==="])
        w.writerow(["Время", "Сервер", "Тип", "Название", "Статус"])
        with get_session() as s:
            tasks = list(s.exec(select(TaskRecord)
                                .order_by(TaskRecord.id.desc()).limit(100)).all())
            srv_names = {x.id: x.name for x in servers}
            for t in tasks:
                w.writerow([t.created_at, srv_names.get(t.server_id, "?"),
                            t.kind, t.title, t.status])
    return str(path)


def list_reports() -> list[Path]:
    return sorted(REPORTS_DIR.glob("report_*.csv"), reverse=True)