#!/usr/bin/env python3
"""
ReceptionistAI Tradie Outreach Automation
==========================================
Pulls uncontacted leads from PostgreSQL, generates personalised
cold emails using the Danny Harris math formula, and sends via
n8n webhook. Tracks opens/replies back to PostgreSQL.

Usage:
    python tradie_outreach_automation.py --run           # Send batch
    python tradie_outreach_automation.py --dry-run       # Preview only
    python tradie_outreach_automation.py --batch 20      # Custom batch size
    python tradie_outreach_automation.py --top-cities    # Brisbane/Cairns first
    python tradie_outreach_automation.py --stats         # Show DB stats

Rules:
    - Only contacts leads with email address
    - Personalises by trade, city, and review count
    - Uses Danny Harris math: missed calls * job value = pain
    - Professional Australian voice — NO G'day, NO "Too easy"
    - Tracks status in PostgreSQL (not_contacted → sent → opened → replied)
"""

import sys
import os
import json
import logging
import argparse
import random
from datetime import datetime, timedelta
from typing import Optional

sys.path.insert(0, r'E:\genesis-system\data\genesis-memory')

import requests
import psycopg2
from psycopg2.extras import RealDictCursor
from elestio_config import PostgresConfig, N8NConfig

# ─── Logging ──────────────────────────────────────────────────────────────────

os.makedirs(r'E:\genesis-system\logs', exist_ok=True)

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s [%(levelname)s] %(message)s',
    handlers=[
        logging.StreamHandler(sys.stdout),
        logging.FileHandler(r'E:\genesis-system\logs\outreach_automation.log', encoding='utf-8')
    ]
)
log = logging.getLogger('outreach')

# ─── Config ───────────────────────────────────────────────────────────────────

DEFAULT_BATCH_SIZE = 25
SENDER_NAME        = 'Kinan'
SENDER_EMAIL       = 'kinan@agileadapt.com'
SENDER_COMPANY     = 'ReceptionistAI'
DEMO_URL           = 'https://sunaivadigital.com/demo'
BOOKING_URL        = 'https://cal.com/kinan'

# Job value estimates by trade (AUD) — from TRADIES research
TRADE_JOB_VALUES = {
    'plumber':     {'min': 250,   'max': 2500,  'label': 'plumbing job'},
    'electrician': {'min': 300,   'max': 3000,  'label': 'electrical job'},
    'builder':     {'min': 5000,  'max': 50000, 'label': 'build project'},
    'hvac':        {'min': 800,   'max': 8000,  'label': 'HVAC installation'},
    'concreter':   {'min': 2000,  'max': 15000, 'label': 'concrete job'},
    'default':     {'min': 500,   'max': 5000,  'label': 'job'},
}

# ─── DB Helpers ───────────────────────────────────────────────────────────────

def get_db():
    return psycopg2.connect(**PostgresConfig.get_connection_params())


def pull_uncontacted_leads(
    batch_size: int = DEFAULT_BATCH_SIZE,
    city_priority: Optional[list] = None
) -> list:
    """
    Pull uncontacted leads with email addresses, ordered by priority score.
    Returns list of dicts.
    """
    conn = get_db()
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            if city_priority:
                placeholders = ','.join(['%s'] * len(city_priority))
                cur.execute(f"""
                    SELECT * FROM tradie_leads
                    WHERE outreach_status = 'not_contacted'
                      AND email IS NOT NULL
                      AND email != ''
                    ORDER BY
                        CASE WHEN city = ANY(%s::text[]) THEN 0 ELSE 1 END,
                        priority_score DESC,
                        review_count DESC,
                        created_at ASC
                    LIMIT %s
                """, [city_priority, batch_size])
            else:
                cur.execute("""
                    SELECT * FROM tradie_leads
                    WHERE outreach_status = 'not_contacted'
                      AND email IS NOT NULL
                      AND email != ''
                    ORDER BY priority_score DESC, review_count DESC, created_at ASC
                    LIMIT %s
                """, [batch_size])
            return [dict(row) for row in cur.fetchall()]
    finally:
        conn.close()


def mark_lead_sent(lead_id: int, channel: str = 'email'):
    """Update lead status to 'sent' after outreach."""
    conn = get_db()
    try:
        with conn.cursor() as cur:
            cur.execute("""
                UPDATE tradie_leads
                SET outreach_status  = 'sent',
                    outreach_sent_at = NOW(),
                    outreach_channel = %s
                WHERE id = %s
            """, [channel, lead_id])
        conn.commit()
    finally:
        conn.close()


