#!/usr/bin/env python3
"""
OpenClaw Local Runner — Autonomous Outbound Revenue Agent
Runs on Kinan's machine while VPS SSH access is being resolved.

Architecture:
  - Reads leads from PostgreSQL tradie_leads table
  - Generates personalised emails using Claude/Gemini API
  - Sends via SMTP (SendGrid)
  - Logs all outreach to agent_execution_log
  - Runs every 15 minutes in background
  - Respects daily cap (50 emails/day max)

Usage:
  python local_runner.py              # Run once
  python local_runner.py --daemon     # Run every 15 min continuously
  python local_runner.py --status     # Show today's stats
  python local_runner.py --dry-run    # Preview without sending

CRITICAL: No SQLite. All storage via Elestio PostgreSQL.
"""

import os
import sys
import time
import json
import logging
import argparse
import smtplib
import hashlib
from datetime import datetime, timedelta
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from pathlib import Path

# ── Path setup ──────────────────────────────────────────────────────────────
GENESIS_ROOT = Path("E:/genesis-system")
sys.path.insert(0, str(GENESIS_ROOT / "data" / "genesis-memory"))

try:
    from elestio_config import PostgresConfig
    import psycopg2
    import psycopg2.extras
    POSTGRES_AVAILABLE = True
except ImportError as e:
    print(f"[WARN] psycopg2 not installed: {e}")
    print("       Install: pip install psycopg2-binary")
    POSTGRES_AVAILABLE = False

# ── Logging ──────────────────────────────────────────────────────────────────
LOG_FILE = GENESIS_ROOT / "mcp-servers" / "storm" / "openclaw_runner.log"
LOG_FILE.parent.mkdir(parents=True, exist_ok=True)

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
    handlers=[
        logging.FileHandler(LOG_FILE),
        logging.StreamHandler(sys.stdout),
    ]
)
log = logging.getLogger("openclaw")

# ── Configuration ─────────────────────────────────────────────────────────────
DAILY_EMAIL_CAP = 50           # Max emails per day
BATCH_SIZE = 10                # Leads per run
LOOP_INTERVAL_SECONDS = 900    # 15 minutes
SEQUENCE_DIR = GENESIS_ROOT / "mcp-servers" / "storm" / "sequences"

# SMTP / SendGrid config — reads from env vars
SMTP_HOST = os.environ.get("SMTP_HOST", "smtp.sendgrid.net")
SMTP_PORT = int(os.environ.get("SMTP_PORT", "587"))
SMTP_USER = os.environ.get("SMTP_USER", "apikey")
SMTP_PASS = os.environ.get("SMTP_PASS", "")           # SendGrid API key
FROM_EMAIL = os.environ.get("FROM_EMAIL", "receptionist@receptionistai.com.au")
FROM_NAME = os.environ.get("FROM_NAME", "ReceptionistAI")

# LLM for personalisation (optional — falls back to template)
OPENROUTER_KEY = os.environ.get("OPENROUTER_API_KEY", "")


# ── Database helpers ──────────────────────────────────────────────────────────

def get_db_conn():
    """Return a live psycopg2 connection to Genesis PostgreSQL."""
    params = PostgresConfig.get_connection_params()
    return psycopg2.connect(**params)


def ensure_tables():
    """Create openclaw tables if they don't exist."""
    ddl = """
    -- Outreach log: one row per email sent
    CREATE TABLE IF NOT EXISTS openclaw_outreach_log (
        id               SERIAL PRIMARY KEY,
        lead_id          INTEGER NOT NULL,
        business_name    TEXT,
        email            TEXT NOT NULL,
        sequence_name    TEXT NOT NULL,
        sequence_step    INTEGER NOT NULL DEFAULT 1,
        subject          TEXT,
        body_hash        TEXT,
        sent_at          TIMESTAMPTZ DEFAULT NOW(),
        status           TEXT DEFAULT 'sent',   -- sent | bounced | opened | replied
        error_msg        TEXT,
        dry_run          BOOLEAN DEFAULT FALSE
    );

    -- Index for fast daily count queries
    CREATE INDEX IF NOT EXISTS idx_openclaw_sent_at
        ON openclaw_outreach_log (sent_at, dry_run);

    -- Index for per-lead history
    CREATE INDEX IF NOT EXISTS idx_openclaw_lead_id
        ON openclaw_outreach_log (lead_id);

    -- Execution log: one row per runner invocation
    CREATE TABLE IF NOT EXISTS openclaw_run_log (
        id            SERIAL PRIMARY KEY,
        started_at    TIMESTAMPTZ DEFAULT NOW(),
        finished_at   TIMESTAMPTZ,
        leads_checked INTEGER DEFAULT 0,
        emails_sent   INTEGER DEFAULT 0,
        errors        INTEGER DEFAULT 0,
        mode          TEXT DEFAULT 'normal',
        notes         TEXT
    );

    COMMENT ON TABLE openclaw_outreach_log IS
        'OpenClaw outbound email audit trail. Never delete rows — append-only.';
    """

    with get_db_conn() as conn:
        with conn.cursor() as cur:
            cur.execute(ddl)
        conn.commit()
    log.info("[DB] Tables verified/created.")


