"""
Email Service für SMTP-Versand und Template-Rendering
"""
import html
import json
import logging
import os
import re
import smtplib
from datetime import datetime
from urllib.parse import quote
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from typing import Optional

from sqlalchemy.orm import Session
from app.models.setting import Setting

logger = logging.getLogger(__name__)

DEFAULT_SLA_BREACH_TEMPLATE = """<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: 'Segoe UI', Arial, sans-serif; margin: 0; padding: 20px; background: #fafafa; color: #333; }
        .container { max-width: 650px; margin: 0 auto; background: #fff; border-radius: 12px; border: 1px solid #e5e7eb; box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1); overflow: hidden; }
        .header { background: #7f1d1d; color: white; padding: 30px; text-align: center; }
        .header h1 { margin: 0; font-size: 24px; font-weight: 800; text-transform: uppercase; letter-spacing: 1px; }
        .body { padding: 40px; }
        .status-banner { background: #fef2f2; border: 1px solid #fee2e2; border-radius: 8px; padding: 15px; margin-bottom: 30px; text-align: center; }
        .status-text { color: #991b1b; font-weight: 700; font-size: 18px; margin: 0; }
        
        .info-grid { display: grid; grid-template-columns: 1fr 1fr; gap: 20px; margin-bottom: 30px; }
        .info-card { background: #f9fafb; padding: 15px; border-radius: 8px; border: 1px solid #f3f4f6; }
        .info-label { display: block; font-size: 11px; font-weight: 700; color: #6b7280; text-transform: uppercase; margin-bottom: 5px; }
        .info-value { font-size: 16px; font-weight: 600; color: #111827; }
        
        .severity-badge { display: inline-block; padding: 4px 12px; rounded: 100px; font-size: 14px; font-weight: 700; border-radius: 20px; }
        .severity-critical { background: #fee2e2; color: #991b1b; border: 1px solid #fecaca; }
        .severity-high { background: #ffedd5; color: #9a3412; border: 1px solid #fed7aa; }
        .severity-medium { background: #dbeafe; color: #1e40af; border: 1px solid #bfdbfe; }
        .severity-low { background: #dcfce7; color: #166534; border: 1px solid #bbf7d0; }

        .btn { display: block; text-align: center; background: #111827; color: #ffffff !important; padding: 16px; border-radius: 8px; text-decoration: none; font-weight: 700; margin-top: 30px; font-size: 16px; letter-spacing: 0.5px; }
        .footer { padding: 25px; background: #f9fafb; border-top: 1px solid #e5e7eb; font-size: 12px; color: #6b7280; text-align: center; line-height: 1.6; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>SLA Violation Warning</h1>
        </div>
        <div class="body">
            <div class="status-banner">
                <p class="status-text">CRITICAL: SLA Breach by {{hours_overdue}} Hours</p>
                <p style="margin: 5px 0 0 0; color: #b91c1c; font-size: 14px;">Immediate action required for compliance.</p>
            </div>

            <div class="info-grid">
                <div class="info-card">
                    <span class="info-label">CVE Identifier</span>
                    <span class="info-value" style="color: #7f1d1d;">{{cve_id}}</span>
                </div>
                <div class="info-card">
                    <span class="info-label">SLA Severity</span>
                    <span class="severity-badge severity-{{severity}}">{{severity_upper}}</span>
                </div>
                <div class="info-card" style="grid-column: span 2;">
                    <span class="info-label">Affected Host / System</span>
                    <span class="info-value">{{asset_hostname}}</span>
                </div>
                <div class="info-card">
                    <span class="info-label">CVSS Score</span>
                    <span class="info-value text-red-600">{{cvss_score}}</span>
                </div>
                <div class="info-card">
                    <span class="info-label">Assigned To</span>
                    <span class="info-value">{{assigned_user}}</span>
                </div>
                <div class="info-card" style="grid-column: span 2;">
                    <span class="info-label">Resource / Package</span>
                    <span class="info-value">{{package_name}}</span>
                </div>
            </div>

            <p style="color: #4b5563; font-size: 14px; line-height: 1.6; background: #fffbeb; border-left: 4px solid #f59e0b; padding: 12px; border-radius: 4px;">
                <strong>Summary:</strong> {{title}}
            </p>
            
            <p style="color: #4b5563; font-size: 14px; line-height: 1.6; margin-top: 20px;">
                This vulnerability was detected at {{detected_at}} and has exceeded its mandatory remediation window. 
                Please remediate this exposure immediately or provide a valid deferral reason in the management console.
            </p>

            <a href="{{dashboard_url}}" class="btn">REMEDIATE NOW</a>
        </div>
        <div class="footer">
            <strong>TrueVuln Security Operations Center</strong><br>
            Automated compliance monitoring system. Do not reply to this email.<br>
            <span style="font-size: 10px; opacity: 0.7;">Sent to: {{recipient_name}} ({{recipient_email}})</span>
        </div>
    </div>
</body>
</html>"""

DEFAULT_SLA_BREACH_SUBJECT = "SLA Breach: {{cve_id}} on {{asset_hostname}} ({{severity_upper}})"

