import sqlite3
import os
import threading
from contextlib import contextmanager

DATABASE_PATH = os.path.join(os.path.dirname(__file__), '../../database/app.db')
SCHEMA_PATH = os.path.join(os.path.dirname(__file__), '../../database/schema.sql')

class ConnectionPool:
    _instance = None
    _lock = threading.Lock()
    
    def __new__(cls):
        if cls._instance is None:
            with cls._lock:
                if cls._instance is None:
                    cls._instance = super().__new__(cls)
                    cls._instance._initialized = False
        return cls._instance
    
    def _ensure_initialized(self):
        if not self._initialized:
            self._init_pool()
            self._initialized = True
    
    def _init_pool(self):
        self._db_path = DATABASE_PATH
        self._init_database()
    
    def _init_database(self):
        db_dir = os.path.dirname(self._db_path)
        if not os.path.exists(db_dir):
            os.makedirs(db_dir)
            print(f'Created directory: {db_dir}')
        
        if not os.path.exists(self._db_path):
            print('数据库不存在，创建新数据库')
            conn = self._create_connection()
            with open(SCHEMA_PATH, 'r', encoding='utf-8') as f:
                schema = f.read()
            conn.executescript(schema)
            conn.commit()
            conn.close()
            print('数据库初始化成功')
        else:
            print('数据库已存在，跳过初始化')
        
        conn = self._create_connection()
        conn.execute('PRAGMA journal_mode=WAL')
        conn.execute('PRAGMA synchronous=NORMAL')
        conn.execute('PRAGMA busy_timeout=30000')
        conn.execute('PRAGMA cache_size=-64000')
        conn.commit()
        conn.close()
        print('数据库优化完成（启用WAL模式）')
    
    def _create_connection(self):
        conn = sqlite3.connect(
            self._db_path,
            timeout=30.0,
            check_same_thread=False,
            isolation_level=None
        )
        conn.row_factory = sqlite3.Row
        return conn
    
    @contextmanager
    def get_connection(self):
        conn = self._create_connection()
        try:
            yield conn
        finally:
            conn.close()

_pool_instance = None

def _get_pool():
    global _pool_instance
    if _pool_instance is None:
        _pool_instance = ConnectionPool()
        _pool_instance._ensure_initialized()
    return _pool_instance

def get_db():
    pool = _get_pool()
    conn = pool._create_connection()
    return conn

def init_db():
    _get_pool()
    # 确保新表存在（兼容已有数据库）
    conn = get_db()
    try:
        conn.execute('''
            CREATE TABLE IF NOT EXISTS sensitive_words (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word TEXT UNIQUE NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        conn.commit()
    finally:
        conn.close()

@contextmanager
def db_transaction():
    pool = _get_pool()
    conn = pool._create_connection()
    try:
        yield conn
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()