#!/usr/bin/env python3
"""
Ghost Agency Revenue Bots - Automated Revenue Generation
1. Upsell Bot - Cross-sell hosting/SEO/backup/maintenance
2. Renewal Bot - Domain/hosting renewals
3. Referral Bot - Client referral program
"""

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

# Config
LEADS_DIR = Path("/home/ubuntu/GhostAgency")
DB_FILE = LEADS_DIR / "revenue_bots.db"
CLIENTS_CSV = LEADS_DIR / "clients.csv"  # Clienti chiusi
UNSUBSCRIBE_URL = os.getenv("UNSUBSCRIBE_URL", "https://svoraj.me/unsubscribe")

# ============================================================
# DATABASE
# ============================================================
def init_db():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    
    # Clients table
    c.execute("""
        CREATE TABLE IF NOT EXISTS clients (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nome TEXT NOT NULL,
            email TEXT,
            telefono TEXT,
            sito_web TEXT,
            dominio TEXT,
            piano TEXT DEFAULT 'base',  -- base, pro, enterprise
            setup_fee REAL DEFAULT 0,
            monthly_fee REAL DEFAULT 50,
            dominio_scadenza DATE,
            hosting_scadenza DATE,
            ssl_scadenza DATE,
            seo_attivo BOOLEAN DEFAULT 0,
            backup_attivo BOOLEAN DEFAULT 0,
            manutenzione_attiva BOOLEAN DEFAULT 0,
            stato TEXT DEFAULT 'attivo',  -- attivo, scaduto, cancellato
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # Upsell history
    c.execute("""
        CREATE TABLE IF NOT EXISTS upsells (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER,
            tipo TEXT,  -- hosting_upgrade, seo, backup, manutenzione, dominio_extra
            importo_mensile REAL,
            importo_setup REAL,
            stato TEXT DEFAULT 'proposto',  -- proposto, accettato, rifiutato
            proposto_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            accettato_at TIMESTAMP,
            note TEXT,
            FOREIGN KEY (client_id) REFERENCES clients(id)
        )
    """)
    
    # Renewals
    c.execute("""
        CREATE TABLE IF NOT EXISTS renewals (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER,
            tipo TEXT,  -- dominio, hosting, ssl
            scadenza DATE,
            importo REAL,
            stato TEXT DEFAULT 'pending',  -- pending, sent, paid, failed
            reminder_1_sent BOOLEAN DEFAULT 0,
            reminder_2_sent BOOLEAN DEFAULT 0,
            reminder_3_sent BOOLEAN DEFAULT 0,
            pagato_at TIMESTAMP,
            FOREIGN KEY (client_id) REFERENCES clients(id)
        )
    """)
    
    # Referrals
    c.execute("""
        CREATE TABLE IF NOT EXISTS referrals (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER,  -- chi referralizza
            lead_nome TEXT,
            lead_email TEXT,
            lead_telefono TEXT,
            lead_categoria TEXT,
            stato TEXT DEFAULT 'nuovo',  -- nuovo, contattato, chiuso_vinto, chiuso_perso
            premio_eur REAL DEFAULT 100,
            pagato BOOLEAN DEFAULT 0,
            creato_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            chiuso_at TIMESTAMP,
            FOREIGN KEY (client_id) REFERENCES clients(id)
        )
    """)
    
    # Revenue tracking
    c.execute("""
        CREATE TABLE IF NOT EXISTS revenue_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER,
            tipo TEXT,  -- setup, monthly, upsell, renewal, referral
            importo REAL,
            descrizione TEXT,
            data TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (client_id) REFERENCES clients(id)
        )
    """)
    
    conn.commit()
    conn.close()

# ============================================================
# CLIENTS CSV MANAGEMENT
# ============================================================
def ensure_clients_csv():
    """Crea clients.csv se non esiste con struttura base."""
    if CLIENTS_CSV.exists():
        return
    
    # Esempio struttura
    headers = [
        "nome", "email", "telefono", "sito_web", "dominio",
        "piano", "setup_fee", "monthly_fee",
        "dominio_scadenza", "hosting_scadenza", "ssl_scadenza",
        "seo_attivo", "backup_attivo", "manutenzione_attiva",
        "stato", "note"
    ]
    
    with open(CLIENTS_CSV, "w", newline="", encoding="utf-8") as f:
        writer = csv.writer(f)
        writer.writerow(headers)
    
    print(f"[+] Creato {CLIENTS_CSV} - aggiungi i tuoi clienti qui")

def load_clients():
    """Carica clienti da CSV."""
    if not CLIENTS_CSV.exists():
        ensure_clients_csv()
        return []
    
    clients = []
    with open(CLIENTS_CSV, newline="", encoding="utf-8") as f:
        reader = csv.DictReader(f)
        for row in reader:
            clients.append(row)
    return clients

def save_clients(clients):
    """Salva clienti su CSV."""
    if not clients:
        return
    
    headers = [
        "nome", "email", "telefono", "sito_web", "dominio",
        "piano", "setup_fee", "monthly_fee",
        "dominio_scadenza", "hosting_scadenza", "ssl_scadenza",
        "seo_attivo", "backup_attivo", "manutenzione_attiva",
        "stato", "note"
    ]
    
    with open(CLIENTS_CSV, "w", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=headers)
        writer.writeheader()
        writer.writerows(clients)

# ============================================================
# UPSELL BOT
# ============================================================
UPSELL_OFFERS = [
    {
        "tipo": "seo",
        "nome": "SEO Locale Avanzato",
        "descrizione": "Ottimizzazione per 'categoria + parma', Google My Business, recensioni, schema markup",
        "setup_fee": 200,
        "monthly_fee": 100,
        "trigger": lambda c: c.get("seo_attivo") == "0" or c.get("seo_attivo") == "False"
    },
    {
        "tipo": "backup",
        "nome": "Backup Automatico Giornaliero",
        "descrizione": "Backup sito + DB su storage off-site (S3/Backblaze), ripristino 1-click, retention 30gg",
        "setup_fee": 50,
        "monthly_fee": 30,
        "trigger": lambda c: c.get("backup_attivo") == "0" or c.get("backup_attivo") == "False"
    },
    {
        "tipo": "manutenzione",
        "nome": "Manutenzione Proattiva",
        "descrizione": "Aggiornamenti WP/plugin/tema, security scan, uptime monitoring, fix rotti",
        "setup_fee": 0,
        "monthly_fee": 80,
        "trigger": lambda c: c.get("manutenzione_attiva") == "0" or c.get("manutenzione_attiva") == "False"
    },
    {
        "tipo": "hosting_upgrade",
        "nome": "Hosting Performance SSD + CDN",
        "descrizione": "Server NVMe, Cloudflare CDN, cache avanzata, 99.9% uptime SLA",
        "setup_fee": 0,
        "monthly_fee": 50,  # upgrade da base 50 a 100
        "trigger": lambda c: c.get("piano") == "base"
    },
    {
        "tipo": "dominio_extra",
        "nome": "Domini Protettivi (.com, .eu, .net)",
        "descrizione": "Registra varianti del tuo brand per evitare cybersquatting",
        "setup_fee": 15,
        "monthly_fee": 0,  # annuale
        "trigger": lambda c: True  # sempre applicabile
    }
]

def run_upsell_bot():
    """Analizza clienti e propone upsell mirati."""
    print(f"\n{'='*60}")
    print(f" UPSELL BOT - Analisi opportunità")
    print(f"{'='*60}")
    
    clients = load_clients()
    if not clients:
        print("[!] Nessun cliente in clients.csv")
        return
    
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    
    proposte = 0
    
    for client in clients:
        nome = client.get("nome", "").strip()
        if not nome:
            continue
        
        client_id_hash = hashlib.md5(nome.encode()).hexdigest()[:12]
        
        # Get or create client in DB
        c.execute("SELECT id FROM clients WHERE nome=?", (nome,))
        row = c.fetchone()
        if row:
            client_db_id = row[0]
        else:
            c.execute("""INSERT INTO clients (nome, email, telefono, sito_web, dominio, piano, 
                          setup_fee, monthly_fee, dominio_scadenza, hosting_scadenza, ssl_scadenza,
                          seo_attivo, backup_attivo, manutenzione_attiva, stato)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                     (nome, client.get("email", ""), client.get("telefono", ""),
                      client.get("sito_web", ""), client.get("dominio", ""),
                      client.get("piano", "base"),
                      float(client.get("setup_fee", 0) or 0),
                      float(client.get("monthly_fee", 50) or 50),
                      client.get("dominio_scadenza", ""), client.get("hosting_scadenza", ""),
                      client.get("ssl_scadenza", ""),
                      int(client.get("seo_attivo", 0) or 0),
                      int(client.get("backup_attivo", 0) or 0),
                      int(client.get("manutenzione_attiva", 0) or 0),
                      client.get("stato", "attivo")))
            client_db_id = c.lastrowid
        
        # Check each upsell offer
        for offer in UPSELL_OFFERS:
            if offer["trigger"](client):
                # Check if already proposed
                c.execute("""SELECT id FROM upsells 
                            WHERE client_id=? AND tipo=? AND stato IN ('proposto', 'accettato')""",
                         (client_db_id, offer["tipo"]))
                if c.fetchone():
                    continue
                
                # Proponi
                c.execute("""INSERT INTO upsells 
                            (client_id, tipo, importo_mensile, importo_setup, stato, note)
                            VALUES (?, ?, ?, ?, 'proposto', ?)""",
                         (client_db_id, offer["tipo"], offer["monthly_fee"], 
                          offer["setup_fee"], offer["descrizione"]))
                
                print(f"  [+] Upsell proposto: {nome} -> {offer['nome']} (€{offer['monthly_fee']}/mese + €{offer['setup_fee']} setup)")
                proposte += 1
    
    conn.commit()
    conn.close()
    print(f"\n[+] Upsell bot completato. {proposte} nuove proposte generate.")

