#!/usr/bin/env python3
"""
Ghost Agency - Additional Revenue Streams Module
Analizza l'infrastruttura esistente e implementa nuovi canali di revenue automatici.
"""

import os
import json
import sqlite3
from pathlib import Path
from datetime import datetime, timedelta
from typing import Dict, List, Any

LEADS_DIR = Path("/home/ubuntu/GhostAgency")
DB_FILE = LEADS_DIR / "revenue_bots.db"
BROKER_DB = LEADS_DIR / "lead_broker.db"

# ============================================================
# ANALISI REVENUE STREAMS POSSIBILI
# ============================================================

REVENUE_STREAMS = {
    # === CORE (già implementati) ===
    "website_build": {
        "name": "Sito Web One-Time",
        "price_range": "500-2000€",
        "frequency": "once",
        "automation_level": "semi",  # radar → lead → close → deploy
        "current_mrr": 0,
        "potential_mrr": 5000,
        "notes": "Core business, serve sales manuale per chiusura"
    },
    "hosting_maintenance": {
        "name": "Hosting + Manutenzione Mensile",
        "price_range": "50-150€/mese",
        "frequency": "monthly",
        "automation_level": "high",  # renewal bot + monitoring
        "current_mrr": 800,
        "potential_mrr": 5000,
        "notes": "Già attivo su 10 clienti, renewal bot funzionante"
    },
    "seo_content": {
        "name": "SEO Locale + Content Mensile",
        "price_range": "100-300€/mese",
        "frequency": "monthly",
        "automation_level": "medium",  # content gen + GMB + citations
        "current_mrr": 0,
        "potential_mrr": 3000,
        "notes": "Upsell bot lo propone, serve delivery"
    },
    "lead_brokerage": {
        "name": "Vendita Lead ad Altre Agenzie",
        "price_range": "10-50€/lead",
        "frequency": "per_lead",
        "automation_level": "high",  # API pronta, broker bot attivo
        "current_mrr": 0,
        "potential_mrr": 2000,
        "notes": "Broker API pronta, serve buyer onboarding"
    },
    
    # === NUOVI STREAMS DA IMPLEMENTARE ===
    "whitelabel_saas": {
        "name": "Whitelabel Automation Platform",
        "price_range": "299-999€/mese",
        "frequency": "monthly",
        "automation_level": "high",  # multi-tenant, self-service
        "current_mrr": 0,
        "potential_mrr": 10000,
        "notes": "Vendi l'infrastruttura ad altre agenzie/dev"
    },
    "affiliate_commissions": {
        "name": "Affiliate/Partner Programs",
        "price_range": "5-30% per vendita",
        "frequency": "per_sale",
        "automation_level": "high",  # tracking link + webhook
        "current_mrr": 0,
        "potential_mrr": 1000,
        "notes": "Hosting, domini, tool SEO, email marketing"
    },
    "market_insights": {
        "name": "Market Intelligence Reports",
        "price_range": "50-200€/report",
        "frequency": "per_report",
        "automation_level": "high",  # radar data + AI analysis
        "current_mrr": 0,
        "potential_mrr": 1500,
        "notes": "Report settimanali: 'Nuove aperture Parma', 'Chiusure', 'Trend categoria'"
    },
    "local_ad_network": {
        "name": "Rete Pubblicitaria Locale",
        "price_range": "100-500€/mese per cliente",
        "frequency": "monthly",
        "automation_level": "medium",  # cross-promo tra clienti
        "current_mrr": 0,
        "potential_mrr": 3000,
        "notes": "Cross-promo ristoranti���palestre, dentist���farmacie, etc."
    },
    "training_course": {
        "name": "Corso 'Ghost Agency Blueprint'",
        "price_range": "297-997€ one-time",
        "frequency": "one_time",
        "automation_level": "high",  # video + community + template
        "current_mrr": 0,
        "potential_mrr": 5000,
        "notes": 'Insegna come replicare il sistema "Ghost Agency"'
    },
    "done_for_you_seo": {
        "name": "Done-For-You SEO Locale",
        "price_range": "500-1500€ setup + 200-500€/mese",
        "frequency": "monthly",
        "automation_level": "medium",  # GMB, citations, reviews, content
        "current_mrr": 0,
        "potential_mrr": 4000,
        "notes": "Google My Business + citazioni + recensioni + blog locale"
    },
    "reputation_management": {
        "name": "Gestione Recensioni/Reputazione",
        "price_range": "100-300€/mese",
        "frequency": "monthly",
        "automation_level": "high",  # monitoring + response templates + solicitation
        "current_mrr": 0,
        "potential_mrr": 2000,
        "notes": "Google/TripAdvisor/Facebook monitoring + response automation"
    },
    "booking_system": {
        "name": "Sistema Prenotazioni Whitelabel",
        "price_range": "50-150€/mese",
        "frequency": "monthly",
        "automation_level": "high",  # SaaS multi-tenant
        "current_mrr": 0,
        "potential_mrr": 3000,
        "notes": "Prenotazioni ristoranti/parrucchieri/dentisti via WhatsApp/Web"
    },
    "email_marketing_managed": {
        "name": "Email Marketing Gestito",
        "price_range": "100-300€/mese",
        "frequency": "monthly",
        "automation_level": "high",  # newsletter + automazioni + segmentazione
        "current_mrr": 0,
        "potential_mrr": 2000,
        "notes": "Newsletter settimanali + automazioni compleanno/riattivazione"
    },
    "social_media_management": {
        "name": "Gestione Social Media Locale",
        "price_range": "200-500€/mese",
        "frequency": "monthly",
        "automation_level": "medium",  # scheduling + content gen + engagement
        "current_mrr": 0,
        "potential_mrr": 3000,
        "notes": "Instagram/FB/TikTok per ristoranti/locali + Reels"
    },
    "google_ads_management": {
        "name": "Gestione Google Ads Locale",
        "price_range": "15-20% ad spend + 200€ setup",
        "frequency": "monthly",
        "automation_level": "medium",  # campaign build + optimization
        "current_mrr": 0,
        "potential_mrr": 3000,
        "notes": "Campagne 'near me', call-only, local services"
    },
    "website_audit_tool": {
        "name": "Audit Tool Whitelabel",
        "price_range": "29-99€/mese",
        "frequency": "monthly",
        "automation_level": "high",  # self-service audit + report PDF
        "current_mrr": 0,
        "potential_mrr": 1500,
        "notes": "Lead magnet per agenzie: 'Audit gratis del tuo sito'"
    },
    "domain_portfolio": {
        "name": "Portfolio Domini Locali",
        "price_range": "500-5000€ per dominio",
        "frequency": "one_time",
        "automation_level": "high",  # acquisto automatico + parking + vendita
        "current_mrr": 0,
        "potential_mrr": 2000,
        "notes": "Acquista .it locali (parmapizzeria.it, parmadentista.it) + rivendi"
    }
}

