# routes/dashboard.py
from flask import Blueprint, render_template, jsonify, request
from flask_login import login_required, current_user
from sqlalchemy import func, extract, and_, or_, literal
from datetime import date, timedelta
import calendar

from extensions import db
from models import Employee, Attendance, LeaveRequest, Payroll, Department

dashboard_bp = Blueprint('dashboard_bp', __name__, url_prefix='/dashboard')

# ------------------------
# Helpers
# ------------------------

def _require_roles(*allowed):
    """Allow if user's role (string or Role.name) matches allowed (case-insensitive) OR user.is_admin."""
    allowed_lc = {str(a).lower() for a in allowed}
    def decorator(view):
        def wrapper(*args, **kwargs):
            role_obj = getattr(current_user, 'role', None)
            user_role = getattr(role_obj, 'name', role_obj)  # Role.name or plain string
            user_role_lc = str(user_role).lower() if user_role is not None else ""
            is_admin = bool(getattr(current_user, 'is_admin', False))
            if (user_role_lc not in allowed_lc) and (not is_admin):
                return render_template('403.html'), 403
            return view(*args, **kwargs)
        wrapper.__name__ = view.__name__
        return login_required(wrapper)
    return decorator


# ---- Central status rule: only show Active + Notice Period ----
ALLOWED_EMPLOYEE_STATUSES = ('active', 'notice period')

def _status_allowed_filter():
    """
    SQLAlchemy filter: keep employees whose status (case-insensitive)
    is either 'active' or 'notice period'. Handles NULLs safely.
    """
    return func.lower(func.coalesce(Employee.status, literal(''))).in_(ALLOWED_EMPLOYEE_STATUSES)


def _payroll_has_year_month() -> bool:
    """True if Payroll has integer-like year & month columns."""
    return hasattr(Payroll, 'year') and hasattr(Payroll, 'month')


def _detect_net_col():
    """Return a Payroll net column attr or None if not present."""
    for name in ['net_pay', 'net_salary', 'net', 'net_amount', 'net_salary_amount']:
        if hasattr(Payroll, name):
            return getattr(Payroll, name)
    return None


def _month_param():
    """
    Accept ?month=YYYY-MM. If missing, fallback to latest frozen month in Payroll.
    Always return 'YYYY-MM' (string) or None.
    """
    m = request.args.get('month')
    if m:
        return m

    if _payroll_has_year_month():
        latest = (
            db.session.query(Payroll.year, Payroll.month)
            .order_by(Payroll.year.desc(), Payroll.month.desc())
            .limit(1)
            .first()
        )
        if latest:
            y, mo = int(latest[0]), int(latest[1])
            return f"{y:04d}-{mo:02d}"
        return None

    # Single Payroll.month column (DATE or 'YYYY-MM' string)
    latest = (
        db.session.query(Payroll.month)
        .order_by(Payroll.month.desc())
        .limit(1)
        .scalar()
    )
    if not latest:
        return None
    if isinstance(latest, date):
        return f"{latest.year:04d}-{latest.month:02d}"
    try:
        y, mo = str(latest).split('-')[:2]
        return f"{int(y):04d}-{int(mo):02d}"
    except Exception:
        return None


def _parse_month(month_str):
    """'YYYY-MM' -> (year:int, month:int)"""
    y, m = map(int, month_str.split('-'))
    return y, m


def _sum_net_for_month(month_str) -> float:
    """
    Sum NET for the given 'YYYY-MM' month.
    - If Payroll has (year, month) + net_col: sum by equality on year, month.
    - Else if Payroll has single month col (DATE or string) + net_col: try DATE range, then string equality.
    - If no net col exists, return 0.0.
    """
    if not month_str:
        return 0.0

    net_col = _detect_net_col()
    if net_col is None:
        return 0.0

    if _payroll_has_year_month():
        y, m = _parse_month(month_str)
        total = (
            db.session.query(func.coalesce(func.sum(net_col), 0.0))
            .filter(Payroll.year == y, Payroll.month == m)
            .scalar()
            or 0.0
        )
        return float(total)

    # Single month column case
    y, m = _parse_month(month_str)
    start = date(y, m, 1)
    end = date(y, m, calendar.monthrange(y, m)[1])

    # Try DATE range first
    try:
        total_date = float(
            db.session.query(func.coalesce(func.sum(net_col), 0.0))
            .filter(Payroll.month >= start, Payroll.month <= end)
            .scalar() or 0.0
        )
    except Exception:
        total_date = 0.0

    # Then try string equality 'YYYY-MM'
    try:
        total_str = float(
            db.session.query(func.coalesce(func.sum(net_col), 0.0))
            .filter(Payroll.month == month_str)
            .scalar() or 0.0
        )
    except Exception:
        total_str = 0.0

    return total_date if total_date > 0 else total_str


