#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
SQLite 数据库操作模块
支持基本的增删改查操作
"""

import sqlite3


class SQLiteDatabase:
    """SQLite 数据库连接和操作类"""
    
    def __init__(self, database='database.db'):
        """
        初始化 SQLite 连接参数
        :param database: 数据库文件路径
        """
        self.database = database
        self.connection = None
    
    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = sqlite3.connect(self.database)
            # 设置行工厂，使查询结果以字典形式返回
            self.connection.row_factory = sqlite3.Row
            return True, f"SQLite 连接成功 (数据库: {self.database})"
        except Exception as e:
            return False, f"SQLite 连接失败: {str(e)}"
    
    def execute(self, sql, params=None):
        """
        执行 SQL 语句或 SQLite 点命令
        :param sql: SQL 语句或点命令（如 .tables, .schema）
        :param params: 参数（可选）
        :return: 执行结果
        """
        if not self.connection:
            return False, "未连接到数据库"
        
        # 处理 SQLite 点命令
        cmd = sql.strip()
        if cmd.startswith('.'):
            try:
                # .tables - 列出所有表
                if cmd in ['.tables', '.table']:
                    cursor = self.connection.cursor()
                    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
                    tables = [row[0] for row in cursor.fetchall()]
                    return True, tables
                
                # .schema - 显示所有表的结构
                elif cmd == '.schema':
                    cursor = self.connection.cursor()
                    cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND sql IS NOT NULL ORDER BY name")
                    schemas = [row[0] for row in cursor.fetchall()]
                    return True, schemas
                
                # .schema <table> - 显示特定表的结构
                elif cmd.startswith('.schema '):
                    table_name = cmd.split(maxsplit=1)[1]
                    cursor = self.connection.cursor()
                    cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
                    result = cursor.fetchone()
                    if result:
                        return True, result[0]
                    else:
                        return False, f"表 '{table_name}' 不存在"
                
                # .databases - 列出数据库信息
                elif cmd in ['.databases', '.database']:
                    return True, [f"main: {self.database}"]
                
                # .indexes - 列出所有索引
                elif cmd in ['.indexes', '.index']:
                    cursor = self.connection.cursor()
                    cursor.execute("SELECT name FROM sqlite_master WHERE type='index' ORDER BY name")
                    indexes = [row[0] for row in cursor.fetchall()]
                    return True, indexes
                
                # .indexes <table> - 列出特定表的索引
                elif cmd.startswith('.indexes ') or cmd.startswith('.index '):
                    table_name = cmd.split(maxsplit=1)[1]
                    cursor = self.connection.cursor()
                    cursor.execute("SELECT name FROM sqlite_master WHERE type='index' AND tbl_name=? ORDER BY name", (table_name,))
                    indexes = [row[0] for row in cursor.fetchall()]
                    return True, indexes
                
                else:
                    return False, f"不支持的点命令: {cmd.split()[0]}"
                    
            except Exception as e:
                return False, f"执行失败: {str(e)}"
        
        # 执行标准 SQL 语句
        try:
            cursor = self.connection.cursor()
            cursor.execute(sql, params or ())
            
            # 判断是查询还是修改操作
            if sql.strip().upper().startswith(('SELECT', 'PRAGMA')):
                rows = cursor.fetchall()
                # 转换为字典列表
                result = [dict(row) for row in rows]
                return True, result
            else:
                self.connection.commit()
                return True, f"影响行数: {cursor.rowcount}"
        except Exception as e:
            self.connection.rollback()
            return False, f"执行失败: {str(e)}"
    
    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None
            return "SQLite 连接已关闭"
        return "连接已经关闭"