# -*- coding: utf-8 -*-
"""博特维采购订单管理系统：制单 → 审批 → 导出 PDF。"""
import io
import os
import sys
from datetime import datetime
from functools import wraps

from flask import (Flask, flash, jsonify, redirect, render_template, request,
                   send_file, send_from_directory, session, url_for)
from sqlalchemy import func, or_
from werkzeug.security import check_password_hash, generate_password_hash
from security import db, current_user, login_required, role_required, init_security, validate_password, sign_in
from money import number, format_price
from order_logic import scope
from order_routes import register_order_routes
from order_tracking import (TRACKING_FILTERS, filter_tracking, register_tracking_routes,
                            tracking_conditions, tracking_priority)
from excel_io import read_rows, read_material_rows, read_supplier_rows, save_upload
from flask import abort

from amount_cn import amount_to_cn
from models import (COMPANY_NAME, DOC_TYPES, ROLE_TEXT, STATUS_TEXT,
                    DEFAULT_DELIVERY_PLACE, DEFAULT_OUR_CONTACT,
                    DEFAULT_OUR_PHONE, DEFAULT_OUR_TAX_NO, DEFAULT_TAX_RATE,
                    ApprovalLog, Material, Order, OrderItem, Supplier, User,
                    init_db)
from material_prices import remember_approved_prices, supplier_price_map
from pdf_export import build_pdf

# 打包成 EXE 后：资源在临时目录 _MEIPASS，数据必须写在 EXE 同级目录
if getattr(sys, "frozen", False):
    RES_DIR = sys._MEIPASS
    BASE_DIR = os.path.dirname(sys.executable)
else:
    RES_DIR = os.path.dirname(os.path.abspath(__file__))
    BASE_DIR = RES_DIR

BASE_DIR = os.path.abspath(os.environ.get("PO_DATA_DIR", BASE_DIR))
INSTANCE = os.path.join(BASE_DIR, "instance")
EXPORTS = os.path.join(BASE_DIR, "exports")
UPLOADS = os.path.join(BASE_DIR, "uploads")
for d in (INSTANCE, EXPORTS, UPLOADS):
    os.makedirs(d, exist_ok=True)

app = Flask(__name__,
            template_folder=os.path.join(RES_DIR, "templates"),
            static_folder=os.path.join(RES_DIR, "static"))

app.config["MAX_CONTENT_LENGTH"] = 8 * 1024 * 1024

engine, Session = init_db(os.path.join(INSTANCE, "po.db").replace("\\", "/"))
app.config['PO_UPLOADS'] = UPLOADS
init_security(app, Session, INSTANCE)
app.jinja_env.filters['price'] = format_price
register_order_routes(app)
register_tracking_routes(app)
from notification_routes import register_notification_routes
register_notification_routes(app)


def now():
    return datetime.now()


# ---------------- 首页 ----------------
@app.route("/")
@login_required
def index():
    u = current_user()
    todo = []
    if u.role in ("approver", "admin"):
        todo = scope(db.query(Order)).filter(Order.status == 'pending_approval', Order.created_by != u.id).order_by(Order.id.desc()).all()
    my_orders = []
    if u.role in ("maker", "admin"):
        my_orders = (scope(db.query(Order)).filter_by(created_by=u.id)
                     .order_by(Order.id.desc()).limit(8).all())
    counts = {s: scope(db.query(Order)).filter_by(status=s).count() for s in STATUS_TEXT}
    month_total = (scope(db.query(func.sum(Order.total_amount)))
                   .filter(Order.status == "approved").scalar() or 0)
    tracking_counts = {key: filter_tracking(scope(db.query(Order)), key).count() for key in TRACKING_FILTERS}
    tracking_key = request.args.get('tracking', 'open')
    tracking_query = filter_tracking(scope(db.query(Order)), tracking_key or 'open')
    tracking_total = tracking_query.count()
    tracking_rows = tracking_query.order_by(tracking_priority(), Order.id.asc()).limit(12).all()
    return render_template("index.html", user=u, todo=todo, my_orders=my_orders,
                           counts=counts, month_total=month_total,
                           tracking_counts=tracking_counts, tracking_rows=tracking_rows,
                           tracking_key=tracking_key or 'open', tracking_total=tracking_total,
                           status_text=STATUS_TEXT, role_text=ROLE_TEXT)


