import { NextRequest, NextResponse } from 'next/server';
import { db } from '@/lib/db';
import { students } from '@/lib/db/schema';
import { eq, ilike, and, sql } from 'drizzle-orm';
import { StudentSearchFilters, PaginatedResponse, Student } from '@/lib/types';

export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);
    
    // 分页参数
    const page = parseInt(searchParams.get('page') || '1');
    const pageSize = parseInt(searchParams.get('pageSize') || '10');
    const offset = (page - 1) * pageSize;

    // 搜索参数
    const filters: StudentSearchFilters = {
      name: searchParams.get('name') || undefined,
      stuNo: searchParams.get('stuNo') || undefined,
      department: searchParams.get('department') || undefined,
      major: searchParams.get('major') || undefined,
      accountStatus: (searchParams.get('accountStatus') as 'active' | 'frozen' | 'reported') || undefined,
    };

    // 构建查询条件
    const conditions = [];
    
    if (filters.name) {
      conditions.push(ilike(students.name, `%${filters.name}%`));
    }
    
    if (filters.stuNo) {
      conditions.push(eq(students.stuNo, filters.stuNo));
    }
    
    if (filters.department) {
      conditions.push(ilike(students.department, `%${filters.department}%`));
    }
    
    if (filters.major) {
      conditions.push(ilike(students.major, `%${filters.major}%`));
    }
    
    if (filters.accountStatus) {
      conditions.push(eq(students.accountStatus, filters.accountStatus as 'active' | 'frozen' | 'reported'));
    }

    const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

    // 获取总数
    const totalResult = await db
      .select({ count: sql<number>`count(*)` })
      .from(students)
      .where(whereClause);
    
    const total = totalResult[0].count;

    // 获取分页数据（排除密码字段）
    const studentsData = await db
      .select({
        studentId: students.studentId,
        stuNo: students.stuNo,
        name: students.name,
        gender: students.gender,
        department: students.department,
        major: students.major,
        grade: students.grade,
        class: students.class,
        phone: students.phone,
        email: students.email,
        maxBorrow: students.maxBorrow,
        currentBorrow: students.currentBorrow,
        accountStatus: students.accountStatus,
        createdAt: students.createdAt,
      })
      .from(students)
      .where(whereClause)
      .limit(pageSize)
      .offset(offset)
      .orderBy(students.createdAt);

    const response: PaginatedResponse<Omit<Student, 'passwordHash'>> = {
      data: studentsData,
      total,
      page,
      pageSize,
      totalPages: Math.ceil(total / pageSize),
    };

    return NextResponse.json({ success: true, data: response });
  } catch (error) {
    console.error('Error fetching students:', error);
    return NextResponse.json(
      { success: false, error: 'Failed to fetch students' },
      { status: 500 }
    );
  }
}

export async function POST(request: NextRequest) {
  try {
    const body = await request.json();

    const newStudent = await db
      .insert(students)
      .values({
        stuNo: body.stuNo,
        name: body.name,
        gender: body.gender || null,
        department: body.department || null,
        major: body.major || null,
        grade: body.grade || null,
        class: body.class || null,
        phone: body.phone || null,
        email: body.email || null,
        maxBorrow: body.maxBorrow || 10,
        currentBorrow: 0,
        accountStatus: body.accountStatus || 'active',
      })
      .returning({
        studentId: students.studentId,
        stuNo: students.stuNo,
        name: students.name,
        gender: students.gender,
        department: students.department,
        major: students.major,
        grade: students.grade,
        class: students.class,
        phone: students.phone,
        email: students.email,
        maxBorrow: students.maxBorrow,
        currentBorrow: students.currentBorrow,
        accountStatus: students.accountStatus,
        createdAt: students.createdAt,
      });

    return NextResponse.json({ 
      success: true, 
      data: newStudent[0],
      message: 'Student created successfully'
    });
  } catch (error) {
    console.error('Error creating student:', error);
    return NextResponse.json(
      { success: false, error: 'Failed to create student' },
      { status: 500 }
    );
  }
}
