#!/usr/bin/env python3
"""
MongoDB helper for Central PHP apps.
Called via shell_exec() from PHP. Returns JSON to stdout.

Usage:
  python3 mongo_helper.py <action> [json_params]

Actions:
  workspace_chats  - Get recent chats for a workspace (params: workspace_id, limit)
  chat_detail      - Get full chat with messages (params: chat_id)
  workspace_stats  - Aggregate stats for a workspace (params: workspace_id)
  integrations     - Get integrations for a workspace (params: workspace_id)
  bulk_stats       - Get chat counts for multiple workspaces (params: workspace_ids[])
  schema_probe     - Return field names from sample docs (no params)

Requires: pip3 install pymongo certifi
"""

import sys
import json
from datetime import datetime, timezone

try:
    import certifi
    CA_FILE = certifi.where()
except ImportError:
    CA_FILE = None

from pymongo import MongoClient
from bson import ObjectId, json_util

MONGO_URI = "mongodb+srv://147reader:JustWing1t1234@central-prod-cluster.esqajwm.mongodb.net/?appName=central-prod-cluster"
DB_NAME = "m32_ai_csr"

def get_client():
    kwargs = {"serverSelectionTimeoutMS": 10000}
    if CA_FILE:
        kwargs["tlsCAFile"] = CA_FILE
    return MongoClient(MONGO_URI, **kwargs)

def serialize(obj):
    """Convert MongoDB types to JSON-safe Python types."""
    if isinstance(obj, ObjectId):
        return str(obj)
    if isinstance(obj, datetime):
        return obj.isoformat()
    if isinstance(obj, dict):
        return {k: serialize(v) for k, v in obj.items()}
    if isinstance(obj, list):
        return [serialize(i) for i in obj]
    return obj

def _get_msgs(doc):
    """Extract messages from chat.messages nested structure."""
    chat_obj = doc.get("chat") or {}
    return chat_obj.get("messages", []) if isinstance(chat_obj, dict) else []

def _extract_contact(doc):
    """Pull visitor info from extractedContactInfo or extractedEmail."""
    info = doc.get("extractedContactInfo") or {}
    if not isinstance(info, dict):
        info = {}
    return {
        "name": info.get("name", ""),
        "email": info.get("email", "") or doc.get("extractedEmail", "") or "",
        "phone": info.get("phone", ""),
    }

WS_FIELD = "centralWorkspaceId"

def workspace_chats(params):
    ws_id = params.get("workspace_id", "")
    limit = int(params.get("limit", 50))
    client = get_client()
    db = client[DB_NAME]
    chats = list(db.workspaceChats.find({WS_FIELD: ws_id}).sort("createdAt", -1).limit(limit))
    client.close()
    result = []
    for c in chats:
        msgs = _get_msgs(c)
        contact = _extract_contact(c)
        first_msg = msgs[0] if msgs else None
        last_msg = msgs[-1] if msgs else None
        entry = {
            "id": str(c.get("_id", "")),
            "createdAt": serialize(c.get("createdAt")),
            "updatedAt": serialize(c.get("updatedAt")),
            "chatEnded": c.get("chatEnded", False),
            "visitorName": contact["name"],
            "visitorEmail": contact["email"],
            "visitorPhone": contact["phone"],
            "chatSource": c.get("chatSource", ""),
            "embeddedUrl": c.get("embeddedWebsiteUrl", ""),
            "messageCount": len(msgs),
            "firstMessage": serialize(first_msg) if first_msg else None,
            "lastMessage": serialize(last_msg) if last_msg else None,
            "feedbackRating": c.get("feedbackRating"),
            "agentTakeover": c.get("agentTakeover", False),
            "isStarred": c.get("isStarred", False),
            "leadId": c.get("leadId", ""),
            "userIp": c.get("userIp", ""),
        }
        result.append(entry)
    return result

