"""Point of sale. Orders are recorded during a cashier session; closing the session posts one accounting entry
(cash and card received, revenue per income account, output VAT, cash over/short) and one stock movement
(cost of goods sold at average cost) for the whole session."""
from collections import defaultdict

from .db import placeholders, q, x
from .i18n import nm, t
from .ledger import AccountingError, audit, check_lock, compute_invoice_line, create_move, now
from .money import round_cur
from .stock import EPS, PICKING_PREFIX, _bump, _out_value, avg_cost, cogs_account, inventory_account, next_number, \
    stock_journal


def register(register_id):
    r = q("SELECT * FROM pos_registers WHERE id = ?", (register_id,), one=True)
    if not r:
        raise AccountingError(t("Register not found."))
    return r


def branch_decimals(branch_id):
    return q("SELECT c.decimals FROM branches b JOIN currencies c ON c.code = b.currency WHERE b.id = ?",
             (branch_id,), one=True)["decimals"]


def open_session(register_id, opening_cash, user_id):
    r = register(register_id)
    if q("SELECT 1 FROM pos_sessions WHERE register_id = ? AND state = 'open'", (register_id,)):
        raise AccountingError(t("This register already has an open session."))
    today = now()[:10]
    sid = x("INSERT INTO pos_sessions (number, register_id, branch_id, user_id, opened_at, opening_cash, state) "
            "VALUES (?,?,?,?,?,?,'open')",
            (next_number("pos_sessions", "number", "POS", r["branch_id"], today), register_id, r["branch_id"],
             user_id, now(), opening_cash))
    audit(user_id, "open", "pos_session", sid)
    return sid


def price_line(product, qty, discount, decimals, branch_id, price_unit=None):
    from .ledger import effective_tax
    tax_id = effective_tax(branch_id, product["sale_tax_id"], "sale")
    tax = q("SELECT * FROM taxes WHERE id = ?", (tax_id,), one=True) if tax_id else None
    price = product["sale_price"] if price_unit is None else price_unit
    gross = int(round(qty * price))
    after = int(round(gross * (1 - (discount or 0) / 100.0)))
    sub, tx = compute_invoice_line(1, after, tax["rate"] if tax else 0, decimals)
    return {"product_id": product["id"], "description": nm(product), "quantity": qty, "price_unit": price,
            "discount": discount or 0, "tax_id": tax["id"] if tax else None, "subtotal": sub, "tax_amount": tx}


def session_pending_qty(session_id, product_id):
    r = q("SELECT COALESCE(SUM(l.quantity), 0) FROM pos_order_lines l JOIN pos_orders o ON o.id = l.order_id "
          "WHERE o.session_id = ? AND l.product_id = ?", (session_id, product_id), one=True)
    return r[0]


def create_order(session_id, items, cash_tendered, card_amount, partner_id=None, user_id=None):
    """items: [{product_id, qty, discount}]. Amounts in mils. Returns order id."""
    s = q("SELECT * FROM pos_sessions WHERE id = ?", (session_id,), one=True)
    if not s or s["state"] != "open":
        raise AccountingError(t("The session is closed."))
    reg = register(s["register_id"])
    dec = branch_decimals(s["branch_id"])
    lines = []
    for it in items:
        qty = float(it.get("qty") or 0)
        if qty <= 0:
            continue
        p = q("SELECT * FROM products WHERE id = ? AND active = 1", (int(it["product_id"]),), one=True)
        if not p:
            raise AccountingError(t("Product not found."))
        disc = float(it.get("discount") or 0)
        if disc < 0 or disc > 100:
            raise AccountingError(t("The discount must be between 0 and 100%."))
        if p["type"] == "stock" and reg["warehouse_id"]:
            on_hand = q("SELECT COALESCE(qty, 0) FROM stock_quants WHERE product_id = ? AND warehouse_id = ?",
                        (p["id"], reg["warehouse_id"]), one=True)
            available = (on_hand[0] if on_hand else 0) - session_pending_qty(session_id, p["id"])
            if qty > available + EPS:
                raise AccountingError(f"{t('Not enough stock for')} {nm(p)}: {t('available')} {max(available, 0):g}")
        lines.append(price_line(p, qty, disc, dec, s["branch_id"]))
    if not lines:
        raise AccountingError(t("The cart is empty."))
    untaxed = sum(ln["subtotal"] for ln in lines)
    tax = sum(ln["tax_amount"] for ln in lines)
    total = untaxed + tax
    card = max(0, min(card_amount or 0, total))
    cash_due = total - card
    tendered = cash_tendered if cash_tendered is not None else cash_due
    if tendered < cash_due:
        raise AccountingError(t("The amount paid is less than the total."))
    oid = x("INSERT INTO pos_orders (number, session_id, partner_id, ts, amount_untaxed, amount_tax, amount_total, "
            "paid_cash, paid_card, tendered, change_given, user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
            (next_number("pos_orders", "number", "RCPT", s["branch_id"], now()[:10]), session_id,
             partner_id or reg["partner_id"], now(), untaxed, tax, total, cash_due, card, tendered,
             tendered - cash_due, user_id))
    for ln in lines:
        x("INSERT INTO pos_order_lines (order_id, product_id, description, quantity, price_unit, discount, tax_id, "
          "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)",
          (oid, ln["product_id"], ln["description"], ln["quantity"], ln["price_unit"], ln["discount"], ln["tax_id"],
           ln["subtotal"], ln["tax_amount"]))
    return oid


