#!/usr/bin/env python3
"""
Ghost Agency Lead Broker API - Sell leads to other agencies
- REST API per vendere lead (€10-50/lead)
- Autenticazione API key per buyer
- Filtri: categoria, città, ha_email, ha_telefono
- Tracking vendite, revenue, delivery
- Webhook per notifiche buyer
"""

import os
import csv
import json
import hashlib
import sqlite3
import secrets
from pathlib import Path
from datetime import datetime, timedelta
from functools import wraps

from flask import Flask, request, jsonify, g
from flask_limiter import Limiter
from flask_limiter.util import get_remote_address

# Config
LEADS_DIR = Path("/home/ubuntu/GhostAgency")
DB_FILE = LEADS_DIR / "lead_broker.db"
API_PORT = int(os.getenv("LEAD_BROKER_PORT", "8080"))

app = Flask(__name__)

# Rate limiting
limiter = Limiter(
    get_remote_address,
    app=app,
    default_limits=["200 per day", "50 per hour"],
    storage_uri="memory://"
)

# ============================================================
# DATABASE
# ============================================================
def init_db():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    
    # Buyers (agenzie che comprano lead)
    c.execute("""
        CREATE TABLE IF NOT EXISTS buyers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            api_key TEXT UNIQUE NOT NULL,
            webhook_url TEXT,
            webhook_secret TEXT,
            monthly_limit INTEGER DEFAULT 1000,
            price_per_lead REAL DEFAULT 25.0,
            active BOOLEAN DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # Leads inventory
    c.execute("""
        CREATE TABLE IF NOT EXISTS leads_inventory (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            lead_hash TEXT UNIQUE NOT NULL,
            nome TEXT NOT NULL,
            email TEXT,
            telefono TEXT,
            indirizzo TEXT,
            categoria TEXT,
            citta TEXT DEFAULT 'Parma',
            fonte TEXT,
            data_scoperta TIMESTAMP,
            prezzo REAL DEFAULT 25.0,
            venduto BOOLEAN DEFAULT 0,
            venduto_at TIMESTAMP,
            buyer_id INTEGER,
            FOREIGN KEY (buyer_id) REFERENCES buyers(id)
        )
    """)
    
    # Sales transactions
    c.execute("""
        CREATE TABLE IF NOT EXISTS sales (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            lead_id INTEGER,
            buyer_id INTEGER,
            prezzo REAL,
            commission_fee REAL DEFAULT 0,
            netto REAL,
            venduto_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            delivery_status TEXT DEFAULT 'pending',  -- pending, delivered, failed
            delivery_at TIMESTAMP,
            FOREIGN KEY (lead_id) REFERENCES leads_inventory(id),
            FOREIGN KEY (buyer_id) REFERENCES buyers(id)
        )
    """)
    
    # API usage tracking
    c.execute("""
        CREATE TABLE IF NOT EXISTS api_usage (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            buyer_id INTEGER,
            endpoint TEXT,
            method TEXT,
            status_code INTEGER,
            response_time_ms INTEGER,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (buyer_id) REFERENCES buyers(id)
        )
    """)
    
    # Webhook deliveries
    c.execute("""
        CREATE TABLE IF NOT EXISTS webhook_deliveries (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            buyer_id INTEGER,
            sale_id INTEGER,
            payload TEXT,
            response_code INTEGER,
            success BOOLEAN,
            attempts INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (buyer_id) REFERENCES buyers(id),
            FOREIGN KEY (sale_id) REFERENCES sales(id)
        )
    """)
    
    conn.commit()
    conn.close()

def get_db():
    try:
        from flask import has_app_context
        if has_app_context():
            if 'db' not in g:
                g.db = sqlite3.connect(DB_FILE)
                g.db.row_factory = sqlite3.Row
            return g.db
    except Exception:
        pass
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn

@app.teardown_appcontext
def close_db(error):
    db = g.pop('db', None)
    if db is not None:
        db.close()

# ============================================================
# AUTH DECORATOR
# ============================================================
def require_api_key(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        api_key = request.headers.get('X-API-Key') or request.args.get('api_key')
        if not api_key:
            return jsonify({"error": "API key required", "code": "MISSING_API_KEY"}), 401
        
        db = get_db()
        buyer = db.execute("SELECT * FROM buyers WHERE api_key=? AND active=1", (api_key,)).fetchone()
        if not buyer:
            return jsonify({"error": "Invalid API key", "code": "INVALID_API_KEY"}), 401
        
        g.buyer = buyer
        g.buyer_id = buyer['id']
        
        # Check monthly limit
        current_month = datetime.now().strftime('%Y-%m')
        usage = db.execute("""
            SELECT COUNT(*) as cnt FROM sales 
            WHERE buyer_id=? AND strftime('%Y-%m', venduto_at) = ?
        """, (buyer['id'], current_month)).fetchone()
        
        if usage['cnt'] >= buyer['monthly_limit']:
            return jsonify({
                "error": "Monthly limit reached",
                "code": "MONTHLY_LIMIT_EXCEEDED",
                "limit": buyer['monthly_limit'],
                "used": usage['cnt']
            }), 429
        
        return f(*args, **kwargs)
    return decorated

def log_api_usage(endpoint, method, status_code, response_time_ms):
    if hasattr(g, 'buyer_id'):
        db = get_db()
        db.execute("""
            INSERT INTO api_usage (buyer_id, endpoint, method, status_code, response_time_ms)
            VALUES (?, ?, ?, ?, ?)
        """, (g.buyer_id, endpoint, method, status_code, response_time_ms))
        db.commit()

# ============================================================
# LEAD INGESTION (from radar)
# ============================================================
def ingest_leads_from_csv(csv_path):
    """Importa lead da CSV radar nel database inventory."""
    db = get_db()
    imported = 0
    skipped = 0
    
    with open(csv_path, newline='', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            nome = row.get('Nome', '').strip()
            email = row.get('Email', '').strip()
            telefono = row.get('Telefono', '').strip()
            indirizzo = row.get('Indirizzo', '').strip()
            categoria = row.get('Categoria', '').strip()
            fonte = row.get('Fonte', 'radar')
            data = row.get('Data', datetime.now().strftime('%Y-%m-%d'))
            
            if not nome:
                continue
            
            # Create unique hash
            lead_hash = hashlib.md5(f"{nome}{telefono}{email}".encode()).hexdigest()[:16]
            
            # Check if exists
            existing = db.execute("SELECT id FROM leads_inventory WHERE lead_hash=?", (lead_hash,)).fetchone()
            if existing:
                skipped += 1
                continue
            
            # Calculate price based on data completeness
            prezzo = 15.0  # base
            if email and '@' in email:
                prezzo += 10.0
            if telefono:
                prezzo += 5.0
            if categoria in ['Ristoranti', 'Pizzerie', 'Autofficine', 'Dentisti']:
                prezzo += 5.0  # high value categories
            
            prezzo = min(prezzo, 50.0)  # cap at 50
            
            try:
                db.execute("""
                    INSERT INTO leads_inventory 
                    (lead_hash, nome, email, telefono, indirizzo, categoria, citta, fonte, data_scoperta, prezzo)
                    VALUES (?, ?, ?, ?, ?, ?, 'Parma', ?, ?, ?)
                """, (lead_hash, nome, email, telefono, indirizzo, categoria, fonte, data, prezzo))
                imported += 1
            except sqlite3.IntegrityError:
                skipped += 1
    
    db.commit()
    return imported, skipped

# ============================================================
# API ROUTES
# ============================================================

@app.route('/health', methods=['GET'])
def health():
    return jsonify({"status": "ok", "service": "ghost-agency-lead-broker", "version": "1.0"})

@app.route('/api/leads/available', methods=['GET'])
@require_api_key
@limiter.limit("30 per minute")
def get_available_leads():
    """Lista lead disponibili per acquisto con filtri."""
    start = time.time()
    
    db = get_db()
    
    # Filtri
    categoria = request.args.get('categoria')
    citta = request.args.get('citta', 'Parma')
    has_email = request.args.get('has_email')
    has_telefono = request.args.get('has_telefono')
    min_price = request.args.get('min_price', type=float)
    max_price = request.args.get('max_price', type=float)
    limit = min(int(request.args.get('limit', 50)), 200)
    offset = int(request.args.get('offset', 0))
    
    query = """
        SELECT id, nome, email, telefono, indirizzo, categoria, citta, 
               fonte, data_scoperta, prezzo
        FROM leads_inventory 
        WHERE venduto = 0 AND citta = ?
    """
    params = [citta]
    
    if categoria:
        query += " AND categoria = ?"
        params.append(categoria)
    
    if has_email == 'true':
        query += " AND email IS NOT NULL AND email != ''"
    elif has_email == 'false':
        query += " AND (email IS NULL OR email = '')"
    
    if has_telefono == 'true':
        query += " AND telefono IS NOT NULL AND telefono != ''"
    elif has_telefono == 'false':
        query += " AND (telefono IS NULL OR telefono = '')"
    
    if min_price is not None:
        query += " AND prezzo >= ?"
        params.append(min_price)
    
    if max_price is not None:
        query += " AND prezzo <= ?"
        params.append(max_price)
    
    query += " ORDER BY prezzo DESC, data_scoperta DESC LIMIT ? OFFSET ?"
    params.extend([limit, offset])
    
    leads = db.execute(query, params).fetchall()
    
    # Count total for pagination
    count_query = query.replace("SELECT id, nome, email, telefono, indirizzo, categoria, citta, fonte, data_scoperta, prezzo", "SELECT COUNT(*)")
    count_query = count_query.replace("ORDER BY prezzo DESC, data_scoperta DESC LIMIT ? OFFSET ?", "")
    total = db.execute(count_query, params[:-2]).fetchone()[0]
    
    result = {
        "leads": [dict(row) for row in leads],
        "pagination": {
            "total": total,
            "limit": limit,
            "offset": offset,
            "has_more": (offset + limit) < total
        }
    }
    
    log_api_usage('/api/leads/available', 'GET', 200, int((time.time() - start) * 1000))
    return jsonify(result)

@app.route('/api/leads/<int:lead_id>', methods=['GET'])
@require_api_key
@limiter.limit("60 per minute")
def get_lead_detail(lead_id):
    """Dettaglio singolo lead."""
    start = time.time()
    db = get_db()
    
    lead = db.execute("SELECT * FROM leads_inventory WHERE id=? AND venduto=0", (lead_id,)).fetchone()
    
    if not lead:
        log_api_usage(f'/api/leads/{lead_id}', 'GET', 404, int((time.time() - start) * 1000))
        return jsonify({"error": "Lead not found or already sold", "code": "LEAD_NOT_FOUND"}), 404
    
    log_api_usage(f'/api/leads/{lead_id}', 'GET', 200, int((time.time() - start) * 1000))
    return jsonify(dict(lead))

@app.route('/api/leads/purchase', methods=['POST'])
@require_api_key
@limiter.limit("10 per minute")
def purchase_leads():
    """Acquista uno o più lead."""
    start = time.time()
    db = get_db()
    buyer_id = g.buyer_id
    
    data = request.get_json()
    if not data or 'lead_ids' not in data:
        return jsonify({"error": "lead_ids required", "code": "MISSING_LEAD_IDS"}), 400
    
    lead_ids = data['lead_ids']
    if not isinstance(lead_ids, list) or not lead_ids:
        return jsonify({"error": "lead_ids must be non-empty array", "code": "INVALID_LEAD_IDS"}), 400
    
    if len(lead_ids) > 50:
        return jsonify({"error": "Max 50 leads per purchase", "code": "TOO_MANY_LEADS"}), 400
    
    # Check monthly limit
    current_month = datetime.now().strftime('%Y-%m')
    usage = db.execute("""
        SELECT COUNT(*) as cnt FROM sales 
        WHERE buyer_id=? AND strftime('%Y-%m', venduto_at) = ?
    """, (buyer_id, current_month)).fetchone()
    
    buyer = db.execute("SELECT monthly_limit FROM buyers WHERE id=?", (buyer_id,)).fetchone()
    if usage['cnt'] + len(lead_ids) > buyer['monthly_limit']:
        return jsonify({
            "error": "Monthly limit would be exceeded",
            "code": "MONTHLY_LIMIT_EXCEEDED",
            "limit": buyer['monthly_limit'],
            "used": usage['cnt'],
            "requested": len(lead_ids)
        }), 429
    
    # Verify leads are available
    placeholders = ','.join('?' * len(lead_ids))
    leads = db.execute(f"""
        SELECT id, prezzo FROM leads_inventory 
        WHERE id IN ({placeholders}) AND venduto = 0
    """, lead_ids).fetchall()
    
    if len(leads) != len(lead_ids):
        return jsonify({"error": "Some leads not available or already sold", "code": "LEADS_UNAVAILABLE"}), 409
    
    # Process purchase
    total = 0
    sales = []
    
    for lead in leads:
        lead_id, prezzo = lead['id'], lead['prezzo']
        
        # Mark as sold
        db.execute("UPDATE leads_inventory SET venduto=1, venduto_at=?, buyer_id=? WHERE id=?", 
                   (datetime.now().isoformat(), g.buyer_id, lead_id))
        
        # Create sale record
        commission = prezzo * 0.1  # 10% platform fee
        netto = prezzo - commission
        
        cursor = db.execute("""
            INSERT INTO sales (lead_id, buyer_id, prezzo, commission_fee, netto, delivery_status)
            VALUES (?, ?, ?, ?, ?, 'delivered')
        """, (lead_id, buyer_id, prezzo, commission, netto))
        
        sale_id = cursor.lastrowid
        
        # Update inventory with buyer
        db.execute("UPDATE leads_inventory SET buyer_id=? WHERE id=?", (buyer_id, lead_id))
        
        sales.append({
            "sale_id": sale_id,
            "lead_id": lead_id,
            "prezzo": prezzo,
            "commission": commission,
            "netto": netto
        })
        total += prezzo
    
    db.commit()
    
    log_api_usage('/api/leads/purchase', 'POST', 200, int((time.time() - start) * 1000))
    
    return jsonify({
        "success": True,
        "purchased": len(sales),
        "total": total,
        "sales": sales
    })

@app.route('/api/leads/stats', methods=['GET'])
@require_api_key
@limiter.limit("30 per minute")
def get_stats():
    """Statistiche lead disponibili per categoria."""
    start = time.time()
    db = get_db()
    citta = request.args.get('citta', 'Parma')
    
    # Totali per categoria
    by_category = db.execute("""
        SELECT categoria, COUNT(*) as count, AVG(prezzo) as avg_price, SUM(prezzo) as total_value
        FROM leads_inventory 
        WHERE venduto = 0 AND citta = ?
        GROUP BY categoria
        ORDER BY count DESC
    """, (citta,)).fetchall()
    
    # Totali per fonte
    by_source = db.execute("""
        SELECT fonte, COUNT(*) as count
        FROM leads_inventory 
        WHERE venduto = 0 AND citta = ?
        GROUP BY fonte
    """, (citta,)).fetchall()
    
    # Email vs no email
    email_stats = db.execute("""
        SELECT 
            SUM(CASE WHEN email IS NOT NULL AND email != '' THEN 1 ELSE 0 END) as with_email,
            SUM(CASE WHEN email IS NULL OR email = '' THEN 1 ELSE 0 END) as without_email
        FROM leads_inventory 
        WHERE venduto = 0 AND citta = ?
    """, (citta,)).fetchone()
    
    # Venduti questo mese
    current_month = datetime.now().strftime('%Y-%m')
    sold_month = db.execute("""
        SELECT COUNT(*) as count, SUM(prezzo) as revenue
        FROM sales 
        WHERE buyer_id = ? AND strftime('%Y-%m', venduto_at) = ?
    """, (g.buyer_id, current_month)).fetchone()
    
    result = {
        "by_category": [dict(row) for row in by_category],
        "by_source": [dict(row) for row in by_source],
        "email_stats": dict(email_stats) if email_stats else {},
        "monthly_purchases": dict(sold_month) if sold_month else {"count": 0, "revenue": 0}
    }
    
    log_api_usage('/api/leads/stats', 'GET', 200, int((time.time() - start) * 1000))
    return jsonify(result)

@app.route('/api/account', methods=['GET'])
@require_api_key
@limiter.limit("30 per minute")
def get_account():
    """Info account buyer."""
    start = time.time()
    db = get_db()
    
    buyer = db.execute("SELECT id, name, email, monthly_limit, price_per_lead, created_at FROM buyers WHERE id=?", 
                       (g.buyer_id,)).fetchone()
    
    current_month = datetime.now().strftime('%Y-%m')
    usage = db.execute("""
        SELECT COUNT(*) as cnt, SUM(prezzo) as spent
        FROM sales 
        WHERE buyer_id=? AND strftime('%Y-%m', venduto_at) = ?
    """, (g.buyer_id, current_month)).fetchone()
    
    result = {
        "account": dict(buyer) if buyer else {},
        "current_month_usage": {
            "leads_purchased": usage['cnt'] if usage else 0,
            "amount_spent": usage['spent'] if usage else 0,
            "limit": buyer['monthly_limit'] if buyer else 0,
            "remaining": (buyer['monthly_limit'] - usage['cnt']) if buyer and usage else 0
        }
    }
    
    log_api_usage('/api/account', 'GET', 200, int((time.time() - start) * 1000))
    return jsonify(result)

# ============================================================
# ADMIN ROUTES (per te)
# ============================================================
ADMIN_API_KEY = os.getenv("LEAD_BROKER_ADMIN_KEY", "admin-secret-change-me")

def require_admin(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        admin_key = request.headers.get('X-Admin-Key') or request.args.get('admin_key')
        if admin_key != ADMIN_API_KEY:
            return jsonify({"error": "Admin key required"}), 401
        return f(*args, **kwargs)
    return decorated

@app.route('/admin/buyers', methods=['POST'])
@require_admin
def create_buyer():
    """Crea nuovo buyer (agenzia)."""
    db = get_db()
    data = request.get_json()
    
    required = ['name', 'email']
    for field in required:
        if field not in data:
            return jsonify({"error": f"Missing {field}"}), 400
    
    api_key = secrets.token_urlsafe(32)
    webhook_secret = secrets.token_urlsafe(16)
    
    try:
        cursor = db.execute("""
            INSERT INTO buyers (name, email, api_key, webhook_url, webhook_secret, 
                               monthly_limit, price_per_lead)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (
            data['name'], data['email'], api_key,
            data.get('webhook_url'), webhook_secret,
            data.get('monthly_limit', 1000),
            data.get('price_per_lead', 25.0)
        ))
        buyer_id = cursor.lastrowid
        db.commit()
        
        return jsonify({
            "success": True,
            "buyer_id": buyer_id,
            "api_key": api_key,
            "webhook_secret": webhook_secret
        })
    except sqlite3.IntegrityError:
        return jsonify({"error": "Email already exists"}), 409