def chat_detail(params):
    chat_id = params.get("chat_id", "")
    client = get_client()
    db = client[DB_NAME]
    try:
        doc = db.workspaceChats.find_one({"_id": ObjectId(chat_id)})
    except Exception:
        doc = db.workspaceChats.find_one({"_id": chat_id})
    client.close()
    if not doc:
        return {"error": "Chat not found"}
    msgs = _get_msgs(doc)
    contact = _extract_contact(doc)
    return {
        "id": str(doc.get("_id", "")),
        "createdAt": serialize(doc.get("createdAt")),
        "updatedAt": serialize(doc.get("updatedAt")),
        "chatEnded": doc.get("chatEnded", False),
        "chatSource": doc.get("chatSource", ""),
        "embeddedUrl": doc.get("embeddedWebsiteUrl", ""),
        "feedbackRating": doc.get("feedbackRating"),
        "agentTakeover": doc.get("agentTakeover", False),
        "isStarred": doc.get("isStarred", False),
        "leadId": doc.get("leadId", ""),
        "userIp": doc.get("userIp", ""),
        "visitorName": contact["name"],
        "visitorEmail": contact["email"],
        "visitorPhone": contact["phone"],
        "messages": serialize(msgs),
    }

def workspace_stats(params):
    ws_id = params.get("workspace_id", "")
    client = get_client()
    db = client[DB_NAME]

    pipeline = [
        {"$match": {WS_FIELD: ws_id}},
        {"$group": {
            "_id": None,
            "totalChats": {"$sum": 1},
            "totalMessages": {"$sum": {"$size": {"$ifNull": ["$chat.messages", []]}}},
            "firstChat": {"$min": "$createdAt"},
            "lastChat": {"$max": "$createdAt"},
        }}
    ]
    result = list(db.workspaceChats.aggregate(pipeline))

    from datetime import timedelta
    cutoff = datetime.now(timezone.utc) - timedelta(days=30)
    daily_pipeline = [
        {"$match": {WS_FIELD: ws_id, "createdAt": {"$gte": cutoff}}},
        {"$group": {
            "_id": {"$dateToString": {"format": "%Y-%m-%d", "date": "$createdAt"}},
            "count": {"$sum": 1},
        }},
        {"$sort": {"_id": 1}},
    ]
    daily = list(db.workspaceChats.aggregate(daily_pipeline))

    stats = result[0] if result else {"totalChats": 0, "totalMessages": 0, "firstChat": None, "lastChat": None}
    stats.pop("_id", None)

    if stats["totalChats"] > 0 and stats["firstChat"] and stats["lastChat"]:
        first = stats["firstChat"]
        last = stats["lastChat"]
        if isinstance(first, datetime) and isinstance(last, datetime):
            span_days = max((last - first).days, 1)
            stats["avgChatsPerDay"] = round(stats["totalChats"] / span_days, 1)
        else:
            stats["avgChatsPerDay"] = 0
    else:
        stats["avgChatsPerDay"] = 0

    stats["daily"] = serialize(daily)
    client.close()
    return serialize(stats)

def integrations(params):
    ws_id = params.get("workspace_id", "")
    client = get_client()
    db = client[DB_NAME]
    docs = list(db.integrations.find({WS_FIELD: ws_id}).sort("createdAt", -1))
    client.close()
    return serialize(docs)

