import type { NextApiRequest, NextApiResponse } from 'next';
import { createConnection } from 'mysql2/promise';
import { createVoucher } from '@/lib/vouchers';
import { sendEmail } from '@/lib/mail';
import {
  paymentSuccessTemplate,
  paymentCancellationTemplate,
  voucherDeliveryEmailTemplate,
  generateInvoiceHtml,
  generateVoucherHtml,
} from '@/lib/emailTemplates';
import { generatePdfFromHtml } from '@/lib/pdf';
import { buildOrderTrackingUrl } from '@/lib/orderTracking';
import { notifyPaidOrderById } from '@/lib/telegram';
import { settleOrderReservations } from '@/lib/checkoutInventory';

interface WebhookData {
  merchant: string;
  transId: string;
  refId: string;
  status: string;
  amount: string;
  curr: string;
  email?: string;
  signature?: string;
}

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,
  });
}

// Mapujeme COMGATE stavy na naše status IDs
// 1 = pending, 2 = paid, 3 = cancelled, 4 = failed
function mapComgateStatusToId(comgateStatus: string): number {
  switch (comgateStatus) {
    case 'PAID':
      return 2;
    case 'CANCELLED':
      return 3;
    case 'FAILED':
      return 4;
    default:
      return 1; // pending
  }
}

