import pymysql
from werkzeug.security import check_password_hash
from werkzeug.security import generate_password_hash
from datetime import datetime, timedelta
import pytz
import bcrypt
import uuid
import hashlib
import math

# 定義台北時區
tw_tz = pytz.timezone('Asia/Taipei')
def get_now():
    return datetime.now(tw_tz)

def get_db_connection():
    return pymysql.connect(
        host='localhost', 
        user='webuser', 
        password='U1224006',
        database='mmwave_v4',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor,
        init_command='SET time_zone = "+08:00"'         # 強制連線時區為台灣
    )

# 304
def get_zone_name(x_str, y_str):
    """
    根據 304 教室的座標判斷所在區塊。
    將右下角雷達的 (x, y) 轉換為以「左下角」為原點的絕對座標。
    """
    if x_str in [None, '---', '', 'NULL', 'nan'] or y_str in [None, '---', '', 'NULL', 'nan']:
        return "無人"
    try:
        x_rad = float(x_str)
        y_rad = float(y_str)
        
        # 座標轉換數學 (旋轉與平移)
        # 雷達在右下角 (3.6, 0)，Y軸朝向左上角(135度)，X軸朝向右上角(45度)
        cos45 = math.sqrt(2) / 2  # 約 0.7071
        
        # 計算在房間裡的絕對座標 (以左下角為原點)
        room_x = 3.6 + (x_rad - y_rad) * cos45
        room_y = (x_rad + y_rad) * cos45
        
        # ====== 套用 304 教室的區塊配置 ======
        # (0~1.8, 0~3.0) 臥室
        # (0~1.8, 3.0~3.6) 窗邊
        # (1.8~3.6, 0~1.8) 書房
        # (1.8~3.6, 1.8~3.6) 衣帽間
        
        if room_x < 0 or room_x > 3.6 or room_y < 0 or room_y > 3.6:
            return "無人"
        
        # if room_x <= 1.8:
        #     if room_y <= 3.0:
        #         return "臥室"
        #     else:
        #         return "窗邊"
        # else:
        #     if room_y <= 1.8:
        #         return "書房"
        #     else:
        #         return "衣帽間"
        
        # ====== 套用lab配置 ======
        # (0~1.2, 0~0.6) 沙發
        # (1.8~3.6, 0~1.8) 書房
        # (1.8~3.6, 1.8~3.6) 衣帽間
        # 其他區域 臥室
        
        if 0 <= room_x <= 1.2 and 0 <= room_y <= 0.6:
            return "沙發"
        elif 1.8 <= room_x <= 3.6 and 0 <= room_y <= 1.8:
            return "書房"
        elif 1.8 <= room_x <= 3.6 and 1.8 < room_y <= 3.6:
            return "衣帽間"
        else:
            return "臥室"
                
    except Exception as e:
        print(f"區塊判斷錯誤: {e}")
        return "無人"

def verify_user(username, password):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            sql = "SELECT * FROM users WHERE username = %s AND is_active = 1"
            cursor.execute(sql, (username,))
            user = cursor.fetchone()

            if user:
                stored_hash = user['password_hash']
                is_valid = False

                # 情況 A：如果是 PHP 的舊格式 ($2y$ 或 $2b$)，手動用 bcrypt 驗證
                if stored_hash.startswith('$2y$'):
                    # Python 的 bcrypt 函式庫不支援 $2y$，必須換成 $2b$
                    adjusted_hash = stored_hash.replace('$2y$', '$2b$', 1)
                    try:
                        # 確保密碼和雜湊都是 bytes
                        is_valid = bcrypt.checkpw(
                            password.encode('utf-8'), 
                            adjusted_hash.encode('utf-8')
                        )
                    except Exception as e:
                        print(f"Bcrypt ($2y$) 驗證失敗: {e}")
                
                elif stored_hash.startswith('$2'):
                    try:
                        is_valid = bcrypt.checkpw(
                            password.encode('utf-8'), 
                            stored_hash.encode('utf-8')
                        )
                    except Exception as e:
                        print(f"Bcrypt 驗證失敗: {e}")
                
                # 情況 B：直接交給 Flask 的 check_password_hash 處理
                else:
                    is_valid = check_password_hash(stored_hash, password)

                if is_valid:
                    update_sql = "UPDATE users SET last_login = NOW() WHERE user_id = %s"
                    cursor.execute(update_sql, (user['user_id'],))
                    db.commit()
                    return user
            
            return None
    except Exception as e:
        print(f"驗證程序出錯: {e}")
        return None
    finally:
        db.close()

def create_user(username, password, name, role):
    # 限制：只能新增 family, caregiver 角色
    if role not in ['family', 'caregiver']:
        return False, "非法角色權限"

    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            # 1. 檢查帳號是否已存在
            cursor.execute("SELECT user_id FROM users WHERE username = %s", (username,))
            if cursor.fetchone():
                return False, "帳號已存在"

            # 2. 加密密碼
            hashed_pw = generate_password_hash(password)

            # 3. 寫入資料庫
            sql = """INSERT INTO users (username, password_hash, name, role, is_active, created_at) 
                     VALUES (%s, %s, %s, %s, 1, NOW())"""
            cursor.execute(sql, (username, hashed_pw, name, role))
            db.commit()
            return True, "使用者建立成功"
    except Exception as e:
        print(f"資料庫註冊錯誤: {e}")
        return False, "資料庫寫入失敗"
    finally:
        db.close()

