import os
import uuid
from pathlib import Path
from flask import current_app


def save_upload(file):
    if not file or not file.filename:
        raise ValueError('请选择 Excel 文件')
    extension = Path(file.filename).suffix.lower()
    if extension not in ('.xls', '.xlsx'):
        raise ValueError('仅支持 .xls 和 .xlsx 文件')
    path = os.path.join(current_app.config['PO_UPLOADS'], uuid.uuid4().hex + extension)
    file.save(path)
    return path


def read_rows(path):
    """读取 xlsx/xls 明细，返回 [代码,规格,单位,数量,单价,交期,备注]。"""
    ext = os.path.splitext(path)[1].lower()
    grid = []
    if ext in (".xlsx", ".xlsm"):
        import openpyxl
        wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
        for row in wb[wb.sheetnames[0]].iter_rows(values_only=True):
            grid.append(["" if c is None else c for c in row])
        wb.close()
    else:
        import xlrd
        bk = xlrd.open_workbook(path)
        sh = bk.sheet_by_index(0)
        for r in range(sh.nrows):
            grid.append([xlrd.xldate_as_datetime(sh.cell_value(r, c), bk.datemode)
                         if sh.cell_type(r, c) == xlrd.XL_CELL_DATE else sh.cell_value(r, c)
                         for c in range(sh.ncols)])
    keys = {"code": [u"代码", u"料号", u"物料编码", u"品号"],
            "spec": [u"规格", u"名称", u"品名", u"描述"],
            "unit": [u"单位"],
            "qty": [u"数量", u"数量(QTY)"],
            "price": [u"单价", u"含税单价", u"价格"],
            "date": [u"交期", u"交货日期", u"日期"],
            "remark": [u"备注"]}
    order = ["code", "spec", "unit", "qty", "price", "date", "remark"]
    header_idx = None
    for i, row in enumerate(grid[:8]):
        text = " ".join(str(c) for c in row)
        if any(k in text for k in keys["code"]) and any(k in text for k in keys["qty"]):
            header_idx = i
            break
    if header_idx is None:
        return [[c for c in row[:7]] for row in grid]
    heads = [str(c or "").strip() for c in grid[header_idx]]
    mapping = {}
    for idx, h in enumerate(heads):
        for k, ks in keys.items():
            if any(x in h for x in ks):
                mapping[k] = idx
                break
    out = []
    for row in grid[header_idx + 1:]:
        vals = []
        for k in order:
            i = mapping.get(k)
            vals.append(row[i] if (i is not None and i < len(row)) else "")
        out.append(vals)
    return out


def read_table(path, keys, order, missing=""):
    """通用表格读取：按关键词映射表头，返回按 order 排列的二维列表。

    与 read_rows() 的区别是表头判定更宽松（命中任意关键词即可），
    因为物料表 / 供应商表都没有"数量"列，套用 read_rows 会退化成按位置读。
    """
    ext = os.path.splitext(path)[1].lower()
    grid = []
    if ext in (".xlsx", ".xlsm"):
        import openpyxl
        wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
        for row in wb[wb.sheetnames[0]].iter_rows(values_only=True):
            grid.append(["" if c is None else c for c in row])
        wb.close()
    else:
        import xlrd
        bk = xlrd.open_workbook(path)
        sh = bk.sheet_by_index(0)
        for r in range(sh.nrows):
            grid.append([xlrd.xldate_as_datetime(sh.cell_value(r, c), bk.datemode)
                         if sh.cell_type(r, c) == xlrd.XL_CELL_DATE else sh.cell_value(r, c)
                         for c in range(sh.ncols)])
    header_idx = None
    for i, row in enumerate(grid[:8]):
        text = " ".join(str(c) for c in row)
        if any(any(k in text for k in ks) for ks in keys.values()):
            header_idx = i
            break
    if header_idx is None:
        return [list(row[:len(order)]) for row in grid]
    heads = [str(c or "").strip() for c in grid[header_idx]]
    mapping = {}
    for idx, h in enumerate(heads):
        for k, ks in keys.items():
            if k in mapping:
                continue
            if any(x in h for x in ks):
                mapping[k] = idx
                break
    out = []
    for row in grid[header_idx + 1:]:
        vals = []
        for k in order:
            i = mapping.get(k)
            vals.append(row[i] if (i is not None and i < len(row)) else missing)
        out.append(vals)
    return out


MATERIAL_KEYS = {
    "code": [u"物料代码", u"料号", u"物料编码", u"品号", u"代码"],
    "spec": [u"规格", u"名称", u"品名", u"描述"],
    "unit": [u"单位"],
    "price": [u"单价", u"价格"],
}
MATERIAL_ORDER = ["code", "spec", "unit", "price"]

SUPPLIER_KEYS = {
    "short_name": [u"简称", u"别名"],
    "code": [u"编号", u"供应商编号", u"代码"],
    "name": [u"名称", u"供应商名称", u"公司"],
    "contact": [u"联系人"],
    "phone": [u"电话", u"手机", u"联系方式"],
    "payment_terms": [u"付款方式", u"结算方式", u"账期"],
    "tax_no": [u"税号", u"纳税人识别号"],
    "bank_name": [u"开户银行", u"银行名称"],
    "bank_account": [u"银行帐号", u"银行账号", u"帐号", u"账号"],
}
SUPPLIER_ORDER = ["code", "name", "contact", "phone", "payment_terms",
                  "tax_no", "bank_name", "bank_account", "short_name"]


def read_material_rows(path):
    """读取物料表，返回 [代码, 规格, 单位, 单价]。"""
    return read_table(path, MATERIAL_KEYS, MATERIAL_ORDER)


def read_supplier_rows(path):
    """简称追加在末列；旧表缺少该列时以 None 表示，更新时保留已有简称。"""
    return read_table(path, SUPPLIER_KEYS, SUPPLIER_ORDER, missing=None)