/**
 * 分类管理API路由
 * Handle category CRUD operations
 */
import { Router, type Request, type Response } from 'express';
import { query, queryOne, execute } from '../config/database.js';
import { authenticateToken, optionalAuth } from '../middleware/auth.js';

const router = Router();

/**
 * 获取所有分类列表
 * GET /api/categories
 */
router.get('/', optionalAuth, async (req: Request, res: Response): Promise<void> => {
  try {
    const categories = await query(`
      SELECT 
        c.id,
        c.name,
        c.description,
        c.created_at as createdAt,
        c.sort_order,
        COUNT(v.id) as videoCount
      FROM categories c
      LEFT JOIN videos v ON c.id = v.category
      GROUP BY c.id, c.name, c.description, c.created_at, c.sort_order
      ORDER BY c.sort_order ASC, c.created_at DESC
    `);

    res.json({
      code: 200,
      message: '获取分类列表成功',
      data: categories
    });
  } catch (error) {
    // 获取分类列表失败
    res.status(500).json({
      code: 500,
      message: '获取分类列表失败',
      data: null
    });
  }
});

/**
 * 获取分类详情
 * GET /api/categories/:id
 */
router.get('/:id', optionalAuth, async (req: Request, res: Response): Promise<void> => {
  try {
    const { id } = req.params;
    
    const category = await queryOne(`
      SELECT 
        c.id,
        c.name,
        c.description,
        c.created_at as createdAt,
        COUNT(v.id) as videoCount
      FROM categories c
      LEFT JOIN videos v ON c.id = v.category
      WHERE c.id = ?
      GROUP BY c.id, c.name, c.description, c.created_at
    `, [id]);

    if (!category) {
      res.status(404).json({
        code: 404,
        message: '分类不存在',
        data: null
      });
      return;
    }

    res.json({
      code: 200,
      message: '获取分类详情成功',
      data: category
    });
  } catch (error) {
    // 获取分类详情失败
    res.status(500).json({
      code: 500,
      message: '获取分类详情失败',
      data: null
    });
  }
});

/**
 * 创建分类
 * POST /api/categories
 */
router.post('/', authenticateToken, async (req: Request, res: Response): Promise<void> => {
  try {
    const { name, description = '' } = req.body;

    if (!name || name.trim() === '') {
      res.status(400).json({
        code: 400,
        message: '分类名称不能为空',
        data: null
      });
      return;
    }

    // 检查分类名称是否已存在
    const existingCategory = await queryOne(
      'SELECT id FROM categories WHERE name = ?',
      [name.trim()]
    );

    if (existingCategory) {
      res.status(400).json({
        code: 400,
        message: '分类名称已存在',
        data: null
      });
      return;
    }

    // 创建分类
    const result = await execute(
      'INSERT INTO categories (name, description, created_at) VALUES (?, ?, datetime("now"))',
      [name.trim(), description.trim()]
    );

    // 获取创建的分类信息
    const newCategory = await queryOne(
      'SELECT id, name, description, created_at as createdAt FROM categories WHERE id = ?',
      [result.lastID]
    );

    res.status(201).json({
      code: 201,
      message: '创建分类成功',
      data: newCategory
    });
  } catch (error) {
    // 创建分类失败
    res.status(500).json({
      code: 500,
      message: '创建分类失败',
      data: null
    });
  }
});

/**
 * 更新分类
 * PUT /api/categories/:id
 */
router.put('/:id', authenticateToken, async (req: Request, res: Response): Promise<void> => {
  try {
    const { id } = req.params;
    const { name, description = '' } = req.body;

    if (!name || name.trim() === '') {
      res.status(400).json({
        code: 400,
        message: '分类名称不能为空',
        data: null
      });
      return;
    }

    // 检查分类是否存在
    const existingCategory = await queryOne(
      'SELECT id FROM categories WHERE id = ?',
      [id]
    );

    if (!existingCategory) {
      res.status(404).json({
        code: 404,
        message: '分类不存在',
        data: null
      });
      return;
    }

    // 检查分类名称是否已被其他分类使用
    const duplicateCategory = await queryOne(
      'SELECT id FROM categories WHERE name = ? AND id != ?',
      [name.trim(), id]
    );

    if (duplicateCategory) {
      res.status(400).json({
        code: 400,
        message: '分类名称已存在',
        data: null
      });
      return;
    }

    // 更新分类
    await execute(
      'UPDATE categories SET name = ?, description = ? WHERE id = ?',
      [name.trim(), description.trim(), id]
    );

    // 获取更新后的分类信息
    const updatedCategory = await queryOne(
      'SELECT id, name, description, created_at as createdAt FROM categories WHERE id = ?',
      [id]
    );

    res.json({
      code: 200,
      message: '更新分类成功',
      data: updatedCategory
    });
  } catch (error) {
    // 更新分类失败
    res.status(500).json({
      code: 500,
      message: '更新分类失败',
      data: null
    });
  }
});

/**
 * 删除分类
 * DELETE /api/categories/:id
 */