def resolve_all_events(name):
    """一鍵將所有未處理的警告標記為已處理，並記錄處理人"""
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            cursor.execute("""
                UPDATE event_logs 
                SET handled = 1, handled_by = %s 
                WHERE handled = 0
            """, (name,))
            db.commit()
            return True
    except Exception as e:
        print(f"解除警告失敗: {e}")
        return False
    finally:
        db.close()

def get_current_ui_state(line_id=None):
    db = None
    try:
        db = get_db_connection()
        with db.cursor() as cursor:
            if line_id:
                cursor.execute("SELECT name FROM users WHERE line_user_id = %s", (line_id,))
                if not cursor.fetchone():
                    return {'title': '未綁定', 'color': '#6c757d', 'icon': '📱', 'desc': '請輸入 bind [代碼]'}

            # 抓取 device_id = 2 最新一筆座標
            cursor.execute("""
                SELECT e.event_type, e.event_time, 
                       (SELECT x_coord FROM activity_logs a 
                        WHERE a.device_id = 2 
                          AND a.record_time <= e.event_time 
                        ORDER BY a.record_time DESC LIMIT 1) as x_coord,
                       (SELECT y_coord FROM activity_logs a 
                        WHERE a.device_id = 2 
                          AND a.record_time <= e.event_time 
                        ORDER BY a.record_time DESC LIMIT 1) as y_coord
                FROM event_logs e
                WHERE e.handled = 0 
                ORDER BY e.event_time DESC LIMIT 1
            """)
            latest_unhandled = cursor.fetchone()

            cursor.execute("""
                SELECT event_type, event_time, handled_by 
                FROM event_logs 
                WHERE handled = 1 ORDER BY event_time DESC LIMIT 1
            """)
            last_handled = cursor.fetchone()

            cursor.execute("SELECT * FROM rooms WHERE room_id = 1")
            room_snap = cursor.fetchone()

            current_x = room_snap['last_x'] if room_snap else None
            current_y = room_snap['last_y'] if room_snap else None
            current_zone = get_zone_name(current_x, current_y)

            state = {
                'title': '連線中', 'color': '#6c757d', 'icon': '📡',
                'desc': '等待雷達數據...', 
                'last_activity_time': '---',
                'last_position_time': '---',
                'x': current_x if current_x is not None else '---',
                'y': current_y if current_y is not None else '---',
                'zone': current_zone,
                'has_alert': bool(latest_unhandled),
                'alert_desc': '',
                'safe_desc': "<div style='font-size: 1.1rem; font-weight: bold; color: #27ae60; margin-bottom: 5px;'>✅ 目前安全</div>尚無歷史警報紀錄"
            }

            event_name_map = {'fall': '跌倒', 'no_move': '異常靜止', 'no_home': '外出'}

            if latest_unhandled:
                e_type = event_name_map.get(latest_unhandled['event_type'], latest_unhandled['event_type'])
                e_time = latest_unhandled['event_time'].strftime('%m/%d %H:%M:%S')
                
                if latest_unhandled['event_type'] == 'no_home':
                    event_zone = "外出"
                else:
                    # 傳入最新座標，交給 Python 判斷
                    event_x = latest_unhandled['x_coord']
                    event_y = latest_unhandled['y_coord']
                    event_zone = get_zone_name(event_x, event_y)

                state['alert_desc'] = (
                    f"<div style='font-size: 1.1rem; font-weight: bold; color: #e74c3c; margin-bottom: 5px;'>"
                    f"⚠️ 發生警告事件</div>"
                    f"系統於 <span class='time-badge'>{e_time}</span> 偵測到 <span class='danger-text'>【{e_type}】</span> 事件！<br>"
                    f"📍 發生位置：<b>{event_zone}</b>"
                )

            if last_handled:
                handler = last_handled['handled_by'] or '未知家屬'
                h_event_time = last_handled['event_time'].strftime('%m/%d %H:%M')
                e_type = event_name_map.get(last_handled['event_type'], last_handled['event_type'])
                state['safe_desc'] = (
                    f"<div style='font-size: 1.1rem; font-weight: bold; color: #27ae60; margin-bottom: 5px;'>"
                    f"✅ 目前安全</div>"
                    f"發生於 <span class='time-badge'>{h_event_time}</span> 的【{e_type}】警報，已由 <b>{handler}</b> 確認安全。"
                )

            if room_snap:
                a_time = room_snap['last_seen_activity']
                p_time = room_snap['last_seen_position']
                state['last_activity_time'] = a_time.strftime('%Y/%m/%d %H:%M:%S') if a_time else '---'
                state['last_position_time'] = p_time.strftime('%Y/%m/%d %H:%M:%S') if p_time else '---'
                act_map = {
                    'walk': {'title': '行走中', 'color': '#28a745', 'icon': '🏃', 'desc': '正在走動中'},
                    'stand': {'title': '站立', 'color': '#17a2b8', 'icon': '🧍', 'desc': '目前處於站立狀態'},
                    'sit': {'title': '坐著', 'color': '#ffc107', 'icon': '🪑', 'desc': '正在休息/坐著'},
                    'transition': {'title': '轉換動作', 'color': '#fd7e14', 'icon': '🔄', 'desc': '動作轉換中'},
                    'lay': {'title': '躺著', 'color': '#6f42c1', 'icon': '🛌', 'desc': '目前處於躺臥狀態'},
                    'fall': {'title': '跌倒狀態', 'color': '#dc3545', 'icon': '🚨', 'desc': '跌倒中'},
                    'null': {'title': '外出', 'color': '#adb5bd', 'icon': '🚪', 'desc': '範圍內無人活動'}
                }
                state.update(act_map.get(room_snap['last_activity'], act_map['null']))
                state['raw_activity'] = room_snap['last_activity'] if room_snap else 'null'

                # 新增：跌倒後倒地不起的 UI 覆寫邏輯
                # 如果現在判定是 lay(躺下) 或 null(無微動)，且身上背著未處理的 fall 警報
                if state['raw_activity'] in ['lay', 'null'] and latest_unhandled and latest_unhandled['event_type'] in ['fall', 'fall_unresponsive']:
                    
                    fall_time = latest_unhandled['event_time']
                    
                    # 🔍 核心修正：移除錯誤的 device_id 限制，讓系統能正確看到動作雷達的數據！
                    # 去資料庫查：跌倒之後，到現在為止，有沒有出現過 行走、站立、坐著 等起身動作？
                    cursor.execute("""
                        SELECT log_id FROM activity_logs 
                        WHERE record_time > %s 
                          AND activity_type IN ('walk', 'stand', 'sit')
                        LIMIT 1
                    """, (fall_time,))
                    
                    has_recovered = cursor.fetchone()
                    
                    # 只有在「跌倒後完全沒有起身過」的狀況下，才顯示恐怖的倒地不起
                    if not has_recovered:
                        state['title'] = '跌倒後倒地不起'
                        state['color'] = '#8b0000' # 深紅色警告
                        state['icon'] = '🆘'
                        state['desc'] = '極度危險：偵測到跌倒後失去行動能力！'
                    # 如果有起身過 (has_recovered 為 True)，則什麼都不做，讓它正常顯示「外出」或「躺著」
                    
            return state
    except Exception as e:
        print(f"資料庫讀取錯誤: {e}")
        return {'title': '系統錯誤', 'color': 'red', 'icon': '❌', 'desc': str(e)}
    finally:
        if db: db.close()