# ============================================================
# PRIORITIZZAZIONE PER IMPLEMENTAZIONE
# ============================================================

IMPLEMENTATION_PRIORITY = [
    # Quick wins (1-2 settimane)
    ("affiliate_commissions", "Alto ROI, basso sforzo, tracking links + webhook"),
    ("market_insights", "Sfrutta dati radar esistenti, report automatici"),
    ("domain_portfolio", "Acquisto automatico domini .it locali + rivendita"),
    ("reputation_management", "Monitoring recensioni + response templates"),
    
    # Medium (1-2 mesi)
    ("done_for_you_seo", "GMB + citations + content, alta domanda"),
    ("email_marketing_managed", "Riutilizza mailer infrastructure"),
    ("website_audit_tool", "Lead magnet per agenzie, self-service"),
    ("booking_system", "SaaS prenotazioni, riutilizza WhatsApp infra"),
    
    # Big bets (3-6 mesi)
    ("whitelabel_saas", "Multi-tenant platform, ricavi ricorrenti alti"),
    ("local_ad_network", "Cross-promo tra clienti esistenti"),
    ("training_course", "Prodotto one-time, margine 100%"),
    ("google_ads_management", "High ticket, richiede expertise"),
    ("social_media_management", "Content generation + scheduling"),
    ("website_audit_tool", "Lead magnet per vendere altri servizi"),
]

