#!/usr/bin/env python3
"""
Ghost Agency Auto Mailer v3 - Professional Edition
- Legge CSV con email verificate (da radar v3)
- Template A/B test
- Tracking pixel aperture
- Unsubscribe GDPR compliant
- Rate limiting intelligente
- Retry logic
- Logging completo
"""

import os
import csv
import smtplib
import time
import random
import hashlib
import sqlite3
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from pathlib import Path
from datetime import datetime

# Config - Environment variables
SMTP_HOST = os.getenv("SMTP_HOST", "smtp.gmail.com")
SMTP_PORT = int(os.getenv("SMTP_PORT", "587"))
SMTP_USER = os.getenv("SMTP_USER", "4ms1xseller@gmail.com")
SMTP_PASS = os.getenv("SMTP_PASS", "")
FROM_NAME = os.getenv("FROM_NAME", "Amraj Singh Atwal")
REPLY_TO = os.getenv("REPLY_TO", "4ms1xseller@gmail.com")
UNSUBSCRIBE_URL = os.getenv("UNSUBSCRIBE_URL", "https://svoraj.me/unsubscribe")
TRACKING_DOMAIN = os.getenv("TRACKING_DOMAIN", "svoraj.me")

LEADS_DIR = Path("/home/ubuntu/GhostAgency")
SENT_LOG = LEADS_DIR / "sent_emails.json"
DB_FILE = LEADS_DIR / "mailer.db"
MAX_EMAILS_PER_RUN = int(os.getenv("MAX_EMAILS_PER_RUN", "15"))
DELAY_MIN = int(os.getenv("DELAY_MIN", "2"))  # Reduced for testing
DELAY_MAX = int(os.getenv("DELAY_MAX", "5"))  # Reduced for testing

# ============================================================
# DATABASE PER TRACKING
# ============================================================
def init_db():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("""
        CREATE TABLE IF NOT EXISTS emails_sent (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL,
            nome TEXT,
            categoria TEXT,
            subject TEXT,
            template_version TEXT,
            tracking_id TEXT UNIQUE,
            sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            opened_at TIMESTAMP,
            clicked_at TIMESTAMP,
            unsubscribed_at TIMESTAMP,
            bounce_reason TEXT,
            status TEXT DEFAULT 'sent'
        )
    """)
    c.execute("""
        CREATE TABLE IF NOT EXISTS email_opens (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email_id INTEGER,
            opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            ip TEXT,
            user_agent TEXT,
            FOREIGN KEY (email_id) REFERENCES emails_sent(id)
        )
    """)
    c.execute("""
        CREATE TABLE IF NOT EXISTS email_clicks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email_id INTEGER,
            clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            url TEXT,
            ip TEXT,
            user_agent TEXT,
            FOREIGN KEY (email_id) REFERENCES emails_sent(id)
        )
    """)
    conn.commit()
    conn.close()

# ============================================================
# TEMPLATE VERSIONS (A/B TEST)
# ============================================================
TEMPLATES = {
    "v1_direct": {
        "name": "Direct - Menu Online",
        "subject": "Il sito di {nome} non si trova su Google",
        "body": """Buongiorno {nome},

vi scrivo perché ho cercato il vostro locale su Google e non vi ho trovato.
Oppure vi ho trovato su un portale generico (mordy.it, quandoo, thefork) che vi toglie identità e clienti.

Sono Amraj, sviluppatore web a Parma. Creo siti web veloci, moderni e di vostra proprietà.
Niente commissioni, niente vincoli, il dominio è vostro per sempre.

Cosa includo:
• Dominio .it/.com registrato a vostro nome
• Sito responsive (mobile-first), veloce (90+ PageSpeed)
• Menu online, prenotazioni, mappa, contatti
• SEO locale: "pizzeria parma", "ristorante centro parma", ecc.
• Hosting incluso 1 anno, poi €50/anno

Costa meno di un abbonamento a TheFork/Quandoo all'anno.

Vi interessa una demo gratuita (15 min, zero impegno)?

Rispondete a questa mail o scrivete su WhatsApp: +39 3780883157

A presto,
Amraj Singh Atwal
svoraj.me

---
Se non vuoi altre mail: {unsubscribe}"""
    },
    
    "v2_educational": {
        "name": "Educational - Perché il vostro sito fa perdere soldi",
        "subject": "Perché {nome} perde clienti ogni giorno (senza saperlo)",
        "body": """Ciao {nome},

la maggior parte dei ristoratori a Parma non lo sa: **il 67% dei clienti cerca il menu su Google prima di decidere dove andare.**

Se non hai un sito tuo, o sei su un portale terzo, quei clienti vanno da chi ce l'ha.

Io sono Amraj, sviluppatore qui a Parma. Aiuto ristoranti, pizzerie, bar e artigiani a riprendersi la loro presenza online.

**Cosa faccio diversamente:**
1. Il dominio è TUO (es. vostronome.it) - non perdi mai l'accesso
2. Niente canoni mensili obbligatori - paghi setup + €50/anno hosting
3. Sito ottimizzato per "parma + categoria" - ti trovi chi cerca TE
4. Integrazione WhatsApp/Telegram per prenotazioni dirette
5. Backup automatici, SSL, GDPR compliant

**Risultati tipici clienti Parma:**
• +40% prenotazioni dirette (zero commissioni)
• +200% traffico organico locale in 3 mesi
• Recupero investimento in 2-3 mesi

Vuoi vedere un esempio live di un sito che ho fatto per un ristorante qui in zona?

Rispondi "S��" e ti mando il link.

Amraj
svoraj.me

---
Disiscriviti: {unsubscribe}"""
    },
    
    "v3_short": {
        "name": "Short - Diretto al punto",
        "subject": "Sito web per {nome}? (demo gratis)",
        "body": """Buongiorno {nome},

sviluppo siti web per ristoranti e attività locali a Parma.
Dominio vostro, niente commissioni, hosting incluso.

Vi interessa una demo gratuita (15 min, zero impegno)?

Rispondete qui o WhatsApp: +39 3780883157

Amraj - svoraj.me

---
Unsubscribe: {unsubscribe}"""
    }
}

