from __future__ import annotations

from datetime import date
from typing import Dict, List, Tuple, Optional

from flask import (
    Blueprint, render_template, request, jsonify,
    send_file, current_app
)
from flask_login import login_required, current_user
from sqlalchemy import or_, func, literal
from sqlalchemy.orm import joinedload
import io

from extensions import db
from models import (
    Employee, Department, Designation,
    EmployeeSalaryStructures, EarningHead, DeductionHead,
    Payroll, MonthlyPayroll,
    FrozenSalaryComponent,
)

team_salary_bp = Blueprint("team_salary", __name__, url_prefix="/salary")

ALLOWED_EMPLOYEE_STATUSES = ("active", "notice period", "notice_period")


# -------------------------------
# Helpers (role/visibility)
# -------------------------------

def _is_hr_or_admin() -> bool:
    role = (getattr(getattr(current_user, "role", None), "name", "") or "").lower()
    return role in ("hr", "admin", "superadmin")


def _is_manager() -> bool:
    try:
        return current_user.reportees.count() > 0
    except Exception:
        return False


def _get_visible_employee_query(filters: dict):
    """
    HR/Admin: all active employees
    Manager: only reportees
    """
    q = (
        Employee.query
        .options(joinedload(Employee.department), joinedload(Employee.designation), joinedload(Employee.reporting_manager))
        .filter(Employee.status.in_(ALLOWED_EMPLOYEE_STATUSES))
    )

    if not _is_hr_or_admin():
        q = q.filter(Employee.reporting_manager_id == current_user.id)

    # Filters
    search = (filters.get("search") or "").strip()
    dept_id = filters.get("department_id") or ""
    desig_id = filters.get("designation_id") or ""
    manager_id = filters.get("manager_id") or ""

    if search:
        like = f"%{search}%"
        q = q.filter(or_(
            Employee.full_name.ilike(like),
            Employee.employee_code.ilike(like),
            Employee.email.ilike(like),
        ))

    if dept_id.isdigit():
        q = q.filter(Employee.department_id == int(dept_id))

    if desig_id.isdigit():
        q = q.filter(Employee.designation_id == int(desig_id))

    if _is_hr_or_admin() and manager_id.isdigit():
        q = q.filter(Employee.reporting_manager_id == int(manager_id))

    return q


def _paginate_numbers(page: int, pages: int, window: int = 2) -> List[int]:
    if pages <= 1:
        return [1]
    start = max(1, page - window)
    end = min(pages, page + window)
    # extend a bit if near edges
    while end - start < window * 2 and start > 1:
        start -= 1
    while end - start < window * 2 and end < pages:
        end += 1
    return list(range(start, end + 1))


def _get_latest_frozen_month() -> Optional[str]:
    # Payroll.month is 'YYYY-MM'
    return db.session.query(func.max(Payroll.month)).scalar()


def _get_frozen_month_choices(limit: int = 24) -> List[str]:
    # list last N distinct months
    rows = (
        db.session.query(Payroll.month)
        .distinct()
        .order_by(Payroll.month.desc())
        .limit(limit)
        .all()
    )
    return [r[0] for r in rows if r and r[0]]


def _apply_sort(q, sort_key: str, direction: str, gross_col, ded_col, net_col, ctc_col=None):
    direction = (direction or "asc").lower()
    is_desc = direction == "desc"

    # tie-breaker for stable results
    base_tie = (Employee.id.asc() if not is_desc else Employee.id.desc())

    if sort_key == "name":
        return q.order_by(Employee.full_name.desc() if is_desc else Employee.full_name.asc(), base_tie)
    if sort_key == "code":
        return q.order_by(Employee.employee_code.desc() if is_desc else Employee.employee_code.asc(), base_tie)
    if sort_key == "gross":
        return q.order_by(gross_col.desc() if is_desc else gross_col.asc(), Employee.full_name.asc(), base_tie)
    if sort_key == "deductions":
        return q.order_by(ded_col.desc() if is_desc else ded_col.asc(), Employee.full_name.asc(), base_tie)
    if sort_key == "net":
        return q.order_by(net_col.desc() if is_desc else net_col.asc(), Employee.full_name.asc(), base_tie)
    if sort_key == "ctc" and ctc_col is not None:
        return q.order_by(ctc_col.desc() if is_desc else ctc_col.asc(), Employee.full_name.asc(), base_tie)

    # default
    return q.order_by(Employee.full_name.asc(), Employee.id.asc())


