"""
Excel (.xlsx) downloads for every report page.

    return xlsx_response('loan_reports', 'Loan Reports', headers, rows,
                         subtitle='From 01 Nov 2026 to 20 Nov 2026')

`rows` is a list of lists (numbers stay numbers so Excel can add them up).
Also has the shared date-range parser used by the report filters.
"""
from datetime import datetime
from decimal import Decimal
from io import BytesIO

from django.http import HttpResponse
from django.utils import timezone
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

HEADER_FILL = PatternFill('solid', fgColor='0B2447')
HEADER_FONT = Font(bold=True, color='FFFFFF')


def _clean(value):
    if isinstance(value, Decimal):
        return float(value)
    if isinstance(value, str) and value[:1] in ('=', '+', '-', '@') and not _is_number(value):
        return "'" + value                        # never let a text cell run as a formula
    return value


def _is_number(text):
    try:
        float(text)
        return True
    except ValueError:
        return False


def xlsx_response(filename, title, headers, rows, subtitle='', totals=None):
    """Build a one-sheet workbook and return it as a download."""
    wb = Workbook()
    ws = wb.active
    ws.title = (title or 'Report')[:31]

    ws.append([title])
    ws['A1'].font = Font(bold=True, size=14)
    ws.append([subtitle or f"Generated {timezone.localtime().strftime('%d %b %Y %I:%M %p')}"])
    ws.append([])
    ws.append(list(headers))
    head_row = ws.max_row
    for cell in ws[head_row]:
        cell.fill, cell.font = HEADER_FILL, HEADER_FONT
        cell.alignment = Alignment(vertical='center')

    for row in rows:
        ws.append([_clean(v) for v in row])
    if totals:
        ws.append([_clean(v) for v in totals])
        for cell in ws[ws.max_row]:
            cell.font = Font(bold=True)

    for i, h in enumerate(headers, start=1):
        width = max([len(str(h))] + [len(str(r[i - 1])) for r in rows[:500] if i - 1 < len(r)])
        ws.column_dimensions[get_column_letter(i)].width = min(max(width + 2, 10), 45)
    ws.freeze_panes = ws.cell(row=head_row + 1, column=1)

    buf = BytesIO()
    wb.save(buf)
    safe = ''.join(c for c in filename if c.isalnum() or c in '-_') or 'report'
    resp = HttpResponse(
        buf.getvalue(),
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    resp['Content-Disposition'] = f'attachment; filename="{safe}.xlsx"'
    return resp


def parse_date(value):
    try:
        return datetime.strptime((value or '').strip(), '%Y-%m-%d').date()
    except ValueError:
        return None


def date_range(request, from_key='from_date', to_key='to_date'):
    """(from, to) dates from the query string; either may be None. Swapped if reversed."""
    start, end = parse_date(request.GET.get(from_key)), parse_date(request.GET.get(to_key))
    if start and end and start > end:
        start, end = end, start
    return start, end


def range_label(start, end):
    fmt = '%d %b %Y'
    if start and end:
        return f'From {start.strftime(fmt)} to {end.strftime(fmt)}'
    if start:
        return f'From {start.strftime(fmt)}'
    if end:
        return f'Up to {end.strftime(fmt)}'
    return 'All dates'
