import os
import uuid
from datetime import date, timedelta

from flask import Blueprint, abort, current_app, flash, redirect, render_template, request, send_from_directory, url_for
from werkzeug.utils import secure_filename

from .. import admin_required, allowed_branch_ids, can_approve, login_required, module_required, write_required
from ..db import commit, placeholders, q, x
from ..i18n import t
from ..ledger import AccountingError, audit, invoice_residuals, now
from .common import branch_map, user_id
from ..vendorportal import accept_invoice, decide_bank_change, decide_doc, pending_bank_change, reject_invoice, \
    request_bank_change

bp = Blueprint("vendors", __name__, url_prefix="/vendors")
bp.before_request(module_required("vendors"))

DOC_TYPES = ["Commercial Registration", "VAT Certificate", "Bank Letter", "Chamber of Commerce", "GOSI Certificate",
             "Zakat Certificate", "Trade License", "ISO Certificate", "Contract", "Insurance", "Other"]
STATUSES = ["prospect", "approved", "blocked"]
CRITERIA = ["quality", "delivery", "price", "service"]
ALLOWED_EXT = {".pdf", ".png", ".jpg", ".jpeg", ".doc", ".docx", ".xls", ".xlsx"}


def upload_dir():
    path = os.path.join(os.path.dirname(current_app.config["DATABASE"]), "uploads", "vendors")
    os.makedirs(path, exist_ok=True)
    return path


def vendor_or_404(pid):
    v = q("SELECT * FROM partners WHERE id = ? AND is_vendor = 1", (pid,), one=True)
    if not v:
        abort(404)
    return v


@bp.route("/")
@login_required
def index():
    status = request.args.get("status", "")
    search = request.args.get("q", "").strip()
    sql = ("SELECT p.*, (SELECT AVG((quality + delivery + price + service) / 4.0) FROM vendor_evaluations e "
           "WHERE e.partner_id = p.id) AS score, (SELECT COUNT(*) FROM vendor_documents d WHERE d.partner_id = p.id "
           "AND d.expiry_date IS NOT NULL AND d.expiry_date <= ?) AS expiring FROM partners p WHERE p.is_vendor = 1")
    args = [(date.today() + timedelta(days=30)).isoformat()]
    if status in STATUSES:
        sql += " AND p.vendor_status = ?"
        args.append(status)
    if search:
        sql += " AND (p.name_en LIKE ? OR p.name_ar LIKE ? OR p.vat_no LIKE ? OR p.vendor_category LIKE ?)"
        args += [f"%{search}%"] * 4
    rows = q(sql + " ORDER BY p.name_en", args)
    return render_template("vendors/list.html", rows=rows, status=status, search=search, statuses=STATUSES)


def performance(pid):
    ids = allowed_branch_ids()
    if not ids:
        return {}
    ph = placeholders(ids)
    since = (date.today() - timedelta(days=365)).isoformat()
    po = q(f"SELECT COUNT(*) AS n, COALESCE(SUM(amount_total), 0) AS total FROM orders WHERE kind = 'purchase' "
           f"AND state = 'confirmed' AND partner_id = ? AND date >= ? AND branch_id IN ({ph})", [pid, since] + ids, one=True)
    rec = q(f"SELECT COUNT(*) AS n, SUM(CASE WHEN pk.date <= COALESCE(o.expected_date, o.date) THEN 1 ELSE 0 END) AS ontime "
            f"FROM pickings pk JOIN orders o ON o.id = pk.order_id WHERE pk.kind = 'in' AND pk.state = 'done' "
            f"AND o.partner_id = ? AND pk.branch_id IN ({ph})", [pid] + ids, one=True)
    bills = q(f"SELECT id FROM invoices WHERE partner_id = ? AND kind = 'in_invoice' AND state = 'posted' "
              f"AND branch_id IN ({ph})", [pid] + ids)
    res = invoice_residuals([b["id"] for b in bills], base=True)
    rfq = q("SELECT COUNT(*) AS invited, SUM(CASE WHEN state IN ('quoted', 'awarded') THEN 1 ELSE 0 END) AS quoted, "
            "SUM(CASE WHEN state = 'awarded' THEN 1 ELSE 0 END) AS awarded FROM rfq_vendors WHERE partner_id = ?",
            (pid,), one=True)
    return {"po_count": po["n"], "po_total": po["total"], "receipts": rec["n"],
            "ontime": (100.0 * (rec["ontime"] or 0) / rec["n"]) if rec["n"] else None,
            "open_bills": sum(1 for v in res.values() if v > 0), "due": sum(res.values()),
            "rfq_invited": rfq["invited"] or 0, "rfq_quoted": rfq["quoted"] or 0, "rfq_awarded": rfq["awarded"] or 0}


