"""Router audit serveurs — résultats des scans + audit temps réel"""
import csv, io
from fastapi import APIRouter, Request, Depends, Query, Form, UploadFile, File
from fastapi.responses import HTMLResponse, RedirectResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from sqlalchemy import text
from ..dependencies import get_db, get_current_user, get_user_perms, can_view, can_edit, can_admin, base_context
from ..services.realtime_audit_service import audit_servers_list, save_audit_to_db, start_audit_job, get_audit_job
from ..config import APP_NAME

router = APIRouter()
templates = Jinja2Templates(directory="app/templates")


@router.get("/audit", response_class=HTMLResponse)
async def audit_page(request: Request, db=Depends(get_db),
                     filter: str = Query(None), search: str = Query(None)):
    user = get_current_user(request)
    if not user:
        return RedirectResponse(url="/login")
    perms = get_user_perms(db, user)
    if not can_view(perms, "audit"):
        return RedirectResponse(url="/dashboard")

    where = ["1=1"]
    params = {}
    if filter == "failed":
        where.append("sa.status = 'CONNECTION_FAILED'")
    elif filter == "disk":
        where.append("sa.disk_alert = true")
    elif filter == "no_qualys":
        where.append("sa.qualys_active = false AND sa.status = 'OK'")
    elif filter == "no_s1":
        where.append("sa.sentinelone_active = false AND sa.status = 'OK'")
    elif filter == "no_autostart":
        where.append("sa.running_not_enabled IS NOT NULL AND sa.running_not_enabled != '' AND sa.status = 'OK'")
    elif filter == "failed_svc":
        where.append("sa.failed_services IS NOT NULL AND sa.failed_services != '' AND sa.status = 'OK'")
    if search:
        where.append("sa.hostname ILIKE :q")
        params["q"] = f"%{search}%"

    wc = " AND ".join(where)

    entries = db.execute(text(f"""
        SELECT sa.*, d.name as domaine, e.name as environnement
        FROM server_audit sa
        LEFT JOIN servers s ON sa.server_id = s.id
        LEFT JOIN domain_environments de ON s.domain_env_id = de.id
        LEFT JOIN domains d ON de.domain_id = d.id
        LEFT JOIN environments e ON de.environment_id = e.id
        WHERE {wc}
        ORDER BY sa.disk_alert DESC, sa.status, sa.hostname
        LIMIT 500
    """), params).fetchall()

    stats = db.execute(text("""
        SELECT
            COUNT(*) as total,
            COUNT(*) FILTER (WHERE status = 'OK') as ok,
            COUNT(*) FILTER (WHERE status = 'CONNECTION_FAILED') as failed,
            COUNT(*) FILTER (WHERE disk_alert = true) as disk_alerts,
            COUNT(*) FILTER (WHERE qualys_active = true) as qualys_ok,
            COUNT(*) FILTER (WHERE sentinelone_active = true) as s1_ok,
            COUNT(*) FILTER (WHERE running_not_enabled IS NOT NULL AND running_not_enabled != '') as no_autostart,
            COUNT(*) FILTER (WHERE failed_services IS NOT NULL AND failed_services != '') as failed_svc
        FROM server_audit
    """)).fetchone()

    # Dernier audit global
    last_audit = db.execute(text(
        "SELECT MAX(audit_date) as last_date, COUNT(*) as count FROM server_audit"
    )).fetchone()

    # Domaines et zones pour exclusions
    domains = db.execute(text("SELECT code, name FROM domains ORDER BY display_order")).fetchall()
    zones = db.execute(text("SELECT DISTINCT name FROM zones ORDER BY name")).fetchall()

    # Jobs audit en cours (RAM)
    from ..services.realtime_audit_service import _audit_jobs
    active_jobs = [
        {"id": j["id"], "total": j["total"], "done": j["done"],
         "parallel": j.get("parallel", "?"), "started_at": j.get("started_at", 0)}
        for j in _audit_jobs.values() if not j.get("finished")
    ]
    active_jobs.sort(key=lambda x: -x["started_at"])

    ctx = base_context(request, db, user)
    ctx.update({
        "app_name": APP_NAME, "entries": entries, "stats": stats,
        "filter": filter, "search": search,
        "last_audit": last_audit, "domains": domains, "zones": zones,
        "active_jobs": active_jobs,
    })
    return templates.TemplateResponse("audit.html", ctx)


