'use server'

import { createClient } from '@/lib/supabase/server'
import { createHash } from 'crypto'
import { createAdminClient } from '@/lib/supabase/admin'
import { revalidatePath } from 'next/cache'
import { sendMail } from '@/utils/mail'
import { InvoicePDF } from '@/components/invoice-pdf'
import { renderToBuffer } from '@react-pdf/renderer'
import React from 'react'
import { buildCustomerSnapshot, buildOrderSnapshot } from '@/lib/license-transform'
import type { Category, EndCustomer, Order, Product, Profile, WizardSelections } from '@/lib/types'
import { getProducts, getCategories } from '@/lib/actions/products'
import { validateWizardSelections } from '@/lib/actions/validation'
import { getOrderEmailTemplate, getStatusEmailTemplate } from '@/lib/actions/email-templates'

// ─── Hilfsfunktionen ─────────────────────────────────────────────────────────

/**
 * Erzeugt eine menschenlesbare, nicht-sequenzielle Bestellnummer.
 * Format: BE-YYYY-NNNNN (z.B. BE-2026-84731)
 * Kein Rückschluss auf Gesamtanzahl der Bestellungen möglich.
 */
function generateOrderNumber(): string {
  const year = new Date().getFullYear()
  const rand = Math.floor(10000 + Math.random() * 90000) // 5-stellig, niemals < 10000
  return `AE-${year}-${rand}`
}

/**
 * Erstellt einen SHA-256 Hash aus dem Bestell-Snapshot.
 * Wird für den Idempotenz-Guard verwendet (verhindert Doppelbestellungen).
 */
function hashOrderSnapshot(snapshot: object): string {
  const str = JSON.stringify(snapshot)
  return createHash('sha256').update(str).digest('hex')
}

// ─── submitOrder ──────────────────────────────────────────────────────────────

/**
 * Speichert eine abgeschlossene Bestellung als versionierten Snapshot in der DB,
 * generiert die PDF-Rechnung und lädt sie in den Supabase Storage hoch.
 *
 * Enthält Idempotenz-Guard: Wenn innerhalb von 30 Sekunden eine identische
 * Bestellung vom gleichen User existiert, wird diese zurückgegeben statt
 * einer neuen erstellt (Schutz gegen Doppelklick / Netzwerk-Retry).
 */
