import type { NextApiRequest, NextApiResponse } from 'next';
import { createConnection } from 'mysql2/promise';
import { getServerSession } from 'next-auth';
import { authOptions } from './auth/[...nextauth]';
import { sendEmail } from '@/lib/mail';
import { paymentInitializationTemplate, orderRecapEmailTemplate } from '@/lib/emailTemplates';
import { buildOrderTrackingUrl, generateOrderTrackingToken } from '@/lib/orderTracking';
import { notifyNewOrder } from '@/lib/telegram';
import { getShippingSettings } from '@/lib/shippingSettings';
import {
  assertCurrentCheckoutStock,
  CheckoutInventoryError,
  ensureStockReservationSchema,
  loadAuthoritativeCheckoutItems,
  refreshTrackedSupplierVariants,
  reserveOrderStock,
  settleOrderReservations,
} from '@/lib/checkoutInventory';
import {
  CheckoutFailureStage,
  ensureCheckoutFailureLogSchema,
  recordCheckoutFailure,
} from '@/lib/checkoutFailureLog';

interface OrderItem {
  productId: number;
  variantId: number;
  name: string;
  brand: string;
  volume: string;
  quantity: number;
  price: number;
  voucherId?: number | null;
}

interface PickupPoint {
  id: string | number;
  name: string;
  city: string;
  street: string;
  zip: string;
  pickupPointType?: string;
}

interface OrderData {
  items: OrderItem[];
  shipping: {
    id?: string;
    method: string;
    price: number;
    type?: 'home_delivery' | 'pickup_point' | 'zbox';
    carrier?: string;
    pickupPoint?: PickupPoint;
  };
  contact: {
    firstName: string;
    lastName: string;
    email: string;
    phone: string;
    street: string;
    city: string;
    zip: string;
  };
  totalPrice: number;
  orderNote?: string;
  voucherCode?: string | null;
  voucherDiscount?: number;
  discountType?: 'fixed' | 'percent';
  isGiftWrapped?: boolean;
}

async function createDatabaseConnection() {
  return await createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  });
}

async function generateUniqueOrderNumber(connection: any): Promise<string> {
  let retries = 0;
  const maxRetries = 10;

  while (retries < maxRetries) {
    const orderNumber = Math.floor(Math.random() * 100000000).toString().padStart(8, '0');

    try {
      const [existing] = await connection.execute(
        'SELECT id FROM orders WHERE order_number = ?',
        [orderNumber]
      );

      if ((existing as any[]).length === 0) {
        return orderNumber;
      }
    } catch (error) {
    }

    retries++;
  }

  throw new Error('Nepodařilo se vygenerovat unikátní order_number po 10 pokusech');
}

