#!/usr/bin/env python3
"""
Altria VICIdial Social Media → CRM Integration
Polls VICIdial social_media_conversations for new inbound messages,
classifies them with keyword analysis, and creates CRM tickets/alerts.
No external AI API needed.
"""
import os, sys, json, sqlite3, logging, urllib.request, ssl, re
from datetime import datetime, timedelta

ENV_FILE = '/opt/altria/.env'
def _load_env():
    if not os.path.exists(ENV_FILE): return
    for line in open(ENV_FILE):
        line = line.strip()
        if line and not line.startswith('#') and '=' in line:
            k, v = line.split('=', 1)
            os.environ.setdefault(k.strip(), v.strip())
_load_env()

SOCIAL_CRM_WEBHOOK_URL = os.environ.get('SOCIAL_CRM_WEBHOOK_URL',
    os.environ.get('CRM_WEBHOOK_URL', '').replace('/email/', '/social/'))
CRM_WEBHOOK_SECRET = os.environ.get('CRM_WEBHOOK_SECRET', '')
DB_HOST = os.environ.get('DB_HOST', 'localhost')
DB_USER = os.environ.get('DB_USER', 'cron')
DB_PASS = os.environ.get('DB_PASS', '1234')
DB_NAME = os.environ.get('DB_NAME', 'asterisk')
DB_PORT = int(os.environ.get('DB_PORT', '3306'))
LOOKBACK_HOURS = int(os.environ.get('SOCIAL_LOOKBACK_HOURS', '2'))

# ─── State DB ─────────────────────────────────────────────────────────────────
STATE_DB = '/opt/altria/state.db'
def _init_state():
    conn = sqlite3.connect(STATE_DB)
    conn.execute('''CREATE TABLE IF NOT EXISTS social_processed
        (conversation_id TEXT PRIMARY KEY, processed_at TEXT, result TEXT)''')
    conn.commit()
    return conn

def _is_processed(conn, cid):
    return conn.execute(
        'SELECT 1 FROM social_processed WHERE conversation_id=?', (cid,)
    ).fetchone() is not None

def _mark_processed(conn, cid, result):
    conn.execute(
        'INSERT OR REPLACE INTO social_processed VALUES (?,?,?)',
        (cid, datetime.utcnow().isoformat(), result)
    )
    conn.commit()

# ─── Logging ──────────────────────────────────────────────────────────────────
os.makedirs('/opt/altria/logs', exist_ok=True)
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s %(levelname)s %(message)s',
    handlers=[
        logging.FileHandler('/opt/altria/logs/social_triage.log'),
        logging.StreamHandler(sys.stdout),
    ],
)
log = logging.getLogger(__name__)

# ─── Classification (no external API) ────────────────────────────────────────
ALERT_KEYWORDS = [
    'lawyer', 'attorney', 'sue', 'lawsuit', 'court', 'legal action',
    'police', 'report you', 'threaten', 'harassment', 'harassing',
    'racist', 'discrimination', 'offensive', 'inappropriate', 'supervisor', 'manager',
]

CATEGORY_KEYWORDS = {
    'billing':    ['refund', 'charge', 'bill', 'invoice', 'payment', 'overcharged', 'credit card', 'price'],
    'technical':  ['not working', "doesn't work", 'broken', 'error', 'bug', 'crash', 'slow', 'outage'],
    'complaint':  ['unhappy', 'disappointed', 'terrible', 'awful', 'worst', 'horrible', 'unacceptable'],
    'inquiry':    ['question', 'info', 'how do i', 'how can', 'what is', 'where is', 'when does', 'can you'],
    'hr':         ['cash advance', 'salary', 'leave', 'schedule', 'shift', 'interview', 'application', 'hiring'],
    'escalation': ['escalate', 'supervisor', 'manager', 'not acceptable', 'unacceptable'],
}

HOSTILE_WORDS  = ['angry', 'furious', 'outraged', 'livid', 'disgusting', 'idiot', 'stupid', 'incompetent']
NEGATIVE_WORDS = ['frustrated', 'annoyed', 'upset', 'unhappy', 'disappointed', 'terrible', 'horrible', 'awful']
POSITIVE_WORDS = ['thank you', 'thanks', 'great', 'excellent', 'wonderful', 'happy', 'satisfied', 'appreciate']

# Platforms that map to known VICIdial channel names
PLATFORM_PRIORITY_DEFAULT = {
    'facebook':  'low',
    'instagram': 'low',
    'whatsapp':  'medium',
    'viber':     'low',
    'twitter':   'medium',
    'tiktok':    'low',
    'youtube':   'low',
}


