"""Bank statements and reconciliation.

Each statement line (money in > 0, money out < 0) is matched to one or more posted journal lines on the
bank journal's account whose debit - credit adds up to the same amount. Lines that have no entry in the books
yet (bank charges, transfers, customer receipts) can be booked straight from the statement line.
"""
import csv
import io
from datetime import date, datetime, timedelta

from .db import placeholders, q, x
from .i18n import t
from .ledger import AccountingError, audit, create_move, create_payment, invoice_residuals, now
from .money import to_int
from .stock import next_number

DATE_FORMATS = {"YYYY-MM-DD": "%Y-%m-%d", "DD/MM/YYYY": "%d/%m/%Y", "MM/DD/YYYY": "%m/%d/%Y",
                "DD-MM-YYYY": "%d-%m-%Y", "DD.MM.YYYY": "%d.%m.%Y"}
HEADERS = {
    "date": ("date", "transaction date", "value date", "posting date", "التاريخ", "تاريخ العملية"),
    "description": ("description", "details", "narration", "narrative", "particulars", "memo", "البيان", "الوصف"),
    "reference": ("reference", "ref", "cheque", "cheque no", "transaction id", "المرجع"),
    "amount": ("amount", "المبلغ"),
    "debit": ("debit", "withdrawal", "withdrawals", "money out", "مدين", "سحب"),
    "credit": ("credit", "deposit", "deposits", "money in", "دائن", "إيداع"),
    "partner": ("partner", "payee", "counterparty", "beneficiary", "name", "المستفيد"),
}


def journal_account(journal_id):
    j = q("SELECT * FROM journals WHERE id = ?", (journal_id,), one=True)
    if not j or j["type"] not in ("bank", "cash") or not j["default_account_id"]:
        raise AccountingError(t("Payments need a bank or cash journal with an account."))
    return j


def last_balance(journal_id):
    r = q("SELECT balance_end FROM bank_statements WHERE journal_id = ? ORDER BY date DESC, id DESC LIMIT 1",
          (journal_id,), one=True)
    return r["balance_end"] if r else 0


def create_statement(journal_id, d, name, balance_start, balance_end, user_id=None):
    j = journal_account(journal_id)
    sid = x("INSERT INTO bank_statements (number, journal_id, branch_id, name, date, balance_start, balance_end, state, "
            "created_by, created_at) VALUES (?,?,?,?,?,?,?,'open',?,?)",
            (next_number("bank_statements", "number", "STMT", j["branch_id"], d), journal_id, j["branch_id"], name, d,
             balance_start, balance_end, user_id, now()))
    audit(user_id, "create", "bank_statement", sid)
    return sid


def add_line(statement_id, d, amount, description="", reference="", partner_name=""):
    if not amount:
        raise AccountingError(t("The amount must not be zero."))
    return x("INSERT INTO bank_statement_lines (statement_id, date, description, reference, partner_name, amount) "
             "VALUES (?,?,?,?,?,?)", (statement_id, d, description, reference, partner_name, amount))


def parse_csv(text, date_format="YYYY-MM-DD"):
    """Rows of (date, description, reference, partner, amount mils) from a bank CSV export."""
    fmt = DATE_FORMATS.get(date_format, "%Y-%m-%d")
    sample = text[:4096]
    try:
        dialect = csv.Sniffer().sniff(sample, delimiters=",;\t")
    except csv.Error:
        dialect = csv.excel
    reader = csv.reader(io.StringIO(text.lstrip("﻿")), dialect)
    rows = [r for r in reader if any(c.strip() for c in r)]
    if not rows:
        raise AccountingError(t("The file is empty."))
    header = [h.strip().lower() for h in rows[0]]
    col = {}
    for key, names in HEADERS.items():
        for i, h in enumerate(header):
            if h in names and key not in col:
                col[key] = i
    if "date" not in col or not ("amount" in col or "debit" in col or "credit" in col):
        raise AccountingError(t("The file needs a date column and an amount (or debit/credit) column."))
    out = []
    for n, r in enumerate(rows[1:], start=2):
        cell = lambda k: r[col[k]].strip() if k in col and col[k] < len(r) else ""  # noqa: E731
        try:
            d = datetime.strptime(cell("date"), fmt).date().isoformat()
        except ValueError:
            raise AccountingError(f"{t('Row')} {n}: {t('invalid date')} '{cell('date')}'")
        try:
            if "amount" in col and cell("amount"):
                amt = to_int(cell("amount").replace(" ", ""))
            else:
                amt = to_int(cell("credit").replace(" ", "") or 0) - to_int(cell("debit").replace(" ", "") or 0)
        except ValueError:
            raise AccountingError(f"{t('Row')} {n}: {t('invalid number')}")
        if amt:
            out.append((d, cell("description"), cell("reference"), cell("partner"), amt))
    return out


