"""Persistent Carlo feedback and topic-scoped prompt guidance."""
from __future__ import annotations
import json, re, sqlite3
from datetime import datetime, timezone
from pathlib import Path

DB = Path(__import__('os').environ.get('SMA_CASES_DB','/opt/struktur/social-media-agent/cases.db')).resolve()
RATINGS={'good','mediocre','bad'}
KEYWORDS={
 'cable_plausibility':('kabel','leitung','anschluss','wand','boden','decke','verschmolzen'),
 'device_plausibility':('messgerät','messgeraet','gerät','geraet','bohrmaschine','schleifer','werkzeug','sonde'),
 'measurement_method':('messung','messen','sensor','sonde','messfläche','messflaeche','wand'),
 'topic_match':('thema','bezug','inhalt','post'),
 'realism':('real','realistisch','glaubwürdig','glaubwuerdig'),
 'composition':('bildaufbau','komposition','umgebung'),
 'visual_attractiveness':('ansprechend','attraktiv','schön','schoen'),
 'Mallorca_context':('mallorca','mediterran','insel'),
 'professional_impression':('professionell','fachlich','seriös','serioes'),
 'physical_plausibility':('physikalisch','plausibel','nachvollziehbar'),
}

def interpret_human_reason(reason: str) -> dict:
    text=str(reason or '').strip(); low=text.casefold(); out={}
    for key,words in KEYWORDS.items():
        if any(w in low for w in words):
            negative=any(x in low for x in ('nicht','kein','keine','unplaus','falsch','kommt aus','wirkt wie','unklar','fehlt'))
            out[key]='negative' if negative else 'positive'
    if not out: out['other']='unclassified'
    return out

def _tokens(package: dict):
    text=' '.join(str(package.get(k,'') or '') for k in ('content_pillar','topic','topic_category')).casefold()
    return {x for x in re.findall(r'[a-zäöüß]{5,}',text) if x not in {'mallorca','feuchte','schimmel','thema','topic'}}

def feedback_prompt_rules(package: dict, limit: int=8) -> list[dict]:
    if not DB.exists(): return []
    try:
        c=sqlite3.connect(f'file:{DB}?mode=ro',uri=True); c.row_factory=sqlite3.Row
        rows=c.execute('''select v.human_rating,v.human_reason_verbatim,v.human_reason_structured_json,p.topic,p.content_pillar
                          from package_image_variants v join publication_packages p on p.package_id=v.package_id
                          where v.human_rating in ('good','mediocre','bad') order by v.human_feedback_at desc limit 100''').fetchall(); c.close()
    except sqlite3.Error: return []
    toks=_tokens(package); pillar=str(package.get('content_pillar','') or '').casefold(); selected=[]
    for r in rows:
        rp=str(r['content_pillar'] or '').casefold(); rt=_tokens({'content_pillar':rp,'topic':r['topic']})
        if rp==pillar or (toks and len(toks & rt)>=1):
            try: structured=json.loads(r['human_reason_structured_json'] or '{}')
            except json.JSONDecodeError: structured={}
            selected.append({'rating':r['human_rating'],'reason':r['human_reason_verbatim'],'features':structured,'source_topic':r['topic']})
            if len(selected)>=limit: break
    return selected

def feedback_prompt_text(package: dict) -> tuple[str,list[dict]]:
    rules=feedback_prompt_rules(package)
    if not rules: return '',[]
    positive=[]; negative=[]
    for r in rules:
        features=', '.join(k for k,v in r['features'].items() if v=='positive')
        bad=', '.join(k for k,v in r['features'].items() if v=='negative')
        if r['rating']=='good': positive.append(features or r['reason'][:180])
        else: negative.append(bad or r['reason'][:180])
    text=' '.join((['Thematisch passende Carlo-Erfahrungen — positiv: '+ '; '.join(positive[:4])] if positive else []) + (['zu vermeiden: '+ '; '.join(negative[:4])] if negative else []))
    return text,rules

def save_variant_feedback(package_id: str, variant_id: str, rating: str, reason: str, evaluated_by: str='Carlo') -> dict:
    if rating not in RATINGS: raise ValueError('human_rating must be good, mediocre or bad')
    if not str(reason or '').strip(): raise ValueError('Begründung erforderlich')
    structured=interpret_human_reason(reason); now=datetime.now(timezone.utc).isoformat()
    c=sqlite3.connect(DB); c.row_factory=sqlite3.Row
    row=c.execute('select variant_id,package_id from package_image_variants where variant_id=? and package_id=?',(variant_id,package_id)).fetchone()
    if not row: c.close(); raise ValueError('image variant not found')
    c.execute('''update package_image_variants set human_rating=?,human_reason_verbatim=?,human_reason_structured_json=?,human_feedback_at=?,human_feedback_by=? where variant_id=?''',(rating,reason,json.dumps(structured,ensure_ascii=False),now,evaluated_by,variant_id)); c.commit(); c.close()
    return {'variant_id':variant_id,'package_id':package_id,'human_rating':rating,'human_reason_verbatim':reason,'structured':structured,'human_feedback_at':now,'human_feedback_by':evaluated_by}