# routes/institute_access.py
from flask import Blueprint, render_template, request, redirect, url_for, flash
from flask_login import login_required, current_user
from sqlalchemy import func, text

from extensions import db
from models import Employee, EmployeeInstituteAccess

institute_access_bp = Blueprint("institute_access", __name__)


def _is_admin_or_hr():
    rn = (getattr(current_user.role, "name", "") or "").lower()
    return rn in ("admin", "hr")


def _clamp_int(v, default, min_v, max_v):
    try:
        v = int(v)
    except Exception:
        v = default
    return max(min_v, min(max_v, v))


@institute_access_bp.route("/institute-access", methods=["GET", "POST"])
@login_required
def institute_access():
    if not _is_admin_or_hr():
        flash("You are not authorized to manage institute access.", "danger")
        return redirect(url_for("dashboard"))

    # -------------------
    # Filters for list
    # -------------------
    q = (request.args.get("q") or "").strip()
    page = _clamp_int(request.args.get("page", 1), default=1, min_v=1, max_v=100000)
    per_page = _clamp_int(request.args.get("per_page", 20), default=20, min_v=10, max_v=200)

    like = f"%{q}%" if q else None

    # -------------------
    # Employee dropdown
    # -------------------
    employees = (
        Employee.query
        .filter(Employee.status != "inactive")
        .order_by(Employee.full_name.asc())
        .all()
    )

    # unique institute list from employees table
    institutes = [
        r[0] for r in db.session.query(func.trim(Employee.location))
        .filter(Employee.location.isnot(None))
        .filter(func.trim(Employee.location) != "")
        .distinct()
        .order_by(func.trim(Employee.location).asc())
        .all()
    ]

    # for edit/prefill (works for GET + POST)
    selected_employee_id = request.values.get("employee_id", type=int)
    selected_institutes = []

    if selected_employee_id:
        selected_institutes = [
            r.institute for r in EmployeeInstituteAccess.query
            .filter_by(viewer_employee_id=selected_employee_id)
            .order_by(EmployeeInstituteAccess.institute.asc())
            .all()
        ]

    # -------------------
    # SAVE mapping
    # -------------------
    if request.method == "POST":
        if not selected_employee_id:
            flash("Please select an employee.", "warning")
            return redirect(url_for("institute_access.institute_access", q=q, per_page=per_page, page=page))

        new_institutes = request.form.getlist("institutes")  # multi-select
        new_institutes = sorted({(x or "").strip() for x in new_institutes if (x or "").strip()})

        # replace mappings
        EmployeeInstituteAccess.query.filter_by(viewer_employee_id=selected_employee_id).delete()
        for inst in new_institutes:
            db.session.add(EmployeeInstituteAccess(viewer_employee_id=selected_employee_id, institute=inst))
        db.session.commit()

        flash("Institute access updated.", "success")
        return redirect(url_for("institute_access.institute_access", employee_id=selected_employee_id, q=q, per_page=per_page, page=page))

    # -------------------
    # Counts (for badges)
    # -------------------
    # Total active employees (respect search)
    total_employees = db.session.execute(
        text("""
            SELECT COUNT(*) AS cnt
            FROM employee e
            WHERE e.status != 'inactive'
              AND (:q = '' OR e.full_name LIKE :like OR e.employee_code LIKE :like)
        """),
        {"q": q, "like": like or ""}
    ).scalar() or 0

    # Employees with at least 1 institute mapping (respect search)
    mapped_employees = db.session.execute(
        text("""
            SELECT COUNT(DISTINCT a.viewer_employee_id) AS cnt
            FROM employee e
            JOIN employee_institute_access a ON a.viewer_employee_id = e.id
            WHERE e.status != 'inactive'
              AND (:q = '' OR e.full_name LIKE :like OR e.employee_code LIKE :like)
        """),
        {"q": q, "like": like or ""}
    ).scalar() or 0

    # -------------------
    # Paginated list query
    # -------------------
    offset = (page - 1) * per_page

    access_rows = db.session.execute(
        text("""
            SELECT
              e.id AS employee_id,
              e.full_name AS full_name,
              e.employee_code AS employee_code,
              GROUP_CONCAT(a.institute ORDER BY a.institute SEPARATOR ', ') AS institutes,
              MAX(a.created_at) AS updated_at
            FROM employee e
            LEFT JOIN employee_institute_access a
              ON a.viewer_employee_id = e.id
            WHERE e.status != 'inactive'
              AND (:q = '' OR e.full_name LIKE :like OR e.employee_code LIKE :like)
            GROUP BY e.id, e.full_name, e.employee_code
            ORDER BY e.full_name ASC
            LIMIT :limit OFFSET :offset
        """),
        {"q": q, "like": like or "", "limit": per_page, "offset": offset}
    ).mappings().all()

    total_pages = max(1, (total_employees + per_page - 1) // per_page)
    if page > total_pages:
        page = total_pages

    return render_template(
        "institute_access.html",
        employees=employees,
        institutes=institutes,
        selected_employee_id=selected_employee_id,
        selected_institutes=selected_institutes,

        # list + filters
        access_rows=access_rows,
        q=q,
        page=page,
        per_page=per_page,
        total_employees=total_employees,
        mapped_employees=mapped_employees,
        total_pages=total_pages,
    )


@institute_access_bp.route("/institute-access/clear/<int:employee_id>", methods=["POST"])
@login_required
def clear_institute_access(employee_id):
    if not _is_admin_or_hr():
        flash("You are not authorized to manage institute access.", "danger")
        return redirect(url_for("dashboard"))

    q = (request.args.get("q") or "").strip()
    page = request.args.get("page", 1)
    per_page = request.args.get("per_page", 20)

    EmployeeInstituteAccess.query.filter_by(viewer_employee_id=employee_id).delete()
    db.session.commit()

    flash("Institute access cleared.", "warning")
    return redirect(url_for("institute_access.institute_access", q=q, page=page, per_page=per_page))