def get_todays_sent_count(conn, dry_run: bool = False) -> int:
    """Return emails sent today (UTC), excluding dry-runs unless we are in dry-run."""
    query = """
        SELECT COUNT(*) FROM openclaw_outreach_log
        WHERE sent_at >= CURRENT_DATE
          AND dry_run = %s
          AND status = 'sent'
    """
    with conn.cursor() as cur:
        cur.execute(query, (dry_run,))
        return cur.fetchone()[0]


def fetch_pending_leads(conn, limit: int = BATCH_SIZE) -> list[dict]:
    """
    Fetch leads ready for first outreach.
    Requires a tradie_leads table with these columns (adjust as needed):
      id, business_name, email, phone, suburb, state,
      industry, website, outreach_status, last_contacted_at
    """
    query = """
        SELECT
            id,
            business_name,
            COALESCE(email, '') AS email,
            COALESCE(phone, '') AS phone,
            COALESCE(suburb, '') AS suburb,
            COALESCE(state, 'QLD') AS state,
            COALESCE(industry, 'tradie') AS industry,
            COALESCE(website, '') AS website,
            COALESCE(outreach_status, 'pending') AS outreach_status,
            last_contacted_at
        FROM tradie_leads
        WHERE outreach_status = 'pending'
          AND email IS NOT NULL
          AND email <> ''
          AND email NOT ILIKE '%@example%'
        ORDER BY id ASC
        LIMIT %s
    """
    with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
        cur.execute(query, (limit,))
        rows = cur.fetchall()
    return [dict(r) for r in rows]


def mark_lead_outreached(conn, lead_id: int, sequence_name: str):
    """Update lead status to 'contacted' after first email."""
    query = """
        UPDATE tradie_leads
        SET outreach_status  = 'contacted',
            last_contacted_at = NOW()
        WHERE id = %s
    """
    with conn.cursor() as cur:
        cur.execute(query, (lead_id,))
    conn.commit()


