deckhearth/pages/api/admin/cards.js

import { sql } from '../../../lib/sql.js';
import { withAdmin } from '../../../lib/permission-middleware';
import { checkImportRateLimit } from '../../../lib/rate-limit.js';

const ALLOWED_SORT_COLUMNS = new Set([
  'name', 'set_name', 'set_code', 'card_number', 'rarity', 'game',
  'mana_cost', 'cmc', 'card_type', 'current_price', 'market_price',
  'prices_updated_at', 'verified', 'created_at', 'updated_at',
  'oracle_id', 'illustration_id', 'edhrec_rank', 'released_at'
]);

const ALLOWED_SORT_DIRS = new Set(['asc', 'desc']);

export default withAdmin(async function handler(req, res, user) {
  if (req.method !== 'GET') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  const { allowed, reset } = await checkImportRateLimit(req, user.userId);
  if (!allowed) {
    res.setHeader('Retry-After', Math.ceil((reset - Date.now()) / 1000));
    return res.status(429).json({ error: 'Too many attempts. Try again later.' });
  }

  try {
    const {
      query = '',
      game = 'all',
      rarity = 'all',
      set = 'all',
      minPrice = '',
      maxPrice = '',
      page = '1',
      limit = '50',
      sort = 'name',
      dir = 'asc',
      verifiedOnly = 'false'
    } = req.query;

    const pageNum = Math.max(1, parseInt(page) || 1);
    const limitNum = Math.min(200, Math.max(1, parseInt(limit) || 50));
    const offset = (pageNum - 1) * limitNum;
    const sortCol = ALLOWED_SORT_COLUMNS.has(sort) ? sort : 'name';
    const sortDir = ALLOWED_SORT_DIRS.has(dir.toLowerCase()) ? dir.toLowerCase() : 'asc';

    const filters = {
      hasQuery: query.trim() !== '',
      hasGame: game !== 'all',
      hasRarity: rarity !== 'all',
      hasSet: set !== 'all',
      hasMinPrice: minPrice && !isNaN(parseFloat(minPrice)),
      hasMaxPrice: maxPrice && !isNaN(parseFloat(maxPrice)),
      verifiedOnly: verifiedOnly === 'true'
    };

    const whereConditions = [];
    const params = [];

    if (filters.hasQuery) {
      whereConditions.push(`name ILIKE '%' || $${params.length + 1} || '%'`);
      params.push(query.trim());
    }
    if (filters.hasGame) {
      whereConditions.push(`game = $${params.length + 1}`);
      params.push(game);
    }
    if (filters.hasRarity) {
      whereConditions.push(`rarity = $${params.length + 1}`);
      params.push(rarity);
    }
    if (filters.hasSet) {
      whereConditions.push(`set_name = $${params.length + 1}`);
      params.push(set);
    }
    if (filters.hasMinPrice) {
      whereConditions.push(`market_price >= $${params.length + 1}`);
      params.push(parseFloat(minPrice));
    }
    if (filters.hasMaxPrice) {
      whereConditions.push(`market_price <= $${params.length + 1}`);
      params.push(parseFloat(maxPrice));
    }
    if (filters.verifiedOnly) {
      whereConditions.push(`verified = true`);
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

    const selectColumns = `
      id, name, set_name, set_code, card_number, rarity, game,
      mana_cost, cmc, card_type, colors, oracle_text,
      power, toughness, image_url, stock_image_url,
      current_price, market_price, prices_updated_at,
      scryfall_id, verified, oracle_id, illustration_id,
      color_identity, keywords, legalities, layout,
      edhrec_rank, reserved, reprint, finishes,
      released_at, created_at, updated_at
    `;

    const querySql = `
      SELECT ${selectColumns}
      FROM cards
      ${whereClause}
      ORDER BY ${sortCol} ${sortDir.toUpperCase()}
      LIMIT $${params.length + 1} OFFSET $${params.length + 2}
    `;
    params.push(limitNum, offset);

    const countSql = `
      SELECT COUNT(*) as total
      FROM cards
      ${whereClause}
    `;

    const [result, countResult] = await Promise.all([
      sql.query(querySql, params),
      sql.query(countSql, params.slice(0, -2))
    ]);

    const total = parseInt(countResult.rows[0].total);
    const totalPages = Math.ceil(total / limitNum);

    // Get filter options for dropdowns
    const filtersResult = await sql`
      SELECT 
        ARRAY_AGG(DISTINCT game) FILTER (WHERE game IS NOT NULL) as games,
        ARRAY_AGG(DISTINCT rarity) FILTER (WHERE rarity IS NOT NULL) as rarities,
        ARRAY_AGG(DISTINCT set_name) FILTER (WHERE set_name IS NOT NULL) as sets
      FROM cards
    `;

    const filterData = filtersResult.rows[0];

    const cards = result.rows.map(card => {
      if (card.colors && typeof card.colors === 'string') {
        try {
          card.colors = JSON.parse(card.colors);
        } catch {
          card.colors = [];
        }
      }
      if (card.color_identity && typeof card.color_identity === 'string') {
        try {
          card.color_identity = JSON.parse(card.color_identity);
        } catch {
          card.color_identity = [];
        }
      }
      if (card.keywords && typeof card.keywords === 'string') {
        try {
          card.keywords = JSON.parse(card.keywords);
        } catch {
          card.keywords = [];
        }
      }
      if (card.legalities && typeof card.legalities === 'string') {
        try {
          card.legalities = JSON.parse(card.legalities);
        } catch {
          card.legalities = {};
        }
      }
      if (card.finishes && typeof card.finishes === 'string') {
        try {
          card.finishes = JSON.parse(card.finishes);
        } catch {
          card.finishes = [];
        }
      }
      return card;
    });

    res.status(200).json({
      success: true,
      cards,
      pagination: {
        page: pageNum,
        limit: limitNum,
        total,
        pages: totalPages,
        hasMore: pageNum < totalPages
      },
      filters: {
        games: filterData.games || [],
        rarities: filterData.rarities || [],
        sets: filterData.sets || []
      },
      sort: { column: sortCol, direction: sortDir }
    });

  } catch (error) {
    console.error('Error searching admin cards:', error);
    res.status(500).json({
      success: false,
      error: 'Internal server error',
      message: error.message
    });
  }
});