def import_csv(statement_id, text, date_format):
    rows = parse_csv(text, date_format)
    for d, desc, ref, partner, amt in rows:
        add_line(statement_id, d, amt, desc, ref, partner)
    return len(rows)


def candidates(line_id, window_days=45, limit=8):
    """Unreconciled bank-account journal lines with the same amount, closest dates first."""
    ln = q("SELECT l.*, s.journal_id, s.branch_id FROM bank_statement_lines l JOIN bank_statements s "
           "ON s.id = l.statement_id WHERE l.id = ?", (line_id,), one=True)
    j = journal_account(ln["journal_id"])
    d = date.fromisoformat(ln["date"])
    rows = q("SELECT ml.*, m.date, m.name AS move_name, m.ref, p.name_en AS pname_en, p.name_ar AS pname_ar "
             "FROM move_lines ml JOIN moves m ON m.id = ml.move_id LEFT JOIN partners p ON p.id = ml.partner_id "
             "WHERE m.state = 'posted' AND ml.account_id = ? AND ml.branch_id = ? AND ml.statement_line_id IS NULL "
             "AND m.date BETWEEN ? AND ? ORDER BY m.date",
             (j["default_account_id"], ln["branch_id"], (d - timedelta(days=window_days)).isoformat(),
              (d + timedelta(days=window_days)).isoformat()))
    exact = [r for r in rows if r["debit"] - r["credit"] == ln["amount"]]
    ref = (ln["reference"] or "").lower()

    def score(r):
        hit = ref and (ref in (r["ref"] or "").lower() or ref in (r["move_name"] or "").lower() or
                       ref in (r["label"] or "").lower())
        return (0 if hit else 1, abs((date.fromisoformat(r["date"]) - d).days))
    exact.sort(key=score)
    return exact[:limit], rows


def match(line_id, move_line_ids, user_id=None):
    ln = q("SELECT l.*, s.journal_id, s.branch_id, s.state AS sstate FROM bank_statement_lines l "
           "JOIN bank_statements s ON s.id = l.statement_id WHERE l.id = ?", (line_id,), one=True)
    if ln["state"] == "matched":
        raise AccountingError(t("This statement line is already matched."))
    if not move_line_ids:
        raise AccountingError(t("Select at least one journal item."))
    j = journal_account(ln["journal_id"])
    items = q(f"SELECT ml.*, m.state FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
              f"WHERE ml.id IN ({placeholders(move_line_ids)})", list(move_line_ids))
    if len(items) != len(set(move_line_ids)):
        raise AccountingError(t("Journal item not found."))
    for it in items:
        if it["account_id"] != j["default_account_id"] or it["branch_id"] != ln["branch_id"] or it["state"] != "posted":
            raise AccountingError(t("Only posted items on this bank account can be matched."))
        if it["statement_line_id"]:
            raise AccountingError(t("A journal item is already reconciled."))
    total = sum(it["debit"] - it["credit"] for it in items)
    if total != ln["amount"]:
        raise AccountingError(t("The selected items do not add up to the statement amount.")
                              + f" ({total / 1000:,.2f} ≠ {ln['amount'] / 1000:,.2f})")
    x(f"UPDATE move_lines SET statement_line_id = ? WHERE id IN ({placeholders(move_line_ids)})",
      [line_id] + list(move_line_ids))
    x("UPDATE bank_statement_lines SET state = 'matched' WHERE id = ?", (line_id,))
    audit(user_id, "match", "bank_statement", ln["statement_id"], f"line {line_id}")


def unmatch(line_id, user_id=None):
    ln = q("SELECT l.*, s.state AS sstate FROM bank_statement_lines l JOIN bank_statements s ON s.id = l.statement_id "
           "WHERE l.id = ?", (line_id,), one=True)
    if ln["sstate"] == "reconciled":
        raise AccountingError(t("Reopen the statement first."))
    x("UPDATE move_lines SET statement_line_id = NULL WHERE statement_line_id = ?", (line_id,))
    x("UPDATE bank_statement_lines SET state = 'unmatched' WHERE id = ?", (line_id,))
    audit(user_id, "unmatch", "bank_statement", ln["statement_id"], f"line {line_id}")


