cli.py

#!/usr/bin/env python3
"""
事件管理系统 - 命令行工具
快速管理数据库的CLI工具 (SQLite版本)
"""

import sys
import sqlite3
import os
import shutil
from datetime import datetime
from pathlib import Path

# 配置
DB_FILE = "kefu.db"  # SQLite数据库文件
DB_DIR = "database"

# ANSI颜色
GREEN = "\033[92m"
BLUE = "\033[94m"
YELLOW = "\033[93m"
RED = "\033[91m"
CYAN = "\033[96m"
RESET = "\033[0m"


def print_header(text):
    """打印标题"""
    print(f"\n{BLUE}{'=' * 50}{RESET}")
    print(f"{BLUE}{text:^50}{RESET}")
    print(f"{BLUE}{'=' * 50}{RESET}\n")


def print_success(text):
    """打印成功信息"""
    # 移除可能导致编码问题的特殊字符
    text = text.replace('✓', '[OK]').replace('✗', '[X]').replace('→', '->').replace('⚠', '[!]')
    try:
        print(f"{GREEN}[OK] {text}{RESET}")
    except UnicodeEncodeError:
        print(f"[OK] {text}")


def print_error(text):
    """打印错误信息"""
    text = text.replace('✓', '[OK]').replace('✗', '[X]').replace('→', '->').replace('⚠', '[!]')
    try:
        print(f"{RED}[X] {text}{RESET}")
    except UnicodeEncodeError:
        print(f"[X] {text}")


def print_info(text):
    """打印提示信息"""
    text = text.replace('✓', '[OK]').replace('✗', '[X]').replace('→', '->').replace('⚠', '[!]')
    try:
        print(f"{YELLOW}-> {text}{RESET}")
    except UnicodeEncodeError:
        print(f"-> {text}")


def print_warning(text):
    """打印警告信息"""
    text = text.replace('✓', '[OK]').replace('✗', '[X]').replace('→', '->').replace('⚠', '[!]')
    try:
        print(f"{CYAN}[!] {text}{RESET}")
    except UnicodeEncodeError:
        print(f"[!] {text}")


def get_db_connection():
    """获取数据库连接"""
    try:
        conn = sqlite3.connect(DB_FILE)
        conn.row_factory = sqlite3.Row  # 支持字典式访问
        return conn
    except Exception as e:
        print_error(f"连接数据库失败: {str(e)}")
        return None


def execute_sql(sql, params=None):
    """执行SQL命令"""
    conn = get_db_connection()
    if not conn:
        return None
    
    try:
        cursor = conn.cursor()
        if params:
            cursor.execute(sql, params)
        else:
            cursor.execute(sql)
        conn.commit()
        return cursor
    except Exception as e:
        print_error(f"执行SQL失败: {str(e)}")
        conn.rollback()
        return None
    finally:
        conn.close()


def execute_sql_file(filepath):
    """执行SQL文件"""
    if not os.path.exists(filepath):
        print_error(f"文件不存在: {filepath}")
        return False
    
    try:
        with open(filepath, 'r', encoding='utf-8') as f:
            sql_script = f.read()
        
        conn = get_db_connection()
        if not conn:
            return False
        
        cursor = conn.cursor()
        cursor.executescript(sql_script)
        conn.commit()
        conn.close()
        return True
    except Exception as e:
        print_error(f"执行SQL文件失败: {str(e)}")
        return False


def cmd_init():
    """初始化数据库"""
    print_header("初始化数据库")
    
    # 如果数据库文件已存在，先备份
    if os.path.exists(DB_FILE):
        backup_name = f"{DB_FILE}.backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        print_info(f"数据库已存在，备份为: {backup_name}")
        shutil.copy2(DB_FILE, backup_name)
        os.remove(DB_FILE)
    
    # 执行schema
    print_info("创建表结构...")
    schema_file = os.path.join(DB_DIR, "schema.sql")
    if execute_sql_file(schema_file):
        print_success("表结构创建成功")
    else:
        return
    
    # 导入测试数据
    print_info("导入测试数据...")
    seed_file = os.path.join(DB_DIR, "seed_data.sql")
    if execute_sql_file(seed_file):
        print_success("测试数据导入成功")
    
    print_success(f"\n数据库初始化完成！")
    print_info(f"数据库文件: {DB_FILE}")


def cmd_reset():
    """重置数据库（重新执行schema和seed）"""
    print_header("重置数据库")
    
    # 删除旧数据库
    if os.path.exists(DB_FILE):
        print_info("删除旧数据库...")
        os.remove(DB_FILE)
    
    # 重新创建
    print_info("重新创建表结构...")
    schema_file = os.path.join(DB_DIR, "schema.sql")
    if execute_sql_file(schema_file):
        print_success("表结构创建成功")
    else:
        return
    
    print_info("重新导入测试数据...")
    seed_file = os.path.join(DB_DIR, "seed_data.sql")
    if execute_sql_file(seed_file):
        print_success("测试数据导入成功")
    
    print_success("\n数据库重置完成！")