@bp.route("/<int:pid>", methods=["GET", "POST"])
@login_required
def view(pid):
    v = vendor_or_404(pid)
    if request.method == "POST":
        if not can_write():
            abort(403)
        f = request.form
        x("UPDATE partners SET vendor_category=?, payment_terms=?, contact_person=?, website=?, "
          "email=?, phone=?, address=?, vat_no=?, cr_no=? WHERE id = ?",
          (f.get("vendor_category"), int(f.get("payment_terms") or 0), f.get("contact_person"), f.get("website"),
           f.get("email"), f.get("phone"), f.get("address"), f.get("vat_no"), f.get("cr_no"), pid))
        audit(user_id(), "update", "vendor", pid, v["name_en"])
        new_bank = {"bank_name": (f.get("bank_name") or "").strip() or None, "iban": (f.get("iban") or "").replace(" ", "").upper() or None,
                    "account_name": (f.get("account_name") or "").strip() or None, "swift": (f.get("swift") or "").strip() or None}
        changed = any(new_bank[k] != v[k] for k in new_bank) and "iban" in f
        msg = t("Saved.")
        if changed:
            try:
                request_bank_change(pid, new_bank, request.files.get("bank_letter"), user_id=user_id())
                msg = t("Saved. The new bank details are waiting for finance approval; payments use the current ones until then.")
            except AccountingError as e:
                from ..db import get_db
                get_db().rollback()
                flash(str(e), "error")
                return redirect(url_for("vendors.view", pid=pid))
        commit()
        flash(msg, "success")
        return redirect(url_for("vendors.view", pid=pid))
    docs = q("SELECT * FROM vendor_documents WHERE partner_id = ? ORDER BY expiry_date IS NULL, expiry_date", (pid,))
    evals = q("SELECT e.*, u.full_name, u.username FROM vendor_evaluations e LEFT JOIN users u ON u.id = e.evaluator_id "
              "WHERE e.partner_id = ? ORDER BY e.date DESC", (pid,))
    scores = {c: (sum(e[c] for e in evals) / len(evals)) if evals else None for c in CRITERIA}
    overall = (sum(scores.values()) / len(CRITERIA)) if evals else None
    today = date.today()
    return render_template("vendors/view.html", v=v, docs=docs, evals=evals, scores=scores, overall=overall,
                           perf=performance(pid), doc_types=DOC_TYPES, criteria=CRITERIA, today=today.isoformat(),
                           soon=(today + timedelta(days=30)).isoformat(),
                           history=q("SELECT * FROM audit_log WHERE model = 'vendor' AND record_id = ? "
                                     "ORDER BY id DESC LIMIT 20", (pid,)),
                           portal_users=q("SELECT * FROM portal_users WHERE partner_id = ? ORDER BY id", (pid,)),
                           doc_updates=q("SELECT * FROM vendor_doc_updates WHERE partner_id = ? AND state = 'pending' "
                                         "ORDER BY id", (pid,)),
                           bank_pending=pending_bank_change(pid),
                           bank_history=q("SELECT * FROM bank_changes WHERE partner_id = ? ORDER BY id DESC LIMIT 5", (pid,)),
                           now_iso=now())


def can_write():
    from flask import g
    return g.user["role"] in ("admin", "accountant")


