# ============================================================
# MODULE: BRZO/Seveso Dienst Transformatie — FASE 3
# HSEQ Intelligence Dashboard
# MAPP, ARIE, Veiligheidsrapport, VBS Compliance Tracker,
# BRZO Inspectie Voorbereiding, Noodscenario Planning
# ============================================================
import json
import sqlite3
from datetime import datetime, timedelta
from flask import jsonify, request, render_template_string
from search import get_db, DB_PATH
# ─── DB Init ───────────────────────────────────────────────────────────────
def init_db_brzo():
"""Create BRZO/Seveso tables (prefix: brzo_)."""
try:
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
# 1. MAPP documents
c.execute('''CREATE TABLE IF NOT EXISTS brzo_mapp (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL DEFAULT 'Major Accident Prevention Policy',
company_name TEXT, establishment_id TEXT,
brzo_tier TEXT DEFAULT 'lower',
policy_text TEXT, safety_objectives TEXT,
vbs_integration TEXT,
status TEXT DEFAULT 'draft',
version INTEGER DEFAULT 1,
created_by TEXT DEFAULT 'system',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
deliverable_id INTEGER
)''')
# 2. ARIE reports
c.execute('''CREATE TABLE IF NOT EXISTS brzo_arie (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL DEFAULT 'Algemene Risicoanalyse',
company_name TEXT, establishment_id TEXT,
scope TEXT, methodology TEXT,
substances_assessed TEXT, installations_assessed TEXT,
scenarios TEXT, risk_results TEXT,
recommendations TEXT,
status TEXT DEFAULT 'draft',
version INTEGER DEFAULT 1,
created_by TEXT DEFAULT 'system',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
deliverable_id INTEGER
)''')
# 3. Veiligheidsrapporten (upper tier only)
c.execute('''CREATE TABLE IF NOT EXISTS brzo_safety_report (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL DEFAULT 'Veiligheidsrapport',
company_name TEXT, establishment_id TEXT,
mapp_id INTEGER, arie_id INTEGER,
additional_data TEXT,
status TEXT DEFAULT 'draft',
version INTEGER DEFAULT 1,
created_by TEXT DEFAULT 'system',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
deliverable_id INTEGER
)''')
# 4. VBS Compliance Tracker items (extends existing vbs_elements)
c.execute('''CREATE TABLE IF NOT EXISTS brzo_vbs_compliance (
id INTEGER PRIMARY KEY AUTOINCREMENT,
vbs_element_id INTEGER NOT NULL,
element_code TEXT NOT NULL,
compliance_pct REAL DEFAULT 0,
status TEXT DEFAULT 'not_started',
action_items TEXT,
last_assessment TEXT,
next_review TEXT,
assessor TEXT DEFAULT 'HSEQ Manager',
notes TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (vbs_element_id) REFERENCES vbs_elements(id)
)''')
# 5. BRZO Inspectie Checklists
c.execute('''CREATE TABLE IF NOT EXISTS brzo_inspection (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL DEFAULT 'BRZO Inspectie Checklist',
inspection_type TEXT DEFAULT 'routine',
template TEXT,
items TEXT,
status TEXT DEFAULT 'draft',
score REAL DEFAULT 0,
findings TEXT,
created_by TEXT DEFAULT 'system',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
deliverable_id INTEGER
)''')
# 6. Noodscenario's
c.execute('''CREATE TABLE IF NOT EXISTS brzo_emergency_scenarios (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
scenario_type TEXT DEFAULT 'release',
description TEXT,
installation_ref TEXT,
substances_involved TEXT,
environmental_factors TEXT,
evacuation_plan TEXT,
response_plan TEXT,
severity TEXT DEFAULT 'medium',
probability TEXT DEFAULT 'low',
risk_score REAL DEFAULT 0,
status TEXT DEFAULT 'draft',
version INTEGER DEFAULT 1,
created_by TEXT DEFAULT 'system',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
deliverable_id INTEGER
)''')
# Indexes
c.execute('CREATE INDEX IF NOT EXISTS idx_brzo_mapp_status ON brzo_mapp(status)')
c.execute('CREATE INDEX IF NOT EXISTS idx_brzo_arie_status ON brzo_arie(status)')
c.execute('CREATE INDEX IF NOT EXISTS idx_brzo_sr_status ON brzo_safety_report(status)')
c.execute('CREATE INDEX IF NOT EXISTS idx_brzo_vbs_elem ON brzo_vbs_compliance(vbs_element_id)')
c.execute('CREATE INDEX IF NOT EXISTS idx_brzo_insp_status ON brzo_inspection(status)')
c.execute('CREATE INDEX IF NOT EXISTS idx_brzo_emerg_status ON brzo_emergency_scenarios(status)')
conn.commit()
conn.close()
print("[OK] BRZO/Seveso FASE 3 tables initialized")
except Exception as e:
print(f"[WARN] init_db_brzo: {e}")
# ─── Lifecycle helper ──────────────────────────────────────────────────────
def _link_deliverable(db, title, dtype, content=None, params=None):
"""Create a dlv_lifecycle entry and return its ID."""
try:
content_j = json.dumps(content, ensure_ascii=False) if isinstance(content, (dict, list)) else content
params_j = json.dumps(params, ensure_ascii=False) if isinstance(params, (dict, list)) else params
cur = db.execute(
'INSERT INTO dlv_lifecycle (title, type, service, status, content, parameters, created_by) VALUES (?,?,?,?,?,?,?)',
(title, dtype, 'brzo', 'draft', content_j, params_j, 'system'))
db.commit()
return cur.lastrowid
except Exception as e:
print(f"[WARN] _link_deliverable: {e}")
return None
# ═══════════════════════════════════════════════════════════════════════════
# 1. MAPP Generator
# ═══════════════════════════════════════════════════════════════════════════
def _mapp_list():
db = get_db()
rows = db.execute("SELECT * FROM brzo_mapp ORDER BY updated_at DESC").fetchall()
db.close()
return jsonify([dict(r) for r in rows])
def _mapp_get(mid):
db = get_db()
row = db.execute("SELECT * FROM brzo_mapp WHERE id=?", (mid,)).fetchone()
db.close()
if not row:
return jsonify({'error': 'Not found'}), 404
return jsonify(dict(row))
def _mapp_create():
data = request.get_json(force=True)
if not data:
return jsonify({'error': 'No data'}), 400
db = get_db()
now = datetime.utcnow().isoformat()
cur = db.execute(
'''INSERT INTO brzo_mapp (title, company_name, establishment_id, brzo_tier,
policy_text, safety_objectives, vbs_integration, status, created_by, created_at, updated_at)
VALUES (?,?,?,?,?,?,?,?,?,?,?)''',
(data.get('title', 'Major Accident Prevention Policy'),
data.get('company_name', ''), data.get('establishment_id', ''),
data.get('brzo_tier', 'lower'),
json.dumps(data.get('policy_text'), ensure_ascii=False) if isinstance(data.get('policy_text'), (dict, list)) else data.get('policy_text'),
json.dumps(data.get('safety_objectives'), ensure_ascii=False) if isinstance(data.get('safety_objectives'), (dict, list)) else data.get('safety_objectives'),
json.dumps(data.get('vbs_integration'), ensure_ascii=False) if isinstance(data.get('vbs_integration'), (dict, list)) else data.get('vbs_integration'),
data.get('status', 'draft'), data.get('created_by', 'system'), now, now))
db.commit()
did = _link_deliverable(db, data.get('title', 'MAPP'), 'mapp', {'mapp_id': cur.lastrowid})
if did:
db.execute("UPDATE brzo_mapp SET deliverable_id=? WHERE id=?", (did, cur.lastrowid))
db.commit()
db.close()
return jsonify({'id': cur.lastrowid, 'deliverable_id': did, 'status': 'draft'}), 201
def _mapp_update(mid):
data = request.get_json(force=True)
db = get_db()
row = db.execute("SELECT id FROM brzo_mapp WHERE id=?", (mid,)).fetchone()
if not row:
db.close()
return jsonify({'error': 'Not found'}), 404
fields, vals = [], []
for f in ('title', 'company_name', 'establishment_id', 'brzo_tier', 'policy_text',
'safety_objectives', 'vbs_integration', 'status'):
if f in data:
v = data[f]
if isinstance(v, (dict, list)):
v = json.dumps(v, ensure_ascii=False)
fields.append(f'{f}=?')
vals.append(v)
if fields:
fields.append("updated_at=?")
vals.append(datetime.utcnow().isoformat())
vals.append(mid)
db.execute(f"UPDATE brzo_mapp SET {','.join(fields)} WHERE id=?", vals)
db.commit()
db.close()
return jsonify({'status': 'updated'})
def _mapp_generate_html(mid):
"""Generate HTML MAPP document."""
db = get_db()
row = db.execute("SELECT * FROM brzo_mapp WHERE id=?", (mid,)).fetchone()
db.close()
if not row:
return jsonify({'error': 'Not found'}), 404
m = dict(row)
# Parse JSON fields
policy_text = m.get('policy_text', '')
safety_objectives = m.get('safety_objectives', '')
vbs_integration = m.get('vbs_integration', '')
try:
if policy_text and isinstance(policy_text, str):
policy_text = json.loads(policy_text)
except: pass
try:
if safety_objectives and isinstance(safety_objectives, str):
safety_objectives = json.loads(safety_objectives)
except: pass
# Build VBS section from existing vbs_elements
db2 = get_db()
vbs_elements = db2.execute("SELECT * FROM vbs_elements ORDER BY sort_order").fetchall()
db2.close()
vbs_rows = ''
for v in vbs_elements:
vbs_rows += f'
{v["code"]}
{v["name_nl"]}
{v["description"] or ""}
\n'
policy_items = ''
if isinstance(policy_text, list):
for p in policy_text:
policy_items += f'
{p}
\n'
elif isinstance(policy_text, str) and policy_text:
policy_items = f'
{policy_text}
'
obj_items = ''
if isinstance(safety_objectives, list):
for o in safety_objectives:
obj_items += f'
{o}
\n'
elif isinstance(safety_objectives, str) and safety_objectives:
obj_items = f'
Dit document bevat het veiligheidsbeleid van {m.get("company_name","het bedrijf")} ter voorkoming van zware ongevallen met gevaarlijke stoffen, conform Bijlage III van de Seveso III richtlijn en artikel 8 van het BRZO 2015.
2. Veiligheidsdoelstellingen
{obj_items or "
Geen doelstellingen gedefinieerd
"}
3. Veiligheidsbeleid
{policy_items or "
Geen beleidspunten gedefinieerd
"}
4. VBS-Elementen Integratie
De MAPP dekt de volgende elementen van het Veiligheidsbeheersingssysteem (VBS):
Code
Element
Beschrijving
{vbs_rows}
5. Organisatie & Verantwoordelijkheden
De directie is eindverantwoordelijk voor de implementatie en handhaving van dit beleid. De HSEQ Manager coördineert de uitvoering en rapporteert aan de directie.
6. Review & Actualisatie
Deze MAPP wordt jaarlijks herzien of bij wijziging van de inrichting, stoffen of processen. De volgende review is gepland binnen 12 maanden na vaststelling.
'''
return jsonify({'html': html, 'title': m.get('title', 'MAPP'), 'mapp_id': mid})
# ═══════════════════════════════════════════════════════════════════════════
# 2. ARIE Generator
# ═══════════════════════════════════════════════════════════════════════════
def _arie_list():
db = get_db()
rows = db.execute("SELECT * FROM brzo_arie ORDER BY updated_at DESC").fetchall()
db.close()
return jsonify([dict(r) for r in rows])
def _arie_get(aid):
db = get_db()
row = db.execute("SELECT * FROM brzo_arie WHERE id=?", (aid,)).fetchone()
db.close()
if not row:
return jsonify({'error': 'Not found'}), 404
return jsonify(dict(row))
def _arie_create():
data = request.get_json(force=True)
if not data:
return jsonify({'error': 'No data'}), 400
db = get_db()
now = datetime.utcnow().isoformat()
cur = db.execute(
'''INSERT INTO brzo_arie (title, company_name, establishment_id, scope, methodology,
substances_assessed, installations_assessed, scenarios, risk_results, recommendations,
status, version, created_by, created_at, updated_at, deliverable_id)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
(data.get('title', 'Algemene Risicoanalyse'),
data.get('company_name', ''), data.get('establishment_id', ''),
data.get('scope', ''), data.get('methodology', ''),
json.dumps(data.get('substances_assessed'), ensure_ascii=False) if isinstance(data.get('substances_assessed'), (dict, list)) else data.get('substances_assessed'),
json.dumps(data.get('installations_assessed'), ensure_ascii=False) if isinstance(data.get('installations_assessed'), (dict, list)) else data.get('installations_assessed'),
json.dumps(data.get('scenarios'), ensure_ascii=False) if isinstance(data.get('scenarios'), (dict, list)) else data.get('scenarios'),
json.dumps(data.get('risk_results'), ensure_ascii=False) if isinstance(data.get('risk_results'), (dict, list)) else data.get('risk_results'),
json.dumps(data.get('recommendations'), ensure_ascii=False) if isinstance(data.get('recommendations'), (dict, list)) else data.get('recommendations'),
data.get('status', 'draft'), 1, data.get('created_by', 'system'), now, now, None))
db.commit()
did = _link_deliverable(db, data.get('title', 'ARIE'), 'arie', {'arie_id': cur.lastrowid})
if did:
db.execute("UPDATE brzo_arie SET deliverable_id=? WHERE id=?", (did, cur.lastrowid))
db.commit()
db.close()
return jsonify({'id': cur.lastrowid, 'deliverable_id': did, 'status': 'draft'}), 201
def _arie_update(aid):
data = request.get_json(force=True)
db = get_db()
if not db.execute("SELECT id FROM brzo_arie WHERE id=?", (aid,)).fetchone():
db.close()
return jsonify({'error': 'Not found'}), 404
fields, vals = [], []
for f in ('title', 'company_name', 'establishment_id', 'scope', 'methodology',
'substances_assessed', 'installations_assessed', 'scenarios', 'risk_results',
'recommendations', 'status'):
if f in data:
v = data[f]
if isinstance(v, (dict, list)):
v = json.dumps(v, ensure_ascii=False)
fields.append(f'{f}=?')
vals.append(v)
if fields:
fields.append("updated_at=?")
vals.append(datetime.utcnow().isoformat())
vals.append(aid)
db.execute(f"UPDATE brzo_arie SET {','.join(fields)} WHERE id=?", vals)
db.commit()
db.close()
return jsonify({'status': 'updated'})
def _arie_cross_reference():
"""Cross-reference with substance_library and risk_scenarios."""
db = get_db()
substances = [dict(r) for r in db.execute("SELECT * FROM substance_library ORDER BY substance_name").fetchall()]
scenarios = [dict(r) for r in db.execute("SELECT * FROM risk_scenarios ORDER BY code").fetchall()]
db.close()
return jsonify({
'substance_library': substances,
'risk_scenarios': scenarios,
'substance_count': len(substances),
'scenario_count': len(scenarios)
})
def _arie_generate_html(aid):
"""Generate HTML ARIE report."""
db = get_db()
row = db.execute("SELECT * FROM brzo_arie WHERE id=?", (aid,)).fetchone()
db.close()
if not row:
return jsonify({'error': 'Not found'}), 404
a = dict(row)
def parse_json(val):
if not val: return []
try: return json.loads(val) if isinstance(val, str) else val
except: return [val] if val else []
substances = parse_json(a.get('substances_assessed'))
scenarios = parse_json(a.get('scenarios'))
results = parse_json(a.get('risk_results'))
recommendations = parse_json(a.get('recommendations'))
sub_rows = ''
for s in substances:
if isinstance(s, dict):
sub_rows += f'
{s.get("name","")}
{s.get("cas_number","")}
{s.get("quantity","")}
{s.get("unit","")}
\n'
elif isinstance(s, str):
sub_rows += f'
{s}
\n'
scen_rows = ''
for sc in scenarios:
if isinstance(sc, dict):
sev = sc.get('severity', 'low')
sev_color = '#D1FAE5' if sev == 'low' else '#FEF3C7' if sev == 'medium' else '#FEE2E2'
scen_rows += f'
{sc.get("code","")}
{sc.get("title","")}
{sc.get("description","")}
{sev}
\n'
elif isinstance(sc, str):
scen_rows += f'
{sc}
\n'
rec_rows = ''
for r in recommendations:
if isinstance(r, dict):
rec_rows += f'
{json.dumps(results, indent=2, ensure_ascii=False) if results else "Geen resultaten beschikbaar"}
4. Aanbevelingen
Prioriteit
Aanbeveling
Beschrijving
{rec_rows or "
Geen aanbevelingen gedefinieerd
"}
'''
return jsonify({'html': html, 'title': a.get('title', 'ARIE'), 'arie_id': aid})
# ═══════════════════════════════════════════════════════════════════════════
# 3. Veiligheidsrapport Generator (upper tier only)
# ═══════════════════════════════════════════════════════════════════════════
def _safety_report_list():
db = get_db()
rows = db.execute("SELECT * FROM brzo_safety_report ORDER BY updated_at DESC").fetchall()
db.close()
return jsonify([dict(r) for r in rows])
def _safety_report_create():
data = request.get_json(force=True)
if not data:
return jsonify({'error': 'No data'}), 400
# Enforce upper tier
db = get_db()
if data.get('brzo_tier') == 'lower':
db.close()
return jsonify({'error': 'Veiligheidsrapport alleen beschikbaar voor upper tier bedrijven'}), 403
now = datetime.utcnow().isoformat()
cur = db.execute(
'''INSERT INTO brzo_safety_report (title, company_name, establishment_id, mapp_id, arie_id,
additional_data, status, created_by, created_at, updated_at)
VALUES (?,?,?,?,?,?,?,?,?,?)''',
(data.get('title', 'Veiligheidsrapport'),
data.get('company_name', ''), data.get('establishment_id', ''),
data.get('mapp_id'), data.get('arie_id'),
json.dumps(data.get('additional_data'), ensure_ascii=False) if isinstance(data.get('additional_data'), (dict, list)) else data.get('additional_data'),
data.get('status', 'draft'), data.get('created_by', 'system'), now, now))
db.commit()
did = _link_deliverable(db, data.get('title', 'Veiligheidsrapport'), 'mapp', {'safety_report_id': cur.lastrowid})
if did:
db.execute("UPDATE brzo_safety_report SET deliverable_id=? WHERE id=?", (did, cur.lastrowid))
db.commit()
db.close()
return jsonify({'id': cur.lastrowid, 'deliverable_id': did, 'status': 'draft'}), 201
def _safety_report_generate_html(srid):
"""Generate combined safety report from MAPP + ARIE."""
db = get_db()
sr = db.execute("SELECT * FROM brzo_safety_report WHERE id=?", (srid,)).fetchone()
if not sr:
db.close()
return jsonify({'error': 'Not found'}), 404
sr = dict(sr)
mapp_data = {}
if sr.get('mapp_id'):
m = db.execute("SELECT * FROM brzo_mapp WHERE id=?", (sr['mapp_id'],)).fetchone()
if m: mapp_data = dict(m)
arie_data = {}
if sr.get('arie_id'):
a = db.execute("SELECT * FROM brzo_arie WHERE id=?", (sr['arie_id'],)).fetchone()
if a: arie_data = dict(a)
db.close()
html = f'''
Veiligheidsrapport — {sr.get("company_name","")}
🚨 VEILIGHEIDSRAPPORT — BRZO Upper Tier
⚠️ CONFIDENTIEEL — Dit rapport is bestemd voor de bevoegde autoriteit
Bedrijf: {sr.get("company_name","—")} | Inrichting: {sr.get("establishment_id","—")}
Datum: {datetime.now().strftime("%d-%m-%Y")} | Versie: {sr.get("version",1)}