type DatabaseConnection = {
  execute: (sql: string, values?: unknown[]) => Promise<[unknown, unknown]>;
  beginTransaction: () => Promise<void>;
  commit: () => Promise<void>;
  rollback: () => Promise<void>;
};

export type CheckoutItem = {
  productId: number;
  variantId: number;
  name: string;
  brand: string;
  volume: string;
  quantity: number;
  price: number;
  voucherId?: number | null;
};

type AuthoritativeItem = CheckoutItem & {
  kind: 'SAMPLE' | 'BOTTLE' | 'OTHER';
  volumeMl: number;
  productType: string;
};

type Requirement = {
  productId: number;
  variantId: number;
  kind: 'SAMPLE' | 'BOTTLE';
  quantity: number;
  productName: string;
  supplierManaged?: boolean;
};

export class CheckoutInventoryError extends Error {
  constructor(
    message: string,
    public readonly status = 409,
    public readonly code = 'STOCK_CHANGED',
    public readonly details?: unknown,
  ) {
    super(message);
    this.name = 'CheckoutInventoryError';
  }
}

const rows = <T>(result: unknown): T[] => result as T[];
const placeholders = (length: number) => Array.from({ length }, () => '?').join(',');
const sleep = (milliseconds: number) => new Promise((resolve) => setTimeout(resolve, milliseconds));

async function wakePriceWorker() {
  const url = process.env.CHECKOUT_WORKER_WAKE_URL?.trim();
  const secret = process.env.CHECKOUT_WORKER_SECRET?.trim();
  if (!url || !secret) {
    throw new CheckoutInventoryError(
      'Ověření skladu není správně nakonfigurované. Objednávku jsme raději nezaložili.',
      503,
      'SUPPLIER_CHECK_UNAVAILABLE',
    );
  }

  const controller = new AbortController();
  const timeout = setTimeout(() => controller.abort(), 15_000);
  try {
    const response = await fetch(url, {
      method: 'POST',
      headers: { Authorization: `Bearer ${secret}` },
      signal: controller.signal,
    });
    if (!response.ok) {
      throw new Error(`Worker wake endpoint odpověděl HTTP ${response.status}.`);
    }
  } catch (error) {
    console.error('Price worker se nepodařilo probudit.', error);
    throw new CheckoutInventoryError(
      'Aktuální dostupnost produktu se nepodařilo ověřit. Zkuste objednávku za chvíli znovu.',
      503,
      'SUPPLIER_CHECK_UNAVAILABLE',
    );
  } finally {
    clearTimeout(timeout);
  }
}