@bp.route("/<int:pid>/status", methods=["POST"])
@write_required
def set_status(pid):
    v = vendor_or_404(pid)
    status = request.form.get("status")
    if status not in STATUSES:
        abort(400)
    if status == "approved" and not can_approve():
        flash(t("Only approvers can approve or reject."), "error")
        return redirect(url_for("vendors.view", pid=pid))
    reason = request.form.get("reason", "")
    x("UPDATE partners SET vendor_status = ? WHERE id = ?", (status, pid))
    audit(user_id(), f"status:{status}", "vendor", pid, reason)
    commit()
    flash(t("Done."), "success")
    return redirect(url_for("vendors.view", pid=pid))


@bp.route("/<int:pid>/documents", methods=["POST"])
@write_required
def add_document(pid):
    vendor_or_404(pid)
    f = request.form
    stored = original = None
    file = request.files.get("file")
    if file and file.filename:
        ext = os.path.splitext(file.filename)[1].lower()
        if ext not in ALLOWED_EXT:
            flash(t("This file type is not allowed."), "error")
            return redirect(url_for("vendors.view", pid=pid))
        original = secure_filename(file.filename) or f"document{ext}"
        stored = f"{uuid.uuid4().hex}{ext}"
        file.save(os.path.join(upload_dir(), stored))
    did = x("INSERT INTO vendor_documents (partner_id, doc_type, number, issue_date, expiry_date, notes, filename, "
            "original_name, uploaded_at) VALUES (?,?,?,?,?,?,?,?,?)",
            (pid, f.get("doc_type") or "Other", f.get("number"), f.get("issue_date") or None,
             f.get("expiry_date") or None, f.get("notes"), stored, original, now()))
    audit(user_id(), "document", "vendor", pid, f.get("doc_type"))
    commit()
    flash(t("Saved."), "success")
    return redirect(url_for("vendors.view", pid=pid) + "#documents")


@bp.route("/documents/<int:doc_id>/file")
@login_required
def document_file(doc_id):
    d = q("SELECT * FROM vendor_documents WHERE id = ?", (doc_id,), one=True)
    if not d or not d["filename"]:
        abort(404)
    return send_from_directory(upload_dir(), d["filename"], as_attachment=True, download_name=d["original_name"])


@bp.route("/documents/<int:doc_id>/delete", methods=["POST"])
@write_required
def delete_document(doc_id):
    d = q("SELECT * FROM vendor_documents WHERE id = ?", (doc_id,), one=True)
    if not d:
        abort(404)
    x("DELETE FROM vendor_documents WHERE id = ?", (doc_id,))
    if d["filename"]:
        try:
            os.remove(os.path.join(upload_dir(), d["filename"]))
        except OSError:
            pass
    audit(user_id(), "delete document", "vendor", d["partner_id"], d["doc_type"])
    commit()
    return redirect(url_for("vendors.view", pid=d["partner_id"]) + "#documents")


@bp.route("/<int:pid>/evaluate", methods=["POST"])
@write_required
def evaluate(pid):
    vendor_or_404(pid)
    f = request.form
    try:
        scores = [int(f[c]) for c in CRITERIA]
    except (KeyError, ValueError):
        abort(400)
    if any(s < 1 or s > 5 for s in scores):
        abort(400)
    x("INSERT INTO vendor_evaluations (partner_id, date, period, quality, delivery, price, service, comments, "
      "evaluator_id) VALUES (?,?,?,?,?,?,?,?,?)",
      (pid, f.get("date") or date.today().isoformat(), f.get("period"), *scores, f.get("comments"), user_id()))
    audit(user_id(), "evaluate", "vendor", pid, f"{sum(scores) / 4:.1f}")
    commit()
    flash(t("Evaluation saved."), "success")
    return redirect(url_for("vendors.view", pid=pid) + "#evaluations")


@bp.route("/documents")
@login_required
def expiring():
    days = request.args.get("days", 30, type=int)
    limit = (date.today() + timedelta(days=days)).isoformat()
    rows = q("SELECT d.*, p.name_en, p.name_ar, p.vendor_status FROM vendor_documents d JOIN partners p "
             "ON p.id = d.partner_id WHERE d.expiry_date IS NOT NULL AND d.expiry_date <= ? ORDER BY d.expiry_date",
             (limit,))
    return render_template("vendors/expiring.html", rows=rows, days=days, today=date.today().isoformat())


