import os, uuid
from jinja2 import Environment, FileSystemLoader
from database import get_db
from questions import CATEGORIES

CATEGORY_COLORS = {
    'Communicatie & Feedback': '#84C2C0',
    'Leiderschap & Besluitvorming': '#155EEF',
    'Samenwerking & Vertrouwen': '#FF6240',
    'DISC & Communicatiestijlen': '#7C3AED',
    'Resultaatgerichtheid & Prestatie': '#F59E0B',
    'Visie & Ontwikkeling': '#06b6d4',
}
TEMPLATE_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'templates', 'report')
DELIVERABLES_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'deliverables', 'html')

def get_category_scores(scan_id):
    conn = get_db()
    rows = conn.execute('SELECT category, score, comment, question_text, question_id, context FROM scan_responses WHERE scan_id=? AND part=2 ORDER BY question_id', (scan_id,)).fetchall()
    conn.close()
    scores = {}
    for cat in CATEGORIES:
        cs = [r for r in rows if r['category'] == cat]
        if cs:
            avg = round(sum(r['score'] for r in cs)/len(cs), 1)
            scores[cat] = {'avg': avg, 'min': min(r['score'] for r in cs), 'max': max(r['score'] for r in cs), 'responses': cs}
        else:
            scores[cat] = {'avg': 0, 'min': 0, 'max': 0, 'responses': []}
    return scores

def generate_report(scan_id):
    conn = get_db()
    scan = dict(conn.execute('SELECT * FROM scans WHERE id=?', (scan_id,)).fetchone())
    conn.close()
    if not scan:
        return None

    cat_scores = get_category_scores(scan_id)
    conn = get_db()
    expert = conn.execute('SELECT * FROM expert_inputs WHERE scan_id=? ORDER BY created_date DESC LIMIT 1', (scan_id,)).fetchone()
    conn.close()
    expert = dict(expert) if expert else None

    overall = round(sum(c['avg'] for c in cat_scores.values()) / len(cat_scores), 1) if cat_scores else 0
    sorted_cats = sorted(cat_scores.items(), key=lambda x: x[1]['avg'], reverse=True)
    strengths = [s[0] for s in sorted_cats[:2]]
    weaknesses = [w[0] for w in sorted_cats[-2:]]

    env = Environment(loader=FileSystemLoader(TEMPLATE_DIR), autoescape=True)
    template = env.get_template('report.html')

    import datetime as dt
    html = template.render(
        scan=scan, cat_scores=cat_scores, category_colors=CATEGORY_COLORS,
        expert=expert, overall=overall, strengths=strengths, weaknesses=weaknesses,
        categories=CATEGORIES, generated_at=dt.datetime.now().strftime('%d-%m-%Y %H:%M')
    )

    os.makedirs(DELIVERABLES_DIR, exist_ok=True)
    name = scan.get('company_name') or scan.get('org_name') or 'unknown'
    filename = f'MT_Scan_{name.replace(" ", "_")}_{scan_id}_v2.html'
    filepath = os.path.join(DELIVERABLES_DIR, filename)
    with open(filepath, 'w') as f:
        f.write(html)

    conn = get_db()
    conn.execute('UPDATE reports SET pdf_path=?, status="generated" WHERE scan_id=? AND status="draft"', (filepath, scan_id))
    conn.commit()
    conn.close()
    return filepath