def log_outreach(conn, lead: dict, sequence_name: str, step: int,
                 subject: str, body: str, status: str = "sent",
                 error_msg: str = None, dry_run: bool = False):
    """Insert a row into openclaw_outreach_log."""
    body_hash = hashlib.sha256(body.encode()).hexdigest()[:16]
    query = """
        INSERT INTO openclaw_outreach_log
            (lead_id, business_name, email, sequence_name, sequence_step,
             subject, body_hash, status, error_msg, dry_run)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    with conn.cursor() as cur:
        cur.execute(query, (
            lead["id"], lead.get("business_name"), lead["email"],
            sequence_name, step, subject, body_hash,
            status, error_msg, dry_run
        ))
    conn.commit()


def log_run(conn, run_id: int = None, **kwargs):
    """Insert or update a run log entry."""
    if run_id is None:
        query = "INSERT INTO openclaw_run_log (mode) VALUES (%s) RETURNING id"
        with conn.cursor() as cur:
            cur.execute(query, (kwargs.get("mode", "normal"),))
            run_id = cur.fetchone()[0]
        conn.commit()
        return run_id
    else:
        sets = ", ".join(f"{k} = %s" for k in kwargs)
        vals = list(kwargs.values()) + [run_id]
        query = f"UPDATE openclaw_run_log SET {sets} WHERE id = %s"
        with conn.cursor() as cur:
            cur.execute(query, vals)
        conn.commit()
        return run_id


# ── Email personalisation ─────────────────────────────────────────────────────

def load_sequence_template(sequence_name: str, step: int = 1) -> dict:
    """
    Load email template from sequences/ directory.
    Returns {"subject": str, "body": str}
    Falls back to hardcoded template if file not found.
    """
    seq_file = SEQUENCE_DIR / f"{sequence_name}.md"

    if seq_file.exists():
        content = seq_file.read_text(encoding="utf-8")
        # Parse markdown sections: ## Email N: Subject / Body
        sections = []
        current = {}
        for line in content.splitlines():
            if line.startswith("## Email "):
                if current:
                    sections.append(current)
                current = {"step": len(sections) + 1, "subject": "", "body": ""}
            elif line.startswith("**Subject:**"):
                current["subject"] = line.replace("**Subject:**", "").strip()
            elif "subject" in current and "body" in current:
                current["body"] = current.get("body", "") + line + "\n"

        if current:
            sections.append(current)

        if sections and step <= len(sections):
            return {"subject": sections[step - 1]["subject"],
                    "body": sections[step - 1]["body"].strip()}

    # Fallback hardcoded template (tradie_cold, step 1)
    return {
        "subject": "Missed calls are costing {business_name} real money",
        "body": (
            "Hi {first_name},\n\n"
            "Quick question: how many calls does {business_name} miss each week?\n\n"
            "Most {industry}s in {suburb} miss 3–5 calls a day. At even $300 a job, "
            "that's $4,500+ a week walking out the door.\n\n"
            "We built an AI receptionist that answers every call, qualifies leads, "
            "and books jobs — 24/7, without you lifting a finger.\n\n"
            "Takes 15 minutes to set up. No lock-in contracts.\n\n"
            "Want to see it in action? I can show you a live demo this week.\n\n"
            "— {sender_name}\n"
            "ReceptionistAI\n"
            "P: 1800 XXX XXX | receptionistai.com.au\n\n"
            "---\n"
            "To opt out of future emails, reply STOP."
        )
    }


def personalise(template: dict, lead: dict) -> dict:
    """Fill template placeholders with lead data."""
    business_name = lead.get("business_name", "your business")
    industry = lead.get("industry", "tradie")
    suburb = lead.get("suburb", "your area")

    # Try to extract first name from business_name
    # e.g. "John Smith Plumbing" -> "John"
    first_name = business_name.split()[0] if business_name else "there"
    # If it looks like a company name (all caps, "PTY", "LTD"), use generic
    if any(word in business_name.upper() for word in ["PTY", "LTD", "SERVICES", "GROUP"]):
        first_name = "there"

    replacements = {
        "{business_name}": business_name,
        "{first_name}": first_name,
        "{industry}": industry,
        "{suburb}": suburb,
        "{state}": lead.get("state", "QLD"),
        "{website}": lead.get("website", ""),
        "{sender_name}": FROM_NAME,
    }

    subject = template["subject"]
    body = template["body"]

    for placeholder, value in replacements.items():
        subject = subject.replace(placeholder, str(value))
        body = body.replace(placeholder, str(value))

    return {"subject": subject, "body": body}


# ── Email sending ─────────────────────────────────────────────────────────────

def send_email(to_email: str, subject: str, body: str,
               to_name: str = "") -> tuple[bool, str]:
    """
    Send email via SMTP (SendGrid or any SMTP).
    Returns (success: bool, error_msg: str)
    """
    if not SMTP_PASS:
        return False, "SMTP_PASS not configured. Set SMTP_PASS env var."

    msg = MIMEMultipart("alternative")
    msg["Subject"] = subject
    msg["From"] = f"{FROM_NAME} <{FROM_EMAIL}>"
    msg["To"] = f"{to_name} <{to_email}>" if to_name else to_email
    msg["List-Unsubscribe"] = f"<mailto:{FROM_EMAIL}?subject=STOP>"

    # Plain text part
    plain = MIMEText(body, "plain", "utf-8")
    msg.attach(plain)

    # Simple HTML version
    html_body = body.replace("\n", "<br>")
    html = MIMEText(
        f"<html><body style='font-family:Arial,sans-serif;max-width:600px;'>"
        f"<p>{html_body}</p></body></html>",
        "html", "utf-8"
    )
    msg.attach(html)

    try:
        with smtplib.SMTP(SMTP_HOST, SMTP_PORT, timeout=30) as server:
            server.ehlo()
            server.starttls()
            server.login(SMTP_USER, SMTP_PASS)
            server.sendmail(FROM_EMAIL, [to_email], msg.as_string())
        return True, ""
    except Exception as e:
        return False, str(e)


# ── Core run logic ─────────────────────────────────────────────────────────────

def run_once(dry_run: bool = False, sequence_name: str = "tradie_cold") -> dict:
    """
    Single execution cycle of OpenClaw outbound engine.
    Returns stats dict.
    """
    stats = {
        "leads_checked": 0,
        "emails_sent": 0,
        "errors": 0,
        "skipped_cap": 0,
        "started_at": datetime.utcnow().isoformat(),
    }

    if not POSTGRES_AVAILABLE:
        log.error("[FATAL] psycopg2 not available. Install: pip install psycopg2-binary")
        return stats

    log.info(f"[RUN] Starting OpenClaw cycle | sequence={sequence_name} | dry_run={dry_run}")

    try:
        conn = get_db_conn()
    except Exception as e:
        log.error(f"[FATAL] Cannot connect to PostgreSQL: {e}")
        log.error("Check VPN / network access to Elestio PostgreSQL.")
        stats["errors"] += 1
        return stats

    run_id = log_run(conn, mode="dry-run" if dry_run else "normal")

    try:
        # Check daily cap
        sent_today = get_todays_sent_count(conn, dry_run=dry_run)
        remaining = DAILY_EMAIL_CAP - sent_today
        log.info(f"[CAP] Sent today: {sent_today} | Remaining: {remaining}")

        if remaining <= 0:
            log.warning(f"[CAP] Daily cap of {DAILY_EMAIL_CAP} reached. Skipping run.")
            stats["skipped_cap"] = BATCH_SIZE
            return stats

        # Fetch leads
        batch_limit = min(BATCH_SIZE, remaining)
        leads = fetch_pending_leads(conn, limit=batch_limit)
        stats["leads_checked"] = len(leads)
        log.info(f"[LEADS] Found {len(leads)} pending leads.")

        if not leads:
            log.info("[LEADS] No pending leads found. Nothing to do.")
            return stats

        for lead in leads:
            email = lead.get("email", "").strip()
            if not email or "@" not in email:
                log.warning(f"[SKIP] Lead {lead['id']} has invalid email: {email!r}")
                continue

            # Load and personalise template
            template = load_sequence_template(sequence_name, step=1)
            personalised = personalise(template, lead)
            subject = personalised["subject"]
            body = personalised["body"]

            log.info(f"[EMAIL] Lead {lead['id']} | {lead.get('business_name')} | {email}")
            log.info(f"        Subject: {subject}")

            if dry_run:
                log.info("[DRY-RUN] Would send (not actually sending).")
                log_outreach(conn, lead, sequence_name, 1, subject, body,
                             status="sent", dry_run=True)
                stats["emails_sent"] += 1
            else:
                success, error_msg = send_email(
                    to_email=email,
                    subject=subject,
                    body=body,
                    to_name=lead.get("business_name", "")
                )

                if success:
                    log_outreach(conn, lead, sequence_name, 1, subject, body,
                                 status="sent", dry_run=False)
                    mark_lead_outreached(conn, lead["id"], sequence_name)
                    stats["emails_sent"] += 1
                    log.info(f"[OK] Sent to {email}")
                else:
                    log_outreach(conn, lead, sequence_name, 1, subject, body,
                                 status="error", error_msg=error_msg, dry_run=False)
                    stats["errors"] += 1
                    log.error(f"[FAIL] {email}: {error_msg}")

            # Small delay to avoid SMTP rate limits
            if not dry_run:
                time.sleep(1.5)

    finally:
        log_run(conn, run_id=run_id,
                finished_at=datetime.utcnow().isoformat(),
                leads_checked=stats["leads_checked"],
                emails_sent=stats["emails_sent"],
                errors=stats["errors"])
        conn.close()

    log.info(f"[DONE] Cycle complete | sent={stats['emails_sent']} errors={stats['errors']}")
    return stats


def show_status():
    """Print today's OpenClaw stats to stdout."""
    if not POSTGRES_AVAILABLE:
        print("[ERROR] psycopg2 not available.")
        return

    try:
        conn = get_db_conn()
    except Exception as e:
        print(f"[ERROR] DB connection failed: {e}")
        return

    try:
        with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
            # Today's summary
            cur.execute("""
                SELECT
                    COUNT(*) FILTER (WHERE status = 'sent' AND NOT dry_run) AS sent_real,
                    COUNT(*) FILTER (WHERE status = 'sent' AND dry_run)     AS sent_dry,
                    COUNT(*) FILTER (WHERE status = 'error')                AS errors,
                    COUNT(*) FILTER (WHERE status = 'opened')               AS opened,
                    COUNT(*) FILTER (WHERE status = 'replied')              AS replied
                FROM openclaw_outreach_log
                WHERE sent_at >= CURRENT_DATE
            """)
            today = cur.fetchone()

            # Last 5 runs
            cur.execute("""
                SELECT started_at, emails_sent, errors, mode
                FROM openclaw_run_log
                ORDER BY id DESC LIMIT 5
            """)
            runs = cur.fetchall()

            # Pipeline stage counts
            cur.execute("""
                SELECT outreach_status, COUNT(*) as cnt
                FROM tradie_leads
                GROUP BY outreach_status
                ORDER BY cnt DESC
            """)
            pipeline = cur.fetchall()

        print("\n" + "=" * 50)
        print("  OpenClaw Status Dashboard")
        print("=" * 50)
        print(f"\n  TODAY:")
        print(f"    Emails sent (real):  {today['sent_real']}")
        print(f"    Emails sent (dry):   {today['sent_dry']}")
        print(f"    Errors:              {today['errors']}")
        print(f"    Opened:              {today['opened']}")
        print(f"    Replied:             {today['replied']}")
        print(f"    Daily cap remaining: {DAILY_EMAIL_CAP - (today['sent_real'] or 0)}")

        print(f"\n  LAST 5 RUNS:")
        for r in runs:
            print(f"    {r['started_at']:%Y-%m-%d %H:%M} | "
                  f"sent={r['emails_sent']} err={r['errors']} [{r['mode']}]")

        print(f"\n  PIPELINE:")
        for p in pipeline:
            print(f"    {p['outreach_status']:15s} {p['cnt']:>6}")

        print("=" * 50 + "\n")

    finally:
        conn.close()


