/**
 * Ogłoszenia giełdy — zapis do MySQL zgodnie z database/marketplace_listings.sql
 */
import type { RowDataPacket } from 'mysql2'
import type { PoolConnection } from 'mysql2/promise'
import { randomUUID } from 'crypto'
import { z } from 'zod'
import { getPool } from '@/lib/db'
import { removeListingUploadDirectoryOnly } from '@/lib/listing-images-upload'

/** Id kategorii — musi się zgadzać z data/listing-field-options LISTING_CATEGORIES */
export const LISTING_CATEGORY_IDS = [
  'sail',
  'power',
  'inflatable',
  'small',
  'engine',
  'trailer',
  'berth',
  'accessories',
] as const

const categoryEnum = z.enum(LISTING_CATEGORY_IDS)

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

function parseIntStrict(s: string): number | null {
  const t = s.trim()
  if (!t) return null
  const n = parseInt(t, 10)
  return Number.isFinite(n) ? n : null
}

/** Ciało żądania = stan formularza bez plików (photos). Akceptacja regulaminu wymagana przy publikacji. */
const listingFieldsSchema = z.object({
  category: categoryEnum,
  engineListingTypes: z.array(z.string()).max(3).default([]),
  smallCraftTypes: z.array(z.string()).max(3).default([]),
  trailerIncluded: z.boolean().default(false),
  berthAvailable: z.boolean().default(false),
  steeringType: z.string().default(''),
  boatType: z.string().default(''),
  manufacturer: z.string().default(''),
  modelTitle: z
    .string()
    .trim()
    .min(1, 'Uzupełnij tytuł ogłoszenia.')
    .max(70, 'Tytuł ogłoszenia może mieć maks. 70 znaków.'),
  subtitle: z.string().default(''),
  yearBuilt: z.string().trim().min(1, 'Podaj rok.'),
  condition: z.string().default(''),
  material: z.string().default(''),
  keelType: z.string().default(''),
  steering: z.string().default(''),
  lengthM: z.string().default(''),
  beamM: z.string().default(''),
  maxDraftM: z.string().default(''),
  draftKeelUpM: z.string().default(''),
  bridgeClearanceM: z.string().default(''),
  weightKg: z.string().default(''),
  ballastKg: z.string().default(''),
  ceMarking: z.string().default(''),
  maxPersons: z.string().default(''),
  mastMaterial: z.string().default(''),
  mastHeightM: z.string().default(''),
  mainsailM2: z.string().default(''),
  jibM2: z.string().default(''),
  genoaM2: z.string().default(''),
  sailsTotalM2: z.string().default(''),
  engineCount: z.string().default(''),
  enginePowerHp: z.string().default(''),
  propulsion: z.string().default(''),
  fuelType: z.string().default(''),
  fuelTankL: z.string().default(''),
  engineHours: z.string().default(''),
  cabins: z.string().default(''),
  berths: z.string().default(''),
  heads: z.string().default(''),
  headroomM: z.string().default(''),
  freshWaterL: z.string().default(''),
  holdingTankL: z.string().default(''),
  equipment: z.preprocess(
    (val) => {
      if (Array.isArray(val)) {
        return [...new Set(val.map((x) => String(x).trim()).filter((s) => s.length > 0))]
      }
      if (val && typeof val === 'object' && !Array.isArray(val)) {
        return [
          ...new Set(
            Object.values(val as Record<string, unknown>)
              .map((x) => String(x).trim())
              .filter((s) => s.length > 0),
          ),
        ]
      }
      return []
    },
    z.array(z.string()),
  ),
  notes: z.string().default(''),
  youtubeUrl: z.string().default(''),
  country: z.string().trim().min(1, 'Podaj kraj.'),
  region: z.string().default(''),
  location: z.string().default(''),
  price: z.string().default(''),
  offerType: z.enum(['fixed', 'negotiable', 'on_request']),
  contactPerson: z.string().default(''),
  email: z.string().trim().email('Podaj poprawny e-mail kontaktowy.'),
  phone: z.string().default(''),
  duration: z.enum(['30', '60', '90']),
  topOffer: z.boolean().default(false),
  highlight: z.boolean().default(false),
  invoiceTitle: z.string().default(''),
  invoiceName: z.string().default(''),
  invoiceStreet: z.string().default(''),
  invoiceHouseNo: z.string().default(''),
  invoiceZip: z.string().default(''),
  invoiceCity: z.string().default(''),
  invoicePhone: z.string().default(''),
})

