import requests
import os
import time
from datetime import datetime
import hashlib
import uuid
from sqlalchemy import select, create_engine, Table, Column, String, MetaData, text
from sqlalchemy.dialects.mysql import insert

class LoginClient:
    def __init__(self):
        self.base_url = "https://116.10.244.81:28001"
        self.session = requests.Session()
        # 禁用SSL验证警告
        requests.packages.urllib3.disable_warnings()
        
    def get_captcha(self):
        """获取验证码图片"""
        # 生成随机cid
        cid = uuid.uuid4().hex.encode()
        cid = hashlib.sha256(cid).hexdigest()
        
        url = f"{self.base_url}/api/vss-auth-center/v1/captcha"
        headers = {
            "Accept": "image/avif,image/webp,image/apng,image/svg+xml,image/*,*/*;q=0.8",
            "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/135.0.0.0 Safari/537.36 Edg/135.0.0.0",
            "Referer": f"{self.base_url}/vss-em-iui/"
        }
        params = {"cid": cid}
        
        response = self.session.get(url, headers=headers, params=params, verify=False)
        
        # 缓存验证码到本地
        captcha_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 
                                   'static', 'captcha.png')
        with open(captcha_path, "wb") as f:
            f.write(response.content)
            
        return cid

    def login(self, account, password, validcode, cid):
        """执行登录"""
        url = f"{self.base_url}/api/vss-auth-center/v1/user/login"
        
        headers = {
            "Accept": "application/json, text/plain, */*",
            "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",
            "Content-Type": "application/json",
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/135.0.0.0 Safari/537.36 Edg/135.0.0.0",
            "Referer": f"{self.base_url}/vss-em-iui/",
            "language": "zh-CN"
        }
        
        params = {
            "cutype": "12"
        }
        
        data = {
            "account": account,
            "password": password,
            "validcode": validcode,
            "cid": cid
        }
        
        response = self.session.post(
            url,
            headers=headers,
            params=params,
            json=data,
            verify=False
        )

        return response.json()

    def get_online_info(self, token, page=1, page_size=1500):
        """获取在线信息"""
        url = f"{self.base_url}/api/vss-user-business/v1/pus/queryPage/4109"
        
        headers = {
            "Accept": "application/json, text/plain, */*",
            "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",
            "Authoration": token,
            "Cache-Control": "no-cache",
            "Connection": "keep-alive",
            "Content-Type": "application/json",
            "Referer": f"{self.base_url}/vss-em-iui/",
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/135.0.0.0 Safari/537.36 Edg/135.0.0.0",
            "language": "zh-CN"
        }
        
        # 生成防伪标识
        forgery_defense = uuid.uuid4().hex
        
        params = {
            "page_size": page_size,
            "page": page,
            "pu_name": "",
            "puid": "",
            "protocol": "",
            "nodeid": "",
            "popunitid": "",
            "status": "",
            "forgerydefense": forgery_defense,
            "csrftoken": forgery_defense
        }
        
        response = self.session.get(
            url,
            headers=headers,
            params=params,
            verify=False
        )
        
        return response.json()

def get_existing_channels(engine, channels_table):
    """获取数据库中现有的通道状态"""
    with engine.connect() as conn:
        query = select(channels_table.c.puid, channels_table.c.status)
        result = conn.execute(query)
        return {row.puid: row.status for row in result}

def update_device_status(engine):
    """更新设备状态"""
    with engine.connect() as conn:
        # 获取channels表和device表的数据
        channels_query = """
            SELECT puid, status, pu_name 
            FROM channels
        """
        channels_result = conn.execute(text(channels_query))
        channels_data = {row.puid: {'status': row.status, 'pu_name': row.pu_name} for row in channels_result}
        
        # 获取device表中的puid
        device_query = """
            SELECT puid 
            FROM device
        """
        device_result = conn.execute(text(device_query))
        
        # 更新设备状态
        updates = []
        for row in device_result:
            puid = row.puid
            if puid in channels_data:
                # 更新在线状态
                is_normal = 1 if channels_data[puid]['status'] == 'online' else 0
                
                # 检查使用状态
                pu_name = channels_data[puid]['pu_name']
                in_use = 0 if any(keyword in pu_name for keyword in ['无学生', '丢失']) else 1
                
                # 构建更新语句
                update_query = """
                    UPDATE device 
                    SET is_normal = :is_normal, in_use = :in_use 
                    WHERE puid = :puid
                """
                updates.append({
                    'puid': puid,
                    'is_normal': is_normal,
                    'in_use': in_use
                })
        
        if updates:
            update_stmt = text("""
                UPDATE device 
                SET is_normal = :is_normal, in_use = :in_use 
                WHERE puid = :puid
            """)
            conn.execute(update_stmt, updates)
            conn.commit()
            print(f"成功更新 {len(updates)} 个设备状态")

def main():
    client = LoginClient()
    
    while True:
        cid = client.get_captcha()
        print("验证码已保存为captcha.png")
        validcode = input("请查看captcha.png并输入验证码: ")
        password = "Root1234#"
        hashed_password = hashlib.sha256(password.encode()).hexdigest()
        
        result = client.login(
            account="DHDX001",
            password=hashed_password,
            validcode=validcode,
            cid=cid
        )
        
        if result.get("result") == 0:
            token = result.get("token")
            
            online_info = client.get_online_info(token)
            if online_info.get("result") == 0:
                rows = online_info.get("rows", [])
                filtered_rows = [
                    row for row in rows 
                    if "大化4+N摄像头" not in row.get("pu_name", "") 
                    and "大化幼儿园监控" not in row.get("pu_name", "")
                ]
                
                engine = create_engine('mysql+pymysql://4an:GtbJp6Ai5azntnTB@10.10.10.253/4an')
                metadata = MetaData()
                channels = Table(
                    'channels', metadata,
                    Column('puid', String(50), primary_key=True),
                    Column('pu_name', String(200)),
                    Column('status', String(20)),
                    Column('update_time', String(50))
                )
                metadata.create_all(engine)
                
                existing_channels = get_existing_channels(engine, channels)
                
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                update_data = []
                
                for row in filtered_rows:
                    puid = row.get('puid')
                    current_status = row.get('status')
                    
                    if puid not in existing_channels or existing_channels[puid] != current_status:
                        
                        update_data.append({
                            'puid': puid,
                            'pu_name': row.get('pu_name'),
                            'status': current_status,
                            'update_time': current_time
                        })
                
                # 执行更新
                if update_data:
                    with engine.connect() as conn:
                        stmt = insert(channels).on_duplicate_key_update(
                            pu_name=channels.c.pu_name,
                            status=channels.c.status,
                            update_time=channels.c.update_time
                        )
                        conn.execute(stmt, update_data)
                        conn.commit()
                    print(f"\n成功更新 {len(update_data)} 条记录")
                else:
                    print("\n没有需要更新的记录")
                
                # 更新设备状态
                print("\n开始更新设备状态...")
                update_device_status(engine)
                
                return
            else:
                print("获取通道信息失败！")
                print(f"错误信息: {online_info.get('err_msg', '未知错误')}")
                return  # 获取信息失败也退出程序
            
        else:
            print("登录失败！")
            print(result)
            if result.get("result") == 12:  # 验证码错误
                retry = input("验证码错误，是否重新获取验证码？(y/n): ")
                if retry.lower() != 'y':
                    return
            else:
                return  # 其他错误直接退出

if __name__ == "__main__":
    main()