#!/usr/bin/env python3
"""
Real Agency Funnel API
- Lead capture
- Email queue
- Stripe webhooks
- Member delivery
"""

import sqlite3
import json
import smtplib
import hashlib
import secrets
from datetime import datetime
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from fastapi import FastAPI, Request, HTTPException
from fastapi.middleware.cors import CORSMiddleware
import uvicorn
import asyncio

app = FastAPI(title="Agency Accelerator API")

# Enable CORS
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["*"],
    allow_headers=["*"],
)

# Database setup
DB_PATH = "/home/opc/agencyfunnel/data.db"

def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    
    # Leads table
    c.execute('''CREATE TABLE IF NOT EXISTS leads (
        id INTEGER PRIMARY KEY,
        email TEXT UNIQUE NOT NULL,
        name TEXT,
        source TEXT,
        status TEXT DEFAULT 'captured',
        smtp_verified BOOLEAN DEFAULT 0,
        stripe_customer_id TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_email_sent TIMESTAMP,
        total_opens INTEGER DEFAULT 0,
        total_clicks INTEGER DEFAULT 0
    )''')
    
    # Purchases table
    c.execute('''CREATE TABLE IF NOT EXISTS purchases (
        id INTEGER PRIMARY KEY,
        lead_id INTEGER,
        stripe_payment_id TEXT UNIQUE,
        amount INTEGER,
        currency TEXT DEFAULT 'eur',
        status TEXT DEFAULT 'pending',
        delivered BOOLEAN DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (lead_id) REFERENCES leads(id)
    )''')
    
    # Email queue
    c.execute('''CREATE TABLE IF NOT EXISTS email_queue (
        id INTEGER PRIMARY KEY,
        lead_id INTEGER,
        email_type TEXT,
        subject TEXT,
        body TEXT,
        scheduled_at TIMESTAMP,
        sent_at TIMESTAMP,
        opened_at TIMESTAMP,
        clicked_at TIMESTAMP,
        status TEXT DEFAULT 'pending'
    )''')
    
    # Access tokens
    c.execute('''CREATE TABLE IF NOT EXISTS access_tokens (
        id INTEGER PRIMARY KEY,
        lead_id INTEGER,
        token TEXT UNIQUE,
        expires_at TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (lead_id) REFERENCES leads(id)
    )''')
    
    # Activity log
    c.execute('''CREATE TABLE IF NOT EXISTS activity_log (
        id INTEGER PRIMARY KEY,
        lead_id INTEGER,
        action TEXT,
        metadata TEXT,
        ip_address TEXT,
        user_agent TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )''')
    
    conn.commit()
    conn.close()

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

@app.on_event("startup")
async def startup():
    init_db()
    print("✓ Database initialized")

@app.post("/api/capture")
async def capture_lead(request: Request):
    """Capture email from squeeze page"""
    data = await request.json()
    email = data.get("email", "").lower().strip()
    source = data.get("source", "squeeze")
    
    if not email or "@" not in email:
        raise HTTPException(400, "Invalid email")
    
    conn = get_db()
    c = conn.cursor()
    
    try:
        c.execute("INSERT OR IGNORE INTO leads (email, source) VALUES (?, ?)", 
                  (email, source))
        conn.commit()
        
        c.execute("SELECT id FROM leads WHERE email = ?", (email,))
        lead_id = c.fetchone()["id"]
        
        # Log activity
        c.execute("""INSERT INTO activity_log 
            (lead_id, action, metadata, ip_address, user_agent)
            VALUES (?, 'captured', ?, ?, ?)""",
            (lead_id, json.dumps({"source": source}),
             request.client.host,
             request.headers.get("user-agent", "")))
        
        conn.commit()
        
        # Queue welcome email
        queue_welcome_email(lead_id, email)
        
        return {"success": True, "lead_id": lead_id}
        
    except Exception as e:
        return {"success": False, "error": str(e)}
    finally:
        conn.close()

def queue_welcome_email(lead_id, email):
    """Queue immediate welcome email"""
    conn = get_db()
    c = conn.cursor()
    
    subject = "The Agency Automation Audit (Inside)"
    
    body = f"""
    <h2>Hey,</h2>
    
    <p>You just grabbed the free Agency Automation Audit.</p>
    
    <p><strong>Here's your actual framework:</strong></p>
    
    <h3>The 3 Systems:</h3>
    <ol>
        <li><strong>Systematize Before Automate</strong> - Document the manual 80%, then build</li>
        <li><strong>Template-First, Not Build-From-Scratch</strong> - 47 battle-tested n8n workflows</li>
        <li><strong>Client Delivery System</strong> - Deploy in 1 click, not 40 hours</li>
    </ol>
    
    <p>Next email in 24 hours: The exact breakdown of how I went from $5K/month at 80 hours to $15K at 20 hours.</p>
    
    <p>- [Name]</p>
    
    <hr>
    <p><small>P.S. I've limited the Accelerator to 50 founders. <a href="https://openclaw-a1-flex.tailc991ce.ts.net/pro/vsl.html">See if spots are left →</a></small></p>
    """
    
    c.execute("""INSERT INTO email_queue 
        (lead_id, email_type, subject, body, scheduled_at, status)
        VALUES (?, 'welcome', ?, ?, datetime('now'), 'pending')""",
        (lead_id, subject, body))
    
    conn.commit()
    conn.close()

@app.get("/api/campaign/lifetime-value")
async def get_campaign_stats():
    """Real funnel metrics"""
    conn = get_db()
    c = conn.cursor()
    
    metrics = {}
    
    c.execute("SELECT COUNT(*) as total FROM leads")
    metrics["total_leads"] = c.fetchone()["total"]
    
    c.execute("SELECT COUNT(*) as total FROM purchases WHERE status = 'completed'")
    metrics["total_customers"] = c.fetchone()["total"]
    
    c.execute("SELECT COALESCE(SUM(amount), 0) as revenue FROM purchases WHERE status = 'completed'")
    metrics["total_revenue_cents"] = c.fetchone()["revenue"]
    
    c.execute("""SELECT COUNT(*) FROM email_queue 
        WHERE status = 'sent'""")
    metrics["total_emails_sent"] = c.fetchone()[0]
    
    c.execute("""SELECT COUNT(*) FROM email_queue 
        WHERE status = 'sent' AND clicked_at IS NOT NULL""")
    metrics["total_clicks"] = c.fetchone()[0]
    
    return metrics

@app.get("/api/campaign/daily")
async def get_daily_stats():
    """Daily breakdown"""
    conn = get_db()
    c = conn.cursor()
    
    c.execute("""
        SELECT 
            date(created_at) as day,
            COUNT(*) as new_leads,
            COUNT(DISTINCT stripe_customer_id) as new_customers
        FROM leads
        GROUP BY date(created_at)
        ORDER BY day DESC
        LIMIT 30
    """)
    
    return [dict(row) for row in c.fetchall()]

# Using FastAPI instead of Flask
if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8089)