function applyListingFieldRefine(data: z.infer<typeof listingFieldsSchema>, ctx: z.RefinementCtx) {
  if (data.category === 'engine') {
    if (data.engineListingTypes.length === 0) {
      ctx.addIssue({ code: z.ZodIssueCode.custom, message: 'Wybierz typ silnika (do 3).' })
    }
  }
  if (data.category === 'small') {
    if (data.smallCraftTypes.length === 0) {
      ctx.addIssue({ code: z.ZodIssueCode.custom, message: 'Wybierz typ jednostki (do 3).' })
    }
  }
  const y = parseIntStrict(data.yearBuilt)
  if (y === null || y < 1800 || y > new Date().getFullYear() + 2) {
    ctx.addIssue({ code: z.ZodIssueCode.custom, message: 'Nieprawidłowy rok.', path: ['yearBuilt'] })
  }
  if (data.offerType !== 'on_request') {
    if (parseDecimal(data.price) === null) {
      ctx.addIssue({
        code: z.ZodIssueCode.custom,
        message: 'Podaj cenę w złotych lub wybierz „Cena na zapytanie”.',
        path: ['price'],
      })
    }
  }

  const invoicePieces = [
    data.invoiceTitle,
    data.invoiceName,
    data.invoiceStreet,
    data.invoiceHouseNo,
    data.invoiceZip,
    data.invoiceCity,
    data.invoicePhone,
  ].map((s) => String(s ?? '').trim())
  const wantsInvoice = invoicePieces.some((s) => s.length > 0)
  if (wantsInvoice) {
    if (!data.invoiceName.trim()) {
      ctx.addIssue({
        code: z.ZodIssueCode.custom,
        message: 'Podaj imię i nazwisko lub firmę na fakturze.',
        path: ['invoiceName'],
      })
    }
    if (!data.invoiceStreet.trim()) {
      ctx.addIssue({ code: z.ZodIssueCode.custom, message: 'Podaj ulicę.', path: ['invoiceStreet'] })
    }
    if (!data.invoiceHouseNo.trim()) {
      ctx.addIssue({
        code: z.ZodIssueCode.custom,
        message: 'Podaj numer domu.',
        path: ['invoiceHouseNo'],
      })
    }
    if (!data.invoiceZip.trim()) {
      ctx.addIssue({ code: z.ZodIssueCode.custom, message: 'Podaj kod pocztowy.', path: ['invoiceZip'] })
    }
    if (!data.invoiceCity.trim()) {
      ctx.addIssue({ code: z.ZodIssueCode.custom, message: 'Podaj miasto.', path: ['invoiceCity'] })
    }
    if (!data.invoicePhone.trim()) {
      ctx.addIssue({
        code: z.ZodIssueCode.custom,
        message: 'Podaj telefon do rozliczeń.',
        path: ['invoicePhone'],
      })
    }
  }
}

/** Pola ogłoszenia — bez akceptacji regulaminu (edycja / PATCH). */
export const listingCoreSchema = listingFieldsSchema.superRefine(applyListingFieldRefine)

export const listingSubmitSchema = listingFieldsSchema
  .extend({
    acceptTerms: z.literal(true, { errorMap: () => ({ message: 'Zaakceptuj regulamin.' }) }),
  })
  .superRefine((data, ctx) => {
    const { acceptTerms: _accept, ...core } = data
    void _accept
    applyListingFieldRefine(core, ctx)
  })

export type ListingSubmitInput = z.infer<typeof listingSubmitSchema>

export const listingUpdateSchema = listingFieldsSchema
  .extend({
    photoPaths: z.array(z.string().max(1024)).max(40).optional(),
  })
  .superRefine(applyListingFieldRefine)