export async function submitOrder(params: {
  selections: WizardSelections
  moduleQuantities?: Record<string, number>
  products?: Product[]
  categories?: Category[]
  customerProfile: Partial<Profile>
  endCustomerId?: string | null
  endCustomer?: EndCustomer | null
  billingInterval?: 'one_time' | 'monthly'
  lastLicenseDate?: string | null
}): Promise<Order> {
  const { selections, moduleQuantities, customerProfile, endCustomerId, endCustomer, billingInterval, lastLicenseDate } = params
  const supabase = await createClient()

  const { data: { user } } = await supabase.auth.getUser()
  if (!user) throw new Error('Not authenticated')

  // Check if user is assigned to a company or is admin
  const { data: dbUser } = await supabase
    .from('users')
    .select('role, company_id')
    .eq('id', user.id)
    .single()

  const isAdminUser = dbUser?.role === 'admin'
  if (!isAdminUser && !dbUser?.company_id) {
    throw new Error('Sie müssen einer Firma zugewiesen sein, um eine Anfrage zu erstellen.')
  }

  // Fetch catalog directly from DB to prevent client tampering
  const dbProducts = await getProducts()
  const dbCategories = await getCategories()

  // ─── Constraint Validation ──────────────────────────────────────────────────
  validateWizardSelections(selections, dbProducts, dbCategories)

  // 1. Snapshots aufbauen
  // Wenn Endkunde vorhanden: dessen Daten einfrieren; sonst Partner-Profil (Fallback)
  const customerSnapshot = buildCustomerSnapshot(customerProfile, endCustomer ?? null)
  const orderSnapshot = buildOrderSnapshot(selections, dbProducts, dbCategories, billingInterval, moduleQuantities, lastLicenseDate)

  // 2. Idempotenz-Guard: Prüfen ob identische Bestellung in den letzten 5 Minuten existiert
  const orderHash = hashOrderSnapshot(orderSnapshot)
  const fiveMinutesAgo = new Date(Date.now() - 5 * 60_000).toISOString()

  const { data: existingOrder } = await supabase
    .from('orders')
    .select('*')
    .eq('user_id', user.id)
    .eq('order_hash', orderHash)
    .gte('created_at', fiveMinutesAgo)
    .maybeSingle()

  if (existingOrder) {
    console.warn('[submitOrder] Duplicate order detected, returning existing order:', existingOrder.order_number)
    return existingOrder as Order
  }

  // 3. Eindeutige Bestellnummer generieren (Retry bei Kollision)
  let orderNumber: string = ''
  for (let attempt = 0; attempt < 5; attempt++) {
    const candidate = generateOrderNumber()
    const { data: collision } = await supabase
      .from('orders')
      .select('id')
      .eq('order_number', candidate)
      .maybeSingle()
    if (!collision) {
      orderNumber = candidate
      break
    }
  }
  if (!orderNumber) throw new Error('Konnte keine eindeutige Bestellnummer generieren.')

  // 4. Bestellung in DB schreiben
  const { data: order, error: orderError } = await supabase
    .from('orders')
    .insert([{
      user_id: user.id,
      company_id: dbUser?.company_id || null,
      order_number: orderNumber,
      order_hash: orderHash,
      end_customer_id: endCustomerId ?? null,
      total_price: orderSnapshot.total,
      customer_data: customerSnapshot,
      order_data: orderSnapshot,
      status: 'pending',
    }])
    .select()
    .single()

  if (orderError) throw orderError

  // 5. PDF generieren (greift auf Snapshot-Daten zu, nicht auf Live-Katalog)
  try {
    const buffer = await renderToBuffer(
      React.createElement(InvoicePDF, {
        order,
        customer: customerSnapshot,
        orderSnapshot,
      })
    )

    // 6. PDF in Supabase Storage hochladen
    const fileName = `ab_${order.id}.pdf`
    const { error: uploadError } = await supabase
      .storage
      .from('invoices')
      .upload(fileName, buffer, { contentType: 'application/pdf', upsert: true })

    if (uploadError) {
      console.error('PDF Upload Error:', uploadError)
    } else {
      await supabase.from('orders').update({ pdf_url: fileName }).eq('id', order.id)
      order.pdf_url = fileName
    }

    // 7. E-Mail mit PDF-Anhang an den Besteller senden
    if (user.email) {
      try {
        const formattedDate = new Date(order.created_at).toLocaleDateString('de-DE')

        const taxRate = orderSnapshot.tax_rate ?? 19
        const items = orderSnapshot.items ?? []
        const oneTimeItems = items.filter((item: any) => item.billing_interval === 'one_time')
        const monthlyItems = items.filter((item: any) => item.billing_interval === 'monthly')

        const oneTimeNet = oneTimeItems.reduce((sum: number, item: any) => sum + (item.item_total || 0), 0)
        const oneTimeTax = Math.round(oneTimeNet * (taxRate / 100) * 100) / 100
        const oneTimeGross = Math.round((oneTimeNet + oneTimeTax) * 100) / 100

        const monthlyNet = monthlyItems.reduce((sum: number, item: any) => sum + (item.item_total || 0), 0)
        const monthlyTax = Math.round(monthlyNet * (taxRate / 100) * 100) / 100
        const monthlyGross = Math.round((monthlyNet + monthlyTax) * 100) / 100

        let totalDetailsText = ''
        if (oneTimeNet > 0 && monthlyNet > 0) {
          totalDetailsText = `\nEinmaliger Betrag:\n- Netto: ${oneTimeNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n- zzgl. ${taxRate}% MwSt: ${oneTimeTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n- Brutto Gesamtbetrag: ${oneTimeGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n\nMonatlicher Betrag:\n- Netto: ${monthlyNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat\n- zzgl. ${taxRate}% MwSt: ${monthlyTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat\n- Brutto Gesamtbetrag: ${monthlyGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat`
        } else if (oneTimeNet > 0) {
          totalDetailsText = `\nGesamtsumme:\n- Netto: ${oneTimeNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n- zzgl. ${taxRate}% MwSt: ${oneTimeTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n- Brutto Gesamtbetrag: ${oneTimeGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}`
        } else {
          totalDetailsText = `\nGesamtsumme:\n- Netto: ${monthlyNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat\n- zzgl. ${taxRate}% MwSt: ${monthlyTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat\n- Brutto Gesamtbetrag: ${monthlyGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat`
        }

        let totalDetailsHtml = ''
        if (oneTimeNet > 0 && monthlyNet > 0) {
          totalDetailsHtml = `
            <tr>
              <td colspan="2" style="padding: 8px 0; border-top: 1px solid #e2e8f0; font-weight: bold; color: #0f172a;">Einmaliger Betrag:</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; color: #475569;">Netto:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${oneTimeNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; color: #475569;">zzgl. ${taxRate}% MwSt:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${oneTimeTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; font-weight: bold; color: #0f172a;">Brutto Gesamtbetrag:</td>
              <td style="padding: 4px 0; text-align: right; font-weight: bold; color: #0f172a;">${oneTimeGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td colspan="2" style="padding: 8px 0; border-top: 1px solid #e2e8f0; font-weight: bold; color: #0f172a;">Monatlicher Betrag:</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; color: #475569;">Netto:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${monthlyNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; color: #475569;">zzgl. ${taxRate}% MwSt:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${monthlyTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; font-weight: bold; color: #0f172a;">Brutto Gesamtbetrag:</td>
              <td style="padding: 4px 0; text-align: right; font-weight: bold; color: #0f172a;">${monthlyGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
          `
        } else if (oneTimeNet > 0) {
          totalDetailsHtml = `
            <tr>
              <td style="padding: 4px 0; color: #475569;">Netto-Gesamtbetrag:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${oneTimeNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; color: #475569;">zzgl. ${taxRate}% MwSt:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${oneTimeTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; font-weight: bold; color: #0f172a; border-top: 1px solid #e2e8f0; padding-top: 6px;">Gesamtbetrag (brutto):</td>
              <td style="padding: 4px 0; text-align: right; font-weight: bold; color: #0f172a; border-top: 1px solid #e2e8f0; padding-top: 6px;">${oneTimeGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
          `
        } else {
          totalDetailsHtml = `
            <tr>
              <td style="padding: 4px 0; color: #475569;">Netto-Gesamtbetrag:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${monthlyNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; color: #475569;">zzgl. ${taxRate}% MwSt:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${monthlyTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; font-weight: bold; color: #0f172a; border-top: 1px solid #e2e8f0; padding-top: 6px;">Gesamtbetrag (brutto):</td>
              <td style="padding: 4px 0; text-align: right; font-weight: bold; color: #0f172a; border-top: 1px solid #e2e8f0; padding-top: 6px;">${monthlyGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
          `
        }

        const emailTemplate = getOrderEmailTemplate({
          orderNumber,
          formattedDate,
          customerCompanyName: customerSnapshot.company_name,
          totalDetailsText,
          totalDetailsHtml
        }, `${process.env.NEXT_PUBLIC_SITE_URL || 'https://staging.hephex.de'}`, false)

        await sendMail({
          to: user.email,
          subject: `Anfragebestätigung ${orderNumber}`,
          text: emailTemplate.text,
          html: emailTemplate.html,
          attachments: [
            {
              filename: `Anfragebestaetigung_${orderNumber}.pdf`,
              content: buffer,
              contentType: 'application/pdf',
            }
          ]
        })
      } catch (mailError) {
        console.error('Failed to send order confirmation mail:', mailError)
      }
    }
  } catch (pdfError) {
    console.error('PDF Generation Error:', pdfError)
  }

  revalidatePath('/my-customers')
  revalidatePath('/my-orders')
  return order as Order
}

