import time
from loguru import logger
from pymysql.cursors import DictCursor
import json
import pymysql
from dbutils.pooled_db import PooledDB


DB_CONFIG = {
    'host': "tenant-2101894307-cn-beijing.bytehouse.ivolces.com",
    'port': 3306,
    'user': "bytehouse",
    'password': "JkoyoRV3PH:7YaWLXRwAO",
    'database': "douchacha_data",
    'charset': 'utf8mb4',
}
#

POOL = PooledDB(
    creator=pymysql,
    maxconnections=50,
    mincached=1,
    maxcached=10,
    blocking=True,
    ping=7,
    cursorclass=DictCursor,
    autocommit=True,
    connect_timeout=5,
    read_timeout=30,
    write_timeout=30,
    **DB_CONFIG
)

def is_connection_error(e):
    if isinstance(e, pymysql.err.OperationalError):
        code = e.args[0] if e.args else None
        return code in (0, 2006, 2013,2014)
    return False


def get_conn(retry_times=2):
    for attempt in range(retry_times + 1):
        conn = None
        try:
            conn = POOL.connection()
            conn.ping()
            return conn
        except Exception as e:
            try:
                if conn is not None:
                    conn.close()
            except Exception:
                pass

            if attempt < retry_times:
                time.sleep(0.2 * (attempt + 1))
                continue

            raise

def query_data(sql, params=None, size=None, retry_times=2):
    for attempt in range(retry_times + 1):
        conn = None
        try:
            conn = get_conn()
            with conn.cursor() as cursor:
                cursor.execute(sql, params)
                if size is None:
                    return cursor.fetchall()
                return cursor.fetchmany(size)

        except Exception as e:
            logger.warning(
                f"查询数据失败 attempt={attempt + 1}/{retry_times + 1}: {e}"
            )

            if attempt < retry_times and is_connection_error(e):
                time.sleep(0.2 * (attempt + 1))
                continue

            return None

        finally:
            try:
                if conn is not None:
                    conn.close()
            except Exception:
                pass


def insert_data(table_name, items, retry_times=2) -> bool:
    if not items:
        return False

    for attempt in range(retry_times + 1):
        conn = None
        try:
            conn = get_conn()

            with conn.cursor() as cursor:
                cols = items[0].keys()
                cols_str = ", ".join(cols)
                placeholders = ", ".join(["%s"] * len(cols))

                processed_data = []
                for item in items:
                    row = []
                    for key in cols:
                        value = item[key]
                        if isinstance(value, (list, dict)):
                            row.append(json.dumps(value, ensure_ascii=False))
                        else:
                            row.append(value)
                    processed_data.append(tuple(row))

                insert_sql = f"INSERT INTO {table_name} ({cols_str}) VALUES ({placeholders})"
                cursor.executemany(insert_sql, processed_data)

            return True

        except Exception as e:
            logger.error(
                f"插入数据失败 attempt={attempt + 1}/{retry_times + 1}, "
                f"table={table_name}: {e}"
            )

            if attempt < retry_times and is_connection_error(e):
                time.sleep(0.2 * (attempt + 1))
                continue

            return False

        finally:
            try:
                if conn is not None:
                    conn.close()
            except Exception:
                pass


