#!/usr/bin/env python3
"""
Standalone Lead Ingestion Script - No Flask context needed
"""

import os
import csv
import sqlite3
import hashlib
from pathlib import Path
from datetime import datetime

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

def get_db():
    conn = sqlite3.connect(BROKER_DB)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    c = conn.cursor()
    
    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
        )
    """)
    
    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
        )
    """)
    
    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',
            delivery_at TIMESTAMP
        )
    """)
    
    conn.commit()
    conn.close()

def ingest_leads_from_csv(csv_path):
    """Importa lead da CSV radar nel database inventory."""
    conn = 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 = conn.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:
                conn.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
    
    conn.commit()
    conn.close()
    return imported, skipped

def main():
    init_db()
    
    csvs = list(Path("/home/ubuntu/GhostAgency").glob("leads_parma_*_local.csv"))
    if not csvs:
        print("No CSV found")
        return
    
    latest = max(csvs, key=lambda p: p.stat().st_mtime)
    imported, skipped = ingest_leads_from_csv(latest)
    print(f"Imported: {imported}, Skipped: {skipped}")
    print(f"CSV: {latest}")

if __name__ == "__main__":
    main()