def chatbot_info(params):
    ws_id = params.get("workspace_id", "")
    client = get_client()
    db = client[DB_NAME]
    bots = list(db.chatbots.find({WS_FIELD: ws_id}).sort("_id", -1))
    client.close()
    result = []
    for b in bots:
        urls = b.get("embeddedWebsiteUrl", [])
        if not isinstance(urls, list):
            urls = [urls] if urls else []
        persona = b.get("persona") or {}
        if not isinstance(persona, dict):
            persona = {}
        greeting = b.get("greetingMessage") or {}
        if not isinstance(greeting, dict):
            greeting = {}
        qas = b.get("quickActions") or []
        enabled_qas = [qa for qa in qas if isinstance(qa, dict) and qa.get("enabled")]
        result.append({
            "id": str(b.get("_id", "")),
            "active": b.get("active", False),
            "widgetInstalled": len(urls) > 0,
            "embeddedUrls": urls,
            "personaName": persona.get("name", ""),
            "personaPicture": persona.get("picture", ""),
            "brandColor": b.get("brandColor", ""),
            "greetingShort": greeting.get("shortMessage", ""),
            "greetingLong": greeting.get("longMessage", ""),
            "agentInstructions": b.get("agentInstructions", ""),
            "quickActionsEnabled": len(enabled_qas),
            "quickActions": serialize(enabled_qas),
            "chatIcon": b.get("chatIcon", ""),
            "popupGreet": b.get("popupGreet", False),
            "popupStyle": b.get("popupStyle", ""),
            "showWatermark": b.get("showWatermark", True),
            "streamResponse": b.get("streamResponse", True),
            "useIntakeForm": b.get("useIntakeForm", False),
            "intakeFormId": b.get("intakeFormId"),
        })
    return serialize(result)


def bulk_chatbot_info(params):
    ws_ids = params.get("workspace_ids", [])
    if not ws_ids:
        return {}
    client = get_client()
    db = client[DB_NAME]
    bots = list(db.chatbots.find({WS_FIELD: {"$in": ws_ids}},
        {"_id": 0, WS_FIELD: 1, "active": 1, "embeddedWebsiteUrl": 1,
         "persona": 1, "brandColor": 1}))
    client.close()
    out = {}
    for b in bots:
        ws = b.get(WS_FIELD, "")
        if not ws:
            continue
        urls = b.get("embeddedWebsiteUrl", [])
        if not isinstance(urls, list):
            urls = [urls] if urls else []
        persona = b.get("persona") or {}
        if not isinstance(persona, dict):
            persona = {}
        existing = out.get(ws)
        if existing:
            existing["botCount"] += 1
            if b.get("active"):
                existing["hasActiveBot"] = True
            if urls:
                existing["widgetInstalled"] = True
                existing["widgetUrls"] += len(urls)
        else:
            out[ws] = {
                "botCount": 1,
                "hasActiveBot": bool(b.get("active")),
                "widgetInstalled": len(urls) > 0,
                "widgetUrls": len(urls),
                "personaName": persona.get("name", ""),
                "brandColor": b.get("brandColor", ""),
            }
    return serialize(out)


def recent_chats_global(params):
    limit = int(params.get("limit", 500))
    ws_ids = params.get("workspace_ids", [])
    client = get_client()
    db = client[DB_NAME]
    query = {}
    if ws_ids:
        query[WS_FIELD] = {"$in": ws_ids}
    chats = list(db.workspaceChats.find(query).sort("createdAt", -1).limit(limit))
    client.close()
    result = []
    for c in chats:
        msgs = _get_msgs(c)
        contact = _extract_contact(c)
        last_msg = msgs[-1] if msgs else None
        result.append({
            "id": str(c.get("_id", "")),
            "workspaceId": c.get(WS_FIELD, ""),
            "createdAt": serialize(c.get("createdAt")),
            "chatEnded": c.get("chatEnded", False),
            "visitorName": contact["name"],
            "visitorEmail": contact["email"],
            "visitorPhone": contact["phone"],
            "chatSource": c.get("chatSource", ""),
            "messageCount": len(msgs),
            "lastMessage": serialize(last_msg) if last_msg else None,
            "agentTakeover": c.get("agentTakeover", False),
            "isStarred": c.get("isStarred", False),
            "embeddedUrl": c.get("embeddedWebsiteUrl", ""),
        })
    return serialize(result)


