"""What vendors do on the portal, and the staff side of it.

Vendors only ever see their own records: RFQs they were invited to (and only their own quotation, also on sealed
RFQs), their purchase orders, their invoices and payments, their documents. Everything a vendor sends comes in as a
request that staff accept:
  - quotations go straight onto the RFQ (as when the buyer enters them), until the closing time;
  - PO acceptance / rejection is recorded on the PO;
  - invoices land in the AP inbox; accepting one creates the draft vendor bill (from the PO when there is one);
  - renewed documents wait for approval before they replace the vendor's documents;
  - bank detail changes go through the approval workflow ("Bank Details Change", finance) and only then reach the
    vendor record that payments use.
"""
import os
import shutil
import uuid
from datetime import date

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


def notify_role(role_name, title, body="", url=None):
    from .workflow import notify
    for r in q("SELECT ru.user_id FROM approval_role_users ru JOIN approval_roles ro ON ro.id = ru.role_id "
               "WHERE ro.name_en = ? AND ro.active = 1", (role_name,)):
        notify(r["user_id"], title, body, url)


def staff_url(endpoint, **kw):
    from .portal import public_url
    return public_url(endpoint, **kw)


def store(file, prefix):
    from .portal import folder, store_file
    stored, name = store_file(file)
    os.rename(os.path.join(folder(), stored), os.path.join(folder(), f"{prefix}-{stored}"))
    return f"{prefix}-{stored}", name


# ---------------------------------------------------------------- RFQs

def vendor_rfqs(partner_id):
    from .procurement import closed_for_quotes
    rows = q("SELECT r.*, rv.id AS rv_id, rv.state AS rv_state, rv.received_at FROM rfqs r JOIN rfq_vendors rv "
             "ON rv.rfq_id = r.id WHERE rv.partner_id = ? AND r.state != 'draft' ORDER BY r.id DESC", (partner_id,))
    out = []
    for r in rows:
        d = dict(r)
        d["closed"] = closed_for_quotes(r)
        d["result"] = result(r, r["rv_state"])
        out.append(d)
    return out


def result(rfq, rv_state):
    if rv_state == "awarded":
        return "awarded"
    if rfq["state"] == "awarded":
        return "not_awarded"
    if rfq["state"] == "cancelled":
        return "cancelled"
    return None


def rfq_for_vendor(rfq_id, partner_id):
    r = q("SELECT r.* FROM rfqs r JOIN rfq_vendors rv ON rv.rfq_id = r.id WHERE r.id = ? AND rv.partner_id = ? "
          "AND r.state != 'draft'", (rfq_id, partner_id), one=True)
    if not r:
        return None, None
    rv = q("SELECT * FROM rfq_vendors WHERE rfq_id = ? AND partner_id = ?", (rfq_id, partner_id), one=True)
    return r, rv


def submit_bid(rfq_id, partner_id, portal_user_id, prices, taxes, offered, alternatives, header, declined=False):
    from .procurement import save_quote
    r, rv = rfq_for_vendor(rfq_id, partner_id)
    if not r:
        raise AccountingError(t("Unknown RFQ."))
    if not declined and not any(v is not None for v in prices.values()):
        raise AccountingError(t("Enter at least one price."))
    save_quote(rv["id"], prices, taxes, header.get("quote_ref"), date.today().isoformat(), header.get("lead_days"),
               header.get("notes"), declined=declined, user_id=None, offered=offered, alternatives=alternatives,
               valid_until=header.get("valid_until"), payment_terms=header.get("payment_terms"))
    x("UPDATE rfq_vendors SET submitted_via = 'portal' WHERE id = ?", (rv["id"],))
    vendor = q("SELECT name_en FROM partners WHERE id = ?", (partner_id,), one=True)["name_en"]
    if r["created_by"]:
        from .workflow import notify
        notify(r["created_by"], f"{t('Quotation received')}: {r['number']} — {vendor}" if not declined else
               f"{t('Vendor declined')}: {r['number']} — {vendor}", "",
               staff_url("procurement.rfq_view", rfq_id=rfq_id), email=False)
    audit(None, "portal_bid" if not declined else "portal_decline", "rfq", rfq_id, f"{vendor} (portal user {portal_user_id})")


# ---------------------------------------------------------------- purchase orders

def vendor_orders(partner_id):
    return q("SELECT * FROM orders WHERE partner_id = ? AND kind = 'purchase' AND state IN ('confirmed', 'cancelled') "
             "AND (sent_at IS NOT NULL OR ack_state IS NOT NULL) ORDER BY date DESC, id DESC", (partner_id,))


