---

from io import BytesIO

from flask import Blueprint, render_template, request, send_file
from flask_login import login_required, current_user
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

from app.models import Intern, Attendance, User
from app.services.statistics import intern_stats, STATUS_LABELS
from app.utils import parse_period

reports_bp = Blueprint("reports", __name__, url_prefix="/reports")


def _resolve_scope():
    date_from, date_to = parse_period(default_days=30)
    mentor_id = request.args.get("mentor_id", type=int)
    if not current_user.is_admin:
        mentor_id = current_user.id
    return date_from, date_to, mentor_id


@reports_bp.route("/")
@login_required
def index():
    date_from, date_to, mentor_id = _resolve_scope()

    q = Intern.query
    if mentor_id:
        q = q.filter(Intern.mentor_id == mentor_id)
    interns = q.order_by(Intern.full_name).all()

    rows = [
        {"intern": i, "stats": intern_stats(i.id, date_from, date_to)}
        for i in interns
    ]

    mentors = User.query.filter_by(role="mentor").order_by(User.full_name).all() \
        if current_user.is_admin else []

    return render_template(
        "reports/index.html",
        rows=rows, mentors=mentors,
        selected_mentor=mentor_id,
        date_from=date_from, date_to=date_to,
    )


@reports_bp.route("/export/excel")
@login_required
def export_excel():
    date_from, date_to, mentor_id = _resolve_scope()

    q = Intern.query
    if mentor_id:
        q = q.filter(Intern.mentor_id == mentor_id)
    interns = q.order_by(Intern.full_name).all()
    intern_ids = [i.id for i in interns]

    wb = Workbook()

    # ---- Лист 1: Сводка ----
    ws = wb.active
    ws.title = "Сводка"

    headers = [
        "Стажёр", "Направление", "Наставник",
        "Всего отметок", "Присутствовал", "Опоздал",
        "Отсутствовал", "Уважительная", "% посещаемости",
    ]
    ws.append(headers)

    header_fill = PatternFill("solid", fgColor="1F4E78")
    for col_idx in range(1, len(headers) + 1):
        c = ws.cell(row=1, column=col_idx)
        c.font = Font(bold=True, color="FFFFFF")
        c.fill = header_fill
        c.alignment = Alignment(horizontal="center", vertical="center")

    for row in interns:
        s = intern_stats(row.id, date_from, date_to)
        ws.append([
            row.full_name,
            row.direction or "",
            row.mentor.full_name if row.mentor else "",
            s["total"], s["present"], s["late"],
            s["absent"], s["excused"], s["rate"],
        ])

    widths = [28, 22, 28, 14, 16, 12, 16, 16, 16]
    for i, w in enumerate(widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = w

    # ---- Лист 2: Детально ----
    ws2 = wb.create_sheet("Посещаемость")
    ws2.append(["Стажёр", "Направление", "Дата", "Статус", "Заметка"])
    for col_idx in range(1, 6):
        c = ws2.cell(row=1, column=col_idx)
        c.font = Font(bold=True, color="FFFFFF")
        c.fill = header_fill
        c.alignment = Alignment(horizontal="center")

    if intern_ids:
        records = (
            Attendance.query
            .filter(Attendance.intern_id.in_(intern_ids))
            .filter(Attendance.date >= date_from, Attendance.date <= date_to)
            .order_by(Attendance.date.desc(), Attendance.intern_id)
            .all()
        )
        intern_map = {i.id: i for i in interns}
        for r in records:
            i = intern_map[r.intern_id]
            ws2.append([
                i.full_name, i.direction or "",
                r.date.strftime("%d.%m.%Y"),
                STATUS_LABELS.get(r.status, r.status),
                r.note or "",
            ])

    for i, w in enumerate([28, 22, 14, 20, 40], start=1):
        ws2.column_dimensions[get_column_letter(i)].width = w

    # ---- Лист 3: Инфо ----
    ws3 = wb.create_sheet("Инфо")
    ws3.append(["Период", f"{date_from.strftime('%d.%m.%Y')} — {date_to.strftime('%d.%m.%Y')}"])
    ws3.append(["Сформировал", current_user.full_name])
    ws3.column_dimensions["A"].width = 20
    ws3.column_dimensions["B"].width = 40

    # Save
    buf = BytesIO()
    wb.save(buf)
    buf.seek(0)

    filename = f"interns_report_{date_from}_{date_to}.xlsx"
    return send_file(
        buf,
        as_attachment=True,
        download_name=filename,
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    )