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

"""
폐쇄망 ↔ 개방망 데이터 Export / Import 인터페이스

Export (폐쇄망 → 파일):
  GET /api/export/bundle          — 전체 데이터 번들 (JSON ZIP)
  GET /api/export/sr              — SR 목록 JSON
  GET /api/export/cmdb            — CMDB 서버 자산 JSON
  GET /api/export/kb              — 지식베이스 JSON
  GET /api/export/audit           — 감사 로그 JSON

Import (파일 → 개방망 GUARDiA):
  POST /api/import/bundle         — 번들 ZIP 업로드 및 병합
  POST /api/import/sr             — SR JSON 업로드
  POST /api/import/cmdb           — CMDB JSON 업로드

보안:
  - Admin JWT 인증 필수
  - 민감 필드(ip_addr, os_pw_enc, ssh_user) Export 시 마스킹
  - 번들에 HMAC-SHA256 서명 포함
"""
import hashlib, hmac, io, json, os, zipfile
from datetime import datetime
from typing import Optional

from fastapi import APIRouter, Depends, File, HTTPException, Query, UploadFile
from fastapi.responses import StreamingResponse
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from core.auth import get_current_user, require_admin_role
from database import get_db
from models import (
    SRRequest, Institution, Server, AuditLog, User,
)

router = APIRouter(prefix="/api/export-import", tags=["Export/Import (폐쇄망 연동)"])

BUNDLE_SECRET = os.environ.get("GUARDIA_BUNDLE_SECRET", "guardia-bundle-hmac-2026")
SENSITIVE_FIELDS = {"ip_addr", "os_pw_enc", "ssh_user", "ssh_pw"}


# ── 유틸 ──────────────────────────────────────────────────────────────────────

def _mask_row(row: dict) -> dict:
    return {k: ("****" if k in SENSITIVE_FIELDS else v) for k, v in row.items()}

def _sign_bundle(data: bytes) -> str:
    return hmac.new(BUNDLE_SECRET.encode(), data, hashlib.sha256).hexdigest()

def _row_to_dict(obj) -> dict:
    from datetime import date
    result = {}
    for col in obj.__table__.columns:
        val = getattr(obj, col.name)
        if isinstance(val, datetime):
            val = val.isoformat()
        elif isinstance(val, date):
            val = val.isoformat()
        elif val is not None and not isinstance(val, (str, int, float, bool, dict, list)):
            val = str(val)
        result[col.name] = val
    return _mask_row(result)


# ── Export 엔드포인트 ─────────────────────────────────────────────────────────

@router.get("/export/sr", summary="SR 목록 Export (JSON)")
async def export_sr(
    limit: int = Query(default=1000, le=5000),
    since: Optional[str] = Query(default=None, description="ISO 날짜, 예: 2026-01-01"),
    db: AsyncSession = Depends(get_db),
    _user=Depends(require_admin_role),
):
    q = select(SRRequest).order_by(SRRequest.created_at.desc()).limit(limit)
    if since:
        try:
            dt = datetime.fromisoformat(since).replace(tzinfo=None)
            q = q.where(SRRequest.created_at >= dt)
        except ValueError:
            raise HTTPException(status_code=400, detail="since 날짜 형식 오류 (ISO 8601)")
    rows = (await db.execute(q)).scalars().all()
    data = [_row_to_dict(r) for r in rows]
    return {
        "exported_at": datetime.utcnow().isoformat(),
        "count": len(data),
        "type": "sr",
        "data": data,
    }


@router.get("/export/cmdb", summary="CMDB 서버 자산 Export (JSON)")
async def export_cmdb(
    db: AsyncSession = Depends(get_db),
    _user=Depends(require_admin_role),
):
    rows = (await db.execute(select(Server))).scalars().all()
    data = [_row_to_dict(r) for r in rows]
    return {
        "exported_at": datetime.utcnow().isoformat(),
        "count": len(data),
        "type": "cmdb",
        "data": data,
    }