def order_for_vendor(order_id, partner_id):
    return q("SELECT * FROM orders WHERE id = ? AND partner_id = ? AND kind = 'purchase' AND state IN "
             "('confirmed', 'cancelled') AND (sent_at IS NOT NULL OR ack_state IS NOT NULL)",
             (order_id, partner_id), one=True)


def respond_po(order_id, partner_id, state, ref, promised, note):
    from .procurement import acknowledge_po
    o = order_for_vendor(order_id, partner_id)
    if not o or o["state"] != "confirmed":
        raise AccountingError(t("This action is not allowed in the current state."))
    acknowledge_po(order_id, state, ref, promised, note, None)
    if o["created_by"]:
        from .workflow import notify
        notify(o["created_by"], f"{o['number']}: {t('Accepted by vendor') if state == 'accepted' else t('Rejected by vendor')}",
               note or "", staff_url("orders.view", order_id=order_id), email=state == "rejected")


# ---------------------------------------------------------------- invoices (AP inbox)

def submit_invoice(partner_id, portal_user_id, vals, file):
    if not vals.get("invoice_no"):
        raise AccountingError(t("Enter your invoice number."))
    if not vals.get("invoice_date"):
        raise AccountingError(t("Enter the invoice date."))
    if vals["amount_total"] <= 0:
        raise AccountingError(t("The document total must be greater than zero."))
    if q("SELECT 1 FROM vendor_invoices WHERE partner_id = ? AND lower(invoice_no) = lower(?) AND state != 'rejected'",
         (partner_id, vals["invoice_no"]), one=True) or \
            q("SELECT 1 FROM invoices WHERE partner_id = ? AND kind = 'in_invoice' AND lower(ref) = lower(?) "
              "AND state != 'cancelled'", (partner_id, vals["invoice_no"]), one=True):
        raise AccountingError(t("This invoice number was already sent."))
    if vals.get("order_id") and not order_for_vendor(vals["order_id"], partner_id):
        raise AccountingError(t("Choose one of your purchase orders."))
    if not file or not file.filename:
        raise AccountingError(t("Attach the invoice (PDF)."))
    stored, name = store(file, "inv")
    vid = x("INSERT INTO vendor_invoices (partner_id, portal_user_id, order_id, invoice_no, invoice_date, amount_untaxed, "
            "amount_tax, amount_total, currency, notes, filename, original_name, received_at) "
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
            (partner_id, portal_user_id, vals.get("order_id"), vals["invoice_no"], vals["invoice_date"],
             vals["amount_untaxed"], vals["amount_tax"], vals["amount_total"], vals.get("currency"), vals.get("notes"),
             stored, name, now()))
    vendor = q("SELECT name_en FROM partners WHERE id = ?", (partner_id,), one=True)["name_en"]
    notify_role("Finance Manager", f"{t('Vendor invoice received')}: {vendor} {vals['invoice_no']}", "",
                staff_url("vendors.ap_view", vid=vid))
    audit(None, "portal_invoice", "vendor", partner_id, vals["invoice_no"])
    return vid