// Generuje unikátní 5-znakový kód (pouze velká písmena a čísla)
async function generateUniqueCouponCode(connection: any): Promise<string> {
  const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  let retries = 0;
  const maxRetries = 10;

  while (retries < maxRetries) {
    let code = '';
    for (let i = 0; i < 5; i++) {
      code += chars.charAt(Math.floor(Math.random() * chars.length));
    }

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

      if ((existing as any[]).length === 0) {
        return code;
      }
    } catch (error) {
      // Error checking coupon uniqueness
    }

    retries++;
  }

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

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse<any>
) {
  if (req.method !== 'POST') {
    return res.status(200).json({ message: 'OK' });
  }

  try {
    // Logování RAW dat co Comgate poslal
    console.log('[COMGATE WEBHOOK] Raw request body:', JSON.stringify(req.body, null, 2));
    console.log('[COMGATE WEBHOOK] Raw request headers:', JSON.stringify(req.headers, null, 2));

    // Parsujeme data z COMGATE
    // COMGATE posílá form-encoded data v POST body
    const data: WebhookData = {
      merchant: (req.body?.merchant as string) || '',
      transId: (req.body?.transId as string) || '',
      refId: (req.body?.refId as string) || '',
      status: (req.body?.status as string) || '',
      amount: (req.body?.amount as string) || '',
      curr: (req.body?.curr as string) || '',
      email: (req.body?.email as string),
      signature: (req.body?.signature as string),
    };

    // Logování webhook příjmu
    console.log('[COMGATE WEBHOOK] Received:', {
      transId: data.transId,
      refId: data.refId,
      status: data.status,
      amount: data.amount,
      signature: data.signature || 'MISSING',
    });

    // Validujeme povinná pole
    if (!data.merchant || !data.transId || !data.status) {
      console.error('[COMGATE WEBHOOK] Missing required fields', { merchant: !!data.merchant, transId: !!data.transId, status: !!data.status });
      return res.status(200).json({ message: 'OK' });
    }

    // Poznámka: Comgate posílá POST request bez signature v novém formátu
    // Validujeme jen basic data
    const COMGATE_MERCHANT = process.env.COMGATE_MERCHANT;

    console.log('[COMGATE WEBHOOK] Validating merchant', {
      received: data.merchant,
      expected: COMGATE_MERCHANT,
    });

    if (data.merchant !== COMGATE_MERCHANT) {
      console.error('[COMGATE WEBHOOK] Invalid merchant ID - BLOCKING', {
        received: data.merchant,
        expected: COMGATE_MERCHANT,
      });
      return res.status(200).json({ message: 'OK' });
    }

    console.log('[COMGATE WEBHOOK] Validation passed, processing...');

    // Připojíme se k databázi a aktualizujeme objednávku
    const connection = await createDatabaseConnection();

    try {
      // Zjistíme ID objednávky podle order_number (refId)
      const [orders] = await connection.execute(
        `SELECT o.id, o.total_price, o.discount_amount, o.voucher_code, o.created_at, o.is_gift_wrapped, o.tracking_token, oa.full_name, oa.email, oa.phone, oa.street, oa.city, oa.zip,
                os.method_name, os.price as shipping_price, os.method_data as shipping_method_data
         FROM orders o
         LEFT JOIN order_addresses oa ON o.id = oa.order_id AND oa.address_type = 'billing'
         LEFT JOIN order_shipping os ON o.id = os.order_id
         WHERE o.order_number = ?`,
        [data.refId]
      );

      const orderList = orders as any[];
      if (orderList.length === 0) {
        console.error('[COMGATE WEBHOOK] Order not found', { refId: data.refId });
        return res.status(200).json({ message: 'OK' });
      }

      console.log('[COMGATE WEBHOOK] Order found', { refId: data.refId, orderId: (orderList[0] as any).id });

      const orderId = orderList[0].id;
      const [currentStatusRows] = await connection.execute(
        'SELECT status_id FROM orders WHERE id = ? LIMIT 1',
        [orderId]
      )
      const previousStatusId = Number((currentStatusRows as Array<{ status_id: number }>)[0]?.status_id || 0)
      const orderTotalPrice = parseFloat(String(orderList[0].total_price));
      const customerEmail = data.email || orderList[0].email;
      const customerName = orderList[0].full_name || '';
      const statusId = mapComgateStatusToId(data.status);
      const trackingUrl = buildOrderTrackingUrl(
        process.env.NEXT_PUBLIC_BASE_URL || 'https://vystrikejto.cz',
        data.refId,
        orderList[0].tracking_token
      );

      // Validujeme cenu - KRITICKÉ!
      // Pokud webhook posílá amount, ověříme ho
      if (data.amount && data.amount.trim() !== '') {
        const webhookAmount = parseFloat(data.amount) / 100; // COMGATE posílá haléře
        const priceDifference = Math.abs(webhookAmount - orderTotalPrice);

        console.log('[COMGATE WEBHOOK] Price validation', {
          transId: data.transId,
          webhookAmount,
          orderTotalPrice,
          difference: priceDifference,
        });

        if (priceDifference > 0.01) {
          // Cena se neshoduje - podezřelý webhook
          console.error('[COMGATE WEBHOOK] Price mismatch - BLOCKING', {
            transId: data.transId,
            refId: data.refId,
            webhookAmount,
            orderTotalPrice,
            difference: priceDifference
          });
          return res.status(200).json({ message: 'OK' });
        }
      } else {
        console.warn('[COMGATE WEBHOOK] Amount is empty, skipping price validation', {
          transId: data.transId,
          refId: data.refId,
        });
      }

      // Aktualizuj payment record
      const paymentResponse = JSON.stringify({
        merchant: data.merchant,
        transId: data.transId,
        status: data.status,
        amount: data.amount,
        curr: data.curr,
        email: data.email,
        receivedAt: new Date().toISOString(),
      });

      console.log('[COMGATE WEBHOOK] Updating/creating payment record', {
        transId: data.transId,
        status: data.status,
      });

      try {
        // INSERT or UPDATE - pokud trans_id neexistuje, vytvoří se nový záznam
        await connection.execute(
          `INSERT INTO payments (order_id, trans_id, amount, currency, status, comgate_response, created_at)
           VALUES (?, ?, ?, ?, ?, ?, NOW())
           ON DUPLICATE KEY UPDATE
           status = VALUES(status),
           comgate_response = VALUES(comgate_response),
           updated_at = NOW()`,
          [
            orderId,
            data.transId,
            data.amount ? parseInt(data.amount) / 100 : 0,
            data.curr || 'CZK',
            data.status,
            paymentResponse,
          ]
        );
        console.log('[COMGATE WEBHOOK] Payment record updated/created');
      } catch (paymentError) {
        console.error('[COMGATE WEBHOOK] Error with payment record', paymentError);
      }

      // Aktualizujeme status objednávky jen pokud je to PAID, CANCELLED, nebo FAILED
      if (statusId !== 1) {
        // Vynecháme PENDING (statusId === 1)
        console.log('[COMGATE WEBHOOK] Updating order status', {
          refId: data.refId,
          orderId,
          newStatusId: statusId,
          comgateStatus: data.status,
        });

        try {
          await connection.execute(
            'UPDATE orders SET status_id = ?, updated_at = NOW() WHERE id = ?',
            [statusId, orderId]
          );
          console.log('[COMGATE WEBHOOK] Order status updated successfully', {
            refId: data.refId,
            newStatusId: statusId,
          });
          if (statusId === 2 && previousStatusId !== 2) {
            await notifyPaidOrderById(connection, orderId)
          }
        } catch (orderUpdateError) {
          console.error('[COMGATE WEBHOOK] Error updating order status', orderUpdateError);
        }
      }

      let reservationSettlement = { found: false, changed: false };
      // Vypořádání je idempotentní. Spouštíme ho i při opakovaném PAID webhooku,
      // aby se rezervace dokončila i po dočasné databázové chybě prvního pokusu.
      if (statusId === 2) {
        reservationSettlement = await settleOrderReservations(connection, orderId, 'PAID');
      } else if (statusId === 3 || statusId === 4) {
        reservationSettlement = await settleOrderReservations(connection, orderId, 'RELEASED');
      }

      // Odešleme příslušné emaily podle statusu
      if (customerEmail) {
        try {
          const [firstName, lastName] = customerName.split(' ').length > 1
            ? customerName.split(' ').slice(0, 1).concat(customerName.split(' ').slice(1).join(' '))
            : [customerName, ''];

          if (statusId === 2) {
            // Status PAID - pošleme potvrzení platby
            try {
              const paymentAmount = parseFloat(data.amount) || parseFloat(String(orderTotalPrice)) || 0;

              const paymentSuccessEmail = paymentSuccessTemplate({
                customerEmail,
                firstName,
                lastName,
                orderNumber: data.refId,
                amount: paymentAmount,
                currency: data.curr,
                paymentDate: new Date(),
                trackingUrl,
              });

              // Generujeme fakturu a přidáváme ji jako přílohu
              try {
                // Načteme všechny položky objednávky s informacemi o variantě
                const [orderItems] = await connection.execute(
                  `SELECT oi.product_name, oi.brand, oi.quantity, oi.unit_price, pv.volume_ml
                   FROM order_items oi
                   LEFT JOIN product_variants pv ON pv.id = oi.variant_id
                   WHERE oi.order_id = ?`,
                  [orderId]
                );

                const invoiceItems = (orderItems as any[])
                  .map(item => {
                    let itemName = item.brand ? `${item.brand} - ${item.product_name}` : item.product_name;
                    // Přidáme informaci o variantě (volume)
                    if (item.volume_ml) {
                      itemName += ` (${item.volume_ml}ml)`;
                    }
                    return {
                      name: itemName,
                      quantity: item.quantity,
                      unitPrice: parseFloat(String(item.unit_price)) || 0,
                      lineTotal: (parseFloat(String(item.unit_price)) || 0) * item.quantity,
                    };
                  })
                  .filter(item => item.lineTotal > 0);

                // Přidáme dárkové balení, pokud je zvoleno
                if (orderList[0].is_gift_wrapped === 1) {
                  invoiceItems.push({
                    name: 'Dárkové balení',
                    quantity: 1,
                    unitPrice: 99,
                    lineTotal: 99,
                  });
                }

                // Určíme doručovací adresu
                let shippingAddress: { street: string; city: string; zip: string } | undefined;
                const shippingMethodData = orderList[0].shipping_method_data;

                if (shippingMethodData) {
                  try {
                    const shippingData = JSON.parse(shippingMethodData);
                    if (shippingData.pickupPoint) {
                      shippingAddress = {
                        street: shippingData.pickupPoint.street,
                        city: shippingData.pickupPoint.city,
                        zip: shippingData.pickupPoint.zip,
                      };
                    }
                  } catch (e) {
                    // Pokud parsování selže, použijeme kontaktní adresu
                  }
                }

                // Pokud nemáme pickup point adresu, použijeme kontaktní adresu
                if (!shippingAddress) {
                  shippingAddress = {
                    street: orderList[0].street || '',
                    city: orderList[0].city || '',
                    zip: orderList[0].zip || '',
                  };
                }

                // Generujeme HTML faktury
                const invoiceHtml = generateInvoiceHtml({
                  orderNumber: data.refId,
                  createdAt: new Date(orderList[0].created_at),
                  items: invoiceItems,
                  shippingPrice: parseFloat(String(orderList[0].shipping_price)) || 0,
                  total: paymentAmount,
                  currency: data.curr || 'Kč',
                  shippingMethod: orderList[0].method_name || 'Neuvedeno',
                  customerName: customerName,
                  customerEmail: customerEmail,
                  customerPhone: orderList[0].phone || undefined,
                  billingAddress: {
                    street: orderList[0].street || '',
                    city: orderList[0].city || '',
                    zip: orderList[0].zip || '',
                  },
                  shippingAddress: shippingAddress,
                  voucherCode: orderList[0].voucher_code || undefined,
                  discountAmount: parseFloat(String(orderList[0].discount_amount)) || 0,
                });

                // Generujeme PDF faktury
                const invoiceBuffer = await generatePdfFromHtml(invoiceHtml);

                // Přidáme fakturu jako přílohu
                paymentSuccessEmail.attachments = [
                  {
                    filename: `faktura_${data.refId}.pdf`,
                    content: invoiceBuffer,
                    contentType: 'application/pdf',
                  },
                ];
              } catch (invoiceError) {
                // Error generating invoice
              }

              await sendEmail(paymentSuccessEmail);
            } catch (successEmailError) {
              // Error sending payment success email
            }

          } else if (statusId === 3 || statusId === 4) {
            // Status CANCELLED nebo FAILED - pošleme email zrušení
            try {
              const cancellationAmount = parseFloat(data.amount) || parseFloat(String(orderTotalPrice)) || 0;

              const cancellationEmail = paymentCancellationTemplate({
                customerEmail,
                firstName,
                lastName,
                orderNumber: data.refId,
                amount: cancellationAmount,
                currency: data.curr,
                trackingUrl,
              });

              await sendEmail(cancellationEmail);
            } catch (cancellationEmailError) {
              // Error sending cancellation email
            }
          }
        } catch (emailError) {
          // General error sending emails
        }
      }

      // Pokud je objednávka zaplacena, vytvoříme automaticky kupon
      if (statusId === 2 && previousStatusId !== 2) {
        // status_id 2 = PAID
        try {
          // Generujeme personální kupon s 5% slevou
          const couponCode = await generateUniqueCouponCode(connection);

          // Zjistíme user_id z objednávky
          const [orderUserData] = await connection.execute(
            'SELECT user_id FROM orders WHERE id = ?',
            [orderId]
          );

          const userId = (orderUserData as any[])[0]?.user_id || null;

          // Vytvoříme kupon
          const expiryDate = new Date();
          expiryDate.setDate(expiryDate.getDate() + 60); // Kupon platný 60 dní

          await connection.execute(
            `INSERT INTO coupons (code, type, discount_type, discount_value, user_id, order_id, is_active, usage_limit, used_count, expires_at, created_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())`,
            [couponCode, 'personal', 'percent', 5, userId, orderId, 1, 1, 0, expiryDate]
          );
        } catch (couponError) {
          // Error creating coupon
        }

        try {
          // Zjistíme všechny voucher položky v objednávce (jen ty s type = 'voucher')
          const [orderItems] = await connection.execute(
            `SELECT oi.id, oi.variant_id, oi.product_id, oi.unit_price, oi.quantity, oi.voucher_id, oi.brand, oi.product_name, p.type
             FROM order_items oi
             JOIN products p ON oi.product_id = p.id
             WHERE oi.order_id = ?`,
            [orderId]
          );

          const voucherItems = (orderItems as any[]).filter(item => {
            // Filtrujeme jen produkty s type = 'voucher'
            return item.type === 'voucher' && !item.voucher_id;
          });

          // Pro každou položku která nemá voucher_id, vytvoříme tolik voucher kolik je v quantity
          for (const item of voucherItems) {
            if (!item.voucher_id && item.variant_id && item.product_id) {
              const quantity = item.quantity || 1;

              try {
                // Vytvoříme voucher(y) podle quantity
                for (let q = 0; q < quantity; q++) {
                  const voucherResult = await createVoucher({
                    productVariantId: item.variant_id,
                    value: item.unit_price,
                    note: `Vytvořen na základě objednávky ${data.refId}`,
                  });

                  if (q === 0) {
                    // Pro první voucher, aktualizujeme původní order_item
                    await connection.execute(
                      `UPDATE order_items SET voucher_id = ?, quantity = 1 WHERE id = ?`,
                      [voucherResult.voucherId, item.id]
                    );
                  } else {
                    // Pro další vouchery, vytvoříme nové order_item řádky (kopie s quantity=1 a novým voucher_id)
                    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.product_id,
                        item.variant_id,
                        item.brand,
                        item.product_name,
                        1, // quantity = 1 pro každý voucher
                        item.unit_price,
                        item.unit_price, // total_price pro quantity=1
                        voucherResult.voucherId,
                      ]
                    );
                  }
                }
              } catch (createVoucherError) {
                // Error creating voucher
              }
            }
          }

          // Teď aktivujeme všechny vouchery
          const [activateResult] = await connection.execute(
            `UPDATE vouchers v
             JOIN order_items oi ON oi.voucher_id = v.id
             SET v.is_active = 1, v.activated_at = NOW(), v.order_id = ?
             WHERE oi.order_id = ?`,
            [orderId, orderId]
          );

          const affectedRows = (activateResult as any).affectedRows || 0;
          if (affectedRows > 0) {
            // Pošleme email s dárkovými poukazy
            try {
              // Načteme aktivované vouchery pro tuto objednávku
              const [vouchers] = await connection.execute(
                `SELECT code, value, created_at, expires_at FROM vouchers WHERE order_id = ?`,
                [orderId]
              );

              const voucherList = vouchers as any[];
              if (voucherList.length > 0 && customerEmail) {
                const [firstName, lastName] = customerName.split(' ').length > 1
                  ? customerName.split(' ').slice(0, 1).concat(customerName.split(' ').slice(1).join(' '))
                  : [customerName, ''];

                const voucherDeliveryEmail = voucherDeliveryEmailTemplate({
                  customerEmail,
                  firstName,
                  lastName,
                  orderNumber: data.refId,
                });

                const attachments = [];

                for (let i = 0; i < voucherList.length; i++) {
                  const voucher = voucherList[i];
                  const voucherHtml = generateVoucherHtml({
                    code: voucher.code,
                    value: parseFloat(String(voucher.value)),
                    currency: data.curr || 'Kč',
                    issueDate: new Date(voucher.created_at),
                    expiryDate: new Date(voucher.expires_at),
                  });

                  const voucherPdfBuffer = await generatePdfFromHtml(voucherHtml);

                  attachments.push({
                    filename: `darkovy_poukaz_${i + 1}.pdf`,
                    content: voucherPdfBuffer,
                    contentType: 'application/pdf',
                  });
                }

                voucherDeliveryEmail.attachments = attachments;
                await sendEmail(voucherDeliveryEmail);
              }
            } catch (voucherEmailError) {
              // Error sending voucher email
            }
          }
        } catch (voucherError) {
          // Error with voucher creation/activation
        }

        // Snížit stock pro všechny položky v objednávce (jen při úspěšné platbě)
        try {
          if (reservationSettlement.found) {
            console.log('[COMGATE WEBHOOK] Sklad byl vypořádán přes checkout rezervace', { orderId });
          } else {
            const [allOrderItems] = await connection.execute(
              `SELECT oi.variant_id, oi.product_id, oi.quantity FROM order_items oi WHERE oi.order_id = ?`,
              [orderId]
            );

            for (const item of allOrderItems as any[]) {
              if (!item.variant_id || !item.product_id) {
                continue;
              }

            // Zkontrolovat, zda je produkt bundle
            const [productTypeResult] = await connection.execute(
              `SELECT p.type as productType FROM products p WHERE p.id = ?`,
              [item.product_id]
            );

            if ((productTypeResult as any[]).length === 0) {
              continue;
            }

            const isBundle = (productTypeResult as any[])[0].productType === 'bundle';

            if (isBundle) {
              // Pro bundle - snížit stock produktů V BUNDLU
              const [bundleItems] = await connection.execute(
                `SELECT
                  p.id as product_id,
                  pv.volume_ml,
                  pv.kind,
                  pv.id AS variant_id
                FROM bundles b
                JOIN bundle_items bi ON bi.bundle_id = b.id
                JOIN product_variants pv ON pv.id = bi.product_variant_id
                JOIN products p ON p.id = pv.product_id
                WHERE b.product_id = ?`,
                [item.product_id]
              );

              for (const bundleItem of bundleItems as any[]) {
                if (!bundleItem.product_id || !bundleItem.volume_ml) {
                  continue;
                }

                if ((bundleItem.kind || 'SAMPLE') === 'SAMPLE') {
                  await connection.execute(
                    `UPDATE product_stock SET stock = GREATEST(0, stock - ?) WHERE product_id = ?`,
                    [bundleItem.volume_ml * (item.quantity || 1), bundleItem.product_id]
                  );
                } else {
                  await connection.execute(
                    `UPDATE product_variants SET stock = GREATEST(0, stock - ?) WHERE id = ?`,
                    [item.quantity || 1, bundleItem.variant_id]
                  );
                }
              }
            } else {
              // Pro normální produkty - snížit stock produktu
              const [variantRows] = await connection.execute(
                `SELECT pv.volume_ml, pv.kind FROM product_variants pv WHERE pv.id = ?`,
                [item.variant_id]
              );

              if ((variantRows as any[]).length > 0) {
                const variant = (variantRows as any[])[0];
                if ((variant.kind || 'SAMPLE') === 'SAMPLE') {
                  await connection.execute(
                    `UPDATE product_stock SET stock = GREATEST(0, stock - ?) WHERE product_id = ?`,
                    [variant.volume_ml * (item.quantity || 1), item.product_id]
                  );
                } else {
                  await connection.execute(
                    `UPDATE product_variants SET stock = GREATEST(0, stock - ?) WHERE id = ?`,
                    [item.quantity || 1, item.variant_id]
                  );
                }
              }
            }
            }
          }
        } catch (stockError) {
          // Error updating stock
        }
      }
    } finally {
      await connection.end();
    }

    // Vrátíme 200 OK aby COMGATE věděl, že jsme zpracovali webhook
    console.log('[COMGATE WEBHOOK] Webhook processed successfully', {
      refId: (req.body?.refId as string) || 'unknown',
      status: (req.body?.status as string) || 'unknown',
    });
    res.status(200).json({
      success: true,
      message: 'Webhook přijat a zpracován',
    });
  } catch (error) {
    // Error processing webhook
    // Return 200 so COMGATE knows we received the notification
    return res.status(200).json({
      message: 'OK',
      error: error instanceof Error ? error.message : 'Chyba serveru',
    });
  }
}