export async function updateOrderStatus(
  orderId: string,
  newStatus: 'pending' | 'active' | 'completed' | 'cancelled'
) {
  const admin = createAdminClient()

  // 1. Fetch current order
  const { data: order, error: fetchError } = await admin
    .from('orders')
    .select('id, user_id, order_number, status')
    .eq('id', orderId)
    .single()

  if (fetchError || !order) {
    throw new Error(`Bestellung nicht gefunden: ${fetchError?.message || 'Unbekannt'}`)
  }

  const oldStatus = order.status

  if (oldStatus === newStatus) {
    return order
  }

  // 2. Update order status
  const { data: updatedOrder, error: updateError } = await admin
    .from('orders')
    .update({ status: newStatus })
    .eq('id', orderId)
    .select()
    .single()

  if (updateError || !updatedOrder) {
    throw new Error(`Fehler beim Aktualisieren des Status: ${updateError?.message || 'Unbekannt'}`)
  }

  // 3. Send email to the retailer (händler)
  if (order.user_id) {
    try {
      const { data: { user }, error: userError } = await admin.auth.admin.getUserById(order.user_id)

      if (!userError && user && user.email) {
        const orderNumber = order.order_number || order.id.slice(0, 8)
        const statusLabelMap: Record<string, string> = {
          pending: 'Eingegangen',
          active: 'In Bearbeitung',
          completed: 'Abgeschlossen',
          cancelled: 'Storniert',
        }
        const oldLabel = statusLabelMap[oldStatus] || oldStatus
        const newLabel = statusLabelMap[newStatus] || newStatus

        const statusEmail = getStatusEmailTemplate(orderNumber, oldLabel, newLabel)

        await sendMail({
          to: user.email,
          subject: `Statusänderung Ihrer Bestellung ${orderNumber}`,
          text: statusEmail.text,
          html: statusEmail.html
        })
      }
    } catch (mailError) {
      console.error('Failed to send status update email:', mailError)
    }
  }

  revalidatePath('/admin/orders')
  revalidatePath('/my-orders')
  return updatedOrder as Order
}

