#!/usr/bin/env bash
# 逻辑库变更任务信息查看脚本
# 用于在命令行界面查看逻辑库变更任务的详细信息
# 根据 schedule_schedule 表的 id 进行查询
# 使用 obclient 客户端工具


set -e


# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color


# 数据库连接配置（从环境变量读取）
DB_HOST="${ODC_DATABASE_HOST:-localhost}"
DB_PORT="${ODC_DATABASE_PORT:-2883}"
DB_NAME="${ODC_DATABASE_NAME:-odc}"
DB_USER="${ODC_DATABASE_USERNAME:-root}"
DB_PASSWORD="${ODC_DATABASE_PASSWORD:-}"


# 构建用户字符串（格式：user@tenant#cluster）
build_user_string() {
    local user="$DB_USER"
    if [ -n "$DB_TENANT" ]; then
        user="${user}@${DB_TENANT}"
    fi
    if [ -n "$DB_CLUSTER" ]; then
        user="${user}#${DB_CLUSTER}"
    fi
    echo "$user"
}


# 构建 obclient 连接命令基础部分
build_obclient_base() {
    local user_string=$(build_user_string)
    
    local cmd="obclient"
    cmd="${cmd} -h${DB_HOST}"
    cmd="${cmd} -P${DB_PORT}"
    cmd="${cmd} -u${user_string}"
    cmd="${cmd} -D${DB_NAME}"
    
    if [ -n "$DB_PASSWORD" ]; then
        cmd="${cmd} -p${DB_PASSWORD}"
    fi
    
    echo "$cmd"
}


# 执行 SQL 查询并返回结果（不显示表头）
execute_query_silent() {
    local query="$1"
    local cmd=$(build_obclient_base)
    
    # 使用 -N 不显示表头，-s 静默模式，-e 执行SQL
    echo "$query" | $cmd -N -s -e "SET sql_mode='';$query" 2>/dev/null || return 1
}


# 执行 SQL 查询并显示结果（带格式）
execute_query() {
    local query="$1"
    local cmd=$(build_obclient_base)
    
    # -e 执行SQL
    $cmd -e "SET sql_mode='';$query" 2>/dev/null || return 1
}


# 检查是否安装了 obclient
check_obclient() {
    if ! command -v obclient &> /dev/null; then
        echo -e "${RED}错误: 未找到 obclient 客户端, 请先安装 OceanBase 客户端工具${NC}"
        echo ""
        echo "提示: 如果已安装但不在 PATH 中，可以："
        echo "  1. 将 obclient 所在目录添加到 PATH"
        echo "  2. 或创建符号链接: ln -s /path/to/obclient /usr/local/bin/obclient"
        exit 1
    fi
}


# 测试数据库连接
test_connection() {
    local cmd=$(build_obclient_base)
    
    if ! echo "SELECT 1;" | $cmd -N -s 2>/dev/null > /dev/null; then
        echo -e "${RED}错误: 无法连接数据库${NC}"
        echo "请检查数据库连接配置:"
        echo "  主机: ${DB_HOST}"
        echo "  端口: ${DB_PORT}"
        echo "  数据库: ${DB_NAME}"
        echo "  用户: $(build_user_string)"
        echo ""
        echo "环境变量配置:"
        echo "  ODC_DATABASE_HOST"
        echo "  ODC_DATABASE_PORT"
        echo "  ODC_DATABASE_NAME"
        echo "  ODC_DATABASE_USERNAME"
        echo "  ODC_DATABASE_PASSWORD"
        return 1
    fi
    return 0
}


# 打印标题
print_header() {
    echo -e "${CYAN}========================================${NC}"
    echo -e "${CYAN}$1${NC}"
    echo -e "${CYAN}========================================${NC}"
}


# 打印分隔线
print_separator() {
    echo -e "${BLUE}----------------------------------------${NC}"
}


# 从 schedule_schedule.id 获取对应的 schedule_task.id
get_schedule_task_id() {
    local schedule_id=$1
    
    local query="
        SELECT latest_schedule_task_id
        FROM schedule_latest_task_mapping
        WHERE schedule_id = $schedule_id
        LIMIT 1;
    "
    
    local task_id=$(execute_query_silent "$query")
    
    if [ -z "$task_id" ]; then
        echo ""
        return 1
    else
        echo "$task_id"
        return 0
    fi
}


