"""Customer invoice types.

standard           Tax invoice.
simplified         Simplified tax invoice (retail / B2C customers): the buyer's VAT number is not needed.
advance            Advance (down-payment) tax invoice: VAT is due when the advance is received or invoiced.
                     Dr Accounts Receivable   Cr Advances from Customers (liability)   Cr Output VAT
final              A standard invoice that deducts earlier advances (a negative line on the advances account
                   carrying the same VAT rate):
                     Dr Accounts Receivable (net)  Dr Advances from Customers  Cr Revenue  Cr Output VAT (net)
progress           Progress / milestone invoice from a project (with the retention deduction).
retention_release  Releases retention held on a project.
proforma           A quotation in invoice form. It is never posted and has no accounting effect; it is issued with
                   its own number (PRO/...) and converted into a tax invoice when the sale is confirmed.
debit_note         Increases the amount of an invoice already issued (price correction, extra charges); it refers
                   to the original invoice and gives the reason.
                     Dr Accounts Receivable   Cr Revenue   Cr Output VAT
"""
from .db import q, x
from .i18n import t
from .ledger import AccountingError, audit, company, compute_invoice_line, now

TYPES = {
    "standard": ("Tax Invoice", "فاتورة ضريبية"),
    "simplified": ("Simplified Tax Invoice", "فاتورة ضريبية مبسطة"),
    "advance": ("Advance Payment Tax Invoice", "فاتورة ضريبية لدفعة مقدمة"),
    "progress": ("Progress Tax Invoice", "فاتورة ضريبية لمرحلة إنجاز"),
    "retention_release": ("Retention Release Invoice", "فاتورة الإفراج عن المحتجزات"),
    "proforma": ("Proforma Invoice", "فاتورة مبدئية"),
    "debit_note": ("Debit Note", "إشعار مدين"),
}
FORM_TYPES = ("standard", "simplified", "advance", "proforma", "debit_note")  # chosen on the invoice form
OTHER_TITLES = {"out_refund": ("Credit Note", "إشعار دائن"), "in_invoice": ("Vendor Bill", "فاتورة مورد"),
                "in_refund": ("Vendor Refund", "إشعار مدين من مورد")}


def title(inv):
    """(English, Arabic) document title."""
    if inv["kind"] != "out_invoice":
        return OTHER_TITLES[inv["kind"]]
    typ = inv["invoice_type"] or "standard"
    if typ == "standard":
        if not inv["amount_tax"]:
            return ("Invoice", "فاتورة")
        if inv["id"] and deductions(inv["id"]):
            return ("Tax Invoice (Final)", "فاتورة ضريبية (نهائية)")
    return TYPES.get(typ, TYPES["standard"])


def advance_account():
    acc = company()["advance_account_id"]
    if not acc:
        raise AccountingError(t("Set the advances from customers account in Company settings."))
    return acc


# ---------------------------------------------------------------- advances

def deductions(inv_id):
    return q("SELECT il.*, a.number AS adv_number FROM invoice_lines il JOIN invoices a ON a.id = il.advance_invoice_id "
             "WHERE il.invoice_id = ? ORDER BY il.sequence", (inv_id,))


def advance_used(adv_id, exclude_invoice=None, posted_only=False):
    """Advance (before VAT) already deducted on other invoices (posted, and drafts unless posted_only)."""
    states = "('posted')" if posted_only else "('posted', 'draft')"
    return -int(q(f"SELECT COALESCE(SUM(il.subtotal), 0) FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id "
                  f"WHERE il.advance_invoice_id = ? AND i.state IN {states} AND i.id != ?",
                  (adv_id, exclude_invoice or 0), one=True)[0])


def open_advances(partner_id, branch_id=None, currency=None, exclude_invoice=None):
    """Posted advance invoices of a customer with what is left to deduct (before VAT)."""
    sql = ("SELECT * FROM invoices WHERE partner_id = ? AND kind = 'out_invoice' AND invoice_type = 'advance' "
           "AND state = 'posted'")
    args = [partner_id]
    if branch_id:
        sql += " AND branch_id = ?"
        args.append(branch_id)
    out = []
    for a in q(sql + " ORDER BY date, id", args):
        if (a["currency"] or None) != (currency or None):
            continue
        refunded = -int(q("SELECT COALESCE(SUM(amount_untaxed), 0) FROM invoices WHERE origin_id = ? AND kind = 'out_refund' "
                          "AND state = 'posted'", (a["id"],), one=True)[0])
        left = a["amount_untaxed"] + refunded - advance_used(a["id"], exclude_invoice)
        if left > 0:
            out.append({**dict(a), "left": left})
    return out


def _totals(inv_id):
    t_ = q("SELECT COALESCE(SUM(subtotal), 0), COALESCE(SUM(tax_amount), 0) FROM invoice_lines WHERE invoice_id = ?",
           (inv_id,), one=True)
    x("UPDATE invoices SET amount_untaxed = ?, amount_tax = ?, amount_total = ? WHERE id = ?",
      (t_[0], t_[1], t_[0] + t_[1], inv_id))


