zioinfo-mail/workspace/guardia-itsm/routers/auto_report.py

"""
자동 보고서 생성 — 주간/월간/분기 운영 보고서 자동 발송

기존 report.py를 확장하여 스케줄 기반 자동 생성 + 이메일 발송.

엔드포인트:
  GET  /api/auto-report/templates     — 보고서 템플릿 목록
  POST /api/auto-report/generate      — 보고서 즉시 생성
  GET  /api/auto-report/list          — 생성된 보고서 목록
  GET  /api/auto-report/{id}/download — 보고서 다운로드
  POST /api/auto-report/schedule      — 자동 발송 스케줄 설정
  GET  /api/auto-report/schedule      — 스케줄 목록
"""
from __future__ import annotations

import io
import json
import logging
from datetime import date, datetime, timedelta
from typing import Optional

from fastapi import APIRouter, Depends, HTTPException, Response
from pydantic import BaseModel, Field
from sqlalchemy import select, func
from sqlalchemy.ext.asyncio import AsyncSession

from core.auth import get_current_user, require_admin_role
from database import get_db
from models import User, SRRequest, SRStatus, ReportRecord, ReportSchedule  # 신규

logger = logging.getLogger(__name__)
router = APIRouter(prefix="/api/auto-report", tags=["Auto Report"])

TEMPLATES = {
    "weekly_ops":    {"name": "주간 운영 보고서", "period": "WEEKLY",  "format": ["excel", "pdf"]},
    "monthly_sla":   {"name": "월간 SLA 보고서",  "period": "MONTHLY", "format": ["excel", "pdf"]},
    "incident_rca":  {"name": "인시던트 분석",    "period": "MONTHLY", "format": ["pdf"]},
    "capacity_plan": {"name": "용량 계획 보고서",  "period": "QUARTERLY","format": ["excel"]},
}


class GenerateRequest(BaseModel):
    template: str = Field(..., description="weekly_ops | monthly_sla | incident_rca | capacity_plan")
    period_start: Optional[str] = None  # YYYY-MM-DD
    period_end: Optional[str] = None
    format: str = Field("excel", pattern="^(excel|pdf)$")
    send_email: bool = False
    email: Optional[str] = None

class ScheduleCreate(BaseModel):
    template: str
    cron: str = Field(..., description="cron 표현식 (예: 0 9 * * 1 = 매주 월요일 9시)")
    email: str
    format: str = "excel"


async def _collect_report_data(template: str, start: date, end: date, db: AsyncSession) -> dict:
    """보고서 데이터 수집."""
    total_r = await db.execute(
        select(func.count(SRRequest.id)).where(
            SRRequest.created_at >= start, SRRequest.created_at <= end
        )
    )
    done_r = await db.execute(
        select(func.count(SRRequest.id)).where(
            SRRequest.status == SRStatus.DONE,
            SRRequest.updated_at >= start, SRRequest.updated_at <= end,
        )
    )
    open_r = await db.execute(
        select(func.count(SRRequest.id)).where(SRRequest.status.in_([SRStatus.OPEN, SRStatus.IN_PROGRESS]))
    )
    mttr_r = await db.execute(
        select(func.avg(
            func.extract('epoch', SRRequest.updated_at - SRRequest.created_at) / 3600
        )).where(SRRequest.status == SRStatus.DONE, SRRequest.updated_at >= start, SRRequest.updated_at <= end)
    )

    total = total_r.scalar() or 0
    done = done_r.scalar() or 0
    return {
        "period": {"start": start.isoformat(), "end": end.isoformat()},
        "sr_total": total, "sr_done": done, "sr_open": open_r.scalar() or 0,
        "completion_rate": round(done / total * 100, 1) if total else 0,
        "mttr_hours": round(mttr_r.scalar() or 0, 1),
    }


