"""Report exports: one description of a report rendered as PDF, Excel (.xlsx) or CSV, with the company letterhead.

A view builds a Report (title, the period and filters, columns, rows) and returns respond(report, name); the
?export= argument picks the format (pdf, xlsx, csv; print = the PDF shown in the browser to print).

Letterhead: the company logo, its name in English and Arabic, CR / VAT and address, then the report title in both
languages and a line with the period, branches and currency. Every page ends with who printed it and when, and
"Page x of y". Documents (vouchers, invoices...) add Prepared by / Reviewed by / Approved by boxes.
"""
import csv
import io
from datetime import datetime

from flask import Response, g, request

from .i18n import AR, get_lang, nm, t
from .money import fmt

STYLES = ("section", "normal", "group", "subtotal", "total", "grand")


class Report:
    def __init__(self, title, meta=(), decimals=2, landscape=None, signatures=False, notes=(), subtitle=None):
        self.title = title                  # English; the Arabic comes from the translations
        self.subtitle = subtitle            # e.g. the partner on a statement
        self.meta = [m for m in meta if m]  # period, branches, currency, filters
        self.decimals = decimals
        self.columns = []
        self.rows = []
        self.landscape = landscape
        self.signatures = signatures
        self.notes = list(notes)
        self.details = []                   # (label, value) pairs shown under the title (documents)
        self.totals = []                    # (label, amount, bold) under the table, right-aligned (documents)

    def column(self, label, kind="text", width=1.0):
        """kind: text | num (amounts in mils) | qty (plain numbers)."""
        self.columns.append({"label": label, "kind": kind, "width": width})
        return self

    def row(self, cells, style="normal", indent=0):
        self.rows.append({"cells": list(cells), "style": style, "indent": indent})
        return self

    def section(self, label):
        self.rows.append({"cells": [label] + [None] * (len(self.columns) - 1), "style": "section", "indent": 0})
        return self

    @property
    def is_landscape(self):
        if self.landscape is not None:
            return self.landscape
        return sum(1 for c in self.columns if c["kind"] != "text") > 5 or len(self.columns) > 7

    def cell_text(self, value, col, for_csv=False):
        if value is None or value == "":
            return ""
        if col["kind"] == "num" and isinstance(value, int):
            if for_csv:
                return f"{value / 1000:.{self.decimals}f}"
            if value == 0:
                return "–"
            s = fmt(abs(value), self.decimals)
            return f"({s})" if value < 0 else s
        if col["kind"] == "qty" and isinstance(value, (int, float)):
            return f"{value:g}"
        return str(value)


def printed_line():
    who = (g.user["full_name"] or g.user["username"]) if g.get("user") else ""
    return f"{t('Printed by')} {who} · {datetime.now().strftime('%d %b %Y %H:%M')}"


def title_ar(title):
    return AR.get(title, "")


# ---------------------------------------------------------------- respond

def respond(report, name):
    """The export the request asks for, or None (show the page)."""
    kind = request.args.get("export")
    safe = "".join(ch if ch.isalnum() or ch in "-_" else "_" for ch in name)
    if kind == "csv":
        return Response(to_csv(report), mimetype="text/csv",
                        headers={"Content-Disposition": f"attachment; filename={safe}.csv"})
    if kind == "xlsx":
        return Response(to_xlsx(report),
                        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                        headers={"Content-Disposition": f"attachment; filename={safe}.xlsx"})
    if kind in ("pdf", "print"):
        disp = "inline" if kind == "print" else "attachment"
        return Response(to_pdf(report), mimetype="application/pdf",
                        headers={"Content-Disposition": f"{disp}; filename={safe}.pdf"})
    return None


# ---------------------------------------------------------------- CSV

def to_csv(report):
    buf = io.StringIO()
    buf.write("﻿")  # BOM so Excel opens Arabic text correctly
    w = csv.writer(buf)
    w.writerow([c["label"] for c in report.columns])
    for r in report.rows:
        w.writerow([report.cell_text(v, c, for_csv=True) for v, c in zip(r["cells"], report.columns)])
    return buf.getvalue()


# ---------------------------------------------------------------- Excel