# ---------------------------------------------------------------- supplier registrations (Supplier Portal module)

def portal_required():
    from .. import has
    if not has("portal"):
        abort(404)


@bp.route("/registrations")
@login_required
def registrations():
    portal_required()
    state = request.args.get("state", "submitted")
    sql = "SELECT r.*, (SELECT COUNT(*) FROM vendor_registration_docs d WHERE d.registration_id = r.id) AS ndocs " \
          "FROM vendor_registrations r"
    args = []
    if state in ("submitted", "info_requested", "approved", "rejected"):
        sql += " WHERE r.state = ?"
        args.append(state)
    rows = q(sql + " ORDER BY r.id DESC LIMIT 500", args)
    counts = {r["state"]: r["n"] for r in q("SELECT state, COUNT(*) AS n FROM vendor_registrations GROUP BY state")}
    from ..portal import public_url
    return render_template("vendors/registrations.html", rows=rows, state=state, counts=counts,
                           link=public_url("supplier.register"))


@bp.route("/registrations/<int:rid>", methods=["GET", "POST"])
@login_required
def registration(rid):
    portal_required()
    from flask import g
    from .. import portal
    reg = portal.get(rid)
    if not reg:
        abort(404)
    if request.method == "POST":
        if g.user["role"] not in ("admin", "accountant"):
            abort(403)
        f = request.form
        action = f.get("action")
        try:
            if action == "approve":
                if not q("SELECT 1 FROM approval_requests WHERE doc_type = 'vendor_registration' AND doc_id = ? "
                         "AND state = 'pending'", (rid,), one=True) and not can_approve():
                    raise AccountingError(t("You do not have permission for this action."))
                pid = portal.approve(rid, user_id(), bool(f.get("override")), f.get("reason") or f.get("comment"))
                commit()
                link = g.get("portal_link")
                from .. import mailer
                if pid and link and not mailer.configured():
                    flash(f"{t('Vendor created. Email is not set up: send this activation link to the supplier yourself')}: "
                          f"{link}", "success")
                elif pid:
                    flash(t("Vendor created and the activation link emailed to the supplier."), "success")
                else:
                    flash(t("Approved for this step."), "success")
            elif action == "reject":
                portal.reject(rid, f.get("reason") or f.get("comment"), user_id())
                commit()
                flash(t("Registration rejected."), "success")
            elif action == "info":
                link = portal.request_info(rid, f.get("message"), user_id())
                commit()
                from .. import mailer
                flash(t("The supplier was emailed a link to correct the registration.") if mailer.configured() else
                      f"{t('Email is not set up: send this link to the supplier yourself')}: {link}", "success")
            else:
                abort(400)
        except AccountingError as e:
            from ..db import get_db
            get_db().rollback()
            flash(str(e), "error")
        return redirect(url_for("vendors.registration", rid=rid))
    docs = q("SELECT * FROM vendor_registration_docs WHERE registration_id = ? ORDER BY id", (rid,))
    return render_template("vendors/registration.html", reg=reg, docs=docs, dups=portal.duplicates(dict(reg)),
                           missing=portal.missing_docs(rid))


@bp.route("/registrations/doc/<int:doc_id>")
@login_required
def registration_doc(doc_id):
    portal_required()
    d = q("SELECT * FROM vendor_registration_docs WHERE id = ?", (doc_id,), one=True)
    if not d:
        abort(404)
    return send_from_directory(upload_dir(), d["filename"], as_attachment=False, download_name=d["original_name"])