DEFAULT_NEW_VULN_TEMPLATE = """<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; margin: 0; padding: 20px; background: #fdfbf7; color: #333; }
        .container { max-width: 600px; margin: 0 auto; background: #fff; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.05); overflow: hidden; }
        .header { background: #d9480f; color: white; padding: 25px; text-align: center; }
        .header h1 { margin: 0; font-size: 24px; font-weight: 600; letter-spacing: 0.5px; }
        .body { padding: 30px; }
        .alert-badge { display: inline-block; background: #fff5f5; color: #c92a2a; border: 1px solid #ffc9c9; padding: 6px 12px; border-radius: 20px; font-weight: bold; font-size: 14px; margin-bottom: 20px; }
        .detail-grid { display: grid; grid-template-columns: 1fr 1fr; gap: 15px; margin-bottom: 20px; }
        .detail-item { background: #f8f9fa; padding: 15px; border-radius: 6px; }
        .detail-label { display: block; font-size: 11px; text-transform: uppercase; letter-spacing: 1px; color: #868e96; margin-bottom: 5px; }
        .detail-value { font-weight: 600; font-size: 15px; color: #212529; }
        .description-box { background: #fff; border: 1px solid #e9ecef; padding: 15px; border-radius: 6px; margin-bottom: 20px; line-height: 1.5; font-size: 14px; color: #495057; }
        .action-btn { display: block; width: 100%; text-align: center; background: #339af0; color: white; padding: 15px 0; border-radius: 6px; text-decoration: none; font-weight: bold; font-size: 16px; transition: background 0.2s; }
        .action-btn:hover { background: #228be6; }
        .footer { padding: 20px; background: #f8f9fa; text-align: center; font-size: 12px; color: #adb5bd; border-top: 1px solid #e9ecef; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>New Vulnerability Detected</h1>
        </div>
        <div class="body">
            <div style="text-align: center;">
                <span class="alert-badge">Severity: {{severity_upper}} ({{cvss_score}})</span>
            </div>
            
            <p style="font-size: 16px; margin-bottom: 25px; text-align: center;">
                A new <strong>{{severity_upper}}</strong> vulnerability has been detected on <strong>{{asset_hostname}}</strong>.
            </p>

            <div class="detail-grid">
                <div class="detail-item">
                    <span class="detail-label">CVE ID</span>
                    <span class="detail-value" style="color: #d9480f;">{{cve_id}}</span>
                </div>
                <div class="detail-item">
                    <span class="detail-label">Package</span>
                    <span class="detail-value">{{package_name}}</span>
                </div>
                <div class="detail-item">
                    <span class="detail-label">CPR (priority)</span>
                    <span class="detail-value">{{cpr_score}}</span>
                </div>
                <div class="detail-item">
                    <span class="detail-label">Affected systems</span>
                    <span class="detail-value">{{affected_assets_count}}</span>
                </div>
                <div class="detail-item">
                    <span class="detail-label">Detected At</span>
                    <span class="detail-value">{{detected_at}}</span>
                </div>
                <div class="detail-item">
                    <span class="detail-label">Affected Host</span>
                    <span class="detail-value">{{asset_hostname}}</span>
                </div>
            </div>

            <div class="detail-label">Description</div>
            <div class="description-box">
                {{description}}
            </div>

            <a href="{{cve_on_asset_link}}" class="action-btn">View this CVE on {{asset_hostname}}</a>
            <a href="{{asset_link}}" class="action-btn" style="background:#495057;margin-top:10px;">View all findings on this asset</a>
        </div>
        <div class="footer">
            Generated by TrueVuln Dashboard • {{detected_at}}
        </div>
    </div>
</body>
</html>"""

DEFAULT_NEW_VULN_SUBJECT = "ALERT: New {{severity_upper}} Vulnerability ({{cve_id}}) on {{asset_hostname}}"


# ---------------------------------------------------------------
# Digest variant — one mail per recipient summarising N new CVEs
# instead of one mail per CVE. Selected by setting `notification_mode`.
# ---------------------------------------------------------------
DEFAULT_DIGEST_SUBJECT = "[TRUEVULN] {{total}} new vulnerabilities detected"
DEFAULT_DIGEST_TEMPLATE = """<!DOCTYPE html>
<html><head><meta charset="utf-8"><style>
body{font-family:Arial,sans-serif;color:#1f2937;max-width:760px;margin:24px auto;padding:0 16px}
.h{background:#1f2937;color:#fff;padding:14px 18px;border-radius:6px 6px 0 0}
.h h1{margin:0;font-size:18px}
.h .sub{font-size:12px;opacity:.85;margin-top:4px}
.box{background:#fff;border:1px solid #e5e7eb;border-top:none;padding:18px;border-radius:0 0 6px 6px}
.cnt{display:flex;gap:12px;flex-wrap:wrap;margin:0 0 14px 0}
.cnt div{flex:1;min-width:90px;text-align:center;padding:8px;border-radius:4px;font-size:12px;font-weight:bold}
.crit{background:#fee2e2;color:#991b1b}.high{background:#ffedd5;color:#9a3412}
.med{background:#dbeafe;color:#1e40af}.low{background:#dcfce7;color:#166534}
table{width:100%;border-collapse:collapse;margin-top:8px;font-size:13px}
th{text-align:left;background:#f3f4f6;padding:8px 6px;border-bottom:1px solid #e5e7eb;font-size:11px;text-transform:uppercase;color:#6b7280}
td{padding:7px 6px;border-bottom:1px solid #f3f4f6}
.sev{display:inline-block;padding:1px 6px;border-radius:3px;font-size:10px;font-weight:bold;text-transform:uppercase}
.sev-critical{background:#fee2e2;color:#991b1b}.sev-high{background:#ffedd5;color:#9a3412}
.sev-medium{background:#dbeafe;color:#1e40af}.sev-low{background:#dcfce7;color:#166534}
.btn{display:inline-block;margin-top:14px;padding:9px 16px;background:#2563eb;color:#fff;text-decoration:none;border-radius:4px;font-size:13px}
.foot{font-size:11px;color:#6b7280;margin-top:14px;padding-top:10px;border-top:1px solid #e5e7eb}
</style></head><body>
<div class="h">
  <h1>{{total}} new vulnerabilities detected</h1>
  <div class="sub">Sync at {{detected_at}} — recipient: {{recipient_name}}</div>
</div>
<div class="box">
  <div class="cnt">
    <div class="crit">CRITICAL<br>{{count_critical}}</div>
    <div class="high">HIGH<br>{{count_high}}</div>
    <div class="med">MEDIUM<br>{{count_medium}}</div>
    <div class="low">LOW<br>{{count_low}}</div>
  </div>
  <table>
    <thead><tr><th>CVE</th><th>Severity</th><th>CVSS</th><th>CPR</th><th>Systems</th><th>Package</th></tr></thead>
    <tbody>{{rows}}</tbody>
  </table>
  <a class="btn" href="{{dashboard_url}}">Open in dashboard</a>
  <div class="foot">
    You receive this because the affected asset or vulnerability is assigned to you or one of your groups.
    Manage assignments and suppression in the TrueVuln UI.
  </div>
</div></body></html>"""


