# ============================================================
# MODULE 4: Incidentmanagement & Milieu
# Phoenix Metals HSEQ VBS — module_incidents_env.py
# ============================================================

import os
import sqlite3
from datetime import datetime, timedelta
from flask import jsonify, request, render_template
from search import get_db


# ─── Helpers ───────────────────────────────────────────────────────────────

def _generate_incident_code(db):
    date_str = datetime.now().strftime('%Y%m%d')
    prefix = f'INC-{date_str}-'
    row = db.execute(
        "SELECT incident_code FROM incidents WHERE incident_code LIKE ? ORDER BY incident_code DESC LIMIT 1",
        (prefix + '%',)
    ).fetchone()
    next_num = (int(row['incident_code'].split('-')[-1]) + 1) if row else 1
    return f'{prefix}{next_num:03d}'


def _generate_capa_code(db):
    year = datetime.now().strftime('%Y')
    prefix = f'CAPA-{year}-'
    row = db.execute(
        "SELECT capa_code FROM capa_actions WHERE capa_code LIKE ? ORDER BY capa_code DESC LIMIT 1",
        (prefix + '%',)
    ).fetchone()
    next_num = (int(row['capa_code'].split('-')[-1]) + 1) if row else 1
    return f'{prefix}{next_num:03d}'


def _d(row):
    return dict(row) if row else None


def _get_substance(db, sid):
    if not sid: return None
    return _d(db.execute("SELECT * FROM substance_library WHERE id = ?", (sid,)).fetchone())


def _get_risk_scenario(db, sid):
    if not sid: return None
    return _d(db.execute("SELECT * FROM risk_scenarios WHERE id = ?", (sid,)).fetchone())


# ─── Incident CRUD ─────────────────────────────────────────────────────────

def _incidents_dashboard():
    db = get_db()
    try:
        total = db.execute("SELECT COUNT(*) as c FROM incidents").fetchone()['c']
        by_status = {r['status']: r['c'] for r in db.execute("SELECT status, COUNT(*) as c FROM incidents GROUP BY status")}
        by_domain = {r['incident_domain']: r['c'] for r in db.execute("SELECT incident_domain, COUNT(*) as c FROM incidents GROUP BY incident_domain")}
        by_type = {r['incident_type']: r['c'] for r in db.execute("SELECT incident_type, COUNT(*) as c FROM incidents GROUP BY incident_type")}
        by_severity = {r['severity']: r['c'] for r in db.execute("SELECT severity, COUNT(*) as c FROM incidents GROUP BY severity")}
        open_capa = db.execute("SELECT COUNT(*) as c FROM capa_actions WHERE status != 'closed'").fetchone()['c']
        overdue_capa = db.execute("SELECT COUNT(*) as c FROM capa_actions WHERE status != 'closed' AND due_date < date('now')").fetchone()['c']
        this_month = db.execute("SELECT COUNT(*) as c FROM incidents WHERE date_occurred >= date('now', 'start of month')").fetchone()['c']
        env_exceedances = db.execute("SELECT COUNT(*) as c FROM environmental_metrics WHERE compliance_status IN ('non_compliant', 'exceedance')").fetchone()['c']
        return jsonify({
            'total': total, 'by_status': by_status, 'by_domain': by_domain,
            'by_type': by_type, 'by_severity': by_severity,
            'open_capa': open_capa, 'overdue_capa': overdue_capa,
            'this_month': this_month, 'environmental_exceedances': env_exceedances
        })
    finally:
        db.close()


def _incidents_list():
    db = get_db()
    try:
        f, p = [], []
        for k, col in [('status','status'),('domain','incident_domain'),('type','incident_type'),
                        ('severity','severity'),('location','location')]:
            v = request.args.get(k)
            if v:
                f.append(f"{col} {'LIKE' if col=='location' else '='} ?")
                p.append(f"%{v}%" if col=='location' else v)
        if request.args.get('date_from'):
            f.append("date_occurred >= ?"); p.append(request.args['date_from'])
        if request.args.get('date_to'):
            f.append("date_occurred <= ?"); p.append(request.args['date_to'])
        w = ("WHERE " + " AND ".join(f)) if f else ""
        rows = db.execute(f"SELECT * FROM incidents {w} ORDER BY date_occurred DESC", p).fetchall()
        return jsonify([_d(r) for r in rows])
    finally:
        db.close()


def _incidents_detail(inc_id):
    db = get_db()
    try:
        inc = _d(db.execute("SELECT * FROM incidents WHERE id = ?", (inc_id,)).fetchone())
        if not inc:
            return jsonify({'error': 'Not found'}), 404
        inc['investigation'] = _d(db.execute("SELECT * FROM incident_investigations WHERE incident_id = ?", (inc_id,)).fetchone())
        inc['witnesses'] = [_d(r) for r in db.execute("SELECT * FROM incident_witnesses WHERE incident_id = ?", (inc_id,)).fetchall()]
        inc['capa_actions'] = [_d(r) for r in db.execute("SELECT * FROM capa_actions WHERE source_type='incident' AND source_id=?", (inc_id,)).fetchall()]
        if inc.get('related_substance_id'):
            inc['substance'] = _get_substance(db, inc['related_substance_id'])
        if inc.get('related_risk_scenario_id'):
            inc['risk_scenario'] = _get_risk_scenario(db, inc['related_risk_scenario_id'])
        return jsonify(inc)
    finally:
        db.close()