# -------------------------------
# Routes
# -------------------------------

@team_salary_bp.route("/team", methods=["GET"])
@login_required
def team_salary():
    """
    View=Current:
      - derives Gross/Deductions from EmployeeSalaryStructures
    View=Frozen:
      - uses Payroll table for a selected month (or latest)
    """
    view = (request.args.get("view") or "current").strip().lower()
    if view not in ("current", "frozen"):
        view = "current"

    filters = {
        "search": request.args.get("search", ""),
        "department_id": request.args.get("department_id", ""),
        "designation_id": request.args.get("designation_id", ""),
        "manager_id": request.args.get("manager_id", ""),
    }

    page = int(request.args.get("page", 1) or 1)
    per_page = int(request.args.get("per_page", 20) or 20)
    per_page = max(10, min(per_page, 100))

    sort = (request.args.get("sort") or "name").strip().lower()
    direction = (request.args.get("dir") or "asc").strip().lower()

    # datasets for dropdowns
    dept_list = Department.query.order_by(Department.name.asc()).all()
    desig_list = Designation.query.order_by(Designation.title.asc()).all()

    managers_list = []
    if _is_hr_or_admin():
        # show potential managers (anyone who has reportees)
        managers_list = (
            Employee.query
            .filter(Employee.status.in_(ALLOWED_EMPLOYEE_STATUSES))
            .order_by(Employee.full_name.asc())
            .all()
        )

    frozen_month_choices = _get_frozen_month_choices()

    frozen_month = (request.args.get("month") or "").strip()
    if view == "frozen":
        if not frozen_month:
            frozen_month = _get_latest_frozen_month() or ""
        # if still empty => no payroll frozen yet
    else:
        frozen_month = ""

    # base employee visibility query
    emp_q = _get_visible_employee_query(filters)

    if view == "current":
        # Earnings subquery
        earn_sq = (
            db.session.query(
                EmployeeSalaryStructures.employee_id.label("emp_id"),
                func.coalesce(func.sum(EmployeeSalaryStructures.amount), 0.0).label("earnings"),
            )
            .filter(EmployeeSalaryStructures.type == "earning")
            .group_by(EmployeeSalaryStructures.employee_id)
            .subquery()
        )
        # Deductions subquery
        ded_sq = (
            db.session.query(
                EmployeeSalaryStructures.employee_id.label("emp_id"),
                func.coalesce(func.sum(EmployeeSalaryStructures.amount), 0.0).label("deductions"),
            )
            .filter(EmployeeSalaryStructures.type == "deduction")
            .group_by(EmployeeSalaryStructures.employee_id)
            .subquery()
        )

        gross_col = func.coalesce(earn_sq.c.earnings, 0.0)
        ded_col = func.coalesce(ded_sq.c.deductions, 0.0)
        net_col = gross_col - ded_col

        q = (
            emp_q
            .outerjoin(earn_sq, earn_sq.c.emp_id == Employee.id)
            .outerjoin(ded_sq, ded_sq.c.emp_id == Employee.id)
            .with_entities(
                Employee,
                gross_col.label("gross"),
                ded_col.label("deductions"),
                net_col.label("net"),
                literal(None).label("ctc"),
                literal(None).label("payroll_month"),
            )
        )

        q = _apply_sort(q, sort, direction, gross_col, ded_col, net_col, None)

    else:
        # Frozen view: join Payroll for selected month
        # If no frozen_month, show empty list
        if not frozen_month:
            pagination = emp_q.paginate(page=page, per_page=per_page, error_out=False)
            return render_template(
                "team_salary.html",
                view=view,
                frozen_month=frozen_month,
                frozen_month_choices=frozen_month_choices,
                filters=filters,
                dept_list=dept_list,
                desig_list=desig_list,
                managers_list=managers_list,
                is_hr_or_admin=_is_hr_or_admin(),
                is_manager=_is_manager(),
                per_page=per_page,
                sort=sort,
                direction=direction,
                rows=[],
                pagination=pagination,
                page_numbers=_paginate_numbers(pagination.page, pagination.pages),
            )

        # payroll rows for month (one row per employee)
        pay_sq = (
            db.session.query(
                Payroll.employee_id.label("emp_id"),
                Payroll.month.label("pay_month"),
                Payroll.gross_pay.label("gross"),
                Payroll.deductions.label("deductions"),
                Payroll.net_pay.label("net"),
                Payroll.ctc.label("ctc"),
            )
            .filter(Payroll.month == frozen_month)
            .subquery()
        )

        gross_col = func.coalesce(pay_sq.c.gross, 0.0)
        ded_col = func.coalesce(pay_sq.c.deductions, 0.0)
        net_col = func.coalesce(pay_sq.c.net, 0.0)
        ctc_col = func.coalesce(pay_sq.c.ctc, 0.0)

        q = (
            emp_q
            .outerjoin(pay_sq, pay_sq.c.emp_id == Employee.id)
            .with_entities(
                Employee,
                gross_col.label("gross"),
                ded_col.label("deductions"),
                net_col.label("net"),
                ctc_col.label("ctc"),
                pay_sq.c.pay_month.label("payroll_month"),
            )
        )

        q = _apply_sort(q, sort, direction, gross_col, ded_col, net_col, ctc_col)

    pagination = q.paginate(page=page, per_page=per_page, error_out=False)
    rows = pagination.items

    return render_template(
        "team_salary.html",
        view=view,
        frozen_month=frozen_month,
        frozen_month_choices=frozen_month_choices,
        filters=filters,
        dept_list=dept_list,
        desig_list=desig_list,
        managers_list=managers_list,
        is_hr_or_admin=_is_hr_or_admin(),
        is_manager=_is_manager(),
        per_page=per_page,
        sort=sort,
        direction=direction,
        rows=rows,
        pagination=pagination,
        page_numbers=_paginate_numbers(pagination.page, pagination.pages),
    )


