foodie-server/updatesql/backfill_food_spec_relation.py

# -*- coding: utf-8 -*-
"""
2026-10-19 008 规格数据源统一（去 food_sku 回退）——存量数据回填脚本。

背景：pos_food.food_sku JSON 是旧规格存储；系统已统一以 food_spec_relation
为唯一规格源（菜单/报价/订单同源）。部署"去回退"版本前，必须先用本脚本把
仅在 food_sku 中的存量规格迁入关联表，否则老商品规格会从 App 消失。

做法（幂等，可重复执行）：
  对每个 food_sku 非空且尚无任何关联的商品：
    - JSON 内每个规格组：缺 food_specs 行则按 JSON 建（保留原 id）；
    - 组内每个规格值：缺 food_specs_value 行则按 JSON 建（价格取 JSON 内嵌价）；
    - 建 food_spec_relation(food_id, specs_id)。
  已有关联的商品整行跳过，不动数据。

用法：
  python backfill_food_spec_relation.py                       # 默认连测试库（application-druid.yml 测试站点配置）
  python backfill_food_spec_relation.py --host H --port P --user U --password P --db foodie   # 指定环境（生产执行用）

执行顺序要求：先在各环境执行本脚本，再部署去回退的后端版本。
"""
import argparse
import json

import pymysql

DEFAULT_HOST = "47.237.129.98"
DEFAULT_PORT = 3306
DEFAULT_USER = "foodie"
DEFAULT_PASSWORD = "pdjpwMmr3tscf2Re"
DEFAULT_DB = "foodie"


def columns(cursor, table):
    cursor.execute("SHOW COLUMNS FROM `%s`" % table)
    return [row["Field"] for row in cursor.fetchall()]


def insert_if_missing(cursor, table, key_column, row, allowed):
    cursor.execute("SELECT `%s` FROM `%s` WHERE `%s` = %%s" % (key_column, table, key_column), (row.get(key_column),))
    if cursor.fetchone():
        return False
    fields = [c for c in allowed if c in row and row[c] is not None]
    sql = "INSERT INTO `%s` (%s) VALUES (%s)" % (table, ",".join("`%s`" % f for f in fields),
                                                 ",".join(["%s"] * len(fields)))
    cursor.execute(sql, [row[f] for f in fields])
    return True


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--host", default=DEFAULT_HOST)
    parser.add_argument("--port", type=int, default=DEFAULT_PORT)
    parser.add_argument("--user", default=DEFAULT_USER)
    parser.add_argument("--password", default=DEFAULT_PASSWORD)
    parser.add_argument("--db", default=DEFAULT_DB)
    args = parser.parse_args()

    conn = pymysql.connect(host=args.host, port=args.port, user=args.user, password=args.password,
                           database=args.db, charset="utf8mb4")
    cur = conn.cursor(pymysql.cursors.DictCursor)
    spec_cols = columns(cur, "food_specs")
    value_cols = columns(cur, "food_specs_value")

    cur.execute("SELECT id, mdid, food_sku FROM pos_food "
                "WHERE food_sku IS NOT NULL AND food_sku <> '' AND food_sku <> '[]'")
    foods = cur.fetchall()
    skipped = linked = groups_created = values_created = relations_created = malformed = 0
    for food in foods:
        cur.execute("SELECT specs_id FROM food_spec_relation WHERE food_id = %s", (food["id"],))
        if cur.fetchall():
            linked += 1
            continue
        try:
            sku_groups = json.loads(food["food_sku"])
        except Exception:
            malformed += 1
            continue
        if not isinstance(sku_groups, list):
            malformed += 1
            continue
        touched = False
        for group in sku_groups:
            gid = group.get("id")
            if gid is None:
                continue
            spec_row = {"id": gid, "title": group.get("title") or "", "type": str(group.get("type") or "1"),
                        "state": str(group.get("state") or "1"), "language": group.get("language"),
                        "md_id": food["mdid"], "sort": group.get("sort") or 0,
                        "is_open": 1 if group.get("isOpen") is not False else 0,
                        "is_delete": 1 if group.get("isDelete") is True else 0}
            if insert_if_missing(cur, "food_specs", "id", spec_row, spec_cols):
                groups_created += 1
            for value in group.get("objects") or []:
                vid = value.get("id")
                if vid is None:
                    continue
                value_row = {"id": vid, "parent_id": value.get("parentId") or gid,
                             "name": value.get("name") or "", "price": value.get("price") or 0,
                             "state": str(value.get("state") or "0"),
                             "is_open": 1 if value.get("isOpen") is not False else 0}
                if insert_if_missing(cur, "food_specs_value", "id", value_row, value_cols):
                    values_created += 1
            cur.execute("SELECT id FROM food_spec_relation WHERE food_id = %s AND specs_id = %s", (food["id"], gid))
            if not cur.fetchone():
                cur.execute("INSERT INTO food_spec_relation (food_id, specs_id) VALUES (%s, %s)", (food["id"], gid))
                relations_created += 1
                touched = True
        if not touched:
            skipped += 1

    conn.commit()
    print("回填完成：商品总数 %d，已有关联跳过 %d，无有效组跳过 %d，JSON异常 %d；"
          "新建规格组 %d，新建规格值 %d，新建关联 %d"
          % (len(foods), linked, skipped, malformed, groups_created, values_created, relations_created))
    conn.close()


if __name__ == "__main__":
    main()