# Default template (can be rotated)
DEFAULT_TEMPLATE = "v2_educational"

# ============================================================
# TRACKING HELPERS
# ============================================================
def generate_tracking_id(email, nome):
    """Genera ID univoco per tracking."""
    raw = f"{email}{nome}{time.time()}"
    return hashlib.sha256(raw.encode()).hexdigest()[:16]

def build_tracking_pixel(tracking_id):
    """Pixel 1x1 per tracking aperture."""
    return f'<img src="https://{TRACKING_DOMAIN}/track/open/{tracking_id}" width="1" height="1" alt="" style="display:none;">'

def build_tracked_link(tracking_id, url, label):
    """Link tracciato per click."""
    return f'https://{TRACKING_DOMAIN}/track/click/{tracking_id}?url={quote_plus(url)}'

# ============================================================
# DATABASE OPERATIONS
# ============================================================
def log_email_sent(email, nome, categoria, subject, template_version, tracking_id):
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("""
        INSERT INTO emails_sent (email, nome, categoria, subject, template_version, tracking_id, status)
        VALUES (?, ?, ?, ?, ?, ?, 'sent')
    """, (email, nome, categoria, subject, template_version, tracking_id))
    email_id = c.lastrowid
    conn.commit()
    conn.close()
    return email_id

def log_open(tracking_id, ip, user_agent):
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("SELECT id FROM emails_sent WHERE tracking_id=?", (tracking_id,))
    row = c.fetchone()
    if row:
        c.execute("INSERT INTO email_opens (email_id, ip, user_agent) VALUES (?, ?, ?)", (row[0], ip, user_agent))
        c.execute("UPDATE emails_sent SET opened_at=CURRENT_TIMESTAMP, status='opened' WHERE id=?", (row[0],))
        conn.commit()
    conn.close()

def log_click(tracking_id, url, ip, user_agent):
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("SELECT id FROM emails_sent WHERE tracking_id=?", (tracking_id,))
    row = c.fetchone()
    if row:
        c.execute("INSERT INTO email_clicks (email_id, url, ip, user_agent) VALUES (?, ?, ?, ?)", (row[0], url, ip, user_agent))
        c.execute("UPDATE emails_sent SET clicked_at=CURRENT_TIMESTAMP, status='clicked' WHERE id=?", (row[0],))
        conn.commit()
    conn.close()

def log_bounce(email, reason):
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("UPDATE emails_sent SET status='bounced', bounce_reason=? WHERE email=?", (reason, email))
    conn.commit()
    conn.close()

def log_unsubscribe(email):
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("UPDATE emails_sent SET status='unsubscribed', unsubscribed_at=CURRENT_TIMESTAMP WHERE email=?", (email,))
    conn.commit()
    conn.close()

# ============================================================
# SENT LOG (JSON backup)
# ============================================================
def load_sent_log():
    if SENT_LOG.exists():
        import json
        with open(SENT_LOG) as f:
            return set(json.load(f))
    return set()

def save_sent_log(sent_set):
    import json
    with open(SENT_LOG, 'w') as f:
        json.dump(list(sent_set), f)

# ============================================================
# FIND LATEST LEADS CSV
# ============================================================
def find_latest_leads_csv():
    csvs = list(LEADS_DIR.glob("leads_*_ultimate.csv")) + list(LEADS_DIR.glob("leads_*_local.csv"))
    if not csvs:
        return None
    return max(csvs, key=lambda p: p.stat().st_mtime)