def to_xlsx(report):
    from openpyxl import Workbook
    from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
    from openpyxl.utils import get_column_letter
    from .branding import logo_path
    from .ledger import company

    c = company()
    accent = (c["accent"] or "#4f46e5").lstrip("#")
    wb = Workbook()
    ws = wb.active
    ws.title = "".join(ch for ch in t(report.title) if ch not in "[]:*?/\\")[:31] or "Report"
    rtl = get_lang() == "ar"
    ws.sheet_view.rightToLeft = rtl
    ws.sheet_view.showGridLines = False
    n = len(report.columns)
    last = get_column_letter(n)
    ink, muted = "0F1729", "6B7489"
    thin, med = Side(style="thin", color="C9CDD8"), Side(style="medium", color=ink)

    row = 1
    path = logo_path(c)
    if path:
        from openpyxl.drawing.image import Image as XLImage
        img = XLImage(path)
        ratio = 46 / img.height
        img.height, img.width = 46, int(img.width * ratio)
        ws.add_image(img, f"{last}1" if not rtl else "A1")
    ws.cell(row, 1, c["name_en"]).font = Font(bold=True, size=14, color=accent)
    if c["name_ar"]:
        ws.cell(row + 1, 1, c["name_ar"]).font = Font(bold=True, size=12, color=ink)
    ids = " · ".join(x for x in (f"CR {c['cr_no']}" if c["cr_no"] else "", f"VAT {c['vat_no']}" if c["vat_no"] else "",
                                 (c["address_en"] or "").replace("\n", ", ")) if x)
    ws.cell(row + 2, 1, ids).font = Font(size=9, color=muted)
    for r in range(1, 4):
        ws.row_dimensions[r].height = 18
    row = 5
    ws.cell(row, 1, " · ".join(x for x in (report.title, title_ar(report.title)) if x)).font = Font(bold=True, size=13, color=ink)
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=n)
    if report.subtitle:
        row += 1
        ws.cell(row, 1, report.subtitle).font = Font(bold=True, size=10, color=ink)
        ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=n)
    row += 1
    ws.cell(row, 1, "  ·  ".join(report.meta)).font = Font(size=9, color=muted)
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=n)
    for lbl, val in report.details:
        row += 1
        ws.cell(row, 1, t(lbl)).font = Font(size=9, color=muted)
        ws.cell(row, 2, val).font = Font(bold=True, size=10, color=ink)
    row += 2
    head = row
    fill = PatternFill("solid", fgColor="EEF0F6")
    for i, col in enumerate(report.columns, 1):
        cell = ws.cell(row, i, col["label"])
        cell.font = Font(bold=True, size=9, color=ink)
        cell.fill = fill
        cell.border = Border(bottom=Side(style="medium", color=accent))
        cell.alignment = Alignment(horizontal="right" if col["kind"] != "text" else ("right" if rtl else "left"),
                                   vertical="center", wrap_text=True)
    ws.row_dimensions[row].height = 30
    numfmt = "#,##0" + ("." + "0" * report.decimals if report.decimals else "")
    numfmt = f'{numfmt};({numfmt});"–"'
    for r in report.rows:
        row += 1
        style = r["style"]
        for i, (v, col) in enumerate(zip(r["cells"], report.columns), 1):
            if col["kind"] == "num" and isinstance(v, int):
                cell = ws.cell(row, i, round(v / 1000, report.decimals))
                cell.number_format = numfmt
                cell.alignment = Alignment(horizontal="right")
            elif col["kind"] == "qty" and isinstance(v, (int, float)):
                cell = ws.cell(row, i, v)
                cell.alignment = Alignment(horizontal="right")
            else:
                cell = ws.cell(row, i, "" if v is None else str(v))
                if i == 1 and r["indent"]:
                    cell.alignment = Alignment(indent=r["indent"])
            bold = style in ("section", "group", "subtotal", "total", "grand")
            cell.font = Font(bold=bold, size=10 if style != "section" else 9,
                             color=accent if style == "section" else ink)
            if style == "section":
                cell.fill = PatternFill("solid", fgColor="F7F8FB")
            if style in ("subtotal", "total"):
                cell.border = Border(top=thin)
            if style == "grand":
                cell.border = Border(top=med, bottom=Side(style="double", color=ink))
    # widths
    for i, col in enumerate(report.columns, 1):
        longest = max([len(str(col["label"]))] + [len(report.cell_text(r["cells"][i - 1], col)) for r in report.rows
                                                  if i - 1 < len(r["cells"])])
        ws.column_dimensions[get_column_letter(i)].width = min(max(longest + 3, 11 if col["kind"] != "text" else 14), 60)
    for lbl, v, bold in report.totals:
        row += 1
        ws.cell(row, max(n - 1, 1), lbl).font = Font(bold=bold, size=10, color=ink)
        cell = ws.cell(row, n, round(v / 1000, report.decimals))
        cell.number_format, cell.font = numfmt, Font(bold=bold, size=10, color=ink)
        if bold:
            cell.border = Border(top=med)
    if report.notes:
        row += 2
        for note in report.notes:
            ws.cell(row, 1, note).font = Font(size=9, italic=True, color=muted)
            row += 1
    ws.freeze_panes = ws.cell(head + 1, 1)
    ws.print_title_rows = f"{head}:{head}"
    ws.page_setup.orientation = "landscape" if report.is_landscape else "portrait"
    ws.page_setup.paperSize = ws.PAPERSIZE_A4
    ws.page_setup.fitToWidth, ws.page_setup.fitToHeight = 1, 0
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ws.page_margins.left = ws.page_margins.right = 0.4
    ws.oddFooter.left.text = printed_line()
    ws.oddFooter.left.size = 8
    ws.oddFooter.right.text = f"{t('Page')} &P / &N"
    ws.oddFooter.right.size = 8
    out = io.BytesIO()
    wb.save(out)
    return out.getvalue()