@router.get("/export/institutions", summary="기관 목록 Export (JSON)")
async def export_institutions(
    db: AsyncSession = Depends(get_db),
    _user=Depends(require_admin_role),
):
    rows = (await db.execute(select(Institution))).scalars().all()
    data = [_row_to_dict(r) for r in rows]
    return {
        "exported_at": datetime.utcnow().isoformat(),
        "count": len(data),
        "type": "institutions",
        "data": data,
    }


@router.get("/export/audit", summary="감사 로그 Export (JSON)")
async def export_audit(
    limit: int = Query(default=500, le=2000),
    db: AsyncSession = Depends(get_db),
    _user=Depends(require_admin_role),
):
    rows = (await db.execute(
        select(AuditLog).order_by(AuditLog.created_at.desc()).limit(limit)
    )).scalars().all()
    data = [_row_to_dict(r) for r in rows]
    return {
        "exported_at": datetime.utcnow().isoformat(),
        "count": len(data),
        "type": "audit",
        "data": data,
    }


@router.get("/export/bundle", summary="전체 번들 Export (ZIP 다운로드)")
async def export_bundle(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_admin_role),
):
    """
    SR, CMDB, 기관, 감사로그를 하나의 ZIP으로 패키징합니다.
    - 민감 필드(IP, 비밀번호)는 마스킹
    - 번들에 HMAC-SHA256 서명 포함
    - Content-Type: application/zip
    """
    bundle: dict[str, dict] = {}

    for name, q in [
        ("sr",           select(SRRequest).order_by(SRRequest.created_at.desc()).limit(2000)),
        ("cmdb",         select(Server)),
        ("institutions", select(Institution)),
        ("audit",        select(AuditLog).order_by(AuditLog.created_at.desc()).limit(1000)),
    ]:
        rows = (await db.execute(q)).scalars().all()
        bundle[name] = {
            "exported_at": datetime.utcnow().isoformat(),
            "count": len(rows),
            "type": name,
            "data": [_row_to_dict(r) for r in rows],
        }

    manifest = {
        "version": "1.0",
        "source": "GUARDiA ITSM (폐쇄망)",
        "exported_at": datetime.utcnow().isoformat(),
        "exported_by": user.username,
        "contents": list(bundle.keys()),
        "signature": "",
    }
    bundle_json = json.dumps(bundle, ensure_ascii=False, indent=2).encode()
    manifest["signature"] = _sign_bundle(bundle_json)

    # ZIP 생성
    buf = io.BytesIO()
    with zipfile.ZipFile(buf, "w", zipfile.ZIP_DEFLATED) as zf:
        zf.writestr("manifest.json", json.dumps(manifest, ensure_ascii=False, indent=2))
        zf.writestr("bundle.json",   bundle_json)
        for name, section in bundle.items():
            zf.writestr(f"{name}.json", json.dumps(section, ensure_ascii=False, indent=2))
    buf.seek(0)

    filename = f"guardia_export_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}.zip"
    return StreamingResponse(
        buf,
        media_type="application/zip",
        headers={"Content-Disposition": f"attachment; filename={filename}"},
    )


# ── Import 엔드포인트 ─────────────────────────────────────────────────────────