def aggregate_by_cve(items: list) -> list:
    """Collapse per-(CVE, asset) items to ONE entry per CVE. The representative
    is the highest-CPR occurrence; `systems` is how many distinct assets in this
    batch carry the CVE. Sorted by CPR descending (unscored last)."""
    groups: dict[str, dict] = {}
    for it in items:
        cve = str(it.get("cve_id") or "")
        g = groups.get(cve)
        if g is None:
            g = groups[cve] = {"rep": it, "assets": set()}
        if it.get("asset_id") is not None:
            g["assets"].add(it["asset_id"])
        if (it.get("cpr_score") or -1) > (g["rep"].get("cpr_score") or -1):
            g["rep"] = it
    out = []
    for cve, g in groups.items():
        rep = dict(g["rep"])
        rep["systems"] = len(g["assets"]) or 1
        out.append(rep)
    out.sort(key=lambda it: (it.get("cpr_score") is None, -(it.get("cpr_score") or 0.0)))
    return out


def render_digest_rows(items: list, base_url: str = "") -> str:
    """Render the digest table — ONE row per CVE, not per (CVE, asset). A CVE on
    200 hosts is one line: the CVE links to the filtered vulnerability view
    (?cve_id=…) that lists every affected asset, and #Systems says how many.
    Columns: CVE | Sev | CVSS | CPR | #Systems | Package, CPR-descending. All
    dynamic values HTML-escaped against compromised scanner data."""
    rows = []
    for it in aggregate_by_cve(items):
        raw_sev = (it.get("severity") or "none").lower()
        sev = raw_sev if raw_sev in {"critical", "high", "medium", "low", "none"} else "none"
        cvss = it.get("cvss_score")
        cvss_str = html.escape(str(cvss)) if cvss is not None else "-"
        cpr = it.get("cpr_score")
        cpr_str = html.escape(f"{cpr:.1f}") if isinstance(cpr, (int, float)) else "-"
        systems = it.get("systems") or 1
        cve = str(it.get("cve_id", ""))
        cve_esc = html.escape(cve)
        if base_url and cve:
            link = f"{base_url}?cve_id={quote(cve)}"  # no asset_id → shows ALL affected assets
            cve_cell = f"<a href='{html.escape(link)}'>{cve_esc}</a>"
        else:
            cve_cell = f"<strong>{cve_esc}</strong>"
        rows.append(
            f"<tr>"
            f"<td>{cve_cell}</td>"
            f"<td><span class='sev sev-{sev}'>{sev.upper()}</span></td>"
            f"<td>{cvss_str}</td>"
            f"<td>{cpr_str}</td>"
            f"<td>{html.escape(str(systems))}</td>"
            f"<td>{html.escape(str(it.get('package_name') or '')[:60])}</td>"
            f"</tr>"
        )
    return "".join(rows)