@team_salary_bp.route("/team/breakdown", methods=["GET"])
@login_required
def team_salary_breakdown():
    """
    Returns JSON for modal:
      - current: EmployeeSalaryStructures + head names
      - frozen: FrozenSalaryComponent for month
    Query params:
      employee_id, view=current|frozen, month=YYYY-MM (for frozen)
    """
    emp_id = int(request.args.get("employee_id", 0) or 0)
    view = (request.args.get("view") or "current").strip().lower()
    month = (request.args.get("month") or "").strip()

    if not emp_id:
        return jsonify({"ok": False, "message": "Invalid employee"}), 400

    emp = Employee.query.get(emp_id)
    if not emp:
        return jsonify({"ok": False, "message": "Employee not found"}), 404

    # auth
    if not _is_hr_or_admin():
        if emp.reporting_manager_id != current_user.id:
            return jsonify({"ok": False, "message": "Not allowed"}), 403

    if view == "frozen":
        if not month:
            month = _get_latest_frozen_month() or ""

        items = (
            FrozenSalaryComponent.query
            .filter(
                FrozenSalaryComponent.employee_id == emp_id,
                FrozenSalaryComponent.month == month,
            )
            .order_by(FrozenSalaryComponent.component_type.asc(), FrozenSalaryComponent.head_name.asc())
            .all()
        )

        earnings = []
        deductions = []
        total_e = 0.0
        total_d = 0.0

        for it in items:
            amt = float(it.amount or 0.0)
            if (it.component_type or "").lower() == "earning":
                earnings.append({"name": it.head_name, "amount": amt})
                total_e += amt
            else:
                deductions.append({"name": it.head_name, "amount": amt})
                total_d += amt

        return jsonify({
            "ok": True,
            "employee": {"id": emp.id, "name": emp.full_name, "code": emp.employee_code},
            "view": "frozen",
            "month": month,
            "earnings": earnings,
            "deductions": deductions,
            "gross": round(total_e, 2),
            "deductions_total": round(total_d, 2),
            "net": round(total_e - total_d, 2),
        })

    # current view
    # Build head lookup maps once
    earning_heads = {h.id: (h.name or "") for h in EarningHead.query.all()}
    deduction_heads = {h.id: (h.name or "") for h in DeductionHead.query.all()}

    rows = (
        EmployeeSalaryStructures.query
        .filter(EmployeeSalaryStructures.employee_id == emp_id)
        .all()
    )

    earnings = []
    deductions = []
    total_e = 0.0
    total_d = 0.0

    for r in rows:
        amt = float(r.amount or 0.0)
        if (r.type or "").lower() == "earning":
            name = earning_heads.get(r.head_id, f"Earning #{r.head_id}")
            earnings.append({"name": name, "amount": amt})
            total_e += amt
        else:
            name = deduction_heads.get(r.head_id, f"Deduction #{r.head_id}")
            deductions.append({"name": name, "amount": amt})
            total_d += amt

    earnings.sort(key=lambda x: x["name"].lower())
    deductions.sort(key=lambda x: x["name"].lower())

    return jsonify({
        "ok": True,
        "employee": {"id": emp.id, "name": emp.full_name, "code": emp.employee_code},
        "view": "current",
        "month": None,
        "earnings": earnings,
        "deductions": deductions,
        "gross": round(total_e, 2),
        "deductions_total": round(total_d, 2),
        "net": round(total_e - total_d, 2),
    })


