"""Context-builder query functions for the Security tab (Chapter 10),
with the IP investigation panel's traffic breakdown upgraded to full-
traffic data (bounded per-IP rollup, added as an explicit follow-up to
Chapter 10's scope gap).
"""
from __future__ import annotations

from collections import defaultdict
from datetime import date

from sqlalchemy import func

from app.extensions import db
from app.models.blocklist_suggestion import BlocklistSuggestion
from app.models.bot_hit import BotHit
from app.models.ip_registry import IPRegistry
from app.models.ip_traffic_stats import IpPathStatsDaily, IpStatusStatsDaily
from app.models.suspicious_event import SuspiciousEvent
from app.services.severity_scoring import SeverityInputs, compute_effective_severity
from app.utils.dates import day_bounds

IP_HISTORY_EVENT_LIMIT = 50
IP_HISTORY_PATH_LIMIT = 20


def get_suspicious_events(
    from_date: date, to_date: date, severity: str | None, rule_type: str | None, page: int, per_page: int,
) -> tuple[list[list], int]:
    """suspicious_events is small/indexed/indefinitely-retained (Ch06) —
    same precedent as Ch09's bot_hits queries, so loading + escalating in
    Python doesn't violate Ch03 rule 6 (that targets raw per-request rows).
    """
    start, end = day_bounds(from_date, to_date)
    query = db.session.query(SuspiciousEvent).filter(
        SuspiciousEvent.timestamp >= start, SuspiciousEvent.timestamp < end
    )
    if rule_type:
        query = query.filter(SuspiciousEvent.rule_matched.like(f"{rule_type}:%"))
    events = query.order_by(SuspiciousEvent.timestamp.desc()).all()

    by_ip: dict[str, list[SuspiciousEvent]] = defaultdict(list)
    for e in events:
        by_ip[e.ip].append(e)

    enriched = []
    for e in events:
        ip_events = by_ip[e.ip]
        timestamps = sorted(ev.timestamp for ev in ip_events)
        avg_interval = (
            (timestamps[-1] - timestamps[0]).total_seconds() / (len(timestamps) - 1)
            if len(timestamps) > 1 else None
        )
        effective = compute_effective_severity(
            SeverityInputs(base_severity=e.severity, ip_event_count=len(ip_events), avg_interval_seconds=avg_interval)
        )
        if severity and effective != severity:
            continue
        enriched.append([e.timestamp.isoformat(), e.ip, e.path, e.rule_matched, effective])

    total = len(enriched)
    offset = (page - 1) * per_page
    return enriched[offset : offset + per_page], total


def get_sensitive_path_summary(from_date: date, to_date: date) -> list[dict]:
    """Grouped by request path; filtered to Ch07's sensitive_path rule
    category. One ranked list, not sub-grouped into config/admin/VCS —
    Ch07's dictionary has no such taxonomy to reuse.
    """
    start, end = day_bounds(from_date, to_date)
    rows = (
        db.session.query(
            SuspiciousEvent.path,
            func.count().label("hit_count"),
            func.count(func.distinct(SuspiciousEvent.ip)).label("distinct_ip_count"),
        )
        .filter(
            SuspiciousEvent.timestamp >= start, SuspiciousEvent.timestamp < end,
            SuspiciousEvent.rule_matched.like("sensitive_path:%"),
        )
        .group_by(SuspiciousEvent.path)
        .order_by(func.count().desc())
        .all()
    )
    return [{"path": r.path, "hit_count": r.hit_count, "distinct_ip_count": r.distinct_ip_count} for r in rows]


def get_ip_history(ip: str) -> dict | None:
    """Pulled from ip_registry (identity + true total_requests), plus
    bot_hits/suspicious_events (flagged activity), plus the bounded
    per-IP traffic rollup (top_paths / status_code_distribution — true
    full-traffic breakdown, added as a follow-up to Ch10's original scope
    gap). Retention caveat: the per-IP rollup covers roughly the last 30
    days (see aggregator.py / flask cleanup).
    """
    registry = db.session.get(IPRegistry, ip)
    if registry is None:
        return None

    path_rows = (
        db.session.query(IpPathStatsDaily.path, func.sum(IpPathStatsDaily.count).label("count"))
        .filter(IpPathStatsDaily.ip == ip)
        .group_by(IpPathStatsDaily.path)
        .order_by(func.sum(IpPathStatsDaily.count).desc())
        .limit(IP_HISTORY_PATH_LIMIT)
        .all()
    )
    status_rows = (
        db.session.query(IpStatusStatsDaily.status_bucket, func.sum(IpStatusStatsDaily.count).label("count"))
        .filter(IpStatusStatsDaily.ip == ip)
        .group_by(IpStatusStatsDaily.status_bucket)
        .all()
    )

    bot_rows = (
        db.session.query(BotHit).filter(BotHit.ip == ip)
        .order_by(BotHit.timestamp.desc()).limit(IP_HISTORY_EVENT_LIMIT).all()
    )
    suspicious_rows = (
        db.session.query(SuspiciousEvent).filter(SuspiciousEvent.ip == ip)
        .order_by(SuspiciousEvent.timestamp.desc()).limit(IP_HISTORY_EVENT_LIMIT).all()
    )
    spoofed_bot_names = sorted({b.bot_name for b in bot_rows if not b.verified})

    return {
        "ip": ip,
        "first_seen": registry.first_seen.isoformat(),
        "last_seen": registry.last_seen.isoformat(),
        "total_requests": registry.total_requests,
        "reputation_score": registry.reputation_score,
        "is_flagged": registry.is_flagged,
        "spoofed_bot_names": spoofed_bot_names,
        "top_paths": [[r.path, r.count] for r in path_rows],
        "status_code_distribution": {r.status_bucket: r.count for r in status_rows},
        "traffic_window_note": "Path/status breakdown reflects roughly the last 30 days (bounded retention).",
        "recent_suspicious_events": [
            {"timestamp": s.timestamp.isoformat(), "path": s.path, "rule_matched": s.rule_matched, "severity": s.severity}
            for s in suspicious_rows
        ],
    }


def format_blocklist(suggestions: list[BlocklistSuggestion], fmt: str) -> str:
    """Ch10: '.htaccess Deny/iptables/fail2ban-style'. 'plain' (a bare IP
    list) is the most portable interpretation of "fail2ban-style input"
    without assuming a specific fail2ban jail configuration Ch10 doesn't
    specify.
    """
    ips = [s.ip for s in suggestions]
    if fmt == "htaccess":
        return "".join(f"Deny from {ip}\n" for ip in ips)
    if fmt == "iptables":
        return "".join(f"iptables -A INPUT -s {ip} -j DROP\n" for ip in ips)
    return "".join(f"{ip}\n" for ip in ips)