def classify(messages_text, platform, sender_name):
    """Classify inbound social media messages. Returns action/priority/sentiment/category/summary."""
    tx = (messages_text or '').lower()

    # Alert check
    triggered = [kw for kw in ALERT_KEYWORDS if kw in tx]
    if triggered:
        return {
            'action':        'alert',
            'priority':      'high',
            'sentiment':     'hostile',
            'category':      'escalation',
            'summary':       f'Social message from {sender_name} on {platform} contains: {", ".join(triggered[:3])}.',
            'alert_message': f'{platform.title()} user {sender_name} used escalation language: "{triggered[0]}". Review immediately.',
        }

    # Category
    category = 'general'
    for cat, words in CATEGORY_KEYWORDS.items():
        if any(w in tx for w in words):
            category = cat
            break

    # Priority
    priority = PLATFORM_PRIORITY_DEFAULT.get(platform.lower(), 'low')
    if category == 'complaint':
        priority = 'high'
    elif category == 'billing':
        priority = 'medium'
    elif category == 'escalation':
        priority = 'high'
    elif category == 'technical':
        priority = 'medium'

    # Sentiment
    if any(w in tx for w in HOSTILE_WORDS):
        sentiment = 'hostile'
        if priority == 'low': priority = 'high'
    elif any(w in tx for w in NEGATIVE_WORDS):
        sentiment = 'negative'
        if priority == 'low': priority = 'medium'
    elif any(w in tx for w in POSITIVE_WORDS):
        sentiment = 'positive'
    else:
        sentiment = 'neutral'

    first_line = messages_text.strip().splitlines()[0][:120] if messages_text else ''
    summary = f'{platform.title()} inbound from {sender_name}. Category: {category}. "{first_line}"'

    return {
        'action':        'ticket',
        'priority':      priority,
        'sentiment':     sentiment,
        'category':      category,
        'summary':       summary,
        'alert_message': None,
    }


# ─── DB ───────────────────────────────────────────────────────────────────────
def get_new_conversations():
    try:
        import mysql.connector
    except ImportError:
        log.error("Run: python3.11 -m pip install mysql-connector-python"); return []

    conn = mysql.connector.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASS,
        database=DB_NAME, port=DB_PORT,
    )
    cur = conn.cursor(dictionary=True)
    since = (datetime.utcnow() - timedelta(hours=LOOKBACK_HOURS)).strftime('%Y-%m-%d %H:%M:%S')

    cur.execute('''
        SELECT c.conversation_id, c.platform, c.sender_name, c.sender_username,
               c.status, c.last_message_preview, c.last_message_at,
               GROUP_CONCAT(m.body ORDER BY m.created_at SEPARATOR "\n") AS messages_text
        FROM social_media_conversations c
        LEFT JOIN social_media_messages m
            ON m.conversation_id = c.conversation_id AND m.direction = 'inbound'
        WHERE c.status IN ('new', 'open')
          AND c.last_message_at >= %s
        GROUP BY c.conversation_id
        ORDER BY c.last_message_at DESC
        LIMIT 100
    ''', (since,))
    rows = cur.fetchall()
    cur.close(); conn.close()
    return rows


# ─── CRM webhook ──────────────────────────────────────────────────────────────
def send_to_crm(cls, conv):
    platform    = conv.get('platform', 'social')
    sender      = conv.get('sender_name') or conv.get('sender_username') or 'Unknown'
    conv_id     = str(conv.get('conversation_id', ''))
    messages    = conv.get('messages_text') or conv.get('last_message_preview', '')

    payload = json.dumps({
        'platform':          platform,
        'sender_name':       sender,
        'phone':             '',
        'message':           messages,
        'conversation_id':   conv_id,
        '_ai_action':        cls['action'],
        '_ai_priority':      cls['priority'],
        '_ai_sentiment':     cls['sentiment'],
        '_ai_category':      cls['category'],
        '_ai_summary':       cls['summary'],
        '_ai_alert_message': cls.get('alert_message') or '',
    }).encode()

    req = urllib.request.Request(
        SOCIAL_CRM_WEBHOOK_URL,
        data=payload,
        method='POST',
        headers={
            'Content-Type':     'application/json',
            'Content-Length':   str(len(payload)),
            'X-Webhook-Secret': CRM_WEBHOOK_SECRET,
        },
    )
    try:
        ctx = ssl.create_default_context()
        with urllib.request.urlopen(req, context=ctx, timeout=15) as resp:
            log.info("CRM: %s %s", resp.status, resp.read(300).decode(errors='replace'))
            return True
    except Exception as e:
        log.error("CRM error: %s", e); return False


# ─── Main ─────────────────────────────────────────────────────────────────────
def main():
    if not SOCIAL_CRM_WEBHOOK_URL:
        log.error("SOCIAL_CRM_WEBHOOK_URL not set in /opt/altria/.env"); sys.exit(1)

    state = _init_state()
    convs = get_new_conversations()
    log.info("Found %d social conversations to check", len(convs))

    for conv in convs:
        cid = str(conv['conversation_id'])
        if _is_processed(state, cid):
            continue

        platform = conv.get('platform', 'social')
        sender   = conv.get('sender_name') or conv.get('sender_username') or 'Unknown'
        messages = conv.get('messages_text') or conv.get('last_message_preview', '')

        log.info("Processing conv %s: platform=%s sender=%s", cid, platform, sender)

        cls = classify(messages, platform, sender)
        log.info("action=%s priority=%s sentiment=%s category=%s",
                 cls['action'], cls['priority'], cls['sentiment'], cls['category'])

        ok = send_to_crm(cls, conv)
        _mark_processed(state, cid, 'sent' if ok else 'crm_failed')

    state.close()
    log.info("Done.")

if __name__ == '__main__':
    main()