def log_outreach_note(lead_id: int, note: str):
    """Append a note to the lead record."""
    conn = get_db()
    try:
        with conn.cursor() as cur:
            cur.execute("""
                UPDATE tradie_leads
                SET notes = COALESCE(notes, '') || %s
                WHERE id = %s
            """, [f'\n[{datetime.now().isoformat()}] {note}', lead_id])
        conn.commit()
    finally:
        conn.close()


# ─── Email Generation ─────────────────────────────────────────────────────────

def get_trade_context(trade: str) -> dict:
    """Return trade-specific copy for personalisation."""
    contexts = {
        'plumber': {
            'pain': "When a pipe bursts at 2am, the customer calls the first plumber who answers. If that's not you, it's your competitor.",
            'missed_calls_per_day': 8,
            'intro': 'plumbing business',
            'qualifier': 'emergency calls'
        },
        'electrician': {
            'pain': "Electrical faults don't wait for business hours. Customers who can't reach you move on to the next licensed electrician immediately.",
            'missed_calls_per_day': 6,
            'intro': 'electrical business',
            'qualifier': 'urgent callouts'
        },
        'builder': {
            'pain': "A homeowner rings three builders for quotes. The one who answers first gets the project. The other two never hear from them again.",
            'missed_calls_per_day': 4,
            'intro': 'building business',
            'qualifier': 'quote requests'
        },
        'hvac': {
            'pain': "Air conditioning failures happen on the hottest days. Customers call until someone answers. That call is worth thousands.",
            'missed_calls_per_day': 7,
            'intro': 'HVAC business',
            'qualifier': 'service calls'
        },
        'concreter': {
            'pain': "Homeowners get 3-4 quotes for concrete work. The contractor who responds fastest almost always wins the job.",
            'missed_calls_per_day': 5,
            'intro': 'concreting business',
            'qualifier': 'quote enquiries'
        },
        'default': {
            'pain': "Customers don't leave voicemails. If you miss their call, they ring the next business on the list.",
            'missed_calls_per_day': 6,
            'intro': 'trade business',
            'qualifier': 'customer calls'
        }
    }
    return contexts.get(trade.lower(), contexts['default'])


def calculate_danny_harris_math(trade: str, missed_per_day: int = None) -> dict:
    """
    The Danny Harris Math — calculates monthly revenue lost to missed calls.
    Returns dict with the numbers for personalised email copy.
    """
    job_vals = TRADE_JOB_VALUES.get(trade.lower(), TRADE_JOB_VALUES['default'])
    ctx = get_trade_context(trade)

    calls_per_day = missed_per_day or ctx['missed_calls_per_day']
    # Assume 30% of missed calls were genuine enquiries
    genuine_rate = 0.30
    genuine_per_day = round(calls_per_day * genuine_rate)
    # Conservative job value (midpoint, leaning low)
    avg_job = round((job_vals['min'] + job_vals['max']) / 2 * 0.6)
    monthly_loss = genuine_per_day * avg_job * 20  # ~20 working days

    return {
        'calls_per_day': calls_per_day,
        'genuine_per_day': max(genuine_per_day, 1),
        'avg_job_value': avg_job,
        'monthly_loss': monthly_loss,
        'job_label': job_vals['label'],
    }


