# 執行方式： python3 generator_db_data.py # 卡注重啟： sudo systemctl restart mariadb # 匯入方式： mysql -u root -p mmwave_v4 < mmwave_v4_simulated_realistic.sql import datetime import random import math # --- 設定參數 --- START_DATE = datetime.datetime(2026, 4, 1, 0, 0, 0) END_DATE = datetime.datetime(2026, 5, 31, 23, 59, 59) OUTPUT_FILE = "mmwave_v4_simulated_realistic.sql" ZONES = { '沙發': (0.6, 0.3), '書房': (2.7, 0.9), '衣帽間': (2.7, 2.7), '臥室': (0.9, 2.0) } def room_to_radar(room_x, room_y): cos45 = math.sqrt(2) / 2 A = (room_x - 3.6) / cos45 B = room_y / cos45 x_rad = (A + B) / 2 y_rad = (B - A) / 2 return round(x_rad, 2), round(y_rad, 2) fall_times = [ START_DATE + datetime.timedelta(days=1, hours=14, minutes=30), START_DATE + datetime.timedelta(days=3, hours=9, minutes=15), START_DATE + datetime.timedelta(days=6, hours=20, minutes=45), START_DATE + datetime.timedelta(days=12, hours=11, minutes=10), START_DATE + datetime.timedelta(days=18, hours=16, minutes=5) ] def get_next_activity(current_time, current_act, current_zone): h = current_time.hour while True: if 23 <= h or h < 7: is_active_night = (hash(current_time.date()) % 100) < 25 if is_active_night: choice = random.choices(['lay', 'null', 'walk'], weights=[85, 5, 10])[0] else: choice = random.choices(['lay', 'null'], weights=[98, 2])[0] if choice == 'lay': act, zone, dur = choice, '臥室', random.randint(3600, 10800) elif choice == 'null': act, zone, dur = choice, '無人', random.randint(1800, 3600) else: act, zone, dur = 'walk', '衣帽間', random.randint(20, 60) elif 7 <= h < 18: # 💡【修正 1：降低白天外出的機率與時間，增加走動機率】 # 外出權重從 40 降到 15，持續時間從最長 4 小時降為 2 小時 choice = random.choices(['null', 'sit', 'walk_long', 'walk_short', 'stand'], weights=[15, 35, 20, 20, 10])[0] if choice == 'null': act, zone, dur = 'null', '無人', random.randint(1800, 7200) elif choice == 'sit': act, zone, dur = 'sit', random.choice(['書房', '沙發']), random.randint(600, 3600) elif choice == 'walk_long': act, zone, dur = 'walk', random.choice(['衣帽間', '臥室']), random.randint(300, 1800) elif choice == 'walk_short': act, zone, dur = 'walk', random.choice(['沙發', '書房']), random.randint(10, 60) else: act, zone, dur = 'stand', random.choice(['書房', '衣帽間']), random.randint(30, 300) else: # 💡 晚上也稍微降低外出機率 choice = random.choices(['sit', 'null', 'walk_short', 'stand'], weights=[50, 10, 30, 10])[0] if choice == 'sit': act, zone, dur = 'sit', '沙發', random.randint(1800, 5400) elif choice == 'null': act, zone, dur = 'null', '無人', random.randint(1800, 5400) elif choice == 'walk_short': act, zone, dur = 'walk', '臥室', random.randint(10, 60) else: act, zone, dur = 'stand', '衣帽間', random.randint(30, 300) if zone != current_zone and zone != '無人' and current_zone != '無人' and act != 'walk': return 'walk', zone, random.randint(5, 12) if act != current_act or zone != current_zone: return act, zone, dur def generate_data(): current_time = START_DATE current_act = 'null' current_zone = '無人' act_end_time = current_time last_act_log_time = None last_act_logged = None is_falling = False fall_end_time = None rescue_time = None current_room_x, current_room_y = 1.8, 1.8 # 💡【修正 2：新增漫步目標點】 wander_target_x = None wander_target_y = None with open(OUTPUT_FILE, 'w', encoding='utf-8') as f: f.write("SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;\nUSE mmwave_v4;\n\n") f.write("TRUNCATE TABLE activity_logs;\nTRUNCATE TABLE event_logs;\nTRUNCATE TABLE notifications;\n") f.write(f"UPDATE rooms SET last_activity='null', last_x=NULL, last_y=NULL, current_act_start='{START_DATE.strftime('%Y-%m-%d %H:%M:%S')}' WHERE room_id=1;\n\n") buffer = [] def flush_buffer(): if buffer: f.write("INSERT INTO activity_logs (device_id, activity_type, x_coord, y_coord, confidence, record_time) VALUES\n") f.write(",\n".join(buffer) + ";\nCOMMIT;\n") buffer.clear() print("開始生成高擬真日常資料，請稍候...") step_count = 0 while current_time <= END_DATE: step_count += 1 if step_count % 100000 == 0: print(f"已處理進度: {current_time.strftime('%Y-%m-%d %H:%M')}") flush_buffer() for ft in fall_times: if not is_falling and ft <= current_time < ft + datetime.timedelta(seconds=1): is_falling = True current_act = 'fall' current_zone = random.choices(['臥室', '沙發', '書房', '衣帽間'], weights=[60, 20, 10, 10])[0] fall_end_time = current_time + datetime.timedelta(seconds=random.randint(1, 2)) rescue_time = fall_end_time + datetime.timedelta(minutes=random.randint(10, 45)) act_end_time = rescue_time break if is_falling: if current_time >= rescue_time: is_falling = False current_act = 'transition' act_end_time = current_time + datetime.timedelta(seconds=5) elif current_time >= fall_end_time: current_act = 'lay' else: if current_time >= act_end_time: if current_act in ['sit', 'lay'] and random.random() < 0.8: current_act = 'transition' act_end_time = current_time + datetime.timedelta(seconds=random.randint(2, 5)) else: current_act, current_zone, duration = get_next_activity(current_time, current_act, current_zone) act_end_time = current_time + datetime.timedelta(seconds=duration) time_str = current_time.strftime('%Y-%m-%d %H:%M:%S') if current_act != last_act_logged and last_act_logged is not None: flush_buffer() f.write(f"UPDATE event_logs SET handled = 1, handled_by = '系統模擬' WHERE handled = 0 AND event_time <= '{time_str}';\n") for _ in range(2): if current_act == 'null': pos_x, pos_y = 'NULL', 'NULL' else: target_x, target_y = ZONES[current_zone] dx = target_x - current_room_x dy = target_y - current_room_y dist = math.hypot(dx, dy) if current_act in ['walk', 'transition', 'fall']: step = random.uniform(0.4, 0.7) if dist > step: # 直線走向目標區域 current_room_x += (dx / dist) * step current_room_y += (dy / dist) * step else: if current_act == 'walk': # 💡【修正 3：到達區域後，設定隨機目標點直線來回走】 if wander_target_x is None or math.hypot(wander_target_x - current_room_x, wander_target_y - current_room_y) < 0.5: # 在房間內隨機挑一個點當作目標 wander_target_x = random.uniform(0.5, 3.1) wander_target_y = random.uniform(0.5, 3.1) dx_w = wander_target_x - current_room_x dy_w = wander_target_y - current_room_y dist_w = math.hypot(dx_w, dy_w) # 朝著隨機目標點「直線」走過去，保證 5 秒的平均點不會被抵銷 current_room_x += (dx_w / dist_w) * step current_room_y += (dy_w / dist_w) * step else: current_room_x = target_x + random.uniform(-0.15, 0.15) current_room_y = target_y + random.uniform(-0.15, 0.15) else: current_room_x += random.uniform(-0.02, 0.02) current_room_y += random.uniform(-0.02, 0.02) current_room_x = current_room_x * 0.95 + target_x * 0.05 current_room_y = current_room_y * 0.95 + target_y * 0.05 current_room_x = max(0.1, min(3.5, current_room_x)) current_room_y = max(0.1, min(3.5, current_room_y)) pos_x, pos_y = room_to_radar(current_room_x, current_room_y) conf = round(random.uniform(0.8, 0.99), 2) if current_act != 'null' else 0 buffer.append(f"(2, NULL, {pos_x}, {pos_y}, {conf}, '{time_str}')") need_act_log = False if current_act != last_act_logged: need_act_log = True wander_target_x = None # 動作改變時，清除漫步目標點 elif last_act_log_time and (current_time - last_act_log_time).total_seconds() >= 5.0: need_act_log = True if need_act_log: conf_act = round(random.uniform(0.85, 0.99), 2) if current_act != 'null' else 0 buffer.append(f"(1, '{current_act}', NULL, NULL, {conf_act}, '{time_str}')") last_act_logged = current_act last_act_log_time = current_time if len(buffer) >= 5000: flush_buffer() current_time += datetime.timedelta(seconds=1) flush_buffer() sql_magic = """ INSERT INTO notifications (event_id, user_id, sent_time) SELECT e.event_id, u.user_id, DATE_ADD(e.event_time, INTERVAL 1 SECOND) FROM event_logs e JOIN users u ON u.is_active = 1 AND u.notify_enabled = 1 AND u.line_user_id IS NOT NULL; UPDATE event_logs SET handled = 1, handled_by = (SELECT name FROM users WHERE is_active = 1 AND name IS NOT NULL ORDER BY RAND() LIMIT 1) WHERE event_id > 0; UPDATE event_logs SET handled = 0, handled_by = NULL ORDER BY event_id DESC LIMIT 3; """ f.write(sql_magic + "\nCOMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;\n") print(f"✅ 高擬真資料生成完成！請匯入 {OUTPUT_FILE} 至資料庫") if __name__ == "__main__": generate_data()