# 更新統計資料：針對 6 類動作
def get_daily_summary_data(start_date, end_date):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            # 加入 device_id = 1 條件，只統計動作雷達的資料
            cursor.execute("""
                SELECT activity_type, COUNT(*) as count 
                FROM activity_logs 
                WHERE DATE(record_time) BETWEEN %s AND %s 
                  AND device_id = 1 
                  AND activity_type IS NOT NULL
                GROUP BY activity_type
            """, (start_date, end_date))
            
            rows = cursor.fetchall()
            
            stats = {row['activity_type']: row['count'] for row in rows}
                
            return {"stats": stats, "total": sum(stats.values())}
    finally:
        db.close()

def get_statistics_data(start_date, end_date):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            weight_map = {
                'walk': 6, 
                'stand': 5, 
                'sit': 4, 
                'lay': 3, 
                'fall': 2, 
                'transition': 1, 
                'null': 0
            }
            
            start_dt = f"{start_date[:10]} 00:00:00"
            end_dt = f"{end_date[:10]} 23:59:59"

            # 使用 DATE_FORMAT 壓縮至分鐘級別
            sql_logs = """
                SELECT 
                    DATE_FORMAT(record_time, '%%Y-%%m-%%d %%H:%%i:00') as minute_time, 
                    activity_type 
                FROM activity_logs 
                WHERE device_id = 1 
                  AND record_time >= %s AND record_time <= %s 
                  AND activity_type IS NOT NULL
                GROUP BY minute_time, activity_type
                ORDER BY minute_time ASC
            """
            cursor.execute(sql_logs, (start_dt, end_dt))
            log_rows = cursor.fetchall()

            timeline_data = []
            prev_act = None
            
            for row in log_rows:
                act_type = row['activity_type']
                if act_type != prev_act:
                    timeline_data.append({
                        'x': row['minute_time'], 
                        'y': weight_map.get(act_type, 0),
                        'label': act_type
                    })
                    prev_act = act_type
            
            if log_rows and log_rows[-1]['activity_type'] == prev_act:
                timeline_data.append({
                    'x': log_rows[-1]['minute_time'], 
                    'y': weight_map.get(prev_act, 0),
                    'label': prev_act
                })

            # 抓取告警事件
            sql_events = """
                SELECT event_time as x, event_type 
                FROM event_logs 
                WHERE event_time >= %s AND event_time <= %s 
                ORDER BY event_time ASC
            """
            cursor.execute(sql_events, (start_dt, end_dt))
            event_rows = cursor.fetchall()

            events = {'fall': [], 'no_move': [], 'no_home': []}
            for er in event_rows:
                e_type = er['event_type']
                if e_type in events:
                    events[e_type].append({
                        'x': er['x'].strftime('%Y-%m-%d %H:%M:%S'),
                        'y': 5.5
                    })
            return timeline_data, events
    finally:
        db.close()