@app.route('/admin/buyers', methods=['GET'])
@require_admin
def list_buyers():
    db = get_db()
    buyers = db.execute("SELECT id, name, email, active, monthly_limit, price_per_lead, created_at FROM buyers").fetchall()
    return jsonify({"buyers": [dict(b) for b in buyers]})

@app.route('/admin/ingest', methods=['POST'])
@require_admin
def admin_ingest():
    """Importa lead da CSV radar."""
    data = request.get_json()
    csv_path = data.get('csv_path')
    
    if not csv_path:
        # Auto-detect latest
        csvs = list(LEADS_DIR.glob("leads_parma_*_local.csv"))
        if not csvs:
            return jsonify({"error": "No CSV found"}), 404
        csv_path = str(max(csvs, key=lambda p: p.stat().st_mtime))
    
    if not os.path.exists(csv_path):
        return jsonify({"error": "CSV not found"}), 404
    
    imported, skipped = ingest_leads_from_csv(csv_path)
    return jsonify({"imported": imported, "skipped": skipped, "csv": csv_path})

@app.route('/admin/stats', methods=['GET'])
@require_admin
def admin_stats():
    db = get_db()
    
    # Inventory stats
    total = db.execute("SELECT COUNT(*) FROM leads_inventory").fetchone()[0]
    available = db.execute("SELECT COUNT(*) FROM leads_inventory WHERE venduto=0").fetchone()[0]
    sold = db.execute("SELECT COUNT(*) FROM leads_inventory WHERE venduto=1").fetchone()[0]
    
    # Revenue
    total_revenue = db.execute("SELECT SUM(prezzo) FROM sales").fetchone()[0] or 0
    total_commission = db.execute("SELECT SUM(commission_fee) FROM sales").fetchone()[0] or 0
    
    # By category
    by_cat = db.execute("""
        SELECT categoria, COUNT(*) as count, SUM(prezzo) as value
        FROM leads_inventory WHERE venduto=0 GROUP BY categoria
    """).fetchall()
    
    return jsonify({
        "inventory": {
            "total": total,
            "available": available,
            "sold": sold
        },
        "revenue": {
            "total": total_revenue,
            "commission": total_commission,
            "net": total_revenue - total_commission
        },
        "by_category": [dict(row) for row in by_cat]
    })