def refund_order(order_id, user_id=None):
    o = q("SELECT * FROM pos_orders WHERE id = ?", (order_id,), one=True)
    s = q("SELECT * FROM pos_sessions WHERE id = ?", (o["session_id"],), one=True)
    if s["state"] != "open":
        raise AccountingError(t("Refunds are only possible while the session is open."))
    if o["refund_of"] or q("SELECT 1 FROM pos_orders WHERE refund_of = ?", (order_id,)):
        raise AccountingError(t("This receipt is already refunded."))
    rid = x("INSERT INTO pos_orders (number, session_id, partner_id, ts, amount_untaxed, amount_tax, amount_total, "
            "paid_cash, paid_card, tendered, change_given, refund_of, user_id) VALUES (?,?,?,?,?,?,?,?,?,?,0,?,?)",
            (next_number("pos_orders", "number", "RCPT", s["branch_id"], now()[:10]), s["id"], o["partner_id"], now(),
             -o["amount_untaxed"], -o["amount_tax"], -o["amount_total"], -o["paid_cash"], -o["paid_card"],
             -o["paid_cash"], order_id, user_id))
    for ln in q("SELECT * FROM pos_order_lines WHERE order_id = ?", (order_id,)):
        x("INSERT INTO pos_order_lines (order_id, product_id, description, quantity, price_unit, discount, tax_id, "
          "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)",
          (rid, ln["product_id"], ln["description"], -ln["quantity"], ln["price_unit"], ln["discount"], ln["tax_id"],
           -ln["subtotal"], -ln["tax_amount"]))
    audit(user_id, "refund", "pos_order", order_id, o["number"])
    return rid


def session_summary(session_id):
    s = q("SELECT * FROM pos_sessions WHERE id = ?", (session_id,), one=True)
    r = q("SELECT COUNT(*) AS n, COALESCE(SUM(amount_untaxed), 0) AS untaxed, COALESCE(SUM(amount_tax), 0) AS tax, "
          "COALESCE(SUM(amount_total), 0) AS total, COALESCE(SUM(paid_cash), 0) AS cash, "
          "COALESCE(SUM(paid_card), 0) AS card, COALESCE(SUM(CASE WHEN refund_of IS NOT NULL THEN amount_total END), 0) "
          "AS refunds FROM pos_orders WHERE session_id = ?", (session_id,), one=True)
    out = dict(r)
    out["expected_cash"] = s["opening_cash"] + r["cash"]
    return out


def _side(amount):
    return {"debit": amount, "credit": 0} if amount >= 0 else {"debit": 0, "credit": -amount}


def close_session(session_id, counted_cash, user_id=None):
    s = q("SELECT * FROM pos_sessions WHERE id = ?", (session_id,), one=True)
    if s["state"] != "open":
        raise AccountingError(t("The session is closed."))
    reg = register(s["register_id"])
    today = now()[:10]
    check_lock(today)
    summ = session_summary(session_id)
    cash_j = q("SELECT * FROM journals WHERE id = ?", (reg["cash_journal_id"],), one=True)
    card_j = q("SELECT * FROM journals WHERE id = ?", (reg["card_journal_id"],), one=True) if reg["card_journal_id"] else None
    label = f"{t('POS session')} {s['number']}"
    lines = []
    if summ["cash"]:
        lines.append({"account_id": cash_j["default_account_id"], "label": label, **_side(summ["cash"])})
    if summ["card"]:
        if not card_j:
            raise AccountingError(t("The register has no card journal."))
        lines.append({"account_id": card_j["default_account_id"], "label": label + " " + t("Card"), **_side(summ["card"])})
    income = defaultdict(int)
    taxes = defaultdict(int)
    for ln in q("SELECT l.*, p.income_account_id FROM pos_order_lines l JOIN pos_orders o ON o.id = l.order_id "
                "JOIN products p ON p.id = l.product_id WHERE o.session_id = ?", (session_id,)):
        acc = ln["income_account_id"] or q("SELECT id FROM accounts WHERE code = '4100'", one=True)["id"]
        income[acc] += ln["subtotal"]
        if ln["tax_id"]:
            taxes[ln["tax_id"]] += ln["tax_amount"]
    for acc, amt in income.items():
        if amt:
            lines.append({"account_id": acc, "label": label, "tax_id": None, **_side(-amt)})
    for tax_id, amt in taxes.items():
        if amt:
            tax = q("SELECT * FROM taxes WHERE id = ?", (tax_id,), one=True)
            lines.append({"account_id": tax["account_id"], "label": tax["name_en"], "tax_id": tax_id, **_side(-amt)})
    diff = (counted_cash if counted_cash is not None else summ["expected_cash"]) - summ["expected_cash"]
    if diff:
        over_short = q("SELECT id FROM accounts WHERE code = '6950'", one=True)
        if not over_short:
            raise AccountingError(t("Missing account") + " 6950")
        lines.append({"account_id": cash_j["default_account_id"], "label": t("Cash difference"), **_side(diff)})
        lines.append({"account_id": over_short["id"], "label": t("Cash difference"), **_side(-diff)})
    move_id = None
    if lines:
        sale_j = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'sale' AND active = 1 ORDER BY id LIMIT 1",
                   (s["branch_id"],), one=True)
        move_id = create_move(s["branch_id"], (sale_j or cash_j)["id"], today, lines, user_id=user_id, ref=s["number"],
                              move_type="pos", partner_id=reg["partner_id"], post=True)
    picking_id = _post_stock(s, reg, today, user_id)
    x("UPDATE pos_sessions SET state = 'closed', closed_at = ?, counted_cash = ?, move_id = ?, picking_id = ? WHERE id = ?",
      (now(), counted_cash if counted_cash is not None else summ["expected_cash"], move_id, picking_id, session_id))
    audit(user_id, "close", "pos_session", session_id, f"diff {diff}")
    return move_id