def check_timed_alerts(device_id, current_activity):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            # 1. 抓取設定值
            # 只抓取警報閾值，避開 '06:00' 這種時間字串，防止 int() 轉換崩潰
            cursor.execute("""
                SELECT setting_key, setting_value 
                FROM system_settings 
                WHERE setting_key IN ('threshold_no_home_min', 'threshold_no_move_min')
            """)
            settings = {row['setting_key']: int(row['setting_value']) for row in cursor.fetchall()}
            
            # 2. 抓取該裝置最後一筆「不同於現在」的動作時間 (找出狀態持續多久了)
            # 邏輯：找最近一筆 activity_type != current_activity 的紀錄
            sql_last_change = """
                SELECT record_time FROM activity_logs 
                WHERE device_id = %s AND activity_type != %s 
                ORDER BY record_time DESC LIMIT 1
            """
            cursor.execute(sql_last_change, (device_id, current_activity))
            last_change = cursor.fetchone()
            
            if not last_change: return # 剛啟動系統，資料不足

            # 計算持續時間 (分鐘)
            duration_min = (datetime.now() - last_change['record_time']).total_seconds() / 60
            # 3. 判斷邏輯
            alert_type = None
            
            # 情況 A：no_home (連續 null 超過閾值)
            if current_activity == 'null' and settings['threshold_no_home_min'] > 0 and duration_min >= settings['threshold_no_home_min']:
                alert_type = 'no_home'
                
            # 情況 B：no_move (連續 stand 或 sit 超過閾值)
            # 你提到的 lay 算睡覺不算，walk/fall 算動，所以只抓 stand/sit
            elif current_activity in ['stand', 'sit'] and settings['threshold_no_move_min'] > 0 and duration_min >= settings['threshold_no_move_min']:
                alert_type = 'no_move'

            # 情況 C - 跌倒後無反應
            if current_activity in ['lay', 'null']:
                # 1.先去資料庫看，是不是「已經」傳過 fall_unresponsive 了？
                cursor.execute("""
                    SELECT event_id FROM event_logs 
                    WHERE device_id = %s AND event_type = 'fall_unresponsive' AND handled = 0
                """, (device_id,))
                
                # 如果找不到 (代表還沒傳過)，我們才開始算 3 分鐘
                if not cursor.fetchone():
                    # 找未處理的跌倒警報
                    cursor.execute("""
                        SELECT event_time FROM event_logs 
                        WHERE device_id = %s AND event_type = 'fall' AND handled = 0
                        ORDER BY event_time DESC LIMIT 1
                    """, (device_id,))
                    unhandled_fall = cursor.fetchone()
                    
                    if unhandled_fall:
                        fall_time = unhandled_fall['event_time']
                        fall_duration_min = (datetime.now() - fall_time).total_seconds() / 60
                        
                        if fall_duration_min >= 3.0:
                            # 2.跌倒之後到現在，有沒有任何「起身」的動作？
                            cursor.execute("""
                                SELECT log_id FROM activity_logs 
                                WHERE record_time > %s 
                                  AND activity_type IN ('walk', 'stand', 'sit')
                                LIMIT 1
                            """, (fall_time,))
                            
                            has_recovered = cursor.fetchone()
                            
                            # 只有「真的沒起身過」，才賦予升級警報
                            if not has_recovered:
                                alert_type = 'fall_unresponsive'

            # 4. 超過 5 筆紀錄（代表長輩真的回來待了一段時間）：系統才判定狀態真正改變，允許下一次的外出、異常靜止警報。
            if alert_type:
                already_fired = False
                
                # 針對「狀態持續型」警報 (外出、異常靜止)
                if alert_type in ['no_home', 'no_move']:
                    # 1. 找最後一次發送此警報的時間
                    cursor.execute("""
                        SELECT event_time FROM event_logs 
                        WHERE device_id = %s AND event_type = %s 
                        ORDER BY event_time DESC LIMIT 1
                    """, (device_id, alert_type))
                    last_alert = cursor.fetchone()
                    
                    if last_alert:
                        # 2. 檢查自從上次警報後，長輩有沒有「真的回來過 / 動過」？
                        if alert_type == 'no_home':
                            # 外出後回來，可能產生走、站、坐、躺的紀錄
                            cursor.execute("""
                                SELECT COUNT(*) as act_count FROM activity_logs 
                                WHERE device_id = %s AND record_time > %s 
                                  AND activity_type IN ('walk', 'stand', 'sit', 'lay')
                            """, (device_id, last_alert['event_time']))
                        else:
                            # 異常靜止後要解除，一定要有「走動(walk)」的紀錄
                            cursor.execute("""
                                SELECT COUNT(*) as act_count FROM activity_logs 
                                WHERE device_id = %s AND record_time > %s 
                                  AND activity_type = 'walk'
                            """, (device_id, last_alert['event_time']))
                            
                        act_data = cursor.fetchone()
                        
                        # 💡 核心濾波器：如果正常活動少於 5 筆，當作雷達短暫雜訊，阻止重複發送
                        if act_data['act_count'] < 5:
                            already_fired = True
                            
                # 針對「事件觸發型」警報 (跌倒後無反應)
                else:
                    cursor.execute("""
                        SELECT event_id FROM event_logs 
                        WHERE device_id = %s AND event_type = %s AND handled = 0
                    """, (device_id, alert_type))
                    if cursor.fetchone():
                        already_fired = True

                # 確定沒發送過，才真正寫入並推播
                if not already_fired:
                    try:
                        cursor.execute("INSERT INTO event_logs (device_id, event_type, handled) VALUES (%s, %s, 0)", 
                                     (device_id, alert_type))
                        db.commit()
                        return alert_type 
                    except Exception as db_err:
                        print(f"寫入資料庫失敗: {db_err}")
                        return None
            
            return None
    finally:
        db.close()

