# ============================================================
# MODULE: Notificaties & Rapportages — FASE 5.2
# HSEQ Intelligence Dashboard
# In-app notificaties, wekelijks compliance rapport
# ============================================================

import sqlite3
from datetime import datetime, timedelta
from flask import jsonify, request, render_template_string
from indexer import DB_PATH
from module_auth import get_current_user, require_login


def init_db_notifications():
    # Tables created in module_auth.init_db_auth() - notifications table
    pass


def _unread_count(user_id):
    conn = sqlite3.connect(DB_PATH)
    cnt = conn.execute("SELECT COUNT(*) FROM notifications WHERE user_id=? AND read=0", (user_id,)).fetchone()[0]
    conn.close()
    return cnt


def create_notification(user_id, ntype, title, message="", company_id=None):
    conn = sqlite3.connect(DB_PATH)
    conn.execute("INSERT INTO notifications (user_id,company_id,type,title,message) VALUES (?,?,?,?,?)",
                 (user_id, company_id, ntype, title, message))
    conn.commit()
    conn.close()


# ─── Weekly Report HTML ──────────────────────────────────────────────────

WEEKLY_REPORT_HTML = """
<div class="card">
<h2>📊 Wekelijks Compliance Rapport</h2>
<p style="font-size:13px;color:#64748B;margin-bottom:16px;">Periode: {{week_start}} — {{week_end}}</p>
</div>

<div class="stats-grid" style="margin-bottom:20px">
<div class="stat-card">
<div class="value" style="color:#10B981">{{stats.compliant}}</div>
<div class="label">Compliant</div>
</div>
<div class="stat-card">
<div class="value" style="color:#F59E0B">{{stats.in_progress}}</div>
<div class="label">In Uitvoering</div>
</div>
<div class="stat-card">
<div class="value" style="color:#EF4444">{{stats.overdue}}</div>
<div class="label">Verlopen</div>
</div>
<div class="stat-card">
<div class="value">{{stats.total}}</div>
<div class="label">Totaal Items</div>
</div>
</div>

<div class="card">
<h2>RAG Matrix Overzicht</h2>
<table>
<tr><th>Categorie</th><th>Compliant</th><th>In Uitvoering</th><th>Verlopen</th><th>Open</th></tr>
{% for cat in rag_rows %}
<tr>
<td>{{cat.name}}</td>
<td><span class="badge status-compliant">{{cat.compliant}}</span></td>
<td><span class="badge status-in_progress">{{cat.in_progress}}</span></td>
<td><span class="badge status-overdue">{{cat.overdue}}</span></td>
<td><span class="badge status-open">{{cat.open}}</span></td>
</tr>
{% endfor %}
</table>
</div>

{% if actions %}
<div class="card">
<h2>⚠️ Open Acties</h2>
<table>
<tr><th>Beschrijving</th><th>Prioriteit</th><th>Deadline</th><th>Status</th></tr>
{% for a in actions %}
<tr>
<td>{{a.description}}</td>
<td><span class="badge priority-{{a.priority}}">{{a.priority}}</span></td>
<td>{{a.deadline or '-'}}</td>
<td><span class="badge status-{{a.status}}">{{a.status}}</span></td>
</tr>
{% endfor %}
</table>
</div>
{% endif %}

<div style="text-align:center;margin-top:16px">
<button class="btn btn-outline" onclick="window.print()">🖨️ Afdrukken</button>
</div>

<style>
@media print {
  .sidebar, #menuBtn, .btn { display: none !important; }
  .main { margin-left: 0 !important; }
}
</style>
"""


def _get_weekly_stats():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    stats = {'compliant': 0, 'in_progress': 0, 'overdue': 0, 'open': 0, 'total': 0, 'not_applicable': 0}
    try:
        rows = conn.execute("SELECT status, COUNT(*) as cnt FROM compliance_status GROUP BY status").fetchall()
        for r in rows:
            s = r['status'] or 'open'
            if s in stats:
                stats[s] = r['cnt']
            stats['total'] += r['cnt']
    except:
        pass

    rag_rows = []
    try:
        cats = conn.execute("SELECT DISTINCT category FROM compliance_status ORDER BY category").fetchall()
        for c in cats:
            cat_name = c['category']
            row = {'name': cat_name, 'compliant': 0, 'in_progress': 0, 'overdue': 0, 'open': 0}
            statuses = conn.execute("SELECT status, COUNT(*) as cnt FROM compliance_status WHERE category=? GROUP BY status", (cat_name,)).fetchall()
            for s in statuses:
                st = s['status'] or 'open'
                if st in row:
                    row[st] = s['cnt']
            rag_rows.append(row)
    except:
        pass

    actions = []
    try:
        actions = conn.execute("SELECT * FROM compliance_status WHERE status IN ('overdue','in_progress','open') ORDER BY CASE priority WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 ELSE 4 END LIMIT 20").fetchall()
    except:
        pass

    conn.close()
    return stats, rag_rows, actions


# ─── Register Routes ──────────────────────────────────────────────────────

def register_notification_routes(flask_app, page_func, BASE_PATH="/hseq-dashboard"):
    app = flask_app

    @app.route(BASE_PATH + '/api/notifications')
    @require_login
    def api_notifications():
        user = get_current_user()
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        notifs = conn.execute("SELECT * FROM notifications WHERE user_id=? ORDER BY created_at DESC LIMIT 50",
                              (user['id'],)).fetchall()
        conn.close()
        return jsonify([dict(n) for n in notifs])

    @app.route(BASE_PATH + '/api/notifications/<int:nid>/read', methods=['POST'])
    @require_login
    def api_notification_read(nid):
        user = get_current_user()
        conn = sqlite3.connect(DB_PATH)
        conn.execute("UPDATE notifications SET read=1 WHERE id=? AND user_id=?", (nid, user['id']))
        conn.commit()
        conn.close()
        return jsonify({"ok": True})

    @app.route(BASE_PATH + '/api/notifications/count')
    @require_login
    def api_notification_count():
        user = get_current_user()
        return jsonify({"count": _unread_count(user['id'])})

    @app.route(BASE_PATH + '/api/reports/weekly')
    @require_login
    def api_weekly_report():
        stats, rag_rows, actions = _get_weekly_stats()
        return jsonify({"stats": stats, "rag": rag_rows, "actions": [dict(a) for a in actions]})

    @app.route(BASE_PATH + '/reports/weekly')
    @require_login
    def reports_weekly():
        stats, rag_rows, actions = _get_weekly_stats()
        now = datetime.now()
        week_start = (now - timedelta(days=now.weekday())).strftime('%d-%m-%Y')
        week_end = (now + timedelta(days=6-now.weekday())).strftime('%d-%m-%Y')
        body = render_template_string(WEEKLY_REPORT_HTML, stats=stats, rag_rows=rag_rows,
                                       actions=actions, week_start=week_start, week_end=week_end)
        return page_func(body, active='reports-weekly', page_title='Wekelijks Rapport')