@bp.route("/registrations/invite", methods=["POST"])
@write_required
def invite_supplier():
    portal_required()
    from .. import mailer
    from ..ledger import company
    from ..portal import EMAIL, public_url
    email = (request.form.get("email") or "").strip()
    try:
        if not EMAIL.match(email):
            raise AccountingError(t("Enter a valid email address."))
        if not mailer.configured():
            raise AccountingError(t("Set up email in Company settings first, or copy the link and send it yourself."))
        mailer.send(email, f"{company()['name_en']} — {t('Supplier registration')}",
                    f"{t('You are invited to register as a supplier of')} {company()['name_en']}:\n"
                    f"{public_url('supplier.register')}", kind="supplier", user_id=user_id())
        audit(user_id(), "invite", "vendor_registration", None, email)
        commit()
        flash(t("Invitation sent."), "success")
    except AccountingError as e:
        flash(str(e), "error")
    return redirect(url_for("vendors.registrations"))


@bp.route("/<int:pid>/portal-user", methods=["POST"])
@write_required
def portal_user(pid):
    portal_required()
    from .. import mailer, portal
    vendor_or_404(pid)
    f = request.form
    try:
        if f.get("action") == "deactivate":
            x("UPDATE portal_users SET active = 0, activation_hash = NULL WHERE id = ? AND partner_id = ?",
              (int(f["uid"]), pid))
            audit(user_id(), "portal_deactivate", "vendor", pid, f["uid"])
            commit()
            flash(t("Portal access removed."), "success")
        else:
            email = f.get("email")
            if f.get("uid"):
                u = q("SELECT * FROM portal_users WHERE id = ? AND partner_id = ?", (int(f["uid"]), pid), one=True)
                email, name = u["email"], u["full_name"]
            else:
                name = f.get("full_name")
            link = portal.invite_user(pid, email, name, user_id())
            commit()
            flash(t("Activation link emailed.") if mailer.configured() else
                  f"{t('Email is not set up: send this activation link to the supplier yourself')}: {link}", "success")
    except AccountingError as e:
        from ..db import get_db
        get_db().rollback()
        flash(str(e), "error")
    return redirect(url_for("vendors.view", pid=pid) + "#portal")



# ---------------------------------------------------------------- AP inbox: invoices sent through the portal

@bp.route("/ap-inbox")
@login_required
def ap_inbox():
    portal_required()
    state = request.args.get("state", "received")
    sql = ("SELECT vi.*, p.name_en, p.name_ar, o.number AS po FROM vendor_invoices vi JOIN partners p ON p.id = vi.partner_id "
           "LEFT JOIN orders o ON o.id = vi.order_id")
    args = []
    if state in ("received", "accepted", "rejected"):
        sql += " WHERE vi.state = ?"
        args.append(state)
    rows = q(sql + " ORDER BY vi.id DESC LIMIT 500", args)
    counts = {r["state"]: r["n"] for r in q("SELECT state, COUNT(*) AS n FROM vendor_invoices GROUP BY state")}
    return render_template("vendors/ap_inbox.html", rows=rows, state=state, counts=counts)


@bp.route("/ap-inbox/<int:vid>", methods=["GET", "POST"])
@login_required
def ap_view(vid):
    portal_required()
    from flask import g
    vi = q("SELECT vi.*, p.name_en, p.name_ar, o.number AS po, o.amount_untaxed AS po_untaxed, o.amount_total AS po_total, "
           "o.branch_id AS po_branch FROM vendor_invoices vi JOIN partners p ON p.id = vi.partner_id "
           "LEFT JOIN orders o ON o.id = vi.order_id WHERE vi.id = ?", (vid,), one=True)
    if not vi:
        abort(404)
    if request.method == "POST":
        if g.user["role"] not in ("admin", "accountant"):
            abort(403)
        f = request.form
        from .. import check_branch
        if vi["order_id"]:
            check_branch(vi["po_branch"])  # the invoice belongs to the PO's branch
        try:
            if f.get("action") == "accept":
                bid = check_branch(f["branch_id"]) if f.get("branch_id") and not vi["order_id"] else None
                inv_id = accept_invoice(vid, user_id(), bid)
                commit()
                bill = q("SELECT amount_total FROM invoices WHERE id = ?", (inv_id,), one=True)
                if bill["amount_total"] != vi["amount_total"]:
                    flash(f"{t('Draft bill created. Its total differs from the vendor invoice')}: "
                          f"{bill['amount_total'] / 1000:,.2f} ≠ {vi['amount_total'] / 1000:,.2f}", "error")
                else:
                    flash(t("Draft bill created. Check it and post it."), "success")
                return redirect(url_for("invoices.view", inv_id=inv_id))
            if f.get("action") != "reject":
                abort(400)
            reject_invoice(vid, f.get("reason"), user_id())
            commit()
            flash(t("Returned to the vendor."), "success")
        except (AccountingError, ValueError) as e:
            from ..db import get_db
            get_db().rollback()
            flash(str(e), "error")
        return redirect(url_for("vendors.ap_view", vid=vid))
    return render_template("vendors/ap_view.html", vi=vi, branches=branch_map())