def _bank_line_of(move_id, account_id):
    return q("SELECT id FROM move_lines WHERE move_id = ? AND account_id = ? AND statement_line_id IS NULL "
             "ORDER BY id LIMIT 1", (move_id, account_id), one=True)["id"]


def book_counterpart(line_id, account_id, partner_id=None, label=None, user_id=None):
    """Post the statement line against another account (bank charges, interest, transfers…) and match it."""
    ln = q("SELECT l.*, s.journal_id, s.branch_id FROM bank_statement_lines l JOIN bank_statements s "
           "ON s.id = l.statement_id WHERE l.id = ?", (line_id,), one=True)
    j = journal_account(ln["journal_id"])
    amt = abs(ln["amount"])
    text = label or ln["description"] or ln["reference"] or ""
    bank = {"account_id": j["default_account_id"], "partner_id": partner_id, "label": text}
    other = {"account_id": int(account_id), "partner_id": partner_id, "label": text}
    if ln["amount"] > 0:
        lines = [{**bank, "debit": amt, "credit": 0}, {**other, "debit": 0, "credit": amt}]
    else:
        lines = [{**other, "debit": amt, "credit": 0}, {**bank, "debit": 0, "credit": amt}]
    mid = create_move(ln["branch_id"], j["id"], ln["date"], lines, user_id=user_id, ref=ln["reference"] or text,
                      move_type="entry", partner_id=partner_id, post=True)
    match(line_id, [_bank_line_of(mid, j["default_account_id"])], user_id)
    return mid


def open_invoices_for(line_id):
    ln = q("SELECT l.*, s.branch_id FROM bank_statement_lines l JOIN bank_statements s ON s.id = l.statement_id "
           "WHERE l.id = ?", (line_id,), one=True)
    kinds = ("out_invoice", "in_refund") if ln["amount"] > 0 else ("in_invoice", "out_refund")
    invs = q("SELECT i.*, p.name_en AS pname_en, p.name_ar AS pname_ar FROM invoices i JOIN partners p ON p.id = i.partner_id "
             "WHERE i.state = 'posted' AND i.branch_id = ? AND i.kind IN (?, ?) AND i.currency IS NULL ORDER BY i.date",
             (ln["branch_id"], *kinds))
    res = invoice_residuals([i["id"] for i in invs])
    out = [dict(i, residual=res.get(i["id"], 0)) for i in invs if res.get(i["id"], 0) > 0]
    out.sort(key=lambda i: (i["residual"] != abs(ln["amount"]), i["date"]))
    return out


def pay_invoice(line_id, invoice_id, user_id=None):
    """Register a payment for an open invoice from the statement line and match it."""
    ln = q("SELECT l.*, s.journal_id, s.branch_id FROM bank_statement_lines l JOIN bank_statements s "
           "ON s.id = l.statement_id WHERE l.id = ?", (line_id,), one=True)
    inv = q("SELECT * FROM invoices WHERE id = ?", (invoice_id,), one=True)
    if not inv or inv["branch_id"] != ln["branch_id"]:
        raise AccountingError(t("The payment must be in the same branch as the document."))
    customer = inv["kind"].startswith("out_")
    pid = create_payment({"branch_id": ln["branch_id"], "journal_id": ln["journal_id"], "partner_id": inv["partner_id"],
                          "partner_type": "customer" if customer else "vendor",
                          "payment_type": "inbound" if ln["amount"] > 0 else "outbound", "date": ln["date"],
                          "amount": abs(ln["amount"]), "ref": ln["reference"] or inv["number"],
                          "invoice_id": invoice_id}, user_id)
    move_id = q("SELECT move_id FROM payments WHERE id = ?", (pid,), one=True)["move_id"]
    j = journal_account(ln["journal_id"])
    match(line_id, [_bank_line_of(move_id, j["default_account_id"])], user_id)
    return pid


