import { NextRequest, NextResponse } from 'next/server';
import { cookies } from 'next/headers';
import { getSupabaseClient } from '@/storage/database/supabase-client';

// 验证管理员身份
async function verifyAdmin() {
  const cookieStore = await cookies();
  const adminId = cookieStore.get('admin_id')?.value;
  return adminId;
}

// POST /api/init-db - 初始化数据库表
export async function POST(request: NextRequest) {
  try {
    const adminId = await verifyAdmin();
    if (!adminId) {
      return NextResponse.json(
        { error: '请先登录' },
        { status: 401 }
      );
    }

    const client = getSupabaseClient();

    // 创建案例表
    const createCasesTable = `
      CREATE TABLE IF NOT EXISTS cases (
        id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid(),
        title VARCHAR(200) NOT NULL,
        description TEXT,
        images TEXT NOT NULL,
        category VARCHAR(50),
        sort_order INTEGER DEFAULT 0,
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
      );
    `;

    // 创建常见问题表
    const createFaqsTable = `
      CREATE TABLE IF NOT EXISTS faqs (
        id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid(),
        question TEXT NOT NULL,
        answer TEXT NOT NULL,
        category VARCHAR(50),
        sort_order INTEGER DEFAULT 0,
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
      );
    `;

    // 创建索引
    const createIndexes = `
      CREATE INDEX IF NOT EXISTS cases_category_idx ON cases(category);
      CREATE INDEX IF NOT EXISTS cases_sort_idx ON cases(sort_order);
      CREATE INDEX IF NOT EXISTS cases_is_active_idx ON cases(is_active);
      CREATE INDEX IF NOT EXISTS faqs_category_idx ON faqs(category);
      CREATE INDEX IF NOT EXISTS faqs_sort_idx ON faqs(sort_order);
      CREATE INDEX IF NOT EXISTS faqs_is_active_idx ON faqs(is_active);
    `;

    // 执行SQL
    const { error: casesError } = await client.rpc('exec_sql', { sql: createCasesTable });
    if (casesError && !casesError.message.includes('already exists')) {
      // 如果rpc不存在，尝试直接使用from检查表是否存在
      const { error: checkError } = await client.from('cases').select('id').limit(1);
      if (checkError && checkError.code === '42P01') {
        // 表不存在，需要手动创建
        console.log('Cases table needs to be created');
      }
    }

    const { error: faqsError } = await client.rpc('exec_sql', { sql: createFaqsTable });
    if (faqsError && !faqsError.message.includes('already exists')) {
      console.log('Faqs table needs to be created');
    }

    return NextResponse.json({ 
      success: true, 
      message: '数据库表创建成功（如果表已存在则跳过）' 
    });
  } catch (error) {
    console.error('Error initializing database:', error);
    return NextResponse.json(
      { error: '数据库初始化失败', details: String(error) },
      { status: 500 }
    );
  }
}
