/**
 * Odczyt ogłoszeń z MySQL — lista giełdy i strona szczegółów.
 * Przy braku DATABASE_URL lub błędzie połączenia zwraca puste wyniki (build / dev bez bazy).
 */
import type { RowDataPacket } from 'mysql2'
import type { Boat, BoatListingFacets } from '@/lib/types'
import { getPool } from '@/lib/db'
import { resolveListingImagePaths } from '@/lib/sync-olx-listing-images'
import { BOAT_EQUIPMENT_ITEMS } from '@/data/boat-equipment'
import { extractYearFromText } from '@/lib/olx-mapper'
import type { MarketplaceSearch } from '@/lib/marketplace-search'
import {
  LISTING_BOAT_HULL_TYPES,
  LISTING_CATEGORIES,
  LISTING_CONDITIONS,
  LISTING_HULL_MATERIALS,
  LISTING_PROPULSION_TYPES,
} from '@/data/listing-field-options'

const PLACEHOLDER_IMAGE = '/images/shop-hero.jpg'

type ListingRow = RowDataPacket & {
  id: string
  user_id: string
  slug: string
  status: string
  category_id: string
  manufacturer_text: string | null
  title: string
  subtitle: string | null
  year_built: number | null
  length_m: string | number | null
  beam_m: string | number | null
  draft_max_m: string | number | null
  price_pln: string | number | null
  price_on_request: number | boolean | null
  offer_type: string
  condition_code: string | null
  hull_material_code: string | null
  boat_hull_type_code: string | null
  propulsion_code: string | null
  region_slug: string | null
  country: string | null
  location_text: string | null
  description_text: string | null
  notes_public: string | null
  youtube_url: string | null
  spec_json: unknown
  is_top_offer: number | boolean | null
  is_highlight: number | boolean | null
  published_at: Date | string | null
  expires_at: Date | string | null
  contact_name: string | null
  contact_email: string | null
  contact_phone: string | null
  import_source: string | null
  import_external_id: string | null
  import_source_url: string | null
}

export type MarketplaceListingPublic = {
  id: string
  slug: string
  categoryId: string
  categoryLabel: string
  manufacturerText: string
  title: string
  subtitle: string | null
  yearBuilt: number | null
  lengthM: number | null
  beamM: number | null
  draftMaxM: number | null
  pricePln: number | null
  priceOnRequest: boolean
  offerType: 'fixed' | 'negotiable' | 'on_request'
  conditionCode: string | null
  conditionLabel: string
  hullMaterialCode: string | null
  hullTypeCode: string | null
  propulsionCode: string | null
  hullMaterialLabel: string | null
  hullTypeLabel: string | null
  propulsionLabel: string | null
  regionSlug: string | null
  country: string
  locationText: string | null
  descriptionText: string | null
  notesPublic: string | null
  youtubeUrl: string | null
  spec: Record<string, unknown>
  equipmentIds: string[]
  isTopOffer: boolean
  isHighlight: boolean
  publishedAt: string | null
  expiresAt: string | null
  contactName: string | null
  contactEmail: string | null
  contactPhone: string | null
  importSourceUrl: string | null
  isExpired: boolean
  /** Ścieżki z `listing_images` (kolejność sort_order). */
  imagePaths: string[]
}

function numOrNull(v: string | number | null | undefined): number | null {
  if (v === null || v === undefined) return null
  const n = typeof v === 'number' ? v : Number(String(v).replace(',', '.'))
  return Number.isFinite(n) ? n : null
}

function boolDb(v: number | boolean | null | undefined): boolean {
  return v === true || v === 1
}

function parseSpec(raw: unknown): Record<string, unknown> {
  if (raw == null) return {}
  if (typeof raw === 'string') {
    try {
      const o = JSON.parse(raw) as unknown
      return typeof o === 'object' && o !== null && !Array.isArray(o) ? (o as Record<string, unknown>) : {}
    } catch {
      return {}
    }
  }
  if (typeof raw === 'object' && !Array.isArray(raw)) return raw as Record<string, unknown>
  return {}
}

function labelMap<T extends readonly { value: string; label: string }[]>(arr: T, value: string | null): string | null {
  if (!value) return null
  return arr.find((x) => x.value === value)?.label ?? null
}