def get_system_settings():
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            cursor.execute("SELECT setting_key, setting_value FROM system_settings")
            # 轉成 dict 方便前端使用: {'threshold_no_move_min': '10', ...}
            return {row['setting_key']: row['setting_value'] for row in cursor.fetchall()}
    finally:
        db.close()

def update_system_settings(key, value):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            sql = "UPDATE system_settings SET setting_value = %s WHERE setting_key = %s"
            cursor.execute(sql, (value, key))
            db.commit()
    finally:
        db.close()

def get_events(page=1, per_page=10, event_type='', start_date='', end_date=''):
    db = None
    try:
        db = get_db_connection()
        with db.cursor() as cursor:
            query_conditions = []
            params = []
            
            if event_type:
                query_conditions.append("e.event_type = %s")
                params.append(event_type)
            if start_date:
                query_conditions.append("DATE(e.event_time) >= %s")
                params.append(start_date)
            if end_date:
                query_conditions.append("DATE(e.event_time) <= %s")
                params.append(end_date)
                
            where_clause = ""
            if query_conditions:
                where_clause = "WHERE " + " AND ".join(query_conditions)
            
            count_query = f"SELECT COUNT(*) as total FROM event_logs e {where_clause}"
            cursor.execute(count_query, params)
            total_count = cursor.fetchone()['total']
            
            offset = (page - 1) * per_page
            
            # 拔除拖慢效能的雙重關聯子查詢，改用 LEFT JOIN 直接拿設備名稱
            sql = f"""
                SELECT e.event_id, e.device_id, e.event_type, e.event_time, e.handled, e.handled_by,
                       d.device_name
                FROM event_logs e
                LEFT JOIN radar_devices d ON e.device_id = d.device_id
                {where_clause}
                ORDER BY e.event_time DESC
                LIMIT %s OFFSET %s
            """
            
            paginated_params = params + [per_page, offset]
            cursor.execute(sql, paginated_params)
            events = cursor.fetchall()
            
            # 在 Python 裡「按需」抓取座標，保證每一頁最多只做 10 次極速查詢
            for e in events:
                cursor.execute("""
                    SELECT x_coord, y_coord FROM activity_logs 
                    WHERE device_id = 2 AND record_time <= %s AND x_coord IS NOT NULL 
                    ORDER BY record_time DESC LIMIT 1
                """, (e['event_time'],))
                coord = cursor.fetchone()
                
                if coord:
                    e['x_coord'] = coord['x_coord']
                    e['y_coord'] = coord['y_coord']
                    if e['event_type'] == 'no_home':
                        e['zone'] = "外出"
                    else:
                        e['zone'] = get_zone_name(e['x_coord'], e['y_coord'])
                else:
                    e['x_coord'] = '---'
                    e['y_coord'] = '---'
                    e['zone'] = "未知"
                    
            return events, total_count
            
    except Exception as err:
        print(f"取得歷史紀錄失敗: {err}")
        return [], 0
    finally:
        if db: db.close()
        
def get_event_trajectory(event_id):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            # 1. 取得事件發生時間
            cursor.execute("SELECT event_time FROM event_logs WHERE event_id = %s", (event_id,))
            event = cursor.fetchone()
            if not event: return []
            
            event_time = event['event_time']
            start_time = event_time - timedelta(seconds=5)
            
            # 2. 撈取該時段內的座標資料 (轉為相對坐標回傳)
            cursor.execute("""
                SELECT x_coord, y_coord 
                FROM activity_logs 
                WHERE device_id = 2 
                  AND x_coord IS NOT NULL 
                  AND record_time BETWEEN %s AND %s
                ORDER BY record_time ASC
            """, (start_time, event_time))
            
            rows = cursor.fetchall()
            trajectory = []
            cos45 = math.sqrt(2) / 2
            
            for row in rows:
                try:
                    x_rad = float(row['x_coord'])
                    y_rad = float(row['y_coord'])
                    # 套用與 get_zone_name 一致的旋轉公式
                    room_x = 3.6 + (x_rad - y_rad) * cos45
                    room_y = (x_rad + y_rad) * cos45
                    trajectory.append({'x': room_x, 'y': room_y})
                except:
                    pass
                    
            return trajectory
    except Exception as e:
        print(f"撈取軌跡失敗: {e}")
        return []
    finally:
        db.close()