def _trend_last_n_months(n=6, selected_month=None):
    """
    Return (labels, values) for last n months of NET in Payroll.
    Handles (year, month) or single month col. If empty, falls back to selected_month.
    """
    net_col = _detect_net_col()
    if net_col is None:
        return [], []

    if _payroll_has_year_month():
        rows = (
            db.session.query(Payroll.year, Payroll.month, func.sum(net_col))
            .group_by(Payroll.year, Payroll.month)
            .order_by(Payroll.year.desc(), Payroll.month.desc())
            .limit(n)
            .all()
        )
        rows = list(reversed(rows))  # oldest first
        labels = [f"{int(y):04d}-{int(mo):02d}" for (y, mo, _sum) in rows]
        values = [float(_sum or 0.0) for (_y, _m, _sum) in rows]
    else:
        rows = (
            db.session.query(Payroll.month, func.sum(net_col))
            .group_by(Payroll.month)
            .order_by(Payroll.month.desc())
            .limit(n)
            .all()
        )
        rows = list(reversed(rows))
        labels, values = [], []
        for mon, total in rows:
            if isinstance(mon, date):
                label = f"{mon.year:04d}-{mon.month:02d}"
            else:
                try:
                    y, mo = str(mon).split('-')[:2]
                    label = f"{int(y):04d}-{int(mo):02d}"
                except Exception:
                    label = str(mon)
            labels.append(label)
            values.append(float(total or 0.0))

    # Fallback to selected month if needed
    if (not labels or all(v == 0 for v in values)) and selected_month:
        total = _sum_net_for_month(selected_month)
        if total > 0:
            return [selected_month], [float(total)]
    return labels, values


def _count_separations_for_month(month_str) -> int:
    """Count employees whose exit_date falls within the selected month."""
    if not month_str or not hasattr(Employee, 'exit_date'):
        return 0
    y, m = _parse_month(month_str)
    exit_attr = getattr(Employee, 'exit_date')
    return int(
        db.session.query(func.count())
        .select_from(Employee)
        .filter(exit_attr != None)
        .filter(extract('year', exit_attr) == y, extract('month', exit_attr) == m)
        .scalar() or 0
    )

# ------------------------
# Pages
# ------------------------

@dashboard_bp.route('/executive')
@_require_roles('executive', 'director', 'ceo', 'cfo', 'coo', 'manager', 'admin')
def executive_dashboard():
    month = _month_param()
    return render_template('dashboard_executive.html', month=month)

@dashboard_bp.route('/hr')
@_require_roles('hr', 'hr manager', 'hr admin', 'manager', 'admin')
def hr_dashboard():
    month = _month_param()
    return render_template('dashboard_hr.html', month=month)

# ------------------------
# Executive APIs
# ------------------------

@dashboard_bp.route('/api/executive/kpis')
@_require_roles('executive', 'director', 'ceo', 'cfo', 'coo', 'manager', 'admin')
def api_exec_kpis():
    month = _month_param()

    # Headcount (Active + Notice Period)
    headcount = int(
        db.session.query(func.count())
        .select_from(Employee)
        .filter(_status_allowed_filter())
        .scalar() or 0
    )

    # Dept-wise headcount (outer join; include 'Unassigned')
    name_coalesced = func.coalesce(Department.name, literal('Unassigned'))
    dept_counts_rows = (
        db.session.query(name_coalesced.label('dept_name'), func.count(Employee.id))
        .select_from(Employee)
        .outerjoin(Department, Employee.department_id == Department.id)
        .filter(_status_allowed_filter())
        .group_by('dept_name')
        .order_by(func.count(Employee.id).desc())
        .limit(50)
        .all()
    )
    dept_counts = [{'label': n, 'value': int(c)} for n, c in dept_counts_rows]

    # Payroll total net (adaptive)
    total_net = float(_sum_net_for_month(month) if month else 0.0)

    # Separations (uses exit_date)
    separations = _count_separations_for_month(month)

    return jsonify({
        'headcount': headcount,
        'dept_counts': dept_counts,
        'total_net': total_net,
        'separations': separations,
        'month': month,
    })


@dashboard_bp.route('/api/executive/payroll_trend')
@_require_roles('executive', 'director', 'ceo', 'cfo', 'coo', 'manager', 'admin')
def api_exec_payroll_trend():
    selected = request.args.get('month') or _month_param()
    labels, values = _trend_last_n_months(6, selected_month=selected)
    return jsonify({'labels': labels, 'values': values})


@dashboard_bp.route('/api/executive/gender_ratio_overall')
@_require_roles('executive', 'director', 'ceo', 'cfo', 'coo', 'manager', 'admin')
def api_exec_gender_ratio_overall():
    # Normalize gender values into M/F/Other buckets
    gender_case = func.lower(func.trim(Employee.gender))
    rows = (
        db.session.query(gender_case, func.count())
        .filter(_status_allowed_filter())
        .group_by(gender_case)
        .all()
    )
    buckets = {'male': 0, 'female': 0, 'other': 0}
    for g, c in rows:
        key = (g or '').lower()
        if key in ('m', 'male'):
            buckets['male'] += c
        elif key in ('f', 'female'):
            buckets['female'] += c
        else:
            buckets['other'] += c
    return jsonify(buckets)


