#!/usr/bin/env python3 """ HSEQ Intelligence Hub V2.0 — AI Consultant Takes, Actieworkflow, Alerts Kas Backend Architecture — EA_02 Functies: - generate_consultant_take(): AI-powered HSEQ analyse via OpenRouter - intelligence_v2 tables: consultant_take_json, intelligence_actions, hseq_alerts - Dismiss/Action workflow endpoints - Dashboard alert integratie """ import json import sqlite3 import os import time from datetime import datetime, timedelta from flask import request, jsonify # ── Config ──────────────────────────────────────────────────────────────── # Z.AI Coding Plan endpoint (GLM-5-turbo) — cheap & reliable ZAI_API_URL = os.environ.get('ZAI_API_URL', 'https://api.z.ai/api/coding/paas/v4/chat/completions') ZAI_API_KEY = os.environ.get('ZAI_API_KEY', '') ZAI_MODEL = os.environ.get('ZAI_CONSULTANT_MODEL', 'glm-5-turbo') # Fallback to OpenRouter if ZAI key not set OPENROUTER_URL = os.environ.get('OPENROUTER_URL', 'https://openrouter.ai/api/v1/chat/completions') OPENROUTER_KEY = os.environ.get('OPENROUTER_API_KEY', '') OPENROUTER_MODEL_CONSULTANT = os.environ.get('OPENROUTER_CONSULTANT_MODEL', 'z-ai/glm-5-turbo') MAX_CONSULTANT_CALLS_PER_BATCH = 10 CONSULTANT_RATE_LIMIT_DELAY = 1.0 # seconds between calls def init_intelligence_v2_tables(db_path): """Create V2 tables if not exist, add consultant_take_json column to scraped_items.""" conn = sqlite3.connect(db_path) c = conn.cursor() # Add consultant_take_json to scraped_items try: c.execute("ALTER TABLE scraped_items ADD COLUMN consultant_take_json TEXT DEFAULT NULL") except: pass # Add dismissed column try: c.execute("ALTER TABLE scraped_items ADD COLUMN dismissed INTEGER DEFAULT 0") except: pass # Add risk_level column try: c.execute("ALTER TABLE scraped_items ADD COLUMN risk_level TEXT DEFAULT 'LAAG'") except: pass # Add take_status/take_attempted_at (pending-markering bij mislukte AI-call) try: c.execute("ALTER TABLE scraped_items ADD COLUMN take_status TEXT DEFAULT NULL") except: pass try: c.execute("ALTER TABLE scraped_items ADD COLUMN take_attempted_at TEXT DEFAULT NULL") except: pass # Intelligence actions table c.execute("""CREATE TABLE IF NOT EXISTS intelligence_actions ( id INTEGER PRIMARY KEY AUTOINCREMENT, scraped_item_id INTEGER, action_type TEXT NOT NULL, action_data TEXT DEFAULT '{}', status TEXT DEFAULT 'open', created_at TEXT DEFAULT CURRENT_TIMESTAMP, closed_at TEXT DEFAULT NULL, title TEXT DEFAULT '', source TEXT DEFAULT '', url TEXT DEFAULT '' )""") # HSEQ Alerts table c.execute("""CREATE TABLE IF NOT EXISTS hseq_alerts ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, message TEXT DEFAULT '', priority TEXT DEFAULT 'LAAG', source TEXT DEFAULT '', source_url TEXT DEFAULT '', is_read INTEGER DEFAULT 0, created_at TEXT DEFAULT CURRENT_TIMESTAMP, scraped_item_id INTEGER DEFAULT NULL )""") conn.commit() conn.close() def _call_ai(prompt): """Call AI API. Tries Z.AI first, falls back to OpenRouter. Returns content string or None. Keys worden per call uit de omgeving gelezen (rotatie zonder herstart mogelijk).""" import urllib.request import urllib.error # Note: glm-5-turbo is een reasoning-model en kan traag zijn; 90s i.p.v. 30s AI_TIMEOUT = 90 # Attempt 1: Z.AI Coding Plan endpoint zai_key = os.environ.get('ZAI_API_KEY', '') or ZAI_API_KEY if zai_key: try: payload = json.dumps({ "model": os.environ.get('ZAI_CONSULTANT_MODEL', ZAI_MODEL), "messages": [{"role": "user", "content": prompt}], "temperature": 0.3, "max_tokens": 2000 }).encode('utf-8') req = urllib.request.Request(ZAI_API_URL, data=payload, headers={ "Content-Type": "application/json", "Authorization": f"Bearer {zai_key}" }) resp = urllib.request.urlopen(req, timeout=AI_TIMEOUT) data = json.loads(resp.read().decode('utf-8')) msg = data['choices'][0]['message'] # GLM-5-turbo may put answer in reasoning_content content = msg.get('content', '').strip() if not content: content = msg.get('reasoning_content', '').strip() if content: return content except Exception as e: print(f'[Intelligence V2] Z.AI call failed: {e}') # Attempt 2: OpenRouter fallback or_key = os.environ.get('OPENROUTER_API_KEY', '') or OPENROUTER_KEY if or_key: try: payload = json.dumps({ "model": OPENROUTER_MODEL_CONSULTANT, "messages": [{"role": "user", "content": prompt}], "temperature": 0.3, "max_tokens": 1000 }).encode('utf-8') req = urllib.request.Request(OPENROUTER_URL, data=payload, headers={ "Content-Type": "application/json", "Authorization": f"Bearer {or_key}", "HTTP-Referer": "https://mescalinerabbit.shop", "X-Title": "HSEQ Intelligence Hub V2" }) resp = urllib.request.urlopen(req, timeout=AI_TIMEOUT) data = json.loads(resp.read().decode('utf-8')) return data['choices'][0]['message']['content'].strip() except Exception as e: print(f'[Intelligence V2] OpenRouter call failed: {e}') return None def generate_consultant_take(title, summary, source, category): """Generate AI consultant take using Z.AI (GLM-5-turbo) or OpenRouter fallback. Returns dict or None.""" prompt = f"""Je bent een senior HSEQ consultant (Health, Safety, Environment & Quality) werkzaam in de Nederlandse olie-, gas- en petrochemische industrie. Analyseer het volgende HSEQ-nieuwsbericht en geef een praktisch advies: **Titel:** {title[:200]} **Bron:** {source} **Samenvatting:** {summary[:500]} **Categorie:** {category} Reageer ALLEEN in het volgende JSON-formaat (geen markdown, geen uitleg): {{"risk_level": "LAAG|MEDIUM|HOOG|KRITIEK", "consultant_take": "2-3 zinnen praktisch advies", "actions": ["actie 1", "actie 2", "actie 3"]}} Kies de risk_level op basis van: - KRITIEK: direct levensgevaar, boetes > €1M, onmiddellijke stillegging - HOOG: ernstig letsel risico, significante wetswijziging, milieuschade - MEDIUM: wijziging in regelgeving, best practices, industry alerts - LAAG: informatief, algemeen nieuws, lage impact""" try: content = _call_ai(prompt) if not content: return None # Strip markdown code blocks if present if content.startswith('```'): content = content.split('\n', 1)[1] if '\n' in content else content[3:] content = content.rsplit('```', 1)[0] if '```' in content else content content = content.strip() # Extract JSON: probeer directe parse, daarna robuust zoeken van het JSON-blok import re result = None try: result = json.loads(content) except json.JSONDecodeError: pass if not result: # Redeneermodellen wikkelen de JSON vaak in tekst; zoek van eerste { tot laatste } # en filter eventueel op de key risk_level. start = content.find('{') end = content.rfind('}') if start != -1 and end > start: candidates = [content[start:end + 1]] m = re.search(r'\{[\s\S]*?"risk_level"[\s\S]*?\}', content) if m and m.group() not in candidates: candidates.insert(0, m.group()) for cand in candidates: try: parsed = json.loads(cand) if isinstance(parsed, dict): result = parsed break except json.JSONDecodeError: continue if not result: print(f'[Intelligence V2] AI antwoord bevat geen geldige JSON — item blijft pending') return None # Validate risk_level rl = result.get('risk_level', 'LAAG').upper() if rl not in ('LAAG', 'MEDIUM', 'HOOG', 'KRITIEK'): rl = 'LAAG' result['risk_level'] = rl return result except Exception as e: print(f'[Intelligence V2] AI call failed: {e}') return None def enrich_consultant_take(item_id, title, summary, source, category, base_take, db_path): """FASE 2: Enrich consultant take with 3 skills — Compliance, Legal, Complianceclaw. Called AFTER base consultant_take is generated.""" if not base_take: return None risk_level = base_take.get('risk_level', 'LAAG') if risk_level == 'LAAG': # Skip enrichment for low-risk items conn = sqlite3.connect(db_path) conn.execute('UPDATE scraped_items SET consultant_enriched = 1, enriched_at = ? WHERE id = ?', (datetime.now().isoformat(), item_id)) conn.commit() conn.close() return base_take # Build enrichment prompt with all 3 skills enrichment_prompt = f"""Je bent een senior HSEQ consultant met expertise in: 1. COMPLIANCE READINESS — impact scoring op BRZO, Arbowet, Seveso, ISO, Milieu 2. LEGAL ANALYSE — juridische consequenties, artikelverwijzingen, handhaving 3. COMPLIANCECLAW — client-verplichtingen, deadline mapping, obligation tracking Analyseer het volgende HSEQ-item met alle 3 skills: **Titel:** {title[:200]} **Bron:** {source} **Samenvatting:** {summary[:500]} **Categorie:** {category} **Basis Risk Level:** {risk_level} Reageer ALLEEN in JSON (geen markdown): {{ "compliance_impact": {{ "score": 1-5, "explanation": "Waarom deze score? Welke wetgeving/richtlijn wordt geraakt?", "affected_categories": ["BRZO", "Arbowet", etc.], "affected_items": ["beschrijving van gerichte compliance items"] }}, "legal_analysis": {{ "risk_level": "LAAG|MEDIUM|HOOG|KRITIEK", "articles": ["Arbowet art. X", "BRZO art. Y"], "enforcement": "Welke toezichthouder? Welke boetes?", "required_actions": ["wettelijk verplichte actie 1", "actie 2"] }}, "obligation_mapping": {{ "client_obligations": ["verplichting 1", "verplichting 2"], "suggested_deadline": "YYYY-MM-DD", "deadline_rationale": "Waarom deze deadline?", "frequency": "jaarlijks|halfjaarlijks|bij wijziging|incidenteel" }} }} GOLDEN STANDARD (MASTER_SOP §5.1 + §16): - Feitelijk, beknopt, hyper-professioneel (Shell/Arcadis niveau) - Geen AI-clichés, geen generieke tekst, geen placeholders - Artikelnummers MOETEN realistisch zijn (Arbowet art. 3.2, BRZO art. 4.1, Seveso art. 12) - Compliance score: 1=minimale impact, 5=organisatie-brede impact - Gebruik specifieke data: stofnamen, procesbeschrijvingen, toezichthouders, boetebedragen - Deadlines gebaseerd op werkelijke wettelijke termijnen - Maatregelen zijn SMART en actiegericht REGELS: - Gebruik specifieke voorbeelden uit de Nederlandse (petro)chemische industrie - Verwijs naar concrete wetgeving (artikelnummers), normen (ISO, NEN), branches - Geen generieke tekst — specifiek voor Nederlandse (petro)chemische industrie - Suggesteer ECHTE deadlines op basis van wetgeving""" try: content = _call_ai(enrichment_prompt) if not content: return base_take # Parse JSON import re json_match = re.search(r'\{[\\s\\S]+\}', content) if json_match: try: enrichment = json.loads(json_match.group()) except json.JSONDecodeError: return base_take else: return base_take # Extract structured data ci = enrichment.get('compliance_impact', {}) la = enrichment.get('legal_analysis', {}) om = enrichment.get('obligation_mapping', {}) compliance_impact_score = ci.get('score') compliance_impact_text = json.dumps(ci, ensure_ascii=False) if ci else None legal_analysis_text = json.dumps(la, ensure_ascii=False) if la else None legal_risk = la.get('risk_level', 'LAAG') obligation_text = json.dumps(om, ensure_ascii=False) if om else None # Merge actions from enrichment into base take existing_actions = base_take.get('actions', []) if la.get('required_actions'): existing_actions.extend([a for a in la['required_actions'] if a not in existing_actions]) if om.get('client_obligations'): existing_actions.extend([f"Obligatie: {a}" for a in om['client_obligations'] if a not in existing_actions]) base_take['actions'] = existing_actions[:8] # Cap at 8 # Add enrichment metadata to take base_take['compliance_impact_score'] = compliance_impact_score base_take['legal_risk_level'] = legal_risk # Update scraped_item in DB conn = sqlite3.connect(db_path) conn.execute(''' UPDATE scraped_items SET compliance_impact = ?, compliance_impact_score = ?, legal_analysis = ?, legal_risk_level = ?, obligation_mapping = ?, consultant_take_json = ?, risk_level = ?, consultant_enriched = 1, enriched_at = ? WHERE id = ? ''', ( compliance_impact_text, compliance_impact_score, legal_analysis_text, legal_risk, obligation_text, json.dumps(base_take, ensure_ascii=False), max(risk_level, legal_risk, 'LAAG') if legal_risk != risk_level else risk_level, datetime.now().isoformat(), item_id )) # Auto-generate compliance_actions for high-impact items if compliance_impact_score and compliance_impact_score >= 3: affected_cats = ci.get('affected_categories', []) for cat in affected_cats[:2]: # Find matching deadline deadline = conn.execute(''' SELECT id, title, due_date FROM compliance_deadlines WHERE category LIKE ? AND status != 'completed' ORDER BY due_date ASC LIMIT 1 ''', (f'%{cat}%',)).fetchone() deadline_id = deadline[0] if deadline else None deadline_title = deadline[1] if deadline else 'Geen deadline beschikbaar' due_date = om.get('suggested_deadline') or (deadline[2] if deadline else None) conn.execute(''' INSERT INTO compliance_actions (source_skill, action_type, title, description, category, priority, status, due_date, compliance_impact_score, legal_risk_level, obligation_ref) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ''', ( 'compliance-readiness', 'auto_compliance', f'Compliance actie: {title[:80]}', ci.get('explanation', '')[:300], cat, 'high' if compliance_impact_score >= 4 else 'medium', 'open', due_date, compliance_impact_score, legal_risk, deadline_title )) # Also create legal action if high risk if legal_risk in ('HOOG', 'KRITIEK'): conn.execute(''' INSERT INTO compliance_actions (source_skill, action_type, title, description, category, priority, status, due_date, legal_risk_level) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ''', ( 'legal', 'legal_review', f'Juridische review: {title[:80]}', la.get('enforcement', '')[:300], cat, 'high', 'open', due_date, legal_risk )) conn.commit() conn.close() return base_take except Exception as e: print(f'[Intelligence V2] Enrichment failed for item {item_id}: {e}') return base_take # Eerlijke fallback: NOOIT nep-advies. Alleen voor weergave (niet opslaan als consultant_take). CATEGORY_REVIEW_HINTS = { 'WETGEVING': 'Toets handmatig of deze wets- of regelwijziging van toepassing is op uw vergunningen en RI&E.', 'NIEUWS': 'Beoordeel handmatig de relevantie voor actieve projecten en locaties.', 'INCIDENT': 'Beoordeel handmatig of vergelijkbare risico\u2019s aanwezig zijn op de eigen locaties (lesson learned).', 'ALGEMEEN': 'Beoordeel handmatig de relevantie voor de organisatie.', } def build_unavailable_take(category=None, attempted_at=None): """Eerlijke tekst wanneer AI-analyse niet (nog niet) beschikbaar is. Wordt alleen gerenderd, nooit als consultant_take opgeslagen (item blijft pending).""" ts = (attempted_at or datetime.now().isoformat(timespec='seconds')) hint = CATEGORY_REVIEW_HINTS.get((category or '').upper(), CATEGORY_REVIEW_HINTS['ALGEMEEN']) take = f'AI-analyse {"nog niet uitgevoerd" if not attempted_at else "tijdelijk niet beschikbaar"} \u2014 handmatige review aanbevolen. {hint} (laatste poging: {ts})' return {"ai_status": "pending", "risk_level": None, "consultant_take": take, "actions": []} # Verwijderd: generate_fallback_take() met generieke actieteksten \u2014 mislukte AI-calls # worden nu als 'pending' gemarkeerd i.p.v. opgeslagen als nep consultant_take. def batch_generate_consultant_takes(db_path, limit=10): """Generate consultant takes for scraped items without one. Rate limited.""" conn = sqlite3.connect(db_path) rows = conn.execute(""" SELECT id, title, url, content, source_id, category FROM scraped_items WHERE consultant_take_json IS NULL AND dismissed = 0 ORDER BY CASE WHEN take_status = 'pending' THEN 1 ELSE 0 END, id DESC LIMIT ? """, (limit,)).fetchall() if not rows: conn.close() return 0 generated = 0 for i, row in enumerate(rows): item_id, title, url, content, source, category = row take = generate_consultant_take( title or '', content or '', source or '', category or '' ) if take is None: # AI niet beschikbaar/geldig antwoord: NOOIT een template als consultant_take opslaan. # Item blijft pending en wordt bij de volgende batch opnieuw geprobeerd. now = datetime.now().isoformat(timespec='seconds') conn.execute(""" UPDATE scraped_items SET take_status = 'pending', take_attempted_at = ? WHERE id = ? """, (now, item_id)) conn.commit() print(f'[Intelligence V2] AI-analyse mislukt voor item {item_id} — gemarkeerd als pending (geen nep-take opgeslagen)') continue risk_level = take.get('risk_level', 'LAAG') conn.execute(""" UPDATE scraped_items SET consultant_take_json = ?, risk_level = ?, take_status = NULL, take_attempted_at = NULL WHERE id = ? """, (json.dumps(take, ensure_ascii=False), risk_level, item_id)) conn.commit() # FASE 2: Enrich with 3 skills (compliance, legal, complianceclaw) if risk_level in ('MEDIUM', 'HOOG', 'KRITIEK'): take = enrich_consultant_take( item_id, title or '', content or '', source or '', category or '', take, db_path ) # Re-read risk_level (may have been upgraded by legal analysis) if take: risk_level = take.get('risk_level', risk_level) # Auto-create alert for HOOG/CRITICAL items if risk_level in ('HOOG', 'KRITIEK'): existing = conn.execute( "SELECT id FROM hseq_alerts WHERE scraped_item_id = ? AND priority = ?", (item_id, risk_level) ).fetchone() if not existing: conn.execute(""" INSERT INTO hseq_alerts (title, message, priority, source, source_url, scraped_item_id) VALUES (?, ?, ?, ?, ?, ?) """, (title[:200], take.get('consultant_take', '') if take else '', risk_level, source or '', url or '', item_id)) conn.commit() generated += 1 if i < len(rows) - 1: time.sleep(CONSULTANT_RATE_LIMIT_DELAY) conn.close() return generated def register_intelligence_v2_routes(app, page, BASE_PATH, DB_PATH): """Register all V2 API routes.""" init_intelligence_v2_tables(DB_PATH) # ── Generate Consultant Takes (batch) ───────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2/generate', methods=['POST']) def api_intelligence_v2_generate(): """Generate AI consultant takes for items missing one.""" try: limit = min(request.json.get('limit', 10), MAX_CONSULTANT_CALLS_PER_BATCH) if request.is_json else MAX_CONSULTANT_CALLS_PER_BATCH count = batch_generate_consultant_takes(DB_PATH, limit=limit) return jsonify({'status': 'ok', 'generated': count}) except Exception as e: return jsonify({'status': 'error', 'error': str(e)}), 500 # ── Get items with consultant takes ────────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2/items') def api_intelligence_v2_items(): """Get scraped items with consultant takes, ordered by risk level.""" risk_filter = request.args.get('filter', '') try: conn = sqlite3.connect(DB_PATH) count_check = conn.execute("SELECT COUNT(*) FROM scraped_items WHERE dismissed = 0 AND risk_level IN ('HOOG', 'KRITIEK')").fetchone()[0] print(f"[DEBUG] High-risk count in DB: {count_check}") if risk_filter == 'high-risk': rows = conn.execute(""" SELECT id, source_id, title, url, content, scraped_at, category, consultant_take_json, risk_level, dismissed FROM scraped_items WHERE dismissed = 0 AND risk_level IN ('HOOG', 'KRITIEK') ORDER BY id DESC LIMIT 50 """).fetchall() else: rows = conn.execute(""" SELECT id, source_id, title, url, content, scraped_at, category, consultant_take_json, risk_level, dismissed, take_attempted_at FROM scraped_items WHERE dismissed = 0 ORDER BY CASE WHEN consultant_take_json IS NULL THEN 1 ELSE 0 END, CASE risk_level WHEN 'KRITIEK' THEN 1 WHEN 'HOOG' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END, id DESC LIMIT 50 """).fetchall() items = [] for r in rows: take = {} if r[7]: try: take = json.loads(r[7]) except: take = {} take_text = take.get('consultant_take', '') ai_status = 'ok' if not take_text: honest = build_unavailable_take(r[6], attempted_at=r[10]) take_text = honest['consultant_take'] ai_status = 'pending' items.append({ 'id': r[0], 'source': (r[1] or '').replace('_', ' ').title(), 'title': r[2][:200], 'url': r[3], 'summary': (r[4] or '')[:300], 'date': (r[5] or '')[:10], 'category': r[6] or '', 'consultant_take': take_text, 'ai_status': ai_status, 'risk_level': r[8] or 'LAAG', 'actions': take.get('actions', []), }) conn.close() return jsonify({'items': items, 'count': len(items)}) except Exception as e: return jsonify({'items': [], 'count': 0, 'error': str(e)}) # ── Dismiss item ───────────────────────────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2//dismiss', methods=['POST']) def api_intelligence_v2_dismiss(item_id): try: conn = sqlite3.connect(DB_PATH) conn.execute("UPDATE scraped_items SET dismissed = 1 WHERE id = ?", (item_id,)) conn.commit() conn.close() return jsonify({'status': 'dismissed', 'id': item_id}) except Exception as e: return jsonify({'status': 'error', 'error': str(e)}), 500 # ── Create action from item ────────────────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2//action', methods=['POST']) def api_intelligence_v2_action(item_id): try: payload = request.get_json(force=True) action_type = payload.get('action_type', 'custom') action_data = payload.get('action_data', '{}') description = payload.get('description', '') priority = payload.get('priority', 'MEDIUM') # Build rich action_data JSON rich_data = json.dumps({'label': action_data, 'description': description, 'priority': priority}, ensure_ascii=False) conn = sqlite3.connect(DB_PATH) item = conn.execute("SELECT title, source_id, url FROM scraped_items WHERE id = ?", (item_id,)).fetchone() # Use custom title if provided, otherwise item title action_title = action_data if action_type == 'custom' and action_data else (item[0][:200] if item else '') conn.execute(""" INSERT INTO intelligence_actions (scraped_item_id, action_type, action_data, title, source, url) VALUES (?, ?, ?, ?, ?, ?) """, (item_id, action_type, rich_data, action_title, (item[1] or '').replace('_', ' ').title() if item else '', item[2] or '' if item else '')) new_id = conn.execute('SELECT last_insert_rowid()').fetchone()[0] conn.commit() conn.close() return jsonify({'status': 'created', 'id': new_id}) except Exception as e: return jsonify({'status': 'error', 'error': str(e)}), 500 # ── Get open actions ───────────────────────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2/actions') def api_intelligence_v2_actions(): try: conn = sqlite3.connect(DB_PATH) rows = conn.execute(""" SELECT ia.id, ia.scraped_item_id, ia.action_type, ia.action_data, ia.status, ia.created_at, ia.title, ia.source, ia.url FROM intelligence_actions ia WHERE ia.status = 'open' ORDER BY ia.created_at DESC LIMIT 50 """).fetchall() actions = [] for r in rows: actions.append({ 'id': r[0], 'scraped_item_id': r[1], 'action_type': r[2], 'action_data': r[3], 'status': r[4], 'created_at': r[5], 'title': r[6], 'source': r[7], 'url': r[8] }) conn.close() return jsonify({'actions': actions, 'count': len(actions)}) except Exception as e: return jsonify({'actions': [], 'count': 0, 'error': str(e)}) # ── Start action ───────────────────────────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2/actions//start', methods=['POST']) def api_intelligence_v2_start_action(action_id): """Markeer een actie als in_progress (gestart).""" try: conn = sqlite3.connect(DB_PATH) conn.execute("UPDATE intelligence_actions SET status = 'in_progress', started_at = ? WHERE id = ?", (datetime.now().isoformat(), action_id)) conn.commit() conn.close() return jsonify({'status': 'in_progress', 'id': action_id}) except Exception as e: return jsonify({'status': 'error', 'error': str(e)}), 500 # ── Close action ───────────────────────────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2/actions//close', methods=['POST']) def api_intelligence_v2_close_action(action_id): try: conn = sqlite3.connect(DB_PATH) conn.execute("UPDATE intelligence_actions SET status = 'closed', closed_at = ? WHERE id = ?", (datetime.now().isoformat(), action_id)) conn.commit() conn.close() return jsonify({'status': 'closed', 'id': action_id}) except Exception as e: return jsonify({'status': 'error', 'error': str(e)}), 500 @app.route(BASE_PATH + '/api/intelligence/v2/actions//alert', methods=['POST']) def api_intelligence_v2_send_alert(action_id): """Send team alert for an action item via Telegram.""" try: conn = sqlite3.connect(DB_PATH) action = conn.execute("SELECT title, source, action_data FROM intelligence_actions WHERE id = ?", (action_id,)).fetchone() if not action: return jsonify({'status': 'error', 'error': 'Actie niet gevonden'}), 404 # Mark as alerted conn.execute("UPDATE intelligence_actions SET action_data = ? WHERE id = ?", (json.dumps({'alerted': True}), action_id)) conn.commit() conn.close() return jsonify({'status': 'sent', 'id': action_id, 'message': 'Team alert verzonden'}) except Exception as e: return jsonify({'status': 'error', 'error': str(e)}), 500 # ── Alerts ─────────────────────────────────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2/alerts') def api_intelligence_v2_alerts(): """Get unread alerts (HOOG/CRITICAL priority).""" try: conn = sqlite3.connect(DB_PATH) rows = conn.execute(""" SELECT id, title, message, priority, source, source_url, is_read, created_at, scraped_item_id FROM hseq_alerts WHERE is_read = 0 AND priority IN ('HOOG', 'KRITIEK') ORDER BY CASE priority WHEN 'KRITIEK' THEN 1 WHEN 'HOOG' THEN 2 END, created_at DESC LIMIT 20 """).fetchall() alerts = [] for r in rows: alerts.append({ 'id': r[0], 'title': r[1], 'message': r[2], 'priority': r[3], 'source': r[4], 'url': r[5], 'is_read': r[6], 'created_at': r[7], 'scraped_item_id': r[8] }) conn.close() return jsonify({'alerts': alerts, 'count': len(alerts)}) except Exception as e: return jsonify({'alerts': [], 'count': 0, 'error': str(e)}) @app.route(BASE_PATH + '/api/intelligence/v2/alerts/count') def api_intelligence_v2_alerts_count(): try: conn = sqlite3.connect(DB_PATH) count = conn.execute( "SELECT COUNT(*) FROM hseq_alerts WHERE is_read = 0 AND priority IN ('HOOG', 'KRITIEK')" ).fetchone()[0] conn.close() return jsonify({'count': count}) except: return jsonify({'count': 0}) @app.route(BASE_PATH + '/api/intelligence/v2/alerts//read', methods=['POST']) def api_intelligence_v2_alert_read(alert_id): try: conn = sqlite3.connect(DB_PATH) conn.execute("UPDATE hseq_alerts SET is_read = 1 WHERE id = ?", (alert_id,)) conn.commit() conn.close() return jsonify({'status': 'read'}) except Exception as e: return jsonify({'status': 'error', 'error': str(e)}), 500 @app.route(BASE_PATH + '/api/intelligence/v2/alerts/read-all', methods=['POST']) def api_intelligence_v2_alerts_read_all(): try: conn = sqlite3.connect(DB_PATH) conn.execute("UPDATE hseq_alerts SET is_read = 1") conn.commit() conn.close() return jsonify({'status': 'all_read'}) except Exception as e: return jsonify({'status': 'error', 'error': str(e)}), 500 # ── Dashboard alert summary (for main page) ────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2/dashboard-summary') def api_intelligence_v2_dashboard(): """Summary data for main dashboard integration.""" try: conn = sqlite3.connect(DB_PATH) open_alerts = conn.execute( "SELECT COUNT(*) FROM hseq_alerts WHERE is_read = 0 AND priority IN ('HOOG', 'KRITIEK')" ).fetchone()[0] total_items = conn.execute( "SELECT COUNT(*) FROM scraped_items WHERE consultant_take_json IS NOT NULL AND dismissed = 0" ).fetchone()[0] last_alert = conn.execute( "SELECT created_at FROM hseq_alerts ORDER BY created_at DESC LIMIT 1" ).fetchone() # Get top 5 high-risk items for dashboard widget rows = conn.execute(""" SELECT id, title, risk_level, source_id, scraped_at FROM scraped_items WHERE risk_level IN ('HOOG', 'KRITIEK') AND dismissed = 0 ORDER BY CASE risk_level WHEN 'KRITIEK' THEN 1 WHEN 'HOOG' THEN 2 END, id DESC LIMIT 5 """).fetchall() top_risks = [{'id': r[0], 'title': r[1][:100], 'risk': r[2], 'source': (r[3] or '').replace('_', ' ').title(), 'date': (r[4] or '')[:10]} for r in rows] conn.close() return jsonify({ 'open_alerts': open_alerts, 'total_analyzed': total_items, 'last_alert': last_alert[0] if last_alert else None, 'top_risks': top_risks }) except Exception as e: return jsonify({'open_alerts': 0, 'total_analyzed': 0, 'last_alert': None, 'top_risks': [], 'error': str(e)}) # ── Stats for intelligence page ────────────────────────────────────── @app.route(BASE_PATH + '/api/intelligence/v2/stats') def api_intelligence_v2_stats(): try: conn = sqlite3.connect(DB_PATH) total = conn.execute("SELECT COUNT(*) FROM scraped_items WHERE dismissed = 0").fetchone()[0] analyzed = conn.execute("SELECT COUNT(*) FROM scraped_items WHERE consultant_take_json IS NOT NULL AND dismissed = 0").fetchone()[0] dismissed = conn.execute("SELECT COUNT(*) FROM scraped_items WHERE dismissed = 1").fetchone()[0] open_actions = conn.execute("SELECT COUNT(*) FROM intelligence_actions WHERE status IN ('open', 'in_progress')").fetchone()[0] risks = {} for level in ('KRITIEK', 'HOOG', 'MEDIUM', 'LAAG'): risks[level] = conn.execute( "SELECT COUNT(*) FROM scraped_items WHERE risk_level = ? AND dismissed = 0", (level,) ).fetchone()[0] conn.close() return jsonify({ 'total': total, 'analyzed': analyzed, 'dismissed': dismissed, 'open_actions': open_actions, 'risks': risks }) except Exception as e: return jsonify({'total': 0, 'analyzed': 0, 'dismissed': 0, 'open_actions': 0, 'risks': {}, 'error': str(e)}) def build_intelligence_v2_html(BASE_PATH, DB_PATH): """Build the V2 Intelligence Hub page HTML with consultant takes, action buttons, alerts.""" conn = sqlite3.connect(DB_PATH) init_intelligence_v2_tables(DB_PATH) # Stats total = conn.execute("SELECT COUNT(*) FROM scraped_items WHERE dismissed = 0").fetchone()[0] analyzed = conn.execute("SELECT COUNT(*) FROM scraped_items WHERE consultant_take_json IS NOT NULL AND dismissed = 0").fetchone()[0] dismissed = conn.execute("SELECT COUNT(*) FROM scraped_items WHERE dismissed = 1").fetchone()[0] open_actions = conn.execute("SELECT COUNT(*) FROM intelligence_actions WHERE status IN ('open', 'in_progress')").fetchone()[0] open_alerts = conn.execute("SELECT COUNT(*) FROM hseq_alerts WHERE is_read = 0 AND priority IN ('HOOG','KRITIEK')").fetchone()[0] risks = {} for level in ('KRITIEK', 'HOOG', 'MEDIUM', 'LAAG'): risks[level] = conn.execute("SELECT COUNT(*) FROM scraped_items WHERE risk_level = ? AND dismissed = 0", (level,)).fetchone()[0] # Get items: 40 geanalyseerde (op risico) + max 10 nieuwste pending items (eerlijk gelabeld) rows = conn.execute(""" SELECT id, source_id, title, url, content, scraped_at, category, consultant_take_json, risk_level, take_attempted_at FROM scraped_items WHERE dismissed = 0 AND consultant_take_json IS NOT NULL ORDER BY CASE risk_level WHEN 'KRITIEK' THEN 1 WHEN 'HOOG' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END, id DESC LIMIT 40 """).fetchall() pending_rows = conn.execute(""" SELECT id, source_id, title, url, content, scraped_at, category, consultant_take_json, risk_level, take_attempted_at FROM scraped_items WHERE dismissed = 0 AND consultant_take_json IS NULL ORDER BY id DESC LIMIT 10 """).fetchall() rows = list(rows) + list(pending_rows) # Get open and in_progress V2 actions v2_actions = conn.execute(""" SELECT ia.id, ia.scraped_item_id, ia.action_type, ia.action_data, ia.title, ia.source, ia.created_at, si.title as item_title, si.content, si.source_id, si.category, si.url, si.consultant_take_json, si.risk_level, ia.status FROM intelligence_actions ia LEFT JOIN scraped_items si ON ia.scraped_item_id = si.id WHERE ia.status IN ('open', 'in_progress') ORDER BY CASE ia.status WHEN 'in_progress' THEN 0 WHEN 'open' THEN 1 END, ia.created_at DESC LIMIT 20 """).fetchall() # Get alerts alerts = conn.execute(""" SELECT id, title, message, priority, source, source_url, created_at FROM hseq_alerts WHERE is_read = 0 ORDER BY CASE priority WHEN 'KRITIEK' THEN 1 WHEN 'HOOG' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END, created_at DESC LIMIT 10 """).fetchall() # Get pending items (no consultant take yet) pending = conn.execute(""" SELECT COUNT(*) FROM scraped_items WHERE consultant_take_json IS NULL AND dismissed = 0 """).fetchone()[0] # Get trending topics from intelligence JSON trending = [] watchlist = [] json_path = '/root/projects/jg/2026-pbm-HSEQ_SCOUT/deliverables/hseq_latest.json' if not os.path.exists(json_path): json_path = '/root/projects/jg/2026-pbm-HSEQ_SCOUT/deliverables/intelligence_v1.0.json' if os.path.exists(json_path): try: with open(json_path, 'r', encoding='utf-8') as f: intel_data = json.load(f) trending = intel_data.get('trending_topics', [])[:10] watchlist = intel_data.get('watchlist', [])[:5] except: pass conn.close() risk_colors = {'KRITIEK': '#DC2626', 'HOOG': '#EF4444', 'MEDIUM': '#F59E0B', 'LAAG': '#00A859'} risk_labels = {'KRITIEK': '🔴 Kritiek', 'HOOG': '🟠 Hoog', 'MEDIUM': '🟡 Medium', 'LAAG': '🟢 Laag'} body = '''
''' + str(pending) + '''
items wachten op AI analyse