def get_spatial_statistics(start_date, end_date):
    """取得空間與作息的進階分析數據"""
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            cursor.execute("SELECT setting_key, setting_value FROM system_settings")
            settings = {row['setting_key']: row['setting_value'] for row in cursor.fetchall()}
            n_start = settings.get('night_start_time', '00:00')
            n_end = settings.get('night_end_time', '06:00')

            start_dt = f"{start_date[:10]} 00:00:00"
            end_dt = f"{end_date[:10]} 23:59:59"

            # 💡 動態區域設定
            AVAILABLE_ZONES = ["沙發", "書房", "衣帽間", "臥室"] # map_lab
            # AVAILABLE_ZONES = ["臥室", "窗邊", "書房", "衣帽間"]  # map_304
            dwell_counts = {zone: 0 for zone in AVAILABLE_ZONES}
            hotspot_data = {zone: 0 for zone in AVAILABLE_ZONES}

            # 1. 🕒 區域停留時間 (1/10 高速抽樣統計)
            cursor.execute("""
                SELECT ROUND(x_coord, 1) as rx, ROUND(y_coord, 1) as ry, COUNT(*) as c
                FROM activity_logs
                WHERE device_id = 2 AND x_coord IS NOT NULL 
                  AND record_time >= %s AND record_time <= %s
                  AND MOD(SECOND(record_time), 10) = 0
                GROUP BY ROUND(x_coord, 1), ROUND(y_coord, 1)
            """, (start_dt, end_dt))
            
            for row in cursor.fetchall():
                zone = get_zone_name(row['rx'], row['ry'])
                if zone in dwell_counts:
                    dwell_counts[zone] += row['c']
            dwell_data = {zone: round(count / 12.0, 1) for zone, count in dwell_counts.items()}

            # 2. 🚨 危險事件熱區分佈
            cursor.execute("""
                SELECT event_time, event_type FROM event_logs
                WHERE event_type != 'no_home' AND event_time >= %s AND event_time <= %s
            """, (start_dt, end_dt))
            
            for ev in cursor.fetchall():
                cursor.execute("""
                    SELECT x_coord, y_coord FROM activity_logs 
                    WHERE device_id = 2 AND record_time <= %s AND x_coord IS NOT NULL 
                    ORDER BY record_time DESC LIMIT 1
                """, (ev['event_time'],))
                coord = cursor.fetchone()
                if coord:
                    zone = get_zone_name(coord['x_coord'], coord['y_coord'])
                    if zone in hotspot_data:
                        hotspot_data[zone] += 1

            # ==========================================
            # 3. 🏃‍♂️ 每日總移動距離 (高速順向索引流 + 物理距離濾波優化)
            # ==========================================
            cursor.execute("""
                SELECT 
                    record_time as rec_time, 
                    AVG(x_coord) as x, 
                    AVG(y_coord) as y
                FROM activity_logs
                WHERE device_id = 2 AND x_coord IS NOT NULL 
                  AND record_time >= %s AND record_time <= %s
                  AND MOD(SECOND(record_time), 5) = 0
                GROUP BY record_time
            """, (start_dt, end_dt))
            
            daily_dist = {}
            prev_x, prev_y, prev_time = None, None, None
            for row in cursor.fetchall():
                curr_time = row['rec_time']
                date_str = curr_time.strftime('%m/%d')
                if date_str not in daily_dist:
                    daily_dist[date_str] = 0.0

                if prev_x is not None:
                    time_diff = (curr_time - prev_time).total_seconds()
                    # 超過 5 分鐘不連線 (代表中間無人、外出或斷開)
                    if time_diff <= 300: 
                        dist = math.sqrt((row['x'] - prev_x)**2 + (row['y'] - prev_y)**2)
                        
                        # 💡 將下限調高至 0.45 公尺 (45公分)。
                        # 濾除 5 秒採樣間定點晃動、呼吸所產生的細碎幽靈雜訊，只累加真正的跨步位移。
                        if 0.45 < dist < 5.0: 
                            daily_dist[date_str] += dist
                            
                prev_x, prev_y, prev_time = row['x'], row['y'], curr_time

            distance_data = {
                'labels': list(daily_dist.keys()),
                'data': [round(d, 1) for d in daily_dist.values()]
            }

            # 4. 🌙 夜間活動頻率 (高速 Index-Only 掃描)
            cursor.execute("""
                SELECT record_time 
                FROM activity_logs
                WHERE device_id = 1 
                  AND activity_type IN ('walk', 'stand', 'sit', 'fall') 
                  AND record_time >= %s AND record_time <= %s
            """, (start_dt, end_dt))
            
            n_s = int(n_start.replace(':', ''))
            n_e = int(n_end.replace(':', ''))
            active_night_minutes = set()
            
            for row in cursor.fetchall():
                dt_obj = row['record_time']
                time_val = dt_obj.hour * 100 + dt_obj.minute
                
                is_night = False
                if n_s <= n_e:
                    if n_s <= time_val <= n_e: is_night = True
                else:
                    if time_val >= n_s or time_val <= n_e: is_night = True
                
                if is_night:
                    md_date = dt_obj.strftime('%m/%d')
                    active_night_minutes.add((md_date, time_val))

            night_counts = {}
            for md_date, _ in active_night_minutes:
                night_counts[md_date] = night_counts.get(md_date, 0) + 1

            night_data = {k: float(v) for k, v in night_counts.items()}

            return {
                'dwell_data': dwell_data,
                'hotspot_data': hotspot_data,
                'distance_data': distance_data,
                'night_data': night_data
            }
    except Exception as e:
        print(f"空間統計計算失敗: {e}")
        return {'dwell_data': {}, 'hotspot_data': {}, 'distance_data': {'labels': [], 'data': []}, 'night_data': {}}
    finally:
        db.close()

