import pymysql
import random

conn = pymysql.connect(host='10.8.0.252', user='root', password='Hbhyg731024@', database='pms')
cur = conn.cursor()

# 1. 添加address字段
try:
    cur.execute("ALTER TABLE cabinets ADD COLUMN address VARCHAR(200) DEFAULT NULL COMMENT '安装地址' AFTER name")
    print("✓ address字段添加成功")
except:
    print("✓ address字段已存在")

# 2. 更新柜子数据（中文名字+地址）
addresses = [
    "无锡市太湖新城观山路88号",
    "无锡市梁溪区中山路168号",
    "无锡市新吴区菱湖大道200号",
    "苏州市工业园区星湖街328号",
    "苏州市高新区竹园路78号",
    "杭州市西湖区文三路123号",
    "杭州市滨江区网商路599号",
    "南京市建邺区庐山路158号",
    "上海市浦东新区张江高科技园区",
    "上海市闵行区紫竹科学园区",
    "合肥市高新区望江西路800号",
    "武汉市东湖高新区光谷大道77号",
    "成都市高新区天府大道999号",
    "重庆市渝北区新溉大道2号",
    "长沙市岳麓区麓谷大道662号",
    "广州市天河区科韵路16号",
    "深圳市南山区科技南路18号",
    "宁波市鄞州区宁穿路1888号",
    "厦门市思明区软件园二期",
    "青岛市崂山区株洲路78号",
]

names = [
    "太湖新城换电柜A", "太湖新城换电柜B",
    "梁溪区换电柜A", "梁溪区换电柜B",
    "新吴区换电柜A",
    "工业园区换电柜A", "工业园区换电柜B",
    "高新区换电柜A",
    "西湖区换电柜A", "西湖区换电柜B",
    "滨江区换电柜A",
    "建邺区换电柜A",
    "张江换电柜A", "张江换电柜B",
    "紫竹换电柜A",
    "高新区换电柜A", "高新区换电柜B",
    "光谷换电柜A",
    "天府新区换电柜A", "天府新区换电柜B",
    "渝北区换电柜A",
    "麓谷换电柜A",
    "天河区换电柜A", "天河区换电柜B",
    "南山换电柜A", "南山换电柜B",
    "鄞州换电柜A",
    "软件园换电柜A",
    "崂山换电柜A",
]

# 更新所有柜子
cur.execute("SELECT id FROM cabinets ORDER BY id")
cabinet_ids = [row[0] for row in cur.fetchall()]

for i, cab_id in enumerate(cabinet_ids):
    name = names[i] if i < len(names) else f"充电柜-{i+1:03d}"
    addr = addresses[i % len(addresses)]
    cur.execute(f"UPDATE cabinets SET name='{name}', address='{addr}' WHERE id={cab_id}")

conn.commit()
print(f"✓ 更新了 {len(cabinet_ids)} 个柜子的名字和地址")

# 统计
cur.execute("SELECT COUNT(*) FROM cabinets WHERE name IS NOT NULL")
print(f"有名字的柜子: {cur.fetchone()[0]}")
cur.execute("SELECT COUNT(*) FROM cabinets WHERE address IS NOT NULL")
print(f"有地址的柜子: {cur.fetchone()[0]}")

conn.close()