"""Foreign-currency revaluation of open invoices and bills.

An invoice in a foreign currency is booked at its own rate. At a month end the part still open is restated at that
day's rate and the difference booked as unrealized:

    customer invoice, currency stronger     Dr Receivable          Cr Unrealized Exchange Gain
    vendor bill, currency stronger          Dr Unrealized Exchange Loss    Cr Payable
    (and the other way round when the currency weakened)

The entry is reversed on the next day, so the invoice is carried at its own rate again; when it is paid the actual
difference is booked as realized gain or loss on the payment.
"""
from datetime import date, timedelta

from .db import placeholders, q, x
from .i18n import t
from .ledger import INVOICE_KINDS, AccountingError, audit, check_lock, company, create_move, cross_rate, \
    invoice_residuals, now, partner_account, to_base
from .stock import next_number


def partner_sign(kind):
    """Sign of an invoice's open amount on the partner account, debit-positive."""
    info = INVOICE_KINDS[kind]
    customer = info["partner_type"] == "customer"
    return info["sign"] * (1 if customer else -1)


def open_items(branch_id, d):
    """Open foreign-currency invoices of a branch on date d with what they are carried at and their value at d's rate."""
    branch = q("SELECT * FROM branches WHERE id = ?", (branch_id,), one=True)
    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.branch_id = ? AND i.state = 'posted' AND i.date <= ? "
             "AND i.currency IS NOT NULL AND i.currency != ? ORDER BY i.currency, i.date",
             (branch_id, d, branch["currency"]))
    ids = [i["id"] for i in invs]
    res = invoice_residuals(ids, as_of=d)
    res_base = invoice_residuals(ids, as_of=d, base=True)
    rates = {}
    out = []
    for i in invs:
        if res.get(i["id"], 0) <= 0:
            continue
        if i["currency"] not in rates:
            rates[i["currency"]] = cross_rate(i["currency"], branch["currency"], d)
        sign = partner_sign(i["kind"])
        carrying = sign * res_base[i["id"]]
        revalued = sign * to_base(res[i["id"]], rates[i["currency"]], branch["currency"])
        out.append({"invoice": i, "residual": res[i["id"]], "book_rate": i["rate"], "rate": rates[i["currency"]],
                    "carrying": carrying, "revalued": revalued, "diff": revalued - carrying})
    return out, branch


def revaluation_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)
    if not j:
        raise AccountingError(t("The branch needs a miscellaneous (general) journal."))
    return j["id"]


def post_revaluation(branch_id, d, user_id=None):
    """Post the revaluation entry at d and its reversal on the next day. Returns the revaluation id."""
    if q("SELECT 1 FROM fx_revaluations WHERE branch_id = ? AND date = ?", (branch_id, d), one=True):
        raise AccountingError(t("This branch is already revalued on this date."))
    items, branch = open_items(branch_id, d)
    items = [it for it in items if it["diff"]]
    if not items:
        raise AccountingError(t("Nothing to revalue: no open foreign-currency documents, or no rate change."))
    c = company()
    if not (c["fx_unrealized_gain_id"] and c["fx_unrealized_loss_id"]):
        raise AccountingError(t("Set the exchange gain and loss accounts in Company settings."))
    lines = []
    for it in items:
        inv = it["invoice"]
        acc = partner_account(inv["partner_id"], INVOICE_KINDS[inv["kind"]]["partner_type"] == "customer")
        lines.append({"account_id": acc, "partner_id": inv["partner_id"], "label": f"{t('Revaluation')} {inv['number']}",
                      "debit": max(it["diff"], 0), "credit": max(-it["diff"], 0)})
    # one line each for the gains and the losses, so both show in the P&L
    gains = sum(it["diff"] for it in items if it["diff"] > 0)
    losses = -sum(it["diff"] for it in items if it["diff"] < 0)
    label = f"{t('Unrealized exchange difference')} {d}"
    if gains:
        lines.append({"account_id": c["fx_unrealized_gain_id"], "label": label, "debit": 0, "credit": gains})
    if losses:
        lines.append({"account_id": c["fx_unrealized_loss_id"], "label": label, "debit": losses, "credit": 0})
    check_lock(d)
    nxt = (date.fromisoformat(d) + timedelta(days=1)).isoformat()
    number = next_number("fx_revaluations", "number", "FXR", branch_id, d)
    jid = revaluation_journal(branch_id)
    move_id = create_move(branch_id, jid, d, lines, user_id, ref=number, narration=t("Foreign currency revaluation"),
                          post=True)
    reversal = [{**ln, "debit": ln["credit"], "credit": ln["debit"]} for ln in lines]
    rev_id = create_move(branch_id, jid, nxt, reversal, user_id, ref=number,
                         narration=t("Reversal of foreign currency revaluation"), post=True)
    rid = x("INSERT INTO fx_revaluations (number, branch_id, date, move_id, reversal_move_id, created_by, created_at) "
            "VALUES (?,?,?,?,?,?,?)", (number, branch_id, d, move_id, rev_id, user_id, now()))
    for it in items:
        x("INSERT INTO fx_revaluation_lines (reval_id, invoice_id, currency, residual, rate, carrying, revalued, diff) "
          "VALUES (?,?,?,?,?,?,?,?)", (rid, it["invoice"]["id"], it["invoice"]["currency"], it["residual"], it["rate"],
                                       it["carrying"], it["revalued"], it["diff"]))
    audit(user_id, "post", "fx_revaluation", rid, number)
    return rid


def cancel_revaluation(rid, user_id=None):
    r = q("SELECT * FROM fx_revaluations WHERE id = ?", (rid,), one=True)
    rev = q("SELECT date FROM moves WHERE id = ?", (r["reversal_move_id"],), one=True)
    check_lock(r["date"])
    if rev:
        check_lock(rev["date"])
    x("DELETE FROM fx_revaluations WHERE id = ?", (rid,))
    x(f"DELETE FROM moves WHERE id IN ({placeholders([1, 2])})", (r["move_id"], r["reversal_move_id"]))
    audit(user_id, "cancel", "fx_revaluation", rid, r["number"])


def due_months(branch_ids):
    """Month ends that have open foreign-currency documents but no revaluation yet (last 3 months)."""
    if not branch_ids:
        return []
    today = date.today()
    ends, d = [], today.replace(day=1) - timedelta(days=1)
    for _ in range(3):
        ends.append(d.isoformat())
        d = d.replace(day=1) - timedelta(days=1)
    out = []
    for bid in branch_ids:
        for e in ends:
            if q("SELECT 1 FROM fx_revaluations WHERE branch_id = ? AND date = ?", (bid, e), one=True):
                break
            items, _ = open_items(bid, e)
            if any(it["diff"] for it in items):
                out.append((bid, e))
                break
    return out