def bulk_stats(params):
    ws_ids = params.get("workspace_ids", [])
    if not ws_ids:
        return {}
    client = get_client()
    db = client[DB_NAME]
    pipeline = [
        {"$match": {WS_FIELD: {"$in": ws_ids}}},
        {"$group": {
            "_id": "$" + WS_FIELD,
            "totalChats": {"$sum": 1},
            "totalMessages": {"$sum": {"$size": {"$ifNull": ["$chat.messages", []]}}},
            "firstChat": {"$min": "$createdAt"},
            "lastChat": {"$max": "$createdAt"},
        }}
    ]
    results = list(db.workspaceChats.aggregate(pipeline))
    client.close()
    out = {}
    for r in results:
        ws = r.pop("_id")
        if r["totalChats"] > 0 and r.get("firstChat") and r.get("lastChat"):
            first = r["firstChat"]
            last = r["lastChat"]
            if isinstance(first, datetime) and isinstance(last, datetime):
                span = max((last - first).days, 1)
                r["avgChatsPerDay"] = round(r["totalChats"] / span, 1)
            else:
                r["avgChatsPerDay"] = 0
        else:
            r["avgChatsPerDay"] = 0
        out[ws] = serialize(r)
    return out

def schema_probe(params):
    client = get_client()
    db = client[DB_NAME]
    collections = db.list_collection_names()
    probe_colls = params.get("collections", ["workspaceChats", "integrations"])
    samples = {}
    for coll_name in probe_colls:
        if coll_name in collections:
            doc = db[coll_name].find_one(sort=[("createdAt", -1)])
            if doc:
                field_info = {}
                for k, v in doc.items():
                    t = type(v).__name__
                    if isinstance(v, list):
                        t = f"list[{len(v)}]"
                        if v:
                            t += f" of {type(v[0]).__name__}"
                    val_preview = str(serialize(v))[:300]
                    field_info[k] = {"type": t, "preview": val_preview}
                samples[coll_name] = field_info
    client.close()
    return {"collections": collections, "samples": samples}

def deep_probe(params):
    """Probe chatbots, workspaces, and other collections for widget/setup info."""
    client = get_client()
    db = client[DB_NAME]
    result = {}

    # Probe chatbots collection
    for coll_name in ["chatbots", "workspaces", "leads", "knowledgeBases", "formcaptures"]:
        doc = db[coll_name].find_one(sort=[("createdAt", -1)])
        if doc:
            fields = {}
            for k, v in doc.items():
                t = type(v).__name__
                if isinstance(v, list):
                    t = f"list[{len(v)}]"
                    if v: t += f" of {type(v[0]).__name__}"
                elif isinstance(v, dict):
                    t = f"dict[{len(v)} keys: {list(v.keys())[:8]}]"
                fields[k] = {"type": t, "preview": str(serialize(v))[:300]}
            result[coll_name] = fields
        result[coll_name + "_count"] = db[coll_name].estimated_document_count()

    client.close()
    return result


ACTIONS = {
    "workspace_chats": workspace_chats,
    "chat_detail": chat_detail,
    "workspace_stats": workspace_stats,
    "integrations": integrations,
    "chatbot_info": chatbot_info,
    "bulk_chatbot_info": bulk_chatbot_info,
    "recent_chats_global": recent_chats_global,
    "bulk_stats": bulk_stats,
    "schema_probe": schema_probe,
    "deep_probe": deep_probe,
}

if __name__ == "__main__":
    if len(sys.argv) < 2:
        print(json.dumps({"error": "Usage: mongo_helper.py <action> [json_params]"}))
        sys.exit(1)

    action = sys.argv[1]
    params = json.loads(sys.argv[2]) if len(sys.argv) > 2 else {}

    if action not in ACTIONS:
        print(json.dumps({"error": f"Unknown action: {action}"}))
        sys.exit(1)

    try:
        result = ACTIONS[action](params)
        print(json.dumps(result, default=str))
    except Exception as e:
        print(json.dumps({"error": str(e)}))
        sys.exit(1)