def deduct_advance(inv_id, adv_id, amount, user_id=None):
    """Add 'Less: advance' to a draft invoice (amount before VAT; the advance's VAT is reversed with it)."""
    from . import currency_decimals
    inv = q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)
    if inv["state"] != "draft" or inv["kind"] != "out_invoice" or inv["invoice_type"] not in ("standard", "progress"):
        raise AccountingError(t("Advances are deducted on a draft tax invoice."))
    adv = next((a for a in open_advances(inv["partner_id"], inv["branch_id"], inv["currency"], inv_id)
                if a["id"] == adv_id), None)
    if not adv:
        raise AccountingError(t("This advance is not available for this invoice."))
    if amount <= 0 or amount > adv["left"]:
        raise AccountingError(t("The amount is more than what is left of the advance."))
    positive = q("SELECT COALESCE(SUM(subtotal), 0) FROM invoice_lines WHERE invoice_id = ? AND subtotal > 0",
                 (inv_id,), one=True)[0]
    already = -q("SELECT COALESCE(SUM(subtotal), 0) FROM invoice_lines WHERE invoice_id = ? AND advance_invoice_id "
                 "IS NOT NULL", (inv_id,), one=True)[0]
    if amount + already > positive:
        raise AccountingError(t("The advances deducted cannot be more than the invoice."))
    aline = q("SELECT tax_id FROM invoice_lines WHERE invoice_id = ? ORDER BY sequence LIMIT 1", (adv_id,), one=True)
    tax_id = aline["tax_id"] if aline else None
    rate = q("SELECT rate FROM taxes WHERE id = ?", (tax_id,), one=True)["rate"] if tax_id else 0
    cur = inv["currency"] or q("SELECT currency FROM branches WHERE id = ?", (inv["branch_id"],), one=True)["currency"]
    sub, tax = compute_invoice_line(1, -amount, rate, currency_decimals(cur))
    seq = q("SELECT COALESCE(MAX(sequence), 0) + 1 FROM invoice_lines WHERE invoice_id = ?", (inv_id,), one=True)[0]
    x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, tax_id, subtotal, "
      "tax_amount, advance_invoice_id) VALUES (?,?,?,?,1,?,?,?,?,?)",
      (inv_id, seq, f"{t('Less: advance invoice')} {adv['number']}", advance_account(), -amount, tax_id, sub, tax, adv_id))
    _totals(inv_id)
    audit(user_id, "deduct_advance", "invoice", inv_id, adv["number"])


def remove_deduction(inv_id, line_id, user_id=None):
    inv = q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)
    if inv["state"] != "draft":
        raise AccountingError(t("Only draft documents can be edited."))
    x("DELETE FROM invoice_lines WHERE id = ? AND invoice_id = ? AND advance_invoice_id IS NOT NULL", (line_id, inv_id))
    _totals(inv_id)
    audit(user_id, "remove_deduction", "invoice", inv_id)


# ---------------------------------------------------------------- checks when saving and posting

def prepare_lines(invoice_type, lines):
    """On save: an advance invoice is booked to the advances account (no products, no stock)."""
    if invoice_type == "advance":
        acc = advance_account()
        for ln in lines:
            ln["account_id"], ln["product_id"], ln["order_line_id"], ln["cost_centre_id"] = acc, None, None, None
            if ln["subtotal"] <= 0:
                raise AccountingError(t("An advance invoice has positive amounts only."))
    return lines


def check_post(inv):
    """Run by post_invoice for customer invoices."""
    typ = inv["invoice_type"] or "standard"
    if typ == "proforma":
        raise AccountingError(t("A proforma invoice is not posted. Convert it to a tax invoice."))
    lines = q("SELECT * FROM invoice_lines WHERE invoice_id = ?", (inv["id"],))
    if typ == "advance":
        acc = advance_account()
        if any(ln["account_id"] != acc or ln["subtotal"] <= 0 for ln in lines):
            raise AccountingError(t("An advance invoice is booked to the advances account with positive amounts only."))
    if typ == "debit_note":
        orig = q("SELECT * FROM invoices WHERE id = ?", (inv["origin_id"],), one=True) if inv["origin_id"] else None
        if not orig or orig["state"] != "posted" or orig["partner_id"] != inv["partner_id"] or orig["kind"] != "out_invoice":
            raise AccountingError(t("A debit note refers to a posted invoice of the same customer."))
        if not (inv["note_reason"] or "").strip():
            raise AccountingError(t("Give the reason for the debit note."))
    for ln in lines:  # each advance is still available (another invoice may have used it meanwhile)
        if ln["advance_invoice_id"]:
            if typ not in ("standard", "progress"):
                raise AccountingError(t("Advances are deducted on a draft tax invoice."))
            adv = q("SELECT * FROM invoices WHERE id = ?", (ln["advance_invoice_id"],), one=True)
            if not adv or adv["state"] != "posted" or adv["partner_id"] != inv["partner_id"]:
                raise AccountingError(t("This advance is not available for this invoice."))
            if -ln["subtotal"] > adv["amount_untaxed"] - advance_used(adv["id"], inv["id"], posted_only=True):
                raise AccountingError(f"{adv['number']}: {t('The amount is more than what is left of the advance.')}")
        elif ln["account_id"] == company()["advance_account_id"] and typ != "advance" and ln["subtotal"] < 0:
            raise AccountingError(t("Deduct advances with “Deduct advance” so they are tracked."))