# 獲取所有裝置清單
def get_all_devices():
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            sql = """
                SELECT d.*, r.room_name 
                FROM radar_devices d
                JOIN rooms r ON d.room_id = r.room_id
            """
            cursor.execute(sql)
            return cursor.fetchall()
    finally:
        db.close()

# 新增裝置
def add_device(name, location):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            sql = "INSERT INTO radar_devices (device_name, location, status) VALUES (%s, %s, 'active')"
            print(f"正在執行 SQL: {sql} 參數: {name}, {location}") # 除錯用
            cursor.execute(sql, (name, location))
            db.commit()
            return True
    except Exception as e:
        print(f"資料庫新增失敗原因: {e}")
        return False
    finally:
        db.close()

def get_user_bind_info(user_id):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            # 1. 取得基本資料
            cursor.execute("SELECT username, line_user_id, bind_code FROM users WHERE user_id=%s", (user_id,))
            user = cursor.fetchone()
            
            if not user:
                return None

            # 2. 如果沒綁定過且沒綁定碼，生一組新的
            if not user['line_user_id'] and not user['bind_code']:
                new_code = hashlib.md5(str(uuid.uuid4()).encode()).hexdigest().upper()[:6]
                cursor.execute("UPDATE users SET bind_code=%s WHERE user_id=%s", (new_code, user_id))
                db.commit()
                user['bind_code'] = new_code

            # 3. 查詢該 LINE ID 已經綁定了哪些帳號 (支援一對多)
            bound_list = []
            if user['line_user_id']:
                cursor.execute("SELECT username, bind_code FROM users WHERE line_user_id=%s", (user['line_user_id'],))
                bound_list = cursor.fetchall()

            return user, bound_list
    finally:
        db.close()

def get_user_by_line_id(line_id):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            cursor.execute("SELECT * FROM users WHERE line_user_id = %s LIMIT 1", (line_id,))
            return cursor.fetchone()
    finally:
        db.close()

# =====line==========================================================
def bind_line_user(bind_code, line_id):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            # 檢查 bind_code 是否存在且尚未被綁定
            cursor.execute("SELECT user_id, name, line_user_id FROM users WHERE bind_code = %s", (bind_code,))
            user = cursor.fetchone()
            if not user: return False, "❌ 找不到此綁定碼。"
            if user['line_user_id']: return False, "⚠️ 此代碼已被綁定。"
            
            cursor.execute("UPDATE users SET line_user_id = %s WHERE bind_code = %s", (line_id, bind_code))
            db.commit()
            return True, f"✅ 綁定成功！\n使用者：{user['name']}"
    finally:
        db.close()

def unbind_line_user(line_id, bind_code):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            # 1. 檢查該 line_id 是否真的擁有這個 bind_code
            sql_check = "SELECT user_id FROM users WHERE line_user_id = %s AND bind_code = %s"
            cursor.execute(sql_check, (line_id, bind_code))
            if not cursor.fetchone():
                return False, "❌ 找不到該綁定紀錄，或該帳號不屬於您。"

            # 2. 執行解綁 (將該使用者的 line_user_id 設為 NULL)
            sql_unbind = "UPDATE users SET line_user_id = NULL WHERE line_user_id = %s AND bind_code = %s"
            cursor.execute(sql_unbind, (line_id, bind_code))
            db.commit()
            return True, f"✅ 已成功解除綁定碼 [{bind_code}] 的連動。"
    except Exception as e:
        print(f"解綁出錯: {e}")
        return False, "❌ 系統錯誤，請稍後再試。"
    finally:
        db.close()

# ===================================================================================
# # 自動發送 LINE 通知: 接收事件 -> 寫入資料庫 -> 找出對應的人 -> 發送 LINE 推播 -> 記錄通知
# def record_event_and_push(device_id, event_type):
#     """處理事件紀錄並啟動推播邏輯"""
#     db = get_db_connection()
#     try:
#         with db.cursor() as cursor:
#             # 1. 寫入事件
#             sql_event = """INSERT INTO event_logs (device_id, event_type, event_time, handled)
#                            VALUES (%s, %s, NOW(), 0)"""
#             cursor.execute(sql_event, (device_id, event_type))
#             event_id = cursor.lastrowid
            