@router.get("/audit/realtime", response_class=HTMLResponse)
async def audit_realtime_redirect(request: Request):
    return RedirectResponse(url="/dashboard", status_code=302)

@router.get("/audit/specific", response_class=HTMLResponse)
async def audit_specific_page(request: Request, db=Depends(get_db)):
    user = get_current_user(request)
    if not user:
        return RedirectResponse(url="/login")
    perms = get_user_perms(db, user)
    if not can_edit(perms, "audit"):
        return RedirectResponse(url="/audit")
    ctx = base_context(request, db, user)
    ctx["app_name"] = APP_NAME
    return templates.TemplateResponse("audit_specific.html", ctx)


@router.get("/audit/{audit_id}", response_class=HTMLResponse)
async def audit_detail(request: Request, audit_id: int, db=Depends(get_db)):
    user = get_current_user(request)
    if not user:
        return HTMLResponse("<p>Non autorisé</p>")
    from ..dependencies import get_user_perms, can_view
    if not can_view(get_user_perms(db, user), "audit"):
        return HTMLResponse("<p>Non autorisé</p>")
    entry = db.execute(text("SELECT * FROM server_audit WHERE id = :id"),
                       {"id": audit_id}).fetchone()
    if not entry:
        return HTMLResponse("<p>Non trouvé</p>")
    return templates.TemplateResponse("partials/audit_detail.html", {
        "request": request, "e": entry,
    })


# --- Audit temps réel ---

@router.post("/audit/global", response_class=HTMLResponse)
async def audit_global(request: Request, db=Depends(get_db)):
    """Lance un audit global sur tous les serveurs Linux hors exclusions"""
    user = get_current_user(request)
    if not user:
        return RedirectResponse(url="/login")
    perms = get_user_perms(db, user)
    if not can_edit(perms, "audit"):
        return RedirectResponse(url="/audit")

    form = await request.form()
    exclude_domains = form.getlist("exclude_domains")
    exclude_zones = form.getlist("exclude_zones")
    parallel = int(form.get("parallel", "5"))

    # Construire la requete
    where = ["s.os_family = 'linux'", "s.etat = 'Production'"]
    params = {}
    # Matche sur domaine plain-text (via d.name) ou via d.code legacy
    # et accepte les serveurs sans domaine attribue (ne pas penaliser le NULL)
    if exclude_domains:
        # Match par nom (servers.domaine / domains.name) ET par code (domains.code).
        # Le form UI peut envoyer soit le name soit le code selon le template.
        # NULL = exclu (serveur non tagge = pas auditable via UI).
        where.append(
            "(s.domaine IS NOT NULL OR d.name IS NOT NULL) "
            "AND COALESCE(s.domaine, d.name) NOT IN :ed "
            "AND COALESCE(d.code, '') NOT IN :ed"
        )
        params["ed"] = tuple(exclude_domains)
    if exclude_zones:
        where.append("(z.name NOT IN :ez OR z.name IS NULL)")
        params["ez"] = tuple(exclude_zones)

    wc = " AND ".join(where)

    query = f"""
        SELECT s.hostname FROM servers s
        LEFT JOIN domain_environments de ON s.domain_env_id = de.id
        LEFT JOIN domains d ON de.domain_id = d.id
        LEFT JOIN zones z ON s.zone_id = z.id
        WHERE {wc} ORDER BY s.hostname
    """
    # Expand IN tuples
    if exclude_domains:
        placeholders = ",".join([f":ed{i}" for i in range(len(exclude_domains))])
        query = query.replace("NOT IN :ed", f"NOT IN ({placeholders})")
        for i, v in enumerate(exclude_domains):
            params[f"ed{i}"] = v
        del params["ed"]
    if exclude_zones:
        placeholders = ",".join([f":ez{i}" for i in range(len(exclude_zones))])
        query = query.replace("NOT IN :ez", f"NOT IN ({placeholders})")
        for i, v in enumerate(exclude_zones):
            params[f"ez{i}"] = v
        del params["ez"]

    rows = db.execute(text(query), params).fetchall()
    hostnames = [r.hostname for r in rows]

    if not hostnames:
        return RedirectResponse(url="/audit?msg=no_hosts", status_code=303)

    # Lancer en arrière-plan avec parallelisme configure
    job_id = start_audit_job(hostnames, parallel=parallel)
    return RedirectResponse(url=f"/audit/realtime/progress/{job_id}", status_code=303)