DEFAULT_SLA_DIGEST_SUBJECT = "[TRUEVULN] {{total}} SLA-breached vulnerabilities require action"
DEFAULT_SLA_DIGEST_TEMPLATE = """<!DOCTYPE html>
<html><head><meta charset="utf-8"><style>
body{font-family:Arial,sans-serif;color:#1f2937;max-width:780px;margin:24px auto;padding:0 16px}
.h{background:#991b1b;color:#fff;padding:14px 18px;border-radius:6px 6px 0 0}
.h h1{margin:0;font-size:18px}
.h .sub{font-size:12px;opacity:.85;margin-top:4px}
.box{background:#fff;border:1px solid #e5e7eb;border-top:none;padding:18px;border-radius:0 0 6px 6px}
.warn{background:#fef2f2;border-left:4px solid #dc2626;color:#7f1d1d;padding:10px 12px;margin-bottom:12px;font-size:13px;border-radius:4px}
.cnt{display:flex;gap:12px;flex-wrap:wrap;margin:0 0 14px 0}
.cnt div{flex:1;min-width:90px;text-align:center;padding:8px;border-radius:4px;font-size:12px;font-weight:bold}
.crit{background:#fee2e2;color:#991b1b}.high{background:#ffedd5;color:#9a3412}
.med{background:#dbeafe;color:#1e40af}.low{background:#dcfce7;color:#166534}
table{width:100%;border-collapse:collapse;margin-top:8px;font-size:13px}
th{text-align:left;background:#f3f4f6;padding:8px 6px;border-bottom:1px solid #e5e7eb;font-size:11px;text-transform:uppercase;color:#6b7280}
td{padding:7px 6px;border-bottom:1px solid #f3f4f6;vertical-align:top}
.sev{display:inline-block;padding:1px 6px;border-radius:3px;font-size:10px;font-weight:bold;text-transform:uppercase}
.sev-critical{background:#fee2e2;color:#991b1b}.sev-high{background:#ffedd5;color:#9a3412}
.sev-medium{background:#dbeafe;color:#1e40af}.sev-low{background:#dcfce7;color:#166534}
.ovd{color:#991b1b;font-weight:bold}
.btn{display:inline-block;margin-top:14px;padding:9px 16px;background:#dc2626;color:#fff;text-decoration:none;border-radius:4px;font-size:13px}
.foot{font-size:11px;color:#6b7280;margin-top:14px;padding-top:10px;border-top:1px solid #e5e7eb}
</style></head><body>
<div class="h">
  <h1>{{total}} SLA-breached vulnerabilities require action</h1>
  <div class="sub">Check at {{checked_at}} — recipient: {{recipient_name}}</div>
</div>
<div class="box">
  <div class="warn"><strong>Action required.</strong> These findings exceeded their remediation SLA. Please patch or formally accept the risk.</div>
  <div class="cnt">
    <div class="crit">CRITICAL<br>{{count_critical}}</div>
    <div class="high">HIGH<br>{{count_high}}</div>
    <div class="med">MEDIUM<br>{{count_medium}}</div>
    <div class="low">LOW<br>{{count_low}}</div>
  </div>
  <table>
    <thead><tr><th>CVE</th><th>Severity</th><th>Host</th><th>Detected</th><th>Overdue</th></tr></thead>
    <tbody>{{rows}}</tbody>
  </table>
  <a class="btn" href="{{dashboard_url}}">Open in dashboard</a>
  <div class="foot">
    You receive this because these vulnerabilities or their affected assets are assigned to you or one of your groups.
    To stop receiving alerts for a specific finding, suppress notifications via the bell icon in the dashboard or
    mark the vulnerability as a false positive.
  </div>
</div></body></html>"""


def render_sla_digest_rows(items: list) -> str:
    """Render <tr> rows for the SLA digest table. Items: list of dicts with
    cve_id, severity, asset_hostname, detected_at, hours_overdue. All
    dynamic values HTML-escaped to prevent injection."""
    rows = []
    for it in items:
        raw_sev = (it.get("severity") or "none").lower()
        sev = raw_sev if raw_sev in {"critical", "high", "medium", "low", "none"} else "none"
        hours = int(it.get("hours_overdue") or 0)
        if hours >= 48:
            overdue_str = f"{hours // 24}d {hours % 24}h"
        else:
            overdue_str = f"{hours}h"
        rows.append(
            f"<tr>"
            f"<td><strong>{html.escape(str(it.get('cve_id', '')))}</strong></td>"
            f"<td><span class='sev sev-{sev}'>{sev.upper()}</span></td>"
            f"<td>{html.escape(str(it.get('asset_hostname', '')))}</td>"
            f"<td>{html.escape(str(it.get('detected_at', '')))}</td>"
            f"<td class='ovd'>{overdue_str}</td>"
            f"</tr>"
        )
    return "".join(rows)


def send_sla_breach_digest(
    db: Session,
    to_email: str,
    recipient_name: str,
    items: list,
    checked_at: str,
    dashboard_url: str,
) -> tuple[bool, str]:
    """One SLA-breach digest mail aggregating all overdue findings for a recipient.
    Replaces per-vuln SLA-breach emails when notification_mode='digest'."""
    if not items:
        return False, "no items"

    counts = {"critical": 0, "high": 0, "medium": 0, "low": 0, "none": 0}
    for it in items:
        sev = (it.get("severity") or "none").lower()
        if sev in counts:
            counts[sev] += 1

    variables = {
        "total": str(len(items)),
        "count_critical": str(counts["critical"]),
        "count_high": str(counts["high"]),
        "count_medium": str(counts["medium"]),
        "count_low": str(counts["low"]),
        "checked_at": checked_at,
        "recipient_name": recipient_name,
        "recipient_email": to_email,
        "dashboard_url": dashboard_url,
        "rows": render_sla_digest_rows(items),
    }

    subject_template, body_template = get_email_template(db, "email_template_sla_breach_digest")
    subject = render_template(subject_template, variables)
    body = render_template(body_template, variables)
    return send_email(db, to_email, subject, body)


