"""
高級(jí)博客系統(tǒng) - 展示SQLite在Python中的完整應(yīng)用
"""
import sqlite3
import json
import hashlib
import uuid
from datetime import datetime, timedelta
from contextlib import contextmanager
from dataclasses import dataclass
from typing import List, Optional, Dict, Any
import logging

# 配置日志系統(tǒng)
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

@dataclass
class User:
    """用戶(hù)數(shù)據(jù)類(lèi)"""
    id: Optional[int] = None
    username: str = ""
    email: str = ""
    password_hash: str = ""
    bio: Optional[str] = None
    created_at: Optional[str] = None
    last_login: Optional[str] = None

@dataclass
class BlogPost:
    """博客文章數(shù)據(jù)類(lèi)"""
    id: Optional[int] = None
    title: str = ""
    content: str = ""
    author_id: int = 0
    status: str = "draft"  # draft, published, archived
    tags: Optional[List[str]] = None
    metadata: Optional[Dict[str, Any]] = None
    created_at: Optional[str] = None
    updated_at: Optional[str] = None
    published_at: Optional[str] = None

class DatabaseConnection:
    """高級(jí)數(shù)據(jù)庫(kù)連接管理器"""
    
    def __init__(self, db_path: str = ":memory:", enable_wal: bool = True):
        """
        初始化數(shù)據(jù)庫(kù)連接
        
        Args:
            db_path: 數(shù)據(jù)庫(kù)路徑，默認(rèn)為內(nèi)存數(shù)據(jù)庫(kù)
            enable_wal: 是否啟用Write-Ahead Logging（提高并發(fā)性能）
        """
        self.db_path = db_path
        self.enable_wal = enable_wal
        
        # 注冊(cè)自定義類(lèi)型適配器
        self._register_adapters()
    
    def _register_adapters(self):
        """注冊(cè)自定義類(lèi)型適配器"""
        # 列表適配器（存儲(chǔ)為JSON）
        def adapt_list(lst):
            return json.dumps(lst)
        
        def convert_list(text):
            return json.loads(text) if text else []
        
        # 字典適配器
        def adapt_dict(dct):
            return json.dumps(dct)
        
        def convert_dict(text):
            return json.loads(text) if text else {}
        
        sqlite3.register_adapter(list, adapt_list)
        sqlite3.register_adapter(dict, adapt_dict)
        sqlite3.register_converter("list", convert_list)
        sqlite3.register_converter("dict", convert_dict)
    
    @contextmanager
    def get_connection(self):
        """
        獲取數(shù)據(jù)庫(kù)連接的上下文管理器
        
        使用示例：
            with db.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT * FROM users")
        """
        conn = None
        try:
            # 創(chuàng)建連接，啟用類(lèi)型檢測(cè)和自定義轉(zhuǎn)換器
            conn = sqlite3.connect(
                self.db_path,
                detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES,
                isolation_level=None  # 使用自動(dòng)提交模式
            )
            
            # 設(shè)置行工廠(chǎng)為字典類(lèi)型
            conn.row_factory = sqlite3.Row
            
            # 啟用外鍵約束
            conn.execute("PRAGMA foreign_keys = ON")
            
            # 啟用WAL模式（提高并發(fā)性能）
            if self.enable_wal:
                conn.execute("PRAGMA journal_mode = WAL")
                conn.execute("PRAGMA synchronous = NORMAL")
            
            # 設(shè)置繁忙超時(shí)
            conn.execute("PRAGMA busy_timeout = 5000")
            
            logger.info(f"數(shù)據(jù)庫(kù)連接已建立: {self.db_path}")
            
            yield conn
            
        except sqlite3.Error as e:
            logger.error(f"數(shù)據(jù)庫(kù)錯(cuò)誤: {e}")
            if conn:
                conn.rollback()
            raise
        
        finally:
            if conn:
                # 如果使用WAL模式，執(zhí)行檢查點(diǎn)
                if self.enable_wal:
                    conn.execute("PRAGMA wal_checkpoint(PASSIVE)")
                conn.close()
                logger.info("數(shù)據(jù)庫(kù)連接已關(guān)閉")

