"""首次初始化: 建表 + 管理员 + 从桌面 Excel 导入真实物料(含期初库存)

用法: 用 venv 的 python 运行
  python init_db.py
"""
import hashlib
from datetime import date
from app import app, db
from models import User, Material, Inbound

EXCEL = r"C:/Users/wps18/Desktop/物料明细表.xlsx"


def main():
    with app.app_context():
        db.create_all()

        # 管理员
        if not db.session.scalar(db.select(User).where(User.username == "admin")):
            db.session.add(User(
                username="admin",
                password=hashlib.sha256("admin123".encode("utf-8")).hexdigest(),
                role="admin",
                display_name="管理员",
            ))
            print("已创建管理员 admin / admin123")

        # 导入物料
        import openpyxl
        wb = openpyxl.load_workbook(EXCEL, data_only=True)
        ws = wb["物料入库"]
        imported = 0
        skipped = 0
        for row in ws.iter_rows(min_row=2, values_only=True):
            pn = row[1]  # B 列 Part Number
            if pn is None:
                continue
            pn = str(pn).strip()
            if not pn:
                continue
            if db.session.scalar(db.select(Material).where(Material.part_number == pn)):
                skipped += 1
                continue
            man = str(row[2]) if row[2] else ""
            m = Material(part_number=pn, manufacturer=man, unit="个", safety_stock=0)
            db.session.add(m)
            db.session.flush()
            # 期初库存: 优先 E 列(库存数量), 其次 D 列(总数量)
            init_qty = row[4] if row[4] not in (None, "") else row[3]
            try:
                init_qty = float(init_qty)
            except (TypeError, ValueError):
                init_qty = 0
            if init_qty > 0:
                db.session.add(Inbound(
                    material_id=m.id, qty=init_qty,
                    source="期初", occur_date=date.today(), operator="系统",
                ))
            imported += 1
        db.session.commit()
        print(f"导入物料 {imported} 条, 跳过已存在 {skipped} 条")


if __name__ == "__main__":
    main()