@bp.route("/ap-inbox/<int:vid>/file")
@login_required
def ap_file(vid):
    portal_required()
    vi = q("SELECT * FROM vendor_invoices WHERE id = ?", (vid,), one=True)
    if not vi or not vi["filename"]:
        abort(404)
    return send_from_directory(upload_dir(), vi["filename"], download_name=vi["original_name"])


# ---------------------------------------------------------------- document renewals and bank detail changes

@bp.route("/doc-updates/<int:did>/<action>", methods=["GET", "POST"])
@login_required
def doc_update(did, action):
    portal_required()
    d = q("SELECT * FROM vendor_doc_updates WHERE id = ?", (did,), one=True)
    if not d:
        abort(404)
    if action == "file":
        return send_from_directory(upload_dir(), d["filename"], download_name=d["original_name"])
    if request.method != "POST" or not can_write():
        abort(403)
    if action not in ("approve", "reject"):
        abort(404)
    try:
        decide_doc(did, action == "approve", user_id(), request.form.get("reason"))
        commit()
        flash(t("Saved."), "success")
    except AccountingError as e:
        from ..db import get_db
        get_db().rollback()
        flash(str(e), "error")
    return redirect(url_for("vendors.view", pid=d["partner_id"]) + "#updates")


@bp.route("/bank-changes")
@login_required
def bank_changes():
    rows = q("SELECT bc.*, p.name_en, p.name_ar FROM bank_changes bc JOIN partners p ON p.id = bc.partner_id "
             "ORDER BY bc.state != 'pending', bc.id DESC LIMIT 300")
    return render_template("vendors/bank_changes.html", rows=rows)


@bp.route("/bank-changes/<int:cid>", methods=["GET", "POST"])
@login_required
def bank_change(cid):
    from flask import g
    c = q("SELECT bc.*, p.name_en, p.name_ar, u.full_name AS requester, pu.email AS portal_email FROM bank_changes bc "
          "JOIN partners p ON p.id = bc.partner_id LEFT JOIN users u ON u.id = bc.requested_by "
          "LEFT JOIN portal_users pu ON pu.id = bc.portal_user_id WHERE bc.id = ?", (cid,), one=True)
    if not c:
        abort(404)
    if request.method == "POST":
        if g.user["role"] not in ("admin", "accountant"):
            abort(403)
        f = request.form
        try:
            pending_req = q("SELECT 1 FROM approval_requests WHERE doc_type = 'bank_change' AND doc_id = ? "
                            "AND state = 'pending'", (cid,), one=True)
            if not pending_req and not can_approve():
                raise AccountingError(t("You do not have permission for this action."))
            decide_bank_change(cid, (f.get("action") or request.args.get("do")) == "approve", user_id(),
                               f.get("reason") or f.get("comment"),
                               bool(f.get("override")))
            commit()
            flash(t("Done."), "success")
        except AccountingError as e:
            from ..db import get_db
            get_db().rollback()
            flash(str(e), "error")
        return redirect(url_for("vendors.bank_change", cid=cid))
    if request.args.get("file") and c["filename"]:
        return send_from_directory(upload_dir(), c["filename"], download_name=c["original_name"])
    return render_template("vendors/bank_change.html", c=c)