@dashboard_bp.route('/api/executive/gender_ratio_by_dept')
@_require_roles('executive', 'director', 'ceo', 'cfo', 'coo', 'manager', 'admin')
def api_exec_gender_ratio_by_dept():
    gender_case = func.lower(func.trim(Employee.gender))
    name_coalesced = func.coalesce(Department.name, literal('Unassigned'))

    rows = (
        db.session.query(
            name_coalesced.label('dept'),
            gender_case.label('gender'),
            func.count().label('cnt')
        )
        .select_from(Employee)
        .outerjoin(Department, Employee.department_id == Department.id)
        .filter(_status_allowed_filter())
        .group_by('dept', 'gender')
        .all()
    )

    depts = {}
    for dept, g, cnt in rows:
        dept = dept or 'Unassigned'
        key = (g or '').lower()
        bucket = 'Other'
        if key in ('m', 'male'): bucket = 'Male'
        elif key in ('f', 'female'): bucket = 'Female'
        depts.setdefault(dept, {'Male': 0, 'Female': 0, 'Other': 0})
        depts[dept][bucket] += cnt

    labels = sorted(depts.keys())
    male = [depts[d]['Male'] for d in labels]
    female = [depts[d]['Female'] for d in labels]
    other = [depts[d]['Other'] for d in labels]
    return jsonify({'labels': labels, 'male': male, 'female': female, 'other': other})

# ------------------------
# HR APIs
# ------------------------

@dashboard_bp.route('/api/hr/kpis')
@_require_roles('hr', 'hr manager', 'hr admin', 'manager', 'admin')
def api_hr_kpis():
    month = _month_param()

    attendance_dist = {}
    if month:
        y, m = _parse_month(month)
        att_rows = (
            db.session.query(Attendance.status, func.count())
            .filter(extract('year', Attendance.date) == y,
                    extract('month', Attendance.date) == m)
            .group_by(Attendance.status)
            .all()
        )
        attendance_dist = {s or 'Unknown': int(c) for s, c in att_rows}

    avg_hours = 0.0
    if month and hasattr(Attendance, 'total_hours'):
        y, m = _parse_month(month)
        avg_hours = float(
            db.session.query(func.coalesce(func.avg(Attendance.total_hours), 0))
            .filter(extract('year', Attendance.date) == y,
                    extract('month', Attendance.date) == m)
            .scalar() or 0.0
        )

    pending_leaves = int(
        db.session.query(func.count())
        .select_from(LeaveRequest)
        .filter(LeaveRequest.status == 'Pending')
        .scalar() or 0
    )

    return jsonify({
        'attendance_dist': attendance_dist,
        'avg_hours': avg_hours,
        'pending_leaves': pending_leaves,
        'month': month,
    })


@dashboard_bp.route('/api/hr/department_leave_heatmap')
@_require_roles('hr', 'hr manager', 'hr admin', 'manager', 'admin')
def api_hr_dept_leave_heatmap():
    month = _month_param()
    if not month:
        return jsonify({'labels': [], 'matrix': [], 'days': 0})

    y, m = _parse_month(month)
    depts = db.session.query(Department.id, Department.name).order_by(Department.name).all()

    days_in_month = calendar.monthrange(y, m)[1]
    labels = [name for (_id, name) in depts]
    matrix = [[0 for _ in range(days_in_month)] for _ in depts]

    leaves = (
        db.session.query(LeaveRequest.employee_id, LeaveRequest.start_date, LeaveRequest.end_date)
        .filter(LeaveRequest.status == 'Approved')
        .filter(
            or_(
                and_(extract('year', LeaveRequest.start_date) == y,
                     extract('month', LeaveRequest.start_date) == m),
                and_(extract('year', LeaveRequest.end_date) == y,
                     extract('month', LeaveRequest.end_date) == m),
            )
        )
        .all()
    )

    # Only map Active + Notice Period employees to departments
    emp_dept = dict(
        db.session.query(Employee.id, Employee.department_id)
        .filter(_status_allowed_filter())
        .all()
    )
    dept_index = {dept_id: idx for idx, (dept_id, _name) in enumerate(depts)}

    for emp_id, sd, ed in leaves:
        dept_id = emp_dept.get(emp_id)
        if not dept_id or dept_id not in dept_index or not sd or not ed:
            continue
        d = sd
        while d <= ed:
            if d.year == y and d.month == m:
                matrix[dept_index[dept_id]][d.day - 1] += 1
            d += timedelta(days=1)

    return jsonify({'labels': labels, 'matrix': matrix, 'days': days_in_month})