"""Excel export endpoint."""

import io
from datetime import date

from flask import Blueprint, render_template, request, g, send_file, session as flask_session
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

from webapp.auth import login_required, role_required
from icp_db import list_contacts

export_bp = Blueprint("export", __name__)


@export_bp.route("/export")
@login_required
def index():
    role = flask_session.get("role", "viewer")
    return render_template("export.html", role=role)


@export_bp.route("/export/download")
@login_required
def download():
    scope = request.args.get("scope", "icp_poi")
    role = flask_session.get("role", "viewer")

    # Determine status filter based on scope and role
    if scope == "full":
        if role != "admin":
            return "Full export requires admin role.", 403
        status_filter = None
    elif scope == "icp":
        status_filter = "ICP"
    else:
        status_filter = "ICP,POI"

    db = g.db
    result = list_contacts(
        db,
        status=status_filter,
        sort_by="total_interactions",
        sort_dir="desc",
        page=1,
        per_page=10000,  # Large enough to get all
    )

    # Build workbook in memory
    wb = Workbook()
    ws = wb.active
    ws.title = "Contacts"

    # Header
    headers = ["Name", "Title", "Company", "Tier", "Status", "Total Interactions",
               "Last 30 Days", "First Seen", "Last Active", "Notes"]
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="133133", end_color="133133", fill_type="solid")

    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=h)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center")

    # Data rows
    for row_idx, c in enumerate(result["items"], 2):
        ws.cell(row=row_idx, column=1, value=c["name"])
        ws.cell(row=row_idx, column=2, value=c["title"])
        ws.cell(row=row_idx, column=3, value=c["company"])
        ws.cell(row=row_idx, column=4, value=c["company_tier"])
        ws.cell(row=row_idx, column=5, value=c["status"])
        ws.cell(row=row_idx, column=6, value=c["total_interactions"])
        ws.cell(row=row_idx, column=7, value=c["last_30d"])
        first_seen = c["first_interaction"]
        ws.cell(row=row_idx, column=8, value=str(first_seen) if first_seen else "")
        last_active = c["latest_interaction"]
        ws.cell(row=row_idx, column=9, value=str(last_active) if last_active else "")
        ws.cell(row=row_idx, column=10, value=c["notes"])

    # Column widths
    widths = [25, 40, 25, 10, 10, 12, 10, 12, 12, 30]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[chr(64 + i)].width = w

    # Freeze header row
    ws.freeze_panes = "A2"

    # Write to bytes buffer
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)

    today_str = date.today().isoformat()
    filename = f"ICP_Tracker_Export_{scope}_{today_str}.xlsx"

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