def accept_invoice(vid, user_id, branch_id=None):
    """Create the draft vendor bill (from the PO when the invoice names one) and attach the vendor's file."""
    from .attachments import root
    from .ledger import compute_invoice_line, effective_tax
    from .portal import folder
    from .stock import create_order_invoice
    vi = q("SELECT * FROM vendor_invoices WHERE id = ?", (vid,), one=True)
    if vi["state"] != "received":
        raise AccountingError(t("This action is not allowed in the current state."))
    if vi["order_id"]:
        inv_id = create_order_invoice(vi["order_id"], vi["invoice_date"], user_id)
    else:
        if not branch_id:
            raise AccountingError(t("Choose the branch."))
        j = q("SELECT * FROM journals WHERE branch_id = ? AND type = 'purchase' AND active = 1 ORDER BY id LIMIT 1",
              (branch_id,), one=True)
        if not j or not j["default_account_id"]:
            raise AccountingError(t("The branch has no sales/purchase journal."))
        tax_id, rate = None, 0
        if vi["amount_tax"]:
            std = q("SELECT id FROM taxes WHERE scope = 'purchase' AND rate > 0 AND active = 1 ORDER BY id LIMIT 1", one=True)
            tax_id = effective_tax(branch_id, std["id"], "purchase") if std else None
            rate = q("SELECT rate FROM taxes WHERE id = ?", (tax_id,), one=True)["rate"] if tax_id else 0
        sub, tax = compute_invoice_line(1, vi["amount_untaxed"], rate, 2)
        inv_id = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, state, amount_untaxed, "
                   "amount_tax, amount_total, created_by, created_at) VALUES (?,?,'in_invoice',?,?,date(?, '+30 days'),"
                   "'draft',?,?,?,?,?)", (branch_id, j["id"], vi["partner_id"], vi["invoice_date"], vi["invoice_date"],
                                          sub, tax, sub + tax, user_id, now()))
        x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, tax_id, "
          "subtotal, tax_amount) VALUES (?,0,?,?,1,?,?,?,?)",
          (inv_id, f"{t('Vendor invoice')} {vi['invoice_no']}", j["default_account_id"], vi["amount_untaxed"], tax_id,
           sub, tax))
    x("UPDATE invoices SET ref = ?, date = ? WHERE id = ?", (vi["invoice_no"], vi["invoice_date"], inv_id))
    if vi["filename"]:  # the vendor's PDF becomes an attachment of the bill
        sub_dir = date.today().strftime("%Y/%m")
        os.makedirs(os.path.join(root(), sub_dir), exist_ok=True)
        stored = f"{sub_dir}/{uuid.uuid4().hex}{os.path.splitext(vi['filename'])[1]}"
        shutil.copyfile(os.path.join(folder(), vi["filename"]), os.path.join(root(), stored))
        x("INSERT INTO attachments (model, record_id, filename, stored, content_type, size, uploaded_by, uploaded_at) "
          "VALUES ('invoice', ?, ?, ?, ?, ?, ?, ?)",
          (inv_id, vi["original_name"], stored, "application/pdf" if stored.endswith(".pdf") else "image/jpeg",
           os.path.getsize(os.path.join(root(), stored)), user_id, now()))
    x("UPDATE vendor_invoices SET state = 'accepted', invoice_id = ?, processed_by = ?, processed_at = ? WHERE id = ?",
      (inv_id, user_id, now(), vid))
    audit(user_id, "accept", "vendor_invoice", vid, vi["invoice_no"])
    return inv_id


def reject_invoice(vid, reason, user_id):
    vi = q("SELECT * FROM vendor_invoices WHERE id = ?", (vid,), one=True)
    if vi["state"] != "received":
        raise AccountingError(t("This action is not allowed in the current state."))
    if not (reason or "").strip():
        raise AccountingError(t("Give the reason."))
    x("UPDATE vendor_invoices SET state = 'rejected', reject_reason = ?, processed_by = ?, processed_at = ? WHERE id = ?",
      (reason.strip(), user_id, now(), vid))
    mail_vendor(vi["partner_id"], f"{t('Invoice returned')}: {vi['invoice_no']}", reason.strip())
    audit(user_id, "reject", "vendor_invoice", vid, reason)


def invoice_status(vi):
    """What the vendor sees for an invoice it sent."""
    if vi["state"] == "rejected":
        return "returned"
    if vi["state"] == "received":
        return "received"
    inv = q("SELECT * FROM invoices WHERE id = ?", (vi["invoice_id"],), one=True)
    if not inv or inv["state"] == "draft":
        return "in_process"
    if inv["state"] == "cancelled":
        return "returned"
    from .ledger import invoice_residuals
    res = invoice_residuals([inv["id"]]).get(inv["id"], 0)
    return "paid" if res <= 0 else ("partly_paid" if res < inv["amount_total"] else "approved")


def statement(partner_id):
    """Posted bills with what is still owed, and payments made."""
    from .ledger import invoice_residuals
    bills = [dict(b) for b in q("SELECT i.*, b.currency AS bcur FROM invoices i JOIN branches b ON b.id = i.branch_id "
                                "WHERE i.partner_id = ? AND i.kind IN ('in_invoice', 'in_refund') AND i.state = 'posted' "
                                "ORDER BY i.date DESC, i.id DESC", (partner_id,))]
    res = invoice_residuals([b["id"] for b in bills])
    for b in bills:
        b["residual"] = res.get(b["id"], 0)
    pays = q("SELECT p.*, b.currency AS bcur FROM payments p JOIN branches b ON b.id = p.branch_id WHERE p.partner_id = ? "
             "AND p.partner_type = 'vendor' AND p.state = 'posted' ORDER BY p.date DESC, p.id DESC LIMIT 200", (partner_id,))
    return bills, pays