export function equipmentLabelsForIds(ids: string[]): { id: string; label: string }[] {
  const byId = new Map<string, string>(BOAT_EQUIPMENT_ITEMS.map((it) => [it.id, it.label]))
  return ids.map((id) => ({ id, label: byId.get(id) ?? id }))
}

function rowToPublic(row: ListingRow, equipmentIds: string[], imagePaths: string[]): MarketplaceListingPublic {
  const spec = parseSpec(row.spec_json)
  const offerType = row.offer_type === 'negotiable' || row.offer_type === 'on_request' ? row.offer_type : 'fixed'
  const conditionCode = row.condition_code
  const conditionLabel =
    LISTING_CONDITIONS.find((c) => c.value === conditionCode)?.label ?? (conditionCode ? conditionCode : '—')

  const parsedImportedYear = row.import_source
    ? extractYearFromText(row.title, row.description_text, row.notes_public)
    : row.year_built
  const yearBuilt = row.import_source && row.year_built === new Date().getFullYear() && parsedImportedYear == null
    ? null
    : row.year_built
  const expiresAtMs = row.expires_at ? new Date(row.expires_at).getTime() : Number.POSITIVE_INFINITY

  return {
    id: row.id,
    slug: row.slug,
    categoryId: row.category_id,
    categoryLabel: LISTING_CATEGORIES.find((c) => c.id === row.category_id)?.label ?? row.category_id,
    manufacturerText: (row.manufacturer_text ?? '').trim(),
    title: row.title,
    subtitle: row.subtitle,
    yearBuilt,
    lengthM: numOrNull(row.length_m),
    beamM: numOrNull(row.beam_m),
    draftMaxM: numOrNull(row.draft_max_m),
    pricePln: numOrNull(row.price_pln),
    priceOnRequest: boolDb(row.price_on_request),
    offerType,
    conditionCode: row.condition_code,
    conditionLabel,
    hullMaterialCode: row.hull_material_code,
    hullTypeCode: row.boat_hull_type_code,
    propulsionCode: row.propulsion_code,
    hullMaterialLabel: labelMap(LISTING_HULL_MATERIALS, row.hull_material_code),
    hullTypeLabel: labelMap(LISTING_BOAT_HULL_TYPES, row.boat_hull_type_code),
    propulsionLabel: labelMap(LISTING_PROPULSION_TYPES, row.propulsion_code),
    regionSlug: row.region_slug,
    country: (row.country ?? 'Polska').trim() || 'Polska',
    locationText: row.location_text,
    descriptionText: row.description_text,
    notesPublic: row.notes_public,
    youtubeUrl: row.youtube_url,
    spec,
    equipmentIds,
    isTopOffer: boolDb(row.is_top_offer),
    isHighlight: boolDb(row.is_highlight),
    publishedAt: row.published_at ? String(row.published_at) : null,
    expiresAt: row.expires_at ? String(row.expires_at) : null,
    contactName: row.contact_name,
    contactEmail: row.contact_email,
    contactPhone: row.contact_phone,
    importSourceUrl: row.import_source_url?.trim() || null,
    isExpired: row.status === 'expired' || (Number.isFinite(expiresAtMs) && expiresAtMs <= Date.now()),
    imagePaths,
  }
}

/** Mapowanie wiersza bazy na kształt `Boat` pod listę i filtry sklepu. */
export function listingPublicToBoat(p: MarketplaceListingPublic): Boat {
  const notes = [p.descriptionText, p.notesPublic].filter(Boolean).join('\n\n') || '—'
  const length = p.lengthM ?? 0
  const price = p.pricePln ?? 0
  const locParts = [p.locationText, p.regionSlug, p.country].filter(Boolean)
  const facetCondition: BoatListingFacets['condition'] =
    p.conditionCode === 'new' ||
    p.conditionCode === 'used' ||
    p.conditionCode === 'refit' ||
    p.conditionCode === 'project'
      ? p.conditionCode
      : 'used'

  return {
    slug: p.slug,
    name: p.title,
    model: p.title,
    year: p.yearBuilt ?? 0,
    length: length || 0,
    price,
    priceOnRequest: p.priceOnRequest,
    subtitle: p.subtitle ?? undefined,
    offerType: p.offerType,
    isTopOfferDb: p.isTopOffer,
    publishedAt: p.publishedAt,
    images: p.imagePaths.length > 0 ? p.imagePaths : [PLACEHOLDER_IMAGE],
    description: notes,
    specifications: {
      beam: p.beamM ?? 0,
      draft: p.draftMaxM ?? 0,
      displacement: 0,
    },
    location: locParts.length ? locParts.join(' · ') : p.country,
    sold: false,
    highlights: [],
    listing: {
      category: p.categoryId as BoatListingFacets['category'],
      manufacturer: p.manufacturerText,
      condition: facetCondition,
      boatType: p.hullTypeCode ?? undefined,
      hullMaterial: p.hullMaterialCode ?? undefined,
      propulsion: p.propulsionCode ?? undefined,
      region: p.regionSlug ?? undefined,
    },
  }
}

