# utils/db_monitor.py
import logging
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import WriteRowsEvent
import database
from routes.line import line_bot_api, build_event_message, generate_location_image
from linebot.models import TextSendMessage
import random

def start_db_monitor(socketio):
    # MySQL 連線資訊
    mysql_settings = {
        "host": "localhost",
        "port": 3306,
        "user": "webuser",
        "passwd": "U1224006"
    }

    def run():
        print("📡 資料庫實時監聽啟動...")
        
        # 1. 取得資料庫最新位置，防止歷史洪災
        try:
            db = database.get_db_connection()
            with db.cursor() as cur:
                cur.execute("SHOW MASTER STATUS")
                status = cur.fetchone()
                log_file = status['File']
                log_pos = status['Position']
            db.close()
        except Exception as e:
            print(f"無法獲取資料庫狀態: {e}")
            return

        random_server_id = random.randint(100, 9999)
        
        try:
            # 2. 恢復 blocking=True 維持單一連線 (不再一秒重連 20 次)
            stream = BinLogStreamReader(
                connection_settings=mysql_settings,
                server_id=random_server_id,
                log_file=log_file,
                log_pos=log_pos,
                resume_stream=True,
                blocking=True,  
                only_events=[WriteRowsEvent],
                only_schemas=['mmwave_v4'],
                only_tables=['activity_logs']
            )

            # 3. 阻塞式迭代，有資料才會進入迴圈
            for binlogevent in stream:
                for row in binlogevent.rows:
                    # 1. 更新網頁 UI
                    state = database.get_current_ui_state()
                    socketio.emit('ui_update', state, namespace='/')

                    # 2. 檢查是否有新的警告需要發送 LINE
                    check_and_push_alerts()
                    
                # 處理完一筆，讓出系統資源
                socketio.sleep(0)
                    
        except Exception as e:
            print(f"資料庫監聽出錯: {e}")
            socketio.sleep(5)

    # 啟動背景任務
    socketio.start_background_task(run)


def check_and_push_alerts():
    """ 檢查並發送 LINE """
    db = None
    try:
        db = database.get_db_connection()
        with db.cursor() as cursor:
            sql = """
                SELECT e.event_id, e.event_type, d.device_name, u.name as owner_name, rm.last_x, rm.last_y
                FROM event_logs e
                JOIN radar_devices d ON e.device_id = d.device_id
                JOIN rooms rm ON d.room_id = rm.room_id
                JOIN users u ON u.role = 'admin'
                WHERE e.handled = 0 
                AND e.event_time >= NOW() - INTERVAL 10 MINUTE
                AND e.event_id NOT IN (SELECT event_id FROM notifications)
                ORDER BY e.event_time DESC LIMIT 1
            """
            cursor.execute(sql)
            event = cursor.fetchone()

            if event:
                event_id = event['event_id']
                event_type = event['event_type']
                device_name = event['device_name']
                owner_name = event['owner_name']
                x_str = event['last_x'] if event['last_x'] is not None else '---'
                y_str = event['last_y'] if event['last_y'] is not None else '---'
                zone = database.get_zone_name(x_str, y_str)
                
                # 生成圖片 (呼叫 line.py 的功能)
                img_msg = generate_location_image(f"alert", x_str, y_str)
                
                # 找出所有要接收 LINE 的家屬
                cursor.execute("""
                    SELECT user_id, name, role, line_user_id 
                    FROM users 
                    WHERE line_user_id IS NOT NULL 
                    AND is_active = 1 
                    AND notify_enabled = 1
                """)
                recipients = cursor.fetchall()

                for user in recipients:
                    # 不管推播開不開，都先把紀錄寫進去
                    cursor.execute("INSERT INTO notifications (event_id, user_id, sent_time) VALUES (%s, %s, NOW())", 
                                   (event_id, user['user_id']))

                    # 組合文字訊息
                    msg_content = build_event_message(
                        event_type, owner_name, device_name, user['name'], user['role'], event_id, zone=zone
                    )
                    
                    # 將文字與圖片包裝成一個 list
                    msgs = [TextSendMessage(text=msg_content)]
                    if img_msg:
                        msgs.append(img_msg)

                    try:
                        # 一次推播文字 + 圖片
                        line_bot_api.push_message(user['line_user_id'], msgs)
                        print(f"✅ 成功發送警報(含圖片與位置:{zone})給 {user['name']}")
                    except Exception as line_e:
                        print(f"發送給 {user['name']} 失敗: {line_e}")
            
            db.commit()
    except Exception as e:
        print(f"處理警報推播發生錯誤: {e}")
        pass
    finally:
        if db:
            db.close()