def mail_vendor(partner_id, subject, body):
    from . import mailer
    users = [u["email"] for u in q("SELECT email FROM portal_users WHERE partner_id = ? AND active = 1", (partner_id,))]
    p = q("SELECT email FROM partners WHERE id = ?", (partner_id,), one=True)
    to = ", ".join(dict.fromkeys(users or ([p["email"]] if p and p["email"] else [])))
    if not to or not mailer.configured():
        return
    try:
        mailer.send(to, subject, body, kind="supplier", partner_id=partner_id)
    except AccountingError:
        pass


# ---------------------------------------------------------------- document renewals

def submit_doc(partner_id, portal_user_id, doc_type, number, expiry, file):
    from .portal import DOC_TYPES
    if not file or not file.filename:
        raise AccountingError(t("Attach the document."))
    stored, name = store(file, "doc")
    did = x("INSERT INTO vendor_doc_updates (partner_id, portal_user_id, doc_type, number, expiry_date, filename, "
            "original_name, submitted_at) VALUES (?,?,?,?,?,?,?,?)",
            (partner_id, portal_user_id, doc_type if doc_type in DOC_TYPES else "Other", number or None, expiry or None,
             stored, name, now()))
    vendor = q("SELECT name_en FROM partners WHERE id = ?", (partner_id,), one=True)["name_en"]
    notify_role("Procurement Manager", f"{t('Document sent by a vendor')}: {vendor} — {t(doc_type)}", "",
                staff_url("vendors.view", pid=partner_id) + "#updates")
    return did


def decide_doc(did, approve, user_id, reason=None):
    d = q("SELECT * FROM vendor_doc_updates WHERE id = ?", (did,), one=True)
    if d["state"] != "pending":
        raise AccountingError(t("This action is not allowed in the current state."))
    if approve:
        x("INSERT INTO vendor_documents (partner_id, doc_type, number, expiry_date, filename, original_name, uploaded_at, "
          "notes) VALUES (?,?,?,?,?,?,?,?)", (d["partner_id"], d["doc_type"], d["number"], d["expiry_date"], d["filename"],
                                             d["original_name"], d["submitted_at"], t("Sent through the supplier portal")))
    elif not (reason or "").strip():
        raise AccountingError(t("Give the reason."))
    x("UPDATE vendor_doc_updates SET state = ?, reason = ?, decided_by = ?, decided_at = ? WHERE id = ?",
      ("approved" if approve else "rejected", reason, user_id, now(), did))
    if not approve:
        mail_vendor(d["partner_id"], f"{t('Document not accepted')}: {t(d['doc_type'])}", reason)
    audit(user_id, "doc_approve" if approve else "doc_reject", "vendor", d["partner_id"], d["doc_type"])


# ---------------------------------------------------------------- bank detail changes (finance approval)

def pending_bank_change(partner_id):
    return q("SELECT * FROM bank_changes WHERE partner_id = ? AND state = 'pending' ORDER BY id DESC LIMIT 1",
             (partner_id,), one=True)


def request_bank_change(partner_id, vals, file=None, portal_user_id=None, user_id=None):
    """New bank details wait for approval; payments keep using the current ones until then."""
    import re
    from . import workflow
    from .portal import iban_ok
    p = q("SELECT * FROM partners WHERE id = ?", (partner_id,), one=True)
    iban = re.sub(r"\s+", "", vals.get("iban") or "").upper()
    if not iban_ok(iban):
        raise AccountingError(t("The IBAN is not valid."))
    if not (vals.get("bank_name") or "").strip() or not (vals.get("account_name") or "").strip():
        raise AccountingError(t("Enter the bank name and the account name."))
    if portal_user_id and (not file or not file.filename):
        raise AccountingError(t("Attach a stamped bank letter for the new account."))
    if iban == (p["iban"] or "") and vals.get("bank_name") == p["bank_name"] and \
            vals.get("account_name") == p["account_name"] and (vals.get("swift") or None) == p["swift"]:
        raise AccountingError(t("These are the current bank details."))
    if pending_bank_change(partner_id):
        raise AccountingError(t("A bank details change is already waiting for approval."))
    stored = name = None
    if file and file.filename:
        stored, name = store(file, "bank")
    cid = x("INSERT INTO bank_changes (partner_id, portal_user_id, requested_by, old_bank_name, old_iban, old_account_name, "
            "old_swift, bank_name, iban, account_name, swift, filename, original_name, created_at) "
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            (partner_id, portal_user_id, user_id, p["bank_name"], p["iban"], p["account_name"], p["swift"],
             vals["bank_name"].strip(), iban, vals["account_name"].strip(), (vals.get("swift") or "").strip() or None,
             stored, name, now()))
    rid = workflow.submit("bank_change", cid, 0, f"{p['name_en']} — IBAN …{iban[-4:]}", user_id)
    if rid is None:  # no rule: finance approvers are told; an approver or administrator decides
        notify_role("Finance Manager", f"{t('Bank details change')}: {p['name_en']}", "",
                    staff_url("vendors.bank_change", cid=cid))
    audit(user_id, "bank_change_request", "vendor", partner_id, f"IBAN …{iban[-4:]}")
    return cid