def auto_match(statement_id, user_id=None):
    """Match lines that have exactly one same-amount candidate, or a same-amount candidate with the same reference."""
    n = 0
    for ln in q("SELECT * FROM bank_statement_lines WHERE statement_id = ? AND state = 'unmatched' ORDER BY date",
                (statement_id,)):
        exact, _ = candidates(ln["id"], window_days=10)
        ref = (ln["reference"] or "").lower()
        ref_hit = bool(exact) and bool(ref) and ref in " ".join(
            (exact[0][k] or "") for k in ("ref", "move_name", "label")).lower()
        if len(exact) == 1 or ref_hit:
            match(ln["id"], [exact[0]["id"]], user_id)
            n += 1
    return n


def statement_totals(statement_id):
    s = q("SELECT * FROM bank_statements WHERE id = ?", (statement_id,), one=True)
    r = q("SELECT COALESCE(SUM(amount), 0) AS total, COUNT(*) AS n, "
          "SUM(CASE WHEN state = 'unmatched' THEN 1 ELSE 0 END) AS open FROM bank_statement_lines WHERE statement_id = ?",
          (statement_id,), one=True)
    computed = s["balance_start"] + r["total"]
    return {"lines": r["n"], "open": r["open"] or 0, "computed_end": computed, "difference": s["balance_end"] - computed}


def validate_statement(statement_id, user_id=None):
    tot = statement_totals(statement_id)
    if tot["open"]:
        raise AccountingError(t("Match every statement line first."))
    if tot["difference"]:
        raise AccountingError(t("The ending balance does not equal the starting balance plus the lines."))
    x("UPDATE bank_statements SET state = 'reconciled' WHERE id = ?", (statement_id,))
    audit(user_id, "reconcile", "bank_statement", statement_id)


def reconciliation_report(journal_id, as_of):
    """Bank reconciliation statement: balance per bank vs balance per books, with the items explaining the gap."""
    j = journal_account(journal_id)
    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 = ? AND m.date <= ?",
             (j["default_account_id"], j["branch_id"], as_of), one=True)[0]
    st = q("SELECT * FROM bank_statements WHERE journal_id = ? AND date <= ? ORDER BY date DESC, id DESC LIMIT 1",
           (journal_id, as_of), one=True)
    bank_balance = st["balance_end"] if st else 0
    # reconciliation starts with the first statement: earlier book items are treated as cleared
    first = q("SELECT MIN(COALESCE((SELECT MIN(l.date) FROM bank_statement_lines l WHERE l.statement_id = s.id), s.date)) "
              "FROM bank_statements s WHERE s.journal_id = ?", (journal_id,), one=True)[0] or as_of
    # books items not yet on a statement, or matched to a statement line dated after as_of
    outstanding = q("SELECT ml.*, m.date, m.name AS move_name, m.ref, p.name_en AS pname_en, p.name_ar AS pname_ar "
                    "FROM move_lines ml JOIN moves m ON m.id = ml.move_id LEFT JOIN partners p ON p.id = ml.partner_id "
                    "LEFT JOIN bank_statement_lines bl ON bl.id = ml.statement_line_id "
                    "WHERE m.state = 'posted' AND ml.account_id = ? AND ml.branch_id = ? AND m.date <= ? AND m.date >= ? "
                    "AND (ml.statement_line_id IS NULL OR bl.date > ?) ORDER BY m.date",
                    (j["default_account_id"], j["branch_id"], as_of, first, as_of))
    deposits = [r for r in outstanding if r["debit"] > r["credit"]]
    payments = [r for r in outstanding if r["credit"] > r["debit"]]
    # statement lines up to as_of not yet in the books
    unbooked = q("SELECT l.* FROM bank_statement_lines l JOIN bank_statements s ON s.id = l.statement_id "
                 "WHERE s.journal_id = ? AND l.state = 'unmatched' AND l.date <= ? ORDER BY l.date", (journal_id, as_of))
    dep_total = sum(r["debit"] - r["credit"] for r in deposits)
    pay_total = sum(r["credit"] - r["debit"] for r in payments)
    unbooked_total = sum(r["amount"] for r in unbooked)
    adjusted_bank = bank_balance + dep_total - pay_total
    adjusted_book = book + unbooked_total
    return {"journal": j, "book": book, "bank": bank_balance, "statement": st, "deposits": deposits,
            "payments": payments, "unbooked": unbooked, "dep_total": dep_total, "pay_total": pay_total,
            "unbooked_total": unbooked_total, "adjusted_bank": adjusted_bank, "adjusted_book": adjusted_book,
            "difference": adjusted_bank - adjusted_book}