export type ListingCoreInput = z.infer<typeof listingCoreSchema>
export type ListingUpdateInput = z.infer<typeof listingUpdateSchema>

export type OwnerListingPayload = {
  id: string
  slug: string
  data: ListingCoreInput
  photoUrls: string[]
}

export type UserListingSummary = {
  id: string
  slug: string
  title: string
  status: string
  publishedAt: string | null
  expiresAt: string | null
}

function trimmedOrEmpty(s: string): string {
  return s.trim()
}

function trimmedOrNull(s: string): string | null {
  const t = s.trim()
  return t ? t : null
}

export function slugifyListingBase(title: string, yearBuilt: string): string {
  const base = [title, yearBuilt].map((part) => part.trim()).filter(Boolean).join('-')
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '')
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-|-$/g, '')
    .slice(0, 120)
  return base || 'ogloszenie'
}

async function uniqueSlug(conn: PoolConnection, candidate: string): Promise<string> {
  let slug = candidate.slice(0, 185)
  for (let i = 0; i < 12; i++) {
    const [rows] = await conn.execute<RowDataPacket[]>(
      'SELECT COUNT(*) AS c FROM listings WHERE slug = ? LIMIT 1',
      [slug],
    )
    const c = Number(rows[0]?.c ?? 0)
    if (c === 0) return slug
    const suffix = randomUUID().slice(0, 8)
    slug = `${candidate.slice(0, 170)}-${suffix}`
  }
  return `${candidate.slice(0, 120)}-${randomUUID()}`
}

function buildSpecJson(p: ListingCoreInput): Record<string, unknown> {
  const o: Record<string, unknown> = {
    keelType: trimmedOrNull(p.keelType),
    steering: trimmedOrNull(p.steering),
    draftKeelUpM: trimmedOrNull(p.draftKeelUpM),
    bridgeClearanceM: trimmedOrNull(p.bridgeClearanceM),
    weightKg: trimmedOrNull(p.weightKg),
    ballastKg: trimmedOrNull(p.ballastKg),
    ceMarking: trimmedOrNull(p.ceMarking),
    maxPersons: trimmedOrNull(p.maxPersons),
    mastMaterial: trimmedOrNull(p.mastMaterial),
    mastHeightM: trimmedOrNull(p.mastHeightM),
    mainsailM2: trimmedOrNull(p.mainsailM2),
    jibM2: trimmedOrNull(p.jibM2),
    genoaM2: trimmedOrNull(p.genoaM2),
    sailsTotalM2: trimmedOrNull(p.sailsTotalM2),
    engineCount: trimmedOrNull(p.engineCount),
    enginePowerHp: trimmedOrNull(p.enginePowerHp),
    fuelType: trimmedOrNull(p.fuelType),
    fuelTankL: trimmedOrNull(p.fuelTankL),
    engineHours: trimmedOrNull(p.engineHours),
    cabins: trimmedOrNull(p.cabins),
    berths: trimmedOrNull(p.berths),
    heads: trimmedOrNull(p.heads),
    headroomM: trimmedOrNull(p.headroomM),
    freshWaterL: trimmedOrNull(p.freshWaterL),
    holdingTankL: trimmedOrNull(p.holdingTankL),
    subtitle: trimmedOrNull(p.subtitle),
    invoiceTitle: trimmedOrNull(p.invoiceTitle),
  }
  return Object.fromEntries(Object.entries(o).filter(([, v]) => v != null && v !== ''))
}

/**
 * INSERT ogłoszenia + tagi wyposażenie faktura w jednej transakcji.
 * Status ACTIVE i daty publikacji — na MVP bez płatności (po integracji zamówienia zmień na pending_payment).
 */
