"""Petty cash: a cash float held by a custodian, spent through expense vouchers and topped up from the bank.

Each fund has its own cash account (under Cash and Bank) and cash journal, so its balance is on the trial balance.

    Expense voucher     Dr Expenses (by line, with cost centre)  Dr Input VAT        Cr Petty Cash
    Top-up              Dr Petty Cash                                                Cr Bank
    Cash count          Dr / Cr Cash Over / Short                                    Cr / Dr Petty Cash

An imprest fund is topped up to its float: the top-up suggested is the float less what is left in the box.
"""
from collections import defaultdict

from .db import q, x
from .i18n import t
from .ledger import AccountingError, audit, check_journal, compute_invoice_line, create_move, now
from .stock import next_number


def fund_balance(fund, upto=None):
    sql = ("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 = ?")
    args = [fund["account_id"], fund["branch_id"]]
    if upto:
        sql += " AND m.date <= ?"
        args.append(upto)
    return q(sql, args, one=True)[0]


def create_fund(branch_id, name_en, name_ar, custodian, float_amount):
    """A new fund: its own cash account (next free code after 1010) and cash journal."""
    parent = q("SELECT parent_id FROM accounts WHERE code = '1010'", one=True)
    code = 1011
    while q("SELECT 1 FROM accounts WHERE code = ?", (str(code),), one=True):
        code += 1
    acc = x("INSERT INTO accounts (code, name_en, name_ar, kind, parent_id) VALUES (?,?,?, 'asset_cash', ?)",
            (str(code), f"Petty Cash — {name_en}", f"العهدة النقدية — {name_ar or name_en}", parent and parent[0]))
    n = 1
    while q("SELECT 1 FROM journals WHERE branch_id = ? AND code = ?", (branch_id, f"PC{n}"), one=True):
        n += 1
    jid = x("INSERT INTO journals (branch_id, code, name_en, name_ar, type, default_account_id) VALUES (?,?,?,?, 'cash', ?)",
            (branch_id, f"PC{n}", f"Petty Cash — {name_en}", f"العهدة النقدية — {name_ar or name_en}", acc))
    return x("INSERT INTO petty_cash_funds (name_en, name_ar, branch_id, journal_id, account_id, custodian, float_amount) "
             "VALUES (?,?,?,?,?,?,?)", (name_en, name_ar, branch_id, jid, acc, custodian, float_amount))


def voucher_totals(lines, decimals):
    taxes = {r["id"]: r for r in q("SELECT * FROM taxes")}
    out = []
    for ln in lines:
        rate = taxes[ln["tax_id"]]["rate"] if ln.get("tax_id") else 0
        sub, tax = compute_invoice_line(1, ln["amount"], rate, decimals)
        out.append({**ln, "amount": sub, "tax_amount": tax})
    return out


