#!/usr/bin/env python3
"""
Ghost Agency Call Followup v3 - WhatsApp Business API + Multi-channel
- Legge call_list.csv (lead senza email)
- Genera messaggi WhatsApp ottimizzati
- Invia via WhatsApp Business API (Twilio/Meta)
- Template A/B test
- Tracking consegna/lettura/risposta
- Fallback: salva file .txt per invio manuale
"""

import os
import csv
import time
import random
import hashlib
import sqlite3
import requests
import json
from pathlib import Path
from datetime import datetime, timedelta

# Config
WHATSAPP_PROVIDER = os.getenv("WHATSAPP_PROVIDER", "twilio")  # "twilio" | "meta" | "manual"
TWILIO_ACCOUNT_SID = os.getenv("TWILIO_ACCOUNT_SID", "")
TWILIO_AUTH_TOKEN = os.getenv("TWILIO_AUTH_TOKEN", "")
TWILIO_WHATSAPP_FROM = os.getenv("TWILIO_WHATSAPP_FROM", "whatsapp:+14155238886")  # Twilio sandbox
META_WHATSAPP_TOKEN = os.getenv("META_WHATSAPP_TOKEN", "")
META_PHONE_NUMBER_ID = os.getenv("META_PHONE_NUMBER_ID", "")
META_WABA_ID = os.getenv("META_WABA_ID", "")

LEADS_DIR = Path("/home/ubuntu/GhostAgency")
CALL_LIST_CSV = LEADS_DIR / "call_list.csv"
DB_FILE = LEADS_DIR / "call_followup.db"
OUTPUT_DIR = LEADS_DIR / "whatsapp_outbound"
OUTPUT_DIR.mkdir(exist_ok=True)

# Template WhatsApp (A/B test)
TEMPLATES = {
    "v1_direct": {
        "name": "Direct - Breve",
        "body": "Ciao {nome}, sono Amraj (sviluppatore web Parma). Ho visto che {nome} non ha un sito proprio e perde clienti che cercano su Google. Ti interessa una demo gratis 15 min? Rispondi 'SÍ' ���\n\nAmraj - svoraj.me"
    },
    "v2_value": {
        "name": "Value - Risultati",
        "body": "Ciao {nome} ���\n\nSono Amraj, faccio siti web per ristoranti/locali a Parma.\n\nI miei clienti recuperano l'investimento in 2-3 mesi grazie a:\n��� Prenotazioni dirette (zero commissioni TheFork)\n��� Sito trovato su 'pizzeria parma', 'ristorante centro parma'\n��� Dominio tuo per sempre, hosting €50/anno\n\nTi mando un esempio live di un ristorante qui in zona? Rispondi 'SÍ'\n\nAmraj - svoraj.me"
    },
    "v3_question": {
        "name": "Question - Coinvolgimento",
        "body": "Ciao {nome}, domanda veloce: quanti clienti al mese perdi perché non ti trovano su Google? ���\n\nFaccio siti web per locali a Parma - dominio tuo, zero commissioni, prenotazioni dirette.\n\nSe vuoi vedere i numeri reali di un ristorante qui vicino, rispondi 'SÍ' e ti mando tutto.\n\nAmraj - svoraj.me"
    },
    "v4_short": {
        "name": "Short - Molto breve",
        "body": "Ciao {nome}, Amraj qui (web developer Parma). Creo siti per ristoranti: dominio tuo, niente commissioni, clienti da Google. Demo gratis 10 min? Rispondi 'SÍ' ���\n\nsvoraj.me"
    }
}

DEFAULT_TEMPLATE = "v2_value"

# ============================================================
# DATABASE
# ============================================================
def init_db():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("""
        CREATE TABLE IF NOT EXISTS messages_sent (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            lead_id TEXT,
            nome TEXT,
            telefono TEXT,
            categoria TEXT,
            template_version TEXT,
            message_body TEXT,
            provider TEXT,
            message_sid TEXT,
            status TEXT DEFAULT 'queued',
            sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            delivered_at TIMESTAMP,
            read_at TIMESTAMP,
            replied_at TIMESTAMP,
            error TEXT
        )
    """)
    c.execute("""
        CREATE TABLE IF NOT EXISTS message_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            message_id INTEGER,
            event_type TEXT,  -- sent, delivered, read, replied, failed
            event_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            payload TEXT,
            FOREIGN KEY (message_id) REFERENCES messages_sent(id)
        )
    """)
    conn.commit()
    conn.close()

