from datetime import date

from flask import Blueprint, abort, flash, redirect, render_template, request, url_for

from .. import check_branch, context_branch_ids, login_required, module_required, write_required
from ..db import commit, get_db, placeholders, q, x
from ..i18n import t
from ..ledger import AccountingError, audit
from ..money import to_int
from ..bankrec import DATE_FORMATS, add_line, auto_match, book_counterpart, candidates, create_statement, import_csv, \
    last_balance, match, open_invoices_for, pay_invoice, reconciliation_report, statement_totals, unmatch, \
    validate_statement
from .common import accounts_list, branch_map, partners_list, user_id

bp = Blueprint("bank", __name__, url_prefix="/bank")
bp.before_request(module_required("bank"))


def bank_journals(ids):
    if not ids:
        return []
    return q(f"SELECT j.*, b.code AS bcode, b.currency FROM journals j JOIN branches b ON b.id = j.branch_id "
             f"WHERE j.type IN ('bank', 'cash') AND j.active = 1 AND j.default_account_id IS NOT NULL "
             f"AND j.branch_id IN ({placeholders(ids)}) ORDER BY b.code, j.type, j.code", list(ids))


def statement_or_404(sid):
    s = q("SELECT s.*, j.code AS jcode, j.name_en AS jname_en, j.name_ar AS jname_ar, j.default_account_id "
          "FROM bank_statements s JOIN journals j ON j.id = s.journal_id WHERE s.id = ?", (sid,), one=True)
    if not s:
        abort(404)
    check_branch(s["branch_id"])
    return s


@bp.route("/")
@login_required
def index():
    ids = context_branch_ids()
    journals = []
    for j in bank_journals(ids):
        book = q("SELECT COALESCE(SUM(ml.debit - ml.credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                 "WHERE m.state = 'posted' AND ml.account_id = ? AND ml.branch_id = ?",
                 (j["default_account_id"], j["branch_id"]), one=True)[0]
        unrec = q("SELECT COUNT(*) FROM move_lines ml JOIN moves m ON m.id = ml.move_id WHERE m.state = 'posted' "
                  "AND ml.account_id = ? AND ml.branch_id = ? AND ml.statement_line_id IS NULL",
                  (j["default_account_id"], j["branch_id"]), one=True)[0]
        last = q("SELECT * FROM bank_statements WHERE journal_id = ? ORDER BY date DESC, id DESC LIMIT 1", (j["id"],), one=True)
        journals.append({"j": j, "book": book, "unreconciled": unrec, "last": last})
    statements = q(f"SELECT s.*, j.code AS jcode, (SELECT COUNT(*) FROM bank_statement_lines WHERE statement_id = s.id) AS n, "
                   f"(SELECT COUNT(*) FROM bank_statement_lines WHERE statement_id = s.id AND state = 'unmatched') AS open "
                   f"FROM bank_statements s JOIN journals j ON j.id = s.journal_id "
                   f"WHERE s.branch_id IN ({placeholders(ids)}) ORDER BY s.date DESC, s.id DESC LIMIT 200", ids) if ids else []
    return render_template("bank/index.html", journals=journals, statements=statements, branches=branch_map())


@bp.route("/new", methods=["GET", "POST"])
@write_required
def new():
    ids = context_branch_ids()
    journals = bank_journals(ids)
    if request.method == "POST":
        f = request.form
        try:
            j = q("SELECT * FROM journals WHERE id = ?", (int(f["journal_id"]),), one=True)
            check_branch(j["branch_id"])
            sid = create_statement(j["id"], f["date"], f.get("name"), to_int(f.get("balance_start") or 0),
                                   to_int(f.get("balance_end") or 0), user_id())
            n = 0
            file = request.files.get("file")
            if file and file.filename:
                n = import_csv(sid, file.read().decode("utf-8-sig", errors="replace"), f.get("date_format"))
            commit()
            flash(f"{t('Statement created.')} {n} {t('lines imported.')}" if n else t("Statement created."), "success")
            return redirect(url_for("bank.statement", sid=sid))
        except (AccountingError, ValueError, KeyError) as e:
            get_db().rollback()
            flash(str(e), "error")
    jid = request.args.get("journal_id", type=int) or (journals[0]["id"] if journals else None)
    return render_template("bank/new.html", journals=journals, jid=jid, formats=list(DATE_FORMATS),
                           starts={j["id"]: last_balance(j["id"]) for j in journals}, today=date.today().isoformat())