# ============================================================
# WEBHOOK DELIVERY
# ============================================================
def deliver_webhook(buyer_id, sale_id, payload):
    """Invia webhook al buyer con retry."""
    db = get_db()
    buyer = db.execute("SELECT webhook_url, webhook_secret FROM buyers WHERE id=?", (buyer_id,)).fetchone()
    
    if not buyer or not buyer['webhook_url']:
        return False
    
    import hmac
    signature = hmac.new(
        buyer['webhook_secret'].encode(),
        json.dumps(payload).encode(),
        hashlib.sha256
    ).hexdigest()
    
    headers = {
        "Content-Type": "application/json",
        "X-Ghost-Signature": f"sha256={signature}",
        "X-Ghost-Event": "lead.purchased"
    }
    
    max_attempts = 3
    for attempt in range(max_attempts):
        try:
            resp = requests.post(buyer['webhook_url'], json=payload, headers=headers, timeout=10)
            success = resp.status_code == 200
            
            db.execute("""
                INSERT INTO webhook_deliveries (buyer_id, sale_id, payload, response_code, success, attempts)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (buyer_id, sale_id, json.dumps(payload), resp.status_code, success, attempt + 1))
            db.commit()
            
            if success:
                return True
        except Exception as e:
            db.execute("""
                INSERT INTO webhook_deliveries (buyer_id, sale_id, payload, response_code, success, attempts)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (buyer_id, sale_id, json.dumps(payload), 0, False, attempt + 1))
            db.commit()
        
        time.sleep(2 ** attempt)  # exponential backoff
    
    return False

# ============================================================
# MAIN
# ============================================================
if __name__ == '__main__':
    init_db()
    print(f"Starting Lead Broker API on port {API_PORT}")
    app.run(host='0.0.0.0', port=API_PORT, debug=False)