router.delete('/:id', authenticateToken, async (req: Request, res: Response): Promise<void> => {
  try {
    const { id } = req.params;

    // 检查分类是否存在
    const existingCategory = await queryOne(
      'SELECT id FROM categories WHERE id = ?',
      [id]
    );

    if (!existingCategory) {
      res.status(404).json({
        code: 404,
        message: '分类不存在',
        data: null
      });
      return;
    }

    // 检查是否有视频使用该分类
    const videosUsingCategory = await queryOne(
      'SELECT COUNT(*) as count FROM videos WHERE category = ?',
      [id]
    );

    if (videosUsingCategory.count > 0) {
      res.status(400).json({
        code: 400,
        message: `无法删除分类，还有 ${videosUsingCategory.count} 个视频使用该分类`,
        data: null
      });
      return;
    }

    // 删除分类
    await execute('DELETE FROM categories WHERE id = ?', [id]);

    res.json({
      code: 200,
      message: '删除分类成功',
      data: null
    });
  } catch (error) {
    // 删除分类失败
    res.status(500).json({
      code: 500,
      message: '删除分类失败',
      data: null
    });
  }
});

/**
 * 批量删除分类
 * DELETE /api/categories/batch
 */
router.delete('/batch', authenticateToken, async (req: Request, res: Response): Promise<void> => {
  try {
    const { categoryIds } = req.body;

    if (!Array.isArray(categoryIds) || categoryIds.length === 0) {
      res.status(400).json({
        code: 400,
        message: '请选择要删除的分类',
        data: null
      });
      return;
    }

    // 检查是否有视频使用这些分类
    const videosUsingCategories = await query(
      `SELECT category, COUNT(*) as count FROM videos WHERE category IN (${categoryIds.map(() => '?').join(',')}) GROUP BY category`,
      categoryIds
    );

    if (videosUsingCategories.length > 0) {
      const categoryNames = await query(
        `SELECT id, name FROM categories WHERE id IN (${categoryIds.map(() => '?').join(',')})`,
        categoryIds
      );
      
      const usedCategories = videosUsingCategories.map(vc => {
        const category = categoryNames.find(cn => cn.id === vc.category);
        return `${category?.name || '未知分类'}(${vc.count}个视频)`;
      });

      res.status(400).json({
        code: 400,
        message: `无法删除分类，以下分类还有视频在使用：${usedCategories.join(', ')}`,
        data: null
      });
      return;
    }

    // 批量删除分类
    await execute(
      `DELETE FROM categories WHERE id IN (${categoryIds.map(() => '?').join(',')})`,
      categoryIds
    );

    res.json({
      code: 200,
      message: `成功删除 ${categoryIds.length} 个分类`,
      data: null
    });
  } catch (error) {
    // 批量删除分类失败
    res.status(500).json({
      code: 500,
      message: '批量删除分类失败',
      data: null
    });
  }
});

/**
 * 更新分类顺序
 * PUT /api/categories/:id/order
 */
router.put('/:id/order', authenticateToken, async (req: Request, res: Response): Promise<void> => {
  try {
    const categoryId = parseInt(req.params.id);
    const { direction } = req.body; // 'up' 或 'down'
    
    if (isNaN(categoryId)) {
      res.status(400).json({
        code: 400,
        message: '分类ID必须是有效的数字',
        data: null
      });
      return;
    }

    if (!direction || !['up', 'down'].includes(direction)) {
      res.status(400).json({
        code: 400,
        message: '方向参数必须是 up 或 down',
        data: null
      });
      return;
    }

    // 获取当前分类信息
    const currentCategory = await queryOne('SELECT * FROM categories WHERE id = ?', [categoryId]);
    if (!currentCategory) {
      res.status(404).json({
        code: 404,
        message: '分类不存在',
        data: null
      });
      return;
    }

    // 获取需要交换的分类
    let targetCategory;
    if (direction === 'up') {
      // 获取排序值小于当前分类且最接近的分类
      targetCategory = await queryOne(`
        SELECT * FROM categories 
        WHERE sort_order < ? 
        ORDER BY sort_order DESC 
        LIMIT 1
      `, [currentCategory.sort_order]);
    } else {
      // 获取排序值大于当前分类且最接近的分类
      targetCategory = await queryOne(`
        SELECT * FROM categories 
        WHERE sort_order > ? 
        ORDER BY sort_order ASC 
        LIMIT 1
      `, [currentCategory.sort_order]);
    }

    if (!targetCategory) {
      res.status(400).json({
        code: 400,
        message: direction === 'up' ? '已经是第一个分类' : '已经是最后一个分类',
        data: null
      });
      return;
    }

    // 交换两个分类的sort_order值
    const tempOrder = currentCategory.sort_order;
    await execute('UPDATE categories SET sort_order = ? WHERE id = ?', [targetCategory.sort_order, categoryId]);
    await execute('UPDATE categories SET sort_order = ? WHERE id = ?', [tempOrder, targetCategory.id]);

    res.json({
      code: 200,
      message: '分类顺序更新成功',
      data: null
    });
  } catch (error) {
    // 更新分类顺序失败
    res.status(500).json({
      code: 500,
      message: '更新分类顺序失败',
      data: null
    });
  }
})

export default router;