def _bank_approved(cid, user_id):
    c = q("SELECT * FROM bank_changes WHERE id = ?", (cid,), one=True)
    if c["state"] != "pending":
        return
    x("UPDATE partners SET bank_name = ?, iban = ?, account_name = ?, swift = ? WHERE id = ?",
      (c["bank_name"], c["iban"], c["account_name"], c["swift"], c["partner_id"]))
    if c["filename"]:
        x("INSERT INTO vendor_documents (partner_id, doc_type, filename, original_name, uploaded_at, notes) "
          "VALUES (?, 'Bank Letter', ?, ?, ?, ?)", (c["partner_id"], c["filename"], c["original_name"], c["created_at"],
                                                    t("New bank account")))
    x("UPDATE bank_changes SET state = 'approved', decided_by = ?, decided_at = ? WHERE id = ?", (user_id, now(), cid))
    audit(user_id, "bank_change_approved", "vendor", c["partner_id"],
          f"{c['old_iban'] or '—'} -> {c['iban']}")
    mail_vendor(c["partner_id"], t("Bank details updated"),
                f"{t('Your new bank account ending')} {c['iban'][-4:]} {t('has been approved for payments.')}")


def _bank_rejected(cid, user_id, reason):
    c = q("SELECT * FROM bank_changes WHERE id = ?", (cid,), one=True)
    x("UPDATE bank_changes SET state = 'rejected', reason = ?, decided_by = ?, decided_at = ? WHERE id = ?",
      (reason, user_id, now(), cid))
    audit(user_id, "bank_change_rejected", "vendor", c["partner_id"], reason)
    mail_vendor(c["partner_id"], t("Bank details change not approved"), reason or "")


def decide_bank_change(cid, approve, user_id, reason=None, override=False):
    from . import workflow
    c = q("SELECT * FROM bank_changes WHERE id = ?", (cid,), one=True)
    if not c or c["state"] != "pending":
        raise AccountingError(t("This action is not allowed in the current state."))
    if q("SELECT 1 FROM approval_requests WHERE doc_type = 'bank_change' AND doc_id = ? AND state = 'pending'",
         (cid,), one=True):
        workflow.act_on_doc("bank_change", cid, user_id, "approve" if approve else "reject", reason, override)
        return
    if c["requested_by"] == user_id:
        raise AccountingError(t("You cannot approve a document you submitted."))
    if not approve and not (reason or "").strip():
        raise AccountingError(t("Give the reason."))
    (_bank_approved(cid, user_id) if approve else _bank_rejected(cid, user_id, reason))


def _register():
    from . import workflow
    workflow.handler("bank_change", "approved")(_bank_approved)
    workflow.handler("bank_change", "rejected")(_bank_rejected)


_register()


def expiry_reminders(today=None):
    """Email vendors whose documents expire in 30 days, in 7 days or today (run daily). Returns how many."""
    from datetime import date, timedelta
    from . import has
    if not has("vendors"):
        return 0
    d0 = date.fromisoformat(today) if today else date.today()
    n = 0
    for days in (30, 7, 0):
        day = (d0 + timedelta(days=days)).isoformat()
        for d in q("SELECT vd.*, p.name_en FROM vendor_documents vd JOIN partners p ON p.id = vd.partner_id "
                   "WHERE vd.expiry_date = ? AND p.active = 1 AND p.vendor_status != 'blocked'", (day,)):
            newer = q("SELECT 1 FROM vendor_documents WHERE partner_id = ? AND doc_type = ? AND expiry_date > ?",
                      (d["partner_id"], d["doc_type"], day), one=True)
            if newer:
                continue
            body = (f"{t(d['doc_type'])} {d['number'] or ''} — {t('expires on')} {day}.\n"
                    f"{t('Please send the renewed document')}" +
                    (f" {t('on the supplier portal')}: {staff_url('supplier.documents')}" if has("portal") else "."))
            mail_vendor(d["partner_id"], f"{t('Document expiring')}: {t(d['doc_type'])}", body)
            n += 1
    return n