def send_new_vulnerability_digest(
    db: Session,
    to_email: str,
    recipient_name: str,
    items: list,
    detected_at: str,
    dashboard_url: str,
) -> tuple[bool, str]:
    """
    Send one digest email aggregating up to N new vulnerabilities for a recipient.
    Used by the Wazuh sync when notification_mode = 'digest' (the default).
    """
    if not items:
        return False, "no items"

    # Counts are per DISTINCT CVE, matching the one-row-per-CVE table — a CVE on
    # 200 hosts counts once, not 200×. `total` = distinct CVEs; the top-level
    # affected-systems tile stays distinct assets across the whole digest.
    counts = {"critical": 0, "high": 0, "medium": 0, "low": 0, "none": 0}
    _assets: set = set()
    for it in items:
        if it.get("asset_id") is not None:
            _assets.add(it["asset_id"])
    unique = aggregate_by_cve(items)
    for it in unique:
        sev = (it.get("severity") or "none").lower()
        if sev in counts:
            counts[sev] += 1

    variables = {
        "total": str(len(unique)),
        # Distinct assets across this digest (top-level "affected systems" tile).
        # Per-CVE spread is the #Systems column inside {{rows}}.
        "affected_assets_count": str(len(_assets)),
        "count_critical": str(counts["critical"]),
        "count_high": str(counts["high"]),
        "count_medium": str(counts["medium"]),
        "count_low": str(counts["low"]),
        "detected_at": detected_at,
        "recipient_name": recipient_name,
        "recipient_email": to_email,
        "dashboard_url": dashboard_url,
        "rows": render_digest_rows(items, base_url=dashboard_url),
    }

    subject_template, body_template = get_email_template(db, "email_template_new_vuln_digest")
    subject = render_template(subject_template, variables)
    body = render_template(body_template, variables)
    return send_email(db, to_email, subject, body)


def get_smtp_config(db: Session) -> Optional[dict]:
    from app.auth.setting_crypto import read_setting_value
    raw = read_setting_value(db, "smtp_config")
    if not raw:
        return None
    try:
        return json.loads(raw)
    except (json.JSONDecodeError, TypeError):
        return None


def get_email_template(db: Session, template_key: str = "email_template_sla_breach") -> tuple[str, str]:
    setting = db.query(Setting).filter(Setting.key == template_key).first()
    
    default_subject = DEFAULT_SLA_BREACH_SUBJECT
    default_body = DEFAULT_SLA_BREACH_TEMPLATE
    
    if template_key == "email_template_new_vuln":
        default_subject = DEFAULT_NEW_VULN_SUBJECT
        default_body = DEFAULT_NEW_VULN_TEMPLATE
    elif template_key == "email_template_new_vuln_digest":
        default_subject = DEFAULT_DIGEST_SUBJECT
        default_body = DEFAULT_DIGEST_TEMPLATE
    elif template_key == "email_template_sla_breach_digest":
        default_subject = DEFAULT_SLA_DIGEST_SUBJECT
        default_body = DEFAULT_SLA_DIGEST_TEMPLATE

    if setting and setting.value:
        try:
            data = json.loads(setting.value)
            return data.get("subject", default_subject), data.get("body", default_body)
        except (json.JSONDecodeError, TypeError):
            pass
    return default_subject, default_body


# Keys whose values are already trusted HTML (pre-rendered with escaping).
# Everything else is HTML-escaped at substitution time.
_SAFE_HTML_KEYS = {"rows"}


def render_template(template: str, variables: dict) -> str:
    def replace_var(match):
        key = match.group(1)
        if key not in variables:
            return f"{{{{{key}}}}}"
        value = str(variables[key])
        if key in _SAFE_HTML_KEYS:
            return value
        return html.escape(value)
    return re.sub(r'\{\{(\w+)\}\}', replace_var, template)


def send_email(db: Session, to_email: str, subject: str, html_body: str, config_override: Optional[dict] = None) -> tuple[bool, str]:
    if config_override:
        config = config_override
    else:
        config = get_smtp_config(db)
        
    if not config:
        return False, "SMTP not configured"

    host = config.get("host", "")
    port = int(config.get("port", 587))
    username = config.get("username", "")
    password = config.get("password", "")
    from_address = config.get("from_address", username)
    use_tls = config.get("use_tls", True)

    if not host:
        return False, "SMTP host not configured"

    try:
        msg = MIMEMultipart("alternative")
        msg["From"] = from_address
        msg["To"] = to_email
        msg["Subject"] = subject
        msg.attach(MIMEText(html_body, "html"))

        if use_tls:
            server = smtplib.SMTP(host, port, timeout=10)
            server.starttls()
        else:
            server = smtplib.SMTP(host, port, timeout=10)

        if username and password:
            server.login(username, password)

        server.sendmail(from_address, [to_email], msg.as_string())
        server.quit()
        logger.info(f"Email sent to {to_email}: {subject}")
        return True, "OK"

    except smtplib.SMTPAuthenticationError as e:
        msg = f"SMTP authentication failed: {e}"
        logger.error(msg)
        return False, msg
    except smtplib.SMTPException as e:
        msg = f"SMTP error: {e}"
        logger.error(msg)
        return False, msg
    except Exception as e:
        msg = f"Email send failed: {e}"
        logger.error(msg)
        return False, msg


def send_sla_breach_notification(db: Session, to_email: str, variables: dict) -> tuple[bool, str]:
    subject_template, body_template = get_email_template(db, "email_template_sla_breach")
    subject = render_template(subject_template, variables)
    body = render_template(body_template, variables)
    return send_email(db, to_email, subject, body)


def send_new_vulnerability_notification(db: Session, to_email: str, variables: dict) -> tuple[bool, str]:
    subject_template, body_template = get_email_template(db, "email_template_new_vuln")
    subject = render_template(subject_template, variables)
    body = render_template(body_template, variables)
    return send_email(db, to_email, subject, body)


# Severity rank: higher = more severe.
_SEVERITY_RANK = {
    "none": 0,
    "low": 1,
    "medium": 2,
    "high": 3,
    "critical": 4,
}


def get_notification_mode(db: Session) -> str:
    """
    `notification_mode` setting: 'digest' (default) or 'single'.
    digest = one summary email per recipient per sync run.
    single = one email per CVE per recipient (legacy, SMTP-spammy).
    """
    from app.models.setting import Setting
    try:
        s = db.query(Setting).filter(Setting.key == "notification_mode").first()
        if s and s.value:
            val = s.value.strip().lower()
            if val in ("digest", "single"):
                return val
    except Exception:
        pass
    return "digest"