def run_daemon(dry_run: bool = False, sequence_name: str = "tradie_cold"):
    """Run OpenClaw every LOOP_INTERVAL_SECONDS until interrupted."""
    log.info(f"[DAEMON] Starting OpenClaw daemon | interval={LOOP_INTERVAL_SECONDS}s")
    log.info(f"         Sequence: {sequence_name} | Dry-run: {dry_run}")
    log.info("         Press Ctrl+C to stop.")

    while True:
        try:
            stats = run_once(dry_run=dry_run, sequence_name=sequence_name)
            log.info(f"[DAEMON] Sleeping {LOOP_INTERVAL_SECONDS}s...")
            time.sleep(LOOP_INTERVAL_SECONDS)
        except KeyboardInterrupt:
            log.info("[DAEMON] Stopped by user.")
            break
        except Exception as e:
            log.error(f"[DAEMON] Unexpected error: {e}. Retrying in 60s.")
            time.sleep(60)


# ── CLI entry point ────────────────────────────────────────────────────────────

def main():
    parser = argparse.ArgumentParser(
        description="OpenClaw Local Runner — Autonomous outbound email agent"
    )
    parser.add_argument("--daemon", action="store_true",
                        help="Run continuously every 15 minutes")
    parser.add_argument("--dry-run", action="store_true",
                        help="Preview without actually sending emails")
    parser.add_argument("--status", action="store_true",
                        help="Show today's stats and exit")
    parser.add_argument("--sequence", default="tradie_cold",
                        choices=["tradie_cold", "agency_cold", "demo_followup"],
                        help="Which email sequence to run (default: tradie_cold)")
    parser.add_argument("--init-db", action="store_true",
                        help="Create PostgreSQL tables and exit")

    args = parser.parse_args()

    if args.init_db:
        log.info("[INIT] Creating database tables...")
        ensure_tables()
        log.info("[INIT] Done.")
        return

    if args.status:
        show_status()
        return

    if args.daemon:
        run_daemon(dry_run=args.dry_run, sequence_name=args.sequence)
    else:
        stats = run_once(dry_run=args.dry_run, sequence_name=args.sequence)
        print(f"\nRun complete: {stats}")


if __name__ == "__main__":
    main()