@bp.route("/<int:sid>")
@login_required
def statement(sid):
    s = statement_or_404(sid)
    lines = []
    for ln in q("SELECT * FROM bank_statement_lines WHERE statement_id = ? ORDER BY date, id", (sid,)):
        item = {"ln": ln, "matched": [], "cands": [], "invoices": []}
        if ln["state"] == "matched":
            item["matched"] = q("SELECT ml.*, m.name AS move_name, m.date, m.id AS mid FROM move_lines ml "
                                "JOIN moves m ON m.id = ml.move_id WHERE ml.statement_line_id = ?", (ln["id"],))
        elif s["state"] == "open":
            exact, nearby = candidates(ln["id"])
            item["cands"] = exact
            item["nearby"] = [r for r in nearby if r not in exact][:12]
            item["invoices"] = open_invoices_for(ln["id"])[:10]
        lines.append(item)
    branch = q("SELECT * FROM branches WHERE id = ?", (s["branch_id"],), one=True)
    return render_template("bank/statement.html", s=s, lines=lines, tot=statement_totals(sid), branch=branch,
                           accounts=accounts_list(), partners=partners_list(), formats=list(DATE_FORMATS),
                           today=date.today().isoformat())


@bp.route("/<int:sid>/<action>", methods=["POST"])
@write_required
def action(sid, action):
    s = statement_or_404(sid)
    f = request.form
    anchor = ""
    try:
        if s["state"] != "open" and action not in ("reopen",):
            raise AccountingError(t("Reopen the statement first."))
        if action == "line":
            add_line(sid, f["date"], to_int(f.get("amount_in") or 0) - to_int(f.get("amount_out") or 0),
                     f.get("description"), f.get("reference"), f.get("partner_name"))
        elif action == "import":
            file = request.files.get("file")
            if not file or not file.filename:
                raise AccountingError(t("Choose a CSV file."))
            n = import_csv(sid, file.read().decode("utf-8-sig", errors="replace"), f.get("date_format"))
            flash(f"{n} {t('lines imported.')}", "success")
        elif action == "match":
            line_id = int(f["line_id"])
            match(line_id, [int(v) for v in f.getlist("move_line_ids")], user_id())
            anchor = f"#l{line_id}"
        elif action == "unmatch":
            unmatch(int(f["line_id"]), user_id())
            anchor = f"#l{f['line_id']}"
        elif action == "book":
            line_id = int(f["line_id"])
            if not f.get("account_id"):
                raise AccountingError(t("Every line needs an account."))
            book_counterpart(line_id, int(f["account_id"]), int(f["partner_id"]) if f.get("partner_id") else None,
                             f.get("label"), user_id())
            anchor = f"#l{line_id}"
        elif action == "pay":
            line_id = int(f["line_id"])
            pay_invoice(line_id, int(f["invoice_id"]), user_id())
            anchor = f"#l{line_id}"
        elif action == "delete_line":
            ln = q("SELECT * FROM bank_statement_lines WHERE id = ? AND statement_id = ?", (int(f["line_id"]), sid), one=True)
            if not ln or ln["state"] != "unmatched":
                raise AccountingError(t("This action is not allowed in the current state."))
            x("DELETE FROM bank_statement_lines WHERE id = ?", (ln["id"],))
        elif action == "auto":
            n = auto_match(sid, user_id())
            flash(f"{n} {t('lines matched automatically.')}", "success")
        elif action == "balances":
            x("UPDATE bank_statements SET name = ?, date = ?, balance_start = ?, balance_end = ? WHERE id = ?",
              (f.get("name"), f["date"], to_int(f.get("balance_start") or 0), to_int(f.get("balance_end") or 0), sid))
        elif action == "validate":
            validate_statement(sid, user_id())
            flash(t("Statement reconciled."), "success")
        elif action == "reopen" and s["state"] == "reconciled":
            x("UPDATE bank_statements SET state = 'open' WHERE id = ?", (sid,))
            audit(user_id(), "reopen", "bank_statement", sid)
        elif action == "delete":
            if q("SELECT 1 FROM bank_statement_lines WHERE statement_id = ? AND state = 'matched'", (sid,)):
                raise AccountingError(t("Unmatch the lines first."))
            x("DELETE FROM bank_statements WHERE id = ?", (sid,))
            commit()
            return redirect(url_for("bank.index"))
        else:
            raise AccountingError(t("This action is not allowed in the current state."))
        commit()
    except (AccountingError, ValueError, KeyError) as e:
        get_db().rollback()
        flash(str(e), "error")
    return redirect(url_for("bank.statement", sid=sid) + anchor)


@bp.route("/report")
@login_required
def report():
    ids = context_branch_ids()
    journals = bank_journals(ids)
    jid = request.args.get("journal_id", type=int) or (journals[0]["id"] if journals else None)
    as_of = request.args.get("date") or date.today().isoformat()
    data = None
    if jid:
        j = q("SELECT * FROM journals WHERE id = ?", (jid,), one=True)
        check_branch(j["branch_id"])
        data = reconciliation_report(jid, as_of)
    cur = next((j["currency"] for j in journals if j["id"] == jid), None)
    return render_template("bank/report.html", journals=journals, jid=jid, as_of=as_of, d=data, cur=cur)