# ============================================================
# RENEWAL BOT
# ============================================================
def run_renewal_bot():
    """Controlla scadenze e invia reminder."""
    print(f"\n{'='*60}")
    print(f" RENEWAL BOT - Controllo scadenze")
    print(f"{'='*60}")
    
    clients = load_clients()
    if not clients:
        return
    
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    
    today = datetime.now().date()
    reminders_sent = 0
    
    for client in clients:
        nome = client.get("nome", "").strip()
        if not nome:
            continue
        
        client_id_hash = hashlib.md5(nome.encode()).hexdigest()[:12]
        c.execute("SELECT id FROM clients WHERE nome=?", (nome,))
        row = c.fetchone()
        if not row:
            continue
        client_db_id = row[0]
        
        # Check each expiration
        for tipo, scadenza_str, importo in [
            ("dominio", client.get("dominio_scadenza", ""), 15),
            ("hosting", client.get("hosting_scadenza", ""), float(client.get("monthly_fee", 50) or 50)),
            ("ssl", client.get("ssl_scadenza", ""), 0)
        ]:
            if not scadenza_str:
                continue
            
            try:
                scadenza = datetime.strptime(scadenza_str, "%Y-%m-%d").date()
            except:
                continue
            
            giorni = (scadenza - today).days
            
            # Check if renewal exists
            c.execute("""SELECT id, reminder_1_sent, reminder_2_sent, reminder_3_sent, stato
                        FROM renewals WHERE client_id=? AND tipo=? AND scadenza=?""",
                       (client_db_id, tipo, scadenza_str))
            row = c.fetchone()
            
            if not row:
                # Crea renewal
                c.execute("""INSERT INTO renewals 
                            (client_id, tipo, scadenza, importo, stato)
                            VALUES (?, ?, ?, ?, 'pending')""",
                         (client_db_id, tipo, scadenza_str, importo))
                conn.commit()
                continue
            
            renewal_id, r1, r2, r3, stato = row
            
            if stato == "paid":
                continue
            
            # Reminder schedule: 30gg, 14gg, 3gg prima
            if giorni == 30 and not r1:
                send_renewal_reminder(nome, client.get("email", ""), tipo, scadenza, 30)
                c.execute("UPDATE renewals SET reminder_1_sent=1 WHERE id=?", (renewal_id,))
                reminders_sent += 1
            elif giorni == 14 and not r2:
                send_renewal_reminder(nome, client.get("email", ""), tipo, scadenza, 14)
                c.execute("UPDATE renewals SET reminder_2_sent=1 WHERE id=?", (renewal_id,))
                reminders_sent += 1
            elif giorni == 3 and not r3:
                send_renewal_reminder(nome, client.get("email", ""), tipo, scadenza, 3)
                c.execute("UPDATE renewals SET reminder_3_sent=1 WHERE id=?", (renewal_id,))
                reminders_sent += 1
            elif giorni < 0 and stato == "pending":
                c.execute("UPDATE renewals SET stato='overdue' WHERE id=?", (renewal_id,))
                print(f"  [!] SCADUTO: {nome} - {tipo} (scaduto {abs(giorni)}gg fa)")
    
    conn.commit()
    conn.close()
    print(f"\n[+] Renewal bot completato. {reminders_sent} reminder inviati.")