# ---------------- 订单 ----------------
@app.route("/orders")
@login_required
def orders():
    u = current_user()
    q = scope(db.query(Order))
    kw = request.args.get("kw", "").strip()
    status = request.args.get("status", "")
    doc_type = request.args.get("doc_type", "")
    date_from = request.args.get("date_from", "")
    date_to = request.args.get("date_to", "")
    material_kw = request.args.get('material_kw', '').strip()[:100]
    if kw:
        q = q.filter(Order.order_no.like("%%%s%%" % kw) |
                     Order.supplier_name.like("%%%s%%" % kw))
    if status:
        q = q.filter(Order.status == status)
    if doc_type:
        q = q.filter(Order.doc_type == doc_type)
    if date_from:
        q = q.filter(Order.order_date >= date_from)
    if date_to:
        q = q.filter(Order.order_date <= date_to)
    if material_kw:
        pattern = '%%%s%%' % material_kw
        q = q.filter(Order.items.any((OrderItem.material_code.ilike(pattern)) |
                                     (OrderItem.material_spec.ilike(pattern))))
    if u.role == "maker":
        q = q.filter(Order.created_by == u.id)
    tracking = request.args.get('tracking', '')
    q = filter_tracking(q, tracking)
    if not tracking and not kw and not material_kw and request.args.get('show_completed') != '1':
        q = q.filter(or_(Order.status != 'approved', ~tracking_conditions()['closed']))
    rows = q.order_by(Order.id.desc()).all()
    matched_items = {}
    if material_kw and rows:
        for item in (db.query(OrderItem).filter(OrderItem.order_id.in_([o.id for o in rows]),
                                                (OrderItem.material_code.ilike(pattern)) |
                                                (OrderItem.material_spec.ilike(pattern)))
                     .order_by(OrderItem.order_id, OrderItem.seq)):
            matched_items.setdefault(item.order_id, []).append(item)
    return render_template("orders.html", user=u, rows=rows, status_text=STATUS_TEXT,
                           doc_types=DOC_TYPES, args=request.args, matched_items=matched_items)


