import { NextRequest, NextResponse } from 'next/server'
import pool from '@/lib/db'

// GET /api/drops/[id] - Get a specific drop
export async function GET(
  request: NextRequest,
  { params }: { params: { id: string } }
) {
  try {
    const id = parseInt(params.id, 10)
    if (isNaN(id)) {
      return NextResponse.json(
        { error: 'Invalid drop ID' },
        { status: 400 }
      )
    }

    const [rows] = await pool.execute(
      'SELECT * FROM drops WHERE id = ?',
      [id]
    )

    const drops = rows as any[]
    if (drops.length === 0) {
      return NextResponse.json(
        { error: 'Drop not found' },
        { status: 404 }
      )
    }

    const drop = drops[0]

    // Calculate fill from sales
    const [salesRows] = await pool.execute(
      'SELECT COALESCE(SUM(size), 0) as total_fill FROM sales WHERE drop_id = ?',
      [id]
    )
    const salesData = salesRows as any[]
    const totalFillInGrams = salesData[0]?.total_fill || 0

    let fill = totalFillInGrams
    if (drop.unit === 'kg') {
      fill = totalFillInGrams / 1000
    }

    return NextResponse.json({
      ...drop,
      fill: fill,
    })
  } catch (error) {
    console.error('Error fetching drop:', error)
    return NextResponse.json(
      { error: 'Failed to fetch drop' },
      { status: 500 }
    )
  }
}

// PUT /api/drops/[id] - Update a drop
export async function PUT(
  request: NextRequest,
  { params }: { params: { id: string } }
) {
  try {
    const id = parseInt(params.id, 10)
    if (isNaN(id)) {
      return NextResponse.json(
        { error: 'Invalid drop ID' },
        { status: 400 }
      )
    }

    const body = await request.json()
    const { item, description, size, unit, ppu, priceChf, priceEur, wholesalePriceChf, wholesalePriceEur, imageUrl, startTime } = body

    // Check if drop exists
    const [existingRows] = await pool.execute(
      'SELECT id FROM drops WHERE id = ?',
      [id]
    )
    const existing = existingRows as any[]
    if (existing.length === 0) {
      return NextResponse.json(
        { error: 'Drop not found' },
        { status: 404 }
      )
    }

    // Build update query dynamically
    const updates: string[] = []
    const values: any[] = []

    if (item !== undefined) {
      updates.push('item = ?')
      values.push(item)
    }

    if (description !== undefined) {
      updates.push('description = ?')
      values.push(description || null)
    }

    if (size !== undefined) {
      if (size <= 0) {
        return NextResponse.json(
          { error: 'Size must be greater than 0' },
          { status: 400 }
        )
      }
      updates.push('size = ?')
      values.push(size)
    }

    if (unit !== undefined) {
      updates.push('unit = ?')
      values.push(unit)
    }

    if (ppu !== undefined) {
      if (ppu <= 0) {
        return NextResponse.json(
          { error: 'Price per unit must be greater than 0' },
          { status: 400 }
        )
      }
      updates.push('ppu = ?')
      values.push(ppu)
    }

    if (priceChf !== undefined) {
      if (priceChf !== null && priceChf < 0) {
        return NextResponse.json(
          { error: 'Price CHF must be greater than or equal to 0' },
          { status: 400 }
        )
      }
      updates.push('price_chf = ?')
      values.push(priceChf !== null && priceChf !== '' ? priceChf : null)
    }

    if (priceEur !== undefined) {
      if (priceEur !== null && priceEur < 0) {
        return NextResponse.json(
          { error: 'Price EUR must be greater than or equal to 0' },
          { status: 400 }
        )
      }
      updates.push('price_eur = ?')
      values.push(priceEur !== null && priceEur !== '' ? priceEur : null)
    }

    if (wholesalePriceChf !== undefined) {
      if (wholesalePriceChf !== null && wholesalePriceChf < 0) {
        return NextResponse.json(
          { error: 'Wholesale price CHF must be greater than or equal to 0' },
          { status: 400 }
        )
      }
      updates.push('wholesale_price_chf = ?')
      values.push(wholesalePriceChf !== null && wholesalePriceChf !== '' ? wholesalePriceChf : null)
    }

    if (wholesalePriceEur !== undefined) {
      if (wholesalePriceEur !== null && wholesalePriceEur < 0) {
        return NextResponse.json(
          { error: 'Wholesale price EUR must be greater than or equal to 0' },
          { status: 400 }
        )
      }
      updates.push('wholesale_price_eur = ?')
      values.push(wholesalePriceEur !== null && wholesalePriceEur !== '' ? wholesalePriceEur : null)
    }

    if (imageUrl !== undefined) {
      updates.push('image_url = ?')
      values.push(imageUrl || null)
    }

    if (startTime !== undefined) {
      updates.push('start_time = ?')
      values.push(startTime || null)
    }

    if (updates.length === 0) {
      return NextResponse.json(
        { error: 'No fields to update' },
        { status: 400 }
      )
    }

    values.push(id)
    const query = `UPDATE drops SET ${updates.join(', ')} WHERE id = ?`
    await pool.execute(query, values)

    // Fetch updated drop
    const [rows] = await pool.execute('SELECT * FROM drops WHERE id = ?', [id])
    const drops = rows as any[]
    const drop = drops[0]

    // Calculate fill
    const [salesRows] = await pool.execute(
      'SELECT COALESCE(SUM(size), 0) as total_fill FROM sales WHERE drop_id = ?',
      [id]
    )
    const salesData = salesRows as any[]
    const totalFillInGrams = salesData[0]?.total_fill || 0

    let fill = totalFillInGrams
    if (drop.unit === 'kg') {
      fill = totalFillInGrams / 1000
    }

    return NextResponse.json({
      ...drop,
      fill: fill,
    })
  } catch (error) {
    console.error('Error updating drop:', error)
    return NextResponse.json(
      { error: 'Failed to update drop' },
      { status: 500 }
    )
  }
}

// DELETE /api/drops/[id] - Delete a drop
export async function DELETE(
  request: NextRequest,
  { params }: { params: { id: string } }
) {
  try {
    const id = parseInt(params.id, 10)
    if (isNaN(id)) {
      return NextResponse.json(
        { error: 'Invalid drop ID' },
        { status: 400 }
      )
    }

    // Check if drop exists
    const [existingRows] = await pool.execute(
      'SELECT id FROM drops WHERE id = ?',
      [id]
    )
    const existing = existingRows as any[]
    if (existing.length === 0) {
      return NextResponse.json(
        { error: 'Drop not found' },
        { status: 404 }
      )
    }

    // Delete drop (cascade will handle related sales)
    await pool.execute('DELETE FROM drops WHERE id = ?', [id])

    return NextResponse.json({ success: true })
  } catch (error) {
    console.error('Error deleting drop:', error)
    return NextResponse.json(
      { error: 'Failed to delete drop' },
      { status: 500 }
    )
  }
}