export const LISTING_IMAGE_PLACEHOLDER = PLACEHOLDER_IMAGE

export type ListingSitemapEntry = {
  slug: string
  lastModified: string | null
}

export type MarketplaceListingPage = {
  listings: MarketplaceListingPublic[]
  page: number
  pageSize: number
  total: number
  totalPages: number
  databaseAvailable: boolean
}

function numericBound(value: string): number | null {
  const normalized = value.trim().replace(/\s/g, '').replace(',', '.')
  if (!normalized) return null
  const parsed = Number(normalized)
  return Number.isFinite(parsed) && parsed >= 0 ? parsed : null
}

function marketplaceWhere(search: MarketplaceSearch): { sql: string; values: Array<string | number> } {
  const clauses = ["status = 'active'", '(expires_at IS NULL OR expires_at > NOW(3))']
  const values: Array<string | number> = []
  const validCategory = LISTING_CATEGORIES.some((category) => category.id === search.category)

  if (validCategory) {
    clauses.push('category_id = ?')
    values.push(search.category)
  }
  if (search.manufacturer) {
    clauses.push("LOWER(CONCAT_WS(' ', manufacturer_text, title)) LIKE ?")
    values.push(`%${search.manufacturer.toLocaleLowerCase('pl-PL')}%`)
  }

  const yearMin = numericBound(search.yearMin)
  const yearMax = numericBound(search.yearMax)
  const priceMin = numericBound(search.priceMin)
  const priceMax = numericBound(search.priceMax)
  const lengthMin = numericBound(search.lengthMin)
  const lengthMax = numericBound(search.lengthMax)

  if (yearMin != null) {
    clauses.push('year_built >= ?')
    values.push(Math.floor(yearMin))
  }
  if (yearMax != null) {
    clauses.push('year_built <= ?')
    values.push(Math.floor(yearMax))
  }
  if (priceMin != null) {
    clauses.push('(price_on_request = 1 OR price_pln >= ?)')
    values.push(priceMin)
  }
  if (priceMax != null) {
    clauses.push('(price_on_request = 1 OR price_pln <= ?)')
    values.push(priceMax)
  }
  if (lengthMin != null) {
    clauses.push('length_m >= ?')
    values.push(lengthMin)
  }
  if (lengthMax != null) {
    clauses.push('length_m <= ?')
    values.push(lengthMax)
  }
  if (LISTING_CONDITIONS.some((condition) => condition.value === search.condition)) {
    clauses.push('condition_code = ?')
    values.push(search.condition)
  }
  if (LISTING_HULL_MATERIALS.some((material) => material.value === search.hullMaterial)) {
    clauses.push('hull_material_code = ?')
    values.push(search.hullMaterial)
  }
  if (LISTING_PROPULSION_TYPES.some((propulsion) => propulsion.value === search.propulsion)) {
    clauses.push('propulsion_code = ?')
    values.push(search.propulsion)
  }
  if (search.region) {
    clauses.push('region_slug = ?')
    values.push(search.region)
  }
  if (search.locationQuery) {
    clauses.push("LOWER(CONCAT_WS(' ', country, region_slug, location_text)) LIKE ?")
    values.push(`%${search.locationQuery.toLocaleLowerCase('pl-PL')}%`)
  }

  return { sql: clauses.join(' AND '), values }
}

