import sqlite3
import logging
from typing import Tuple, Union, List, Optional

# 定义一个装饰器，用于处理数据库操作中的异常
def db_exception_handler(func):
    from functools import wraps
    @wraps(func)
    def wrapper(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except sqlite3.DatabaseError as db_err:
            logging.error(f"Database operation failed: {db_err}")
            raise DBOperationError(f"Database operation error: {db_err}")
        except sqlite3.IntegrityError as int_err:
            logging.error(f"Integrity constraint violated: {int_err}")
            raise DBOperationError(f"Integrity error: {int_err}")
        except ValueError as val_err:
            logging.error(f"Value error: {val_err}")
            raise ValueError(f"Invalid value: {val_err}")
        except TypeError as type_err:
            logging.error(f"Type error: {type_err}")
            raise TypeError(f"Invalid type: {type_err}")
        except Exception as e:
            logging.error(f"An unexpected error occurred: {e}")
            raise DBOperationError(f"Unexpected database error: {e}")
    return wrapper

# 定义一个数据库接口类，用于处理SQLite数据库操作
class SQLiteInterface:
    def __init__(self, db_path: str = 'family_financial_data.db'):
        self.db_path = db_path  # 数据库文件路径

    @db_exception_handler
    def run_sql(self, sql: str, params: Optional[Union[Tuple, List[Tuple]]] = None, is_select: bool = False, return_id: bool = False):
        # 使用上下文管理器连接数据库，确保在操作完成后自动关闭连接
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()  # 创建游标对象
            if isinstance(params, list):
                cursor.executemany(sql, params)  # 执行多条SQL语句
            else:
                cursor.execute(sql, params if params else ())  # 执行单条SQL语句
            if is_select:
                return cursor.fetchall()  # 如果是查询操作，返回所有结果
            else:
                conn.commit()  # 提交非查询操作
                if return_id:
                    return cursor.lastrowid  # 返回最后插入行的ID

    @db_exception_handler
    def read(self, sql: str, params: Optional[Tuple] = None):
        return self.run_sql(sql, params, is_select=True)  # 读取操作

    @db_exception_handler
    def write(self, sql: str, params: Optional[Tuple] = None, return_id: bool = False):
        return self.run_sql(sql, params, return_id=return_id)  # 写入操作

    @db_exception_handler
    def batch_write(self, sql: str, params_list: List[Tuple]):
        self.run_sql(sql, params_list)  # 批量写入操作

# 自定义异常类，用于捕获数据库操作中的异常
class DBOperationError(Exception):
    """Exception raised for errors that occur during database operations."""
    def __init__(self, message=None):
        super().__init__(message or "An error occurred during a database operation.")

""" 以下为接口测试代码，如需测试接口，请删除注释符号
# 主程序入口，进行数据库表的创建和数据插入
if __name__ == "__main__":
    db_interface = SQLiteInterface()
    
    # SQL语句：创建ActivityCategories表
    create_table_sql = '''
    CREATE TABLE IF NOT EXISTS ActivityCategories (
    activity_id INTEGER PRIMARY KEY AUTOINCREMENT,
    activity_name TEXT NOT NULL UNIQUE,
    category TEXT NOT NULL CHECK (category IN ('Operating', 'Investing', 'Financing')),
    direction TEXT NOT NULL CHECK (direction IN ('Inflow', 'Outflow')),
    description TEXT,
    parent_category_id INTEGER,
    FOREIGN KEY (parent_category_id) REFERENCES ActivityCategories (activity_id)
    );
    '''

    # SQL语句：插入示例数据到ActivityCategories表
    insert_data_sql = '''
    INSERT INTO ActivityCategories (activity_id, activity_name, category, direction, description, parent_category_id) VALUES
    -- 经营活动
    (10, 'SalaryIncome', 'Operating', 'Inflow', '提供劳务收到的现金', NULL),
    (11, 'GoodIncome', 'Operating', 'Inflow', '销售边际成本为零的产品（杠杆品）收到的现金', NULL),
    (12, 'OtherIncome', 'Operating', 'Inflow', '意外收到的现金', NULL),
    (13, 'NoneIncome', 'Operating', 'Inflow', '未消费收到的现金退款', NULL),
    (14, 'FixedExpenses', 'Operating', 'Outflow', '必须且固定的现金支出', NULL),
    (15, 'DailyExpenses', 'Operating', 'Outflow', '必须但不固定的现金支出', NULL),
    (16, 'PersonalizedExpenses', 'Operating', 'Outflow', '不必须的现金支出', NULL),
    -- 投资活动
    (30, 'InvestmentLiquidation', 'Investing', 'Inflow', '收回投资收到的现金', NULL),
    (31, 'InvestmentReturns', 'Investing', 'Inflow', '取得投资收益收到的现金', NULL),
    (32, 'CashflowFromCapEx', 'Investing', 'Inflow', '处置固定资产、无形资产和其他长期资产收回的现金净额', NULL),
    (33, 'DefensiveAssets', 'Investing', 'Outflow', '购建防守资产支付的现金', NULL),
    (34, 'StableAssets', 'Investing', 'Outflow', '购建稳健资产支付的现金', NULL),
    (35, 'AggressiveAssets', 'Investing', 'Outflow', '购建进攻资产支付的现金', NULL),
    (36, 'PerpetualAssets', 'Investing', 'Outflow', '购建永续资产支付的现金', NULL),
    (37, 'CapitalExpenditures', 'Investing', 'Outflow', '购建固定资产、无形资产和其他长期资产支付的现金', NULL),
    (40, 'USInvestmentLiquidation', 'Investing', 'Inflow', '收回投资收到的美元/港币现金', NULL),
    (41, 'USInvestmentReturns', 'Investing', 'Inflow', '取得投资收益收到的美元/港币现金', NULL),
    (42, 'USStableAssets', 'Investing', 'Outflow', '购建稳健资产支付的美元/港币现金', NULL),
    (43, 'USAggressiveAssets', 'Investing', 'Outflow', '购建进攻资产支付的美元/港币现金', NULL),
    -- 融资活动 
    (70, 'ProceedsfromIssuingShares', 'Financing', 'Inflow', '吸收投资收到的现金', NULL),
    (71, 'ProceedsfromBorrowings', 'Financing', 'Inflow', '取得借款收到的现金', NULL),
    (72, 'ShortTermLoans', 'Financing', 'Outflow', '短期债务', NULL),
    (73, 'DebtRepayments', 'Financing', 'Outflow', '中长期债务', NULL),
    (74, 'FamilyFunds', 'Financing', 'Outflow', '家庭共同基金', NULL);
    '''
    
    # 执行创建表的SQL语句
    db_interface.run_sql(create_table_sql)
    # 执行插入数据的SQL语句
    db_interface.run_sql(insert_data_sql)
"""