# 查询逻辑库变更任务列表（基于 schedule_schedule）
list_tasks() {
    print_header "逻辑库变更任务列表（基于 schedule_schedule）"
    
    local query="
        SELECT 
            ss.id AS '计划ID',
            ss.job_type AS '任务类型',
            ss.database_name AS '数据库名称',
            ss.status AS '状态',
            ss.create_time AS '创建时间',
            ss.update_time AS '更新时间',
            COALESCE(CAST(st.id AS CHAR), 'N/A') AS '最新任务ID',
            COALESCE(st.status, 'N/A') AS '任务状态'
        FROM schedule_schedule ss
        LEFT JOIN schedule_latest_task_mapping sltm ON ss.id = sltm.schedule_id
        LEFT JOIN schedule_task st ON sltm.latest_schedule_task_id = st.id
        WHERE ss.job_type = 'LOGICAL_DATABASE_CHANGE'
        ORDER BY ss.create_time DESC
        LIMIT 20;
    "
    
    execute_query "$query" || {
        test_connection
        exit 1
    }
}


# 查询任务详情（基于 schedule_schedule.id）
task_detail() {
    local schedule_id=$1
    
    if [ -z "$schedule_id" ]; then
        echo -e "${RED}错误: 请提供计划ID (schedule_schedule.id)${NC}"
        echo "用法: $0 detail <schedule_id>"
        exit 1
    fi
    
    # 获取对应的 schedule_task.id
    local task_id=$(get_schedule_task_id $schedule_id)
    
    if [ -z "$task_id" ]; then
        echo -e "${YELLOW}警告: 未找到该计划对应的执行任务${NC}"
        echo -e "${YELLOW}可能该计划还未执行过，或任务已被清理${NC}"
        print_separator
        
        # 只显示计划基本信息
        local schedule_query="
            SELECT 
                id AS '计划ID',
                job_type AS '任务类型',
                database_name AS '数据库名称',
                status AS '状态',
                description AS '描述',
                create_time AS '创建时间',
                update_time AS '更新时间'
            FROM schedule_schedule
            WHERE id = $schedule_id;
        "
        
        echo -e "${YELLOW}计划基本信息:${NC}"
        if ! execute_query "$schedule_query"; then
            echo -e "${RED}错误: 查询计划信息失败或计划不存在${NC}"
            exit 1
        fi
        return
    fi
    
    print_header "任务详情 (计划ID: $schedule_id, 任务ID: $task_id)"
    
    # 查询计划基本信息
    local schedule_query="
        SELECT 
            id AS '计划ID',
            job_type AS '任务类型',
            database_name AS '数据库名称',
            status AS '状态',
            description AS '描述',
            create_time AS '创建时间',
            update_time AS '更新时间'
        FROM schedule_schedule
        WHERE id = $schedule_id;
    "
    
    echo -e "${YELLOW}计划基本信息:${NC}"
    if ! execute_query "$schedule_query"; then
        echo -e "${RED}错误: 查询计划信息失败${NC}"
        exit 1
    fi
    
    print_separator
    
    # 查询任务基本信息
    local task_query="
        SELECT 
            id AS '任务ID',
            job_name AS '任务名称',
            status AS '状态',
            fire_time AS '执行时间',
            progress_percentage AS '进度(%)',
            create_time AS '创建时间',
            update_time AS '更新时间'
        FROM schedule_task
        WHERE id = $task_id;
    "
    
    echo -e "${YELLOW}任务基本信息:${NC}"
    execute_query "$task_query"
    
    print_separator
    
    # 查询执行单元统计信息（详细版本）
    local stats_query="
        SELECT 
            e.physical_database_id AS '物理库ID',
            COALESCE(d.name, 'N/A') AS '物理库名称',
            COUNT(*) AS 'SQL总数',
            SUM(CASE WHEN e.status = 'SUCCESS' THEN 1 ELSE 0 END) AS '成功',
            SUM(CASE WHEN e.status = 'FAILED' THEN 1 ELSE 0 END) AS '失败',
            SUM(CASE WHEN e.status = 'SKIPPED' THEN 1 ELSE 0 END) AS '已跳过',
            SUM(CASE WHEN e.status = 'RUNNING' THEN 1 ELSE 0 END) AS '运行中',
            SUM(CASE WHEN e.status = 'PENDING' THEN 1 ELSE 0 END) AS '等待中',
            SUM(CASE WHEN e.status IN ('SKIPPING', 'TERMINATING') THEN 1 ELSE 0 END) AS '处理中',
            SUM(CASE WHEN e.status IN ('TERMINATE_FAILED', 'TERMINATED') THEN 1 ELSE 0 END) AS '已终止',
            SUM(CASE WHEN e.status IN ('SUCCESS', 'SKIPPED') THEN 1 ELSE 0 END) AS '已完成数',
            ROUND(SUM(CASE WHEN e.status IN ('SUCCESS', 'SKIPPED') THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS '完成率(%)',
            MIN(e.execution_order) AS '起始顺序',
            MAX(e.execution_order) AS '结束顺序',
            MIN(e.create_time) AS '最早开始时间',
            MAX(e.update_time) AS '最后更新时间',
            CASE 
                WHEN SUM(CASE WHEN e.status NOT IN ('SUCCESS', 'SKIPPED', 'FAILED', 'TERMINATED', 'TERMINATE_FAILED') THEN 1 ELSE 0 END) > 0 
                THEN '执行中'
                WHEN SUM(CASE WHEN e.status = 'FAILED' THEN 1 ELSE 0 END) > 0 
                THEN '有失败'
                WHEN SUM(CASE WHEN e.status IN ('TERMINATE_FAILED', 'TERMINATED') THEN 1 ELSE 0 END) > 0 
                THEN '已终止'
                WHEN SUM(CASE WHEN e.status IN ('SUCCESS', 'SKIPPED') THEN 1 ELSE 0 END) = COUNT(*)
                THEN '已完成'
                ELSE '未知'
            END AS '整体状态'
        FROM logicaldatabase_database_change_execution_unit e
        LEFT JOIN connect_database d ON e.physical_database_id = d.id
        WHERE e.schedule_task_id = $task_id
        GROUP BY e.physical_database_id, d.name
        ORDER BY e.physical_database_id;
    "
    
    echo -e "${YELLOW}执行单元统计（详细）:${NC}"
    execute_query "$stats_query"
    
    print_separator
    
    # 状态分布详情
    echo -e "${YELLOW}状态分布详情:${NC}"
    local status_dist_query="
        SELECT 
            e.physical_database_id AS '物理库ID',
            COALESCE(d.name, 'N/A') AS '物理库名称',
            e.status AS '状态',
            COUNT(*) AS '数量',
            ROUND(COUNT(*) * 100.0 / (
                SELECT COUNT(*) 
                FROM logicaldatabase_database_change_execution_unit 
                WHERE schedule_task_id = $task_id 
                  AND physical_database_id = e.physical_database_id
            ), 2) AS '占比(%)'
        FROM logicaldatabase_database_change_execution_unit e
        LEFT JOIN connect_database d ON e.physical_database_id = d.id
        WHERE e.schedule_task_id = $task_id
        GROUP BY e.physical_database_id, d.name, e.status
        ORDER BY e.physical_database_id, e.status;
    "
    
    execute_query "$status_dist_query"
    
    print_separator
}


# 查询物理库的执行详情（基于 schedule_schedule.id）
physical_database_detail() {
    local schedule_id=$1
    local physical_db_id=$2
    
    if [ -z "$schedule_id" ] || [ -z "$physical_db_id" ]; then
        echo -e "${RED}错误: 请提供计划ID和物理库ID${NC}"
        echo "用法: $0 physical <schedule_id> <physical_database_id>"
        exit 1
    fi
    
    # 获取对应的 schedule_task.id
    local task_id=$(get_schedule_task_id $schedule_id)
    
    if [ -z "$task_id" ]; then
        echo -e "${RED}错误: 未找到该计划对应的执行任务${NC}"
        exit 1
    fi
    
    print_header "物理库执行详情 (计划ID: $schedule_id, 任务ID: $task_id, 物理库ID: $physical_db_id)"
    
    # 查询物理库名称
    local db_query="
        SELECT name FROM connect_database WHERE id = $physical_db_id;
    "
    local db_name=$(execute_query_silent "$db_query")
    
    if [ -n "$db_name" ]; then
        echo -e "${GREEN}物理库: ${db_name} (ID: $physical_db_id)${NC}"
    else
        echo -e "${YELLOW}物理库ID: $physical_db_id (未找到名称)${NC}"
    fi
    print_separator
    
    # 查询执行单元详情
    local detail_query="
        SELECT 
            execution_order AS '执行顺序',
            status AS '状态',
            sql_content AS 'SQL内容',
            create_time AS '创建时间',
            update_time AS '更新时间'
        FROM logicaldatabase_database_change_execution_unit
        WHERE schedule_task_id = $task_id
          AND physical_database_id = $physical_db_id
        ORDER BY execution_order;
    "
    
    echo -e "${YELLOW}执行单元列表:${NC}"
    execute_query "$detail_query"
    
    print_separator
    
    # 查询详细的SQL内容
    echo -e "${YELLOW}SQL内容详情:${NC}"
    local sql_query="
        SELECT 
            execution_order,
            status,
            sql_content
        FROM logicaldatabase_database_change_execution_unit
        WHERE schedule_task_id = $task_id
          AND physical_database_id = $physical_db_id
        ORDER BY execution_order;
    "
    
    execute_query "$sql_query" | while IFS=$'\t' read -r order status sql; do
        if [ "$status" = "SUCCESS" ]; then
            status_color="${GREEN}"
        elif [ "$status" = "FAILED" ]; then
            status_color="${RED}"
        elif [ "$status" = "RUNNING" ] || [ "$status" = "PENDING" ]; then
            status_color="${YELLOW}"
        else
            status_color="${NC}"
        fi
        echo -e "${BLUE}[顺序 $order] ${status_color}${status}${NC}"
        echo "$sql"
        echo ""
    done
}


# 查询所有物理库的执行情况（基于 schedule_schedule.id）
all_physical_databases() {
    local schedule_id=$1
    
    if [ -z "$schedule_id" ]; then
        echo -e "${RED}错误: 请提供计划ID${NC}"
        echo "用法: $0 all <schedule_id>"
        exit 1
    fi
    
    # 获取对应的 schedule_task.id
    local task_id=$(get_schedule_task_id $schedule_id)
    
    if [ -z "$task_id" ]; then
        echo -e "${RED}错误: 未找到该计划对应的执行任务${NC}"
        exit 1
    fi
    
    print_header "所有物理库执行情况 (计划ID: $schedule_id, 任务ID: $task_id)"
    
    local query="
        SELECT 
            e.physical_database_id AS '物理库ID',
            d.name AS '物理库名称',
            COUNT(*) AS 'SQL总数',
            SUM(CASE WHEN e.status = 'SUCCESS' THEN 1 ELSE 0 END) AS '成功',
            SUM(CASE WHEN e.status = 'FAILED' THEN 1 ELSE 0 END) AS '失败',
            SUM(CASE WHEN e.status IN ('RUNNING', 'PENDING') THEN 1 ELSE 0 END) AS '进行中',
            MAX(e.update_time) AS '最后更新时间'
        FROM logicaldatabase_database_change_execution_unit e
        LEFT JOIN connect_database d ON e.physical_database_id = d.id
        WHERE e.schedule_task_id = $task_id
        GROUP BY e.physical_database_id, d.name
        ORDER BY e.physical_database_id;
    "
    
    execute_query "$query"
}


# 显示帮助信息
show_help() {
    echo "逻辑库变更任务信息查看工具"
    echo ""
    echo "用法: $0 <命令> [参数...]"
    echo ""
    echo "注意: 所有命令使用的ID都是 schedule_schedule 表的 id"
    echo ""
    echo "命令:"
    echo "  list                          列出所有逻辑库变更任务（最近20条）"
    echo "  detail <schedule_id>           查看任务详情（使用 schedule_schedule.id）"
    echo "  all <schedule_id>              查看任务所有物理库的执行情况"
    echo "  physical <schedule_id> <db_id> 查看指定物理库的执行详情"
    echo "  help                          显示此帮助信息"
    echo ""
    echo "环境变量:"
    echo "  ODC_DATABASE_HOST             数据库主机 (默认: localhost)"
    echo "  ODC_DATABASE_PORT             数据库端口 (默认: 2883)"
    echo "  ODC_DATABASE_NAME             数据库名称 (默认: odc)"
    echo "  ODC_DATABASE_USERNAME         数据库用户名 (默认: root)"
    echo "  ODC_DATABASE_PASSWORD         数据库密码"
    echo ""
    echo "示例:"
    echo "  $0 list"
    echo "  $0 detail 123                  # 使用 schedule_schedule.id = 123"
    echo "  $0 all 123"
    echo "  $0 physical 123 456"
    echo ""
    echo "注意: 本脚本使用 obclient 客户端工具连接 OceanBase 数据库"
}


# 主函数
main() {
    check_obclient
    
    case "${1:-help}" in
        list)
            list_tasks
            ;;
        detail)
            task_detail "$2"
            ;;
        all)
            all_physical_databases "$2"
            ;;
        physical)
            physical_database_detail "$2" "$3"
            ;;
        help|--help|-h)
            show_help
            ;;
        *)
            echo -e "${RED}错误: 未知命令 '$1'${NC}"
            echo ""
            show_help
            exit 1
            ;;
    esac
}


# 执行主函数
main "$@"