def _build_excel(data: dict, template: str) -> bytes:
    """Excel 보고서 생성 (openpyxl)."""
    try:
        import openpyxl
        from openpyxl.styles import Font, PatternFill, Alignment

        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = TEMPLATES.get(template, {}).get("name", "보고서")

        # 헤더
        ws["A1"] = TEMPLATES.get(template, {}).get("name", "운영 보고서")
        ws["A1"].font = Font(bold=True, size=14)
        ws["A2"] = f"기간: {data['period']['start']} ~ {data['period']['end']}"

        ws["A4"] = "지표"; ws["B4"] = "값"
        ws["A4"].font = Font(bold=True)
        ws["B4"].font = Font(bold=True)

        rows = [
            ("신규 SR", data["sr_total"]),
            ("완료 SR", data["sr_done"]),
            ("미처리 SR", data["sr_open"]),
            ("완료율 (%)", data["completion_rate"]),
            ("평균 처리 시간 (시간)", data["mttr_hours"]),
        ]
        for i, (label, value) in enumerate(rows, start=5):
            ws[f"A{i}"] = label
            ws[f"B{i}"] = value

        ws["A4"].fill = PatternFill(start_color="003366", end_color="003366", fill_type="solid")
        ws["A4"].font = Font(bold=True, color="FFFFFF")
        ws["B4"].fill = PatternFill(start_color="003366", end_color="003366", fill_type="solid")
        ws["B4"].font = Font(bold=True, color="FFFFFF")
        ws.column_dimensions["A"].width = 25
        ws.column_dimensions["B"].width = 15

        output = io.BytesIO()
        wb.save(output)
        return output.getvalue()
    except ImportError:
        # openpyxl 없으면 CSV 대체
        lines = [f"{k},{v}" for k, v in [("지표", "값")] + [(str(k), str(v)) for k, v in [
            ("신규 SR", data["sr_total"]), ("완료율", data["completion_rate"])
        ]]]
        return "\n".join(lines).encode('utf-8-sig')


@router.get("/templates")
async def list_templates():
    return [{"code": k, **v} for k, v in TEMPLATES.items()]


@router.post("/generate")
async def generate_report(
    req: GenerateRequest, db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    if req.template not in TEMPLATES:
        raise HTTPException(400, f"알 수 없는 템플릿: {req.template}")

    today = date.today()
    if req.period_start and req.period_end:
        start = date.fromisoformat(req.period_start)
        end = date.fromisoformat(req.period_end)
    else:
        period = TEMPLATES[req.template]["period"]
        if period == "WEEKLY":
            start = today - timedelta(days=7); end = today
        elif period == "QUARTERLY":
            q_start = date(today.year, ((today.month - 1) // 3) * 3 + 1, 1)
            start = q_start; end = today
        else:  # MONTHLY
            start = today.replace(day=1); end = today

    data = await _collect_report_data(req.template, start, end, db)
    excel_bytes = _build_excel(data, req.template)

    record = ReportRecord(
        tenant_id=user.tenant_id, template=req.template,
        period_start=start, period_end=end,
        format=req.format, file_size=len(excel_bytes),
        status="DONE", generated_by=user.id, created_at=datetime.utcnow()
    )
    db.add(record)
    await db.commit()
    await db.refresh(record)

    return {
        "ok": True, "report_id": record.id,
        "template": req.template, "period": data["period"],
        "data_summary": data,
    }


@router.get("/{report_id}/download")
async def download_report(
    report_id: int, db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    row = await db.execute(
        select(ReportRecord).where(ReportRecord.id == report_id, ReportRecord.tenant_id == user.tenant_id)
    )
    record = row.scalar_one_or_none()
    if not record: raise HTTPException(404, "보고서 없음")

    data = await _collect_report_data(record.template, record.period_start, record.period_end, db)
    excel_bytes = _build_excel(data, record.template)
    filename = f"report_{record.template}_{record.period_start}.xlsx"
    return Response(
        content=excel_bytes,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f"attachment; filename={filename}"},
    )


@router.get("/list")
async def list_reports(db: AsyncSession = Depends(get_db), user: User = Depends(get_current_user)):
    rows = await db.execute(
        select(ReportRecord).where(ReportRecord.tenant_id == user.tenant_id)
        .order_by(ReportRecord.created_at.desc()).limit(50)
    )
    records = rows.scalars().all()
    return [
        {"id": r.id, "template": r.template, "period": f"{r.period_start}~{r.period_end}",
         "format": r.format, "status": r.status, "created_at": r.created_at}
        for r in records
    ]