# ---------------------------------------------------------------- PDF

def to_pdf(report):
    from reportlab.lib import colors
    from reportlab.lib.enums import TA_LEFT, TA_RIGHT
    from reportlab.lib.pagesizes import A4, landscape
    from reportlab.lib.styles import ParagraphStyle
    from reportlab.lib.units import mm
    from reportlab.platypus import Image, KeepTogether, SimpleDocTemplate, Spacer, Table, TableStyle
    from .branding import logo_path
    from .ledger import company
    from .pdf import INK, LINE, MUTED, SOFT, BidiParagraph, _register, shape

    _register()
    c = company()
    rtl = get_lang() == "ar"
    accent = colors.HexColor(c["accent"] or "#4f46e5")
    page = landscape(A4) if report.is_landscape else A4
    margin = 14 * mm
    width = page[0] - 2 * margin
    align = TA_RIGHT if rtl else TA_LEFT
    S = {
        "co": ParagraphStyle("co", fontName="Plex-Bold", fontSize=13, leading=16, textColor=accent, alignment=TA_LEFT),
        "co_ar": ParagraphStyle("co_ar", fontName="Plex-Bold", fontSize=13, leading=16, textColor=INK, alignment=TA_RIGHT),
        "small": ParagraphStyle("small", fontName="Plex", fontSize=7.8, leading=10.5, textColor=MUTED, alignment=TA_LEFT),
        "small_r": ParagraphStyle("small_r", fontName="Plex", fontSize=7.8, leading=10.5, textColor=MUTED, alignment=TA_RIGHT),
        "title": ParagraphStyle("title", fontName="Plex-Bold", fontSize=15, leading=19, textColor=INK, alignment=1),
        "sub": ParagraphStyle("sub", fontName="Plex-Bold", fontSize=10, leading=13, textColor=INK, alignment=1),
        "meta": ParagraphStyle("meta", fontName="Plex", fontSize=8.5, leading=11, textColor=MUTED, alignment=1),
        "th": ParagraphStyle("th", fontName="Plex-Bold", fontSize=7.8, leading=10, textColor=INK, alignment=align),
        "thn": ParagraphStyle("thn", fontName="Plex-Bold", fontSize=7.8, leading=10, textColor=INK, alignment=TA_RIGHT),
        "note": ParagraphStyle("note", fontName="Plex", fontSize=7.5, leading=10, textColor=MUTED, alignment=align),
        "sig": ParagraphStyle("sig", fontName="Plex", fontSize=8, leading=11, textColor=MUTED, alignment=1),
    }

    def cell_style(style, num, indent):
        bold = style in ("section", "group", "subtotal", "total", "grand")
        return ParagraphStyle(f"c{style}{num}{indent}", fontName="Plex-Bold" if bold else "Plex",
                              fontSize=7.8 if style == "section" else 8.3, leading=10.4,
                              textColor=accent if style == "section" else INK,
                              alignment=TA_RIGHT if num else align,
                              leftIndent=0 if (num or rtl) else indent * 9, rightIndent=indent * 9 if (rtl and not num) else 0)

    def P(text, style):
        return BidiParagraph("" if text is None else str(text), style)

    # letterhead
    story = []
    left = [P(c["name_en"], S["co"])]
    ids = " · ".join(x for x in (f"{t('CR')} {c['cr_no']}" if c["cr_no"] else "",
                                 f"{t('VAT')} {c['vat_no']}" if c["vat_no"] else "") if x)
    if ids:
        left.append(P(ids, S["small"]))
    if c["address_en"]:
        left.append(P(c["address_en"].replace("\n", ", "), S["small"]))
    contact = " · ".join(x for x in (c["phone"], c["email"]) if x)
    if contact:
        left.append(P(contact, S["small"]))
    right = [P(c["name_ar"] or "", S["co_ar"])]
    if c["address_ar"]:
        right.append(P(c["address_ar"].replace("\n", "، "), S["small_r"]))
    path = logo_path(c)
    logo = ""
    if path:
        from PIL import Image as PILImage
        with PILImage.open(path) as im:
            w, h = im.size
        lh = 15 * mm
        lw = min(lh * w / h, 55 * mm)
        logo = Image(path, width=lw, height=lw * h / w)
    cols = [logo, left, right] if logo else [left, right]
    widths = [60 * mm, (width - 60 * mm) / 2, (width - 60 * mm) / 2] if logo else [width / 2, width / 2]
    head = Table([cols], colWidths=widths)
    head.setStyle(TableStyle([("VALIGN", (0, 0), (-1, -1), "MIDDLE"), ("LEFTPADDING", (0, 0), (-1, -1), 0),
                              ("RIGHTPADDING", (0, 0), (-1, -1), 0)]))
    rule = Table([[""]], colWidths=[width], rowHeights=[3])
    rule.setStyle(TableStyle([("LINEBELOW", (0, 0), (-1, -1), 1.6, accent)]))
    story += [head, Spacer(1, 2.5 * mm), rule, Spacer(1, 4 * mm)]
    ar_title = title_ar(report.title)
    story.append(P(report.title + (f"   ·   {ar_title}" if ar_title else ""), S["title"]))
    if report.subtitle:
        story.append(P(report.subtitle, S["sub"]))
    if report.meta:
        story += [Spacer(1, 1.2 * mm), P("   ·   ".join(report.meta), S["meta"])]
    if report.details:
        half = (len(report.details) + 1) // 2
        cells = []
        dl = ParagraphStyle("dl", parent=S["note"], fontSize=7.5)
        dv = ParagraphStyle("dv", fontName="Plex-Bold", fontSize=8.6, leading=11, textColor=INK, alignment=align)
        for i in range(half):
            rowc = []
            for pair in (report.details[i], report.details[i + half] if i + half < len(report.details) else None):
                rowc += [P(t(pair[0]) if pair else "", dl), P(pair[1] if pair else "", dv)]
            cells.append(list(reversed(rowc)) if rtl else rowc)
        w4 = [width * 0.16, width * 0.34, width * 0.16, width * 0.34]
        det = Table(cells, colWidths=w4)
        det.setStyle(TableStyle([("VALIGN", (0, 0), (-1, -1), "TOP"), ("TOPPADDING", (0, 0), (-1, -1), 2),
                                 ("BOTTOMPADDING", (0, 0), (-1, -1), 2), ("LEFTPADDING", (0, 0), (-1, -1), 0),
                                 ("BOX", (0, 0), (-1, -1), 0.4, LINE), ("BACKGROUND", (0, 0), (-1, -1), SOFT),
                                 ("LEFTPADDING", (0, 0), (-1, -1), 6)]))
        story += [Spacer(1, 3 * mm), det]
    story.append(Spacer(1, 5 * mm))

    # the table
    cols = report.columns
    total_w = sum(col["width"] for col in cols)
    col_w = [width * col["width"] / total_w for col in cols]
    data = [[P(col["label"], S["thn"] if col["kind"] != "text" else S["th"]) for col in cols]]
    style = [("VALIGN", (0, 0), (-1, -1), "MIDDLE"), ("TOPPADDING", (0, 0), (-1, -1), 2.4),
             ("BOTTOMPADDING", (0, 0), (-1, -1), 2.4), ("LEFTPADDING", (0, 0), (-1, -1), 4),
             ("RIGHTPADDING", (0, 0), (-1, -1), 4), ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#eef0f6")),
             ("LINEBELOW", (0, 0), (-1, 0), 1.2, accent)]
    for i, r in enumerate(report.rows, 1):
        st = r["style"]
        data.append([P(report.cell_text(v, col), cell_style(st, col["kind"] != "text", r["indent"] if j == 0 else 0))
                     for j, (v, col) in enumerate(zip(r["cells"], cols))])
        if st == "section":
            style += [("BACKGROUND", (0, i), (-1, i), SOFT), ("TOPPADDING", (0, i), (-1, i), 4)]
        elif st in ("subtotal", "total"):
            style += [("LINEABOVE", (0, i), (-1, i), 0.7, MUTED)]
        elif st == "grand":
            style += [("LINEABOVE", (0, i), (-1, i), 1, INK), ("LINEBELOW", (0, i), (-1, i), 0.5, INK),
                      ("BACKGROUND", (0, i), (-1, i), SOFT)]
        else:
            style += [("LINEBELOW", (0, i), (-1, i), 0.3, LINE)]
    if rtl:
        data = [list(reversed(rw)) for rw in data]
        col_w = list(reversed(col_w))
    tbl = Table(data, colWidths=col_w, repeatRows=1)
    tbl.setStyle(TableStyle(style))
    story.append(tbl)
    if report.totals:
        tl = ParagraphStyle("tl", parent=S["thn"], fontName="Plex", fontSize=8.6)
        tb = ParagraphStyle("tb", parent=S["thn"], fontSize=9.6, leading=13)
        tdata = [[P(lbl, tb if bold else tl), P(report.cell_text(v, {"kind": "num"}), tb if bold else tl)]
                 for lbl, v, bold in report.totals]
        tt = Table([list(reversed(r)) for r in tdata] if rtl else tdata, colWidths=[55 * mm, 35 * mm],
                   hAlign="LEFT" if rtl else "RIGHT")
        tt.setStyle(TableStyle([("TOPPADDING", (0, 0), (-1, -1), 2.5), ("BOTTOMPADDING", (0, 0), (-1, -1), 2.5)] +
                               [("LINEABOVE", (0, i), (-1, i), 0.9, INK) for i, (_, _, b) in enumerate(report.totals) if b]))
        story += [Spacer(1, 3 * mm), tt]
    if report.notes:
        story.append(KeepTogether([Spacer(1, 3 * mm)] + [P(note, S["note"]) for note in report.notes]))
    if report.signatures:
        from .pdf import signature_table
        story.append(KeepTogether([Spacer(1, 12 * mm), signature_table(S["th"], S["note"], width)]))

    buf = io.BytesIO()
    doc = SimpleDocTemplate(buf, pagesize=page, leftMargin=margin, rightMargin=margin, topMargin=12 * mm,
                            bottomMargin=17 * mm, title=f"{report.title} — {c['name_en']}", author=c["name_en"])
    from .pdf import numbered_canvas
    doc.build(story, canvasmaker=numbered_canvas(page, margin, f"{c['name_en']} · {report.title}"))
    return buf.getvalue()


