import os
import sqlite3
from sqlite3 import Error
from queue import Queue, Empty
from typing import List,Tuple, Any


class SQLiteDB:

    default_db_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    default_db_file = default_db_path+"/db/mydb.db"

    def __init__(self, db_file=None, max_connections=10):
        self.db_file = db_file or self.default_db_file
        self.max_connections = max_connections  # 连接池最大连接数
        self.pool = Queue(maxsize=max_connections)  # 连接池

        for _ in range(max_connections):
            self.pool.put(sqlite3.connect(db_file))

    # 获得数据库连接
    def _get_connection(self):
        try:
            return self.pool.get(timeout=5)
        except Empty:
            raise Exception("No available database connections")

    # 释放数据库连接
    def _release_connection(self, conn):
        self.pool.put(conn)

    # 执行SQL语句
    def _execute_sql(self, sql: str, params: Tuple[Any, ...] = ()):
        conn = self._get_connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql, params)
            conn.commit()
        finally:
            cursor.close()
            self._release_connection(conn)

    # 创建表格
    def create_table(self, create_table_sql):
        self._execute_sql(create_table_sql)

    # 删除表格
    def drop_table(self, table_name):
        sql = f'DROP TABLE IF EXISTS {table_name}'
        self._execute_sql(sql)

    # 插入数据
    def insert(self, table, data):
        columns = ', '.join(data.keys())
        placeholders = ', '.join('?' * len(data))
        sql = f'INSERT INTO {table} ({columns}) VALUES ({placeholders})'
        self._execute_sql(sql, tuple(data.values()))

    # 更新数据
    def update(self, table, data,  condition="1=1"):
        placeholders = ', '.join([f"{column} = ?" for column in data.keys()])
        sql = f'UPDATE {table} SET {placeholders} WHERE {condition}'
        self._execute_sql(sql, tuple(data.values()))

    # 删除数据
    def delete(self, table, condition="1=1"):
        sql = f'DELETE FROM {table} WHERE {condition}'
        self._execute_sql(sql)

    # 查询多条数据
    def find_all(self, table, condition="1=1"):
        sql = f'SELECT * FROM  {table} WHERE {condition}'
        conn = self._get_connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            return cursor.fetchall()
        finally:
            cursor.close()
            self._release_connection(conn)

    # 分页查询
    def find_page(self, table, page=1, page_size=10, condition="1=1"):
        offset = (page - 1) * page_size
        sql = f'SELECT * FROM  {table} WHERE {condition} LIMIT {page_size} OFFSET {offset}'
        conn = self._get_connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            return cursor.fetchall()
        finally:
            cursor.close()
            self._release_connection(conn)

    # 查询单条数据
    def find_one(self, table, condition="1=1"):
        sql = f'SELECT * FROM  {table} WHERE {condition}'
        conn = self._get_connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            return cursor.fetchone()
        finally:
            cursor.close()
            self._release_connection(conn)

    # 查询多条数据（自定义sql）
    def query_all(self, sql: str, params: Tuple[Any, ...] = ()) -> List[Tuple]:
        conn = self._get_connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql, params)
            return cursor.fetchall()
        finally:
            cursor.close()
            self._release_connection(conn)

    # 查询单条数据（自定义sql）
    def query_one(self, sql: str, params: Tuple[Any, ...] = ()) -> Tuple:
        conn = self._get_connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql, params)
            return cursor.fetchone()
        finally:
            cursor.close()
            self._release_connection(conn)


# 数据库定义，供各个应用程序使用
mydb = SQLiteDB('mydb.db')


# 使用示例
if __name__ == '__main__':

    db = SQLiteDB('example.db')

    # 创建表格
    create_table_sql = """ CREATE TABLE IF NOT EXISTS users (
                                            id integer PRIMARY KEY AUTOINCREMENT,
                                            name text NOT NULL,
                                            age integer,
                                            salary REAL,
                                            is_active BOOLEAN
                                        ); """
    db.create_table(create_table_sql)

    # 插入数据
    db.insert('users', {'name': 'Alice', 'age': 30, 'salary': 5000.88, 'is_active': False})
    db.insert('users', {'name': 'Tom', 'age': 40, 'salary': 6000.66, 'is_active': True})
    db.insert('users', {'name': 'Lily', 'age': 28, 'salary': 7000.77, 'is_active': True})
    db.insert('users', {'name': 'Bate', 'age': 25, 'salary': 9000.99, 'is_active': True})
    db.insert('users', {'name': 'Lucy', 'age': 28, 'salary': 3000.33, 'is_active': True})

    # 更新数据
    condition = "name='{}'".format("Alice") # 查询条件 name='Alice'
    db.update('users', {'age': 33, 'salary': 8000.88}, condition)

    # 查询数据
    condition = "name='{}'".format("Tom") # 查询条件 name='Tom'
    print(db.find_one("users", condition))
    print(db.find_all("users"))
    print(db.find_page("users",2,3))

    # 删除数据
    condition = "name='{}'".format("Alice") # 查询条件 name='Alice'
    db.delete('users', condition)
    # db_tools.delete('users')  # 删除所有数据

    print("删除数据后——————————————————")
    print(db.find_one("users"))
    print(db.find_all("users"))

    print("自定义Sql 查询——————————————————")

    sql = "SELECT * FROM users where name=? "
    print(db.query_one(sql, ("Tom",)))

    sql = "SELECT * FROM users where salary > ? and is_active = ?"
    print(db.query_all(sql, (7000, True)))

    # 删除表格
    db.drop_table('users')