def _incidents_create():
    data = request.get_json()
    if not data or not data.get('title'):
        return jsonify({'error': 'Title required'}), 400
    db = get_db()
    try:
        code = _generate_incident_code(db)
        now = datetime.utcnow().isoformat()

        investigation_required = 1 if data.get('severity') in ('major', 'catastrophic') else 0
        reporting_required = 0
        reporting_authority = None
        reporting_deadline = None

        if data.get('hospitalization_required'):
            reporting_required = 1
            reporting_authority = 'Nederlandse Arbeidsinspectie'
            reporting_deadline = (datetime.utcnow() + timedelta(hours=24)).isoformat()
        if data.get('environmental_impact') in ('significant_regional', 'major_widespread'):
            reporting_required = 1
            reporting_authority = (reporting_authority + ', Omgevingsdienst') if reporting_authority else 'Omgevingsdienst'
        if data.get('brzo_major_accident'):
            reporting_required = 1
            reporting_authority = (reporting_authority + ', DCMR') if reporting_authority else 'DCMR'

        sub_name = data.get('substance_name')
        if data.get('related_substance_id') and not sub_name:
            s = _get_substance(db, data['related_substance_id'])
            if s: sub_name = s.get('name')

        cur = db.execute(
            """INSERT INTO incidents (incident_code,title,incident_domain,incident_type,severity,status,
               date_occurred,date_reported,location,description,reported_by,reporter_role,
               reporter_department,reporter_contact,anonymous_report,related_substance_id,
               related_risk_scenario_id,related_moc_id,related_ptw_id,equipment_tag,process_unit,
               substance_involved,substance_quantity_released,substance_quantity_unit,
               release_pathway,containment_type,number_of_casualties,number_of_fatalities,
               first_aid_required,medical_treatment_required,hospitalization_required,lost_time_injury,
               lost_time_days,environmental_impact,spill_contained,spill_reached_drain,
               spill_reached_soil,spill_reached_water,air_emission_exceeded,brzo_major_accident,
               immediate_actions,scene_secured,evidence_preserved,witnesses_interviewed,
               investigation_required,reporting_required,reporting_authority,reporting_deadline) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (code, data.get('title'), data.get('incident_domain','process_safety'),
             data.get('incident_type','near_miss'), data.get('severity','minor'), 'reported',
             data.get('date_occurred'), now, data.get('location'), data.get('description'),
             data.get('reported_by'), data.get('reporter_role'), data.get('reporter_department'),
             data.get('reporter_contact'), data.get('anonymous_report',0), data.get('related_substance_id'),
             data.get('related_risk_scenario_id'), data.get('related_moc_id'), data.get('related_ptw_id'),
             data.get('equipment_tag'), data.get('process_unit'), data.get('substance_involved',0),
             data.get('substance_quantity_released'), data.get('substance_quantity_unit'),
             data.get('release_pathway'), data.get('containment_type'), data.get('number_of_casualties',0),
             data.get('number_of_fatalities',0), data.get('first_aid_required',0),
             data.get('medical_treatment_required',0), data.get('hospitalization_required',0),
             data.get('lost_time_injury',0), data.get('lost_time_days',0),
             data.get('environmental_impact'), data.get('spill_contained',0),
             data.get('spill_reached_drain',0), data.get('spill_reached_soil',0),
             data.get('spill_reached_water',0), data.get('air_emission_exceeded',0),
             data.get('brzo_major_accident',0), data.get('immediate_actions'),
             data.get('scene_secured',0), data.get('evidence_preserved',0),
             data.get('witnesses_interviewed',0), investigation_required, reporting_required,
             reporting_authority, reporting_deadline))
        db.commit()
        return jsonify({'id': cur.lastrowid, 'incident_code': code}), 201
    finally:
        db.close()


def _incidents_update(inc_id):
    data = request.get_json()
    db = get_db()
    try:
        inc = db.execute("SELECT * FROM incidents WHERE id = ?", (inc_id,)).fetchone()
        if not inc: return jsonify({'error': 'Not found'}), 404

        if data.get('status') == 'investigating' and not data.get('investigation_lead') and not inc['investigation_lead']:
            return jsonify({'error': 'investigation_lead required'}), 400
        if data.get('status') == 'closed':
            open_c = db.execute("SELECT COUNT(*) as c FROM capa_actions WHERE source_type='incident' AND source_id=? AND status NOT IN ('verified','closed')", (inc_id,)).fetchone()['c']
            if open_c > 0: return jsonify({'error': f'{open_c} CAPA actions must be verified first'}), 400

        fields, vals = [], []
        for k in ('title','description','incident_domain','incident_type','severity','status','location',
                   'reported_by','reporter_role','reporter_department','reporter_contact','equipment_tag',
                   'process_unit','substance_involved','substance_name','substance_quantity_released',
                   'substance_quantity_unit','release_pathway','containment_type','number_of_casualties',
                   'number_of_fatalities','first_aid_required','medical_treatment_required',
                   'hospitalization_required','lost_time_injury','lost_time_days','environmental_impact',
                   'spill_contained','spill_reached_drain','spill_reached_soil','spill_reached_water',
                   'air_emission_exceeded','brzo_major_accident','immediate_actions','scene_secured',
                   'evidence_preserved','witnesses_interviewed','investigation_required','investigation_lead',
                   'investigation_due_date','reporting_required','reporting_authority','reporting_deadline',
                   'reporting_submitted','reporting_submitted_date','related_substance_id',
                   'related_risk_scenario_id','related_moc_id','related_ptw_id'):
            if k in data: fields.append(f"{k}=?"); vals.append(data[k])
        if not fields: return jsonify({'error': 'No fields'}), 400
        fields.append("updated_at=?"); vals.append(datetime.utcnow().isoformat()); vals.append(inc_id)
        db.execute(f"UPDATE incidents SET {','.join(fields)} WHERE id=?", vals)
        db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


# ─── Investigations ────────────────────────────────────────────────────────

def _investigation_get(inc_id):
    db = get_db()
    try:
        inv = _d(db.execute("SELECT * FROM incident_investigations WHERE incident_id=?", (inc_id,)).fetchone())
        return jsonify(inv) if inv else (jsonify({'error': 'No investigation'}), 404)
    finally:
        db.close()


def _investigation_create(inc_id):
    data = request.get_json() or {}
    db = get_db()
    try:
        if db.execute("SELECT id FROM incident_investigations WHERE incident_id=?", (inc_id,)).fetchone():
            return jsonify({'error': 'Already exists'}), 409
        now = datetime.utcnow().isoformat()
        cur = db.execute(
            "INSERT INTO incident_investigations (incident_id,investigation_method,lead_investigator,investigation_team,started_at) VALUES (?,?,?,?,?)",
            (inc_id, data.get('investigation_method','five_why'), data.get('lead_investigator'), data.get('investigation_team'), now))
        db.execute("UPDATE incidents SET status='investigating',investigation_lead=?,updated_at=? WHERE id=?",
                   (data.get('lead_investigator'), now, inc_id))
        db.commit()
        return jsonify({'id': cur.lastrowid, 'status': 'created'}), 201
    finally:
        db.close()


def _investigation_update(inc_id):
    data = request.get_json()
    db = get_db()
    try:
        inv = db.execute("SELECT id FROM incident_investigations WHERE incident_id=?", (inc_id,)).fetchone()
        if not inv: return jsonify({'error': 'No investigation'}), 404
        fields, vals = [], []
        for k in ('investigation_method','status','why_1','why_2','why_3','why_4','why_5','root_cause',
                   'tripod_preconditions','tripod_unsafe_acts','tripod_organisational','tripod_hardware',
                   'contributing_factors','conclusions','lessons_learned','recommendations',
                   'lead_investigator','investigation_team','reviewed_by'):
            if k in data: fields.append(f"{k}=?"); vals.append(data[k])
        if data.get('status') == 'completed':
            fields.append("completed_at=?"); vals.append(datetime.utcnow().isoformat())
        if not fields: return jsonify({'error': 'No fields'}), 400
        vals.append(inv['id'])
        db.execute(f"UPDATE incident_investigations SET {','.join(fields)} WHERE id=?", vals)
        db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


# ─── Witnesses ─────────────────────────────────────────────────────────────

def _witnesses_list(inc_id):
    db = get_db()
    try:
        return jsonify([_d(r) for r in db.execute("SELECT * FROM incident_witnesses WHERE incident_id=?", (inc_id,)).fetchall()])
    finally:
        db.close()


def _witnesses_create(inc_id):
    data = request.get_json()
    if not data or not data.get('witness_name'): return jsonify({'error': 'witness_name required'}), 400
    db = get_db()
    try:
        cur = db.execute(
            "INSERT INTO incident_witnesses (incident_id,witness_name,witness_role,witness_department,witness_contact,statement,statement_date,created_at) VALUES (?,?,?,?,?,?,?,?)",
            (inc_id, data['witness_name'], data.get('witness_role'), data.get('witness_department'),
             data.get('witness_contact'), data.get('statement'), data.get('statement_date'), datetime.utcnow().isoformat()))
        db.commit()
        return jsonify({'id': cur.lastrowid}, 201)
    finally:
        db.close()


# ─── CAPA ──────────────────────────────────────────────────────────────────

def _capa_list_incident(inc_id):
    db = get_db()
    try:
        return jsonify([_d(r) for r in db.execute("SELECT * FROM capa_actions WHERE source_type='incident' AND source_id=? ORDER BY priority DESC, due_date", (inc_id,)).fetchall()])
    finally:
        db.close()


def _capa_create_incident(inc_id):
    data = request.get_json()
    if not data or not data.get('description'): return jsonify({'error': 'description required'}), 400
    db = get_db()
    try:
        code = _generate_capa_code(db)
        now = datetime.utcnow().isoformat()
        cur = db.execute(
            "INSERT INTO capa_actions (source_type,source_id,action_type,category,title,description,priority,status,assigned_to,due_date) VALUES (?,?,?,?,?,?,?,?,?,?)",
            ('incident', inc_id, data.get('action_type','corrective'), data.get('category'),
             data.get('title',''), data['description'], data.get('priority','medium'), 'open', data.get('assigned_to'), data.get('due_date')))
        db.commit()
        return jsonify({'id': cur.lastrowid, 'capa_code': code}, 201
        )
    finally:
        db.close()


def _capa_update(aid):
    data = request.get_json()
    db = get_db()
    try:
        if not db.execute("SELECT id FROM capa_actions WHERE id=?", (aid,)).fetchone():
            return jsonify({'error': 'Not found'}), 404
        fields, vals = [], []
        for k in ('action_type','category','description','priority','status','assigned_to','due_date',
                   'completed_date','verified_by','verified_date','effectiveness_check','effectiveness_check_date','progress_notes'):
            if k in data: fields.append(f"{k}=?"); vals.append(data[k])
        fields.append("updated_at=?"); vals.append(datetime.utcnow().isoformat()); vals.append(aid)
        db.execute(f"UPDATE capa_actions SET {','.join(fields)} WHERE id=?", vals)
        db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


def _capa_list_all():
    db = get_db()
    try:
        f, p = [], []
        if request.args.get('status'): f.append("status=?"); p.append(request.args['status'])
        if request.args.get('source_type'): f.append("source_type=?"); p.append(request.args['source_type'])
        if request.args.get('assigned_to'): f.append("assigned_to=?"); p.append(request.args['assigned_to'])
        if request.args.get('overdue') == 'true': f.append("status NOT IN ('closed','verified') AND due_date < date('now')")
        w = ("WHERE " + " AND ".join(f)) if f else ""
        return jsonify([_d(r) for r in db.execute(f"SELECT * FROM capa_actions {w} ORDER BY priority DESC, due_date", p).fetchall()])
    finally:
        db.close()


# ─── AI Root-Cause Analysis ────────────────────────────────────────────────

def _incidents_analyze(inc_id):
    db = get_db()
    try:
        inc = _d(db.execute("SELECT * FROM incidents WHERE id=?", (inc_id,)).fetchone())
        if not inc: return jsonify({'error': 'Not found'}), 404

        substance = _get_substance(db, inc.get('related_substance_id'))
        domain = inc.get('incident_domain', 'process_safety')
        sub_name = substance.get('substance_name', 'onbekende stof') if substance else 'onbekende stof'
        equip = inc.get('equipment_tag', 'onbekend equipment')
        loc = inc.get('location', 'onbekende locatie')

        # 5-Why
        why = {}
        if domain == 'process_safety':
            why = {
                'why_1': f"Er vond een Loss of Primary Containment (LOPC) plaats bij {equip} waarbij {sub_name} vrijkwam",
                'why_2': f"De primaire afdichting faalde op {loc}",
                'why_3': "Onderhoud/inspectie was niet tijdig uitgevoerd of het component bereikte eind-of-life",
                'why_4': "Het PM-programma of inspectie-interval was niet afgestemd op de actuele bedrijfsomstandigheden",
                'why_5': "Er was geen systematisch programma voor predictief onderhoud op kritieke afdichtingen",
                'root_cause': f"Deficiëntie in het preventief onderhoudsprogramma voor {equip}, resulterend in falen van de primaire afdichting"
            }
        elif domain == 'arbo':
            why = {
                'why_1': (inc.get('description', 'Een arbeidsongeval vond plaats'))[:200],
                'why_2': "De blootstelling aan het gevaar was niet voldoende afgedekt door de beheersmaatregelen",
                'why_3': "De taakrisicoanalyse (TRA) was niet toereikend of niet uitgevoerd",
                'why_4': "Er was onvoldoende bewustzijn of toezicht op het naleven van de veiligheidsregels",
                'why_5': "De veiligheidscultuur of just-culture was onvoldoende verankerd in de dagelijkse praktijk",
                'root_cause': "Structureel tekort aan veiligheidsbewustzijn en toereikende taakrisicoanalyse"
            }
        elif domain == 'environmental':
            rp = inc.get('release_pathway', 'onbekende pathway')
            why = {
                'why_1': f"Er vond een emissie/lozing plaats van {sub_name} via {rp}",
                'why_2': "Het containment of abatiesysteem faalde",
                'why_3': "Het monitoring/alarmsysteem detecteerde de afwijking niet tijdig",
                'why_4': "De inspectie en onderhoudsfrequentie van het abatiesysteem was ontoereikend",
                'why_5': "Er was geen geïntegreerd milieumanagementsysteem met preemptieve monitoring",
                'root_cause': f"Falen van het abatiesysteem en ontoereikende monitoring voor {sub_name}"
            }
        else:  # near_miss
            why = {
                'why_1': f"Er ontstond een onveilige situatie bij {loc} die niet leidde tot schade, maar wel potentieel had voor letsel/milieubelasting",
                'why_2': "De bestaande beheersmaatregelen waren ontoereikend of werden niet nageleefd",
                'why_3': "De risicobeoordeling had dit scenario niet of onvoldoende geïdentificeerd",
                'why_4': "Er was onvoldoende toezicht en controle op de werkplekcondities",
                'why_5': "De preventieve veiligheidscultuur was onvoldoende verankerd in de operationele praktijk",
                'root_cause': f"Gebrek aan preventieve controles en risicobewustzijn op {loc}"
            }

        # HAZOP Cross-Check
        hazop = {'scenario_known': False, 'matched_scenario': None, 'assessment': '', 'blind_spot': True}
        q = "SELECT * FROM risk_scenarios WHERE 1=0"
        p = []
        if inc.get('related_substance_id'): q += " OR substance_id=?"; p.append(inc['related_substance_id'])
        if inc.get('equipment_tag'): q += " OR equipment_tag=?"; p.append(inc['equipment_tag'])
        if inc.get('location'): q += " OR source_location LIKE ?"; p.append(f"%{inc['location']}%")
        matches = db.execute(q, p).fetchall() if p else []
        if matches:
            m = _d(matches[0])
            hazop['scenario_known'] = True
            hazop['blind_spot'] = False
            code = m.get('code', m.get('scenario_code', ''))
            hazop['matched_scenario'] = {'code': code, 'title': m.get('title', ''), 'raw_risk_category': m.get('raw_risk_category', '')}
            if m.get('raw_risk_category') == 'extreme':
                hazop['assessment'] = f"🚨 KRITIEK — Gekoppeld aan EXTREEM risicoscenario {code}. Direct management escalatie vereist."
            else:
                hazop['assessment'] = f"✅ Dit incident stond als risicoscenario {code} in onze HAZOP. De beheersmaatregel heeft blijkbaar gefaald."
        else:
            hazop['assessment'] = "⚠️ BLINDE VLEK — Dit incident was NIET geïdentificeerd in onze HAZOP. Aanbeveling: direct aanvullende risicobeoordeling uitvoeren."

        # CAPA Suggestions
        capa = [{"priority": "high", "category": "investigation", "action": "Onderzoeksteam formeren. Getuigen horen. Plaats incident veiligstellen."}]
        if substance:
            cas = substance.get('cas_number', '')
            if substance.get('is_toxic') and domain == 'process_safety':
                capa.append({"priority": "critical", "category": "containment", "action": "Isolerend gebied afzetten min. 25m radius. Gasmeetapparatuur inzetten. Evacuatie overwegen."})
            if cas == '7664-39-3':
                capa.append({"priority": "critical", "category": "containment", "action": "Calciumgluconate gel paraat. Brandweer alarmeren. Betreffende leiding drukloos maken via LOTO."})
            if cas == '21324-40-3':
                capa.append({"priority": "critical", "category": "containment", "action": "Ventilatie maximaal openen. HF-gas vorming mogelijk bij vochtcontact. Ademluchtmasker verplicht."})
            if cas == '616-38-6' and substance.get('is_flammable'):
                capa.append({"priority": "critical", "category": "containment", "action": "Ontstekingsbronnen elimineren. LEL-meting uitvoeren. Brandblusser paraat."})
        if inc.get('spill_reached_water'):
            capa.append({"priority": "critical", "category": "notification", "action": "Omgevingsdienst direct informeren. Olie/spill-schermen plaatsen stroomafwaarts."})
        if inc.get('spill_reached_soil'):
            capa.append({"priority": "high", "category": "remediation", "action": "Bodemsanering specialist inschakelen. Monsters nemen per RIVM protocol."})
        if inc.get('hospitalization_required'):
            capa.append({"priority": "critical", "category": "notification", "action": "Arbeidsinspectie informeren binnen 24 uur (Arbowet art. 9). Slachtofferbegeleiding starten."})

        rec_method = 'tripod_beta' if domain == 'process_safety' else ('five_why' if domain == 'arbo' else 'fishbone')

        risk_gap = f"Het falen van de primaire afdichting bij {equip} was niet volledig gedekt door het bestaande PM-programma"

        return jsonify({
            'status': 'Analysis Complete',
            'incident_code': inc.get('incident_code', ''),
            'incident_id': inc_id,
            'analysis_date': datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%SZ'),
            'analyst': 'Kas \u2014 AI Root-Cause Analist',
            'suggested_5_why': why,
            'hazop_check': hazop,
            'immediate_capa_suggestions': capa,
            'risk_assessment_gap': risk_gap,
            'recommended_investigation_method': rec_method,
            'recommended_actions_count': len(capa)
        })
    finally:
        db.close()


# ─── Generate Report (Overheids-Melding) ─────────────────────────────────

def _incidents_generate_report(inc_id):
    db = get_db()
    row = db.execute('SELECT * FROM incidents WHERE id = ?', (inc_id,)).fetchone()
    if not row:
        return jsonify({'error': 'Incident not found'}), 404
    inc = _d(row)

    if not inc.get('reporting_required'):
        return jsonify({'error': 'Dit incident heeft geen externe meldingsplicht. Status: reporting_required = 0'}), 400

    substance = _get_substance(db, inc.get('related_substance_id')) if inc.get('related_substance_id') else None
    risk = _get_risk_scenario(db, inc.get('related_risk_scenario_id')) if inc.get('related_risk_scenario_id') else None

    # CAPA's
    capas_raw = db.execute('SELECT * FROM capa_actions WHERE source_type = ? AND source_id = ?', ('incident', inc_id)).fetchall()
    capas = [_d(c) for c in capas_raw]
    capa_text = '\n'.join([f'   - {c["title"]}: {c["description"]} (verantwoordelijke: {c["assigned_to"]}, deadline: {c["due_date"]})' for c in capas]) if capas else '   - Nog geen CAPA acties gedefinieerd'

    # Bepaal report type en wettelijke grondslag
    authority = inc.get('reporting_authority', 'Onbekend')
    legal_basis = ''
    report_type = 'algemene_melding'

    if inc.get('hospitalization_required') or inc.get('number_of_fatalities', 0) > 0:
        legal_basis = 'artikel 9 van de Arbeidsomstandighedenwet (melding ernstig arbeidsongeval)'
        report_type = 'arbowet_melding'
    if inc.get('brzo_major_accident'):
        if legal_basis:
            legal_basis += ' en artikel 20 van het Besluit risico\'s zware ongevallen (BRZO 2015)'
        else:
            legal_basis = 'artikel 20 van het Besluit risico\'s zware ongevallen (BRZO 2015)'
        report_type = 'brzo_melding'
    if inc.get('environmental_impact') in ('significant_regional', 'major_widespread'):
        if legal_basis:
            legal_basis += ' en artikel 18.33 van de Omgevingswet (melding ongeval/buitenplanmatige lozing)'
        else:
            legal_basis = 'artikel 18.33 van de Omgevingswet (melding ongeval/buitenplanmatige lozing)'
        report_type = 'omgevingswet_melding'

    if not legal_basis:
        legal_basis = 'Interne meldingsplicht conform HSEQ-beleid'

    # Substance sectie
    substance_text = '   - Geen gevaarlijke stof betrokken'
    if substance:
        hazards = []
        if substance.get('is_flammable'): hazards.append('Brandbaar')
        if substance.get('is_toxic'): hazards.append('Toxisch')
        if substance.get('is_corrosive'): hazards.append('Bijtend')
        if substance.get('is_explosive'): hazards.append('Explosief')
        if substance.get('is_oxidizing'): hazards.append('Oxiderend')
        if substance.get('is_carcinogenic'): hazards.append('Carcinogeen')
        h_text = ', '.join(hazards) if hazards else 'Geen specifieke gevarenklasse'
        substance_text = f'''   - Stof: {substance.get('substance_name', 'Onbekend')}
   - CAS-nummer: {substance.get('cas_number', 'Onbekend')}
   - Chemische formule: {substance.get('chemical_formula', 'Onbekend')}
   - Gevarenklassen: {h_text}
   - Vrijgekomen hoeveelheid: {inc.get('substance_quantity_released', 0)} {inc.get('substance_quantity_unit', 'kg')}'''

    # Consequence sectie
    consequences = []
    if inc.get('number_of_casualties', 0) > 0:
        consequences.append(f'Aantal gewonden: {inc.get("number_of_casualties")}')
    if inc.get('number_of_fatalities', 0) > 0:
        consequences.append(f'Aantal doden: {inc.get("number_of_fatalities")}')
    if inc.get('lost_time_injury'):
        consequences.append(f'Verleturen: {inc.get("lost_time_days", 0)} dagen')
    consequences.append(f'Milieu-impact: {inc.get("environmental_impact", "geen")}')
    if inc.get('spill_reached_water'):
        consequences.append('Spill bereikt oppervlaktewater')
    if inc.get('spill_reached_soil'):
        consequences.append('Spill bereikt bodem')
    consequences_text = '\n'.join([f'   - {c}' for c in consequences])

    # Investigation info
    inv_text = 'nog niet gestart'
    if inc.get('investigation_lead'):
        inv_text = f'gestart onder leiding van {inc.get("investigation_lead")}'
        if inc.get('investigation_due_date'):
            inv_text += f', verwachte afronding: {inc.get("investigation_due_date")}'
    elif inc.get('investigation_required'):
        inv_text = 'gepland (verplicht i.v.m. ernst)'

    report = f'''BRIEF \u2014 MELDING INCIDENT

Aan: {authority}
Van: Phoenix Metals B.V.
Betreft: Melding {inc.get('incident_type', '').replace('_', ' ')} \u2014 {inc.get('incident_code', '')}
Datum: {inc.get('date_reported', datetime.utcnow().strftime('%Y-%m-%d'))}

Geachte heer/mevrouw,

Hierbij melden wij, conform {legal_basis}, het volgende incident dat heeft plaatsgevonden op {inc.get('date_occurred', 'Onbekend')} te {inc.get('location', 'Onbekend')} in onze inrichting Phoenix Metals, Industrieweg 42, 1234 AB Rotterdam.

1. SAMENVATTING
{inc.get('description', 'Geen beschrijving beschikbaar.')}

2. BETROKKEN GEVAARLIJKE STOF
{substance_text}

3. DIRECTE GEVOLGEN
{consequences_text}

4. GENOMEN DIRECTE ACTIES
{inc.get('immediate_actions', 'Geen acties gedocumenteerd.')}

5. ACTUELE STATUS
Het incident is {inc.get('status', 'onbekend').replace('_', ' ')}.

6. VERVOLGACTIES
{capa_text}

Onderzoek is {inv_text}.

Wij houden u op de hoogte van de resultaten van het onderzoek en eventuele aanvullende maatregelen.

Met vriendelijke groet,

Jorick van Gemert
HSEQ Manager
Phoenix Metals B.V.
Industrieweg 42, 1234 AB Rotterdam
T: +31 (0)10-123 4567 | E: hseq@phoenixmetals.nl'''

    return jsonify({
        'status': 'Report Generated',
        'incident_code': inc.get('incident_code', ''),
        'report_type': report_type,
        'authority': authority,
        'legal_basis': legal_basis,
        'draft_report_text': report,
        'substance_details': {
            'name': substance.get('substance_name', '') if substance else '',
            'cas_number': substance.get('cas_number', '') if substance else '',
            'quantity_released': f"{inc.get('substance_quantity_released', 0)} {inc.get('substance_quantity_unit', 'kg')}"
        },
        'generated_at': datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%SZ')
    })


# ─── Environmental Metrics ────────────────────────────────────────────────

def _env_dashboard():
    db = get_db()
    total = db.execute('SELECT COUNT(*) as c FROM environmental_metrics').fetchone()['c']
    non_compliant = db.execute("SELECT COUNT(*) as c FROM environmental_metrics WHERE compliance_status IN ('non_compliant','exceedance')").fetchone()['c']
    warnings = db.execute("SELECT COUNT(*) as c FROM environmental_metrics WHERE compliance_status = 'warning'").fetchone()['c']
    type_counts = {}
    for row in db.execute('SELECT metric_type, COUNT(*) as c FROM environmental_metrics GROUP BY metric_type'):
        type_counts[row['metric_type']] = row['c']
    location_alerts = db.execute("SELECT source_location, parameter_name, value, unit, permit_limit FROM environmental_metrics WHERE compliance_status IN ('warning','non_compliant','exceedance') ORDER BY value DESC LIMIT 10").fetchall()
    return jsonify({
        'stats': {
            'total_measurements': total,
            'non_compliant': non_compliant,
            'warnings': warnings,
            'compliant': total - non_compliant - warnings,
            'by_type': type_counts,
            'active_alerts': len(location_alerts)
        },
        'alerts': [_d(r) for r in location_alerts]
    })


def _env_metrics_list():
    db = get_db()
    q = 'SELECT * FROM environmental_metrics WHERE 1=1'
    params = []
    if request.args.get('type'):
        q += ' AND metric_type = ?'
        params.append(request.args.get('type'))
    if request.args.get('compliance'):
        q += ' AND compliance_status = ?'
        params.append(request.args.get('compliance'))
    if request.args.get('date_from'):
        q += ' AND measurement_date >= ?'
        params.append(request.args.get('date_from'))
    if request.args.get('date_to'):
        q += ' AND measurement_date <= ?'
        params.append(request.args.get('date_to'))
    if request.args.get('location'):
        q += ' AND source_location LIKE ?'
        params.append(f'%{request.args.get("location")}%')
    q += ' ORDER BY measurement_date DESC LIMIT 200'
    rows = db.execute(q, params).fetchall()
    return jsonify([_d(r) for r in rows])


def _env_metric_detail(mid):
    db = get_db()
    row = db.execute('SELECT * FROM environmental_metrics WHERE id = ?', (mid,)).fetchone()
    if not row:
        return jsonify({'error': 'Not found'}), 404
    return jsonify(_d(row))


def _env_metric_create():
    db = get_db()
    data = request.get_json()
    if not data:
        return jsonify({'error': 'No data'}), 400

    value = data.get('value', 0)
    permit_limit = data.get('permit_limit')

    # Auto-compliance berekening
    compliance = 'compliant'
    pct = None
    if permit_limit and permit_limit > 0:
        pct = round((value / permit_limit) * 100, 1)
        if pct > 150:
            compliance = 'exceedance'
        elif pct > 100:
            compliance = 'non_compliant'
        elif pct > 80:
            compliance = 'warning'

    db.execute('''
        INSERT INTO environmental_metrics
        (metric_type,parameter_name,parameter_code,value,unit,measurement_date,measurement_period,
         measurement_method,measurement_device,source_location,emission_point_id,process_unit,
         permit_limit,permit_limit_unit,alert_threshold,percentage_of_limit,compliance_status,
         related_substance_id,measured_by,laboratory,lab_report_number,notes)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    ''', (
        data.get('metric_type'), data.get('parameter_name'), data.get('parameter_code'),
        value, data.get('unit'), data.get('measurement_date'), data.get('measurement_period'),
        data.get('measurement_method'), data.get('measurement_device'), data.get('source_location'),
        data.get('emission_point_id'), data.get('process_unit'),
        permit_limit, data.get('permit_limit_unit'), data.get('alert_threshold'),
        pct, compliance, data.get('related_substance_id'),
        data.get('measured_by'), data.get('laboratory'), data.get('lab_report_number'), data.get('notes')
    ))
    db.commit()
    return jsonify({'status': 'created', 'compliance_status': compliance, 'percentage_of_limit': pct}), 201


def _env_metric_update(mid):
    db = get_db()
    existing = db.execute('SELECT * FROM environmental_metrics WHERE id = ?', (mid,)).fetchone()
    if not existing:
        return jsonify({'error': 'Not found'}), 404
    data = request.get_json()
    if not data:
        return jsonify({'error': 'No data'}), 400

    value = data.get('value', existing['value'])
    permit_limit = data.get('permit_limit', existing['permit_limit'])
    compliance = data.get('compliance_status', existing['compliance_status'])
    pct = data.get('percentage_of_limit', existing['percentage_of_limit'])

    if permit_limit and permit_limit > 0:
        pct = round((value / permit_limit) * 100, 1)
        if pct > 150:
            compliance = 'exceedance'
        elif pct > 100:
            compliance = 'non_compliant'
        elif pct > 80:
            compliance = 'warning'
        else:
            compliance = 'compliant'

    fields = ['metric_type','parameter_name','parameter_code','value','unit','measurement_date',
              'measurement_period','measurement_method','measurement_device','source_location',
              'emission_point_id','process_unit','permit_limit','permit_limit_unit','alert_threshold',
              'percentage_of_limit','compliance_status','related_substance_id','measured_by',
              'laboratory','lab_report_number','notes']
    sets = []
    vals = []
    for f in fields:
        if f in ('percentage_of_limit', 'compliance_status'):
            continue
        if data.get(f) is not None:
            sets.append(f'{f} = ?')
            vals.append(data.get(f))
    sets.extend(['percentage_of_limit = ?', 'compliance_status = ?'])
    vals.extend([pct, compliance])
    vals.append(mid)
    db.execute(f'UPDATE environmental_metrics SET {", ".join(sets)} WHERE id = ?', vals)
    db.commit()
    return jsonify({'status': 'updated', 'compliance_status': compliance, 'percentage_of_limit': pct})


# ─── Waste Streams ────────────────────────────────────────────────────────

def _waste_list():
    db = get_db()
    q = 'SELECT * FROM waste_streams WHERE 1=1'
    params = []
    if request.args.get('category'):
        q += ' AND waste_category = ?'
        params.append(request.args.get('category'))
    if request.args.get('period'):
        q += ' AND period = ?'
        params.append(request.args.get('period'))
    if request.args.get('disposal'):
        q += ' AND disposal_method = ?'
        params.append(request.args.get('disposal'))
    q += ' ORDER BY period DESC, created_date DESC LIMIT 200'
    rows = db.execute(q, params).fetchall()
    return jsonify([_d(r) for r in rows])


def _waste_detail(wid):
    db = get_db()
    row = db.execute('SELECT * FROM waste_streams WHERE id = ?', (wid,)).fetchone()
    if not row:
        return jsonify({'error': 'Not found'}), 404
    return jsonify(_d(row))


def _waste_create():
    db = get_db()
    data = request.get_json()
    if not data:
        return jsonify({'error': 'No data'}), 400
    db.execute('''
        INSERT INTO waste_streams
        (waste_code,waste_name,waste_category,physical_state,quantity,unit,period,
         source_process,source_location,process_unit,disposal_method,disposal_contractor,
         disposal_permit_number,transport_date,related_substance_id,notes)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    ''', (
        data.get('waste_code'), data.get('waste_name'), data.get('waste_category'),
        data.get('physical_state'), data.get('quantity', 0), data.get('unit', 'ton'),
        data.get('period'), data.get('source_process'), data.get('source_location'),
        data.get('process_unit'), data.get('disposal_method'), data.get('disposal_contractor'),
        data.get('disposal_permit_number'), data.get('transport_date'),
        data.get('related_substance_id'), data.get('notes')
    ))
    db.commit()
    return jsonify({'status': 'created'}), 201


def _waste_update(wid):
    db = get_db()
    existing = db.execute('SELECT * FROM waste_streams WHERE id = ?', (wid,)).fetchone()
    if not existing:
        return jsonify({'error': 'Not found'}), 404
    data = request.get_json()
    if not data:
        return jsonify({'error': 'No data'}), 400
    fields = ['waste_code','waste_name','waste_category','physical_state','quantity','unit','period',
              'source_process','source_location','process_unit','disposal_method','disposal_contractor',
              'disposal_permit_number','transport_date','related_substance_id','notes']
    sets = []
    vals = []
    for f in fields:
        if data.get(f) is not None:
            sets.append(f'{f} = ?')
            vals.append(data.get(f))
    if sets:
        vals.append(wid)
        db.execute(f'UPDATE waste_streams SET {", ".join(sets)} WHERE id = ?', vals)
        db.commit()
    return jsonify({'status': 'updated'})


def _waste_dashboard():
    db = get_db()
    total = db.execute('SELECT COUNT(*) as c FROM waste_streams').fetchone()['c']
    by_category = {}
    for r in db.execute('SELECT waste_category, COUNT(*) as c, SUM(quantity) as qty FROM waste_streams GROUP BY waste_category'):
        by_category[r['waste_category']] = {'count': r['c'], 'total_tons': round(r['qty'] or 0, 2)}
    by_disposal = {}
    for r in db.execute('SELECT disposal_method, COUNT(*) as c FROM waste_streams GROUP BY disposal_method'):
        by_disposal[r['disposal_method']] = r['c']
    return jsonify({
        'stats': {
            'total_streams': total,
            'by_category': by_category,
            'by_disposal_method': by_disposal
        }
    })


# ─── Route Registration ──────────────────────────────────────────────────

def register_incidents_env_routes(app, page, BASE_PATH="/hseq-dashboard"):
    # Page route
    @app.route(BASE_PATH + '/incidents-env')
    def incidents_env_dashboard_page():
        bp = os.environ.get('BASE_PATH', '')
        body = render_template('incidents_env_dashboard.html', BASE_PATH=bp)
        return page(body, active='incidents-env', page_title='Incidenten & Milieu')

    # Incidents
    app.add_url_rule(BASE_PATH + '/api/incidents/dashboard', view_func=_incidents_dashboard, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/incidents', view_func=_incidents_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>', view_func=_incidents_detail, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/incidents', view_func=_incidents_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>', view_func=_incidents_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/investigation', view_func=_investigation_get, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/investigation', view_func=_investigation_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/investigation', view_func=_investigation_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/witnesses', view_func=_witnesses_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/witnesses', view_func=_witnesses_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/capa', view_func=_capa_list_incident, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/capa', view_func=_capa_create_incident, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/capa/<int:aid>', view_func=_capa_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/capa', view_func=_capa_list_all, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/analyze', view_func=_incidents_analyze, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/incidents/<int:inc_id>/generate-report', view_func=_incidents_generate_report, methods=['GET'])

    # Environment
    app.add_url_rule(BASE_PATH + '/api/environment/dashboard', view_func=_env_dashboard, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/environment/metrics', view_func=_env_metrics_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/environment/metrics/<int:mid>', view_func=_env_metric_detail, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/environment/metrics', view_func=_env_metric_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/environment/metrics/<int:mid>', view_func=_env_metric_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/environment/waste', view_func=_waste_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/environment/waste-dashboard', view_func=_waste_dashboard, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/environment/waste/<int:wid>', view_func=_waste_detail, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/environment/waste', view_func=_waste_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/environment/waste/<int:wid>', view_func=_waste_update, methods=['PUT'])