@team_salary_bp.route("/team/export", methods=["GET"])
@login_required
def team_salary_export():
    """
    Export current/frozen salary list with same filters.
    """
    from openpyxl import Workbook
    from openpyxl.utils import get_column_letter

    view = (request.args.get("view") or "current").strip().lower()
    if view not in ("current", "frozen"):
        view = "current"

    filters = {
        "search": request.args.get("search", ""),
        "department_id": request.args.get("department_id", ""),
        "designation_id": request.args.get("designation_id", ""),
        "manager_id": request.args.get("manager_id", ""),
    }

    frozen_month = (request.args.get("month") or "").strip()
    if view == "frozen" and not frozen_month:
        frozen_month = _get_latest_frozen_month() or ""

    # base query
    emp_q = _get_visible_employee_query(filters)

    if view == "current":
        earn_sq = (
            db.session.query(
                EmployeeSalaryStructures.employee_id.label("emp_id"),
                func.coalesce(func.sum(EmployeeSalaryStructures.amount), 0.0).label("earnings"),
            )
            .filter(EmployeeSalaryStructures.type == "earning")
            .group_by(EmployeeSalaryStructures.employee_id)
            .subquery()
        )
        ded_sq = (
            db.session.query(
                EmployeeSalaryStructures.employee_id.label("emp_id"),
                func.coalesce(func.sum(EmployeeSalaryStructures.amount), 0.0).label("deductions"),
            )
            .filter(EmployeeSalaryStructures.type == "deduction")
            .group_by(EmployeeSalaryStructures.employee_id)
            .subquery()
        )

        gross_col = func.coalesce(earn_sq.c.earnings, 0.0)
        ded_col = func.coalesce(ded_sq.c.deductions, 0.0)
        net_col = gross_col - ded_col

        q = (
            emp_q
            .outerjoin(earn_sq, earn_sq.c.emp_id == Employee.id)
            .outerjoin(ded_sq, ded_sq.c.emp_id == Employee.id)
            .with_entities(
                Employee,
                gross_col.label("gross"),
                ded_col.label("deductions"),
                net_col.label("net"),
                literal(None).label("ctc"),
                literal(None).label("payroll_month"),
            )
            .order_by(Employee.full_name.asc(), Employee.id.asc())
        )

    else:
        if not frozen_month:
            # nothing to export
            q = (
                emp_q.with_entities(
                    Employee,
                    literal(0.0).label("gross"),
                    literal(0.0).label("deductions"),
                    literal(0.0).label("net"),
                    literal(0.0).label("ctc"),
                    literal(None).label("payroll_month"),
                )
                .order_by(Employee.full_name.asc(), Employee.id.asc())
            )
        else:
            pay_sq = (
                db.session.query(
                    Payroll.employee_id.label("emp_id"),
                    Payroll.month.label("pay_month"),
                    Payroll.gross_pay.label("gross"),
                    Payroll.deductions.label("deductions"),
                    Payroll.net_pay.label("net"),
                    Payroll.ctc.label("ctc"),
                )
                .filter(Payroll.month == frozen_month)
                .subquery()
            )

            q = (
                emp_q
                .outerjoin(pay_sq, pay_sq.c.emp_id == Employee.id)
                .with_entities(
                    Employee,
                    func.coalesce(pay_sq.c.gross, 0.0).label("gross"),
                    func.coalesce(pay_sq.c.deductions, 0.0).label("deductions"),
                    func.coalesce(pay_sq.c.net, 0.0).label("net"),
                    func.coalesce(pay_sq.c.ctc, 0.0).label("ctc"),
                    pay_sq.c.pay_month.label("payroll_month"),
                )
                .order_by(Employee.full_name.asc(), Employee.id.asc())
            )

    rows = q.all()

    wb = Workbook()
    ws = wb.active
    ws.title = "Team Salary"

    if view == "frozen":
        headers = ["Employee Code", "Employee Name", "Department", "Designation", "Manager", "Gross", "Deductions", "Net", "CTC", "Month"]
    else:
        headers = ["Employee Code", "Employee Name", "Department", "Designation", "Manager", "Gross", "Deductions", "Net"]

    ws.append(headers)

    for (emp, gross, deductions, net, ctc, payroll_month) in rows:
        dept = emp.department.name if emp.department else ""
        desig = emp.designation.title if emp.designation else ""
        mgr = emp.reporting_manager.full_name if emp.reporting_manager else ""
        if view == "frozen":
            ws.append([
                emp.employee_code,
                emp.full_name,
                dept,
                desig,
                mgr,
                float(gross or 0),
                float(deductions or 0),
                float(net or 0),
                float(ctc or 0),
                payroll_month or frozen_month or "",
            ])
        else:
            ws.append([
                emp.employee_code,
                emp.full_name,
                dept,
                desig,
                mgr,
                float(gross or 0),
                float(deductions or 0),
                float(net or 0),
            ])

    # autosize columns
    for col in range(1, ws.max_column + 1):
        max_len = 10
        for r in range(1, ws.max_row + 1):
            v = ws.cell(row=r, column=col).value
            if v is None:
                continue
            max_len = max(max_len, len(str(v)))
        ws.column_dimensions[get_column_letter(col)].width = min(max_len + 2, 40)

    out = io.BytesIO()
    wb.save(out)
    out.seek(0)

    filename = f"team_salary_{view}"
    if view == "frozen" and frozen_month:
        filename += f"_{frozen_month}"
    filename += ".xlsx"

    return send_file(
        out,
        as_attachment=True,
        download_name=filename,
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    )

# For menu mapping convenience:
# endpoint: team_salary.team_salary
# url: /salary/team
# export: /salary/team/export
# breakdown: /salary/team/breakdown