@app.route("/import_template.xlsx")
@login_required
def import_template():
    import openpyxl
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append([u"物料代码", u"物料规格", u"单位", u"数量", u"含税单价", u"交期", u"备注"])
    ws.append(["M-1001", u"铝合金支架 6061-T6", u"件", 100, 38.5, "2026-09-20", u""])
    for col, w in zip("ABCDEFG", (16, 34, 8, 10, 12, 14, 20)):
        ws.column_dimensions[col].width = w
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return send_file(buf, as_attachment=True, download_name=u"明细导入模板.xlsx",
                     mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")


# ---------------- 供应商 / 物料 ----------------
@app.route("/suppliers", methods=["GET", "POST"])
@login_required
@role_required("admin", "maker")
def suppliers():
    u = current_user()
    if request.method == "POST":
        sid = request.form.get("id")
        s = db.get(Supplier, int(sid)) if sid else Supplier()
        if s is None:
            abort(404)
        for f in ("code", "name", "contact", "phone", "tax_no", "bank_name",
                  "bank_account", "payment_terms"):
            setattr(s, f, request.form.get(f, "").strip())
        if not s.name:
            raise ValueError('供应商名称必填')
        if 'short_name' in request.form:
            short_name = request.form['short_name'].strip()
            if len(short_name) > 60:
                raise ValueError('供应商简称不能超过60个字符')
            s.short_name = short_name
        if not sid:
            db.add(s)
        db.commit()
        flash(u"已保存", "ok")
        return redirect(url_for("suppliers"))
    kw = request.args.get('kw', '').strip()[:100]
    query = db.query(Supplier)
    if kw:
        pattern = '%%%s%%' % kw
        query = query.filter(Supplier.name.ilike(pattern) | Supplier.short_name.ilike(pattern) |
                             Supplier.code.ilike(pattern))
    rows = query.order_by(Supplier.id).all()
    return render_template("suppliers.html", user=u, rows=rows, kw=kw)


@app.route("/suppliers/<int:sid>/toggle", methods=["POST"])
@login_required
@role_required("admin", "maker")
def supplier_toggle(sid):
    """停用 / 启用互切。停用后不出现在下单选择里，但保留历史数据。"""
    s = db.get(Supplier, sid)
    if s:
        s.active = not s.active
        db.commit()
        flash(u"已%s：%s" % (u"启用" if s.active else u"停用", s.name), "ok")
    return redirect(url_for("suppliers"))


@app.route("/suppliers/<int:sid>/delete", methods=["POST"])
@login_required
@role_required("admin")
def supplier_delete(sid):
    """彻底删除。订单里存的是供应商名称快照，删库记录不影响历史单据。"""
    s = db.get(Supplier, sid)
    if s:
        name = s.name
        db.delete(s)
        db.commit()
        flash(u"已删除供应商：%s" % name, "ok")
    return redirect(url_for("suppliers"))


@app.route("/suppliers/import_excel", methods=["POST"])
@login_required
@role_required("admin", "maker")
def supplier_import_excel():
    file = request.files.get("file")
    if not file or not file.filename:
        flash(u"请选择文件", "error")
        return redirect(url_for("suppliers"))
    path = save_upload(file)
    try:
        rows = read_supplier_rows(path)
    except Exception as e:
        flash(u"读取失败：%s" % e, "error")
        return redirect(url_for("suppliers"))
    mode = request.form.get("mode", "update")
    added = updated = skipped = failed = 0
    errs = []

    def s(v):
        return str(v or "").strip()

    for i, r in enumerate(rows, start=2):
        if not any(r):
            continue
        (code, name, contact, phone, payment_terms, tax_no,
         bank_name, bank_account, short_name) = (list(r) + [None] * 9)[:9]
        code, name = s(code), s(name)
        if not name:
            failed += 1
            errs.append(u"第%d行：供应商名称为空" % i)
            continue
        if short_name is not None and len(s(short_name)) > 60:
            failed += 1
            errs.append(u"第%d行：供应商简称不能超过60个字符" % i)
            continue
        obj = db.query(Supplier).filter_by(code=code).first() if code else None
        if not obj:
            obj = db.query(Supplier).filter_by(name=name).first()
        if obj:
            if mode == "skip":
                skipped += 1
                continue
            obj.code = code or obj.code
            obj.name = name
            obj.contact = s(contact)
            obj.phone = s(phone)
            obj.payment_terms = s(payment_terms)
            obj.tax_no = s(tax_no)
            obj.bank_name = s(bank_name)
            obj.bank_account = s(bank_account)
            if short_name is not None:
                obj.short_name = s(short_name)
            obj.active = True
            updated += 1
        else:
            db.add(Supplier(code=code, name=name, contact=s(contact),
                            phone=s(phone), payment_terms=s(payment_terms),
                            tax_no=s(tax_no), bank_name=s(bank_name),
                            bank_account=s(bank_account), short_name=s(short_name), active=True))
            added += 1
    db.commit()
    msg = u"导入完成：新增 %d，更新 %d，跳过 %d，失败 %d" % (added, updated, skipped, failed)
    if errs:
        msg += u"。失败明细：" + u"；".join(errs[:8])
        if len(errs) > 8:
            msg += u" 等共 %d 条" % len(errs)
    flash(msg, "ok" if not failed else "error")
    return redirect(url_for("suppliers"))


@app.route("/suppliers/import_template.xlsx")
@login_required
def supplier_import_template():
    import openpyxl
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append([u"编号", u"名称", u"联系人", u"电话", u"付款方式", u"税号",
               u"开户银行", u"银行帐号", u"公司简称"])
    ws.append(["S003", u"苏州精锐五金制品有限公司", u"周经理", "13800000001",
               u"月结30天", "91320500MA1XXXXXXX", u"中国银行苏州工业园区支行",
               "4562 1088 0012 3456", u"精锐五金"])
    for col, w in zip("ABCDEFGHI", (10, 30, 12, 16, 14, 22, 26, 24, 18)):
        ws.column_dimensions[col].width = w
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return send_file(buf, as_attachment=True, download_name=u"供应商导入模板.xlsx",
                     mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")


@app.route("/materials", methods=["GET", "POST"])
@login_required
@role_required("admin", "maker")
def materials():
    u = current_user()
    if request.method == "POST":
        mid = request.form.get("id")
        m = db.get(Material, int(mid)) if mid else Material()
        if m is None:
            abort(404)
        m.code = request.form.get("code", "").strip()
        if not m.code:
            raise ValueError('物料代码必填')
        m.spec = request.form.get("spec", "").strip()
        m.unit = request.form.get("unit", "").strip()
        m.price = number(request.form.get('price'), '默认含税单价')
        if not mid:
            db.add(m)
        db.commit()
        flash(u"已保存", "ok")
        return redirect(url_for("materials"))
    kw = request.args.get('kw', '').strip()[:100]
    query = db.query(Material)
    if kw:
        pattern = '%%%s%%' % kw
        query = query.filter(Material.code.ilike(pattern) | Material.spec.ilike(pattern))
    rows = query.order_by(Material.id).all()
    return render_template("materials.html", user=u, rows=rows, kw=kw)


@app.post('/materials/prices/from-approved-orders')
@login_required
@role_required('admin')
def sync_approved_material_prices():
    # Old approved orders may predate automatic catalog price updates.
    orders = (db.query(Order).filter(Order.status == 'approved')
              .order_by(Order.approved_at, Order.id).all())
    count = sum(remember_approved_prices(db, order) for order in orders)
    db.commit()
    flash('已按审批先后同步 %s 张订单，更新 %s 条物料单价。' % (len(orders), count), 'ok')
    return redirect(url_for('materials'))


@app.route("/materials/<int:mid>/toggle", methods=["POST"])
@login_required
@role_required("admin", "maker")
def material_toggle(mid):
    """停用 / 启用互切。停用后不出现在下单物料下拉里，但保留历史数据。"""
    m = db.get(Material, mid)
    if m:
        m.active = not m.active
        db.commit()
        flash(u"已%s：%s" % (u"启用" if m.active else u"停用", m.code), "ok")
    return redirect(url_for("materials"))


@app.route("/materials/<int:mid>/delete", methods=["POST"])
@login_required
@role_required("admin")
def material_delete(mid):
    """彻底删除。订单明细里存的是物料快照，删库记录不影响历史单据。"""
    m = db.get(Material, mid)
    if m:
        code = m.code
        db.delete(m)
        db.commit()
        flash(u"已删除物料：%s" % code, "ok")
    return redirect(url_for("materials"))


@app.route("/materials/import_excel", methods=["POST"])
@login_required
@role_required("admin", "maker")
def material_import_excel():
    file = request.files.get("file")
    if not file or not file.filename:
        flash(u"请选择文件", "error")
        return redirect(url_for("materials"))
    path = save_upload(file)
    try:
        rows = read_material_rows(path)
    except Exception as e:
        flash(u"读取失败：%s" % e, "error")
        return redirect(url_for("materials"))
    mode = request.form.get("mode", "update")
    added = updated = skipped = failed = 0
    errs = []

    def s(v):
        return str(v or "").strip()

    def num(v):
        return number(v, '导入单价')

    for i, r in enumerate(rows, start=2):
        if not any(r):
            continue
        code, spec, unit, price = (list(r) + [""] * 4)[:4]
        code = s(code)
        if not code:
            failed += 1
            errs.append(u"第%d行：物料代码为空" % i)
            continue
        m = db.query(Material).filter_by(code=code).first()
        if m:
            if mode == "skip":
                skipped += 1
                continue
            m.spec = s(spec)
            m.unit = s(unit)
            m.price = num(price)
            m.active = True
            updated += 1
        else:
            db.add(Material(code=code, spec=s(spec), unit=s(unit),
                            price=num(price), active=True))
            added += 1
    db.commit()
    msg = u"导入完成：新增 %d，更新 %d，跳过 %d，失败 %d" % (added, updated, skipped, failed)
    if errs:
        msg += u"。失败明细：" + u"；".join(errs[:8])
        if len(errs) > 8:
            msg += u" 等共 %d 条" % len(errs)
    flash(msg, "ok" if not failed else "error")
    return redirect(url_for("materials"))


@app.route("/materials/import_template.xlsx")
@login_required
def material_import_template():
    import openpyxl
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append([u"物料代码", u"物料规格", u"单位", u"默认含税单价"])
    ws.append(["M-1004", u"铝合金支架 6061-T6 200×80×10mm", u"件", 38.5])
    ws.append(["M-1005", u"步进电机 57BYG250 2相", u"台", 265.0])
    for col, w in zip("ABCD", (16, 34, 10, 16)):
        ws.column_dimensions[col].width = w
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return send_file(buf, as_attachment=True, download_name=u"物料导入模板.xlsx",
                     mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")


@app.route("/api/materials")
@login_required
def api_materials():
    rows = db.query(Material).filter_by(active=True).all()
    return jsonify([{"id": m.id, "code": m.code, "spec": m.spec,
                     "unit": m.unit, "price": m.price} for m in rows])


@app.route("/api/suppliers")
@login_required
def api_suppliers():
    rows = db.query(Supplier).filter_by(active=True).all()
    return jsonify([{"id": s.id, "name": s.name, "short_name": s.short_name, "contact": s.contact,
                     "phone": s.phone, "tax_no": s.tax_no, "bank_name": s.bank_name,
                     "bank_account": s.bank_account,
                     "payment_terms": s.payment_terms} for s in rows])


@app.route('/api/material-prices')
@login_required
@role_required('maker')
def api_material_prices():
    return jsonify(supplier_price_map(db, request.args.get('supplier_name', ''),
                                      request.args.get('supplier_tax_no', ''), current_user()))


# ---------------- 用户 ----------------
@app.route("/users", methods=["GET", "POST"])
@login_required
@role_required("admin")
def users():
    u = current_user()
    if request.method == "POST":
        uid = request.form.get("id")
        t = db.get(User, int(uid)) if uid else User(session_version=1)
        if t is None:
            abort(404)
        username = request.form.get('username', '').strip()
        name = request.form.get('name', '').strip()
        role = request.form.get('role', 'maker')
        active = request.form.get('active', '1') == '1'
        if not username or len(username) > 50 or not name or len(name) > 50:
            raise ValueError('登录名和姓名必填，且不超过 50 字')
        if role not in ROLE_TEXT:
            raise ValueError('角色无效')
        if uid and t.role == 'admin' and t.active and (role != 'admin' or not active):
            if db.query(User).filter_by(role='admin', active=True).count() <= 1:
                raise ValueError('必须保留至少一个启用的管理员')
        t.username, t.name, t.role, t.active = username, name, role, active
        pw = request.form.get('password', '')
        if pw:
            validate_password(pw)
            t.password_hash = generate_password_hash(pw)
            t.must_change_password = True
        elif not uid:
            raise ValueError('请为新账号设置初始密码')
        if uid:
            t.session_version += 1
        if not uid:
            db.add(t)
        db.commit()
        if t.id == u.id:
            sign_in(t)
        flash(u"已保存，原有登录已失效", "ok")
        return redirect(url_for("users"))
    rows = db.query(User).order_by(User.id).all()
    edit_id = request.args.get('edit', type=int)
    target = db.get(User, edit_id) if edit_id else None
    return render_template("users.html", user=u, rows=rows, roles=ROLE_TEXT, target=target)


# ---------------- 统计 ----------------
@app.route("/stats")
@login_required
def stats():
    u = current_user()
    by_status = (scope(db.query(Order.status, func.count(Order.id), func.sum(Order.total_amount)))
                 .group_by(Order.status).all())
    by_supplier = (scope(db.query(Order.supplier_name, func.count(Order.id),
                            func.sum(Order.total_amount)))
                   .filter(Order.status == "approved")
                   .group_by(Order.supplier_name)
                   .order_by(func.sum(Order.total_amount).desc()).limit(20).all())
    by_month = (scope(db.query(func.substr(Order.order_date, 1, 7), func.count(Order.id),
                         func.sum(Order.total_amount)))
                .group_by(func.substr(Order.order_date, 1, 7)).all())
    return render_template("stats.html", user=u, by_status=by_status,
                           by_supplier=by_supplier, by_month=by_month,
                           status_text=STATUS_TEXT)


@app.route("/export/ledger")
@login_required
def export_ledger():
    import openpyxl
    rows = scope(db.query(Order)).order_by(Order.id.desc()).all()
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append([u"采购单号", u"单据类型", u"供应商", u"采购日期", u"状态", u"金额",
               u"制单人", u"审批人", u"创建时间", '需预付款', '收票状态', '付款状态',
               '付款日期', '交货状态', '采购跟进备注', '付款备注', '变更后待核对'])
    for o in rows:
        ws.append([o.order_no, o.doc_type_text, o.supplier_name, o.order_date,
                   o.status_text, o.total_amount, o.created_by_name,
                   o.approved_by,
                   o.created_at.strftime("%Y-%m-%d %H:%M") if o.created_at else "",
                   '是' if o.needs_prepayment else '否', o.invoice_status_text, o.payment_status_text,
                   o.payment_date, o.delivery_status_text, o.fulfillment_note, o.payment_note,
                   '、'.join(label for label, stale in [('采购跟进', o.fulfillment_stale), ('付款', o.payment_stale)] if stale)])
    for col, w in zip("ABCDEFGHIJKLMNOPQ", (18, 14, 30, 12, 12, 12, 12, 12, 18, 12, 12, 12, 14, 12, 36, 36, 22)):
        ws.column_dimensions[col].width = w
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return send_file(buf, as_attachment=True,
                     download_name=u"采购订单台账_%s.xlsx" % now().strftime("%Y%m%d"),
                     mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")


@app.route("/exports/<name>")
@login_required
def exports_file(name):
    # Legacy filenames are not an authorization boundary. Use the order PDF endpoint.
    abort(404)


@app.context_processor
def inject_common():
    return {"COMPANY_NAME": COMPANY_NAME, "STATUS_TEXT": STATUS_TEXT,
            "ROLE_TEXT": ROLE_TEXT, "DOC_TYPES": DOC_TYPES}


def port_busy(p):
    import socket
    s = socket.socket()
    s.settimeout(0.4)
    try:
        s.connect(("127.0.0.1", p))
        return True
    except Exception:
        return False
    finally:
        s.close()


if __name__ == "__main__":
    import threading
    import webbrowser

    port = int(sys.argv[1]) if len(sys.argv) > 1 else 8080
    while port_busy(port) and port < 8120:
        port += 1
    open_browser = os.environ.get("PO_OPEN_BROWSER")
    if open_browser == "1" or (getattr(sys, "frozen", False) and open_browser != "0"):
        threading.Timer(1.5, lambda: webbrowser.open("http://127.0.0.1:%d" % port)).start()
    print("=" * 56)
    print("  博特维采购订单管理系统已启动")
    print("  本机访问： http://127.0.0.1:%d" % port)
    print("  关闭本窗口即停止服务")
    print("=" * 56)
    from waitress import serve
    from notifications import start_notification_worker
    notification_stop = start_notification_worker(app, Session)
    try:
        serve(app, host=os.environ.get('PO_HOST', '0.0.0.0'), port=port, threads=8)
    finally:
        notification_stop.set()