export async function updateOrder(
  orderId: string,
  params: {
    selections: WizardSelections
    moduleQuantities?: Record<string, number>
    customerProfile: Partial<Profile>
    endCustomerId?: string | null
    endCustomer?: EndCustomer | null
    billingInterval?: 'one_time' | 'monthly'
    lastLicenseDate?: string | null
    companyId?: string | null
  }
): Promise<Order> {
  const { selections, moduleQuantities, customerProfile, endCustomerId, endCustomer, billingInterval, lastLicenseDate, companyId } = params
  const supabase = await createClient()
  const admin = createAdminClient()

  const { data: { user } } = await supabase.auth.getUser()
  if (!user) throw new Error('Not authenticated')

  // 1. Fetch current order & verify permissions
  const { data: existingOrder, error: fetchError } = await admin
    .from('orders')
    .select('*')
    .eq('id', orderId)
    .single()

  if (fetchError || !existingOrder) {
    throw new Error(`Bestellung nicht gefunden: ${fetchError?.message || 'Unbekannt'}`)
  }

  const { data: dbUser } = await admin
    .from('users')
    .select('role, company_id')
    .eq('id', user.id)
    .single()

  const isAdmin = dbUser?.role === 'admin'
  let isAuthorized = existingOrder.user_id === user.id

  if (!isAuthorized && dbUser?.company_id && existingOrder.user_id) {
    const { data: creatorUser } = await admin
      .from('users')
      .select('company_id')
      .eq('id', existingOrder.user_id)
      .single()

    if (creatorUser?.company_id === dbUser.company_id) {
      isAuthorized = true
    }
  }

  if (!isAdmin && !isAuthorized) {
    throw new Error('Keine Berechtigung dieses Anfrage zu bearbeiten.')
  }

  if (!isAdmin && !dbUser?.company_id) {
    throw new Error('Sie müssen einer Firma zugewiesen sein, um diese Anfrage zu bearbeiten.')
  }

  if (existingOrder.status === 'completed' && !isAdmin) {
    throw new Error('Abgeschlossene Anfragen können nicht mehr bearbeitet werden.')
  }

  // Fetch catalog directly from DB to prevent client tampering
  const dbProducts = await getProducts()
  const dbCategories = await getCategories()

  // ─── Constraint Validation ──────────────────────────────────────────────────
  validateWizardSelections(selections, dbProducts, dbCategories)

  // 1. Snapshots aufbauen
  const customerSnapshot = buildCustomerSnapshot(customerProfile, endCustomer ?? null)
  const orderSnapshot = buildOrderSnapshot(selections, dbProducts, dbCategories, billingInterval, moduleQuantities, lastLicenseDate)
  const orderHash = hashOrderSnapshot(orderSnapshot)

  // 2. Bestellung in DB aktualisieren
  const updatePayload: any = {
    end_customer_id: endCustomerId ?? null,
    total_price: orderSnapshot.total,
    customer_data: customerSnapshot,
    order_data: orderSnapshot,
    order_hash: orderHash,
  }

  if (isAdmin && companyId !== undefined) {
    updatePayload.company_id = companyId
  }

  const { data: order, error: orderError } = await admin
    .from('orders')
    .update(updatePayload)
    .eq('id', orderId)
    .select()
    .single()

  if (orderError || !order) throw orderError || new Error('Fehler beim Aktualisieren der Bestellung.')

  // 3. PDF generieren und überschreiben
  try {
    const buffer = await renderToBuffer(
      React.createElement(InvoicePDF, {
        order,
        customer: customerSnapshot,
        orderSnapshot,
      })
    )

    // PDF hochladen (upsert: true, um das alte zu ersetzen)
    const fileName = `ab_${order.id}.pdf`
    const { error: uploadError } = await supabase
      .storage
      .from('invoices')
      .upload(fileName, buffer, { contentType: 'application/pdf', upsert: true })

    if (uploadError) {
      console.error('PDF Upload Error:', uploadError)
    } else {
      await admin.from('orders').update({ pdf_url: fileName }).eq('id', order.id)
      order.pdf_url = fileName
    }

    // 4. E-Mail mit PDF-Anhang an den Besteller senden (Info über Änderung)
    const orderUserEmail = user.email || (await admin.auth.admin.getUserById(existingOrder.user_id).then(res => res.data.user?.email))
    if (orderUserEmail) {
      try {
        const formattedDate = new Date(order.created_at).toLocaleDateString('de-DE')

        const taxRate = orderSnapshot.tax_rate ?? 19
        const items = orderSnapshot.items ?? []
        const oneTimeItems = items.filter((item: any) => item.billing_interval === 'one_time')
        const monthlyItems = items.filter((item: any) => item.billing_interval === 'monthly')

        const oneTimeNet = oneTimeItems.reduce((sum: number, item: any) => sum + (item.item_total || 0), 0)
        const oneTimeTax = Math.round(oneTimeNet * (taxRate / 100) * 100) / 100
        const oneTimeGross = Math.round((oneTimeNet + oneTimeTax) * 100) / 100

        const monthlyNet = monthlyItems.reduce((sum: number, item: any) => sum + (item.item_total || 0), 0)
        const monthlyTax = Math.round(monthlyNet * (taxRate / 100) * 100) / 100
        const monthlyGross = Math.round((monthlyNet + monthlyTax) * 100) / 100

        let totalDetailsText = ''
        if (oneTimeNet > 0 && monthlyNet > 0) {
          totalDetailsText = `\nEinmaliger Betrag:\n- Netto: ${oneTimeNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n- zzgl. ${taxRate}% MwSt: ${oneTimeTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n- Brutto Gesamtbetrag: ${oneTimeGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n\nMonatlicher Betrag:\n- Netto: ${monthlyNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat\n- zzgl. ${taxRate}% MwSt: ${monthlyTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat\n- Brutto Gesamtbetrag: ${monthlyGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat`
        } else if (oneTimeNet > 0) {
          totalDetailsText = `\nGesamtsumme:\n- Netto: ${oneTimeNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n- zzgl. ${taxRate}% MwSt: ${oneTimeTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}\n- Brutto Gesamtbetrag: ${oneTimeGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}`
        } else {
          totalDetailsText = `\nGesamtsumme:\n- Netto: ${monthlyNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat\n- zzgl. ${taxRate}% MwSt: ${monthlyTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat\n- Brutto Gesamtbetrag: ${monthlyGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat`
        }

        let totalDetailsHtml = ''
        if (oneTimeNet > 0 && monthlyNet > 0) {
          totalDetailsHtml = `
            <tr>
              <td colspan="2" style="padding: 8px 0; border-top: 1px solid #e2e8f0; font-weight: bold; color: #0f172a;">Einmaliger Betrag:</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; color: #475569;">Netto:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${oneTimeNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; color: #475569;">zzgl. ${taxRate}% MwSt:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${oneTimeTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; font-weight: bold; color: #0f172a;">Brutto Gesamtbetrag:</td>
              <td style="padding: 4px 0; text-align: right; font-weight: bold; color: #0f172a;">${oneTimeGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td colspan="2" style="padding: 8px 0; border-top: 1px solid #e2e8f0; font-weight: bold; color: #0f172a;">Monatlicher Betrag:</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; color: #475569;">Netto:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${monthlyNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; color: #475569;">zzgl. ${taxRate}% MwSt:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${monthlyTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; padding-left: 10px; font-weight: bold; color: #0f172a;">Brutto Gesamtbetrag:</td>
              <td style="padding: 4px 0; text-align: right; font-weight: bold; color: #0f172a;">${monthlyGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
          `
        } else if (oneTimeNet > 0) {
          totalDetailsHtml = `
            <tr>
              <td style="padding: 4px 0; color: #475569;">Netto-Gesamtbetrag:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${oneTimeNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; color: #475569;">zzgl. ${taxRate}% MwSt:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${oneTimeTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; font-weight: bold; color: #0f172a; border-top: 1px solid #e2e8f0; padding-top: 6px;">Gesamtbetrag (brutto):</td>
              <td style="padding: 4px 0; text-align: right; font-weight: bold; color: #0f172a; border-top: 1px solid #e2e8f0; padding-top: 6px;">${oneTimeGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })}</td>
            </tr>
          `
        } else {
          totalDetailsHtml = `
            <tr>
              <td style="padding: 4px 0; color: #475569;">Netto-Gesamtbetrag:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${monthlyNet.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; color: #475569;">zzgl. ${taxRate}% MwSt:</td>
              <td style="padding: 4px 0; text-align: right; color: #475569;">${monthlyTax.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
            <tr>
              <td style="padding: 4px 0; font-weight: bold; color: #0f172a; border-top: 1px solid #e2e8f0; padding-top: 6px;">Gesamtbetrag (brutto):</td>
              <td style="padding: 4px 0; text-align: right; font-weight: bold; color: #0f172a; border-top: 1px solid #e2e8f0; padding-top: 6px;">${monthlyGross.toLocaleString('de-DE', { style: 'currency', currency: 'EUR' })} / Monat</td>
            </tr>
          `
        }

        const emailTemplate = getOrderEmailTemplate({
          orderNumber: order.order_number,
          formattedDate,
          customerCompanyName: customerSnapshot.company_name,
          totalDetailsText,
          totalDetailsHtml
        }, `${process.env.NEXT_PUBLIC_SITE_URL || 'https://staging.hephex.de'}`, true)

        await sendMail({
          to: orderUserEmail,
          subject: `Anfrageänderung ${order.order_number}`,
          text: emailTemplate.text,
          html: emailTemplate.html,
          attachments: [
            {
              filename: `Anfragebestaetigung_${order.order_number}.pdf`,
              content: buffer,
              contentType: 'application/pdf',
            }
          ]
        })
      } catch (mailError) {
        console.error('Failed to send order update confirmation mail:', mailError)
      }
    }
  } catch (pdfError) {
    console.error('PDF Re-generation Error:', pdfError)
  }

  revalidatePath('/my-customers')
  revalidatePath('/my-orders')
  revalidatePath('/admin/orders')
  return order as Order
}