#             # 2. 獲取被照護者名字 (role='admin')
#             cursor.execute("SELECT name FROM users WHERE role = 'admin' LIMIT 1")
#             owner = cursor.fetchone()
#             owner_name = owner['name'] if owner else "被照護者"

#             # 3. 找出所有綁定 LINE 的活動使用者
#             cursor.execute("SELECT user_id, name, role, line_user_id FROM users WHERE line_user_id IS NOT NULL AND is_active = 1")
#             recipients = cursor.fetchall()
            
#             # 4. 獲取裝置名稱
#             cursor.execute("SELECT device_name FROM radar_devices WHERE device_id = %s", (device_id,))
#             device = cursor.fetchone()
#             device_name = device['device_name'] if device else "未知裝置"

#             db.commit()
#             return event_id, owner_name, device_name, recipients
#     finally:
#         db.close()

# 自動發送 LINE 通知: 接收事件 -> 寫入資料庫 -> 找出對應的人 -> 發送 LINE 推播 -> 記錄通知
def record_event_and_push(device_id, event_type):
    """處理事件紀錄並啟動推播邏輯"""
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            # 1. 寫入事件
            sql_event = """INSERT INTO event_logs (device_id, event_type, event_time, handled)
                           VALUES (%s, %s, NOW(), 0)"""
            cursor.execute(sql_event, (device_id, event_type))
            event_id = cursor.lastrowid
            
            # 2. 獲取被照護者名字 (role='admin')
            cursor.execute("SELECT name FROM users WHERE role = 'admin' LIMIT 1")
            owner = cursor.fetchone()
            owner_name = owner['name'] if owner else "被照護者"

            # 3. 🚨 關鍵新增：獲取裝置名稱與最新座標，用來畫圖
            cursor.execute("""
                SELECT r.device_name, rm.last_x, rm.last_y 
                FROM radar_devices r
                JOIN rooms rm ON r.room_id = rm.room_id
                WHERE r.device_id = %s
            """, (device_id,))
            device = cursor.fetchone()
            device_name = device['device_name'] if device else "未知裝置"
            x_str = device['last_x'] if device and device['last_x'] is not None else '---'
            y_str = device['last_y'] if device and device['last_y'] is not None else '---'

            # 4. 找出所有綁定 LINE 的活動使用者
            cursor.execute("SELECT user_id, name, role, line_user_id FROM users WHERE line_user_id IS NOT NULL AND is_active = 1")
            recipients = cursor.fetchall()
            
            db.commit()

        # 呼叫 line.py 的圖片合成推播功能！
        try:
            # 嘗試載入 line.py 的推播函式
            try:
                from line import broadcast_new_alert
            except ImportError:
                from routes.line import broadcast_new_alert
                
            broadcast_new_alert(event_type, owner_name, device_name, event_id, x_str, y_str)
        except Exception as push_err:
            print(f"呼叫 LINE 警報圖片推播發生錯誤: {push_err}")

        return event_id, owner_name, device_name, recipients
    except Exception as e:
        print(f"記錄事件發生錯誤: {e}")
        return None, None, None, None
    finally:
        if 'db' in locals() and db:
            db.close()

def mark_event_handled(event_id):
    db = get_db_connection()
    try:
        with db.cursor() as cursor:
            cursor.execute("UPDATE event_logs SET handled = 1 WHERE event_id = %s", (event_id,))
            db.commit()
            return True
    finally:
        db.close()
# ===================================================================================

def resolve_all_alerts_by_line(line_uid):
    """一鍵解除所有警告，並紀錄是誰解除的"""
    try:
        db = get_db_connection()
        with db.cursor() as cursor:
            # 1. 先用 LINE ID 查出這位使用者的名字
            cursor.execute("SELECT name FROM users WHERE line_user_id = %s", (line_uid,))
            user = cursor.fetchone()
            if not user:
                return None
            user_name = user['name']

            # 2. 將所有未處理的警告標記為已處理 (1)，並填入處理者姓名
            cursor.execute("""
                UPDATE event_logs 
                SET handled = 1, handled_by = %s 
                WHERE handled = 0
            """, (user_name,))
            db.commit()
            
            # 如果有更新到資料，回傳名字給 LINE 組合回覆訊息
            return user_name if cursor.rowcount > 0 else "NO_ALERTS"
    except Exception as e:
        print(f"解除警告發生錯誤: {e}")
        return None
    finally:
        if 'db' in locals() and db:
            db.close()

def set_line_notify(line_uid, enabled):
    """設定個人的 LINE 通知開關"""
    try:
        db = get_db_connection()
        with db.cursor() as cursor:
            val = 1 if enabled else 0
            cursor.execute("UPDATE users SET notify_enabled = %s WHERE line_user_id = %s", (val, line_uid))
            db.commit()
    except Exception as e:
        print(f"設定通知開關發生錯誤: {e}")
    finally:
        if 'db' in locals() and db:
            db.close()