# ============================================================
# PHONE NUMBER NORMALIZATION
# ============================================================
def normalize_phone(phone):
    """Normalizza numero italiano per WhatsApp."""
    # Rimuovi tutto tranne cifre
    digits = "".join(c for c in phone if c.isdigit())
    
    # Se inizia con 0039 o +39 o 39
    if digits.startswith("0039"):
        digits = digits[4:]
    elif digits.startswith("39") and len(digits) > 10:
        digits = digits[2:]
    
    # Se inizia con 0 (prefisso italiano) -> rimuovi 0 iniziale per mobile
    # Ma per fissi serve il prefisso
    # WhatsApp vuole formato internazionale: 39XXXXXXXXXX
    if digits.startswith("0"):
        # Numero fisso - mantieni prefisso
        return f"39{digits}"
    else:
        # Mobile - dovrebbe essere 3XX XXXXXXX
        if len(digits) == 10 and digits.startswith("3"):
            return f"39{digits}"
        elif len(digits) == 9 and digits.startswith("3"):
            return f"39{digits}"
    
    return f"39{digits}"

def format_whatsapp_number(phone):
    """Formatta per WhatsApp API (whatsapp:+39XXXXXXXXXX)."""
    normalized = normalize_phone(phone)
    return f"whatsapp:+{normalized}"

# ============================================================
# TEMPLATE SELECTION (A/B test rotation)
# ============================================================
def select_template(lead_index):
    templates = list(TEMPLATES.keys())
    return templates[lead_index % len(templates)]

# ============================================================
# WHATSAPP SENDING
# ============================================================
def send_whatsapp_twilio(to_number, body):
    """Invia via Twilio WhatsApp API."""
    if not all([TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN]):
        return None, "Twilio credentials missing"
    
    url = f"https://api.twilio.com/2010-04-01/Accounts/{TWILIO_ACCOUNT_SID}/Messages.json"
    data = {
        "From": TWILIO_WHATSAPP_FROM,
        "To": f"whatsapp:{to_number}",
        "Body": body
    }
    
    try:
        resp = requests.post(url, data=data, auth=(TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN), timeout=30)
        if resp.status_code in [200, 201]:
            return resp.json().get("sid"), None
        else:
            return None, f"Twilio error {resp.status_code}: {resp.text}"
    except Exception as e:
        return None, f"Twilio exception: {e}"

def send_whatsapp_meta(to_number, body):
    """Invia via Meta WhatsApp Business API."""
    if not all([META_WHATSAPP_TOKEN, META_PHONE_NUMBER_ID]):
        return None, "Meta credentials missing"
    
    url = f"https://graph.facebook.com/v18.0/{META_PHONE_NUMBER_ID}/messages"
    headers = {
        "Authorization": f"Bearer {META_WHATSAPP_TOKEN}",
        "Content-Type": "application/json"
    }
    data = {
        "messaging_product": "whatsapp",
        "to": to_number.replace("whatsapp:+", "").replace("+", ""),
        "type": "text",
        "text": {"body": body}
    }
    
    try:
        resp = requests.post(url, headers=headers, json=data, timeout=30)
        if resp.status_code in [200, 201]:
            return resp.json().get("messages", [{}])[0].get("id"), None
        else:
            return None, f"Meta error {resp.status_code}: {resp.text}"
    except Exception as e:
        return None, f"Meta exception: {e}"

def send_whatsapp_manual(to_number, body, nome, template_version):
    """Salva su file per invio manuale."""
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    filename = OUTPUT_DIR / f"whatsapp_{nome.replace(' ', '_')}_{timestamp}.txt"
    
    content = f"""=== WHATSAPP MANUAL SEND ===
To: {to_number}
Nome: {nome}
Template: {template_version}
Timestamp: {datetime.now().isoformat()}
Provider: MANUAL
Status: READY_TO_SEND

--- MESSAGE ---
{body}
=== END ===
"""
    
    with open(filename, "w", encoding="utf-8") as f:
        f.write(content)
    
    return f"manual_{timestamp}", None

