import os import json import traceback from flask import Blueprint, render_template, request, session, jsonify from ..decorators import client_required from ..db_analytics import ( search_projects_for_analytics, get_project_detail_proc, get_analytics_from_proc, ) analytics_bp = Blueprint("analytics", __name__, url_prefix="/client") # ── PAGE ROUTES ─────────────────────────────────────────────────────────────── @analytics_bp.route("/realstate-analytics") @client_required def realstate_analytics(): from ..db import get_client_categories categories = get_client_categories(session["user_id"]) folder = session.get("ssrs_folder", "") return render_template( "client/realstate-analytics.html", client_name=session["user_name"], folder=folder, categories=categories, category_list=list(categories.keys()), ) # ── HELPERS ─────────────────────────────────────────────────────────────────── def _client_db(): db = session.get("client_db") or None if not db: return None return db def _err(msg, code=400): return jsonify({"error": msg}), code # ── SINGLE UNIFIED API — returns everything from proc in one call ───────────── @analytics_bp.route("/api/analytics/load") @client_required def api_analytics_load(): """ Primary endpoint: calls the stored proc once and returns the full analytics payload. Frontend splits it into all the sub-sections. Query params: project_id – for ProjectType C / S parent_project_id – for ProjectType G """ db = _client_db() if not db: return _err("No client database configured for this account.") try: project_id = request.args.get("project_id") parent_project_id = request.args.get("parent_project_id") project_id = int(project_id) if project_id else None parent_project_id = int(parent_project_id) if parent_project_id else None data = get_analytics_from_proc(db, project_id, parent_project_id) return jsonify(data) except Exception as e: import traceback; traceback.print_exc() return _err(str(e), 500) # ── PROJECT SEARCH ──────────────────────────────────────────────────────────── @analytics_bp.route("/api/analytics/search-projects") @client_required def api_analytics_search_projects(): db = _client_db() if not db: return _err("No client database configured.") q = request.args.get("q", "").strip() if not q: return jsonify([]) try: return jsonify(search_projects_for_analytics(db, q)) except Exception as e: import traceback; traceback.print_exc() return _err(str(e), 500) # ── RAW PROC ROWS (kept for any other consumer) ─────────────────────────────── @analytics_bp.route("/api/analytics/project-detail") @client_required def api_analytics_project_detail(): db = _client_db() if not db: return _err("No client database configured.") try: project_id = request.args.get("project_id") parent_project_id = request.args.get("parent_project_id") project_id = int(project_id) if project_id else None parent_project_id = int(parent_project_id) if parent_project_id else None return jsonify(get_project_detail_proc(db, project_id, parent_project_id)) except Exception as e: import traceback; traceback.print_exc() return _err(str(e), 500) @analytics_bp.route("/api/analytics/ai-suggest", methods=["POST"]) @client_required def api_analytics_ai_suggest(): """ Returns contextually relevant follow-up suggestions based on what was just asked. Uses a curated question bank so no hallucination is possible. """ body = request.get_json(force=True, silent=True) or {} last_question = (body.get("last_question") or "").strip().lower() asked_before = [q.lower() for q in body.get("asked_before", [])] QUESTION_BANK = { "kpi": ["What is the CVR?", "Total completes so far?", "How many terminates?", "What are the dropouts?", "Total responses received?"], "fraud": ["What is the fraud rate?", "Fraud breakdown by type?", "How many duplicate suppliers?", "Any proxy detections?", "Cost wasted on fraud?"], "financial": ["What is the gross margin?", "What is the margin %?", "Avg project CPI?", "Avg supplier CPI?", "Total supplier cost?"], "loi": ["What is the avg LOI?", "How many speeders?", "Avg LOI for terminates?", "Median LOI for completes?", "Speeder rate?"], "quality": ["What is the quality pass rate?", "Avg composite score?", "High threat count?", "Avg threat score?"], "funnel": ["What is the IR?", "Gross traffic count?", "How many reached the survey?", "Effective yield?", "After fraud filter count?"], "country": ["Top countries by completes?", "Which country leads responses?"], "supplier": ["Supplier performance summary?", "Top supplier by completes?"], "device": ["Device breakdown?", "Mobile vs desktop split?"], } topic_map = { "kpi": ["kpi", "performance indicator", "completes", "terminates", "dropout", "cvr", "conversion", "total response", "total project"], "fraud": ["fraud", "flagged", "duplicate", "proxy", "vpn", "rd failure", "rdtext", "geo ip", "rejected"], "financial": ["financial", "spend", "margin", "cpi", "cost", "supplier cost", "budget"], "loi": ["loi", "interview", "duration", "speeder", "minutes", "avg loi", "length"], "quality": ["quality", "composite", "threat", "pass rate", "score"], "funnel": ["funnel", "ir", "incidence", "traffic", "yield", "reached survey"], "country": ["countr", "geographic", "region", "location"], "supplier": ["supplier", "vendor", "panel", "provider"], "device": ["device", "mobile", "desktop", "tablet"], } detected = "kpi" for topic, keywords in topic_map.items(): if any(kw in last_question for kw in keywords): detected = topic break section_order = [t for t in QUESTION_BANK if t != detected] + [detected] suggestions = [] seen = set(asked_before + [last_question]) for section in section_order: for q in QUESTION_BANK[section]: if q.lower() not in seen and q not in suggestions: suggestions.append(q) if len(suggestions) >= 6: break if len(suggestions) >= 6: break return jsonify({"suggestions": suggestions[:6]}) # ── AI CHAT ENDPOINT ────────────────────────────────────────────────────── @analytics_bp.route("/api/analytics/ai-chat", methods=["POST"]) @client_required def api_analytics_ai_chat(): """ Accepts: { question, project_id, parent_project_id, project_code, project_name, project_type } Fetches fresh analytics data for the project, builds a rich context, calls the OpenAI chat completions API, and streams back the answer. """ import urllib.request body = request.get_json(force=True, silent=True) or {} question = (body.get("question") or "").strip() project_id = body.get("project_id") parent_project_id = body.get("parent_project_id") project_code = body.get("project_code", "") project_name = body.get("project_name", "") project_type = body.get("project_type", "") if not question: return _err("No question provided.") # ── Fetch analytics data ───────────────────────────────────────────────── db = _client_db() analytics_context = "" if db: try: pid = int(project_id) if project_id else None ppid = int(parent_project_id) if parent_project_id else None data = get_analytics_from_proc(db, pid, ppid) if "error" not in data: kpis = data.get("kpis", {}) fin = data.get("financial", {}) loi = data.get("loi_kpi", {}) quality = data.get("quality", {}) funnel = data.get("funnel", {}) fraud = data.get("fraud_breakdown", {}) status_d = data.get("status_distribution", []) country = data.get("country_list", []) supplier = data.get("supplier_performance", []) device = data.get("device_distribution", []) analytics_context = f""" PROJECT DETAILS: - Code: {project_code} - Name: {project_name} - Type: {'Group Project' if project_type == 'G' else 'Project'} SURVEY KPIs: - Total Projects: {kpis.get('total_projects')} - Total Responses: {kpis.get('total_responses')} - Completes: {kpis.get('completes')} - Terminates: {kpis.get('terminates')} - Dropouts: {kpis.get('drops')} - Fraud Events: {kpis.get('fraud_events')} - Conversion Rate (CVR): {kpis.get('conversion_rate')}% - Fraud Rate: {kpis.get('fraud_rate')}% - Avg LOI (Length of Interview): {kpis.get('avg_loi_min')} minutes FINANCIAL METRICS: - Total Project Spend: ${fin.get('total_project_spend')} - Total Supplier Cost: ${fin.get('total_supplier_cost')} - Gross Margin: ${fin.get('gross_margin')} - Margin %: {round((fin.get('margin_pct') or 0) * 100, 2)}% - Avg Project CPI: ${fin.get('avg_project_cpi')} - Avg Supplier CPI: ${fin.get('avg_supplier_cpi')} - Cost Wasted on Fraud: ${fin.get('cost_wasted_on_fraud')} - Effective Cost per Clean Complete: ${fin.get('effective_cost_per_clean_complete')} SURVEY FUNNEL: - Gross Traffic: {funnel.get('gross_traffic')} - After Fraud Filter: {funnel.get('after_fraud')} - Reached Survey: {funnel.get('reached_survey')} - Completed Survey: {funnel.get('completed_survey')} - Incidence Rate (IR): {funnel.get('incidence_rate')}% - Effective Yield: {funnel.get('effective_yield')}% LOI (LENGTH OF INTERVIEW): - Avg LOI (Completes): {loi.get('avg_loi_completes')} min - Median LOI (Completes): {loi.get('median_loi_completes')} min - Max LOI: {loi.get('max_loi_complete')} min - Min LOI: {loi.get('min_loi_complete')} min - Speeders (<3 min): {loi.get('speeders')} - Speeder Rate: {loi.get('speeder_rate_pct')}% - Avg LOI (Terminates): {loi.get('avg_loi_terminates')} min - Avg LOI (Drops): {loi.get('avg_loi_drops')} min QUALITY METRICS: - Avg Composite Score (Completes): {quality.get('avg_composite_completes')} - Avg Composite Score (Fraud): {quality.get('avg_composite_fraud')} - Avg Threat Score (RD Failures): {quality.get('avg_threat_score_rd_failures')} - Avg Threat Potential (Flagged): {quality.get('avg_threat_potential_flagged')} - High Threat Count (score ≥50): {quality.get('high_threat_count')} - High Threat Potential (score ≥75): {quality.get('high_threat_potential_count')} - Quality Pass Rate: {quality.get('quality_pass_rate')}% FRAUD BREAKDOWN: - Total Flagged: {fraud.get('total_flagged')} - Fraud %: {fraud.get('fraud_pct')}% - By Type: {json.dumps(fraud.get('breakdown', []))} STATUS DISTRIBUTION (top statuses): {json.dumps(status_d[:8], indent=2)} TOP COUNTRIES (by completes): {json.dumps(country[:5], indent=2)} SUPPLIER PERFORMANCE (top suppliers): {json.dumps(supplier[:5], indent=2)} DEVICE DISTRIBUTION: {json.dumps(device, indent=2)} """.strip() else: analytics_context = f"Project '{project_name}' (Code: {project_code}) — No data found in database." except Exception: traceback.print_exc() analytics_context = f"Project '{project_name}' (Code: {project_code}) — Data could not be retrieved." else: analytics_context = "No database configured. Answering based on general knowledge only." # ── Build system prompt ────────────────────────────────────────────────── system_prompt = f"""You are an expert Market Research Analytics Assistant inside MRReports. You answer questions about survey project data accurately and concisely. CURRENT PROJECT DATA: {analytics_context} SECTION DEFINITIONS — these are strict boundaries. Never mix fields across sections: KPIs (Key Performance Indicators) = ONLY these fields: Total Projects, Total Responses, Completes, Terminates, Dropouts, Conversion Rate (CVR) FRAUD section = Total Flagged, Duplicate IP, Duplicate Supplier, Proxy Validation, RDText Failure, Geo IP Mismatch, RD Failure, Fraud User, Rejected Identifier, Fraud Rate %, Fraud Events FINANCIAL section = Total Project Spend, Total Supplier Cost, Gross Margin, Margin %, Avg Project CPI, Avg Supplier CPI, Cost Wasted on Fraud, Effective Cost per Clean Complete LOI section = Avg LOI, Median LOI, Max LOI, Min LOI, Speeders, Speeder Rate, Avg LOI Terminates, Avg LOI Drops QUALITY section = Avg Composite Score, High Threat Count, Quality Pass Rate FUNNEL section = Gross Traffic, After Fraud Filter, Reached Survey, Completed Survey, IR (Incidence Rate), Effective Yield STRICT RULES: 1. Answer ONLY the section the user asked about. If they ask for KPIs, return ONLY the 6 KPI fields. Nothing from fraud, LOI, financial, or any other section. 2. NEVER use markdown headers (###, ##, #). 3. Use plain bullet points for lists. Single sentence for single-metric answers. 4. Use exact numbers from the data. Format: $ for money, % for rates. 5. If a value is None or not available, say "not available". 6. Stop as soon as the question is fully answered. Do not add summaries or extra context. 7. If asked to explain a term, give one sentence definition then the value only. """ # ── Call OpenAI API ────────────────────────────────────────────────────── openai_key = os.getenv("OPENAI_API_KEY", "") if not openai_key: return _err("OpenAI API key not configured in .env", 500) payload = json.dumps({ "model": "gpt-4o-mini", "messages": [ {"role": "system", "content": system_prompt}, {"role": "user", "content": question}, ], "max_tokens": 800, "temperature": 0.3, }).encode("utf-8") req = urllib.request.Request( "https://api.openai.com/v1/chat/completions", data=payload, headers={ "Content-Type": "application/json", "Authorization": f"Bearer {openai_key}", }, method="POST", ) try: with urllib.request.urlopen(req, timeout=30) as resp: result = json.loads(resp.read().decode("utf-8")) answer = result["choices"][0]["message"]["content"] return jsonify({"answer": answer}) except urllib.error.HTTPError as e: err_body = e.read().decode("utf-8", errors="replace") traceback.print_exc() return _err(f"OpenAI error {e.code}: {err_body}", 502) except Exception as e: traceback.print_exc() return _err(f"Failed to call OpenAI: {str(e)}", 502)