def cmd_test():
    """运行测试"""
    print_header("运行测试")
    test_file = os.path.join(DB_DIR, "test_queries.sql")
    
    if not os.path.exists(test_file):
        print_warning("测试文件不存在")
        return
    
    print_info("执行测试查询...")
    if execute_sql_file(test_file):
        print_success("测试执行完成")


def cmd_status():
    """查看数据库状态"""
    print_header("数据库状态")
    
    if not os.path.exists(DB_FILE):
        print_error("数据库不存在")
        print_info("请先运行 'python cli.py init' 初始化数据库")
        return
    
    conn = get_db_connection()
    if not conn:
        return
    
    try:
        cursor = conn.cursor()
        
        # 统计数据
        stats = []
        
        # 事件总数
        cursor.execute("SELECT COUNT(*) FROM events")
        total = cursor.fetchone()[0]
        stats.append(("事件总数", total))
        
        # 待处理
        cursor.execute("SELECT COUNT(*) FROM events WHERE status = 'pending'")
        pending = cursor.fetchone()[0]
        stats.append(("待处理", pending))
        
        # 已完成
        cursor.execute("SELECT COUNT(*) FROM events WHERE status = 'completed'")
        completed = cursor.fetchone()[0]
        stats.append(("已完成", completed))
        
        # 紧急事件
        cursor.execute("SELECT COUNT(*) FROM events WHERE is_urgent = 1")
        urgent = cursor.fetchone()[0]
        stats.append(("紧急事件", urgent))
        
        # 跟进记录
        cursor.execute("SELECT COUNT(*) FROM followups")
        followups = cursor.fetchone()[0]
        stats.append(("跟进记录", followups))
        
        # 打印结果
        print(f"{'指标':<12} | 数值")
        print("-" * 25)
        for name, value in stats:
            print(f"{name:<12} | {value}")
        
        conn.close()
    except Exception as e:
        print_error(f"查询失败: {str(e)}")


def cmd_list():
    """列出所有事件"""
    print_header("事件列表")
    
    if not os.path.exists(DB_FILE):
        print_error("数据库不存在")
        print_info("请先运行 'python cli.py init' 初始化数据库")
        return
    
    conn = get_db_connection()
    if not conn:
        return
    
    try:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT 
                id,
                SUBSTR(title, 1, 30) as title,
                CASE 
                    WHEN status = 'pending' THEN '进行中'
                    ELSE '已完成'
                END as status,
                CASE 
                    WHEN is_urgent = 1 THEN '紧急'
                    ELSE '普通'
                END as priority,
                DATE(created_at) as created_date
            FROM events
            ORDER BY is_urgent DESC, updated_at DESC
            LIMIT 10
        """)
        
        rows = cursor.fetchall()
        
        if rows:
            print(f"{'ID':<10} | {'标题':<30} | {'状态':<8} | {'优先级':<6} | 创建日期")
            print("-" * 85)
            for row in rows:
                print(f"{row[0]:<10} | {row[1]:<30} | {row[2]:<8} | {row[3]:<6} | {row[4]}")
        else:
            print_info("暂无事件")
        
        conn.close()
    except Exception as e:
        print_error(f"查询失败: {str(e)}")


def cmd_urgent():
    """查看紧急事件"""
    print_header("紧急事件")
    
    if not os.path.exists(DB_FILE):
        print_error("数据库不存在")
        return
    
    conn = get_db_connection()
    if not conn:
        return
    
    try:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT 
                id,
                SUBSTR(title, 1, 30) as title,
                urgent_deadline,
                CASE 
                    WHEN datetime(urgent_deadline) < datetime('now') THEN '已超时'
                    ELSE '进行中'
                END as time_status
            FROM events
            WHERE is_urgent = 1 AND status = 'pending'
            ORDER BY urgent_deadline ASC
        """)
        
        rows = cursor.fetchall()
        
        if rows:
            print(f"{'ID':<10} | {'标题':<30} | {'截止时间':<20} | 状态")
            print("-" * 75)
            for row in rows:
                print(f"{row[0]:<10} | {row[1]:<30} | {row[2]:<20} | {row[3]}")
        else:
            print_info("暂无紧急事件")
        
        conn.close()
    except Exception as e:
        print_error(f"查询失败: {str(e)}")