async function releasePreparedOrder(connection: any, orderId: number) {
  try {
    await settleOrderReservations(connection, orderId, 'RELEASED');
    await connection.execute(
      'UPDATE orders SET status_id = 4, updated_at = NOW() WHERE id = ? AND status_id = 1',
      [orderId],
    );
  } catch (error) {
    console.error('[CHECKOUT] Rezervaci neúspěšné objednávky se nepodařilo uvolnit', { orderId, error });
  }
}

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse<any>
) {
  if (req.method === 'GET') {
    const { orderRef } = req.query;

    if (!orderRef || typeof orderRef !== 'string') {
      return res.status(400).json({ error: 'Chybí referenční číslo objednávky' });
    }

    // Tento endpoint vrací data účtu, proto zůstává dostupný jen vlastníkovi.
    // Veřejné sledování objednávky používá /api/order-tracking a tajný token.
    const session = await getServerSession(req, res, authOptions);

    if (!session?.user?.email) {
      return res.status(401).json({ error: 'Unauthorized' });
    }

    let connection: any = null;
    try {
      connection = await createDatabaseConnection();

      const [userRows] = await connection.execute(
        'SELECT id FROM users WHERE email = ?',
        [session.user.email]
      );

      if ((userRows as any[]).length === 0) {
        return res.status(404).json({ error: 'Uživatel nenalezen' });
      }

      const userId = (userRows as any[])[0].id;

      const [orderRows] = await connection.execute(
        `SELECT o.id, o.order_number, o.total_price, o.discount_amount, o.is_gift_wrapped, s.name as status_name
         FROM orders o
         JOIN order_statuses s ON o.status_id = s.id
         WHERE o.order_number = ? AND o.user_id = ?`,
        [orderRef, userId]
      );

      if ((orderRows as any[]).length === 0) {
        return res.status(404).json({ error: 'Objednávka nebyla nalezena' });
      }

      const order = (orderRows as any[])[0];

      return res.status(200).json({
        success: true,
        order: {
          id: order.id,
          orderNumber: order.order_number,
          totalPrice: order.total_price,
          status: order.status_name
        }
      });
    } catch (error) {
      return res.status(500).json({ error: 'Chyba serveru při hledání objednávky' });
    } finally {
      if (connection) await connection.end();
    }
  }

  if (req.method !== 'POST') {
    return res.status(405).json({ error: 'Metoda není povolena' });
  }

  const session = await getServerSession(req, res, authOptions);

  let connection: any = null;
  let userId: number | null = null;

  try {
    // Pokud je uživatel přihlášen, vyhledáme jeho ID z databáze podle emailu
    if (session?.user?.email) {
      connection = await createDatabaseConnection();
      const [userRows] = await connection.execute(
        'SELECT id FROM users WHERE email = ?',
        [session.user.email]
      );
      if ((userRows as any[]).length > 0) {
        userId = (userRows as any[])[0].id;
      }
      connection.end();
    }

    const orderData: OrderData = req.body;

    if (
      !orderData.items ||
      !Array.isArray(orderData.items) ||
      orderData.items.length === 0
    ) {
      return res.status(400).json({ error: 'Objednávka musí obsahovat produkty' });
    }

    if (!orderData.contact || !orderData.shipping) {
      return res.status(400).json({ error: 'Chybí kontaktní údaje nebo doprava' });
    }

    const requiredContactFields = ['firstName', 'lastName', 'email', 'phone', 'street', 'city', 'zip'];
    const missingFields = requiredContactFields.filter(field => !orderData.contact[field as keyof typeof orderData.contact]);
    if (missingFields.length > 0) {
      return res.status(400).json({ error: `Chybí povinná pole: ${missingFields.join(', ')}` });
    }

    let actualVoucherDiscount = 0;
    let voucherId: number | null = null;
    let productTypes: string[] = [];

    connection = await createDatabaseConnection();
    const initiallyAvailableItems = await loadAuthoritativeCheckoutItems(connection, orderData.items);
    await assertCurrentCheckoutStock(connection, initiallyAvailableItems);
    await refreshTrackedSupplierVariants(
      connection,
      orderData.items.map((item) => Number(item.variantId)),
    );
    const authoritativeItems = await loadAuthoritativeCheckoutItems(connection, orderData.items);
    const changedPrices = authoritativeItems.filter((item, index) =>
      Math.abs(item.price - Number(orderData.items[index].price)) > 0.009,
    );
    if (changedPrices.length > 0) {
      await connection.end();
      return res.status(409).json({
        error: 'Cena některého produktu se mezitím změnila. Košík jsme aktualizovali; zkontrolujte jej a objednávku potvrďte znovu.',
        code: 'PRICE_CHANGED',
        items: changedPrices.map((item) => ({ variantId: item.variantId, price: item.price })),
      });
    }
    orderData.items = authoritativeItems;
    productTypes = [...new Set(authoritativeItems.map((item) => item.productType))];

    const hasOnlyVouchers = productTypes.length === 1 && productTypes[0] === 'voucher';

    if (!hasOnlyVouchers) {
      const shippingSettings = await getShippingSettings();
      const shippingMethod = shippingSettings.methods.find(
        (method) => method.id === orderData.shipping.id && method.enabled,
      );
      if (!shippingMethod) {
        await connection.end();
        return res.status(400).json({ error: 'Vybraný způsob dopravy již není dostupný. Obnovte košík a vyberte dopravu znovu.' });
      }
      if (shippingMethod.requiresLocation && !orderData.shipping.pickupPoint?.id) {
        await connection.end();
        return res.status(400).json({ error: 'Vyberte výdejní místo dopravce.' });
      }

      const giftWrapPrice = orderData.isGiftWrapped ? 99 : 0;
      const merchandiseTotal = authoritativeItems.reduce(
        (total, item) => total + item.price * item.quantity,
        0,
      );
      const configuredShippingPrice = merchandiseTotal >= 999 ? 0 : shippingMethod.price;

      orderData.shipping.method = shippingMethod.name;
      orderData.shipping.price = configuredShippingPrice;
      orderData.shipping.type = shippingMethod.type;
      orderData.shipping.carrier = shippingMethod.carrier;
      orderData.totalPrice = merchandiseTotal + configuredShippingPrice + giftWrapPrice;
    } else {
      orderData.shipping.method = 'Elektronické doručení';
      orderData.shipping.price = 0;
      orderData.totalPrice = authoritativeItems.reduce(
        (total, item) => total + item.price * item.quantity,
        0,
      ) + (orderData.isGiftWrapped ? 99 : 0);
    }

    // Pokud má jen voucher produkty, neumožnit aplikaci voucheru
    if (hasOnlyVouchers && orderData.voucherCode) {
      connection.end();
      return res.status(400).json({
        error: 'Nelze aplikovat poukaz při nákupu samotných poukazů. Pokud si přejete koupit poukaz, prosím nepoužívejte kód sleva.'
      });
    }

    if (orderData.voucherCode) {
      const upperCode = orderData.voucherCode.toUpperCase().trim();

      // First try to find in vouchers table
      const [voucherResults] = await connection.execute(
        `SELECT id, value, is_active, is_used, expires_at FROM vouchers WHERE code = ?`,
        [upperCode]
      );

      const vouchers = voucherResults as any[];

      if (vouchers.length > 0) {
        // It's a voucher (traditional gift voucher)
        const voucher = vouchers[0];
        voucherId = voucher.id;

        if (voucher.is_used) {
          connection.end();
          return res.status(400).json({ error: 'Tento poukaz byl již použit' });
        }

        if (!voucher.is_active) {
          connection.end();
          return res.status(400).json({ error: 'Tento poukaz není aktivní' });
        }

        const expiresAt = new Date(voucher.expires_at);
        if (expiresAt < new Date()) {
          connection.end();
          return res.status(400).json({ error: 'Tento poukaz vypršel' });
        }

        // Výpočet skutečné slevy: min(voucher_value, order_total)
        // DŮLEŽITÉ: Pro perfume produkty nesleva na dopravu - slevu počítáme pouze na produkt
        let discountBase = orderData.totalPrice;
        if (productTypes.includes('perfume') && !hasOnlyVouchers) {
          // Pokud máme perfume (a nejsme jen vouchers), slevu počítáme bez dopravy
          discountBase = orderData.totalPrice - orderData.shipping.price;
        }

        if (orderData.isGiftWrapped) {
          discountBase -= 99;
        }

        const voucherValue = typeof voucher.value === 'string' ? parseFloat(voucher.value) : voucher.value;
        actualVoucherDiscount = Math.min(voucherValue, Math.max(0, discountBase));

        await connection.execute(
          `UPDATE vouchers SET is_used = 1, is_active = 0, updated_at = NOW() WHERE id = ?`,
          [voucherId]
        );
      } else {
        // Try to find in coupons table
        const [couponResults] = await connection.execute(
          `SELECT id, discount_value, discount_type, is_active, used_count, usage_limit, expires_at FROM coupons WHERE code = ?`,
          [upperCode]
        );

        const coupons = couponResults as any[];

        if (coupons.length === 0) {
          connection.end();
          return res.status(400).json({ error: 'Neplatný kód poukazu nebo kuponu' });
        }

        const coupon = coupons[0];

        if (!coupon.is_active) {
          connection.end();
          return res.status(400).json({ error: 'Tento kupón není aktivní' });
        }

        if (coupon.used_count >= coupon.usage_limit) {
          connection.end();
          return res.status(400).json({ error: 'Tento kupón byl již použit' });
        }

        const expiresAt = new Date(coupon.expires_at);
        if (expiresAt < new Date()) {
          connection.end();
          return res.status(400).json({ error: 'Tento kupón vypršel' });
        }

        // Výpočet skutečné slevy pro kupón
        let discountBase = orderData.totalPrice;
        if (productTypes.includes('perfume') && !hasOnlyVouchers) {
          // Pro perfume, slevu počítáme bez dopravy
          discountBase = orderData.totalPrice - orderData.shipping.price;
        }

        if (orderData.isGiftWrapped) {
          discountBase -= 99;
        }

        const discountValue = typeof coupon.discount_value === 'string'
          ? parseFloat(coupon.discount_value)
          : coupon.discount_value;

        if (coupon.discount_type === 'percent') {
          // Procentní sleva
          actualVoucherDiscount = (Math.max(0, discountBase) * discountValue) / 100;
        } else {
          // Pevná sleva v Kč
          actualVoucherDiscount = Math.min(discountValue, Math.max(0, discountBase));
        }

        // Inkrementujeme used_count
        await connection.execute(
          `UPDATE coupons SET used_count = used_count + 1 WHERE id = ?`,
          [coupon.id]
        );
      }

      connection.end();
    } else if (connection) {
      connection.end();
    }

    const {
      COMGATE_MERCHANT,
      COMGATE_SECRET,
      COMGATE_TEST,
      COMGATE_CURRENCY,
      COMGATE_METHOD,
    } = process.env;

    if (!COMGATE_MERCHANT || !COMGATE_SECRET) {
      return res.status(500).json({ error: 'Chybí konfigurace COMGATE' });
    }

    connection = await createDatabaseConnection();
    await ensureStockReservationSchema(connection);
    await ensureCheckoutFailureLogSchema(connection);
    let orderId = 0;
    let checkoutFailureStage: CheckoutFailureStage = 'create_order';

    const productStockMap = new Map<number, { totalRequired: number; available: number }>();
    const bottleStockMap = new Map<number, { totalRequired: number; available: number; productName: string }>();

    for (const item of orderData.items) {
      if (!item.variantId || !item.productId) continue;

      const [variantRows] = await connection.execute(
        `SELECT pv.volume_ml, pv.kind, pv.stock AS variant_stock, pv.is_active,
                ps.stock AS sample_stock, p.type as product_type
         FROM product_variants pv
         LEFT JOIN product_stock ps ON ps.product_id = ?
         LEFT JOIN products p ON p.id = pv.product_id
         WHERE pv.id = ?`,
        [item.productId, item.variantId]
      );

      if ((variantRows as any[]).length === 0) {
        connection.end();
        return res.status(404).json({
          error: `Varianta produktu ${item.name} nenalezena`
        });
      }

      const variant = (variantRows as any[])[0];

      if (variant.product_type === 'voucher' || variant.product_type === 'bundle') continue;
      if (!variant.is_active) {
        connection.end();
        return res.status(409).json({ error: `Varianta produktu "${item.name}" už není aktivní.` });
      }

      if ((variant.kind || 'SAMPLE') !== 'SAMPLE') {
        const current = bottleStockMap.get(item.variantId) || {
          totalRequired: 0,
          available: Number(variant.variant_stock) || 0,
          productName: item.name,
        };
        current.totalRequired += item.quantity || 1;
        bottleStockMap.set(item.variantId, current);
        continue;
      }

      const volumeMl = variant.volume_ml;
      const requiredForItem = volumeMl * (item.quantity || 1);
      const availableStock = variant.sample_stock || 0;

      if (!productStockMap.has(item.productId)) {
        productStockMap.set(item.productId, {
          totalRequired: 0,
          available: availableStock
        });
      }

      const productStock = productStockMap.get(item.productId)!;
      productStock.totalRequired += requiredForItem;
    }

    for (const [productId, stockInfo] of productStockMap) {
      if (stockInfo.totalRequired > stockInfo.available) {
        const productItem = orderData.items.find(i => i.productId === productId);
        connection.end();
        return res.status(409).json({
          error: `Produkt "${productItem?.name}" nemá dostatek ml na skladě.`
        });
      }
    }

    for (const stockInfo of bottleStockMap.values()) {
      if (stockInfo.totalRequired > stockInfo.available) {
        connection.end();
        return res.status(409).json({
          error: `Produkt "${stockInfo.productName}" nemá dostatek celých flakonů skladem.`
        });
      }
    }

    // Vygenerujeme unikátní order_number (8 číslic)
    const orderRef = await generateUniqueOrderNumber(connection);
    const trackingToken = generateOrderTrackingToken();

    // Vypočítáme skutečnou cenu po slevě
    const finalTotalPrice = orderData.totalPrice - actualVoucherDiscount;

    // Konvertujeme cenu na haléře (pro CZK - základní jednotka)
    const priceInHaleres = Math.round(finalTotalPrice * 100);

    await connection.beginTransaction();
    try {
      // Vložíme objednávku do tabulky orders
      // Status ID 1 = pending/přijatá
      const [orderResult] = await connection.execute(
        `INSERT INTO orders (order_number, tracking_token, status_id, total_price, currency, user_id, note, voucher_code, discount_amount, is_gift_wrapped, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
        [orderRef, trackingToken, 1, finalTotalPrice, 'Kč', userId, orderData.orderNote || null, orderData.voucherCode || null, actualVoucherDiscount, orderData.isGiftWrapped ? 1 : 0]
      );

      orderId = (orderResult as any).insertId;
      checkoutFailureStage = 'create_order_items';
      for (const item of orderData.items) {
        await connection.execute(
          `INSERT INTO order_items (order_id, product_id, variant_id, brand, product_name, quantity, unit_price, total_price, voucher_id)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            orderId,
            item.productId || null,
            item.variantId || null,
            item.brand || null,
            item.name || null,
            item.quantity || 0,
            item.price || 0,
            (item.price || 0) * (item.quantity || 0),
            item.voucherId || null,
          ]
        );
      }

      let methodData = null;
      if (orderData.shipping.pickupPoint) {
        methodData = JSON.stringify({
          type: orderData.shipping.type,
          carrier: orderData.shipping.carrier || 'packeta',
          pickupPoint: {
            id: orderData.shipping.pickupPoint.id,
            name: orderData.shipping.pickupPoint.name,
            city: orderData.shipping.pickupPoint.city,
            street: orderData.shipping.pickupPoint.street,
            zip: orderData.shipping.pickupPoint.zip,
            pickupPointType: orderData.shipping.pickupPoint.pickupPointType,
          },
        });
      } else if (orderData.shipping.type === 'home_delivery') {
        // Store home delivery shipping info with delivery address (PPL na adresu, etc.)
        methodData = JSON.stringify({
          type: orderData.shipping.type,
          carrier: orderData.shipping.carrier || 'ppl',
          deliveryAddress: {
            street: orderData.contact.street,
            city: orderData.contact.city,
            zip: orderData.contact.zip,
          },
        });
      }

      checkoutFailureStage = 'create_shipping';
      await connection.execute(
        `INSERT INTO order_shipping (order_id, method_name, price, method_data)
         VALUES (?, ?, ?, ?)`,
        [orderId, orderData.shipping.method || null, orderData.shipping.price || 0, methodData]
      );

      checkoutFailureStage = 'create_address';
      await connection.execute(
        `INSERT INTO order_addresses (order_id, address_type, full_name, street, city, zip, country_code, phone, email)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          orderId,
          'billing', // typ adresy - billing
          `${orderData.contact.firstName} ${orderData.contact.lastName}`,
          orderData.contact.street || '',
          orderData.contact.city || '',
          orderData.contact.zip || '',
          'CZ',
          orderData.contact.phone || null,
          orderData.contact.email || null,
        ]
      );
      checkoutFailureStage = 'reserve_stock';
      await reserveOrderStock(connection, orderId, authoritativeItems);
      checkoutFailureStage = 'commit';
      await connection.commit();
    } catch (dbError) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error('[CHECKOUT] Rollback transakce selhal', { orderId: orderId || null, rollbackError });
      }

      const failureEntry = {
        attemptedOrderId: orderId || null,
        orderNumber: orderRef,
        stage: checkoutFailureStage,
        error: dbError,
        requestPath: req.url || null,
        userAgent: req.headers['user-agent'] || null,
      };

      try {
        await recordCheckoutFailure(connection, failureEntry);
      } catch (logError) {
        console.error('[CHECKOUT] Zápis rollbacku do stejného spojení selhal', { logError });
        let logConnection: any = null;
        try {
          logConnection = await createDatabaseConnection();
          await recordCheckoutFailure(logConnection, failureEntry);
        } catch (fallbackLogError) {
          console.error('[CHECKOUT] Zápis rollbacku do databáze selhal', { fallbackLogError });
        } finally {
          if (logConnection) await logConnection.end();
        }
      }

      console.error('[CHECKOUT] Transakce byla vrácena zpět', {
        orderId: orderId || null,
        orderNumber: orderRef,
        stage: checkoutFailureStage,
        error: dbError,
      });
      await connection.end();
      if (dbError instanceof CheckoutInventoryError) {
        return res.status(dbError.status).json({
          error: dbError.message,
          code: dbError.code,
          details: dbError.details,
        });
      }
      return res.status(500).json({
        error: 'Chyba při uložení objednávky do databáze',
      });
    }

    const params = new URLSearchParams();
    params.append('merchant', COMGATE_MERCHANT);
    params.append('refId', orderRef);
    params.append('price', priceInHaleres.toString());
    params.append('curr', COMGATE_CURRENCY || 'CZK');
    params.append('label', `Objednávka ${orderRef}`);
    params.append('method', (COMGATE_METHOD || 'ALL').toUpperCase());
    params.append('country', 'CZ');
    params.append('email', orderData.contact.email);

    const baseUrl = req.headers.origin || `${req.headers['x-forwarded-proto'] || 'http'}://${req.headers.host}`;
    const trackingUrl = buildOrderTrackingUrl(baseUrl, orderRef, trackingToken);

    params.append('url_paid', trackingUrl);
    params.append('url_cancelled', trackingUrl);
    params.append('url_pending', trackingUrl);

    params.append('prepareOnly', 'true');

    params.append('test', COMGATE_TEST === 'true' ? '1' : '0');

    params.append('secret', COMGATE_SECRET);

    const comgateResponse = await fetch('https://payments.comgate.cz/v1.0/create', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/x-www-form-urlencoded',
      },
      body: params.toString(),
    });

    if (!comgateResponse.ok) {
      const errorText = await comgateResponse.text();
      await releasePreparedOrder(connection, orderId);
      return res.status(500).json({
        error: `COMGATE API vrátil ${comgateResponse.status}: ${errorText}`,
      });
    }

    const comgateData = await comgateResponse.text();

    const comgateParams = new URLSearchParams(comgateData);
    const code = comgateParams.get('code');
    const message = comgateParams.get('message');
    const transId = comgateParams.get('transId');
    const redirectUrl = comgateParams.get('redirect');

    // Logování pro debug
    console.log('[COMGATE] Response data:', comgateData);
    console.log('[COMGATE] Parsed - code:', code, 'transId:', transId, 'redirectUrl:', redirectUrl);

    if (code !== '0') {
      await releasePreparedOrder(connection, orderId);
      return res.status(400).json({
        error: `COMGATE chyba (kód ${code}): ${message || 'Neznámá chyba'}`,
      });
    }

    if (!transId) {
      await releasePreparedOrder(connection, orderId);
      return res.status(500).json({
        error: 'COMGATE nevrátil transaction ID - webhook nebude moci objednávku aktualizovat',
      });
    }

    if (!redirectUrl) {
      await releasePreparedOrder(connection, orderId);
      return res.status(500).json({
        error: 'COMGATE nevrátil redirect URL',
      });
    }

    // Uložíme payment informaci do payments tabulky (POVINNÉ)
    const comgateResponseJson = JSON.stringify({
      code,
      message,
      transId,
      redirect: redirectUrl,
      createdAt: new Date().toISOString(),
    });

    try {
      await connection.execute(
        `INSERT INTO payments (order_id, trans_id, amount, currency, status, comgate_response)
         VALUES (?, ?, ?, ?, ?, ?)`,
        [orderId, transId, finalTotalPrice || 0, 'Kč', 'PENDING', comgateResponseJson]
      );
    } catch (paymentError) {
      await releasePreparedOrder(connection, orderId);
      // Uzavřeme DB connection
      try {
        await connection.end();
      } catch (e) {
      }

      return res.status(500).json({
        error: 'Chyba při vytvoření platby - nepodařilo se uložit záznam do systému',
      });
    }

    const decodedRedirectUrl = decodeURIComponent(redirectUrl);

    try {
      await connection.end();
    } catch (e) {
    }

    void notifyNewOrder({
      orderId,
      orderNumber: orderRef,
      customerName: `${orderData.contact.firstName} ${orderData.contact.lastName}`.trim(),
      email: orderData.contact.email,
      phone: orderData.contact.phone,
      transId,
      total: finalTotalPrice,
      shipping: orderData.shipping.method,
      note: orderData.orderNote,
      items: orderData.items.map((item) => ({
        name: [item.brand, item.name, item.volume].filter(Boolean).join(' · '),
        quantity: item.quantity,
        price: item.price,
      })),
    })

    try {
      const paymentInitEmail = paymentInitializationTemplate({
        customerEmail: orderData.contact.email,
        firstName: orderData.contact.firstName,
        lastName: orderData.contact.lastName,
        orderNumber: orderRef,
        amount: finalTotalPrice,
        currency: 'Kč',
        paymentUrl: decodedRedirectUrl,
        trackingUrl,
      });

      await sendEmail(paymentInitEmail);
    } catch (emailError) {
    }

    try {
      const imgConnection = await createDatabaseConnection();

      const recapItems = await Promise.all(
        orderData.items.map(async (item) => {
          let image: string | undefined = undefined;

          let productType = 'perfume';
          if (item.productId) {
            try {
              const [productRows] = await imgConnection.execute(
                `SELECT type FROM products WHERE id = ?`,
                [item.productId]
              );
              if ((productRows as any[]).length > 0) {
                productType = (productRows as any[])[0].type;
              }

              const [imageRows] = await imgConnection.execute(
                `SELECT url FROM product_images WHERE product_id = ? AND is_primary = 1 LIMIT 1`,
                [item.productId]
              );

              if ((imageRows as any[]).length > 0) {
                image = (imageRows as any[])[0].url;
              }
            } catch (imgError) {
            }
          }

          return {
            name: item.brand ? `${item.brand} - ${item.name}${item.volume ? ` (${item.volume})` : ''}` : `${item.name}${item.volume ? ` (${item.volume})` : ''}`,
            quantity: item.quantity,
            unitPrice: item.price,
            lineTotal: item.price * item.quantity,
            image: image,
            productType: productType,
          };
        })
      );

      await imgConnection.end();

      if (orderData.isGiftWrapped) {
        recapItems.push({
          name: 'Dárkové balení',
          quantity: 1,
          unitPrice: 99,
          lineTotal: 99,
          image: undefined,
          productType: 'gift-wrap',
        });
      }

      let shippingAddress: { street: string; city: string; zip: string } | undefined;

      if (orderData.shipping.type === 'pickup_point' || orderData.shipping.type === 'zbox') {
        if (orderData.shipping.pickupPoint) {
          shippingAddress = {
            street: orderData.shipping.pickupPoint.street,
            city: orderData.shipping.pickupPoint.city,
            zip: orderData.shipping.pickupPoint.zip,
          };
        }
      } else {
        shippingAddress = {
          street: orderData.contact.street,
          city: orderData.contact.city,
          zip: orderData.contact.zip,
        };
      }

      const recapEmail = orderRecapEmailTemplate(orderData.contact.email, {
        orderNumber: orderRef,
        createdAt: new Date(),
        items: recapItems,
        shippingPrice: orderData.shipping.price,
        total: finalTotalPrice,
        currency: 'Kč',
        shippingMethod: orderData.shipping.method,
        billingName: `${orderData.contact.firstName} ${orderData.contact.lastName}`,
        customerEmail: orderData.contact.email,
        customerPhone: orderData.contact.phone,
        billingAddress: {
          street: orderData.contact.street,
          city: orderData.contact.city,
          zip: orderData.contact.zip,
        },
        shippingAddress: shippingAddress,
        voucherCode: orderData.voucherCode || undefined,
        discountAmount: orderData.voucherDiscount || 0,
        orderNote: orderData.orderNote || undefined,
        trackingUrl,
      });

      await sendEmail(recapEmail);
    } catch (recapEmailError) {
      // Error sending recap email
    }

    return res.status(200).json({
      success: true,
      orderId,
      orderRef,
      transId,
      redirectUrl: decodedRedirectUrl,
      trackingUrl,
      amount: finalTotalPrice,
    });
  } catch (error) {
    if (connection) {
      try {
        await connection.end();
      } catch (e) {
      }
    }
    if (error instanceof CheckoutInventoryError) {
      return res.status(error.status).json({
        error: error.message,
        code: error.code,
        details: error.details,
      });
    }
    return res.status(500).json({
      error: error instanceof Error ? error.message : 'Chyba serveru',
    });
  }
}