@router.post("/audit/realtime", response_class=HTMLResponse)
async def audit_realtime(request: Request, db=Depends(get_db),
                         hostnames_text: str = Form(""),
                         hostnames_file: UploadFile = File(None)):
    user = get_current_user(request)
    if not user:
        return RedirectResponse(url="/login")
    perms = get_user_perms(db, user)
    if not can_edit(perms, "audit"):
        return RedirectResponse(url="/audit")

    # Collecter les hostnames
    hostnames = []
    if hostnames_text.strip():
        hostnames = [h.strip() for h in hostnames_text.strip().replace(",", "\n").split("\n") if h.strip()]
    if hostnames_file and hostnames_file.filename:
        content = (await hostnames_file.read()).decode("utf-8", errors="replace")
        hostnames += [h.strip() for h in content.split("\n") if h.strip() and not h.startswith("#")]

    if not hostnames:
        return RedirectResponse(url="/audit?msg=no_hosts", status_code=303)

    # Lancer en arrière-plan
    job_id = start_audit_job(hostnames)

    return RedirectResponse(url=f"/audit/realtime/progress/{job_id}", status_code=303)


@router.get("/audit/realtime/progress/{job_id}", response_class=HTMLResponse)
async def audit_realtime_progress(request: Request, job_id: str, db=Depends(get_db)):
    user = get_current_user(request)
    if not user:
        return RedirectResponse(url="/login")
    perms = get_user_perms(db, user)
    if not can_view(perms, "audit"):
        return RedirectResponse(url="/audit")

    job = get_audit_job(job_id)
    if not job:
        return RedirectResponse(url="/audit?msg=job_not_found", status_code=303)

    ctx = base_context(request, db, user)
    ctx.update({"app_name": APP_NAME, "job_id": job_id, "total": job["total"]})
    return templates.TemplateResponse("audit_realtime_progress.html", ctx)


@router.get("/audit/realtime/results/{job_id}", response_class=HTMLResponse)
async def audit_realtime_results(request: Request, job_id: str, db=Depends(get_db)):
    user = get_current_user(request)
    if not user:
        return RedirectResponse(url="/login")
    perms = get_user_perms(db, user)
    if not can_view(perms, "audit"):
        return RedirectResponse(url="/audit")

    job = get_audit_job(job_id)
    if not job:
        return RedirectResponse(url="/audit?msg=job_not_found", status_code=303)

    ctx = base_context(request, db, user)
    ctx.update({"app_name": APP_NAME, "job_id": job_id, "results": job["results"]})
    return templates.TemplateResponse("audit_realtime_results.html", ctx)