def _get_setting_str(db: Session, key: str) -> Optional[str]:
    from app.models.setting import Setting
    try:
        s = db.query(Setting).filter(Setting.key == key).first()
        return s.value.strip() if s and s.value else None
    except Exception:
        return None


def is_lifecycle_finding(cve_id: Optional[str]) -> bool:
    """True for pseudo-findings that aren't real CVEs — endoflife.date rows
    (EOL-*), Android patch-level staleness (ANDROID-PATCH-*), Nessus plugin
    rows (NESSUS-PLUGIN-*), etc. Deliberately 'anything not CVE-*' so a new
    pseudo prefix is covered without touching this."""
    return not (cve_id or "").upper().startswith("CVE-")


def get_notification_lifecycle_mode(db: Session) -> str:
    """`notification_lifecycle_mode`: 'exclude' (default) or 'include'.
    exclude = lifecycle/EOL pseudo-findings never trigger the CVE mails, so
              vulnerability reporting stays unmixed with lifecycle hygiene.
    include = legacy behaviour, everything in one mail."""
    val = (_get_setting_str(db, "notification_lifecycle_mode") or "").lower()
    return val if val in ("exclude", "include") else "exclude"


def get_notification_schedule(db: Session) -> str:
    """`notification_schedule`: 'per_sync' (default) or 'nightly'.
    per_sync = notify at the end of each sync run (immediate).
    nightly  = per-sync sends are suppressed; one roundup job at night sends
               ALL of the day's new CVEs in a single aggregated mail per
               recipient (fewer mails → friendlier to provider anti-spam)."""
    val = (_get_setting_str(db, "notification_schedule") or "").lower()
    return val if val in ("per_sync", "nightly") else "per_sync"


def get_notification_nightly_hour(db: Session) -> int:
    """Hour (0-23, server local time) the nightly roundup fires. Default 6."""
    try:
        h = int(_get_setting_str(db, "notification_nightly_hour") or "6")
        return h if 0 <= h <= 23 else 6
    except (TypeError, ValueError):
        return 6


def get_email_rate_limit(db: Session) -> tuple[float, int]:
    """(delay_seconds_between_mails, max_mails_per_run). 0 = unlimited/no delay.
    Throttles the send loop so a big batch doesn't trip provider rate limits."""
    try:
        delay = float(_get_setting_str(db, "email_rate_delay_seconds") or "0")
    except (TypeError, ValueError):
        delay = 0.0
    try:
        cap = int(_get_setting_str(db, "email_max_per_run") or "0")
    except (TypeError, ValueError):
        cap = 0
    return max(0.0, delay), max(0, cap)


def get_default_recipients(db: Session) -> list[tuple]:
    """Fallback recipients for findings with NO assignee anywhere in the
    cascade. The `notification_default_recipients` setting (comma/semicolon/
    space-separated emails) wins; if it's unset/empty, every active admin
    user — so 'the admin gets everything' works out of the box, which is
    what operators expect after configuring SMTP + an admin email but never
    assigning the thousands of scanner findings to anyone.

    Returns (user_id_or_None, email, display_name).
    """
    from app.models.setting import Setting
    from app.models.user import User, UserRole

    out: list[tuple] = []
    seen: set[str] = set()
    raw = None
    try:
        s = db.query(Setting).filter(Setting.key == "notification_default_recipients").first()
        raw = s.value if s else None
    except Exception:
        raw = None

    if raw and raw.strip():
        for em in (e.strip() for e in re.split(r"[,;\s]+", raw) if e.strip()):
            if em in seen:
                continue
            u = db.query(User).filter(User.email == em).first()
            out.append(((u.id if u else None), em, (u.username if u else em)))
            seen.add(em)
        return out

    # No configured default → all active admins.
    try:
        admins = db.query(User).filter(
            User.role == UserRole.ADMIN, User.is_active.is_(True)).all()
    except Exception:
        admins = []
    for u in admins:
        if u.email and u.email not in seen:
            out.append((u.id, u.email, u.username))
            seen.add(u.email)
    return out


def _resolve_recipients_for_vuln(db: Session, vuln) -> list[tuple[int, str, str]]:
    """
    Returns list of (user_id, email, username) for a vulnerability following
    the cascade: vuln.assigned_user > vuln.assigned_group > asset.assigned_user
    > asset.assigned_group. When the cascade is empty, falls back to the
    configured default recipients / active admins (get_default_recipients).
    """
    from app.models.group import Group
    from app.models.user import User

    recipients: list[tuple[int, str, str]] = []
    seen_emails: set[str] = set()

    def _push(u):
        if u and u.email and u.email not in seen_emails:
            recipients.append((u.id, u.email, u.username))
            seen_emails.add(u.email)

    if vuln.assigned_user_id:
        _push(db.query(User).filter(User.id == vuln.assigned_user_id).first())
    elif vuln.assigned_group_id:
        g = db.query(Group).filter(Group.id == vuln.assigned_group_id).first()
        if g:
            for u in g.users:
                _push(u)
    elif vuln.asset and vuln.asset.assigned_user_id:
        _push(db.query(User).filter(User.id == vuln.asset.assigned_user_id).first())
    elif vuln.asset and getattr(vuln.asset, "groups", None):
        for g in vuln.asset.groups:
            for u in g.users:
                _push(u)

    if not recipients:
        for uid, email, uname in get_default_recipients(db):
            if email and email not in seen_emails:
                recipients.append((uid, email, uname))
                seen_emails.add(email)
    return recipients


