#!/usr/bin/env python3
"""
Ghost Agency Admin API - Serves dashboard and provides admin endpoints
"""

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

from flask import Flask, request, jsonify, send_from_directory
from flask_cors import CORS

LEADS_DIR = Path("/home/ubuntu/GhostAgency")
DB_FILE = LEADS_DIR / "revenue_bots.db"
BROKER_DB = LEADS_DIR / "lead_broker.db"
ADMIN_KEY = os.getenv("ADMIN_API_KEY", "admin-secret-change-me")

app = Flask(__name__, static_folder=str(LEADS_DIR))
CORS(app)

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_KEY:
            return jsonify({"error": "Admin key required"}), 401
        return f(*args, **kwargs)
    return decorated

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

# ============================================================
# DASHBOARD SERVING
# ============================================================
@app.route('/')
def dashboard():
    return send_from_directory(str(LEADS_DIR), 'dashboard.html')

@app.route('/dashboard.html')
def dashboard_html():
    return send_from_directory(str(LEADS_DIR), 'dashboard.html')

# ============================================================
# ADMIN ENDPOINTS
# ============================================================

@app.route('/admin/stats')
@require_admin
def admin_stats():
    # Lead Broker stats
    broker_conn = get_db(BROKER_DB)
    total = broker_conn.execute("SELECT COUNT(*) FROM leads_inventory").fetchone()[0]
    available = broker_conn.execute("SELECT COUNT(*) FROM leads_inventory WHERE venduto=0").fetchone()[0]
    sold = broker_conn.execute("SELECT COUNT(*) FROM leads_inventory WHERE venduto=1").fetchone()[0]
    
    total_revenue = broker_conn.execute("SELECT SUM(prezzo) FROM sales").fetchone()[0] or 0
    total_commission = broker_conn.execute("SELECT SUM(commission_fee) FROM sales").fetchone()[0] or 0
    
    by_cat = broker_conn.execute("""
        SELECT categoria, COUNT(*) as count, SUM(prezzo) as value
        FROM leads_inventory WHERE venduto=0 GROUP BY categoria
    """).fetchall()
    
    # Revenue bots stats
    rev_conn = get_db(DB_FILE)
    mrr = rev_conn.execute("SELECT SUM(monthly_fee) FROM clients WHERE stato='attivo'").fetchone()[0] or 0
    
    # Upsell count
    upsell_count = rev_conn.execute("SELECT COUNT(*) FROM upsells WHERE stato='proposto'").fetchone()[0]
    
    # Renewal stats
    today = datetime.now().date()
    upcoming_renewals = rev_conn.execute("""
        SELECT COUNT(*) FROM renewals 
        WHERE stato='pending' AND date(scadenza) BETWEEN date('now') AND date('now', '+30 days')
    """).fetchone()[0]
    
    overdue_renewals = rev_conn.execute("""
        SELECT COUNT(*) FROM renewals 
        WHERE stato='overdue'
    """).fetchone()[0]
    
    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],
        "mrr": mrr,
        "upsell_proposals": upsell_count,
        "upcoming_renewals": upcoming_renewals,
        "overdue_renewals": overdue_renewals
    })

@app.route('/admin/leads')
@require_admin
def admin_leads():
    limit = min(int(request.args.get('limit', 50)), 200)
    offset = int(request.args.get('offset', 0))
    status = request.args.get('status')  # available, sold, all
    
    conn = get_db(BROKER_DB)
    
    query = "SELECT * FROM leads_inventory"
    params = []
    
    if status == 'available':
        query += " WHERE venduto = 0"
    elif status == 'sold':
        query += " WHERE venduto = 1"
    
    query += " ORDER BY data_scoperta DESC LIMIT ? OFFSET ?"
    params.extend([limit, offset])
    
    leads = conn.execute(query, params).fetchall()
    
    total = conn.execute("SELECT COUNT(*) FROM leads_inventory" + (" WHERE venduto=0" if status=='available' else (" WHERE venduto=1" if status=='sold' else ""))).fetchone()[0]
    
    return jsonify({
        "leads": [dict(row) for row in leads],
        "pagination": {"total": total, "limit": limit, "offset": offset}
    })

@app.route('/admin/clients')
@require_admin
def admin_clients():
    conn = get_db(DB_FILE)
    clients = conn.execute("""
        SELECT c.*, 
               (SELECT COUNT(*) FROM upsells WHERE client_id=c.id AND stato='proposto') as upsell_count,
               (SELECT MIN(scadenza) FROM renewals WHERE client_id=c.id AND stato='pending') as next_renewal
        FROM clients c
        WHERE c.stato='attivo'
        ORDER BY c.monthly_fee DESC
    """).fetchall()
    
    return jsonify({"clients": [dict(row) for row in clients]})