export async function ensureStockReservationSchema(connection: DatabaseConnection) {
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS stock_reservations (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      order_id INT NOT NULL,
      product_id INT NOT NULL,
      variant_id INT NOT NULL,
      stock_kind ENUM('SAMPLE','BOTTLE') NOT NULL,
      quantity INT NOT NULL,
      supplier_managed TINYINT(1) NOT NULL DEFAULT 0,
      status ENUM('ACTIVE','CONSUMED','RELEASED') NOT NULL DEFAULT 'ACTIVE',
      expires_at DATETIME NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY stock_reservations_order_stock_key (order_id, product_id, variant_id, stock_kind),
      KEY stock_reservations_variant_active_idx (variant_id, status, expires_at),
      KEY stock_reservations_product_active_idx (product_id, stock_kind, status, expires_at),
      KEY stock_reservations_order_idx (order_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `);
  await connection.execute(
    'ALTER TABLE stock_reservations ADD COLUMN IF NOT EXISTS supplier_managed TINYINT(1) NOT NULL DEFAULT 0 AFTER quantity',
  );
}

export async function refreshTrackedSupplierVariants(
  connection: DatabaseConnection,
  variantIds: number[],
) {
  if (process.env.CHECKOUT_LIVE_SUPPLIER_CHECK === 'false' || variantIds.length === 0) return;

  const uniqueVariantIds = [...new Set(variantIds.filter(Number.isInteger))];
  const [trackedResult] = await connection.execute(
    `SELECT pv.id AS variant_id, MAX(pl.stock_checked_at) AS stock_checked_at
       FROM product_variants pv
       JOIN product_links pl ON pl.variant_id = pv.id AND pl.is_active = 1
      WHERE pv.id IN (${placeholders(uniqueVariantIds.length)})
        AND COALESCE(pv.kind, 'SAMPLE') <> 'SAMPLE'
        AND pl.source_stock IS NOT NULL
      GROUP BY pv.id`,
    uniqueVariantIds,
  );

  const freshnessSeconds = Math.max(5, Number(process.env.CHECKOUT_SUPPLIER_FRESH_SECONDS || 30));
  const now = Date.now();
  const tracked = rows<{ variant_id: number; stock_checked_at: string | Date | null }>(trackedResult)
    .filter((item) => !item.stock_checked_at || now - new Date(item.stock_checked_at).getTime() > freshnessSeconds * 1000);
  if (tracked.length === 0) return;

  const runs: Array<{ id: number; variantId: number; requestedAt: Date }> = [];
  for (const item of tracked) {
    const [activeResult] = await connection.execute(
      `SELECT id, created_at
         FROM price_worker_runs
        WHERE variant_id = ? AND status IN ('QUEUED','RUNNING')
        ORDER BY id DESC LIMIT 1`,
      [item.variant_id],
    );
    const active = rows<{ id: number; created_at: string | Date }>(activeResult)[0];
    if (active) {
      runs.push({ id: active.id, variantId: item.variant_id, requestedAt: new Date(active.created_at) });
      continue;
    }
    const requestedAt = new Date();
    const [insertResult] = await connection.execute(
      `INSERT INTO price_worker_runs (status, variant_id, created_at) VALUES ('QUEUED', ?, NOW())`,
      [item.variant_id],
    );
    runs.push({ id: Number((insertResult as { insertId: number }).insertId), variantId: item.variant_id, requestedAt });
  }

  await wakePriceWorker();

  const timeoutMs = Math.max(5000, Number(process.env.CHECKOUT_SUPPLIER_TIMEOUT_MS || 45000));
  const deadline = Date.now() + timeoutMs;
  const pending = new Map(runs.map((run) => [run.id, run]));
  while (pending.size > 0 && Date.now() < deadline) {
    const ids = [...pending.keys()];
    const [runResult] = await connection.execute(
      `SELECT id, status, error_message FROM price_worker_runs WHERE id IN (${placeholders(ids.length)})`,
      ids,
    );
    for (const run of rows<{ id: number; status: string; error_message: string | null }>(runResult)) {
      if (['COMPLETED', 'COMPLETED_WITH_ERRORS', 'FAILED'].includes(run.status)) {
        const expected = pending.get(run.id)!;
        pending.delete(run.id);
        if (run.status === 'FAILED') {
          throw new CheckoutInventoryError(
            'Aktuální dostupnost produktu se nepodařilo ověřit. Zkuste objednávku za chvíli znovu.',
            503,
            'SUPPLIER_CHECK_FAILED',
            { variantId: expected.variantId },
          );
        }
      }
    }
    if (pending.size > 0) await sleep(400);
  }
  if (pending.size > 0) {
    throw new CheckoutInventoryError(
      'Ověření dostupnosti u dodavatele trvá příliš dlouho. Zkuste objednávku za chvíli znovu.',
      503,
      'SUPPLIER_CHECK_TIMEOUT',
    );
  }

  for (const run of runs) {
    const checkedAfter = new Date(run.requestedAt.getTime() - 2000);
    const [freshResult] = await connection.execute(
      `SELECT COUNT(*) AS total_count,
              SUM(status = 'OK' AND source_stock IS NOT NULL AND stock_checked_at >= ?) AS fresh_count,
              SUM(status = 'ERROR' AND source_stock = 0 AND stock_checked_at >= ?) AS unavailable_count
         FROM product_links
        WHERE variant_id = ? AND is_active = 1`,
      [checkedAfter, checkedAfter, run.variantId],
    );
    const freshness = rows<{
      total_count: number;
      fresh_count: number;
      unavailable_count: number;
    }>(freshResult)[0];
    if (
      Number(freshness?.total_count || 0) > 0
      && Number(freshness?.unavailable_count || 0) === Number(freshness?.total_count || 0)
    ) {
      throw new CheckoutInventoryError(
        'Produkt už není u dodavatele dostupný. Obnovte prosím košík.',
        409,
        'SUPPLIER_OUT_OF_STOCK',
        { variantId: run.variantId },
      );
    }
    if (Number(freshness?.fresh_count || 0) === 0) {
      throw new CheckoutInventoryError(
        'Dodavatel nepotvrdil aktuální sklad produktu. Objednávku jsme raději nezaložili.',
        503,
        'SUPPLIER_STOCK_UNCONFIRMED',
        { variantId: run.variantId },
      );
    }
  }
}

export async function loadAuthoritativeCheckoutItems(
  connection: DatabaseConnection,
  submittedItems: CheckoutItem[],
): Promise<AuthoritativeItem[]> {
  const variantIds = [...new Set(submittedItems.map((item) => Number(item.variantId)).filter(Number.isInteger))];
  if (variantIds.length !== submittedItems.length) {
    throw new CheckoutInventoryError('Košík obsahuje neplatnou nebo duplicitní variantu.', 400, 'INVALID_CART');
  }
  const [variantResult] = await connection.execute(
    `SELECT pv.id AS variant_id, pv.product_id, pv.label, pv.volume_ml, pv.kind, pv.is_active,
            p.name AS product_name, p.type AS product_type, COALESCE(b.name, '') AS brand_name,
            (SELECT pr.price FROM prices pr
              WHERE pr.variant_id = pv.id AND pr.valid_to IS NULL
              ORDER BY pr.valid_from DESC, pr.id DESC LIMIT 1) AS current_price
       FROM product_variants pv
       JOIN products p ON p.id = pv.product_id
       LEFT JOIN brands b ON b.id = p.brand_id
      WHERE pv.id IN (${placeholders(variantIds.length)})`,
    variantIds,
  );
  const variants = new Map(rows<any>(variantResult).map((variant) => [Number(variant.variant_id), variant]));

  return submittedItems.map((item) => {
    const variant = variants.get(Number(item.variantId));
    const quantity = Number(item.quantity);
    if (!variant || Number(variant.product_id) !== Number(item.productId)) {
      throw new CheckoutInventoryError(`Varianta produktu „${item.name}“ už neexistuje.`, 404, 'VARIANT_NOT_FOUND');
    }
    if (!variant.is_active) {
      throw new CheckoutInventoryError(`Varianta produktu „${variant.product_name}“ už není aktivní.`);
    }
    if (!Number.isInteger(quantity) || quantity < 1 || quantity > 50) {
      throw new CheckoutInventoryError(`Neplatné množství produktu „${variant.product_name}“.`, 400, 'INVALID_QUANTITY');
    }
    const price = Number(variant.current_price);
    if (!Number.isFinite(price) || price <= 0) {
      throw new CheckoutInventoryError(`Produkt „${variant.product_name}“ momentálně nemá platnou cenu.`, 409, 'PRICE_UNAVAILABLE');
    }
    return {
      ...item,
      productId: Number(variant.product_id),
      variantId: Number(variant.variant_id),
      name: String(variant.product_name),
      brand: String(variant.brand_name || ''),
      volume: String(variant.label || ''),
      quantity,
      price,
      kind: (variant.kind || 'SAMPLE') as AuthoritativeItem['kind'],
      volumeMl: Number(variant.volume_ml) || 0,
      productType: String(variant.product_type || 'perfume'),
    };
  });
}

async function buildRequirements(connection: DatabaseConnection, items: AuthoritativeItem[]) {
  const requirements = new Map<string, Requirement>();
  const add = (requirement: Requirement) => {
    const key = `${requirement.kind}:${requirement.kind === 'SAMPLE' ? requirement.productId : requirement.variantId}`;
    const current = requirements.get(key);
    if (current) current.quantity += requirement.quantity;
    else requirements.set(key, requirement);
  };

  for (const item of items) {
    if (item.productType === 'voucher') continue;
    if (item.productType !== 'bundle') {
      const kind = item.kind === 'SAMPLE' ? 'SAMPLE' : 'BOTTLE';
      add({
        productId: item.productId,
        variantId: item.variantId,
        kind,
        quantity: kind === 'SAMPLE' ? item.volumeMl * item.quantity : item.quantity,
        productName: item.name,
      });
      continue;
    }

    const [bundleResult] = await connection.execute(
      `SELECT selected_pv.product_id, selected_pv.id AS variant_id, selected_pv.kind,
              selected_pv.volume_ml, p.name AS product_name
         FROM bundles b
         JOIN bundle_items bi ON bi.bundle_id = b.id
         JOIN product_variants source_pv ON source_pv.id = bi.product_variant_id
         JOIN product_variants selected_pv
           ON selected_pv.product_id = source_pv.product_id
          AND selected_pv.volume_ml = ? AND selected_pv.is_active = 1
         JOIN products p ON p.id = selected_pv.product_id
        WHERE b.product_id = ?`,
      [item.volumeMl, item.productId],
    );
    const bundleItems = rows<any>(bundleResult);
    if (bundleItems.length === 0) {
      throw new CheckoutInventoryError(`Obsah sady „${item.name}“ se nepodařilo ověřit.`, 409, 'BUNDLE_UNAVAILABLE');
    }
    for (const bundled of bundleItems) {
      const kind = (bundled.kind || 'SAMPLE') === 'SAMPLE' ? 'SAMPLE' : 'BOTTLE';
      add({
        productId: Number(bundled.product_id),
        variantId: Number(bundled.variant_id),
        kind,
        quantity: kind === 'SAMPLE'
          ? Number(bundled.volume_ml) * item.quantity
          : item.quantity,
        productName: String(bundled.product_name),
      });
    }
  }
  const result = [...requirements.values()];
  const variantIds = [...new Set(result.map((item) => item.variantId))];
  if (variantIds.length > 0) {
    const [supplierResult] = await connection.execute(
      `SELECT DISTINCT variant_id FROM product_links
        WHERE is_active = 1 AND source_stock IS NOT NULL
          AND variant_id IN (${placeholders(variantIds.length)})`,
      variantIds,
    );
    const supplierVariants = new Set(
      rows<{ variant_id: number }>(supplierResult).map((item) => Number(item.variant_id)),
    );
    for (const requirement of result) {
      requirement.supplierManaged = supplierVariants.has(requirement.variantId);
    }
  }
  return result;
}

export async function assertCurrentCheckoutStock(
  connection: DatabaseConnection,
  items: AuthoritativeItem[],
) {
  const requirements = await buildRequirements(connection, items);

  for (const requirement of requirements) {
    const [stockResult] = requirement.kind === 'SAMPLE'
      ? await connection.execute(
          'SELECT stock FROM product_stock WHERE product_id = ?',
          [requirement.productId],
        )
      : await connection.execute(
          'SELECT stock FROM product_variants WHERE id = ?',
          [requirement.variantId],
        );
    const available = Number(rows<{ stock: number }>(stockResult)[0]?.stock || 0);
    if (available < requirement.quantity) {
      throw new CheckoutInventoryError(
        `Produkt „${requirement.productName}“ už není dostupný v požadovaném množství. Obnovte prosím košík.`,
        409,
        'STOCK_CHANGED',
        { productId: requirement.productId, variantId: requirement.variantId },
      );
    }
  }
}

export async function reserveOrderStock(
  connection: DatabaseConnection,
  orderId: number,
  items: AuthoritativeItem[],
) {
  const requirements = await buildRequirements(connection, items);
  const expiresAt = new Date(Date.now() + Math.max(15, Number(process.env.CHECKOUT_RESERVATION_MINUTES || 120)) * 60_000);

  for (const requirement of requirements) {
    let available = 0;
    if (requirement.kind === 'SAMPLE') {
      const [stockResult] = await connection.execute(
        'SELECT stock FROM product_stock WHERE product_id = ? FOR UPDATE',
        [requirement.productId],
      );
      available = Number(rows<{ stock: number }>(stockResult)[0]?.stock || 0);
    } else {
      const [stockResult] = await connection.execute(
        'SELECT stock FROM product_variants WHERE id = ? FOR UPDATE',
        [requirement.variantId],
      );
      available = Number(rows<{ stock: number }>(stockResult)[0]?.stock || 0);
    }

    const [reservedResult] = await connection.execute(
      `SELECT COALESCE(SUM(quantity), 0) AS reserved
         FROM stock_reservations
        WHERE (status = 'ACTIVE'
           OR (status = 'CONSUMED' AND supplier_managed = 1 AND expires_at > NOW()))
          AND stock_kind = ? AND ${requirement.kind === 'SAMPLE' ? 'product_id = ?' : 'variant_id = ?'}`,
      [requirement.kind, requirement.kind === 'SAMPLE' ? requirement.productId : requirement.variantId],
    );
    const reserved = Number(rows<{ reserved: number }>(reservedResult)[0]?.reserved || 0);
    if (available - reserved < requirement.quantity) {
      throw new CheckoutInventoryError(
        `Produkt „${requirement.productName}“ už není dostupný v požadovaném množství. Košík jsme nezaplatili.`,
        409,
        'STOCK_CHANGED',
        { productId: requirement.productId, variantId: requirement.variantId },
      );
    }

    await connection.execute(
      `INSERT INTO stock_reservations
        (order_id, product_id, variant_id, stock_kind, quantity, supplier_managed, status, expires_at)
       VALUES (?, ?, ?, ?, ?, ?, 'ACTIVE', ?)`,
      [orderId, requirement.productId, requirement.variantId, requirement.kind, requirement.quantity, requirement.supplierManaged ? 1 : 0, expiresAt],
    );
  }
}

export async function settleOrderReservations(
  connection: DatabaseConnection,
  orderId: number,
  outcome: 'PAID' | 'RELEASED',
) {
  await ensureStockReservationSchema(connection);
  await connection.beginTransaction();
  try {
    const [reservationResult] = await connection.execute(
      `SELECT id, product_id, variant_id, stock_kind, quantity, supplier_managed, status
         FROM stock_reservations WHERE order_id = ? FOR UPDATE`,
      [orderId],
    );
    const reservations = rows<any>(reservationResult);
    const active = reservations.filter((reservation) => reservation.status === 'ACTIVE');
    if (outcome === 'PAID') {
      for (const reservation of active) {
        if (reservation.supplier_managed) continue;
        if (reservation.stock_kind === 'SAMPLE') {
          await connection.execute(
            'UPDATE product_stock SET stock = GREATEST(0, stock - ?) WHERE product_id = ?',
            [reservation.quantity, reservation.product_id],
          );
        } else {
          await connection.execute(
            'UPDATE product_variants SET stock = GREATEST(0, stock - ?) WHERE id = ?',
            [reservation.quantity, reservation.variant_id],
          );
        }
      }
      await connection.execute(
        `UPDATE stock_reservations
            SET status = 'CONSUMED',
                expires_at = CASE WHEN supplier_managed = 1
                  THEN DATE_ADD(NOW(), INTERVAL ? HOUR) ELSE expires_at END,
                updated_at = NOW()
          WHERE order_id = ? AND status = 'ACTIVE'`,
        [Math.max(24, Number(process.env.CHECKOUT_SUPPLIER_COMMIT_HOURS || 72)), orderId],
      );
    } else {
      await connection.execute(
        `UPDATE stock_reservations SET status = 'RELEASED', updated_at = NOW()
          WHERE order_id = ? AND status = 'ACTIVE'`,
        [orderId],
      );
    }
    await connection.commit();
    return { found: reservations.length > 0, changed: active.length > 0 };
  } catch (error) {
    await connection.rollback();
    throw error;
  }
}