# ============================================================
# DATABASE LOGGING
# ============================================================
def log_message_sent(lead_id, nome, telefono, categoria, template_version, body, provider, message_sid, status):
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("""
        INSERT INTO messages_sent 
        (lead_id, nome, telefono, categoria, template_version, message_body, provider, message_sid, status)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (lead_id, nome, telefono, categoria, template_version, body, provider, message_sid, status))
    msg_id = c.lastrowid
    conn.commit()
    conn.close()
    return msg_id

def log_event(message_id, event_type, payload=None):
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute("""
        INSERT INTO message_events (message_id, event_type, payload)
        VALUES (?, ?, ?)
    """, (message_id, event_type, json.dumps(payload) if payload else None))
    conn.commit()
    conn.close()

# ============================================================
# MAIN
# ============================================================
def main():
    init_db()
    
    if not CALL_LIST_CSV.exists():
        print("[!] call_list.csv non trovato")
        return
    
    print(f"[*] Leggo call list da {CALL_LIST_CSV}")
    
    leads = []
    with open(CALL_LIST_CSV, newline="", encoding="utf-8") as f:
        reader = csv.DictReader(f)
        for row in reader:
            leads.append(row)
    
    if not leads:
        print("[=] Nessun lead nella call list")
        return
    
    print(f"[*] Trovati {len(leads)} lead da contattare")
    print(f"[*] Provider: {WHATSAPP_PROVIDER}")
    print(f"[*] Output dir: {OUTPUT_DIR}")
    
    sent_count = 0
    failed_count = 0
    
    for i, lead in enumerate(leads):
        nome = lead.get("Nome", "").strip()
        telefono = lead.get("Telefono", "").strip()
        categoria = lead.get("Categoria", "").strip()
        indirizzo = lead.get("Indirizzo", "").strip()
        data_lead = lead.get("Data", "").strip()
        
        if not nome or not telefono:
            print(f"    [=] Skip: dati incompleti")
            continue
        
        # Normalizza telefono
        wa_number = format_whatsapp_number(telefono)
        lead_id = hashlib.md5(f"{nome}{telefono}".encode()).hexdigest()[:12]
        
        # Seleziona template
        template_version = select_template(i)
        template = TEMPLATES[template_version]
        body = template["body"].format(nome=nome)
        
        print(f"\n[{i+1}/{len(leads)}] {nome} ({categoria}) -> {wa_number}")
        print(f"    Template: {template_version}")
        
        # Invia
        message_sid = None
        error = None
        status = "sent"
        
        if WHATSAPP_PROVIDER == "twilio":
            message_sid, error = send_whatsapp_twilio(wa_number, body)
            if error:
                status = "failed"
        elif WHATSAPP_PROVIDER == "meta":
            message_sid, error = send_whatsapp_meta(wa_number, body)
            if error:
                status = "failed"
        else:  # manual
            message_sid, error = send_whatsapp_manual(wa_number, body, nome, template_version)
        
        if error:
            print(f"    [!] Errore: {error}")
            failed_count += 1
            status = "failed"
        else:
            print(f"    [+] Inviato! SID: {message_sid}")
            sent_count += 1
        
        # Log
        log_message_sent(lead_id, nome, telefono, categoria, template_version, body, 
                        WHATSAPP_PROVIDER, message_sid or "", status)
        
        if status == "sent":
            log_event(message_sid or lead_id, "sent")
        
        # Rate limiting
        if i < len(leads) - 1:
            delay = random.randint(3, 8)
            time.sleep(delay)
    
    # Summary
    print(f"\n{'='*50}")
    print(f"WHATSAPP FOLLOWUP COMPLETATO")
    print(f"Inviati: {sent_count}")
    print(f"Falliti: {failed_count}")
    print(f"Provider: {WHATSAPP_PROVIDER}")
    print(f"File output: {OUTPUT_DIR}")
    print(f"DB: {DB_FILE}")

if __name__ == "__main__":
    main()