@router.post("/import/bundle", summary="번들 ZIP Import (폐쇄망 데이터 업로드)")
async def import_bundle(
    file: UploadFile = File(..., description="export_bundle에서 생성된 ZIP 파일"),
    dry_run: bool = Query(default=True, description="True: 검증만 (실제 저장 안 함)"),
    db: AsyncSession = Depends(get_db),
    _user=Depends(require_admin_role),
):
    """
    폐쇄망에서 Export한 번들 ZIP을 업로드합니다.
    - HMAC 서명 검증
    - dry_run=True (기본): 데이터 검증만 수행 (DB 저장 없음)
    - dry_run=False: 실제 병합 (중복 sr_id는 SKIP)
    """
    if not file.filename.endswith(".zip"):
        raise HTTPException(status_code=400, detail="ZIP 파일만 업로드 가능합니다.")

    content = await file.read()
    if len(content) > 50 * 1024 * 1024:  # 50MB 제한
        raise HTTPException(status_code=413, detail="파일 크기 50MB 초과")

    try:
        buf = io.BytesIO(content)
        with zipfile.ZipFile(buf, "r") as zf:
            names = zf.namelist()
            if "manifest.json" not in names or "bundle.json" not in names:
                raise HTTPException(status_code=400, detail="유효하지 않은 번들 파일입니다.")

            manifest = json.loads(zf.read("manifest.json"))
            bundle_json = zf.read("bundle.json")
            bundle = json.loads(bundle_json)
    except (zipfile.BadZipFile, json.JSONDecodeError) as e:
        raise HTTPException(status_code=400, detail=f"파일 파싱 오류: {e}")

    # HMAC 검증
    expected_sig = _sign_bundle(bundle_json)
    if manifest.get("signature") != expected_sig:
        raise HTTPException(status_code=400, detail="번들 서명 검증 실패 (위변조 의심)")

    stats = {
        "manifest": {
            "exported_at": manifest.get("exported_at"),
            "source":      manifest.get("source"),
            "exported_by": manifest.get("exported_by"),
        },
        "sections": {},
    }

    for section_name, section in bundle.items():
        count = section.get("count", 0)
        stats["sections"][section_name] = {
            "count": count, "status": "dry_run" if dry_run else "imported",
        }

    if not dry_run:
        # SR Import (중복 sr_id SKIP)
        sr_section = bundle.get("sr", {})
        imported = 0
        for row in sr_section.get("data", []):
            existing = await db.execute(
                select(SRRequest).where(SRRequest.sr_id == row.get("sr_id"))
            )
            if existing.scalars().first():
                continue
            sr = SRRequest(
                sr_id       = row.get("sr_id", f"IMP-{hashlib.md5(str(row).encode()).hexdigest()[:8].upper()}"),
                title       = row.get("title", "(imported)"),
                description = row.get("description", ""),
                status      = row.get("status", "RECEIVED"),
                priority    = row.get("priority", "MEDIUM"),
                sr_type     = row.get("sr_type", "OTHER"),
                requested_by= row.get("requested_by", "imported"),
            )
            db.add(sr); imported += 1
        await db.commit()
        stats["sections"]["sr"]["imported"] = imported

    return {
        "status":  "dry_run" if dry_run else "imported",
        "message": "검증 완료 (dry_run)" if dry_run else f"Import 완료",
        **stats,
    }


@router.post("/import/sr", summary="SR JSON Import")
async def import_sr(
    file: UploadFile = File(..., description="export/sr에서 내보낸 JSON 파일"),
    dry_run: bool = Query(default=True),
    db: AsyncSession = Depends(get_db),
    _user=Depends(require_admin_role),
):
    content = await file.read()
    try:
        data = json.loads(content)
        records = data.get("data", data) if isinstance(data, dict) else data
    except json.JSONDecodeError as e:
        raise HTTPException(status_code=400, detail=f"JSON 파싱 오류: {e}")

    if not isinstance(records, list):
        raise HTTPException(status_code=400, detail="data 배열이 필요합니다.")

    imported = 0
    if not dry_run:
        for row in records:
            existing = await db.execute(
                select(SRRequest).where(SRRequest.sr_id == row.get("sr_id"))
            )
            if existing.scalars().first():
                continue
            db.add(SRRequest(
                sr_id       = row.get("sr_id", f"IMP-{imported:06d}"),
                title       = row.get("title", "(imported)"),
                description = row.get("description", ""),
                status      = row.get("status", "RECEIVED"),
                priority    = row.get("priority", "MEDIUM"),
                sr_type     = row.get("sr_type", "OTHER"),
                requested_by= row.get("requested_by", "imported"),
            ))
            imported += 1
        await db.commit()

    return {
        "status":   "dry_run" if dry_run else "imported",
        "total":    len(records),
        "imported": imported,
        "message":  f"SR {'검증' if dry_run else '가져오기'} 완료: {len(records)}건",
    }