# ============================================================
# EMAIL SENDING
# ============================================================
def send_email(to_email, to_name, to_categoria, template_version="v2_educational"):
    if not SMTP_PASS:
        print("[!] SMTP_PASS non configurato")
        return False, None
    
    template = TEMPLATES.get(template_version, TEMPLATES[DEFAULT_TEMPLATE])
    
    # Generate tracking
    tracking_id = generate_tracking_id(to_email, to_name)
    email_id = log_email_sent(to_email, to_name, to_categoria, 
                               template["subject"].format(nome=to_name), 
                               template_version, tracking_id)
    
    # Build message
    msg = MIMEMultipart("alternative")
    msg['From'] = f"{FROM_NAME} <{SMTP_USER}>"
    msg['To'] = to_email
    msg['Subject'] = template["subject"].format(nome=to_name)
    msg['Reply-To'] = REPLY_TO
    msg['List-Unsubscribe'] = f"<{UNSUBSCRIBE_URL}>"
    msg['X-Tracking-ID'] = tracking_id
    
    # Build body with tracking
    body = template["body"].format(
        nome=to_name,
        unsubscribe=UNSUBSCRIBE_URL
    )
    
    # Add tracking pixel
    tracking_pixel = build_tracking_pixel(tracking_id)
    body_html = body.replace('\n', '<br>') + tracking_pixel
    
    # Plain text version (no tracking)
    body_text = template["body"].format(
        nome=to_name,
        unsubscribe=UNSUBSCRIBE_URL
    )
    
    msg.attach(MIMEText(body_text, 'plain', 'utf-8'))
    msg.attach(MIMEText(body_html, 'html', 'utf-8'))
    
    try:
        server = smtplib.SMTP(SMTP_HOST, SMTP_PORT, timeout=30)
        server.starttls()
        server.login(SMTP_USER, SMTP_PASS)
        server.sendmail(SMTP_USER, to_email, msg.as_string())
        server.quit()
        
        print(f"[+] Inviato a {to_name} ({to_email}) | Template: {template_version} | Tracking: {tracking_id}")
        return True, tracking_id
    except smtplib.SMTPAuthenticationError:
        print(f"[!] Errore autenticazione SMTP per {to_email}")
        log_bounce(to_email, "auth_failed")
        return False, None
    except smtplib.SMTPRecipientsRefused:
        print(f"[!] Destinatario rifiutato: {to_email}")
        log_bounce(to_email, "recipient_refused")
        return False, None
    except smtplib.SMTPSenderRefused:
        print(f"[!] Mittente rifiutato: {to_email}")
        log_bounce(to_email, "sender_refused")
        return False, None
    except Exception as e:
        print(f"[!] Errore invio a {to_email}: {e}")
        log_bounce(to_email, str(e)[:100])
        return False, None

# ============================================================
# MAIN
# ============================================================
def main():
    init_db()
    
    csv_path = find_latest_leads_csv()
    if not csv_path:
        print("[!] Nessun CSV leads trovato in GhostAgency/")
        return
    
    print(f"[*] Leggo lead da {csv_path.name}")
    
    sent_log = load_sent_log()
    sent_count = 0
    template_cycle = list(TEMPLATES.keys())
    template_idx = 0
    
    with open(csv_path, newline='', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            if sent_count >= MAX_EMAILS_PER_RUN:
                print(f"[=] Limite {MAX_EMAILS_PER_RUN} email/run raggiunto")
                break
            
            nome = row.get('nome', '').strip()
            email = row.get('email', '').strip()
            telefono = row.get('telefono', '').strip()
            categoria = row.get('categoria', '').strip()
            fonte = row.get('fonte', '').strip()
            
            if not email or '@' not in email:
                print(f"    [=] Skip {nome}: email non valida ({email})")
                # Aggiungi a call list
                call_list_path = LEADS_DIR / "call_list.csv"
                file_exists = call_list_path.exists()
                with open(call_list_path, "a", newline="", encoding="utf-8") as cf:
                    writer = csv.writer(cf)
                    if not file_exists:
                        writer.writerow(["Nome", "Telefono", "Indirizzo", "Categoria", "Data", "Fonte"])
                    writer.writerow([nome, telefono, row.get('Indirizzo', ''), categoria, 
                                   datetime.now().strftime("%Y-%m-%d"), fonte])
                continue
            
            if email in sent_log:
                print(f"[=] Già inviato a {email}, salto")
                continue
            
            # Rotate template per A/B test
            template_version = template_cycle[template_idx % len(template_cycle)]
            template_idx += 1
            
            success, tracking_id = send_email(email, nome, categoria, template_version)
            
            if success:
                sent_log.add(email)
                sent_count += 1
                save_sent_log(sent_log)
                
                # Rate limiting casuale
                if sent_count < MAX_EMAILS_PER_RUN:
                    delay = random.randint(DELAY_MIN, DELAY_MAX)
                    print(f"[*] Pausa {delay}s...")
                    time.sleep(delay)
            else:
                print(f"[!] Fallito invio a {email}, continuo...")
                time.sleep(5)
    
    print(f"\n[+] Fatto. Inviate {sent_count} email nuove.")
    print(f"[*] Template usati: {template_cycle}")
    print(f"[*] DB tracking: {DB_FILE}")

if __name__ == "__main__":
    import time
    import random
    from urllib.parse import quote_plus
    main()