import cv2
from ultralytics import YOLO
from opcua import Client, ua
import dmPython
import pandas as pd
from datetime import datetime, timedelta
import time
import threading


class ConveyorBeltMonitor:
    def __init__(self, model_path, plc_config, cameras_config):
        self.model = YOLO(model_path)
        self.plc_url = plc_config['url']
        self.plc_username = plc_config['username']
        self.plc_password = plc_config['password']

        self.client = None
        self.plc_timeout = 5
        self.max_retries = 3
        self.cameras = {}
        self.camera_states = {}
        self.skip_frames = 20

        self.setup_cameras(cameras_config)

    def setup_cameras(self, cameras_config):
        for camera_id, config in cameras_config.items():
            try:
                cap = cv2.VideoCapture(config['rtsp_url'])
                if not cap.isOpened():
                    print(f"打开 {camera_id} 摄像头失败!")
                    continue
                self.cameras[camera_id] = {
                    'capture': cap,
                    'frame_count': 0,
                    'rtsp_url': config['rtsp_url'],
                    'plc_nodes': config['plc_nodes'],
                    'failed_read_count': 0
                }
                self.camera_states[camera_id] = {
                    'last_plc_values': {node: None for node in config['plc_nodes']},
                    'has_queried_database': False,
                    'coal_detection_count': 0
                }
            except Exception as e:
                print(f"Error setting up camera {camera_id}: {e}")

    def connect_plc(self):
        self.client = Client(self.plc_url)
        self.client.set_user(self.plc_username)
        self.client.set_password(self.plc_password)
        self.client.timeout = self.plc_timeout
        try:
            self.client.connect()
            print("连接到 OPC UA 服务器")
        except Exception as e:
            print(f"Failed to connect to PLC: {e}")
            self.reconnect_plc()

    def reconnect_plc(self):
        print("尝试重新连接 PLC...")
        time.sleep(5)
        self.connect_plc()

    def check_plc_conditions(self, camera_id):
        plc_nodes = self.cameras[camera_id]['plc_nodes']
        current_values = {}
        last_values = self.camera_states[camera_id]['last_plc_values']

        for node_id in plc_nodes:
            current_values[node_id] = self.read_plc_node(node_id, camera_id)

        trigger_condition = any(
            last_values[node_id] == 3 and current_values[node_id] != 3
            for node_id in plc_nodes
        )

        self.camera_states[camera_id]['last_plc_values'] = current_values

        return trigger_condition

    def read_plc_node(self, node_id, camera_id):
        for attempt in range(self.max_retries):
            try:
                node = self.client.get_node(node_id)
                value = node.get_value()
                return value
            except (ua.UaError, TimeoutError) as e:
                print(f"Attempt {attempt + 1}: Failed to read PLC node {node_id}, Error: {e}")
                time.sleep(1)
        print(f"Max retries reached for node {node_id}. Returning last value.")
        return self.camera_states[camera_id]['last_plc_values'][node_id]
    
    def reconnect_camera(self, camera_id):
        """Attempt to reconnect the camera after failing to read frames"""
        config = self.cameras[camera_id]
        print(f"重连摄像头 {camera_id}...")
        config['capture'].release()  # Release the current capture object
        try:
            cap = cv2.VideoCapture(config['rtsp_url'])
            if cap.isOpened():
                print(f"重连摄像头 {camera_id} 成功")
                config['capture'] = cap
                config['failed_read_count'] = 0  # Reset the failed count
            else:
                print(f"Failed to reconnect to camera {camera_id}")
        except Exception as e:
            print(f"Error reconnecting to camera {camera_id}: {e}")
    
    def process_frame(self, frame):
        try:
            dim = (int(frame.shape[1] * 0.5), int(frame.shape[0] * 0.5))
            resized_frame = cv2.resize(frame, dim, interpolation=cv2.INTER_AREA)

            results = self.model(resized_frame)
            annotated_frame = results[0].plot()
            detections = results[0].boxes

            return annotated_frame, detections
        except Exception as e:
            print(f"Error processing frame: {e}")
            return frame, None
    
    def fetch_alarm_data(self):
        """查询 达梦 报警信息"""
        db_config = {
            'host': "10.248.56.208",
            'port': "5236",
            'user': "XMSW",
            'password': "7m*d7Zf04#G%4je"
        }

        try:
            connection = dmPython.Connection(
                user=db_config['user'],
                password=db_config['password'],
                server=db_config['host'],
                port=db_config['port']
            )
            cursor = connection.cursor()
            now = datetime.now()
            two_minutes_ago = now - timedelta(minutes=2)

            query = f"""
                SELECT TAG_NAME, TAG_COMMENT, ALARM_TIME, EVENT_TIME, ALARM_TEXT
                FROM XMSW.T_ALARM
                WHERE ALARM_TIME BETWEEN '{two_minutes_ago}' AND '{now}'
                ORDER BY ALARM_TIME DESC
            """

            cursor.execute(query)
            rows = cursor.fetchall()
            columns = ['TAG_NAME', 'TAG_COMMENT', 'ALARM_TIME', 'EVENT_TIME', 'ALARM_TEXT']
            df = pd.DataFrame(rows, columns=columns)

            cursor.close()
            connection.close()
            return df
        except Exception as e:
            print(f"Failed to fetch alarm data: {e}")
            return pd.DataFrame()  # 返回空 DataFrame
        
    def handle_detections(self, camera_id, detections):

        current_time = datetime.now().strftime("%Y-%m-%d %H-%M-%S")
        alarm_data = self.fetch_alarm_data()

        excel_file_name = f"胶带机_{camera_id}_{current_time}.xlsx"
        try:
            alarm_data.to_excel(excel_file_name, index=False, sheet_name='QueryResults', engine='openpyxl')
            print(f"Camera {camera_id}: Alarm data written to {excel_file_name}")
        except Exception as e:
            print(f"Failed to write alarm data to Excel: {e}")
            
    def run_camera(self, camera_id):
        camera_info = self.cameras[camera_id]
        while True:
            trigger_condition = self.check_plc_conditions(camera_id)
            if trigger_condition:
                detect_counter = 0
                loop_start_time = time.time()
                while detect_counter < 5:
                    cap = camera_info['capture']
                    success, frame = cap.read()
                    if not success:
                        camera_info['failed_read_count'] += 1
                        print(
                            f"Failed to read frame from camera {camera_id}, Attempt: {camera_info['failed_read_count']}")
                        if camera_info['failed_read_count'] >= 5:
                            self.reconnect_camera(camera_id)
                        break

                    annotated_frame, detections = self.process_frame(frame)

                    if detections is not None and detections.shape[0] > 0:
                        detect_counter += 1
                        print(f"Camera {camera_id}: Detection count: {detect_counter}")

                    if detect_counter >= 5:
                        self.handle_detections(camera_id, detections)
                        break

                    if time.time() - loop_start_time > 5:
                        print(f"Timeout reached while detecting on camera {camera_id}")
                        break

    def run(self):
        self.connect_plc()

        # Create and start threads for each camera
        threads = []
        for camera_id in self.cameras:
            thread = threading.Thread(target=self.run_camera, args=(camera_id,))
            thread.start()
            threads.append(thread)

        # Join threads to the main thread
        for thread in threads:
            thread.join()

    def cleanup(self):
        if self.client:
            self.client.disconnect()
        for camera_info in self.cameras.values():
            camera_info['capture'].release()
        cv2.destroyAllWindows()


# Configuration (Same as before)
plc_config = {
    'url': "opc.tcp://10.248.56.206:37802",
    'username': "swxmc",
    'password': "swxmc@147!"
}

cameras_config = {
    'cam218': {
        'rtsp_url': 'rtsp://admin:hik12345+@10.248.53.20',
        'plc_nodes': ["ns=2;s=XMSW_Raw_CON218_RunState11"]
    },
    'cam201': {
        'rtsp_url': 'rtsp://admin:hik12345+@10.248.53.96',
        'plc_nodes': [
            "ns=2;s=XMSW_Raw_CON201_RunState11",
            "ns=2;s=XMSW_Raw_CON201_RunState22"
        ]
    }
}

# Create and run monitor
monitor = ConveyorBeltMonitor("best.pt", plc_config, cameras_config)
monitor.run()
请检查上述完整代码，检查是否有变量或函数没有被用到，并且没有用到的是否需要使用并如何使用