#!/bin/bash

# 使用现有PostgreSQL配置数据库
set -e

echo "🚀 配置现有 PostgreSQL 数据库..."
echo ""

SERVER="root@45.249.208.118"
SERVER_PASSWORD="lxvhNYFZ4160"
DB_NAME="flnxi_db"
DB_USER="flnxi_user"
DB_PASSWORD="Flnxi@2025!SecurePassword"

GREEN='\033[0;32m'
BLUE='\033[0;34m'
YELLOW='\033[1;33m'
NC='\033[0m'

ssh_exec() {
    sshpass -p "$SERVER_PASSWORD" ssh -o StrictHostKeyChecking=no $SERVER "$1"
}

echo -e "${BLUE}步骤 1/6: 创建数据库用户...${NC}"
ssh_exec "sudo -u postgres psql -c \"CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';\" 2>/dev/null || echo '用户可能已存在'"
echo -e "${GREEN}✅ 用户已创建/存在${NC}"
echo ""

echo -e "${BLUE}步骤 2/6: 创建数据库...${NC}"
ssh_exec "sudo -u postgres psql -c \"CREATE DATABASE $DB_NAME OWNER $DB_USER;\" 2>/dev/null || echo '数据库可能已存在'"
ssh_exec "sudo -u postgres psql -c \"GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;\""
echo -e "${GREEN}✅ 数据库已创建${NC}"
echo ""

echo -e "${BLUE}步骤 3/6: 创建用户表...${NC}"
ssh_exec "sudo -u postgres psql -d $DB_NAME << 'EOFSQL'
-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);

-- 授权
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO $DB_USER;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO $DB_USER;

-- 查看表结构
\d users
EOFSQL
"
echo -e "${GREEN}✅ 用户表已创建${NC}"
echo ""

echo -e "${BLUE}步骤 4/6: 配置环境变量...${NC}"
DATABASE_URL="postgresql://${DB_USER}:${DB_PASSWORD}@localhost:5432/${DB_NAME}?schema=public"
ssh_exec "cd /opt/flnxi-website && cat >> .env.local << EOFENV

# PostgreSQL 数据库配置
DATABASE_URL=\"${DATABASE_URL}\"
EOFENV
"
echo -e "${GREEN}✅ 环境变量已配置${NC}"
echo ""

echo -e "${BLUE}步骤 5/6: 测试数据库连接...${NC}"
ssh_exec "sudo -u postgres psql -d $DB_NAME -c 'SELECT current_database(), current_user;'"
echo -e "${GREEN}✅ 数据库连接正常${NC}"
echo ""

echo -e "${BLUE}步骤 6/6: 清理Docker容器...${NC}"
ssh_exec "cd /opt/flnxi-website && docker-compose -f docker-compose-db.yml down 2>/dev/null || true"
ssh_exec "docker rm -f flnxi-postgres 2>/dev/null || true"
echo -e "${GREEN}✅ 已清理${NC}"
echo ""

# 保存配置信息
cat > DATABASE_CONFIG.md << EOFCONFIG
# PostgreSQL 数据库配置信息

## 连接信息

- **数据库名**: \`$DB_NAME\`
- **用户名**: \`$DB_USER\`
- **密码**: \`$DB_PASSWORD\`
- **端口**: \`5432\` (本地)
- **连接字符串**: 
  \`\`\`
  $DATABASE_URL
  \`\`\`

## 管理命令

### 连接数据库
\`\`\`bash
ssh root@45.249.208.118
sudo -u postgres psql -d $DB_NAME
\`\`\`

### 查看表
\`\`\`sql
\dt
\`\`\`

### 查看用户数据
\`\`\`sql
SELECT id, name, email, email_verified, created_at FROM users;
\`\`\`

### 备份数据库
\`\`\`bash
sudo -u postgres pg_dump $DB_NAME > backup_\$(date +%Y%m%d).sql
\`\`\`

## 表结构

### users 表
| 字段 | 类型 | 说明 |
|------|------|------|
| id | SERIAL | 主键 |
| name | VARCHAR(255) | 用户名 |
| email | VARCHAR(255) | 邮箱（唯一） |
| password | VARCHAR(255) | 密码（bcrypt加密） |
| email_verified | BOOLEAN | 邮箱是否验证 |
| created_at | TIMESTAMP | 创建时间 |
| updated_at | TIMESTAMP | 更新时间 |

---
生成时间: $(date)
EOFCONFIG

echo -e "${GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${GREEN}🎉 PostgreSQL 配置完成！${NC}"
echo -e "${GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo ""
echo -e "${YELLOW}📊 数据库信息：${NC}"
echo "数据库名: $DB_NAME"
echo "用户名: $DB_USER"
echo "密码: $DB_PASSWORD"
echo "连接: localhost:5432"
echo ""
echo -e "${YELLOW}📝 配置已保存到: DATABASE_CONFIG.md${NC}"
echo ""
echo -e "${YELLOW}🔍 测试连接：${NC}"
echo "sshpass -p '$SERVER_PASSWORD' ssh $SERVER 'sudo -u postgres psql -d $DB_NAME -c \"SELECT * FROM users;\"'"
echo ""