def generate_email(lead: dict) -> dict:
    """
    Generate a personalised cold email for a tradie lead.
    Returns dict with subject, body_text, body_html.

    Voice: warm + professional Australian. NO G'day. NO "Too easy".
    Based on: Danny Harris Math + ReceptionistAI pricing ($497/mo entry).
    """
    name = lead.get('business_name', 'there')
    trade = lead.get('trade', 'default')
    city = lead.get('city', 'Australia')
    review_count = lead.get('review_count', 0)
    rating = lead.get('rating')

    ctx = get_trade_context(trade)
    math = calculate_danny_harris_math(trade)

    # Social proof line based on review count
    if review_count >= 50:
        social_proof = f"With {review_count}+ reviews, you're clearly doing solid work."
    elif review_count >= 20:
        social_proof = f"Your {review_count} reviews show you're well established in {city}."
    elif review_count >= 5:
        social_proof = f"I can see you've built a genuine reputation in {city}."
    else:
        social_proof = f"I came across your {ctx['intro']} in {city}."

    # Personalised pain line
    pain = ctx['pain']

    # The Math
    m = math
    math_block = (
        f"Here's the maths most {trade}s don't track:\n\n"
        f"  - If you're missing {m['calls_per_day']} calls a day on the tools\n"
        f"  - And {m['genuine_per_day']} of those are genuine {m['job_label']} enquiries\n"
        f"  - At an average of ${m['avg_job_value']:,} per job\n"
        f"  - That's ${m['monthly_loss']:,}/month walking out the door\n\n"
        f"Our AI receptionist answers every call, 24/7, in a professional Australian voice.\n"
        f"It qualifies the lead, takes their details, and books them directly into your calendar.\n"
        f"You only deal with people who are ready to hire."
    )

    subject = f"The calls you're missing while you're on the tools — {name}"

    body_text = f"""Hi {name},

{social_proof}

{pain}

{math_block}

We run this for trade businesses across {city} for $497/month — less than the value of a single missed job.

There's no setup fee right now (we're in our launch phase), and it's month-to-month. No lock-in.

Happy to show you exactly how it works for a {ctx['intro']}. Takes about 15 minutes:
{BOOKING_URL}

Or call the demo line first to hear it in action:
{DEMO_URL}

Best,
{SENDER_NAME}
{SENDER_COMPANY}
{SENDER_EMAIL}

---
To unsubscribe, reply with "unsubscribe" and I'll remove you immediately.
"""

    body_html = f"""<!DOCTYPE html>
<html>
<body style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; color: #222; line-height: 1.6;">
  <p>Hi {name},</p>
  <p>{social_proof}</p>
  <p>{pain}</p>
  <p><strong>Here's the maths most {trade}s don't track:</strong></p>
  <ul>
    <li>Missing {m['calls_per_day']} calls/day while on the tools</li>
    <li>{m['genuine_per_day']} of those are genuine {m['job_label']} enquiries</li>
    <li>At ~${m['avg_job_value']:,} per job</li>
    <li>= <strong>${m['monthly_loss']:,}/month you're not capturing</strong></li>
  </ul>
  <p>Our AI receptionist answers every call, 24/7, in a professional Australian voice.
  It qualifies the lead, takes their details, and books them into your calendar.
  You only deal with people who are ready to hire.</p>
  <p>We run this for {city} trade businesses for <strong>$497/month</strong> —
  less than one missed job. No setup fee right now. Month-to-month.</p>
  <p style="margin: 24px 0;">
    <a href="{BOOKING_URL}" style="
      background: #00bf80;
      color: white;
      padding: 12px 24px;
      text-decoration: none;
      border-radius: 6px;
      font-weight: bold;
      display: inline-block;
    ">Book a 15-Minute Call</a>
  </p>
  <p>Or <a href="{DEMO_URL}">try the live demo first</a> to hear it in action.</p>
  <p>Best,<br>
  <strong>{SENDER_NAME}</strong><br>
  {SENDER_COMPANY}<br>
  {SENDER_EMAIL}</p>
  <hr style="border: none; border-top: 1px solid #eee; margin-top: 40px;">
  <p style="font-size: 11px; color: #999;">
    To unsubscribe, reply with "unsubscribe" and I'll remove you immediately.
  </p>
</body>
</html>"""

    return {
        'to_email': lead['email'],
        'to_name': name,
        'subject': subject,
        'body_text': body_text,
        'body_html': body_html,
        'lead_id': lead['id'],
        'trade': trade,
        'city': city,
        'priority_score': lead.get('priority_score', 0),
    }


# ─── Sending ──────────────────────────────────────────────────────────────────

def send_via_n8n(email_data: dict) -> bool:
    """
    POST email payload to n8n webhook for sending.
    n8n handles the SMTP / SendGrid / GHL email delivery.
    """
    n8n = N8NConfig()
    webhook_url = f"https://{n8n.host}/webhook/tradie-outreach"

    payload = {
        'to_email':   email_data['to_email'],
        'to_name':    email_data['to_name'],
        'subject':    email_data['subject'],
        'body_text':  email_data['body_text'],
        'body_html':  email_data['body_html'],
        'lead_id':    email_data['lead_id'],
        'sender':     SENDER_EMAIL,
        'sender_name': SENDER_NAME,
        'timestamp':  datetime.utcnow().isoformat(),
        'source':     'tradie_outreach_automation_v2',
    }

    try:
        resp = requests.post(
            webhook_url,
            json=payload,
            headers={'Content-Type': 'application/json'},
            timeout=15
        )
        if resp.status_code in (200, 201, 202):
            log.info(f"  Sent to {email_data['to_email']} via n8n")
            return True
        else:
            log.warning(f"  n8n returned {resp.status_code}: {resp.text[:200]}")
            return False
    except Exception as e:
        log.error(f"  n8n send error: {e}")
        return False