def cmd_search(keyword):
    """搜索事件"""
    print_header(f"搜索: {keyword}")
    
    if not os.path.exists(DB_FILE):
        print_error("数据库不存在")
        return
    
    conn = get_db_connection()
    if not conn:
        return
    
    try:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT 
                id,
                title,
                order_number
            FROM events
            WHERE title LIKE ? 
               OR order_number LIKE ?
               OR description LIKE ?
            LIMIT 10
        """, (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        
        rows = cursor.fetchall()
        
        if rows:
            print(f"{'ID':<10} | {'标题':<40} | 单号")
            print("-" * 75)
            for row in rows:
                print(f"{row[0]:<10} | {row[1]:<40} | {row[2]}")
        else:
            print_info("未找到匹配的事件")
        
        conn.close()
    except Exception as e:
        print_error(f"搜索失败: {str(e)}")


def cmd_show(event_id):
    """查看事件详情"""
    print_header(f"事件详情: {event_id}")
    
    if not os.path.exists(DB_FILE):
        print_error("数据库不存在")
        return
    
    conn = get_db_connection()
    if not conn:
        return
    
    try:
        cursor = conn.cursor()
        
        # 查询事件信息
        cursor.execute("""
            SELECT 
                id, title, order_number, creator, description,
                status, created_at, updated_at
            FROM events
            WHERE id = ?
        """, (event_id,))
        
        event = cursor.fetchone()
        
        if not event:
            print_error(f"事件 {event_id} 不存在")
            conn.close()
            return
        
        print(f"ID: {event[0]}")
        print(f"标题: {event[1]}")
        print(f"单号: {event[2]}")
        print(f"创建人: {event[3]}")
        print(f"描述: {event[4] or '无'}")
        print(f"状态: {event[5]}")
        print(f"创建时间: {event[6]}")
        print(f"更新时间: {event[7]}")
        
        # 查询跟进记录
        print_info("\n跟进记录:")
        cursor.execute("""
            SELECT follower, content, created_at
            FROM followups
            WHERE event_id = ?
            ORDER BY created_at
        """, (event_id,))
        
        followups = cursor.fetchall()
        
        if followups:
            for i, f in enumerate(followups, 1):
                print(f"\n{i}. [{f[0]}] {f[2]}")
                print(f"   {f[1
                ]}")
        else:
            print("   暂无跟进记录")
        
        conn.close()
    except Exception as e:
        print_error(f"查询失败: {str(e)}")


def cmd_backup():
    """备份数据库"""
    print_header("备份数据库")
    
    if not os.path.exists(DB_FILE):
        print_error("数据库文件不存在")
        return
    
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    backup_file = f"backup_{timestamp}.db"
    
    print_info(f"备份到: {backup_file}")
    try:
        shutil.copy2(DB_FILE, backup_file)
        
        # 显示文件大小
        size = os.path.getsize(backup_file)
        size_mb = size / 1024 / 1024
        print_success(f"备份成功: {backup_file} ({size_mb:.2f} MB)")
    except Exception as e:
        print_error(f"备份失败: {str(e)}")


def cmd_shell():
    """打开SQLite交互式终端"""
    if not os.path.exists(DB_FILE):
        print_error("数据库文件不存在")
        print_info("请先运行 'python cli.py init' 初始化数据库")
        return
    
    print_info(f"连接到数据库 {DB_FILE}...")
    print_info("输入 .quit 退出")
    print_info("输入 .tables 查看所有表")
    print_info("输入 .schema 表名 查看表结构")
    print()
    
    try:
        import subprocess
        subprocess.run(["sqlite3", DB_FILE])
    except FileNotFoundError:
        print_warning("未找到sqlite3命令")
        print_info("你可以使用Python方式查询:")
        print(f"  {CYAN}python -c \"import sqlite3; conn=sqlite3.connect('{DB_FILE}'); ...\" {RESET}")


def cmd_check():
    """检查环境配置"""
    print_header("环境检查")
    
    # 检查Python版本
    print_info("检查Python版本...")
    py_version = f"{sys.version_info.major}.{sys.version_info.minor}.{sys.version_info.micro}"
    if sys.version_info >= (3, 7):
        print_success(f"Python {py_version}")
    else:
        print_error(f"Python {py_version} (需要 3.7+)")
    
    # 检查SQLite支持
    print_info("\n检查SQLite...")
    try:
        import sqlite3
        print_success(f"SQLite {sqlite3.sqlite_version}")
    except ImportError:
        print_error("SQLite 模块不可用")
        return
    
    # 检查数据库目录
    print_info("\n检查SQL脚本...")
    files_to_check = ["schema.sql", "seed_data.sql"]
    all_exist = True
    for filename in files_to_check:
        filepath = os.path.join(DB_DIR, filename)
        if os.path.exists(filepath):
            print_success(f"{filename}")
        else:
            print_error(f"{filename} 缺失")
            all_exist = False
    
    if not all_exist:
        print_warning(f"\n请确保 {DB_DIR}/ 目录下有完整的SQL脚本")
        return
    
    # 检查数据库文件
    print_info(f"\n检查数据库文件...")
    if os.path.exists(DB_FILE):
        size = os.path.getsize(DB_FILE)
        size_kb = size / 1024
        print_success(f"数据库文件存在: {DB_FILE} ({size_kb:.2f} KB)")
        
        # 检查表是否存在
        print_info("\n检查数据库表...")
        conn = get_db_connection()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
            tables = cursor.fetchall()
            
            if tables:
                table_names = [t[0] for t in tables]
                if 'events' in table_names and 'followups' in table_names:
                    print_success(f"数据库表完整")
                    for table in table_names:
                        print(f"  - {table}")
                else:
                    print_warning("数据库表不完整")
                    print_info("请运行 'python cli.py init' 初始化数据库")
            else:
                print_warning("数据库为空")
            
            conn.close()
    else:
        print_warning(f"数据库文件不存在: {DB_FILE}")
        print_info("请运行 'python cli.py init' 初始化数据库")
    
    # 显示配置信息
    print_info("\n当前配置:")
    print(f"  数据库类型: SQLite")
    print(f"  数据库文件: {DB_FILE}")
    print(f"  SQL脚本目录: {DB_DIR}/")
    
    print_success("\n环境检查完成")


def cmd_version():
    """显示版本信息"""
    print_header("版本信息")
    print(f"事件管理系统 CLI v2.0.0 (SQLite版)")
    print(f"Python {sys.version}")
    try:
        import sqlite3
        print(f"SQLite {sqlite3.sqlite_version}")
    except ImportError:
        print("SQLite: 不可用")


def print_help():
    """打印帮助信息"""
    print_header("事件管理系统 - CLI工具 (SQLite版)")
    
    commands = [
        ("check", "检查环境配置（推荐首次运行）"),
        ("init", "初始化数据库（创建表、导入测试数据）"),
        ("reset", "重置数据库（删除并重建）"),
        ("status", "查看数据库状态统计"),
        ("list", "列出最近的事件（前10条）"),
        ("urgent", "查看所有紧急事件"),
        ("search <关键词>", "搜索事件"),
        ("show <事件ID>", "查看事件详情和跟进记录"),
        ("backup", "备份数据库（复制文件）"),
        ("shell", "打开SQLite交互式终端"),
        ("version", "显示版本信息"),
        ("help", "显示此帮助信息"),
    ]
    
    print("使用方法:")
    print(f"  {YELLOW}python cli.py <命令> [参数]{RESET}\n")
    
    print("可用命令:")
    for cmd, desc in commands:
        print(f"  {GREEN}{cmd:20}{RESET} {desc}")
    
    print(f"\n推荐流程:")
    print(f"  {CYAN}1. python cli.py check      {RESET}# 首次运行，检查环境")
    print(f"  {CYAN}2. python cli.py init       {RESET}# 初始化数据库")
    print(f"  {CYAN}3. python cli.py status     {RESET}# 验证安装")
    
    print(f"\n示例:")
    print(f"  python cli.py check         # 检查环境")
    print(f"  python cli.py init          # 初始化数据库")
    print(f"  python cli.py list          # 列出事件")
    print(f"  python cli.py search 张女士  # 搜索事件")
    print(f"  python cli.py show evt_001  # 查看详情")
    print(f"  python cli.py backup        # 备份数据库")
    
    print(f"\n优势:")
    print(f"  [OK] 无需安装数据库软件")
    print(f"  [OK] 单文件数据库，易于备份")
    print(f"  [OK] 跨平台，开箱即用")
    print()


def main():
    """主函数"""
    if len(sys.argv) < 2:
        print_help()
        return
    
    command = sys.argv[1].lower()
    
    commands = {
        "check": cmd_check,
        "init": cmd_init,
        "reset": cmd_reset,
        "test": cmd_test,
        "status": cmd_status,
        "list": cmd_list,
        "urgent": cmd_urgent,
        "backup": cmd_backup,
        "shell": cmd_shell,
        "version": cmd_version,
        "help": print_help,
    }
    
    if command in commands:
        commands[command]()
    elif command == "search":
        if len(sys.argv) < 3:
            print_error("请提供搜索关键词")
            print_info("用法: python cli.py search <关键词>")
        else:
            cmd_search(sys.argv[2])
    elif command == "show":
        if len(sys.argv) < 3:
            print_error("请提供事件ID")
            print_info("用法: python cli.py show <事件ID>")
        else:
            cmd_show(sys.argv[2])
    else:
        print_error(f"未知命令: {command}")
        print_info("使用 'python cli.py help' 查看帮助")


if __name__ == "__main__":
    main()