export async function insertListingTransactional(
  userId: string,
  p: ListingSubmitInput,
): Promise<{ id: string; slug: string }> {
  const pool = getPool()
  const conn = await pool.getConnection()
  const listingId = randomUUID()

  try {
    await conn.beginTransaction()

    const yearNum = parseIntStrict(p.yearBuilt)!
    const baseSlug = slugifyListingBase(p.modelTitle, String(yearNum))
    const slug = await uniqueSlug(conn, baseSlug)

    const durationDays = Number.parseInt(p.duration, 10)
    const priceNum = parseDecimal(p.price)
    const price_on_request = p.offerType === 'on_request'
    const price_pln = price_on_request ? null : priceNum

    const length_m = parseDecimal(p.lengthM)
    const beam_m = parseDecimal(p.beamM)
    const draft_max_m = parseDecimal(p.maxDraftM)

    const equipmentIds = [
      ...new Set((p.equipment ?? []).map((x) => String(x).trim()).filter((s) => s.length > 0)),
    ]

    const specPayload: Record<string, unknown> = {
      ...buildSpecJson(p),
      /** Kopia ID podglądu w JSON (tabela listing_equipment jest źródłem do filtrów). */
      equipment_item_ids: equipmentIds,
    }

    await conn.execute(
      `INSERT INTO listings (
        id, user_id, slug, status, category_id, manufacturer_text, title, subtitle,
        year_built, length_m, beam_m, draft_max_m,
        price_pln, price_on_request, offer_type,
        condition_code, hull_material_code, boat_hull_type_code, propulsion_code,
        region_slug, country, location_text,
        description_text, notes_public, youtube_url,
        spec_json,
        trailer_included, berth_available, steering_engine_code,
        publication_duration_days, is_top_offer, is_highlight,
        published_at, expires_at,
        contact_name, contact_email, contact_phone
      ) VALUES (
        ?, ?, ?, 'active', ?, ?, ?, ?,
        ?, ?, ?, ?,
        ?, ?, ?,
        NULLIF(TRIM(?), ''), NULLIF(TRIM(?), ''), NULLIF(TRIM(?), ''), NULLIF(TRIM(?), ''),
        NULLIF(TRIM(?), ''), ?, NULLIF(TRIM(?), ''),
        NULL, NULLIF(TRIM(?), ''), NULLIF(TRIM(?), ''),
        CAST(? AS JSON),
        ?, ?, NULLIF(TRIM(?), ''),
        ?, ?, ?,
        NOW(3), DATE_ADD(NOW(3), INTERVAL ? DAY),
        COALESCE(NULLIF(TRIM(?), ''), ''), ?, NULLIF(TRIM(?), '')
      )`,
      [
        listingId,
        userId,
        slug,
        p.category,
        trimmedOrEmpty(p.manufacturer).slice(0, 255),
        p.modelTitle.trim().slice(0, 512),
        trimmedOrNull(p.subtitle),
        yearNum,
        length_m,
        beam_m,
        draft_max_m,
        price_pln,
        price_on_request ? 1 : 0,
        p.offerType,
        p.condition,
        p.material,
        p.boatType,
        p.propulsion,
        p.region,
        trimmedOrEmpty(p.country).slice(0, 80),
        trimmedOrNull(p.location),
        trimmedOrNull(p.notes)?.slice(0, 65535) ?? null,
        trimmedOrNull(p.youtubeUrl),
        JSON.stringify(specPayload),
        p.category === 'small' ? (p.trailerIncluded ? 1 : 0) : null,
        p.category === 'small' ? (p.berthAvailable ? 1 : 0) : null,
        p.category === 'engine' ? (trimmedOrNull(p.steeringType) ?? null) : null,
        durationDays,
        p.topOffer ? 1 : 0,
        p.highlight ? 1 : 0,
        durationDays,
        trimmedOrEmpty(p.contactPerson),
        trimmedOrEmpty(p.email).toLowerCase(),
        trimmedOrNull(p.phone),
      ],
    )

    for (const v of [...new Set(p.engineListingTypes.map((x) => x.trim()).filter(Boolean))]) {
      await conn.execute(
        `INSERT INTO listing_classification_tags (listing_id, tag_kind, value_code) VALUES (?, 'engine_listing_type', ?)`,
        [listingId, v.slice(0, 64)],
      )
    }

    for (const v of [...new Set(p.smallCraftTypes.map((x) => x.trim()).filter(Boolean))]) {
      await conn.execute(
        `INSERT INTO listing_classification_tags (listing_id, tag_kind, value_code) VALUES (?, 'small_craft_type', ?)`,
        [listingId, v.slice(0, 64)],
      )
    }

    if (equipmentIds.length > 0) {
      await conn.query('INSERT INTO listing_equipment (listing_id, equipment_item_id) VALUES ?', [
        equipmentIds.map((id) => [listingId, id.slice(0, 96)]),
      ])
    }

    await conn.execute(
      `INSERT INTO listing_invoice_details (
        listing_id, invoice_title_or_company, invoice_name, street, house_no, zip, city, phone
      ) VALUES (?, NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''))`,
      [
        listingId,
        p.invoiceTitle,
        p.invoiceName,
        p.invoiceStreet,
        p.invoiceHouseNo,
        p.invoiceZip,
        p.invoiceCity,
        p.invoicePhone,
      ],
    )

    await conn.commit()
    return { id: listingId, slug }
  } catch (e) {
    await conn.rollback()
    throw e
  } finally {
    conn.release()
  }
}