def check_unpost(inv):
    if inv["invoice_type"] == "advance" and advance_used(inv["id"], posted_only=True):
        raise AccountingError(t("This advance is deducted on a posted invoice; reset that invoice first."))


# ---------------------------------------------------------------- proforma and debit notes

def issue_proforma(inv_id, user_id=None):
    from .stock import next_number
    inv = q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)
    if inv["invoice_type"] != "proforma" or inv["state"] != "draft":
        raise AccountingError(t("This action is not allowed in the current state."))
    if not q("SELECT 1 FROM invoice_lines WHERE invoice_id = ?", (inv_id,), one=True):
        raise AccountingError(t("Add at least one line."))
    number = inv["number"] or next_number("invoices", "number", "PRO", inv["branch_id"], inv["date"])
    x("UPDATE invoices SET state = 'proforma', number = ? WHERE id = ?", (number, inv_id))
    audit(user_id, "issue_proforma", "invoice", inv_id, number)
    return number


def convert_proforma(inv_id, d, user_id=None):
    """A draft tax invoice with the proforma's lines; the proforma is marked converted."""
    inv = q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)
    if inv["invoice_type"] != "proforma" or inv["state"] not in ("draft", "proforma"):
        raise AccountingError(t("This action is not allowed in the current state."))
    if q("SELECT 1 FROM invoices WHERE proforma_id = ? AND state != 'cancelled'", (inv_id,), one=True):
        raise AccountingError(t("This proforma is already converted."))
    cols = ("branch_id", "journal_id", "kind", "partner_id", "ref", "notes", "amount_untaxed", "amount_tax",
            "amount_total", "order_id", "currency", "rate", "terms", "project_id")
    new_id = x(f"INSERT INTO invoices ({', '.join(cols)}, date, due_date, state, invoice_type, proforma_id, created_by, "
               f"created_at) VALUES ({', '.join('?' * len(cols))}, ?, date(?, '+30 days'), 'draft', 'standard', ?, ?, ?)",
               [inv[c] for c in cols] + [d, d, inv_id, user_id, now()])
    if not inv["ref"] and inv["number"]:
        x("UPDATE invoices SET ref = ? WHERE id = ?", (inv["number"], new_id))
    for ln in q("SELECT * FROM invoice_lines WHERE invoice_id = ? ORDER BY sequence, id", (inv_id,)):
        x("INSERT INTO invoice_lines (invoice_id, sequence, product_id, order_line_id, description, account_id, quantity, "
          "price_unit, tax_id, subtotal, tax_amount, cost_centre_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
          (new_id, ln["sequence"], ln["product_id"], ln["order_line_id"], ln["description"], ln["account_id"],
           ln["quantity"], ln["price_unit"], ln["tax_id"], ln["subtotal"], ln["tax_amount"], ln["cost_centre_id"]))
    x("UPDATE invoices SET state = 'converted' WHERE id = ?", (inv_id,))
    audit(user_id, "convert_proforma", "invoice", inv_id, f"invoice {new_id}")
    return new_id


def create_debit_note(inv_id, user_id=None):
    """Draft debit note on a posted customer invoice: one line on the same income account and VAT, to be priced."""
    from datetime import date
    inv = q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)
    if inv["kind"] != "out_invoice" or inv["state"] != "posted" or inv["invoice_type"] in ("advance", "proforma"):
        raise AccountingError(t("This action is not allowed in the current state."))
    first = q("SELECT * FROM invoice_lines WHERE invoice_id = ? AND subtotal > 0 ORDER BY sequence LIMIT 1", (inv_id,),
              one=True)
    d = date.today().isoformat()
    new_id = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, ref, state, amount_untaxed, "
               "amount_tax, amount_total, origin_id, currency, rate, invoice_type, project_id, created_by, created_at) "
               "VALUES (?,?,'out_invoice',?,?,date(?, '+30 days'),?,'draft',0,0,0,?,?,?,'debit_note',?,?,?)",
               (inv["branch_id"], inv["journal_id"], inv["partner_id"], d, d, inv["number"], inv_id, inv["currency"],
                inv["rate"], inv["project_id"], user_id, now()))
    if first:
        x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, tax_id, "
          "subtotal, tax_amount, cost_centre_id) VALUES (?,0,?,?,1,0,?,0,0,?)",
          (new_id, f"{t('Additional amount for invoice')} {inv['number']}", first["account_id"], first["tax_id"],
           first["cost_centre_id"]))
    audit(user_id, "create", "invoice", new_id, f"debit note on {inv['number']}")
    return new_id
