import os, uuid, time, sqlite3, json
from flask import Flask, request, render_template, jsonify
app = Flask(__name__)
app.config['MAX_CONTENT_LENGTH'] = 30 * 1024 * 1024
BASE = '/opt/struktur/case-intake'
DB_PATH = BASE + '/data/cases.db'
UPLOAD_DIR = BASE + '/uploads'
ALLOWED = {'jpg','jpeg','png','heic','heif','mp4','mov'}
def get_db():
db = sqlite3.connect(DB_PATH)
db.row_factory = sqlite3.Row
return db
def allowed(fn):
return '.' in fn and fn.rsplit('.',1)[1].lower() in ALLOWED
def compute_content_score(falltyp, ort, beschr, mv, mn, wa, foto_anzahl,
freigabe, has_vorher, has_nachher):
"""Content-Score 0-100. Freigabe=0 gibt Score 0."""
if not freigabe:
return 0, []
score = 0
tags = []
if foto_anzahl > 0:
score += 20
tags.append('bildmaterial')
if has_vorher and has_nachher:
score += 20
tags.append('vorher_nachher')
if mv and mn:
score += 20
tags.append('messwerte')
if wa:
score += 10
tags.append('emotionale_story')
if ort:
score += 5
tags.append('mallorca_bezug')
if beschr and len(beschr) > 50:
score += 10
if falltyp in ('schimmel', 'feuchte', 'kondensation', 'lueftung'):
tags.append('edukativ')
return min(score, 100), tags
@app.route('/')
def index():
return render_template('index.html')
@app.route('/submit', methods=['POST'])
def submit():
cid = str(uuid.uuid4())[:8].upper()
now = int(time.time())
d = request.form
falltyp = d.get('falltyp', 'unbekannt')
problemtyp = d.get('problemtyp', 'unbekannt')
ort = d.get('ort', '').strip()[:100]
beschr = d.get('beschreibung', '').strip()[:300]
mv = d.get('messwert_vorher', '').strip()[:50]
mn = d.get('messwert_nachher', '').strip()[:50]
wa = d.get('whatsapp_quelle', '').strip()[:300]
prio = d.get('prioritaet', 'p2')
freigabe = 1 if d.get('freigabe') == 'ja' else 0
sensor = d.get('sensor_bezug', '').strip()[:100]
vento_id = d.get('vento_session_id', '').strip()[:100]
baustelle = d.get('baustelle_id', '').strip()[:100]
ergebnis = d.get('ergebnis', '').strip()[:500]
db = get_db()
cur = db.cursor()
cur.execute(
'INSERT INTO cases (case_id,erfasst_am,falltyp,problemtyp,ort,kurzbeschreibung,'
'messwert_vorher,messwert_nachher,whatsapp_quelle,prioritaet,freigabe,'
'sensor_bezug,vento_session_id,baustelle_id,ergebnis,'
'content_status,radar_signal_ids,created_at,updated_at) '
'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"raw","[]",?,?)',
(cid, now, falltyp, problemtyp, ort, beschr, mv, mn, wa, prio, freigabe,
sensor, vento_id, baustelle, ergebnis, now, now))
fotos = 0
has_vorher = False
has_nachher = False
cdir = os.path.join(UPLOAD_DIR, cid)
os.makedirs(cdir, exist_ok=True)
for key in request.files:
for f in request.files.getlist(key):
if f and f.filename and allowed(f.filename):
ext = f.filename.rsplit('.', 1)[1].lower()
isv = ext in {'mp4', 'mov'}
lbl = 'vorher' if 'vorher' in key else ('nachher' if 'nachher' in key else 'allgemein')
if lbl == 'vorher': has_vorher = True
if lbl == 'nachher': has_nachher = True
fn = f'{cid}_{uuid.uuid4().hex[:6]}.{ext}'
f.save(os.path.join(cdir, fn))
cur.execute(
'INSERT INTO case_media (media_id,case_id,filename,media_type,label,uploaded_at) '
'VALUES (?,?,?,?,?,?)',
(str(uuid.uuid4()), cid, fn, 'video' if isv else 'foto', lbl, now))
if not isv:
fotos += 1
else:
cur.execute('UPDATE cases SET video_vorhanden=1 WHERE case_id=?', (cid,))
score, tags = compute_content_score(falltyp, ort, beschr, mv, mn, wa,
fotos, freigabe, has_vorher, has_nachher)
status = 'verwertbar' if score >= 60 else ('ergaenzen' if score >= 40 else 'raw')
cur.execute(
'UPDATE cases SET foto_anzahl=?, content_score=?, content_tags=?, content_status=? WHERE case_id=?',
(fotos, score, json.dumps(tags), status, cid))
db.commit()
db.close()
return render_template('success.html', case_id=cid, falltyp=falltyp,
problemtyp=problemtyp, ort=ort, fotos=fotos,
prio=prio, score=score)
@app.route('/cases')
def list_cases():
db = get_db()
cur = db.cursor()
cur.execute(
'SELECT case_id,falltyp,problemtyp,ort,prioritaet,freigabe,'
'foto_anzahl,content_status,content_score,erfasst_am '
'FROM cases ORDER BY erfasst_am DESC LIMIT 50')
cases = cur.fetchall()
db.close()
return render_template('cases.html', cases=cases)
@app.route('/api/cases')
def api_cases():
status_filter = request.args.get('status')
min_score = request.args.get('min_score', type=int)
db = get_db()
cur = db.cursor()
query = 'SELECT * FROM cases'
params = []
conditions = []
if status_filter:
conditions.append('content_status = ?')
params.append(status_filter)
if min_score is not None:
conditions.append('content_score >= ?')
params.append(min_score)
if conditions:
query += ' WHERE ' + ' AND '.join(conditions)
query += ' ORDER BY erfasst_am DESC LIMIT 200'
cur.execute(query, params)
rows = [dict(r) for r in cur.fetchall()]
db.close()
return jsonify(rows)
@app.route('/api/cases/<case_id>', methods=['GET'])
def api_case_detail(case_id):
db = get_db()
cur = db.cursor()
cur.execute('SELECT * FROM cases WHERE case_id=?', (case_id,))
row = cur.fetchone()
if not row:
db.close()
return jsonify({'error': 'not found'}), 404
result = dict(row)
cur.execute('SELECT * FROM case_media WHERE case_id=?', (case_id,))
result['media'] = [dict(m) for m in cur.fetchall()]
db.close()
return jsonify(result)
@app.route('/api/cases/<case_id>/score', methods=['POST'])
def api_recalculate_score(case_id):
"""Score neu berechnen — z.B. nach nachtraeglichem Foto-Upload."""
db = get_db()
cur = db.cursor()
cur.execute('SELECT * FROM cases WHERE case_id=?', (case_id,))
row = cur.fetchone()
if not row:
db.close()
return jsonify({'error': 'not found'}), 404
r = dict(row)
cur.execute("SELECT label FROM case_media WHERE case_id=? AND media_type='foto'", (case_id,))
labels = [m['label'] for m in cur.fetchall()]
has_vorher = 'vorher' in labels
has_nachher = 'nachher' in labels
score, tags = compute_content_score(
r['falltyp'], r['ort'], r['kurzbeschreibung'],
r['messwert_vorher'], r['messwert_nachher'], r['whatsapp_quelle'],
r['foto_anzahl'], r['freigabe'], has_vorher, has_nachher)
status = 'verwertbar' if score >= 60 else ('ergaenzen' if score >= 40 else 'raw')
now = int(time.time())
cur.execute(
'UPDATE cases SET content_score=?, content_tags=?, content_status=?, updated_at=? WHERE case_id=?',
(score, json.dumps(tags), status, now, case_id))
db.commit()
db.close()
return jsonify({'case_id': case_id, 'content_score': score, 'content_tags': tags, 'content_status': status})
@app.route('/api/stats')
def api_stats():
db = get_db()
cur = db.cursor()
cur.execute('SELECT COUNT(*) FROM cases')
total = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM cases WHERE content_score >= 60 AND freigabe=1")
verwertbar = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM cases WHERE content_score < 40 AND freigabe=1")
raw = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM cases WHERE foto_anzahl=0 AND freigabe=1")
ohne_bilder = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM cases WHERE prioritaet='p1'")
p1 = cur.fetchone()[0]
db.close()
return jsonify({
'total': total,
'verwertbar': verwertbar,
'raw': raw,
'ohne_bilder': ohne_bilder,
'prioritaet_1': p1
})
if __name__ == '__main__':
app.run(host='127.0.0.1', port=8651, debug=False)