# ============================================================
# IMPLEMENTAZIONE MODULI
# ============================================================

class RevenueStreamManager:
    def __init__(self):
        self.db = DB_FILE
        self.init_db()
    
    def init_db(self):
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        
        # Revenue streams tracking
        c.execute("""
            CREATE TABLE IF NOT EXISTS revenue_streams (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stream_key TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                status TEXT DEFAULT 'planned',  -- planned, building, live, paused
                monthly_target REAL DEFAULT 0,
                current_mrr REAL DEFAULT 0,
                setup_cost REAL DEFAULT 0,
                monthly_cost REAL DEFAULT 0,
                automation_level TEXT,  -- low, medium, high
                notes TEXT,
                launched_at TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Revenue events per stream
        c.execute("""
            CREATE TABLE IF NOT EXISTS stream_revenue (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stream_key TEXT NOT NULL,
                amount REAL NOT NULL,
                type TEXT,  -- setup, monthly, one_time, commission
                client_id INTEGER,
                description TEXT,
                date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stream_key) REFERENCES revenue_streams(stream_key)
            )
        """)
        
        # Affiliate tracking
        c.execute("""
            CREATE TABLE IF NOT EXISTS affiliate_clicks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stream_key TEXT,
                affiliate_program TEXT,
                tracking_id TEXT,
                ip TEXT,
                user_agent TEXT,
                clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                converted_at TIMESTAMP,
                commission REAL
            )
        """)
        
        # Market insights reports
        c.execute("""
            CREATE TABLE IF NOT EXISTS market_reports (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                category TEXT,
                city TEXT DEFAULT 'Parma',
                content TEXT,
                price REAL,
                format TEXT DEFAULT 'pdf',  -- pdf, html, json
                status TEXT DEFAULT 'draft',  -- draft, published, sold
                sold_count INTEGER DEFAULT 0,
                revenue REAL DEFAULT 0,
                generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Domain portfolio
        c.execute("""
            CREATE TABLE IF NOT EXISTS domain_portfolio (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                domain TEXT UNIQUE NOT NULL,
                purchase_price REAL,
                purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                status TEXT DEFAULT 'parked',  -- parked, for_sale, sold, developed
                list_price REAL,
                sold_price REAL,
                sold_at TIMESTAMP,
                buyer_email TEXT,
                notes TEXT
            )
        """)
        
        conn.commit()
        conn.close()
    
    def register_stream(self, key: str, config: dict):
        """Registra un nuovo revenue stream."""
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute("""
            INSERT OR REPLACE INTO revenue_streams 
            (stream_key, name, status, monthly_target, current_mrr, setup_cost, 
             monthly_cost, automation_level, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            key, config["name"], "planned", 
            config.get("potential_mrr", 0) / 12,  # monthly target
            config.get("current_mrr", 0),
            0, 0, config.get("automation_level", "medium"),
            config.get("notes", "")
        ))
        conn.commit()
        conn.close()
    
    def log_revenue(self, stream_key: str, amount: float, rtype: str, 
                    client_id: int = None, description: str = ""):
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute("""
            INSERT INTO stream_revenue (stream_key, amount, type, client_id, description)
            VALUES (?, ?, ?, ?, ?)
        """, (stream_key, amount, rtype, client_id, description))
        conn.commit()
        conn.close()
    
    def get_stream_status(self, stream_key: str = None):
        conn = sqlite3.connect(self.db)
        conn.row_factory = sqlite3.Row
        c = conn.cursor()
        
        if stream_key:
            stream = c.execute("SELECT * FROM revenue_streams WHERE stream_key=?", (stream_key,)).fetchone()
            revenue = c.execute("""
                SELECT SUM(amount) as total, type 
                FROM stream_revenue WHERE stream_key=? 
                GROUP BY type
            """, (stream_key,)).fetchall()
            return dict(stream) if stream else None, [dict(r) for r in revenue]
        else:
            streams = c.execute("SELECT * FROM revenue_streams ORDER BY potential_mrr DESC").fetchall()
            return [dict(s) for s in streams], []

# ============================================================
# IMPLEMENTAZIONE SPECIFICA PER STREAM PRIORITARI
# ============================================================

class AffiliateManager:
    """Gestisce programmi affiliate e commissioni."""
    
    AFFILIATE_PROGRAMS = {
        "hosting": {
            "name": "Hosting Affiliate",
            "programs": [
                {"name": "SiteGround", "commission": "50-100€ per vendita", "url": "https://partner.siteground.com"},
                {"name": "Cloudways", "commission": "30% ricorrente", "url": "https://cloudways.partnerstack.com"},
                {"name": "Kinsta", "commission": "50-500€ per vendita", "url": "https://kinsta.com/affiliate/"},
            ],
            "tracking_param": "ref"
        },
        "domains": {
            "name": "Domain Registrar",
            "programs": [
                {"name": "Namecheap", "commission": "20% ricorrente", "url": "https://namecheap.pxf.io"},
                {"name": "Porkbun", "commission": "5$ per dominio", "url": "https://porkbun.com/affiliate"},
            ],
            "tracking_param": "aff"
        },
        "seo_tools": {
            "name": "SEO Tools",
            "programs": [
                {"name": "SEMrush", "commission": "200$ per vendita", "url": "https://semrush.com/affiliate"},
                {"name": "Ahrefs", "commission": "20% ricorrente", "url": "https://ahrefs.com/affiliate"},
                {"name": "Ubersuggest", "commission": "30% ricorrente", "url": "https://neilpatel.com/affiliate/"},
            ],
            "tracking_param": "via"
        },
        "email_marketing": {
            "name": "Email Marketing",
            "programs": [
                {"name": "ActiveCampaign", "commission": "20-30% ricorrente", "url": "https://activecampaign.com/affiliate"},
                {"name": "ConvertKit", "commission": "30% ricorrente", "url": "https://convertkit.com/affiliates"},
                {"name": "MailerLite", "commission": "30% ricorrente", "url": "https://mailerlite.com/affiliate"},
            ],
            "tracking_param": "ref"
        }
    }
    
    def __init__(self):
        self.db = DB_FILE
        self.init_db()
    
    def init_db(self):
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute("""
            CREATE TABLE IF NOT EXISTS affiliate_links (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stream_key TEXT,
                program_name TEXT,
                affiliate_url TEXT,
                tracking_id TEXT UNIQUE,
                clicks INTEGER DEFAULT 0,
                conversions INTEGER DEFAULT 0,
                total_commission REAL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        c.execute("""
            CREATE TABLE IF NOT EXISTS affiliate_clicks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                tracking_id TEXT,
                ip TEXT,
                user_agent TEXT,
                referrer TEXT,
                clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.commit()
        conn.close()
    
    def create_tracking_link(self, program_category: str, program_name: str, base_url: str) -> str:
        """Crea link di tracking per un programma affiliate."""
        import secrets
        tracking_id = secrets.token_urlsafe(16)
        
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute("""
            INSERT INTO affiliate_links (stream_key, program_name, affiliate_url, tracking_id)
            VALUES (?, ?, ?, ?)
        """, (f"affiliate_{program_category}", program_name, base_url, tracking_id))
        conn.commit()
        conn.close()
        
        # Il link finale: tuo_dominio.com/aff/{tracking_id} -> redirect a base_url?tracking_param={tracking_id}
        return f"https://svoraj.me/aff/{tracking_id}"
    
    def log_click(self, tracking_id: str, ip: str, user_agent: str, referrer: str = ""):
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute("""
            INSERT INTO affiliate_clicks (tracking_id, ip, user_agent, referrer)
            VALUES (?, ?, ?, ?)
        """, (tracking_id, ip, user_agent, referrer))
        c.execute("UPDATE affiliate_links SET clicks = clicks + 1 WHERE tracking_id = ?", (tracking_id,))
        conn.commit()
        conn.close()
    
    def log_conversion(self, tracking_id: str, commission: float):
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute("""
            UPDATE affiliate_links 
            SET conversions = conversions + 1, total_commission = total_commission + ?
            WHERE tracking_id = ?
        """, (commission, tracking_id))
        conn.commit()
        conn.close()


class MarketInsightsGenerator:
    """Genera report di market intelligence automatici dai dati radar."""
    
    REPORT_TYPES = {
        "new_openings": {
            "name": "Nuove Aperture Settimanali",
            "frequency": "weekly",
            "price": 50,
            "template": "new_openings_template.md"
        },
        "category_trends": {
            "name": "Trend per Categoria",
            "frequency": "monthly",
            "price": 100,
            "template": "category_trends_template.md"
        },
        "competitor_analysis": {
            "name": "Analisi Competitor",
            "frequency": "monthly",
            "price": 150,
            "template": "competitor_analysis_template.md"
        },
        "closure_alert": {
            "name": "Allarme Chiusure",
            "frequency": "weekly",
            "price": 75,
            "template": "closure_alert_template.md"
        },
        "market_report": {
            "name": "Report Mensile Mercato Locale",
            "frequency": "monthly",
            "price": 200,
            "template": "market_report_template.md"
        }
    }
    
    def __init__(self):
        self.db = DB_FILE
        self.init_db()
    
    def init_db(self):
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute("""
            CREATE TABLE IF NOT EXISTS market_reports (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                report_type TEXT NOT NULL,
                category TEXT,
                city TEXT DEFAULT 'Parma',
                content TEXT,
                price REAL,
                format TEXT DEFAULT 'pdf',
                status TEXT DEFAULT 'draft',
                sold_count INTEGER DEFAULT 0,
                revenue REAL DEFAULT 0,
                generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        c.execute("""
            CREATE TABLE IF NOT EXISTS report_sales (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                report_id INTEGER,
                buyer_email TEXT,
                amount REAL,
                sold_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (report_id) REFERENCES market_reports(id)
            )
        """)
        conn.commit()
        conn.close()
    
    def generate_weekly_openings_report(self):
        """Genera report nuove aperture dalla base lead."""
        conn = sqlite3.connect(BROKER_DB)
        c = conn.cursor()
        
        # Lead degli ultimi 7 giorni
        week_ago = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')
        leads = c.execute("""
            SELECT nome, categoria, indirizzo, fonte, data_scoperta, prezzo
            FROM leads_inventory 
            WHERE data_scoperta >= ? AND venduto = 0
            ORDER BY data_scoperta DESC
        """, (week_ago,)).fetchall()
        conn.close()
        
        if not leads:
            return None
        
        # Group by category
        by_cat = {}
        for lead in leads:
            cat = lead[1]
            if cat not in by_cat:
                by_cat[cat] = []
            by_cat[cat].append(lead)
        
        # Generate markdown report
        report = f"""# Nuove Aperture Parma - Settimana {datetime.now().strftime('%d/%m/%Y')}

**Totale nuove attività rilevate:** {len(leads)}

## Per Categoria
"""
        for cat, items in sorted(by_cat.items(), key=lambda x: len(x[1]), reverse=True):
            report += f"\n### {cat} ({len(items)} nuove)\n"
            for item in items[:10]:  # top 10 per categoria
                report += f"- **{item[0]}** - {item[2]} | Fonte: {item[3]} | Valore lead: €{item[5]}\n"
            if len(items) > 10:
                report += f"- *...e altri {len(items) - 10}*\n"
        
        report += f"\n---\n*Report generato automaticamente da Ghost Agency Radar v3 il {datetime.now().strftime('%d/%m/%Y %H:%M')}*"
        
        return report
    
    def save_report(self, report_type: str, content: str, title: str = None):
        """Salva report nel database."""
        if title is None:
            title = self.REPORT_TYPES[report_type]["name"]
        
        price = self.REPORT_TYPES[report_type]["price"]
        
        conn = sqlite3.connect(DB_FILE)
        c = conn.cursor()
        c.execute("""
            INSERT INTO market_reports (title, report_type, content, price, status)
            VALUES (?, ?, ?, ?, 'published')
        """, (title, report_type, content, price))
        report_id = c.lastrowid
        conn.commit()
        conn.close()
        return report_id


class DomainPortfolioManager:
    """Gestisce portfolio domini locali per rivendita."""
    
    TARGET_PATTERNS = [
        "{categoria}{citta}.it",
        "{categoria}-{citta}.it", 
        "{categoria}{citta}.com",
        "migliore{categoria}{citta}.it",
        "top{categoria}{citta}.it",
        "{categoria}{citta}online.it",
        "prenota{categoria}{citta}.it",
    ]
    
    CATEGORIES = [
        "pizzeria", "ristorante", "trattoria", "osteria", "paninoteca",
        "gelateria", "pasticceria", "caffetteria", "bar", "pub",
        "dentista", "ortodontista", "igienista", "chirurgo",
        "idraulico", "elettricista", "impianti", "termoidraulico",
        "autofficina", "carrozzeria", "gommista", "meccanico",
        "parrucchiere", "barbiere", "estetista", "centroestetico",
        "farmacia", "parafarmacia", "erboristeria", "ottico",
        "avvocato", "commercialista", "notaio", "geometra",
        "agenziaimmobiliare", "impresaedile", "impiantielettrici",
        "traslochi", "pulizie", "giardinaggio", "disinfestazione"
    ]
    
    CITIES = [
        "parma", "fidenza", "salsomaggiore", "noceto", "collecchio",
        "sorbolo", "traversetolo", "montechiarugolo", "fontanellato",
        "borgotaro", "bedonia", "compiano", "corniglio", "palanzano"
    ]
    
    def __init__(self):
        self.db = DB_FILE
        self.init_db()
    
    def init_db(self):
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute("""
            CREATE TABLE IF NOT EXISTS domain_portfolio (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                domain TEXT UNIQUE NOT NULL,
                pattern_used TEXT,
                category TEXT,
                city TEXT,
                purchase_price REAL,
                purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                expiry_date TIMESTAMP,
                status TEXT DEFAULT 'available',  -- available, registered, parked, for_sale, sold, developed
                list_price REAL,
                sold_price REAL,
                sold_at TIMESTAMP,
                buyer_email TEXT,
                registrar TEXT,
                nameservers TEXT,
                notes TEXT
            )
        """)
        c.execute("""
            CREATE TABLE IF NOT EXISTS domain_sales (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                domain_id INTEGER,
                sold_price REAL,
                buyer_email TEXT,
                sold_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                commission_paid REAL DEFAULT 0,
                FOREIGN KEY (domain_id) REFERENCES domain_portfolio(id)
            )
        """)
        conn.commit()
        conn.close()
    
    def generate_domain_list(self, max_domains: int = 1000) -> list:
        """Genera lista domini target."""
        import itertools
        domains = []
        
        for city in self.CITIES:
            for cat in self.CATEGORIES:
                for pattern in self.TARGET_PATTERNS:
                    domain = pattern.format(categoria=cat, citta=city)
                    domains.append({
                        "domain": domain,
                        "pattern_used": pattern,
                        "category": cat,
                        "city": city
                    })
                    if len(domains) >= max_domains:
                        return domains
        return domains
    
    def check_availability(self, domains: list) -> list:
        """Controlla disponibilità domini (mock - integrare con API registrar)."""
        import socket
        available = []
        for d in domains[:50]:  # Test solo primi 50
            try:
                socket.gethostbyname(d["domain"])
                d["available"] = False
            except socket.gaierror:
                d["available"] = True
                available.append(d)
            import time
            time.sleep(0.1)
        return available
    
    def register_domains(self, domains: list, registrar: str = "namecheap") -> int:
        """Registra domini disponibili (mock - integrare con API)."""
        registered = 0
        for d in domains:
            # Qui integrare API registrar (Namecheap, Porkbun, Cloudflare, etc.)
            print(f"[REGISTER] {d['domain']} via {registrar}")
            registered += 1
        return registered
    
    def auto_acquire_and_list(self, max_budget: float = 500) -> dict:
        """Pipeline automatica: genera -> controlla -> registra -> lista in vendita."""
        print(f"[DOMAIN] Generazione lista target...")
        domains = self.generate_domain_list(2000)
        print(f"[DOMAIN] Controllo disponibilità...")
        available = self.check_availability(domains)
        print(f"[DOMAIN] Disponibili: {len(available)}")
        
        # Filtra per budget (assume ~10€/dominio .it)
        affordable = [d for d in available[:int(max_budget/10)]]
        print(f"[DOMAIN] Registrazione {len(affordable)} domini (budget €{max_budget})...")
        
        registered = self.register_domains(affordable)
        
        # Lista in vendita
        for d in affordable:
            list_price = self.calculate_list_price(d)
            self.list_for_sale(d, list_price)
        
        return {
            "checked": len(domains[:50]),
            "available": len(available),
            "registered": registered,
            "listed": len(affordable)
        }
    
    def calculate_list_price(self, domain_info: dict) -> float:
        """Calcola prezzo di vendita basato su categoria/città."""
        base = 150
        premium_cats = ["pizzeria", "ristorante", "dentista", "avvocato", "farmacia"]
        premium_cities = ["parma", "fidenza", "salsomaggiore"]
        
        if domain_info["category"] in premium_cats:
            base += 200
        if domain_info["city"] in premium_cities:
            base += 100
        
        return base
    
    def list_for_sale(self, domain_info: dict, price: float):
        conn = sqlite3.connect(DB_FILE)
        c = conn.cursor()
        c.execute("""
            INSERT OR IGNORE INTO domain_portfolio 
            (domain, pattern_used, category, city, list_price, status)
            VALUES (?, ?, ?, ?, ?, 'for_sale')
        """, (domain_info["domain"], domain_info["pattern_used"], 
              domain_info["category"], domain_info["city"], price))
        conn.commit()
        conn.close()


# ============================================================
# MAIN - DEPLOY ALL NEW STREAMS
# ============================================================

def deploy_new_revenue_streams():
    """Deploya tutti i nuovi revenue streams."""
    print(f"\n{'='*60}")
    print(f" GHOST AGENCY - DEPLOY NUOVI REVENUE STREAMS")
    print(f"{'='*60}\n")
    
    # 1. Registra tutti gli stream
    manager = RevenueStreamManager()
    for key, config in REVENUE_STREAMS.items():
        manager.register_stream(key, config)
        print(f"[+] Registrato: {config['name']} (potenziale €{config['potential_mrr']}/mese)")
    
    # 2. Inizializza moduli specifici
    affiliate = AffiliateManager()
    insights = MarketInsightsGenerator()
    domains = DomainPortfolioManager()
    
    # 3. Genera primo report market insights
    print(f"\n[*] Generazione primo report market insights...")
    report = insights.generate_weekly_openings_report()
    if report:
        report_id = insights.save_report("new_openings", report)
        print(f"[+] Report generato: ID {report_id}")
    
    # 4. Genera link affiliate di esempio
    print(f"\n[*] Creazione link affiliate di esempio...")
    for cat, config in affiliate.AFFILIATE_PROGRAMS.items():
        for prog in config["programs"]:
            link = affiliate.create_tracking_link(cat, prog["name"], prog["url"])
            print(f"  [+] {prog['name']}: {link}")
    
    # 4. Avvia domain portfolio (dry run)
    print(f"\n[*] Avvio domain portfolio (dry run)...")
    result = domains.auto_acquire_and_list(max_budget=100)
    print(f"  Risultato: {result}")
    
    # 5. Report finale
    print(f"\n{'='*60}")
    print(f" DEPLOY COMPLETATO")
    print(f"{'='*60}")
    
    streams, _ = manager.get_stream_status()
    print(f"\nStream registrati: {len(streams)}")
    total_potential = sum(s.get('potential_mrr', 0) for s in REVENUE_STREAMS.values())
    print(f"Potenziale MRR totale: €{total_potential}/mese")
    print(f"MRR attuale: €{sum(s.get('current_mrr', 0) for s in REVENUE_STREAMS.values())}/mese")
    
    return {
        "streams_registered": len(streams),
        "potential_mrr": total_potential,
        "modules_initialized": ["affiliate", "market_insights", "domain_portfolio"]
    }


if __name__ == "__main__":
    deploy_new_revenue_streams()