def post_voucher(vid, user_id=None):
    v = q("SELECT * FROM petty_cash_vouchers WHERE id = ?", (vid,), one=True)
    if v["state"] != "draft":
        raise AccountingError(t("Only draft documents can be posted."))
    fund = q("SELECT * FROM petty_cash_funds WHERE id = ?", (v["fund_id"],), one=True)
    lines = q("SELECT * FROM petty_cash_lines WHERE voucher_id = ? ORDER BY id", (vid,))
    if not lines:
        raise AccountingError(t("Add at least one line."))
    seen = set()
    for ln in lines:  # a shop's receipt / invoice number is paid only once to the same payee
        ref = (ln["receipt_ref"] or "").strip().lower()
        if not ref:
            continue
        if ref in seen:
            raise AccountingError(t("Receipt number") + f" {ln['receipt_ref']} " + t("appears twice in this voucher."))
        seen.add(ref)
        dup = q("SELECT v.number FROM petty_cash_lines l JOIN petty_cash_vouchers v ON v.id = l.voucher_id "
                "WHERE v.id != ? AND v.state = 'posted' AND lower(trim(l.receipt_ref)) = ? "
                "AND lower(trim(COALESCE(v.payee, ''))) = lower(trim(?))", (vid, ref, v["payee"] or ""), one=True)
        if dup:
            raise AccountingError(t("Receipt number") + f" {ln['receipt_ref']} " + t("from this payee is already paid in") +
                                  f" {dup['number']}.")
    total = sum(ln["amount"] + ln["tax_amount"] for ln in lines)
    available = fund_balance(fund, v["date"])
    if total > available:
        raise AccountingError(f"{t('Not enough cash in the fund')}: {available / 1000:,.2f}. {t('Top it up first.')}")
    entry, taxes = [], defaultdict(int)
    label = f"{v['number']} {v['payee'] or ''}".strip()
    for ln in lines:
        entry.append({"account_id": ln["account_id"], "label": ln["description"] or label, "debit": ln["amount"],
                      "credit": 0, "tax_id": ln["tax_id"], "cost_centre_id": ln["cost_centre_id"]})
        if ln["tax_id"] and ln["tax_amount"]:
            taxes[ln["tax_id"]] += ln["tax_amount"]
    for tax_id, amt in taxes.items():
        tax = q("SELECT * FROM taxes WHERE id = ?", (tax_id,), one=True)
        if not tax["account_id"]:
            raise AccountingError(t("The tax has no account") + f": {tax['name_en']}")
        entry.append({"account_id": tax["account_id"], "label": tax["name_en"], "debit": amt, "credit": 0, "tax_id": tax_id})
    entry.append({"account_id": fund["account_id"], "label": label, "debit": 0, "credit": total})
    move_id = create_move(fund["branch_id"], fund["journal_id"], v["date"], entry, user_id, ref=v["number"],
                          narration=t("Petty cash voucher"), post=True)
    x("UPDATE petty_cash_vouchers SET state = 'posted', move_id = ?, total = ? WHERE id = ?", (move_id, total, vid))
    audit(user_id, "post", "petty_cash", vid, v["number"])
    return move_id


def top_up(fund_id, bank_journal_id, amount, d, user_id=None):
    fund = q("SELECT * FROM petty_cash_funds WHERE id = ?", (fund_id,), one=True)
    j = check_journal(fund["branch_id"], bank_journal_id)
    if j["type"] != "bank" or not j["default_account_id"]:
        raise AccountingError(t("Choose a bank journal of the branch."))
    if amount <= 0:
        raise AccountingError(t("The amount must be greater than zero."))
    label = f"{t('Petty cash top-up')} — {fund['name_en']}"
    move_id = create_move(fund["branch_id"], j["id"], d, [
        {"account_id": fund["account_id"], "label": label, "debit": amount, "credit": 0},
        {"account_id": j["default_account_id"], "label": label, "debit": 0, "credit": amount}],
        user_id, ref=t("Petty cash top-up"), narration=label, post=True)
    x("INSERT INTO petty_cash_topups (fund_id, date, amount, move_id, kind, created_by, created_at) "
      "VALUES (?,?,?,?, 'topup', ?, ?)", (fund_id, d, amount, move_id, user_id, now()))
    audit(user_id, "topup", "petty_cash_fund", fund_id, str(amount))
    return move_id


def count_cash(fund_id, counted, d, user_id=None):
    """Book the difference between the cash counted and the book balance to Cash Over / Short."""
    fund = q("SELECT * FROM petty_cash_funds WHERE id = ?", (fund_id,), one=True)
    diff = counted - fund_balance(fund, d)
    if not diff:
        return None
    over_short = q("SELECT id FROM accounts WHERE code = '6950'", one=True)
    if not over_short:
        raise AccountingError(t("Missing account") + " 6950")
    label = f"{t('Cash count')} — {fund['name_en']}"
    move_id = create_move(fund["branch_id"], fund["journal_id"], d, [
        {"account_id": fund["account_id"], "label": label, "debit": max(diff, 0), "credit": max(-diff, 0)},
        {"account_id": over_short["id"], "label": label, "debit": max(-diff, 0), "credit": max(diff, 0)}],
        user_id, ref=t("Cash count"), narration=label, post=True)
    x("INSERT INTO petty_cash_topups (fund_id, date, amount, move_id, kind, created_by, created_at) "
      "VALUES (?,?,?,?, 'count', ?, ?)", (fund_id, d, diff, move_id, user_id, now()))
    return move_id


def next_voucher_number(fund, d):
    return next_number("petty_cash_vouchers", "number", "PCV", fund["branch_id"], d)