def send_renewal_reminder(nome, email, tipo, scadenza, giorni):
    """Invia reminder rinnovo (placeholder - integra con mailer)."""
    print(f"  [����] Reminder {giorni}gg: {nome} - {tipo} scade {scadenza} (email: {email})")
    # TODO: Integra con auto_mailer_v3.py per invio reale

# ============================================================
# REFERRAL BOT
# ============================================================
def run_referral_bot():
    """Gestisce programma referral clienti."""
    print(f"\n{'='*60}")
    print(f" REFERRAL BOT - Programma referrals")
    print(f"{'='*60}")
    
    clients = load_clients()
    if not clients:
        return
    
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    
    # Per ogni cliente attivo, invia email referral (1 volta al mese)
    for client in clients:
        nome = client.get("nome", "").strip()
        email = client.get("email", "").strip()
        if not nome or not email:
            continue
        
        client_db_id = hashlib.md5(nome.encode()).hexdigest()[:12]
        
        # Check se già mandato questo mese
        c.execute("""SELECT id FROM referrals 
                    WHERE client_id=? AND creato_at > date('now', '-30 days')""",
                 (client_db_id,))
        if c.fetchone():
            continue
        
        # Crea referral entry (placeholder per email)
        c.execute("""INSERT INTO referrals 
                    (client_id, premio_eur, stato)
                    VALUES (?, 100, 'nuovo')""",
                 (client_db_id,))
        
        print(f"  [����] Referral email da inviare a: {nome} ({email})")
        # TODO: Integra con mailer per invio template referral
    
    conn.commit()
    conn.close()
    print(f"\n[+] Referral bot completato.")