def _post_stock(s, reg, d, user_id):
    if not reg["warehouse_id"]:
        return None
    from flask import has_app_context
    if has_app_context():
        from . import has
        if not has("inventory"):
            return None
    net = {r["product_id"]: r["qty"] for r in q(
        "SELECT l.product_id, SUM(l.quantity) AS qty FROM pos_order_lines l JOIN pos_orders o ON o.id = l.order_id "
        "JOIN products p ON p.id = l.product_id WHERE o.session_id = ? AND p.type = 'stock' GROUP BY l.product_id",
        (s["id"],)) if abs(r["qty"]) > EPS}
    if not net:
        return None
    pid = x("INSERT INTO pickings (number, kind, branch_id, warehouse_id, partner_id, date, state, notes, created_by, "
            "created_at) VALUES (?, 'pos', ?, ?, ?, ?, 'done', ?, ?, ?)",
            (next_number("pickings", "number", PICKING_PREFIX["pos"], s["branch_id"], d), s["branch_id"],
             reg["warehouse_id"], reg["partner_id"], d, f"{t('POS session')} {s['number']}", user_id, now()))
    entry = defaultdict(int)  # account -> debit-positive amount
    for product_id, qty in net.items():
        prod = q("SELECT * FROM products WHERE id = ?", (product_id,), one=True)
        cogs = cogs_account(prod)
        inv_acc = inventory_account(prod)
        if qty > 0:
            value = _out_value(product_id, reg["warehouse_id"], qty)
            dq, dv = -qty, -value
        else:
            value = int(round(-qty * avg_cost(product_id, reg["warehouse_id"])))
            dq, dv = -qty, value
        _bump(product_id, reg["warehouse_id"], dq, dv)
        entry[cogs] -= dv
        entry[inv_acc] += dv
        x("INSERT INTO picking_lines (picking_id, product_id, quantity, qty_signed, value_signed) VALUES (?,?,?,?,?)",
          (pid, product_id, abs(qty), dq, dv))
    lines = [{"account_id": a, "label": f"{t('POS session')} {s['number']}", **_side(v)} for a, v in entry.items() if v]
    if lines:
        mid = create_move(s["branch_id"], stock_journal(s["branch_id"]), d, lines, user_id=user_id, ref=s["number"],
                          move_type="stock", post=True)
        x("UPDATE pickings SET move_id = ? WHERE id = ?", (mid, pid))
    from . import lots
    lots.record(pid)
    return pid


def pos_tax_totals(branch_id, date_from, date_to):
    """{tax_id: [base, tax]} of POS sales in closed sessions, for the VAT report."""
    out = defaultdict(lambda: [0, 0])
    for r in q("SELECT l.tax_id, SUM(l.subtotal) AS base, SUM(l.tax_amount) AS tax FROM pos_order_lines l "
               "JOIN pos_orders o ON o.id = l.order_id JOIN pos_sessions s ON s.id = o.session_id "
               "WHERE s.state = 'closed' AND s.branch_id = ? AND substr(s.closed_at, 1, 10) BETWEEN ? AND ? "
               "GROUP BY l.tax_id", (branch_id, date_from, date_to)):
        out[r["tax_id"]][0] += r["base"]
        out[r["tax_id"]][1] += r["tax"]
    return out