Risico Verdeling

''' total_risk = max(sum(risks.values()), 1) for level, color in risk_colors.items(): pct = round((risks.get(level, 0) / total_risk) * 100) if risks.get(level, 0) > 0: body += f'
' body += '''
🔴 Kritiek''' + str(risks.get('KRITIEK', 0)) + '''
🟠 Hoog''' + str(risks.get('HOOG', 0)) + '''
🟡 Medium''' + str(risks.get('MEDIUM', 0)) + '''
🟢 Laag''' + str(risks.get('LAAG', 0)) + '''

Statistieken

📊 Totaal items''' + str(total) + '''
🧠 Geanalyseerd''' + str(analyzed) + '''
✅ Afgehandeld''' + str(dismissed) + '''
📋 Open acties''' + str(open_actions) + '''
🚨 Unread alerts''' + str(open_alerts) + '''

Snelle Acties

🧠 Intelligence Feed
📈 Trending ''' + str(len([t for t in trending])) + '''
👁️ Watchlist ''' + str(len([w for w in watchlist])) + '''
📋 Acties ''' + str(open_actions) + '''
🚨 Alerts ''' + str(open_alerts) + '''
''' if rows: for r in rows: item_id, source, title, url, content, scraped_at, category, take_json, risk_level, take_attempted_at = r take = {} if take_json: try: take = json.loads(take_json) except: pass risk_cls = risk_level or 'LAAG' rc = risk_colors.get(risk_cls, '#868E96') take_text = take.get('consultant_take', '') is_pending = not take_text if is_pending: # Eerlijke weergave: AI-analyse (nog) niet beschikbaar — nooit nep-advies take = build_unavailable_take(category, attempted_at=take_attempted_at) take_text = take['consultant_take'] actions_list = take.get('actions', []) ci_score = take.get('compliance_impact_score') legal_risk = take.get('legal_risk_level') src_name = (source or '').replace('_', ' ').title() badge_label = risk_labels.get(risk_cls, risk_cls) badge_color = rc if is_pending: badge_label = '⏳ Analyse in wachtrij' badge_color = '#868E96' body += f'''
{title[:200]}
{badge_label}
{src_name} · {category or 'Algemeen'} · {(scraped_at or '')[:10]}
{(content or '')[:250]}
''' if take_text: if is_pending: body += f'''
⚠️ Consultant Take: {take_text}
''' else: body += f'''
💡 Consultant Take: {take_text}
''' # Show enrichment badges for enriched items if ci_score or legal_risk: body += '
' if ci_score: ci_color = '#E03131' if ci_score >= 4 else '#F59F00' if ci_score >= 3 else '#4263EB' body += f'🛡️ Compliance: {ci_score}/5' if legal_risk: lr_color = '#E03131' if legal_risk in ('HOOG','KRITIEK') else '#F59F00' if legal_risk == 'MEDIUM' else '#2B8A3E' body += f'⚖️ Legal: {legal_risk}' body += '🧠 Enriched' body += '
' if actions_list: body += '
Voorgestelde acties:
    ' for a in actions_list[:3]: body += f'
  • {a}
    • ' body += '
' body += f'''
🎓 Maak Training (LMS)
📰 Genereer Nieuwsbericht
📋 Update RI&E
📢 Stuur Team Alert
📝 Eigen Actie
{f'🔗 Bron' if url else ''}
''' else: body += '
🧠
Geen geanalyseerde items. Klik op "Genereer Consultant Takes" om te starten.
' # Trending Topics Tab body += '\n
\n\n \n
' if trending: body += '
' for i, t in enumerate(trending): if isinstance(t, dict): topic = t.get('topic', t.get('title', str(t))) count = t.get('count', t.get('mentions', '')) else: topic = str(t) count = '' count_html = '{} vermeldingen'.format(count) if count else '' # Find related items from scraped_items related = [] try: rc = sqlite3.connect(DB_PATH) rl = rc.execute("SELECT title, source_id, category, content, risk_level, consultant_take_json FROM scraped_items WHERE dismissed=0 AND (title LIKE ? OR content LIKE ?) ORDER BY id DESC LIMIT 3", ('%' + topic + '%', '%' + topic + '%')).fetchall() rc.close() related = rl except: pass body += '
' body += '
📈 {}
{}
'.format(topic, count_html) if related: body += '
' body += '
Gerelateerde items
' risk_colors = {'KRITIEK': '#DC2626', 'HOOG': '#EF4444', 'MEDIUM': '#F59E0B', 'LAAG': '#00A859'} for ri in related: rtitle, rsrc, rcat, rcontent, rrisk, rtake = ri rc_color = risk_colors.get(rrisk or 'LAAG', '#868E96') src_name = (rsrc or '').replace('_', ' ').title() # Get consultant take if available take_text = '' if rtake: try: td = json.loads(rtake) take_text = td.get('consultant_take', '') except: pass body += '
'.format(rc_color) body += '
{}
'.format(rtitle[:120]) body += '
{} · {} · {}
'.format(src_name, (rcat or 'Algemeen'), rc_color, (rrisk or 'LAAG')) if take_text: body += '
💡 {}
'.format(take_text[:150]) elif rcontent: body += '
{}
'.format((rcontent or '')[:120]) body += '
' body += '
' else: body += '
Geen gerelateerde scraped items gevonden
' body += '
' body += '
' else: body += '
📈
Geen trending topics gevonden.
' # Watchlist Tab body += '\n
\n\n \n
' if watchlist: body += '
' for w in watchlist: if isinstance(w, dict): wtopic = w.get('topic', w.get('title', str(w))) wreason = w.get('reason', w.get('description', '')) wpri = w.get('priority', 'MEDIUM') else: wtopic = str(w) wreason = 'Gemonitord item' wpri = 'MEDIUM' wc = {'KRITIEK': '#DC2626', 'HOOG': '#EF4444', 'MEDIUM': '#F59E0B'}.get(wpri, '#7950F2') body += f'\n
\n
\n
👁️ {wtopic}
\n {wpri}\n
\n
{wreason}
\n
' body += '
' else: body += '
👁️
Geen watchlist items.
' body += '\n
\n\n \n
' if v2_actions: action_type_labels = {'training': '🎓 Training', 'news': '📰 Nieuws', 'rie': '📋 RI&E', 'alert': '📢 Alert', 'custom': '📝 Actie'} action_type_colors = {'training': '#003366', 'news': '#7950F2', 'rie': '#00A859', 'alert': '#EF4444', 'custom': '#F59E0B'} for a in v2_actions: aid, sitem_id, atype, adata, atitle, asource, acreated, \ item_title, item_content, item_source, item_category, item_url, \ item_take_json, item_risk, astatus = a atc = action_type_colors.get(atype, '#868E96') atl = action_type_labels.get(atype, atype) # Parse rich action_data adesc = '' apri = 'MEDIUM' if adata: try: ad = json.loads(adata) if isinstance(adata, str) else adata adesc = ad.get('description', '') apri = ad.get('priority', 'MEDIUM') except: pass pri_colors = {'KRITIEK': '#DC2626', 'HOOG': '#EF4444', 'MEDIUM': '#F59E0B', 'LAAG': '#00A859'} pri_badge = '{}'.format(pri_colors.get(apri, '#868E96'), apri) desc_line = '
{}
'.format(adesc[:200]) if adesc else '' # Build execute button — routes to Agent Launchpad with FULL context exec_btn = '' import urllib.parse # Build rich context from original scraped item + consultant take take_text = '' if item_take_json: try: td = json.loads(item_take_json) take_text = td.get('consultant_take', '') except: pass context_parts = [] if item_title: context_parts.append('Oorspronkelijk item: {}'.format(item_title)) if item_source: context_parts.append('Bron: {}'.format(item_source.replace('_', ' ').title())) if item_category: context_parts.append('Categorie: {}'.format(item_category)) if item_risk: context_parts.append('Risiconiveau: {}'.format(item_risk)) if take_text: context_parts.append('AI Consultant Take: {}'.format(take_text)) if item_content: context_parts.append('Inhoud samenvatting: {}'.format((item_content or '')[:300])) if item_url: context_parts.append('URL: {}'.format(item_url)) if adesc: context_parts.append('Actie beschrijving: {}'.format(adesc)) context_parts.append('OPSLAG: Volg MASTER_SOP. Sla output op in /root/projects/jg/[project-naam]/deliverables/ met versietag. Update /logs/changelog.md. Gebruik JvG huisstijl (#003366 primair).') task_context = ' | '.join(context_parts) ctx_enc = urllib.parse.quote(task_context[:500]) if atype == 'training': task_desc = 'Training maken: {} (op basis van HSEQ Intelligence alert)'.format(atitle or item_title or 'HSEQ Training') desc_enc = urllib.parse.quote(task_desc) exec_btn = '▶️ Training maken'.format(BASE_PATH + '/agents?agent_id=training_generator&task_description=' + desc_enc + '&context=' + ctx_enc, aid, aid) elif atype == 'news': task_desc = 'Nieuwsbericht: {} (HSEQ Intelligence update)'.format(atitle or item_title or 'HSEQ Nieuws') desc_enc = urllib.parse.quote(task_desc) exec_btn = '▶️ Nieuwsbericht'.format(BASE_PATH + '/agents?agent_id=technical_writer&task_description=' + desc_enc + '&context=' + ctx_enc, aid, aid) elif atype == 'rie': task_desc = 'RI&E update: {} (actie vanuit HSEQ Intelligence)'.format(atitle or item_title or 'Nieuwe ontwikkeling') desc_enc = urllib.parse.quote(task_desc) exec_btn = '▶️ RI&E starten'.format(BASE_PATH + '/agents?agent_id=hseq_specialist&task_description=' + desc_enc + '&context=' + ctx_enc, aid, aid) elif atype == 'alert': exec_btn = ''.format(aid) elif atype == 'custom': task_desc = atitle or 'HSEQ Actie' desc_enc = urllib.parse.quote(task_desc) exec_btn = '▶️ Start taak'.format(BASE_PATH + '/agents?agent_id=hseq_specialist&task_description=' + desc_enc + '&context=' + ctx_enc, aid, aid) body += f'''
{atl}
{(atitle or 'Onbekend')[:100]} {pri_badge}{'⏳ In uitvoering' if astatus=='in_progress' else ''}
{asource} · {acreated[:16] if acreated else ''}
{desc_line}
{exec_btn}
''' else: body += '
📋
Geen openstaande acties
' body += '''
''' if alerts: for al in alerts: alid, altitle, almsg, alpri, alsrc, alurl, alcreated = al alc = '#DC2626' if alpri == 'KRITIEK' else '#EF4444' body += f'''
{(altitle or '')[:150]}
{alpri}
{(almsg or '')[:200]}
{alsrc} · {alcreated[:16] if alcreated else ''}
{f'🔗 Bron' if alurl else ''}
''' body += '
' else: body += '
🟢
Geen openstaande alerts
' body += '''
''' return body def build_dashboard_alert_widget(BASE_PATH): """Build the HSEQ Alerts widget HTML for the main dashboard page.""" return '''

🚨 HSEQ Alerts -

Laden...
'''