def _affected_counts(db: Session, cve_ids: set) -> dict:
    """{cve_id: number of distinct assets with this CVE in an active state}.
    One grouped query for the whole batch."""
    if not cve_ids:
        return {}
    from sqlalchemy import func
    from app.models.vulnerability import Vulnerability, VulnerabilityStatus
    active = (VulnerabilityStatus.open, VulnerabilityStatus.pending_verification,
              VulnerabilityStatus.patch_failed)
    rows = (
        db.query(Vulnerability.cve_id, func.count(func.distinct(Vulnerability.asset_id)))
        .filter(Vulnerability.cve_id.in_(list(cve_ids)), Vulnerability.status.in_(active))
        .group_by(Vulnerability.cve_id)
        .all()
    )
    return {cve: cnt for cve, cnt in rows}


def dispatch_new_vuln_notifications(db: Session, new_vulns: list, respect_schedule: bool = True) -> dict:
    """
    Shared entry point for every sync (Wazuh/Nessus/app-scan/Defender). Groups
    new vulns by recipient, applies the severity threshold, and dispatches either:
      - one digest email per recipient (mode='digest', default), or
      - one email per CVE per recipient (mode='single', legacy).

    respect_schedule: when True (sync callers) and notification_schedule is
    'nightly', sending is skipped here — the nightly roundup job sends instead.
    The nightly job calls with respect_schedule=False to actually send.

    Returns a stats dict for logging.
    """
    from app.models.notification_log import NotificationLog, NotificationType, NotificationStatus

    stats = {"emails_sent": 0, "emails_failed": 0, "recipients": 0, "vulns_considered": len(new_vulns)}
    if not new_vulns:
        return stats

    # Keep CVE reporting unmixed with lifecycle/EOL hygiene findings unless the
    # operator opts back in. Applies to both delivery modes and both schedules.
    if get_notification_lifecycle_mode(db) == "exclude":
        kept = [v for v in new_vulns if not is_lifecycle_finding(v.cve_id)]
        skipped = len(new_vulns) - len(kept)
        if skipped:
            stats["lifecycle_skipped"] = skipped
        new_vulns = kept
        if not new_vulns:
            return stats

    if respect_schedule and get_notification_schedule(db) == "nightly":
        stats["deferred_to_nightly"] = len(new_vulns)
        return stats

    smtp = get_smtp_config(db)
    if not smtp:
        logger.info("SMTP not configured — skipping new-vuln notifications")
        return stats

    rate_delay, rate_cap = get_email_rate_limit(db)
    mode = get_notification_mode(db)
    dashboard_url = os.getenv("DASHBOARD_URL", "http://localhost:3000").rstrip("/") + "/vulnerabilities"
    detected_at_str = datetime.now().strftime("%Y-%m-%d %H:%M UTC")

    # How many distinct assets each CVE affects (whole inventory, not just this
    # batch) — lets the template convey blast radius / spread.
    affected = _affected_counts(db, {v.cve_id for v in new_vulns if v.cve_id})

    # Bucket vulns per recipient email (only those above the severity threshold).
    buckets: dict[str, dict] = {}  # email -> {user_id, username, items: [vuln_summary]}
    notif_log_anchors: dict[str, list] = {}  # email -> list of (vuln_id, asset_id) for logging

    for vuln in new_vulns:
        if not should_notify_for_severity(db, vuln.severity):
            continue
        for r_uid, r_email, r_username in _resolve_recipients_for_vuln(db, vuln):
            b = buckets.setdefault(r_email, {"user_id": r_uid, "username": r_username, "items": []})
            b["items"].append({
                "cve_id": vuln.cve_id,
                "severity": vuln.severity.value if vuln.severity else "none",
                "cvss_score": vuln.cvss_score,
                "cpr_score": vuln.cpr_score,
                "affected_count": affected.get(vuln.cve_id, 1),
                "asset_hostname": vuln.asset.hostname if vuln.asset else "Unknown",
                "package_name": vuln.package_name,
                "vuln_id": vuln.id,
                "asset_id": vuln.asset_id,
            })
            notif_log_anchors.setdefault(r_email, []).append((vuln.id, vuln.asset_id))

    stats["recipients"] = len(buckets)

    import time as _time
    _sent_this_run = 0
    for email, bucket in buckets.items():
        # Rate-limit: cap per run + pace between mails (provider anti-spam).
        if rate_cap and _sent_this_run >= rate_cap:
            stats["rate_capped"] = stats.get("rate_capped", 0) + 1
            continue
        if rate_delay and _sent_this_run > 0:
            _time.sleep(rate_delay)
        _sent_this_run += 1
        items = bucket["items"]
        username = bucket["username"]
        user_id = bucket["user_id"]

        if mode == "digest":
            success, err = send_new_vulnerability_digest(
                db,
                to_email=email,
                recipient_name=username,
                items=items,
                detected_at=detected_at_str,
                dashboard_url=dashboard_url,
            )
            anchor_vuln_id = items[0]["vuln_id"]
            anchor_asset_id = items[0]["asset_id"]
            _distinct = len({it.get("cve_id") for it in items})
            db.add(NotificationLog(
                vulnerability_id=anchor_vuln_id,  # anchor — full list is in body
                asset_id=anchor_asset_id,
                user_id=user_id,
                notification_type=NotificationType.NEW_VULNERABILITY,
                sent_at=datetime.now(),
                subject=f"[TRUEVULN] {_distinct} new vulnerabilities detected",
                recipient_email=email,
                status=NotificationStatus.SENT if success else NotificationStatus.FAILED,
                message_body=f"Digest of {_distinct} distinct CVEs across {len(items)} findings",
                error_message=None if success else err,
            ))
            if success:
                stats["emails_sent"] += 1
                logger.info(f"Digest email sent to {email}: {_distinct} distinct CVEs ({len(items)} findings)")
            else:
                stats["emails_failed"] += 1
                logger.warning(f"Digest email FAILED to {email}: {err}")
        else:
            # Legacy single mode — one mail per CVE per recipient
            for item in items:
                _cve = item["cve_id"]
                _aid = item.get("asset_id")
                _cpr = item.get("cpr_score")
                variables = {
                    "cve_id": _cve,
                    "severity": item["severity"],
                    "severity_upper": item["severity"].upper(),
                    "cvss_score": str(item.get("cvss_score") or "N/A"),
                    "cpr_score": (f"{_cpr:.1f}" if isinstance(_cpr, (int, float)) else "N/A"),
                    "affected_assets_count": str(item.get("affected_count") or 1),
                    "asset_hostname": item["asset_hostname"],
                    "package_name": item.get("package_name") or "",
                    "title": _cve,
                    "detected_at": detected_at_str,
                    "dashboard_url": dashboard_url,
                    # Ready-made deep links (no manual ?cve_id= assembly needed):
                    "cve_link": f"{dashboard_url}?cve_id={quote(_cve)}",
                    "asset_link": (f"{dashboard_url}?asset_id={_aid}" if _aid else dashboard_url),
                    "cve_on_asset_link": (f"{dashboard_url}?asset_id={_aid}&cve_id={quote(_cve)}" if _aid else f"{dashboard_url}?cve_id={quote(_cve)}"),
                    "recipient_name": username,
                    "recipient_email": email,
                }
                success, err = send_new_vulnerability_notification(db, email, variables)
                db.add(NotificationLog(
                    vulnerability_id=item["vuln_id"],
                    asset_id=item["asset_id"],
                    user_id=user_id,
                    notification_type=NotificationType.NEW_VULNERABILITY,
                    sent_at=datetime.now(),
                    subject=f"[TRUEVULN] New {item['severity'].upper()} Vulnerability: {item['cve_id']}",
                    recipient_email=email,
                    status=NotificationStatus.SENT if success else NotificationStatus.FAILED,
                    message_body=f"New {item['severity']} vulnerability {item['cve_id']} on {item['asset_hostname']}",
                    error_message=None if success else err,
                ))
                if success:
                    stats["emails_sent"] += 1
                else:
                    stats["emails_failed"] += 1

    db.commit()
    return stats