# ============================================================
# REVENUE REPORT
# ============================================================
def run_revenue_report():
    """Genera report revenue mensile."""
    print(f"\n{'='*60}")
    print(f" REVENUE REPORT - {datetime.now().strftime('%B %Y')}")
    print(f"{'='*60}")
    
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    
    # MRR (Monthly Recurring Revenue)
    c.execute("""SELECT SUM(monthly_fee) FROM clients WHERE stato='attivo'""")
    mrr = c.fetchone()[0] or 0
    
    # Setup fees this month
    c.execute("""SELECT SUM(importo) FROM revenue_events 
                WHERE tipo='setup' AND data > date('now', 'start of month')""")
    setup_month = c.fetchone()[0] or 0
    
    # Upsells this month
    c.execute("""SELECT SUM(importo_mensile) FROM upsells 
                WHERE stato='accettato' AND accettato_at > date('now', 'start of month')""")
    upsell_mrr = c.fetchone()[0] or 0
    
    # Renewals this month
    c.execute("""SELECT SUM(importo) FROM renewals 
                WHERE stato='paid' AND pagato_at > date('now', 'start of month')""")
    renewals_month = c.fetchone()[0] or 0
    
    # Referrals paid
    c.execute("""SELECT COUNT(*) FROM referrals WHERE pagato=1""")
    referrals_paid = c.fetchone()[0] or 0
    referral_cost = referrals_paid * 100
    
    print(f"  MRR Attuale: €{mrr:.0f}/mese")
    print(f"  Setup fee mese: €{setup_month:.0f}")
    print(f"  Nuovo MRR da upsell: €{upsell_mrr:.0f}/mese")
    print(f"  Rinnovi mese: €{renewals_month:.0f}")
    print(f"  Referral pagati: {referrals_paid} (€{referral_cost:.0f} costo)")
    print(f"  NETTO MESE: €{setup_month + renewals_month - referral_cost:.0f}")
    print(f"  MRR PROIETTATO: €{mrr + upsell_mrr:.0f}/mese")
    
    conn.close()

# ============================================================
# MAIN
# ============================================================
def main():
    init_db()
    
    print(f"\n{'='*60}")
    print(f" GHOST AGENCY REVENUE BOTS")
    print(f" Avviato: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print(f"{'='*60}")
    
    run_upsell_bot()
    run_renewal_bot()
    run_referral_bot()
    run_revenue_report()
    
    print(f"\n{'='*60}")
    print(f" TUTTI I BOT COMPLETATI")
    print(f"{'='*60}")

if __name__ == "__main__":
    main()