function marketplaceOrder(search: MarketplaceSearch): string {
  const top = 'is_top_offer DESC'
  switch (search.sort) {
    case 'newest':
      return `${top}, published_at DESC, slug ASC`
    case 'price_asc':
      return `${top}, CASE WHEN price_on_request = 1 OR price_pln IS NULL OR price_pln <= 0 THEN 1 ELSE 0 END ASC, price_pln ASC, slug ASC`
    case 'price_desc':
      return `${top}, CASE WHEN price_on_request = 1 OR price_pln IS NULL OR price_pln <= 0 THEN 1 ELSE 0 END ASC, price_pln DESC, slug ASC`
    case 'year_desc':
      return `${top}, CASE WHEN year_built IS NULL OR year_built <= 0 THEN 1 ELSE 0 END ASC, year_built DESC, slug ASC`
    case 'length_desc':
      return `${top}, CASE WHEN length_m IS NULL OR length_m <= 0 THEN 1 ELSE 0 END ASC, length_m DESC, slug ASC`
    default:
      return `${top}, is_highlight DESC, published_at DESC, slug ASC`
  }
}

async function fetchImagePathsForListings(
  pool: ReturnType<typeof getPool>,
  listingIds: string[],
): Promise<Map<string, string[]>> {
  const byListing = new Map<string, string[]>()
  if (listingIds.length === 0) return byListing
  const placeholders = listingIds.map(() => '?').join(', ')
  const [rows] = await pool.execute<RowDataPacket[]>(
    `SELECT listing_id, url_path
     FROM listing_images
     WHERE listing_id IN (${placeholders})
     ORDER BY listing_id ASC, sort_order ASC, created_at ASC`,
    listingIds,
  )
  for (const row of rows) {
    const listingId = String(row.listing_id)
    const current = byListing.get(listingId) ?? []
    current.push(String(row.url_path))
    byListing.set(listingId, current)
  }
  return byListing
}

/** Filtrowana i stronicowana lista. Filtry wykonuje MySQL, więc obejmują cały katalog. */
export async function fetchMarketplaceListingPage(
  search: MarketplaceSearch,
  requestedPageSize = 48,
): Promise<MarketplaceListingPage> {
  const pageSize = Math.max(12, Math.min(Math.floor(requestedPageSize), 60))
  if (!process.env.DATABASE_URL?.trim()) {
    return { listings: [], page: 1, pageSize, total: 0, totalPages: 0, databaseAvailable: false }
  }

  try {
    const pool = getPool()
    const where = marketplaceWhere(search)
    const [countRows] = await pool.execute<RowDataPacket[]>(
      `SELECT COUNT(*) AS total FROM listings WHERE ${where.sql}`,
      where.values,
    )
    const total = Number(countRows[0]?.total ?? 0)
    const totalPages = total > 0 ? Math.ceil(total / pageSize) : 0
    const page = totalPages > 0 ? Math.min(search.page, totalPages) : 1
    const offset = (page - 1) * pageSize
    const [rows] = await pool.execute<ListingRow[]>(
      `SELECT * FROM listings
       WHERE ${where.sql}
       ORDER BY ${marketplaceOrder(search)}
       LIMIT ${pageSize} OFFSET ${offset}`,
      where.values,
    )
    const images = await fetchImagePathsForListings(pool, rows.map((row) => row.id))
    return {
      listings: rows.map((row) => rowToPublic(row, [], images.get(row.id) ?? [])),
      page,
      pageSize,
      total,
      totalPages,
      databaseAvailable: true,
    }
  } catch (e) {
    console.error('fetchMarketplaceListingPage', e)
    return { listings: [], page: 1, pageSize, total: 0, totalPages: 0, databaseAvailable: false }
  }
}

/** Lekki odczyt do sitemap.xml — bez zdjęć, wyposażenia i danych kontaktowych. */
export async function fetchActiveListingSitemapEntries(): Promise<ListingSitemapEntry[]> {
  if (!process.env.DATABASE_URL?.trim()) return []
  try {
    const pool = getPool()
    const [rows] = await pool.execute<RowDataPacket[]>(
      `SELECT slug, COALESCE(updated_at, published_at) AS last_modified
       FROM listings
       WHERE status = 'active'
         AND (expires_at IS NULL OR expires_at > NOW(3))
       ORDER BY published_at DESC`,
    )

    return rows.map((row) => {
      const raw = row.last_modified as Date | string | null
      const date = raw ? new Date(raw) : null
      return {
        slug: String(row.slug),
        lastModified: date && !Number.isNaN(date.getTime()) ? date.toISOString() : null,
      }
    })
  } catch (e) {
    console.error('fetchActiveListingSitemapEntries', e)
    return []
  }
}