function specStr(spec: Record<string, unknown>, key: string): string {
  const v = spec[key]
  if (v == null) return ''
  return String(v)
}

function parseSpecJson(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 durationToken(days: number | null): '30' | '60' | '90' {
  if (days === 30 || days === 60 || days === 90) return String(days) as '30' | '60' | '90'
  return '60'
}

function boolDb01(v: unknown): boolean {
  return v === true || v === 1
}

type ListingRowDb = RowDataPacket & {
  id: string
  user_id: string
  slug: string
  category_id: string
  manufacturer_text: string | null
  title: string
  subtitle: string | null
  year_built: number | null
  length_m: unknown
  beam_m: unknown
  draft_max_m: unknown
  price_pln: unknown
  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
  notes_public: string | null
  youtube_url: string | null
  spec_json: unknown
  trailer_included: number | boolean | null
  berth_available: number | boolean | null
  steering_engine_code: string | null
  publication_duration_days: number | null
  is_top_offer: number | boolean | null
  is_highlight: number | boolean | null
  contact_name: string | null
  contact_email: string | null
  contact_phone: string | null
  inv_invoice_title: string | null
  inv_invoice_name: string | null
  inv_street: string | null
  inv_house_no: string | null
  inv_zip: string | null
  inv_city: string | null
  inv_phone: string | null
}

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

export async function fetchListingOwnerPayload(
  userId: string,
  listingId: string,
): Promise<OwnerListingPayload | null> {
  if (!process.env.DATABASE_URL?.trim()) return null
  const pool = getPool()
  const [rows] = await pool.execute<ListingRowDb[]>(
    `SELECT l.*,
      inv.invoice_title_or_company AS inv_invoice_title,
      inv.invoice_name AS inv_invoice_name,
      inv.street AS inv_street,
      inv.house_no AS inv_house_no,
      inv.zip AS inv_zip,
      inv.city AS inv_city,
      inv.phone AS inv_phone
    FROM listings l
    LEFT JOIN listing_invoice_details inv ON inv.listing_id = l.id
    WHERE l.id = ? AND l.user_id = ?
    LIMIT 1`,
    [listingId, userId],
  )
  const row = rows[0]
  if (!row) return null

  const [eqRows] = await pool.execute<RowDataPacket[]>(
    'SELECT equipment_item_id AS id FROM listing_equipment WHERE listing_id = ? ORDER BY equipment_item_id',
    [listingId],
  )
  const equipment = eqRows.map((r) => String((r as { id: string }).id))

  const [tagRows] = await pool.execute<RowDataPacket[]>(
    `SELECT tag_kind, value_code FROM listing_classification_tags WHERE listing_id = ?`,
    [listingId],
  )
  const engineListingTypes: string[] = []
  const smallCraftTypes: string[] = []
  for (const t of tagRows) {
    const tr = t as { tag_kind: string; value_code: string }
    if (tr.tag_kind === 'engine_listing_type') engineListingTypes.push(tr.value_code)
    if (tr.tag_kind === 'small_craft_type') smallCraftTypes.push(tr.value_code)
  }

  const [imgRows] = await pool.execute<RowDataPacket[]>(
    'SELECT url_path FROM listing_images WHERE listing_id = ? ORDER BY sort_order ASC, created_at ASC',
    [listingId],
  )
  const photoUrls = imgRows.map((r) => String((r as { url_path: string }).url_path))

  const spec = parseSpecJson(row.spec_json)
  const offerType =
    row.offer_type === 'negotiable' || row.offer_type === 'on_request' ? row.offer_type : 'fixed'
  const priceOnRequest = boolDb01(row.price_on_request)
  let priceStr = ''
  if (!priceOnRequest && row.price_pln != null) {
    const p = typeof row.price_pln === 'number' ? row.price_pln : Number(String(row.price_pln))
    if (Number.isFinite(p)) priceStr = String(p)
  }

  const data: ListingCoreInput = {
    category: row.category_id as ListingCoreInput['category'],
    engineListingTypes,
    smallCraftTypes,
    trailerIncluded: boolDb01(row.trailer_included),
    berthAvailable: boolDb01(row.berth_available),
    steeringType: row.steering_engine_code ?? '',
    boatType: row.boat_hull_type_code ?? '',
    manufacturer: (row.manufacturer_text ?? '').trim(),
    modelTitle: row.title ?? '',
    subtitle: (row.subtitle ?? specStr(spec, 'subtitle')).trim(),
    yearBuilt: row.year_built != null ? String(row.year_built) : '',
    condition: row.condition_code ?? '',
    material: row.hull_material_code ?? '',
    keelType: specStr(spec, 'keelType'),
    steering: specStr(spec, 'steering'),
    lengthM: fmtNumForForm(row.length_m),
    beamM: fmtNumForForm(row.beam_m),
    maxDraftM: fmtNumForForm(row.draft_max_m),
    draftKeelUpM: specStr(spec, 'draftKeelUpM'),
    bridgeClearanceM: specStr(spec, 'bridgeClearanceM'),
    weightKg: specStr(spec, 'weightKg'),
    ballastKg: specStr(spec, 'ballastKg'),
    ceMarking: specStr(spec, 'ceMarking'),
    maxPersons: specStr(spec, 'maxPersons'),
    mastMaterial: specStr(spec, 'mastMaterial'),
    mastHeightM: specStr(spec, 'mastHeightM'),
    mainsailM2: specStr(spec, 'mainsailM2'),
    jibM2: specStr(spec, 'jibM2'),
    genoaM2: specStr(spec, 'genoaM2'),
    sailsTotalM2: specStr(spec, 'sailsTotalM2'),
    engineCount: specStr(spec, 'engineCount'),
    enginePowerHp: specStr(spec, 'enginePowerHp'),
    propulsion: row.propulsion_code ?? '',
    fuelType: specStr(spec, 'fuelType'),
    fuelTankL: specStr(spec, 'fuelTankL'),
    engineHours: specStr(spec, 'engineHours'),
    cabins: specStr(spec, 'cabins'),
    berths: specStr(spec, 'berths'),
    heads: specStr(spec, 'heads'),
    headroomM: specStr(spec, 'headroomM'),
    freshWaterL: specStr(spec, 'freshWaterL'),
    holdingTankL: specStr(spec, 'holdingTankL'),
    equipment,
    notes: (row.notes_public ?? '').trim(),
    youtubeUrl: (row.youtube_url ?? '').trim(),
    country: (row.country ?? 'Polska').trim() || 'Polska',
    region: (row.region_slug ?? '').trim(),
    location: (row.location_text ?? '').trim(),
    price: priceStr,
    offerType,
    contactPerson: (row.contact_name ?? '').trim(),
    email: (row.contact_email ?? '').trim(),
    phone: (row.contact_phone ?? '').trim(),
    duration: durationToken(
      row.publication_duration_days != null ? Number(row.publication_duration_days) : null,
    ),
    topOffer: boolDb01(row.is_top_offer),
    highlight: boolDb01(row.is_highlight),
    invoiceTitle: (row.inv_invoice_title ?? '').trim(),
    invoiceName: (row.inv_invoice_name ?? '').trim(),
    invoiceStreet: (row.inv_street ?? '').trim(),
    invoiceHouseNo: (row.inv_house_no ?? '').trim(),
    invoiceZip: (row.inv_zip ?? '').trim(),
    invoiceCity: (row.inv_city ?? '').trim(),
    invoicePhone: (row.inv_phone ?? '').trim(),
  }

  return { id: row.id, slug: row.slug, data, photoUrls }
}

export async function updateListingTransactional(
  userId: string,
  listingId: string,
  p: ListingCoreInput,
  photoPaths?: string[] | undefined,
): Promise<{ slug: string }> {
  const pool = getPool()
  const conn = await pool.getConnection()

  try {
    await conn.beginTransaction()

    const [ownRows] = await conn.execute<RowDataPacket[]>(
      'SELECT id, slug FROM listings WHERE id = ? AND user_id = ? LIMIT 1',
      [listingId, userId],
    )
    if (!ownRows[0]) {
      await conn.rollback()
      const [anyRow] = await conn.execute<RowDataPacket[]>(
        'SELECT id FROM listings WHERE id = ? LIMIT 1',
        [listingId],
      )
      if (!anyRow[0]) {
        const e = new Error('NOT_FOUND')
        ;(e as { code?: string }).code = 'NOT_FOUND'
        throw e
      }
      const e = new Error('FORBIDDEN')
      ;(e as { code?: string }).code = 'FORBIDDEN'
      throw e
    }
    const slug = String((ownRows[0] as { slug: string }).slug)

    const yearNum = parseIntStrict(p.yearBuilt)!
    const priceNum = parseDecimal(p.price)
    const price_on_request = p.offerType === 'on_request'
    const price_pln = price_on_request ? null : priceNum

    const length_m = parseDecimal(p.lengthM)
    const beam_m = parseDecimal(p.beamM)
    const draft_max_m = parseDecimal(p.maxDraftM)

    const equipmentIds = [
      ...new Set((p.equipment ?? []).map((x) => String(x).trim()).filter((s) => s.length > 0)),
    ]

    const specPayload: Record<string, unknown> = {
      ...buildSpecJson(p),
      equipment_item_ids: equipmentIds,
    }

    const durationDays = Number.parseInt(p.duration, 10)

    await conn.execute(
      `UPDATE listings SET
        category_id = ?, manufacturer_text = ?, title = ?, subtitle = ?,
        year_built = ?, length_m = ?, beam_m = ?, draft_max_m = ?,
        price_pln = ?, price_on_request = ?, offer_type = ?,
        condition_code = NULLIF(TRIM(?), ''), hull_material_code = NULLIF(TRIM(?), ''),
        boat_hull_type_code = NULLIF(TRIM(?), ''), propulsion_code = NULLIF(TRIM(?), ''),
        region_slug = NULLIF(TRIM(?), ''), country = ?, location_text = NULLIF(TRIM(?), ''),
        notes_public = NULLIF(TRIM(?), ''), youtube_url = NULLIF(TRIM(?), ''),
        spec_json = CAST(? AS JSON),
        trailer_included = ?, berth_available = ?, steering_engine_code = NULLIF(TRIM(?), ''),
        publication_duration_days = ?, is_top_offer = ?, is_highlight = ?,
        contact_name = ?, contact_email = ?, contact_phone = NULLIF(TRIM(?), '')
      WHERE id = ? AND user_id = ?`,
      [
        p.category,
        trimmedOrEmpty(p.manufacturer).slice(0, 255),
        p.modelTitle.trim().slice(0, 512),
        trimmedOrNull(p.subtitle),
        yearNum,
        length_m,
        beam_m,
        draft_max_m,
        price_pln,
        price_on_request ? 1 : 0,
        p.offerType,
        p.condition,
        p.material,
        p.boatType,
        p.propulsion,
        p.region,
        trimmedOrEmpty(p.country).slice(0, 80),
        trimmedOrNull(p.location),
        trimmedOrNull(p.notes)?.slice(0, 65535) ?? null,
        trimmedOrNull(p.youtubeUrl),
        JSON.stringify(specPayload),
        p.category === 'small' ? (p.trailerIncluded ? 1 : 0) : null,
        p.category === 'small' ? (p.berthAvailable ? 1 : 0) : null,
        p.category === 'engine' ? (trimmedOrNull(p.steeringType) ?? null) : null,
        durationDays,
        p.topOffer ? 1 : 0,
        p.highlight ? 1 : 0,
        trimmedOrEmpty(p.contactPerson),
        trimmedOrEmpty(p.email).toLowerCase(),
        trimmedOrNull(p.phone),
        listingId,
        userId,
      ],
    )

    await conn.execute('DELETE FROM listing_classification_tags WHERE listing_id = ?', [listingId])
    for (const v of [...new Set(p.engineListingTypes.map((x) => x.trim()).filter(Boolean))]) {
      await conn.execute(
        `INSERT INTO listing_classification_tags (listing_id, tag_kind, value_code) VALUES (?, 'engine_listing_type', ?)`,
        [listingId, v.slice(0, 64)],
      )
    }
    for (const v of [...new Set(p.smallCraftTypes.map((x) => x.trim()).filter(Boolean))]) {
      await conn.execute(
        `INSERT INTO listing_classification_tags (listing_id, tag_kind, value_code) VALUES (?, 'small_craft_type', ?)`,
        [listingId, v.slice(0, 64)],
      )
    }

    await conn.execute('DELETE FROM listing_equipment WHERE listing_id = ?', [listingId])
    if (equipmentIds.length > 0) {
      await conn.query('INSERT INTO listing_equipment (listing_id, equipment_item_id) VALUES ?', [
        equipmentIds.map((id) => [listingId, id.slice(0, 96)]),
      ])
    }

    await conn.execute(
      `INSERT INTO listing_invoice_details (
        listing_id, invoice_title_or_company, invoice_name, street, house_no, zip, city, phone
      ) VALUES (?, NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''), NULLIF(TRIM(?),''))
      ON DUPLICATE KEY UPDATE
        invoice_title_or_company = VALUES(invoice_title_or_company),
        invoice_name = VALUES(invoice_name),
        street = VALUES(street),
        house_no = VALUES(house_no),
        zip = VALUES(zip),
        city = VALUES(city),
        phone = VALUES(phone)`,
      [
        listingId,
        p.invoiceTitle,
        p.invoiceName,
        p.invoiceStreet,
        p.invoiceHouseNo,
        p.invoiceZip,
        p.invoiceCity,
        p.invoicePhone,
      ],
    )

    if (photoPaths !== undefined) {
      await conn.execute('DELETE FROM listing_images WHERE listing_id = ?', [listingId])
      if (photoPaths.length > 0) {
        const cleaned = photoPaths.map((u) => u.trim()).filter((u) => u.length > 0)
        const tuples = cleaned.map((urlPath, i) => [randomUUID(), listingId, urlPath.slice(0, 1024), i])
        await conn.query(
          'INSERT INTO listing_images (id, listing_id, url_path, sort_order) VALUES ?',
          [tuples],
        )
      } else {
        await removeListingUploadDirectoryOnly(listingId)
      }
    }

    await conn.commit()
    return { slug }
  } catch (e) {
    await conn.rollback()
    throw e
  } finally {
    conn.release()
  }
}

export async function fetchListingSummariesForUser(userId: string): Promise<UserListingSummary[]> {
  if (!process.env.DATABASE_URL?.trim()) return []
  try {
    const pool = getPool()
    const [rows] = await pool.execute<RowDataPacket[]>(
      `SELECT id, slug, title, status, published_at, expires_at
       FROM listings WHERE user_id = ?
       ORDER BY published_at DESC, created_at DESC
       LIMIT 200`,
      [userId],
    )
    return rows.map((r) => {
      const row = r as {
        id: string
        slug: string
        title: string
        status: string
        published_at: Date | string | null
        expires_at: Date | string | null
      }
      return {
        id: String(row.id),
        slug: String(row.slug),
        title: String(row.title ?? ''),
        status: String(row.status ?? ''),
        publishedAt: row.published_at != null ? String(row.published_at) : null,
        expiresAt: row.expires_at != null ? String(row.expires_at) : null,
      }
    })
  } catch (e) {
    console.error('fetchListingSummariesForUser', e)
    return []
  }
}