import type { APIRoute } from 'astro';
import { query } from '../../../lib/db';

export const GET: APIRoute = async ({ url }) => {
  try {
    const page = parseInt(url.searchParams.get('page') || '1');
    const limit = Math.min(parseInt(url.searchParams.get('limit') || '50'), 100);
    const offset = (page - 1) * limit;
    const search = url.searchParams.get('search') || '';
    const brand = url.searchParams.get('brand') || '';
    const isSneaker = url.searchParams.get('is_sneaker');
    const hasCutout = url.searchParams.get('has_cutout');
    const minPrice = url.searchParams.get('min_price');
    const maxPrice = url.searchParams.get('max_price');
    const sortBy = url.searchParams.get('sort') || 'created_at';
    const sortDir = url.searchParams.get('dir') || 'desc';

    const params: any[] = [];
    const conditions: string[] = [];

    if (search) {
      params.push(`%${search}%`);
      conditions.push(`(p.name ILIKE $${params.length} OR p.brand ILIKE $${params.length})`);
    }
    if (brand) {
      params.push(brand);
      conditions.push(`p.brand = $${params.length}`);
    }
    if (isSneaker === 'true') conditions.push('p.is_sneaker = true');
    if (isSneaker === 'false') conditions.push('p.is_sneaker = false');
    if (hasCutout === 'true') conditions.push('p.cutout_image_url IS NOT NULL');
    if (hasCutout === 'false') conditions.push('p.cutout_image_url IS NULL');
    if (minPrice) {
      params.push(parseFloat(minPrice));
      conditions.push(`lowest.price >= $${params.length}`);
    }
    if (maxPrice) {
      params.push(parseFloat(maxPrice));
      conditions.push(`lowest.price <= $${params.length}`);
    }

    const where = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';

    const allowedSorts: Record<string, string> = {
      name: 'p.name',
      price: 'COALESCE(lowest.price, 999999)',
      offers: 'COALESCE(lowest.offer_count, 0)',
      created_at: 'p.created_at',
    };
    const orderCol = allowedSorts[sortBy] || 'p.created_at';
    const orderDir = sortDir === 'asc' ? 'ASC' : 'DESC';

    params.push(limit, offset);

    const result = await query(`
      SELECT p.id, p.name, p.slug, p.brand, p.image_url, p.cutout_image_url, p.is_sneaker,
             p.created_at,
             COALESCE(lowest.price, 0) as lowest_price,
             COALESCE(lowest.offer_count, 0) as offer_count
      FROM products p
      LEFT JOIN LATERAL (
        SELECT MIN(po.price) as price, COUNT(*) as offer_count
        FROM product_offers po WHERE po.product_id = p.id AND po.in_stock = true
      ) lowest ON true
      ${where}
      ORDER BY ${orderCol} ${orderDir} NULLS LAST
      LIMIT $${params.length - 1} OFFSET $${params.length}
    `, params);

    // Count query
    const countParams = params.slice(0, params.length - 2);
    const countResult = await query(`
      SELECT COUNT(*) as total
      FROM products p
      LEFT JOIN LATERAL (
        SELECT MIN(po.price) as price, COUNT(*) as offer_count
        FROM product_offers po WHERE po.product_id = p.id AND po.in_stock = true
      ) lowest ON true
      ${where}
    `, countParams);

    // Brands for filter
    const brandsResult = await query(`
      SELECT DISTINCT brand, COUNT(*) as count FROM products
      WHERE brand IS NOT NULL AND brand != ''
      GROUP BY brand ORDER BY count DESC LIMIT 50
    `);

    return new Response(JSON.stringify({
      products: result.rows,
      total: parseInt(countResult.rows[0].total),
      page,
      limit,
      brands: brandsResult.rows,
    }), { headers: { 'Content-Type': 'application/json' } });
  } catch (err: any) {
    return new Response(JSON.stringify({ error: err.message }), { status: 500 });
  }
};

export const PUT: APIRoute = async ({ request }) => {
  try {
    const body = await request.json();

    // Bulk update
    if (body.ids && Array.isArray(body.ids)) {
      const updates: string[] = [];
      const params: any[] = [body.ids];

      if (body.is_sneaker !== undefined) {
        params.push(body.is_sneaker);
        updates.push(`is_sneaker = $${params.length}`);
      }

      if (updates.length === 0) {
        return new Response(JSON.stringify({ error: 'No fields to update' }), { status: 400 });
      }

      await query(`UPDATE products SET ${updates.join(', ')}, updated_at = now() WHERE id = ANY($1)`, params);
      return new Response(JSON.stringify({ success: true, updated: body.ids.length }));
    }

    // Single update
    if (body.id) {
      const fields: string[] = [];
      const params: any[] = [];
      let idx = 1;

      for (const key of ['is_sneaker']) {
        if (body[key] !== undefined) {
          fields.push(`${key} = $${idx}`);
          params.push(body[key]);
          idx++;
        }
      }
      if (fields.length === 0) return new Response(JSON.stringify({ error: 'No fields' }), { status: 400 });

      fields.push('updated_at = now()');
      params.push(body.id);
      await query(`UPDATE products SET ${fields.join(', ')} WHERE id = $${idx}`, params);
      return new Response(JSON.stringify({ success: true }));
    }

    return new Response(JSON.stringify({ error: 'Invalid request' }), { status: 400 });
  } catch (err: any) {
    return new Response(JSON.stringify({ error: err.message }), { status: 500 });
  }
};