async function fetchEquipmentIdsForListing(pool: ReturnType<typeof getPool>, listingId: string): Promise<string[]> {
  const [rows] = await pool.execute<RowDataPacket[]>(
    'SELECT equipment_item_id AS id FROM listing_equipment WHERE listing_id = ? ORDER BY equipment_item_id',
    [listingId],
  )
  return rows.map((r) => String((r as { id: string }).id))
}

async function fetchImagePathsForListing(pool: ReturnType<typeof getPool>, listingId: string): Promise<string[]> {
  const [rows] = await pool.execute<RowDataPacket[]>(
    'SELECT url_path FROM listing_images WHERE listing_id = ? ORDER BY sort_order ASC, created_at ASC',
    [listingId],
  )
  return rows.map((r) => String((r as { url_path: string }).url_path))
}

/** Aktywne ogłoszenia (nieprzeterminowane), najnowsze pierwsze. */
export async function fetchActiveListingsForMarketplace(): Promise<MarketplaceListingPublic[]> {
  if (!process.env.DATABASE_URL?.trim()) return []
  try {
    const pool = getPool()
    const [rows] = await pool.execute<ListingRow[]>(
      `SELECT * FROM listings
       WHERE status = 'active'
         AND (expires_at IS NULL OR expires_at > NOW(3))
       ORDER BY is_top_offer DESC, is_highlight DESC, published_at DESC
       LIMIT 500`,
    )
    const out: MarketplaceListingPublic[] = []
    for (const row of rows) {
      const eq = await fetchEquipmentIdsForListing(pool, row.id)
      const imgs = await fetchImagePathsForListing(pool, row.id)
      out.push(rowToPublic(row, eq, imgs))
    }
    return out
  } catch (e) {
    console.error('fetchActiveListingsForMarketplace', e)
    return []
  }
}

export async function fetchListingBySlug(slug: string): Promise<MarketplaceListingPublic | null> {
  if (!slug || !process.env.DATABASE_URL?.trim()) return null
  try {
    const pool = getPool()
    const [rows] = await pool.execute<ListingRow[]>(
      `SELECT * FROM listings
       WHERE slug = ?
         AND status IN ('active', 'expired')
       LIMIT 1`,
      [slug],
    )
    const row = rows[0]
    if (!row) return null
    const eq = await fetchEquipmentIdsForListing(pool, row.id)
    const localImgs = await fetchImagePathsForListing(pool, row.id)
    const imgs = await resolveListingImagePaths(row.id, row.import_source, row.import_external_id, localImgs, {
      tryPersist: true,
    })
    return rowToPublic(row, eq, imgs)
  } catch (e) {
    console.error('fetchListingBySlug', e)
    return null
  }
}

/** Kilka pozostałych ofert pod „Podobne” (bez bieżącego slug). */
export async function fetchSimilarListingSummaries(excludeSlug: string, limit: number): Promise<MarketplaceListingPublic[]> {
  if (!process.env.DATABASE_URL?.trim()) return []
  try {
    const pool = getPool()
    const [rows] = await pool.execute<ListingRow[]>(
      `SELECT * FROM listings
       WHERE status = 'active'
         AND (expires_at IS NULL OR expires_at > NOW(3))
         AND slug <> ?
       ORDER BY published_at DESC
       LIMIT ?`,
      [excludeSlug, limit],
    )
    const out: MarketplaceListingPublic[] = []
    for (const row of rows) {
      const eq = await fetchEquipmentIdsForListing(pool, row.id)
      const imgs = await fetchImagePathsForListing(pool, row.id)
      out.push(rowToPublic(row, eq, imgs))
    }
    return out
  } catch (e) {
    console.error('fetchSimilarListingSummaries', e)
    return []
  }
}