"""One ledger account per customer and per vendor (optional, Company settings).

With company.partner_accounts = 'each', every customer gets its own receivable account and every vendor its own
payable account, e.g. 1100-0001 "Al Noor Trading" under the Receivables group. The account carries the partner's
id (accounts.partner_id) so it is recognised as "own". Balances a partner still has on shared accounts are moved
across with a transfer entry:  Dr own receivable / Cr shared receivable (customers),
                               Dr shared payable  / Cr own payable     (vendors).
Open invoices are tracked by document, not by account, so payments after the transfer clear them normally.
"""
from datetime import date

from .db import q, x

SIDES = {True: ("receivable_account_id", "asset_receivable"), False: ("payable_account_id", "liability_payable")}


def enabled():
    from .ledger import company
    return (company()["partner_accounts"] or "shared") == "each"


def own_account(partner_id, customer):
    _, kind = SIDES[customer]
    r = q("SELECT id FROM accounts WHERE partner_id = ? AND kind = ? AND is_group = 0 ORDER BY id LIMIT 1",
          (partner_id, kind), one=True)
    return r["id"] if r else None


def _next_code(base_code):
    n = 0
    for r in q("SELECT code FROM accounts WHERE code LIKE ?", (base_code + "-%",)):
        tail = r["code"][len(base_code) + 1:]
        if tail.isdigit():
            n = max(n, int(tail))
    return f"{base_code}-{n + 1:04d}"


def ensure_account(partner_id, customer, user_id=None, move_balance=True):
    """The partner's own receivable/payable account, opened if missing. Returns its id."""
    from .ledger import company
    col, kind = SIDES[customer]
    acc = own_account(partner_id, customer)
    p = q("SELECT * FROM partners WHERE id = ?", (partner_id,), one=True)
    if not p:
        return None
    if not acc:
        base = q("SELECT * FROM accounts WHERE id = ?", (company()[col],), one=True)
        if not base:
            return None
        acc = x("INSERT INTO accounts (code, name_en, name_ar, kind, parent_id, is_group, partner_id, active) "
                "VALUES (?,?,?,?,?,0,?,1)",
                (_next_code(base["code"]), p["name_en"], p["name_ar"], kind, base["parent_id"], partner_id))
    if p[col] != acc:
        x(f"UPDATE partners SET {col} = ? WHERE id = ?", (acc, partner_id))
    if move_balance:
        move_balances(partner_id, customer, user_id)
    return acc


def _journal(branch_id):
    j = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'general' AND active = 1 "
          "ORDER BY code = 'MISC' DESC, id LIMIT 1", (branch_id,), one=True)
    return j["id"] if j else None


def move_balances(partner_id, customer, user_id=None, d=None):
    """Transfer what the partner still carries on shared receivable/payable accounts to its own account."""
    from .ledger import AccountingError, check_lock, create_move
    from .i18n import t
    _, kind = SIDES[customer]
    acc = own_account(partner_id, customer)
    if not acc:
        return 0
    rows = q("SELECT ml.branch_id, ml.account_id, COALESCE(ml.currency, '') AS cur, "
             "SUM(ml.debit) - SUM(ml.credit) AS bal, SUM(COALESCE(ml.amount_currency, 0)) AS bal_cur "
             "FROM move_lines ml JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id "
             "WHERE m.state = 'posted' AND ml.partner_id = ? AND a.kind = ? AND a.id != ? "
             "AND (a.partner_id IS NULL OR a.partner_id != ?) "
             "GROUP BY ml.branch_id, ml.account_id, cur HAVING bal != 0 OR bal_cur != 0",
             (partner_id, kind, acc, partner_id))
    if not rows:
        return 0
    d = d or date.today().isoformat()
    check_lock(d)
    p = q("SELECT name_en FROM partners WHERE id = ?", (partner_id,), one=True)
    by_branch = {}
    for r in rows:
        by_branch.setdefault(r["branch_id"], []).append(r)
    n = 0
    for branch_id, items in by_branch.items():
        j = _journal(branch_id)
        if not j:
            raise AccountingError(t("The branch needs a miscellaneous (general) journal."))
        lines = []
        label = t("Transfer to own account") + f": {p['name_en']}"
        for r in items:
            bal, cur = r["bal"], r["cur"] or None
            amt_cur = r["bal_cur"] if cur else None
            # take the balance off the shared account ...
            lines.append({"account_id": r["account_id"], "partner_id": partner_id, "label": label,
                          "debit": -bal if bal < 0 else 0, "credit": bal if bal > 0 else 0,
                          "currency": cur, "amount_currency": -amt_cur if amt_cur else None})
            # ... and put it on the partner's own account
            lines.append({"account_id": acc, "partner_id": partner_id, "label": label,
                          "debit": bal if bal > 0 else 0, "credit": -bal if bal < 0 else 0,
                          "currency": cur, "amount_currency": amt_cur})
        lines = [ln for ln in lines if ln["debit"] or ln["credit"]]
        if len(lines) < 2:
            continue
        create_move(branch_id, j, d, lines, user_id, ref=t("Partner account opening"),
                    narration=label, partner_id=partner_id, post=True)
        n += 1
    return n


def open_all(user_id=None):
    """Open accounts for every customer and vendor and move their balances. Safe to run again."""
    opened = q("SELECT COUNT(*) FROM accounts WHERE partner_id IS NOT NULL", one=True)[0]
    moves = 0
    for p in q("SELECT id, is_customer, is_vendor FROM partners ORDER BY id"):
        for customer, flag in ((True, p["is_customer"]), (False, p["is_vendor"])):
            if flag or own_account(p["id"], customer):
                ensure_account(p["id"], customer, user_id, move_balance=False)
                moves += move_balances(p["id"], customer, user_id)
    created = q("SELECT COUNT(*) FROM accounts WHERE partner_id IS NOT NULL", one=True)[0] - opened
    return created, moves


def sweep(user_id=None):
    """Daily: move any balance that reached a shared account again (e.g. reversal of an old entry)."""
    if not enabled():
        return 0
    n = 0
    for r in q("SELECT DISTINCT partner_id, kind FROM accounts WHERE partner_id IS NOT NULL"):
        n += move_balances(r["partner_id"], r["kind"] == "asset_receivable", user_id)
    return n


def rename(partner_id):
    """Keep the own accounts' names in step with the partner's name."""
    p = q("SELECT name_en, name_ar FROM partners WHERE id = ?", (partner_id,), one=True)
    if p:
        x("UPDATE accounts SET name_en = ?, name_ar = ? WHERE partner_id = ?", (p["name_en"], p["name_ar"], partner_id))