# ---------------------------------------------------------------- financial statements

def statement_report(st, title, f, extra_meta=()):
    """A Report from a profit & loss / balance sheet Statement (rows already nested under their groups)."""
    from . import currency_decimals
    meta = [f"{f['date_from']} → {f['date_to']}" if f["need_from"] else f"{t('As of')} {f['date_to']}",
            ", ".join(f"{b['code']} {nm(b)}" for b in st.branches),
            f"{t('Amounts in')} {st.currency}"] + list(extra_meta)
    rep = Report(title + (f" — {t('Consolidated')}" if st.multi else ""), meta, currency_decimals(st.currency))
    rep.title = title
    if st.multi:
        rep.subtitle = t("Consolidated")
    rep.column(t("Account"), width=3.2)
    if st.multi:
        for b in st.branches:
            rep.column(b["code"], "num")
        rep.column(t("Eliminations"), "num").column(t("Consolidated"), "num", 1.15)
    else:
        rep.column(st.currency, "num", 1.2)
    for r in st.rows:
        if r["kind"] == "section":
            rep.section(r["label"])
            continue
        label = f"{r['account']['code']}  {nm(r['account'])}" if r.get("account") else r["label"]
        style = {"group": "group", "subtotal": "subtotal", "total": "total", "grand": "grand"}.get(r["kind"], "normal")
        vals = (list(r["vals"]) + [r["elim"] or None, r["total"]]) if st.multi else [r["total"]]
        rep.row([label] + vals, style, max((r.get("depth") or 1) - 1, 0))
    if st.multi:
        rep.notes.append(t("Eliminations remove balances on inter-branch accounts so only transactions with outside parties remain."))
    if st.translated:
        rep.notes.append(t("Translation: balance sheet at the closing rate, profit & loss at the average rate; the difference goes to the currency translation reserve."))
    rep.notes += list(st.notes)
    return rep