class BlogDatabase:
    """博客系統(tǒng)數(shù)據(jù)庫(kù)管理器"""
    
    def __init__(self, db_path: str = "blog_system.db"):
        self.db_conn = DatabaseConnection(db_path)
        self._init_database()
    
    def _init_database(self):
        """初始化數(shù)據(jù)庫(kù)表結(jié)構(gòu)"""
        with self.db_conn.get_connection() as conn:
            cursor = conn.cursor()
            
            # 啟用外鍵和性能優(yōu)化
            cursor.execute("PRAGMA foreign_keys = ON")
            cursor.execute("PRAGMA journal_mode = WAL")
            
            # 創(chuàng)建用戶(hù)表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    email TEXT UNIQUE NOT NULL,
                    password_hash TEXT NOT NULL,
                    bio TEXT,
                    avatar_url TEXT,
                    is_admin BOOLEAN DEFAULT 0,
                    is_active BOOLEAN DEFAULT 1,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    last_login TIMESTAMP,
                    metadata dict DEFAULT '{}',
                    
                    -- 添加索引以提高查詢(xún)性能
                    CONSTRAINT chk_username_length CHECK (length(username) >= 3),
                    CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%')
                )
            """)
            
            # 創(chuàng)建用戶(hù)表的索引
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_users_created ON users(created_at)")
            
            # 創(chuàng)建博客文章表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS blog_posts (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    slug TEXT UNIQUE NOT NULL,
                    content TEXT NOT NULL,
                    excerpt TEXT,
                    author_id INTEGER NOT NULL,
                    status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
                    tags list DEFAULT '[]',
                    category TEXT,
                    view_count INTEGER DEFAULT 0,
                    like_count INTEGER DEFAULT 0,
                    metadata dict DEFAULT '{}',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    published_at TIMESTAMP,
                    
                    -- 全文搜索列（虛擬列）
                    content_search TEXT GENERATED ALWAYS AS (lower(title || ' ' || content)),
                    
                    -- 外鍵約束
                    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
                    
                    -- 約束條件
                    CONSTRAINT chk_title_length CHECK (length(title) >= 5),
                    CONSTRAINT chk_slug_format CHECK (slug GLOB '[a-z0-9-]*')
                )
            """)
            
            # 創(chuàng)建博客文章表的索引
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_posts_author ON blog_posts(author_id)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_posts_status ON blog_posts(status)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_posts_created ON blog_posts(created_at)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_posts_published ON blog_posts(published_at)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_posts_category ON blog_posts(category)")
            
            # 創(chuàng)建全文搜索虛擬表的觸發(fā)器
            cursor.execute("""
                CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5(
                    title, content, content_search,
                    tokenize="porter unicode61"
                )
            """)
            
            # 創(chuàng)建觸發(fā)器以同步FTS表
            cursor.execute("""
                CREATE TRIGGER IF NOT EXISTS posts_ai AFTER INSERT ON blog_posts
                BEGIN
                    INSERT INTO posts_fts(rowid, title, content, content_search)
                    VALUES (new.id, new.title, new.content, new.content_search);
                END
            """)
            
            cursor.execute("""
                CREATE TRIGGER IF NOT EXISTS posts_ad AFTER DELETE ON blog_posts
                BEGIN
                    DELETE FROM posts_fts WHERE rowid = old.id;
                END
            """)
            
            cursor.execute("""
                CREATE TRIGGER IF NOT EXISTS posts_au AFTER UPDATE ON blog_posts
                BEGIN
                    DELETE FROM posts_fts WHERE rowid = old.id;
                    INSERT INTO posts_fts(rowid, title, content, content_search)
                    VALUES (new.id, new.title, new.content, new.content_search);
                END
            """)
            
            # 創(chuàng)建評(píng)論表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS comments (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    post_id INTEGER NOT NULL,
                    user_id INTEGER NOT NULL,
                    parent_id INTEGER,  -- 支持嵌套評(píng)論
                    content TEXT NOT NULL,
                    is_approved BOOLEAN DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    
                    FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
                )
            """)
            
            # 創(chuàng)建評(píng)論表的索引
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_comments_post ON comments(post_id)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_comments_user ON comments(user_id)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_comments_parent ON comments(parent_id)")
            
            conn.commit()
            logger.info("數(shù)據(jù)庫(kù)表結(jié)構(gòu)初始化完成")
    
    @staticmethod
    def _hash_password(password: str) -> str:
        """生成密碼哈希值"""
        salt = uuid.uuid4().hex
        return hashlib.sha256(salt.encode() + password.encode()).hexdigest() + ':' + salt
    
    @staticmethod
    def _check_password(hashed_password: str, user_password: str) -> bool:
        """驗(yàn)證密碼"""
        password_hash, salt = hashed_password.split(':')
        return password_hash == hashlib.sha256(salt.encode() + user_password.encode()).hexdigest()
    
    def create_user(self, username: str, email: str, password: str, bio: str = None) -> Optional[int]:
        """
        創(chuàng)建新用戶(hù)
        
        Returns:
            新用戶(hù)的ID，如果失敗則返回None
        """
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                # 檢查用戶(hù)名和郵箱是否已存在
                cursor.execute(
                    "SELECT COUNT(*) FROM users WHERE username = ? OR email = ?",
                    (username, email)
                )
                if cursor.fetchone()[0] > 0:
                    logger.warning(f"用戶(hù)名或郵箱已存在: {username}, {email}")
                    return None
                
                # 創(chuàng)建用戶(hù)
                password_hash = self._hash_password(password)
                cursor.execute("""
                    INSERT INTO users (username, email, password_hash, bio, last_login)
                    VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
                """, (username, email, password_hash, bio))
                
                user_id = cursor.lastrowid
                conn.commit()
                
                logger.info(f"用戶(hù)創(chuàng)建成功: {username} (ID: {user_id})")
                return user_id
                
        except sqlite3.Error as e:
            logger.error(f"創(chuàng)建用戶(hù)失敗: {e}")
            return None
    
    def authenticate_user(self, username: str, password: str) -> Optional[User]:
        """用戶(hù)認(rèn)證"""
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                cursor.execute("""
                    SELECT id, username, email, password_hash, bio, created_at, last_login
                    FROM users 
                    WHERE username = ? AND is_active = 1
                """, (username,))
                
                row = cursor.fetchone()
                if not row:
                    return None
                
                # 驗(yàn)證密碼
                if self._check_password(row['password_hash'], password):
                    # 更新最后登錄時(shí)間
                    cursor.execute(
                        "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?",
                        (row['id'],)
                    )
                    conn.commit()
                    
                    return User(
                        id=row['id'],
                        username=row['username'],
                        email=row['email'],
                        password_hash=row['password_hash'],
                        bio=row['bio'],
                        created_at=row['created_at'],
                        last_login=row['last_login']
                    )
                return None
                
        except sqlite3.Error as e:
            logger.error(f"用戶(hù)認(rèn)證失敗: {e}")
            return None
    
    def create_blog_post(self, title: str, content: str, author_id: int, 
                        tags: List[str] = None, category: str = None) -> Optional[int]:
        """
        創(chuàng)建博客文章
        
        Returns:
            新文章的ID，如果失敗則返回None
        """
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                # 生成slug
                import re
                slug = re.sub(r'[^a-z0-9]+', '-', title.lower()).strip('-')
                
                # 確保slug唯一
                counter = 1
                original_slug = slug
                while True:
                    cursor.execute("SELECT COUNT(*) FROM blog_posts WHERE slug = ?", (slug,))
                    if cursor.fetchone()[0] == 0:
                        break
                    slug = f"{original_slug}-{counter}"
                    counter += 1
                
                # 創(chuàng)建文章摘要
                excerpt = content[:150] + "..." if len(content) > 150 else content
                
                cursor.execute("""
                    INSERT INTO blog_posts 
                    (title, slug, content, excerpt, author_id, tags, category, created_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                """, (title, slug, content, excerpt, author_id, 
                      tags or [], category))
                
                post_id = cursor.lastrowid
                conn.commit()
                
                logger.info(f"博客文章創(chuàng)建成功: {title} (ID: {post_id})")
                return post_id
                
        except sqlite3.Error as e:
            logger.error(f"創(chuàng)建博客文章失敗: {e}")
            return None
    
    def publish_blog_post(self, post_id: int) -> bool:
        """發(fā)布博客文章"""
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                cursor.execute("""
                    UPDATE blog_posts 
                    SET status = 'published', 
                        published_at = CURRENT_TIMESTAMP,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE id = ? AND status != 'published'
                """, (post_id,))
                
                success = cursor.rowcount > 0
                conn.commit()
                
                if success:
                    logger.info(f"博客文章已發(fā)布: {post_id}")
                else:
                    logger.warning(f"博客文章發(fā)布失敗或已發(fā)布: {post_id}")
                
                return success
                
        except sqlite3.Error as e:
            logger.error(f"發(fā)布博客文章失敗: {e}")
            return False
    
    def search_posts(self, query: str, limit: int = 10, offset: int = 0) -> List[Dict]:
        """
        全文搜索博客文章
        """
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                # 使用FTS5進(jìn)行全文搜索
                cursor.execute("""
                    SELECT 
                        p.id,
                        p.title,
                        p.slug,
                        p.excerpt,
                        p.author_id,
                        u.username as author_name,
                        p.category,
                        p.tags,
                        p.view_count,
                        p.like_count,
                        p.created_at,
                        p.published_at,
                        snippet(posts_fts, 0, '<b>', '</b>', '...', 64) as snippet,
                        rank
                    FROM posts_fts f
                    JOIN blog_posts p ON f.rowid = p.id
                    JOIN users u ON p.author_id = u.id
                    WHERE posts_fts MATCH ?
                        AND p.status = 'published'
                    ORDER BY rank
                    LIMIT ? OFFSET ?
                """, (f"{query}*", limit, offset))
                
                results = []
                for row in cursor.fetchall():
                    results.append(dict(row))
                
                return results
                
        except sqlite3.Error as e:
            logger.error(f"搜索文章失敗: {e}")
            return []
    
    def get_popular_posts(self, days: int = 30, limit: int = 10) -> List[Dict]:
        """
        獲取熱門(mén)文章（基于瀏覽量和點(diǎn)贊數(shù)）
        """
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                cursor.execute("""
                    SELECT 
                        p.id,
                        p.title,
                        p.slug,
                        p.excerpt,
                        p.author_id,
                        u.username as author_name,
                        p.category,
                        p.tags,
                        p.view_count,
                        p.like_count,
                        p.created_at,
                        p.published_at,
                        -- 熱度分?jǐn)?shù) = 瀏覽量 + 點(diǎn)贊數(shù)*5
                        (p.view_count + p.like_count * 5) as hot_score
                    FROM blog_posts p
                    JOIN users u ON p.author_id = u.id
                    WHERE p.status = 'published'
                        AND p.published_at >= datetime('now', ?)
                    ORDER BY hot_score DESC
                    LIMIT ?
                """, (f"-{days} days", limit))
                
                results = []
                for row in cursor.fetchall():
                    results.append(dict(row))
                
                return results
                
        except sqlite3.Error as e:
            logger.error(f"獲取熱門(mén)文章失敗: {e}")
            return []
    
    def increment_view_count(self, post_id: int) -> bool:
        """增加文章瀏覽量"""
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "UPDATE blog_posts SET view_count = view_count + 1 WHERE id = ?",
                    (post_id,)
                )
                conn.commit()
                return cursor.rowcount > 0
                
        except sqlite3.Error as e:
            logger.error(f"增加瀏覽量失敗: {e}")
            return False
    
    def add_comment(self, post_id: int, user_id: int, content: str, 
                   parent_id: int = None) -> Optional[int]:
        """
        添加評(píng)論
        
        Returns:
            新評(píng)論的ID，如果失敗則返回None
        """
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                cursor.execute("""
                    INSERT INTO comments (post_id, user_id, parent_id, content)
                    VALUES (?, ?, ?, ?)
                """, (post_id, user_id, parent_id, content))
                
                comment_id = cursor.lastrowid
                conn.commit()
                
                logger.info(f"評(píng)論添加成功: 文章 {post_id}, 用戶(hù) {user_id}")
                return comment_id
                
        except sqlite3.Error as e:
            logger.error(f"添加評(píng)論失敗: {e}")
            return None
    
    def get_post_with_comments(self, post_id: int) -> Optional[Dict]:
        """獲取文章及其評(píng)論（嵌套結(jié)構(gòu)）"""
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                # 獲取文章信息
                cursor.execute("""
                    SELECT 
                        p.*,
                        u.username as author_name,
                        u.avatar_url as author_avatar
                    FROM blog_posts p
                    JOIN users u ON p.author_id = u.id
                    WHERE p.id = ?
                """, (post_id,))
                
                post_row = cursor.fetchone()
                if not post_row:
                    return None
                
                # 獲取評(píng)論（使用遞歸CTE獲取嵌套結(jié)構(gòu)）
                cursor.execute("""
                    WITH RECURSIVE comment_tree AS (
                        -- 頂級(jí)評(píng)論
                        SELECT 
                            c.id,
                            c.post_id,
                            c.user_id,
                            c.parent_id,
                            c.content,
                            c.is_approved,
                            c.created_at,
                            u.username,
                            u.avatar_url,
                            1 as level,
                            printf('%010d', c.id) as path
                        FROM comments c
                        JOIN users u ON c.user_id = u.id
                        WHERE c.post_id = ? AND c.parent_id IS NULL
                        
                        UNION ALL
                        
                        -- 遞歸獲取子評(píng)論
                        SELECT 
                            c.id,
                            c.post_id,
                            c.user_id,
                            c.parent_id,
                            c.content,
                            c.is_approved,
                            c.created_at,
                            u.username,
                            u.avatar_url,
                            ct.level + 1,
                            ct.path || printf('-%010d', c.id)
                        FROM comments c
                        JOIN users u ON c.user_id = u.id
                        JOIN comment_tree ct ON c.parent_id = ct.id
                        WHERE c.post_id = ?
                    )
                    SELECT * FROM comment_tree
                    ORDER BY path
                """, (post_id, post_id))
                
                comments = []
                for row in cursor.fetchall():
                    comments.append(dict(row))
                
                # 構(gòu)建結(jié)果
                result = dict(post_row)
                result['comments'] = comments
                
                return result
                
        except sqlite3.Error as e:
            logger.error(f"獲取文章詳情失敗: {e}")
            return None
    
    def backup_database(self, backup_path: str) -> bool:
        """備份數(shù)據(jù)庫(kù)"""
        try:
            with self.db_conn.get_connection() as conn:
                backup_conn = sqlite3.connect(backup_path)
                with backup_conn:
                    conn.backup(backup_conn)
                backup_conn.close()
                
                logger.info(f"數(shù)據(jù)庫(kù)備份成功: {backup_path}")
                return True
                
        except sqlite3.Error as e:
            logger.error(f"數(shù)據(jù)庫(kù)備份失敗: {e}")
            return False
    
    def get_statistics(self) -> Dict[str, Any]:
        """獲取系統(tǒng)統(tǒng)計(jì)信息"""
        try:
            with self.db_conn.get_connection() as conn:
                cursor = conn.cursor()
                
                stats = {}
                
                # 用戶(hù)統(tǒng)計(jì)
                cursor.execute("""
                    SELECT 
                        COUNT(*) as total_users,
                        COUNT(CASE WHEN is_admin THEN 1 END) as admin_users,
                        COUNT(CASE WHEN last_login >= datetime('now', '-7 days') THEN 1 END) as active_users_7d,
                        COUNT(CASE WHEN created_at >= datetime('now', '-30 days') THEN 1 END) as new_users_30d
                    FROM users
                    WHERE is_active = 1
                """)
                stats.update(dict(cursor.fetchone()))
                
                # 文章統(tǒng)計(jì)
                cursor.execute("""
                    SELECT 
                        COUNT(*) as total_posts,
                        COUNT(CASE WHEN status = 'published' THEN 1 END) as published_posts,
                        COUNT(CASE WHEN status = 'draft' THEN 1 END) as draft_posts,
                        SUM(view_count) as total_views,
                        SUM(like_count) as total_likes,
                        COUNT(CASE WHEN published_at >= datetime('now', '-7 days') THEN 1 END) as new_posts_7d
                    FROM blog_posts
                """)
                stats.update(dict(cursor.fetchone()))
                
                # 評(píng)論統(tǒng)計(jì)
                cursor.execute("""
                    SELECT 
                        COUNT(*) as total_comments,
                        COUNT(CASE WHEN is_approved THEN 1 END) as approved_comments,
                        COUNT(CASE WHEN created_at >= datetime('now', '-7 days') THEN 1 END) as new_comments_7d
                    FROM comments
                """)
                stats.update(dict(cursor.fetchone()))
                
                # 熱門(mén)分類(lèi)
                cursor.execute("""
                    SELECT 
                        category,
                        COUNT(*) as post_count,
                        SUM(view_count) as total_views
                    FROM blog_posts
                    WHERE category IS NOT NULL AND status = 'published'
                    GROUP BY category
                    ORDER BY total_views DESC
                    LIMIT 5
                """)
                stats['top_categories'] = [dict(row) for row in cursor.fetchall()]
                
                return stats
                
        except sqlite3.Error as e:
            logger.error(f"獲取統(tǒng)計(jì)信息失敗: {e}")
            return {}

def main():
    """主函數(shù) - 演示完整的博客系統(tǒng)"""
    print("=" * 60)
    print("SQLite博客系統(tǒng)演示")
    print("=" * 60)
    
    # 創(chuàng)建數(shù)據(jù)庫(kù)實(shí)例
    blog_db = BlogDatabase("blog_demo.db")
    
    # 創(chuàng)建測(cè)試用戶(hù)
    print("\n1. 創(chuàng)建測(cè)試用戶(hù)...")
    user_id = blog_db.create_user(
        username="techwriter",
        email="writer@example.com",
        password="securepassword123",
        bio="技術(shù)作家，熱愛(ài)分享知識(shí)"
    )
    
    if not user_id:
        print("用戶(hù)創(chuàng)建失敗或用戶(hù)已存在，嘗試認(rèn)證...")
        user = blog_db.authenticate_user("techwriter", "securepassword123")
        if user:
            user_id = user.id
            print(f"用戶(hù)認(rèn)證成功: {user.username}")
        else:
            print("無(wú)法創(chuàng)建或認(rèn)證用戶(hù)，退出演示")
            return
    
    # 創(chuàng)建博客文章
    print("\n2. 創(chuàng)建博客文章...")
    post_content = """
    # SQLite在現(xiàn)代Web開(kāi)發(fā)中的革命性作用
    
    在當(dāng)今快速迭代的開(kāi)發(fā)環(huán)境中，SQLite已經(jīng)從簡(jiǎn)單的嵌入式數(shù)據(jù)庫(kù)演變?yōu)橐粋€(gè)能夠支撐中等規(guī)模生產(chǎn)應(yīng)用的強(qiáng)大工具。
    
    ## 為什么SQLite被低估了？
    
    大多數(shù)開(kāi)發(fā)者認(rèn)為SQLite只適合小型項(xiàng)目或原型開(kāi)發(fā)，但這種看法已經(jīng)過(guò)時(shí)了。SQLite 3.0引入的WAL模式、全文搜索和JSON支持，使其具備了處理復(fù)雜應(yīng)用的能力。
    
    ## 關(guān)鍵技術(shù)特性
    
    1. **無(wú)服務(wù)器架構(gòu)**：不需要獨(dú)立的數(shù)據(jù)庫(kù)服務(wù)器
    2. **零配置**：開(kāi)箱即用
    3. **ACID合規(guī)**：完整的事務(wù)支持
    4. **全文搜索**：內(nèi)置FTS5擴(kuò)展
    5. **JSON支持**：直接處理JSON數(shù)據(jù)
    6. **并發(fā)控制**：WAL模式支持多讀單寫(xiě)
    
    ## 生產(chǎn)環(huán)境最佳實(shí)踐
    
    - 使用WAL模式提高并發(fā)性能
    - 合理設(shè)計(jì)索引優(yōu)化查詢(xún)
    - 定期備份和優(yōu)化數(shù)據(jù)庫(kù)
    - 使用連接池管理數(shù)據(jù)庫(kù)連接
    
    SQLite正在改變我們對(duì)輕量級(jí)數(shù)據(jù)庫(kù)的認(rèn)知，它不再是"玩具數(shù)據(jù)庫(kù)"，而是現(xiàn)代應(yīng)用架構(gòu)中的重要組件。
    """
    
    post_id = blog_db.create_blog_post(
        title="SQLite在現(xiàn)代Web開(kāi)發(fā)中的革命性作用",
        content=post_content,
        author_id=user_id,
        tags=["sqlite", "database", "web-development", "python"],
        category="Technology"
    )
    
    if post_id:
        print(f"文章創(chuàng)建成功，ID: {post_id}")
        
        # 發(fā)布文章
        if blog_db.publish_blog_post(post_id):
            print("文章已成功發(fā)布")
        
        # 增加瀏覽量
        for _ in range(5):
            blog_db.increment_view_count(post_id)
        print("模擬了5次文章瀏覽")
    
    # 創(chuàng)建第二篇文章
    print("\n3. 創(chuàng)建第二篇文章...")
    post2_id = blog_db.create_blog_post(
        title="Python數(shù)據(jù)持久化策略比較",
        content="本文比較了Python中各種數(shù)據(jù)持久化方案...",
        author_id=user_id,
        tags=["python", "database", "persistence"],
        category="Programming"
    )
    
    if post2_id:
        blog_db.publish_blog_post(post2_id)
        print("第二篇文章創(chuàng)建并發(fā)布成功")
    
    # 添加評(píng)論
    print("\n4. 添加評(píng)論...")
    blog_db.add_comment(
        post_id=post_id,
        user_id=user_id,
        content="非常好的文章！SQLite確實(shí)被很多人低估了。"
    )
    
    blog_db.add_comment(
        post_id=post_id,
        user_id=user_id,
        content="請(qǐng)問(wèn)WAL模式和普通模式的具體性能差異有多大？",
        parent_id=1  # 回復(fù)第一條評(píng)論
    )
    
    # 搜索文章
    print("\n5. 搜索文章...")
    search_results = blog_db.search_posts("SQLite 開(kāi)發(fā)")
    print(f"搜索到 {len(search_results)} 篇文章:")
    for result in search_results:
        print(f"  - {result['title']} (相關(guān)性分?jǐn)?shù): {result['rank']:.2f})")
        print(f"    摘要: {result['snippet'][:100]}...")
    
    # 獲取熱門(mén)文章
    print("\n6. 熱門(mén)文章排名...")
    popular_posts = blog_db.get_popular_posts(days=30, limit=3)
    print("本月熱門(mén)文章:")
    for i, post in enumerate(popular_posts, 1):
        print(f"  {i}. {post['title']}")
        print(f"     瀏覽量: {post['view_count']}, 點(diǎn)贊: {post['like_count']}, 熱度: {post['hot_score']}")
    
    # 獲取文章詳情
    print("\n7. 獲取文章詳情...")
    post_details = blog_db.get_post_with_comments(post_id)
    if post_details:
        print(f"文章標(biāo)題: {post_details['title']}")
        print(f"作者: {post_details['author_name']}")
        print(f"標(biāo)簽: {', '.join(post_details['tags'])}")
        print(f"瀏覽量: {post_details['view_count']}")
        print(f"評(píng)論數(shù): {len(post_details['comments'])}")
        
        # 顯示評(píng)論
        for comment in post_details['comments']:
            indent = "  " * (comment['level'] - 1)
            print(f"{indent}├─ {comment['username']}: {comment['content'][:50]}...")
    
    # 獲取系統(tǒng)統(tǒng)計(jì)
    print("\n8. 系統(tǒng)統(tǒng)計(jì)信息...")
    stats = blog_db.get_statistics()
    print(f"總用戶(hù)數(shù): {stats.get('total_users', 0)}")
    print(f"總文章數(shù): {stats.get('total_posts', 0)}")
    print(f"已發(fā)布文章: {stats.get('published_posts', 0)}")
    print(f"總瀏覽量: {stats.get('total_views', 0)}")
    print(f"總評(píng)論數(shù): {stats.get('total_comments', 0)}")
    
    # 備份數(shù)據(jù)庫(kù)
    print("\n9. 備份數(shù)據(jù)庫(kù)...")
    if blog_db.backup_database("blog_demo_backup.db"):
        print("數(shù)據(jù)庫(kù)備份成功")
    
    print("\n" + "=" * 60)
    print("演示完成！")
    print("數(shù)據(jù)庫(kù)文件: blog_demo.db")
    print("備份文件: blog_demo_backup.db")
    print("=" * 60)

if __name__ == "__main__":
    main()

# 啟用WAL模式
conn.execute("PRAGMA journal_mode = WAL")

# 創(chuàng)建全文搜索虛擬表
CREATE VIRTUAL TABLE posts_fts USING fts5(...)

# 自定義類(lèi)型適配器
sqlite3.register_adapter(dict, adapt_dict)
sqlite3.register_converter("dict", convert_dict)

# 使用遞歸CTE獲取嵌套評(píng)論
WITH RECURSIVE comment_tree AS (...)