type SqlConnection = {
  execute: (sql: string, values?: unknown[]) => Promise<unknown>
}

export type CheckoutFailureStage =
  | 'create_order'
  | 'create_order_items'
  | 'create_shipping'
  | 'create_address'
  | 'reserve_stock'
  | 'commit'

export interface CheckoutFailureEntry {
  attemptedOrderId?: number | null
  orderNumber?: string | null
  stage: CheckoutFailureStage
  error: unknown
  requestPath?: string | null
  userAgent?: string | null
}

export async function ensureCheckoutFailureLogSchema(connection: SqlConnection) {
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS checkout_failure_logs (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      attempted_order_id BIGINT UNSIGNED NULL,
      order_number VARCHAR(32) NULL,
      stage VARCHAR(64) NOT NULL,
      error_code VARCHAR(100) NULL,
      error_number INT NULL,
      sql_state VARCHAR(20) NULL,
      error_message TEXT NOT NULL,
      error_details JSON NULL,
      request_path VARCHAR(255) NULL,
      user_agent VARCHAR(500) NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_checkout_failure_created_at (created_at),
      KEY idx_checkout_failure_attempted_order (attempted_order_id),
      KEY idx_checkout_failure_stage (stage)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)
}

function serializeError(error: unknown) {
  if (!(error instanceof Error) && (typeof error !== 'object' || error === null)) {
    return {
      code: null,
      errno: null,
      sqlState: null,
      message: String(error),
      details: null,
    }
  }

  const value = error as Error & {
    code?: string
    errno?: number
    sqlState?: string
    details?: unknown
  }

  return {
    code: value.code || null,
    errno: Number.isInteger(value.errno) ? value.errno! : null,
    sqlState: value.sqlState || null,
    message: value.message || 'Neznámá chyba checkoutu',
    details: value.details ?? null,
  }
}

export async function recordCheckoutFailure(connection: SqlConnection, entry: CheckoutFailureEntry) {
  await ensureCheckoutFailureLogSchema(connection)
  const error = serializeError(entry.error)

  await connection.execute(
    `INSERT INTO checkout_failure_logs
      (attempted_order_id, order_number, stage, error_code, error_number, sql_state, error_message, error_details, request_path, user_agent)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      entry.attemptedOrderId || null,
      entry.orderNumber || null,
      entry.stage,
      error.code,
      error.errno,
      error.sqlState,
      error.message.slice(0, 65535),
      error.details === null ? null : JSON.stringify(error.details),
      entry.requestPath || null,
      entry.userAgent?.slice(0, 500) || null,
    ],
  )
}