368 lines
12 KiB
Python
Executable File
368 lines
12 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""
|
|
ISP UPDATE CHECKER
|
|
Vérifie chaque semaine les changements de filtres ISP:
|
|
- Détecte les baisses soudaines d'inbox rate
|
|
- Identifie les méthodes qui ne marchent plus
|
|
- Suggère des adaptations
|
|
- Met à jour les configs gagnantes
|
|
"""
|
|
|
|
import psycopg2
|
|
import json
|
|
from datetime import datetime, timedelta
|
|
from collections import defaultdict
|
|
|
|
DB_CONFIG = {
|
|
'host': 'localhost',
|
|
'database': 'adx_system',
|
|
'user': 'admin',
|
|
'password': 'admin123'
|
|
}
|
|
|
|
# ISPs majeurs à surveiller
|
|
MAJOR_ISPS = ['gmail', 'hotmail', 'yahoo', 'aol', 'icloud', 'outlook',
|
|
'ziggo', 'kpn', 'orange', 'sfr', 'free', 'att', 'comcast']
|
|
|
|
# Thresholds pour détecter changements
|
|
CHANGE_THRESHOLDS = {
|
|
'inbox_drop': 15, # % drop = potential filter change
|
|
'spam_increase': 20, # % increase = definitely something changed
|
|
'volume_threshold': 50 # Min sends to consider
|
|
}
|
|
|
|
def get_db():
|
|
return psycopg2.connect(**DB_CONFIG)
|
|
|
|
def get_isp_performance_by_period(isp, days_ago_start, days_ago_end):
|
|
"""Get ISP performance for a specific period"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("""
|
|
SELECT
|
|
COUNT(*) as total,
|
|
SUM(CASE WHEN inbox_placement = 'inbox' THEN 1 ELSE 0 END) as inbox,
|
|
SUM(CASE WHEN inbox_placement = 'spam' THEN 1 ELSE 0 END) as spam
|
|
FROM admin.seed_tracking
|
|
WHERE seed_isp ILIKE %s
|
|
AND sent_at BETWEEN NOW() - INTERVAL '%s days' AND NOW() - INTERVAL '%s days'
|
|
""", (f'%{isp}%', days_ago_start, days_ago_end))
|
|
|
|
row = cur.fetchone()
|
|
conn.close()
|
|
|
|
if not row or row[0] == 0:
|
|
return None
|
|
|
|
return {
|
|
'total': row[0],
|
|
'inbox': row[1],
|
|
'spam': row[2],
|
|
'inbox_rate': (row[1] / row[0] * 100) if row[0] > 0 else 0,
|
|
'spam_rate': (row[2] / row[0] * 100) if row[0] > 0 else 0
|
|
}
|
|
|
|
def compare_periods(isp):
|
|
"""Compare this week vs last week"""
|
|
this_week = get_isp_performance_by_period(isp, 7, 0)
|
|
last_week = get_isp_performance_by_period(isp, 14, 7)
|
|
|
|
if not this_week or not last_week:
|
|
return None
|
|
|
|
if this_week['total'] < CHANGE_THRESHOLDS['volume_threshold']:
|
|
return None
|
|
|
|
inbox_change = this_week['inbox_rate'] - last_week['inbox_rate']
|
|
spam_change = this_week['spam_rate'] - last_week['spam_rate']
|
|
|
|
return {
|
|
'isp': isp,
|
|
'this_week': this_week,
|
|
'last_week': last_week,
|
|
'inbox_change': inbox_change,
|
|
'spam_change': spam_change,
|
|
'significant_change': abs(inbox_change) >= CHANGE_THRESHOLDS['inbox_drop'] or
|
|
spam_change >= CHANGE_THRESHOLDS['spam_increase']
|
|
}
|
|
|
|
def detect_method_failures(isp):
|
|
"""Detect which sending methods stopped working for this ISP"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
# Compare methods this week vs last week
|
|
cur.execute("""
|
|
WITH this_week AS (
|
|
SELECT
|
|
COALESCE(wt.sending_method, 'unknown') as method,
|
|
COUNT(*) as total,
|
|
SUM(CASE WHEN st.inbox_placement = 'inbox' THEN 1 ELSE 0 END) as inbox
|
|
FROM admin.seed_tracking st
|
|
LEFT JOIN admin.warmup_tracking wt ON wt.domain = SPLIT_PART(st.sender_email, '@', 2)
|
|
WHERE st.seed_isp ILIKE %s
|
|
AND st.sent_at > NOW() - INTERVAL '7 days'
|
|
GROUP BY COALESCE(wt.sending_method, 'unknown')
|
|
HAVING COUNT(*) >= 20
|
|
),
|
|
last_week AS (
|
|
SELECT
|
|
COALESCE(wt.sending_method, 'unknown') as method,
|
|
COUNT(*) as total,
|
|
SUM(CASE WHEN st.inbox_placement = 'inbox' THEN 1 ELSE 0 END) as inbox
|
|
FROM admin.seed_tracking st
|
|
LEFT JOIN admin.warmup_tracking wt ON wt.domain = SPLIT_PART(st.sender_email, '@', 2)
|
|
WHERE st.seed_isp ILIKE %s
|
|
AND st.sent_at BETWEEN NOW() - INTERVAL '14 days' AND NOW() - INTERVAL '7 days'
|
|
GROUP BY COALESCE(wt.sending_method, 'unknown')
|
|
HAVING COUNT(*) >= 20
|
|
)
|
|
SELECT
|
|
tw.method,
|
|
lw.inbox * 100.0 / NULLIF(lw.total, 0) as last_week_rate,
|
|
tw.inbox * 100.0 / NULLIF(tw.total, 0) as this_week_rate,
|
|
(tw.inbox * 100.0 / NULLIF(tw.total, 0)) - (lw.inbox * 100.0 / NULLIF(lw.total, 0)) as change
|
|
FROM this_week tw
|
|
JOIN last_week lw ON tw.method = lw.method
|
|
WHERE (lw.inbox * 100.0 / NULLIF(lw.total, 0)) - (tw.inbox * 100.0 / NULLIF(tw.total, 0)) > 15
|
|
""", (f'%{isp}%', f'%{isp}%'))
|
|
|
|
failures = []
|
|
for row in cur.fetchall():
|
|
failures.append({
|
|
'method': row[0],
|
|
'last_week_rate': row[1],
|
|
'this_week_rate': row[2],
|
|
'drop': -row[3]
|
|
})
|
|
|
|
conn.close()
|
|
return failures
|
|
|
|
def record_isp_update(isp, update_type, description, severity, affected_methods=None, detection_metrics=None):
|
|
"""Record ISP update in database"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("""
|
|
INSERT INTO admin.isp_updates
|
|
(isp_name, update_date, update_type, change_description, affected_methods,
|
|
severity, detected_by, detection_metrics)
|
|
VALUES (%s, CURRENT_DATE, %s, %s, %s, %s, 'auto_scan', %s)
|
|
RETURNING id
|
|
""", (
|
|
isp, update_type, description, affected_methods, severity,
|
|
json.dumps(detection_metrics) if detection_metrics else None
|
|
))
|
|
|
|
update_id = cur.fetchone()[0]
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
return update_id
|
|
|
|
def invalidate_affected_configs(isp, methods):
|
|
"""Mark configs as needing revalidation"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
# Mark headers configs
|
|
cur.execute("""
|
|
UPDATE admin.winning_headers SET
|
|
needs_revalidation = true,
|
|
status = CASE WHEN status = 'winner' THEN 'outdated' ELSE status END
|
|
WHERE %s = ANY(target_isps)
|
|
AND target_method = ANY(%s)
|
|
""", (isp, methods))
|
|
|
|
affected = cur.rowcount
|
|
|
|
# Mark DNS configs
|
|
cur.execute("""
|
|
UPDATE admin.winning_dns_configs SET
|
|
status = 'pending'
|
|
WHERE %s = ANY(target_isps)
|
|
""", (isp,))
|
|
|
|
affected += cur.rowcount
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
return affected
|
|
|
|
def run_weekly_check():
|
|
"""Run full weekly ISP check"""
|
|
print("=" * 70)
|
|
print(f"🔍 ISP WEEKLY UPDATE CHECK - {datetime.now().strftime('%Y-%m-%d')}")
|
|
print("=" * 70)
|
|
|
|
issues_found = []
|
|
|
|
for isp in MAJOR_ISPS:
|
|
print(f"\n📧 Checking {isp}...")
|
|
|
|
comparison = compare_periods(isp)
|
|
|
|
if not comparison:
|
|
print(f" ⚪ Insufficient data")
|
|
continue
|
|
|
|
if comparison['significant_change']:
|
|
if comparison['inbox_change'] < 0:
|
|
# Inbox dropped
|
|
severity = 'critical' if comparison['inbox_change'] < -25 else 'high'
|
|
update_type = 'filter_change'
|
|
|
|
print(f" 🔴 INBOX DROP: {comparison['inbox_change']:.1f}% (was {comparison['last_week']['inbox_rate']:.1f}%, now {comparison['this_week']['inbox_rate']:.1f}%)")
|
|
|
|
# Check which methods failed
|
|
failures = detect_method_failures(isp)
|
|
if failures:
|
|
affected_methods = [f['method'] for f in failures]
|
|
print(f" ⚠️ Failed methods: {affected_methods}")
|
|
else:
|
|
affected_methods = None
|
|
|
|
# Record update
|
|
update_id = record_isp_update(
|
|
isp, update_type,
|
|
f"Inbox rate dropped {abs(comparison['inbox_change']):.1f}% week-over-week",
|
|
severity, affected_methods,
|
|
{'this_week': comparison['this_week'], 'last_week': comparison['last_week']}
|
|
)
|
|
|
|
# Invalidate configs
|
|
if affected_methods:
|
|
invalidated = invalidate_affected_configs(isp, affected_methods)
|
|
print(f" ⚙️ Invalidated {invalidated} configs for revalidation")
|
|
|
|
issues_found.append({
|
|
'isp': isp,
|
|
'type': 'inbox_drop',
|
|
'severity': severity,
|
|
'change': comparison['inbox_change']
|
|
})
|
|
|
|
elif comparison['spam_change'] > CHANGE_THRESHOLDS['spam_increase']:
|
|
# Spam increased
|
|
print(f" 🟡 SPAM INCREASE: +{comparison['spam_change']:.1f}%")
|
|
|
|
record_isp_update(
|
|
isp, 'spf_strict',
|
|
f"Spam rate increased {comparison['spam_change']:.1f}%",
|
|
'medium', None,
|
|
{'this_week': comparison['this_week'], 'last_week': comparison['last_week']}
|
|
)
|
|
|
|
issues_found.append({
|
|
'isp': isp,
|
|
'type': 'spam_increase',
|
|
'severity': 'medium',
|
|
'change': comparison['spam_change']
|
|
})
|
|
else:
|
|
print(f" ✅ Stable (inbox: {comparison['this_week']['inbox_rate']:.1f}%)")
|
|
|
|
# Summary
|
|
print("\n" + "=" * 70)
|
|
print("📊 WEEKLY CHECK SUMMARY")
|
|
print("=" * 70)
|
|
|
|
if issues_found:
|
|
print(f"\n⚠️ {len(issues_found)} ISP changes detected:")
|
|
for issue in issues_found:
|
|
icon = "🔴" if issue['severity'] == 'critical' else "🟡"
|
|
print(f" {icon} {issue['isp']}: {issue['type']} ({issue['change']:+.1f}%)")
|
|
else:
|
|
print("\n✅ No significant changes detected")
|
|
|
|
return issues_found
|
|
|
|
def show_recent_updates():
|
|
"""Show recent ISP updates"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
print("=" * 70)
|
|
print("📋 RECENT ISP UPDATES (last 30 days)")
|
|
print("=" * 70)
|
|
|
|
cur.execute("""
|
|
SELECT isp_name, update_date, update_type, change_description, severity, affected_methods
|
|
FROM admin.isp_updates
|
|
WHERE update_date > CURRENT_DATE - INTERVAL '30 days'
|
|
ORDER BY update_date DESC
|
|
""")
|
|
|
|
for isp, date, utype, desc, severity, methods in cur.fetchall():
|
|
icon = {'critical': '🔴', 'high': '🟠', 'medium': '🟡', 'low': '🟢'}.get(severity, '⚪')
|
|
print(f"\n{icon} {isp} - {date}")
|
|
print(f" Type: {utype}")
|
|
print(f" {desc}")
|
|
if methods:
|
|
print(f" Affected: {methods}")
|
|
|
|
conn.close()
|
|
|
|
def schedule_revalidations():
|
|
"""Schedule revalidation for configs marked as needing it"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
# Get configs needing revalidation
|
|
cur.execute("""
|
|
SELECT id, config_name, target_isps
|
|
FROM admin.winning_headers
|
|
WHERE needs_revalidation = true OR status = 'outdated'
|
|
""")
|
|
|
|
scheduled = 0
|
|
for config_id, name, isps in cur.fetchall():
|
|
cur.execute("""
|
|
INSERT INTO admin.validation_schedule
|
|
(config_type, config_id, scheduled_at, next_validation)
|
|
VALUES ('headers', %s, NOW(), NOW() + INTERVAL '1 day')
|
|
ON CONFLICT DO NOTHING
|
|
""", (config_id,))
|
|
scheduled += 1
|
|
print(f" 📅 Scheduled: {name}")
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
return scheduled
|
|
|
|
def main():
|
|
import sys
|
|
|
|
if len(sys.argv) < 2:
|
|
run_weekly_check()
|
|
return
|
|
|
|
cmd = sys.argv[1]
|
|
|
|
if cmd == 'check':
|
|
run_weekly_check()
|
|
elif cmd == 'updates':
|
|
show_recent_updates()
|
|
elif cmd == 'schedule':
|
|
scheduled = schedule_revalidations()
|
|
print(f"Scheduled {scheduled} configs for revalidation")
|
|
elif cmd == 'isp':
|
|
isp = sys.argv[2] if len(sys.argv) > 2 else 'gmail'
|
|
comparison = compare_periods(isp)
|
|
if comparison:
|
|
print(f"\n{isp} Analysis:")
|
|
print(f" This week: {comparison['this_week']['inbox_rate']:.1f}% inbox ({comparison['this_week']['total']} sent)")
|
|
print(f" Last week: {comparison['last_week']['inbox_rate']:.1f}% inbox ({comparison['last_week']['total']} sent)")
|
|
print(f" Change: {comparison['inbox_change']:+.1f}%")
|
|
else:
|
|
print(f"Insufficient data for {isp}")
|
|
else:
|
|
print("Commands: check, updates, schedule, isp <name>")
|
|
|
|
if __name__ == '__main__':
|
|
main()
|