def _set_setting_str(db: Session, key: str, value: str) -> None:
    from app.models.setting import Setting
    s = db.query(Setting).filter(Setting.key == key).first()
    if s:
        s.value = value
    else:
        db.add(Setting(key=key, value=value))
    db.commit()


def send_nightly_new_vuln_digest(db: Session) -> dict:
    """Nightly roundup: one aggregated mail per recipient with ALL new CVEs
    since the last run. Only active when notification_schedule == 'nightly'
    (per-sync sends are suppressed in that mode). Window is tracked in the
    `notification_nightly_last_run` setting so nothing is sent twice and
    nothing is missed between runs."""
    from datetime import timedelta
    from app.models.vulnerability import Vulnerability, VulnerabilityStatus

    if get_notification_schedule(db) != "nightly":
        return {"skipped": "notification_schedule != nightly"}

    last_raw = _get_setting_str(db, "notification_nightly_last_run")
    since = None
    if last_raw:
        try:
            since = datetime.fromisoformat(last_raw)
        except ValueError:
            since = None
    if since is None:
        since = datetime.now() - timedelta(hours=24)

    active = (VulnerabilityStatus.open, VulnerabilityStatus.pending_verification,
              VulnerabilityStatus.patch_failed)
    vulns = (
        db.query(Vulnerability)
        .filter(Vulnerability.status.in_(active),
                Vulnerability.detected_at >= since)
        .all()
    )
    stats = dispatch_new_vuln_notifications(db, vulns, respect_schedule=False)
    _set_setting_str(db, "notification_nightly_last_run", datetime.now().isoformat())
    logger.info("Nightly new-vuln digest: %s new since %s → %s", len(vulns), since, stats)
    return stats


def should_notify_for_severity(db: Session, severity) -> bool:
    """
    Return True if the configured notification threshold lets this severity
    trigger a 'new vulnerability' email.

    Threshold is stored in setting `notification_min_severity`
    (one of: critical, high, medium, low). Default: critical.
    A severity at or above the threshold triggers a notification.
    """
    from app.models.setting import Setting

    threshold = "critical"
    try:
        setting = db.query(Setting).filter(Setting.key == "notification_min_severity").first()
        if setting and setting.value:
            candidate = setting.value.strip().lower()
            if candidate in _SEVERITY_RANK:
                threshold = candidate
    except Exception as e:
        logger.warning(f"Failed to read notification_min_severity setting, defaulting to 'critical': {e}")

    severity_value = getattr(severity, "value", severity)
    if not isinstance(severity_value, str):
        return False
    return _SEVERITY_RANK.get(severity_value.lower(), -1) >= _SEVERITY_RANK[threshold]