@app.route('/admin/upsells')
@require_admin
def admin_upsells():
    conn = get_db(DB_FILE)
    upsells = conn.execute("""
        SELECT u.*, c.nome as client_nome, c.email as client_email
        FROM upsells u
        JOIN clients c ON u.client_id = c.id
        ORDER BY u.proposto_at DESC
    """).fetchall()
    
    return jsonify({"upsells": [dict(row) for row in upsells]})

@app.route('/admin/renewals')
@require_admin
def admin_renewals():
    conn = get_db(DB_FILE)
    renewals = conn.execute("""
        SELECT r.*, c.nome as client_nome, c.email as client_email
        FROM renewals r
        JOIN clients c ON r.client_id = c.id
        ORDER BY r.scadenza ASC
    """).fetchall()
    
    return jsonify({"renewals": [dict(row) for row in renewals]})

@app.route('/admin/ingest', methods=['POST'])
@require_admin
def admin_ingest():
    """Importa lead da CSV radar."""
    from lead_broker_api import ingest_leads_from_csv
    
    data = request.get_json() or {}
    csv_path = data.get('csv_path')
    
    if not csv_path:
        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/run/<bot_name>', methods=['POST'])
@require_admin
def run_bot(bot_name):
    """Esegue un bot specifico."""
    bots = {
        'radar': '/home/ubuntu/GhostAgency/ghost_radar_v3.py',
        'mailer': '/home/ubuntu/GhostAgency/auto_mailer_v3.py',
        'call_followup': '/home/ubuntu/GhostAgency/call_followup_v3.py',
        'upsell': '/home/ubuntu/GhostAgency/revenue_bots.py',
        'renewal': '/home/ubuntu/GhostAgency/revenue_bots.py',
        'referral': '/home/ubuntu/GhostAgency/revenue_bots.py',
        'broker_ingest': '/home/ubuntu/GhostAgency/lead_broker_api.py',
    }
    
    if bot_name not in bots:
        return jsonify({"error": f"Unknown bot: {bot_name}"}), 404
    
    import subprocess
    script = bots[bot_name]
    
    # Run in background
    proc = subprocess.Popen(
        ['python3', script],
        cwd='/home/ubuntu/GhostAgency',
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE
    )
    
    return jsonify({
        "started": True,
        "bot": bot_name,
        "pid": proc.pid
    })

# ============================================================
# REVENUE REPORT ENDPOINT
# ============================================================
@app.route('/api/revenue/report')
def revenue_report():
    conn = get_db(DB_FILE)
    
    mrr = conn.execute("SELECT SUM(monthly_fee) FROM clients WHERE stato='attivo'").fetchone()[0] or 0
    
    setup_month = conn.execute("""
        SELECT SUM(importo) FROM revenue_events 
        WHERE tipo='setup' AND data > date('now', 'start of month')
    """).fetchone()[0] or 0
    
    upsell_mrr = conn.execute("""
        SELECT SUM(importo_mensile) FROM upsells 
        WHERE stato='accettato' AND accettato_at > date('now', 'start of month')
    """).fetchone()[0] or 0
    
    renewals_month = conn.execute("""
        SELECT SUM(importo) FROM renewals 
        WHERE stato='paid' AND pagato_at > date('now', 'start of month')
    """).fetchone()[0] or 0
    
    referrals_paid = conn.execute("SELECT COUNT(*) FROM referrals WHERE pagato=1").fetchone()[0] or 0
    referral_cost = referrals_paid * 100
    
    return jsonify({
        "mrr": mrr,
        "setup_month": setup_month,
        "upsell_mrr": upsell_mrr,
        "renewals_month": renewals_month,
        "referrals_paid": referrals_paid,
        "referral_cost": referral_cost,
        "net_month": setup_month + renewals_month - referral_cost,
        "projected_mrr": mrr + upsell_mrr
    })

# ============================================================
# STATIC FILES
# ============================================================
@app.route('/static/<path:filename>')
def static_files(filename):
    return send_from_directory(str(LEADS_DIR), filename)

if __name__ == '__main__':
    print("Starting Ghost Agency Admin API on port 8081")
    app.run(host='0.0.0.0', port=8081, debug=False)