"""Batch (lot) and expiry tracking for products marked "Batch / expiry".

Quantities and values stay on the warehouse (average cost); batches only say WHICH units are where, so a batch never
changes an accounting entry. Every done stock line of a tracked product is split over batches:

  goods coming in   the batch typed on the receipt; else, for a customer return, the batch last delivered to that
                    customer; else a batch named after the document (so every unit still traces to where it came from)
  goods going out   the batch chosen (write-offs), else earliest expiry first (FEFO), then oldest batch
  transfers         the receiving warehouse gets exactly the batches the sending warehouse gave

Stock that existed before tracking was switched on has no batch; it simply goes out unallocated.
"""
from datetime import date, timedelta

from .db import q, x
from .i18n import t
from .ledger import AccountingError, now

EPS = 1e-9


def tracked(product_id):
    r = q("SELECT tracking FROM products WHERE id = ?", (product_id,), one=True)
    return bool(r and r["tracking"] == "lot")


def get_or_create(product_id, number, expiry=None):
    number = (number or "").strip()
    if not number:
        raise AccountingError(t("Enter the batch number."))
    r = q("SELECT * FROM lots WHERE product_id = ? AND number = ?", (product_id, number), one=True)
    if r:
        if expiry and not r["expiry_date"]:
            x("UPDATE lots SET expiry_date = ? WHERE id = ?", (expiry, r["id"]))
        elif expiry and r["expiry_date"] != expiry:
            raise AccountingError(f"{t('Batch')} {number}: {t('already exists with expiry')} {r['expiry_date']}")
        return r["id"]
    return x("INSERT INTO lots (product_id, number, expiry_date, created_at) VALUES (?,?,?,?)",
             (product_id, number, expiry or None, now()))


def _bump(lot_id, wid, qty):
    x("INSERT INTO lot_quants (lot_id, warehouse_id, qty) VALUES (?,?,?) "
      "ON CONFLICT (lot_id, warehouse_id) DO UPDATE SET qty = qty + excluded.qty", (lot_id, wid, qty))


def _book(line_id, wid, lot_id, qty):
    x("INSERT INTO picking_line_lots (picking_line_id, lot_id, qty) VALUES (?,?,?)", (line_id, lot_id, qty))
    _bump(lot_id, wid, qty)


def available(product_id, wid):
    """Batches with stock in a warehouse, earliest expiry first (FEFO)."""
    return q("SELECT l.*, lq.qty FROM lot_quants lq JOIN lots l ON l.id = lq.lot_id WHERE l.product_id = ? "
             "AND lq.warehouse_id = ? AND lq.qty > 0.000001 "
             "ORDER BY l.expiry_date IS NULL, l.expiry_date, l.id", (product_id, wid))


def _take(line_id, product_id, wid, qty, lot_id=None):
    """Book qty going out; a chosen batch must hold enough, otherwise FEFO."""
    if lot_id:
        r = q("SELECT l.number, COALESCE(lq.qty, 0) AS qty FROM lots l LEFT JOIN lot_quants lq ON lq.lot_id = l.id "
              "AND lq.warehouse_id = ? WHERE l.id = ? AND l.product_id = ?", (wid, lot_id, product_id), one=True)
        if not r:
            raise AccountingError(t("The batch does not belong to this product."))
        if r["qty"] < qty - EPS:
            raise AccountingError(f"{t('Batch')} {r['number']}: {t('available')} {r['qty']:g}, {t('needed')} {qty:g}")
        _book(line_id, wid, lot_id, -qty)
        return
    for b in available(product_id, wid):
        if qty <= EPS:
            break
        n = min(qty, b["qty"])
        _book(line_id, wid, b["id"], -n)
        qty -= n


def _incoming_lot(picking, line):
    if picking["kind"] == "return_in" and picking["partner_id"]:
        r = q("SELECT pll.lot_id FROM picking_line_lots pll JOIN picking_lines pl ON pl.id = pll.picking_line_id "
              "JOIN pickings pk ON pk.id = pl.picking_id WHERE pk.kind = 'out' AND pk.state = 'done' "
              "AND pk.partner_id = ? AND pl.product_id = ? ORDER BY pk.date DESC, pll.id DESC LIMIT 1",
              (picking["partner_id"], line["product_id"]), one=True)
        if r:
            return r["lot_id"]
    shelf = q("SELECT shelf_life_days FROM products WHERE id = ?", (line["product_id"],), one=True)["shelf_life_days"]
    expiry = (date.fromisoformat(picking["date"]) + timedelta(days=int(shelf))).isoformat() if shelf else None
    return get_or_create(line["product_id"], picking["number"], expiry)


def record(picking_id, given=None, mirror=None):
    """Split the done lines of a picking over batches.
    given: {picking_line_id: (batch number, expiry) for goods in, or lot_id for goods out}.
    mirror: id of a done picking whose batches this one repeats line by line with the opposite sign (transfers)."""
    given = given or {}
    pk = q("SELECT * FROM pickings WHERE id = ?", (picking_id,), one=True)
    lines = q("SELECT pl.*, p.tracking FROM picking_lines pl JOIN products p ON p.id = pl.product_id "
              "WHERE pl.picking_id = ? ORDER BY pl.id", (picking_id,))
    source = q("SELECT * FROM picking_lines WHERE picking_id = ? ORDER BY id", (mirror,)) if mirror else []
    for i, ln in enumerate(lines):
        if ln["tracking"] != "lot" or abs(ln["qty_signed"]) < EPS:
            continue
        qty = ln["qty_signed"]
        if mirror and i < len(source):
            # units that left without a batch arrive without one too
            for b in q("SELECT lot_id, qty FROM picking_line_lots WHERE picking_line_id = ?", (source[i]["id"],)):
                _book(ln["id"], pk["warehouse_id"], b["lot_id"], -b["qty"])
            continue
        if qty > 0:
            g = given.get(ln["id"])
            lot = get_or_create(ln["product_id"], g[0], g[1]) if g and g[0] else _incoming_lot(pk, ln)
            _book(ln["id"], pk["warehouse_id"], lot, qty)
        else:
            _take(ln["id"], ln["product_id"], pk["warehouse_id"], -qty, given.get(ln["id"]))


def line_lots(line_ids):
    """{picking_line_id: [lots]} for showing batches on documents."""
    if not line_ids:
        return {}
    out = {}
    for r in q(f"SELECT pll.picking_line_id, pll.qty, l.id, l.number, l.expiry_date FROM picking_line_lots pll "
               f"JOIN lots l ON l.id = pll.lot_id WHERE pll.picking_line_id IN ({','.join('?' * len(line_ids))}) "
               f"ORDER BY pll.id", list(line_ids)):
        out.setdefault(r["picking_line_id"], []).append(r)
    return out


def expiry_status(expiry, today=None):
    if not expiry:
        return None
    today = today or date.today().isoformat()
    days = (date.fromisoformat(expiry) - date.fromisoformat(today)).days
    return days