@router.get("/audit/realtime/status/{job_id}")
async def audit_realtime_status(request: Request, job_id: str, db=Depends(get_db)):
    from fastapi.responses import JSONResponse
    user = get_current_user(request)
    if not user:
        return JSONResponse({"ok": False}, status_code=401)

    job = get_audit_job(job_id)
    if not job:
        return JSONResponse({"ok": False, "msg": "Job introuvable"}, status_code=404)

    import time
    elapsed = int(time.time() - job["started_at"])

    # When finished, store results in app state for save/export
    if job["finished"]:
        request.app.state.last_audit_results = job["results"]

    return JSONResponse({
        "ok": True,
        "job_id": job_id,
        "total": job["total"],
        "done": job["done"],
        "finished": job["finished"],
        "elapsed": elapsed,
        "servers": job["servers"],
    })


@router.post("/audit/realtime/save")
async def audit_realtime_save(request: Request, db=Depends(get_db)):
    user = get_current_user(request)
    if not user:
        return RedirectResponse(url="/login")
    perms = get_user_perms(db, user)
    if not can_edit(perms, "audit"):
        return RedirectResponse(url="/audit")

    results = getattr(request.app.state, "last_audit_results", None)
    if not results:
        return RedirectResponse(url="/audit?msg=no_results", status_code=303)

    updated, inserted = save_audit_to_db(db, results)
    return RedirectResponse(url=f"/audit?msg=saved_{updated}_{inserted}", status_code=303)


@router.get("/audit/export/csv")
async def audit_export_csv(request: Request, db=Depends(get_db),
                           filter: str = Query(None), search: str = Query(None)):
    user = get_current_user(request)
    if not user:
        return RedirectResponse(url="/login")
    perms = get_user_perms(db, user)
    if not can_view(perms, "audit"):
        return RedirectResponse(url="/audit")

    where = ["1=1"]
    params = {}
    if filter == "failed":
        where.append("sa.status = 'CONNECTION_FAILED'")
    elif filter == "no_qualys":
        where.append("sa.qualys_active = false AND sa.status = 'OK'")
    elif filter == "no_s1":
        where.append("sa.sentinelone_active = false AND sa.status = 'OK'")
    if search:
        where.append("sa.hostname ILIKE :q"); params["q"] = f"%{search}%"
    wc = " AND ".join(where)

    rows = db.execute(text(f"""
        SELECT sa.hostname, sa.status, sa.connection_method, sa.resolved_fqdn,
            sa.os_release, sa.kernel, sa.uptime, sa.selinux,
            sa.disk_detail, sa.disk_alert, sa.apps_installed,
            sa.services_running, sa.running_not_enabled, sa.listening_ports,
            sa.db_detected, sa.cluster_detected, sa.containers,
            sa.agents, sa.qualys_active, sa.sentinelone_active,
            sa.failed_services, sa.audit_date
        FROM server_audit sa WHERE {wc} ORDER BY sa.hostname
    """), params).fetchall()

    output = io.StringIO()
    writer = csv.writer(output, delimiter=";")
    headers = ["Hostname", "Statut", "Connexion", "FQDN", "OS", "Kernel", "Uptime", "SELinux",
               "Disque", "Alerte disque", "Applications", "Services running",
               "Sans auto-start", "Ports", "BDD", "Cluster", "Containers",
               "Agents", "Qualys", "SentinelOne", "Services KO", "Date audit"]
    writer.writerow(headers)
    for r in rows:
        writer.writerow([
            r.hostname, r.status, r.connection_method, r.resolved_fqdn,
            (r.os_release or "")[:80], r.kernel, r.uptime, r.selinux,
            (r.disk_detail or "")[:100], "OUI" if r.disk_alert else "NON",
            (r.apps_installed or "")[:100], (r.services_running or "")[:100],
            (r.running_not_enabled or "")[:100], (r.listening_ports or "")[:100],
            r.db_detected, r.cluster_detected, (r.containers or "")[:80],
            r.agents, "OUI" if r.qualys_active else "NON",
            "OUI" if r.sentinelone_active else "NON", r.failed_services,
            r.audit_date.strftime("%Y-%m-%d %H:%M") if r.audit_date else "",
        ])

    output.seek(0)
    return StreamingResponse(
        iter([output.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": f"attachment; filename=audit_export_{filter or 'all'}.csv"}
    )
