#!/bin/bash
# AI多语言资讯系统 - 一键部署脚本
# 适用于PostgreSQL/Supabase环境

echo "🚀 开始部署AI多语言资讯系统..."

# 设置变量
DB_HOST=${DB_HOST:-"localhost"}
DB_PORT=${DB_PORT:-"5432"}
DB_NAME=${DB_NAME:-"ai_news"}
DB_USER=${DB_USER:-"postgres"}
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

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

# 打印带颜色的消息
print_message() {
    local color=$1
    local message=$2
    echo -e "${color}${message}${NC}"
}

# 检查PostgreSQL连接
check_connection() {
    print_message $BLUE "检查数据库连接..."
    if command -v psql &> /dev/null; then
        if psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -c '\q' 2>/dev/null; then
            print_message $GREEN "✅ 数据库连接成功"
            return 0
        else
            print_message $RED "❌ 数据库连接失败"
            return 1
        fi
    else
        print_message $YELLOW "⚠️  未找到psql命令，请手动执行SQL文件"
        return 1
    fi
}

# 执行SQL文件
execute_sql() {
    local sql_file=$1
    local description=$2
    
    print_message $BLUE "执行 ${description}..."
    
    if [ ! -f "$sql_file" ]; then
        print_message $RED "❌ 文件不存在: $sql_file"
        return 1
    fi
    
    if psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -f "$sql_file"; then
        print_message $GREEN "✅ ${description} 执行成功"
        return 0
    else
        print_message $RED "❌ ${description} 执行失败"
        return 1
    fi
}

# 验证部署
verify_deployment() {
    print_message $BLUE "验证部署结果..."
    
    # 检查表数量
    local table_count=$(psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE 'ak_%';" 2>/dev/null | tr -d ' ')
    
    if [ "$table_count" -gt 15 ]; then
        print_message $GREEN "✅ 数据库表创建成功 (${table_count}个表)"
    else
        print_message $YELLOW "⚠️  数据库表数量异常 (${table_count}个表)"
    fi
    
    # 检查RLS策略
    local policy_count=$(psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -t -c "SELECT COUNT(*) FROM pg_policies WHERE schemaname = 'public';" 2>/dev/null | tr -d ' ')
    
    if [ "$policy_count" -gt 5 ]; then
        print_message $GREEN "✅ RLS安全策略创建成功 (${policy_count}个策略)"
    else
        print_message $YELLOW "⚠️  RLS策略数量异常 (${policy_count}个策略)"
    fi
    
    # 检查索引
    local index_count=$(psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -t -c "SELECT COUNT(*) FROM pg_indexes WHERE schemaname = 'public' AND indexname LIKE 'idx_%';" 2>/dev/null | tr -d ' ')
    
    if [ "$index_count" -gt 15 ]; then
        print_message $GREEN "✅ 性能索引创建成功 (${index_count}个索引)"
    else
        print_message $YELLOW "⚠️  索引数量异常 (${index_count}个索引)"
    fi
}

# 主部署流程
main() {
    print_message $BLUE "===========================================" 
    print_message $BLUE "  AI多语言资讯系统 - 自动部署工具"
    print_message $BLUE "==========================================="
    
    # 检查SQL文件是否存在
    if [ ! -f "${SCRIPT_DIR}/ai_multilingual_news_database.sql" ]; then
        print_message $RED "❌ 主数据库文件不存在: ai_multilingual_news_database.sql"
        exit 1
    fi
    
    # 部署步骤1: 创建数据库结构
    print_message $YELLOW "📊 步骤1: 创建数据库结构..."
    if check_connection; then
        execute_sql "${SCRIPT_DIR}/ai_multilingual_news_database.sql" "数据库结构创建"
    else
        print_message $YELLOW "请手动执行: ai_multilingual_news_database.sql"
    fi
    
    # 部署步骤2: 插入演示数据（可选）
    if [ -f "${SCRIPT_DIR}/topics_comments_init_data.sql" ]; then
        print_message $YELLOW "📝 步骤2: 插入演示数据..."
        read -p "是否插入演示数据? (y/N): " -n 1 -r
        echo
        if [[ $REPLY =~ ^[Yy]$ || $REPLY =~ ^[是]$ ]]; then
            execute_sql "${SCRIPT_DIR}/topics_comments_init_data.sql" "演示数据插入"
        else
            print_message $BLUE "跳过演示数据插入"
        fi
    fi
    
    # 验证部署
    if check_connection; then
        verify_deployment
    fi
    
    print_message $GREEN "===========================================" 
    print_message $GREEN "🎉 部署完成！"
    print_message $GREEN "==========================================="
    print_message $BLUE "系统功能包括:"
    print_message $BLUE "- 多语言内容管理和AI翻译"
    print_message $BLUE "- 专题、评论、收藏、转发功能"
    print_message $BLUE "- 用户行为分析和推荐系统"
    print_message $BLUE "- 智能聊天和成本控制"
    print_message $BLUE "- 完整的安全访问控制(RLS)"
    print_message $GREEN "==========================================="
}

# 处理命令行参数
while [[ $# -gt 0 ]]; do
    case $1 in
        -h|--host)
            DB_HOST="$2"
            shift 2
            ;;
        -p|--port)
            DB_PORT="$2"
            shift 2
            ;;
        -d|--database)
            DB_NAME="$2"
            shift 2
            ;;
        -U|--username)
            DB_USER="$2"
            shift 2
            ;;
        --help)
            echo "用法: $0 [选项]"
            echo "选项:"
            echo "  -h, --host      数据库主机 (默认: localhost)"
            echo "  -p, --port      数据库端口 (默认: 5432)"
            echo "  -d, --database  数据库名称 (默认: ai_news)"
            echo "  -U, --username  数据库用户 (默认: postgres)"
            echo "  --help          显示此帮助信息"
            exit 0
            ;;
        *)
            print_message $RED "未知参数: $1"
            print_message $BLUE "使用 --help 查看帮助"
            exit 1
            ;;
    esac
done

# 运行主程序
main