# ─── Stats ────────────────────────────────────────────────────────────────────

def print_stats():
    """Print outreach pipeline statistics."""
    conn = get_db()
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT
                    outreach_status,
                    COUNT(*) as count,
                    COUNT(CASE WHEN email != '' THEN 1 END) as with_email
                FROM tradie_leads
                GROUP BY outreach_status
                ORDER BY count DESC
            """)
            rows = cur.fetchall()

            cur.execute("SELECT COUNT(*) FROM tradie_leads")
            total = cur.fetchone()[0]

            cur.execute("""
                SELECT city, COUNT(*) as cnt
                FROM tradie_leads
                GROUP BY city ORDER BY cnt DESC LIMIT 10
            """)
            by_city = cur.fetchall()

            cur.execute("""
                SELECT trade, COUNT(*) as cnt
                FROM tradie_leads
                GROUP BY trade ORDER BY cnt DESC
            """)
            by_trade = cur.fetchall()

        print("\n=== RECEPTIONISTAI LEAD PIPELINE STATS ===")
        print(f"Total leads: {total:,}")
        print("\nBy Status:")
        for status, count, with_email in rows:
            print(f"  {status:<20} {count:>6,}  ({with_email:,} with email)")
        print("\nBy City (top 10):")
        for city, cnt in by_city:
            print(f"  {city:<20} {cnt:>6,}")
        print("\nBy Trade:")
        for trade, cnt in by_trade:
            print(f"  {trade:<20} {cnt:>6,}")
        print()
    finally:
        conn.close()


# ─── Main ─────────────────────────────────────────────────────────────────────

def run_outreach(
    batch_size: int = DEFAULT_BATCH_SIZE,
    dry_run: bool = False,
    top_cities: bool = False,
):
    """Main outreach loop."""
    log.info("=" * 60)
    log.info("ReceptionistAI Outreach Automation — STARTING")
    log.info(f"Batch size: {batch_size} | Dry run: {dry_run}")
    log.info("=" * 60)

    city_priority = ['Brisbane', 'Cairns', 'Gold Coast'] if top_cities else None
    leads = pull_uncontacted_leads(batch_size, city_priority)

    if not leads:
        log.info("No uncontacted leads with email addresses found.")
        return

    log.info(f"Pulled {len(leads)} leads for outreach.")

    sent = 0
    failed = 0

    for lead in leads:
        try:
            email = generate_email(lead)

            if dry_run:
                print(f"\n--- DRY RUN: Lead #{lead['id']} ---")
                print(f"To:      {email['to_email']}")
                print(f"Subject: {email['subject']}")
                print(f"Preview: {email['body_text'][:300]}...")
                sent += 1
                continue

            success = send_via_n8n(email)

            if success:
                mark_lead_sent(lead['id'], channel='email')
                log_outreach_note(lead['id'], f"Cold email sent — subject: {email['subject'][:80]}")
                sent += 1
            else:
                failed += 1
                log_outreach_note(lead['id'], "Email send FAILED via n8n")

            # Polite delay between sends (avoid spam triggers)
            delay = random.uniform(8, 20)
            log.debug(f"Sleeping {delay:.1f}s between sends...")

            import time
            time.sleep(delay)

        except Exception as e:
            log.error(f"Error processing lead #{lead.get('id', '?')}: {e}")
            failed += 1

    log.info("=" * 60)
    log.info(f"COMPLETE: sent={sent}, failed={failed}, total={len(leads)}")
    if not dry_run:
        log.info(f"Check n8n dashboard for delivery confirmations.")
    log.info("=" * 60)


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='ReceptionistAI Outreach Automation')
    parser.add_argument('--run',        action='store_true', help='Send outreach batch')
    parser.add_argument('--dry-run',    action='store_true', help='Preview emails without sending')
    parser.add_argument('--batch',      type=int, default=DEFAULT_BATCH_SIZE, help='Batch size')
    parser.add_argument('--top-cities', action='store_true', help='Prioritise Brisbane/Cairns')
    parser.add_argument('--stats',      action='store_true', help='Print pipeline stats')
    args = parser.parse_args()

    if args.stats:
        print_stats()
    elif args.run or args.dry_run:
        run_outreach(
            batch_size=args.batch,
            dry_run=args.dry_run,
            top_cities=args.top_cities,
        )
    else:
        parser.print_help()
        print("\nQuick start:")
        print("  python tradie_outreach_automation.py --dry-run --batch 5")
        print("  python tradie_outreach_automation.py --run --top-cities")
        print("  python tradie_outreach_automation.py --stats")