import { NextResponse } from 'next/server'
import { getCurrentUser } from '@/lib/auth'
import { getGameDbPool, getGameDbConfig } from '@/lib/game-db'
import { getCharactersByDiscordId } from '@/lib/services/character-service'
import type { RowDataPacket } from 'mysql2'

export const dynamic = 'force-dynamic'

export async function GET(
  request: Request,
  { params }: { params: Promise<{ id: string }> }
) {
  try {
    const user = await getCurrentUser()
    if (!user) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
    }

    const { id: characterId } = await params
    const discordId = user.discordId

    if (!discordId) {
      return NextResponse.json({ error: 'No Discord ID linked' }, { status: 400 })
    }

    // Verify this character belongs to the user
    const characters = await getCharactersByDiscordId(discordId)
    const character = characters.find(c => c.id === characterId || c.citizenid === characterId)
    
    if (!character) {
      return NextResponse.json({ error: 'Character not found or access denied' }, { status: 403 })
    }

    const config = await getGameDbConfig()
    if (!config) {
      return NextResponse.json({ error: 'Game database not configured' }, { status: 500 })
    }

    const pool = await getGameDbPool()
    if (!pool) {
      return NextResponse.json({ error: 'Database connection failed' }, { status: 500 })
    }

    let properties: any[] = []

    // Try different property tables depending on server setup
    const propertyTables = [
      'player_houses',
      'properties', 
      'apartments',
      'housing',
      'owned_properties',
      'houselocations'
    ]

    for (const table of propertyTables) {
      try {
        if (config.framework === 'QBCORE') {
          const [rows] = await pool.query<RowDataPacket[]>(
            `SELECT * FROM ${table} WHERE citizenid = ? OR owner = ?`,
            [characterId, characterId]
          )
          
          if (rows && rows.length > 0) {
            properties = rows.map(row => ({
              id: row.id || row.house || row.name,
              name: row.name || row.label || row.house || `Immobilie ${row.id}`,
              type: row.type || row.tier || 'house',
              price: row.price || 0,
              garage: row.garage || false,
              keyholders: row.keyholders ? (typeof row.keyholders === 'string' ? JSON.parse(row.keyholders) : row.keyholders) : [],
            }))
            break
          }
        } else {
          // ESX
          const [rows] = await pool.query<RowDataPacket[]>(
            `SELECT * FROM ${table} WHERE owner = ? OR identifier = ?`,
            [character.identifier || characterId, character.identifier || characterId]
          )
          
          if (rows && rows.length > 0) {
            properties = rows.map(row => ({
              id: row.id || row.name,
              name: row.name || row.label || `Immobilie ${row.id}`,
              type: row.type || 'house',
              price: row.price || 0,
            }))
            break
          }
        }
      } catch {
        // Table doesn't exist, try next
        continue
      }
    }

    return NextResponse.json({ properties })
  } catch (error) {
    console.error('[Properties API] Error:', error)
    return NextResponse.json({ error: 'Internal server error' }, { status: 500 })
  }
}