"""
数据库连接与操作封装
提供 stock_image_summary_wj（账号表）、stock_image_id_wj（图片ID表）、
stock_image_detail_wj（图片详情表）的增删改查方法
"""
import json

import pandas as pd
from secure_db_client import get_remote_engine
import time
from sqlalchemy import create_engine
class ConnectSpider:
    def __init__(self):
        self.db_engine = get_remote_engine(
            site_name='us',  # -> database "selection"
            db_type="postgresql_14_outer",  # -> 服务端 alias "mysql"
        )
        self.db_engine192 = get_remote_engine(
            site_name='us',  # -> database "selection"
            db_type="postgresql_14_outer",  # -> 服务端 alias "mysql"
        )
        # self.pg_port = 54328
        # self.pg_db = "selection"
        # self.pg_user = "postgres"
        # self.pg_pwd = "F9kL2sXe81rZq"
        # self.pg_host = "61.145.136.61"
        # pg_host = "192.168.10.223"
        # self.db_engine192 = create_engine(
        #     f"postgresql://{self.pg_user}:{self.pg_pwd}@{self.pg_host}:{self.pg_port}/{self.pg_db}")
    def mysql(self):
        mysql_engine = get_remote_engine(
            site_name='us',  # -> database "selection"
            db_type="mysql",  # -> 服务端 alias "mysql"
        )
        return mysql_engine

    def save_stock_img_id(self,items):
        # sql = """
        # INSERT INTO stock_image_id_wj
        #   (account_id, image_id, state, created_at, image_title, image_size_info)
        # VALUES (%s, %s, %s, %s, %s, %s)
        # ON DUPLICATE KEY UPDATE
        #   state = VALUES(state),
        #   created_at = VALUES(created_at),
        #   image_title = VALUES(image_title),
        #   image_size_info = VALUES(image_size_info)
        # """
        sql = """
        INSERT INTO stock_image_id_wj
          (account_id, image_id, state, created_at, image_title, image_size_info)
        VALUES (%s, %s, %s, %s, %s, %s::jsonb)
        ON CONFLICT (account_id, image_id) DO UPDATE SET
          state = EXCLUDED.state,
          created_at = EXCLUDED.created_at,
          image_title = EXCLUDED.image_title,
          image_size_info = EXCLUDED.image_size_info;
        """
        params = [
            (
                item['account_id'],
                item['image_id'],
                item['state'],
                item['created_at'],
                item.get('title', ''),
                json.dumps(item.get('sizes', {}))
        )
            for item in items
        ]
        for i in range(5):
            try:
                with self.db_engine192.begin() as conn:
                    conn.execute(sql, params)
                print('存储更新成功')
                break
            except Exception as e:
                time.sleep(30)
                print('save_stock_img_id 报错。', e)
    def update_id_to_3(self, account_id):
        for i in range(5):
            try:
                with self.db_engine192.begin() as connection:
                    table_name = "stock_image_summary_wj"
                    print(account_id)
                    sql_update = f"UPDATE {table_name} SET state = 3 WHERE account_id='{account_id}'"
                    print(sql_update,'成功更新为3')
                    connection.execute(sql_update)
                break
            except Exception as e:
                time.sleep(30)
                print('update_id_to_3 报错。', e)

    def update_all_states_to_1(self, state=1, item_id=None):
        for i in range(5):
            try:
                with self.db_engine192.begin() as connection:  # 使用 begin() 自动管理事务
                    table_name = "stock_image_summary_wj"
                    if state == 3:
                        sql_update = f"UPDATE {table_name} SET state = {state} where id={item_id}"
                    else:
                        sql_update = f"UPDATE {table_name} SET state = {state}"
                    print(sql_update)
                    connection.execute(sql_update)
                break
            except Exception as e:
                time.sleep(30)
                print(f'更新状态失败：{e}')
                # 回滚事务

    def save_stock_detail(self, item):
        """批量保存数据到数据库。"""
        table_name = "stock_image_detail_wj"
        # 将item包装成列表
        items_list = [item]

        # 定义DataFrame的列
        columns = ['account_id', 'image_id', 'image_size_info', 'image_title', 'image_type', 'image_url', 'state',
                   'created_time']
        df = pd.DataFrame(items_list, columns=columns)
        for i in range(5):
            try:
                self.db_engine192.to_sql(df, table_name, if_exists='append')
                print("保存成功！")
                break
            except Exception as e:
                time.sleep(30)
                print(f'save_stock_detail 报错：{e}')
                # 回滚事务

    def get_stock_images_id(self, account_id):
        for i in range(5):
            try:
                table_name = "stock_image_id_wj"
                # 修改查询语句以匹配你的数据表名称和列名称
                query = f""" SELECT image_id,id,image_title,image_size_info FROM {table_name} where account_id ='{account_id}' and state = 1"""
                print(query)
                df_status = self.db_engine192.read_sql(query)
                try:
                    df_status['id'] = df_status['id'].astype(str)
                    image_id_id_pairs = list(
                        df_status['image_id'] + '||-||' + df_status['id'] + '||-||' + df_status['image_title'] + '||-||' +
                        df_status['image_size_info'])
                    print(f'账号：{account_id}需爬取{len(image_id_id_pairs)}张')
                    return image_id_id_pairs
                except Exception as e:
                    print(e)
                    return False
            except Exception as e:
                time.sleep(30)
                print(f'get_stock_images_id 报错：{e}')
                # 回滚事务

    def update_image_id_to_3(self, item_id):
        for i in range(5):
            try:
                with self.db_engine192.begin() as connection:
                    table_name = "stock_image_id_wj"
                    sql_update = f"UPDATE {table_name} SET state = 3 WHERE id = {item_id}"
                    connection.execute(sql_update)
                break
            except Exception as e:
                time.sleep(30)
                print(f'update_image_id_to_3 报错：{e}')
                # 回滚事务
    def update_image_id_to_4(self, item_id):
        for i in range(5):
            try:
                with self.db_engine192.begin() as connection:
                    table_name = "stock_image_id_wj"
                    sql_update = f"UPDATE {table_name} SET state = 4 WHERE id = {item_id}"
                    connection.execute(sql_update)
                break
            except Exception as e:
                time.sleep(30)
                print(f'update_image_id_to_4 报错：{e}')

    def get_cookie_account(self, item_id,state=1):
        for i in range(5):
            try:
                table_name = "stock_image_summary_wj"
                # 修改查询语句以匹配你的数据表名称和列名称
                query = f"""SELECT account_id,account_secret FROM {table_name} where id = {item_id} and state={state}"""
                print(query)
                df_status = self.db_engine192.read_sql(query)
                if len(df_status) > 0:
                    account_id = df_status.account_id.iloc[0]
                    account_secret = df_status.account_secret.iloc[0]
                    account_list = [account_id, account_secret]
                    print(account_list, '232323====32')
                    return account_list
                else:
                    return None
            except Exception as e:
                time.sleep(30)
                print(f'get_cookie_account 报错：{e}')

    def get_all_image_id(self):
        for i in range(5):
            try:
                table_name = "stock_image_detail_wj" # 全部下载完成之后删除这个条件
                sql_query = f"SELECT image_id FROM {table_name}"
                df_status = self.db_engine192.read_sql(sql_query)
                image_id = list(df_status['image_id'].astype(str))
                return image_id
            except Exception as e:
                time.sleep(30)
                print(f'get_all_image_id 报错：{e}')

    def update_url_state_to_3(self, image_id):
        for i in range(5):
            try:
                with self.db_engine192.begin() as connection:
                    table_name = "stock_image_detail_wj"
                    sql_update = f"UPDATE {table_name} SET state = 3 WHERE image_id ='{image_id}' and state = 2"
                    print(sql_update,'下载完成修改状态为3 .条件为2 的')
                    connection.execute(sql_update)
                break
            except Exception as e:
                time.sleep(30)
                print(f'update_url_state_to_3 报错：{e}')

    def get_pic_urls(self, account_id):
        pic_data_list = []  # 创建一个空列表来存储结果
        table_name = "stock_image_detail_wj"
        query =f"""select image_url, image_id, image_title,id from {table_name} where account_id = '{account_id}' and state = 1"""
        print('== query::',query)
        try:
            # result_df = self.db_engine192.read_sql(query)
            result_df = self.db_engine192.read_then_update(
                select_sql=query,
                update_table=table_name,
                set_values={"state": 2},  # 修改状态下载中。状态为2
                where_keys=["id"]
            )
            # print(result_df)
            result_list = result_df.values.tolist()
            for row in result_list:  # 遍历所有的结果行
                if row is not None:
                    # 直接构建所需格式的字符串
                    pic_datas = f"{row[0]}||{row[1]}||{row[2]}"
                    pic_data_list.append(pic_datas)  # 添加到列表中
                    """
                    ['https://download.shutterstock.com/gatekeeper/W3siZCI6ICJzaHV0dGVyc3RvY2stbWVkaWEiLCAiayI6ICJwaG90by8yNDY2MDI5NDI1L2h1Z2UuanBnIiwgImRjIjogImlkbF8xMjMiLCAiZSI6IDE3NDYwMTIzNDQsICJtIjogMX0sICJBb0dOUzZDMXNiVU1XczgxMFN5YVBsUEJrakEiXQ==/shutterstock_2466029425.jpg||2466029425||Beautiful smiling model in sunglasses. Female dressed in summer hipster white T-shirt and jeans. Posing near white wall in the street. Funny and positive woman having fun outdoors, in sunglasses']
                    """
            if not pic_data_list:
                # print("No data found for the given account_id")
                return False
            else:
                return pic_data_list  # 返回列表
        except Exception as e:
            print(f"An error occurred: {e}")
            return False


if __name__ == '__main__':
    # UPDATE stock_image_detail_wj SET state = 3 WHERE image_id ='2668613199' and state = 1
    ConnectSpider().get_pic_urls('xiezhihao@yswg.com.cn')
