"""Convenience features shared by every list: Excel export of what is on screen, and saved (favourite) searches."""
import re
from datetime import date

from flask import Blueprint, Response, abort, flash, g, redirect, request, url_for

from .. import login_required
from ..db import commit, q, x
from ..exports import Report, to_xlsx
from ..i18n import t
from ..ledger import now

bp = Blueprint("ux", __name__, url_prefix="/ux")
NUM = re.compile(r"^\(?-?[\d,]+(\.\d+)?\)?$")


def _num(text):
    """'1,234.50' -> 1234500 mils, '(80.00)' -> -80000; None when the cell is not an amount."""
    s = (text or "").strip().replace("−", "-")
    if not s or not NUM.match(s):
        return None
    neg = s.startswith("(") or s.startswith("-")
    v = float(s.strip("()").replace(",", "").lstrip("-"))
    return -round(v * 1000) if neg else round(v * 1000)


@bp.route("/export", methods=["POST"])
@login_required
def export():
    """The list on screen (its filters, search and grouping already applied) as an Excel file."""
    data = request.get_json(silent=True) or {}
    cols = data.get("columns") or []
    rows = data.get("rows") or []
    if not cols or len(rows) > 20000:
        abort(400)
    title = (data.get("title") or "Export")[:80]
    numeric = [bool(c.get("num")) for c in cols]
    decimals = 3 if any(re.search(r"\.\d{3}\b", str(cell)) for r in rows[:200] for cell in (r.get("cells") or [])) else 2
    rep = Report(title, [f"{t('Exported')} {date.today().isoformat()}", f"{len(rows)} {t('rows')}"], decimals)
    for c, isnum in zip(cols, numeric):
        rep.column(str(c.get("label") or "")[:60], "num" if isnum else "text", 1.0 if isnum else 1.4)
    for r in rows:
        cells = list(r.get("cells") or [])[:len(cols)]
        cells += [""] * (len(cols) - len(cells))
        out = []
        for v, isnum in zip(cells, numeric):
            n = _num(str(v)) if isnum else None
            out.append(n if n is not None else str(v)[:500])
        if r.get("group"):
            rep.row(out, "subtotal")
        else:
            rep.row(out)
    safe = "".join(ch if ch.isalnum() or ch in "-_" else "_" for ch in title) or "export"
    return Response(to_xlsx(rep), mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    headers={"Content-Disposition": f'attachment; filename="{safe}-{date.today().isoformat()}.xlsx"'})


def favorites(path):
    if not g.get("user"):
        return []
    return q("SELECT * FROM saved_filters WHERE user_id = ? AND path = ? ORDER BY name", (g.user["id"], path))


@bp.route("/favorites", methods=["POST"])
@login_required
def save_favorite():
    path = request.form.get("path") or ""
    name = (request.form.get("name") or "").strip()[:60]
    query = (request.form.get("query") or "").lstrip("?")[:2000]
    if not path.startswith("/") or "//" in path or not name:
        flash(t("Give the search a name."), "error")
    else:
        old = q("SELECT id FROM saved_filters WHERE user_id = ? AND path = ? AND name = ?", (g.user["id"], path, name), one=True)
        if old:
            x("UPDATE saved_filters SET query = ? WHERE id = ?", (query, old["id"]))
        else:
            x("INSERT INTO saved_filters (user_id, path, name, query, created_at) VALUES (?,?,?,?,?)",
              (g.user["id"], path, name, query, now()))
        commit()
        flash(t("Search saved in Favorites."), "success")
    return redirect(path + ("?" + query if query else "") if path.startswith("/") and "//" not in path else url_for("home.index"))


@bp.route("/favorites/<int:fid>/delete", methods=["POST"])
@login_required
def delete_favorite(fid):
    f = q("SELECT * FROM saved_filters WHERE id = ? AND user_id = ?", (fid, g.user["id"]), one=True) or abort(404)
    x("DELETE FROM saved_filters WHERE id = ?", (fid,))
    commit()
    return redirect(f["path"])
